Revision as of 01:32, 22 January 2008

This is an attempted writeup of the methodology used with Mono / SQLite / ADO.NET in OpenSim. There is a real lack of information out there when trying to do something with these technologies.

Note: this document is very raw, and under rapid changes. Excuse the brain dump status at this point, it will evolve into something more readable as time goes on.

ADO.NET Overview

ADO.NET provides a level of abstraction from embedding SQL statements all through your code (which would be very bad). This is done by creating a top level set of objects that you can use to manipulate loaded data, as well as a way to connect these top level objects to your actual database.

• Top Level Types
  • DataSet - an in memory copy of your database
  • DataTable - a mapping of a database table
  • DataRow, DataColumn, etc. - you get the picture
• Vendor Specific DB Types
  • SqliteConnection - provides connection to an sqlite database
  • SqliteCommand - an SQL query against the database
  • SqliteParameter - an SQL parameter for parametrized queries

One of the early confusion points in reading any MicroSoft based documentation is that MS has claimed Sql as their vendor prefix for MS SQL Server. This can cause quite a bit of confusion if you think of Sql as something generic, and don't realize those are all examples specific to MS SQL Server.

Connecting Top Level Types to the Database

This example code will work, but it shouldn't be used, for reasons we'll go into later.

SqliteConnection con = new SqliteConnection("URI=file:opensim.db,version=3");
SqliteCommand fillcmd = new SqliteCommand("select * from prims", con);
SqliteDataAdapter da = new SqliteDataAdapter(fillcmd);
DataSet ds = new DataSet(); 
da.Fill(ds, "prims");

The database connection is vendor specific, obviously, as is the SQL statement (less obvious, but there are vendor differences that need to be handled). DataAdapter is an intermediary which manages pulling things from the database and getting them into the DataSet. Again, you can understand why this would be vendor specific.

The Dataset then uses that DataAdapter to fill itself from the dataconnection using the fillcmd we have specified.

Displaying Data

DataTable prims = ds.Tables["prims"];
foreach (DataRow row in prims.Rows) {
    ... (TODO: print)
}

This shows how you would get access to rows in a table. This is equivalent to the "select * from prims" query that was used to Fill() this table.

XML for Free

One of the great things about using ADO.NET is that you get an intermediary XML format for free. Every DataSet has the following methods:

• GetXml(), GetXmlSchema()
• WriteXml(), WriteXmlSchema()
• ReadXml(), ReadXmlSchema()

Each of these provide a full dump/store/load mechanism for the entire DataSet as an XML document. This includes all tables stored in the DataSet. GetXml() can be very useful for debugging.

Creating a more usable DataAdapter

The Short Commings of Fill()

Fill will automatically pull in data, create a DataTable, and build some adhoc schema information on that DataTable. For Sqlite, this information is woefully lacking, as all columns are mapped to strings, and no key information is loaded as part of it (FillSchema() doesn't do it either, go figure).

I'm lazy, and don't like to repeat myself in code, which makes this even more of an issue, as we'll see once we ge to building update commands.

Defining Tables in Advance

After a couple of iterations on the OpenSim database, the following pattern has emerged. It has the advantage of letting you defining data tables in a more abstract way that should be usable across database engines with a little bit of work.

DataTable prims = new DataTable("prims");
prims.Columns.Add(new DataColumn("UUID", typeof(System.String)));
prims.Columns.Add(new DataColumn("RegionUUID", typeof(System.String)));
prims.Columns.Add(new DataColumn("ParentID", typeof(System.Int32)));
...
prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] };

This produces a much richer DataTable definition, which includes type information on each column, as well as the primary key encoded in the table. If we have such a table already defined we can use that information in the ds.Fill() call instead.

ds.Tables.Add(prims);
da.Fill(ds.Tables["prims"]);

Building SqliteCommands

The DataAdapter Fill() method gets

Storing and Loading Objects

Initializing the Database