[MUD-Dev] Comments on the DB layer

[Jon A. Lambert]

> From: clawrenc at cup.hp.com
>    at 10:41 PM, "Jon A. Lambert" <jlsysinc at ix.netcom.com> said: >>
> 
> Later thought:  If I make my object formats known by the DB (ie
> tightly bind my DB implementation to my object format), then it would
> be fairly easy to have the DB only store deltas for the older
> versions.  Currently my objects consist of four lists:
> 
>   List of parents
>   List of attributes
>   List of methods
>   List of verb templates
> 
> with a little blob at the top for ObjectID and other maintenance data. 
> It would be fairly easy to make the prior versions of the objects only
> contain those members of the list which have changed...
> 
> <thinking>

I settled for modeling a generic C++-like class hierarchy into an RDB model.
It's not really tightly coupled to the data itself, but a coupling of the RDB catalog
structure to an OO model (At least I think so?).
 
At server boot I initialize my class structure by examining the DB catalog
definitions starting with some pre-defined "$roots" (classes):

For "public" attributes/object attributes: 
  SELECT column type length FROM SYSCOLUMNS WHERE TABLE = class;
For user type attributes:
  SELECT column foreigntable FROM SYSKEYS WHERE TABLE = class;
For "private" attributes/class attributes and methods
  SELECT column type length FROM ClassTable WHERE CLASS = class;
For method code:
  SELECT binary_blob FROM MethodTable WHERE CLASS = class AND 
      METHOD = method; 
For Inheritance:
  SELECT child FROM ChildTable WHERE CLASS = class;

This is somewhat simplified.  It gets very nasty checking 
multiple inheritance and potential inheritance recursion. ;-)
  
> 
> >3) I can see how you would get numbers of killed mobiles by checking
> >    how many old objects of the type were dead.  I don't see how you 
> >    XREF with the weapons or spells, unless you store this info with
> >    the dead mobile object or the weapon or spell object undergoes a 
> >    state change requiring it to be stored with the same transaction 
> >    time. 
> >
<snip>
> 
> -- Iterate across the list and record the transactions which deleted
> them.
> 
> -- Iterate across those transactions and list all the player objects
> referenced by the transaction.
> 
These two statements lead me to suspect you are storing transactions
as objects.  No?

The type of logging I am doing knows nothing about the transaction
itself.  It can be used for transaction rollback also.  

Here's an example of what I'm thinking (its likely to pose problems *hehe):

Bubba> drink water from canteen.

Lets assume that an event is issued and during the course of its execution
it will modify 2 objects, Bubba and the canteen.  Bubba will have his thirst
attribute modified and the canteen will have its weight attribute modified.
Assume the event handles the canteen first then Bubba.

If this is successful it might be logged as follows:

tranid   
00001 ,  start tran
00001 ,  old canteen , new canteen
00001 ,  old bubba , new bubba
99999,  canteen disk commit
00001 ,  end tran
99999,  bubba disk commit

The 99999 tranids come from the cache manager and may be asynchronous
like I have shown.

Now for rollback lets assume player Jon blows Bubba away before he can
quench his thirst.

tranid   
00001 ,  start tran
00001 ,  old canteen , new canteen
00002 ,  old bubba , new bubba  <--- bubba dies here
<--- bubba's drink event fails here, cause bubba has changed state 
00001 ,  old bubba , new bubba ---> never logged
99999,  canteen disk commit  
00001 ,  end tran   ---> never logged
99999,  bubba disk commit

Rollback would read the log backwards looking for tranid 00001,
restoring canteen back to its original state and mark all the restored 
objects dirty again.
Server bootup/recovery would read the log differently, probably
forward from the last 'syncpoint'  making sure that all objects 
between 'start trans' and 'end trans' had 'disk commits'.  

Does this seem workable?  This log could be certainly remain in 
memory and be flushed out to disk at syncpoints.

> I can move backwards along the player object-version line, I can
> examine their inventory.  Heck, if I also store transaction owner's
> with the transactions (probably a good idea), I could actually
> recreate and watch the whole fight, blow by blow as it happened, along
> with watching Bubba call in his Wiz friend to...).  Just roll the DB
> back to that time, and replay.  It makes snooping a thing of the past.

