Database Update Wizard

Performing the Transfer: Merge Data Dictionary Tables

The Archibus data dictionary contains a database-independent representation of the tables and columns used in the Archibus applications. When you customize the data dictionary (add tables or fields, or modify field attributes), those changes need to be transferred to other project instances.

For example, good practice is to make the changes in a test project and then merge these changes to the production environment. Another example of merging data dictionary changes is when you want to upgrade a customized schema to a new version of the Archibus schema. You would need to add the changes in the new version of the Archibus schema to your customized schema.

The data dictionary transfer is a two-step process.

1. On the Perform Transfer tab, you compare the data dictionary .csv files extracted from the project of the modified data dictionary to the schema of the database to which you are logged in. This generates a comprehensive list of differences.
2. On the Merge Data Dictionaries tab, you control which changes the merge process will include.

The process of updating a database can either be executed immediately on the database, and/or can be scripted to a series of SQL files that are run using a SQL query tool.

This topic has the following sections:

• Specifying the Transfer

• Specifying the Database Dictionary Tables

• Performing the Transfer

• Merging Data Dictionaries

For background, see:

• Database Update Wizard Overview

• Database Update Wizard: Transfer Data Overview

Specifying the Transfer

When the Database Update Wizard is first accessed, it will automatically run a process to bootstrap changes it needs to operate. (You will see a message dialog that says "Preparing schema".) These changes are automatically applied to the database and written to the file: ..\archibus\projects\users\public\dt\database-update\personalized-database\01_changes-database-update-wizard.sql.

First, you need to specify that you want to Transfer In and what you want to import.

The Transfer In process looks for .csv files in the folder you select from the drop down list "Database format to transfer in:" The choices are:

• personalized-database
• v.20.2
• v.21.1
• v.21.2
• v.21.3 and so on
  

The v.* options are used for upgrading a project to a new version of Archibus. The personalized-database folder is used for importing your specific data (for example, porting schema changes from a staging database to the production system). The location of the these folder is: ...\archibus\projects\users\public\dt\database-update

Note that this file location is fixed. The name of the file is the same as the table name. For example if you were importing field definition changes from a staging database, the .csv file would be ...\archibus\projects\users\public\dt\database-update\afm_flds.csv.

This form also allows you to specify if the changes should "Execute immediately on the project database". If this box is checked, The wizard will make the changes directly in the database that you are logged in to. Use caution when choosing this option , as you can't "undo" except by restoring a backup of the database.

You can also elect to "Create SQL scripts of transfer in process". This is very useful in situations where the changes need to be ported to multiple projects. Checking this box will create the file ..\archibus\projects\users\public\dt\database-update\personalized-database\02_changes-data-dictionary.sql

Note that the file name and location are fixed. This sql file should be opened in an appropriate SQL query tool for your database and executed.

Specifying the Database Dictionary Tables

To access the features for merging the data dictionary, you must choose to transfer in Data Dictionary tables when specifying the tables with which to work. The Database Update Wizard prevents you from selecting other types of tables so that it can present the forms specifically required for working with the database dictionary.

Performing the Transfer

The system selects the table and field definition tables for you.

Status - Lists the current status as:

• Pending -- the initial status of all specified tables.
• Compared - comparison completed - examine the logs for Inserted, Updated, and Missing records and check for errors in the Error log.
• No Extract File - reported when no .csv file exists for a specified table.

The Source Extract File column indicates the number of records in the .csv file which can be viewed by clicking on the ellipsis [...]. The Destination Table columns shows the current number of records in the database and can be previewed by clicking on the ellipsis [...].

To begin the comparison of chosen csv files and the current data dictionary definition, click on Start Compare. This process can take up to 1.5 hours depending on the speed of your application and database servers and on the type of database. The progress of the comparison is reported in the Job Progress panel.

Merging Data Dictionaries

Next, the Wizard displays the Merge Data Dictionaries tab.

Step 1: Set the form to show the changes that you want to review.

