[Refdb-devel] upgrading the main database

[Markus Hoenicka]

Hi,

the current SVN version (revision 167) implements a first stab at the
main database upgrading mechanism required for smooth upgrades of
packages and ports. It is currently only implemented for
SQLite/SQLite3, but the implementation for MySQL and PostgreSQL is
mainly monkey business. I'll try to add that code during the next
week.

In addition to the standalone and daemonized ways of running refdbd
there is an additional mode now. You can use it to either check the
database connection or to upgrade an existing main database. In both
cases refdbd will evaluate <prefix>/etc/refdb/refdbdrc as usual, so
you may get away using the existing connection parameters.

To check the connection to the database engine and to read out the
version of the main database, run something like this:

refdbd -c -D sqlite3 -e 0 -l 7 -i /path/to/db 2>&1|grep "version:"

refdbd will display some messages and exit. Similarly, to upgrade an
existing main database, run something like this:

refdbd -a -D sqlite3 -e 0 -l 7 -i /path/to/db

David, if you use the latter in your Debian package postinstall
script, you may have to explicitly name the database engine and the
default database location as shown here because the user may have
changed these settings in refdbdrc after the package was installed. Of
course you'd run it without dumping the debug log messages to the
screen.

In order to make the upgrades possible in future releases I've
versioned the refdb dump files. That is, refdb.dump.sqlite(.in) was
renamed to refdb.2.dump.sqlite(.in) and so on. Future releases will
probably have to provide several dump files of older database versions
in order to allow upgrades from these older versions.

Please give the upgrade code a try. I use a 0.9.7 version to create
the database and to add a few styles. Then I try to upgrade it with
the current SVN version. Finally I read out the styles with
the SVN version. So far the tests looked ok.

regards,
Markus

[David Nebauer]

Hi Markus,

Post by Markus Hoenicka
Similarly, to upgrade an
refdbd -a -D sqlite3 -e 0 -l 7 -i /path/to/db
David, if you use the latter in your Debian package postinstall
script, you may have to explicitly name the database engine and the
default database location as shown here because the user may have
changed these settings in refdbdrc after the package was installed. Of
course you'd run it without dumping the debug log messages to the
screen.

Thanks for adding this feature with your customary speed. I'm afraid I
won't get around to building the new debs till later this week as I have
a few other things on my plate.

Regards,
David.

[David Nebauer]

Hi Markus,

Post by Markus Hoenicka
To check the connection to the database engine and to read out the
refdbd -c -D sqlite3 -e 0 -l 7 -i /path/to/db 2>&1|grep "version:"
refdbd will display some messages and exit. Similarly, to upgrade an
refdbd -a -D sqlite3 -e 0 -l 7 -i /path/to/db

The easiest thing to do from a packager's point of view would be to run
this command unconditionally as part of the postinstall process. The
reason is that it is difficult to tell the new db version immediately
after installation but before the daemon has been started. Can running
the upgrade after each server upgrade cause harm? I hope refdbd is
smart enough to know when an upgrade is unnecessary and can be skipped.

Post by Markus Hoenicka
David, if you use the latter in your Debian package postinstall
script, you may have to explicitly name the database engine and the
default database location as shown here because the user may have
changed these settings in refdbdrc after the package was installed. Of
course you'd run it without dumping the debug log messages to the
screen.

The postinstall script already checks /etc/refdb/refdbdrc for an
alternate dbpath. It is a simple matter to also check the database engine.

Post by Markus Hoenicka
In order to make the upgrades possible in future releases I've
versioned the refdb dump files. That is, refdb.dump.sqlite(.in) was
renamed to refdb.2.dump.sqlite(.in) and so on. Future releases will
probably have to provide several dump files of older database versions
in order to allow upgrades from these older versions.

The postinstall script checks for a main refdb database. If none is
found it automatically creates a main sqlite database using the sqlite
dump. Now that you have versioned the dump files it will be difficult
for the postinstall script to know which dump file to import. I could
include a loop in which it checks each refdb.[ver].dump.sqlite and
determines the largest 'ver'. It would be easier, however, for me (and,
I presume, other packagers) if the newest dump file had a predictable,
stable name. Could you call the newest version 'refdb.dump.sqlite' with
only older versions numbered? When the newest version is replaced it
would then be renamed to include the version number and the replacement
(now the newest) would receive the unversioned name. Alternately, and
to avoid any possible confusion, the newest dump might be called
'refdb.newest.dump.sqlite' or 'refdb.latest.dump.sqlite'.

Regards,
David.

[Markus Hoenicka]

Hi David,

Post by David Nebauer
The easiest thing to do from a packager's point of view would be to run
this command unconditionally as part of the postinstall process. The
reason is that it is difficult to tell the new db version immediately
after installation but before the daemon has been started. Can running
the upgrade after each server upgrade cause harm? I hope refdbd is
smart enough to know when an upgrade is unnecessary and can be skipped.

You can do just this. If refdbd -a notices that the installed main database is
already at the latest version, it does nothing.

Post by David Nebauer
The postinstall script checks for a main refdb database. If none is
found it automatically creates a main sqlite database using the sqlite
dump. Now that you have versioned the dump files it will be difficult
for the postinstall script to know which dump file to import. I could
include a loop in which it checks each refdb.[ver].dump.sqlite and
determines the largest 'ver'. It would be easier, however, for me (and,
I presume, other packagers) if the newest dump file had a predictable,
stable name. Could you call the newest version 'refdb.dump.sqlite' with
only older versions numbered? When the newest version is replaced it
would then be renamed to include the version number and the replacement
(now the newest) would receive the unversioned name. Alternately, and
to avoid any possible confusion, the newest dump might be called
'refdb.newest.dump.sqlite' or 'refdb.latest.dump.sqlite'.

