We need to select a good replacement for our embedded HSQLDB 1.8, which is holding us back in terms of features, not supported upstream anymore (we have to maintain it ourselves, see e.g. bug 36824). The current ideas: * update to HSQLDB 2.2: + little programming job on LibO side, it is already there, only version bump + easy "ascending compatibility" migration (opening old files with new LibO) - problems with descending compatibility (opening new files with old LibO) - do we have to keep 1.8 *and* 2.2 in LibO to solve descending compatibility? - keeps a hard Java dependency * Firebird - more programming job on LibO side: write SDBC(X) driver, hooks for embedded mode - will have to keep HSQLDB 1.8 in LibO anyway for ascending/descending compatibility + gets rid (for new files) of one Java dependency Add the "enhancement" bug for any other runner-up as depending on this bug. Non-solutions: * sqlite: too far from SQL standards, no decent date/time support; see https://bugs.freedesktop.org/show_bug.cgi?id=38811#c19 and following comments. * MySQL/MariaDB/... : can be embedded, but GPL * CSQL: GPL * MonetDB: introduced embedded mode in 2004, but dropped it as of 2011 (source says it was not a true embedded mode anyway, only a single client server)
> * update to HSQLDB 2.2: > - problems with descending compatibility (opening new files with old LibO) Why would anyone want to/need to open a new file with an old LibO version? > - do we have to keep 1.8 *and* 2.2 in LibO to solve descending compatibility? HSQLDB 2.2 is backwards compatible, so I cannot see any need to keep 1.8. To create "old" files? > - keeps a hard Java dependency Doesn't LibO not anyway require JRE? I do not know any computer in my surroundings that does not have JRE installed.
(In reply to comment #1) >> * update to HSQLDB 2.2: >> - problems with descending compatibility (opening new files with old LibO) > Why would anyone want to/need to open a new file with an old LibO version? For example because one is using an older version and one gets sent an .odb file. Or because one has an older version on one computer and a newer version on another computer (e.g. different distros or versions of the same distro: Ubuntu LTS on one, and Ubuntu latest bleeding edge on the other), and one wants to access the same file with both computers. >> - do we have to keep 1.8 *and* 2.2 in LibO to solve descending >> compatibility? > HSQLDB 2.2 is backwards compatible, so I cannot see any need to keep 1.8. To > create "old" files? It has only ascending compatibility: it can open files created with 1.8, but it silently upgrades them to 2.0 format. This means that any file merely opened with a "new" LibreOffice (no change made) cannot any more be opened with older LibreOffice. That would be rather onerous. >> - keeps a hard Java dependency > Doesn't LibO not anyway require JRE? LibO requires a JRE only for some features. We are trying to minimise the features that require a JRE; there is no plan to remove support for writing extensions in Java and for controlling / scripting a LibO instance from Java. AFAIK Base is the last holdout that needs a JRE for basic/core features. As soon as Java (running the Oracle implementation) is involved, debugging becomes much harder, because of the mix of C(++) and Java code calling each other. > I do not know any computer in my surroundings > that does not have JRE installed. Surroundings differ. Many people don't know any computer in their surroundings that does not have Microsoft Windows installed. Personally, on my home computers, and all computers in my office, LibO is the only reason there is a JRE.
If there are legitimate needs to keep running older LibO versions, then perhaps it would be easier to write an external file converter, to downgrade 2.0 files to 1.x? With "easier" I mean better than to keep two SQL engine versions in the product.
HSQLDB 2.2.x can open files created by version 1.8.x as readonly. An upgraded Base could check the version in the .properties file and show a dialog box to the user to decide whether to upgrade the database or open it as readonly.
If you need help in writing the Firebird SDBC driver please ask I will add the notice for developers who want to help with that
> If you need help in writing the Firebird SDBC driver please ask > I will add the notice for developers who want to help with that Wow that would be fantastic - we always need help :-) With a good firebird driver, there is a very good chance that we'll switch to Firebird as our default, built-in database engine so ... hopefully that is motivating :-) Any questions on sdbc - introduce yourselves on #libreoffice-dev irc.freenode.net and/or grab me 'mmeeks' - I'd love to answer queries / help get a build going.
+1 Firebird. In my experience solid like a rock. With both server and embedded mode operation.
As one of the reasons for ruling out SQLite was its dynamic typing I want to point out that SQLite actually supports type constraints: http://www.sqlite.org/lang_createtable.html#ckconst So by adding CHECK() constraints one actually can ensure that columns only contain data that matches the declared type. The other reason I find in bug 36824 were the embarrassing stunts apparently needed for date-time operations. One would work arround the demonstrated issues by assigning a date-time aware collation to date-time columns: http://www.sqlite.org/datatype3.html#collation I also want to point out that SQLite has the huge advantage of being deployed already on a wide range of platforms. Additionally LibO already has proper drivers for it it seems.
(In reply to comment #8) > As one of the reasons for ruling out SQLite was its dynamic typing I want to > point out that SQLite actually supports type constraints: > http://www.sqlite.org/lang_createtable.html#ckconst > So by adding CHECK() constraints one actually can ensure that columns only > contain data that matches the declared type. It is out of the question to require users to add check constraints manually. As to adding them automatically, it would be a significant deviation of the behaviour "with SQLite" (add the CHECK clauses) and "with another datasource" (don't add the CHECK clauses); the least we have to special-case, the better. Especially since this "add CHECK clauses" would be rather difficult to do at the driver level (rather than adding SQLite-specific hacks to the part of LibO that is supposed to be datasource-agnostic, and which constructs SQL statements to be executed); the driver gets an SQL string, so if it wants to fiddle with it, then it has to parse it. If we can use SQLite's parser (can we? is there an API call to get a parse tree from an SQL string?), then maybe OK. Or using LibO's parser... after all it wouldn't be so bad that the embedded DB can only do what LibO's parser can understand. Another thing I dislike about SQLite is that it does not support RIGHT JOIN, as per http://www.sqlite.org/syntaxdiagrams.html#join-op . That's often theoretically not a problem (just switch the arguments and do a LEFT JOIN), but again that's a "fixup of SQL string to comply to SQLite restrictions" we'd have to do. The more of these things we have to do, the more fragile the whole edifice becomes. Also, it conflicts with the fix for bug 42165. Yes, SQLite supports nested/bracketed joins, so we can theoretically not force left-recursion when connecting to SQLite, but *again* a thing we need to do different for SQLite than for all other datasources. > The other reason I find in bug 36824 were the embarrassing stunts apparently > needed for date-time operations. One would work arround the demonstrated > issues by assigning a date-time aware collation to date-time columns: > http://www.sqlite.org/datatype3.html#collation The documentation does not speak of datetime collation, so you mean we have to write it ourselves? You are just making the point that SQLite is a "storage engine" that one can possibly BUILD UPON to make an ISO SQL database, but it is not there. I don't think that a datetime collation will fix all the issues, for example of non-comparison operators doing the right thing. What about: CREATE TEMPORARY TABLE tst (num INTEGER NOT NULL, d DATE, PRIMARY KEY ("num")); INSERT INTO tst VALUES (1, '1970-03-01'); INSERT INTO tst VALUES (2, '1970-03-31'); SELECT num, d+1 AS dp1 FROM tst; This should give: 1 | 1970-03-02 2 | 1970-04-01 Will it? Don't think so. Right now (so without the putative datetime collation) it gives: 1 | 1971 2 | 1971
AFAIK we already did the selection of a good replacement: https://bugs.freedesktop.org/show_bug.cgi?id=51780 and Firebird is the answer - all that remains is a chunk of typing, debugging, etc. - for which there code pointers in that bug :-) So - could we close this one ?
Have you considered using the H2 database? It is from the original author as HSQLDB and seems to be well maintained. I've had success connecting LibO to it using JDBC.
As Firebird commits start to appear in master and bug 51780 depends on this bug I will mark this report as RESOLVED FIXED per comment 10. Do not hesitate to reopen in case of mistriage.