Serialization with MySQL
Prerequisites
- UObject Base Class
- Variants - Generic Containers
- Serializable Objects
Article
While SQLite is great for a local database, especially one on the client, soon we're going to be looking at a dedicated game server. That will mean multiple instances or servers talking to the same database - and as you scale, you'll probably end up running a dedicated database server.
Enter MySQL - or Postgres, or SQL Server. In this article, we'll focus on MySQL, but you can easily port the code over to whatever backend you want to run.
We'll leverage the DataLoader base class we created in our SQLite tutorial. Let's review quickly:
class UDataLoader : public USystem {
public:
UDataLoader() = default;
virtual ~UDataLoader() = default;
/**
* Open / close this data source
*/
virtual bool Open(std::string location) = 0;
virtual void Close() { }
/**
* Get all objects of a specific type
*/
virtual std::vector<UDataRecord*> GetRecords(std::string type, std::map<std::string, UVariant> search = std::map<std::string, UVariant>()) {
UASSERT(false, "Method not implemented.");
return(std::vector<UDataRecord*>());
}
/**
* Get one object of a specific type and ID
*/
UDataRecord* GetRecord(std::string type, uint32_t id);
/**
* Save records
*/
virtual void SaveRecords(std::string type, std::vector<UDataRecord*> records) = 0;
protected:
// Location that is opened
std::string m_location;
};This class provides us with 4 functions to overload (Open, Close, GetRecords, and SaveRecords) and gives us a uniform way to access data regardless of the backend we choose for it.
Here's our MySQL-specific version:
class UENGINE_API UMySQLDataLoader : public UDataLoader {
public:
UMySQLDataLoader() : m_handle(0) { }
~UMySQLDataLoader() = default;
/**
* Open / close this data source
* Open param is a connection string format "user:password@host:port/schema"
*/
bool Open(std::string location);
void Close();
/**
* Get all objects of a specific type
*/
std::vector<UDataRecord*> GetRecords(
std::string type,
std::map<std::string, UVariant> search = std::map<std::string, UVariant>()
);
/**
* Save records
*/
void SaveRecords(std::string type, std::vector<UDataRecord*> records);
protected:
// Map UVariant type to MySQL column name
std::string GetColumnTypeName(int type);
protected:
MYSQL* m_handle;
};Most of this is the same as the base class, but we've added a helper function called GetColumnTypeName. This function will convert our Variant data types into their MySQL equivalents. For example, we'll store all of our integer types as "INT" and our objects, vectors, quaternions (and actual strings) as VARCHARs, or strings.
We'll be using the MySQL C API to connect to MySQL. Unlike our SQLite or JSON equivalents, MySQL needs a little more information to connect - since most connections are to a remote machine, it takes a host, port, username, and password as well as a database. Other SQL databases support a connection string that incorporates all of this - unfortunately the MySQL C API doesn't provide support for a similar format, so we'll have to create our own.
Let's take a look at the Open function:
bool UMySQLDataLoader::Open(std::string location) {
// Open param is a connection string format "user:password@host:port/schema", parse that
std::string username, password, host, port, database;
try {
username = location.substr(0, location.find_first_of(":"));
password = location.substr(username.length() + 1, location.find_first_of("@") - (username.length() + 1));
host = location.substr(location.find_first_of("@") + 1, location.find_last_of(":") - (location.find_first_of("@") + 1));
port = location.substr(location.find_last_of(":") + 1, location.find_last_of("/") - (location.find_last_of(":") + 1));
database = location.substr(location.find_last_of("/") + 1);
}
catch (std::exception ex) {
UASSERT(false, "Unable to parse connection string.");
return(false);
}
// Initialize and connect
m_handle = mysql_init(0);
if (mysql_real_connect(m_handle, host.c_str(), username.c_str(), password.c_str(), database.c_str(), atoi(port.c_str()), 0, 0) == 0) {
UASSERT(false, "Unable to connect to MySQL.");
return(false);
}
return(true);
}The first part of Open is dedicated to parsing the connection string into parts. We don't use a lot of try catch blocks, but this is a lot of string parsing that will throw exceptions, and we just want to return an error if it fails. Otherwise, it calls the mysql_init function to initialize our MySQL handle and mysql_real_connect to establish a connection.
The Close function is very simple:
void UMySQLDataLoader::Close() {
mysql_close(m_handle);
}Let's move on to the meat... the GetRecords function will load rows from a table and return UDataRecords:
std::vector<UDataRecord*> UMySQLDataLoader::GetRecords(std::string type, std::map<std::string, UVariant> search) {
std::vector<UDataRecord*> records;
// Start by constructing a query
std::string query = "SELECT * FROM " + type;
// Add where clauses
std::string primaryKey = type + "_id";
if (search.size()) {
query += " WHERE 1=1 ";
std::map<std::string, UVariant>::iterator it = search.begin();
for (; it != search.end(); it++) {
query += " AND " + it->first + " = '" + it->second.ToString() + "'";
}
}
// Execute
if (mysql_query(m_handle, query.c_str()) != 0) {
UASSERT(false, "Error executing MySQL query: " + query);
return(records);
}
// Fetch rows
MYSQL_RES* result = mysql_store_result(m_handle);
while (MYSQL_ROW row = mysql_fetch_row(result)) {
// Create a new data record
UDataRecord* record = new UDataRecord();
records.push_back(record);
// Retrieve fields
unsigned int numFields = mysql_num_fields(result);
MYSQL_FIELD* fields = mysql_fetch_fields(result);
for (int i = 0; i < numFields; i++) {
std::string value = row[i];
// Determine field type
int type = 0;
switch (fields[i].type) {
case MYSQL_TYPE_TINY:
case MYSQL_TYPE_SHORT:
case MYSQL_TYPE_LONG:
case MYSQL_TYPE_INT24:
type = UVariant::VAR_INT32;
break;
case MYSQL_TYPE_LONGLONG:
type = UVariant::VAR_INT64;
break;
case MYSQL_TYPE_DECIMAL:
case MYSQL_TYPE_NEWDECIMAL:
case MYSQL_TYPE_FLOAT:
case MYSQL_TYPE_DOUBLE:
type = UVariant::VAR_FLOAT;
break;
case MYSQL_TYPE_BIT:
type = UVariant::VAR_BOOL;
break;
case MYSQL_TYPE_STRING:
case MYSQL_TYPE_VAR_STRING:
type = UVariant::VAR_STRING;
break;
default:
break;
}
UASSERT(type != 0, "Unrecognized data type.");
// Check for objects
if (type == UVariant::VAR_STRING) {
// Use our predefined object format to check for objects
if (value.substr(0, 1) == "{" && value.substr(value.length() - 1) == "}") {
// Get the type and overwrite
std::string typestr = value.substr(1, value.find_first_of(":") - 1).c_str();
type = atoi(typestr.c_str());
value = value.substr(typestr.length() + 2, value.length() - typestr.length() - 3);
}
// Handle object case (store DataRecord)
if (type == UVariant::Type::VAR_OBJECT) {
// Split the type and unique ID
std::string objectType = value.substr(0, value.find(":"));
uint32_t uid = atoi(value.substr(value.find(":") + 1).c_str());
// Case where it points to an empty object
if (uid == 0) {
record->Set(fields[i].name, (UDataRecord*)0);
}
else {
// If this is a nested object, go fetch the actual object as a DataRecord
UDataRecord* subrecord = GetRecord(objectType, uid);
record->Set(fields[i].name, subrecord);
}
continue;
}
}
// Read in data
UVariant v;
v.FromString(value, type);
record->Set(fields[i].name, v);
}
}
mysql_free_result(result);
return(records);
}We start by constructing a SELECT query and adding any necessary WHERE clauses. Then we execute it by called mysql_query. This runs the query on the server, but it doesn't actually return the result to our application yet. That happens when we call mysql_store_result. We can iterate through the rows using mysql_fetch_row. This returns a MYSQL_ROW, which is actually a collection of strings. To get the field information, we can use mysql_fetch_fields to get MYSQL_FIELD structures, giving us the column name, type, size, etc.
Armed with all of this, we can now use the MYSQL_ROW and the MYSQL_FIELDs to get our data. First, we convert the MySQL types to UVariant types. For vectors and quaternions, we'll store them as strings. Similar to our SQLite format, we'll store them as (for example), "(0, 1, 0)" for a 3-component vector, wrapped in some type information. We'll also store objects as pseudo-foreign-key references to other tables in a string. Finally, we store strings as strings.
Therefore, if it's a string, we'll see if it matches our predefined format, wrapping in curly braces. If it is, we parse it as "type:value", where type is a UVariant type and value is a string representation of our object. Finally, it could be an object type, which we'll need to reduce one step further to get a "tableName:foreignKey" pair that we can use to load a record from another table.
We store all of this information into a Variant using the FromString function and then store all of those Variants into a DataRecord. Finally, because we stored a local copy of our result set, we get rid of it by calling mysql_free_result.
With a way to get records, we now need a way to save them back to the database. Before that, like we converted MySQL types into Variant types, we need a way to convert Variant types into MySQL types. We have a helper function GetColumnTypeName to do just that:
std::string UMySQLDataLoader::GetColumnTypeName(int type) {
// Map column type
std::string typestr = "";
switch (type) {
case UVariant::VAR_INT32:
case UVariant::VAR_UINT32:
typestr = "INT";
break;
case UVariant::VAR_INT64:
case UVariant::VAR_UINT64:
typestr = "BIGINT";
break;
case UVariant::VAR_BOOL:
typestr = "BIT(1)";
break;
case UVariant::VAR_FLOAT:
typestr = "FLOAT";
break;
case UVariant::VAR_VECTOR2:
case UVariant::VAR_VECTOR3:
case UVariant::VAR_VECTOR4:
case UVariant::VAR_QUATERNION:
case UVariant::VAR_STRING:
case UVariant::VAR_OBJECT:
typestr = "VARCHAR(200)";
break;
default: break;
};
return(typestr);
}This function will return a string that represents a column type in MySQL from a Variant type in our code. We'll use this in our CREATE TABLE and ALTER TABLE queries to add and remove tables and columns. As you can see, we map the 32-bit integer types to "INT", the 64-bit integer types to "BIGINT", boolean values to a single "BIT", and floats to "FLOAT". As we saw in our GetRecords method, we'll store vectors, quaternions, object references, and strings as strings.
Here is the SaveRecords function:
void UMySQLDataLoader::SaveRecords(std::string type, std::vector<UDataRecord*> records) {
// Make sure we have records in the list
if (records.size() == 0) return;
// Make sure the table exists and spec matches
std::string query = "CREATE TABLE IF NOT EXISTS ";
query += type + " (";
// Add a primary key
query += type + "_id INT";
// Use the first record as a model
UDataRecord* record = records[0];
std::vector<std::string> keys = record->GetKeys();
for (auto fi = keys.begin(); fi != keys.end(); fi++) {
// Map column type
std::string typestr = GetColumnTypeName(record->Get(*fi).Type());
// Add all columns here as TEXT type
query += "," + (*fi) + " " + typestr;
}
// Specify primary key
query += ", PRIMARY KEY(" + type + "_id ASC))";
if (mysql_query(m_handle, query.c_str()) != 0) {
UASSERT(false, "Unable to create MySQL table.");
return;
}
// Look for columns that may not exist (but the table does)
query = "SHOW COLUMNS FROM " + type;
mysql_query(m_handle, query.c_str());
// Put the column names into a vector
std::vector<std::string> existingCols;
MYSQL_RES* result = mysql_store_result(m_handle);
while (MYSQL_ROW row = mysql_fetch_row(result)) {
// Fetch a single row and get the column names
int fieldCount = mysql_num_fields(result);
MYSQL_FIELD* fields = mysql_fetch_fields(result);
for (int i = 0; i < fieldCount; i++) {
existingCols.push_back(fields[i].name);
}
break;
}
mysql_free_result(result);
// Add any we can't find
auto fi = keys.begin();
for (; fi != keys.end(); fi++) {
auto it = std::find(existingCols.begin(), existingCols.end(), *fi);
if (it == existingCols.end()) {
std::string typestr = GetColumnTypeName(record->Get(*fi).Type());
query = "ALTER TABLE " + type + " ADD COLUMN " + (*fi) + " " + typestr;
if (mysql_query(m_handle, query.c_str()) != 0) {
UASSERT(false, "Unable to alter MySQL DB.");
return;
}
}
}
// Iterate over each record
auto it = records.begin();
for (; it != records.end(); it++) {
// First, check for new records (ID == 0)
if ((*it)->ID() == 0) {
std::string query = "INSERT INTO " + type + " (";
std::string values = ") VALUES (";
// Use the "data model" from the first record
auto fi = keys.begin();
for (; fi != keys.end(); fi++) {
UVariant v = (*it)->Get(*fi);
std::string value = v.ToString();
// Special case for vectors and quaternions
if (v.IsVector2() || v.IsVector3() || v.IsVector4() || v.IsQuaternion()) {
value = "{" + std::to_string(v.Type()) + ":" + value + "}";
}
if (v.IsObject()) {
// Serialize this object
UDataRecord record;
USerializable* serializable = v.AsObject<USerializable*>();
UObject* obj = v.AsObject();
// If this is both a serializable object and a UObject...
if (obj && serializable) {
// Serialize object
serializable->Serialize(&record);
// Get class name
std::string subtype = obj->UType().className;
// Save record
SaveRecords(subtype, { &record });
value = "{" + std::to_string(v.Type()) + ":" + subtype + ":" + std::to_string(record.ID()) + "}";
}
}
query += "`" + *fi + "`,";
values += "'" + value + "',";
}
// Replace the last character with a closing bracket
values = values.substr(0, values.length() - 1);
// Complete query
query = query.substr(0, query.length() - 1) + values;
query += ")";
if (mysql_query(m_handle, query.c_str()) != 0) {
UASSERT(false, "Unable to insert object in MySQL DB.");
}
unsigned int newID = (unsigned int)mysql_insert_id(m_handle);
if (newID == 0) {
UASSERT(false, "Unable to insert object in MySQL DB.");
}
(*it)->ID(newID);
continue;
}
// We'll need the ID for the rest
std::string primaryKey = std::to_string((*it)->ID());
// The process deletes
if ((*it)->Deleted()) {
// Construct query
std::string query = "DELETE FROM " + type + " WHERE " + type + "_id = " + primaryKey;
if (mysql_query(m_handle, query.c_str()) != 0) {
UASSERT(false, "Unable to delete object from MySQL.");
}
continue;
}
// Finally process updates
std::string query = "UPDATE " + type + " SET ";
auto f = keys.begin();
for (; f != keys.end(); f++) {
UVariant v = (*it)->Get(*f);
std::string value = v.ToString();
// Special case for vectors and quaternions
if (v.IsVector2() || v.IsVector3() || v.IsVector4() || v.IsQuaternion()) {
value = "{" + std::to_string(v.Type()) + ":" + value + "}";
}
// Handle object case
if (v.IsObject()) {
// Serialize this object
UDataRecord record;
USerializable* serializable = v.AsObject<USerializable*>();
UObject* obj = v.AsObject();
// If this is both a serializable object and a UObject...
if (obj && serializable) {
// Serialize object
serializable->Serialize(&record);
// Get class name
std::string subtype = obj->UType().className;
// Save record
SaveRecords(subtype, { &record });
value = "{" + std::to_string(v.Type()) + ":" + subtype + ":" + std::to_string(record.ID()) + "}";
}
}
if (f != keys.begin()) query += ",";
query += "`" + *f + "` = '" + value + "'";
}
query += " WHERE " + type + "_id = " + primaryKey;
if (mysql_query(m_handle, query.c_str()) != 0) {
UASSERT(false, "Unable to update object in MySQL DB.");
}
}
}This function is broken down into three parts:
- CREATE TABLE will create a new table schema for a data type that doesn't exist yet
- ALTER TABLE will modify an existing table to add new columns
- Finally, we'll store our actual objects. This is also broken down into three parts: INSERT, DELETE, and UPDATE statements for new, deleted, and modified objects respectively.
Our CREATE and ALTER TABLE queries leverage the GetColumnTypeName helper function to help determine what the MySQL column type should be. We create an auto-incrementing primary key column called tableName_id, where tableName is the same as class or object name.
We then iterate through our DataRecords, crafting INSERT statements where the ID is set to zero (we set the ID by calling the mysql_insert_id function after the INSERT), DELETE statements where the Deleted flag is set to true, and UPDATE for everything else. You'll see the same formatting of data for vectors and quaternions as well as objects.
Conclusion
We can now store collection data locally and in a remote SQL database. The remote database serves one purpose: a single server that multiple computers can connect to. You may use this to create a multiplayer dedicated server (we'll cover that in a future article) or enable collaboration between programmers in your editor.
Both databases work great for storing collections of information. Next we'll look at serialization with JSON for cases where we want to store data into text files - engines such as Unity use small JSON files extensively since they work well with source control systems and are human editable and understandable.