Metadata and Upgrade

See http://db.apache.org/derby/papers/versionupgrade.html for a description of Derby's versioning scheme and upgrade mechanisms.

Background

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/metadata.properties and java/engine/org/apache/derby/impl/sql/catalog/metadata_net.properties, 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 metadata.properties (or metadata_net.properties) 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 metadata.properties; 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 metadata.properties; 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

  • Dropping and recreating SPSs when moving to a version where the metadata queries have changed. This ensures that the new queries are used when moving to a newer version. It does however not ensure that the old query is used when moving back to the old version. This might actually not be a bad thing, since metadata changes between maintenance releases most likely are bug fixes. The query must be backwards compatible, otherwise moving back to the old version is impossible.
  • Enabling soft upgrade mode when moving to a version where the metadata queries have changed. This solves the problem of the query not being reverted when reverting the version. However, the performance is lower.
  • Creating new metadata queries with new names. Instead of modifying the old query, one can create a new one with a new name (for instance by appending a version number). When moving from an older version, the new query has to be created, but the old query is kept in the database. This way, the old query is still there when moving back to the old version.

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.

  • No labels