Thank you for coming or at least being in this room already. You're really helping my numbers Hopefully you find what I'm about to talk about kind of interesting you want to join in to For those who actually are here because they know of the schema first tournament also welcome and hopefully it's gonna be a good time

Welcome pizza, please. Enjoy There's lots so What the schema verse is if you haven't found out is a game that I've developed Directly inside the Postgres database so everything about the game is done using SQL commands and

It's all using features within Postgres like views triggers rules Basically any any little thing that I could use security wise to kind of build a game and enforce rules I used in this game So Josh Burkus came to me and said I want to bring a tournament of the game to the conference So that's why we're here for the next

Roughly 24 hours I'm going to start an instance of the tournament and everyone has a chance to connect to it and Actually go and play and issue commands and compete for some pretty cool prizes as well So I'll start getting into more specific Specifics of the game in a bit. Let's go over my huge slide deck here

If I bore you, I apologize. Just eat more pizza So first of all, I have to thank these people Mostly PG con for bringing me here to do this. This is pretty awesome It's just a game that I wrote and now I get to come share with a bunch of people Who love Postgres as much as I do?

Postgres experts Also have a hand in the pizza on the table. So they are definitely one to thank There's also been a lot of prizes supplied by them. So I owe them a big. Thank you NaviCat For some reason like my game quite a bit and they tend to support me

They they also gave a prize for the Def Con tournament that there was last year An info bunker is my host currently They give me a server to host the databases on for absolutely no reason and I'll never know why Because they usually don't want press But fun little thing. It's a data center. That's actually in a bunker. I thought that's pretty cool. So anyways

those are my sponsors and They're awesome as I said, so the important part about the tournament, what are you actually competing for? And I don't mean how do you win the game yet? Sorry? But what is there for prizes first prize is going to be a championship hoodie

Which seems super cool. I'm really excited to see it I haven't actually seen it, but I know my designer did a really good job on the actual graphics So I assume it translated well into sweater. I don't know what size it is yet but

Hopefully hopefully it works out First place will also receive a signed 9.2 poster, which is kind of exciting as well The next prize is for best hack Because I've developed this game in a database I've put a lot of time and effort into creating a lot of as I said rules triggers functions

Anything and everything to keep the user at bay and only do certain things But of course everyone makes mistakes if you can break out of anything SQL injections come to mind and I think what you've done is fairly cool Then you have a chance at winning a license of navicat for postgres, which is really nice software

As far as database clients go I've used it quite a bit Crashes less than most We're also giving away a postgres 9.2 t-shirt with that award If for some reason there's no great hack Then I'm going to default to doing a random user

So as long as you sign up to play you can potentially get this like $300 worth of software and a pretty sweet shirt. So That's kind of incentive just for signing up In the game there's a bunch of trophies the trophies themselves are just sequel scripts that will run and see what user won it

So I'm going to give a I'm going to give a 9-1 poster to anyone who wins these prizes here Assuming the same person doesn't win them all which should be possible If you want to know what these trophies actually are Then you can go in take a look at the code and see what it's actually looking for

It's just in the trophy table Enough you select one thing about post or about the game if you wanted to add more trophies The way people could win you can actually insert into the trophy table with your own code as well to make new ways As far as what these kind of are size matter is going to be basically the person with the greatest fleet

So it's a space game The whole point is to build some plan or conquer planets Build up your resources by mining them build more fleets go out conquer more planets, etc So if you have the biggest number of ships Basically the largest count of your my ships table

Then you're gonna get that the discoverer is the person who goes and actually finds the most planets and conquers the most planets also known as Chris greatest Empire is the person at the end of the game currently holding the most planets and First blood is an easy one first blood is going to be the first person to actually attack another person

Let me rephrase that First blood is the first person to attack something Friendly fire does is on and

Finally, I have another postgres 9.2 t-shirt that I can give away to anyone who registers so Hopefully I'm pretty excited about these prizes So as far as the actual tournament details go I've got some handouts up here that have basically the same information

The host for the tournament is PG con dot scheme averse comm This again this tournament is played directly in a postgres database when you register The account you use to register is a user on the postgres database So any client any programming language you want whatever you're familiar with and how you work with postgres every day

