Please note : This help page is not for the latest version of Enterprise Architect. The latest help can be found here.
Topic |
Prev | Next |
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.
Access
Open the Database Builder window, then display the 'Database Compare' tab.
Access |
Method |
---|---|
Ribbon |
Design > Tools > 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 the 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 the 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
Unpaired objects
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.
|
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.
As an example, both a column (tax_amount) and constraint (ck_customersordersitems_discount) were renamed in Table 'public.customers_order_items' (in the Example model) and a database compare performed; this image shows the differences found:
In the image there is only one Table that had detected differences - 'public.customers_order_items'; selecting this populates the 'Components' list. From the detected results it can be determined that the data model contains a column (tax_amount2) and a check constraint (ck_customerordersitems_discount1) that the live database doesn't and in turn the live database contains a column (tax_amount) and a check constraint (ck_customerordersitems_discount) that the data model doesn't.
Comparing with Options
The 'Compare with Options' functionality works in the same manner as for a direct comparison, except that you are prompted to choose which object/property comparisons should be performed. This enables you to ignore particular differences that are not of relevance at the current time.
These tables describe the different comparisons that can be enabled or disabled.
All Objects, Owner
Comparison |
Action |
---|---|
Owner |
Select to indicate that the 'Owner' property of all database objects should be compared, after the objects have been 'paired'. |
Table Options
Option |
Action |
---|---|
Tables |
Select this parent option to enable all of the Table comparison options. Deselect to disable all the other options. You would then deselect or select specific options in the list. |
Table - Extended Properties |
Select to indicate that extended properties of Tables (such as DB Version and Tablespace) should be compared. |
Table - Remarks |
Select to indicate that remarks applied to Tables should be compared. |
Columns |
Select this parent option to enable all of the 'Column comparison' options. Deselect to disable all the other 'Column' options. You would then deselect or select specific options in the list. |
Column - Type |
Select to indicate that the datatype name for the Table Columns should be compared. |
Column - Size |
Select to indicate that the datatype size for the Table Columns should be compared. |
Column - Default Value |
Select to indicate that the default values of the Table Columns should be compared. |
Column - Position |
Select to indicate that the Table Column positions should be compared. |
Column - Not Null |
Select to indicate that the not null property of the Table Columns should be compared. |
Column - Auto Numbering |
Select to indicate that the autonumbering properties for the Table Columns should be compared (such as AutoNum, StartNum and Increment). |
Column - Unmatched Columns |
Select to indicate that Table Columns that are unmatched between the model and the live database should be compared. Typically these are columns that exist in one system but do not exist in the other. |
Column - Extended Properties |
Select to indicate that extended properties of Table Columns (such as Unsigned and Zerofill) should be compared. |
Column - Remarks |
Select to indicate that remarks applied to Table Columns should be compared. |
Constraints |
Select this parent option to enable all of the 'Table Constraint comparison' options. Deselect to disable all the 'Table Constraint' options. You would then deselect or select specific options in the list. |
Constraint - Primary Keys |
Select to indicate that properties related to Primary Keys should be compared. |
Constraint - Foreign Keys |
Select to indicate that properties related to Foreign Keys should be compared. |
Constraint - Indexes |
Select to indicate that properties related to Indexes should be compared. |
Constraint - Unique Constraints |
Select to indicate that properties related to Unique Constraints should be compared. |
Constraint - Check Constraints |
Select to indicate that properties related to Check Constraints should be compared. |
Constraint - Table Triggers |
Select to indicate that properties related to Table Triggers should be compared. |
Constraint - Unmatched Constraints |
Select to indicate that Table Constraints that are unmatched between the model and the live database should be compared. Typically these are constraints that exist in one system but do not exist in the other. |
Constraints - Extended Properties |
Select to indicate that extended properties of Table Constraints (such as Fill Factor and Clustered) should be compared. |
Constraints - Remarks |
Select to indicate that remarks applied to Table Constraints should be compared. |
Notes
Learn more