Pooled PostgreSQL database

Download Code File

Foreword:
Thanks to Tony "SgtFlame" Richards and the Event Driven Database resource, I modeled some of the threading code around that resource. Kindly let me know if you spot any bugs.

What is PostgreSQL?
PostgreSQL is a real RDBMS SQL server that "is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data". Postgres has been quoted as "not only as fast or faster than MySQL, but almost as fast as Oracle" which is the software many organizations, including banks, use for storing data.

More information about PostgresSQL can be found here www.postgresql.org/about/

What about SQLite?
Comparing SQLite to PostgresSQL is not really a fair comparison as SQLite is more of a local data storage system that just happens to use SQL as an interface for storing and retrieving data. It is faster simply due to the lack of the TCP/IP stack overhead in RDBMS servers, it cannot scale or accept concurrent requests for data like true RDBMS can. Their own website explains this much better www.sqlite.org/cvstrac/wiki?p=WhenToUseSqlite


Installation:
- Install PostgreSQL: www.postgresql.org/
- Extract the files in pgSQL.zip to your engine folder.
- Ensure the PostgreSQL library and include folder are in the build environment.
- Add the functions in the code block below to core/llist.h.
- Add libpq.lib to the linker dependencies.
- Copy the PostgreSQL DLL's to the game folder (libpq.dll, ssleay32.dll, libintl-2.dll, krb5_32.dll, libiconv-2.dll).
- Build!
- Add the initializing code somewhere useful. I use the createServer TorqueScript function.
- After the first run, the connection information variables are placed in the server's pref.cs files, add your credentials here.


core/llist.h optimizations

T *pop_first(void)
   {
	   if (!first_entry)
		   return NULL;

	   T* e = first_entry->Data;
	   LListNode<T> *entryNode = first_entry;
      
       first_entry = first_entry->Next;
 
      if( entryNode->Next == NULL )
      {
         last_entry = entryNode->Prev;
      }
      else
      {
         entryNode->Next->Prev = entryNode->Prev;
      }

      delete entryNode;

      --cnt;
	   return e;
   }

   T *pop_last(void)
   {
	   if (!last_entry)
		   return NULL;

	   T* e = last_entry->Data;
	   LListNode<T> *entryNode = last_entry;
      
      if( entryNode->Prev == NULL )
      {
         first_entry = entryNode->Next;
      }
      else
      {
         entryNode->Prev->Next = entryNode->Next;
      }
      last_entry = entryNode->Prev;

      delete entryNode;

      --cnt;
	   return e;
   }
   void push_back(T &entry)
   {

	  T *newEntry = new T;
      *newEntry = entry;

      LListNode<T> *prevNode = NULL;
      LListNode<T> *newNode = new LListNode<T>;

      newNode->Data = newEntry;

      prevNode = last_entry;
      last_entry = newNode;
 
      newNode->Next = NULL;
      newNode->Prev = prevNode;

	  if (!first_entry)
		  first_entry = last_entry;

      ++cnt;
   }

   //---------------------------------------------------------------------------------------
   // Unlink node from list (without destroying it)
   //---------------------------------------------------------------------------------------
   void unlink(LListNode<T> *entryNode)
   {
      if( !entryNode ) return;

      if( entryNode->Prev == NULL )
      {
         first_entry = entryNode->Next;
      }
      else
      {
         entryNode->Prev->Next = entryNode->Next;
      }

      if( entryNode->Next == NULL )
      {
         last_entry = entryNode->Prev;
      }
      else
      {
         entryNode->Next->Prev = entryNode->Prev;
      }

      delete entryNode;

      --cnt;
   }   

   void free(LListNode<T> *entry)
   {
      unlink(entry);
      delete entry;
   }

Example initializing code.

function createServer(%serverType, %mission)
{
   if (%mission $= "") {
      error("createServer: mission name unspecified");
      return;
   }
   
   // Make sure our mission name is relative so that it can send
   // across the network correctly
   %mission = makeRelativePath(%mission, getWorkingDirectory());

[b]   PGSQLPool::createPool();[/b]
   destroyServer();
   ...

Now go create your database enabled app! :D


Pool API

%db = PGSQLPool::getPool();
Obtain the handle to the query pool.

%db.Exec([%object], %callbackName, "SQL Query;", ...)
WARNING!! NEVER EVER CONCATENATE VARIABLES INTO THE SQL QUERY!! USE THE PROVIDED INTERFACE. IGNORING THIS WARNING WILL RESULT IN SQL INJECTION VULNERABLITIES IN YOUR GAME.

When building your query, you can omit the space where you would insert variables in favor of one of the replacement tokens. These are;

%s Normal Strings
%b Binary Strings
%x Everything else, including Real's and Integers (as a string)

Then add the actual variables to the variable argument list. %s and %b will automatically apply quotes.
E.g.

Vulnerable:

%db.Exec(%client, "onPlayerListCallback", "SELECT * FROM players WHERE uid = " @ %client.uid @ " AND name = \"" @ %client.name @ "\"";");

Safe:

%db.Exec(%client, "onPlayerListCallback", "SELECT * FROM players WHERE uid = %x AND name = %s;", %client.uid, %client.name);

Even with this tool you should still validate client input as good security practice.

Result API

%num = %db.getValue(%resultset, %row, %fieldnum)
Get the value of a row and field in the result set.

%num = %db.getNamedValue(%resultset, %row, %fieldname)
Get the value of a row and field in the result set by field name.



%num = %db.getNumRows(%resultset)
Get the number of rows in a result set.

%num = %db.getNumFields(%resultset)
Get the number of fields in a result set.

%name = %db.getFieldName(%resultset, %fieldnum)
Get the name of a field in a result set.

%num = %db.getFieldNum(%resultset, %fieldname)
Get the number of a field in a result set by name.


Use and abuse
Here are some example usages;

Object in TorqueScript

function GameConnection::sendAvailablePlayers( %client )
{
	%db = PGSQLPool::getPool();
	%db.Exec(%client, "onPlayerListCallback", "SELECT * FROM players WHERE uid = %x;", %client.uid);
}

function GameConnection::onPlayerListCallback( %client, %r )
{
	commandToClient(%client, 'availablePlayers');
	%db = PGSQLPool::getPool();
	%num = %db.getNumRows(%r);
	for (%i = 0; %i < %num; %i++)
	{
		%pid = %db.getNamedValue(%r,%i,"pid");
		%name = %db.getNamedValue(%r,%i,"name");
		%class = %db.getNamedValue(%r,%i,"class");
		%level = %db.getNamedValue(%r,%i,"level");
		if (%pid)
			commandToClient(%client, 'sendPlayer', %pid, %name, %class, %level);
	}
}

Function in TorqueScript

function onRegister( %user, %pass, %email )
{
	%db = PGSQLPool::getPool();
	%db.Exec("onRegisterCallback", "SELECT addUser(%s, %s, %s);", %user, %pass, %email);
}

function onRegisterCallback( %r )
{
	%db = PGSQLPool::getPool();
	%uid = %db.getValue(%r,0,0);	
	if (%uid)
	{
		// do stuff
	}
}