For example, you can hide the common, rote changes so that you can concentrate on the unique conditions and errors. Choose the changes you want to eliminate from the Per-field Dictionary Changes in the bottom panel by choosing the following options.

• Hide Field Heading Changes (Multi-line Heading Changes)
• Hide Field Size Changes
• Hide New Tables and Fields
• Hide All Data Dictionary Differences
• Hide All SQL Table Differences

Step 2: Review the Per-field Dictionary Changes

You can review the list of Per-field dictionary changes online as described below. To review the changes off line, you can export the data using the PDF and/or DOC action buttons.

Each row represents one type of change to one Archibus Field entry. For instance, if a field differed in both Data Type and Field Heading, these changes would show as different rows. The exception is new fields, which are represented by a single row.

Each row lists the table and field with the difference and then how they differ:

Field Differs In -- Specifies the type difference found. The type of difference is related to whether the difference is a data dictionary change or a SQL table change.

Data Dictionary Differences -- Lists differences in key parameters between:

• The Archibus Data Dictionary (this project’s Archibus Fields table, afm_flds)
• The extract file (the afm_flds.csv file extracted from the prototypical database)

The grid shows the project value -- the definition as it exists in the current data dictionary of the project you are logged into -- and the corresponding definition in the extract file (afm_tbls.csv). These are the differences that will be implemented when you select one of the panel actions (Apply Chosen, Apply Recommended, Keep Field Headings).

Specifically, the compare function during a data dictionary merge checks for differences listed below. The type of difference would be shown in the column "Field Differs In"

• Field has circular reference
• Default Value not in Enum
• Database Value not in Enum
• Field is new
• Table is New
• Table is only in project
• Field is only in project
• Is NULL?
• AFM Type
• Attributes
• Comments
• Data Type
• Decimals
• Dependent Columns
• Default Value
• Edit Group
• Edit Mask
• Enum List
• Primary Key
• Field Grouping
• Is Asset text
• Is Tc_Traceable
• Maximum Val
• Minimum Val
• Field Heading (Multiline Heading)
• Numeric Format
• Primary Key
• Reference table
• Review Group
• Size
• Single Line Heading
• String Format
• Validate Data

Additionally the compare checks for:

• Missing Table- Reports “Table does not exist in project” if the table does not exist in the project at all. This condition is listed instead of the field-by-field differences.
• Missing Field in Project - Reports “Field does not exist in extract.” if the field exists in the project database but not in the extract.
• Missing Field in Database - Reports “Field does not exist in project.” if the field exists in the extract but not in the project database.
• Default Value - Reports “Default Value does not exist in extract’s Enum” .
• Enumerations - Reports “Database uses values not in extract’s Enum”. For example, consider a field in the extract file with enumeration list values of "Open" and "Closed". A project database containing a record with the value "Canceled" would cause this difference to be reported. It is likely that you will also have a difference reported for Enumeration List because the project definition for the field probably includes the enumeration value "Canceled".
• Circular References - Reports “Field has a circular reference on <tbl.fld>” A circular reference is a field that refers to a table that ultimately refers back to the current field. This sometimes occurs with improperly specified validating tables.

SQL Table Differences -- This lists the differences between

• the Archibus Data Dictionary (this project’s Archibus Fields table as it exists at this instant), and
• the SQL database tables (the structure of the table as currently defined in the physical database).

These are the differences that would be implemented if you run the Schema Change Wizard. (There is no need to ever compare the extract file to the SQL database tables – you will always read the extract files into the afm_flds and afm_tbls tables before running the Schema Change Wizard.)

Specifically, the compare function during a data dictionary merge checks for these types of differences which are shown in the "Fields Differ In" column.

• Data Type
• Data Size
• Allow NULL
• Primary Keys
• Foreign Keys
• Default Value
• A change to an auto-numbered default.

Action - Recommended -- The recommended action is the one that is most likely right. In general, people don’t make changes to the standard schema without a reason. As such, the strategy is to recommend to keep the existing change except in the cases enumerated below.

