Because data dictionaries contain many of the essential definitions that your client applications use, 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, 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 is to change an existing data dictionary and its associated files (such as tables), and then deploy the updated files.
In most applications, however, this is not a viable solution. Specifically, most client applications contain data that is specific to the organization using the application. If you were to simply replace the data dictionary and its files, custom data would be lost. The same would be true about other possible customizations, such as any users added by the organization since you originally shipped the data dictionary.
Fortunately, the Advantage Data Architect includes the Dictionary Differentiator, a utility that can examine the differences between two data dictionaries. Furthermore, you can use the result of this examination to generate a SQL script that contains instructions that can be executed to update a data dictionary.
You can use the SQL scripts generated by the Dictionary Differentiator to write a utility that you distribute to locations using your application. Within this utility, which each customer only needs to run once, your code determines 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.
NOTE: Changing a data dictionary on a production application is a very delicate operation and deserves a few warnings. First, it is paramount that the existing data dictionary be backed up prior to an update, permitting a rollback to the original data dictionary if something goes wrong during the update. Second, you should always thoroughly test any update scripts for potential unwanted side effects. Third, while the Dictionary Differentiator does a good job of generating SQL to convert one version of a data dictionary to another, it does not know details specific to your client applications. You are responsible for converting any non-dictionary items. Finally, you need to confirm that the SQL that the Dictionary Differentiator generates is suitable for your upgrade needs. In some cases, it is better if you write your own conversion code instead of relying on what the Dictionary Differentiator produces.
The following steps describe how you can use the Dictionary Differentiator:
1. If you have any active connections to either of the data dictionaries that you want to compare, disconnect them before continuing. You disconnect an active connection by right-clicking the connection in the Connection Repository of the Advantage Data Architect and selecting Disconnect.
|2.||From the Advantage Data Architect main menu, select Tools | Compare Data Dictionaries. The Dictionary Differentiator, shown in Figure 4-15, is displayed.|
Figure 4-15: The Dictionary Differentiator
3. Use the First Dictionary section to select one version of your data dictionary.
|4.||Use the Second Dictionary section to select the second data dictionary.|
|5.||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 shown in Figure 4-16 is displayed.|
|6.||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.|
|7.||When you are ready to continue, click OK to display the Differences dialog box shown in Figure 4-17. (If you did not display the Select Objects for Exclusion dialog box, you may now be asked to enter a password for your data dictionaries.)|
Figure 4-16: The Select Objects for Exclusion dialog box
Figure 4-17: The Differences dialog box shows objects that the Dictionary Differentiator can use to compare two data dictionaries
8. The objects that the Dictionary Differentiator can generate code for appear as nodes in the Differences dialog box. Examine a node to see the objects that differ between the two data dictionaries. For example, consider Figure 4-17, in which the tables node has been expanded. The dictionary on the right contains the tables CUST_BAK and EMP_BAK, while the data dictionary on the left does not. The table glyphs associated with the data dictionary on the right 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 left show – signs, which indicates that this data dictionary does not have these objects.
|9.||To generate a SQL script that can be used to convert one dictionary to the other based on the identified differences, you can select the node associated with the changes you want to make and then click the Write Script button that corresponds to the direction of the change. For example, to generate code that will add the CUST_BAK and EMP_BAK tables to the data dictionary on the left, click the button labeled << Write Script. To generate a script that produces all changes in the desired direction, select the root node before clicking the corresponding Write Script button.|
|10.||Click the Close button when you are done using the Dictionary Differentiator.|
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.