+++ 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!
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
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 ...
Changed status to "NEW"
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)
(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?
An alternative way to proceed, rather than using the FireBird API directly a
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.
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/
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.
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 ? ].
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.
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 [!].
(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"
Added my email to the CC list.
Work for this task is being coordinated in: https://wiki.documentfoundation.org/Development/Base/FirebirdSQL
adding LibreOffice developer list as CC to unresolved EasyHacks for better visibility. see e.g. http://nabble.documentfoundation.org/minutes-of-ESC-call-td4076214.html for details
uneasyhackifying this -- way to complex an issue to get started.
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
Adding self to CC if not already on
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.
P.S. Tested with 5.2.0 beta2
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?
Could you add #101399 as blocker for this issue?
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 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.
(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.
(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 ;-)