Hello World with SQLite

Hello World with SQLite

In this Hello World tutorial we look at using SQLite in libxayagame.

There are several advantages:

• You don't need to create undo data or a backwards callback
• All your reorg work is done for you automatically through libxayagame!
• You can have MASSIVE game worlds

Creating undo data and backwards callbacks can be a significant amount of work, and particularly for complex games. See here and here in the Mover console tutorial for what it takes to create undo data and a backwards callback. With all that work done automatically through libxayagame, it's often a better choice to use a xaya::SQLiteGame rather than a xaya::CachingGame.

Using SQLiteGame also allows for MASSIVE game states. As we'll see below, the game state is updated in the database piece by piece. Further, with a SQLite game, we never need to pass around a full game state. Instead, we can query the database for what small parts we need then update the game UI as required. This allows for game states to grow to gigabytes of data. Thus, with SQLiteGame, your game world has virtually unlimited potential.

Much of what is in this tutorial is already covered in the Hello World in C++ tutorial, so we won't go over that again. If you've not already finished the Hello World in C++ tutorial, you may wish to do so before proceeding.

What we will cover here is how to create a SQLite game with libxayagame.

Download the Code

The Hello World with SQLite code is available in the XAYA Tutorial Code respository.

Includes

The first differences for hellosqlite.cpp are some new includes:

#include <xayagame/sqlitegame.hpp>
#include <sqlite3.h>

These include the sqlitegame classes in libxayagame and the actual SQLite 3 database software.

DEFINE_xxx

Recall from the previous tutorial that we must set a storage type. Here we don't use the storage_type definition because we know that we're going to use SQLite.

When you run this version of Hello World, you'll need to adjust the parameters that you send in through the command line, i.e. remove the storage_type parameter from the previous tutorial.

Helper Methods

To work with SQLite, we need a couple methods to bind strings in a SQLite prepared statement and to get a column string value from a SQLite statement. (Refer to the links for SQLite documentation. See SQLITE_TRANSIENT here and SQLITE_OK here.)

BindString

BindString is called multiple times. It updates values for our prepared SQL statement with the value, val, for a position, pos, i.e. a column, inside the statement.

Note that the position is a position inside of the prepared SQL statement and not a database index. Refer to SetupSchema below to see that name is in position 1 and msg is in position 2, and UpdateState for how BindString is used.

void
BindString (sqlite3_stmt* stmt, const int pos, const std::string& val)
{
  CHECK_EQ (sqlite3_bind_text (stmt, pos, &val[0], val.size (),
                               SQLITE_TRANSIENT),
            SQLITE_OK);
}

GetStringColumn

In GetStringColumn we call sqlite3_column_text to get a column's value from a record set and then cast and return the column's value as a string.

std::string
GetStringColumn (sqlite3_stmt* stmt, const int pos)
{
  const unsigned char* str = sqlite3_column_text (stmt, pos);
  return reinterpret_cast<const char*> (str);
}

We'll use these methods later when we get to the UpdateState and GetStateAsJson methods.

HelloWorld Class

In the previous tutorial, we used a CachingGame.

class HelloWorld : public xaya::CachingGame

However, SQLiteGame offers many advantages, and that's what this tutorial is about.

class HelloWorld : public xaya::SQLiteGame

From this point on, our HelloWorld class will be radically different than previously.

Recall our methods in the previous tutorial:

xaya::GameStateData GetInitialStateInternal (unsigned& height, 
      std::string& hashHex) override
xaya::GameStateData UpdateState (const xaya::GameStateData& oldState,
      const Json::Value& blockData) override 
Json::Value GameStateToJson (const xaya::GameStateData& state) override

This time around, our HelloWorld class will look like this:

void SetupSchema (sqlite3* db) override
void GetInitialStateBlock (unsigned& height, 
     std::string& hashHex) const override
void InitialiseState (sqlite3* db) override
void UpdateState (sqlite3* db, 
     const Json::Value& blockData) override
Json::Value GetStateAsJson (sqlite3* db) override

Note that again, all of these methods are overrides of libxayagame methods.

SetupSchema

