MonoSqlite
From OpenSimulator
| (6 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
| − | + | {{Quicklinks}} | |
| + | <br /> | ||
| − | Note: this document is very raw, and under rapid changes. | + | '''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. | ||
= ADO.NET Overview = | = ADO.NET Overview = | ||
| − | ADO.NET provides a level of abstraction from embedding SQL statements all through your code (which would be very bad). | + | 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 | * Top Level Types | ||
| Line 16: | Line 21: | ||
** SqliteParameter - an SQL parameter for parametrized queries | ** 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. | + | 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 == | == Connecting Top Level Types to the Database == | ||
| Line 27: | Line 32: | ||
da.Fill(ds, "prims"); | 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). | + | 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. | The Dataset then uses that DataAdapter to fill itself from the dataconnection using the fillcmd we have specified. | ||
| Line 37: | Line 42: | ||
} | } | ||
| − | This shows how you would get access to rows in a table. | + | 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 == | == XML for Free == | ||
| − | One of the great things about using ADO.NET is that you get an intermediary XML format 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() | * GetXml(), GetXmlSchema() | ||
* WriteXml(), WriteXmlSchema() | * WriteXml(), WriteXmlSchema() | ||
* ReadXml(), ReadXmlSchema() | * ReadXml(), ReadXmlSchema() | ||
| − | Each of these provide a full dump/store/load mechanism for the entire DataSet as an XML document. | + | 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 = | = Creating a more usable DataAdapter = | ||
== The Short Commings of Fill() == | == The Short Commings of Fill() == | ||
| − | Fill will automatically pull in data, create a DataTable, and build some adhoc schema information on that DataTable. | + | 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. | 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 == | == Defining Tables in Advance == | ||
| − | After a couple of iterations on the | + | 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"); | DataTable prims = new DataTable("prims"); | ||
| Line 64: | Line 69: | ||
prims.PrimaryKey = new DataColumn[] { prims.Columns["UUID"] }; | 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. | + | 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); | ds.Tables.Add(prims); | ||
| Line 74: | Line 79: | ||
= Storing and Loading Objects = | = Storing and Loading Objects = | ||
= Initializing the Database = | = Initializing the Database = | ||
| + | |||
[[Category:Development]] | [[Category:Development]] | ||
Latest revision as of 09:17, 18 July 2014
 |
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 |
[edit] 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.
[edit] 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.
[edit] 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.
[edit] 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.
[edit] Creating a more usable DataAdapter
[edit] 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.
[edit] 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"]);
[edit] Building SqliteCommands
The DataAdapter Fill() method gets
