Review DatabaseMetaData Table Types

[enqueue]

Motivation

In ClickHouseDatabaseMetaData we currently differentiate between these table types: DICTIONARY, LOG TABLE, MEMORY TABLE, REMOTE TABLE, TABLE, VIEW, SYSTEM TABLE, TEMPORARY TABLE .

• Are these distinctions useful to most ClickHouse JDBC users?
• Is there any reference for the table types or is this supposed to be very vendor specific? I think we should not "invent" new stuff here.

What does the standard say?

• I could not find any relevant hint in JDBC Spec 4.2 PDF
• The API doc says:

Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".

What others are doing?

• Postgresql: SYSTEM TABLE, SYSTEM VIEW, SYSTEM_INDEX, SYSTEM TOAST TABLE, SYSTEM TOAST INDEX, SYSTEM TEMPORARY TABLE, SYSTEM TEMPORARY INDEX, SYSTEM TEMPORARY SEQUENCE, SYSTEM TEMPORARY VIEW, TEMPORARY TABLE, TEMPORARY INDEX, TEMPORARY SEQUENCE, TEMPORARY VIEW, TABLE, PARTITIONED TABLE, INDEX, PARTITIONED INDEX, SEQUENCE, VIEW, TYPE, FOREIGN TABLE, MATERIALIZED VIEW
• Firebird: SYSTEM TABLE, TABLE, VIEW, GLOBAL TEMPORARY
• Mysql: LOCAL TEMPORARY, SYSTEM TABLE, SYSTEM VIEW, TABLE (alias VIEW, BASE TABLE)
• Oracle ❓
• SQL Server: VIEW, TABLE_TYPE ❓, TABLE, SYSTEM TABLE
• H2: BASE TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, SYNONYM, VIEW
• Redshift: TABLE, VIEW, INDEX, SEQUENCE, TYPE, SYSTEM TABLE, SYSTEM TOAST TABLE, SYSTEM TOAST INDEX, SYSTEM VIEW, SYSTEM INDEX, TEMPORARY TABLE, TEMPORARY INDEX, TEMPORARY SEQUENCE, TEMPORARY VIEW, EXTERNAL TABLE