Alias Tables Demystified
Alias tables are a topic that comes up in the forums year after year, usually in the form of an application deadlock that a developer is trying to debug. I will try to simplify how you, the DataFlex developer, think about alias tables. It’s really a great and straightforward technique once you understand it and get the basics right.
What is an Alias Table?
An alias table is an additional handle to a physical table that is already used by the application. An example of a typical scenario for using an alias table is the ability to connect to the same Address table twice, once for shipping and once for billing.
Configuring Alias Tables
The “trick” to using alias tables is to make sure they are configured correctly. As of DataFlex 17.0, this has become much easier to do and maintain in the Studio – just follow the steps in DataFlex’s Help > What’s New > DataFlex 17.0 > Data Dictionary Improvements 17.0 > Better Alias Table DD Support. The older DF_File_Alias attribute still works, as well.
Once the above configuration is done, alias tables are used in an application just like any other table. The table shows up in the Studio’s Table Explorer, you create one or more Data Dictionary classes for it, which you then use when creating views, lookup lists and other application components.
Closing and Reopening Alias Tables
If you close and reopen tables in your application and this included alias tables, you must reapply the alias table attributes after reopening alias tables (see DF_File_Alias in the help). If you do that, you are good to go. In applications where I close and reopen tables, I usually create a method that opens all tables for the application and sets all attributes properly. This way, no matter when you open a table, at application launch or anytime thereafter, just call that one method that does it right. This also makes application maintenance and debugging simpler, since all of the table opening logic is in one place.
Relationships in Alias Tables
In the embedded database, table relationships are stored in the table header. In an SQL database (e.g. Microsoft SQL Server), table relationships are stored in the master table root name .INT file. In both cases, since an alias and its master table are both handles to the same physical table, any relationships defined in the master table are also defined in the alias table.
If you want to change any of these relationships, we recommend doing so using local DD relationships (see pbUseDDRelates in the help – there’s even an alias sample at the bottom of that topic).
If you create an alias table in the Studio, by right-clicking on a table in the Table Explorer and choosing ‘New <tableName> Alias Table’… from the context menu, the Studio creates an alias table, and also a DataDictionary Class for that table. This DD class is a subclass of the master table’s DD class, but with local relationships turned on.
If the master table for which you are creating an alias has a lot of ancestors, you will probably want to cancel all of those relationships and make the alias a simple lookup table with no ancestors. If you do need the ancestor tables for the alias, as well, you will also need to create alias table for all of the ancestors, or you can run into circular relationship issues. If you need a large ancestor tree for an alias table, you may want to rethink the design.
Alias Table Alternatives
In both Windows and Web Applications, the peUpdateMode property of the cDbCJGridPromptList and cWebPromptList classes, respectively, can be set to umPromptValue, which allows for returning a value from a lookup list without the need for a relationship. This can be a good solution for simple scenarios where a single, or perhaps a couple of values, need to be returned from a lookup list to an invoking view. If, however, you need to display multiple values from the table being looked up in an invoking view, a relationship simplifies this. In that case, I advise using an alias table.
SQL Views are another potential alternative for alias tables when using an SQL database.
Locking Issues
The way locking in DataFlex using the embedded database works is that, when a lock (or reread) is issued, every open, writable table in a program is locked, in the order the tables are listed in filelist.cfg. Since an alias table is a second handle to the same table, if it is not configured properly, the application will try to lock the same physical table twice (or more). This will cause a deadlock, where the application hangs until a database timeout occurs.
If you are using an SQL database (e.g. Microsoft SQL Server), this should not be an issue. All locking is row locking and the same transaction can lock the same row more than once.
Debugging Deadlock Issues
As stated, there are times you can run into deadlock issues. If you have followed our forum discussions on deadlocks, these nearly always turn out to be due to incorrectly configured alias tables. If this happens, the trick is figuring out which table (or tables) is causing the deadlock. Fortunately, DataFlex has tools built in to make debugging easier. I recently posted a package in our Code Library forum (http://support.dataaccess.com/Forums/showthread.php?57527-Function-for-Debugging-Dead-Locks) that makes debugging these issues easier.