RFC 1 -- State Machine Stored as a Sqlite3 database: Functionality and Behavior

mein1156 commented 10 years ago

A database is a powerful storage and organizational tool. Sqlite3 is a powerful embedded database that requires no installation or setup and can be seamlessly integrated into Python. Also, a Sqlite3 database can be ran in memory only rather than a file, which would increase retrieval speed (if a problem).

As I do not know anything about Sqlacademy, I’ll discuss everything in terms of real SQL queries as necessary. Since this database will probably be in its own class, then everyone not involved with this programming will only be concerned with the API and how events are handled (See RFC 2)

Terminology: PK -- Primary Key FK -- Foreign Key X.Y -- Column Y in Table X

-- optional GM -- The game engine

Overview This is my idea only just to get something going. Very rough and incomplete.

Information to store in the database I envision that all data can be stored in the database. This can include data on cards. Why the excess amount of tables? I have normalized all the tables to provide a great deal of error prevention and detection. By using foreign constraints, most values can be protected against incorrect values and all values can be referenced between tables by number and not strings, which would also be important for the API in whatever form.

Also, multiple rules can simply be built into the database, for example, if a ship tries to go from one planet to another planet in another galaxy, the Star System.ID will not match so the move is not possible. If the ship tries to leave the system, it must do so through a Star System Path.ID where the in or out is the current Star System. This can be overwritten for things like getting lost in space. Also triggers can be used to enforce some rules, such as transporting units (some units cannot leave the surface, or the unit is on a starship, etc.). Of course, this is not necessary. All of these rules can also be covered by a GM outside of the database.

Map The map is comprised of star systems. Each star system is comprised of a name, a star, a number of planets, paths to jump points between star systems, and drifts. Each star has a number (ID number?) and 1-3 planets and each planet contains environs, with a name, a loyalty marker, PB marker, environs, a number of supported troops, possibly a name of a sovereign and monster.

Layout of databases for Map

First, a list of all possible locations for spaceships. This includes all environs on all planets, all orbits (2 for both interception and noninterception stacks), all stars, all drifts. Some locations are given numbers on the board. The environs and orbits are not unfortunately. Also the environs do not have unique names.

This list would be comprised of numbers that would represent all possible locations that an object can move to, move from, be at. Each star system has a number and so does each planet. However, each environ, drift, etc. does not have a unique ID in regards to the entire game.

A possible system to develop a consistent id for all locations in the game using the original numbers.

51 -- Star 510X -- Drifts (or 518,9) 511 -- Planet 5111,2 -- Orbits 5113,4,5 -- Environs

Good thing about the current numbering system is that all stars have 2 digits. Each star has at least one planet and up to 3, and each planet has up to 2 environs, so leading zeros are not required for this method.

As mentioned in RFC 2, having a set of ids to populate to the players will decrease packet size and be a lot easier to manage. The clients might request the map, and the ids would be included so that movement would simply talking about integers.

By looking at the Location.Type field, we can detect the current type of location we are at for any other tables Locations field.

Star Systems ID(PK:FK:Locations.ID)|Star System Name | Capital (FK:Planets.ID) 51 | Zakir | ... ....

Star Systems Paths Two possible designs. Either assign in and out to be from the system with the lower number to that of the higher number:

Path (PK) | In (FK:Locations.ID) | Out (FK:Locations.ID)

Or

Simply a list of pairs with the primary key being the uniqueness of the pairs. We can search (SELECT) the Star System Paths for locations with In or Out with the same location as the star. For an environ, this is found by joining databases together to find the star system of the planet, etc.

Star Systems ID(PK:FK:Locations.ID) | Star System Name | Capital (FK:Environs.ID)

Drift ID(PK:FK:Locations.ID) | Star System (FK:Star Systems.ID)

Environs ID | Type 0 | Water …

Creature ID(PK) | Name 0 | … …

Default values will be entered by the startup engine.

Cards There are 140 cards in the game Card Number (PK) | Type (FK:Card Type.ID) 0 | … …

And for each type of cards Each character has a special ability. The question is how to encode this functionality. If we develop an API, then the field can be encoded in that API (e.g. JSON) to be used later by the GM.

