SQLiteInterface - Threaded SQLite3 support for Torque

Download code

----------------------------
Notice:Last changes are from 2012-09-20:
Fixed potential heap corruption which can occur when deleting SQLiteRecordSet if no data provided by SQLite3 engine (such as running "SELECT avg(field1) FROM table1" on the empty table - the field count is 1 and value is NULL).
Manual fix:
File: SQLiteProcess.cpp
on line 93 change from :

row->mValues.push_back("");

to

row->mValues.push_back(dStrdup(""));

----------------------------
Notice:Previous changes from 2011-09-28:
Fixed memory leak and added ability to perform SQLite requests directly "in-line" (not via events) in main thread. Added examples (how-to) for engine and scripts. Archive is updated with new files.
----------------------------


First of all I would like to thank / give credits to:
Tony Richards for Event Driven Database resource
John Vanderbeck for SQLite Integration for Torque resource
GarageGames team for such a great engine as Torque is

----------------------------
WARNING!!!
This resource depends on Thread-safe console for Torque 3D,
be sure you have implemented it before taking your hands on SQLiteInterface!

If you skip, you will get memory corruption and crashes,
as by default, Torque's console sub-system doesn't play nice when used in threads!
----------------------------
Now, to the actual resource...

----------------------------
This resource gives a full, threaded/event-based/async SQLite3 support.
Implementation is simple:
1. Download code
2. Unpack/copy to your Torque3D installation (keeping the folder structure)
3. Adjust config files:
Templates/Full/buildFiles/config/project.conf (using Full template as example)

//..skipped
// Configure Torque 3D
Torque3D::beginConfig( "win32", "Full" );

//..skipped

   includeModule( 'sqlite' ); // <--- Add this line

Torque3D::endConfig();
?>

4. Generate projects
5. Build/compile
And you are ready.
----------------------------I've used latest (when preparing this resource) version of amalgamation build of SQLite.
If you want to update to the latest, just replace the sqlite3.c and sqlite3.h files.
Files included in the resource:

// the original SQLite3 3.7.7.1
Engine/source/sim/sqlite/sqlite3.c

// header of the above
Engine/source/sim/sqlite/sqlite3.h

// The actual SQLiteInterface class
Engine/source/sim/sqlite/SQLiteInterface.cpp

// header of the above
Engine/source/sim/sqlite/SQLiteInterface.h

// Helper class to work with results
Engine/source/sim/sqlite/SQLiteRecordSet.cpp

// header of the above
Engine/source/sim/sqlite/SQLiteRecordSet.h

// We have put "process" methods separately so it's easier to work with it
Engine/source/sim/sqlite/SQLiteProcess.cpp

// The description of the structs used in SQLiteInterface
Engine/source/sim/sqlite/SQLiteEvents.h

// An example implementation of "own&custom" way of handling/processing the data
Engine/source/sim/sqlite/SQLiteRequestExample.cpp

// A module for project generator
Tools/projectGenerator/modules/sqlite.inc

