Information schema tables

[Boris Stumm]

Hello,

In my project, I need to extract database schemas from a databases
INFORMATION_SCHEMA. Currently I use PostgreSQL, but thought of
switching to H2. During my testing, I found out that in the H2
INFORMATION_SCHEMA there are missing several tables.

The tables I need are:
tables, columns, key_column_usage, table_constraints,
referential_constraints, check_constraints, constraint_column_usage,
view_column_usage, views.

I already found some priority 2 item on the roadmap regarding
key_column_usage, but I could not find some overview of the H2
information schema. So my questions are the following:

* Is there an overview over the INFORMATION_SCHEMA in H2 to be found
somewhere?

* Is there any estimate if/when the mentioned tables will be added to
the information schema?

Best regards,
Boris Stumm

[Thomas Mueller]

Hi,

> I found out that in the H2
> INFORMATION_SCHEMA there are missing several tables.
>
> The tables I need are:
> tables, columns, key_column_usage, table_constraints,
> referential_constraints, check_constraints, constraint_column_usage,
> view_column_usage, views.

Did you try the H2 Console tool? You will get the list of tables if
you execute this query:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

Both MySQL and PostgreSQL support KEY_COLUMN_USAGE, but the following
tables are only supported in PostgreSQL: table_constraints,
referential_constraints, check_constraints, constraint_column_usage,
view_column_usage.

I suggest to use the DatabaseMetaData, those methods are standardized.

Regards,
Thomas

> * Is there an overview over the INFORMATION_SCHEMA in H2 to be found
> somewhere?

Yes, execute the command:
HELP INFORMATION_SCHEMA;

> * Is there any estimate if/when the mentioned tables will be added to
> the information schema?

Because those tables are non-standard, I did not plan so far to add
them at all. Could you explain why you like to have them?

Thanks,
Thomas

[Boris Stumm]

Hello,

On Jan 29, 10:35 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Did you try the H2 Console tool? You will get the list of tables if
> you execute this query:
> SELECT TABLE_SCHEMA, TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES

[...]

> > * Is there an overview over the INFORMATION_SCHEMA in H2 to be found
> > somewhere?
>
> Yes, execute the command:
> HELP INFORMATION_SCHEMA;

No, sorry. I will have a look at this.

> Both MySQL and PostgreSQL support KEY_COLUMN_USAGE, but the following
> tables are only supported in PostgreSQL: table_constraints,
> referential_constraints, check_constraints, constraint_column_usage,
> view_column_usage.
>
> I suggest to use the DatabaseMetaData, those methods are standardized.
>

> > * Is there any estimate  if/when the mentioned tables will be added to
> > the information schema?
>
> Because those tables are non-standard, I did not plan so far to add
> them at all. Could you explain why you like to have them?

I actually had a look at the SQL:2003 standard, but
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
says that all of these tables already were there in the SQL92
standard.

What I want to do is basically get as much information as possible
from
the database. DatabaseMetaData will tell me about tables, columns and
keys, but not about CHECK clauses and not give me VIEW definitions
and dependencies. That is why I access the INFORMATION_SCHEMA
to get this info.
I wanted to use H2 for reasons of simplicity and portability, but
since
is it a research project, it does not really matter which database I
use,
so I can just stick with PostgreSQL for this specific task.

Thanks for your help!

Boris

[Thomas Mueller]

Hi,

Thanks a lot for the link! I was not aware the PostgreSQL tables are
part of the standard. None of the databases I have tested supports
those. I have added a feature request: Support standard
INFORMATION_SCHEMA tables. I'm not sure however when I can implement
that.

Regards,
Thomas