Using SQLite in your game (The good the bad and the ugly)

[Road Kill Kenny]

Hi all,

 

I decided to make a topic out of this considering there was so much talk about it under my status thread and I think it would be interesting for people to know the good the bad and the ugly of using SQLite or and sql databases for you game.

 

There also seems to be some uncertainty as to 'why' use this over a simple text file parser and I'll get to that in a moment. Feel free to add your own information / experience with SQLite I have only been using it for a few days now but It's just that easy.

 

How it works:

Ok so instead of storing data trivially in a text file you store it in a very structured was in tables that have columns and rows. Each table holds data about specific things such as Amour, Weapons, Abilities, BodyModels etc etc.

 

Within those tables every record or row has an ID which is unique to that record and a number of fields. So in our example in our armor table we could have: Armor_ID, Name, Model_Path, Health, Dmg_Reduction etc etc.

 

Now we can see that things are being stored in a much more structured way. Now each field gets assigned a variable type such as integer / text / bool/ etc. You can also put constraints on different fields such as "Not Null" this basically means that that field will not accept you creating a record with a Null value in that field which is good because what if you tried to load a path that was Null... <ERROR> Text files won't help you in that regard.

 

Ok so we have our database and now we want to use it in game. These are pretty much all the commands you need. Pretty simple ....

#include "sqlite3.h"


sqlite3 *database;

sqlite3_open("ElementalDatabase.s3db", &database)

sqlite3_stmt *statement;

sqlite3_prepare_v2(db, "SELECT columnA, columnB FROM TableA;", -1, &statement, 0)==SQLITE_OK

sqlite3_step(statement)

sqlite3_finalize(statement);

 

Anyway the cool thing is that you can set it up in a function that returns a std::vector<std::vector<Type>> array that that basically represents a table or a number of columns in a table like this

 

std::vector<std::vector<int>> QueryDatabase(std::string);

 

So once you have setup your QueryDatabase functions like above its as easy as calling it like this

 

QueryDatabase("SELECT columnA, columnB FROM TableA;");

 

Ok so I hope that gives you an overview. Keep in mind the above function is only for reading from the db.

 

The Pro's:

1. Well structured information that is easy to read and easy to find what you are looking for.

2. No need to write long winded text file parsers for each textfile. Everything can be efficiently stored in 1 file if you wish.

3. If you set up your database correctly SQLite won't allow you leave Null values or type in wrong types.

4. Can store extremely large amounts of data and access any of it easily and quickly. Much faster than reading from a text file especially a really big one.

5. Perfect tool for data driven design

 

The Con's:

I can't think of any.... tbh someone fill me in if you know any Cons.

 

 

 

So on a final note. You can also download SQLite database editors that allow you to visually go into tables etc and edit your data. The one I use is SQLite Administrator and it looks like this: So easy to use and you are less likely to make a mistake than if you are writing into a text file:

 

 

 

 

 

[Rick]

For the Lua people I've precompiled a DLL that can be used in LE http://www.leadwerks.com/werkspace/files/file/302-sqlite-for-les-lua/

 

It's similar to the above syntax with Ken with a slightly different way to do select statements and loop through the results.

 

 

The only con I can think of is you must know SQL. It's not a strong con but for someone who doesn't know SQL it'll be a barrier. Much like in the other post, people who don't know SQL will probably be intimidated and possibly not use it with the fear that it'll maybe take to long to learn SQL.

[Road Kill Kenny]

For the Lua people I've precompiled a DLL that can be used in LE http://www.leadwerks...te-for-les-lua/

 

It's similar to the above syntax with Ken with a slightly different way to do select statements and loop through the results.

 

 

The only con I can think of is you must know SQL. It's not a strong con but for someone who doesn't know SQL it'll be a barrier. Much like in the other post, people who don't know SQL will probably be intimidated and possibly not use it with the fear that it'll maybe take to long to learn SQL.

 

Well that is sort of a con. Though you don't have to know all SQL. I for sure don't. I only know how to read and write and thats pretty much all I need.

 

And it is a cake walk to learn don't you think.

 

It''s not hard to figure out that the SQL query: "SELECT ColumnA, ColumnB FROM Table TableA;"

 

means that you are getting the data from ColumnA and ColumnB in TableA....... real simple stuff.

[Rick]

And it is a cake walk to learn don't you think.

 

I do yeah. But that other thread is case in point on how some/most people approach learning new things. They don't feel comfortable and so stick with what they know. Everyone does that sometimes with some things in their life. Human nature I suppose.

[dennis]

Hello rick! this seems very useful to me

is there also a way to use a network based database (as in online)

[Rick]

Generally you won't let customers directly access your database. You'll have a layer between them and the database. In terms of games this would be your server application. The client and server applications exchange messages and information between each other and the server is then responsible to update the database based on these messages where it needs to. For example when you logout of a MMO the server will store your last position/rotation/buffs/etc into the database.

[dennis]

Generally you won't let customers directly access your database. You'll have a layer between them and the database. In terms of games this would be your server application. The client and server applications exchange messages and information between each other and the server is then responsible to update the database based on these messages where it needs to. For example when you logout of a MMO the server will store your last position/rotation/buffs/etc into the database.

aha, you mean as in a database sync with a encrypted connection ? (whaha I followed CISCO CCNA Network accedemy - graduaded)

anyway, I get it

[Canardia]

No, over the internet only binary - often even compressed - UDP/TCP messages are sent between game client and game server. The client only tells the server in symbolic patterns that for example the player has hit a tiger with a sword, and the server checks then from his databases which player race, what skills he has in swordfighting, what sword he has, what level the tiger is, what the tiger's defensive skills are, etc.... and then tells the client for how much he damaged the tiger.

 

There's no point querying like 10 different databases from the client over the internet, when a simple message is enough that he hit something, the server will know where he is and what he hit.

[Road Kill Kenny]

No, over the internet only binary - often even compressed - UDP/TCP messages are sent between game client and game server. The client only tells the server in symbolic patterns that for example the player has hit a tiger with a sword, and the server checks then from his databases which player race, what skills he has in swordfighting, what sword he has, what level the tiger is, what the tiger's defensive skills are, etc.... and then tells the client for how much he damaged the tiger.

 

There's no point querying like 10 different databases from the client over the internet, when a simple message is enough that he hit something, the server will know where he is and what he hit.

 

I try to keep things as simple as possible when doing this stuff. You have to keep the server in command....

 

So basically the server knows what everyone has... their weapons stats, armor stats etc....

 

When the client presses a key to attack a "Request to Attack" is sent to the server. The server analysis whether the attack can be done (i.e. goes through a bunch of pre-requisite tests") and.... if successful the server calculates the damage done by using all the stats on the server database.... then it does the damage and simply updates the HP amount on the client (as well as telling the client to do the attack but without dmg calcs) which is even safer than sending a dmg amount to the client because even if an error occurs and somehow a wrong number gets accross... the HP amount will be correct with the server next time it is updated at least.