Implement DbConnection.GetSchema

[Thorium]

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:

• DataTypes:
  • DataType, TypeName, ProviderDbType
  • Gives the information how this provider maps types to Sqlite database
• Tables
  • TABLE_TYPE, TABLE_CATALOG, TABLE_NAME
  • List of tables in the database, type is only interesting not to be a SYSTEM_TABLE.
• ForeignKeys
  • CONSTRAINT_NAME, TABLE_CATALOG, TABLE_NAME, FKEY_TO_CATALOG, FKEY_TO_TABLE, FKEY_TO_COLUMN, FKEY_FROM_COLUMN
  • Information of relations between tables.

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

[Thorium]

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]

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]

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]

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]

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]

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]

@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]

+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]

@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]

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

[bricelam]

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]

Should any of this information from PRAGMA foreign_key_list be included?

• id
• seq
• on_update
• on_delete
• match

[bricelam]

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

• rootpage
• sql

[bricelam]

Additional sources of metadata:

• PRAGMA collation_list
• PRAGMA database_list
• PRAGMA index_info
• PRAGMA index_list
• PRAGMA index_xinfo
• PRAGMA table_info
• SELECT sqlite_master WHERE type IN ('index', 'trigger')
• sqlite3_table_column_metadata()

[Thorium]

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]

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]

@divega Do you have any thoughts on this?

[Thorium]

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

[ErikEJ]

@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]

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

[bricelam]

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]

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.

[bricelam]

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]

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]

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

[bricelam]

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