Extracting Metadata - Using Constraints
(Page 16 of 22 )
Many of the information schema views are self-explanatory. However, the constraints are a bit confusing. Although column constraints are limited to check constraints, table-level constraints can be check, unique, primary key, or foreign key constraints. In all cases, the TABLE_CONSTRAINT view contains a row with the full name of a constraint in the Catalog/Schema/Name format and the constraint type. The constraint’s type indicates where to go looking for more information.
Retrieving Check Constraints Check constraints are the easiest type of constraint to retrieve. The full name of the constraint in the TABLE_CONSTRAINT view points to an entry in the CHECK_CONSTRAINT view. This view supplies the check clause as text. That’s simple enough (see Figure 2-4, A).
Once your metadata includes the information from the check constraint of the SQL Server column, you can pass the constraint information out to a user interface tier to build its validation (such as a Web page that validates using JavaScript), to build middle tier validation, and/or build intelligent messages for your users on validation failures and other slick things.
Because you’re using the constraint in a different language than the TSQL that defines the constraint, you’ll have to parse to clean up the syntax. Dragons lurk in these waters. At the simplest, you’ll have to do some parsing to remove square brackets and adjust naming in a consistent manner, including replacing keywords and any spaces that snuck into your column names. This won’t be sufficient where SQL functions are used because .NET won’t understand these functions. It also won’t be enough if your properties don’t map directly to columns or the check is more complex. Problems generally get deeper as you look at UI-tier code where you also need to reference a middle-tier object within the constraint. It’s easy enough for you to see the intent in the following check constraint, but how do you turn this into UI validation code?
<dbs:CheckConstraint Clause="([BirthDate] < getdate())" />
It’s actually a two-headed dragon that breathes fire from these depths. Attempting to handle all possible constraints eats too much development time. Handling some, but not all constraints, can result in lost constraints, which is very, very bad. It’s especially bad because missing constraints are hard to catch in testing. Invalid constraints that raise compile time errors are just fine—good in fact. You still have a dragon, but he has such a bad case of the hiccups he can’t sneak up on you.
Looking ahead to what generation actually does may clarify how you work with check constraints and the types of problems you’re likely to encounter. In the middle tier, you can just insert the text of the constraint after the square bracket, and name parsing is complete. This results in the following:
If Not (BirthDate < getdate()) Then
' Respond to Validation Failure
This assumes BirthDate is a property of the current object, which is likely because you are performing this validation within a middle-tier object. The compiler raises an error on getdate() because .NET doesn’t include this function, forcing you to add this method to your class. It’s easy enough to include a standard block of wrapper functions corresponding to common SQL functions in all of your generated classes. Alternatively, you can transpose these functions into framework functions during metadata extraction, or you can prefix all SQL functions with a specific class name such as SQLUtility.getdate() and add the methods to this class.
Compiler errors point you to the specific types of constraint validation used in your application. Be cautious to ensure that you never lose a constraint, even if you get something weird in the constraint. If something goes wrong, either output the original string (which you anticipate raising a compiler error) or something that’ll explicitly raise an exception such as the string Error Parsing Constraint.
TIP: Endeavor to parse constraints such that they’re fully complete and usable, or they’ll cause a compiler error.
Retrieving Primary Key and Unique Key Constraints It’s only a little more convoluted to extract the unique and primary key constraints. Here, the constraint full name in the TABLE_CONSTRAINT view corresponds to one or more rows in the KEY_COLUMN_USAGE table, as shown in Figure 2-4, B. The KEY_COLUMN_USAGE table includes the full constraint name, the full table name (that you already had), and the column name and ordinal position, which are the two new pieces of information you need. There may be multiple columns as part of the primary key, and thus multiple rows in the KEY_COLUMN_USAGE view are possible. The KEY_COLUMN_USAGE view contains the columns for all the constraint types that use key columns, including parent and child constraints as well as primary key and unique constraints.
Retrieving Parent Relations Relations between tables are called referential constraints and consist of a parent and child table. Child tables are also called foreign key tables, and parent tables are also called primary key tables. From the point of view of any single table, it can have any combination of parent and child relations.
The REFERENTIAL_CONSTRAINTS information schema view defines referential constraints. Each row in this view contains the full name to a parent table constraint and the full name of a child table constraint. The parent table is referred to in the view as the UNIQUE_CONSTRAINT and the child table simply as CONSTRAINT. You can retrieve both parent and child tables for any table from this view. In metadata, it’s best to include the relation definition as part of both the parent and child tables’ descriptions.
The foreign key relations of the TABLE_CONSTRAINT references the CONSTRAINT in the REFERENTIAL_CONSTRAINTS view. Effectively the current table is the child or foreign key table in the relation. The corresponding record in the REFERENTIAL_CONSTRAINTS view provides the full name of the parent constraint and table from the UNIQUE_CONSTRAINT fields of the view. With the full name of the parent constraint, you can grab the columns from KEY_COLUMN_USAGE in a manner similar to the primary and unique keys. This somewhat convoluted approach is illustrated in Figure 2-4, C.
TIP: It’s worth the trouble to add both parent and child relations to each table element in your metadata because it avoids convoluted XPath in your code templates. Convoluted XPath makes your code templates far less maintainable and far more sensitive to changes in metadata layout.
Child relations are all those relations with the current table listed as the parent table or UNIQUE_CONSTRAINT. To retrieve information on this constraint, you can search the REFERENTIAL_CONSTRAINTS view for all records with the current table as the UNIQUE_CONSTRAINT. Using the corresponding CONSTRAINT records, you can retrieve the table name and retrieve the keys for the child tables from KEY_COLUMN_USAGE. Figure 2-4, D, illustrates this relationship.
Retrieving Child Relations Grabbing child relations is similar.
 This is from Code Generation in Microsoft .NET, by Kathleen Dollard (Apress, ISBN 1590591372). Check it out at your favorite bookstore today. Buy this book now. |
Next: Modifying Mappings >>
More Database Articles
More By Apress Publishing
/ 17 
