Bug 51780 (Database-Firebird) - [META] Default to Firebird not HSQLDB in Base
Summary: [META] Default to Firebird not HSQLDB in Base
Status: NEW
Alias: Database-Firebird
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: Other All
: medium enhancement
Assignee: Not Assigned
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on: 70425 71007 85148 104734 104918 104942 104957 104986 105062 105075 105112 105220 105604 105711 51781 69949 70506 71009 71251 72827 72987 74019 74172 76072 91324 101399 101789 103074 103379 103384 104227 104316 105101 105142 105240
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2012-07-06 03:54 UTC by Lionel Elie Mamane
Modified: 2017-11-15 23:18 UTC (History)
25 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2012-07-06 03:54:07 UTC
+++ This bug was initially created as a clone of Bug #38811 +++

Background: currently when creating an embedded database we default to the Java-based HSQLDB database engine. That is somewhat unfortunate. It would be better to default to an embedded FireBird provider - unfortunately we don't have one. So - it needs writing - and then the default changing. See [http://www.sqlite.org/c3ref/intro.html The SQLite C/C++ Interface].

Resources on the Firebird side:
Embedding:
 http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg25-classic-or-super.html
 http://www.firebirdsql.org/manual/ufb-cs-embedded.html
 http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg25-databases.html#qsg25-databases-connstrings-local
SQL Language:
 http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25.html
API:
 examples/api in the FireBird sources
 sources of isql-fb program
 ...

