Extracting Metadata - Working with SQL-92 Databases (SQL Server)
(Page 13 of 22 )
Databases might be the only metadata source you ever use. Everything else may be optional. Most applications are data centric, and most modern relational databases hold a phenomenal amount of information in their schemas. Many of the backends, including SQL Server and Oracle, provide rich information using features corresponding to the SQL-92 standard.
Selecting a Standard SQL-92 is the most mature in a long series of standards that have been used to define relational databases. It’s supported by SQL Server, Oracle, and several other databases.
Most databases go beyond the standards to provide their own extensions. Auto-incremented columns aren’t SQL-92 compliant—they’re extensions in any backend that supports them. Starting with SQL Server 2000, Microsoft provides extended properties that allow you to put name-value pairs on any object including tables, columns, stored procedures, stored procedure parameters, and so on. This allows you to extend metadata within the database. You can use this to add captions, descriptions for ToolTips, lookup information, and anything else you can imagine. You can add extended properties via Transact SQL (TSQL) statements, by right-clicking the object in Query Analyzer, or by creating your own tool to edit them. Extended properties aren’t part of the SQL-92 specification.
NOTE: There’s another standard you might be interested in if you’re doing data warehousing. If you know you need information from the Open Information Model (OIM) standard, then you can write code similar to the SQL-92 extraction tool to extract that metadata. If you don’t know about the OIM standard, the chances are excellent that the simpler approach of SQL-92 with database-specific extensions will provide all the metadata you need. |
Working with the Metadata Extraction Tool
The metadata extraction tool that you can download from the Web site extracts a ton of information about your database. This section discusses a little theory behind the tool and what it accomplishes. Appendix B walks through important parts of this tool.
TIP The metadata extraction tool at the Web site accesses only one database at a time. To work with multiple databases, you can extract data separately and combine it using the merge techniques discussed later in this chapter.
Understanding the Target Structure
Before looking at how the extraction tool works, it’ll help to know what the target XML file looks like. This is the target for both SQL-92 extraction and the XSDToFriendlyXML transformation. The opening of Northwind reveals familiar element names such as DataStructure and Table, rather than the obtuse element names of XSD. Column elements are called TableColumn because there are both table and views that contain columns, and that needs to be differentiated:
<?xml version="1.0" encoding="UTF-8"?>
<dbs:DataStructures xmlns:dbs="http://kadgen/DatabaseStructure">
<dbs:DataStructure Name="Northwind">
<dbs:Tables>
<dbs:Table Name="Orders"
OriginalName="Orders"
SingularName="Order"
PluralName="Orders">
<dbs:TableColumns>
<dbs:TableColumn Name="OrderID"
OriginalName="OrderID"
Ordinal="1" Default=""
AllowNulls="False"
SQLType="int"
NETType="System.Int32"
MaxLength=""
IsAutoIncrement="true">
<additional tables clipped for brevity>
To help sort out naming, the extraction process provides four names for each table. Table 2-2 explains the source and use of each name. The Name and OriginalName attributes are the same, unless there are spaces in the original name. For reasons that seem mysterious outside Microsoft, names of database elements can contain spaces. I recommend you avoid names containing spaces because they can cause problems all over the place. Metadata extraction replaces any spaces in the original name with underscores to create the Name attribute. Many table names based on English language words can be automatically converted to singular and plural forms. It makes no difference to this process whether the original table names are singular or plural, but it’s rather maddening for humans to work with databases intermixing singulars and plural table names, and the ORM process in Chapter 6 is a little easier if you use singular table names.
CAUTION -- The logic used for making singular and plural forms of tables names won’t work for all table names and won’t work with names not based on the English language.
ATTRIBUTE | DESCRIPTION | USAGE | PROVIDED FOR COLUMNS? | PROVIDED FOR TABLES? |
Name | The name of the element as used in the database, with spaces replaced with underscores and keywords prefaced with an underscore. Plurality isn’t changed. | The primary identifier for the element. | Yes | Yes |
OriginalName | The name of the element used in the database. | Provided for reference and used when generating stored procedures. | Yes | Yes |
Singular | The Name attribute modified to make it singular if it isn’t already singular, | Name of row classes; prefix for collection classes that end with Collection. | Yes | No |
Plural | The Name attribute modified to make it plural if it isn’t already plural. | Name of variables pointing to collection classes. | Yes | No |
Table 2-2. The Use of Each of the Name Attributes Provided for Tables and Columns
NOTE: This and other aspects of the samples and example architectures throughout this book assume you don’t have both singular and plural forms as separate database tables (for example, Customer and Customers tables in the same database). If you do, you’ll have to adjust. You can either modify the extraction tool or supply modified singular and plural names via freeform meta-data (discussed later in this chapter). . |
The TableColumn element contains information retrieved from SQL Server. The NETType attribute contains a string translated from the SQL type.
Each column has privileges defined in the database. If you include these privileges in your metadata, you can fine-tune your user interface behavior via your templates. Each column may also have check constraints, but the specific column schema shown here doesn’t have a check constraint:
<dbs:TableColumnPrivileges>
<dbs:TableColumnPrivilege Grantor="dbo" Grantee="public"
Type="REFERENCES" />
<dbs:TableColumnPrivilege Grantor="dbo" Grantee="public"
Type="SELECT" />
<dbs:TableColumnPrivilege Grantor="dbo" Grantee="public"
Type="UPDATE" />
</dbs:TableColumnPrivileges>
<dbs:CheckConstraints />
</dbs:TableColumn>
</dbs: TableColumns >
Like columns, tables have privileges defined in the database. This information is included in the XML as follows:
<dbs:TablePrivileges>
<dbs:TablePrivilege Grantor="dbo" Grantee="public" Type="REFERENCES" />
<dbs:TablePrivilege Grantor="dbo" Grantee="public" Type="SELECT" />
<dbs:TablePrivilege Grantor="dbo" Grantee="public" Type="INSERT" />
<dbs:TablePrivilege Grantor="dbo" Grantee="public" Type="DELETE" />
<dbs:TablePrivilege Grantor="dbo" Grantee="public" Type="UPDATE" />
</dbs:TablePrivileges>
One of the most important things in the XML metadata is information about table constraints. This includes primary keys and table relations. The primary key information contains a list of primary keys. Generally each table has a single primary key, but it may have a compound key made up of several columns so it’s a child element, rather than an attribute:
<dbs:TableConstraints>
<dbs:PrimaryKey>
<dbs:PKField Name="OrderID" Ordinal="1" />
</dbs:PrimaryKey>
Table elements in the metadata file contain both parent and child relations. This means that each relation is described both from the perspective of the parent and from the child. This allows templates using the metadata to access relation information easily and helps avoid convoluted XPath statements. The Orders table has one child and three parent tables, each of which uses one key field. The child table element doesn’t need to list the parent key fields because it’s immediately available as the primary key of the current table. Multiple key fields would result in multiple ParentKeyField and ChildField elements:
<dbs:TableRelations>
<dbs:ChildTable Name="Order_Details">
<dbs:ChildKeyFields Name="OrderID" Ordinal="1" />
</dbs:ChildTable>
<dbs:ParentRelations>
<dbs:ParentRelation ParentTable="Customers">
<dbs:ParentKeyField Name="CustomerID" Ordinal="1" />
<dbs:ChildField Name="CustomerID" Ordinal="1" />
</dbs:ParentRelation>
<dbs:ParentRelation ParentTable="Employees">
<dbs:ParentKeyField Name="EmployeeID" Ordinal="1" />
<dbs:ChildField Name="EmployeeID" Ordinal="1" />
</dbs:ParentRelation>
<dbs:ParentRelation ParentTable="Shippers">
<dbs:ParentKeyField Name="ShipperID" Ordinal="1" />
<dbs:ChildField Name="ShipVia" Ordinal="1" />
</dbs:ParentRelation>
</dbs:ParentRelations>
</dbs:TableRelations>
</dbs:TableConstraints>
<dbs:ExtendedProperties />
</dbs:Table>
The remainder of the table elements repeat this sequence. The XML document contains similar sections for views, stored procedures, and so on. By the time the metadata encompasses all of the information in the database, the metadata files can become fairly large. The metadata file for Northwind, which isn’t a particularly complex database, is about 200KB.
 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: Understanding the Tool Architecture >>
More Database Articles
More By Apress Publishing
/ 17 
