Database upgrade script

A quick answer to this question is: yes, registering a database upgrade script for each release is usually a good idea.

People ask this question for different reasons.

Experienced USoft consultants often wonder why Delivery Manager should produce a database upgrade script when USoft also allows you to run Create Tables directly (from the context menu of a USoft Binder). When you run Create Tables directly, a comparison is made between the "new" database structure and whatever is found locally, and then database changes are immediately made on the basis of that comparison. That usually works fine, but has major drawbacks:

•The changes applied are not traceable.

•If, for some reason, the existing local database state was different (older) than expected, unexpected errors could occur.

•It may not be practical or acceptable to run USoft Binder on a specific production machine.

•There is no structured way to combine the database changes with (functionally closely related) appdata and authorisation data changes as defined by pre-upgrade scripts and post-upgrade scripts. There is no structured way to to register that these related upgrade actions have taken place, or are planned to take place.

Others may feel that it is cumbersome to register a database upgrade script even for very small releases, where nothing changes in the database.

If you have a policy of registering larger release versions, it is likely that each version will have at least some database change. In this situation it is practical, and also best practice, to register a database upgrade script for each release. Occasionally, you will have a release that does not change the database. In that case, the database upgrade script will simply remain empty.

If you have a policy of registering many small release versions (patch releases) that often have very few deliverables compared to the previous release, then you may want to skip registering a database upgrade script for such minor releases. In this situation, some extra manual action is required when the next major release comes along that does require database changes and therefore a database upgrade script. This is because Delivery Manager computes a database upgrade script by comparing the current database state to its most recent previous state. It is only able to compute a database upgrade script if the most recent previous release folder contains an "application.USD.xml" file in its "metadata" section that describes the situation before the database changes. Otherwise, the "Release scripts" action will raise the error:

Release is non-initial but XML repository file for previous version is not found at: previous-release-location\application.USD.xml

where previous-release-location is the release folder where "Release scripts" expected to find the description of the most recent database state before it was changed.

In general terms, the database upgrade script contains SQL statements that are understood by your RDBMS and that change the RDBMS tables, indexes and (in the case of Oracle RDBMS) sequences to create the data structures associated with your new application release. A database upgrade script may contain DDL statements such as CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX.

To find out what is in the database upgrade script, inspect it. Do this by releasing the database upgrade script (see next section below), then finding the "application.200.upg.rdbms.sql" script in the \scripts subfolder of the current release folder, and then opening it in a text editor.

The database script attempts to preserve existing data. One strategy it uses for this is (roughly) the following sequence of statements, where existing data is temporarily preserved in an OLD_... table:

RENAME mytable TO OLD_mytable;

INSERT into OLD_mytable(...) 

SELECT * FROM mytable;

CREATE TABLE mytable (...);

INSERT INTO mytable (...)

SELECT * from OLD_mytable;

Another strategy uses ALTER TABLE rather than CREATE TABLE:

ALTER TABLE mytable ADD COLUMN ...

With ALTER TABLE, existing data is preserved in the table itself while it undergoes change.

In Delivery Manager, you can influence how database change is implemented in the database upgrade script in 2 ways.

CAUTION: These 2 ways of interfering with the database upgrade script are NOT particularly recommended, except if you have special reasons:

•You can determine that a table must be ignored, ie, not included in the database upgrade script even if it has changed. Do this on a per-table basis by finding the table in the Application Tables window (choose Define, Application Tables) and setting Skip = Yes for the table.

•You can suggest that a CREATE TABLE statement is used rather than an ALTER TABLE statement. Do this on a per-table basis by finding the table in the Application Tables window (choose Define, Application Tables from the menu) and setting Upgrade Hint = Create for the table.

If you do not find the table in the Application Tables list, run the " Populate tables list " preparatory action.

You can create and release the database upgrade script by running the "Release scripts" action with "Include physical tables upgrade script" = Yes.

Except when your current release is an initial release, a prerequisite is that the "metadata" section of the previous release folder contains a file (called "application.USD.xml") that describes the previous database state. See the previous section "Do you really need a database upgrade script?" for details.