Bug 5577 - Mono.Data.Sqlite: column types aren't preserved
Summary: Mono.Data.Sqlite: column types aren't preserved
Status: RESOLVED FIXED
Alias: None
Product: Class Libraries
Classification: Mono
Component: Mono.Data.Sqlite ()
Version: master
Hardware: PC Mac OS
: --- normal
Target Milestone: Untriaged
Assignee: Rolf Bjarne Kvinge [MSFT]
URL:
Depends on:
Blocks:
 
Reported: 2012-06-07 17:08 UTC by Jonathan Pryor
Modified: 2012-08-09 16:26 UTC (History)
5 users (show)

Tags:
Is this bug a regression?: ---
Last known good build:

Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on GitHub or Developer Community with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED FIXED

Description Jonathan Pryor 2012-06-07 17:08:35 UTC
This is similar to #5078.

Create a new project, add an assembly reference to Mono.Data.Sqlite.dll, and run the following code:


	string dbPath = System.IO.Path.Combine(
			System.Environment.GetFolderPath(
				System.Environment.SpecialFolder.Personal),
			"testdb.db");
	bool exists = System.IO.File.Exists (dbPath);
	var conn = new SqliteConnection ("Data Source=" + dbPath);
	conn.Open ();
	if (exists) {
		using (var c = conn.CreateCommand ()) {
			c.CommandText = "DROP TABLE TESTTABLE";
			c.ExecuteNonQuery ();
		}
	}
	using (var c = conn.CreateCommand ()) {
		c.CommandText =
			"CREATE TABLE TESTTABLE (DATA guidblob not null)";
		c.ExecuteNonQuery ();
	}
	using (var c = conn.CreateCommand ()) {
		c.CommandText = "SELECT * FROM TESTTABLE";
		using (var r = c.ExecuteReader ()) {
			string typeName = r.GetDataTypeName(0);
			Console.WriteLine ("TESTTABLE.DATA type name: {0}",
					typeName);
			if (typeName != "guidblob")
				throw new InvalidOperationException (
					"Expected type 'guidblob'; got type: " +
					typeName);
		}
	}

Expected behavior: no exception; what we get out is what we get in. This is reportedly the behavior in MfA 4.0.7:

http://lists.ximian.com/pipermail/monodroid/2012-June/010721.html

Actual behavior: `typeName` is "Object", not "guidblob".
Comment 1 Jonathan Pryor 2012-06-07 17:13:43 UTC
This also appears to be a regression on regular mono. Take the above, stick into ye olde console Main(), and run.

Mono 2.10.9:

> $ mono --version
> Mono JIT compiler version 2.10.9 (tarball Mon May  7 20:25:51 EDT 2012)
> $ mono sql-types.exe 
> TESTTABLE.DATA type name: guidblob

i.e. it works

Mono master:

> $ mono --version
> Mono JIT compiler version 2.11.2 (master/f78e6f8 Thu May 31 09:58:26 EDT 2012)
> Copyright (C) 2002-2012 Novell, Inc, Xamarin Inc and Contributors. www.mono-project.com
> $mono sql-types.exe
> TESTTABLE.DATA type name: Object
> 
> Unhandled Exception: System.InvalidOperationException: Expected type 'guidblob'; got type: Object
>   at Test.Main () [0x00000] in <filename unknown>:0 

I.e. it fails, just as it does in Mono for Android 4.2.3.
Comment 2 Marek Habersack 2012-06-19 06:18:02 UTC
I can confirm that it does work with Mono 2.10.9 as well as the upstream version of the library (System.Data.SQLite from phxsoftware.com)
Comment 3 Rolf Bjarne Kvinge [MSFT] 2012-06-19 10:14:51 UTC
Fixed.

