hsqldb 2.28 or 2.30 Beta and Autovalue ID's

[MikeytheMagnificent]

The attractions of autovalue in split mode for ID fields via the GUI is much appreciated in 2.3, although it has always been available via sql , has anyone discovered any bugs making it difficult to settle with 2.30.
I wizarded both, far too late last evening, and apart from the descriptive filenames from DACM tutorial wondered how, after changing the names to something more personal perhaps, I would be able to version the hsql to remind myself which version I am using, and indeed how to update to a newer compatible version when applicable??? or even, heaven forbid, need to revert to the earlier version without a fresh install

I note that field editability is still only available for the table creation session, thereafter they revert to 'grayed out' so hanging on to a populated data set later when you may need to edit a fields properties (say simply increase the length of a text field) is not all summer sailing' since cutting and re-pasting the offending field seems to destroy its data. With 1.8 Embedded this was not a problem as the field properties are always editable (within reason) ID fields not so straightforward
Any GUI suggestions to achieve this??? or need we revert to a sql alter table.

One tiny aside related to all thisSolveig pointed out how to make the cursor auto appear in the first form field by setting auto control focus (ACF) which must be done in design mode (despite the icon active and toggling meaninglessly in data mode). Make sure the first field is not ID(entity) and/or Read Only because the cursor will still land in that field (also meaninglessly) for the first record only, thereafter it omits it. Also similarly strange, if you are browsing/editing existing records whose first tab is an editable one containing data, the cursor now appears beginning of the first tab of the first record, ready to prepend data but at the end of ALL subequent tabs ie. in overtype mode. I can't see anything in FPDB (form properties Dialog) or CPDB although analyse SQL command (NO) makes the form read only (may be a bug) Its somehow as if the first tab is set before the form is actually built.So is ACF effectively a SQL property or a GUI property???
Any 'fix' ideas welcome,

[DACM]

...field edit-ability is still only available for the table creation session, thereafter they revert to 'grayed out'...need to edit a fields properties...Any GUI suggestions to achieve this??? or need we revert to a sql alter table.

Unfortunately, the [LibreOffice] devs remain preoccupied with the embedded database concept based on yet another default engine (Firebird). They're determined to package the database, itself, into single-file database (.odb), while deleting the source database -- precisely in the manner we've grown to lament today with HSQLDB Embedded. They don't seem to understand or acknowledge that the user community has shelved this particular implementation because it is inherently unreliable (as confirmed by Microsoft with their own MDB/ACCDB files). We've also moved beyond the idea of a default database with Base. This actually free's the devs to eliminate Java dependencies from the LibO/AOO code-base, perhaps with the exception of the hooks necessary for JDBC support.

With "templates" such as these (macro-enhanced Base front-end files), we don't need a default database or a single-file database (ODB) for portability. We now enjoy a fully-portable, cross-platform, reliable solution for the first time with Base. If we really need a single-file for distribution purposes, we can simply right-click the database folder and produce a [self-extracting?] ZIP file. And this split-database solution is backwards-compatible with all versions of AOO, LibO, and OOo 3.3, assuming a JRE and macros enabled in *Office. Unfortunately, we don't enjoy the best ease-of-use during table schema modification through the GUI. Apparently, there's no developer interest in DDL support through the GUI for externally-connected (JDBC/ODBC) databases (HSQLDB, H2, PostgreSQL, MySQL, Firebird, Oracle, etc.). Ironically, this is where all efforts should be focused, because I would argue that to date, there's been no reason to have an SDBC driver for HSQLDB Embedded (ODB) support (see below); Firebird Embedded is another matter, but it should be possible to employ DSN-less connectivity using the platform-specific Firebird ODBC driver supplied with LibreOffice. Overall, I think proper JDBC/ODBC support would be as easy as an expanded table of commands stored within the ODB file, such as we currently enjoy for AutoValue setup.

In other words, the devs are currently stuck off-track, so you're stuck with SQL when the GUI workarounds prove insufficient for DDL tasks.

And if you consider SQL difficult, just wait until you need something as simple as an AutoValue column with 'Firebird Embedded' available as an experimental feature in LibreOffice 4.2. While these auto-incremented columns (often used as primary keys) are an essential feature built-into all modern database engines, Firebird is apparently stuck in a bygone-era championed only by silver-haired database professionals enjoying the job-security. I'm a fan of Firebird among open source database options, but only when paired with a proper database manager. Unfortunately, Base pales in comparison, and the default 'Firebird Embedded' document-format (Base .odb) is essentially a zip-archive (ODF v1.2 notwithstanding), so third-party tools are rendered useless for all but the most savvy Base users. So we're on our own with 'Firebird Embedded' in LibreOffice until we see proper DDL support in Base.

As an aside, I'm not personally convinced that the SDBC driver project was ever necessary for HSQLDB integration, because they never implemented the original single-file backend concept, apparently choosing instead to unzip and rezip the ODB for each Base session, while deleting the split database! At the point of unzip (splitting the database from the ODB), they could have simply used the official JDBC driver, on a cross-platform basis, as evidenced by our Base templates and FreeHSQLDB extension. That might have alleviated some of the holes in Base DDL-functionality (cited above) associated with external connectivity (JDBC/ODBC). In any case, deleting the split-database on a session-basis is an inappropriate implementation of ODB destined for data-corruption at the end-user's expense.

So the SDBC driver (for HSQLDB embedded) and risky ODB implementation (session database deletion) have failed us, while causing far-reaching credibility issues for Base, Open/LibreOffice, and the ODF spec. Perhaps unbelievably, the LibreOffice devs are determined to employ the same failed concepts using Firebird, as we speak. This really begs the question: Are the devs simply unaware of the issues and remedies with respect to database data-durability and portability? It seems well-proven that database portability can be achieved with or without ODB-embedding in a data-safe manner. After all, we can currently move portable split-database folder with ease, using drag-&-drop or copy/paste, just like moving a single file. The database works immediately in any location. Likewise, we can or zip a split-database folder for single-file distribution at any time, without deleting the source database -- thereby eliminating the risks inherent to session-based embedding. In other words, we don't carelessly delete the split-database on a session basis in order to generate a single-file database (ODB/ZIP). That might have been the goal at some point in history in order to promote the ODF spec through ODB attributes. But we have learned all-too-well that ODB/ZIP packaging can result in file-corruption and data-loss. So it's imperative that we now adopt a persistent split-database mentality, while packaging single-file databases (ODB/ZIP) by exception and without deleting the source database. Otherwise, the concept of a single-file database for distribution is simply too risky.
...