If you want to implement this manually, add all *.c/*.cpp/*.h to the solution and define SQLITE_ENABLED.
No external libs/dlls needed!

----------------------------Usage:
1. Initialization:

new SQLiteInterface(sqli);
sqli.initialize("art/mydatabase.db");

Be sure you implement the default callbacks in your scripts:
On success, it will call:

function SQLiteInterface::onInitialized(%this)
{
   echo("Connected!");
}

if failed:

function SQLiteInterface::onOpenFailed(%this, %errorCode, %errorMessage)
{
   error(%this SPC "filed to open the database:");
   error(%errorCode SPC %errorMessage);
}

If there will be any problem during query it calls:

function SQLiteInterface::onError(%this, %errorCode, %errorMessage)
{
   error(%this SPC "filed to open the database:");
   error(%errorCode SPC %errorMessage);
}

Public/scripted methods:

// Initialize SQLiteInterface (start the thread and open specified database).
void SQLiteInterface::initialize(%database); 
// @param database a filename of the SQLite3 database.
----------------------
// Returns true if the SQLiteInterface successfully initialized.
bool SQLiteInterface::isInitialized();
// @return bool value of the initialization status.
----------------------
// Pass true to enable the debug mode of the SQLiteInterface.
void SQLiteInterface::setDebug(bool debug);
// @param debug pass true to output debug information, false to disable.
----------------------
// Submit a query to SQLiteInterface for processing.
S32 SQLiteInterface::Execute(%sql, %callback = NULL, %queryReference = NULL); 
// @param sql The SQL request the SQLite3 should process.
// @param callback The console function or method (if queryRef is a valid SimObject) to call after the query is processed.
// @param queryRef SimObject to call the callback on, can be NULL - in this case it will call global function.
// @return SQLiteRecordSet object with results.
----------------------
// Run sql request directly from main thread.
S32 SQLiteInterface::processSQL(%sql);
// @param sql The SQL request the SQLite3 should process.
// @return SQLiteRecordSet object with results.
----------------------
// Submit a query (select) to SQLiteInterface for processing.
S32 SQLiteInterface::SelectSQL(%sql, %callback, %queryReference = NULL); 
// @param sql The SQL request the SQLite3 should process.
// @param callback The console function or method (if queryRef is a valid SimObject) to call after the query is processed.
// @param queryRef SimObject to call the callback on, can be NULL - in this case it will call global function.
// @return SQLiteRecordSet object with results.
----------------------
// Submit a query (update/delete/insert) to SQLiteInterface for processing.
S32 SQLiteInterface::UpdateSQL(%sql, %callback = NULL, %queryReference = NULL); 
// @param sql The SQL request the SQLite3 should process.
// @param callback The console function or method (if queryRef is a valid SimObject) to call after the query is processed.
// @param queryRef SimObject to call the callback on, can be NULL - in this case it will call global function.
// @return SQLiteRecordSet object with results.
----------------------
// Dumps information about tables in the currently-used database.
// Using a custom method of parsing data, just for example on how to work with SQLiteInterface.
void SQLiteInterface::dumpSQLiteTables();

----------------------------The SQLiteRecordSet:

const char* SQLiteRecordSet::getSQL();
// @return original SQL request string
----------------------
S32 SQLiteRecordSet::getLastInsertId();
// @return The last value used for an AUTO_INCREMENT field of the INSERT command
----------------------
S32 SQLiteRecordSet::getAffectedRowsCount();
// @return The number of rows affected by the query (INSERT/UPDATE/DELETE request)
----------------------
S32 SQLiteRecordSet::getRecordCount();
// @return The total number of records received from the SQLite3 by the SQL request.
----------------------
// Move to the next record on the dataset.
bool SQLiteRecordSet::nextRecord();
// @return true if we have a valid data, false when finished scrolling.
----------------------
// Rewind the recordSet to the first record (so you can start browsing through results again)
void SQLiteRecordSet::rewind();
----------------------
S32 SQLiteRecordSet::getDataFieldCount();
// @return Amount of fields retrieved from the database.
----------------------
const char * SQLiteRecordSet::getDataFieldName(S32 index);
// @return Field name for specified index.
----------------------
const char * SQLiteRecordSet::getDataFieldValue(S32 index);
// @return Field value from specified index or field name

----------------------------Example:

function loadDialogs()
{
   new ScriptObject(dialogLoader);
   %sql = "SELECT * FROM dialogsTable WHERE lang = 'EN';
   sqli.Execute(%sql, "dialogsLoadedCB", dialogLoader);
}
function dialogLoader::dialogsLoadedCB(%this, %result)
{
   while(%result.nextRecord())
   {
      createNewDialog(%result.dialogTitle, %result.dialogBody, %result.answersCount, %result.andswersString);
   }
   echo("Parsed" SPC %result.getRecordCount() SPC "rows of data.");
   echo("The original SQL string was:" SPC %result.getSQL());
   %result.delete();
}
function updateInventory(%type, %value)
{
   %sql = "UPDATE localInventory SET currentValue = '"@ %value @"' WHERE itemType = '"@ %type @"';";
   sqli.UpdateSQL(%sql);
   // We don't really care about result after update -- it will be automatically deleted,
   // so no need to pass the callback function/method and object
}
function increaseEverythingInInventory(%type)
{
   %sql = "UPDATE localInventory SET currentValue = currentValue + 1 WHERE itemType = '"@ %type @"';";
   sqli.UpdateSQL(%sql, "inventoryUpdatedCB", inventoryManager);
   // We don't really care about result after update -- it will be automatically deleted,
   // so no need to pass the callback function/method and object
}
function inventoryManager::inventoryUpdatedCB(%this, %result)
{
   echo("We have just updated" SPC %result.getAffectedRowsCount() SPC "records in DB!");
   %result.delete(); // Don't forget to clean-up!
}
function inventoryManager::giveMoney(%this, %amount)
{
   %sql = "INSERT INTO myInventory SET itemType = 'money', amount = "@ %amount @";";
   sqli.UpdateSQL(%sql, moneyGivenCB, %this);
}
function inventoryManager::moneyGivenCB(%this, %result)
{
   echo("The ID of the new inserted item is" SPC %result.getLastInsertId());
   %result.delete();
}

----------------------------Example using "exclusive" mode from scripts:

// Exclusive 
function retriveData()
{
   %sql = "SELECT fieldvalue1, fieldvalue2 FROM myTable WHERE field1 = '1';";
   %recordSet = sqli.processSQL(%sql);
   while(%recordSet.nextRecord())
   {
      echo("Row data:" SPC %recordSet.fieldvalue1 SPC %recordSet.fieldvalue2);
   }
   %recordSet.delete(); // don't forget to delete when finished.
}

You can have a look at how the SQLiteInterface::processSQL console method is done, so you can copy that code and use it for your own c++-based data retrieving.
----------------------------
So far it looks stable, but you find a bug or have something to share - welcome!