Skip to content

SQL Files

These are various kinds of scripts which the IFS Installer will deploy during the installation process. Manual steps in an installation should be avoided.

The use of the ACCEPT keyword is not allowed since it halts the execution waiting for user input. The use of the & character should only be used for defined values (either locally or in deploy.ini file of the component). Even in comments the IFS Installer will try to replace the value with a predefined value and if it doesn't exist the execution should halt waiting for user input. Since the scripts are executed unattended, executions may not halt awaiting user interaction.

Pre-scripts

An automatic pre-scripts is deployed before all the UPG files.

The name standard to use is PRE_<Component>_<Description>.sql and the file should be located in the source/<component>/database folder. A reference to the file should be added in the section [<component>PreUpgrade] in deploy.ini.

An automatic pre-script could not rely on existing packages in the database since they might be invalid after e.g. an Oracle database upgrade.

Post-scripts

The automatic post-scripts are deployed by the IFS Installer after all the UPG/CRE, CDB, API, APY, RDF and INS files for all components and after the recompilation of invalid objects and refreshing of caches. Therefore it is possible to include references to business logic.

The name standard to use is POST_<Component>_<Description>.sql and the file should be located in the source/<component>/database folder. A reference to the file should be added in the section [PostInstallationData] or [PostInstallationDataSeq] in deploy.ini if it contains DML (Data Manipulation Language) and to [PostInstallationObject] if it contains DDL (Data Definition Language).

An automatic post-script must be re-executable without corrupting data or generate errors.

Clear-scripts

The clear scripts are used to remove obsolete database object that contains data. Those object can't be removed automatically in the UPG file. Tables are renamed in the UPG file and then removed in this script. NOT NULL columns should be made nullable in the UPG and then removed in this script.

The name standard to use is <Component>cl.sql and the file should be located in the folder manualdeploy.

A clear script should have following sections in the header:

- File name
- Module
- Purpose
- Design History

In the top of the code section an EXIT should be placed with preceding information that this file must be edited before usage.

Each block should be preceded with a PROMPT describing which type of objects that are removed. In the end a PROMPT with information that removal of obsolete objects are done.

The public interfaces in the package Database_SYS should be used for the removal. The parameter show_info should be set to TRUE when calling the methods within this package. Information regarding the success or failure of the execution method will be displayed in the installation logs.

Drop-scripts

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.