Database Upgrade Scripts¶

The purpose of this document is to describe the upgrade script standards used in IFS Cloud. This section is intended to offer guidance to developers when writing UPG, CDB and SQL files.

File Types Used During the Upgrade Process¶

UPG Files¶

UPG (Upgrade) files are used when upgrading from a previous application version (track) to a new version. These files are only executed during the upgrade process and are not used during a fresh installation. Depending on the current version of IFS Cloud it might be required to execute several upgrade files per component in order to upgrade to the latest CORE version. Eg. Apps 7.5 to IFS Cloud 21.2.

CDB Files¶

CDB (Change Database) files are created to fix DDL (Data Definition Language) and DML (Data Manipulation Language) issues in support versions. e.g., adding, removing and changing table columns and inserting or updating data into new or existing columns. CDB files are also used in internal development projects to keep the development environments up to date on a daily basis. All database changes done during development in the CDB files must be manually merged into the UPG files. CDB files are not included in a core release. The exception is when an Industry Extension is delivered on an existing track, then CDB files will be used to apply the database changes. If a new component is part of the Industry Extension release then CRE files will be generated from the storage files and used in the installation.

CDB files are executed when:

Applying a new Update delivery, either from the current core release or between Update deliveries. E.g. SU2 to SU3
Applying an Industry Extension

SQL Files¶

There are different types of SQL files and they are used for different purposes. Some examples of these are:

Pre-scripts: Pre-scripts are run during the preliminary stages of an upgrade.
Post-scripts: Post-script files are usually run after deploying all modules, compiling invalid objects and granting security.
Drop-scripts: The Drop script file is used for removing an entire component with all the database objects from the database.
Clear-scripts: Clear script files are used to remove unused columns and renamed tables that have been made obsolete.

These scripts are automatically executed by the IFS Installer in a deployment. Excepted are the clear-scripts and the part of the drop scripts that drop tables, that must be executed manually with confirmation from the customer since they will drop data.

INS Files¶

INS (Insert) files are used to insert basic data into logical units. Public interfaces in a logical unit should be used, since direct manipulation of data in tables is not allowed. The insert scripts should be re-deployable and only contain DML (Data Manipulation Language) statements.

DRP Files¶

DRP (Drop) files are used to drop an entire component. These files should only exist for components that have become obsolete, and they should be stored in the folder source\prifs\database of prifs component. The files should be named after the component to be dropped, e.g. busper.drp.

The drp files remove all business objects logic belonging to the component. Typipcal objects to drop are the packages, views, materialized views, triggers and sequences. The tables are not dropped, instead they are renamed to mark them as obsolete. To rename the table, method Database_SYS.Obsolete_Table should be used.

To the drop script, a corresponding manually deployed files should be created to drop the obsolete and renamed tables. These files are also stored in component prifs, but in folder manualdeploy\database\prifs. File name should be moduledr.sql, e.g. busberdr.sql.

Both these files are generated from IFS Developer Studio, and a context menu action on the component that is going to become obsolete. The option is named Generate Drop Script. The content of the files are based on the model and source code files of the component, but it is a good habit to validate the files as well before putting them under version control.