The best way to use SQLite is to embed it in the game using the recommended amalgamation source code available on sqlite.org, but it is also available as a library for Windows (DLL) or Linux (shared-library .so ) .
From a licensing point of view, using SQLite in any commercial game is also VERY safe since SQLite’s source has been released into the public domain.
The following steps should allow C++ programmers to embed SQLite into their games.
Step 1
Go to http://www.sqlite.org and click on Download and grab a file named sqlite-amalgamation-X_X_X.zip where the X’s are replaced by the appropriate version number. In my case I downloaded sqlite-amalgamation-3_7_0.zip (Windows) or sqlite-amalgamation-3.7.0.tar.gz (Linux)
Step 2
Extract the downloaded file and add the sqlite3.h and sqlite3.c files to the project. The size of the sqlite3.c file should be really big, around 3.96MB.
Step 3
Compile sqlite3.c in order to create a object file for it. The object file should be around 1.09MB.
Step 4
Include the sqlite3.h file in the game’s c++ files. For demonstration purposes I created a main.cpp file that looks as follows:
#include <stdio.h>
#include <cstdlib>
#include "sqlite3/sqlite3.h"
#include <string>
using namespace std;
int main(void){
sqlite3 *db;
char *zErrMsg = 0;
int rc;
string q = "";
string dbName = "game.dat";
//Attempt to open database. If it does not exist create it.
rc = sqlite3_open(dbName.c_str(), &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
//Set query to execute
q = "";
sqlite3_close(db); //Always close db when done with it
return 0;
}
Compile and run. There should now be a file called game.dat in the debug directory since SQLite will create the database file by default if it is not found.
Step 5
Since it is now possible to create a database it is time to create some tables. For demonstration purposes I created 2 tables. t_type and t_assets.
The Types table will simply contain information about various types of assets. The table will consist of an id for identification purposes, a parent id in order to be able to create a tree hierarchy and a title for what type it is, e.g. weapon, vehicle, npc, etc…
The Assets table will contain information about a specific asset such as an image file or 3d object. The table consist of an id, a type id, src or location of the asset, title or name of the asset and a description. E.g. Image of a “Toyota Bakkie” that is located at /assests/vehicles/toyota.png with the description:”A Red 4×4 Toyota Bakkie”.
In order to execute queries on the database the sqlite3_exec function will be used. The code to do that follows:
//Set query to execute
q = "CREATE TABLE t_type"
"("
"'ID' INTEGER PRIMARY KEY AUTOINCREMENT,"
"'PARENT_ID' INTEGER,"
"'TITLE' TEXT" ");"
"CREATE TABLE t_assets"
"("
"'ID' INTEGER PRIMARY KEY AUTOINCREMENT,"
"'TYPE_ID' INTEGER,"
"'SRC' TEXT,"
"'TITLE' TEXT,"
"'DESCRIPTION' TEXT"
");";
rc = sqlite3_exec(db, q.c_str(), NULL, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
Note that I do not test whether a table actually exist, maybe you can add that to your code as a bit of an exercise?
Step 6
It is now time to insert some data. The query to do that follows:
q = "INSERT INTO t_type('PARENT_ID','TITLE') VALUES (0,'VEHICLES');"
"INSERT INTO t_type('PARENT_ID','TITLE') VALUES (0,'ANIMALS');"
"INSERT INTO t_assets('TYPE_ID','SRC','TITLE','DESCRIPTION') VALUES (1,'/assets/vechicles/toyota.png','A Red 4x4 Toyota Bakkie');"
"INSERT INTO t_assets('TYPE_ID','SRC','TITLE','DESCRIPTION') VALUES (1,'/assets/vechicles/ferrari.png','A twin-turbo F50 Ferrari, like the one John Carmack drives');"
"INSERT INTO t_assets('TYPE_ID','SRC','TITLE','DESCRIPTION') VALUES (2,'/assets/vechicles/munky.png','A munky. It is a bloody munky');";
You can of course do more advanced stuff like getting the ID of the types and then use that in the query.
A Fast way to check the data in the file is to open it with notepad or notepad++ to check the results; but I will not recommend that approach with big files.
Step 7
In order to query the data, a callback function needs to be declared. The declaration for that looks as follows:
static int callback(void*, int, char**, char**);
This callback function will be called for each row returned from the query.
The first parameter of this function is not really used.
The second parameter contains the number of columns.
The third parameter is an array containing the value of each column as a char*.
The Fourth and last parameter contains the Names of each column.
An example callback function follows:
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
And this is how it will be used:
q = "SELECT * FROM t_assets WHERE TYPE_ID=1;";
rc = sqlite3_exec(db, q.c_str(), callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
Expected Output:
And that is it..The full source code for main.cpp looks as follows:
#include <stdio.h>
#include <conio.h>
#include <cstdlib>
#include "sqlite3/sqlite3.h"
#include <string>
using namespace std;
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(void){
sqlite3 *db;
char *zErrMsg = 0;
int rc;
string q = "";
string dbName = "game.dat";
//Attempt to open database. If it does not exist create it.
rc = sqlite3_open(dbName.c_str(), &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
//Set query to execute
q = "CREATE TABLE t_type"
"("
"'ID' INTEGER PRIMARY KEY AUTOINCREMENT,"
"'PARENT_ID' INTEGER KEY,"
"'TITLE' TEXT"
");"
"CREATE TABLE t_assets"
"("
"'ID' INTEGER PRIMARY KEY AUTOINCREMENT,"
"'TYPE_ID' INTEGER KEY,"
"'SRC' TEXT,"
"'TITLE' TEXT,"
"'DESCRIPTION' TEXT"
");";
rc = sqlite3_exec(db, q.c_str(), NULL, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
q = "INSERT INTO t_type('PARENT_ID','TITLE') VALUES (0,'VEHICLES');"
"INSERT INTO t_type('PARENT_ID','TITLE') VALUES (0,'ANIMALS');"
"INSERT INTO t_assets('TYPE_ID','SRC','TITLE','DESCRIPTION') VALUES (1,'/assets/vechicles/toyota.png','Toyota Bakkie','A Red 4x4 Toyota Bakkie');"
"INSERT INTO t_assets('TYPE_ID','SRC','TITLE','DESCRIPTION') VALUES (1,'/assets/vechicles/ferrari.png','Ferrari','A twin-turbo F50 Ferrari, like the one John Carmack drives');"
"INSERT INTO t_assets('TYPE_ID','SRC','TITLE','DESCRIPTION') VALUES (2,'/assets/vechicles/munky.png','Munky','A munky. It is a bloody munky');";
rc = sqlite3_exec(db, q.c_str(), NULL, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
q = "SELECT * FROM t_assets WHERE TYPE_ID=1;";
rc = sqlite3_exec(db, q.c_str(), callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db); //Always close db when done with it
printf("\nPress Enter.");
getch();
return 0;
}
