Metadata

Create a Database Schema Repository with Meta Data Services in SQL Server 2000

Alok Mehta and Ricardo Rodriguez

Code download available at:MetaDataServices.exe(114 KB)

This article assumes you're familiar with T-SQL, XML, and XSL

Level of Difficulty123

SUMMARY

SQL Server 2000 Meta Data Services is a repository technology that stores and manages metadata for SQL Server. Instead of building database schemas over and over, Meta Data Services allows you to freeze an entire schema for use in other projects. You can also use these schemas for training, testing, or debugging. In this article, the authors will review the various components of Meta Data Services and show how it can be programmed using a Visual Basic client, XML, and XSLT. They will also show you how to manage and manipulate your metadata by generating a simple database schema using a SQL Server repository.

Contents

Getting Started
Creating Repository Databases
Exporting the Metadata
Transforming the XML Document
Creating a SQL Schema from SQL Script
AppContent Example
Exporting AppContent Meta Data
Generating XSLT to Create SQL Script
Execute SQL Script to Generate SQL Server Schema
Conclusion

M etadata is everywhere. It's an integral part of database schemas, object models, project descriptions, make files, cross references, reusable components, software/hardware configurations, business rules, and knowledge bases. Although metadata is very common, the tools and methodologies used to manage it are not. In many cases, developers find themselves designing and implementing similar database schemas over and over again for various different projects.

Furthermore, when an application is moved into a production environment, developers often like to freeze the version of database schema without the data so they can use the frozen schema for other purposes. Developers can start a new project with either a full or partially frozen schema or they can use it for documentation and training. They also might want to freeze the schema for testing, debugging, and optimizing the application without the production data in it. There are several ways to achieve these tasks. Developers can write SQL statements to make a backup of the database, remove all data from it, and use stored procedures to get information on the schema by writing custom solutions.

In this article, we'll discuss one of the hidden gems of SQL Server™ 2000, which will allow you to create a repository of database schema with ease—namely, Meta Data Services. We'll demonstrate the power of SQL Server 2000 Meta Data Services using XML so you can freeze the database schema and use parts of frozen schemas for other projects. We'll discuss the constituents of Meta Data Services and how they can be programmed using a Visual Basic® client, XML, and XSLT. We'll also walk through a simple data model in order to show you how it all works.

Getting Started

SQL Server 2000 Meta Data Services is an object-oriented repository technology that stores and manages metadata for SQL Server and its components. Meta Data Services is designed not only to store metadata but also to integrate with other tools and applications. It provides a solution for storing and managing data warehousing definitions, online analytical processing (OLAP) definitions, design data used in development tools, and any other type of metadata used in a programming environment. Meta Data Services offers much more than we will have time to cover in this article. Here are some of the features and services you can explore on your own.

Wide support for open standards Meta Data Services supports a variety of standards, such as COM-based interfaces, XML encoding, and Open Information Model (OIM). OIM supports standard metadata. XML encoding supports import and exporting of OIM metadata. For more information on OIM in Meta Data Services and XML in Meta Data Services, see Ways to Use XML in Meta Data Services.

Various information models Meta Data Services supports standard and user-defined metadata through the OIM and other information models. Model designers and programmers can use the SDK to create metadata-based applications and to build or extend information models that are the basis of shared metadata. For more information, see the Meta Data Services SDK.

A repository engine Meta Data Services provides a repository engine, which stores, consolidates, and retrieves metadata in repository databases. The repository engine is exposed as an object model that you can access using the Repository API.

A Repository API The Repository API that's included exposes repository engine functions and information model definitions through COM interfaces.

The Meta Data Browser This tool allows you to browse through registered databases. It can be used from within SQL Server 2000 or as a snap-in to Microsoft® Management Console (MMC). For more information, take a look at "Using Meta Data Browser".

As you can see, Meta Data Services provides a wide array of options that exceed the scope of this article. We'll focus on the power of the repository databases and their integration with XML within SQL Server 2000. We'll also show how you can create, browse, import, and export information within the repository databases using components developed by Microsoft. First, you'll need to install the Meta Data Services SDK version 3.0. Once you install the SDK, you will see several COM components installed in the directory, C:\Program Files\Common Files\Microsoft Shared\Repostry\. We will use some of the components in this directory.

Figure 1 Transforming a Schema

Figure 1** Transforming a Schema **

While Meta Data Services in SQL Server 2000 can be used in several different ways, our approach uses its services as a transition step to assist you in creating transformed schema. For instance, when you start a new project, you may want a subset of the schema from an existing project rather than the entire schema. Figure 1 shows an overview of our approach. The next four sections describe each step in the process.

