HSQL Text Table Integration

We plan to create a user interface for HSQLDB's feature of linking external Text/CSV files as if they were native HSQL tables. See the specification for details.

This page collects issues around this project.

HSQL issues

Thinking about the whole feature, there's a number of issues in the current implementation of HSQL, mostly feature-wise.

Current Text Table Settings (P2)

We need a possibility to obtain *current* text table settings

ideally (?), this is some result set listing the various settings of a concrete (or all?) tables. But perhaps a simple

 CALL "<function>"( <tableName> )

which just returns the complete text source string, is sufficient

Date Formats (P2)

Only YYYY-MM-DD is accepted as date format ATM.

I think using a java.text.(Simple)DateFormatter, probably even a (per-table) format string, is better. In an international world, we cannot expect all our users to normalize their files for date formats.

Numeric Formats (P2)

Only "." as decimal separator, no thousands separator recognized

Similar to the previous item, this is pretty unacceptable for an non-en-US audience. I suppose java.text.DecimalFormat is the right class here to use. IMO, parametrizing a text table with a decimal and a group/thousands separator is sufficient, I don't intend to allow for the full functionality of a java.text.DecimalFormat.

Error Messages (P3)

In general, there are various causes for why creating / setting a source for a text table can fail. Most interesting to me, the file could have a wrong format (e.g. not enough data in a row, or wrongly formatted data), or the data could not confor to the PK/Index restrictions.

Current error handling here is rather generous, not telling the user what actually went wrong. We should improve here, as only meaninful error messages from HSQL enable the OOo user to find and fix the problem.

Charsets / Encodings (P3)

When specifying an encoding not supported as a Java Charset, a warning should be issued.

Currently, if an invalid encoding is specified, this problem is completely silenced. We need a mechanism here to better propagate this error.

Also (but this might be a different problem), we need a mechanism to tell unsupported encodings apart from supported ones, *before* actually issueing the SET TABLE SOURCE command. (Fortunately Java uses IANA names for charsets, as does OOo internally, so we at least speak the same language. I love standards. :)

Quote characters (P4)

Users should be able to choose the quote character

Row Order (P?)

Normally, for database tables you cannot rely on the order of records, since it's a row *set*, not a row *sequence*.

However, for Text/CSV files, this is different. Users here might expect that the rows are in the same order as in the file.

However, this is not the case in HSQLDB's text tables as soon as the table has a PK/Index.