Database Update Wizard

Writing a DUW Script: Supported Operations

Business partners or Archibus administrators may want to write scripts to add tables, fields, indexes, and data to the database.

Scripts can invoke the Archibus features that alter tables and re-create tables, and can also refresh the Archibus data dictionary and workflow rules registry. You can create scripts to import data and features in a form that will run identically on Oracle, Microsoft SQL Server and Sybase.

DUW scripts are also handy for documenting, testing, and staging your database personalizations in a repeatable way.

The following operations are supported in script files:

Note: All commands must end with semi-colons(;) in order to be interpreted as commands.

Commented lines

Any line that starts with the following is considered to be a comment and will be ignored.

Type	Example
PREFIX	-- Your comment here
PREFIX	// Your comment here
PREFIX	REM Your comment here
WRAPPED	/* Your comment here */

Path aliases

Alias	Folder
%publicDataTransferDirectory%	\projects\users\public\dt
%webAppDirectory%	\webapps/archibus
not specified	\webapps/archibus

Import CSV or Excel files

Action

Example

Details

import CSV file

${import.file('file.csv'), 'update'};

${import.file('file.csv'), 'insert'};

${import.file('file.csv')};

${import.file('file.csv'), importMode}

importMode can have the following values:

update - updates only records that match those in the import file. New records are skipped.
insert - inserts only records that are new in the import file. The update skips existing records. If you do not specify importMode, then both insert and update are applied.

import Excel file

${import.file('file.xsl'), 'update'};

${import.file('file.xsl'), 'insert'};

${import.file('file.xsl')};

Same as above.

In the "script file", you can use %webAppDirectory% and %publicDataTransferDirectory% macros for the files to be imported. If you do not mention any path, then the Database Upgrade Wizard automatically appends the %webAppDirectory% macro to the beginning of the file path name by default.

SQL and DUW scripts

File type	Example	Comments
.sql	`${run.script('file.sql')};`	Executes the indicated .sql file only
.duw	`${run.script('file.duw')};`	Executes the indicated .duw file, including nested .duw files recursively

Refresh Core objects

Command	Description
`${refresh.data.dictionary}`	This reloads the Data Dictionary after a change is made in the Data Dictionary. The Schema Change Wizard will do this automatically, but you can also call this explicitly for changes that do not affect schema database (examples: afm_flds.afm_type, afm_flds.num_format, afm_flds.dtring_format).
`${reload.workflow.rules}`	Refresh and re-run the workflow rules. In case you change or add new workflow rule, this command reloads workflow rules from the afm_wf_rules table without restarting the webc server.
`${reload.activity.parameters}`	Refresh activity parameters

Set database context

Context	Description
`${db.oracle};`	The context is set to ORACLE. If your project DB is ORACLE, then all the commands that follow will be executed, otherwise skipped.
`${db.mssql};`	The context is set to SQL Server. If your project DB is SQL Server, then all the commands that follow will be executed, otherwise skipped.
`${db.sybase};`	The context is set to SYBASE. If your project DB is SYBASE, then all the commands that follow will be executed, otherwise skipped.
`${db.all};`	The context is reset. All commands that follows will be executed. If you have used a specific context above, do not forget to call this command if the commands that follows are applicable for all DB types.

Archibus SQL macros and binding expressions

Scripted data transfers using the Database Update Wizard can require use of SQL statements. Take, for example, insertions of records having foreign key relationships to other new records with auto-numbered keys. While generic SQL will often suffice, some use cases require statements involving database-specific syntax, such as inserts of date or time content that process without trouble in Sybase but require explicit formatting instructions in Oracle using TO_DATE(...).

You can run an SQL statement containing Archibus macros and SQL binding expressions that are currently supported for axvw datasources:

${sql('SELECT 'A' ${sql.concat} 'B' ${sql.as} name FROM bl')};

Conditional statements

For the most part, DUW scripts isolate the database update from database-specific dependencies through actions, such as "alter.table". However, for a select set of SQL commands – such as those used for GRANTing permissions or create indexes – database-specific options are required. To support this, the DUW scripts support conditional statements by using comments in keywords.

