Event Driven Database
by Tony Richards · 12/14/2006 (3:02 pm) · 35 comments
If you need a high performance database in your game engine, but you don't want database access slowing down game-play then this resource is for you.
Disclaimer
Please note that this is an Advanced Resource and if you're not a strong C++ programmer that has experience with multi-threading then proceed with caution (and consider getting outside help).
Notes:
I used TGE 1.5 as a starting point and I'm not sure when the multi-threading stuff was added to Torque. If it wasn't before TGE 1.5 then you're going to need to upgrade (which I highly recommend anyway :P)
Although I use MySQL++ here, you could use any database.
In this example I have references to a login server, which is not part of this resource, so this resource will not compile correctly without some adjustment, but you'll have to make modifications anyway to add new queries.
Edit: If you follow along this entire thread then you'll see how to get rid of the login server stuff since you probably don't need it and to add a generic SQL handler to get the record sets back to your Torque Script code.
I have #ifdef DEDICATED / #endif around the code because I only include MySQL code in my dedicated server. If you're using SQLite or some other embedded database on the client, you'll want to not do this. If you're using both MySQL and SQLite then you'll have a bit of refactoring to do. In my game I'll probably use both and quite likely I'll use two implementations like MySQLDatabaseInterface* gServerDatabase and SQLiteDatabaseInterface* gClientCache.
This resource takes a bit too much CPU time due to the fact that Torque doesn't have a built-in condition variable (yet). I'll add that as a separate resource eventually. If you use that resource, simply follow the source-code comments and uncomment every line that references m_pNotEmptyEvent. You could possibly use a semaphore too, but that's not as clean.
I did my best to match the rest of Torque's coding style, other than using m_ instead of m... Sorry, some old habits die hard. I didn't make a private copy constructor like I should have, but just remember that this is coded as a singleton and you shouldn't be creating new one's and/or copying the thing around without knowing what you're doing, ok? :P
Feel free to use this resource for personal and/or commercial use, but please give me credit (Tony "SgtFlame" Richards) where possible.
I placed my code in "core", but in retrospect, it might have been better to place it in "platform". I'm not making that change right now for other reasons, but my suggestion to you is you add the new files to the "engine/core" directory.
databaseEvent.h
Add this new file.
databaseInterface.h
databaseInterface.cc
platform/event.h
Modify the enum EventTypes:
platform/gameInterface.h
Add this somewhere at the top where the other #includes are:
In the Event Handlers section, add this:
platform/gameInterface.cc
In GameInterface::processEvent add this:
game/main.cc
Somewhere in initGame() you have to create gDatabase, and in shutdownGame() you have to delete it. Since the script needs to be executed first (because the database credentials are initialized in the script) you really need to do something like this in initGame().
At the top you'll need to include the database interface header. Remember, it's either core or platform, depending on the choice you made earlier.
Change this:
To this:
And in shutdownGame() add this somewhere at the top, but after calling the script onExit:
Adding more queries:
To add another query, add new events in databaseEvent.h like LoginRequest and LoginReply and make sure in the constructor of these events you correctly initialize type2 to a new DatabaseEventType (and don't forget to add the new enum to DatabaseEventType.)
Remember that these new events are copied using memcpy(), so be sure if you pass pointers around to evaluate the lifetime of the object pointers you're passing so as to not leave a reference to a deleted object hanging around. If you must, make sure processEvent correctly deletes any pointers to things in DatabaseRequest, and make sure your final DatabaseReply handler deletes anything in DatabaseReply.
In databaseInterface.cc in processEvent, add a new case statement to handle the new type2. As you add more queries you're going to want to extract these cases to call other methods instead of making them all in-line, otherwise the processEvent function will become huge and unwieldy.
Also add a new case statement in processDatabaseReplyEventType to handle the new type2.
So there you have it. This mechanism will allow you to do your slower database calls in a separate thread while allowing your main game thread to continue processing. Everything in GameInterface::processDatabaseReplyEventType is executed in the main game thread, and everything in DatabaseInterface::processEvent is executed in the database thread.
Let me know if I left something out. Happy Database Torquing!
Disclaimer
Please note that this is an Advanced Resource and if you're not a strong C++ programmer that has experience with multi-threading then proceed with caution (and consider getting outside help).
Notes:
I used TGE 1.5 as a starting point and I'm not sure when the multi-threading stuff was added to Torque. If it wasn't before TGE 1.5 then you're going to need to upgrade (which I highly recommend anyway :P)
Although I use MySQL++ here, you could use any database.
In this example I have references to a login server, which is not part of this resource, so this resource will not compile correctly without some adjustment, but you'll have to make modifications anyway to add new queries.
Edit: If you follow along this entire thread then you'll see how to get rid of the login server stuff since you probably don't need it and to add a generic SQL handler to get the record sets back to your Torque Script code.
I have #ifdef DEDICATED / #endif around the code because I only include MySQL code in my dedicated server. If you're using SQLite or some other embedded database on the client, you'll want to not do this. If you're using both MySQL and SQLite then you'll have a bit of refactoring to do. In my game I'll probably use both and quite likely I'll use two implementations like MySQLDatabaseInterface* gServerDatabase and SQLiteDatabaseInterface* gClientCache.
This resource takes a bit too much CPU time due to the fact that Torque doesn't have a built-in condition variable (yet). I'll add that as a separate resource eventually. If you use that resource, simply follow the source-code comments and uncomment every line that references m_pNotEmptyEvent. You could possibly use a semaphore too, but that's not as clean.
I did my best to match the rest of Torque's coding style, other than using m_ instead of m... Sorry, some old habits die hard. I didn't make a private copy constructor like I should have, but just remember that this is coded as a singleton and you shouldn't be creating new one's and/or copying the thing around without knowing what you're doing, ok? :P
Feel free to use this resource for personal and/or commercial use, but please give me credit (Tony "SgtFlame" Richards) where possible.
I placed my code in "core", but in retrospect, it might have been better to place it in "platform". I'm not making that change right now for other reasons, but my suggestion to you is you add the new files to the "engine/core" directory.
databaseEvent.h
Add this new file.
#ifndef _DATABASE_EVENT_H_
#define _DATABASE_EVENT_H_
// This is only used on dedicated servers
#ifdef DEDICATED
#include "platform/event.h"
enum DatabaseEventType
{
LoginQuery
};
struct DatabaseRequest
: public Event
{
DatabaseEventType type2;
DatabaseRequest() { type = DatabaseRequestEventType; }
};
struct DatabaseReply
: public Event
{
DatabaseEventType type2;
DatabaseReply() { type = DatabaseReplyEventType; }
};
#define MAX_USER_ID_SIZE 128
#define MAX_PASSWORD_SIZE 128
struct LoginRequest
: public DatabaseRequest
{
LoginRequest() { size = sizeof(LoginRequest); type2 = LoginQuery; }
char szUserId[MAX_USER_ID_SIZE];
char szPassword[MAX_PASSWORD_SIZE];
NetSocket connection;
};
struct LoginReply
: public DatabaseReply
{
LoginReply() { size = sizeof(LoginReply); type2 = LoginQuery; }
bool passwordMatched;
NetSocket connection;
};
#endif // DEDICATED
#endif // _DATABASE_EVENT_H_databaseInterface.h
//-----------------------------------------------------------------------------
// Fractured Universe
// Copyright (C) Tony Richards
//-----------------------------------------------------------------------------
#ifndef _DATABASE_INTERFACE_H_
#define _DATABASE_INTERFACE_H_
// This is only used on dedicated servers
#ifdef DEDICATED
#include "core/tVector.h"
#include "platform/event.h"
#include "core/databaseEvent.h"
#include "platform/platformThread.h"
#include <mysql++.h>
class DatabaseInterface
{
//-~-~-~-~-~-~
// Structors
public:
DatabaseInterface();
virtual ~DatabaseInterface();
//-~-~-~-~-~-~-~-~-~
// Implementation
public:
/// Place an event in Game's event queue.
virtual void postEvent(DatabaseRequest& event);
/// Static thread startup method.
/// @note Don't call this; the thread lifetime
/// is automatically handled.
static void processThread(void *udata);
//-~-~-~-~-~-~-~-~-~-~-~-~-~-~
// Additional implementation
protected:
/// Main process loop
void processLoop();
/// Process a single event
void processEvent(DatabaseRequest* event);
//-~-~-~-~-~-~-~-~-~
// Member Variables
private:
/// Events are stored here by any thread, for processing by the main thread.
Vector<DatabaseRequest*> m_EventQueue1, m_EventQueue2, *m_pEventQueue;
volatile bool m_ShuttingDown;
void * m_pEventQueueMutex;
//void * m_pNotEmptyEvent;
Thread* m_ProcessThread;
mysqlpp::Connection m_con;
};
/// Global database instance.
extern DatabaseInterface *gDatabase;
#endif // DEDICATED
#endif // _DATABASE_INTERFACE_H_databaseInterface.cc
//-----------------------------------------------------------------------------
// Fractured Universe
// Copyright (C) Tony Richards
//-----------------------------------------------------------------------------
// This module is only used on Dedicated Servers
#ifdef DEDICATED
#include "core/databaseInterface.h"
#include "platform/platform.h"
#include "platform/platformMutex.h"
#include "platform/gameInterface.h"
#include "console/console.h"
#include "game/mmo/login/loginServer.h"
DatabaseInterface *gDatabase = NULL;
DatabaseInterface::DatabaseInterface()
: m_pEventQueue(&m_EventQueue1)
, m_pEventQueueMutex(Mutex::createMutex())
//, m_pNotEmptyEvent(Condition::createCondition())
, m_ShuttingDown(false)
, m_con(false)
{
m_ProcessThread = new Thread((ThreadRunFunction)processThread, (S32) this, 1);
}
DatabaseInterface::~DatabaseInterface()
{
// TODO: Shutdown gracefully. For now everything just quieces correctly
// anyway, but it'd be nice to do this correctly.
}
void DatabaseInterface::postEvent(DatabaseRequest& event)
{
// Only one thread can post at a time.
Mutex::lockMutex(m_pEventQueueMutex);
// Create a deep copy of event, and save a pointer to the copy in a vector.
DatabaseRequest* copy = (DatabaseRequest*)dMalloc(event.size);
dMemcpy(copy, &event, event.size);
m_pEventQueue->push_back(copy);
// TODO: Assert the condition variable
// Condition::assert(m_pNotEmptyEvent);
Mutex::unlockMutex(m_pEventQueueMutex);
}
void DatabaseInterface::processEvent(DatabaseRequest* event)
{
switch(event->type2)
{
case LoginQuery:
{
LoginRequest* pRequest = (LoginRequest*)event;
// Create default reply
LoginReply reply;
reply.passwordMatched = false;
reply.connection = pRequest->connection;
mysqlpp::Query query = m_con.query();
query << "select * from Player where UserName = "
"\'" << pRequest->szUserId << "\'";
mysqlpp::ResUse res = query.use();
if (res)
{
try
{
mysqlpp::Row row;
while (row = res.fetch_row())
{
if (dStricmp(row["EncryptedPassword"].c_str(), pRequest->szPassword) == 0)
{
// Password matched
reply.passwordMatched = true;
Con::printf("%s matched", row["UserName"].c_str());
break;
}
else
{
Con::printf("%s didn't match", row["UserName"].c_str());
}
}
}
catch(...)
{
}
}
Game->postEvent(reply);
}
break;
}
}
void DatabaseInterface::processLoop()
{
if (!m_con.connect(
Con::getVariable("Database::database"),
Con::getVariable("Database::host"),
Con::getVariable("Database::user"),
Con::getVariable("Database::password")))
{
Con::printf("Error connecting to database!");
// TODO: Loop a few times before giving up?
return;
}
while(!m_ShuttingDown)
{
Mutex::lockMutex(m_pEventQueueMutex);
// swap event queue pointers
Vector<DatabaseRequest*> &fullEventQueue = *m_pEventQueue;
if(m_pEventQueue == &m_EventQueue1)
m_pEventQueue = &m_EventQueue2;
else
m_pEventQueue = &m_EventQueue1;
// TODO: signal that the queue is now empty
// Condition::retract(m_pNotEmptyEvent);
Mutex::unlockMutex(m_pEventQueueMutex);
// Keep track of the original size
const int size = fullEventQueue.size();
// Walk the event queue in fifo order, processing the events, then clear the queue.
for(int i=0; i < size; i++)
{
processEvent(fullEventQueue[i]);
dFree(fullEventQueue[i]);
}
fullEventQueue.clear();
if (size == 0)
{
// HACK: This is a bad hack. Instead of doing this polling, use a true thread-safe queue.
// TODO: Check to see if the next queue is empty, and if it is, wait for a condition variable
// and don't do the Platform::sleep(0);
// Condition::require(m_pNotEmptyEvent);
Platform::sleep(0);
}
}
}
void DatabaseInterface::processThread( void *udata )
{
DatabaseInterface* pThis = (DatabaseInterface *)udata;
pThis->processLoop();
}
void GameInterface::processDatabaseReplyEventType(DatabaseReply* event)
{
switch(event->type2)
{
case LoginQuery:
// Note that this won't compile.
g_pLoginServer->processLoginReply((LoginReply*)event);
break;
default:
Con::printf("Got an illegal database reply type: %i", event->type2);
break;
}
}
#endifplatform/event.h
Modify the enum EventTypes:
/// Available event types.
enum EventTypes
{
InputEventType,
MouseMoveEventType,
PacketReceiveEventType,
TimeEventType,
QuitEventType,
ConsoleEventType,
ConnectedReceiveEventType,
ConnectedAcceptEventType,
ConnectedNotifyEventType,
DatabaseRequestEventType,
DatabaseReplyEventType
};platform/gameInterface.h
Add this somewhere at the top where the other #includes are:
#ifdef DEDICATED #include "core/databaseEvent.h" #endif
In the Event Handlers section, add this:
virtual void processConnectedNotifyEvent(ConnectedNotifyEvent *event); #ifdef DEDICATED virtual void processDatabaseReplyEventType(DatabaseReply* event); #endif /// @}
platform/gameInterface.cc
In GameInterface::processEvent add this:
void GameInterface::processEvent(Event *event)
{
...
case ConnectedNotifyEventType:
processConnectedNotifyEvent( (ConnectedNotifyEvent *) event );
break;
#ifdef DEDICATED
case DatabaseReplyEventType:
processDatabaseReplyEventType( (DatabaseReply*) event );
break;
#endif
}
...
}game/main.cc
Somewhere in initGame() you have to create gDatabase, and in shutdownGame() you have to delete it. Since the script needs to be executed first (because the database credentials are initialized in the script) you really need to do something like this in initGame().
At the top you'll need to include the database interface header. Remember, it's either core or platform, depending on the choice you made earlier.
#include "platform/databaseInterface.h"
Change this:
// run the entry script and return. return runEntryScript(argc, argv); }
To this:
// run the entry script and return. bool rc = runEntryScript(argc, argv); #ifdef DEDICATED // Create the database object gDatabase = new DatabaseInterface; #endif return rc; }
And in shutdownGame() add this somewhere at the top, but after calling the script onExit:
/// Shutdown the game and delete core objects
void shutdownGame()
{
//exec the script onExit() function
Con::executef(1, "onExit");
#ifdef DEDICATED
delete gDatabase;
#endif
BadWordFilter::destroy();
ParticleEngine::destroy();Adding more queries:
To add another query, add new events in databaseEvent.h like LoginRequest and LoginReply and make sure in the constructor of these events you correctly initialize type2 to a new DatabaseEventType (and don't forget to add the new enum to DatabaseEventType.)
Remember that these new events are copied using memcpy(), so be sure if you pass pointers around to evaluate the lifetime of the object pointers you're passing so as to not leave a reference to a deleted object hanging around. If you must, make sure processEvent correctly deletes any pointers to things in DatabaseRequest, and make sure your final DatabaseReply handler deletes anything in DatabaseReply.
In databaseInterface.cc in processEvent, add a new case statement to handle the new type2. As you add more queries you're going to want to extract these cases to call other methods instead of making them all in-line, otherwise the processEvent function will become huge and unwieldy.
Also add a new case statement in processDatabaseReplyEventType to handle the new type2.
So there you have it. This mechanism will allow you to do your slower database calls in a separate thread while allowing your main game thread to continue processing. Everything in GameInterface::processDatabaseReplyEventType is executed in the main game thread, and everything in DatabaseInterface::processEvent is executed in the database thread.
Let me know if I left something out. Happy Database Torquing!
About the author
I am the founder of IndieZen.org, a website dedicated to the Indie 2.0 Revolution where a number of Indie game development studios and individuals collaborate and share a suite of custom built open source game development tools and middleware.
#2
Regards,
Dreamer
12/07/2006 (8:05 am)
This would be a great addition to the MMOKit and Titas especially if we refactored it to use ODBC instead of MySQL (MySQL may not be everyones DB of choice). Mind if this makes it's way into the MMOKit?Regards,
Dreamer
#3
@Dreamer - I don't mind at all.
12/07/2006 (8:18 am)
@bank - Your welcome. Hope it works for you.@Dreamer - I don't mind at all.
#4
12/15/2006 (7:41 am)
Sweet and delectable! I can't wait for the day to come that I can use database code in the game engine for all the little inventory and item creation transactions that I want to, without being afraid of speed and concurrency problems.
#5
12/20/2006 (10:43 am)
Tony, I didn't want to hijack Vince's Plan thread www.garagegames.com/index.php?sec=mg&mod=resource&page=view&qid=11922 and I wanted to try out this resource soon, so I am putting in my vote here for your semaphore version ;)
#6
12/20/2006 (12:18 pm)
Hehe, no problem... I'm at work right now, but when I get home later this evening I'll post it.
#7
databaseInterface.h
Uncomment this line:
databaseInterface.cc
Change the includes at the top:
Change the constructor to this:
Change DatabaseInterface::postEvent:
In DatabaseInterface::processLoop, make this change:
This will make the application take significantly less processor time and the database calls will be more responsive.
Happy Database Torquing with Semaphores :P
12/20/2006 (4:22 pm)
Update for using a Semaphore instead of Sleep:databaseInterface.h
Uncomment this line:
void * m_pNotEmptyEvent;
databaseInterface.cc
Change the includes at the top:
#include "platform/platform.h" #include "platform/platformMutex.h" #include "platform/platformSemaphore.h" #include "platform/gameInterface.h"
Change the constructor to this:
DatabaseInterface::DatabaseInterface()
: m_pEventQueue(&m_EventQueue1)
, m_pEventQueueMutex(Mutex::createMutex())
, m_pNotEmptyEvent(Semaphore::createSemaphore(0))
, m_ShuttingDown(false)
, m_con(false)
{
m_ProcessThread = new Thread((ThreadRunFunction)processThread, (S32) this, 1);
}Change DatabaseInterface::postEvent:
void DatabaseInterface::postEvent(DatabaseRequest& event)
{
// Only one thread can post at a time.
Mutex::lockMutex(m_pEventQueueMutex);
// Create a deep copy of event, and save a pointer to the copy in a vector.
DatabaseRequest* copy = (DatabaseRequest*)dMalloc(event.size);
dMemcpy(copy, &event, event.size);
m_pEventQueue->push_back(copy);
// Assert the condition variable
Semaphore::releaseSemaphore(m_pNotEmptyEvent);
Mutex::unlockMutex(m_pEventQueueMutex);
}In DatabaseInterface::processLoop, make this change:
void DatabaseInterface::processLoop()
{
if (!m_con.connect(
Con::getVariable("Database::database"),
Con::getVariable("Database::host"),
Con::getVariable("Database::user"),
Con::getVariable("Database::password")))
{
Con::printf("Error connecting to database!");
// TODO: Loop a few times before giving up?
return;
}
while(!m_ShuttingDown)
{
// Wait for the event to be signalled
Semaphore::acquireSemaphore(m_pNotEmptyEvent, true);
Mutex::lockMutex(m_pEventQueueMutex);
// swap event queue pointers
Vector<DatabaseRequest*> &fullEventQueue = *m_pEventQueue;
if(m_pEventQueue == &m_EventQueue1)
m_pEventQueue = &m_EventQueue2;
else
m_pEventQueue = &m_EventQueue1;
Mutex::unlockMutex(m_pEventQueueMutex);
// Keep track of the original size
const int size = fullEventQueue.size();
// Walk the event queue in fifo order, processing the events, then clear the queue.
for(int i=0; i < size; i++)
{
processEvent(fullEventQueue[i]);
dFree(fullEventQueue[i]);
}
fullEventQueue.clear();
}
}This will make the application take significantly less processor time and the database calls will be more responsive.
Happy Database Torquing with Semaphores :P
#8
Note that this uses STL and it's probably best not to use the game-engine memory allocator and instead use the standard one. If you don't want to do that then feel free to replace the STL stuff with your own code. :D
New file console/recordSet.h
New file console/recordSet.cpp (or .cc your choice)
Now, in core/databaseEvent.h add a new database event type GenericQuery in the DatabaseEventType enum.
Then in that same file add this:
Now, I deviated a bit from my original code and in databaseInterface.cc I changed DatabaseInterface::processEvent to be more like this:
Also, add a new function in that file
And make sure you add the new methods to the header databaseInterface.h
Then for a test script
And I think that's pretty much it.
Feel free to e-mail me if you have any problems with it... I don't really provide a warranty or anything, but I'm generally happy to help if I can.
03/09/2007 (12:09 pm)
Ok, so if you're interested in a more generic / scriptable approach, take a look at this.Note that this uses STL and it's probably best not to use the game-engine memory allocator and instead use the standard one. If you don't want to do that then feel free to replace the STL stuff with your own code. :D
New file console/recordSet.h
//-----------------------------------------------------------------------------
// Copyright (C) 2007 Tony "Sgt Flame" Richards
//
// Feel free to use this in your TGE / TGEA games, but if you use it
// then please mention my name in your "credits" section of your game.
// If you have any questions or problems, feel free to contact me:
//
// E-mail: SgtFlame@GamesBySarge.com
//
//-----------------------------------------------------------------------------
#ifdef DEDICATED
#ifndef _RECORDSET_H_
#define _RECORDSET_H_
#ifndef _SIMBASE_H_
#include "console/simBase.h"
#endif
#include <vector>
#include <map>
#include <string>
#include <list>
class RecordSet
: public SimObject
{
//-~-~-~-~-~
// Typedefs
private:
typedef SimObject Parent;
//-~-~-~-~-~-~
// 'Structors
public:
RecordSet();
~RecordSet();
//-~-~-~-~-~-~-~-~-~-~-~-~-~
// SimObject implementation
public:
virtual bool onAdd();
virtual void onRemove();
static void initPersistFields();
//-~-~-~-~-~-~-~-~-~-~-~-~-~
// Getter / Setter methods
public:
void setQueryId(S32 queryId);
S32 getQueryId();
void setFieldCount(S32 fieldCount);
S32 getFieldCount();
void setFieldName(S32 index, const char* pFieldName);
const char* getFieldName(S32 index);
void setFieldValue(S32 index, const char* pValue);
const char* getFieldValue(S32 index);
const char* getFieldValue(const char* pFieldName);
//-~-~-~-~-~-~-~-~-~
// Record traversal
public:
void addRecord();
bool nextRecord();
//-~-~-~-~-~-~-~-~-~
// Member variables
private:
typedef std::vector<std::string> record_type;
typedef std::list<record_type> record_set_type;
S32 m_queryId;
S32 m_fieldCount;
/// Field names kept in order
std::vector<std::string> m_fieldNames;
/// Maps the name of the field to the index
/// This is slightly inefficient to store the
/// full copy of the string instead of a reference
/// since it's already being stored in m_fieldNames,
/// but I'm lazy and it's really not an important
/// optimization unless you have a ton of recordsets
/// hanging around.
std::map<std::string, S32> m_fieldNameMap;
/// Record set
record_set_type m_recordSet;
bool m_firstRecord;
record_set_type::iterator m_currentRecord;
//-~-~-~-~-~-~-~-~-~-~-~
// Console Object Stuff
public:
DECLARE_CONOBJECT(RecordSet);
}; // class RecordSet
#endif // _RECORDSET_H_
#endif // DEDICATEDNew file console/recordSet.cpp (or .cc your choice)
//-----------------------------------------------------------------------------
// Copyright (C) 2007 Tony "Sgt Flame" Richards
//
// Feel free to use this in your TGE / TGEA games, but if you use it
// then please mention my name in your "credits" section of your game.
// If you have any questions or problems, feel free to contact me:
//
// E-mail: SgtFlame@GamesBySarge.com
//
//-----------------------------------------------------------------------------
#ifdef DEDICATED
#include "console/recordSet.h"
#include "console/simBase.h"
#include "console/consoleInternal.h"
#include "core/databaseInterface.h"
#include <algorithm>
#include <cctype>
IMPLEMENT_CONOBJECT(RecordSet);
RecordSet::RecordSet()
: m_queryId(0)
, m_fieldCount(0)
, m_firstRecord(true)
{
}
RecordSet::~RecordSet()
{
}
bool RecordSet::onAdd()
{
if (!Parent::onAdd())
return false;
const char *name = getName();
if(name && name[0] && getClassRep())
{
Namespace *parent = getClassRep()->getNameSpace();
Con::linkNamespaces(parent->mName, name);
mNameSpace = Con::lookupNamespace(name);
}
return true;
}
void RecordSet::onRemove()
{
Parent::onRemove();
}
void RecordSet::initPersistFields()
{
Parent::initPersistFields();
}
void RecordSet::setQueryId(S32 queryId)
{
m_queryId = queryId;
}
S32 RecordSet::getQueryId()
{
return m_queryId;
}
void RecordSet::setFieldCount(S32 fieldCount)
{
// We don't really need to keep track of this, but we do use it
// as a hint for the vector size.
m_fieldCount = fieldCount;
m_fieldNames.resize(fieldCount);
}
S32 RecordSet::getFieldCount()
{
return m_fieldCount;
}
void RecordSet::setFieldName(S32 index, const char* pFieldName)
{
std::string tmpFieldName(pFieldName);
m_fieldNames[index] = tmpFieldName;
m_fieldNameMap[tmpFieldName] = index;
}
const char* RecordSet::getFieldName(S32 index)
{
return m_fieldNames[index].c_str();
}
void RecordSet::setFieldValue(S32 index, const char *pValue)
{
m_recordSet.back()[index] = pValue;
}
const char* RecordSet::getFieldValue(S32 index)
{
if (m_firstRecord)
{
Con::errorf("Call RecordSet::nextRecord() before calling getFieldValue");
return "";
}
return (*m_currentRecord)[index].c_str();
}
const char* RecordSet::getFieldValue(const char* pFieldName)
{
std::string tmpFieldName(pFieldName);
std::transform(tmpFieldName.begin(), tmpFieldName.end(), tmpFieldName.begin(),
(int(*)(int)) std::tolower);
// TODO: Use find() to see if the field name actually
// exists and display an error if it doesn't exist.
// For now, this will return the value of the first field
// if the specified field name doesn't exist.
return getFieldValue(m_fieldNameMap[tmpFieldName]);
}
void RecordSet::addRecord()
{
record_type tmpRecord;
m_recordSet.push_back(tmpRecord);
m_recordSet.back().resize(m_fieldCount);
}
bool RecordSet::nextRecord()
{
if (m_firstRecord)
{
m_firstRecord = false;
m_currentRecord = m_recordSet.begin();
}
else
{
++m_currentRecord;
}
return m_currentRecord != m_recordSet.end();
}
ConsoleMethod(RecordSet, getQueryId, S32, 2, 2, "getQueryId()")
{
return object->getQueryId();
}
ConsoleMethod(RecordSet, nextRecord, bool, 2, 2, "nextRecord()")
{
return object->nextRecord();
}
ConsoleMethod(RecordSet, getFieldCount, S32, 2, 2, "getFieldCount()")
{
return object->getFieldCount();
}
ConsoleMethod(RecordSet, getFieldName, const char*, 3, 3, "getFieldName(%index)")
{
return object->getFieldName(dAtoi(argv[2]));
}
ConsoleMethod(RecordSet, getFieldValue, const char*, 3, 3, "getFieldValue(%index or %fieldName)")
{
if (dIsalpha(argv[2][0]))
{
return object->getFieldValue(argv[2]);
}
else
{
return object->getFieldValue(dAtoi(argv[2]));
}
}
static int gStatementHandle = 0;
ConsoleFunction(query, S32, 3, 3, "query(%sqlStatement, %callback)")
{
GenericQueryRequest request;
request.pRecordSet = new RecordSet;
request.pRecordSet->registerObject();
request.pRecordSet->setQueryId(gStatementHandle++);
request.pSQLString = dStrdup(argv[1]);
request.pQueryCallback = dStrdup(argv[2]);
gDatabase->postEvent(request);
return request.pRecordSet->getQueryId();
}
#endif // DEDICATEDNow, in core/databaseEvent.h add a new database event type GenericQuery in the DatabaseEventType enum.
enum DatabaseEventType
{
... possibly more types ...
GenericQuery
};Then in that same file add this:
class RecordSet;
struct GenericQueryRequest
: public DatabaseRequest
{
GenericQueryRequest() { size = sizeof(GenericQueryRequest); type2 = GenericQuery; }
const char* pSQLString;
const char* pQueryCallback;
RecordSet* pRecordSet;
};
struct GenericQueryReply
: public DatabaseReply
{
GenericQueryReply() { size = sizeof(GenericQueryReply); type2 = GenericQuery; }
const char* pQueryCallback;
RecordSet* pRecordSet;
};Now, I deviated a bit from my original code and in databaseInterface.cc I changed DatabaseInterface::processEvent to be more like this:
void DatabaseInterface::processEvent(DatabaseRequest* event)
{
try
{
// First, always ping to attempt to reconnect if we've dropped
if (!m_con.connected())
{
Con::printf("Lost database connection... reconnecting");
if (!m_con.connect(
Con::getVariable("Database::database"),
Con::getVariable("Database::host"),
Con::getVariable("Database::user"),
Con::getVariable("Database::password")))
{
Con::printf("Unable to reconnect with connect()");
}
}
switch(event->type2)
{
case GenericQuery:
Con::printf("processGenericQuery");
processGenericQuery((GenericQueryRequest*)event);
break;
}
}
catch(...)
{
Con::printf("Database thread threw an unhandled exception.");
}
}Also, add a new function in that file
void DatabaseInterface::processGenericQuery(GenericQueryRequest* pRequest)
{
// Create the reply message and initialize it from the request
GenericQueryReply reply;
reply.pQueryCallback = pRequest->pQueryCallback;
reply.pRecordSet = pRequest->pRecordSet;
// Create the query
mysqlpp::Query query = m_con.query();
query << pRequest->pSQLString;
// Free up the memory for this query
delete pRequest->pSQLString;
// Execute the query
mysqlpp::ResUse res = query.use();
if (res)
{
try
{
mysqlpp::Row row;
// Set the field count
reply.pRecordSet->setFieldCount(res.columns());
// Loop through all of the columns and set them in the recordset
for(int x = 0; x < res.columns(); x++)
{
reply.pRecordSet->setFieldName(x, res.field_name(x).c_str());
}
// Loop through all of the rows and set the record field values
while (row = res.fetch_row())
{
reply.pRecordSet->addRecord();
for(int x = 0; x < res.columns(); x++)
{
reply.pRecordSet->setFieldValue(x, row.at(x).c_str());
}
}
}
catch(...)
{
// Hard error
Con::errorf("DatabaseInterface::processEvent exception!");
}
}
else
{
// Soft error
Con::errorf("DatabaseInterface::processEvent error: %s", query.error().c_str());
}
Game->postEvent(reply);
}And make sure you add the new methods to the header databaseInterface.h
//-~-~-~-~-~-~-~-~-~-~-~-~-~-~ // Additional implementation protected: ... /// Process a generic query void processGenericQuery(GenericQueryRequest* pRequest);
Then for a test script
function testDatabase()
{
// Save the query Id for later use if you need
%queryId = query("Select * from Toon", testDatabaseCallback);
}
function testDatabaseCallback(%recordSet)
{
// Compare the queryId with the one
echo(%recordSet.getQueryId());
while(%recordSet.nextRecord())
{
// Dump all of the fields
for(%x = 0; %x < %recordSet.getFieldCount(); %x++)
{
echo(%recordSet.getFieldName(%x) SPC "=" SPC %recordSet.getFieldValue(%x));
}
echo(%recordSet.getFieldValue("FirstName") SPC %recordSet.getFieldValue("LastName"));
}
// Make sure to clean this up.
%recordSet.delete();
}And I think that's pretty much it.
Feel free to e-mail me if you have any problems with it... I don't really provide a warranty or anything, but I'm generally happy to help if I can.
#9
03/09/2007 (12:13 pm)
Oh, oops, one more thing... in databaseInterface.cc change GameInterface::processDatabaseReplyEventType to be something like this:void GameInterface::processDatabaseReplyEventType(DatabaseReply* event)
{
switch(event->type2)
{
case GenericQuery:
{
GenericQueryReply* const pReply = (GenericQueryReply*)event;
Con::executef(3, pReply->pQueryCallback, pReply->pRecordSet->getIdString());
// Delete the callback string, but don't delete the recordset
// because it'll be handled by the script engine.
delete pReply->pQueryCallback;
}
break;
default:
Con::printf("Got an illegal database reply type: %i", event->type2);
break;
}
}
#10
03/09/2007 (4:50 pm)
Tony, this has got to be one of the most useful resources ever made! Thank you so much.
#11
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2365: 'operator new' : redefinition; previous definition was 'function'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2078: too many initializers
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2440: 'initializing' : cannot convert from 'int' to 'void *'
10> Conversion from integral type to pointer type requires reinterpret_cast, C-style cast or function-style cast
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2143: syntax error : missing ';' before '['
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C3409: empty attribute block is not allowed
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2226: syntax error : unexpected type 'size_t'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : warning C4229: anachronism used : modifiers on data are ignored
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2365: 'operator new' : redefinition; previous definition was 'function'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2078: too many initializers
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2440: 'initializing' : cannot convert from 'int' to 'void *'
10> Conversion from integral type to pointer type requires reinterpret_cast, C-style cast or function-style cast
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2143: syntax error : missing ';' before '('
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1126) : error C2226: syntax error : unexpected type 'size_t'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1130) : error C2059: syntax error : ')'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : warning C4229: anachronism used : modifiers on data are ignored
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2365: 'operator new' : redefinition; previous definition was 'function'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2078: too many initializers
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2440: 'initializing' : cannot convert from 'int' to 'void *'
10> Conversion from integral type to pointer type requires reinterpret_cast, C-style cast or function-style cast
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2143: syntax error : missing ';' before '['
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C3409: empty attribute block is not allowed
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1133) : error C2226: syntax error : unexpected type 'size_t'
When trying to compile the recordset.cc. Any thoughts?
03/09/2007 (5:53 pm)
Yep, a compile question! This automatically makes me unqualified from trying this resource, but...10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2365: 'operator new' : redefinition; previous definition was 'function'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2078: too many initializers
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2440: 'initializing' : cannot convert from 'int' to 'void *'
10> Conversion from integral type to pointer type requires reinterpret_cast, C-style cast or function-style cast
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2143: syntax error : missing ';' before '['
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C3409: empty attribute block is not allowed
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1122) : error C2226: syntax error : unexpected type 'size_t'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : warning C4229: anachronism used : modifiers on data are ignored
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2365: 'operator new' : redefinition; previous definition was 'function'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2078: too many initializers
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2440: 'initializing' : cannot convert from 'int' to 'void *'
10> Conversion from integral type to pointer type requires reinterpret_cast, C-style cast or function-style cast
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1125) : error C2143: syntax error : missing ';' before '('
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1126) : error C2226: syntax error : unexpected type 'size_t'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1130) : error C2059: syntax error : ')'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : warning C4229: anachronism used : modifiers on data are ignored
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2365: 'operator new' : redefinition; previous definition was 'function'
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2078: too many initializers
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2440: 'initializing' : cannot convert from 'int' to 'void *'
10> Conversion from integral type to pointer type requires reinterpret_cast, C-style cast or function-style cast
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C2143: syntax error : missing ';' before '['
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1132) : error C3409: empty attribute block is not allowed
10>C:\Program Files\Microsoft Visual Studio 8\VC\include\crtdbg.h(1133) : error C2226: syntax error : unexpected type 'size_t'
When trying to compile the recordset.cc. Any thoughts?
#12
In platform.h:
That should solve the compile errors you're getting.
03/09/2007 (6:31 pm)
If you're using STL you have to disable the TGE memory manager and use the standard library. In platform.h:
#ifndef _PLATFORMASSERT_H_ # include "platform/platformAssert.h" #endif #define TORQUE_DISABLE_MEMORY_MANAGER #include <new>
That should solve the compile errors you're getting.
#13
05/09/2007 (2:21 pm)
I'm still new to this, what would the loginServer.h look like?
#14
If that's the case whats the impact of turning off Torques memory manager and using the standard version instead?
Thanks, Andy
05/26/2007 (11:53 am)
Great resource and something that will be really useful to us, there is something I'm not 100% sure of though, and wondered if it's something you know the answer to - as far as I am aware to use this resource with the mysql++ api means you have to use STL? is that right? or is there another way around?If that's the case whats the impact of turning off Torques memory manager and using the standard version instead?
Thanks, Andy
#15
CComPtr cpVoice;
Is the "" syntax dependent on the Standard Template Library?
Thanks,
Mark
10/08/2007 (8:27 am)
Looks like I need to learn more about STL (Standard Template Libarary?). I can't quite see where your example uses it. I'm trying to work with another microsoft library, the Speech API (SAPI 5.1), which may also require STL. Here's a sample of the declaration of a handle for the TTS:CComPtr
Is the "
Thanks,
Mark
#16
@Mark - CComPtr is ATL, not STL. You have to be very careful using COM / ATL in your games. If the COM Object you are using is Apartment threaded then you must be sure the object is created, destroyed and utilized in that thread. Apartment threaded or otherwise, I believe you have to add some more code to the main window message pump, plus call CoInitialize(Ex) during startup. I suggest you play around with the speech API outside of Torque first, then once you're comfortable with it then you can begin integrating it into the game engine. Just remember that going down this path is going to make your game single platform (i.e. Windows only).
10/08/2007 (2:52 pm)
@Andy - I've not experienced any undue performance issues turning off the Torque memory manager. Modern STL implementations (STLPort, Plauger, GNU) utilize the OS in ways that are efficient enough. That was not the case when Dynamix originally implemented their own memory manager. Just be careful with GNU's implementation. They broke a few rules that don't conform to STL performance guarantees (e.g stl::list<>::size() is not constant time but the specification indicates that it should be.)@Mark - CComPtr is ATL, not STL. You have to be very careful using COM / ATL in your games. If the COM Object you are using is Apartment threaded then you must be sure the object is created, destroyed and utilized in that thread. Apartment threaded or otherwise, I believe you have to add some more code to the main window message pump, plus call CoInitialize(Ex) during startup. I suggest you play around with the speech API outside of Torque first, then once you're comfortable with it then you can begin integrating it into the game engine. Just remember that going down this path is going to make your game single platform (i.e. Windows only).
#17
in the code, but I get the message "mysql++.h: No such file or directory" when I try to compile. Do I have to link to the library somewhere?
12/05/2007 (8:00 pm)
I seem to be having some trouble getting set up with the MySQL++ library on my linux box. I've installed the latest version of the library itself (do I need the devel library?) and included
#18
If you need more help feel free to e-mail me (view my profile, humans can figure out my e-mail :-D). I'd be more than happy to help.
12/05/2007 (8:50 pm)
You definately need the devel library... not just MySQL but also MySQL++. You might also need to modify your makefile to include the mysql++ and mysql headers.If you need more help feel free to e-mail me (view my profile, humans can figure out my e-mail :-D). I'd be more than happy to help.
#19
Trying to port to TGEA 1.8.
Any help?
12/16/2008 (3:01 pm)
Ack, they changed the event code and some other things in TGEA.Trying to port to TGEA 1.8.
Any help?
#20
12/16/2008 (5:57 pm)
I'll take a look at it when 1.8 goes gold. I don't know what else is going to change that might possibly break it again. 
Associate Fyodor -bank- Osokin
Dedicated Logic
(hope tonight i'll have time to play with it...)