[Opensim-dev] Solution attempt about code duplication in database plugins

[Laurent B.]

Hello, 

My name is Laurent (Larry). I am a MCAD developer / architect / instructor on behalf of a service company based in Luxembourg, and I live in France. I'm French, 
so I humbly request your indulgence for the poor quality of my writing.

I am fascinated by your teamwork. So I decided to put my nose in the OpenSim code... Thanks to it, I have learnt a lot already about open-sourcing, third party tools I never see before (as prebuild, nini...), and have a better idea about how mono freaks are doing.

Taking a close look at the plugins database, I noticed that the persistence logic was replicated in each, which must be difficult to maintain, and it may leads to ennoying bugs... That's why I started to write a plugin generic database in an attempt to find a solution to this problem.

It consists of an additional OpenSim.Framework.Data.DbEngine assembly which contains the usual classes you know that implements IAssetProvider, IGridData, IInventoryData, IUserData. Instead of creating ADO.NET Connection, Command objects, theses classes logic relies on an IDbEngine helper that creates thoses objects and handle queries parameters.

IDbEngine is an interface that has to be implemented by as many classes as desired available database engine. This class helps to instantiate ADO.NET objects the best way the specifics of the database engine allows to.

As an example of code is better than too much explanations, please find AssetData.cs here: http://pastebin.ca/845808 and MSSQLDbEngine here: http://pastebin.ca/845813. GridData.cs, InventoryData.cs, UserData.cs have also been implemented. Also SQLiteDbEngine.cs is ready.

However, in trying to design this plugin, I encountered several problems. The first one stucks me a little...

* The structure of the database differs from a plug to another. 
For example, there is a field called assets.id in MySQL plugin, although it is called assets.uuid in SQLite. The same way, some varchar field size are fixed to 255 in all SQLite tables that are generated from DataSet. What about to keep a database structure common to all plug-ins ? This would be better to keep this new approach simple.

* For the moment, there is two different strategies of access to the data: one with DataSets implemented, the other with SQL queries. I think it's an opportunity to experiment other approaches, knowing that the datasets eats a lot of memory when they are well filled, and chews up performance when commit is done.
Maybe we could implement stored procedures ? The proposed approach will make possible to implement a variety of strategies, which could be activated in turn in order to choose the most effective for a given database engine. 

That is, if you like the idea, I would love to hear feedback, comments and suggestions before to continue to work on it.

Sincerely, 
Laurent.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://opensimulator.org/pipermail/opensim-dev/attachments/20080108/f933336d/attachment-0001.html>