aspnet / Microsoft.Data.Sqlite

[Archived] SQLite implementations of the System.Data.Common interfaces. Project has moved to https://github.com/aspnet/EntityFrameworkCore
https://docs.microsoft.com/dotnet/api/microsoft.data.sqlite
Apache License 2.0
347 stars 97 forks source link

Implement DbConnection.GetSchema #443

Closed Thorium closed 7 years ago

Thorium commented 7 years ago

Is there a way to get basic meta data information from the database,?

I would actually need just need initial version of System.Data.Common style GetSchema supporting these:

That would be enough, as table column names and primary key info can be gotten from pragma table_info.

Thorium commented 7 years ago

Data types are constant:

DataType, TypeName, ProviderDbType
'System.Int16','smallint','10'
'System.Int32','int','11'
'System.Double','real','8'
'System.Single','single','15'
'System.Double','float','8'
'System.Double','double','8'
'System.Decimal','money','7'
'System.Decimal','currency','7'
'System.Decimal','decimal','7'
'System.Decimal','numeric','7'
'System.Boolean','bit','3'
'System.Boolean','yesno','3'
'System.Boolean','logical','3'
'System.Boolean','bool','3'
'System.Boolean','boolean','3'
'System.Byte','tinyint','2'
'System.Int64','integer','12'
'System.Int64','counter','12'
'System.Int64','autoincrement','12'
'System.Int64','identity','12'
'System.Int64','long','12'
'System.Int64','bigint','12'
'System.Byte[]','binary','1'
'System.Byte[]','varbinary','1'
'System.Byte[]','blob','1'
'System.Byte[]','image','1'
'System.Byte[]','general','1'
'System.Byte[]','oleobject','1'
'System.String','varchar','16'
'System.String','nvarchar','16'
'System.String','memo','16'
'System.String','longtext','16'
'System.String','note','16'
'System.String','text','16'
'System.String','ntext','16'
'System.String','string','16'
'System.String','char','16'
'System.String','nchar','16'
'System.DateTime','datetime','6'
'System.DateTime','smalldate','6'
'System.DateTime','timestamp','6'
'System.DateTime','date','6'
'System.DateTime','time','6'
'System.Guid','uniqueidentifier','4'
'System.Guid','guid','4'

Tables can be gotten from the query:

SELECT type as TABLE_TYPE, 'main' as TABLE_CATALOG, name as TABLE_NAME FROM sqlite_master WHERE type='table';

...and from that, for each tablename:

TABLE_NAME tablename

Get pragma foreign_key_list(tablename)

And from that:

FKEY_TO_CATALOG 'main'
TABLE_CATALOG 'main'
FKEY_TO_TABLE table
FKEY_TO_COLUMN to
CONSTRAINT_NAME 'fk' + TABLE_NAME + id

