SQLite RevEng: Sample data to determine CLR type

[bricelam]

Reverse engineering SQLite column types is tricky due to it's dynamic typing. The column type hold no real meaning.

Today, we apply the same type affinity rules that SQLite does to the specified column type name to put it into one of the four primitive types (which map to long, double, string & byte[]).

I think we could do a much better job by sampling the data to determine a suitable CLR type. This would allow us to reverse engineer types that need to be coerced like decimal, DateTime and Guid. We could also provide a more natural numeric type like int if the data fits.

[bricelam]

@ErikEJ was that a 👎 to doing this? or because SQLite is weird.

[ErikEJ]

It was actually to doing it, it will give users a false sense of strong typing

[bricelam]

I imagine something like this to get the average field type:

SELECT typeof(column1)
FROM table1
WHERE typeof(column1) != 'null'
GROUP BY typeof(column1)
ORDER BY count() DESC
LIMIT 1;

If INTEGER or REAL, use max() and min() to get the range.
If BLOB, use length() to see if it's a GUID.
If TEXT, use datetime() and time() to see if it parses. Maybe length() can indicate a GUID. Maybe CAST..AS REAL can indicate a decimal.

[jonreis]

Hello @bricelam, question regarding where this is going please. If I have the following table definition:

CREATE TABLE [Schema] (
[SchemaId] INTEGER NOT NULL
, [SchemaVersion] nvarchar(16) NOT NULL
, [DatabaseVersion] int NOT NULL
, [Expression] ntext NULL
, [Description] nvarchar(1024) NULL
, [ReadOnly] bit NOT NULL
, [ModifiedDateTime] datetime NOT NULL
, CONSTRAINT [sqlite_master_PK_Schema] PRIMARY KEY ([SchemaId])

In EF6
bit => bool
int => int
datetime => datetime

EF Core
bit => string
int => long
datetime => string

Are you saying this is how it will be in the future, or is it going to get fixed post 2.1 preview 1?

I'm assuming EF Core would follow the same rules as EF6, which seemed to work well in this regard:

https://system.data.sqlite.org/index.html/artifact/18f60c317bbdfb16

///

/// SQLite is inherently un-typed. All datatypes in SQLite are natively strings. The definition of the columns of a table
/// and the affinity of returned types are all we have to go on to type-restrict data in the reader.

[bricelam]

I'm assuming EF Core would follow the same rules as EF6

No. In SQLite, column type names mean nothing*. System.Data.SQLite added additional semantics to them to map them CLR types.

Early on, we decided that Microsoft.Data.Sqlite and EF Core's SQLite provider should not add these additional semantics instead exposing the underlying SQLite behavior more directly.

This issue is about looking the actual data to determine the CLR type. The column type names would continue to just be application-defined strings with application-only semantics.

To illustrate the problem, EF6 and System.Data.SQLite map columns with type FLOAT to System.Double, but most database created outside of EF6 probably intended those columns to map to System.Single. For further discussion, see aspnet/Microsoft.Data.Sqlite#457

By continuing to ignore the column type and going directly to the data, we could see that a column contains only 1s and 0s and map it to System.Bool. The column type could be whatever the application thinks is best: BIT, BOOL, BOOLEAN, FLAG, YESNO, SWITCH, etc.

* Column type names are actually used by SQLite to determine type affinity, but that really only affects the on-disk format.

[bricelam]

For additional reference, System.Data.SQLite's column to EDM type mapping is specified here.

[bricelam]

The Microsoft.Data.Sqlite Data Type Mappings might also be interesting.

[jonreis]

Hi Brice. Thank you for the reply. Please bear with me I am new to EF Core and I just spent a couple of days attempting to port a relatively simple data model from EF6 to EF Core 2.1, just to see where the pain points will be. I was expecting a simpler port, but found that it was more difficult due not being able to be involved in the code generation (solved with EF Core Power Tools) and the changes to how data types are being presented by EF Core.

This issue is about looking the actual data to determine the CLR type. The column type names would continue to just be application-defined strings with application-only semantics.

So I can only reverse engineer a database that is fully populated? The old way of defining datatypes within the table definition seems less error-prone and more declarative. If I define a column as

, [ModifiedDateTime] datetime NOT NULL

If I use Dapper, EF6, or any other micro-ORM I get a datetime. Why would I want the data to come back as a string and write code to make the conversion manually? Also, according the Sqlite documentation, datetimes can be stored as REAL, INTEGER, or STRING in the database. How are you going to determine that a REAL, or INTEGER is really meant to be a datetime?

The column type names would continue to just be application-defined strings with application-only semantics.

What would the application want to do with the column type names? I'm pretty sure they want their ORM to use it to interpret the types for them rather than having to write the conversion code themselves?

One last point on this. With EF6, when I moved my application from SqlServerCe to Sqlite, I did not have to change anything. I was pretty much insulated by EF to the specifics of how things were stored in the database. Now what I hear is that this is no longer the case. We need to change our applications to conform to the semantics of the database. Is this correct?

[bricelam]

You can change the CLR types after reverse engineering.

How are you going to determine that a REAL, or INTEGER is really meant to be a datetime?

We won't. You'll have to update the type manually if you know they're datetime values.

they want their ORM to use it to interpret the types for them

We could apply heuristics, but since the type names could literally be anything (including no type name), we would constantly be getting issues to update the heuristics to match/not match every new type name somebody's application decided to use. SQLite does not apply semantics to these names so we don't want to either.

Reverse Engineering is a best guess at what we think you want your domain model to look like. Since SQLite only has four primitive data types, there is A LOT of information lost when looking just at the database. This issue is about making a more educated guess, but at the end of the day it's still just a guess and you'll probably have to provide the missing information by manually updating the model after it's generated.

This is one place where reverse engineer templates can help. If you have the column type names, you can map to the appropriate CLR types in the template.