H2 does not use SYSTEM TABLE in table types

[prrvchr]

Hi all,

It's just a question. Why H2 does not use SYSTEM TABLE in the table types returned by the DatabaseMetaData.getTableTypes() method.

How to filter tables found in system schemas (ie: INFORMATION_SCHEMA) using the getTableType() method?

Thank you for your suggestions.

[Evgenij Ryazanov]

Hi!

> Why H2 does not use SYSTEM TABLE in the table types returned by the DatabaseMetaData.getTableTypes() method

Because there is no such thing as system table in the SQL Standard. JDBC metadata is an entirely unreliable thing, you can't construct anything serious on top of it.

There is a difference between type of a table (base table, view, local temporary table, global temporary table and so on) and its actual implementation.

In modern versions of H2 you can use a query like that to distinguish user-defined tables and views from system tables and views:

SELECT *, DB_OBJECT_ID('TABLE', TABLE_SCHEMA, TABLE_NAME) < 0 IS_SYSTEM FROM INFORMATION_SCHEMA.TABLES;

[prrvchr]

Hi Evgenij,

All databases use a different type in order to be able to filter system tables, well all those that I manage in Base (HsqlDB, SQLite xerial, Derby, PostgreSQL pgJDBC, Firebird Jaybird).

MariaDB connector/J gives system tables only for those located in the information_schema.

It seems to me that if this is used by so many databases it is because it is the simplest solution to provide such functionality.

I already have specific code for H2 which returns TABLE BASE instead of TABLE and I had to set up mock ResultSet on the DatabaseMetaData.getTypeInfos() and DatabaseMetaData.getTablePrivileges() methods for databases returning non-compliant resultsets.

Unfortunately for this specific problem I can't do much since we are unable to filter the tables due to lack of differentiation of types... For this specific functionality I only depend on the underlying implementation.

[prrvchr]

To be more precise, it is not a change in the type of tables that I am asking for, but the possibility of making the DatabaseMetaData.getTablesTypes() method more intelligent in order to be able to filter or not the system tables.

That this method recognizes the types TABLE, SYSTEM TABLE and BASE TABLE in the type parameter in order to only return respectively all non-system tables, all system tables and all tables.

[prrvchr]

Correction: DatabaseMetaData.getTables() method more intelligent...

[Evgenij Ryazanov]

H2 returns table types exactly as required by the SQL Standard. JDBC doesn't have any own requirements.

[prrvchr]

No definitely, JDBC has nothing to do with it. It is your implementation which by not differentiating the type of the tables, necessarily, does not allow any filtering on the tables. Too bad and I don't think it's going to change any time soon since you can't understand such a simple thing...

[Noel Grandin]

On Wed, 10 Apr 2024 at 11:00, prrvchr <prr...@gmail.com> wrote:

No definitely, JDBC has nothing to do with it. It is your implementation which by not differentiating the type of the tables, necessarily, does not allow any filtering on the tables. Too bad and I don't think it's going to change any time soon since you can't understand such a simple thing...

That kind of comment is uncalled for, and not appreciated. This is not a commercial product, we do not owe you anything. We do not get paid for working on this project, nor do we get paid for spending time responding to issues.

Now, Let us examine the proposal.

Firstly, yes, it could be improved. But we have already supplied a workaround that extracts the required information.

Secondly, if we _did_ improve it, we would break some downstream libraries (like JOOQ and Hibernate), who rely on the values we currently return from that method.

We would also likely break the code of various people who use H2.

[prrvchr]

Hi Noel,

If this is not appreciated, then I apologize.

But I'm not paying either and I'm trying to bring some more functionality to LibreOffice Base for H2.

Now if it is forbidden to talk about what is not working and it is preferable to hide behind bogus justifications, I do not think that this is of much interest.

Sometimes it is better to say:

- Yes this is a limitation of our implementation.

- Than trying to make people believe that it is JDBC.

Because first of all it's taking me for an imbecile and if you want to be able to improve H2, I think this is the way to follow.

[prrvchr]

> if we _did_ improve it, we would break some downstream libraries (like JOOQ and Hibernate), who rely on the values we currently return from that method

Not if you only change the input handling: support the SYSTEM TABLE and TABLE types in addition to TABLE BASE in the type given as input to the DatabaseMetaData.getTables() method.

I think less than 10 lines of code are necessary....

[Noel Grandin]

On Wed, 10 Apr 2024 at 13:01, prrvchr <prr...@gmail.com> wrote:

> if we _did_ improve it, we would break some downstream libraries (like JOOQ and Hibernate), who rely on the values we currently return from that method

Not if you only change the input handling: support the SYSTEM TABLE and TABLE types in addition to TABLE BASE in the type given as input to the DatabaseMetaData.getTables() method.

I think less than 10 lines of code are necessary....

Possibly I am missing something. 

I am sure we could do that, and possibly it would have no negative side effects.

But for you, that would require waiting for a new version to become available, which might take some months.

Why is it hard to use the workaround? ie. doing

[prrvchr]

> But for you, that would require waiting for a new version to become available, which might take some months.

To be honest, the deadline is not very important, what matters is that ultimately we can offer users a tool (LibreOffice Base) that is easy to use for databases. This is necessary if we want to be able to offer intelligent and communicative functionalities.

> Why is it hard to use the workaround? ie. doing

I already have specific code for H2 because of the type of table named TABLE BASE. If you use H2 in LibreOffice Base with the native JDBC driver you will only see the views, the tables are invisible...

So I have mock java.sql.ResultSet in place allowing me to patch the content in order to satisfy Base.

But in this specific case, it is more complicated for me to be able to overcome this limitation than it is for you. I don't think I can write this in less than 10 lines...

Furthermore I think that any good database management software will be happy to take advantage of such functionality.

[prrvchr]

If we want to be able to offer users intelligent software, we must find intelligence where it is found.

In this case, you are the one holding it...