[Opensim-dev] further db thoughts

Fri Jan 11 20:46:21 UTC 2008

Sean,

I know you're going to hate me for this, but before I post code, I just wanted to explain whats here 'on my own hard-disk' to see if it's anything like what you want to do:

* I have a DbConnectionPool that acts as a programmatic manager of connections (Subclassed into MySqlDbConnectionPool)
* this is passed to a TableMapper which exposes it as a protected method WithConnection(delegate) for subclasses to access; they never access the connectionpool directly; the delegate approach lets the TableMapper lock, allocate, pre- and postprocess the connection securely and encapsulated. (kind of like a using clause)
* The TableMapper is subclassed into a TableMapper<TRowMapper> that implements a CRUD interface by creating dynamic sql queries (the param prefix is pulled from the ConnectionPool) and populating them from a 'Schema'
* A 'Schema' is a collection of FieldMapper<T>
* FieldMapper<TField> is initialized with a field/param name (same for laziness sake), TField is the RowMapper field value type, a get delegate and a set delegate for the TField type object. It also does all the db/field type casting as well as expands things like Vector and Quaternion into several db fields.
* The delegate approach keeps everything field related together, as opposed to a FromReader/ToReader method approach, which helps dodge inconsistencies.* The TRowMapper is mostly of type RowMapper<TRowObject> which just creates a class that has a member of TRowObject; this lets you subclass TRowMapper<TRowObject> and add db-specific extra-fields to it in order to manage hierarchies.
* Upon operations, you create your rowmapper object (or just a plain object if there is no aux data) and either do FillObject( Schema, rowObject) or extract stuff by overrifing the FromReader(rowObject, DbReader) - the latter I use to populate a group from a part table (the select on the key field returns multiple rows, so i just PartMapper.FromReader() on each one to build the group.

- it does NOT auto-create tables, as I'm not interested in that approach as you know, but you already have the code for it and the data in the 'Schema' so it should be a piece of pie.

All in all, everything is explicit and compile-time; no cacheing, synchronization or double-buffering, no reflected and emitted code, but it works, and is quite efficient. It takes a while to get into the flow of how the various components work together, but after that I feel it's very conveniens and trustworthy.

Of course, it needs more work to get it going on all different database interfaces, but it's created from the start to be subclassed into differing databases.

If this sounds like something we'd want, I'd be happy to make it available as a third-party BSD Licence lib within the project, my only demand is that it is called TribalMedia.Framework.Data and that the BSD header remains intact.

Best regards,
Stefan Andersson
Tribal Media AB

> Date: Fri, 11 Jan 2008 08:58:15 -0500> From: sean at dague.net> To: opensim-dev at lists.berlios.de> Subject: [Opensim-dev] further db thoughts> > I spent another day wasted on nhibernate, until someone can sort out an> example using Mono + Linux + Mono.Data.SqliteClient, I think we're dead> there. I do encourage someone to sort that out, as nhibernate would> definitely be better than any other approach, but I haven't figured out> how to get it to work all the places we need it to.> > That being said, there is real work that can be done today to make what> we have a lot better (both performance wise, and maintainability wise).> I started hacking on the SQLite adapter in this direction yesterday, and> will be spending the next week+ working out these new ideas there.> > Step 1) remove most of the ADO.NET plumbing, get us back to straight db> access. For things like Users and Assets this actually will reduce the> lines of code in the plugin (I dropped the LOC for the friends list> functions yesterday by about 1/2). I think this will remove some of the> nasty prim move lag for linked sets, as well as the asset store startup> time.> > Step 1b) move the SQL definition out of the code and into seperate> Resource files. I'm taking a page of inspiration here from the Ruby on> Rails approach to things, which I'm hoping will work out well. Each> datastore will have a set of DataStoreName_XXX.sql files. XXX will be a> sequence number. We'll have a dataversions table in each store, which> will store which version the DataStore is at. On startup, we'll check> to see if there are any newer XXX files for the store, and if so, apply> them in order. This mirrors the "migrations" concept in RoR, and works> *really well* for being able to version databases forward. I expect to> have the first working bits of this approach the middle of next week.> > Step 2) Adapt the upward facing interfaces. Right now the earliest data> interfaces are: GetAll & Store, which can be seen with prims. Some of> the later interfaces are more along the lines of Add, Update, Get,> Remove. I think there is some value getting towards that even if we> still need GetAll for the base prim load for sims.> > Anyway, back to hacking. Again, comments here are welcomed for sure.> > -Sean> > -- > __________________________________________________________________> > Sean Dague Mid-Hudson Valley> sean at dague dot net Linux Users Group> http://dague.net http://mhvlug.org> > There is no silver bullet. Plus, werewolves make better neighbors> than zombies, and they tend to keep the vampire population down.> __________________________________________________________________
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-dev/attachments/20080111/e0288a25/attachment-0001.html>