Chapter 4: Understanding and Using Data Dictionaries | Advantage Database Server: The Official Guide

A data dictionary is a special file that serves as the sole access point for database tables. Although the use of a data dictionary is optional, it is hard to imagine anyone creating a serious database without one. This is because a data dictionary provides access to many of the Advantage Database Server’s most powerful and advanced features.

This chapter has two goals. First, it provides you with an overview of data dictionaries, including a tour of the features that they make available. The second purpose of this chapter is to show you how to set up a data dictionary with tables and security. This dictionary will be used in many of the later chapters of this book to demonstrate some of ADS’s most advanced features, including AEPs (Advantage Extended Procedures) and Advantage Triggers.

Data Dictionary Overview

A data dictionary is a special file that contains a wide range of definitions that are used by ADS to access your data. Some of these definitions apply to individual tables of your database. For example, a data dictionary can be used to associate one or more non-structural indexes with a table. This permits ADS to auto-open these indexes any time the associated table is opened. With free tables, you are responsible for opening these non-structural indexes yourself, and risk index corruption if your fail to do so.

Other definitions in a data dictionary apply to two or more tables. For example, a data dictionary permits you to create referential integrity definitions. Referential integrity definitions specify how data in related tables is maintained.

When your client applications connect to ADS in order to use free tables, you connect to a directory in which your free tables are stored. By comparison, when your data is stored in database tables—those bound to a data dictionary—you make your connection to the data dictionary itself. Using this connection, ADS enforces the definitions found in the data dictionary, assuring that the tables associated with that data dictionary are maintained correctly.

When you are using ADT tables, the recommended table type for ADS, the use of a data dictionary is an all or none proposition. Specifically, an ADT table is either a free table or a database table. Once an ADT table is associated with a data dictionary, it cannot be accessed as a free table anymore (unless you specifically free it from the data dictionary).

Data dictionaries provide a great wealth of features, making them an indispensable part of almost all ADS applications. The following is a list of many of the capabilities that data dictionaries provide you and your applications:

You can configure database tables and/or index files to be auto-created. The table structures of auto-created tables are stored in the data dictionary. At runtime, if an auto-created table is not found, the data dictionary will create it and its indexes the first time a client attempts to open that table. This feature simplifies database deployment for those developers who normally distribute their databases empty.
Data dictionaries permit you to define default values for fields for newly inserted records. If a value is not assigned to the field when the record is being inserted into the table, that field will be assigned the specified default value.
When using a data dictionary with encrypted tables, your client applications do not have to supply each table with a password. The data dictionary does this automatically.
Data dictionaries provide your client applications with access to stored procedures. Stored procedures are precompiled subroutines that run on the server and are ideal for data intensive operations. Stored procedures (Advantage Extended Procedures) are described in Chapter 7.
Data dictionaries permit you to define views. A view is a SQL SELECT statement that resides on the server, and that can be treated like a table from your client applications. Views are described in Chapter 6.
Data dictionaries permit you to implement sophisticated security. Every user can have a different user name and password that they use to access the data dictionary. Furthermore, you can customize the access rights of each user to the tables, fields in the tables, stored procedures, links, and views in the data dictionary.
Data dictionaries enable you to create triggers. Triggers are server-side subroutines similar to stored procedures. Unlike stored procedures, which are invoked by the client application, triggers are executed when changes are posted to a database table. Triggers permit you to implement sophisticated actions in response to changes in your data. Triggers are described in Chapter 8.
Up to 14 non-structural index files can be associated with each database table in a data dictionary. The data dictionary will automatically open these index files any time you open the table, closing them when the associated table is closed.
Data dictionaries permit you to associate record-level and field-level constraints with tables. Constraints are rules that prohibit invalid data from being posted. Defining constraints is described in Chapter 5.
Data dictionaries permit you to enforce referential integrity. Referential integrity is described in Chapter 5.
Data dictionary definitions can only be updated through a special data dictionary administrator account, which can be password protected. This allows you to prevent unauthorized changes to your data dictionary definitions.
Using a data dictionary, you can permit your client applications to connect to your tables over the Internet directly without having to resort to a Web browser interface.
You can assign to your data dictionary a major and minor version number. If you update your data dictionary in the future, for example, adding or removing tables, or changing existing table structures, a special Advantage Data Architect utility can generate SQL scripts that you can use to upgrade older data dictionaries. This feature is particularly valuable when you need to upgrade data dictionaries remotely. However, the major and minor versions are for your benefit and are not used by this utility.
When using data dictionaries, ADS can ensure that all of your indexes use the same ANSI collation sequence or OEM character set. If ADS discovers a mismatch among ANSI collation sequences or OEM character sets, it automatically rebuilds your indexes.

You can work with data dictionaries through the Advantage Database Manager in the Advantage Data Architect. This graphical tool allows you to easily manage all of your data dictionary’s definitions.

Note

Actually, you can also work with data dictionaries using SQL and the ACE (Advantage Client Engine) API. However, the Advantage Data Architect provides a much more convenient and graphical way to work with data dictionaries.

Data dictionaries can be used with both ADS and ALS (Advantage Local Server). The only data dictionary feature that is not also available for ALS is data access over the Internet. This feature requires ADS.