Database Update Wizard

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.

Languages should be disabled in \WEB-INF\config\context\compatibility\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.

validate.png

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

folders.png

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.

Troubleshooting

This script below, needs to be run if there are issues with tables that have autoincrement fields in Oracle.

  • Check the table definition:

    • Review the CREATE TABLE statement to ensure the column is correctly defined with the DEFAULT clause referencing the sequence.

  • Verify sequence existence:

    • Use SELECT * FROM user_sequences to check if the sequence exists in the database.

  • Check user privileges:

    • Use SELECT * FROM user_tab_privs to see if the user has the necessary privileges to access the sequence.

  • Test with a simple insert:

    • Execute a basic INSERT statement without specifying a value for the column to see if the database automatically populates it using the sequence. 

See Also

Database Update Wizard Overview