Creating Repository Databases

Sometimes developers are interested in freezing the schema of a specific version of a database. As you can see in Figure 1, Application Data Model 1 has many versions, but we want to freeze version 3. Our approach suggests creating the repository of database version 3 in this step. The repository engine is a service that provides basic functionality for storing and retrieving objects and maintaining the relationships among them. In this example, the database schema is the object used by the repository engine. Repository databases can be created in the following ways: using the COM component REPODBC.DLL in a Visual Basic client, or using the Enterprise Manager within SQL Server 2000. In this article, we will be using Enterprise Manager.

Exporting the Metadata

Once the repository is created, you can access it programmatically using the COM interfaces provided by the Meta Data Services SDK to export the schema as XML. SQL Server 2000 Meta Data Services supports XML by encoding repository databases for the purpose of importing, exporting, and publishing metadata in XML. You can also exchange metadata between two repository databases, between a repository database and an application, or between two applications that can interpret the same XML format. Exporting and importing data using XML is supported in the Meta Data Services using the COM interface. As mentioned earlier, we'll use the Meta Data Services facility within the Enterprise Manager to export the schema of the database. Although this schema can be useful for many purposes, our goal is to transform this XML into SQL script using XSLT.

Transforming the XML Document

Essentially, this next step automates the process of creating a database and the new schema. You can also use your custom DLL or third-party tools to transform the XML created in Step 2 of Figure 1 to create the target database. We use the power of XSLT to transform the schema for Application 2.

We've created a transformation into the exported XML based on the dbm namespace elements present. In this example, we have focused on creating a portable SQL script, which uses a set of generic language attributes.

We should mention that a metadata repository abstracts several information models for software design, and its creation is based on the OIM-supported models listed in Figure 2.

Figure 2 Information Models Used in Metadata

OIM Groupings by Subject Areas OIM Information Models Namespace Identifier
Analysis and design model Unified Modeling Language uml
UML extensions umx
Common data types dtm
Generic elements gen
Object and component description model Component descriptions cde
Database and data warehousing Database schema elements dbm
Data transformation elements tfm
OLAP schema elements olp
Record oriented legacy rec
Knowledge management model Semantic definition elements sim

Since the goal here is to reproduce a database model based on its attributes, the logical choice to achieve this would be to work primarily on the elements related with the description of database schema elements. This means that we would be primarily processing elements from the dbm namespace. For this purpose, we have chosen two basic database elements that allow us to recreate a database up to a certain point: tables and indexes. We proceed to map them into the exported XML document from the elements dbm:DeployedTable and dbm:Index.

In order to separate the meta information and the information in the database itself, the metadata repository stores each definition separately. A relational association between the database structure and the data types it uses is made using IDs. For this reason, once we generate the first script from the exported XML, it does not contain any specific type information (such as nvarchar, integer, and so on), but simply the IDs related to its description. To generate this data, we have to process this XML and complete it dynamically. This means that once we've defined the basic layout of our script, we proceed to fill in the missing pieces inside the transformed page. In order to achieve this on-the-spot processing, the XSLT transformation creates a transformed document that presents the script in a user-friendly format, in this case HTML. Since the XPath expressions in an XSLT transformation are namespace aware, and in this case we need to match elements from other namespaces, the namespace needs to be declared within the XSLT file. The general flow is shown in Figure 3.

Figure 3 Transform Flow

Figure 3** Transform Flow **

Once this document is generated, it is composed of an HTML tags frame. Inside it we will create the core document using JavaScript, which will create the final content of the document as well as retrieve the data types needed for the database script. This process is achieved using ADO from JavaScript through the creation of ActiveX® objects. Once we are able to retrieve these pieces, we'll dynamically write into the HTML document, as well as the other pieces of the page, in order to present the script. We will assume that the XML will get the reference to the stylesheet. This is done in the exported XML file header by adding the following at the beginning of the line:

<?xml-stylesheet type="text/xsl" href="https://C:\XML2Script.xslt"?>

Creating a SQL Schema from SQL Script

In this step we simply execute the SQL script to create the transformed schema in SQL Server 2000. This SQL script will automatically create the database needed for the new application (Application 2). Once executed, you are now ready to start the new project. Once the resulting document has been generated, it contains a UI where the script is shown inside an HTML tag. This could be used in several ways. Basically, the execution of the SQL script is left to the user's preference and can be performed either through a stored procedure, a query processor, or any other tool.