Would you accept a PR (C#) or should I just create the code for my own use (F#)?

Thorium commented 7 years ago

Ok, got it working already!

    let GetSchema name conn =
        let dt = new DataTable(name)
        match name with
        | "DataTypes" -> 
            dt.Columns.AddRange([|"DataType",typeof<string>;"TypeName",typeof<string>;"ProviderDbType",typeof<int>|]|>Array.map(fun (x,t) -> new DataColumn(x,t)))
            let addrow(a:string,b:string,c:int) = dt.Rows.Add([|box(a);box(b);box(c);|]) |> ignore
            [   "System.Int16","smallint",10
                "System.Int32","int",11
                "System.Double","real",8
                "System.Single","single",15
                "System.Double","float",8
                "System.Double","double",8
                "System.Decimal","money",7
                "System.Decimal","currency",7
                "System.Decimal","decimal",7
                "System.Decimal","numeric",7
                "System.Boolean","bit",3
                "System.Boolean","yesno",3
                "System.Boolean","logical",3
                "System.Boolean","bool",3
                "System.Boolean","boolean",3
                "System.Byte","tinyint",2
                "System.Int64","integer",12
                "System.Int64","counter",12
                "System.Int64","autoincrement",12
                "System.Int64","identity",12
                "System.Int64","long",12
                "System.Int64","bigint",12
                "System.Byte[]","binary",1
                "System.Byte[]","varbinary",1
                "System.Byte[]","blob",1
                "System.Byte[]","image",1
                "System.Byte[]","general",1
                "System.Byte[]","oleobject",1
                "System.String","varchar",16
                "System.String","nvarchar",16
                "System.String","memo",16
                "System.String","longtext",16
                "System.String","note",16
                "System.String","text",16
                "System.String","ntext",16
                "System.String","string",16
                "System.String","char",16
                "System.String","nchar",16
                "System.DateTime","datetime",6
                "System.DateTime","smalldate",6
                "System.DateTime","timestamp",6
                "System.DateTime","date",6
                "System.DateTime","time",6
                "System.Guid","uniqueidentifier",4
                "System.Guid","guid",4 ] |> List.iter(addrow)
            dt
        | "Tables" -> 
            dt.Columns.AddRange([|"TABLE_TYPE";"TABLE_CATALOG";"TABLE_NAME"|]|>Array.map(fun x -> new DataColumn(x)))

            let query = "SELECT type as TABLE_TYPE, 'main' as TABLE_CATALOG, name as TABLE_NAME FROM sqlite_master WHERE type='table';"

            use com = new SqliteCommand(query,conn)
            use reader = com.ExecuteReader()
            while reader.Read() do
                dt.Rows.Add([|box(reader.GetString(0));box(reader.GetString(1));box(reader.GetString(2));|]) |> ignore
            dt
        | "ForeignKeys" ->
            let tablequery = "SELECT name as TABLE_NAME FROM sqlite_master WHERE type='table';"
            let tables = 
                use com = new SqliteCommand(tablequery,conn)
                use reader = com.ExecuteReader()
                [while reader.Read() do yield reader.GetString(0)]

            dt.Columns.AddRange([|"TABLE_NAME";"FKEY_TO_CATALOG";"TABLE_CATALOG";"FKEY_TO_TABLE";"FKEY_FROM_COLUMN";"FKEY_TO_COLUMN";"CONSTRAINT_NAME"|]|>Array.map(fun x -> new DataColumn(x)))

            tables |> List.iter(fun tablename ->
                let query = sprintf "pragma foreign_key_list(%s)" tablename
                use com = new SqliteCommand(query,conn)
                use reader = com.ExecuteReader()
                while reader.Read() do 
                    dt.Rows.Add([|box(tablename); box("main"); box("main"); box(reader.GetString(2));box(reader.GetString(3));box(reader.GetString(4));box("fk_"+tablename+reader.GetString(0));|]) |> ignore
            )
            dt
        | _ -> failwith "Not supported. This custom getSchema will be removed when the corresponding System.Data.Common interface is supported by the connection driver. "
Thorium commented 7 years ago

The DbConnection.GetSchema method documentation.

The optional second parameter is restriction which is is described here.

The full support is a lot of work, so I would prefer a start with simple cases (as described above) and throw NotSupportedException on runtime for collection names that are not yet implemented.

AlexanderTaeschner commented 7 years ago

Could you explain what the number in the ProviderDbType column means (Sqlite has only five types: NULL, INTEGER, REAL, TEXT, and BLOB)? Where does the names in the TypeName column come from (they are not Sqlite types)? In general you have to be careful with a translation table since some types (e.g. GUID) can be stored in different ways inside the database (although for new write using this library only a specific format is used).

Thorium commented 7 years ago

The response of GetSchema("DataTable") is not specific to any physical database. So the output result of "DataType","TypeName","ProviderDbType" is just made similar to the official System.Data.SQLite.dll library. As you might want the migration process for your users to this library be as painless as possible.

Thorium commented 7 years ago

The number is the System.Data common enumeration DbType code as number.

https://msdn.microsoft.com/en-us/library/system.data.dbtype(v=vs.110).aspx

bricelam commented 7 years ago

@Thorium what is consuming this information?

I've always hated DbConnection.GetSchema() because of its poor semantics. The closest thing to a specification is the common schema collections that the built-in providers System.Data.Odbc, System.Data.OleDb, System.Data.OracleClient (deprecated), and System.Data.SqlClient use, but ADO.NET providers are free to deviate completely form this. Also, the common set isn't very useful since it doesn't even include tables.

DbDataReader.GetSchemaTable() improved a lot the much better defined GetColumnSchema() API was added over the top of it.

I'd like to look at current polymorphic uses of this API to see what information is useful and, more-or-less standard across providers.

Using that information, let's decide what collections, columns, and filters to include, what the data should be, and where the data should come from.

I'm OK implementing this in parts.

One consumer of this API could be EF Core we currently query the information directly (see SqliteDatabaseModelFactory) to populate the DatabaseModel.

bricelam commented 7 years ago

+1 to @AlexanderTaeschner's comment. We try and maintain a purist representation of SQLite in this provider and not add any arbitrary semantics to it. The only types listed should be INTEGER, REAL, TEXT, and BLOB.

Thorium commented 7 years ago

@bricelam, I needed the GetSchema() support for a tool giving a data access to language called F#. Micorosoft.Data.Sqlite was needed for .NET Core support. But I already got everything working with the custom script above. So the PR was just to help this library with this missing feature.

bricelam commented 7 years ago

lol, I've hard of it. 😄 I think markrendle/Simple.Data will have very similar requirements.

bricelam commented 7 years ago

A couple fundamental questions:

  1. Should the Tables collection contain both tables and views?
  2. Should the foreign key data be split into two collections: ForeignKeys and ForeignKeyColumns?
bricelam commented 7 years ago

Should any of this information from PRAGMA foreign_key_list be included?

bricelam commented 7 years ago

Should any of this information from sqlite_master be included in tables/views?

bricelam commented 7 years ago

Additional sources of metadata:

Thorium commented 7 years ago

As this is totally new functionality, I think there is no one right answer: There will be no existing users complaining about API changes yet. So I would go for the simplest solution first, and add more items later. The other approach is to be consistent with other database providers, mainly System.Data.MySqlite.

And this is functionality that can be used to build very dynamic and complex enterprise systems but I don't see that there would be huge amount of .GetSchema() users for next few years and for that reason the improvements can be taken as PRs.

bricelam commented 7 years ago

I'm really starting to question the value of any of this. Calling this API requires you to know provider-specific details. Why bother creating provider-specific views over sqlite_master and these PRAGMAs when you could just query them directly?

Instead of inventing a new concept, let's push users to the underlying concept so their knowledge transfers to all SQLite-based technologies.

Implementing DbDataReader.GetSchemaTable() was useful because the data is well-defined and can be used polymorphicly across providers. There are even a few first-class consumers of the API like DataTable.Load().

On the other hand, implementing DbConnection.GetSchema() feels like we're just adding a convoluted way to do something that's already possible.

bricelam commented 7 years ago

@divega Do you have any thoughts on this?

Thorium commented 7 years ago

"DataTypes" is not available. Foreign key mapping is just hard to find in reliable way. Others are not so important.

ErikEJ commented 7 years ago

@bricelam In my tools I use three ADO.NET providers (SQLite classic, SqlCe and SqlClient) that support GetSchema(), but I do not use that with any of them to get schema information, as what the views provide is not detailed/granular/useful/correct enough.

bricelam commented 7 years ago

I had the same thought about DataTypes, but wondered if a doc page about it would be equally as helpful.

bricelam commented 7 years ago

We discussed this as a team and decided that there is little-to-negative value in implementing this. The last effort to improve this area of ADO.NET (see issue dotnet/corefx#3423) resulted in the better DbDataReader.GetTableSchema() API. Until the DbConnection.GetSchema() API is similarly improved, we'll push users toward querying for the metadata directly (as you've already done in fsprojects/SQLProvider).

Closing as "won't fix" for now, but we'll certainly re-evaluate if a better defined ADO.NET API is layered on top of GetSchema().

We're also working on adding some conceptual documentation for Microsoft.Data.Sqlite. We'll be sure to add a table of our type mappings.

Thorium commented 7 years ago

That's fine. :-)

On same level I find it ironic that Microsoft has designed a common API for everyone, and then Microsoft product team decides not to implement the Microsoft API because it's so poorly designed.

but that is nothing new

bricelam commented 7 years ago

Don't misunderstand my reasons for pushing back. It's not because it's a poorly designed API, its because there is already a more natural way of obtaining this information. If there was any value in adding this second way of doing it, we'd consider it. We just don't want to take on the cost of maintaining this without a good reason to.

The only possible value I can see is if we maintained compatibility with System.Data.SQLite. But, like many other features we've implemented so far, we'd probably do it differently based on our experiences of crafting .NET APIs and working with relational databases.

bricelam commented 7 years ago

To further illustrate my assessment of value: you could replace the old System.Data.SQLite code path with the new one you wrote for Microsoft.Data.Sqlie and it would work with both providers

Thorium commented 7 years ago

Yes, I don't disagree. I don't like GetSchema either. I just need the data. (Which is not in the Wiki yet ;-)

bricelam commented 7 years ago

I jotted down Data Type Mappings on the wiki for now.