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?

Turnedon

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.

Gonzalez

Just using datetime and bit as data types did the trick with DbLinq, thanks. – Turnedon

Answer 2

I'm using System.Data.SQLite and from Design tools in VS2008, it gives me DateTime and Bit data type. Although I've not much idea about DbLinq. But DateTime and Bit datatypes are supported by SqlLite

Danieldaniela

Answer 3

The System.Data.SQLite ADO.NET provider contains a SQLiteMetaDataCollectionNames class that allows you to retrieve the various meta-data about the columns in a specific table.

It includes a 'DataTypes' property that describes the 'hint' data type (SQLite doesn't enforce strict typing) of each column. Using this would allow an ORM to then perform the required conversion to/from the equivalent C# type.

Disport

Answer 4

I worked on an adapter for SQLite in Zend Framework.

To get metadata and data types for an SQLite table, I used PRAGMA TABLE_INFO( <tablename> ) and parsed its output.

Faunus