Before we begin describing the nitty gritty of our sample project, there's just one more thing we need to discuss. After starting with the new project, you may find yourself repeating the process. In other words, the data model of Application 2 may evolve into several versions and you may want to freeze one of its versions. This is shown in Step 5 of Figure 1.

AppContent Example

Our example starts with a simple data model (called AppContent) shown in Figure 4. This is a model of a typical Web site that has pages and content or menus on those pages. Each menu can have several menu items with their own properties. In this example, we will start a new project using the pages and contents tables shown in Figure 4. Figure 5 shows the Enterprise Manager with all the tables of AppContent.

Figure 4 Data Model

Figure 4** Data Model **

The first step is to create the repository in SQL Server Enterprise Manager. On the local machine create the AppContent database with five tables—Pages, Contents, Images, Menu, and MenuItems. Configure the relationships among them, as shown in Figure 4. Navigate to Data Transformation Services | Meta Data within the Enterprise Manager. Right-click and select Import Metadata. Select the AppContent database within the local server, then click OK. You will see metadata from AppContent within the repository, as shown in Figure 6.

Figure 6 Metadata in the Repository

Figure 5 Tables in Enterprise Manager

Figure 5** Tables in Enterprise Manager **

Note that the repository offers several neat features such as tracking and merging versions of schemas and Data Transformation Services (DTS) packages. We simply use the repository as a transitional step in our approach. You can manage the schema within the repository, as well as doing many other tasks using COM interfaces provided by the Meta Data Services SDK 3.0. The following Visual Basic code programmatically creates the repository:

Public Sub CreateRepository() 
  Dim oRepository As New RepositoryTypeLib.Repository 
  oRepository.Create("SERVER= (local);DATABASE=AppContent","sa","") 
  Set oRepository = Nothing 
End Sub

RepositoryTypeLib.Repository is the COM interface available in the SDK. It offers many other methods and features. We cannot describe all of them here, so we encourage you explore them on your own. SQL Server creates several tables behind the scene (prefixed by "RTL") when the .create method is invoked.

Exporting AppContent Meta Data

The next step is to create an XML schema of the AppContent Meta Data. In the Enterprise Manager, navigate to Meta Data Services | Contents | Microsoft Data Warehousing Framework | OLE DB Database Schema | Name of your SQL Server Machine | AppContent. Then right-click and select Export to XML. You will be asked to name the exported file. Navigate to where you want to save that file and name it AppContent.XML. Its contents are shown in Figure 7. As mentioned before, this XML file contains a detailed description of the database, based on several OIM-supported information models (see Figure 2), including a description about the characteristics of each element selected to be exported as well as their subelements. Also note that you can export everything from the complete database down to any self-descriptive element.

Figure 7 Database Items

