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 or taking a service update. 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 9 to IFS Cloud 22.1.
Read more in section UPG Files.
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. There should only be one CDB file per component and service update.
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 are written into one and the same CDB file and it will at the end of the development project be converted into UPG for taking this version. Note that CDB files for corrections are allowed to have in the development project in order to not soil the forthcoming UPG file..
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
Read more in section CDB Files
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 obsolete tables and columns. These must be executed manually with confirmation from the customer since they will drop customer drop data (even though the data is not used anylonger).
Read more in section SQL Files.
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 are named after the component, e.g. busper.drp.
The drp files remove all business objects logic belonging to the component. Typical 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.
After the drop files have been generated from the IFS Developer Studio, the obsolete component can be removed from the version control system (Bit Bucket).