Duckdb: Support `INFORMATION_SCHEMA`

[hannesmuehleisen]

We should support the special INFORMATION_SCHEMA schema and associated tables to query the structure of the database. This can then be used for example by frontent drivers (e.g. JDBC) to expose schema information to tools such as DBeaver.

Loads of tables are defined in various information schemata, but not all are equally relevant. I propose to start with a minimal schema that will allow full reflection on what the database structure is. Some other systems define additional columns, we can always add NULL columns, but let's start without.

• SCHEMATA
  
  
  
  • CATALOG_NAME
  
  
  • SCHEMA_NAME
  
  
• TABLES
  
  
  
  • TABLE_CATALOG
  
  
  • TABLE_SCHEMA
  
  
  • TABLE_NAME
  
  
  • TABLE_TYPE (BASE TABLE, VIEW or LOCAL TEMPORARY)
  
  
• COLUMNS
  
  
  
  • TABLE_CATALOG
  
  
  • TABLE_SCHEMA
  
  
  • TABLE_NAME
  
  
  • COLUMN_NAME
  
  
  • ORDINAL_POSITION
  
  
  • COLUMN_DEFAULT
  
  
  • IS_NULLABLE (YES or NO)
  
  
  • DATA_TYPE
  
  
  • CHARACTER_MAXIMUM_LENGTH
  
  
  • CHARACTER_OCTET_LENGTH
  
  
  • NUMERIC_PRECISION
  
  
  • NUMERIC_PRECISION_RADIX
  
  
  • NUMERIC_SCALE
  
  
  • DATETIME_PRECISION
  
  

Some columns are always going to be NULL obviously.

[Mytherin]

Agreed, these could be implemented as built-in views over the other catalog functions.

[hannesmuehleisen]

The tricky thing here is applications need to be able to rely on this schema being what they expect from consensus because there is no meta-way to reflect on the information schema.

[jwills]

I would love this to exist, as I started hacking on a duckdb plugin for dbt and it would speed things along for me. Is it something that I, as a rank amateur duckdb user, could take on implementing?

Related: is there a way in the current SQL pragmas to get the list of relations for a given schema? I can get all of the relations from sqlite_master(), but that doesn't include the schema info AFAICT. Thanks!

[Mytherin]

I would love this to exist, as I started hacking on a duckdb plugin for dbt and it would speed things along for me. Is it something that I, as a rank amateur duckdb user, could take on implementing?

Depending on your C++ skills, I think it should be relatively feasible :) Most of the groundwork has already been done. You can take a look at the implementation of the sqlite_master function (src/function/table/sqlite/sqlite_master.cpp). The information schema is an extension of that and should be relatively similar.

Related: is there a way in the current SQL pragmas to get the list of relations for a given schema? I can get all of the relations from sqlite_master(), but that doesn't include the schema info AFAICT. Thanks!

I don't think this is possible at the moment, but indeed it should be. The information schema would make this possible.

[jwills]

Okay, so you're envisioning 3 table functions (maybe information_schema_schemata, information_schema_tables, and informtion_schema_columns), defined in the src/function/table/information_schema directory, and then a hook somewhere else (not sure where that will go; there is that bit in the python code where a sqlite_master view is created over the sqlite_master() table function?) in the code to automatically create the information_schema schema and the associated views? I haven't written C++ professionally in about 10 years, but I think I can muddle through that.

[Mytherin]

For now the functions are sufficient. We can always define views over those functions later.

More information can be found here. There are a lot of tables, not just the ones listed here, but starting out the ones listed here are sufficient. The information schema is defined by the SQL standard with the purpose that they are standardized across systems (see the wikipedia page).

If you are not sure what should go into a field, check the Postgres documentation of the tables, schemata and columns. You can also test in Postgres to see what happens. The tables have many properties that are not relevant for our system (e.g. schema_owner is not relevant: we don't have users/owners). In that case just returning a NULL is sufficient. It is important that all the columns listed are present, though, even if they just return NULL. That way the information schema is consistent with other systems.

[jwills]

Working on this over here: https://github.com/cwida/duckdb/compare/master...jwills:jwills_info_schema?expand=1

Should have something PR-able by tomorrow.

[jwills]

Do we close this now, or tag it for the next release or some such thing?

[Mytherin]

Can be closed now, thanks for the implementation :)