Documentation

Cedocument en français.

The use of this software subjects you to ourTerms Of Use.

version1.1.8

Introduction:

HyperSQLOOo is part of aSuite ofLibreOfficeand/orOpenOffice extensions allowing to offer you innovative services in these office suites.

This extension allows you:

• To overcomebug 139538 for users ofLibreOffice on Linux.
• To use HyperSQL database in embedded mode, making the database portable (a single odb file).
• To take advantage of the improvements offered by thejdbcDriverOOo extension:
  • The management of users and roles (groups).
  • The management of nanoseconds and time zones.
  • The management ofjava.sql.Array,java.sql.Blob,java.sql.Clob…
• To replace the integratedHsqlDB 1.8 driver provided by LibreOffice / OpenOffice, a version that will soon be more than 20 years old, with a recent HsqlDB version of your choice.
• To support theACID properties of the underlyingHsqlDB database.

Attention: If you wish to migrate odb files created with LibreOffice or OpenOffice and HsqlDB version 1.8, it is imperative to use version 1.1.2 or higher of HyperSQLOOo otherwise there is a great risk of data loss. The migration procedure is given in theHow to migrate an embedded database section. Whatever happens, keep your backups up to date.

Being free software I encourage you:

• To duplicate itssource code.
• To make changes, corrections, improvements.
• To openissue if needed.

In short, to participate in the development of this extension.
Because it is together that we can make Free Software smarter.

Requirement:

The HyperSQLOOo extension uses the jdbcDriverOOo extension to work.
It must therefore meet therequirement of the jdbcDriverOOo extension.

