Development/Base/FirebirdSQL

This page is intended to help coordinate development of the Firebird SQL DB connector for Base. Feel free to add and edit as you see fit if you're hacking on this too!

Work is now on master -- the sdbc driver itself is in connectivity/source/drivers/firebird/, the firebird gbuild integration and patches are in firebird/.

The driver is slowly approaching a usable status.

Current Status

 * Firebird building within LibreOffice, using bundled icu: building on all platforms, usable on Linux, Mac, Windows.
 * SDBC driver building against LO's firebird, or alternatively against system firebird (requires firebird-devel and fbembed packages installed, some distros may be missing the required fb_config and/or pkg-config files, if so please report that as a bug to your distro and manually add the required config variables -- details are in configure.ac).
 * SDBC driver can now create, open and save .odb files with internal firebird. It also supports external files (and will hopefully remote dbs)
 * The SDBC driver store the archive format inside the odb file after saving, because this format is platform independent.
 * SDBC driver usable but there are a few blocker bugs being worked on

Technical Summary

 * Uses Firebird 3.0 (libfbclient and libEngine12).
 * Supports dbs embedded in an odb and external files, will probably support remote databases.
 * Uses Firebird's native C api.
 * Currently only implements an SDBC driver but should eventually support SDBCX too.

How to test
.odbs are still being created with hsqldb for the moment. To use firebird you need to create a new .odb, edit the content.xml within: repalace sdbc:embedded:hsqldb with sdbc:embedded:firebird, and reopen; alternatively open you can set org.openoffice.Office.DataAccess/EmbeddedDatabases/DefaultEmbeddedDatabase/Value to sdbc:embedded:firebird.

Graphical way :

Firebird is still an experimental feature. Enable it by: then

TODO
Various things that still need to be done in the sdbc driver (not a complete list -- look at the code for more pointers):
 * Implement connecting to remote dbd, Here is the thread with steps needed to be implemented https://lists.freedesktop.org/archives/libreoffice/2013-December/058465.html
 * Ensure the db is in a safe state when writing it to storage (OConnection::documentEventOccured). (?)
 * Implement Blob properly -- there are segmented and streamed blobs, each requiring separate logic for reading them (see mailing list for details). (?)
 * Use the new C++ API of Firebird instead of the old C API. The documentation for the C++ API is in Firebird source (doc/Using_OO_API.html).
 * Solve issue with different Firebird versions.
 * etc. (Just do 'cd connectivity/source/drivers/firebird && git grep TODO')

Older Firebird databases
Older odb files (which were created before the upgrade to 3.0) may store Firebird 2.5  file inside instead of the backup format. A Firebird 2.5 file cannot be opened with 3.0. The official way to update the firebird database is to back up the 2.5 file using the 2.5 system. Then the archive file can be restored with both systems:


 * install Firebird 2.5
 * uncompress odb file
 * in database subdirectory:
 * gbak -backup firebird.fdb firebird.fbk"
 * rm firebird.fdb
 * change owner and rights of the file firebird.fbk
 * recompress all the elements (with the brand new firebird.fbk)
 * change extension from zip to odb

To make the transition smoother, and avoid maintaining both 2.5 and 3.0 firebird systems in LibreOffice, the idea is to make an external tool, probably a web service, where the user can upload an older  file. The web service would unzip the  file from the uploaded database, it would make the backup file using fb 2.5 gbak tool, put the result back to the   file. This could be opened with the new LO version.

Background Info & Links

 * Original bug report:
 * Thread on the Firebird devel list: https://sourceforge.net/p/firebird/mailman/firebird-devel/thread/518E4DD2.20606%40miguelangel.mobi/

External project
LibreOffice can build its own Firebird (add without-system-firebird switch to autogen.sh). The driver uses dynamic library fbclient which loads the Engine12 library at runtime. The patch external/firebird-Engine12.patch is about to make fbclient library looking for Engine12 in it's current location (as it is placed in LO next to each other).

Windows
Uses gcc-wrapper instead of the Visual Studio project files granted by the fb. build system. It can also be built with MSVC 14.0. For more deatils see external/firebird-cygwin-msvc.patch.

macOS
The Firebird build system has a build for macOS too, although it is not prepared to create a movable library for embedded use. That is solved using the environment variable "LIBREOFFICE_FIREBIRD_LIB" (for catching Engine12.dylib) and using macOS-change-install-names pearl file (located in solenv/bin) to make an rpath like behavior. For more details see external/firebird-macOS.patch.

