SQLite question
by Gareth Fouche · in Torque Game Engine · 02/12/2006 (3:55 am) · 5 replies
I've recently got the SQLite resource working, but I'm a bit unsure of things when it comes to working with databases, its been a while since I used them. I have a client sqlite object, and a server sqlite object. Can I make them point at the same database file, at the same time, with no worries?
I'm using it for a single player RPG, been looking through Dreamers tutorials, but I was wondering if instead of say having a client side inventory table and a server side one, I could simply write entries to a single inventory table in the server code and then read that same table from the client side, etc.
Can anyone point out any problems with this idea? Will this work? Will I run into concurrency problems, the server side locking the table and the client side being denied access, or something? Will I have to code to check for this, or will one transaction wait until the other is finished, then continue. Will SQLite handle this behind the scenes for me?
I'm using it for a single player RPG, been looking through Dreamers tutorials, but I was wondering if instead of say having a client side inventory table and a server side one, I could simply write entries to a single inventory table in the server code and then read that same table from the client side, etc.
Can anyone point out any problems with this idea? Will this work? Will I run into concurrency problems, the server side locking the table and the client side being denied access, or something? Will I have to code to check for this, or will one transaction wait until the other is finished, then continue. Will SQLite handle this behind the scenes for me?
#2
try (update)
If database locked, wait half a second and try again, until successful.
Would prefer it if I didn't have to write that kind of thing everywhere I put database access code. But if, as you say, its one thread, it should be fine.
To test I did write some code that updates a table on the server side and reads it on the client side, with both objects being open the whole time; ran each at a 50 millisecond delay, seemed to work fine, just wasn't sure that it wasn't a coincidence. Would be a hard bug to pick up on during the game.
02/13/2006 (12:12 am)
Ok cool, thanks Brian. So you dont check that the database is locked before updating/reading? I'm just wondering if I need to do something like (psuedocode):try (update)
If database locked, wait half a second and try again, until successful.
Would prefer it if I didn't have to write that kind of thing everywhere I put database access code. But if, as you say, its one thread, it should be fine.
To test I did write some code that updates a table on the server side and reads it on the client side, with both objects being open the whole time; ran each at a 50 millisecond delay, seemed to work fine, just wasn't sure that it wasn't a coincidence. Would be a hard bug to pick up on during the game.
#3
The TDN topic on multithreading in 1.4 says that TORQUE_MULTITHREAD needs to be defined in core/torqueConfig.h. It appears to be defined on my copy (that's how it was setup by default).
However, it also says that you should pass the -pthread option to GCC on Mac OS X and Linux (I'm on Mac OS X), but it doesn't appear to be in the xcode project that comes with 1.4. (?)
So, Torque seems to be running multithreaded now in 1.4, but I'm not entirely sure where the threads run, or exactly what they do (what thread are script callbacks running in, etc...). I'm going to read this TDN article later and get a better handle on it:
tdn.garagegames.com/wiki/Torque/1.4/MultiThread
In any case, it might be safest to recompile your sqlite with 'THREADSAFE' defined (if using sqlite 3). According to the sqlite documentation, it's important to use each sqlite3 connection in one and only one thread, so you'd HAVE to create multiple references to it if you accessed from seperate threads. However, I don't quite see where the threads are used in Torque yet, so I don't know if the way we're using sqlite is 'dangerous' or the not. If all the scripts are run in the same thread, for example, there shouldn't be any problem.
On edit:
Found this in Ben Garney's .plan that seems to be relevant:
That seems to mean that all the scripts would be run in the same thread, ergo there's no need to use the multi-threaded version of sqlite3, nor worry about multithreaded access to the database. I'll let you know if I find anything else about the threads/sqlite issue if I get the chance later.
02/13/2006 (7:59 am)
Well, I've succeeded in confusing myself regarding the status of threads in Torque. It looks like multithreading is turned on by default in 1.4.The TDN topic on multithreading in 1.4 says that TORQUE_MULTITHREAD needs to be defined in core/torqueConfig.h. It appears to be defined on my copy (that's how it was setup by default).
However, it also says that you should pass the -pthread option to GCC on Mac OS X and Linux (I'm on Mac OS X), but it doesn't appear to be in the xcode project that comes with 1.4. (?)
So, Torque seems to be running multithreaded now in 1.4, but I'm not entirely sure where the threads run, or exactly what they do (what thread are script callbacks running in, etc...). I'm going to read this TDN article later and get a better handle on it:
tdn.garagegames.com/wiki/Torque/1.4/MultiThread
In any case, it might be safest to recompile your sqlite with 'THREADSAFE' defined (if using sqlite 3). According to the sqlite documentation, it's important to use each sqlite3 connection in one and only one thread, so you'd HAVE to create multiple references to it if you accessed from seperate threads. However, I don't quite see where the threads are used in Torque yet, so I don't know if the way we're using sqlite is 'dangerous' or the not. If all the scripts are run in the same thread, for example, there shouldn't be any problem.
On edit:
Found this in Ben Garney's .plan that seems to be relevant:
Quote:
We've also added a lot of multithread safing into the engine. TGE runs single-threaded, same as before, but if you want to interface it with multithreaded code (like, say, the background loader in Atlas, or a certain IRC library I know of), the hooks are in there now.
www.garagegames.com/blogs/8863/8670
That seems to mean that all the scripts would be run in the same thread, ergo there's no need to use the multi-threaded version of sqlite3, nor worry about multithreaded access to the database. I'll let you know if I find anything else about the threads/sqlite issue if I get the chance later.
#4
Is there a danger to this though? Should I make seperate client and server sqlite connections, or can I safely use just the one on both sides? I thought that even in single player mode, variables and functions defined on the server were 'scoped' only to the server calls.? But I seem to have been wrong?
02/13/2006 (10:21 am)
Hmmm, another question, can I use sqlite objects I create on the server side ( stored in a global variable, say $WorldDB), and reference the same object (global variable) on the client side? I've run a little test, it seems to work, it looks like if I create that global variable on the client side the server also sees it, and can use it to access the DB.Is there a danger to this though? Should I make seperate client and server sqlite connections, or can I safely use just the one on both sides? I thought that even in single player mode, variables and functions defined on the server were 'scoped' only to the server calls.? But I seem to have been wrong?
#5
02/13/2006 (10:32 am)
Function calls are definately scoping to both, I have an OpenWorldDB() function call defined in both the client and server side scripts, I exec the client one, then the server, then to test I exec the client one again, it calls the server function (ie the server function definition seems to have overwritten it!). Must admit, its good to know how that before I start coding, could lead to confusion down the line!
Torque Owner Brian Hill
I create a couple of different sqlite objects of different subclasses for different purposes (one for creating new characters on the 'client side', another for using the in-game database editor I'm cobbling together, and another for actually playing the game). I did this mostly to split up the code -- I hate scrolling through 12 yard long source code files.
While my code usually doesn't have two sqlite objects for the same file existing for long, I haven't noticed any trouble instantiating and using my 'database editor' sqlite object at the same time my 'game' sqlite object also has the same file open. I think sqlite uses file locking to handle concurrent access, and the database file is only locked for a moment while it's updated.
IOW, SQLite seems to handle it all behind the scenes.