Improvements to java.sql.DatabaseMetaData

[prrvchr]

Hi all,

I'm trying to integrate the SQLite JDBC driver into my jdbcDriverOOo extension which normally allows any JDBC driver to work in LibreOffice / OpenOffice Base.

From the first tests, it seems that java.sql.DatabaseMetaData does not return certain values necessary for the correct management of the schema and tables in Base.

Are you prepared to make the necessary changes to DatabaseMetaData if I find the methods need fixing?

Thanks .

[gotson]

if you can provide small sample of codes, unit test like, with a table creation and a query to show what's missing, we can have a look at it

[prrvchr]

Hi gotson,

Unfortunately, I don't have any test case, but I know roughly what Base is asking for.

For table creation, it seems that no DataType returned by DatabaseMetaData.getTypeInfo() has the AUTO_INCREMENT column set to true. Normally it seems that DataType: INTEGER, BIGINT, TINYINT, SMALLINT, NUMERIC, DECIMAL should be autoincrement and have AUTO_INCREMENT column set to true.
This prevents me from being able to handle auto-increments in creating tables in LibreOffice / OpenOffice Base...

For a complete management of the views this seems to me more complicated, since Base uses the INFORMATION_SCHEMA.VIEWS table in order to know the SQL command of each view.
Unfortunately SQLite does not give access to these system tables even if we claim the SYSTEM TABLE type tables in the DatabaseMetaData.getTables() method.

[gotson]

What's Base?

[prrvchr]

LibreOffice / OpenOffice Base

[gotson]

I just checked DatabaseMetaData#getTypeInfo(), and so far it's always returning the same thing, and indeed doesn't return AUTO_INCREMENT to true for any of the types. It should return true for the INTEGER type at least.

For a complete management of the views this seems to me more complicated, since Base uses the INFORMATION_SCHEMA.VIEWS table in order to know the SQL command of each view

I would say that's incorrect, since INFORMATION_SCHEMA.VIEWS doesn't exist in all RDBMS. It doesn't exist in Oracle apparently.

SQLite does not give access to these system tables even if we claim the SYSTEM TABLE type tables in the DatabaseMetaData.getTables() method.

I just checked, and DatabaseMetaData#getTables() only returns TABLE and VIEW. It should probably be changed to also return SYSTEM_TABLE, but if Base only expects INFORMATION_SCHEMA.VIEWS that won't really help you, will it ?

[prrvchr]

I just checked DatabaseMetaData#getTypeInfo(), and so far it's always returning the same thing, and indeed doesn't return AUTO_INCREMENT to true for any of the types. It should return true for the INTEGER type at least.

Great, if it can be done, it will allow Base to handle autoincrements when creating a table with an INTEGER column.

I would say that's incorrect, since INFORMATION_SCHEMA.VIEWS doesn't exist in all RDBMS. It doesn't exist in Oracle apparently.

Although this view is not part of any standard at first glance, it can be found in any JDBC driver:

• HsqlDB: INFORMATION_SCHEMA.VIEWS
• MariaDB JDBC driver: information_scheme.VIEWS
• H2: INFORMATION_SCHEMA.VIEWS
• Derby: SYS.SYSVIEWS
• I don't know about Oracle JDBC driver, I don't have Oracle base. ;-)

List of databases providing INFORMATION_SCHEMA

It allows Base in addition to creation, to modify a view (this is possible in graphic mode or in text mode).
It would be really great, if it could be implemented in SQLite JDBC to allow full integration of SQLite in LibreOffice / OpenOffice.

Thanks.

[gotson]

It would be really great, if it could be implemented in SQLite JDBC to allow full integration of SQLite in LibreOffice / OpenOffice.

if it's not in SQLite, it cannot be in the JDBC driver.

[prrvchr]

if it's not in SQLite, it cannot be in the JDBC driver.

Is it worth claiming it from the SQLite team?
If not or as a replacement, could the sqlite_master SYSTEM TABLE be accessible?

[gotson]

Is it worth claiming it from the SQLite team?

You can try

could the sqlite_master SYSTEM TABLE be accessible?

If Base expects the other one, how would that solve your issue?

[prrvchr]

If Base expects the other one, how would that solve your issue?

Specifically Base needs to know for each view:

• the name of the Catalog.
• the name of the Schema.
• the name of the View.
• the SQL command (SELECT ...)
• the CheckOption (always NONE)

After some tests it seems that the table sqlite_master, although not listed by DatabaseMetaData#getTables(), is accessible and allows me to extract the necessary data.

Does SQLite support the ALTER VIEW ... AS ... command, because my current implementation performs a DROP VIEW ... then a CREATE VIEW ... which has a dereferencing issue?

There remains a problem with the reading of the primary keys and indexes, I need to do more investigation in order to be able to say where it comes from.
It looks like DatabaseMetaData#getPrimaryKeys() returns a null PK_NAME column. Would it be possible to get the name of the primary key in this column?

[gotson]

Does SQLite support the ALTER VIEW ... AS ... command, because my current implementation performs a DROP VIEW ... then a CREATE VIEW ... which has a dereferencing issue?

that is a generic SQLite question, better suited to check on the official SQLite website or StackOverflow

It looks like DatabaseMetaData#getPrimaryKeys() returns a null PK_NAME column. Would it be possible to get the name of the primary key in this column?

PK_NAME is the name of the primary key, ie the constraint. Not the name of the column it applies on. In SQLite the constraint can be unnamed, in which case this should be null.

[gotson]

@prrvchr the latest snapshot has the enhancement for getTypeInfo to return the correct value for auto increment, among a few other things. Please give it a try.

[prrvchr]

@gotson Thanks for the improvement, I'll test and let you know.

[prrvchr]

@gotson I'm sorry but I failed to get the jar archive from the source files.
Maybe you have some advice for that.
Thanks

[gotson]

It's published on maven snapshots

[gotson]

I've added more changes so that getTables would return sqlite_schema as a SYSTEM TABLE

[prrvchr]

Hi, gotson

Thank you for these new changes.
I just tested with the first snapshot and it allows the creation of autoincrements.

I still have one last problem to solve which prevents the edition of the contents of the tables.
I need to do more investigation to find out what is preventing this edit.
I allow myself to come back as soon as I have more information.

[prrvchr]

@gotson
I finally managed to integrate version 3.42.0.0 in LibreOffice / OpenOffice.

Two extensions are available:
jdbcDriverOOo which allows the use of SQLite in normal mode.
SQLiteOOo which allows the use of SQLite in integrated mode (a single odb file).

I wanted to thank you for the improvements you made that made this possible...