4. Object Relational Mapping & automated DB handling¶
4.1. Introduction to iFun Engine’s ORM¶
It’s not unusual to experience frequent changes in database schema during game development; you will need to add or omit new fields/tables as your game gets richer features. And naturally, matching game server-side code for database accesses should be also updated as database schema changes. This procedure is very tedious and error-prone (especially because SQL queries are typically treated as strings and the compiler cannot detect errors in strings).
So, iFun Engine solves this annoyance by its Object Relational Mapping (ORM). It automatically generates C++ classes matching DB schema and updates the classes as DB schema changes.
This is a typical workflow when using iFun Engine:
Server programmer designs game objects in JSON. And she does not directly access the database.
iFun Engine takes the JSON file and emits a class that reflects each game object type in the file. Generated code transparently has SQL queries to fetch/update objects from/to the database. In addition, it is designed to lease an object from other server, rather than issuing a SQL query, if the particular object has already been loaded by another server. This distributed caching is to prevent from overloading the database and makes sure the object is accessed without explicit locking. So, programmer does not have to explicitly issue SQL queries nor implement distributed caching.
On game server’s starting, iFun Engine checks with the database and makes sure all required tables and fields exist in the database. For omitted tables and fields, it automatically adds.
Tip
For safety, iFun Enigne’s ORM does not remove tables and fields even if they are not specified in the game object JSON file. This implies that it’s also possible to add tables and fields to share the database with external systems. For example, adding auxiliary fields to keep track of customer support history.
4.2. Enabling the DB ORM feature¶
First, enable database accesses and specify a database credential in MANIFEST.json.
Let’s assume we have a MySQL (or MariaDB) server on the same server and a user named funapi has an access to a schema named funapi.
"Object": {
...
"enable_database" : true,
"db_mysql_server_address" : "tcp://127.0.0.1:3306",
"db_mysql_id" : "funapi",
"db_mysql_pw" : "funapi",
"db_mysql_database" : "funapi",
...
},
Please note that we flipped the enable_database option to true.
Tip
If your server doesn’t have MySQL server installed, please follow these steps:
$ sudo apt-get install mysql-server
$ mysql -u root -p
Then, type in commands after the mysql prompt mysql>.
mysql> create user 'funapi'@'localhost' identified by 'funapi';
mysql> grant all privileges on *.* to 'funapi'@'localhost';
mysql> create database funapi;
4.3. Specifying game objects in JSON¶
Now, it’s time to take a look at a JSON file decalring game objects.
In hello_world-source/src/object_model/example.json, you will find objects named User and Character.
{
"User": {
"Id": "String KEY",
"MyCharacter": "Character"
},
"Character": {
"Name": "String KEY",
"Exp": "Integer",
"Level": "Integer",
"Hp": "Integer",
"Mp": "Integer"
}
}
According to the JSON specification, there are classes named User and Character.
And User has a key string Id and a field MyCharacter of Character type.
Character type has name, experience point, level, hit point, mana point as members.
Now, iFun Engine will connect to the database with the credential specified in the previous MANIFEST.json, check if there are tables named User and Character with required fields, and automatically adds tables/fields if there’s missing tables/fields.
4.4. Generated ORM code¶
Build the game server with the JSON file
$ make
You will notice that files named user.h and character.h are generated under hello_world-source/src/object_model/. Those files define classes named User and Character, respectively.
$ ls ../../hello_world-source/src/object_model
character.h common.h example.json item.h test_object.cc user.h
Each class have methods like Create(...) and Fetch(...) to create game objects and access them from the database. And you will also find getters and setters like GetHp() and SetHp to manipulate game objects. Below is the part of a content generated in hello_world-source/src/object_model/character.h.
class Character : public ObjectProxy {
...
// Create
static Ptr<Character> Create(const string &name);
// Fetch by object id
static Ptr<Character> Fetch(
const Object::Id &id,
LockType lock_type = kWriteLock);
static void Fetch(
const std::vector<Object::Id> &ids,
std::vector<std::pair<Object::Id, Ptr<Character> > > *result,
LockType lock_type = kWriteLock);
// Fetch by Name
static Ptr<Character> FetchByName(
const string &value,
LockType lock_type = kWriteLock);
static void FetchByName(
const std::vector<string> &values,
std::vector<std::pair<string, Ptr<Character> > > *result,
LockType lock_type = kWriteLock);
...
// Getter/Setter for 'Name' attribute
string GetName() const;
void SetName(const string &value);
// Getter/Setter for 'Exp' attribute
int64_t GetExp() const;
void SetExp(const int64_t &value);
// Getter/Setter for 'Level' attribute
int64_t GetLevel() const;
void SetLevel(const int64_t &value);
// Getter/Setter for 'Hp' attribute
int64_t GetHp() const;
void SetHp(const int64_t &value);
// Getter/Setter for 'Mp' attribute
int64_t GetMp() const;
void SetMp(const int64_t &value);
...
};
4.5. Checking auto-generated DB tables¶
Run the server again, after turnning the enable_database field in MANIFEST.json to true.
As explained, the server should generate tables on the database given in MANIFEST.json.
$ ./hello_world-local (Stop the server with Ctrl+c after it succeeds to start.)
$ mysql -u funapi -p funapi
mysql> show tables;
+-----------------------+
| Tables_in_funapi |
+-----------------------+
| tb_Key_Character_Name |
| tb_Key_User_Id |
| tb_ObjectLock |
| tb_Object_Character |
| tb_Object_User |
+-----------------------+
5 rows in set (0.00 sec)
mysql> desc tb_Object_Character;
+----------------+---------------+------+-----+------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+------------------+-------+
| col__ObjectId_ | binary(16) | NO | PRI | | |
| col_Name | varchar(4096) | YES | UNI | NULL | |
| col_Exp | bigint(8) | YES | | NULL | |
| col_Level | bigint(8) | YES | | NULL | |
| col_Hp | bigint(8) | YES | | NULL | |
| col_Mp | bigint(8) | YES | | NULL | |
| col__tag | varchar(4096) | YES | | NULL | |
+----------------+---------------+------+-----+------------------+-------+
7 rows in set (0.00 sec)
You can find the tables have been populated automatically from the example above.
Let’s say our game designer wants us to add Sp into Character.
This requires manual database work and source code update, in general.
With iFun Engine, however, only thing you should do is to update the JSON specification.
Update hello_world-source/src/object_model/example.json like this:
{
"User": {
"Id": "String KEY",
"MyCharacter": "Character"
},
"Character": {
"Name": "String KEY",
"Exp": "Integer",
"Level": "Integer",
"Hp": "Integer",
"Mp": "Integer",
"Sp": "Integer"
}
}
Build again.
$ make
Please find that Character now has methods related to Sp.
class Character : public ObjectProxy {
...
// Getter/Setter for 'Sp' attribute
int64_t GetSp() const;
void SetSp(const int64_t &value);
...
};
Run the server again and verify that the Character table now has Sp.
$ ./hello_world-local (Hit Ctrl+c and stop the server shortly after start.)
$ mysql -u funapi -p funapi
mysql> desc tb_Object_Character;
+----------------+---------------+------+-----+------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+------------------+-------+
| col__ObjectId_ | binary(16) | NO | PRI | | |
| col_Name | varchar(4096) | YES | UNI | NULL | |
| col_Exp | bigint(8) | YES | | NULL | |
| col_Level | bigint(8) | YES | | NULL | |
| col_Hp | bigint(8) | YES | | NULL | |
| col_Mp | bigint(8) | YES | | NULL | |
| col_Sp | bigint(8) | YES | | NULL | |
| col__tag | varchar(4096) | YES | | NULL | |
+----------------+---------------+------+-----+------------------+-------+
7 rows in set (0.00 sec)
See? We never need to manually change the database schema and update the source code.
4.6. Using the generated classes in a server code¶
Finally, let’s create an object from the game server.
We will reuse the OnHello event handler that we have written before.
Please see the code below.
void OnHello(const Ptr<Session> &session, const Json &message) {
Ptr<Character> ch = Character::Create("ifun");
if (!ch) {
LOG(ERROR) << "Already exists";
} else {
LOG(INFO) << "Created";
}
Json empty_response;
session->SendMessage("world", empty_response);
}
Now, the server will try to create an instance named ifun once it receives hello from a client.
Please note that the JSON specification of Character has Name as a key string.
That’s why the Character::Create takes a string argument.
Build the server and run it. And verify the result after running the command below.
$ wget -qO- --post-data="{}" http://localhost:8018/v1/messages/hello
Let’s see what happened in the database.
$ mysql -u funapi -p funapi
mysql> select * from tb_Object_Character;
+------------------+----------+---------+-----------+--------+--------+--------+----------+
| col__ObjectId_ | col_Name | col_Exp | col_Level | col_Hp | col_Mp | col_Sp | col__tag |
+------------------+----------+---------+-----------+--------+--------+--------+----------+
| ???8?F?????[?& | ifun | 0 | 0 | 0 | 0 | 0 | |
+------------------+----------+---------+-----------+--------+--------+--------+----------+
1 row in set (0.01 sec)
The database indeed has a record named ifun.
We have extended the game server by adding a message handler and could access a database. However, it’s very rare to have only one server in real game service environments. So, we will walk through iFun Engine’s multi-server supports in the following chapters.