MonoSqlite
From OpenSimulator
 |
Languages: |
|
This page is probably now terrible out of date.
This is an attempted writeup of the methodology used with Mono / SQLite / ADO.NET in OpenSimulator. 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.
Contents |
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 OpenSimulator 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