The SetupSchema method sets up the basic database schema. This method is called on each start of the game daemon. It should make sure that the database schema is set up correctly. It can also be used to upgrade the existing schema in the database, for instance when the game daemon is updated to a new version.

void
SetupSchema (sqlite3* db) override
{
  auto* stmt = PrepareStatement (R"(
    CREATE TABLE IF NOT EXISTS `messages` (
      `name` TEXT PRIMARY KEY,
      `msg` TEXT NOT NULL
      )
    )");
  CHECK_EQ (sqlite3_step (stmt), SQLITE_DONE);
}

GetInitialStateBlock

The GetInitialStateBlock method is similar to GetInitialStateInternal in the previous tutorial, but there are some important differences.

The GetInitialStateBlock method returns the block height and corresponding block hash at which the game "starts". At that block, the initial state (as defined by InitialiseState) is stored in the database and processing of moves starts. Everything before that block is ignored completely.

InitialiseState

The InitialiseState method initialises the state in the database to what the initial game state at the "starting block" should be. This method is called exactly once, before the processing of moves starts.

For our Hello World game, the initial state is simply an empty database (nobody has said anything so far). However, we could set some hardcoded initial message here if we wanted, for instance.

void
InitialiseState (sqlite3* db) override { }

UpdateState

The UpdateState method updates the game state for a new block with (potentially) moves.

Most of the method is the same as in the previous tutorial's UpdateState method. However, there are some significant differences.

First, the signature is different. Compare the following (hello.cpp first and hellosqlite.cpp second).

xaya::GameStateData
UpdateState (const xaya::GameStateData& oldState,
             const Json::Value& blockData) override

void
UpdateState (sqlite3* db, const Json::Value& blockData) override

In the previous tutorial, we had to construct the new game state and update it. However, with a SQLiteGame we merely update the database with any new moves. That is, our game state is the database.

Getting moves from the blockData variable is substantially the same as it was in the previous tutorial. Once we have our new move, we then update the database as follows.

auto* stmt = PrepareStatement (R"(
  INSERT OR REPLACE INTO `messages`
    (`name`, `msg`) VALUES (?1, ?2)
  )");
BindString (stmt, 1, name);
BindString (stmt, 2, message.asString ());
CHECK_EQ (sqlite3_step (stmt), SQLITE_DONE);

With the database/game state now updated, we can query it in the front end to update the GUI.

GetStateAsJson

In the previous tutorial, we extracted JSON from a xaya::GameStateData object.

However, as our game state is now stored in the SQLite database, we must query the database.

Json::Value
GetStateAsJson (sqlite3* db) override
{
  Json::Value state(Json::objectValue);

  auto* stmt = PrepareStatement (R"(
    SELECT `name`, `msg` FROM `messages`
  )");
  while (true)
    {
      const int rc = sqlite3_step (stmt);
      if (rc == SQLITE_DONE)
        break;
      CHECK_EQ (rc, SQLITE_ROW);

      const std::string name = GetStringColumn (stmt, 0);
      const std::string msg = GetStringColumn (stmt, 1);
      state[name] = msg;
    }

  return state;
}

In the while loop, we get records using the sqlite3_step method and then get the name and message in strings using our GetStringColumn from above and store those in the game state, state[name] = msg.

Once the server returns SQLITE_DONE, we exit the loop and return our complete game state.

HelloWorld Class Summary

1. SetupSchema: We set up a database schema
2. GetInitialStateBlock: We set the proper block to start the game at
3. InitialiseState: We could set an initial state if we wished
4. UpdateState: We updated the game state directly in the SQLite database
5. GetStateAsJson: We queried the database to get a game state

main

There are some minor differences in flags as we didn't use the storage_type flag. You can check those differences on your own.

The major difference then between our previous main method and our SQLite main method, respectively, can be seen below.

HelloWorld logic;
const int res = xaya::DefaultMain (config, "helloworld", logic);

HelloWorld logic;
const int res = xaya::SQLiteMain (config, "helloworld", logic);

And we got our reorg and undo work done for free as libxayagame automatically manages it with SQLiteGame.

Wasn't that just ridiculously easy?