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.

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.
On the Merge Data Dictionaries tab, you control which changes the merge process will include.

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

You can select the tables you want to compare by selecting the check boxes for a single table. Selecting the check box at the top of the column, selects all tables.

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.

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 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 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 Multi-line 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 Multi-line 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"

Data Type
Size
Decimals
Allow NULL
Primary Key
Validating Table
Fields to Validate
ARCHIBUS Type
String Format
Default Value
Numeric Format
Enumeration List

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.

Step 3: Implement your Changes

After reviewing the data and making changes as necessary, move to the top of the form and 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 Multi-Line Headings - marks all Multi-line Heading changes as changes as “Keep”; copies the multi-line 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 multi-line heading changes. This option is useful when your site has modified multi-line headings and you are upgrading to a new version of the ARCHIBUS schema. This action preserves your custom multi-line 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.