<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class='hmmessage'>Sean,<BR>
<BR>
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:<BR>
<BR>
* I have a DbConnectionPool that acts as a programmatic manager of connections (Subclassed into MySqlDbConnectionPool)<BR>
* 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)<BR>
* 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'<BR>
* A 'Schema' is a collection of FieldMapper<T><BR>
* 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.<BR>
* The delegate approach keeps everything field related together, as opposed to a FromReader/ToReader method approach, which helps dodge inconsistencies.<BR>* 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.<BR>
* 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.<BR>
<BR>
- 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.<BR>
<BR>
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.<BR>
<BR>
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.<BR>
<BR>
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.<BR>
<BR>
Best regards,<BR>
Stefan Andersson<BR>
Tribal Media AB<BR><BR><BR><BR>
<HR id=stopSpelling>
<BR>
> Date: Fri, 11 Jan 2008 08:58:15 -0500<BR>> From: sean@dague.net<BR>> To: opensim-dev@lists.berlios.de<BR>> Subject: [Opensim-dev] further db thoughts<BR>> <BR>> I spent another day wasted on nhibernate, until someone can sort out an<BR>> example using Mono + Linux + Mono.Data.SqliteClient, I think we're dead<BR>> there. I do encourage someone to sort that out, as nhibernate would<BR>> definitely be better than any other approach, but I haven't figured out<BR>> how to get it to work all the places we need it to.<BR>> <BR>> That being said, there is real work that can be done today to make what<BR>> we have a lot better (both performance wise, and maintainability wise).<BR>> I started hacking on the SQLite adapter in this direction yesterday, and<BR>> will be spending the next week+ working out these new ideas there.<BR>> <BR>> Step 1) remove most of the ADO.NET plumbing, get us back to straight db<BR>> access. For things like Users and Assets this actually will reduce the<BR>> lines of code in the plugin (I dropped the LOC for the friends list<BR>> functions yesterday by about 1/2). I think this will remove some of the<BR>> nasty prim move lag for linked sets, as well as the asset store startup<BR>> time.<BR>> <BR>> Step 1b) move the SQL definition out of the code and into seperate<BR>> Resource files. I'm taking a page of inspiration here from the Ruby on<BR>> Rails approach to things, which I'm hoping will work out well. Each<BR>> datastore will have a set of DataStoreName_XXX.sql files. XXX will be a<BR>> sequence number. We'll have a dataversions table in each store, which<BR>> will store which version the DataStore is at. On startup, we'll check<BR>> to see if there are any newer XXX files for the store, and if so, apply<BR>> them in order. This mirrors the "migrations" concept in RoR, and works<BR>> *really well* for being able to version databases forward. I expect to<BR>> have the first working bits of this approach the middle of next week.<BR>> <BR>> Step 2) Adapt the upward facing interfaces. Right now the earliest data<BR>> interfaces are: GetAll & Store, which can be seen with prims. Some of<BR>> the later interfaces are more along the lines of Add, Update, Get,<BR>> Remove. I think there is some value getting towards that even if we<BR>> still need GetAll for the base prim load for sims.<BR>> <BR>> Anyway, back to hacking. Again, comments here are welcomed for sure.<BR>> <BR>> -Sean<BR>> <BR>> -- <BR>> __________________________________________________________________<BR>> <BR>> Sean Dague Mid-Hudson Valley<BR>> sean at dague dot net Linux Users Group<BR>> http://dague.net http://mhvlug.org<BR>> <BR>> There is no silver bullet. Plus, werewolves make better neighbors<BR>> than zombies, and they tend to keep the vampire population down.<BR>> __________________________________________________________________<BR><BR></body>
</html>