Game Development Community

dev|Pro Game Development Curriculum

Postgres as a database solution?

by Danni · 05/18/2008 (2:37 pm) · 9 comments

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

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. :)
NextNext blog
PrevPrevious blog
More blogs


Torque Owner Quinton Delpeche
Gobbo Games
#1
05/19/2008 (12:17 pm)
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. :)

Associate Orion Elenzil
Real Life Plus
#2
05/19/2008 (1:52 pm)
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.

Torque 3D Owner Dave Young
Dave Young Games
#3
05/19/2008 (1:58 pm)
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.

Associate Orion Elenzil
Real Life Plus
#4
05/19/2008 (2:22 pm)
roger that,
i was just curious to see how it looked in use from script.

Torque 3D Owner Dave Young
Dave Young Games
#5
05/19/2008 (2:34 pm)
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.

Associate Orion Elenzil
Real Life Plus
#6
05/19/2008 (2:55 pm)
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!

Torque 3D Owner Danni
#7
05/19/2008 (3:58 pm)
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.

Torque 3D Owner Danni
#8
05/19/2008 (4:07 pm)
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.

Torque Owner Adam Beer
Ignition Games Inc.
#9
07/15/2008 (8:03 pm)
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.