Metadata and Upgrade

See for a description of Derby's versioning scheme and upgrade mechanisms.


Some of the methods in java.sql.DatabaseMetaData return a ResultSet value. In Derby, these methods execute a stored prepared statement (SPS) which performs a query and returns a ResultSet. An SPS is a compiled query that is stored in SYS.SYSSTATEMENTS. The stored prepared statements for metadata queries are created when the database is created. At that point, the queries are read from two property files, java/engine/org/apache/derby/impl/jdbc/ and java/engine/org/apache/derby/impl/sql/catalog/, compiled and stored in the database.

When moving to another version of Derby, the metadata queries might have changed due to bug fixes or new features. If one performs a metadata query against a database created with an older version, the stored prepared statements from the old version might give incorrect results. Therefore, Derby needs to have a way to make the metadata calls use the new queries instead of the old ones.

Soft Upgrade

Starting with Derby 10.2, EmbedDatabaseMetaData will read the queries directly from (or instead of using an SPS in soft upgrade mode. This approach has a small performance penalty, but it ensures that the new query is available even though the database is not upgraded.

There is however one case which needs extra care in soft upgrade mode. If a new/modified query depends on new system tables, new system table columns or new system procedures, it will not work, since new tables and procedures are not created until a full upgrade is performed. This case has been solved by keeping two versions of the queries in; one which is supposed to be used in soft upgrade mode, and one for full upgrade.

For instance, the queries used by getColumnPrivileges() and getTablePrivileges() were updated in Derby 10.2 in such a way that they did not work in soft upgrade mode. To prevent the queries from failing in soft upgrade mode, two extra queries were added to; getColumnPrivileges_10_1 and getTablePrivileges_10_1. The extra queries return empty result sets and do not depend on any new features in Derby 10.2. Additionally, EmbedDatabaseMetaData.getQueryFromDescription() had to be equipped with some logic to decide which version of the queries to choose.

Full Upgrade

Starting with Derby 10.2, all stored prepared statements are dropped and recreated when performing a full upgrade. This way, all changes made to the metadata queries will be stored in the database and visible to the users.

Maintenance and Point Releases

Currently, there is no mechanism for handling changes in metadata queries between maintenance releases or point releases (third and fourth digit in the version number). Since new features require a new minor version, metadata changes between maintenance/point releases must be bug fixes only.

There are essentially three ways of handling metadata changes between maintenance/point releases:

  1. By dropping and recreating the stored prepared statements on every version change.
  2. By making Derby run in soft upgrade mode when the version changes.
  3. By adding special upgrade code when the need arises.

Option 1 and 2 will make the queries work as they should when moving to a newer version, and they will revert to the old behaviour when going back to the older version. The down side of these solutions is performance. Option 1 requires recreation and recompilation of all stored statements, even when they have not changed. Similarly, option 2 forces soft upgrade for all version changes, even when no changes have been made to the metadata queries.

Adding special upgrade code when the need arises solves the performance problems for the normal case where no metadata changes have been made. There are many ways to do this, for instance

Note that all of these solution, except those enabling soft upgrade mode, will cause problems for read-only databases. For read-only databases, one either has to enable soft upgrade mode or continue using the old metadata queries.

Client/Server Mixed Version Metadata Negotiation

With new features DatabaseMetaData calls will potentially need to be "negotiated down" if the client is older and cannot support the new feature. There is no infrastrure in place for this at this time and will have to be implemented as the need arises.

MetadataUpgrade (last edited 2009-09-20 22:11:09 by localhost)