you can then use to play this game if you'd like to write a pearls a Little pearl program to go and connect and manage everything you can do so If you want to use PG admin or PSQL to do your hacking around there's no problem with that either

So basically anything that can touch postgres can be used to play this game So that's just the details up there If at any point you need an issue you have an issue and you need to get a hold of me The game isn't perfect There are certainly bugs here and there Or it's also not always explained well

You can see I've got a list of things Basically type schema verse into something on the internet and it's going to come to me eventually Google just stopped correcting you when you type schema verse no longer says schema version So that's kind of my excitement. I added a word

So on to what the game actually is To do this, I'm actually just going to go to the website and kind of well What I was going to do is just kind of open discussion about the game Because that seems a lot more interesting you most of the time than me standing up here and going over

Over and over and over every single row or every single view every single table every single function that's available They're all there online, especially if you want to go to this link up here schema verse comm slash tutorial slash tutorial dot PHP That's on a public database right now. So if you just signed up here that account won't work there

But again registration is always there's a beautiful shirt that you can potentially win There's a number of people who can potentially get shirts

I'm pretty excited about the hoodie. Let's see the hoodie and we got pizza

So as far as the actual game goes the best way to look for a reference is to go to that tutorial link or just schema verse comm and go to learn and There's going to be a reference of every view every table and every function that you need to actually play the game I'm not going to stand up here and listen But I'll start giving you a little bit of kind of how to on what's going on in the game

so the first thing you do when you join up in the game is you're given a sorry it's When you start you're you're given one planet Each player has one planet. There's some planets that don't have anyone conquering it yet Boy, what you want to do is start building ships at that planet. So how do you build ships?

Well to manage your ships. There's a view called my ships So because this is sequel in order to build a new ship what you want to do is insert into my ships This will subtract money from your balance and then create that ship There's some extra changes you can do based on what columns you pass through so long as they fit into the rules

But generally it can be that easy the easiest way is just give it Give it insert into my ships name values with a name So Once you've done that it's going to automatically build that ship if you haven't given it a location

Then it will use the location of any random planet you've conquered since it's the beginning of the game You only have one planet. It's going to build that ship directly on that planet You can only build ships on points where there's a planet that you're conqueror of So I can't just go build a ship beside Chris's planet and try and kill him early in the game. I

Have to actually build up on my planet and then use the move command to send my ships over there But before you want to go start discovering others first, you need more resources So as you create new ships by inserting

You can then use the mine command To tell you tell the system that you want that ship to mine that planet that it's currently on Mining planets does two things one. It gives you resources each tech I guess that's another thing I should get to but basic mining gives you resources which you then need to build more ships

upgrade your ships and then also including moving the ships because you need fuel to move the ships and It also does another thing mining conquers planets as well So when you do go to discover new planets in order to conquer it and call it your own

You have to mine it more than another player at that point in time So those are the two key things of move So the first thing you want to do after you've created your ship is start mining that planet So there's a mind function which just takes two arguments of a planet ID and a ship ID other way around ship ID and a planet ID or

Every ship also has state So if I know that this ship is always going to be mining this planet I can update the action column of the on the my ships table for that ship and the target To be that planet's ID number if you want to see what planet you're actually on

there's a planet's view that lists all the details for everyone and who's currently conquering everything and then you can just Call that down by your own player ID based on the Conqueror ID of that point So is this making any sense at all or how psychotic am I at this point really?

And The tutorial is awful at giving you a reason why it's not working But was that failing? Yeah, my cert expired your break. It's been busy

Mm-hmm

Yeah, I want to say no, but they like the more likely answer is yes, however if you log in right now and Over the court. Well, not right now. The tournament has started over the course of the next 15 or so minutes build up

enough miners to Steadily mine your home planet That way your resources keep coming in coming in so long as you're protecting your home planet Then you'll likely keep it So every planet has a couple of tributes every planet has a fuel amount that you can't see it might run out

Your mining might just stop working at that planet. It's not likely to happen too soon in the game, though It also has a mine limit. The mine limit is the amount of ships that can mine that planet each tick So if a mine limit is 30 You can build 30 ships there that keep mining that one planet and you'll likely always keep it conquered

The only way someone can beat you is by coming in and mining it Maybe 16 times when you're trying to mine it 15 times and then they've now conquered it over you and they can build their ships there So there is a way right now where you can if you're not really ready to play until later

