SQLite Lua interface

totebo

Hi,

I've tried the SQLite implementation and it works great. Being a little rusty on the SQL syntax, and general DB thinking, has anyone created a more abstracted way of using a SQLite, to avoid hacking SQL commands? Of course this may be tricky because of varying database structures, but I'm looking to use it instead of a JSON textfile, which means the most basic implementation would be enough.

If not, I am going to attempt this myself, so may be back with more SQL related questions!

Cheers,

Niclas

ar2rsawseen

so basically you want to create a key value store from SQL?
that kind of really kills the purpose of having SQL, data you should store should be splitted in normal form, etc. And it is hard to abstract it much then, except from ORM, where you populate class with queried data, as in you match your underlaying schema with an class structure, but again that might be an overkill for what you need.

So what was the reason for using SQL in the first place?

totebo

@ar2rsawseen, I was fearing a response like that!

I want to use a database rather than a text file to avoid saving mostly redundant data which consumes processing power. In my game I want to store new x and y coordinates for each tap. The text file could eventually grow to 1MB or more.

If I use a text file I have to:

1. Read the entire text file into memory (for each tap)
2. Add the x and y coordinates
3. Save the entire text file

If I used a database I would instead:

1. Load the database (once per session)
2. Insert a row of new x and y coordinates (for each tap)

If I used a database it would mean a consistent time for saving coordinates, and if I used a text file the save time would grow for each tap. The overhead of regularly saving a text file that could exceed 1MB seems to me like a Bad Idea (tm), but then again, I haven't actually tested this.

Feel free to slap my fingers for using SQL as a means to do something that it wasn't actually meant to do.

Niclas

ar2rsawseen

@totebu, actually you only need to read file once at the start of the app.
Store it in table, then put all new taps into table and on app exit event save it in the file.
It would make it even more efficient than sql (no writing on each tap).

About overhead, that might be true, as SQL probably use binary data, it will consume less.
And sql to insert and select x,y coordinates is not that hard.

but if you are concerned about file size, you could also split a normal file let's say by days, creating new file, or by game session, depending on your logic and when you need it.

So you must chose what seems to be more fit for you

totebo

Those are good suggestions! I was thinking about saving the file on exit, but my concern was that it might take too long and the OS would kill the app before the data was written. Also, if the app crashes (it happens! ) the player would lose all the data for that session.

Keen to try the SQL option for those reasons, so I think I'll give that a bash.

ar2rsawseen

@totebo, well even if you save the table contents on each new coordinate, you would basically reach SQL efficiency, well except from file size

SinisterSoft

totebo: What you should do rename the old file to a .bak (delete the previous .bak) first then save the file. On loading if the main file is corrupt erase and rename the .bak back again and reload.

totebo

Ar2: You're warming to the SQL solution? Or, did you actually just say text file would be equally efficient? Surely writing a lot of data all the time takes longer than writing tiny amounts of data all the time? I like to encrypt the text file too, which would add time.

Sinister: You mean keep a "passive" backup of the textfile, and revert if needed? That's clever.