--Server-independent SQL statement DELETE FROM bl WHERE bl_id='mytest'; INSERT INTO bl(bl_id,name) values('mybldg','test'); COMMIT; ${db.oracle}; GRANT REFERENCES, SELECT, INSERT, UPDATE, DELETE ON password_token TO afm_secure; GRANT REFERENCES, SELECT, INSERT, UPDATE, DELETE ON afm.password_token TO afm_secure; ${db.sybase}; GRANT CONNECT TO NewUserForRole IDENTIFIED BY newpswd; GRANT MEMBERSHIP IN GROUP AFM_USER_GROUP TO NewUserForRole ; ${db.mssql}; // Grant access to the AFM user EXEC sp_grantdbaccess 'afm', 'afm'; // The security account relates to groups,roles and process assignments. GRANT REFERENCES, SELECT ON AFM.AFM_GROUPS TO afm_secure; GRANT REFERENCES, SELECT ON AFM.AFM_ROLES TO afm_secure; ${db.all}; --COMMENT ${import.file('D:/test.csv')}; ${refresh.data.dictionary}; ${alter.table('zone',true)}; ${recreate.table('ac',false)}; ${refresh.data.dictionary}; ${reload.workflow.rules};'

You can change the context using the following macro commands:

Macro command	Explanation
{db.oracle}	Set the context to Oracle. All subsequent commands will be executed for Oracle only.
{db.mssql}	Set the context to SQL Server. All subsequent commands will be executed for SQL Server only.
{db.sybase}	Set the context to Sybase. All subsequent commands will be executed for Sybase only.
{db.all}	Clear all previous contexts. All subsequent commands will be executed on any DB type

Update schema

Action	Example	Comments
Alter table	`${alter.table('table', true)};`	Both parameters are mandatory. The commands alter the table using alter command. The table name can also be a LIKE expression (eg: 'afm%'). The second parameter indicates if the foreign keys are re-created from/to tables specified. Set the second parameter to `true` if you: modified a foreign key field (size, data type, allow null, default value) add a new foreign key field removed a foreign key field added a new table dropped a table which had foreign key fields This command will not drop any field. See Re-create table, below.
Re-create table	`${recreate.table('table', true)};`	Same as above This command drops any fields that exists in the physical database but not in the Data Dictionary.
Re-create foreign key	`${recreate.fk}`	Creates missing foreign keys
Re-create all foreign keys	`${alter.table('%', true)};`	Alter all tables in the database and re-create all foreign keys for all tables in the database
Use Archibus macros	`${sql('UPDATE afm_scmpref SET date_lang_de = ${sql.currentDate},date_lang_fr = ${sql.currentDate},date_lang_es = ${sql.currentDate},date_lang_it = ${sql.currentDate},date_lang_nl = ${sql.currentDate}')};`	Evaluates the expressions and executes the SQL
Run plain SQL commands	`UPDATE bl SET name = 'new name';`	Anything else that is not a comment or doesn't start with ${ is interpreted as SQL command and executed

Notes: Alter Table vs. Re-create Table

Details below explain differences between ${recreate.table()} and ${alter.table()} commands. In most cases, use ${alter.table()}. For special cases noted below, use ${recreate.table()}.

${alter.table()} operates as follows:

The function checks for missing fields, or differences in data type, size, allow null, default values, and primary key.
If the function detects differences, ${alter.table()} synchronizes the dictionaries. It does not delete any fields.
If the function detects differences in primary keys, it calls ${recreate.table()}.

Always use this command when you:

Add new fields to a table.
Modify fields.
Add, remove, or modify foreign keys. These actions require second parameter = true.
Re-create foreign keys. This action requires second parameter = true.

${recreate.table()} command forces table re-creation, whether or not the differences noted for alter table exist. Macro logic operates as follows:

Create a temporary table.
Copy meta data into temporary table.
Drop the original table.
Rename the temporary table to the original one.
Create primary keys.
Create foreign keys, if second parameter=true.