Ask Your Question
2

Base: Firebird vs HSQLDB embedded database?

asked 2017-02-28 22:49:27 +0100

LO has had HSQLDB embedded for quite awhile. And now with newer versions of LO you also get the choice of using the embedded Firebird data base.

  • But why the change?, ...especially in light of the fact that: the default Embedded HSQLDB is the old version of HSQLDB, i.e. 1.8? In other words, before adding a whole new database, why not upgrade existing users first to a newer version of HSLQDB (2.3)?

  • Also, what version of Firebird is in LO? (I would think it should be made really easy in LO to see this, like right where it says Firebird it should have the version#.)

  • Finally, what's Firebird got that HSQLDB doesn't, and vise versa? (I can see that it might be much, much faster being written in C++ rather than Java.)

  • I can also see that the software licenses are different between these two databases. What does that mean for LO users?

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
2

answered 2017-03-03 13:54:33 +0100

edit flag offensive delete link more
1

answered 2017-03-03 12:31:58 +0100

updated 2017-03-04 14:53:43 +0100

@EasyTrieve There has been talk since at least 2013 that HSQLDB will be replaced by Firebird for embedded databases. It is available to test in later versions of LO.

I tried it a while back with LO ver. 5.0.2.2. I quickly found a problem in creating auto-increment fields. The GUI in creating a Table with an auto-increment field appears to work but after saving the Table and opening it in Edit mode it does not have it as auto-increment. Looking at the documentation for Firebird I found that it does not implement auto-increment fields directly. You have to create a Trigger to do so. After creating a Table with an auto-increment field it is necessary to use SQL to create the Trigger. Opening the Table and putting data in the Table produces a confusing result as every record added shows the auto-increment field as 0 until you close the table and open it again when the correct auto-increment values are shown. The same happens if you use a Form to input data - you need to close and open the form again for the correct values to be shown. I don't know if this has been improved in later LO versions. With HSQLDB the Database directory in the .odb zip file has a script containing the SQL to create tables and includes the creation of auto-increment fields. With Firebird there is just a single firebird.fdb file.

UPDATE 4/3/2017

I can confirm that the auto-increment problem I mentioned above has been fixed in version 5.3 of LO.

edit flag offensive delete link more
0

answered 2017-03-03 16:56:33 +0100

updated 2017-03-04 08:58:35 +0100

Thanks for the links @Mike Kaganski.

So it all started with this:

"Background: currently when creating a local database we default to this Java database engine. That is somewhat unfortunate. It would be better to default to a SQLite provider .."

It looks like these are the points:

  • Maintaining Base (fixing it's bugs) is hard because people need to be an expert in Java, C++, UNO, and databases, especially SQL. It's hard to attract people that can cover all of these well.

  • There is fear of Java because of Oracle. (HSQLDB runs in Java). Even though there is OpenJDK, I'm getting that there is still fear that somehow that might go away leaving Oracle in control.

  • Other software can not access the LO database without also having Java setup which complicates things.

  • Java can be a bit slower than C++. ODBC was also looked at but is also limiting.

  • An effort was first made to investigate replacing HSQLDB w/ SQLite. This was later abandoned because SQLite was type poor and SQL is type rich. Also SQLite it was SQL feature poor. And SQLite was not multi-user. It would have meant significantly dumbing down LO.

  • MariaDB and others were also considered.

  • Firebird was then suggested.

It's unclear to me how Firebird compares with HSQLDB. But Firebird does appear to have a higher ranking here, and it doesn't depend on Java, but rather is written in C++.

However I noticed that Firebird does not currently (at least as of the latest reference manual) support TINYINT but HSQLDB does. I use TINYINT. It's what is suggested by MS Access To MySQL for converting Access Yes/No (Boolean) types to MySQL.


(So at least for me, this answers my questions, except for the version of Firebird currently in LO, which remains unknown.)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

3 followers

Stats

Asked: 2017-02-28 22:49:27 +0100

Seen: 5,156 times

Last updated: Mar 04 '17
{"cc_re__cc_re__cc_by_sa_3_0_le": ["or dB? [closed]</a> </p> <p> <a href=\"/en/question/157587/mac-java-problem-when-creating-a-database/\">Mac Java problem when creating a database</a> </p> <p> <a href=\"/en/question/59321/how-to-export-data-from-base/\">How to export data from Base?</a> </p> <p> <a href=\"/en/question/53844/importing-csv-files-to-a-base-table/\">importing csv files to a Base table</a> </p> </div> </div> </div> </div> <!-- template footer.html --> <div id=\"ground\"> <div class=\"content-wrapper\"> <div class=\"copyright\"> <a href=\"http://creativecommons.org/licenses/by-sa/3.0/legalcode\"> <img\n                            class=\"license-logo\"\n                            src=\"/m/default/media/images/cc-by-sa.png?v=20\"\n                            title=\"cc-by-sa\"\n                            alt=\"cc-by-sa\"\n                        /> </a>\n                \n                 Content on this site is licensed under a <a href=\"http://creativecommons.org/licenses/by-sa/3.0/legalcode\">Creative Commons Attribution Share Alike 3.0</a> license.\n\n            </div> <div class=\"footer-links\" > <a", "/div> <!-- template footer.html --> <div id=\"ground\"> <div class=\"content-wrapper\"> <div class=\"copyright\"> <a href=\"http://creativecommons.org/licenses/by-sa/3.0/legalcode\"> <img\n                            class=\"license-logo\"\n                            src=\"/m/default/media/images/cc-by-sa.png?v=20\"\n                            title=\"cc-by-sa\"\n                            alt=\"cc-by-sa\"\n                        /> </a>\n                \n                 Content on this site is licensed under a <a href=\"http://creativecommons.org/licenses/by-sa/3.0/legalcode\">Creative Commons Attribution Share Alike 3.0</a> license.\n\n            </div> <div class=\"footer-links\" > <a id=\"toTop\" href=\"#\" title=\"scroll to top of page\" style=\"display: block; \">\u2b06 to top</a> <a href=\"https://www.documentfoundation.org/imprint\">impressum (legal info)</a><span class=\"link-separator\"> |</span> <a href=\"/en/about/\">about</a><span class=\"link-separator\"> |</span> <a href=\"/en/faq/\">faq</a><span class=\"link-separator\"> |</span> <a href=\"/en/help/\" title=\"help\">help</a> <span c"], "copyright__copyright__copyright": ["ently (at least as of the latest reference manual) support TINYINT but HSQLDB does. I use TINYINT. It's what is suggested by MS Access To MySQL for converting Access Yes/No (Boolean) types to MySQL.\n\n\n(So at least for me, this answers my questions, except for the version of Firebird currently in LO, which remains unknown.)\n\nedit flag offensive delete link more\nLogin/Signup to Answer\n\nQuestion Tools\n\n3 followers\n\nStats\n\nAsked: 2017-02-28 22:49:27 +0100\n\nSeen: 5,156 times\n\nLast updated: Mar 04 '17"]}