Libreoffice Base Firebird

admin 11/22/2021
81 Comments
  • LibreOffice Base users are encouraged to migrate their database using the embedded HSQLDB to embedded Firebird new migration assistant (released with LibreOffice 6.1 as part of the tender), which is not using the HSQLDB database engine and will be available in LibreOffice releases even after the embedded HSQLDB support will be fully removed.
  • Reading a previous answer, I now understand that from LO 6.2 onwards Base will have Firebird as the embedded server. That is great - I really approve of the ethics behind the group who do the Fire. software:) So I thought I would get a head start on learning the little quirks that every different SQL engine has (so much for standards, eh wot?).
  1. Libreoffice Base Firebird Hsqldb 違い
  2. Libreoffice Base Firebird Hsqldb
Base

TDF has published tenders to improve LibreOffice in some areas, including its database management program, Base. The goal here is to implement the import of HSQLDB databases without Java Runtime Environment, in order to facilitate the change-over to
Firebird. In this post I would like to explain what steps are made at Collabora so far to achieve this goal.

Before dealing with the compatibility of the old HSQLDB-based odf files, an improvement had to be made on the Firebird driver. Within this context I implemented those types which could be created with the HSQLDB driver, but not with Firebird. Those Hyper SQL types are:

Base

Questions and answers for LibreOffice. Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license. This book introduces Base, the database component of LibreOffice. Base uses the HSQLDB database engine to create database documents. It can access databases created by many database programs, including Microsoft Access, MySQL, Oracle, and PostgreSQL. In LibreOffice Base, you can access data that is stored in a wide variety of database file formats. LibreOffice Base natively supports some flat file database formats, such as the dBASE format. You can also use LibreOffice Base to connect to external relational databases, such.

  • LONGVARCHAR, implemented as a BLOB holding text in Firebird context;
  • BINARY (fix) and VARBINARY. These types can be created as a CHAR/VARCHAR
    field with a special character set called OCTETS. This character set indicates
    that it stores binary format, and it is needed to indicate that no character
    set or collation makes sense here.
  • LONGVARBINARY. This type is responsible for holding images. As images can be
    really big, I implemented this as a subtype of BLOB.
Libreoffice base firebird_sdbc error

