Pre-upgrade scripts

You may not need pre-upgrade scripts in your release at all. But every decision to create one must always be made during development, at the time when you are conscious of making changes that could affect data upgrades on target machines. It is bad practice to leave these decisions until release time.

In practice, the most common use case for a pre-upgrade script is that you want to preserve application data before some structural change is made that causes that data to be lost. A classic example is when you move a column from a subtype table to a supertype table. USoft will drop the column from the subtype, and add it to the supertype, but it will not automatically understand that the application data in the old subtype column must migrate to the supertype table. One way of dealing with this is stashing: make a temporary copy of the data, then change the data structure, then read the data from the copy into their new location (in the example, the supertype column).

Typically, with stashing, the data preserved by your pre-upgrade script is picked up later by a post-upgrade script that is part of the same release. A good way of signposting that 2 scripts are related in this way is to give them identical subject Descriptions, for example, "Addresses" or "Employees".

To create a pre-upgrade script, add a new entry in the Current Upgrade Scripts window and :

•Set Processing Order = Pre-upgrade.

•Set a Seqno (sequence number). The sequence number determines in what order pre-upgrade scripts must be run between them.

•Set the script's Processing Mode.

Next, in the SQL Statements box, write the SQL statement(s) that you want your pre-upgrade script to perform. Again, set Seqnos (sequence numbers) to decide in what order these statements are to be executed.

For a step-by-step instruction, go to " Writing an upgrade script ".

Before you can release a pre-upgrade script, you must have it checked. Do this by pressing the Check button. If the check is successful, the script is set to Checked = Yes and you are ready to release it.

The script checker performs three important tasks:

•The SQL statement is checked for syntax correctness and correctness against the data model (table names, column names, relationship names).

•Path variables and path placeholders (if any) are expanded to absolute and machine-dependent paths.

•For each item of added XSL, a call to USXSL.APPLY2FILE is added so that the XSL will be applied when the released script is run on a target machine.

Delivery Manager will attempt to determine whether a script must be re-checked. For example, if you change a SQL Statement, the script will be set to Checked = No. However, because the data model is in a remote database account and not in Delivery Manager, it is not possible to detect automatically all events that could potentially invalidate a script. For this reason, the "Release scripts" action performs a last-minute re-check of all the scripts before they are released to the file system.

You can release pre-upgrade scripts only by running a Task that executes the "Release scripts" action. Releasing a script consists of compiling the actual script file with file extension .SQL, and placing it in the \scripts subfolder (the "scripts" section) of the current release folder.

The Release Scripts action is called for all the current upgrade scripts of a given application. You can only release the scripts of an application if there are no remaining unchecked scripts associated with that application.