Game Development Community

SQLite integration - RFC

by John Vanderbeck · in Torque Game Engine · 04/10/2004 (9:06 am) · 16 replies

Ok so i've been working on integrating SQLite into Torque. Started yesterday and i'm planning to have it done either today or tomorrow. I have the basics working and all, but i'd like to get some comments on how you all would like to "work" with querys. The interface so to speak.

What i'm looking at right now is something similiar to how PHP works with databases. The way i'm looking at it right now you would do something akin to this:
new SQLiteObject(sqlite);
sqlite.openDatabase("Foobar");
%query = "SELECT username,password FROM users;";
%result = sqlite.query(%query);
while (sqlite.resultHasMore(%result))
{
   %username = sqlite.getColumn(%result, "username");
   %password = sqlite.getColumn(%result, "password");
   sqlite.nextRow(%result);
}
sqlite.closeDatabase();
sqlite.destroy();

getColumn() would allow you to use either the name of the column/field in the database, or an index number. There would be functions to cross reference those two methods.

Thoughts?
NextNext Thread
PrevPrevious Thread
More from Torque Game Engine


Torque Owner Westy
#1
04/10/2004 (9:51 am)
I use PHP to connec to MySQL databases and i think its a simple but very effective approach, as long as its fairly easy to pass results into an array then the metho aove looks fine.

Torque Owner John Vanderbeck
VanderGames
#2
04/10/2004 (10:04 am)
Well if you just wanted to stuff an array then it shuld be simple enough. Does TorqueScript support 2D arrays? I'll expand on the above using features that I didin't show but that are planned.
new SQLiteObject(sqlite);
sqlite.openDatabase("Foobar");
%query = "SELECT username,password FROM users;";
%result = sqlite.query(%query);
%rows = sqlite.numRows(%result);
%cols = sqlite.numCols(%result);
%results[0][0] = "";
for (%x = 0; %x < %rows; %x++)
{
   for (%y = 0; %y < %cols; %y = %y + 2)
   {
      // pack an array, interleaving the column name then the value
      %results[x][y] = sqlite.getColumnName(%result, %y);
      %results[x][y+1] = sqlite.getColumn(%result, %x, %y);
   }
}
sqlite.closeDatabase();
sqlite.destroy();

Something like that.

Torque Owner John Vanderbeck
VanderGames
#3
04/10/2004 (10:07 am)
Hmm something I just thought of. I'm not sure if Torque supports overloaded ConsoleFunction()'s

Associate Kyle Carter
#4
04/10/2004 (10:48 am)
It doesn't.

2D arrays work like foo[%d, %e], I believe.

Torque Owner John Vanderbeck
VanderGames
#5
04/10/2004 (11:29 am)
Thanks Ben I was thinking that was the case. I'll have to make some changes to my proposed script interface then. I was planning to use overloaded versions of things like getColumn() to provide a variety of ways to do it.

Associate Kyle Carter
#6
04/10/2004 (6:48 pm)
You can have a ConsoleFunction that accepts variable arguments, then have it do different stuff based on number of objects, though. Look at the audio code, it does this in a few places.

One thing that would be very very useful would be an easy way to pass arguments to a query that are 100% guaranteed to be escaped. A lot of DB wrappers for Torque forget this step and it's a real pain to add it.

Torque Owner John Vanderbeck
VanderGames
#7
04/10/2004 (7:55 pm)
Yeah adding an escape function makes sense. I'll do that.

I think i've got most of the interface mapped out. I'll be hopefully finishing this up tomorrow. I just need to work out handling of the result sets. When you start getting Vectors inside Vectors inside Vectors it gets rather confusing :D

Torque Owner John Vanderbeck
VanderGames
#8
04/12/2004 (9:59 am)
Just wanted to post an update on this. I know I said it would be out by yesterday, but its been delayed a tad. Essentially its all done, but i'm having some big problems with memory management, and I don't want to release something that has memory leaks and corruption in it :)

Hopefully i'll have it all worked out in a day or two, and i'll be sure to post it as soon as I do.

Torque 3D Owner Phil Carlisle
#9
04/12/2004 (10:13 am)
One thing I'd like, is to get the current row index..

Using something like a "cursor" position..

Basically, so while youre iterating something, you can update the "current position" in the rows returned.

Nice tho.. good luck with it.

Torque Owner John Vanderbeck
VanderGames
#10
04/12/2004 (10:35 am)
Phil, thats basically exactly how it works.

When you issue a query, SQLiteObject creates a result set containing the entire results form that query. The interface then allows you to step through the rows in the result set, retrieving the data. There is a nextRow() function which advances the result set to the next row. It would be trivial to add in some more unfctions to manipulate that "row cursor" in the result set.

Torque Owner John Vanderbeck
VanderGames
#11
04/12/2004 (12:39 pm)
Looks like i've managed to clean up my memory errors. At least it doesn't get an access violation anymore lol. I had to do an ugly hack though to make it work, due in part to my lack of experience with Vectors.

Anyway, now I just need to go back and clean things up, add in a few support functions to make wokring with this easier and more flexible, then type up some docs. I don't see any reaosn why I couldn't get it out by tonight.

Torque Owner John Vanderbeck
VanderGames
#12
04/12/2004 (5:27 pm)
The initial version has been released. The resource is not approved yet, but for those who dont' want to wait you can get it here. Standard disclaimer applies, this isn't an approved resource, use it at your own risk, blah blah.

Torque Owner Prairie Games
Prairie Games, Inc.
#13
04/12/2004 (6:04 pm)
SQLite is extremely cool... our persistence layer uses it.

Associate Kyle Carter
#14
04/12/2004 (9:03 pm)
See my comments in your resource. Nice work, btw.

Torque Owner John Eric Miller
#15
04/13/2004 (7:28 am)
Are there admin tools for adding data to it or does this all have to be done through script? For example what if I want to store the stats to objects in my game in a table, I only need to enter this data once but would retrieve it though script multiple times. What is the best way to add this data? Use a one time script?

Torque Owner John Vanderbeck
VanderGames
#16
04/13/2004 (7:31 am)
There are tools on the SQLite webpage that allow you to admin a SQLite database. They will safely co-exist with my implementation.