External project libtommath
Firebird 3.0 requires an installed tommath on Linux or Mac. Therefore there is an externalProject libtommath built before Firebird (see external/libtommath). The location of the tommath files are passed with CXXFLAGS and CPPFLAGS.

Although there is an external libtommath in firebird source too, it is only used by the Visual Studio project files, and it seemed simpler to make a new external in LibreOffice instead of making modifications on the Firebird source with a patch. Firebird 4 will eventually have a switch for using embedded libtommath, so LibreOffice will need this external no more.

Compilation
The implementation is currently being done in Linux and using system's fbclient.so as external dependency.

Hence, as the goal is to have the FB database stored inside  files, in order to have an compatibility among different versions and platforms the best solution seems to not allow firebird as an external dependency. This is, compile always internally and using the bundled libicu provided by firebird. In any case, this problem may even appear just with FB upgrades so we have to put an eye on this and, maybe, set a way of running the mentioned script/actions when the  file has been last saved with a different version of FB ('s SDBC).

Among other things, FB 3.0 package is currently downloaded from http://dev-www.libreoffice.org/src. Check  and.

ODB files
Again, the goal is to have the FB database stored inside  files, as the HSQLDB driver is working.


 * Initially, FB's approach will be to unzip the  structure into a temporal directory and repack everytime we save.

Implementing tips
While implementing FB SDBC driver, I'm working in GNU/Linux in a Ubuntu 14.04 x64 LTS. My compilation and installation line is as follows:

In order to get logging/debugging information we have defined a new SAL debug area named connectivity.firebird and we are also making use of the  macro for temporary debug. We have to make sure this disappears once integrating in master.

Hence, for running LibO with the logging output for FB SDBC I'm using: $ SAL_LOG="+WARN+INFO.connectivity.firebird" ./instdir/program/soffice.bin

Further information: Development/GeneralProgrammingGuidelines

Firebird developer's documentation
Developers documentation is quite spread. Also, FB/IB development API has been stable for a long time so examples from a big amount of different versions can be found but, supposedly, most if not all of them should still be valid.

As we are initially using the native plain C API, the best reference is still Interbase 6.0's API guide

Other documentation can be found at: http://firebirdsql.org/en/reference-manuals/

In addition, code samples included in FB's source code are quite helpfull: https://github.com/FirebirdSQL/firebird/blob/master/examples/api/

Firebird driver implementation

 * 1) Connection
 * The Firebird connection current implementation is kind of dummy, checking out just if the string typed by the user matches the  substring. There is no different behaviour depending on the database creation method selected by the user.
 * 1) Creation
 * We have to define the required logic to deal with the 3 different methods to open/create the ODB file:
 * Create new database
 * Using an existing ODB file.
 * Connect to an existing database.
 * 1) Embedded vs Dedicated Server
 * I'm not sure if we are interested on a dedicated server configuration (eg. Postgres). If that's the case, we should implement different logic at connection creation time. We could follow a similar approach to what Postgres does.
 * 1) Create new database
 * The current implementation assumes there is already an .rdb file with a database created in the path.
 * We should implement the  statement using the user defined parameters.
 * 1) Statements
 * Right now it's possible to instantiate XStatement interfaces and execute both, queries and insert/updates, via  and   methods. however, the   method has the   statement hardcoded.
 * 1) Implementing a generic   method.
 * We should somehow parsing the SQL statement received as the string argument in order to determine the kind of statement to process. The Firebird API provides functions that would help to implement this logic.
 * 1) Factorization
 * The PreparedStatement implementation shares an important amount of logic with the Statement; this is even more true using the Firebird C API, since all the statements should be executed inside a transaction context. Besides, all the queries, being preparedStatements or not, should be prepared.
 * 1) Create Table
 * As it was already commented, the  implements the   statement. The tables created by the wizard (dbaccess module) define the SQL statement to execute.
 * I had to change a bit the SQL syntax, which I think we should evaluate and see whether we could provide a better implementation, or even avoid such modification (using a different SQL dialect ?)
 * 1) ResultSet
 * The implementation of the ResultSet interface is still a bit dummy, using a custom defined type, which perhaps is not the best approach to follow. In any case, the free/destroy operations might handle this data structure.
 * 1) Error Management
 * The current driver implementation uses a static function, which probably we might get rid of and defining new and specific exceptions.

