My project is to model a small book collection. This is a valuable tutorial because it contains multiple tables and is realistic enough to serve as a foundation for larger data model and data access layer development. I will first generate the SQL to build this database and then generate C# and PHP database access layers for the database.
C# and PHP are proven application development technologies and they are representative of both a latently-typed scripting language and a strongly-typed system languages. With this example in hand you will be able to generate your database application layer in the language of your choice; you will not be bound to the technology that you begin with.
The model should include the name of the book, the author, and the publisher. Using a normalized relational model, I will need one table for each of those fields. These tables relate to each other as shown in Figure 1.
Figure 1. Tables: You'll need three tables to hold the information for the book database.
shows a model of Figure 1 in XML.
For simplicity I've combined the table definition, the connection information for the database, and the test data, into one file.
More detail about different types of XML nodes is provided in the following table:
The database node includes attributes which describe the connection to the database.
A table node defines a table in the database. It must include a <field> node for each field in the table. This <field> node has the name of the field and it's type.
If a <select> node is included then this node defines a query that should be associated with the table. The <select> node must include a <query> node that defines the SQL for the query, and a set of <param> nodes for each run-time parameter.
A data node defines a row of test data. It includes the name of the table to insert the data into, as well as a set of <field> nodes which include the data for each field in the row.
Your application may require more information to properly model your database layer. You can easily add this to the code generation templates in this article.
With the data model and test data in hand I can start building the code generators that will turn the design into reality. I've chosen PHP and C# as my generation targets, but I'll also generate SQL for the physical model so that the database schema always remains in sync with the database access layer sitting above it. For the generator I have chosen to use XSLT.
Generating Code with XSLT
XSLT is not just about converting one XML form to another or converting XML to HTMLit can also be used for code generation by building source code as text.
The value of XSLT is that it is a standard and has implementations in a number of languages, making it very accessible. There are a number of books and articles on XSLT. It also helps that with version 1.1 you can create multiple output files with a single XSL template. I'll start with a simple example stylesheet called 'simple.xsl':
<xsl:stylesheet version="1.1" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />
CREATE TABLE <xsl:value-of select="@name" />( );
For this article I've used the Saxon XSLT processor (v.6.5.3)
. To run 'simple.xsl' on the 'schema.xml' input file the command is:
> java jar saxon.jar schema.xml simple.xsl
The output looks like this:
CREATE TABLE Book( );
CREATE TABLE Author( );
CREATE TABLE Publisher( );
In the process above, the XSL processor searches the templates against each of the nodes it encounters in the input XML. In this case it matches against <table>
nodes within the <schema>
node. It finds the <xsl:template>
node because of the match="schema/table"
attribute. Within the template the text is output to standard output. The <xsl:value-of>
node tells XSL to output the value of the name attribute into the standard output. The name attribute is specified by use of the select="@name"
attribute. The '@' sign tells XSLT to look for the attribute as opposed to an interior node. The XSL template registers its intention to create text (instead of XML or HTML) by using the method="text"
attribute on the <xsl:output>
My guess is that the reason that people often find XSL difficult is because it uses an event-driven coding technique rather than an imperative one (e.g. C, Java, etc.). In Java you say, "Open the XML file and find this node then do x," in XSL you say "when you see this node do x."
The next step is to finish the implementation of the SQL generator.