PDA

View Full Version : [WIP] SQLite support for libcod



voron00
28th October 2017, 21:59
I'm currenctly working on SQLite support for libcod. MySQL is nice but might be too heavy and/or hard for some people. SQLite is a lightweight and compact local database engine, and it's also quite easy to use.

The basic support has been added, there are no async support yet, but i'm working on it.

On Arch Linux, SQLite is preinstalled. On ubuntu proably apt-get install sqlite or something like that. It won't be compiled if you don't have the library though.

Unlike MySQL, SQLite is VERY simple, and doesn't need any wrappers, here's a little how to:

Open your database:

level.ranksdb = sqlite_open("databases/ranks.db");

Execute your query:

rows = sqlite_query(level.ranksdb, "SELECT id, rank FROM ranks");

Where the first argument is the database pointer and second is your query.
The returned result of rows is the sqlite fields and rows of each field.
So for our query, the result will be like: rows[0][0] where the first number is the FIELD number and second is ROW number.

If you do:


for (i = 0; i < rows.size; i++)
printf("% - %\n", rows[i][0], rows[i][1]);

The result will be:


1 - Idiot
2 - Noob
3 - Sergant Slaughter
4 - Lowak
5 - Loler
6 - Private
7 - OVER 9000
8 - JERKASS
9 - Crap
10 - Faggot
11 - Maggot
12 - Nanny's boyfriend
13 - Leader of gay-parade
14 - Captain Obvious
15 - Ass
16 - Cock of Duty 2

Close your database:

sqlite_close(level.ranksdb);

You can close your database on endmap for example, but that's not neccecary, a new sqlite_open will just return the previous database pointer on new map/restart.

Also made some nice error messages if you f*ck up your query for example:

******* script runtime error *******
gsc_sqlite_query() failed to fetch query data: near "FRM": syntax error: (file 'novoselscripts/zom.gsc', line 126)
rows = sqlite_query(db, "SELECT * FRM ranks");
*
called from:
(file 'maps/mp/gametypes/_callbacksetup.gsc', line 15)
[[level.callbackStartGameType]]();
*
started from:
(file 'maps/mp/gametypes/_callbacksetup.gsc', line 10)
CodeCallback_StartGameType()
*
************************************
Sys_Error: Error during initialization:
script runtime error
(see console for details)
gsc_sqlite_query() failed to fetch query data: near "FRM": syntax error

Lonsofore
29th October 2017, 03:22
Now we can use db on our $1-hostings, yay!

voron00
31st October 2017, 05:51
The async support has been added and so far has been more or less stable, but still quite experimental for now.

Currently limited to 256 rows/fields max and 512 async tasks max (you proably won't come even close to that limit).

Also added fixes to free task/db connections on map change/restart.

How to use async:

Init your async sqlite at gametype start:


async_sqlite_initialize();

Execute your query:


async_sqlite_create_query(level.yourdb, "SELECT SQLITE_VERSION()", ::callbackFunc, "somearg");


callbackFunc(rows, arg)
{
printf("%\n", rows[0][0]);
}

Else is similar to sqlite_query.

voron00
31st October 2017, 16:16
Added fixes for errors when database file may be locked, for example if you are running multiple servers trying to simultaneously write to one database, especially for synchronous sqlite_query, now there is a 2 seconds timeout, should be enough, but try to avoid using sqlite_query in that cases because it will cause hangups, use async for that.

voron00
1st November 2017, 20:18
Ugh, using internal cod's string conversion was a bad idea, reverted, gotta find a better solution on how to store more rows/fields since you cant allocate too big structs.

voron00
30th July 2018, 19:57
Fixed a crash that was apparently caused by sqlite3_column_text could return null in some rare cases. I guess this stuff should be fully stable now.