<?xml version="1.0" encoding="windows-1252" ?>
<oim:Transfer
    xmlns:oim="https://www.mdcinfo.com/oim/oim.dtd"
    xmlns:Umx="REPOS:Umx.dtd"
    xmlns:Tfm="REPOS:Tfm.dtd"
    xmlns:Gen="REPOS:Gen.dtd"
    xmlns:Uml="REPOS:Uml.dtd"
    xmlns:Dbm="REPOS:Dbm.dtd">
    <oim:TransferHeader Exporter="MSMDCXML" 
      ExporterVersion="2.0" TransferDateTime="2002-09-04T12:18:54" />
    <Dbm:DeployedCatalog oim:id="_0" 
      oim:objid="{{C376B717-BB5A-4504- 83ED-256CCDB477FB},00000001}" 
      RepositoryUpdatedBy="dbo" RepositoryUpdated="2002-08-10T18:51:02" 
      URL="AppContent" name="AppContent">
        <Uml:NamespaceOwnedElement>
            <Dbm:Schema oim:id="_1" 
              oim:objid="{{C376B717-BB5A-4504-83ED- 256CCDB477FB},00000003}" oim:label="dbo" name="dbo">
                <Uml:ClassifierFeature />
                <Uml:NamespaceOwnedElement>
                    <Dbm:DeployedTable oim:id="_2" 
                      oim:objid="{{C376B717-BB5A- 4504-83ED-256CCDB477FB},00000041}" 
                      oim:label="Contents" name="Contents"> ••• 
                        <Dbm:Index oim:href="#_8" 
                          oim:objid="{{C376B717-BB5A-4504- 83ED-256CCDB477FB},0000004D}" 
                          oim:label="Contents.PK_Contents" />
                        <Dbm:DeployedTable oim:id="_10" 
                          oim:objid="{{C376B717-BB5A- 4504-83ED-256CCDB477FB},00000053}" 
                          oim:label="Images" name="Images"> ••• 
                            <Dbm:Index oim:href="#_16" 
                              oim:objid="{{C376B717-BB5A-4504- 83ED-256CCDB477FB},0000005F}" 
                              oim:label="Images.PK_Images" />
                            <Dbm:DeployedTable oim:id="_18" 
                              oim:objid="{{C376B717-BB5A- 4504-83ED-256CCDB477FB},00000065}" 
                              oim:label="MenuItems" name="MenuItems"> ••• 
                                <Dbm:Index oim:href="#_24" 
                                  oim:objid="{{C376B717-BB5A-4504- 83ED-256CCDB477FB},00000071}" 
                                  oim:label="MenuItems.PK_MenuItems" />
                                <Dbm:DeployedTable oim:id="_26" 
                                  oim:objid="{{C376B717-BB5A- 4504-83ED-256CCDB477FB},00000077}" 
                                  oim:label="Menus" name="Menus"> ••• 
                                    <Dbm:Index oim:href="#_31" 
                                       oim:objid="{{C376B717-BB5A-4504- 83ED-256CCDB477FB},00000081}" oim:label="Menus.PK_Menus" />
                                    <Dbm:DeployedTable oim:id="_33" 
                                      oim:objid="{{C376B717-BB5A- 4504-83ED-256CCDB477FB},00000087}" 
                                      oim:label="Pages" name="Pages"> ••• 
                                        <Dbm:Index oim:href="#_37" 
                                          oim:objid="{{C376B717-BB5A-4504-83ED- 256CCDB477FB},0000008F}" 
                                          oim:label="Pages.PK_Pages" />
                                    </Uml:NamespaceOwnedElement>
                                </Dbm:Schema>
                            </Uml:NamespaceOwnedElement>
                        </Dbm:DeployedCatalog>
                    </oim:Transfer>

Figure 8 shows the code needed to export the repository programmatically from a Visual Basic client into an XML file. (Just note that in a real scenario you should avoid the default "sa/no password" credentials.) The statement

Dim objMDExport As MSMDCXMLLib.Export

creates the export library from the MSMDC (Meta Data Coalition) XML component, which is also part of Meta Data Services. Likewise, the other objects—namely, RepositoryTypeLib.RepositoryObject, RepositoryTypeLib.Repository, and RepositoryTypeLib.ReposRoot—offer several methods to manipulate the repository. Finally, objMDExport.Export exports the metadata into an XML file. (For more information on the MDC, see OIM in Meta Data Services.)

Figure 8 Export from Visual Basic

Public Sub ExportRepositoryData() 
  Dim objMDExport As MSMDCXMLLib.Export 
  Dim objMyObject As RepositoryTypeLib.RepositoryObject 
  Dim objRepository As New RepositoryTypeLib.Repository() 
  Dim objRoot As RepositoryTypeLib.RepositoryObject 
  Dim objRepID As Integer 
  
  Call objRepository.Open("SERVER=(local);DATABASE=AppContent", "sa", "") 
  objMyObject = objRepository.Object(objRepID) 
  Call objMDExport.Add(objMyObject) Call objMDExport.Export("C:\XMLFile.xml") 
  objMyObject = Nothing 
  objRoot = Nothing 
  objRepository = Nothing 
  objMDExport = Nothing 
End Sub

Generating XSLT to Create SQL Script

Once the exported XML is created, it should be associated with the transformation by including the following line at the beginning of the file:

<?xml-stylesheet type="text/xsl" 
  href="https://C:MSDN\MetaData\XML2Script.xslt"?>

Then open it by using a browser. This way, the JavaScript can be executed in order to fill the missing parameters.

We then divide the script creation into two parts: XSLT processing and page generation with JavaScript. The XSLT process takes care of each element by applying the corresponding template to each element that we would like to make part of our schema—in this case, tables and indexes.

It is here that the JavaScript functions are created. Once executed, this script will return fully formed SQL syntax strings. These functions will form our final result: a SQL script capable of recreating the original database (or elements) exported from the metadata repository. This first part of the process will leave all type-specific information, which is unknown at this point, defined in terms of other functions that will retrieve it later during the second step of the process. Here's an example of the source data obtained from the XML exported file. Notice that there's no explicit data type information given:

