Building a Game Server in C++ #7: Connecting with MySQL
When I began to put my game server idea into code, the storage backend was way down the list of my priorities. Today I decided to take a deep breath and get it over with. In this article we will implement a database and refactor the Storage class to connect to it.
Designing the Database
As we have already established, there should be a table with users and passwords. But I also want to add privileges which users can have either explicitly, or implicitly via group membership. Those privileges can be either general (ie shut down the server, add/delete users etc) or with respect to a specific object (ie create instances of a specific game). There is a third category of composite privileges, but since they depend on several factors, they should be implemented at the application layer.
With that in mind, let’s put together some ground rules:
- There are two kinds of entities: users and groups
- Entities can have general privileges
- Some privileges can refer to objects
- Entities can have object privileges on objects
- A special kind of object is a game
Right off the bat, we have four IS-A relations:
- A user is an entity
- A group is an entity
- An object privilege is a privilege
- A game is an object
Other than that, the relation between entities and privileges is m:n, as is the one between users and groups. The one between entities, objects and object privileges is m:n:k. With all that in mind, let’s come up with an ER diagram:
MySQL Implementation
First of all, we have to create a database and a user with all privileges to it:
create database gamesrv; create user user@localhost identified by 'opensessame'; grant all privileges on gamesrv.* to user@localhost;
Let’s first define objects and privileges, since they are simple. We only have one game (rps) and we will introduce some privileges, one of which (run) will be an object one:
create table object (id varchar(100) not null primary key);
create table game (id varchar(100) not null primary key references object(id));
insert into object (id) values ('rps');
insert into game (id) values ('rps');
create table privilege (id varchar(100) not null primary key);
create table object_privilege (id varchar(100) not null primary key references privilege(id));
insert into privilege (id) values ('shutdown'),('manage_users'),('run');
insert into object_privilege (id) values ('run');
Now let’s do the same for the user/group part:
create table entity (name varchar(100) not null primary key);
create table user (
username varchar(100) not null primary key references entity(name),
password varchar(100) not null
);
insert into entity (name) values
('root'),('mario'),('luigi'),('michelangelo'),('sonic'),('robotnik');
insert into user (username,password) values
('root','123qwe'),('mario','Its-aMe'),('luigi','OkeyDokey'),
('michelangelo','cowabunga'),('sonic','u2slo'),('robotnik','eggman');
create table `group` (name varchar(100) not null primary key references entity(name));
insert into entity (name) values ('admins'),('heroes'),('villains');
insert into `group` (name) values ('admins'),('heroes'),('villains');
create table belongs (
user varchar(100) not null references user,
`group` varchar(100) not null references `group`(name),
primary key(user,`group`)
);
insert into belongs (`group`,user) values
('admins','mario'),('admins','michelangelo'),
('villains','robotnik'),
('heroes','mario'),('heroes','luigi'),('heroes','sonic');
As we can see in the last part, mario and michelangelo are admins, robotnik is the only villain and mario, luigi and sonic belong to the heroes group. Moving on, let’s give away some privileges:
create table can (
entity varchar(100) not null references entity,
privilege varchar(100) not null references privilege,
primary key (entity,privilege)
);
insert into can (entity,privilege) select 'root',id from privilege;
insert into can (entity,privilege) values ('admins','manage_users'),('admins','run');
create table can_object (
entity varchar(100) not null references entity,
object varchar(100) not null references object,
privilege varchar(100) not null references object_privilege,
primary key (entity,object,privilege)
);
insert into can_object (entity,object,privilege) values ('villains','rps','run');
After that, root can do anything, admins (mario and michelangelo) can manage users and run any game, and villains (ie robotnic) can explicitly run rps — for heroes to challenge them, apparently…
Finally, I want to have a convenient way to retrieve user privileges regardless of where they come from, meaning either through direct assignment or via group membership. To do that, we will create two views:
create view ext_can as
select distinct u.username,c.privilege from user u
left join belongs b on (u.username=b.user)
join can c on (c.entity=u.username or c.entity=b.`group`);
create view ext_can_object as
select distinct u.username,c.object,c.privilege from user u
left join belongs b on (u.username=b.user)
join can_object c on (c.entity=u.username or c.entity=b.`group`);
Let’s test them:
mysql> select * from ext_can; +--------------+--------------+ | username | privilege | +--------------+--------------+ | mario | manage_users | | mario | run | | michelangelo | manage_users | | michelangelo | run | | root | manage_users | | root | run | | root | shutdown | +--------------+--------------+ 7 rows in set (0,04 sec) mysql> select * from ext_can_object; +----------+--------+-----------+ | username | object | privilege | +----------+--------+-----------+ | robotnik | rps | run | +----------+--------+-----------+ 1 row in set (0,01 sec)
Refactoring the Storage class
From the C++ side of things, I will be using Connector/C++. Let’s start our refactoring from the constructor, and introduce a new destructor:
class Storage {
private:
Connection *db;
public:
Storage(string,string,string,string);
~Storage();
bool matchPassword(string,string);
};
Storage::Storage(string h,string d,string u,string p) {
db=get_driver_instance()->connect("tcp://"+h,u,p);
db->setSchema(d);
}
Storage::~Storage() { delete db; }
Pretty simple: we acquire a connection to the server and select our database and, when the object is destroyed, we free the memory we allocated with the relevant field. Next, let’s see how we should change matchPassword() to work with the database:
bool Storage::matchPassword(string username,string password) {
string q="select password from user where username=?";
PreparedStatement *ps=db->prepareStatement(q);
ps->setString(1,username);
ResultSet *r=ps->executeQuery();
bool match=r->next()&&(r->getString(1)==password);
delete ps;
delete r;
return match;
}
The idea here is that the check fails if the user does not exist (the query returned zero rows) or the user exists but their password doesn’t match the one given.