Condition	Recommended Action
The Archibus field size is larger than the project field size	Apply Archibus size, as new Archibus standard apps will depend on it.
The project field size differs from the field size of its validating value (e.g. eq.fl_id is not the same size as fl.fl_id).	Review the field. This is a schema error.
Default value is not in Enum list.	Review the field. This is a schema error.
Enum lists differs.	Review the field. The new Enum list must have both the project and the Archibus values.
Data Type differs.	Keep the current data type.

Actions - Chosen -- For each change shown, choose one of these actions:

• Apply Change - Mark this as a change to be copied from the extract file to the project.
• Keep Existing - Mark this as a change to be ignored; keep the project’s value.
• Delete Existing- Mark this field in the project data dictionary as one to be deleted.

The actual change doesn't take place in the data dictionary until you select an action in Step 3, below, so you can change this selection to another as many time as you like. The Wizard highlights the chosen action and sets it off in braces, such as: [ Keep ].

The Drill-down Edit action ([…]) pops up a browser window with an edit view of just the given record in the Archibus Fields table. Use this editor to reconcile changes that are more complex than just accepting or rejecting a default action. For instance, you might use this edit view to merge the project and the schema enumeration lists for a field to make the list a superset of the two, or to make the Default value for a field equal to a member of an enumeration list.

To make the analysis process more focused and to provide the ability to choose an action for a group of dictionary changes, the "Multiple Selection" panel had a filter capability that applies to the "Per-field Dictionary Changes". Select the type of change (one or more) and click on Filter. The multi-select list contains the possible values that are shown in the Data Dictionary Differences column (see list above). The "Apply Chosen" panel allows you to select how to handle the filtered list.

• Apply Selected - Mark this as a change to be copied from the extract file to the project.
• Keep Selected - Mark this as a change to be ignored; keep the project’s value.

Note: Clicking on the Apply Selected or Keep Selected only applies the change to records visible in the grid. If there are multiple pages indicated (bottom left of the grid), you need to display those changes and click on Apply Selected or Keep Selected.

The mini console allows you to further refine the selection process and works together with the Multiple Selection filter. For example you could multiple select on "Field Heading" and in the mini console select "No Action" for the Action Chosen column. Then if you had multiple pages to "Apply Selected", each time you click "Apply Selected" those records would automatically be filtered from the grid and only those with no action would be shown. This is a quick way to process multiple pages of a particular type of change.

Step 3: Implement your Changes

After reviewing the data and selecting the actions to implement, make sure that you have selected the method of update you prefer under "How should the wizard apply changes?" You can select one or both options.

• Execute changes immediately on project database -- the wizard will make the changes directly in the database that you are logged in to. Use caution when choosing this option , as you can't "undo" except by restoring a backup of the database.
• Output changes to SQL Script -- Checking this box will create the file cts\users\public\dt\database-update\personalized-database\02_changes-data-dictionary.sql.Note that the file name and location are fixed. This sql file should be opened in an appropriate SQL query tool for your database and executed.

Once you are satisfied with the changes and method of update, select one of the following buttons.

• Apply Chosen - prompts for confirmation and then performs all of the actions you’ve specified. This option does not implement any changes for which you have not selected one of the three actions (Apply Change, Keep Existing, or Delete Existing).
• Apply Recommended - prompts for confirmation then applies the recommended action to all records that do not have an explicit choice. For example if you had selected Keep Existing and the Recommended Action was Apply Chosen, the recommended action to apply the change would not be implement.
• Keep Field Headings (Multi-Line Headings) - marks all Field Heading changes as changes as “Keep”; copies the Field Heading from the project database to the transaction table (afm_flds_trans) so that they won’t show as changes; and refreshes the grid to hide the Field Heading changes. This option is useful when your site has modified Field Headings and you are upgrading to a new version of the Archibus schema. This action preserves your custom Field Headings in one step.

When you select the button, the wizard performs the changes.

Note: Choosing a button implements changes shown in the Data Dictionary Differences column. The Database Update Wizard operates only on data contained in the database. The Database Update Wizard does not make changes to the structure of the database. After merging the data dictionary, you must run the Schema Change Wizard in order to change the physical database.