[Python] Macro to extract and reconnect embedded HSQLDB

Creating Extension - 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 forum is not for asking questions about writing your own macros.

[Python] Macro to extract and reconnect embedded HSQLDB

Postby Villeroy » Tue Nov 15, 2016 2:47 pm

Update 2017-01-25: Complete rewrite.

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>Options>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.
Extract the attached ExtractHSQL.py.zip which contains the Python script ExtractHSQL.py to directory Scripts/python within your profile folder. If this is your first Python macro, create the python subdirectory within Scripts. Within the python folder you are free to organize your python macros in further subdirectories. I keep my database related Python macros in Scripts/python/pyDBA/.

Typical Windows folders:
%APPDATA%\OpenOffice\4\user\Scripts\python\ [and pyDBA if you want]
%APPDATA%\LibreOffice\4\user\Scripts\python\ [and pyDBA if you want]

Typical Linux folders:
~/.openoffice/4/user/Scripts/python/ [and pyDBA if you want]
~/.config/libreoffice/4/user/Scripts/python/ [and pyDBA if you want]

After extracting the Python file 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   Expand viewCollapse view

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/

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 db 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.

The status bar of your database document changes from this ...
Status bar of embedded HSQLDB
Embedded_HSQLDB.png (3.63 KiB) Viewed 923 times

... to something like this ...
Status bar stand-alone HSQLDB
Extracted_HSQLDB.png (6.31 KiB) Viewed 1187 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 database remains embedded in your odb file but it won't be used anymore. You may remove the database directory manually to save some space. odb documents are zip archives.

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 embedded database.

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

-- Detect if there there are database files (*.script and *.properties at least) with the same base name as the odb document in subdirectory "database".

-- 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.
2. 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.
3. 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: https://forum.openoffice.org/en/forum/v ... 83&t=47098
Extract and connect embedded HSQL or reconnect existing files with HSQL 1.9 or HSQL 2.x
(2.98 KiB) Downloaded 88 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 2 guests