Storing/extracting embedded db within .odb

[Lionel Elie Mamane]

On Wed, Jul 10, 2013 at 08:47:49AM +0100, Andrzej J. R. Hunt wrote:
> On Tue, 2013-07-09 at 15:59 +0200, Lionel Elie Mamane wrote:
>> On Mon, Jul 08, 2013 at 03:19:49PM +0100, Andrzej J. R. Hunt wrote:

>>> W.r.t. to the location of the extracted firebird db: would an
>>> appropriate naming scheme be to have e.g. ~foo.odb.fdb# for a
>>> foo.odb (in the same directory) similarly to the lock files that
>>> are created? (This could also allow for better recovery should we
>>> end up with similar corruption as is apparently happening with
>>> HSQLDB if there is a complete separate db.)

>> It is a temporary file. I'd stick it in $TMPDIR (or the platform
>> equivalent). Surely LibreOffice already has a platform abstraction
>> utility function to find where to put temporary files.

> That was my original plan, however that would require writing the
> whole db back to the .odb every time there is a change.

Or every time the user presses the "save" button for the odb... OO.org
moved away from requiring "save" for embedded database in the past,
but frankly to me it seems to bring more problems than it is worth.

> If there were a file in the same directory instead we could
> potentially only have to extract the db at startup and write it back
> into the .odb at shutdown (which means we can ensure that the .fdb
> is in a good state by shutting down FB first) -- if we crash (lose
> power/etc.) in the meantime the local file could then be used to
> restore the database (alternatively the location of the temp file
> could be stored, but that would be more complicated to do I think).

You make a good point. I wouldn't go into the "alternatively", because
temp files are typically deleted on computer startup / shutdown and /
or on user logout / user login, so thinking of crash / power loss
recovery, temp dir is not such a good idea.

OTOH, I'd like to see an analysis (and plan of implementation) of how
this way of crash recovery would interact with the recovery of the
autosave copy of .odb; which one will override the other? Will we
"intelligently" take the newest data, or if one of them is newer but
corrupted, then take the older clean copy? The plan of implementation
can be done at a later stage, not necessarily this week, obviously :)

I'm a bit uncomfortable that the database file is committed to the odb
only when closing the file. I'd definitely do it at least at each odb
save operation (including the background auto-save, but then commit in
the auto-save copy, *not* in the main odb file). With some luck,
Firebird has a way of making that safe without closing the connection
(which would not be acceptable); for some databases all one has to do
is take a write lock on the whole database / all tables, flush
buffers, copy the file(s) and release the write lock. That's how
mysqlhotcopy does database backups:

 LOCK TABLES (...);
 FLUSH TABLES (...);
 FLUSH LOGS;  -- optionally
 -- copy files
 UNLOCK TABLES;

> On Tue, 2013-07-09 at 16:14 +0200, Lionel Elie Mamane wrote:
>> On Tue, Jul 09, 2013 at 11:25:58AM +0100, Andrzej J. R. Hunt wrote:

>>> store the db itself as database/database.fdb. It shouldn't be too
>>> hard to have the same driver load an external fdb file and
>>> configure this using the properties file (some users are
>>> requesting this)

>> Indeed, it would be rather a pity to have this driver not be able
>> to access an external database. But *please* don't configure that
>> with any internal properties file, but configure it through the
>> sdbc:// URL! This allows also to make a SDBC connection
>> "disconnected" from any .odb file, and this is how *all* our
>> drivers do it.

> Yes, I'll do that then . Is it worth emulating the hsqldb
> driver structure whereby the embedded driver only deals with the .odb
> specifics, and then delegates to the firebird driver to actually connect
> to firebird itself?

Whatever is most convenient / safe / clean / ... to implement. The
firebird driver could also directly handle the different sdbc URLs:

 sdbc:embedded:firebird
 sdbc:firebird:XXXXXXXXXXXXXXXXX

and set an internal flag "m_bIsEmbedded" based on that, and then do
different stuff in the few places where different stuff is needed in
the two cases. My guess would be that this would be best, but since
you are writing the code, you have much better information than me on
that :-) As far as I am concerned, you can make that decision.

-- 
Lionel