Thursday, May 26, 2011

Using SQLite as a data store in C++

There are various advantages in a using a SQLite as a database with the best being that it’s small, fast and reliable.

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;
}

Tuesday, May 24, 2011

It all starts with an idea

This piece is taken directly from my blog post over at goagri.com. It applies to game development as well and I thought it best to share it here again.

While I was studying IT back in 1997 one of the projects I had to do was to develop a software application for a subject of my choice. I chose to develop a dairy farming application. The reason why I chose farming was because I grew up on a farm and I saw a great need for such a type of software.

Coincidently someone else had also chosen to create a similar project and at the end of the day his project was marked before mine and as such I was told by the lecturer that I “copied” my idea from that person and as such I got lower marks.

Do you see the irony? I developed a complete Dairy software program and someone else did exactly the same. How can ideas that relates to the same subject be copied from each other? Each software product covers the following subjects:
•A Farm
•With Cows
•That eat food
•Lives on fields
•And deliver liters of milk
•To a supplier.

That was the last time I developed a complete product for farmers since I was totally devastated by my experience. That is until now. I have come full circle.

As time passed I always had the yearning to attempt such a project again using the knowledge I gathered during the years together with the technology of the day. But always I will think of that lecturer and it will discourage me because, “What if someone else also have the same ideas?”

Today I am a wiser man and I know that there will always be the same ideas out there. It’s called Simultaneous Invention. I quote:
“In any period, ideas are discovered at the same time. Even big ideas. This is true for the past, present, and in different culturess.”


This image is copyright by http://www.kk.org

Monday, April 4, 2011

Wika 2011 Video on Youtube

Over the weekend I uploaded my first video to youtube. Jay! :)

Video follows: