Using a SQLite database with Source
January 2024
You can help by adding links to this article from other relevant articles.
January 2024
This article explains how to use SQLite binaries or source with the Source SDK, describes a simple a simple wrapper class to use as a simplified interface into the SQL database, and offers some notes on the usage of this class. It does not explain the SQL language, the specifics of SQLite, or attempt to introduce a novice user to SQL syntax; these concepts are outwith the scope of this article.
Contents
SQLite
SQLite is "a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain." In short: its SQL, and it's very easy to set up and use. It's also very small, which is nice.
Why SQLite?
- It's very easy to set up
- It's small
- It represents a very (almost) complete implementation of SQL
- It's entirely public domain
- Each database is saved as a single file, which is very portable
- A simple database browser is available here
Linking binaries vs including source
From the download page, you have the option of getting the amalgamated source or precompiled binaries. Linking against the precompiled binaries is likely to be your preferred solution, but achieving this in Linux stymied the author, and so the amalgamated source was used instead. Note that including the source in your project will cause each recompile to produce around 1000 warnings, depending on your compiler, which can be a little disconcerting.
The dbHandler class
This code, while sufficient for the author's purposes, is not presented as a complete solution, or even a particularly comprehensive one. Much of it could likely be improved significantly. Nonetheless, it is provided here in a functional form, to be modified as the user sees fit.
Source code
Where you put these files is dependent on what you wish to use them for; if only your server will be using a database, then include them in the server project only, but if your client will also use a database, put them in shared code and include it in both. Note that the client will not be able to query the server database or vice versa - except for in a single-player game, or on the host of a listen server, client and server databases will be seperate files on separate computers.
dbhandler.h
#ifndef DBHANDLER_H
#define DBHANDLER_H
#pragma once
#include "sqlite3.h"
#include "utlvector.h"
// set this as you see fit
#define MAX_DB_STRING 38 // max length of a string returned from database
enum ValueType
{
ISNULL = 0,
INTEGER,
FLOATING,
TEXT,
};
struct dbValue
{
public:
dbValue(bool isNull)
{
type = ISNULL;
}
dbValue(int value)
{
type = INTEGER;
integer = value;
}
dbValue(double value)
{
type = FLOATING;
floating = value;
}
dbValue(const char* value)
{
type = TEXT;
Q_snprintf(text, sizeof(text), value);
}
ValueType type;
int integer;
double floating;
char text[MAX_DB_STRING];
};
typedef CUtlVector<dbValue> dbReadResult;
class dbHandler
{
public:
dbHandler();
~dbHandler();
static void Initialise(const char *filename);
// calling a series of commands between BeginTransaction and EndTransaction will significantly increase write speed...
// see http://www.sqlite.org/faq.html#q19 for more information. These functions ensure that nested calls will cause no harm
void BeginTransaction();
void CommitTransaction();
bool Command(const char *cmd, ...);
const char* ReadString(const char *cmd, ...);
int ReadInt(const char *cmd, ...);
dbReadResult* ReadMultiple(const char *cmd, ...);
void ShowError(int returnCode, const char *action, const char *command, const char *customError=NULL);
sqlite3 *Instance() { return db; }
sqlite3 **Reference() { return &db; }
int LastInsertID();
void EnableDebugging(bool b) { m_bIsDebugging = b; }
private:
sqlite3 *db;
bool m_bIsDebugging;
int m_iInTransaction;
};
extern dbHandler *g_pDB;
inline dbHandler* db()
{
return g_pDB;
}
#endif
dbhandler.cpp
#include "cbase.h"
#include "dbhandler.h"
#include "tier0/icommandline.h"
#include "filesystem.h"
void DebugDatabase_ChangeCallback( IConVar *pConVar, char const *pOldString, float flOldValue );
ConVar debug_database( "debug_database", "0", FCVAR_CHEAT, "Show all database commands in the server console", true, 0, true, 1, DebugDatabase_ChangeCallback );
void DebugDatabase_ChangeCallback( IConVar *pConVar, char const *pOldString, float flOldValue )
{
db()->EnableDebugging( debug_database.GetInt() == 1 );
}
#ifndef CLIENT_DLL
#define VarArgs UTIL_VarArgs
#endif
dbHandler* g_pDB;
dbHandler::dbHandler()
{
m_bIsDebugging = false;
m_iInTransaction = 0;
}
dbHandler::~dbHandler()
{
sqlite3_close(db);
if ( m_bIsDebugging )
Msg("database connection closed\n");
}
// returns true if the database already existed, false otherwise (it will be created in this case)
void dbHandler::Initialise(const char *filename)
{
//filesystem->FileExists(filename, "MOD");
g_pDB = new dbHandler();
int rc = sqlite3_open(VarArgs("%s\\%s",CommandLine()->ParmValue( "-game", "hl2" ),filename), g_pDB->Reference());
if( rc && g_pDB->m_bIsDebugging )
Msg("Failed to open database: %s\n", sqlite3_errmsg(g_pDB->Instance()));
}
void dbHandler::ShowError(int returnCode, const char *action, const char *command, const char *customError)
{
Warning(VarArgs("Database error #%i when %s command:\n%s\nError message: %s\n", returnCode, action, command, customError == NULL ? sqlite3_errmsg(db) : customError));
}
void dbHandler::BeginTransaction()
{
m_iInTransaction ++;
if ( m_iInTransaction == 1 )
{
bool bDebugging = m_bIsDebugging;
m_bIsDebugging = false; // don't write out BEGIN / COMMIT commands
Command("BEGIN");
m_bIsDebugging = bDebugging;
}
}
void dbHandler::CommitTransaction()
{
m_iInTransaction = min(m_iInTransaction-1,0);
if ( m_iInTransaction == 0 )
{
bool bDebugging = m_bIsDebugging;
m_bIsDebugging = false; // don't write out BEGIN / COMMIT commands
Command("COMMIT");
m_bIsDebugging = bDebugging;
}
}
// execute a command that outputs no data, returns true on success or false on failure
bool dbHandler::Command(const char *cmd, ...)
{
va_list marker;
char msg[4096];
va_start(marker, cmd);
Q_vsnprintf(msg, sizeof(msg), cmd, marker);
va_end(marker);
const char *command = VarArgs( msg );
if ( m_bIsDebugging )
Msg("%s\n",command);
bool retVal = true;
sqlite3_stmt *stmt;
int rc = sqlite3_prepare_v2(db, command, -1, &stmt, 0);
if( rc )
{
ShowError(rc, "preparing", command);
retVal = false;
}
else
{
rc = sqlite3_step(stmt);
switch( rc )
{
case SQLITE_DONE:
case SQLITE_OK:
break;
default:
ShowError(rc, "processing", command);
retVal = false;
break;
}
// finalize the statement to release resources
rc = sqlite3_finalize(stmt);
if( rc != SQLITE_OK)
ShowError(rc, "finalising", command);
}
return retVal;
}
const char* dbHandler::ReadString(const char *cmd, ...)
{
va_list marker;
char msg[4096];
va_start(marker, cmd);
Q_vsnprintf(msg, sizeof(msg), cmd, marker);
va_end(marker);
const char *command = VarArgs( msg );
if ( m_bIsDebugging )
Msg("%s\n",command);
bool isnull = true;
char retVal[MAX_DB_STRING];
sqlite3_stmt *stmt;
int rc = sqlite3_prepare_v2(db, command, -1, &stmt, 0);
if( rc )
ShowError(rc, "preparing", command);
else
{
//int cols = sqlite3_column_count(stmt);
do
{
rc = sqlite3_step(stmt);
switch( rc )
{
case SQLITE_DONE:
case SQLITE_OK:
break;
case SQLITE_ROW:
// print results for this row, the only row (hopefully)
Q_snprintf(retVal, sizeof(retVal), (const char*)sqlite3_column_text(stmt, 0));
isnull = false;
break;
default:
ShowError(rc, "processing", command);
break;
}
} while( rc==SQLITE_ROW );
// finalize the statement to release resources
rc = sqlite3_finalize(stmt);
if( rc != SQLITE_OK)
ShowError(rc, "finalising", command);
}
if ( m_bIsDebugging )
Msg("returning: %s\n", isnull ? "null" : retVal);
return isnull ? NULL : retVal;
}
int dbHandler::ReadInt(const char *cmd, ...)
{
va_list marker;
char msg[4096];
va_start(marker, cmd);
Q_vsnprintf(msg, sizeof(msg), cmd, marker);
va_end(marker);
const char *command = VarArgs( msg );
if ( m_bIsDebugging )
Msg("%s\n",command);
int retVal = -1;
sqlite3_stmt *stmt;
int rc = sqlite3_prepare_v2(db, command, -1, &stmt, 0);
if( rc != SQLITE_OK)
ShowError(rc, "preparing", command);
else
{
//int cols = sqlite3_column_count(stmt);
do
{
rc = sqlite3_step(stmt);
switch( rc )
{
case SQLITE_DONE:
case SQLITE_OK:
break;
case SQLITE_ROW:
// print results for this row, the only row (hopefully)
//retVal = sqlite3_column_int64(stmt, 0); - primary key values are int64, I think?
retVal = sqlite3_column_int(stmt, 0);
break;
default:
ShowError(rc, "processing", command);
break;
}
} while( rc==SQLITE_ROW );
// finalize the statement to release resources
rc = sqlite3_finalize(stmt);
if( rc != SQLITE_OK)
ShowError(rc, "finalising", command);
}
if ( m_bIsDebugging )
Msg(VarArgs("returning: %i\n",retVal));
return retVal;
}
dbReadResult* dbHandler::ReadMultiple(const char *cmd, ...)
{
va_list marker;
char msg[4096];
va_start(marker, cmd);
Q_vsnprintf(msg, sizeof(msg), cmd, marker);
va_end(marker);
const char *command = VarArgs( msg );
if ( m_bIsDebugging )
Msg("%s\n",command);
dbReadResult* output = new dbReadResult();
sqlite3_stmt *stmt;
int rc = sqlite3_prepare_v2(db, command, -1, &stmt, 0);
if( rc )
{
ShowError(rc, "preparing", command);
}
else
{
int cols = sqlite3_column_count(stmt);
// execute the statement
do
{
rc = sqlite3_step(stmt);
switch( rc )
{
case SQLITE_DONE:
case SQLITE_OK:
break;
case SQLITE_ROW:
for( int col=0; col<cols; col++)
switch ( sqlite3_column_type(stmt, col) )
{
case SQLITE_INTEGER:
output->AddToTail(dbValue(sqlite3_column_int(stmt, col))); break;
case SQLITE_TEXT:
output->AddToTail(dbValue((const char*)sqlite3_column_text(stmt, col))); break;
case SQLITE_FLOAT:
output->AddToTail(dbValue(sqlite3_column_double(stmt, col))); break;
case SQLITE_BLOB:
case SQLITE_NULL:
default:
ShowError(rc, "processing", command, "Data type is not supported; must be SQLITE_INTEGER, SQLITE_FLOAT or SQLITE_TEXT!"); break;
}
break;
default:
ShowError(rc, "processing", command);
break;
}
} while( rc==SQLITE_ROW );
// finalize the statement to release resources
rc = sqlite3_finalize(stmt);
if( rc != SQLITE_OK)
ShowError(rc, "finalising", command);
}
if ( m_bIsDebugging )
{
Msg("returning: ");
int num = output->Count();
for (int i=0; i<num; i++ )
{
if ( i > 0 )
Msg(", ");
switch ( output->Element(i).type )
{
case INTEGER:
Msg(VarArgs("%i",output->Element(i).integer)); break;
case TEXT:
Msg(VarArgs("%s",output->Element(i).text)); break;
case FLOATING:
Msg(VarArgs("%f",output->Element(i).floating)); break;
default:
Msg("<INVALID>"); break;
}
}
Msg("\n");
}
return output;
}
int dbHandler::LastInsertID()
{
return sqlite3_last_insert_rowid(db);
}
Suggested initialisation
Wherever you access the database, including the dbhandler.h header should suffice. Assuming that a single global database instance is sufficient for your needs, all functions and commands can be accessed through the db() object.
If you will be accessing the database throughout the game (rather than just at the start and end of a map), then it is recommended that you initialise the dbHandler class in your gamerules constructor, and delete it in the gamerules destructor. If you will only access it at particular times in the game, and these are reasonably spread out, then initialising and deleting it as required are recommended.
Here is a sample database initialisation and deletion, note that this will succeed regardless of whether the database file exists already or not, and that the use of IF NOT EXISTS on the creation commands avoids having to check for whether each table already exists, simplifying your code logic.
In the gamerules constructor:
db()->Initialise("filename.db"); // filename relative to mod directory
db()->BeginTransaction();
db()->Command("CREATE TABLE IF NOT EXISTS Characters (ID integer primary key asc, Name varchar, Level integer, Experience integer, Model varchar, Created date, LastActive date)");
db()->Command("CREATE TABLE IF NOT EXISTS CharacterAbilities (ID integer primary key asc, CharacterID integer, AbilityID varchar, Level integer)");
db()->Command("CREATE UNIQUE INDEX IF NOT EXISTS IX_Abilities ON CharacterAbilities (CharacterID, AbilityID)");
db()->CommitTransaction();
And in the gamerules destructor:
delete db(); // cleans everything up as appropriate
Database commands
For any non-query, use the db()->Command("command") function. It accepts multiple arguments in the same manner as UTIL_VarArgs, eg:
db()->Command("insert into Characters (Name, Level, Experience, Model, Created, LastActive) Values ('%s', %i, %i, '%s', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)", GetPlayerName(), 1, 0, GetModelName());
Single-field queries
To return a single value, there are the following functions: ReadString, ReadInt, and ReadFloat. These are each accessed and accept parameters in the same manner as the Command function, but return the relevant data type. If the result of the query is null for either the int or float datatypes, -1 is returned.
const char *name = db()->ReadString("SELECT Name FROM Characters WHERE ID=%i", characterID);
int id = db()->ReadInt("SELECT ID FROM Characters WHERE Name='%s'", characterName);
Multi-field queries
For multiple values, a CUtlVector is returned by the ReadMultiple function, where each element is a struct that could store one of many data types. This has been typdefed as dbReadResult for simplicity. These must be purged and then deleted after use, to avoid memory leaks. When you know in advance the types to expect, these can be accessed directly, eg
dbReadResult *character = db()->ReadMultiple("select Model, Level from Characters where ID = %i", characterID);
pPlayer->SetModel(character->Element(0).text);
pPlayer->SetLevel(character->Element(1).integer);
character->Purge();
delete character;
If you don't know the type of an element, it can be determined via: the .type value, eg
if ( character->Element(i).type == INTEGER )
szTemp = UTIL_VarArgs("Value is: %i\n", character->Element(i).integer )
else if ( character->Element(i).type == TEXT )
szTemp = UTIL_VarArgs("Value is: %s\n", character->Element(i).text )
It should be noted that sqlite is not strongly typed, and will readily convert the text value '27' into an integer 27, but this database handler class does not take this into account.
When returning values from multiple rows, ReadMultiple returns them in a 1-dimensional array, as if they were all from a single database row. This is intentional, and users wishing other behaviour are welcome to alter their code (and this article) to implement such. As it stands, a multi-row query could be handled like so:
Msg("Listing all characters...\n");
dbReadResult *characters = db()->ReadMultiple("select Name, Level from Characters");
for ( int i=0; i<characters.Count(); i+=2 )
Msg(UTIL_VarArgs("%s is level %i\n", characters->Element(i).text, characters->Element(i+1).integer));
characters->Purge();
delete characters;
Notes on usage and debugging
String length
The MAX_DB_STRING definition limits the maximum length of any text returned from the database - it is intended that the user change this value to suit their own needs.
Debugging database queries
When the debug_database cheat convar is set to 1, all database queries and commands (and their results) will be written to the server console. Regardless of whether this is set, any database errors will always be written to the server console as warnings - you obviously want to ensure that you never produce any warnings.
Transactions
The BeginTransaction and CommitTransaction functions may seem largely unnecessary (as the Command function could be used to call "BEGIN" and "COMMIT" respectively), but they were provided such that they could be called in a nested manner without producing unexpected results. As an example, if the server contains a function to save all data for a particular player, and this requires several database commands to do so, then it would be optimal for this function to encapsulate those commends in a single transaction. The server may also have a function to save all data for all players, and in this case it would make sense to include the saving of all players as a single transaction.
The BeginTransaction and CommitTransaction functions track the 'nested transaction depth,' and so BeginTransaction will increase this 'depth' by 1, and only call "BEGIN" when this depth is increased to 1, and CommitTransaciton will reduce this depth by 1, only actually calling "COMMIT" when this depth is decreased to 0. In this manner, the following code would commit both a save of a single player, and a save of all players, as a single transaction, reducing write time considerably:
void SavePlayer(CBasePlayer *pPlayer)
{
db()->BeginTransaction();
db()->Command("update Characters set Experience = %i where ID = %i", pPlayer->GetExperience(), pPlayer->GetCharacterID());
for ( int i=0; i<pPlayer->GetNumAbilities(); i++ )
db->Command("update CharacterAbilities set Level = %i where CharacterID = %i AND AbilityID = %i",
pPlayer->GetAbilityLevel(i), pPlayer->GetCharacterID(), i);
db()->CommitTransaction();
}
void SaveAllPlayers()
{
db()->BeginTransaction();
for ( int i=1; i<gpGlobals->numPlayers; i++ )
{
CBasePlayer *pPlayer = UTIL_PlayerByIndex(i);
if ( pPlayer )
SavePlayer(pPlayer);
}
db()->CommitTransaction();
}