Besides that, to prevent the changing of the DBMS back end being a setback, there were some bugs with high priority to fix. For example the impossibility to copy integer values from Calc to Base (tdf#70425) is fixed by allowing the driver to set numerical values even with the setString method, when it is called on a column with numerical type (int, smallint, etc.).

Libreoffice Base Firebird Hsqldb 違い

After that, in order to prepare the removal of the legacy java-based DBMS, it is needed to create a way to import the database schema and the binary data stored in older odf files without Java. Technically it means a new library in module “dbaccess”, which does all the import, and which is used in the dba (core) library when creating connection.

BaseFirebirds

Splitting it to subtasks, I decided to implement the schema import first. The database schema is zipped into the odf file in a text file called “script”. This file consists of DDL statements in a strict format. The new library reads these SQL statements line-by-line, transforms it a little, and then executes them using the sdbc driver. A modification is needed in statements which contain column definitions (since the types typically differ in different systems), and there are some HSQLDB specific keywords too (e.g. CACHED tables).

The migration can be enabled using DBACCESS_HSQL_MIGRATION=1 when starting
LibreOffice. Currently, as the binary import is not ready yet, the result of the migration is some empty tables mostly.

Next step will be to implement the binary import and create unit tests for all.
the changes I have made.

Libreoffice Base Firebird Hsqldb

Online DocumentationDownloadable Version
Current Versions
Firebird 3.0 Quick Start GuidePDF available
Firebird 3.0 Language Reference (English)PDF available
Firebird 3.0 Language Reference (Russian)PDF available
Firebird 3.0 Developer's Guide (English)PDF available
Database (Fb 2.5 or Fb 3.0)
FireDac (Delphi)
Microsoft Entity Framework/ADO.NET
Microsoft Entity Framework/MVC
PHP
Java/jOOQ/Spring MVC Updated 2018-10-21
All Projects Updated 2018-10-21
Firebird 2.5 Language Reference (English)PDF available
Firebird 2.5 Language Reference (German)PDF available
Firebird 2.5 Language Reference (Russian)PDF available
Firebird 2.5 Quick Start GuidePDF available
Firebird 2.5 Language Reference Update
Firebird 2.5 Language Reference Update (Russian)
PDF available
PDF available
Firebird Command Line Tools
Firebird's isql Interactive SQL toolPDF available
Firebird's gsec User Management toolPDF available
Firebird's gbak Backup and Restore tool
Firebird's gbak Backup and Restore tool (German)
PDF available
PDF available
Firebird's nbackup tool
Firebird's nbackup tool (German)
Firebird's nbackup tool (French)
Firebird's nbackup tool (Dutch)
Firebird's nbackup tool (Russian)
PDF available
PDF available
PDF available
PDF available
PDF available
Firebird's gstat Database Statistics Reporting tool
Firebird's gstat Database Statistics Reporting tool (German)
PDF available
PDF available
Firebird's gfix Database Housekeeping toolPDF available
Firebird's fbmgr Database Engine Manager toolPDF available
Firebird's gsplit Dumpfile Splitting toolPDF available
Firebird's Shell Script tools
Firebird's Shell Script tools (German)
PDF available
PDF available
Firebird command-line utilities (Brazilian Portuguese)
Firebird command-line utilities (Russian)
PDF available
PDF available
Various User Manuals
Setting up Firebird on Ubuntu Linux
Setting up Firebird on Ubuntu Linux (Italian)
PDF available
PDF available
Firebird Generator Guide
Firebird Generator Guide (German)
Firebird Generator Guide (Russian)
PDF available
PDF available
PDF available
Firebird Null Guide
Firebird Null Guide (Spanish)
Firebird Null Guide (French)
Firebird Null Guide (Italian)
Firebird Null Guide (Russian)
PDF available
PDF available
PDF available
PDF available
PDF available
MS SQL to Firebird Migration Guide
MS SQL to Firebird Migration Guide (Russian)
PDF available
PDF available
Firebird File and Metadata Security
Firebird File and Metadata Security (German)
Firebird File and Metadata Security (Brazilian Portuguese)
Firebird File and Metadata Security (Russian)
PDF available
PDF available
PDF available
PDF available
Using ZeBeDee with FirebirdPDF available
Embedded SQL Guide for RM/CobolPDF available
Using Firebird (work in progress)PDF available
Reference Material
Firebird Database Limits
Firebird 2.1 Error CodesPDF available
Firebird Date Literals
Locale/Collation Charts (A. Barkov, MariaDB)
Firebird SQL Conformance
ISO 9075 SQL Standard Keywords/Reserved Words
Firebird Internals (Work In Progress)PDF available
Firebird Wire ProtocolPDF available
Firebird Cache
Firebird Cache (German)
PDF available
PDF available
Firebird Licenses
InterBase Public License
Initial Developer's Public License
Public Documentation License
Public Documentation License (French)
PDF available
Legacy Versions (no longer supported)
Firebird 2.0 and 2.1 Quick Start Guide
Firebird 2.0 and 2.1 Quick Start Guide (Italian)
Firebird 2.0 and 2.1 Quick Start Guide (German)
PDF available
PDF available
PDF available
Firebird 2 Migration & Installation Notes v. 2.0.x and 2.1PDF available
Firebird 2.1 Language Reference UpdatePDF available
Firebird 2.0 Language Reference UpdatePDF available
Firebird 2.0 Error CodesPDF available
Migrating from InterBase 6.01 to Firebird 1.5 and 2.0 (third-party HOWTO)
Firebird 1.5 Language Reference UpdatePDF available
Firebird 1.5 Quick Start Guide
Firebird 1.5 Quick Start Guide (Spanish)
Firebird 1.5 Quick Start Guide (French)
Firebird 1.5 Quick Start Guide (Japanese)
Firebird 1.5 Quick Start Guide (Russian)
PDF available
PDF available
PDF available
PDF available
PDF available
Firebird 1.5 Error CodesPDF available
Firebird 1.5 Character Sets & Collations
Coexistence of Firebird 1.5 and InterBase
Coexistence of Firebird 1.5 and InterBase (Brazilian Portuguese)
Coexistence of Firebird 1.5 and InterBase (Russian)
PDF available
PDF available
PDF available
Firebird 1.0 Quick Start GuidePDF available
InterBase 6.0 Manuals
API GuideZIP available
Data Definition GuideZIP available
Developers GuideZIP available
Embedded SQL GuideZIP available
Language ReferenceZIP available
Operations GuideZIP available
Full Set, cross-indexedZIP available
IB 6.0 SQL Reference (not included in full set)
Manuals for Firebird Docwriters
Intro to Writing Firebird Docs (conference session)PDF available
Firebird Docbuilding Howto
Firebird Docbuilding Howto (Spanish)
Firebird Docbuilding Howto (Italian)
Firebird Docbuilding Howto (Brazilian Portuguese)
PDF available
PDF available
PDF available
PDF available
Firebird Docwriting Guide
Firebird Docwriting Guide (German)
Firebird Docwriting Guide (Italian)
Firebird Docwriting Guide (Brazilian Portuguese)
PDF available
PDF available
PDF available
PDF available
Using non-Western fonts in your Firebird docs
Using non-Western fonts in your Firebird docs (German)
PDF available
PDF available
Docwriters' Tools Downloads (.zip files)