The transactions I log above are probably too generic to show what
originated them and why.  They are just sequences of otherwise
arbitrary state changes.  

>>  I
> >use a timestamp field in  the RDB, also automatic but it is not part
> >of the "loaded" object.  It exists  solely in the RDB and is very
> >efficient.
> 
> What does the timestamp give you?

Nothing much.  I have plans for external utilities which may take 
advantage of it.  I like to timestamp db records for debugging.  Its easy
to select everything that's changed within a given time period.

> 
> >Class Versioning happens through SQL DDL.  Attributes that are
> >removed  are removed from all instanced objects.  Attributes that are
> >added are added to all objects as nulls.   Methods reside in the
> >class along with class instance attributes.   (That ColdC vs "real
> >OOP" thing we discussed  earlier ;-)  )  
> 
> If added attributes default to NULL, how do you propagate an attribute
> value to all children/instances?  Similarly, how does this work for
> methods?
> 

For object instance storage, class = table, an object instance = row.
Class attributes, inheritance and methods are stored in other tables (see above).  
Classes may be locked to prevent attribute and/or method changes. 

If a class is not locked adding a or deleting a new attribute is done
through 'ALTER|DROP TABLE class ADD COLUMN attribute datatype;'
User datatypes are mostly handled through foreign keys (64-bit integers). 
Note this will propagate NULLs throughout the database and not in memory
objects.   The translation layer uses dynamic SQL to read tables of unlocked
classes, when a class is locked the translator executes pre-bound SQL. 
The memory cache for all instances of the class is then marked for reload
(a dirty/no read flag) .
 
Children != object instances in my model.  A child is a descendent class
and needs no special update.   Inheritance is done through foreign keys
on the ClassTable.

> >...Versioning can be expensive
> >if done late in a class's life, but  this is part of interactive
> >programming and not a runtime thing.
> 
> And the expense is due to the fact that you now have two or more
> versions of the same base class, each with its own collection of
> instances?

No. Two versions will not exist at the same time.  If I add an attribute
to class Apple and I have 10000 apples in existence at the time.  There
is bound to be some delay in referencing an apple because all apples
are marked to be reloaded from DB.  The DB will also churn a bit while
it adds attribute to all the records.
 
> 
> How do you handle the case where you want to propagate a change, say
> an added/change method or attribute, to all current instances?   As I
> undersand your current system making the change to the class definesa
> new version of the class and only affects new instances of that class. 
> Old instances continue to behave as versions of the old class
> (pre-edit).

Any old instances that are accessed between the update of the DB and the
marking of the objects to be reloaded are handled through dynamic SQL
of the translation layer.  And it shouldn't(?) update them.  Hrrrm, I must
do some work on this, for it has a potential bite.  I could issue a table lock
and mark the objects reloadable and wait for DB completion. :-)

> 
> 
> This is pretty close to what I'm attempting (tho I had no idea that
> DB2 did it too -- I just thunk it up one night).  My idea is to run a
> seperate database, a simple ISAM pretty well, for the transaction log. 
> Log entries would be of three types:
> 
>   Start of cache commit.
>   Specification for a given transaction  
>   ...(may be many of these)...
>   End of cache commit

It's what in your specification part that may be worrisome.  Is it too
dependent of transaction's "context"?

> 
> Odds to dollars your OS is not quite this stupid (I guess we're
> talking Win NT here, so it actually may be pretty likely). 

Hey, do I detect an OS bias here?  At least I'm not using a 
cheap imitation *gurgle* ;-)
 
> Re-opening the file every IO and then closing it to keep everybody in
> sync is pathetically expensive.  The standard solution is to run file
> IO's thru a dup()'ed or dup2()'ed handle.  
> 
Yep, the trusty mainframe will let you thrash the hell out of it with 
re-opens too.  

<code snippit snipped>

This might work if close() doesn't attempt to free handles/buffers.  I will
have to test it.  Thanks for the idea.

JL