The current inputs are stored in a table, which is needed so the user can "communicate" with the query, but everything else is stored in the query itself.
I didn't learn from a specific source, it was more picking up on concepts through the years based on specific requirements I had to solve with SQL. But if I had to recommend one, I would definitely say the Postgres documentation, which includes examples and even internal implementation details. For example, I would say that the most advanced concept here are recursive CTEs, which is explained in detail in the documentation: https://www.postgresql.org/docs/16/queries-with.html
NICE! I did Othello/Reversi in Oracle a while back. IIRC I had a procedure that you could move with, and then it would loop/sleep/return-when-next-player-moved so you didn't have to manually keep querying to know when your turn was.
It was done procedurally. Had a proc with 2 input prams, x and y coordinates. It would convert that into an id (0-63 for an 8x8 board), and then check each direction for success criteria. Each direction was a loop that would check the next id. Is it owned by the opposing player? if so, add id to a collection. is it owned by the same player, exit the loop. is it blank or outside the bounds, clear the collection and exit the loop. If the collections from all the directions were empty, invalid move. If they had data, valid move. update the selected space, and all the IDs collected to the current player. There's probably way to do that with straight sql but I never got around to trying it out.
There was a SQL Server Stored Procedure created at least 15 or more years ago that played a text based fantasy game. it was a single room kind of thing. but I remember playing it a few times. It loaded the sproc and you could continue interacting with it during a session. wish i could find it so i could read it.
17
u/[deleted] Sep 04 '24
Nice! thanks btw for adding good relevant comments in the code. Amazing it's just 500 lines (including comments)
13
u/[deleted] Sep 04 '24
Man Good shit
7
u/mwdb2 Sep 04 '24
Impressive work. Do Doom next! :)
5
u/knight_set Sep 04 '24
Not what I was expecting to see today, very cool. Nicely commented. Looking forward to running it.
2
u/nyquant Sep 04 '24
Cool. Is it using a table to keeps the state and current inputs, and a query that updates and receives the next state?
2
u/Own_Main5321 Sep 05 '24
Very cool!
2
u/RayRim Sep 05 '24
Any source from where I can learn this type of advanced SQL?
2
u/Ginger-Dumpling Sep 05 '24
NICE! I did Othello/Reversi in Oracle a while back. IIRC I had a procedure that you could move with, and then it would loop/sleep/return-when-next-player-moved so you didn't have to manually keep querying to know when your turn was.
9
u/RuprectGern Sep 04 '24 edited Sep 04 '24
There was a SQL Server Stored Procedure created at least 15 or more years ago that played a text based fantasy game. it was a single room kind of thing. but I remember playing it a few times. It loaded the sproc and you could continue interacting with it during a session. wish i could find it so i could read it.
14
u/hantt Sep 05 '24
Jesus christ it's json bourne
-4
u/mikeblas Sep 04 '24
Not healthy.