General Method

For each table except for the Archibus Tables and Archibus Fields tables (which already have their structure updated), the wizard:

1. Compares the data definition in the destination database for the table and its fields against the SQL database structure in the destination database.
2. Alters any tables with differing definitions.

A table differs if:

• It does not exist in the SQL database.
• Its autonumbered status has changed.
• Its primary key is different.
• Its foreign keys are different.
• If there is any other structural difference in one or more of the fields such as data type, size, default value, or allow null.

Altering a Table

If a table has a differing definition as described above, the program alters it using the process below.

If the table does not exist in the SQL database, note that it is to be "re-created" even if the customizer has chosen the alter table method on the Advanced Options form.

If this is an Microsoft SQL table and the autonumbered IDENTITY constraint has been added or removed, or if the primary key has changed, mark the table to be re-created, or change the primary key since Microsoft SQL Server must reorganize the data on disk. (There is no other way to add or remove this constraint.)

If the primary key is different:

• Drop the existing primary key constraint.
• For Oracle, drop any existing trigger and sequence implementing the autonumbered default.

If the primary key or any foreign key in a table is different, or if the table is to be re-created, or if the table does not exist:

• Drop all foreign keys from this table to other tables.
• Drop all foreign keys referring to this table.
• Add the table to a list of tables whose foreign keys are to be re-created, if it is not already on the list.

If the table is to be re-created:

• Create a new table, afm_temp, with the new definition.
• Copy all existing data in the SQL table to the afm_temp table.
• Drop the existing sql table.
• Rename afm_temp to the true table name.
• Create primary key.

If the table is to be altered:

• If a not null restriction is to be added to a field, update the field value for existing records with the default value so the alter statement will succeed.
• Issue one or more ALTER TABLE statements to effect the change.

If the primary key is different:

• Re-create it.

Comparison Rules

A table's primary key is different if:

• The table is re-created as opposed to altered (and will be dropped).
• The table formerly was autonumbered and is not now, or vice versa.
• The list of primary key fields has changed.
• The size, data type, or default value of one of the primary key fields has changed.

A table's foreign keys are different if:

• The table is re-created as opposed to altered (and will be dropped).
• The primary key is different.
• There is a difference in a foreign key field.
• A field is now validated and previously was not, or vice versa.

A field of a table is different if:

• It does not yet exist in the SQL database table.
• The data type or data size in the SQL database is different than the Archibus Fields Size value.
• It is a NUMERIC field and the number of decimals in the SQL Database is different from the Archibus Fields Decimals value.
• The NULL/NOT NULL constraint for the field is different from the Archibus Fields Allow Null? value.
• The default value in the SQL database is different than the Archibus Fields Default value.

The wizard checks the data size only for the following types of fields:

• Sybase: CHAR, VARCHAR, NUMERIC
• Oracle: CHAR, VARCHAR2, NUMBER
• Microsoft SQL Server: CHAR, VARCHAR, NUMERIC

Autonumbered Tables

The wizard works with autonumbered tables in the following way:

• For Sybase databases, the primary key field of the table will have the AUTOINCREMENT default value.
• For Oracle, the wizard will create a trigger named in the form "tablename_T" and a sequence named in the form "AFM_tablename_S". For example, the fn table will have trigger "fn_T" and sequence "AFM_fn_S".
• For Microsoft SQL Server, the table will be created with the IDENTITY constraint.

Ownership and Security

The wizard logs into the destination database as user AFM, and all tables it creates have user AFM as their owner, (unless you change the user name when specifying which projects to use).

The wizard grants permissions to the afm_users and afm_groups tables to user AFM_SECURE. When security is on, the Archibus program first logs into each project as user AFM_SECURE to look up information from the afm_users table on the person who has just logged in and to write information about the number of login retries to the afm_users table.

• For Sybase and MS SQL databases, the Archibus program (as user afm_secure) looks for table "afm.afm_users". The "afm" ownership prefix lets the program find the table owned by user "afm".
• For Oracle databases, the Archibus program (again as user afm_secure) looks for the table "afm_users". The program depends on the synonyms and default tablespace setting for the afm_secure account to locate the afm_users table.

There is no security conflict when having AFM as the owner of the tables. If you establish SQL-level security, you create new users with the same permissions as the AFM_USERS_GROUP role (and not with the same permissions as AFM), and then you revoke the rights that that user should not have.

Cascading Update and Deletions

For Sybase databases, the foreign key cascading updates and deletions are handled at the server level.

• Foreign keys constraints that allow null values include the "ON DELETE SET NULL" clause.
• Constraints that do not allow null (for the most part fields that are part of the primary key fields) include the "ON DELETE CASCADE" clause.

For Oracle and Microsoft SQL Server databases, the cascading updates and deletions are handled in the Archibus application, which cascades changes you make in the Archibus grid.