Molding Mono.Data.SQLiteClient to use csharp-sqlite
Teravus Ovares
As part of my work on OpenSimulator, we ran into an issue with the
sqlite/native wrapper in Mono.Data.SQLiteClient so.. to get to the
bottom of it.. I molded the Mono.Data.SQLiteClient over top of
csharp-sqlite to enable full ADO.NET support.
It turns out, there were a few methods that needed to be made public
as well as a few bugs relating to the parameter preparing, there was
also a string->blob, blob->stream issue... which I resolved.
These are the source files. Based on Mono.Data.SQLiteClient, you
can find Community.Data.SQLiteClient at the following address:
http://teravus.wmcv.com/googletester/csharp-sqliteado.zip
The above zip file contains both the changed csharp-sqlite /and/ the
Community.Data.SQLiteClient project.
I changed the source for csharp-sqlite, I've supplied a patch against
hg default, 40 at
http://teravus.wmcv.com/googletester/csharp-sqlite-changes-patch.zip
The changes to csharp-sqlite are licensed under whatever licence
csharp-sqlite is (MIT). The Community.Data.SQLiteClient is licensed
under whatever license Mono.Data.SQLiteClient is licensed under
because it's a derivative work. (I think it's also MIT, but Miguel
would know better).
This is only set-up for .NET 3.5. It will not currently work for
.NET 2.0. This also only supports the SQLite Version 3+ API, not 2.
From Mono.Data.SQLiteClient, the namespace was changed to
Community.Data.SQLiteClient and the following files were modified to
make use of csharp-sqlite:
SqliteConnection.cs
SqliteCommand.cs
SqliteDataAdapter.cs
SqliteDataReader.cs
Sqlite.cs
Let me know what you think! :)
Regards
Dan
Daniel Morgan
I have a few suggestioons for SQLite and SQLiteClient. Please really
consider the 1st item.
1. Can you default the version to 3 in SQLiteClient please? It is
silly to default to version 2 when the managed csharp-sqlite database
engine will always be version 3 or higher.
Otherwise, people will get strange errors. I also would not allow
them to set the version to 2.
2. Add a property ServerVersion which returns a String to
SqliteConnection
This would call sqlite3_libversion to get the version string.
3. If you made major changes to a file, add your name to the list of
Authors and add a copyright, year, your name or company.
If it is a new file, also add the MIT X11 license text to the top
of the file.
4. Remove old commented code that was originally there. It just
clutters the code. If someone needs to see what it is was before,
they can look at history in the repository.
5. Add some examples or tests of the SQLiteClient. See below for an
example.
6. Do you intend to support the old NET_1_1 profile?
If not, we could remove that support. However, at least support
the NET_2_0 profile at the minimum.
7. Delete sqlite.cs from the SQLiteClient because you no longer need
these DllImport attributes to the native sqlite library anymore.
8. Add missing GetSchema methods to SqliteConnection. You can see
examples in System.Data.SqlClient in Mono.
And take a look in mono's svn repository for the sqliteprovider in
monodevelop.database.
trunk/monodevelop/extras/MonoDevelop.Database/
MonoDevelop.Database.Sql.Sqlite/SqliteSchemaProvider.cs
9. Add missing methods to SqliteDataReader based on
System.Data.SqlClient.SqlDataReader
GetProviderSpecificFieldType, GetProviderSpecificValue, and
GetProviderSpecificValues
10. This item was already like this in mono. NextResult in
SqliteDataReader is implemented wrong because it should be getting the
next result set, not read the next row.
Read should read the next row, not get the next result set. I'm
not sure if SQLite allows multiple result sets resulting from an
execution.
11. When you zip up the source and binaries, can you include all of
the stuff that is in this HG repository for csharp-sqlite including
performance and unit tests?
12. Someone could add a solution/projects for MonoDevelop.
13. Create makefiles so mono/gmcs can build it? This may not be
necessary if 12 is done.
Example:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text;
using Community.Data.SQLiteClient;
namespace SQLiteClientTests
{
public class SQLiteClientTestDriver
{
public void Test1()
{
Console.WriteLine("Test1 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}",
dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
SqliteCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER,
COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB,
COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB,
COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT COLA, COLB, COLC FROM
TEST_TABLE";
SqliteDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
Console.WriteLine(" COLA: {0}", i);
string s =
reader.GetString(reader.GetOrdinal("COLB"));
Console.WriteLine(" COLB: {0}", s);
DateTime dt =
reader.GetDateTime(reader.GetOrdinal("COLC"));
Console.WriteLine(" COLB: {0}", dt.ToString("MM/dd/
yyyy HH:mm:ss"));
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
SqliteCommand command = new SqliteCommand("PRAGMA
table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
public void DisplayDataTable(DataTable table, string name)
{
Console.WriteLine("Display DataTable: {0}", name);
int r = 0;
foreach (DataRow row in table.Rows)
{
Console.WriteLine("Row {0}", r);
int c = 0;
foreach (DataColumn col in table.Columns)
{
Console.WriteLine(" Col {0}: {1} {2}", c,
col.ColumnName, col.DataType);
Console.WriteLine(" Value: {0}", row[col]);
c++;
}
r++;
}
Console.WriteLine("Rows in data table: {0}", r);
}
public static int Main(string[] args)
{
SQLiteClientTestDriver tests = new
SQLiteClientTestDriver();
tests.Test1();
tests = null;
return 0;
}
}
}
Daniel Morgan
Teravus had already removed the platform invoke functions. Only thing
that was left was the SqliteError enum. We still need that enum.
Can it be restored, but renamed as SqliteError.cs ?
Noah Hart
2) Done
3) OK
4) Will do as time allows
5) Excellent Suggestion, done
6) Didn't see any references to NET_1_1 at all
7) Done, Created file SQLiteErrors
8-10 Todo
11) OK
12) Volunteers?
Noah
Kevin Gadd
fine. However, there are some errors that prevent compilation in mono
2.6. I don't really have time to dig into fixing them right now since
I'm not a linux expert and I'm not using it on linux, but I'll make
sure to figure out how to fix them the next time I'm messing with the
library.
Here are the errors if anyone wants to fix them - I can easily try
building again if fixes get checked into trunk. Alternately, these
might represent a bug in mono.
root@localhost:~/hg/csharp-sqlite/Community.Data.SQLiteClient# mdtool
build Community.Data.SQLiteClient.sln
/root/hg/csharp-sqlite/C#-SQLite/src/date_c.cs(90,0): error CS1032:
Cannot define or undefine preprocessor symbols after first token in
file
/root/hg/csharp-sqlite/C#-SQLite/src/sqliteLimit_h.cs(163,0): error
CS1032: Cannot define or undefine preprocessor symbols after first
token in file
/root/hg/csharp-sqlite/C#-SQLite/src/sqliteLimit_h.cs(178,0): error
CS1032: Cannot define or undefine preprocessor symbols after first
token in file
-kg
Noah
changeset: 46:50d0f8e77f2c
tag: tip
user: Noah
date: Wed Feb 17 21:36:08 2010 -0800
Please review when you get a moment.
Noah
On Feb 17, 5:52 pm, Daniel Morgan <monodanm...@gmail.com> wrote:
> I was wrong about deleting Sqlite.cs in Community.Data.SQLiteClient
>
> Teravus had already removed the platform invoke functions. Only thing
> that was left was theSqliteErrorenum. We still need that enum.
Noah
changeset: 47:434e124bc785
tag: tip
user: Noah
date: Wed Feb 17 21:49:40 2010 -0800
files: C#-SQLite/src/date_c.cs C#-SQLite/src/sqliteLimit_h.cs
description:
Fix for issue # 51
Mono complains about the following even if the #undef is never
compiled
#if SQLITE_MAX_DEFAULT_PAGE_SIZE //
SQLITE_MAX_DEFAULT_PAGE_SIZE>SQLITE_MAX_PAGE_SIZE
# undef SQLITE_MAX_DEFAULT_PAGE_SIZE
const int SQLITE_MAX_DEFAULT_PAGE_SIZE SQLITE_MAX_PAGE_SIZE
#endif
The fix is simply the following, esp since the #undef will NEVER be
compiled
//# undef SQLITE_MAX_DEFAULT_PAGE_SIZE
Noah
Daniel Morgan
#define NET_2_0
// do something that only works for .NET 2.0 or higher
#else
// otherwise, do something that will only work with .NET 1.1 or
1.0
#endif
In your sqlite port to C#, you are using partial classes which means
it will only work with .net 2.0 or higher. Therefore, we do not need
to support the .NET 1.1 / 1.0 profile in the SqliteClient.
Noah
We've been compiling without the NET_2_0 defines, since it was not in
the project.
When I add the #define for NET_2_0, I get
errors like "The type or namespace name 'DbConnectionFactory' could
not be found (are you missing a using directive or an assembly
reference?)"
Am I missing a file in the solution?
Also, I get errors on the lines
[MonoTODO]
Ideas?
Noah
Teravus Ovares
wouldn't work without some more work in .NET 2.0 :).
>This is only set-up for .NET 3.5. It will not currently work for
>NET 2.0. This also only supports the SQLite Version 3+ API, not 2.
Work is demanding my attention though, so I'm unable to work on it
more at this time.
Regards
Dan
Noah
On Feb 22, 1:20 pm, Teravus Ovares <tera...@gmail.com> wrote:
> It wasn't designed for.NET 2.0. I previously stated that it
> wouldn't work without some more work in .NET 2.0 :).
>
> >This is only set-up for .NET 3.5. It will not currently work for
> >NET 2.0. This also only supports the SQLite Version 3+ API, not 2.
>
> Work is demanding my attention though, so I'm unable to work on it
> more at this time.
>
> Regards
>
> Dan
>
Teravus Ovares
to work with 2.0.. You'd just have to make sure the whole project
was set up for 2.0. The NET_2_0 defines are part of the way that the
Mono.Data.SQLiteClient originally worked. They key to it, however,
is to make sure that the original SQLite native defines are mapped to
the proper csharp-sqlite methods. It compiles fine in 3.5 because
the compiler excludes the code inside the if #NET_2_0. After work
lets up, I'll have time to evaluate it further.
Regards
Dan
Daniel Morgan
of the native sqlite library, how come you didn't use what was found
at mcs\Mono.Data.Sqlite\Mono.Data.Sqlite_2.0 in mono's svn?
This Mono.Data.Sqlite_2.0 already has the NET_2_0 profile defines
working correctly. The Banshee media player uses this on Linux.
Mono.Data.Sqlite_2.0 is based on System.Data.SQLite.
On Feb 22, 5:57 pm, Teravus Ovares <tera...@gmail.com> wrote:
> Just a point of note, it probably wouldn't be too difficult to get it
> to work with 2.0.. You'd just have to make sure the whole project
> was set up for 2.0. The NET_2_0 defines are part of the way that the
> Mono.Data.SQLiteClient originally worked. They key to it, however,
> is to make sure that the original SQLite native defines are mapped to
> the proper csharp-sqlite methods. It compiles fine in 3.5 because
> the compiler excludes the code inside the if #NET_2_0. After work
> lets up, I'll have time to evaluate it further.
>
> Regards
>
> Dan
>
Teravus Ovares
Mono.Data.SQLiteClient namespace because I already had a wealth of
code to test against.
An alternative solution would be for you to take what I've done with
Mono.Data.SQLiteClient and implement it in Mono.Data.Sqlite.
Regards
Dan
Daniel Morgan
UnsafeNativeMethods. Sqlite3.cs wraps these external methods and
provides an internal API for the rest of the provider.
SqliteConnection and SqliteCommand and SqliteDataReader use this
internal API in Sqlite3. So, most of the porting to use csharp-sqlite
would be done in Sqlite3 and UnsafeNativeMethods.
Since Mono.Data.Sqlit_2.0 is based on System.Data.SQLite, I thought I
would post a link on how someone got System.Data.SQLite to build for
Visual Studio 2010 Beta2.