I was thinking along these lines too. I think I'll reverse the current filenames
to refdb.dump.sqlite and version only the older ones.

regards,
Markus

[Markus Hoenicka]

Post by Markus Hoenicka

Post by David Nebauer
The postinstall script checks for a main refdb database. If none is
found it automatically creates a main sqlite database using the sqlite
dump. Now that you have versioned the dump files it will be difficult
for the postinstall script to know which dump file to import. I could
include a loop in which it checks each refdb.[ver].dump.sqlite and
determines the largest 'ver'. It would be easier, however, for me (and,
I presume, other packagers) if the newest dump file had a predictable,
stable name. Could you call the newest version 'refdb.dump.sqlite' with
only older versions numbered? When the newest version is replaced it
would then be renamed to include the version number and the replacement
(now the newest) would receive the unversioned name. Alternately, and
to avoid any possible confusion, the newest dump might be called
'refdb.newest.dump.sqlite' or 'refdb.latest.dump.sqlite'.

I was thinking along these lines too. I think I'll reverse the current filenames
to refdb.dump.sqlite and version only the older ones.

Now that I turned my brain on again (it must have been offline yesterday...),
renaming the files is not necessary. Your postinstall script should simply rely
on refdbd -a doing the right thing if it is run unconditionally. As mentioned
before, if the main database is at the current version, this command will do
nothing. If the main database is too old, it will be upgraded. If there is no
main database, it will be created. Therefore you no longer need to know which
dump file to use, and you no longer need a dependency on the sqlite binary
either. refdbd -a will do all this for you.

regards,
Markus

[David Nebauer]

Hi Markus,

Post by Markus Hoenicka
refdbd will display some messages and exit. Similarly, to upgrade an
refdbd -a -D sqlite3 -e 0 -l 7 -i /path/to/db

refdbd is now failing unless a username is supplied. Here is the
feedback if there is no username:

---------------------------------------------------------------------------------------
# refdbd -a -D sqlite -e 0 -l 7 -i /var/lib/refdb/db/refdb
incorrect username
# refdbctl start
incorrect username
/usr/bin/refdbctl start: bibliography tool application server could not
be started
/
#
---------------------------------------------------------------------------------------

refdbd has not previously required a username when running an sqlite
backend.

When a spurious username is supplied it attempts to run but still fails:

---------------------------------------------------------------------------------------
# refdbd -a -D sqlite -u spurious -e 0 -l 7 -i /var/lib/refdb/db/refdb
dbi_driver_dir went to:

dbi is up using default driver dir
Available libdbi database drivers:
sqlite3
mysql
pgsql
sqlite
Requested libdbi driver found
Database directory:
/var/lib/refdb/db/refdb
check main database version
localhost
david



sqlite
/var/lib/refdb/db/refdb
UTF-8
refdb
failed to connect to database server using database:
refdb
libdbi could not establish a connection
could not open main database
Can't access or create SQLite database
maintenance of main database failed
#
---------------------------------------------------------------------------------------

The database location is correct.

Regards,
David.

[Markus Hoenicka]

Hi David,

Post by David Nebauer
refdbd is now failing unless a username is supplied. Here is the

[...]

Post by David Nebauer
refdbd has not previously required a username when running an sqlite
backend.

refdbd only requires a username if you use the -a or -c switches. In this case
no client connection can provide the username and password required to connect
to the database server. However, you are right that this does not make much
sense in the case of SQLite. Actually refdbd attempts to guess a username using
the getlogin() function if none is provided. This is sufficient on all systems
that I've tested so far. I have no idea why this fails on your system. I've
changed refdbd to use a dummy username "root" if getlogin() fails. Please check
whether this fixes the problem.
[...]

Post by David Nebauer
The database location is correct.

Do you have write permissions in the database directory? I see no other reason
why this should fail. I've tested this on FreeBSD and Windows/Cygwin
successfully. I'll give it a try on the weekend on a Debian box to make sure.

regards,
Markus

[David Nebauer]

Hi Markus,

Post by Markus Hoenicka
refdbd only requires a username if you use the -a or -c switches. In this case
no client connection can provide the username and password required to connect
to the database server. However, you are right that this does not make much
sense in the case of SQLite. Actually refdbd attempts to guess a username using
the getlogin() function if none is provided. This is sufficient on all systems
that I've tested so far. I have no idea why this fails on your system. I've
changed refdbd to use a dummy username "root" if getlogin() fails. Please check
whether this fixes the problem.

Mystery solved. The problem was with my refdbd command. I was passing
to the '-i' parameter the full filepath to the database file itself
</var/lib/refdb/db/refdb> instead of the path to the database
*directory* </var/lib/refdb/db>. When I used the latter value the
command worked fine.

You may wish to reverse the recent change to refdbd.

Regards,
David.

[Markus Hoenicka]

Post by David Nebauer
You may wish to reverse the recent change to refdbd.

It doesn't hurt as it will kick in only in very obscure
circumstances, if at all. I'll just leave it as it is.

regards,
Markus