<Dbm:DeployedTable oim:id="_2" 
  oim:objid="{{91A6DEB7-0703-4DA4-980E- 9317736AB009},00000091}" 
  oim:label="Contents" name="Contents">
    <Uml:ClassifierFeature>
        <Dbm:DeployedColumn oim:id="_3" 
          oim:objid="{{91A6DEB7-0703- 4DA4-980E-9317736AB009},00000093}" 
          oim:label="PageID" initialValue="0" Ordinal="1" 
          name="PageID" Length="0" OctetLength="0" 
          NumericPrecision="10" NumericScale="0" IsNullable="1">
            <Uml:StructuralFeatureType/>
        </Dbm:DeployedColumn>

And here's an example of the processing transformation:

<xsl:template match="Dbm:DeployedTable">
    <!-- Checkup to remove previous existence of table objects, if so, then drop them --> 
    document.writeln('DROP TABLE [dbo].[
    <xsl:value-of select="@name"/>]'); document.writeln('GO\n');

For the second script creation part, the function called RetrieveDataTypeStr, created by the transformation, fetches the data type name from the database and from there it is included dynamically in the resulting HTML. The core part of the function is shown in Figure 9, but be sure to replace the server name, password, and user name to suit your configurations.

Figure 9 RetrieveDataTypeStr

// Create conection using ADO 
myConnection.Provider="SQLOLEDB.1"; 
  myConnection.Open("Data Source=<xsl:value-of select='$DBServer'/>;
  User ID=<xsl:value-of select='$DBUsername'/>;
  Password=<xsl:value-of select='$DBPassword'/>;
  Initial Catalog=msdb"); 
// Querying the corresponding Object 
sSQL = 'SELECT dbo.RTblNamedObj.[Name]' + ' FROM dbo.RTblNamedObj' + 
  ' WHERE dbo.RTblNamedObj.IntID = ' + ' (SELECT DstID' + ' FROM dbo.RTblRelships' + 
  ' INNER JOIN dbo.RTblNamedObj' + ' ON dbo.RTblRelships.OrgId = ' + ' dbo.RTblNamedObj.IntID' + 
  ' WHERE SUBSTRING(ORGID, 2, 4) = " + ' CONVERT(BINARY(8)," 0x' + sID + ')' +
   ' AND RTblNamedObj.Name = \'' + sName+ '\')'; 
myTable.Open(sSQL, myConnection, 2, 2); 
sValue = myTable.fields(0).value;

Executing the code produces the result shown in Figure 10. This is the SQL script that you can now copy and paste into the SQL Server 2000 Query Analyzer. The Submit Query and Download button on this page can automate the running of this script, which we won't cover here. Simply copying and pasting will do the trick.

Figure 10 Run the Function

Execute SQL Script to Generate SQL Server Schema

The new schema is shown in Figure 11. You can start developing the new project now. Creating the database schema is very straightforward since execution scripts are natural to the database management. For this reason, we will not go into detail about how to create or execute SQL scripts, except to say that tools like Query Analyzer can ease the task of reproducing the database wherever you should need it.

Figure 11 Schema

Figure 11** Schema **

Conclusion

Meta Data Services in SQL Server 2000 offers many more features than we have discussed here, such as DTS packages, metadata versioning capabilities, and the ability to perform configuration management. Besides Meta Data Services, other Microsoft technologies provide ways to create new application schemas from an existing database. For example, you can develop custom SQL scripts using ADO or ADO.NET or using the Schema Object Model (SOM), though most developers use SQL scripts and ADO to achieve this task. SOM, which is not yet widely used, is part of the MSXML 4.0 SDK. This SDK provides a navigable set of classes that directly reflect the W3C XML Schema definition language (XSD) specification. These classes enable you to walk through the elements of an XML Schema document and obtain information about the properties and declarations and about the relationships between them through either a Visual Basic or Visual C++® interface.

In this article, we have shown you a methodology to manage metadata using a SQL Server repository and its integration with XML. We then transformed the XML using XSLT into SQL scripts to generate the transformed database schema. We have purposely used a simple data model to illustrate our approach, but you can apply our approach to a large data model as well. The only thing that will change in using a different data model is the XSLT to transform the XML into SQL script.

For related articles see:
XML in Meta Data Services

For background information see:
https://www.w3.org/TR/xmlschema-0
Meta Data Services Architecture
Meta Data Services Software Development Kit

Alok Mehtais the CTO and Senior VP of AFS Technologies Inc. in Weston, MA where he is in charge of technology research and development. Alok has published several research papers on component-based software engineering and Web development.

Ricardo Rodriguez, currently Technology Director at SegurosdeVida.com, has a B.S. in Systems Engineering, and studies Electronics Engineering and Pure Mathematics. He is currently pursuing his MA in Information Technology at Harvard.