27.1. Database Design with XML Schemas
You have already looked extensively at the Client tier. This chapter will focus on the Database and Component tiers. Our goal is to complete the development of the CRSS to the point where you can add and view the reservation requests. Therefore, this chapter will model the database tier and the application tier.
Many existing applications add significant business logic in Active Server Page (ASP) or JavaServer Page (JSP) pages. In Chapter 26, we added some workflow logic to our user interface (UI) logic. This illustrated how to send and receive the XSLT-generated data to the Web tier. You will be simplifying the Web tier considerably as you further develop your business components.
Prior to working on the business components, you need to develop a database for the CRSS application. In Chapter 26 you developed a complete XML schema that models the reservation request. This XML schema was created by using the sequence diagram that is shown in Figure 26.3. You will now use this XML schema to assist in the creation of the database.
The fundamental difficulty in working with XML and relational databases is mapping the database tables and fields to the XML elements and attributes. A number of tools and competing standards are emerging to assist in this work.
Note
Microsoft created a schema dialect called XDR, (XML Data Reduced), to work with Internet Explorer prior to the W3C development of the XML Schema Recommendation by the W3C. This format is still used by some tools to provide integration to databases. Other initiatives are aimed at integrating XML schemas with relational databases. Two of the more notable open-source initiatives are Schematron and RELAX NG.
Perhaps the quickest way to use our XML schema to create a database is to open the schema by using Visual Studio .NET. Visual Studio .NET provides a view of the XML schema that looks like a relational database structure. The partial view in Figure 27.1 is of the reservation request schema created in the Chapter 26.
Figure 27.1. CRSS reservation request schema in Visual Studio .NET.
You can modify this view by right-clicking and selecting Preview Dataset. Voila, you are looking at the start of a database table structure, as shown in Figure 27.2.
Figure 27.2. Dataset Properties from Visual Studio .NET.
Naturally, no database design will ever be completed automatically. Proper database design entails a good model, normalization, security, and so on, but it never hurts to have a little help from good tools. Using the dataset in .NET, you could write code to loop through the Tables collection and create a new table on the database. Although this is a valid approach, it is not too useful because you would end up making changes to the database design anyway.
Another approach is to use XML Spy (http://www.xmlspy.com) and load the schema. After the schema has been loaded, you can use XML Spy to automatically generate a valid XML document that can be used to create a database structure.
The Visual Studio .NET view of a schema is shown in Figure 27.3.
Figure 27.3. Dataset preview from Visual Studio .NET.
After you have created the XML structure for the database as shown in Figure 27.3, you simply select the elements that will become tables. Several options exist for mapping attributes to columns, determining the depth within the XML document you will traverse, and more. The XML Spy documentation and tutorial cover these options in detail. Figure 27.4 illustrates how you create the tables using XML Spy.
Figure 27.4. XML Spy table creation.
After using XML Spy, you have tables in your database, but you still have quite a bit of work to do. The database conversion has not made any relationships, the names of primary keys should conform to your naming standards, and other relationship constraints need to be applied. In the future you should see significant improvements in moving from XML Schema to relational databases. Most of the tools today do a better job by using an existing database to create an XML schema. The final table structure produced using XML Spy is shown in Figure 27.5.
Figure 27.5. Final table design from XML Spy.
Caution
XML Spy will throw an error if your XML sample document has attribute or element names that conflict with SQL’s reserved names. If this occurs, you should simply rename the element or attribute and allow XML Spy to succeed. Afterward, change the guilty attribute or element back to your preferred name.
You will clean up the database schema manually and use the table structure illustrated in Figure 27.6 for the remainder of your work in the CRSS application. As you can see, the relational structure of the CRSS database is radically different from the XML hierarchical structure.
Figure 27.6. CRSS database model.
27.1.1. Mapping XML to Relational Databases
Mapping hierarchical XML schemas to relational database structures is inherently difficult. Consequently, maintaining this mapping is equally difficult. Many vendors are working on solutions for this problem. Microsoft, Oracle, IBM, Sun, and others have been XML-enabling their databases for the past few years. XML schemas have been the key ingredient that was missing to allow a simple integration between the XML hierarchical structure and the relational structure.
Microsoft updates SQL 2000 with more XML capabilities periodically. The CRSS case study uses SQLXML3, which was released in March 2002. If SQL 2000 is your database environment, watch for more tools at http://www.microsoft.com/sql/techinfo/xml. Oracle has a wealth of information on its Web site at http://www.oracle.com/ regarding tools for mapping XML to Oracle data.
You are going to map the XML reservation request message that you saw in the previous chapter (reprinted in Listing 27.1) by using a Microsoft tool called the SQL XML View Mapper, available online at http://www.microsoft.com/sql.
Listing 27.1. Reservation Request XML Message Reprinted
<?xml version="1.0" encoding="utf-8" ?>
<reservation_request>
<reservation building="Linden Hall" room="Gymnasium"
start_date="2001-02-23" start_time="22:30:00"
duration="5" description="Varsity basketball"
configuration="basketball"/>
<security>
<requestor first="Paul" last="Lamere"
email="[email protected]" type="coach"/>
</security>
<building id="1" name="Linden Hall"
location="images/linden.gif">
<room name="Gymnasium" capacity="100">
<form id="201" name="legal"
url="forms/legal.html"/>
<form id="202" name="rental_contract"
url="forms/rental.html"/>
<form id="203" name="cost_contract"
url="forms/costContract.html"/>
<resource_owner first="Ed" last="Agenda"
email="[email protected]"
type="administration"/>
</room>
</building>
<workflow>
<request view="xslt/request.xslt" status="false"/>
<approval view="xslt/approval.xslt" status="false"/>
<viewCalendar view="xslt/viewCalendar.xslt"
status="false"/>
<viewPending view="xslt/viewPending.xslt"
status="false"/>
</workflow>
</reservation_request> |
With the SQL XML View Mapper, you can graphically create a mapping schema between your XML message and the database tables.
The following software must be installed as a prerequisite to the use of SQL XML View Mapper:
SQL 2000
Microsoft Windows 2000 (any edition)
Microsoft Installer v2.0+
After correctly installing the XML View Mapper, you can launch it and start working on your mapping schema, as shown in Figure 27.7.
Figure 27.7. CRSS database model.
The SQL XML View Mapper only accepts Microsoft-specific XDR schemas: SQL XML View Mapper does not accept Schema-Recommendation-compliant schemas. XML Spy has the capability to perform the translation; after translating, you can load the reservation request XDR schema. Microsoft uses its proprietary XDR schemas to map SQL 2000 databases to XML. XDR schemas are not an Internet standard. Figure 27.8 shows the initial view that is presented when the SQL XML View Mapper is launched.
Figure 27.8. XDR Schema (partial).
<?xml version="1.0"encoding="UTF-8"?> <!--XML-Data generated by XML Spy v4.3 U (http://www.xmlspy.com)--> <Schema name="Untitled-schema"xmlns="urn:schemas-microsoft-com:xml-data"xmlns:dt="urn |
:schemas-microsoft-com:datatypes">
<ElementType name="reservation_request"model="closed"content="eltOnly"order="seq">
<AttributeType name="xmlns"dt:type="string"/>
<attribute type="xmlns"/>
<group minOccurs="1"maxOccurs="1"order="seq">
<element type="reservation"minOccurs="1"maxOccurs="1"/>
<element type="security"minOccurs="1"maxOccurs="1"/>
<element type="building"minOccurs="1"maxOccurs="1"/>
<element type="workflow"minOccurs="1"maxOccurs="1"/>
</group> Note
Microsoft’s SQLXML3 does support XML schemas and has a tool to assist in converting XDR schemas to W3C-compliant XML schemas. Unfortunately, the SQL XML View Mapper tool only uses XDR schemas. You will use the SQL XML View Mapper tool to learn about mapping XML hierarchical data to relational database data. You will then return to using Schema-Recommendation-compliant schemas. You can also create the XDR mapping schemas using XML Spy if you find that using the SQL XML View Mapper is cumbersome. The tool is quite powerful but also quite difficult to master.
It’s easy to see that XDR schemas have similiarities to the Schema-Recommendation-compliant schemas. Microsoft has stated that they will be updating their tools to conform to the XML Schema Recommendation, so perhaps this step will not be required in the future.
Now that you have an XDR schema, you can take another look at the XML Mapper tool. Figure 27.9 shows a partial listing of the XDR schema that the SQL XML View Mapper created.
Figure 27.9. XML Mapper loaded with XDR schema.
You can see from Figure 27.9 that the SQL XML View Mapper has automatically found several matches between our SQL schema and the reservation request XDR schema. The only action required for the XML Mapping tool to get started was connecting to the CRSS database and the XDR schema. Now you must manually set some matches and accept or reject others.
The first step in creating a successful mapping is to map the element types to their corresponding tables. Then you map the attribute types to their respective fields. Finally, you look at mapping attribute types across tables, resulting in implicit join operations. Figure 27.10 illustrates the mapping results thus far.
Figure 27.10. XML Mapper with tables mapped to elements.
Notice what occurs when you map one of the elements to its respective table. Additional information is added to the mapping XDR schema, using the sql namespace. The mapping information is shown in Listing 27.2.
Listing 27.2. Reservation Element XDR After Mapping
<ElementType name="reservation"
model="closed"
content="empty"
order="many"
sql:relation="reservation"> |
You can see the newly added relationship in the last step of Listing 27.2. This is how Microsoft uses XML schemas to provide additional mapping information. These additions allow XML technologies to be used from anywhere. In fact, Microsoft’s latest SQLXML3 release allows us to expose any database query as a Simple Object Access Protocol (SOAP)-based Web service. This is beyond the scope of this chapter; however, it will be an exciting area as business-to-business partnerships grow, using HTTP and HTTPS for data transport.
Note
The XML Mapping tool allows objects other than tables to be mapped. Tables, views, and columns can be mapped to XDR element types and attribute types.
The best way to ensure that you have mapped all of your XDR elements and attributes is to right-click anywhere on one of the XDR elements. This opens a context-sensitive menu where you can select the Show Pending Mappings menu item. You can then continue mapping until all XDR mappings are completed. You can appreciate the tool more after viewing the complete mapping shown in Figure 27.11.
Figure 27.11. Completed mapping of XDR to SQL.
The XML Mapping tool has created all the necessary relationships to let you deal with SQL tables as if they were XML, and vice versa. You can examine one of the relationships in more detail to understand what the tool is doing behind the scenes.
If you look at a partial mapping of the reservation ElementType element shown in Listing 27.3, you can see several uses of the sql namespace. The ‘sql’ prefix applies only to one namespace in the document’s root element namespace declaration. The declaration was left out for the sake of brevity. You are only going to look at the building and room fields within the XDR mapping. Each of these represents a mapping that was done by the SQL XML View Mapper tool.
Listing 27.3. Partial XDR-to-SQL Mapping Result
<attribute type="building"
required="yes"
sql:relation="building"
sql:field="name" >
<sql:relationship key-relation="reservation"
key="id_building"
foreign-relation="building"
foreign-key="id_building" >
</sql:relationship>
</attribute>
<attribute type="room"
required="yes"
sql:relation="room"
sql:field="roomName" >
<sql:relationship key-relation="reservation"
key="id_room"
foreign-relation="room"
foreign-key="id_room" >
</sql:relationship>
</attribute> |
When you examine the results shown in Listing 27.3, the purpose of the tool becomes clearer. You can see that the building attribute type’s relationships are being defined. The attribute type building is mapped to the building table’s name column. Looking a bit further down in the listing you can see that the reservation table is joined to the building table using the id_building foreign key. The XDR schema enables SQL server to enforce referential integrity rules when data is being added, deleted, or updated using a XML source.
The latter half of Listing 27.3 illustrates another relationship. This time the room attribute type is mapped to the roomName column in the room table. The foreign key relationship is specified showing that the id_room column in the reservation table is acting as a foreign key linking the reservation and the room tables.
The sql:relation annotation is added to map an element in the XML schema document to a column in a database table. The name of a table (view) is specified as the value of the sql:relation annotation.
Microsoft refers to attributes like sql:relation and sql:field as annotations in their documentation. This term should not be confused with XML schema annotation elements that are used to provide documentation within a schema. To be consistent with Microsoft’s documentation on XDR schemas this chapter will use the term “annotate” when referring to XDR mapping attributes.
When sql:relation is specified on an element type, the scope of this annotation applies to all attributes and subelements described in the complex type definition of that element, providing a shortcut in writing annotations.
The sql:relation annotation is also useful when identifiers that are valid in Microsoft SQL Server are not valid in XML. For example, say you have a table named Reservation Request. This is a valid table name in SQL Server but not in XML. In these instances, the sql:relation annotation can be used to specify the mapping, as in this example:
<xsd:element name="resRequest"
sql:relation="[Reservation Request]"> The sql:field annotation is added to map an XML attribute type or element type in the schema to a database column. You cannot specify sql:field on an empty content element.
27.1.2. Testing the SQL Mapping
Now that you have mapped your reservation request to the SQL tables, what can you do with it? You can load some data into the database tables and begin to execute XPath queries against the database. All you have to do is select the XPath Query tool from within the XML Mapping program by pressing F5 and begin entering queries. If you issue a simple XPath query as shown in Figure 27.12, you will get the result displayed.
Figure 27.12. XPath query example.
Notice that the XPath query is configured as if you were querying the XML data. Remember that you do not even have any XML data in the tool—you only supplied the XDR schema. The result of the XPath query is a well-formed XML document that conforms to your XDR schema. You now can query, using XPath for all reservations, for a particular room in a certain date range and get the results as XML.
You might be wondering why you should bother with getting the data as XML. You could simply continue to deal with the database and get data back and deal with it as before. The advantage of XML over traditional database techniques is that it provides structure for data that is inherently unstructured. As a result, you have a way to manipulate data and use frameworks such as Microsoft’s .NET or Sun’s J2EE.
You also can expose certain data as Web services that will facilitate many capabilities. Imagine not having to completely redo the security module at your company for each new application. Simply create one server-based security module by using the database and expose the login as a Web service. All developers anywhere in the world can use it. If scalability is required, add more servers to the Web farm. XML provides extensibility that makes these new database capabilities exciting.
27.1.3. Database Mapping Summary
You now have a basis for working with the database. You have seen how to use the SQL XML View Mapper with XDR schemas to create the illusion that the SQL Server is merely a large XML document. You finish your work with the SQL XML View Mapper by exporting your XDR mapping schema so you can reuse it in your application later.
You know from the work you did in the previous chapter that you can receive data from the Web browser into your ASP.NET page. Now you can take this data and insert the request into the database, using the mapping schema you just created. To accomplish this, you need to create custom code using Visual Basic .NET to do the following:
Receive HTML form data
Package the form data into an XML message
Validate the XML message using your XML schema
Insert the reservation request into the database using the mapping schema