Compare Data Models and create DDL scripts
During the development cycle of data model, several changes/updates may take place over time depending upon the needs and requirement of that project. As and when required, different data model versions will be created with little or more changes between them. At one point of time, one may require to know the exact differences between different versions(older version and newer version) of data models. Knowing these will help in assuring the updates in the newer version are correct and staying intact with the needs of the requirement. Also you can apply these changes to the underlying database by creating “ALTER” scripts. In that case, data modeling tools can help us to sort out the differences between data models.
With the help of a data modeling tool you can compare:
- between two data models
- data model with the script
- data model with the underlying database
Significance of comparing data models:
- To know the differences between them Or to know what changes have been taken place in the latest version.
- Whatever differences are there, we can create scripts and the same can be implemented on the database.
In this article, we are using Toad software to compare between two different data model versions as well as to create ALTER scripts for database update.
Note: In Toad Data Modeler Software, data models are saved with file extension of .txp.
The following table represents a data model with a file name “Domain_Rule_Default_v1.txp” in the first stage of a development of a data model.
[ultimatetables 13 /]
In the second stage of a development of a data model, you are requested to add another column “COUNTRY_NAME VARCHAR2 (30) NOT NULL)”.
Follow the guidelines below to insert the requested column in the data model using Toad Software.
- Save the existing data model “Domain_Rule_Default_v1.txp” as “Domain_Rule_Default_v2.txp”.
- Add a column “COUNTRY_NAME VARCHAR2 (30)” and assign NOT NULL to it.
- Save the data model.
The following table represents the data model “Domain_Rule_Default_v2.txp” created in the second stage of a development of a data model.
[ultimatetables 14 /]
For comparing the data models:
- Open TOAD Data Modeler.
- Click menu File/Sync & Convert/Sync & Convert Wizard.
- Action: On the new window that is appearing, click the button “Compare Models and Generate Alter Report”. Click next.
- Select Left Side: Click the button Model File “*.txp, *.txl “. Select “Domain_Rule_Default_v2.txp”. Click next.
- Select Right Side: Similarly click the button Model File “*.txp, *.txl “. Select “Domain_Rule_Default_v1.txp”. Click next.
- Settings: You can select the comparison rules (Ignore text case, Ignore Model Name, Resolve Application Variables, Ignore empty Primary Keys, etc). Click next.
- Select Object Types: You have compare all, compare all without graphics, compare settings buttons. Click compare all and. Click next.
- Select Items: A new window appears and you can see that COUNTRY_NAME doesn’t exist in “Domain_Rule_Default_v1.txp”.
How to generate Alter Script after comparing the data models?
- Open TOAD Data Modeler.
- Click menu File/Sync & Convert/Sync & Convert Wizard.
- Action: On the new window that is appearing, click the button “Generate Alter Script”. Click next.
Finally, the script generated by TOAD as follows:
ALTER TABLE “DOMAIN_RULE_DEFAULT” ADD (“COUNTRY_NAME” Varchar2(30) NOT NULL)