For the last couple months I've been working on the ADO.NET vNext provider for SQLite with support for eSQL, LINQ, etc (aka the Entity Framework).
I had a eureka moment today while struggling with the ADO.NET vNext bits as it relates to SQLite's limited type support. I thought I'd share, and see if there's any interest in maybe modifying this idea to make it work in the core engine through perhaps an enhancement to the sqlite3_decltype() functionality. The crux of all of my problems are that SQLite only understands a limited number of types. The ADO.NET Provider tries to provide a much broader type support system by inspecting the table definition during queries to do automatic conversions from SQLite's limited types to a much broader set of types. However, I can only do so much. In the case of this query: SELECT CAST(1 AS int), CAST (2 as smallint), CAST (3 as int) The only information I have is the values. They're going to be INT64 because SQLite tells me they are INT64 and I have no way to narrow the types down to something more accurate because there's no table definition for me to refer to. Any time you use an aggregate function or some other function to return a value that isn't backed directly to a table, the ADO.NET provider has no way of providing enhanced types for you. SO, I decided to try something outside the box. I added pre-parsed SQL statement "TYPES", that gets processed when statements are prepared in the provider. You can put it directly above any SELECT statement that you want, to provide type-specific information about the columns a subsequent query is going to produce. Here's an example: TYPES integer, smallint, float; SELECT 1, 2, 3; When the ADO.NET provider compiles these statements, it'll preparse the TYPES keyword and apply those types to the SELECT statement that follows the TYPES statement. Now when the provider executes the SELECT, it will return 1, 2 and 3 as Int64, Int16 and Double. I buried this code into the functions that wrap the sqlite3_decltype() function. So I got to wondering how hard this would be to implement in the engine itself? When you called sqlite3_decltype() on a statement like the above, it could return the types declared in the TYPES statement rather than blank. Robert ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
