Database Compare

The 'Database Compare' tab provides a mechanism for comparing the current data model with a live database, and optionally synchronizing any differences in either direction. Differences 'pushed' into a live database are performed using 'Alter DDL' statements, while changes imported from the live database can be directly 'pulled' into the model.

The Database Compare functionality requires the Database Builder to have a valid ODBC connection to a live database. This database connection is shared by the 'SQL Scratch Pad', 'Database Compare' and 'Execute DDL' tabs of the Database Builder.

AccessTools | Database Builder > Database Compare

The DDL Compare Tab

The 'Database Compare' tab has a number of controls, as described here.

Number & Name

Description

1 Case Sensitive

Click on this checkbox to make all comparisons of properties recognize differences in letter-case in the property text.

2 Use Alias if Available

Click on this checkbox to indicate that any defined aliases should be used instead of object names (at both object and column level).

3 Reset All

Click on this button to set the Action flag for all objects back to the default value.

4 Set Import All

Click on this button to set the Action flag of all detected differences to <====; that is, update the model with value(s) from the live database.

5 Set Synchronize All

Click on this button to set the Action flag of all detected differences to ====>; that is, update the live database with value(s) from the model.

6 Differences

Review the list of objects found to have mis-matches between the model and the live database. Selecting an item in this list will populate the 'Components' list.

(See the Differences List table for a detailed description of each column.)

7 Components

Review this list of properties of the selected object that differ between the model and the live database.

(See the Component List table for a detailed description of each column.)

8 Reset

Click on this button to set the Action flag for all properties of the current object back to the default value.

9 Import from ODBC

Click on this button to import all properties values (with the Action of <===) from the live database into the model.

10 Generate DDL

Click on this button to generate the 'Alter DDL' statements for all objects with an Action of ====>, and send the statements to the 'Execute DDL' tab.

Differences List

Column

Description

EA

Displays the name of each object in the model that has one or more detected differences. Blank values indicate that the object is missing in the model but exists in the live database.

Action

Defaults to 'No Action' as the action to take considering this object's difference(s). Click on the drop-down arrow and select a specific action. The list of available actions in the list will depend on whether or not the given object is paired in the model and live database.

Paired objects

No Action - do not update the database or model with this change

===> - update the object in the database from the model

<=== - update the object in the model from the database

Customize - set the items to No Action prior to setting different actions on each item in the lower panel

Unpair - separate the paired objects so that they are not compared with each other or updated from each other

Unpaired objects

Create <object name> - create the missing database object in the database or model, as appropriate

Delete <object name> - delete the object from the model

Drop <object name> - delete the object from the database

Pair with <object name> - pair the object in the database with the named (unpaired) object in the model, so that they are compared for differences between them

The 'Action' fields in the 'Components List' (the lower panel) will be updated based on the selection of this field.

For example, if the live database has a Table column 'Address1' and the model doesn't, setting the object 'Action' to '===>' (update the object in the database from the model) sets the column 'Item Action' to 'Drop Address1', which will remove the column from the live database.

ODBC

Shows the name of each object in the live database that has one or more detected differences. Blank values indicate that the object exists in the model but is missing in the live database.

Count

Shows the total number of detected differences for the object (and all of its components) between the model and live database.

Component List

Column

Description

Item

Shows the component name or description for each detected difference. The differences are grouped into three categories: Properties, Columns and Constraints, in a tree structure.

EA

Shows the value of the given component as detected in the model. Blank values indicate that the value is missing in the model but exists in the live database.

Action

Defaults to the action corresponding to the setting of the object 'Action' field in the 'Differences' list, to indicate the action to take regarding the difference detected for the component. Click on the drop-down arrow to select an alternative action; the available options in the list depend on the component's type and the detected difference.

No Action - do not update the database or model

===> - update the object in the live database from the model

<=== - update the object in the model from the live database

Add <item name> - create the missing item in the database or model, as appropriate

Delete <item name> - delete the item from the model

Drop <item name> - delete the item from the live database

ODBC

Shows the value for the selected component in the live database. Blank values indicate that the value exists in the model but is missing in the live database.

Count

Shows the number of differences between the model and the live database detected in the selected component.

Working with the Database Comparison

Whenever you perform a comparison, Enterprise Architect reads the definition from both the live database and the model, and then attempts to 'pair' each object from one source with the other, using its name (and schema, if relevant for the current DBMS).

If a match is found, the object name is shown in both the EA and ODBC columns with a default action of 'No Action'. The count column indicates the total number of differences found for the Object and its components or properties.

If a match is not found between the systems, the object name is shown in the source column (either 'EA' or 'ODBC') while the other column is blank. In this state it is possible to pair the object with an object of a different name; the 'Action' dropdown list will present the available objects. If a new pairing is made the two objects' definitions are compared for differences and the results are shown in the 'Components' list, with the default action of '====>' selected.

If you select an action at the object level, this will set the matching action for all of the object's components and properties. However, if you select the 'Customize' action at the object level, you can determine a different action for each component.

Notes

The Database Compare functionality currently only performs comparisons on Table objects