| Edit: Various versions will be posted here as development progresses... |
- OVERVIEW: This Base template (.odb) generates a 'split database' as necessary for proper data-durability and other considerations. It serves as a replacement for the 'New database' wizard in Base. Simply download the template to an empty folder and open it in Base with macros enabled (or follow step-by-step instructions below).
The template combines a preset Base document (.odb) with the popular HSQLDB 2.x engine -- collectively providing one of the most powerful and flexible, cross-platform, database options available for the desktop today. Key features include: a new database creation wizard; drag-&-drop database portability; seamless HSQLDB 2.3.x integration; and a fix for the AutoValue setup bug in the Base GUI. Existing split-database users can add the macro (below) to enhance the portability of their own project. Likewise, a legacy 'embedded database' file may be converted to a 'split database' as necessary to avoid rampant data-corruption with Base.
A split-database involves multiple files, but the template maintains everything within a dedicated folder for portability and backup purposes. This folder may be renamed or moved as desired, or distributed as a single zip-archive file. The macro-automation ensures seamless database access on any computer (Windows, OS X, Linux) while uniquely allowing these 'split databases' to be run along-side legacy 'embedded databases' as desired. These databases work with AOO / LibO / portable on any computer with a proper Java Runtime Environment (JRE / portable) installed -- as also necessary with Base for legacy 'embedded database' file support. This template is configured for single-user access, suitable for non-concurrent database access, but the database itself may be hosted in 'server mode' for concurrent read/write access through Base or other front-ends on a LAN or web-server in multi-user scenarios. Needless to say, this database solution is much more powerful, flexible and robust than the default Base implementation.
However, initial table development is slightly easier with the default 'embedded-database' configuration due to better GUI support in Base. Specifically, the Table Designer (GUI) in Base exhibits some limitations with a 'split' database. Technically speaking, it comes down to the database connection driver; the Base Table Designer is better-tuned for the built-in SDBC driver used to connect Base with the bundled HSQL 1.8 database engine -- as opposed to other data-connectivity options including other SDBC, JDBC or ODBC drivers. Since HSQLDB 2.x requires 'JDBC' connectivity with Base, the Table Designer proves more limited. I say 'more limited' because the Base Table Designer proves inadequate in any case ('split' or 'embedded'), so workarounds are often necessary when managing table structures with this GUI tool. In any case, the Table Designer can be used to create, cut, copy, paste, insert or delete tables and fields (columns) in either configuration ('split' or 'embedded'). And as expected, we can specify individual field 'properties' (field name, length, data type, etc.) when creating a field. However, after saving the field to the table structure, it is no longer possible to modify these field-properties in a 'split' database, using the Table Designer GUI. In fact, existing properties will appear frozen (grayed-out) in the GUI when connected to a 'split' database. Thankfully we do have some [superior] alternatives. A GUI Table Designer such as we find in Base is simply a graphical tool used to generate and send the necessary SQL (DDL) commands to the 'back-end' database engine. These SQL (DDL) commands can be created manually and sent directly to the database engine using the Base SQL Console (Tools > SQL...). Novice database designers should also consider the excellent commercial GUI options (30-day free trial) available for 'split' database management. Otherwise, when relying solely on the Base Table Designer (GUI) to 'manage' table structures in a 'split' database, it becomes necessary to create a new field with all the necessary properties, while eventually deleting the old field. Of course, once we've added user-data, relations or constraints to a table, it can be more difficult by any means (including SQL/DDL) to modify or delete a field within that table. In particular, it may be necessary to suspend or remove associated table relations before deleting a field. In any case, it's a simple matter to copy data between compatible fields (similar data-type, adequate length, etc.) using SQL. All of that to say, we have several options including SQL, commercial GUI alternatives, and/or the Base GUI workarounds should it becomes necessary to 'modify' existing table structures in a split database, but some users might find it easier to begin with an 'embedded database' file for table-structure development, while converting to a 'split database' configuration before adding critical data to the tables.
In any case, this 'split HSQL database' solution is more than adequate and much easier to setup and use with Base than similar options including PostgreSQL, MySQL or Firebird. Thanks goes out to Villeroy for this critical tip because it's paving the way for some major breakthroughs in Base user-community support, such as this.
(1) Ensure global macro security in *Office is set to Medium (or Low):
(2) Remove any global Class Path to hsqldb.jar that you may have setup manually in *Office:
- Tools (*Office) > Options (Preferences) > *Office > Java/Advanced > Class Path > Remove
(3) Create a new folder on your computer to serve as a dedicated split-database folder (one per database)
(4) Download the above template and place this (.odb) file in the newly created folder.
(5) Then click to open the template in Base while selecting Enable Macros if prompted.
(6) Click the Tables icon in Base to begin (or optionally utilize SQL).
- The integral macro-wizard runs automatically. The macro initially prompts the user for a database name. It then adds a copy of the HSQLDB engine to the split-database folder ('driver' subfolder) and configures the template for single-user database access based on the current folder path. Upon clicking the Tables icon in Base, a connection is established to HSQLDB. The engine automatically creates a new database within the split-database folder ('database' subfolder). Thus, your entire Base project (including the database, engine, and Base front-end) is neatly contained within the dedicated folder you created in step 3 above. Feel free add standalone forms or additional subfolders (such as a portable 'images' subfolder) to your split-database folder.
This entire split-database folder is portable. With Base shutdown, you can move or rename this dedicated folder as desired (drag-&-drop the entire folder). Or you can zip the entire folder for archiving or electronic distribution purposes as a single-file. The template serves as your Base (.odb) front-end file used for storing your Queries, Forms, Reports and Macros. You can rename this ODB file, but it must remain inside the dedicated folder to support database portability. The integrated macro runs quietly, for a split-second, each time you open the ODB file in Base, in order to ensure database connectivity based on the current location (folder path). If you never plan to move or rename your split-database folder (or files), you can safely bypass/disable the macro, since the current settings are stored within the template (.odb). You could theoretically disable the macro and then move the ODB file independent of a fixed database location (since the path is saved), but this can affect your ease-of-backup as well. So it's best to leave everything within the dedicated folder, and simply create a desktop-shortcut to this ODB file and/or folder as desired. You can also register the ODB file in *Office as a 'data source' (Tools > Options > Base > Databases) for seamless access (F4) from Writer or Calc documents, including standalone forms.
Consider moving your entire split-database folder into a cloud folder (such as Dropbox, Google Drive or SpiderOak) to gain automated session backup with file-history and personal client-side encryption. A cloud folder can also provide non-concurrent internet access to the database.
Additional Information on the split-database concept, history, features, and limitations with Base:
- BACKGROUND: When 'Creating a new database' using the built-in Base wizard, a special Base (.odb) file is produced which encapsulates both front-end and back-end components of your database application including your queries, forms, reports, macros and tables. In other words, everything is stored within a single file (.odb). This all-in-one 'embedded database' concept is nice in theory, but the particular Base implementation is risky, and has proven highly-susceptible to data corruption. Fortunately, Base offers an alternative wizard dubbed 'Connect to an existing database.' This wizard generates a more traditional Base (.odb) file containing only the connection parameters and other front-end components (limited to queries, forms, reports and macros). The back-end database tables and user-data are maintained as separate files in this configuration, as necessary for data-reliability. This configuration is quite flexible, supporting both flat-file and relational databases, as well as multi-user environments. But users can get lost in the myriad of options and manual settings presented by the necessary connection wizard. Actually, it's quite simple to create flat-file database-tables through Base by selecting the 'dBase' option in the Base wizard (dubbed 'Connect to an Existing Database'). But unbridling the potential of a relational database is another matter. Relational databases traditionally involve separate DBMS installation, setup, and management. These tasks include database creation and schema development largely using hand-coded SQL (actually DDL), in addition to daily chores such as engine start-up, shutdown, data-backup, etc. And that's to say nothing of the cryptic connection settings involved with Base connectivity.
In response, the Base user-community has turned to pre-configured Base (.odb) templates, allowing users to bypass the cryptic wizards, while historically leveraging the bundled HSQLDB 1.8 engine. After all, this engine is reliable, seamless, portable, and enjoys tight integration and wide community support with Base. It generates new databases as necessary, through Base. It meets the database needs of most users, while providing an easy upgrade path to HSQLDB 2.x which enjoys a feature-set that rivals anything on the desktop today in both speed and function -- including billions of records, ANSI SQL, built-in functions, LOBs, encryption, and procedural SQL with triggers -- all for free. The HSQLDB engine scales modestly in multi-user environments complete with access control and hot backups. And as a bonus, HSQL databases are portable and cross-platform (Windows, Mac, Linux). This overall flexibility and SQL-power in an end-user product is unmatched today, even by MS Access (although the ease/appeal/depth of MS Access as a front-end is otherwise undisputed). The manual setup steps have been depreciated (in single-user configuration) because some issues remain with a macro-free approach. For instance, with a manual setup, database-folder portability requires tedious modification of the 'data source URL' associated with the Base (.odb) file. And while savvy users upgrade to the latest HSQLDB engine, this step can lead to unintended consequences for your legacy 'embedded database' files. Also, managing the required Class Path settings globally while ensuring HSQLDB version-compatibility across multiple computers, databases and *Office updates becomes a nightmare in practice. That is, until now...
Enter an era of macro-enhanced Base templates featuring:
- 1. automated 'split HSQL database' creation wizard
2. portable database-folders (automatic Class Path and Data-Source URL handling for seamless portability)
3. minimal setup (limited to manually enabling macros in *Office)
4. simultaneous support for all HSQL databases and versions (dedicated engine per database-folder; run along-side 'embedded databases')
5. drop-in support of existing 'split' HSQL databases (add the template to an existing database folder or copy the macro to an existing .odb)
6. built-in, light-weight, database management GUI with HSQLDB 2.x (click hsqldb.jar to start)
7. built-in, light-weight, command-line, database access tool (sqltool.jar; see the SqlTool documentation for use)
[future] 8. automated template begins as an 'embedded database' for initial table development, then expands on-command into a split-database for production-use as necessary for data-reliability
[future] 9. easy switching between single-user and multi-user access modes
[future] 10. templates optimized for large, multi-million record databases/result-sets (such as large outer-joins)
[on hold] 11. "Pack & Go" user-selection for automated portability of 'split' databases as a single-file
LIMITATIONS: While this macro-enhanced template attempts to overcome some glaring deficiencies with Base, it scarcely addresses the holes in the Base GUI with respect to DDL tasks (database schema development) when connected through standard ODBC/JDBC drivers. As mentioned in the overview above, the Base Table Designer exhibits an anomaly when connected to HSQLDB in a split-database configuration. The Table Designer can be used to create Tables and Fields in the normal fashion. But after saving the results, existing field-attributes appear frozen in the Table Designer. So existing attributes can't be modified using this graphical tool. The Table Designer also fails to setup Default field values for most data-types. These deficiencies are limited to the table-design phase, when field-attribute modifications are essential but rare. So when modifying existing table-structures with a split-database of any kind, even novice users should resort to SQL, perhaps utilizing the Base SQL console (Tools > SQL...) or an alternative GUI database manager. Consider these interactive SQL tutorials: link1, link2. However, some GUI workarounds do exist:Base limitations as a database manager:- The Frozen 'Field Property' Workaround (adjusting existing Table structures with Base including AutoValue Primary Keys and Default values)
Upgrade to HSQLDB 2.3.x (a fix for the AutoValue setup issue in Base)
Alternative SQL database managers (free, cross-platform, cross-database, administration tools)
- 1. automated 'split HSQL database' creation wizard
- Existing 'split HSQL database' users should add the following macro to their .odb while removing the global Class Path to hsqldb.jar (as outlined in step 2 above). In this case, you'll also need to add a copy of your current HSQLDB engine to your split-database folder (choose between the default hsqldb.jar 1.8.0.10 -or- upgraded hsqldb.jar 2.3.2). You may also be interested in a copy of sqltool.jar for command-line SQL support (or server-mode management automation), as included with the template above. These steps will preserve your database-compatibility in all cases, including portable environments, while returning Base to its originally installed configuration. This has become important for a few reasons. First, a global Class Path setup is no longer necessary since we now rely on a portable 'session' Class Path (in single-user environments) derived by the macro; this not only enhances portability but also allows simultaneous support for legacy 'embedded databases.' Second, and most critical, a global Class Path now risks inadvertent upgrade of all your HSQL databases accessed with OpenOffice/LibreOffice, including legacy 'embedded HSQL databases.' With the debut of HSQLDB 2.3, Base no longer recognizes the distinction between its built-in HSQLDB 1.8.0.10 version and the newest HSQLDB releases. This is particularly dangerous because inadvertent upgrade of an embedded database renders the ODB file unusable. So it's time to end the practice of setting a global Class Path manually in OpenOffice/LibreOffice in support of HSQLDB in single-user environments (multi-user server-mode HSQLDB access still requires global Class Path setup...for now).
Here's the source code (macro) found in the original mydb_wizard.odb. This macro is provided exclusively for existing 'split HSQL database' users in order to add the macro-enhanced features to their own .odb file while eliminating global Class Path use as outlined in step 2 of the 'Instructions for Use' above:[/color]Code: Select all
REM ***** BASIC ***** 'Instructions for use: ' (1) Macro security must be set to Medium (or Low) in: Tools > Options > *Office > Security > Macro Security > Medium ' (2) Remove any Class Path to hsqldb.jar if setup in: Tools > Options > *Office > Java/Advanced > Class Path > Remove ' (3) Place this .odb file in a dedicated (empty) database folder ' (4) Add a copy of the HSQLDB engine (hsqldb.jar) to the database folder ' ' NOTE: If copying this code to your own .odb, press Alt-F11, ' then select your .odb in Basic Macros (DO NOT add this macro to the global 'My Macros' location), ' then click: New (button) > New Module, then paste this code into the Basic code window, ' then set to auto-run: Tools > Customize > Events > Open Document > Assign Macro > "Setup" Sub Setup 'Tools > Customize > Events > Open Document > Assign Macro > "Setup" 'Globalscope.BasicLibraries.LoadLibrary("MRILib") On Error Goto ErrorHandler 'get the current path to this .odb file sPathURL = ThisDatabaseDocument.URL sPath = ConvertFromURL(sPathURL) sName = ThisDatabaseDocument.Title iLen = InStr(sPath, sName) sPath = Left(sPath, iLen-1) 'setup Class Path ClassPath: sClassPath = sPath & "hsqldb.jar" If Not FileExists(sClassPath) Then sLine1 = "Please add a copy of the HSQLDB engine (hsqldb.jar) to the current folder : " sLine2 = "NOTE: This is necessary for proper wizard function, but additional benefits include : " sLine3 = "* enhanced portability of the database-folder" sLine4 = "* ensures database compatibility across computers and *Office installations" sLine5 = "* guards against inadvertent upgrade of your database since the results are uncertain and irreversible" sLine6 = "* hsqldb 2.x provides a built-in database management GUI accessible by clicking hsqldb.jar." iButton = MsgBox (chr(13) & sLine1 & chr(13) & chr(13) & sPath & chr(13) & chr(13) & sLine2 & _ chr(13) & sLine3 & chr(13) & sLine4 & chr(13) & sLine5 & chr(13) & sLine6, 18, "hsqldb.jar not found") If iButton = 3 Then Exit Sub 'ThisDatabaseDocument.close(True) If iButton = 4 Then Goto ClassPath End If sClassPath = ConvertToURL(sClassPath) ThisDatabaseDocument.DataSource.Settings.JavaDriverClassPath = sClassPath 'get the HSQL database name from the current folder NextFile = Dir(sPath, 0) While NextFile <> "" If (Right(NextFile, 7) = ".script") Then dbName = (Left(NextFile, Len(NextFile)-7)) NextFile = Dir Wend If dbName = Empty Then sLine1 = "Optionally provide a name for your back-end data files. " sLine2 = "NOTE: The particular name is not important. The default below will suffice. " dbName = InputBox(sLine1 & chr(13) & chr(13) & sLine2, "Create a new database * JDBC | HSQL database engine | non-embedded data files *", "mydb") If dbName = "" Then dbName = "mydb" End If 'check for existing database sLine1 = "A new database will be created in the current folder: " sLine2 = "NOTE: This folder constitutes your ""database."" " sLine3 = "A dedicated database folder is fully portable, and as such it may be renamed or moved as desired." sLine4 = "NOTE: This Base front-end file (" & sName & ") must remain in this database folder. " sLine5 = "You may rename this file as desired, but do maintain the .odb extension if visible. Create a desktop shortcut to this file as desired." sLine6 = "NOTE: The back-end HSQL data files will be named: " & dbName & ".* " sLine7 = "These files must also remain in this database folder." If Not FileExists(sPath & dbName & ".script") Then MsgBox sLine1 & chr(13)_ & sPath & chr(13) & chr(13) & sLine2 & sLine3 _ & chr(13) & chr(13) & sLine4 & sLine5 _ & chr(13) & chr(13) & sLine6 & sLine7 _ , 64, "Please Read" 'setup Data Source URL sURL_prefix = "jdbc:hsqldb:" sURL_args = ";default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false" sURL = sURL_prefix & "file:///" & sPath & dbName & sURL_args ThisDatabaseDocument.DataSource.URL = sURL ThisDatabaseDocument.DataSource.Settings.JavaDriverClass = "org.hsqldb.jdbcDriver" ' ThisDatabaseDocument.DataSource.User = "SA" Exit Sub ErrorHandler: MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")", 16, "Setup: macro code error" End Sub
some users.