[Python] Macro to extract and reconnect embedded HSQLDB

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Python] Macro to extract and reconnect embedded HSQLDB

Post by Villeroy »

Update 2017-01-25: Complete rewrite.
Update 2018-7-27: Wrapped the same code in an installer document (Writer with embedded Basic).
2021-01-24: Since HSQL version 2.5 the automatic conversion won't work anymore. HSQL 2.5. is no longer compatible with 1.8. Download some earlier version 2.4.

Python macro to extract the embedded HSQLDB out of the current database document into a subdirectory named "database". The document will be connected to the extracted files in the database directory using either a given hsqldb.jar in subdirectory "driver" or to the hsqldb.jar which is shipped with the office suite.

Install the Python macro
Precondition: Python needs to be enabled which is the case if there is a menu entry Tools>Macros>Organize>Python. Ubuntu Linux installs LibreOffice without the Python bridge. Add package "libreoffice-script-provider-py". Under Windows you have to re-run the installer and add this package if it had been deselected.
Open the attached Writer document and follow the instructions if you get a macro security warning when loading this document.

After the Python code has been saved to the right directory, you'll find a new office macro
menu:Tools>Macros>Organize>Python ... pyDBA > ExtractHSQL > Main
menu:Tools>Macros>Run ... user > pyDBA > ExtractHSQL > Main

Suggestion for organizing stand-alone HSQL databases
Having many database documents connected to stand-alone database backends, you can keep all the frontends (odb documents) in a dedicated directory, the backend files (*.backend, *.data, *.script) in one subdirectory "database" and any database driver(s) in subdirectory "driver".

Suggested directory structure for MyDatabase_A.odb and MyDatabase_B.odb:

Code: Select all

MyDatabase_A.odb
MyDatabase_B.odb
[database]
    MyDatabase_A.backend
    MyDatabase_A.data
    MyDatabase_A.properties
    MyDatabase_A.script
    MyDatabase_B.backend
    MyDatabase_B.data
    MyDatabase_B.properties
    MyDatabase_B.script
[driver]
    hsqldb.jar
With this setup you can open any database document from this folder and run the extraction macro which will add the properly named backend files to the database directory.
Any given hsqldb.jar in subdirectory "driver" will be used as this database document's database driver. If no driver/hsqldb.jar can be found, the built-in hsqldb.jar in the installation directory of your office suite will be used.
You find the most recent HSQL package with documentation, source code and libraries here: http://hsqldb.org/

Usage
1. This macro has been written by an amateur. Keep a backup copy of your original database document. Suggestion: Copy it to a dedicated directory for your stand-alone HSQL databases as suggested above.
2. [optional] If you want to upgrade to a recent HSQL database driver other than the 10 year old driver shipped with the office suite, create a subdirectory named "driver" within your database directory and add the hsqldb.jar of your downloaded HSQL package. If you don't do this, the macro will store the absolute path to the old hsqldb.jar which is shipped with the office suite.
3. Save and close all your office work. Open the database document with embedded HSQL.
4. Run the "Main" macro if Python module "ExtractHSQL". It should prompt you to confirm actions and finish with a success message.
5. Select the "Tables" section. If it is selected already, switch to another section (e.g. Queries) and then back to "Tables". You should see the same tables as before. All queries, forms and reports should work as before.
6. Store your modified database document if everything works as expected.

Since January 2017 the Python module includes a routine "Remove_Embedded" which should be run only if you you are very sure that the embedded database does not serve any purpose. Your database document may become significantly smaller. If you ran this routine by mistake confirming the warning message, shut down the office suite without saving the database document.
Now there is also a "Revert_To_Embedded" routine which can be used to reconnect the document to its embedded HSQLDB if it exists within the document container.

When you run the main routine of this Python module, the status bar of your database document changes from this ...
Status bar of embedded HSQLDB
Status bar of embedded HSQLDB
Embedded_HSQLDB.png (3.63 KiB) Viewed 6797 times
... to something like this ...
Status bar stand-alone HSQLDB
Status bar stand-alone HSQLDB
Extracted_HSQLDB.png (6.31 KiB) Viewed 7061 times
The major difference is that the database files won't be extracted from the document when opening and they won't be wrapped back into the database document when you quit working with the database. The database remains in place which increases the safety of your precious data significantly.
Note that the original HSQL 1.8 database remains embedded in your odb file but it won't be used anymore. The Python module has an additional macro to remove the embedded database in order to save some disk space.

In case of trouble
If you encounter any problems when running the extraction macro and you did not back up your database document, shut down the whole office suite without saving the modified database document. The macro leaves it up to you to save the modifications or not. Then you can open the unmodified database document again, run the macro and note the detailed error messages. Post a report to the forum for external data sources with detailed error descriptions.
An old HSQL1.8 database will be converted automatically by a new HSQL2 driver and there is no easy way back. If there are any problems with HSQL2, they can be resolved by little changes to the script file of the database.

What the macro does
Update 2017-01-25: Complete rewrite.
The Python macro performs the following steps:

-- Release any connection to the database document in the active window.

-- Detect if there is a HSQLDB embedded in the database document (2 files database/script and database/properties at least).

-- Detect if there there is a subdirectory "database" wiith database files (*.script and *.properties at least) having the same base name as the odb document..

-- Prompt you for 4 possible actions:
1. There is an embedded but no stand-alone database: Extract the embedded database and connect to the new files if there are no preexisting database files. This action converts an embedded database into a stand-alone database.
2. There is no embedded database but (possibly) connectable files: This action reconnects the database when the document and its database directory has been moved to another directory or another computer.
3. Both versions of database exist: Let you decide if you want to replace the existing files with the embedded database if both databases exist. Replacing may be useful when you want to discard your external database and start again with the formerly embedded version of your database. In most cases you want to reconnect only.
4. No database: You get an error message when there is no connectable database at all and nothing will be done.

-- Before extracting any embedded HSQLDB, the macro removes all HSQL files with the same base name as the document.
-- After extracting any embedded HSQLDB, the macro renames the extracted files to the same base name as the document.
-- After extracting any embedded HSQLDB, the macro resets the database log-in to user SA with no password since this is how all embedded databases work.
-- Finally, the macro connects the current database document to the database using either a given hsqldb.jar in subdirectory "driver" or the built-in HSQL driver which resides in the installation path of your office suite.

Files that belong to other databases with base names other than the base name of the odb document won't be touched.

Further steps
1. You may upgrade HSQL 1.8 to HSQL 2.x later. Simply add a driver/hsqldb.jar to your hsql directory and re-run this macro.
2. If you store the odb, the database files and the hsqldb.jar in separate directories, My macro suite "FreeHSQLDB" provides a little GUI which helps you to keep your odb document in touch with its external files and HSQL driver wherever they are.
3. With the help of "FreeHSQLDB" can build redistributable database packages very similar to the embedded databases. Just add the auto-install module to your document and wrap everything in a zip archive.
4. A stand-alone HSQLDB can be run in server mode. Refer to [Tutorial] Avoiding data loss with built in HSQLDB. Set up your HSQL server and integrate the database directory in order to access your database over the network simultaniously from many work places. Somebody wrote a Windows tool for this: viewtopic.php?f=83&t=47098
Attachments
ExtractHSQL.py.odt
Extract and connect embedded HSQL or reconnect existing files with HSQL 1.9 or HSQL 2.x
(28.03 KiB) Downloaded 753 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply