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.
Contents
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.
Relative Paths (P3)
Not all types of relative paths are allowed: trying to set a text table's source to something like "../filename" results in an "Access Denied" error message.
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. They're used to doing so from other CSV integrations in OpenOffice.org, but HSQLDB currently does not allow this.
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.
Design Issues
Persistence
Should settings made in the linked table editor be remembered for future invocations? If yes: Per data source? Per session? Do we need a full-blown administration of "setting sets", similar to what MSA has (Load/Save)?
Relative Path Compatibility
What happens when we open an old embedded HSQLDB .odb file, where the textdb.allow_full_path property is FALSE? Should we automatically set it to TRUE, assuming TEXT TABLES are a rarely used feature so far?
Keeping the table up to date
audionuma@gmail.com I've got some issues concerning keeping the data displayed by Base up to date when the source file of a text table is edited by another process while the base document is opened. Under Mac OS X.4.7 and OOo 2.0.3, I can set a text table in a base document thru Tools > SQL ... If the source text file is edited while the Base document is opened, the modifications are not reflected in the Base document until I close the document and re-open it.
Arithmetic operators
On Base, when the database (i.e. the odb file) is created by connecting to a csv file, it seems that you can not use arithmetic operators on fields in a SELECT clause ; oddly enough, you can use trigonometric functions, for instance. Fixed in OOo 3.2.