Migrating HSQLDB Base files to Firebird Base files

From The Document Foundation Wiki
Jump to: navigation, search

Migration HSQLDB → Firebird

As a first step towards sunsetting embedded HSQLdb support LibreOffice 6.1 includes an auto migration function to convert existing HSQLdb Base files to Firebird Base files.

Why

With version 6.1 support for a Firebird database embedded in a Base file moves from an experimental feature to a standard feature of LibreOffice. With this version the embedded Firebird database becomes the default for new Base files, while continuing to support embedded HSQLdb Base files. In a future version support for embedded HSQLdb data will be retired.

How

An auto migration function is started whenever a Base file, with an embedded HSQLdb database, is opened and the first connection to the data schema is created.

A dialog box will give you the option to either forego the migration at this time by selecting 'Later' or to begin the process by selecting 'Now'.

Schema tables, views, relationships and data are automatically transferred into a new embedded Firebird database in the current file.

The Firebird 3.0 database engine supports a different collection of available field data types from HSQLdb. During transfer of the schema information field data type definitions are altered as required.

Table field data type after auto migration
HSQLdb --> Firebird
Tiny Integer [ TINYINT ] Small Integer [ SMALLINT ]
BigInt [ BIGINT ] BigInt [ BIGINT ]
Image [ LONGVARBINARY ] Image [ BLOB ] SUB_TYPE -9546
Binary [ VARBINARY ] Binary [ VARCHAR ] CHARACTER SET OCTECTS
Binary(fix) [ BINARY ] Binary(fix) [ CHAR ] CHARACTER SET OCTECTS
Memo [ LONGVARCHAR ] CLOB [ BLOB ] SUB_TYPE TEXT
Text(fix) [ CHAR ] Text(fix) [ CHAR ]
Number [ NUMERIC ] Number [ NUMERIC ]
Decimal [ DECIMAL ] Decimal [ DECIMAL ]
Integer [ INTEGER ] Integer [ INTEGER ]
Small Integer [ SMALLINT ] Small Integer [ SMALLINT ]
Float [ FLOAT ] Float [ FLOAT ]
Real [ REAL ] Real [ REAL ]
Double [ DOUBLE ] Double [ DOUBLE ]
Text [ VARCHAR ] Text [ VARCHAR ] COLLATION UTF8 (default)
Text [ VARCHAR_IGNORECASE ] Text [ VARCHAR ] COLLATION UNICODE_CI
Yes/No [ BOOLEAN ] Yes/No [ BOOLEAN ]
Date [ DATE ] Date [ DATE ]
Time [ TIME ] Time [ TIME ]
Date/Time [ TIMESTAMP ] Date/Time [ TIMESTAMP ]
OTHER [ OTHER ] CLOB [ BLOB ]

Note The Firebird 3.0 engine imposes a constraint of 30 characters for schema identifiers such as Tables, Views and Field names while HSQLdb supported much longer identifiers. Where an existing schema identifier exceeds this limit the migration will alter the names. Details on automatic name change to follow


WORK IN PROGRESS DETAILS OF AUTO MIGRATION FEATURES TO COME

Reference information on differences between the HSQLdb and Firebird 3.0 database engines

Note Base uses a rather old (v1.8x), and tweaked, version of the HSQLdb embedded database engine. Tables below included HSQLdb info for the Base embedded engine.

Base 6.1 uses the Firebird 3.0 embedded engine which offers, along with data access performance increases and file size savings, a more expansive set of standard functions available for use in your SQL select statements beyond the scope of this wiki page. Firebird project documentation available at https://www.firebirdsql.org/en/reference-manuals/

Data engine field types

Available field types in Base Table Editor
HSQLdb Firebird
Tiny Integer [ TINYINT ] N/A
BigInt [ BIGINT ] BigInt [ BIGINT ]
Image [ LONGVARBINARY ] Image [ BLOB ]
Binary [ VARBINARY ] N/A
Binary(fix) [ BINARY ] Binary(fix) [ CHAR ]
Memo [ LONGVARCHAR ] Memo [ LONGVARCHAR ]
Text(fix) [ CHAR ] Text(fix) [ CHAR ]
Number [ NUMERIC ] Number [ NUMERIC ]
Decimal [ DECIMAL ] Decimal [ DECIMAL ]
Integer [ INTEGER ] Integer [ INTEGER ]
Small Integer [ SMALLINT ] Small Integer [ SMALLINT ]
Float [ FLOAT ] Float [ FLOAT ]
Real [ REAL ] Real [ REAL ]
Double [ DOUBLE ] Double [ DOUBLE ]
Text [ VARCHAR ] Text [ VARCHAR ]
Text [ VARCHAR_IGNORECASE ] N/A
Yes/No [ BOOLEAN ] Yes/No [ BOOLEAN ]
Date [ DATE ] Date [ DATE ]
Time [ TIME ] Time [ TIME ]
Date/Time [ TIMESTAMP ] Date/Time [ TIMESTAMP ]
OTHER [ OTHER ] N/A
N/A BLOB [ BLOB ]
N/A CLOB [ BLOB ]

Data engine standard functions

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)