You can at least have your script or have your system start preparing for you giving you resources So when you do log in tonight You've got all this money and then you can go out and attack If you do happen to have some time right now to put into it

The best idea would be to start scripting something Because no one wants to sit here for the next 24 hours and just type sequel command after sequel command The game has something called fleet scripts in it Basically the system itself will run scripts that you write on your behalf

Each tick I should really explain tick at this point. So the game is tick based So every minute approximately is a new tick each ship can do one action per tick So there's three kinds of actions. There's attack. There's repair and then there's mine

So you can choose one of those to perform each tick If You write a fleet script the system will run that script on your behalf each time So you don't have to sit there managing it You can just write some little AI that will then go and build it up so a simple one for your home mining planet would be

check or Check to see what the mine limit is of the planet Check to see how many ships I have on that location or around that location If it's let if the amount of ships I have is less then build another ship and Tell it to start mining and then the next time you've got a bit more resources and you say okay

Let's do this again and do this again So there's very simple even like five ten line pieces of code. You can start writing That'll start getting your resources Another one. So there's also So the my ships table or view I should say is for your own ships

if you want to see when enemies are approaching there's another table called ships in range and That'll show you roughly what health they have as well as where they are and what player they're from I admit there's a lot to it, but it's super addictive actually Once you start writing scripts and kind of play and getting to know the system then you start kind of

Tweaking here and there and you're always watching stats here. You look like you've got a question or cut There is a sample script and I was hoping to rewrite it before today because it it works really well about six months ago

now there's been a lot of changes to the game unfortunately, I Will what I'll do is I'll try and fix it up quickly after this talk and then I'll post on my Twitter feed So everyone who wants to go you should be able to just copy and paste the script and then you'll start playing

Or at least it will start playing for you What's the air you're getting on the and then you can't Yeah, but it's saying login complete and then you click something else. It just says

Yeah, it likes cookies in JavaScript and all sorts of stuff like that

So yeah, forgive me. This isn't actually like a real talk I Was just gonna go over some of the random stuff if you'd like I can stand up here and start playing the game, which is kind of seems

Yeah, actually those are pretty cool so There's been a bit of discussion lately about Visualizing this game. So I created this game. I'm horrible. Hopefully there's nothing embarrassing in this I'm absolutely horrible at anything design. This is why I created a game in a database Oh

Yeah, there we go But lately some people are talking about actually still visualizing what's going on and the results have been pretty cool So this is the universe on the public server One day and then this is the universe the next day. Each color is a different player

So you can kind of see how the different Strategies that everyone has kind of shows and how well it's happening Like purple just kind of takes over the right and then orange gets a little bit eaten and Somehow yellow just kind of goes everywhere

So there is One needs web access. There's another one. Someone did a while ago You cannot see that at all, but and I don't have internet right now, so I can't show you that one

Yeah, let me tether to myself and then maybe show this is there yeah

This is the yeah, Chris is kind of a jerk

The tournament itself hasn't started yet. So right now is just kind of like a little play area Yeah, I will be resetting it after I finish yapping up here. So then it'll start and then we'll play And maybe Chris will be nice and give you like a 10-minute head start or something before he starts his script

He's looking like no

Someone was just telling me they were using interactive lists to play And that seems awesome. I know another person who uses interactive Python to play. I think that's pretty fun, too Here's the other graph someone created there. That's pretty cool

Yeah, well I can go talk to this guy I don't know where he is but what's he using I think he says

But

Scoot this is working great the Roku app.com

Yeah, it's visual and the public game I'm going to try and use this and hopefully I have more success and

Peter Yeah, this is a this is the public server the one that you registered on my iPad is for PG con dot scheme verse calm It's not a website, but it's the host of the database for the tournament This one is accessible all the time

The rounds last a week instead of two days. So it's a bit of a different competition at times Yeah, that one there's a server up and running you can connect to it But the actual tournament itself hasn't started that'll be in about 20 minutes

PG con dot scheme reverse comm there's no there's no online presence for that. I Got lots of handouts Excessive amounts of handouts. I killed too many trees Okay, so how well can you see that?

