How to Update your Customized Schema after an Archibus Upgrade

The Archibus program works on earlier versions of Archibus databases. This means that even if a later version of the Archibus comes with an expanded database schema, you do not necessarily need to apply the new schema to your own custom database in order to use the new version of Archibus: it will work on your project as it is, unchanged.

However, if the new schema contains a new table or application that you wish to add to your custom project, you will need to upgrade the tables in your project database to match the new Archibus schema that came with a newer version of Archibus. A mature database is likely to contain a significant amount of data. So rather than migrating project data into a new database, the usual method is to apply the Archibus schema changes to the project database. With this method some care must be taken to make sure that the new schema doesn't overwrite customizations that you wish to preserve.

Pre-step: Check the current state of your database

Before updating the database, you can check its current state by using the Validate Schema feature of the Update Database Wizard. This action alerts you to violations of "best practice" database conventions. You can then fix any issues before you update the database with new values.

Step 1: Review the differences between the new Archibus schema and your custom project.

You should review what the changes will be before applying them. In this procedure it as assumed that the data dictionary and physical SQL tables of your project are consistent.

1. Determine if your data dictionary and physical schema are the same. Follow the instructions in How to Compare Data Dictionary to SQL Tables.
2. Evaluate the differences between your schema and the new standard Archibus schema. Follow the instructions in How to Compare Data Dictionaries.
3. Evaluate the differences between your Application Dictionary and the standard Application Dictionary using the Perform Comparison Only option of the Database Update Wizard.
4. Evaluate the differences between your Navigator and the standard Navigator using the Perform Comparison Only option of the Database Update Wizard.

Step 2: Decide which customizations to preserve.

You may not wish to remove all differences between your database and the standard Archibus schema.

For example, suppose you have widened one of the primary keys fields of your project database. You may have done this for an important reason (e.g. your Floor Codes were too long to fit in the default field width), and you want to preserve this difference. If you revert back to the standard field width, your data will be truncated and a number of records may now have duplicate primary keys.

Alternately, suppose you have changed the list of primary key fields in a table. For instance, you have created a two-part key for the Equipment Standard table so that you could handle a large number of equipment items. Or, suppose you have changed the data type of one of your fields. Again, you will want to preserve these differences, as otherwise your schema will not hold the data you have developed. Another common change to preserve is a custom enumerated list where you have added your site-specific values to the list.

Step 3: Selectively update the schema of your custom project.

In this step you will update the data dictionary of your project with only those new Archibus schema changes you wish to implement. To accomplish this task perform the following steps

1. Log into your project in Web Central.
2. Use the Transfer In option of the Database Update Wizard for the Data Dictionary. Follow the instructions in the topic Merging Data Dictionary Changes to apply only the changes you want to the data dictionary.
3. Use the Schema Change Wizard to generate an SQL script file that will implement the changes you just made to the data dictionary in the physical database tables.
4. Review the script file and remove any the changes you do not want. If you identify unwanted changes in this script file you may want to go back and make corresponding changes in the data dictionary so they remain consistent.
5. Run the script file against your project database in an appropriate SQl query tool such as: ISQL, SQL Worksheet, or the Query Analyzer.

Step 4: Selectively update your custom Navigator.

Suppose you have changed the existing Navigator entries to have them refer to new views. You will not want the wizard to revert your Navigator to the standard version. Yet you may wish to add new Navigator entries.

In this case, run the Database Update Wizard

• Specify Transfer = Transfer In
• Select the desired version from the drop down list.
• Un-check Execute changes immediately on project database
• Select: Next
• Select Navigator (un-check all other file types)
• Select: Next
• Check the box to select all tables
• Click on Start Import

When the import completes, the following file will be created: ...\archibus\projects\users\public\dt\database-update\personalized-database

Review the sql statements in this file and delete any modifications that are not in line with you plan.

Note: The Archibus application exposes the ProjUpWiz (Database Update Wizard) and SchemUpWiz (Schema Change Wizard) as DWR-exposed services. Otherwise, you could not invoke the Database Update Wizard and Schema Change Wizard forms on older databases that did not have the afm_wf_rules records for these wizards. The core secures these DWR-exposed services along with the other “bootstrap” DWR-exposed services.

Note: The Schema Change Wizard clears any cached table definitions so that you can transfer in tables without having to restart the application server. If the tables have not changed, there is no need to recompile views (.axvw) files into JSPs, to reload cached datasources on which the views depend, or to reload workflow rules. If the tables have changed, you need to manually edit the views and workflow rules to match the new schema, and then restart the application server.