System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: e87af1d06adc5bd02fed1722ff372935792c85fa
Title: New internal mappings break some behaviour
Status: Closed Type: Feature_Request
Severity: Important Priority: Blocker
Subsystem: Convert Resolution: Fixed
Last Modified: 2014-02-14 15:50:58
Version Found In: 1.0.90.0
User Comments:
anonymous added on 2014-01-29 10:18:44:
Database was created with 1.0.66 version and uses column type 'DATE ASC'. Utc DateTime values are there, so in code our queries returned DateTime objects with unspecified kind. We had to specify utc kind manually. Issue: in 1.0.89 version SQLite cannot more parse 'DATE ASC' and returns String objects (formatted in Utc). After conversion we get automatically localized datetime objects and still mark them as Utc, that is not correct.

Reason of issue is changed internal SQLite mappings engine. We could not find way to extend it with our own mapping rules, so we updated our code to correctly process both strings and datetimes to support old databases.

mistachkin added on 2014-01-29 11:05:26:
The type "DATE ASC" does not exist in the current mappings.  Can you try using
just "DATE" instead?

anonymous added on 2014-01-31 13:31:56:
Yes, we can for newly created databases. But we also have to support existent ones used locally by our product users..

mistachkin added on 2014-01-31 22:36:00:
In the original report, you stated that you have already modified your code to
support the old databases?

As far as I can tell, the type name "DATE ASC" was never officially supported by
System.Data.SQLite (at least not judging from the source code history).  I'm not
sure how it worked for you before.

mistachkin added on 2014-02-01 00:57:05:

Per-connection type name mappings implemented on trunk via check-in [d84ccf015e].

It would be very helpful if you could test this feature in your environment and let us know how it works for you.

To take advantage of this feature for your use-case, you will want to do the following:

1. Use the new "UseConnectionTypes" connection flag when opening connections.

2. Add the "DATE ASC" type name to the per-connection type mappings, like so:

// create and open connection...
connection.AddTypeMapping("DATE ASC", DbType.DateTime, false);

This feature will be present in the next release (1.0.91.0), currently scheduled for mid-February.


anonymous added on 2014-02-05 15:04:22:
Ok, thank you!
I'll test new SQLite version when possible. Currently we used a code patch to support old databases, so it is not a blocking issue for us.

In past, there were used several exotic column types in our databases: INTEGER UNSIGNED, LONGBLOB, INT2 UNSIGNED, DATE ASC. For 'DATE ASC' I suppose it worked because of previously used method TypeNameToDbType() in SQLite 1.0.66:
...
 if (string.Compare(Name, 0, _typeNames.typeName, 0, _typeNames.typeName.Length, StringComparison.OrdinalIgnoreCase) == 0)
            {
                return _typeNames.dataType;
            }
...
where it seems to be enough to contain 'DATE' in column type 'DATE ASC' to be mapped correctly.

anonymous added on 2014-02-14 15:50:58:
SQLite 1.0.91 works great, I was able to apply custom mapping:

connection.Open();
connection.Flags |= SQLiteConnectionFlags.UseConnectionTypes;
connection.AddTypeMapping("DATE ASC", DbType.DateTime, false);

and this solved the issue.