Base is a graphical user interface (GUI) used primarily to connect OpenOffice documents to various databases (data sources). Base generates a connection file (.odb) which provides access to a data sources through special software drivers. Base contributes only “front-end” components including table-views, stored queries, forms, reports and macros. OpenOffice is bundled with various “back-end” options including spreadsheet access, dBase (flat-file) table creation and access, as well as an SQL (relational) database-engine (HSQLDB). The bundled HSQLDB engine is often sufficient, but HSQLDB 2.x takes Base to new levels with all of the latest desktop database features and functions.
Base is a database connection tool. Base can be used to generate connections to various data-sources, thereby enabling data access/integration across the *Office suite. To provide this function, a Base wizard is used to create a connection file (.odb). This standalone file is then ‘registered’ in *Office for universal access to the data-source. This process may be repeated to gain access to additional data-sources. Simply press the ‘F4′ key from any *Office application to view all registered data-sources.
Typical data-source usage includes:
- Writer (mail-merge, forms),
- Calc (pivot tables, graphs, forms),
- Impress (dynamic slide data, graphs),
- Base (stored queries, forms, reports).
- Supported data sources include:
- spreadsheets (tabular data; read-only),
- delimited text files (CSV; read-only),
- popular address book formats,
- dBase tables (one table per file; read/write to include table creation),
- SQL relational databases (multiple tables; read/write plus many additional functions).
- a command-line SQL console
- a visual table designer
- an index manager
- editable table views
- a visual data-relationships designer (cardinality)
- a decent visual query builder
- a relatively powerful form designer
- a rudimentary reports designer [requires JRE]
- a macro development environment
- various extensions compliment this list nicely
Database Application Development
Base provides a database application development environment. Base is merely a front-end, but it’s bundled with a few ‘back-end’ database options. You’ll find both flat-file (non-relational) and relational database functionality included. “Flat-file” connection drivers (dBase, Spreadsheet and/or Text) may offer SQL-like functions, but don’t expect them to be as extensive as those available when connecting Base to an SQL engine. Some additional function is provided by Base SubForms, which support filtering and ad hoc relationships even among flat-file tables. Beyond these Base functions, Calc ‘pivot tables’ offer pseudo-queries including aggregate functions, while Calc charting is suitable for reports. So flat-file tables can provide entry-level database functionality, sufficient for many home-projects including a simple contact list, inventory, etc. But when data redundancies become a liability, or the full power of SQL is desired, it’s time to connect Base to an SQL engine such as the bundled HSQL database engine. In this case, the ‘existing database’ wizard should be used to create a ‘JDBC’ connection with HSQL (see the ‘split database’ setup steps below). This engine adds a plethora of features, functions, and query options to the mix. So, while you’re not limited to the installed back-end database options, everything required to create a complete *Office database application is included with Base. A quick summary of the bundled back-end options include:
- flat-file table (Calc spreadsheet with tabular data, CSV, or dBase file)
- flat-file table queries (dBase file-format generated through Base; single-table query support; one table per file; similar to MS Works)
- flat-file based functions (provided by Base through selected flat-file ‘drivers’ such as dBase, Text, etc.)
- flat-file filtering and ad hoc relationships (provided by SubForms in Base/Writer/Calc)
- flat-file pseudo queries (provided by Calc through Pivot Tables)
- SQL relational database (multiple related tables and extensive functionality through the included HSQL database engine; similar to MS Access + MS Jet engine).
Base offers to create an all-in-one ‘embedded database’ file similar to MS Access. In this configuration, the front-end components (queries, forms, reports and macros) are packaged together with the database files produced by HSQLDB, all within a single Base (.odb) file. Unfortunately, this single-file concept has proven unreliable with Base, leading to rampant data corruption. So it’s best to avoid the ‘Create a new database’ wizard in Base thereby avoiding ‘embedded database’ files (perhaps with the exception of developing prototypes or distributingexamples). Fortunately, Base offers an alternative wizard which produces a robust ‘split-database‘ configuration in which the database itself (tables and user-data) is maintained as a separate file (or files) as necessary for the proper stability. Steps for creating a new ‘split-database’ utilizing the built-in HSQL database-engine with Base are outlined below.
Java is used to run various wizards and data sources such as the built-in “HSQL relational database engine.” With a Java Runtime Environment (JRE) installed (even portably) Base runs seamlessly with various Java databases including the built-in HSQLDB. This has some advantages over SQLite, and perhaps over other relational database (RDBMS) options. Like SQLite, Java database-engines are light-weight, fast, cross-platform and run seamlessly (auto start/stop) with Base. But Java databases enjoy broad support by the Base GUI including most database creation (DDL) and data manipulation (DML) tasks. And Java databases are particularly rich in features. For example, modern Java databases like HSQLDB 2.x, H2 and perhaps Apache Derby offer advanced features including: ACID transactions, modern SQL syntax, data-typing, custom functions, stored procedures, encryption, hot-backups, and advanced multi-user support (MVCC). These additional features can ease project-development to a degree. For instance Base queries, forms and reports thrive in a full-featured SQL environment, thereby avoiding macros. Something as simple as ‘data-typing’ enables flexible queries based on date/time fields; something that becomes a nightmare with a weak data-typed engine like SQLite. And as the need arises, these Java engines scale modestly in support of multiple, concurrent users. So Java database engines occupy a niche on the desktop and pair well with Base, expanding the scope and potential of Base projects while easing application development. Given the advantages, it’s no wonder that HSQLDB 1.8 (released July 2005) was selected over SQLite as the default with Base — albeit a rather dated/crippled version of HSQLDB by today’s standards. But you’re not confined to the bundled version/engine. HSQLDB 2.x and H2 are much more advanced and particularly well-suited for Base projects. You’ll also find wide support for HSQLDB and H2 in the Base community forums and other support channels.
HSQLDB is an open source, cross-platform, SQL, relational database management system (RDBMS). It’s also known as HyperSQL or HSQL database engine. It’s written in Java so a JRE (Java Runtime Environment) must be installed (or available as a portable app) in order to utilize this database engine.
Version 126.96.36.199 (circa 2005) of HSQLDB is integrated with *Office (AOO/LibO).
The integrated JDBC driver supports several HSQLDB operating modes including two with Base.
- In-process (‘file mode’) provides seamless, single-user access with Base. “Seamless” implies automated database access with no need to start/stop a database server; similar to H2,dBase, and SQLite.
- Client-server (‘server mode’) provides multi-user access on a network or locally from client-applications such as Base.
HSQLDB is a robust, transactional, database engine with ACID properties and a mature code base. These same technologies are at the heart of PostgreSQL, MySQL, Oracle, DB2, MS SQL Server, etc. In addition, HSQLDB employs a transaction log with auto-recovery. An automatic backup file is also maintained if enabled. The end-user can supplement these measures by storing the database files in a folder protected by automatic backup software or cloud-sync services. It’s also prudent to run database applications on a computer protected by an ‘uninterruptable power supply’ (UPS) or on a laptop with the battery installed. These measures avoid loss (rollback) of any buffered data in the event of a power failure. Speaking of buffered data, all transactional database engines employ a write-delay buffer to enhance performance. The buffer can be set to 0 seconds at the expense of write performance. HSQLDB uses a modest 500ms buffer by default, so new data is safely written to disk after a half-second delay. Base sets this write-delay buffer to 60 seconds for all ‘embedded databases’ files, which is excessive, but most folks can recover from the loss of one minute’s productivity in the event of a power failure or similar event. In all cases, buffered data is written immediately to disk upon normal shutdown. Advanced, enterprise-level, reliability measures such as hot backups, clustering and replication, designed for 24/7 availability, are not available with HSQLDB 1.8 — although hot backups are supported by HSQLDB 2.x, while clustering is oddly-enough a feature of H2 database.
HSQL Database Template
A macro-enhanced Base template (.odb) as offered in the link below is highly recommended for single-user, split-database access. These templates do not require manual Class Path setup as outlined below. The templates use a macro-derived ‘session’ Class Path as opposed to a manually-setup ‘global’ Class Path. So unless you’re running a split HSQL database in server-mode (multi-user support), there’s no good reason to setup the global Class Path manually in Base.
See Wizard – Portable’Split’ HSQL Database Template.