Define the Schema Objects for the DbConnection getSchema

[saurabh500]

Ref issue https://github.com/dotnet/corefx/issues/3423

The Database schema being exposed by the DbConnection.GetSchema() needs to return the schema of the database.

The API should expose strongly typed objects. Issue #15269 is an effort to design the DbColumn representing the Column schema which will be one of the items of the collection of Base types which the Database schema should expose.

This issue is for the discussion of the Types for the schema and refining the types.

I will add more details here as we come up with the design.

The Base types will be added to System.Data.Common and an API will be added to the DbConnection exposing the top level schema object.

[skyhoshi]

After reading (most) of the discussion chain starting in #14302 I was curious as to how you might handle FileStreams, as they are of the VarBinary DataType I didn't see much discussion around the less basic Data types. e.g. Spacial's, FileStreams and the more complex data types.

its my assumption that these can be broken down into more simple data types such as the backing VarBinary(Max) for the file streams and decimals(floats) for Spacial's, though it would be reassuring to hear these complex types addressed as well.

I appreciate the distance this conversation has traveled and I wouldn't want to muck the conversation up though these data type can muddy up the waters a bit when dealing with a [DataType] abstraction. the conversation has been light on details of these more complex SQL Data Types.

These data types are a bit more "Product Specific" than this discussion chain would require but having said that the "GetSchemaTable()" or Meta Data Retrieval Methods used should be brethed enough to deal with these more complex and qualified Data Types.

[saurabh500]

For the DbSchema API on top of DbConnection, I propose the following Classes

DbSchema is the root of the schema and represents a database.

DbSchema   // Represents a Database
    - DbName : String           // Represents the Database name 
    - List < DbTable >  : tables    // A list of Database tables


DbTable    // A Database tabke
    - Name : string                 // Table name
   - Columns : List < DbColumn >       // List of the columns in the table  
   - Indexes : List < DbIndex >        // List of the indexes on the table columns
   - ForeignKey : List < DbForeignKey >  // List of the foreign keys applied to the columns 
   - Database : DbSchema           // The database of which this table is a part of 


DbForeignKey                       // Represents the foreign key details
    - Name: string                  // The name of the constraint
    - SourceTable : DbTable         // The table to which this constraint is applied to 
    - ReferenceTable : DbTable      // The table to which this contraint refers 
    - ReferenceColumn : DbColumn    // The columns which this Foreign Key is applied to.
    - SourceColumn : DbColumn       // The source column of the index


DbIndex
   - Name: string          // Name of the index
   - Column : List  <  DbIndexColumn >     // Column(s) on which index is applied
   - Table : DbTable       // Table on which the index is applied
   - IsUnique : bool     // Is the index unique? 

 DbIndexColumn
   - SortOrder : DbSortOrder // The sort order of the column in the metadata
   - Column : DbColumn       // The sort order of the column in the 

DbColumn 
   - Table : DbTable   // Table which contains this column
   - Existing attributes

DbSortOrder
   - ASC
   - DESC
   - None

EDIT: Using Markdown and edited ForeignKey : List < ForeignKey > to ForeignKey : List < DbForeignKey >

[saurabh500]

@FransBouma @NickCraver @roji @mgravell
Please take a look. Looking forward to your feedback.

[NickCraver]

Quick thoughts off the top of my head in no particular order:

• What does DbSchema scope to? Is it for all schemas in a database or one? If it's many (which I'd expect) we have a naming conflict at the root.
• ForeignKey : List < ForeignKey > - assuming typo and meant DbForeignKey?
• Database table would need a Schema string as well
• The parent ref naming is inconsistent, it should probably be consistently the parent type without the Db prefix. e.g. for DbColumn it's Table.
• The parent reference probably does more harm than good on the serialization, GC, etc. fronts - I'd personally vote for removing it across the board.
• DbIndex is missing a lot of information I'd typically be after: is it the clustered? columnstore? etc. Perhaps a string type?
• Can you expand Existing attributes on DbColumn for ease of seeing it all together?
• What is Sort Order on the index? (per column looks good)
• Does DbIndex only include the indexes columns or also included columns?
• What about views?
• Perhaps a string IndexType on DbIndex would replace the IsUnique property? This is more extensible and better scales to the many types of indexes. DbIndex could have a built-in static class with string constants for the currently known types as a common source to use but not a locked-down enum approach.

I'll think on it a bit more, but wanted to get the ball rolling here.

[saurabh500]

@NickCraver

