Prev/Next Blog
by date

Postgres as a database solution?

Name:	Danni
Date Posted:	May 18, 2008
Rating:	4.7 out of 5
Public:	YES
Comments:	YES
RSS Feed:	or Subscribe with .
Profile Page:	View profile page for Danni

Blog post

This post is out of date, check the completed resource here.
www.garagegames.com/index.php?sec=mg&mod=resource&page=view&qid=1497...

After the frustrating discovery that MySQL is no longer LGPL and free to link I have started dabbling with other databases. Since many of the resources here are either MySQL or SQLite which is not suited to high query environments I decided to check out Postgres and develop a simple object i can use in TorqueScript and C++ within Torque.

One of the most interesting features of libpq, which is Postgre's standard C API client, is that one can perform asynchronous or non-blocking queries allowing the development of fire and forget queries for simple things like non-critical updates. For Indies and others where server resources are limited, this is a powerful tool when 99% reliability is acceptable.

There are three basic classes, PGSQL a C++ interface, PGQuery a C++ interface to a query result and PGSQLDB a TorqueScript interface.

Basic C++ usage;

   PGSQL* db = new PGSQL();
   if (!db->Connect("localhost","5432","TGEA","tgea","test"))
   {
	   Con::printf("Could not connect to PG database");
	   return;
   }
   PGQuery* r = db->Exec("SELECT * FROM tgea_test where second = %s;", "hi");
   const char *str = r->getNamedValue(0,"second");
   or
   const char *str = r->getValue(0,1);
   delete r; // Don't forget to delete it when done!
   db->Close();

Example TorqueScript usage;

   %db = new PGSQLDB();
   if (!%db.Connect("localhost", "5432","TGEA","tgea","test"))
   {
	  echo("Error connecting to database");
   }
   
   %q = %db.Exec("SELECT * FROM tgea_test where second = %s;", "hi");
   echo("got query " @ %q);
   %val = %db.getValue(%q, 0, 1);
   echo("got val " @ %val);
   %nval = %db.getNamedValue(%q, 0, "second");
   echo("got nval " @ %nval);   
   %db.freeQuery(%q);
   %db.Close();

Some notes on Exec, it works similar to printf with a variable argument interface. %s will escape control characters and wrap a string with quotes, %b will escape a binary string and wrap in quotations, %x will insert verbatim. %x is typically used for integers, floats, etc.

Play around with it and let me know if you find any bugs while I continue to work on the rest of the resource. :)

Recent Blog Posts

List:	07/17/08 - AFX Selected Objects, AutoAttack AI and Jerkiness. 06/01/08 - Async (non-blocking) Postgres Database Handler 05/18/08 - Postgres as a database solution? 05/04/08 - Step sounds, recoil and hit scans. 01/27/08 - Torque prediction hack for hitscans

Submit your own resources!

Quinton Delpeche (May 19, 2008 at 19:17 GMT) Resource Rating: 4

Danni ... now this is coincidence ... I was just about to embed Postgres into TGEA. :) My idea was not to make Postgres available via TorqueScript as I merely want the engine itself to do the queries ... this prevents people from reverse engineering my queries and underlying DB structure. :)

I am gladly going to do some testing on this for you ... awesome job. :)

Orion Elenzil (May 19, 2008 at 20:52 GMT)

i don't forsee using this personally, but this looks like a cool resource.

you mention you support non-blocking queries,
i'd be interested to see how you're handling one of those.

Dave Young (May 19, 2008 at 20:58 GMT)

Not speaking for Danni, but we can do non-blocking queries by separating the query and the result handling, as in
Event Driven Database

Result handling would still 'block', but by separating the query and result handling at least the app need not wait for the db server.

Orion Elenzil (May 19, 2008 at 21:22 GMT)

roger that,
i was just curious to see how it looked in use from script.

Dave Young (May 19, 2008 at 21:34 GMT)

The way I run it is to have a console method like EventExecute(%query,%callback,%arg1,%arg2);
where
%query = "SELECT * From whatever;";
%callback = "SomeFunctionName";
%arg1, %arg2 etc are extra 'context' arguments I want passed to the callback function

and then the callbackfunction looks like

function SomeFunctionName(%resultid,%arg1, %arg2)
{
   %numrows = $MySQLDB.getnumrows(%resultid);
   ... other db code
   $MySQLDB.freeResult(%resultid);

}

so each query and handler is split into two parts, making it trickier to code but extremely performant.

Orion Elenzil (May 19, 2008 at 21:55 GMT)

interesting, thanks.

we have something similar for asynchronous curl requests,
but instead of passing the optional arguments along with the callback name we store the stuff in a request object.

ala

%request = newCurlRequest();
%request.url = %someUrl;
%request.callBack = "SomeFunctionName";
%request.arg1 = %stuff;
%request.arg2 = %otherStuff;
%request.bindClassName("curlRequest");
%request.start();

then later

function curlRequest::SomeFunctionName(%this)
{
   // do stuff with %this.arg1 and %this.arg2
}

.. which is effectively the same thing.
thanks for the info!

Danni (May 19, 2008 at 22:58 GMT) Resource Rating: 5

Dave, actually you doing what I was planning for non-blocking queries. :) I will probably be using a pool of connections however as today's multi-core/processor systems can be doing more than one thing at once. I may even work in a QoS system too.

Quinton; simply nuke the PGSQLDB class and supporting functions in the scripting.cpp then it will have no interface within TorqueScript.
Edited on May 19, 2008 22:58 GMT

Danni (May 19, 2008 at 23:07 GMT) Resource Rating: 5

Sorry forgot to ask, Dave how are you storing the result? Right now for TorqueScript the PGQuery is thrown into an array of linked lists similar to a hashmap. It was the most efficient method I could come up with. :)

C++ just keeps the PGQuery around.

Adam Beer (Jul 16, 2008 at 03:03 GMT)

Could this be used in place of this?:

http://www.garagegames.com/index.php?sec=mg&mod=resource&page=view&qid=4085

I basically want to have a database that keeps track of a clients ranks, and use it for logging into the game.

You must be a member and be logged in to either append comments or rate this resource.