format: expose other metadata in GetObjects or add new metadata function

[lidavidm]

For example: user-defined types, available functions, UDFs, nested schemas, etc.

[lidavidm]

Currently we have a getObjects function that returns a single, nested data structure describing all info. This is sort of convenient in some ways (effectively, it's dictionary/run-length encoding common fields, and some systems can generate the nested structure directly) but inconvenient in others. It means we can't easily extend the hierarchy (changing the schema = potential breakage), we can't redefine what "depth=ALL" means easily¹, and it's very annoying to build up if your database doesn't directly support building that nested structure.

Also, getObjects doesn't actually expose all the metadata you might expect. Even if you look at just methods returning ResultSet in JDBC's DataBaseMetaData, there's quite a bit of info that we don't expose.

We should figure out what is common between JDBC and ODBC (probably, everything); see if there's any other common info (in PostgreSQL, SQL Server?), and define separate methods (auxiliary to GetObjects) for each of those.

For consistency with GetObjects we may still nest things? But separate out different kinds of metadata into different functions.

Footnotes

1. technically it's just an alias...but for source compatibility it would still be best to not change it. ↩

[lidavidm]

Additional table/column metadata

• JDBC: getColumnPrivileges, ODBC: SQLColumnPrivileges
• JDBC: getCrossReference
• JDBC: getExportedKeys, JDBC: getImportedKeys
• JDBC: getPseudoColumns, JDBC: getVersionColumns - are we willing to extend the set of 'xdbc' columns?
• JDBC: getSuperTables
• JDBC: getTablePrivileges, ODBC: SQLTablePrivileges
• JDBC: getBestRowIdentifier

Frankly, I'm tempted to punt on these? Knowing about procedures, functions, and types is useful for a system that wishes to use ADBC to federate with other data systems, but these seem like more limited utility (and can be added alongside getObjects in the future).

Functions/user-defined functions

• JDBC: getSystemFunctions
• JDBC: getFunctions
• JDBC: getFunctionColumns
• ODBC: SQLGetFunctions is not actually about the database, but which ODBC API functions are supported
• PostgreSQL:
  • pg_aggregate
  • pg_operator

Proposal:

AdbcConnectionGetFunctions(struct AdbcConnection*,
                           const char* catalog,
                           const char* db_schema,
                           const char* function_name,
                           struct ArrowArrayStream* out);

Returns a nested structure like GetObjects (but there is no depth parameter), with catalogs, then schemas, then functions, then columns.

Procedures

• JDBC: getProcedures, ODBC: SQLProcedures
• JDBC: getProcedureColumns, ODBC: SQLProcedureColumns

Proposal:

AdbcConnectionGetProcedures(struct AdbcConnection*,
                            const char* catalog,
                            const char* db_schema,
                            const char* procedure_name,
                            struct ArrowArrayStream* out);

Returns a nested structure like GetObjects (but there is no depth parameter), with catalogs, then schemas, then procedures, then columns.

Types/user-defined types

• JDBC: getUDTs
• JDBC: getAttributes
• JDBC: getSuperTypes
• PostgreSQL:
  • pg_enum
  • pg_type

We can also return a nested structure here. Super types can be modeled as another column of struct type that (if not null) contains the name of the super type.

For each of these, we should add functions to query the equivalent Arrow schema or type, analogous to AdbcConnectionGetTableSchema.

[lidavidm]

• Are we willing to extend the schema in GetObjects? (That would probably be easiest to handle a few of the additional metadata items)
• Functions, procedures, and types should each get their own function (but possibly each of them should return all info at once in a nested structure - we should see how hard this is to do in, say, PostgreSQL)
• A few things (like getBestRowIdentifier) we may punt on
• Do we want a GetFunctionSchema?