What does DbSchema scope to? Is it for all schemas in a database or one? If it's many (which I'd expect) we have a naming conflict at the root.

For a given database there would be one root at DbSchema

ForeignKey : List < ForeignKey > - assuming typo and meant DbForeignKey?

Fixed it

Database table would need a Schema string as well

Thanks, I will add this.

DbIndex is missing a lot of information I'd typically be after: is it the clustered? columnstore? etc. Perhaps a string type?

Can you add more information here about what more should it get. Yes Clustered should be added in this case on DbIndex.

Can you expand Existing attributes on DbColumn for ease of seeing it all together?

I will expand it. For current reference, it will be the code at https://github.com/dotnet/corefx/blob/master/src/System.Data.Common/src/System/Data/Common/DbColumn.cs

What is Sort Order on the index? (per column looks good)

I messed this up. The sort order should exist only on DbIndexColumn

Does DbIndex only include the indexes columns or also included columns?

The columns are include in DbIndexColumn. Each index can have multiple column and the sort order can be specific to each column.

What about views?

I haven't yet worked on the design for exposing views as a part of the schema. Any thoughts into what the model for the views should look like?

Perhaps a string IndexType on DbIndex would replace the IsUnique property? This is more extensible and better scales to the many types of indexes. DbIndex could have a built-in static class with string constants for the currently known types as a common source to use but not a locked-down enum approach.

Do you mean that instead of having an IsUnique: bool we have a single string property which can be overloaded to represent the different types of index. I don't think I follow completely. Can you elaborate with an example?

[ErikEJ]

I think there is a mistake in DbForeignKey design:

 DbForeignKey                       // Represents the foreign key details
  - Name: string                  // The name of the constraint
  - SourceTable : DbTable         // The table to which this constraint is applied to 
  - ReferenceTable : DbTable      // The table to which this contraint refers 
  **- ReferenceColumns : List < DbColumn  >  // The target columns this Foreign Key refers to.**
  **- SourceColumns : List < DbColumn >      // The source columns this Foreign Key contains.**

[FransBouma]

Have to look more in depth to this, but I see already one glaring error: a schema isn't 'a database'. A schema is a schema, a catalog can contain multiple schemas in some RDBMSs. Using the term 'database' will create only confusion.

FK indeed contains errors, @ErikEJ's correction is needed. One could design this in multiple ways btw: the ReferenceTable already implies the ReferenceColumns (as those are the same as the PK). One could also think about storing the pk-fk fields in pairs so it becomes:

DbForeignKey
  - Name
  - SourceTable
  - ReferenceTable
  - KeyPairs: List<KeyPair>
where KeyPair is a struct/simple class with two properties: PkField and FkField. 

[FransBouma]

