LO / Firebird DB Integration

[Lionel Elie Mamane]

On Mon, Jun 24, 2013 at 09:38:11PM +0200, Jean-Pierre Ledure wrote:

>> I do not see any improvement with having to bundle hsqldb
>> vs. having to bundle firebird.

> If Firebird becomes the default database in Base, it will not be a
> matter of bundling either HSQLDB or Firebird, but of bundling _both_
> !

IMO the TDF builds will bundle both; hopefully we can use firebird
unpatched, and then distro builds will use their system firebird.

> AFAIK the main issue in using a HSQLDB database embedded in the .odb
> file is that, when LO crashes, the chance is big that the database
> is destroyed: the recovery process does not recover that part of the
> file.

Oh. That's bad. I never encountered it, but I don't use embedded
database much. If this is reproducible (e.g. by killing LibreOffice at
during some operation) or if you have such a "broken" file, it is well
worth a complete bug report.

Would you happen to know the exact issue "under the hood"? AFAIK the
recovery process just opens an "automatic save" backup of the file
from the user's configuration directory; I doubt it goes within the
file structure and does anything special in it.

So the issue could be:

 - The recovery process goes within the file but "skips" the database/
   directory.

 - The database itself is corrupted (e.g. the SQL file is only
   partially written, or the binary-format "CACHED" table file is
   corrupted, ...).

 - ...?

> Will it still be the case with an embedded Firebird database ?

It depends what the underlying cause is.

> In that context I invite interested people to read a.o. the thread
> published last monday on
> http://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=62419 by
> DACM.
> An extract:

>> Unfortunately, the devs remain preoccupied with the embedded
>> database concept based on a default database engine.

I disagree with the unqualified scope of that assessment. AFAIK, work
on the embedded database is a small fraction of what is happening with
Base. And this "summer work" is done by people that AFAIK (apologies
in advance if I'm mistaken) don't really work on other aspects of
Base. (I *do* hope that after being introduced to Base through this
work they will "hang around" and work on other stuff.)

I do think that embedded database has a role to play in the "early
life" of users; think e.g. of those just "graduating" from using a
spreadsheet as a rudimentary database.

Embedded database works like they expect it to, and then don't need to
understand the difference between database and UI. I've found that
this is a pretty big cognitive hurdle in the beginning. E.g. if they
email the file to another user, it "just works". If they make a copy
of the file, it "just works" (the two copies contain
forked/independent data).

Once they've become familiar with the concepts of tables, columns,
rows, queries, reports, forms, etc they can begin to understand the
split between database and UI, and reap the benefits of the
split. Like, yes, better reliability, concurrent access by several
users, ... And they are *interested* enough to actually listen to the
explanation of the split (they have seen the advantages of using a
RDBMS instead of a spreadsheet). In the beginning it just goes over
their head, when faced with a "forced split" they decide this RDBMS
thing is too complicated for them and end of story.<

And even for expert users, there are inherent advantages to embedded
database. Essentially, their easy transportability. I can email one to
another user for data entry and he emails it back to me. Using a split
(external) database would require one of:

 1) Network file system over the Internet... bleh...

 2) Running my MariaDB / PostgreSQL / ... "open to the Internet"
    ... bleh for security...

 3) Laborious explanations of "save these two files, open file1, then
    go in the settings and change the 'path to data' setting to point
    to file2. To do your data entry, open file1. To give me the
    results, email me file2." In the best case, they then email me
    file1. Because that's the one they open, so that's the one that
    changes... Makes sense to them, and that's to be expected because
    that's ALL OTHER PROGRAMS WORK.

    In a worse case, I get an ungeeky version of "tl;dr" like "too
    complicated", "I don't understand", "what do I have to put in the
    'path to data' setting? I don't know where I put file2. I just put
    'save'".

In the absence of embedded database, this use case basically has to
revert to... Calc sheets (!) as database.


DACM also says:

>> The devs don't seem to be interested in DDL support through the GUI
>> for externally-connected (JDBC/ODBC) databases

I *very* strongly disagree with that. I'd be *very* happy to take
patches for data definition support in the GUI for
externally-connected databases. I do treat DDL support has lower
priority than many other issues for scheduling my work, that's
true. I'd be very happy if lack of DDL support were Base's main / most
serious issue, because it would mean it would be in a better shape
than now!

One also has to keep in mind the limits of what's possible. AFAIK,
ODBC offers very little DDL abstraction / standardisation, so there is
not much one can do generically.
http://msdn.microsoft.com/en-us/library/windows/desktop/ms713602(v=vs.85).aspx
One very quickly delves into DBMS-specific syntax, and then basically
we need DBMS-specific drivers. These drivers, however, can be thin
wrappers around JDBC or ODBC like the embedded-HSQLB or the "MySQL
(ODBC)" and "MySQL (JDBC)" drivers are.

AFAIK, JDBC does not do better on that front.


Where we already have a specific driver, the GUI support of DDL only
"needs to be implemented": PostgreSQL, MySQL, ...


>> We've also moved beyond the idea of a default database with Base.
>> This actually free's the devs to eliminate internal Java
>> dependencies from the entire LibO/AOO code-base, perhaps with the
>> exception of the hooks necessary for external JDBC support.

There is very little internal Java dependency that is specific to
embedded HSQLDB. Dropping the concept of embedded database will *not*
help in eliminating Java dependencies. The big dependencies are either
database-neutral (like wizards and report builder) or "the hooks
necessary for JDBC support".

It may surprise you, but LibreOffice Base uses JDBC to communicate
with its embedded HSQLDB.

The HSQLDB-specific Java part is about 306 lines of code (in
connectivity/com/sun/star/sdbcx/comp/hsqldb/)

-- 
Lionel