Initial GSOC 2015 details
The main remaining issue is that the firebird data format embedded in the .odb file is endianess-dependent. The idea is to switch to Firebird's "archive" format that is not. I started by cleaning the previous gerrit and submitting new one Development/GSoC/Ideas

Andrzej's initial GSOC 2013 details
This is extracted from Andrzej's GSOC 2013 application, i.e. some technical discussion and an estimated timeplan. Could be useful for reference. Nothing here is firm yet.

People

 * Andrzej Hunt is working on this as his GSOC project. IRC nick: ahunt

Technical details

 * Use Firebird SQL, directly in LibreOffice.
 * Could be done using the existing stable Firebird 2.5, or Firebird 3.0 which has a reputedly cleaner API in C++ (Alpha release expected within the next 2 months according to http://www.firebirdsql.org/en/roadmap/), no idea when it will be stable though.
 * Firebird 2.5 only offers a native C interface, there is however a C++ wrapper (IBPP: http://ibpp.org/).
 * Firebird 3 has a C++ interface:https://github.com/FirebirdSQL/firebird/blob/master/src/include/firebird/Interface.h (Examples of use: https://github.com/FirebirdSQL/firebird/tree/master/examples/interfaces)
 * I would personally prefer Firebird 3, except for the fact that this would mean having to wait for it to be stable before making it the default database in LO (file format and api should be stable with the aforementioned alpha release of Firebird).
 * It looks like the db has to have its own file in every case, i.e. for saving an odb the database will first have to be store to a temp. file, even in Firebird 3 (https://github.com/FirebirdSQL/firebird/blob/master/doc/README.user.embedded).
 * Embedding 2.5 is much more complicated because it requires the full Firebird server to be available, not just the embedded library: http://www.firebirdfaq.org/Firebird-Embedded-Linux-HOWTO.html
 * If Firebird 3 is used, then hsqldb left as the default DB until Firebird 3 is stable (default easily changeable in the config in org/openoffice/Office/DataAccess/EmbeddedDatabases/DefaultEmbeddedDatabase, which would be very simple if an about:config get implemented?).

Rough Timeplan / Estimates

 * Add necessary scripting/build system stuff to build FirebirdSQL, based on how things are done in hsqldb/mysql (and in other external projects) [up to 1 week, but hopefully less].
 * Create the scaffolding for an SDBC driver for Firebird in connectivity/ [2 days], test that it can actually connect to / open a valid db (based on the skeleton driver).
 * Implement the necessary code to be able to read and save a Firebird db to/from an odb file using temporary files, i.e. add hooks for embedded firebird (dbaccess/source/core/misc/dsntypes.cxx), and implement the rest of the read/write code. [3 days]
 * Implement the SDBC driver – the core of the work, based on the recipe in https://wiki.openoffice.org/wiki/Documentation/DevGuide/Database/SDBC_Driver and https://wiki.openoffice.org/wiki/Base_New_Driver_Tutorial
 * This mostly involves wrapping the various Firebird classes, found in https://github.com/FirebirdSQL/firebird/blob/master/src/include/firebird/Interface.h
 * Implement the Connection Service: i.e. creating statements, transaction control, etc. – wraps IAttachment [3 days]
 * DatabaseMetaData – all the specific details of the db [2-3 days – simple but fairly bulky]
 * Statement – wrap IStatement [2 days]
 * PreparedStatement – also wrap IStatement [3 days]
 * ResultSet – wrap IResultSet [2 days]
 * ResultSetMetaData – wrap IMessageMetadata [1-2 days]
 * Additional/unexpected problems I might run into whilst implementing the driver [1-5 days]
 * Merge into master [1 day – possibly more if it turns out there are build problems on Mac – I only have access to Linux and Windows currently – but hopefully I should be able to use the buildbot to test building on all platforms]
 * Implement HSQL migration: migrate a previously hsqldb file to a firebird db, i.e. simply copy all the data from a hsqldb file into a firebird file. Also possibly a dialog to prompt conversion when opening old files? (Possibly also implement a C++ hsqldb parser to import from the existing files, to remove the need for java even for old odb files, but that's likely outside the scope of a summers worth of work). [~1 week]
 * Implement various tests / regression tests [1-∞ weeks] -- I have little experience with what sort of tests are sensible so would need advice on what is worthwhile, is fairly open ended.