I almost finished my stock ticker game, still have some issues to deal with. The game is powered by an sql database. During the process, I did hit some walls in the design, which makes the limits visible now. Doing simple things like shuffling cards or player order can be a complete mess if done strictly in SQL. The advantage to do it strictly in SQL, it that the "rules" can be in a script and tested without even running the game making debugging easier. Individual queries can be tested too and the game becomes moddable. The "Put the rules in a script" was not originally anticipated, it became a useful emergent behavior.
That means that the way to design games has to be different if I want sql script friendly games. So the game design needs to be adapted to fir the new medium. I had a discussion with chat GPT about how to change my mindset during game design to make sure it is SQL friendly. I asked to use Twilight imperium and PTO 2 (which can be converted as a space opera) as example of mechanics that would fit well or not into an sqlscript only game. We roughly came up with the following guidelines:
1. Focus on state transition instead of series of steps(procedures) with decisions at each step.
2. Avoid ordering stuff, or manipulating the order of stuff (ex: deck of cards). Simultaneous actions is preferred to sequenced turn order.
3. Avoid Heterogeneous Collections (ex: mixing cards of different cards in the same deck)
4. Bulk transaction at once. Resolve all actions at once instead of individually.
5. Avoid pools of tokens or resources, replace with only a resource value (1 variable)
6. Deterministic resolution is better than random resolution.
7. Automated resolution (combat) is better than having choice at every steps
8. Avoid any interruption of opponent's actions (take that cards) or interactions.
When you look at those rules, it seems to make Play by Mail or Play by Web games fits more the criteria. In those kind of games, all players submit their order, and they are all resolved at the same time at the end of the turn.
Now the most complex part is how to design games under those rules that are strategically interesting. The AI came up with a golden rule:
"Interesting choices come from constraints + uncertainty, not from execution complexity."
It Identified 5 design patterns which are database friendly:
1. Overcommitment / Allocation: Player must divide limited resources. Pure SQL. Deep strategy.
2. Simultaneous Bluffing: Players commit without knowing other player's data. Emergent gameplay. No procedural complexity
3. Threshold Effects: Outcomes depend on totals that triggers effects. If sum > 10, trigger effect. Clean. Scales well.
4. Positional Advantage: (using Precomputed adjacency) area of control, Choke points, connection. No pathfinding needed during play. Strategy emerges from graph structure.
5. Delayed Payoffs: Actions resolve later. Encourages planning. Works well with logged orders.
So I'll have to rewire my brain and see how some of the ideas I have in my lists could fit this paradigm. There are ways to bend those rules, but that means it requires coding it differently. Like if I want tactical battles, I need interaction, so I cannot do the entire combat resolution automatically in an sqlscript.
I am getting close to finishing the implementation. I plugged in the AI and it plays the game pretty well, it defeats me all the time so far. I need to fix some details am implement the automated build and deployment process which will be a pain.
SQL script do have a logic, but a logic of its own. "Iterate over all records in a table" = easy. "Iterate only 3 times" = hard. So it is not super intuitive.
Now the SQL script is a side effect I did not expect when I designed my library. Of course I do some SQL command inside the C code, but it is much more annoying to manage, especially when reading data from the database. I need custom utility functions, or use my GUI that query the information for me. Else I need to call a function for each column to read in a record, not convenient.
Adding game logs using triggers is another side effect that works pretty well that I did not expect when I started making my library.
So if the queries could be only put in a file, that is way more convenient. It's also testable from the command line without even running the game. I can make a bash script that creates a new game and insert test data without opening the game. I could even make automated test using this method.
So If I could find a game format that can keep the rules in scripts, that will be fine with me. I will make the sacrifices listed in the first post to be easier to implement and maintain as scripts.
If you want more "classic" scripts: here are the new game scripts that create tables and insert data:
https://gitlab.com/larienna/stockticker/-/blob/dev/sql/newgame_create.sq...
https://gitlab.com/larienna/stockticker/-/blob/dev/sql/newgame_insert.sq...
Else there is the famous end of turn script that implements the core rules.
https://gitlab.com/larienna/stockticker/-/blob/dev/sql/endofturn.sql?ref...
I just insert the dice rolls into the database, and launch the script. Easy. Think of it as state transition. This is the list of steps that the game rules force a transition from one state to another.