Define data model for INFORMATION_SCHEMA and expose it

[krlmlr]

The INFORMATION_SCHEMA is a standardized way for databases to provide information on tables, columns and keys. See https://dataedo.com/kb/databases/all/information_schema for details.

To better understand it, we should build a dm for it. This requires compound keys and needs to be done on top of #335, or as part of it.

• [x] Build data model for INFORMATION_SCHEMA for MariaDB, as found in relational.fit
• [x] Define a simpler "meta dm" derived from INFORMATION_SCHEMA that captures all necessary information (table definition, pk, fk)
  • easy to derive from existing INFORMATION_SCHEMA implementations
  • capture all necessary information to create the dm object and also to reconstruct tables with SQL data types on the database
  • what about color?
• [x] Make compatible with Postgres
• [x] Make compatible with SQL Server
• [ ] Rethink 0dd37dc0aab329ecf4a4ec50e17e9d0ee121ea13 which affects contents of constraint_column_usage
• [ ] Shoehorn SQLite (https://github.com/cynkra/dm/pull/352)
• [ ] Make compatible with MariaDB (https://github.com/cynkra/dm/issues/366)
• [ ] Support dm_meta() for arbitrary databases: if INFORMATION_SCHEMA not available, fall back to an implementation that only queries table names and has no schemas or catalogs
• [ ] Implement get_src_tbl_names() via dm_meta()
• [ ] Use JSON to reduce the number of queries (requires dbplyr >= 2.2.0)
• [ ] Use S3 dispatch instead of explicit checks

[krlmlr]

Diagram for INFORMATION_SCHEMA: http://mysql.xonu.de/INFORMATION_SCHEMA_5_1/

Details

[krlmlr]

The MySQL version is better, because we lose one table: column_constraints. Postponing, keeping private for now.