[vworld-tech] RE: databse usage

[Miroslav Silovic]

Alex Chacha wrote:

> Dynamic content needs to have a whole different database strategy.  
> This data
> must live on the database as it will be used to keep all your servers 
> in synch.
> You also need to develop an intelligent strategy for writing dirty 
> bits and to
> constantly update it with changes (such as NPC movement, combat, 
> spells being
> cast, etc).  In this case the database is a repository for all common
> information that your world servers have to share.

The problem that keeps me awake at nights (well, not really ;) ) is
consistancy. If you've cached a bunch of objects and then changed some
of them, you need to flush the cache so that the transaction boundaries
are preserved. This *hurts*. I came up with several possible solutions.
Note that all of below assume that there's only a single server
connected to the db:

    1. Link the objects with one or more transaction objects, one for
each transaction that an object participated in (there may be LOTS
between flushes). When you want to flush, write out the transactions,
rather than individual objects, by traversing the unflushed transactions
and writing all the objects that haven't been written yet.

    2. Write out the objects in any random order. Sync and backup the
whole db occasionally, and use that in the case of crash. This is what
traditional MUDs do. Alternatively sync the whole cache every few
seconds, and close-and-reopen the transaction. This isn't great for a
realtime engine but should work nicely for a text game. Yes, people
still play those. ;)

    3. Use embedded db (SQL Lite, or Gigabase or something similar).
Forget about caching. This is the solution I favor right now, for the
reason that follows.

Even with a single server, some objects, as returned by db query, may
depend on changes on the other objects. For example, player  object has
'location' property, and the room object has 'list-of-players' property.
Now, the proper db design says that you should only persist the location
on the player - then the many-to-1 relationship between the room and the
player is maintained by indexing the location column in the player table
and retrieving the list on the room by something along the lines of
`SELECT id FROM players WHERE location = <room id>'. Now, if you modify
the player's location, and then touch the room object (which you have to
do, since the next thing after moving the player into a room is loading
the room), the room will load from the db, and the list of players
located in the room will be stale (because the player still hadn't been
written out yet, and the db index hasn't been updated). The only
solution I thought of is to stop relying on the db for updating this
automatically and actually add the player into the list of located
players when the player moves. While this would fix this particular
problem, it strikes me as inellegant and error prone. Any ideas?

    Miro