====== 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.

===== Basic structure of an upgrade script =====

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.


===== Potential issues =====

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:

==== Failure to run ====

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.

==== Data loss ====

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''

==== Unexpected changes/loss in functionality ====

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.

==== Performance issues ====

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!

===== Testing database upgrade scripts =====

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 [[https://pgtap.org/|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.