On second thought, this will be a massive class model, as it has to potentially be able to define every different element in all RDBMS types, as not every RDBMS stores the same type of elements. Think about e.g. packages in oracle with stored procs in packages, overloads of procs, UDTs (in all types and forms), assemblies, users, views, partitioned / indexed views, the list is very very large. At the same time, going this route will thus force a choice: either implement everything (which will likely be a challenge as there is always a database out there which stores something that's not implemented, and what to do in that kind of situation?) or implement a subset. The latter is a waste of time, I can give you that up front. The former will likely be much more effort than porting DataTable.

Keep in mind that existing ADO.NET providers already have implementations of metadata->datatable conversions for the existing DbConnection.GetSchemaTable.

I likely start sounding like a broken record, but by simply porting DataTable things will be much simpler. And no offense to @saurabh500, I know you mean well, but seeing a schema as a database, unless you're an Oracle originating DBA, doesn't bode well for the future: designing such a class model requires deep understanding of what a schema is across multiple RDBMSs. That requires a lot of knowledge that's not available. I spent considerable time in this field and I too still find myself running into new things: it's not as simple as cooking up some classes around what defines a table, it's much broader than that. And sadly it changes all the time too.

Whoever decided this whole plan was a good idea over at Microsoft clearly has no clue how much work this really is and how much work it will be for all the ADO.NET provider writers out there, not to mention existing software which also has to be ported to this new class model (and likely will run into a limitation of it, which forces the developer to go the raw metadata route anyway, so all work will be for nothing).

Waste of time.

(edit: waste of time, because it's a project that results in a limited class model that's always behind reality, and offers no flexibility towards ADO.NET provider writers as they now don't have the ability anymore to provide schema data in a form they think is best and provide data not in the class model)

[ErikEJ]

@FransBouma Agree with your last comment, coming up with a useable model for all provider scenarios is impossible, so possibly this model is a lost cause.

On another note: Do not assume that a foreign key references a primary key, a foreign key in SQL Server can reference a Unique key in the "parent"

[martinjw]

How do you find the primary key of a DbTable?
DbTable needs a DbPrimaryKey with a name and List
On DbColumn I don't see a "IsPrimaryKey" either (not really needed if you have a DbPrimaryKey).

[FransBouma]

@ErikEJ Thanks for the correction! I was commenting before my breakfast even, and one shouldn't do that haha :) Indeed FK points to a 'key' which indeed can be a unique key, where one per table can be marked as 'primary' (have to look it up, but I think Oracle defines it that way even)

Not sure about 'parent' though, that suggests hierarchy, but it's not a tree but a DAG ;)

[roji]

A very quick comment to @FransBouma and @ErikEJ: I don't think there's any idea of this class model being complete, i.e. representing all metadata possibilities across all databases. As we've discussed before the idea is rather to capture standard features that are found in (almost) all relational databases, and allow providers to subclass the Db* classes and add their own database-specific features (we also discussed adding database-specific features in a weakly-typed way to allow accessing them without referencing the provider assembly).

In other words, if we simply reproduced the current DataTable schema but with a strongly-typed class model, without attempting to add any new metadata fields, you'll have already improved the situation.

Regardless, I totally agree that schemas need to be represented in a clear and distinct way - there are multiple database systems out there where a single database can contain multiple schema. Accordingly, I propose that we use the term "schema" only in that sense (i.e. a namespace of tables in a given database), and talk of metadata when discussing this entire project.

[FransBouma]

In other words, if we simply reproduced the current DataTable schema but with a strongly-typed class model, without attempting to add any new metadata fields, you'll have already improved the situation.

True, but which 'current datatable schema' ? :) Every ADO.NET provider returns a different set of schema tables, with different layout: some very verbose, others very limited. That's already a big problem.

Perhaps the standarized INFORMATION_SCHEMA views could be used as a starting point of what to encapsulate at least in the class model. There's another problem though if the class model is too limited:

As we've discussed before the idea is rather to capture standard features that are found in (almost) all relational databases, and allow providers to subclass the Db* classes and add their own database-specific features (we also discussed adding database-specific features in a weakly-typed way to allow accessing them without referencing the provider assembly).

Subclassing is out of the question, really, as accessing these subtypes requires a reference to the ADO.NET assembly, making code which isn't tied to a specific ADO.NET provider version (so doesn't suffer from the same mess as .NET 1.x) impossible.

[roji]

In other words, if we simply reproduced the current DataTable schema but with a strongly-typed class model, without attempting to add any new metadata fields, you'll have already improved the situation.

True, but which 'current datatable schema' ? :) Every ADO.NET provider returns a different set of schema tables, with different layout: some very verbose, others very limited. That's already a big problem.
Perhaps the standarized INFORMATION_SCHEMA views could be used as a starting point of what to encapsulate at least in the class model. There's another problem though if the class model is too limited:

That's a good point. It feels like there's a common core of metadata properties that (almost?) all providers currently implement in DataTable. Ideally someone (the Microsoft team?) would go over the major ADO.NET providers and map out what metadata is provided where. This would provide the basis for a more-or-less database-independent metadata specification which would be strongly-typed. For database-specific properties see below.

As we've discussed before the idea is rather to capture standard features that are found in (almost) all relational databases, and allow providers to subclass the Db* classes and add their own database-specific features (we also discussed adding database-specific features in a weakly-typed way to allow accessing them without referencing the provider assembly).

Subclassing is out of the question, really, as accessing these subtypes requires a reference to the ADO.NET assembly, making code which isn't tied to a specific ADO.NET provider version (so doesn't suffer from the same mess as .NET 1.x) impossible.

This seems like a rehash of the conversation we had in #15269 (see https://github.com/dotnet/corefx/issues/3423#issuecomment-165116489 and above). We would ideally have both approaches:

• Subtypes of the metadata classes (e.g. NpgsqlForeignKey) with strongly-typed PostgreSQL-specific properties. These would require referencing the provider assembly.
• Weakly-typed access via indexers, for scenarios where you don't want to reference an assembly.

Does that sound right?

Regardless of the above, DbForeignKey should probably have a ReferentialAction enum, expressing what happens on deletion (NoAction, Restrict, Cascade, SetNull, SetDefault).