Skip to main content
gamedev.net
🔒 Locked

SQLite3 suggestions

Started by evillive2 Sep 21, 2006 at 10:45 PM 4 replies 2.4k views
Original Post
evillive2
evillive2
I have been using SQLite3 for a while now but only through thin wrappers that don't do much error checking. I am currently converting an extensive flat text file database into an SQLite3 database because of it's flexibility, portability, embedability (sp?), small size and ease of integration. The game server runs on a linux machine and the clients are generally on windows but some on other OS's as well but windows and linux being the 2 main platforms. The main reason I am going with SQLite3 over something like MySQL is that I can embed it in the app and only have to distribute the sqlite3.dll file or the equivilent on linux and no server has to be connected to or started to use. This comes into play on the client side because the client will able to get schema information from the server to create/update a compatable local database for creating new content for the game that can be submitted as a sql script for approval before being introduced into the actual game. I started by creating my own thin wrappers around the SQLite3 API knowing ahead of time some were available for me to download but wanted to know what was going on and what can go wrong before getting used to the limitations of just one wrapper. This has worked out fine so far but the thin wrapper has gotten a little large and now it looks a bit more like something that 12 people put together each with varying knowledge of the underlying SQLite3 API [smile]. Finally, onto the question at hand. I have decided that I know enough about the inner workings of SQLite3 to know that I don't want to re-write anymore if I don't have to. So are there any suggestions on available wrappers that are cross platform? Preferably that use STL containers and strings and don't involve linking to another lib. This is a pain for me because I am not too good at making/using makefiles.. yeah VS has spoiled me since the days I began learning Turbo Pascal on the 286 in high school in an editor resembling DOS edit. I am currently looking at the ones available from the SQLite.org web page but any suggestions on what some other people have found useful would be great. Thanks in advance /*------------------------------------------------------ FYI - converting an existing text based MUD to be sort of graphical. Think DikuMUD meets Roguelike meets Atari. Using SDL for graphics & input, SDL_Net for networking although possibly moving to NET2 in the near future. I haven't chosen a sound API yet but leaning towards SDL_mixer or FMOD and obviously I have chosen SQLite3 for a database. -------------------------------------------------------*/
Evillive2
Kylotan
Kylotan
I'm not sure what sort of extras you could need. I get along fine just by pulling each row into a vector of strings, and it's trivial to convert from a string to an integer or a floating point value if you need it. So I think your requirement from a wrapper will depend entirely on your program's requirement.
evillive2
evillive2
Quote:
Original post by Kylotan
I'm not sure what sort of extras you could need. I get along fine just by pulling each row into a vector of strings, and it's trivial to convert from a string to an integer or a floating point value if you need it. So I think your requirement from a wrapper will depend entirely on your program's requirement.


I figured as much. None of the ones I have looked at really offer anything I need that I don't already have although bunches of stuff I don't need. I suppose that is another blessing in disguise... the interface can be as simple or robust as you want it to be. I was just hoping to cheat a little I guess :)

I do the vector of strings thing myself and then a vector of rows as a dataset. I was thinking this would be ok but then again I am still a newbie to STL (Sandard Library) containers and SQL databases in general and figured why not look around to see what others are using.

Thanks for the reply though. I feel a little safer in my decisions.
Evillive2
Kylotan
Kylotan
You might want to clean things up a little with some overloaded conversion functions.

eg.
void Get(string& s, const string& dbValue){ s = dbValue; }void Get(int& i, const string& value){ i = atoi(dbValue.c_str()); }void Get(MonsterID& id, const string& dbValue){ id = LookUpMonsterById(atoi(dbValue.c_str())); }


Then you can convert a row to an object pretty quickly:
class Monster {    string name;    int level;    MonsterID uniqueId;    // Assumes SELECT name,level,id FROM monster    void GetFromDBRow(DBRow& row)    {        Get(name, row[0]);        Get(level, row[1]);        Get(uniqueID, row[2]);    }};


You probably have something similar though...
evillive2
evillive2
Quote:

// Assumes SELECT name,level,id FROM monster
void GetFromDBRow(DBRow& row)
{
Get(name, row[0]);
Get(level, row[1]);
Get(uniqueID, row[2]);
}


That is one reason I was looking into a map > for a dataset for lookup by column name instead of index but it looks like no one else is using a map in this way in their wrappers. Should I be discouraged by this? I only use the sqlite database at load time or to save durring editing so I don't think performance would be an issue for me (although using BEGIN/END TRANSACTION around blocks of queries/insets/updates speeds things up tremendously) as it literally takes less than 2 seconds to load or rewrite a 3 MB database made up of mostly strings. Right now I am using look up tables and hard coded indexes instead which is getting pretty messy hence the thought of std::map.

One thing I did want to ask is how do you handle your queries? Do you use the sqlite3_step function with a prepared statement, sqlite3_exec with a callback or sqlite3_get_table and convert the table to strings? I haven't gotten into binding or anything of that nature yet as this is still a pretty simple system.

I currently use the sqlite3_step function and it seems to work fine for me. I use a combination of the database and query object that can be used in 2 ways:

// perform a query the quick waySQLiteDB db; // the databaseSQLiteDataset data; // the data from a querystring sql; // the sql to execute...db.ExecQuery( sql, data ); // returns records into data which also has error info...// build a query for re-useSQLiteDB db; // the databaseSQLiteDataset data; // the data from a querystring sql; // the sql to executeSQLiteQuery query; // the query stored in the db object...db.BuildQuery( query, "name of query", sql ); // build the query...db.ExecQuery( "name of query", data ); // call it by name...// query can be found by calling db.GetQuery( "name of query" );db.ExecQuery( query, data ); // execute the query contained in the query object


I want to keep the database separate from the actual objects (in case I change database backends etc.) so I have a derived database class that has the overloaded functionality similar to what you mentioned above that takes references to objects and loads data into them via their various interface functions.

Any reason I should do this differently? I wrote it out on paper first and it seems solid enough to me for many reasons but maybe there are reasons I don't know about to do it differently.

Thanks for your replies :) Most people I talk to use MySQL (readily available on most servers) and tell me to switch so they can help but I just don't think you should have to use something that big for an editor and SQLite seems to offer everything I need without that hassle.
Evillive2
Kylotan
Kylotan
An std::map of column names to fields seems reasonable, but I generally found that pulling the columns out sequentially was never a problem anyway, unless you do a lot of 'SELECT *' queries. You generally need to know exactly which fields you're retrieving anyway, so getting them by name rather than by order is not a great benefit. But by all means, if you find it's a problem you need solving, solve it.

I just use sqlite3_exec() directly with a trivial callback that pushes the data into the vector of strings. You could spend ages optimising this side of things but as you say, often performance isn't a problem in this part of the system. I just like to keep it simple.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.