The user-community revisited this
problem with data corruption in the Fall of 2010. The results are very encouraging. To begin, I should clarify that the data loss issue is
not attributed to the built-in
HyperSQL database engine (HSQLDB). We find no fault or instability associated with this third-party database engine. The fault lies entirely with Base, and is limited to the all-in-one "embedded database" ODB file created by the "New Database" wizard. This wizard uniquely 'embeds' the user-data files associated with HSQLDB
inside the Base container file (.odb) where they become susceptible to corruption
by Base. And unfortunately, a similar
propensity for file corruption can impact all OOo/AOO/LibO users (Writer, Calc, etc) so consider saving all related documents and databases to a
protected folder utilizing the latest
version history features.
- Note the status bar below (shown at the bottom of the Base window) indicates an 'embedded database' is in use.
There is a fix, however, for Base...
The data loss problem is easily overcome because Base also supports a traditional "
split database" configuration using the 'Connect to an
existing database' wizard. In this configuration, the
back-end database files (Tables & Data) are maintained separately & exclusively by the database engine. So the database data is
inherently safe from corruption by Base.
Problem solved. And despite the wizard's name, a new database is generated, as needed, by the HSQLDB engine. This Base wizard is a bit daunting, so simply download a
preset Base template which automatically creates a split HSQL 2.3.x database within a portable folder. Existing
"embedded databases" can be converted to this "split database" configuration while maintaining seamless database access with Base or
other front-ends.
- Note the status bar below indicates a split-database configuration.
It remains prudent to protect this (split database) folder using automated backup/file-history options as mentioned previously, because several
front-end components remain inside the Base (.odb) file including your Forms, Queries, Reports and Macros. And while you're not likely to encounter
back-end database corruption in this configuration,
no system is fully immune...enough said. So for peace-of-mind, simply move (drag-&-drop) the split-database folder to your
cloud backup folder where you can run the database without fear of losing critical data (recommend
SpiderOak for
personal encryption and unlimited
file-history features).
A split database can also be run in 'server mode' in support of multiple users (see the definitions, notes, and links below). Some users also
export their Forms which effectively hides the Base environment at runtime.
You'll find additional resources including an
automated wizard under the "
Related posts" links below. But suffice to say that we encourage the use of HSQLDB with Base in a proper configuration, even over alternative engines, due to the tight-integration including
seamless engine startup. Additional benefits include unsurpassed: ease-of-setup, Base-community support, cross-platform and portability options. In fact, when paired with the latest
HSQLDB 2.x engine there's
little reason to supplant HSQLDB for small-to-medium size database projects including encrypted, multi-user (50+ simultaneous users) database-access on a network (LAN) with role-based security.
This is not to overlook the merits of other good options with Base such as
H2,
PostgreSQL,
MySQL/
MariaDB,
Firebird,
SQL Server Express,
Oracle Database XE, etc. If you have significant experience with these or need features not available with HSQLDB 2.x then your choice is clear. But rest assured, these are
not necessary to achieve a
reliable, even
full-featured, desktop-database with Base. And I think you'll find that the user-community is best primed for HSQLDB support with Base.
Here's a
glossary of terms associated with this topic.
'file mode' runs HSQLDB "
in-process" with *Office for seamless database access by a single-user. The user-experience is similar to the default "
embedded database" configuration in terms of automatic engine start-up and shutdown with Base. The available
Base templates utilize this single-user configuration.
'server mode' employs HSQLDB in the standalone server/listener configuration requiring separate engine start-up/shutdown. This mode enables concurrent, multi-user, read/write access with role-based user-security over a network. It also contributes to data
durability through additional layers of protection.
'split database' terminology is adopted to provide distinction from the Base default "embedded database" configuration. A
split database refers to the separation of files on disk; separate front-end (Base) files and back-end (database) files. So a portable split-database will consist of multiple files in a single
folder rather than 'embedded' within a single [
zip-archive] file. The status bar in Base will reflect "
JDBC" when running a split database configuration based on the bundled Java engine (HSQLDB).
- Note: A split database can be run in 'file' or 'server' mode interchangeably. Server-mode supports multiple, concurrent users but is more difficult to setup and manage (see the related links below). File-mode is preferred in single-user environments due to the relative ease, including seamless engine management and database portability when using an available Base template (.odb).
Note: Microsoft has also adopted this 'split database' terminology as they recognized the need to separate MS Access databases into the respective components for a variety of reasons, including data-durability.
Note: The current templates are a boon for split-database portability. But to take portability to the next level, consider installing LibreOffice Portable & JavaPortable to a cloud folder or perhaps a fast portable drive. This allows you to run your database application entirely from the portable folder without installation on the host computer.
Note: Cloud storage has come-of-age through synchronized folders such as we find with Dropbox, Google Drive, SkyDrive, Box, Wuala, SpiderOak and many more. Cloud storage won't effect database performance because everything is run from your local disk; cloud storage is simply an automated backup and synchronization medium. Most cloud storage solutions include 'file history/versioning.' This feature is essential with databases and other critical documents, since you can recover previous versions of each file in the event of file-corruption/accidental deletion/etc. And while all of these services encrypt your files in-transit and on the cloud servers, only 'client-side encryption' can guarantee your data is secure from prying-eyes at the cloud server-farm or in response to a court-order or government-oversight. In response, we are now seeing the rapid introduction of cloud-backup services featuring 'client-side encryption.' This ensures that nobody can access the contents of your cloud-based files without access to your personal computer(s). Wuala and SpiderOak have implemented client-side encryption, while Boxcrypt is an add-on used to add this critical layer of encryption to most other popular cloud-storage solutions.
I should emphasize that a cloud-sync'd folder is not appropriate for simultaneous read/write database sharing among multiple users, although non-concurrent access is possible. Otherwise, concurrent internet access to your database involves running the HSQL engine in 'web server mode' and the appropriate 'https' protocol in the HSQLDB startup string. This is different than the 'hsql' protocol used for LAN database access. For web access, you'll generally need admin access to the web-server in order to setup HSQLDB and the necessary Java support. Many web hosting services already offer Java support in the form of Tomcat, Jetty, GlassFish, etc. Once setup, running HSQLDB in web-server mode allows concurrent internet access from Base or other front-ends. HSQLDB 2.x supports connection pooling and MVCC (multi-version concurrency control) so in theory, HSQLDB running within an adequate JVM (Java heap set to 1GB RAM or more; -Xmx1024m) provides a robust online database capable of supporting hundreds of simultaneous read/write users -- seamlessly using Base (*Office) for remote access. Realistically, web-server database deployment is the domain of IT professionals using MySQL, PostgreSQL, Firebird, SQL Server, Oracle, etc. combined with browser-based front-ends hosting Rich Internet Apps.
'Class path' refers to the location of critical program components in Java environments. In database parlance the 'class path' refers to the location (folder path) of a
JDBC driver. Drivers are necessary to connect with any RDBMS engine including the built-in HSQLDB engine, SQLite, or otherwise.
- Note: The user-community has traditionally relied on the 'global' class path settings in support of 'split HSQL databases.' This 'global' solution worked, but it also disabled support for legacy 'embedded databases' while risking inadvertent database upgrade and compatibility headaches -- effectively limiting the Base installation to a specific HSQLDB version while killing database portability. Recent user-community developments have eliminated use of the global class path setting, thereby eliminating the drawbacks. We now leverage 'session' class path settings accessible only through macros (preset; no coding necessary). The resulting macro-enhanced 'split HSQL database' templates or add-ins have eased split-database adoption, while largely supplanting the 'New database' wizard in Base, and effectively eliminating the need for a default database engine in Base. Simply download a macro-enhanced template, which instantly creates a new 'split HSQL database' utilizing the latest HSQLDB engine in a fully-portable database package (folder).
Given the advantages of a macro-derived session class path, we now strongly discourage manual/global class path setup, in favor of adopting macro-enhanced templates for all single-user HSQL database needs with Base.
Note: Multi-user support is also possible through future macro automation (templates), but for now it is necessary to revert to the manual global class path setup process on each computer when supporting multiple users accessing a 'split HSQL database' running in 'server-mode.' This requirement will be relatively easy to eliminate as template development continues.
'Data-source URL' refers to the database connection settings. These settings are stored within the Base (.odb) file. We can setup the data-source URL with a macro or manually in Base (
Edit > Database > Properties). This somewhat-cryptic string of characters determines the database-access mode: file or server mode.
- 'File mode' is seamless, allowing us to click directly on a properly-configured Base file (.odb) for startup.
But 'server mode' requires separate HSQLDB startup for Table access. So it becomes a two-step process: (1) start HSQLDB in 'server mode' (2) and click mydb.server.odb to open the front-end in Base. This process is typically automated using a script/batch file. For example, the provided Windows script file ('START.vbs') automates all aspects of 'server mode' through a single mouse click. The associated Windows batch files (server.start.bat and server.stop.bat) will require some tweaking to reflect your actual folder-paths. This setup process is consolidated in a nice Windows system-tray app created by Greengiant224 found here.
Related links (must reads) (in particular the linked post)
Split HSQL database setup instructions:
Legacy database recovery and migration:
Base limitations as a database manager:
Slow database remedies:
Server mode (multi-user) setup links:
