Using XML For Data Transport in Torque Script

Because of the limits on returning arrays or objects in Torque Script, I have been wracking my brain trying to come up with a decent way to handle the DB query results (and anything else array related) without causing a lot of I/O and other overhead. The idea I have come up with will require a bit more memory, but the additional memory required should be negligible as long as the system is used responsibly.

First I would like to get some nomenclature out of the way:

Database - Any database we are using, whether file/library based or server based.
Table - A table existing on the database.
Result Node - An object that has been built by parsing XML from a database return result. The result node will be kept in memory until it is no longer needed.
Result List - A vector container containing result node objects.
Result Manager - The manager that maintain all methods for adding, deleting, and querying on the result list.
Result ID - The iterator that will be used to request nodes from the vector container maintained within the result manager. I haven't tested passing iterators back to torque script, but it should work fine since I am only storing it for future result node queries and not actually using them in Torque Script.
XML Parser - The object that will parse the XML sent from the game server's database query result logic (or any other logic that needs to build arrays).

The basic idea for our project is:

1. When the game server makes a query to the database, the DB result handling logic will build an XML stream and send it to the XML parser for parsing.

2. The XML will be parsed, and using the result manager, a result node will be built and added to the result list containing an ID that will make it unique. The ID will be returned to the game server (script logic) so that the script logic knows which result node to request from the result manager when requesting data or submitting a delete node request.

3. While the table exists, the game server can submit commands to the result manager to get any data it needs from a specific result node. The result node ID will need to be passed to the result manager in order to get the correct node.

4. When the server no longer needs the result node, it will submit a "delete node" request to the result manager using the unique ID that was provided when the result node was created. The node will be deleted. The one problem I foresee here is losing track of IDs since they are the iterators for the vector container. I need to give this part some thought, because, to make it faster, I would like to access the vector nodes directly. However, if one function adds a node, and another functions removes a node, then the iterators will change (if my memory serves me correctly). The alternative is to just NULL the node and leave it there until it hits a predefined threshold which could trigger a cleanup and reassignment of IDs. Or, I could just access the vector sequentially and define a variable within the node struct as the ID instead of trying to use the iterator. This would cause more overhead, but it would be easier.

Each time the server needs new information from the database, it can submit the DB query, but if it needs data that it has already sent to the result manager, then it can send queries through the result manager to get that data.

Here is code similar to the code I use to build the XML stream from the database result:

function dbQuery(%query)
   {
      DBI.query(%query);
      %result = DBI.StoreResult();

      %returnString = "";      
      
      for (%i= 0; %i< DBI.NumRows (%result); %i++)
      {
         DBI.FetchRow (%result);

         %returnString = %returnString @ 
                         "<record>";
         %returnString = %returnString @ 
                         "<id>" @
                         DBI.GetRowCellDirect(%result, 0) @ 
                         "</id>";
         for (%i2 = 0; %i2 < DBI.NumFields (); %i2++)
         {
            %fieldName = DBI.GetFieldName(%result, %i2);
            %returnString = %returnString @ 
                            "<" @ 
                            %fieldName @ 
                            ">" @ 
                            DBI.GetRowCellDirect(%result, %i2) @ 
                            "</" @ 
                            %fieldName @ 
                            ">";
         }
         %returnString = %returnString @ "</record>";
      }
      DBI.FreeResult(%result);
      return %returnString;      
   }

I cannot post the actual database logic because it is in the engine and it could be any number of databases which all have their own APIs and functions. The packages I added to the torque script just serve as interfaces to the APIs in the engine. Right now I only have MySQL and SQLite APIs implemented, but this could be used for any number of databases as long as the packages contain the same function names. Keeping the interface generic will go a long way to providing portability.

I don't have any decent example logic for accessing the result manager to provide you at this time other than the example below. I will add more logic as I experiment more and receive more feedback.

%sXMLStream = dbQuery("SELECT * FROM actorTable WHERE actorTable.actorName = '"@%actorName@"'");
%actorNodeID = ResultManager.addNode(%sXMLStream);

%weaponID = ResultManager.getColData(%actorNodeID, "currentWeapon");
%sXMLStream = dbQuery("SELECT * FROM weaponTable WHERE weaponTable.weaponID = '"@%weaponID@"'");
%weaponNodeID = ResultManager.addNode(%sXMLStream);

%rmWeaponBaseDamage = ResultManager.getColData(%weaponNodeID, "weaponBaseDamage");

I know this little bit of code is probably overkill, but I hope you can see the potential for having a result manager of this type. In my case, I would rather use a bit more memory than make more database queries, especially if the database exists on another network. Also, I hope that using XML and a fast parser will resolve some of the other non-DB related issues that Torque Script presents.

With the example above, I can now use the result manager to pull the data from memory multiple times without ever making another DB query. In addition, I can use this manager to access data from other pieces of the system, without having to worry about other work-arounds for passing arrays.

This example may work best in a single player game, though it presents another way for the player to cheat, unless you added some checksum type logic in there. For example, you could create a hash value of each node so that when the game server accesses it, it will know if it has been tampered with by checking the hash value of the node against a previously built hash value of the node. The game we are planning on implementing this in is an MMO, so obviously we will need to watch the memory usage and provide good cleanup as necessary.

This is not a replacement to the other resource "TScriptArray Object". It is just another, more managed way, to handle passing and returning of data without a lot of excess scripting.

Let me know what you think can be done differently. I will expand on this as I add more logic and ideas.