master: b6fee6af5dad8efa6705b485b5dfbd7c3d001946
2.10: 32f743c2e0134c38dbcc45a0fd5458aa9e47033c
mobile-master: 48fadd0fb8aa016723cbe7e726b2907ef1515b81
Comment 4 Robin 2012-08-08 04:21:13 UTC
Sadly this bug hasn’t been fixed. The specific example with “guidblob” works, but any other type still gives unexpected results. We’ve seen the following:
Table declaration	=>	SqliteDataReader.GetDataTypeName()
int32 null	=>	“object”
int32 not null	=>	“int64” but sometimes also “object”
uint1 not null	=>	“int64” but sometimes also “object”
… we could continue indefinitely with custom types. The problem is that the implementation of SqliteDataReader.GetDataTypeName() is wrong. This should always return the result of UnsafeNativeMethods.sqlite3_column_decltype(), however this is not always the case (only when SqliteDataReader.GetSQLiteType() returns DbType.Object). This bug will be resolved when you remove the following code from SqliteDataReader.GetDataTypeName():
	SQLiteType sQLiteType = this.GetSQLiteType(i);
	if (sQLiteType.Type == DbType.Object)
	{
		return SqliteConvert.SQLiteTypeToType(sQLiteType).Name;
	}

We rely on the fact that SqliteDataReader.GetDataTypeName() returns the defined column name (as in the CREATE TABLE statement).


Also, on a side-note: in our case SqliteDataReader.GetSQLiteType() should NOT be called, but this method doesn’t work correctly according to the SQLite specifications. The problem lies in SqliteConvert.TypeNameToDbType(), which compares a fixed list of SQLite typenames to determine the DbType. SQLite determines the type by checking if the typename CONTAINS a part of the type name. Please check part 2.1 here: http://www.sqlite.org/datatype3.html

2.1 Determination Of Column Affinity

The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:

 1.   If the declared type contains the string "INT" then it is assigned INTEGER affinity.

 2.  If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.

 3.  If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity NONE.

  4.  If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.

  5.  Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.
Comment 5 Rolf Bjarne Kvinge [MSFT] 2012-08-09 09:13:16 UTC
Sqllite's loosely typed structure doesn't map very well to the managed data model, and it's been extended a bit to make a bit more sense. For instance TIMESTAMP and DATETIME map to System.DateTime instead of System.Decimal (which it would be according to rule 5).

As a curiosity your column type INT32 will actually map to System.Int64 (since it matches rule 1).

I believe I have fixed it better this time, hopefully it works properly now.
master: 74f598ce37222d5916f7f8788f09da533df7a1d0
2.10: c1288989c967ff5f277ad5a272065ebd13c292ae
mobile-master: 98597fedba8b626e76346dbd0d44bdbbc7cdcabb
Comment 6 Robin 2012-08-09 10:36:20 UTC
So now SqliteDataReader.GetDataTypeName() will return the actual defined column type ?
When and in which version can we expect this change ? (will it be in 4.2.5 ?)

We had the same problems with the loosly typed structure and that’s why we need the SQLite’s defined type. So, although “int32” will map to a System.Int64 we can store it in a System.Int32 in our own DataTable class. This saves a lot of memory, especially with booleans. In our case a boolean column with 1024 rows will only take up 128 bytes instead of 8KB.
Comment 7 Jonathan Pryor 2012-08-09 16:26:48 UTC
> So now SqliteDataReader.GetDataTypeName() will return the actual defined column type

Give or take. If I use mono/master as a guide and update the test app to create this table:

> c.CommandText = "CREATE TABLE TESTTABLE (DATA guidblob not null, A int32 null, B int32 not null, C uintl not null)";

Then alter output:

	for (int i = 0; i < 4; ++i) {
		string typeName = r.GetDataTypeName(i);
		Console.WriteLine ("TESTTABLE.DATA type name: {0}", typeName);
	}

The updated output:

> TESTTABLE.DATA type name: guidblob
> TESTTABLE.DATA type name: int32
> TESTTABLE.DATA type name: int32
> TESTTABLE.DATA type name: uintl

So "null" vs. "not null" is lost (both "int32 null" and "int32 not null" are mapped to "int"), but "uintl" is preserved.

As for Mono for Android, this should make it into the _stable_ 4.2.5 build. It is not in the current alpha build.