Firebird ICU/binary compatibility

On Tue, May 28, 2013 at 07:48:36PM +0100, Andrzej J. R. Hunt wrote:

> I've create a wiki page where I'm putting down some notes on whats
> happening here (...):
> https://wiki.documentfoundation.org/Development/Base/FirebirdSQL

Wrt to section 3.2 Compilation:

 In indices on text-based columns (CHAR/VARCHAR), Firebird uses ICU to
 get binary-comparable sequences (collations). These collations may be
 different in different ICU versions.

 (...) this may lead to incompatibilities in the binary index
 representation (on disk) between databases created by Debian-packaged
 Firebird, and upstream.

 This can also lead to incompatibilities when the Firebird packages
 are re-built using different version of (...) ICU or when (...) ICU
 (...) is upgraded.

 One way to fix this (...) Another is to re-generate all indices that
 contain a text-based columns. (...) a script is provided (...) named
 reindex-db (...)

 (...) the best solution seems to not allow firebird as an external
 dependency. This is, compile always internally and using the bundled
 libicu provided by firebird. In any case, this problem may even
 appear just with FB upgrades (...)

I disagree. This seems like a recipe for disaster to me. Our
downstream distributors *will*, it at all possible, force system
firebird and system ICU (e.g. Debian has a very strong policy on that
kind of things, embedded copies of libraries are actively chased down
and killed), and then we'll have the incompatibility problems anyway,
but in an uncontrolled way. I'd much rather we face the problem and
manage it. Especially since we will have the problem one day anyway,
for upgrades of firebird versions and what not.

How about this:

When opening an embedded-firebird .odb file, detect whether the
indices are built with an incompatible / other ICU version, and then
just automatically (silently?) rebuild them. I hope we can detect the
situation automatically.

In this way, we don't only get compatibility between e.g. a
Debian-built LibreOffice and a TDF-built LibreOffice, but *also*
seamless compatibility between putative TDF-built LibreOffice 5.3 with
FireBird 3.18 and ICU 4.12.3 and TDF-built LibreOffice 4.7 with
FireBird 3.12 and ICU 4.10.1.

Actually, this kind of automatic conversion could be upstreamed in
FireBird itself, in one of these ways:

 - Do it automatically (in background?) / eagerly when opening a
   database (on all tables).

 - Do it automatically (in background?) / eagerly when opening a
   table (on all indices of that table).

 - Do it automatically / lazily when a query tries to use the index.
   What happens while the index is being rebuilt?

   * The query blocks?

   * The query falls back to another index / full table scan?

For doing it in LibreOffice (as opposed to in FireBird): Debian's
reindex-db script basically runs
 ALTER INDEX $i ACTIVE
up to twice on each possibly affected index.

1) Why twice? Why not once, or thrice?

2) Could we just blindly do that, even if the ICU version did not
   change? The command looks like it would be a quick no-op if the
   index is already OK (that is, not forcing a rebuild of the index).

3) I don't see that command documented at
   
http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-ddl-index.html
   Is it 3.0-only? So what should we do in 2.5? "DROP INDEX" and then
   "ADD INDEX"? DROP INDEX is also not documented, but OTOH, "DROP
   TABLE" seems to also not be documented as such (only mentioned in
   passing), and I would find it hard to believe that "DROP INDEX" is
   not supported :)

   Ah no, this documents only what's new since InterBase 6, and
   http://www.ibphoenix.com/files/60sqlref.html#RSf39848 documents
   ALTER INDEX... So OK for FireBird 2.5.

-- 
Lionel