Documentation/HowTo/MigrateFromHSQLDB
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
The LibreOffice 6.1 release will gradually replace the internal HSQLDB with the internal Firebird database. First of all, this is indicated by the fact that existing Firebird databases no longer require activation of the experimental mode.
If the experimental mode is activated in LibreOffice, the following dialog appears when accessing the tables of an internal HSQLDB database:
This should be chosen Later until all of the following precautions have already been taken.
- Backup the HSQLDB database file.
- Copy views from the SQL code and save them as queries. Adjust later and save as views again. Views can not be edited in Firebird at this time!
- Table names and column names in Firebird can only be up to 31 characters long. If necessary adjust.
- Due to a bug, in all tables date fields, time fields and date / time fields must be supplemented by a field that stores the contents as text.
- Due to a bug, images read in an HSQLDB are not displayed in the Firebird database after migration.
- Pure text tables (integrated * .csv table etc.) are not possible under Firebird.
Migrate date fields, time fields, and date / time fields
The Migration Wizard does not manage to correctly migrate date fields, time fields, and date / time fields. This is because the HSQLDB works with local time zones. During the date-time migration, this suddenly turns into summer time values that are 2 hours earlier, but only one hour in winter time. Dates are always advanced by one day, and so on. The following method helps to properly get the appropriate values from the HSQLDB into the Firebird database, and then create time and date values again:
1. Tables that contain date fields, time fields, and date / time fields in the HSQLDB to add one VARCHAR field each:
"Date_T" VARCHAR (10),
"Time_T" VARCHAR (8),
"DateTime_T" VARCHAR (19)
2. Run the following command via Tools → SQL:
UPDATE "Table" SET "Date_T" = CAST ("Date" AS VARCHAR (10));
UPDATE "Table" SET "Time_T" = CAST ("Time" AS VARCHAR (8));
UPDATE "Table" SET "DateTime_T" = LEFT (CAST ("DateTime" AS VARCHAR (30)), 19);
3. After all fields are also available as text fields in the tables, the migration can proceed. Subsequently, the migrated Firebird database file must be in update again:
UPDATE "Table" SET "Date" = "Date_T";
UPDATE "Table" SET "Time" = "Time_T";
UPDATE "Table" SET "DateTime" = "DateTime_T";
After this action, the corresponding values from the HSQLDB are also available in the new Firebird database file. The fields "Date_T", "Time_T" and "DateTime_T" can then be deleted.
Make pictures from tables in forms visible and changeable
The migration wizard creates fields with the type image [BLOB] in fields that were responsible for images in the HSQLDB (image [Longvarbinary]). This type is present in the GUI, but probably not linked to the correct data type. In order to make these images visible again, they have to be copied into a field of the type BLOB [BLOB], so that a display and modification of the image content in the form is possible. Reports are currently unable to display images from Firebird database files.
Even if no changes have been made by the user on a picture field, the message appears that a change is not possible and instead the column can be deleted and recreated. It is best to select No, otherwise the data stored in the field will be lost.
1. Tables, which contain images, a BLOB field should be complemented: "Image_N" BLOB [BLOB]
2. When saving does't allow the data type of the image field created during import to be changed.
3. Run the following command via Tools → SQL:
UPDATE "Table" SET "Image_N" = "Image";
4. Open the table editor, delete the old "Image" column and don't allow the data type of the new image field to be changed by deleting the field.
5. Open the table editor, add a new "Image" column and don't allow the data type of an image field to be changed by deleting the field.
6. Run the following command via Tools → SQL:
UPDATE "Table" SET "Image" = "Image_N";
7. Open the table editor, delete "Image_N" column and don't allow the data type of the new image field to be changed by deleting the field.
Migration bug for subforms and parameter queries
Due to a bug in the migration parameter queries and subforms do not work properly. For this unfortunately the database file has to be unpacked and the contained content.xml file has to be processed. This file incorrectly states
db: parameter-name-substitution = "false"
This must be changed to
db: parameter-name-substitution = "true"
or it can be completely removed immediately. Then the content.xml can be read back into the database file.
Contents of * .odb file after migration
The contents of the HSQLDB are not removed from the * .odb file during migration:
After the migration, the file "firebird.fbk" is located in the database file in the subdirectory "database" in addition to the HSQLDB. This file contains the migrated data and is unpacked into the temporary directory of the operating system when the database file is started.
Also the previous settings of the content.xml are retained. The corresponding file has just been renamed to "content_before_migration.xml". In order to be able to access the data of the HSQLDB again, only the new "content.xml" would have to be renamed to "content_new.xml" and the file "content_before_migration.xml" renamed to "content.xml".
After successful migration, the "content_before_migration.xml" file and the "backup", "data", "properties", and "script" files located in the "database" directory can be deleted. This is recommended, of course, if the database was already quite extensive before. Finally, the data has been doubled during migration.
Customize functions in queries
Many functions in queries have the same names in HSQLDB and Firebird and work the same. However, when opening a query that previously worked in the HSQLDB, it may happen that a message like the following appears:
The IFNULL function is unknown in Firebird. A look at the following list shows that the COALESCE function can be used instead in both Firebird and the internal HSQLDB. This function is even more universal because it can query any number of values and the first hit that is not NULL returns the corresponding value. Unfortunately, the base SQL editor does not provide the ability to share a term with Find and Replace. Otherwise IFNULL could simply be replaced by COALESCE if the term appears more frequently in a query. For such transformations, it is therefore advisable to copy the query into a simple text editor with search-and-replace function, then do the replacement and overwrite the old content with it.
It gets more complicated when the following message appears:
The error message is meaningless for now. No useful details. It appeared here for the first time when the SUBSTRING function appeared in a query. This feature is also known in Firebird, but Firebird does not allow the comma-separated shorthand notation that is possible with HSQLDB. Instead you have to rewrite to SUBSTRING (s FROM start [FOR len]).
The following table gives an overview of which features might need to be adjusted because they do not have the same name in Firebird or, in the worst case, do not exist at all. The list says nothing about the fact that Firebird has fewer functions than the internal HSQLDB. Only there is no substitute for certain functions.
A function that appears in the HSQLDB and Firebird column can already be changed before the migration. An example of this is the combination of several strings: CONCAT as well as the combination with '+' can already be replaced by '||'. This connection works in both internal databases. The further functioning of HSQLDB is not affected by this change.
A function that appears only under HSQLDB or Firebird, but not in the middle column, can not yet be replaced. For this the database would have to be migrated first. The code of views with one of these functions has to be stored elsewhere - e.g. as a query. Changing the code is only possible after the migration.
Some features can not be migrated. This is indicated by the red-crossed note.
Data engine functions in HSQLDB and Firebird
First column shows functions of HSQLDB, which aren't available in Firebird. Second column will show functions, which will work in HSQLDB and Firebird with the same result. Third column show functions of Firebird, which must be chosen instead of functions in HSQLDB. This functions give the same result as the functions in the same row for HSQLDB, but aren't available with this name of the function in HSQLDB.
| Numeric | ||
| HSQLDB | HSQLDB and Firebird | Firebird |
|---|---|---|
| BITAND(a,b) | BIN_AND(a,b) | |
| BITOR(a,b) | BIN_OR(a,b) | |
| BITXOR(a,b) | BIN_XOR(a,b) | |
| DEGREES(d) | <value for radians>*360/(2*PI()) | |
| LOG(d) | LN(d) | |
| RADIANS(d) | <value for degrees>*2*PI()/360 | |
| ROUNDMAGIC(d) | No Function | |
| TRUNCATE(a,b) | TRUNC(a,b) | |
| TEXT | ||
| HSQLDB | HSQLDB and Firebird | Firebird |
| ASCII(s) | ASCII_VAL(s) | |
| BIT_LENGTH(s) | Returns length of text. HSQLDB: 16bit per character Firebird: 8bit, special character 16bit. Same Name, different counting! | BIT_LENGTH(s) |
| CHAR(c) | ASCII_CHAR(c) | |
| CONCAT(str1,str2)
'str1'+'str2'+'str3' |
'str1'‖'str2'‖'str3' | |
| DIFFERENCE(s1,s2) | No Function | |
| HEXTORAW(s) | No Function | |
| INSERT(s1,start,len,s2) | OVERLAY(s1 PLACING s2 FROM start [FOR len])
Not working in query-GUI | |
| LCASE(s) | LOWER(s) | |
| LENGTH(s) | CHAR_LENGTH(s)
CHARACTER_LENGTH() |
|
| LOCATE(search,s[,start]) | POSITION(search IN s) | POSITION(search,s[,start]) |
| LTRIM(s) | TRIM(LEADING FROM s) | |
| OCTET_LENGTH(s) | Same Name, different counting! | |
| RAWTOHEX(s) | No Function | |
| REPEAT(s,count) | No Function | |
| RTRIM(s) | TRIM(TRAILING FROM s) | |
| SOUNDEX(s) | No Function | |
| SPACE(count) | No Function | |
| SUBSTR(s,start[,len])
SUBSTRING(s,start[,len]) |
SUBSTRING(s FROM start [ FOR len]) | |
| UCASE(s) | UPPER(s) | |
| DATE/TIME | ||
| HSQLDB | HSQLDB and Firebird | Firebird |
| CURDATE() | CURRENT_DATE | |
| CURTIME() | CURRENT_TIME | |
| DATEDIFF(string, datetime1, datetime2)'ms'='millisecond', 'ss'='second', 'mi'='minute', 'hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year' | DATEDIFF(string, datetime1, datetime2) millisecond, second, minute, hour, day, week, month, year
Only long version without | |
| DAY(date)DAYOFMONTH(date) | EXTRACT(DAY FROM datetime) | |
| DAYNAME(date) | No Function | |
| DAYOFWEEK(date) | EXTRACT(WEEKDAY FROM datetime)
Not working in query-GUI | |
| DAYOFYEAR(date) | EXTRACT(YEARDAY FROM datetime)
Not working in query-GUI | |
| HOUR(time) | EXTRACT(HOUR FROM datetime) | |
| MINUTE(time) | EXTRACT(MINUTE FROM datetime) | |
| MONTH(date) | EXTRACT(MONTH FROM datetime) | |
| MONTHNAME(date) | No Function | |
| NOW() | CURRENT_TIMESTAMP | |
| QUARTER(date) | CEILING(EXTRACT(MONTH FROM date)/3.00) | |
| SECOND(time) | EXTRACT(SECOND FROM datetime) | |
| WEEK(date) | EXTRACT(WEEK FROM datetime)
Not working in query-GUI | |
| YEAR(date) | EXTRACT(YEAR FROM datetime) | |
| TO_CHAR(datetime, string) | No Function | |
| Databaseconnection | ||
| HSQLDB | HSQLDB and Firebird | Firebird |
| DATABASE() | RDB$GET_CONTEXT('SYSTEM', 'DB_NAME')
Not working in query-GUI | |
| USER()
Not working in query-GUI |
CURRENT_USER | |
| IDENTITY() | No Function | |
| System | ||
| HSQLDB | HSQLDB and Firebird | Firebird |
| IFNULL(exp,value) | COALESCE(expr1,expr2,expr3,...) | |
| CASEWHEN(exp,v1,v2) | CASE WHEN expr1 THEN v1[WHEN expr2 THEN v2] [ELSE v4] END | IIF(exp,v1,v2)(only if CASE WHEN is to complicated) |
| CONVERT(term,type) | CAST(term AS type) | |
For more information like field types in HSQLDB and Firebird see Documentation/FirebirdMigration.