Migrating HSQLDB Base files to Firebird Base files

    From The Document Foundation Wiki

    TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

    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)
    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Documentation/FirebirdMigration&oldid=177299"