Grr... It says (http://www.firebirdsql.org/manual/ufb-cs-embedded.html#ufb-cs-embedded-linux)

 Finally, you can't just ship libfbembed.so with your application and
 use it to connect to local databases. Under Linux, you always need a
 properly installed server, be it Classic or Super.

That could be a problem, depending on what "a properly installed server" is. Can we just ship it in LIBROFFICE_ROOT/program/fbserver? Or will it want config files in /etc/ and that kind of things? To be investigated.

With HSQLDB, we override its "access to files" class so that it writes directly into our in-memory and on-disk ZIP file structure (.odb file). Can we do something similar with FireBird or will we have to use temporary files that are copied into the .odb file at each .odb "file save" operation? In the latter case, how to detect those have changed and activate the "file save" icon?

Resources on the LibreOffice side:

OpenOffice.org Developer's Guide at
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/OpenOffice.org_Developers_Guide,
and in particular these sections:

 * Writing Database Drivers:
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Database/Writing_Database_Drivers
 * About UNO:
    
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/ProUNO/Professional_UNO
    
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/WritingUNO/Writing_UNO_Components
    
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/AdvUNO/Advanced_UNO


There is also a driver skeleton  in the SDK
documentation, and it has a file named "How_to_write_my_own_driver.txt" with a
more hands-on approach than the Developer's Guide, which gives more a
high-level overview and reference. In a git checkout of the source tree, it is
in odk/examples/DevelopersGuide/Database/DriverSkeleton

Also gleaned:

 * http://help.libreoffice.org/Common/Integrating_new_UNO_components

> * What classes/services/interfaces at a minimum have to be implemented?

See
http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Database/SDBC_Driver
for a functional minimum. After, you can add SDBCX services which provide added
features.

> * Are the code skeletons for the drivers usually auto-generated or written by
> hand?

The Database Driver Skeleton in the SDK seems to be written by hand, as it is
"as is" in the git sources.

> * Are there any existing unit tests for testing Base functionality with new
> drivers? If not, how were the existing drivers tested?

We currently don't have any, but that would be a fantastic opportunity to add some :)

> * (a simple one) How will I set the default driver to SQLite in the source
> code?

Chase for "embedded" (and especially EMBEDDED_HSQLDB) in connectivity and
dbaccess directories of the source tree. That's the points where it currently
does something "special" for embedded HSQLDB, and where you'll have to do
something similar for embedded SQLite DB.



Let me introduce myself. I've started taking care of the PostgreSQL driver for
LibreOffice. That was also my first sizeable contribution to LibreOffice. As
such, I think:

 - I can be useful for you in the "bootstrap" phase sharing the things I
   learned while doing the PostgreSQL driver.

 - It would be useful for both us if we stayed in touch and shared
   experiences / what we learn with each other.

I was rather lucky in that there was a "basically working / beta quality"
driver for PostgreSQL for OpenOffice.org already, that was actively used in
Debian&Ubuntu, and I basically handled bitrot (updating it to new OO.org /
Libreoffice version) and added a few "easy" features and optimisations I liked
or wanted.

For FireBird, you'll have to start from scratch.

As to PostgreSQL-specific knowledge (like "how to list a table's columns and
their datatype"), I've found it useful to just look at how the ODBC and JDBC
driver do things, and how the "psql" command-line interface program does
things. For that, you could use the sources of the ODBC, JDBC, .NET, Python,
C++ drivers/wrappers.

Actually, maybe using the C++ wrapper could be efficient; to be investigated.


One last thing: once you have decided to do this, put the bug's status to
"ASSIGNED" and put yourself into "assigned to". If you change your mind in
future and move to other things, put the bug back in "NEW" state and "reset
assignee to default".

Welcome to the community and happy hacking!
Comment 1 Popa Adrian Marius 2012-07-10 09:41:46 UTC
The short route would be to use the ibpp api it is battle tested in flamerobin codebase and quite stable 

Another route is to create a small wrapper over the c api like is done in the 
node js driver 
https://github.com/xdenser/node-firebird-libfbclient/tree/master/src


And of course the future the clean firebird 3.0 api could be used
Comment 2 robert 2012-07-13 19:10:11 UTC
Two hints:

Embedding Firebird in Linux:
http://www.firebirdfaq.org/Firebird-Embedded-Linux-HOWTO.html

Have tested Firebird for the first time. I have mostly worked with MySQL and Base/HSQLDB. Firebird seems to be more complicated than HSQLDB or MySQL. I only wanted to create a table with incemented value at the primary-key. You have to create a generator and a trigger before it works ...
Comment 3 Jochen 2012-08-30 07:08:06 UTC
Changed status to "NEW"
Comment 4 Popa Adrian Marius 2012-11-16 22:50:02 UTC
I have decided to use the Firebird 3 OO api 

Here are a few examples 
https://github.com/zedalaye/fb3-oo-api
https://github.com/asfernandes/fbstuff

One of the reasons is that firebird 3 will be available on android the future (Fbclient can be compiled today)
Api is quite clean compared to c version , and it can be better integrated with libreoffice driver api 
Firebird 3.0 api will work with existing firebird 2.5 servers
In long term i think Firebird 3.0 api is the better way (call it guts)
Comment 5 Mathias Hasselmann 2013-01-14 13:06:28 UTC
(In reply to comment #4)
> I have decided to use the Firebird 3 OO api 
> 
> Here are a few examples 
> https://github.com/zedalaye/fb3-oo-api
> https://github.com/asfernandes/fbstuff
> 
> One of the reasons is that firebird 3 will be available on android the
> future (Fbclient can be compiled today)
> Api is quite clean compared to c version , and it can be better integrated
> with libreoffice driver api 
> Firebird 3.0 api will work with existing firebird 2.5 servers
> In long term i think Firebird 3.0 api is the better way (call it guts)

What's the status of this work?
Comment 6 Lionel Elie Mamane 2013-03-11 15:12:36 UTC
An alternative way to proceed, rather than using the FireBird API directly a
Comment 7 Lionel Elie Mamane 2013-03-11 15:20:33 UTC
An alternative way to proceed, rather than using the FireBird API directly and writing a native SDBC driver, would be to:

 - Link LibreOffice's "embedded FireBird" driver directly to
   the/a FireBird ODBC driver
   (without the use of an ODBC driver manager)

 - Ship the FireBird ODBC driver "embedded" with LibreOffice

 - Make the "embedded FireBird" driver a redirection to an ad-hoc ODBC URL
   or reuse the ODBC-SDBC layer in some other way.
   That's how the sdbc:mysql:odbc:, sdbc:mysql:jdbc: and sdbc:embedded:hsqldb
   URL schemes actually work :) The difference being that for HSQLDB, JDBC *is*
   its native API.

We would possibly pay some performance penalty, but it could be less work.

Open question: can we redirect FireBird's read()/write() to our internal data structures in this scenario? This could be a blocking issue for this alternative way.
Comment 8 m.a.riosv 2013-03-22 00:37:11 UTC
I do not know if it can be an of interest, as some things have been changed to python.

Firebird Python final driver FDB 1.0 is released
http://www.firebirdsql.org/en/news/firebird-python-final-driver-fdb-1-0-is-released-19176/
Comment 9 Javier Fernández 2013-05-24 11:04:01 UTC
Just for the shake if keeping this alive, I started to look at this some weeks ago. Not sure how far I would go, but I've already implemented some very basic prototipes and how we could integrate Firebird bits into the Libo architecture.
Comment 10 Michael Meeks 2013-05-24 12:31:46 UTC
Javier - I think it'd be well worth creating a branch for this - my hope is that Andrzej will be able to work with you on this soon - you have commit access right ?

feature/firebird

might be a good name :-) and of course, we'll re-base / squash that all into something sensible as we push it to master so I'd commit early & often there ? [ assuming that's ok ? ].
Comment 11 m.a.riosv 2013-05-27 22:10:40 UTC
There is a thread in Firbird dev mailing list

http://www.mail-archive.com/firebird-devel@lists.sourceforge.net/msg03936.html
Firebird as backend in LibreOffice

that I have started a few days ago, and seems even more persons are working on the issue.
Comment 12 Michael Meeks 2013-05-28 09:13:06 UTC
mario - thanks for that link ! :-) Javier / Andrzej - I think it's important that you make contact with these guys - build a CC of all who are interested (please CC me) and try to  migrate that discussion onto the libreoffice-dev list - and get these guys setup with git push access so we can get the code into this feature branch - and everyone working around the same code-base: it's important to avoid duplication & splits early in the work [!].
Comment 13 Javier Fernández 2013-06-06 12:04:52 UTC
(In reply to comment #10)
> Javier - I think it'd be well worth creating a branch for this - my hope is
> that Andrzej will be able to work with you on this soon - you have commit
> access right ?
> 
> feature/firebird
> 
> might be a good name :-) and of course, we'll re-base / squash that all into
> something sensible as we push it to master so I'd commit early & often there
> ? [ assuming that's ok ? ].

Just to inform that I've pushed the branch: "feature/firebird-sdbc"
Comment 14 Javier Fernández 2013-06-06 12:06:02 UTC Comment hidden (no-value)
Comment 15 Andrés Gómez García 2013-06-06 16:36:11 UTC
Work for this task is being coordinated in:
https://wiki.documentfoundation.org/Development/Base/FirebirdSQL
Comment 16 Björn Michaelsen 2013-10-04 18:46:26 UTC Comment hidden (obsolete)
Comment 17 Björn Michaelsen 2014-02-09 18:33:30 UTC
uneasyhackifying this -- way to complex an issue to get started.
Comment 18 ribotb 2014-04-24 21:38:33 UTC
Adding as blockers:
  bug 69949 - table editor should support the autoincrement
  bug 71009 - relationship management not implemented in GUI
  bug 71251 - Creating table from default choices via wizard fails
  bug 74019 - Any table or select query having more than 10 fields fails
  bug 74172 - no data type DECIMAL/NUMERIC 
  bug 76072 - view creation not implemented
Comment 19 Alex Thurgood 2015-01-03 17:40:50 UTC Comment hidden (no-value)
Comment 20 Gerhard Schaber 2016-06-28 19:37:13 UTC
I would like to vote for bug #91324 to be added here. The behavior of the Date type is generally rather unpredictable. For example, create a table with a Date field and try to enter 2015-12-12. It always changes the date to 2016-01-12. Or And Extract ( Year FROM x ) returns 3915 if x is a date with year 2015.
Comment 21 Gerhard Schaber 2016-06-28 19:38:42 UTC
P.S. Tested with 5.2.0 beta2
Comment 22 Gerhard Schaber 2016-06-29 12:07:11 UTC
Thanks. Another question--is there a way to allow external file tables with Firebird? Apparently, ExternalFileAccess must be set in firebird.conf to Full for full file system access to allow Firebird to store files. I could not find a firebird.conf in the LibreOffice installation, though.

Well, it will only allow files with fixed width columns as opposed to the CSV files of the HSQLDB. I guess there are no plans to allow CSV text tables, unless someone implements this in Firebird, right?
Comment 23 Gerhard Schaber 2016-08-08 21:25:22 UTC
Could you add #101399 as blocker for this issue?
Comment 24 Michael Meeks 2016-11-14 09:51:34 UTC
Added, but it's fixed already - wow - it's great to see all of these blockers to Firebird being the default fixed =) Can we switch the default for 5.3 ? =)
Comment 25 Gerhard Schaber 2016-11-14 10:00:17 UTC
I agree, great progress. Amazing. One thing that still makes it a little difficult to use is #103379. A text table for import and export via CSV would be nice, but this can be done in a macro as well.
Comment 26 Julien Nabet 2016-11-14 11:23:32 UTC
(In reply to Michael Meeks from comment #24)
> Added, but it's fixed already - wow - it's great to see all of these
> blockers to Firebird being the default fixed =) Can we switch the default
> for 5.3 ? =)

I add tdf#101789 because I think not being able to build Firebird part on MacOs is blocking.
There's been some discussion about root cause of the pb here:
http://nabble.documentfoundation.org/Firebird-build-fails-on-MacOs-tdf-101789-td4199396.html
but no solution for the moment.
Comment 27 Alex Thurgood 2016-11-14 12:03:15 UTC
(In reply to Julien Nabet from comment #26)

> 
> I add tdf#101789 because I think not being able to build Firebird part on
> MacOs is blocking.


Agreed. I would further add that backwards incompatibility between FB3 and FB2 embedded database versions can only be resolved by downloading and installing the whole Firebird installation set and using the included CLI tools. In between the time when we (if ever) get around to providing a fix for this knowingly taken decision, we will no doubt be "submerged" by screaming users raging at us for having killed acces to their previous FB2 databases. Well, we might get two or three reports ;-)