Database Update Wizard

DUW Scripts: Best Practices

Best Practices for DUW Scripts

Always use CSV/Excel files for data import, rather than SQL scripts. These files are easier to maintain. Since the Data Transfer generates error log files in \schema\per-site\datatransfer\<user>\<table_name>_errors.xls, it is also easier to debug.
Use SQL commands for complex queries that cannot be build with CSV/Excel.
Try to group the SQL commands in an .SQL file.
In the DUW file, do not mix .SQL and .DUW file calls unless some logic requires so.
There should not be any DDL or ${alter.table}, ${recreate.table} in sample-data scripts.
All DDL or ${alter.table}, or ${recreate.table} calls should be under schema scripts.
Try to synchronize with your team members and use a single ${alter.table} or ${recreate.table} per module. For example, you do want to end up altering the bl table more than one time. This will unnecessary increase the running time of the upgrade.

Note: Languages should be disabled in afm-config.xml if there is the possibility of outdated enumeration list translations as any step in the update scripts.

Checking a DUW Script

Before executing a script with the Database Update Wizard, you may wish to check it for syntax errors using the Validate option, which runs:${check.script(<file.duw>)};.

This command validates the indicated script (including nested scripts) for any possible errors that can occur during the real execution of the script. The command checks your scripts; it does not execute any commands.

From the Database Update Wizard, select that you want to update by running a script. On the Run Script screen, select the Validate Schema option. A checkmark next to this option indicates that the schema is valid.

The command detects the following errors:

Error or Warning	Description
Wrong path File not found	Detected in ${run.script} and ${import.file} if they reference a non-existent path or file.
Zero file size	Detected in ${run.script} and ${import.file} if they refer to an empty file.
Unsupported macro	Detects if you have a typo, or if you used a non-existent macro such as `${transfer.file}`.
Unsupported data transfer mode	Archibus supports only UPDATE/INSERT as a second parameter in `${import.file}` command.
Empty commands (warning)	Indicates if you have specified an empty SQL command.

Best Practices for Folders

Master Folder

The master folder is the path for the script files in \webcentral-war-root\src\main\webapp\projects\users\public\dt\xx.x.xxx where xx.x.xxx is defined as <Major version>.<Minor version>.<Patch number>

Each master folder should contain three files:

update-schema.duw
update-sample-data.duw
update-database.duw -- calls the files above, in the listed order

Each master folder should contain two folders:

sample-data
schema

The sample-data folder structure should contain:

folder with the module's name
the last level (leaf) update-sample.duw file can contain the actual scripts calls
update-sample-data.duw script file. This file should only contain calls of subsequent update-sample-data.duw from sub-modules of the current module

${run.script('%publicDataTransferDirectory%\23.2-plus\sample-data\app-bldgops\common\update-sample-data.duw')};
${run.script('%publicDataTransferDirectory%\23.2-plus\sample-data\app-bldgops\business-value-scripts\update-sample-data.duw')};

The same applies to the schema folder, but applicable for the update-schema.duw file.

Schema Folder

Changes to the following tables belongs to the schema folder:

Category	Description
Data Dictionary	afm_tbls, afm_flds, afm_flds_lang
Navigator	afm_ptasks, afm_subtasks, afm_actprods, afm_activities, afm_psubtasks, afm_products, afm_processes, afm_activity_cats, afm_tasks
Application Dictionary	messages, afm_scmpref, afm_dwgpub, afm_layr, afm_wf_rules, afm_bim_families, afm_bim_categories, afm_atyp, afm_activity_params
Project Data	all tables that do not fall in the categories above. We aim that these are the tables to customize.

Sample Data Folder

Changes that do not fall into categories above belongs to the sample-data, if not stated otherwise.