Hi Ajax,
Here are a couple points to consider:
0) Derby supports two kinds of upgrade: soft and hard. Soft-upgraded
databases can be downgraded to previous releases of Derby (but not to a
release earlier than the original version of the database).
Hard-upgraded databases can not be downgraded. Please see the section on
upgrades in the Derby Developer's Guide:
http://db.apache.org/derby/docs/10.15/devguide/cdevupgrades.html
1) Consider using operating system commands to simply copy your old
database to a new location and then hard-upgrade the new copy in place.
You will need to quiesce the source database first, that is, gracefully
shut it down. The Derby database format has not changed since the code
was open-sourced in the early noughties. It is a platform-agnostic
format, so you can simply tar up the whole directory tree holding the
old database and then un-tar it in your target installation--even if the
target machine has a different architecture. Hard-upgrade is something
which we test whenever we vet a new Derby release. The test involves
creating a database from each of the releases which we have produced
over the past fifteen years and then hard-upgrading those databases to
the latest version, that is, to the release which we are vetting. Very
few bugs have been logged against hard-upgrade.
2) If you want to make this as difficult as MySQL, you can, of course,
dump the schema-creating DDL from the old database and then replay it in
a fresh database. Use the dblook tool for this purpose. It is somewhat
analogous to the MySQL Export tool mentioned below. Please see its
documentation in the Tools Guide:
http://db.apache.org/derby/docs/10.15/tools/ctoolsdblook.html Once you
have a schema shell, you will need to populate the tables with data from
the original database. There are two ways to do this:
i) Use the SYSCS_UTIL.SYSCS_EXPORT_TABLE system procedure to dump data
from the original database into flat files and then use the
SYSCS_UTIL.SYSCS_IMPORT_TABLE system procedure to load those files into
the new target database. Please see the sections on these procedures in
the Reference Manual:
http://db.apache.org/derby/docs/10.15/ref/rrefexportproc.html and
http://db.apache.org/derby/docs/10.15/ref/rrefimportproc.html
ii) You can avoid indirecting through flat files and, instead, transfer
your data quickly and directly by using the foreignViews tool documented
in the Tools Guide. Please see
http://db.apache.org/derby/docs/10.15/tools/rtoolsoptforeignviews.html
In case, you're interested, this tool can also be used to quickly
migrate data out of non-Derby databases.
I would rank these approaches in ascending complexity and time needed,
as follows:
1
2ii
2i
I would avoid over-thinking this problem. Derby does not suffer from the
kinds of version-specific format incompatibilities which plague other
databases. Option 1 is your best choice.
Please feel free to ask more questions.
Hope this helps,
-Rick
On 6/28/20 10:17 AM, recei...@gowdygroup.net wrote:
It would be nice to have a way to convert a database to a form that is
independent of the software used to support the database. For example,
when using phpMyAdmin on a MySQL database there is an option referred
to as "Export". This option causes the creation of a text file that
contains all of the SQL statements required to recreate the database
at least in MySQL or Maria DB. In theory, such a file should also be
useful for migrating the same database to other kinds of relational
databases that support SQL.
At least in the case of MySQL/MariaDB this capability provides an easy
way to transfer databases from one server to another which may be
desirable when making transition to a new release. If nothing else it
preserves the ability to restore to current release. Given how many
versions of Derby presently exist it seems like this should be
especially important.
There is a present need to do such a migration simply to upgrade to a
new version of Derby. In the case where this migration is from a very
old version of Derby the idea of a version independent file format
would seem to be very desirable. Is that possible with Derby? If so,
some reference to appropriate technique would be appreciated. If NOT,
is there a prescribed method for doing such that is safe and effective?
Ajax ...