Create-tables script

A create-tables script is a SQL script that is run directly against the RDBMS and that will make all the necessary database changes associated with your release. It will create, drop or alter database tables, database indexes, and (in the case of the Oracle and Sql Server RDBMS) database sequences in order to achieve the database state described by the new version of your application.

A create-tables script produced by Delivery Manager has the file name "application.200.upg.rdbms.sql". It is placed in the \scripts subfolder (the scripts section) of the release folder.

A create-tables script is RDBMS-specific. Delivery Manager is able to generate Oracle and SQL Server syntax.

On a per-table basis, you can indicate that you want a create-tables script to skip the table or to generate a CREATE TABLE (as opposed to an ALTER TABLE) statement. Do this at application table level in Delivery Manager.

Otherwise, you could tailor the create-tables script that Delivery Manager generates to your special needs. This is not recommended unless you have special reasons.

A quick answer to this question is: yes, registering a create-tables 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 create-tables 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 create-tables 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 create-tables script for each release. Occasionally, you will have a release that does not change the database. In that case, the create-tables 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 create-tables 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 create-tables script. This is because Delivery Manager computes a create-tables script by comparing the current database state to its most recent previous state. It is only able to compute a create-tables 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 create-tables script contains SQL statements that are understood by your RDBMS and that change the RDBMS tables, indexes and (in the case of Oracle and Sql Server RDBMS) sequences to create the data structures associated with your new application release. A create-tables script may contain DDL statements such as CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX.

To find out what is in the create-tables script, inspect it. Do this by releasing the create-tables 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.

USoft automatically decides, on a per-table basis, whether a strategy with ALTER TABLE or with CREATE TABLE statements is applied.

•You can determine that a table is skipped, ie, not included in the create-tables 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 determine 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.