Base: Firebird vs HSQLDB embedded database?

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 close merge delete

Sort by » oldest newest most voted

The reasoning and progress may be seen here:

more

Thanks. BTW, this bug (feature suggestion) refers to an even older feature discussion, and in particular I noticed this interesting post.

( 2017-03-03 15:42:39 +0200 )edit

@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.

more

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.)

more

looks like Firebird has less, database wise, than HSQLDB

Not true. It is enterprise-grade RDBMS.

the version of Firebird currently in LO

https://wiki.documentfoundation.org/R...

( 2017-03-03 17:51:11 +0200 )edit

@Mike Kaganski, You're right, I don't really know how the two compare, and my initial take on this was wrong. Corrected my comment to reflect this.

( 2017-03-04 09:00:57 +0200 )edit

( 2017-03-04 13:00:15 +0200 )edit

@mike-kaganski, sorry, but still not sure of version. Both 2.5 and 3.0 have been in LO. And it says, "A Firebird 2.5 file cannot be opened with 3.0." So how does one know what they are working with? Furthermore, even with 3.0 in the current stuff, the manual is only at 2.5, and "beta" at that, so I think we are still living in a barely 2.5 world. rant: The developers I most admire are the ones who are smart enough to see that the code is not complete until the documentation is also complete.

( 2017-03-04 20:05:34 +0200 )edit

@EasyTrieve: We are most excited if you step in and improve this situation. To start contributing to LO is easy, and actually you have already started being here.

( 2017-03-04 20:24:06 +0200 )edit

@Mike Kaganski, I'm doing all I can for now. We all have our limitations. There are more important things I need from LO than Firebird, like for example I need combo and list boxes that support multiple columns, and queries that support foreign key pull downs, to make it easier to edit related tables. Subjects for another day.

( 2017-03-04 21:41:07 +0200 )edit

Has anyone thought about what all will happen if LO drops support for HSQL? HELLO!!!

If "The document foundation" (Read the mission statement) argues over what to replace, instead of improving what is already there and so many people have used for years, and making LO easier and more comprehensive to use, efficient, remove the bugs and keep it up to date with the technology it already features, then change the mission statement, because you keep us from making documents by screwing with it!

( 2017-04-12 21:45:53 +0200 )edit
1

@Zoidman: could you try to read and comprehend prior to starting to generate random accusations?

The replacement discussed here deals with default format of new Base documents. No one wants to make countless existing documents to become unreadable. But if you are eager to help making LO easier and more comprehensive to use, efficient, remove the bugs and keep it up to date with the technology it already features, then we welcome any constructive help.

( 2017-04-12 22:00:52 +0200 )edit

@Mike Kaganski: Most of us don't have the where with it all to be constructive, we use computers to get work done, as tools, not as tool makers. I have been using mostly FOSS software for decades now, and still have no other way to deal with its drawbacks than to rattle the cages of those who make it. What good is the ideology of free software (in the R Stallman sense) made by a community, if they can't act like one, let alone attract users instead of drive them away to megalomaniac Micro$oft! ( 2017-04-12 22:32:16 +0200 )edit @EasyTrieve I suppose there's still a point at fearing Java, but Oracle did pretty long ago (fully ?) open-source Java by moving the vanilla Java code into OpenJDK and have everyone work on just OpenJDK instead of OpenJDK following Oracle within a distance, from which Oracle Java is compiled. Oracle Java has proven itself to work under considerable loads on Linux, so as long as Oracle distributes it, I'll use it, after which I'll go OpenJDK. ( 2017-04-13 17:40:07 +0200 )edit To determine the version, go to Tools -> SQLand run this statement from https://stackoverflow.com/questions/1.... SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')
as version from rdb\$database;


The output agrees with the link that @Mike Kaganski gave:

3.0.0,

more