So, let's do You don't want to play but you want to see what's going on There's a bunch of stats tables that'll kind of give you An interesting like current player stats if you want to see who's winning that one's gonna give you a lot of information

Current player tick stats is just the stats from the tick that just happened So that's kind of more what just happened. That's better for gaining advantage finding out who's suddenly winning My events is where Everything in the game really gets logged to if you create a ship it's gonna be logged there if you conquer a planet

It's gonna be logged there if you upgrade your ship It's gonna be logged there if you mind something it's gonna be logged there the table grows sensibly fairly fast But it's there my fleets is where you write your fleet script my player holds your player balance and

Your fuel amount or your fuel reserve. It also holds another interesting piece of information That's called the error channel. I Don't know if you're if everyone is familiar with the listen notify system in Postgres Because you're because the system is running scripts on your behalf it needed a way of informing you if there's errors that are happening

So what the system will do is if you type listen and then type the name of your air channel Then every time you run a query It's gonna return any new error messages with the payload of that map of that query. So That that secret number that each player has that's unique is found in your my player table

Your my player your inventory No one's actually created an item. So there's no inventory really if you want to create an item though There's an item table. Feel free to insert into it with whatever you want. I'm sorry. I

It's all done in cash in person So if you want if you want to just bring me some sort of sequel script in cash Then I'll get whatever you want in the system Yeah, yeah, it's a thousand dollars for drop database schema verse

My query store is mostly just used for something else there It's that if you if you're on the the scheme of us website There's a training wheel section Which is just like a nice little web interface where it gives you some helping functions and lets you run sequel within the browser

That's what stores those if you want to use it for storing something just for anything. It's there. It's kind of dumb store My ships again, that's where all your ships details are your ship stats your ships current trajectory

My ships flight recorder is kind of a fun one. I assume especially for visualization. I haven't seen anyone play with it though But that basically maps the path of every single ship you have that one also gets large my trade I Have never seen a user use my trades It's there. It probably took me the longest to code out of most things in the game. No one's ever used it though

I'm sure it's super buggy. So if you want to find a way to break the game start taking a look at trades You may have to find a friend or maybe you can trade with yourself I don't remember

Online players just going to show you who's online Which is basically what sessions are open on the database time Planets that's the view it holds the detail for all planets You can see all planets in the system at any time. You can see who's conquered them You can't see how much fuel they have but you can see Their mind limit so that's kind of like how big it is

Planets and range is just going to show you don't use that actually that's broken Yeah It's pulling from a cache and we stopped updating the cache because that was too intensive and then I realized why are we making a Cache when Postgres is pretty good at indexing so

Yeah, we we stopped reinventing the wheel and I just have an update of that view Yeah ships and range is certainly important Public variable you can store variables As much as you want That's useful if you're doing scripting and you want to kind of keep track of what you're doing or keep track of what you're attacking

Next or stuff like that use it. Whatever you want. You can either store text or numeric values Ships and range again, that's the important one we've Optimized that a lot. So I think it's useful now I think you can actually run a query against it and it will return something

I'm sorry writing a game in sequel is hard. All right Yeah, exactly Absolutely the game with that being said the game is on github

So if you want to learn anything about it or do something to it, it's there all the code is there Please enjoy. There's also that that air channel thing. I talked about there's a Python script in there called schema verse output stream SOS and That will just log into the database and just keep

Or keep pulling it to tell you if there's any new errors being listed. It's kind of a nice little Python script And then oh he's got actually talked about something useful All right Yeah

I'll just what's gonna be useful but quickly go over functions here and Then I'll leave I swear If you want to move your ships, you got to use something called ship course control if that's too long for you It's also you can use SCC. I

Would certainly suggest looking up. How do you do that on this website? Refuel When you mine it goes into your players balance and then you can distribute it to your ship with refuel These are the three actions you can do. They're great use them Convert resources converts your money to fuel or fuel to money. It's a one-to-one ratio

Don't ask me why they're different in the game at all at one point I had an idea of some sort of economy where every time you traded fuel it changed the value of it But you never trade money back to fuel. So that was stupid Upgrade is how you upgrade your ships and The rest are just a bunch of utilities that are there and super useful This is all on the website for the tutorial. If you would like to come register, please find me also, please eat more pizza

I bet you are