format: expose more catalog/schema/table/column properties

[lidavidm]

What feature or improvement would you like to see?

We should make sure we expose everything from JDBC and ODBC, and consider standardizing other common properties, and/or clarifying that drivers may return custom properties of their own choosing (we may want to semi-standardize some of these custom properties by defining a name/type/semantics but not necessarily requiring them to be present).

• BigQuery: Feature Request: Comprehensive Metadata Discovery for Data Development Tools adbc-drivers/bigquery#118 (point 10, 11, 12, 13)
• Databricks: Feature Request: Enhanced Metadata Discovery for Databricks ADBC Driver adbc-drivers/databricks#236 (point 6)
• PostgreSQL: Feature Request: [postgresql] Comprehensive Metadata Discovery for Data Development Tools #3971 (point 20)
• Trino: [Go] Feature Request: Comprehensive Metadata Discovery for Data Development Tools adbc-drivers/trino#60 (point 5, 7, 9, 10)

[lidavidm]

Proposal: allow adding database-specific values as a struct that must be the last column, and the struct children are driver-defined

• What is the name of this field?
• Do we want a standard option to fetch (potentially expensive) "extra" info?
• Maybe we want to promote some fields to standard?
  • catalog, schema owner
  • remarks (comment/description)
  • creation time?

Catalog-level

JDBC only has catalog name.
ODBC uses GetTables for catalogs, which has remarks.

Databricks

• information_schema.catalogs

PostgreSQL

• pg_database
• pg_shdescription

Schema-level

JDBC only has schema name.
ODBC uses GetTables for schemas, which has remarks.

BigQuery

• information_schema.schemata: properties like owner and creation_time
• information_schema.schemata_options: arbitrary key-value options

Databricks

• information_schema.schemata has more info

PostgreSQL

• information_schema.schemata: chiefly owner, also description via pg_catalog

Trino

• system.metadata.schema_properties

Table-level

JDBC has remarks, but also some properties whose purpose is unclear: the types catalog/schema/name, SELF_REFERENCING_COL_NAME, and REF_GENERATION.

ODBC has remarks.

BigQuery: these tables have more information:

• information_schema.tables
• information_schema.table_options
• information_schema.materialized_views

Databricks

• SHOW TABLE EXTENDED has more metadata

Trino

• system.metadata.table_properties

Column-level

JDBC: we should add a xdbc_source_data_type.