Database upgrade scripts

Any time you make a change to the database schema, stored procedures, or seed data, you should make it in two places:

• In the seed data in Open-ILS/src/sql/Pg – this will be used by new installations of Evergreen
• In the database upgrade scripts in Open-ILS/src/sql/Pg/upgrade – this will be used by existing Evergreen installations who upgrade to a version of Evergreen that contains your development

This page discusses the upgrade scripts.

Upgrade scripts have a few basic parts:

• A transaction that wraps the entire script. The first line of your upgrade script should be BEGIN;, and the last line should be COMMIT;
• A check to make sure the upgrade script should be applied (i.e. it hasn't been deprecated or superseded by another script). When you submit your code, this check should look like – SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
  • When your code is accepted and brought into main, the 'XXXX' will be replaced with a sequential id that represents the database script.
• The actual changes that are needed to bring an existing system to the desired state.

Upgrade scripts can have a few different potential issues, in the following categories:

• They fail to run
• They cause data loss
• They cause unexpected changes/losses in functionality
• They cause performance issues

Let's take a look at each of these in turn:

Since the upgrade script is wrapped in a transaction, if the script encounters any errors along the way, it will rollback all changes. Keep in mind that an upgrade script may run without error in a simple development environment, but may fail in a complex production database with years of unexpected data.

For example, an upgrade script that deletes rows from actor.org_unit_setting and config.org_unit_setting_type may succeed in a development environment where nobody has ever changed the values of those settings in the UI. However, a production system may also have entries in config.org_unit_setting_type_log, meaning that the attempted DELETE violates a foreign key constraint, and the upgrade script would fail.

Be sure to check any potentially destructive actions (UPDATE, DELETE, TRUNCATE, ALTER, modifications to stored procedures that make changes in the database, etc.) to make sure that they don't cause data loss, including local modifications.

For example, the following line in an upgrade script will overwrite any custom description that a local Evergreen installation has provided for the lib.timezone library setting:

UPDATE config.org_unit_setting_type SET description = 'My new description' WHERE name='lib.timezone'; – BAD example, do not use it

The following example is better, since it only will affect sites that have not customized the lib.timezone setting:

UPDATE config.org_unit_setting_type SET description = 'My new description' WHERE name='lib.timezone' AND description = 'The old description found in the seed data'; – Better example, although still missing i18n

A common example here is when an upgrade script creates a new permission for an existing action, but does not assign the permission to the relevant permission groups. This leads to situations in which staff members are able to do a particular task before the upgrade script is run, but are blocked from doing it after the script is run.

Most upgrade scripts run very quickly, even in large systems. But certain schema changes (adding new constraints to existing data in large tables, performing a time-consuming operation like adding an index to a large table while holding a lock, etc.) can take hours, especially for old Postgres versions. For these, be sure that you try it out on production data, and ask for help if you need it – the community will be happy to help you troubleshoot performance issues, especially since it means a more pleasant upgrade experience for all!

When your development makes changes to the database, you should always test them! In particular, you should:

• Try rebuilding the database from the seed data including your changes (this is the eg_db_config script)
• Try upgrading an existing database using your upgrade script (You can run psql -f /path/to/upgrade-script.sql)

Another good practice is to write pgtap tests to confirm that your changes leave the database in the state you expect. These tests can be found in Open-ILS/src/sql/Pg/t and Open-ILS/src/sql/Pg/live_t.