How to differentiate system tables from others

[prrvchr]

I am trying to display the tables of an H2 database but cannot differentiate the system tables (generally the tables located under the schemas: INFORMATION_SCHEMA, SYSTEM_LOBS, ...) from the others.

How is this possible?

[Noel Grandin]

Assuming you are using the normal API, it's in the TABLE_TYPE column:

https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTables-java.lang.String-java.lang.String-java.lang.String-java.lang.String:A-

[prrvchr]

Hi Noel,

In fact H2 only seems to differentiate tables and views  (TABLE and VIEW) but nothing on system tables (SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM)

I haven't tried again recently, but in 2.2.219 it doesn't work...

[Evgenij Ryazanov]

Hi!

The latest version of H2 is 2.1.214, there is no such version as 2.2.219.

You can detect types of tables with the following query in modern versions of H2:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, STORAGE_TYPE, DB_OBJECT_SQL('TABLE', TABLE_SCHEMA, TABLE_NAME) IS NULL IS_SYSTEM FROM INFORMATION_SCHEMA.TABLES;

[Pierre Vacher]

Hi,

2.2.219 is a SNAPSHOT that fixes the problem of autoincrement types if I remember correctly...

In fact the problem is not to find the type of the table, but that the DataBaseMetadata.getTables() method does not know how to filter on the type that normally we must be able to control...

Maybe I'm wrong...

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0c9892ec-4a71-4802-9f21-9ee9fc42da8an%40googlegroups.com.

[Evgenij Ryazanov]

The SQL Standard describes 'BASE TABLE', 'VIEW', 'GLOBAL TEMPORARY', 'LOCAL TEMPORARY', and 'SYSTEM VERSIONED' table types. H2 doesn't support system versioned tables, but supports all other types.

The JDBC Specification doesn't describe (and shouldn't describe) any table types.

Javadoc of DatabaseMetaData.getTables() also doesn't describe any table types, but it provides a list of possible examples ("TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM"). The actual table types available in database are returned by DatabaseMetaData.getTableTypes(), this method should return what database is actually has and returned list of actual table types is not required to have mentioned example table types. This area is database and driver-specific.

Some parts of JDBC metadata look like they were written for MySQL and partially for Oracle, because they include some exotic features of these systems, but don't include many common standard features.

It means you can get some basic information from DatabaseMetaData, but there are no unified ways for other features. The INFORMATION_SCHEMA from the SQL Standard is designed significantly better, but Standard also cannot describe all possible features of all database systems. Unfortunately, some database systems don't have this schema, but have some own non-standard and non-portable sources of information. Various applications and libraries use different queries for different database systems due to all these problems.

[prrvchr]

To be more precise:

 Here is the result of some tests on the DataBaseMetadata.getTables() method with the String[] types parameter:

- String[] types = null;

• H2 lists all
• HsqlDB lists all
• SQLite JDBC Driver lists all
  

- String[] types = new String[]{"TABLE", "VIEW"};

• H2 lists all
• HsqlDB lists all tables and views except those under schemas: INFORMATION_SCHEMA and SYSTEM_LOBS
• SQLite JDBC Driver list all tables and views except table sqlite_sequence (sqlite as no schema)
  

[prrvchr]

It seems that in order to be able to display only non-system tables, the DataBaseMetadata.getTables() method must only return system tables if the list of types includes SYSTEM_TABLE.

Apparently that's how it works with HsqlDB and Sqlite.

[prrvchr]

And I think that if this is not the case then the type parameter does not have much interest...

[prrvchr]

In fact, I don't know anything about JDBC and SQL, but I would like H2 to work correctly under LibreOffice / OpenOffice, and I think that on this specific problem I can't do much without your help.

[Evgenij Ryazanov]

Do you still have an own special driver for LibreOffice?

If so, you can assume that rows with TABLE_SCHEM = 'INFORMATION_SCHEMA' describe system tables.

(Actually they aren't tables, but it doesn't matter.)

[prrvchr]

jdbcDriverOOo is a JDBC 4 driver for LibreOffice and OpenOffice.

It is packed with H2 driver 2.2.219 you can try it if you want...

The problem is that this driver is only an adapter I do not analyze the resultset before returning them...

To solve this problem, I only see a modification of the code of H2.

[prrvchr]

I think that if we manage to progress then we will be able to reach the level of integration of HsqlDB, namely to be able to manage the rights on the tables...

[prrvchr]

If needed I can do something specific for H2.

I have java services for each built-in driver...

But honestly I don't see the point of the Type parameter with the DataBaseMetadata.getTable() method if it treats it of this way...

[prrvchr]

Well I would like you to realize that this problem which looks simple, trivial and irrefutable, in fact took me a lot of time in order to be able to find it, understand it and be able to explain it to you clearly. So sure I can patch it, but I think it would be reasonable to assume that your use of the type parameter in DataBaseMetadata.getTables() is unusable as implemented in your driver. So if you have to talk for three days, in order to get three lines of correction code, I give up...

[Noel Grandin]

On Tue, 2 May 2023 at 15:30, prrvchr <prr...@gmail.com> wrote:

To be more precise:

 Here is the result of some tests on the DataBaseMetadata.getTables() method with the String[] types parameter:

types = new String[] { "BASE TABLE", "VIEW" };

Should do what you need

[prrvchr]

I'll try...

[prrvchr]

no, that doesn't change anything...