This extension cannot be installed together with theSQLiteOOo extension.
It’s one or the other, but at the moment they can’t work together (seeissue #156471).

On Linux and macOS the Python packages used by the extension, if already installed, may come from the system and thereforemay not be up to date.
To ensure that your Python packages are up to date it is recommended to use theSystem Info option in the extension Options accessible by:
Tools -> Options -> Base drivers -> Embedded HsqlDB Driver -> View log -> System Info
If outdated packages appear, you can update them with the command:
pip install --upgrade <package-name>

For more information see:What has been done for version 1.1.0.

Installation:

It seems important that the file was not renamed when it was downloaded.
If necessary, rename it before installing it.

• InstalljdbcDriverOOo.oxt extension

  This extension is necessary to use HsqlDB version 2.7.2 with all its features.

• InstallHyperSQLOOo.oxt extension

Restart LibreOffice after installation.
Be careful, restarting LibreOffice may not be enough.

• On Windows to ensure that LibreOffice restarts correctly, use Windows Task Manager to verify that no LibreOffice services are visible after LibreOffice shuts down (and kill it if so).
• Under Linux or macOS you can also ensure that LibreOffice restarts correctly, by launching it from a terminal with the commandsoffice and using the key combinationCtrl + C if after stopping LibreOffice, the terminal is not active (no command prompt).

Use:

How to create a new database:

In LibreOffice go to:File -> New -> Database…:

In step: Select database:

• select: Create a new database
• in: Emdedded database: choose: Embedded HsqlDB Driver
• click on button: Next

In step: Save and proceed:

• adjust the parameters according to your needs…
• click on button: Finish

Have fun…

How to import data from a Calc file:

LibreOffice offers you the possibility to import data from Calc directly into an existing table or one created for this import. Here is the procedure to follow:

• You must first create a new HsqlDB database as described in theprevious section.
• In Calc select a range of cells then:Edit -> Copy. When creating a new table, it is possible to put in the first row the names of the columns as they will be imported into this new table.
• In Base (ie: the new HsqlDB database), after selectingTables in theDatabase pane, right-click in theTables pane then:Paste and follow the wizard that LibreOffice offers.It is important to name the table with a full name (ie:PUBLIC.PUBLIC.Table1). See theHsqlDB documentation about thedefault catalog and schema.

How to migrate an embedded database:

If you want to migrate an integrated database (HsqlDB version 1.8.0) to a newer version (for example 2.7.2), follow these steps:

1. Make a copy (backup) of your database (odb file).
2. If not already installed, install this extension and thejdbcDriverOOo extension.
3. Update driver archive of the HsqlDB driver in:Tools -> Options -> Base drivers -> JDBC driver -> JDBC drivers settings -> Driver archive -> Update, with a version1.8.0.10.
4. Restart LibreOffice / OpenOffice after changing the driver (hsqldb.jar).
5. Open the odb file in Base (double click on the odb file).
6. In Base go to:Tools -> SQL and type the SQL command:SHUTDOWN COMPACT orSHUTDOWN SCRIPT.

• Repeat this procedure at step 3 using version2.4.0 or2.4.1 or2.5.0.
• Repeat this procedure at step 3 using version2.7.2.

How does it work:

HyperSQLOOo is ancom.sun.star.sdbc.Driver UNO service written in Python.
It is an overlay to thejdbcDriverOOo extension allowing to store the HsqlDB database in an odb file (which is, in fact, a compressed file).

Its operation is quite basic, namely:

• When requesting a connection, several things are done:
  • If it does not already exist, asubdirectory with name:. +odb_file_name +.lck is created in the location of the odb file where all HsqlDB files are extracted from thedatabase directory of the odb file (unzip).
  • ThejdbcDriverOOo extension is used to get thecom.sun.star.sdbc.XConnection interface from thesubdirectory path +/hsqldb.
  • If the connection is successful, aDocumentHandler is added as ancom.sun.star.util.XCloseListener andcom.sun.star.document.XStorageChangeListener to the odb file.
  • If the connection is unsuccessful and the files was extracted in phase 1, thesubdirectory will be deleted.
• When closing or renaming (Save As) the odb file, if the connection was successful, theDocumentHandler copies all files present in thesubdirectory into the (new)database directory of the odb file (zip), then delete thesubdirectory.

The main purpose of this mode of operation is to take advantage of the ACID characteristics of the underlying database in the event of an abnormal closure of LibreOffice.On the other hand, the function:file -> Save hasno effect on the underlying database. Only closing the odb file or saving it under a different name (File -> Save As) will save the database in the odb file.

Has been tested with:

• LibreOffice 24.2.1.2 (x86_64)- Windows 10

• LibreOffice 7.3.7.2 - Lubuntu 22.04

• LibreOffice 24.2.1.2 - Lubuntu 22.04

• LibreOffice 24.8.0.3 (x86_64) - Windows 10(x64) - Python version 3.9.19 (under Lubuntu 22.04 / VirtualBox 6.1.38)

I encourage you in case of problem
to create anissue
I will try to solve it

Historical:

What has been done for version 0.0.1:

• The writing of this driver was facilitated by adiscussion with Villeroy, on the OpenOffice forum, which I would like to thank, because knowledge is only worth if it is shared…

• Using the old version of HsqlDB 1.8.0 (can be easily updated).

• Added a dialog box allowing to update the driver (hsqldb.jar) in: Tools -> Options -> Base drivers -> Embedded HsqlDB driver

• Many other fix…

What has been done for version 0.0.2:

• Now the driver automatically splits an odb when opened… This allow conversion of odb files produced by the built-in LibreOffice / OpenOffice HsqlDB driver

• Many other fix…

What has been done for version 0.0.3:

• I especially want to thank fredt athsqldb.org for:

  • His welcome for this project and his permission to use the HsqlDB logo in the extension.

  • The quality of its HsqlDB database.

• Now works with OpenOffice on Windows.

• When unzipping, a file name clash now displays a precise error.

• Now correctly handles spaces in filenames and paths.

• Many other fix…

What has been done for version 0.0.4:

• Modification ofDriver.py in order to make possible the use of the Uno service:com.sun.star.sdb.RowSet.

• Many other fix…

What has been done for version 0.0.5:

• Writing aDocumentHandler to allow:
  • The extraction of the database files contained in theodb file on connection.
  • Saving database files toodb file when closing it.
• RewroteDriver.py to allow:
  • Its operation with the new JDBC driver provided by the extensionjdbcDriverOOo version 0.0.4.
  • The support for the newDocumentHandler to makeodb files portable as they were in LibreOffice / OpenOffice with version 1.8 of HsqlDB.
• Many other fix…

What has been done for version 1.0.0:

• Renamed the extension from HsqlDBembeddedOOo to HsqlDriverOOo.

• Integration of HyperSQL version 2.7.2.

What has been done for version 1.0.1:

• Renamed the extension from HsqlDriverOOo to HyperSQLOOo.

• Fixedbug 156511 occurring when using the com.sun.star.embed.XStorage interface. Theworkaround is to use the copyElementTo() method instead of moveElementTo(). Versions of LibreOffice 7.6.x and higher become usable.

What has been done for version 1.0.2:

• The absence or obsolescence ofjdbcDriverOOo extension necessary for the proper functioning ofHyperSQLOOo now displays an error message.

• Many other things…

What has been done for version 1.1.0:

• All Python packages necessary for the extension are now recorded in arequirements.txt file followingPEP 508.
• Now if you are not on Windows then the Python packages necessary for the extension can be easily installed with the command:
  pip install requirements.txt
• Modification of theRequirement section.

What has been done for version 1.1.1:

• Support fornew features injdbcDriverOOo 1.1.2.

What has been done for version 1.1.2:

• Support for the latest version ofjdbcDriverOOo 1.3.1.
• When saving under a different name, the database if open will be closed correctly.
• When opening an odb file, if the connection fails, to avoid data destruction, recompression of the database files will not take place. Thanks to Robert for being able to detect thisissue.

What has been done for version 1.1.3:

• Use of the new data format implemented in version 1.1.2. As a result, if you need to open odb files created with a version lower than 1.1.2 you must first open them with version 1.1.2, otherwise an error will be thrown.

What has been done for version 1.1.4:

• Updated thePython packaging package to version 24.1.
• Updated thePython setuptools package to version 72.1.0.
• The extension will ask you to install the jdbcDriverOOo extension in versions 1.4.2 minimum.

What has been done for version 1.1.5:

• Fixedissue #2 which appears to be a regression related to the release of JaybirdOOo. Thanks to TeddyBoomer for reporting it.
• Updated thePython setuptools package to version 73.0.1.
• The extension options are now accessible via:Tools -> Options… -> LibreOffice Base -> Embedded HsqlDB Driver

What has been done for version 1.1.6:

• Logging accessible in extension options now displays correctly on Windows.
• Changes to extension options that require a restart of LibreOffice will result in a message being displayed.
• Support for LibreOffice version 24.8.x.

What has been done for version 1.1.7:

• Requires the latest version ofjdbcDriverOOo 1.4.4.
• In the extension options it is possible to define the options:View system tables,Use bookmarks andForce SQL mode which will be specific to this driver.

What has been done for version 1.1.8:

• The extension will ask you to install jdbcDriverOOo extension in versions 1.4.6 minimum.
• Modification of the extension options accessible via:Tools -> Options… -> LibreOffice Base -> Embedded HsqlDB Driver in order to comply with the new graphic charter.

What remains to be done for version 1.1.8:

• Add new language for internationalization…

• Anything welcome…