Would an ORM have any way of determining that a SQLite column contains date-times or booleans?

Question

I've been thinking about using SQLite for my next project, but I'm concerned that it seems to lack proper datetime and bit data types.

If I use DbLinq (or some other ORM) to generate C# classes, will the data types of the properties be "dumbed down"? Will date-time data be placed in properties of type string or double? Will boolean data be placed in properties of type int?

If yes, what are the implications? I'm envisioning a scenario where I need to write a whole second layer of classes with more specific data types and do a bunch of transformations and casts, but maybe it's not as bad as I fear. If you have any experience with this or a similar scenario, how did you handle it?

Answer 1

SQLite itself recognizes only five data types: NULL, INTEGER, REAL, TEXT, and BLOB. But it lets you declare any type name that you want, so you can write

CREATE TABLE SomeTable (
   TimeAdded DATETIME,
   SomeFlag  BOOLEAN
);

and have your ORM interpret the types the way you want to.

I've written a C++ wrapper around SQLite, and took the different approach of representing all database values with a variant type. This variant provides conversions between different types, so SqlValue("2010-05-03 01:01:04").AsTimestamp() gives the expected timestamp object.