Because data dictionaries contain many of the essential definitions that are used by your client applications, changes to your database nearly always involve changes to its associated data dictionary. If your database is deployed to a single location, making changes to your data dictionary is not a major issue. However, when a database is deployed to a large number of sites, such as is often the case with vertical market applications, updating the data dictionary can be a complicated task.
The simplest way to update a data dictionary would be to change an existing data dictionary, and then deploy the update. In many applications, however, this is not a reasonable solution. Specifically, some client applications permit an administrative user to add users, make them members of groups, grant additional access rights to users or groups, and so forth. If you were to replace the data dictionary that the client applications were using, these customizations would be lost.
Fortunately, since ADS 7.0, the process of updating data dictionaries is much more straightforward. The Advantage Data Architect in ADS 7.0 and later includes the Dictionary Differentiator, a utility that can examine two data dictionaries. Moreover, as a result of this examination, the Dictionary Differentiator can generate a SQL script that contains instructions that can be executed to update a data dictionary.
You use the SQL scripts generated by the Dictionary Differentiator to write a utility that you distribute to your Customers. Within this utility, which each customer only needs to run once, your code examines the version number of the current data dictionary. If it determines that an older version of the data dictionary is present, it executes the SQL script, which applies the updates.
The following steps describe how you can use the Dictionary Differentiator:
From the Advantage Data Architect’s main menu, select Database | Compare Data Dictionaries. The Dictionary Differentiator, shown in Figure 4-10, is displayed.
Figure 4-10: Use the Dictionary Differentiator to compare two data dictionaries.
Use the First Dictionary section to select one version of your data dictionary.
Use the Second Dictionary section to select the second data dictionary.
If there are objects that you do not want to include in the comparison and SQL script generation, click the Exclude Objects button. If you click Exclude Objects, you may be asked to log into one or both data dictionaries. Provide an administrative user name and password for each data dictionary you are asked to log into. Once you have provided any requested passwords, the Select Objects for Exclusion dialog box is displayed:
Using the Select Objects for Exclusion dialog box, place a checkmark next to any category of object that you do not want to be included in the comparison. For example, if user names and groups are something that can be administered through client applications, you probably do not want to include these in the comparison. Click OK to close the Select Objects for Exclusion dialog box when you are done to return to the Dictionary Differentiator.
When you are ready to continue, click OK to display the Object Differences dialog box shown in Figure 4-11.
The objects that the Dictionary Differentiator can generate code for appear as nodes in the Object Differences dialog box. Examine a node to see the objects that both data dictionaries share, as well as those objects that are included in only one data dictionary. For example, consider Figure 4-11, in which the tables node has been expanded. The dictionary on the left includes the tables CUST_BAK and EMP_BAK, while the data dictionary on the right does not. The table glyphs associated with the data dictionary on the left show + signs, indicating that this dictionary has objects that the other does not. The glyphs for these same tables in the data dictionary on the right show – signs, which indicates that this data dictionary does not have these objects.
Figure 4-11: Use the Object Differences dialog box to compare your data dictionaries.
To generate a SQL script that will convert the selected node for the data dictionary on the left side, tables in the example shown in Figure 4-11, to match the structure of the corresponding node on the right side, click the button labeled Write Script >>. This script would contain SQL DROP TABLE statements, which would remove these two tables from the data dictionary on the left.
To generate a SQL script that will convert the node on the right to the node on the left, click the << Write Script button. This script would contain a series of SQL CREATE TABLE statements, and any other statements that would apply, such a CREATE INDEX, CREATE TRIGGER, as well as the execution of one or more system stored procedures, such as sp_ModifyTableProperty, to name a few of the items you might expect in this script.
Save each script that you generate, until you have created scripts to update the one data dictionary from the other. Click Close when you are done.
In the next chapter, you will learn how to define field-level and record-level constraints for your database tables and implement referential integrity, a special type of constraint.