Possessions are basically personal possessions and starships. Each can be in a separate table.

Possession Cards ID (PK:FK:Card Type.ID) | Type | Name

Possession Abilities -- a line for each ability ID (PK) | Possession (FK:Possession Cards.ID) | Text | Ability(API)

Action Cards ID (PK:FK:Card Type.ID)

Each action card can (does) have multiple envrons. A row for each environ. Action Environs

Galactic Event Cards ID (PK:FK:Card Type.ID) | Result (API)

Assignment Cards ID (PK:FK:Card Type.ID) | Result (API)

And also units can be put into the database. I imagine that there exists a VIEW of all units in play using a UNION to display all of them as needs be.

Triggers provide additional error checking. For character units, we can check to see if the location is a valid environ or starship for example. This can also be part of the GM outside of the database

Also, I imagine two sets of IDs for non-location objects. The first will be the ID of the “abstract” object. Each type will have a unique id. When an object is created at some time, it is given another id representing its play id. Along with the location ids, non-location object information are sent. That way, when a unit is created or made, etc. we can talk about a single id and the player will know everything with that one “abstract” id. This is also part of RFC 2.

The rebel and imperial units are almost the same. Two different tables?

Allegiance ID (PK) | Description 0 | Rebel 1 | Imperial

Then an actual list of the military units in play:

Units: ID(PK) | Type(FK:Unit Types.ID) | Status | Damage | Location(Locations.ID)

How do we know if a PDB is up? We can use a SELECT CASE statement to get the information.

SELECT Status FROM Units WHERE ID = 45676

Mobile might be the same as undetected for Imperial units or add a mobile status. Don’t know what is best for adding different status for each different unit. Triggers or value constraints can limit value stored.

Retrieving information is also simple using this database design. If the player is asking for the location of all troops, the database can SELECT ID, LOCATION, STATUS FROM UNITS and then choose which of those units to display to the player. The GM can convert from ID to API ID if necessary for communication. Also, the IDs should not be sequential, rather randomly assigned so the opposing player cannot guess the number of units. Probably not a big deal.

Finally, the state of the machine can be stored in the database:

Turn Number ID (PK) | Turn

Score ID (PK ) | Score

Phasing Player ID (PK) | Description 0 | Rebel First Turn 1 | Imperial

mein1156 commented 10 years ago

Using a design like this, the backend would only need to consist of three classes.

CM (Connection Manager, SnakeMQ, etc.)
GM (Game Manager)
DM (Database Manager)

The job of the CM would be to manage the message flow between the different parts of the game, nothing more nothing less.

The job of the GM would be to dissect the API messages for calls to the database and also form the response based on the DM response.

The job of the DM would be most of the logic of the game and decide if a request is valid or not.

For example, if the client wanted to move units X1...Xn from id 1 to 2, the client would form the message according to the API and then send it to the CM. The connection manager would then unserialize it and pass the response to the GM. The GM would then call the move function of DM (e.g. DM.move([X1...X2], 2) and then the DM would check to see if the move is possible, if so, would return success or failure.

Inside of the DM, the logic would be if the current id is an environ, the id to move to should be another environ on the planet or the orbit. Those are the only choices. That would be easy using a database where the locations have types. All you would have to do, is to check that the new id is an environ on the same planet or the orbit of the same planet. Other moves would be similar.

I imagine that a lot of the code would write itself, especially the movement. With all the foreign key constrains in the database, movement could be check by one simple if statement.

If we wanted to manage multiple game, that would either have DM for each game and the GM would hold connections for each and each DM would join a default table that held the basic relations.

Battles could be managed by the GM by pulling information from the database, figuring the results, sending requests or interrupts for the clients to act on, then storing the new information in the database by calling, DM.remove_unit(X2) and DM.injure(X3, 2), etc. Or call DM.injure(X3, 2) and have the DM kill the unit if needed and then return the new stats.

The possibilities are endless so that is why I think we should have a good detailed plan going forward for this.

hallister commented 10 years ago

We have several issues up regarding databases/api so going to close these for now.

ui-cs383 / Freedom-Galaxy

RFC 1 -- State Machine Stored as a Sqlite3 database: Functionality and Behavior #25