Base: Firebird vs HSQLDB embedded database?

[peterwt]

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

[EasyTrieve]

Thanks for the links @mikekaganski.

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

[mikekaganski]

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/ReleaseNotes/5.3#Base

[EasyTrieve]

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

[mikekaganski]

and didn’t second link in my previous comment answer your last question?

[EasyTrieve]

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

[mikekaganski]

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

[EasyTrieve]

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

[Zoidman]

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!

[mikekaganski]

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

[Zoidman]

@mikekaganski: 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!

[rautamiekka]

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

[BEEDELLROKEJULIANLOCKHART92300]

@Zoidman, nobody is attempting to remove support for HSQLDB, though, so your weird argument is irrelevant and unfounded.

[Zoidman]

@EasyTrieve wrote:

There has been talk since at least 2013 that HSQLDB will be replaced by Firebird for embedded databases.

So then in that context, it is relevant, and FYI I wrote that like what, 2+ years ago?

I gave up on LO for database creation a long time ago, because it wasn’t just unwieldy and needed way too much in depth understanding of DB creation, but also lacking in usable features like drop-downs with selectable code which is all over this forum, but not in the app where it would be of most use, rather than having to spend hours, sometimes days finding it, and risking the info being outdated, and to top it off, changes to tables and forms… would often not be applied, Base would freeze or crash… and sorry, but it just didn’t cut it, and I wasted more time trying to use it and finding help than just doing things the hard way, making base the actual real hard way of doing things.

Maybe I will give it a shot again, but I will not get my hopes up, because all of LO is just not made very user friendly, never was as Open Office either, and although a lot of stuff has been added and much has changed for the better, it’s still an unwieldy beast that’s not very intuitive to use, and that needs the most work and seems to get the least attention.

[Villeroy]

@EasyTrieve

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.

HSQL is well maintained by the HSQL project. LO won’t maintain Firebird neither. Meanwhile HSQL has become very mature and rich of feature compared to the 15 year old version. A recent HSQL2 is still more compatible with LibreOffice than Firebird. LO wasted thousands of development hours with Firebird.

@EasyTrieve

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.

Turned out that Java has become one of the most important programming languages during the past 12 years following the “fear of Java because of Oracle”.
How about the almost useless Base wizards? They are completely written in Java. They are not even necessaray to use a connected database. Without any Java I can do almost everything with a MySQL connection in Base except using wizards.

@EasyTrieve

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

Database programs can’t access any database in a zip archive.

@EasyTrieve

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

HSQL is not slow. If anybody ever complained about performance, extracting the HSQLDB from its zip cage was the solution.

@EasyTrieve

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.

An embedded database can’t be accessed by multiple users. The idea of database frontend with backend in a single file has nothing to do with any kind of multi-user database server.

@EasyTrieve

MariaDB and others were also considered.

Almost any database can be mutilated like this, I think.

@EasyTrieve

Firebird was then suggested.

… 9 years ago and still does not work as well with the Base frontend as any version of HSQL.

[EasyTrieve]

I got an email informing me that Villeroy had added some comments to this thread, but when I open this thread, his comments are missing from the web page. What is going on?

[Villeroy]

You have to find a hyperlink like “show X more comments”. This web page is almost as evil as Base.

[jimk]

To determine the version, go to Tools → SQL and run this statement from c++ - Ways to Determine the Version of Firebird SQL? - Stack Overflow.

SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION') 
         as version from rdb$database;

The output agrees with the link that @mikekaganski gave:

3.0.0,

[westmj]

SELECT rdb$get_context(‘SYSTEM’, ‘ENGINE_VERSION’)
as version from rdb$database;

gives me

1: Access is denied: RDB$GET_CONTEXT in statement [SELECT rdb$get_context(] …

Any advice? Thanks.

[jimk]

Open a new question and mention your LibreOffice version and operating system. Since this is your first question, take a look at guidelines for asking.