Set Up SQLite in C++ Plugin For UE4 and Unity3D – Part 1

Introduction

This post is a part of the tutorial series in which we are taking a look at creating a C++ plugin that will be cross-compatible with both Unreal Engine as well as Unity3D.

We have completed two parts of the series so far where we have set up the plugin to have delegates that can be registered from the game engines and are invoked in the plugin for passing any logs that the plugin generates.

In this post, we will set up SQLite3 for creating and saving any data that is passed from the game engines.

Storage Singleton

Let’s start by creating a very simple singleton class that can keep member variables that reference the database and maybe maintain queues later on for threading.

This is just a very simple singleton implementation. We are not going to be looking into a proper implementation that is thread-safe etc. That I felt would be out of scope for this tutorial. I strongly recommend that you create a better singleton class. There are quite a lot of resources for this.

Storage Manager Class

Let’s start by creating a class called StorageManager in our plugin project and adding the functionality for making this a singleton class that can be referenced from our plugin interface class. Here is the code for the class:

StorageManager.h

#pragma once
#include "stdafx.h"
class StorageManager
{
private:
	//Singleton Instance
	static StorageManager* s_refInstance;
	StorageManager();
public:
	~StorageManager();
	static StorageManager* GetInstance();
	void Initialize();
};

StorageManager.cpp

#include "StorageManager.h"
StorageManager* StorageManager::s_refInstance = NULL;
StorageManager* StorageManager::GetInstance()
{
	if (s_refInstance == NULL)
		s_refInstance = new StorageManager();
	return s_refInstance;
}
StorageManager::StorageManager()
{
}

StorageManager::~StorageManager()
{
}
void StorageManager::Initialize()
{
}

With this, we now have the singleton class that we need to handle the request to store any data that is passed to it. We just need to call the Initialize function in the interface class’ Initialize function so that the singleton instance is created as soon as the plugin is Initialized. Later we will be adding more code to this to set up the table creation etc.

StorageManager::GetInstance()->Initialize();

Creating the Database and Table

First, let’s start by importing the SQLite3 library and including the header that is needed. In the StorageManager header file, we need to add the following lines right at the top along with any other header includes that you might have.

#include <winsqlite/winsqlite3.h>
#pragma comment(lib, "winsqlite3")

Next, we need a handle to the database. For this, we need to create a sqlite3 variable in the header file that we will initialize.

sqlite3 *m_dbSession;

For this tutorial, we will save the data in the windows user’s local folder. We will need to get the path to this folder using Windows API. We will need to include the right headers for this.

#include <ShlObj.h>
#include <Shlwapi.h>
#pragma comment(lib, "shlwapi")

Now we can start by adding the private functions that will initialize SQLite as well as create the database with the table that we need.

void StorageManager::InitializeSQLTable()
{
	m_bInitialized = false;

	//Get the path to AppData Roaming folder
	char szPath[MAX_PATH] = "";
	if (SUCCEEDED(SHGetFolderPath(NULL, CSIDL_LOCAL_APPDATA, NULL, SHGFP_TYPE_CURRENT, szPath)))
		Logger::GetInstance()->InvokeLogDelegate(szPath);
	else
	{
		Logger::GetInstance()->InvokeExceptionDelegate("Disabling logging; Path to DB was not set");
		return;
	}
	PathCombine(szPath, szPath, m_strDBName.c_str());

	if (sqlite3_enable_shared_cache(1) != SQLITE_OK)
	{
		Logger::GetInstance()->InvokeExceptionDelegate("Disabling logging since DB could not be opened in shared cache mode");
		return;
	}

	if (sqlite3_open(szPath, &m_dbSession) != SQLITE_OK)
	{
		Logger::GetInstance()->InvokeExceptionDelegate("Disabling logging since DB could not be opened");
		const char* strError = sqlite3_errmsg(m_dbSession);
		Logger::GetInstance()->InvokeExceptionDelegate(strError);
		return;
	}

	if (!CheckAndCreateTable(m_strTableName))
		return;
	m_bInitialized = true;
}

In the InitializeSQLTable function above, we start by first getting the path to the local folder that the database needs to be created and then initialize SQLite3 in shared cache mode. We proceed to open the file. The function has a call to the CheckAndCreateTable function that is shown below. We can use this to create as many tables as we might need based on the needs of the game.

bool StorageManager::CheckAndCreateTable(string a_strTableName)
{
	char strCreateTable[256];
	sprintf_s(strCreateTable, "CREATE TABLE IF NOT EXISTS %s (bussID INTEGER PRIMARY KEY, save_data TEXT, Game_Name TEXT,Event_Time INTEGER);", a_strTableName.c_str());
	sqlite3_stmt *createStmt;
	int iCode = sqlite3_prepare_v2(m_dbSession, strCreateTable, strlen(strCreateTable), &createStmt, NULL);
	if (iCode != SQLITE_OK)
	{
		string strErr = "Saving Disabled since table could not be opened";
		strErr.append(to_string(iCode));
		Logger::GetInstance()->InvokeExceptionDelegate(strErr.c_str());
		return false;
	}

	if (sqlite3_step(createStmt) != SQLITE_DONE)
	{
		Logger::GetInstance()->InvokeExceptionDelegate("Saving Disabled since table could not be created");
		return false;
	}

	return true;
}

In the function above, we are creating a table in the database that will hold the save data. The table will be created in the following structure.

Coloumn NameColoumn Data TypeDescription
bussIDIntegerThis is the primary key of the table. Everytime a new entry is posted. This is incremented automatically.
save_dataTextThis will store the actual data that is sent from the game engine.
Event_TimeIntegerThis is the time that the record was created. We will store the local UTC time.

Now that we have the setup functions ready we can go ahead and call the InitializeSQLTable function in the Initialize function. Here is the modified function.

void StorageManager::Initialize(string a_strGameName)
{
	//Add the game name to the DB file name so that we can have a different DB for each game
	if (!a_strGameName.empty())
	{
		m_strDBName.append("_");
		m_strDBName.append(a_strGameName);
	}

	InitializeSQLTable();
}

The plugin interface’s Initialize function also needs to be modified so that the game name can be passed as an argument. Here is the modified version.

void Initialize(const char* a_strGameName)
{
	//Do any initialization here
	Logger::GetInstance()->Initialize();

	StorageManager::GetInstance()->Initialize(a_strGameName);
}

We can now compile and replace the DLL in the Unity3d or UE4 project that we have created in the previous posts of the tutorial.

Don’t forget to change the Initialize function’s signature in the projects and passing the game name. Running the code will give you a database file that is saved in the AppData/Local folder of your windows user.

In the next post, we will add the functionality to start receiving the data from the game engine and adding it to the database table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.