SQLite is Fun!

I have been playing with SQLite for creating my databases for a game concept I am working on. I am using the SQLite resources with the TGE engine. Right now I am in the "create the functions that manipulate the databases" phase. It is really a lot of fun. I never thought databases could be so fun.

For starters I have created a "maindatabase.sdb" that is opened when databases are initialized. This function is called "databaseInit()". It goes through and tries to open the database, query for a header table, query for other databases, and then loads and opens the other databases. The instances of these databases are stored in a scriptGroup that automagically closes all the databases when it is deleted (via the onRemove callback of a scriptGroup object). The function also assigns a callback for error messages with the message customized to show which database it came from.

Another function I wrote creates a new database and puts an entry into the main database as well as some initial data in the database.

I still need to write some support functions to handle deleting (and/or unreferencing a database in the maindatabase), referencing an existing database into the main database, and various table manipulation routines custom to my game concept. Some functions I want are altering existing tables, copying tables, etc.

The reason I wrote this blog is so I can ask the question, "Would some working code be useful to anyone once I get some basic functions running?". It is all script and I would imagine someone could use it to create their own routines for accessing the database. I know that I have had to learn a bit of SQL to do this, but this stuff comes easy for me. I have been commenting nearly every line for my own purposes so someone should be able to get an idea of what I am doing. I am also VERY heavy on error messages and testing inputs and outputs.

So, let me know if something like this would be useful.

I know it is not a screenshot, but it is some code:

function DatabaseInit()
{
   // create a script group to manage databases
   %databasegroup = new ScriptGroup(databaseGroup);

   // open the main database
   %maindatabase = new SQLiteObject(maindatabase);
   if(%maindatabase == 0)
   {
      echo("ERROR: Failed to create database object. maindatabase load aborted.");
      return;
   }
   // setup error function for main database
   createQueryErrorFunction("maindatabase");
   // add database to script group
   %databasegroup.add(maindatabase);

   // open database
   %dbname = ExpandFilename($database::maindatabasepath);
   if (maindatabase.openDatabase(%dbname) == 0)
   {
      echo("ERROR: Failed to open database: " @ %dbname);
      maindatabase.delete();
      return;
   }

   // create a new simple table for demonstration purposes
//    %query = "CREATE TABLE IF NOT EXISTS databases (name TEXT, path TEXT)";
//    %result = maindatabase.query(%query, 0);
//    if (%result == 0)
//    {
//       // query failed
//       echo("ERROR: Failed to create new table.  maindatabase test aborted.");
//       maindatabase.closeDatabase();
//       maindatabase.delete();
//       return;
//    }
//    // cleanup query result
//    maindatabase.clearResult(%result);

   // input data for testing
//    %query = "INSERT INTO databases (name, path) VALUES ('test value', 'test path')";
//    %result = maindatabase.query(%query, 0);
//    if(%result == 0)
//    {
//       echo("ERROR: Failed to INSERT into databases table.");
//    }
//    // cleanup query result
//    maindatabase.clearResult(%result);

   // query for additional databases
   %query = "SELECT * FROM databases";
   %result = maindatabase.query(%query, 0);
   if (%result == 0)
   {
      echo("ERROR: Failed to SELECT from databases table.");
   }
   // attempt to retrieve "result" data
   while (!maindatabase.endOfResult(%result))
   {
      %databaseindex = maindatabase.getColumn(%result, "index");
      %databasename = maindatabase.getColumn(%result, "name");
      %databasepath = maindatabase.getColumn(%result, "path");
      %databasedesc = maindatabase.getColumn(%result, "description");
      echo("database index = " @ %databaseindex);
      echo("         name = " @ %databasename);
      echo("         path = " @ %databasepath);
      echo("         description = " @ %databasedesc);

      // open additional database
      if(!isFile(%databasepath))
      {
         echo("ERROR: Database file" SPC %databasepath SPC "does not exist.");
         continue;
      }

      %newdatabase = new SQLiteObject(%databasename) {
         description = %databasedesc;
      };
      if(%newdatabase == 0)
      {
         echo("ERROR: Failed to create database object. maindatabase load aborted.");
         continue;
      }

      // open database
      %dbname = ExpandFilename(%databasepath);
      if (%newdatabase.openDatabase(%dbname) == 0)
      {
         echo("ERROR: Failed to open database: " @ %databasename);

         %newdatabase.delete();
         continue;
      }

      // setup error function for database
      createQueryErrorFunction(%databasename);

      // reference database in database group
      %databasegroup.add(%newdatabase);

      // get next row of query data
      maindatabase.nextRow(%result);
   }
   maindatabase.clearResult(%result);
}

Note: From looking at the function I am not checking the header table. I guess I should to make sure the database is even valid? Maybe not, I am doing a SELECT from the databases table...

Later,
Frank