Fun with automatically storing SimObjects in a database

Since it's been christmas, I've been forcing myself to not work for a couple of weeks to help avoid the annoying 6 months of burnout that followed last christmas. Not coding for a couple of weeks was quite painful, so I decided I'd write something completely useless. I used to like writing completely useless code, and all the code I've written in the last couple of years has had some degree of commercial point to it. In the process, I discovered that these days I'm really crap at writing useless code. It seems that however hard I try, everything I write has some degree of point.

It started so simply. A random stupid discussion on christmas eve about databases. Nothing new there, it happens a lot on IRC. Since I was trying to find something useless yet fun to work on, I decided I'd take up the torch and create the topic of discussion. That was, automatic persistance of SimObjects into a database. It seemed so useless. It was perfect.

Unfortunately, Paul Dana decided to piss on my fire and pointed out there are a lot of cool uses for the code. Thanks, Paul. Damnit.

So, here's what I came up with.

Database Abstraction Layer

The abstraction layer is currently just a C++ class called DBConnection, which provides standard methods for querying the database. The API if fairly similar to the standard PHP and Perl database APIs, mostly because I have served my time as a database monkey and thats what I'm used to.

In hindsight, this is where it all started to go wrong. I figured since I might need some in-TGE database code at some point in the future, I figured I'd write the database code in such a way that later on I could use multiple database backends. The current thing only uses SQLite, but it would be patheticaly easy to add support for MySQL later.

Declaring Objects

A major "design" feature (in so far as "making it up as you go along" can be called design) was that it should be extremely painless to declare database objects. This basically means everything that can be automatic, should be.

To this end, I wrote the IDBObject class. This deals with the meat of storing and retrieving the object in/from the database. The general idea is you add IDBObject to the inheritance list of any SimObject derived class, and IDBObject does the rest.




setDBConnection() just sets the database connection that this object is associated with.

storeObject() causes the object to be stored in the database (more later).

In addition, a couple of global functions are available for restoring objects from the database:




These are responsible for creating an object of the correct class and restoring it's data from the database. This ensures that if you store, for example, an Item, the object restored from the database will also be an Item and not a plain SimObject.

Creating Objects

Of course, everything is accessible in both script and C++. From hereon everything is in script, as thats what I was using for testing.

Creating an object for the first time (e.g. creating one that is not in the database) is hardly any different from normal. The only real difference is you have to set the database connection. This could be automated with a global database connection and onAdd(), but I havent bothered yet.




testDBConnect() is just a quick function that connects to the database and createObjectTables() creates the neccessary tables for storing objects if they don't already exist.

For testing, I created a new C++ object called DBTestObject. This is nothing more then a bog standard SimObject that declares a couple of fields in initPersistFields(). Since IDBObject does not derive from anything, it cant add ConsoleMethods for itself. Therefore, DBTestObject also declares a couple of ConsoleMethods for functions implemented in IDBObject.

As you can see from the above code, the remainder of the function is just standard TGE code to set the object's data.

To store this object in the database, you would use:




And that's it, the object is in the database. If you change the data you would have to call storeObject() again, of course. I have some ideas to automate object storage, but none have been implemented yet.

Objects in the Database

Once the object has been stored, the database looks something like this:




As you can see, there's an Object table that stores the basic information for the object, including the object's name. There's then a table with the same name as the object's class that stores the actual object's fields. This table is automatically created as needed by IDBObject::storeObject() based on the class name and fields declared in initPersistFields().

Restoring Objects

Restoring objects is even easier. The restoreDBObject() console function creates an object of the correct class based on the name or ID of an object and populates it with the correct data, as demonstrated in the following snippet.




Conclusion

I don't know why, but I find this kind of code really fun to write. It was meant to be completely useless, but there's (semi annoyingly) a hell of a lot of decent uses for it. In total it was only really a day's worth of work, but spread over a week due to things like christmas and the new year. At some point I will probably tidy up the code and take it beyond the first pass prototype stage, but as of tuesday it becomes low-priority-unless-someone-pays-me code as I have a butt load of other things to do :)

Happy New Year!