Development/Base/FirebirdSQL

    From The Document Foundation Wiki

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

    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 Tools ▸ Options ▸ LibreOffice ▸ Advanced ▸ Expert Configuration 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: Tools ▸ Options ▸ LibreOffice ▸ Advanced ▸ Enable experimental features then File ▸ New Database ▸ Select database ▸ Create a new database ▸ Embedded Database: Firebird Embedded

    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 .fdb file inside instead of the backup format .fbk. 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 .odb file. The web service would unzip the .fdb file from the uploaded database, it would make the backup file using fb 2.5 gbak tool, put the result back to the .odb file. This could be opened with the new LO version.

    Background Info & Links

    Technical details

    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 .odb 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 .odb 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 download.lst and Makefile.fetch.

    ODB files

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

    • Initially, FB's approach will be to unzip the .odb 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: 

    $ apt-get build-dep libreoffice 
$ ./autogen.sh --enable-firebird-sdbc --without-system-firebird 

make -s debug=2

    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 SAL_DEBUG 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#Assertions and Logging

    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 sdbc:firebird 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:
        1. Create new database
        2. Using an existing ODB file.
        3. Connect to an existing database.
      2. 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.
      3. Create new database
        The current implementation assumes there is already an .rdb file with a database created in the path /var/tmp/libo-fb/new.fdb.
        We should implement the CREATE DATABASE statement using the user defined parameters.
    2. Statements
      Right now it's possible to instantiate XStatement interfaces and execute both, queries and insert/updates, via executeQuery() and execute() methods. however, the execute() method has the CREATE TABLE statement hardcoded.
      1. Implementing a generic execute() 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.
      2. 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.
      3. Create Table
        As it was already commented, the OStatement_Base::execute implements the CREATE TABLE 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 ?)
      4. ResultSet
        The implementation of the ResultSet interface is still a bit dummy, using a custom defined type (TTable), which perhaps is not the best approach to follow. In any case, the free/destroy operations might handle this data structure.
      5. Error Management
        The current driver implementation uses a static function pr_error (long*, char*), 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#Finish Firebird driver integration

    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

    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.
    Retrieved from "https://wiki.documentfoundation.org/index.php?title=Development/Base/FirebirdSQL&oldid=417905"