[MUD-Dev] Re: Databases: was Re: skill system

[Jon A. Lambert]

On 20 Jun 98, Adam J. Thornton wrote:
> On Sat, Jun 20, 1998 at 04:17:25AM +0000, Jon A. Lambert wrote:
> > An OO DB might be more attractive than an RDB.  Since a whole host 
> > of OO comes for free.  Yet your mud programming language's particular
> > implementation of OO might have to match or be translated to a form
> > recognizable by the OO DB.
> 
> I'm using PostgreSQL in the project I'm working on.  I have no idea whether
> the speed will eventually be acceptable or not, yet.
> 
> My objects are runtime-static in the sense that new object properties and
> methods will not be created on the fly.
> 
> Postgres works rather nicely for this since you can define tables/classes
> in an inheritance relationship, so that class Item can inherit Object but
> add the properties Legal_Owner, Parent_Object, and Container_Relationship,
> for instance.  Then items with weight and bulk can inherit item and add
> weight and bulk properties.  You can do multiple inheritance, too, so I can
> have an item class that inherits both Bulky_Item and Container, to get,
> say, an orange crate.  Or those two plus Lockable for a chest.
>

It is particularly important to develop unique OIDs (object IDs) to 
use as keys in the design of tables.  If you are implementing 
concurrency or a distributed environment, the mechanism for 
assigning OIDs needs to be even more robust.  Using anything from the 
problem domain (i.e. user name, etc.) as table keys creates a mess of 
maintainence and access problems.

Object attributes can be mapped to SQL-RDBMS columns.  But since 
RBMS usually only allow data primitives, sometimes it makes better
sense to map nested structures/ADTs to one-to-one table associations 
and sometimes fold them into the class table.  It depends.

One can map objects directly to classes and instances to rows, or 
joined rows as with ADTs, there are some other ways to implement 
inheritence.   For instance you can map entire class heirarchies to a 
single table.  This has the advantage of making queries and access 
very simple and performance quite high, but it wastes disk space and 
couples the classes very tightly to the class heirarchy, so making 
small changes to a single class affects every class within that 
heirarchy.   Another way would be to map only sub-classes of 
superclasses to tables.  This is probably worse than he former since
one has to add attributes to both the base class and the descendent 
classes.  

The method I like best is the direct class to table approach since it
preserves the spirit of OO.   However, the ease and speed of access
is directly related to the depth of your inheritence tree. :(

The only problem areas come from many-to-many relationships which
ideally become meta-tables which function solely to maintain the 
association and thus create additional tables in the RDB. 

> Clearly database manipulation gets very time-expensive.  My strategy,
> although it's not completely thought through yet is to maintain much of the
> world state in in-memory data structures and only commit it to the DB when
> a) there's a lull and my system load is low enough that I can do so
> without fear of vastly slowing down things, or b) when something that it's
> crucial that the rest of the world know about happens (figuring out the
> criteria for this is the tricky part), or c) when a timeout happens such
> that the DB hasn't been written in X seconds, so that I don't end up with
> an impossibly huge transaction queue.
>

I'm an advocate of having a translation layer (or persistence layer) 
to encapsulate SQL.  Leave hard-coded SQL out and use dynamic SQL in 
the translation layer.  This way the access interface from the "real" 
application is very simple (get/put) and easily replaceable.

Keep in mind everything I've written here, with the exception of 
OIDs, is based on the assumption of the objects' attributes being 
static at runtime.  Dynamic objects are a whole different ball-game.  

--
--/*\ Jon A. Lambert - TychoMUD     Internet:jlsysinc at ix.netcom.com /*\--
--/*\ Mud Server Developer's Page <http://www.netcom.com/~jlsysinc> /*\--
--/*\   "Everything that deceives may be said to enchant" - Plato   /*\--