Wishlist: Database Provider Meta Model

[jzabroski]

For now, classifying this as documentation - I don't think any development project can be in the works until we document what is possible to put into a static model.

One of the tricky things with abstracting SQL across so many SQL providers (Oracle, MySQL, PostgreSQL, DB2, MSSQL, Snowflake, SAP Hana, etc) is that there are inconsistencies in:

• What forces an implicit commit
• What can't be run inside a transaction, at all
  • for example, in SQL Server, you cannot change COLLATION on a database inside a transaction.
• What statements are required to run as "the only statement in the statement batch" or "must start a statement batch"
• What set of functionality the database provides;
  • for example, SQLite does not support a ALTER TABLE .. DROP COLUMN syntax
• How errors bubble up when the storage engine detects inconsistencies and errors with a series of DDL statements, each nested in their own private transaction hosted by a parent transaction ("session")
• How name resolution (deferred, eager, etc.) is handled
  • for example, SQL Server requires SYNONYMs to be resolvable to remote entities at time of stored procedure creation, but other stored procedure dependencies will only generate a warning if not pre-defined before the stored procedure definition is executed. In SQL Server, two-part names that resolve to synonyms are eagerly resolved.
• How security rights of the user running migrations effects object visibility and the ensuing error messages if the user running migrations is not a super user account
  • for example, if you use a one-part name "MyProcedure" to reference "Dbo.MyProcedure", and you are not db_owner (either explicitly or implicitly through GRANT CONTROL on dbo schema), then your migrations might fail with cryptic error messages.

[jzabroski]

@fubar-coder I think we need at least two meta models:

1. "SQL For Grown Ups": Oracle, DB2, SAP Hana, SQL Server, Postgresql
2. "SQL As ACID-based File System": SQLite

The big problem is that SQLite is so simple that it doesn't do much else beyond the ANSI SQL 92 syntax and semantics. Still, it's extremely popular for storage on Android and iOS mobile devices, so we need some way to support it if people would rather write C# than SQL for migrating customer databases.