Generate SQL with XSLT 2.0

Assume that you are the engineering team leader for a new PHP project and that the prerequisites are met, and that the initial data model includes approximately 150 tables. It's time to think about the schedule. It is estimated that you can write a database access class and complete its unit tests every day, so 150 tables take 150 days. Assuming 20 days of work per month, is it going to take nearly eight months to write the database access layer?

This may not work, and there needs to be a way to shorten the time required. You can use the persistence framework, but even if the framework can halve the time, it still takes four months. You can also write common class libraries, but such libraries are complex to use, and are very error-prone and difficult to debug.

You can also write one or two sample classes, and then use the Code Builder to create additional classes. In the worst case scenario, it could take one months to write the generator itself. But after that, it takes only a few minutes for all the code to build. In addition, the flaw caused by the mismatch between the SQL schema and the database access code can be avoided entirely because the SQL schema and access code are created by the generator.

Is such a plan feasible? Of course, this article tells you how to do this and explains why the application is easier to maintain, more robust, and easier to write. This series has two articles, part 1th introduces the basics of code generation, and then discusses part of the build builder to create SQL for a given model. Part 2nd will drill down into the XSL and explain how to complete the generator by traversing the code that creates PHP.

Code Generation Basics

First, we need a generator that accepts the abstract model of the database and establishes the SQL schema and the PHP database access class. Figure 1 illustrates this model and shows the possibility of generating database code for other technologies, such as Java™ code.

Figure 1. Basic flow of generators

The Dotted box (SQL model and Database access model) in Figure 1 represents a temporary model created from an abstract model. The Dotted box (Java) represents another possible model.

The next question is what technology to use. You can write generators using Java code, Perl, Python, or Ruby. Because this task is simpler, you can also use a template engine such as Velocity or XSLT to implement the task. The template engine such as XSLT 2.0 is a good starting point, not only is it powerful, but it can also be embedded into Java code for further expansion.

XSLT 2.0

XSLT 2.0 provides a wide range of language capabilities for developers looking for a code generation template engine. This article uses these features to build a robust generator that generates SQL and PHP from an abstract table definition. Only one-tier transformations can be used in the XSLT 1.x, but in XSLT 2.0 it is possible to build an intermediate model that will generate code. This feature makes it easier to understand and maintain the builder, and can redirect the build to other target languages.

As an XML transformation technology, XSLT can transform XML into other XML or text. The two tools are used in our code generator project, and XSLT 2.0 has made a lot of improvements to the XSLT 1.x, and this article will use the three new features in the standard second edition:

Function: You can now customize the function. This is a significant improvement for the first edition of XSLT, and the template syntax used in the first edition is too verbose.

Middle Tree: The first version of the XSLT can only operate on the input XML tree. XSLT 2.0 can create an intermediate tree in memory and then use it to derive other templates.

Results document: Now a single template can generate multiple output files. I use this feature to generate SQL files and separate PHP files.

The following describes how to build a builder.

Build Builder

Starting with the input from the generator, listing 1 provides examples of table definitions for a simple library database.

Listing 1. The abstract table definition entered

<?xml version="1.0" encoding="UTF-8"?>
<tables>
　　 <table name="Author">
　　　　 <field name="first" type="text"/>
　　　　 <field name="last" type="text"/>
　　 </table>
　　 <table name="Publisher">
　　　　 <field name="name" type="text"/>
　　　　 <field name="last" type="text"/>
　　 </table>
　　 <table name="Book">
　　　　 <field name="name" type="text"/>
　　　　 <field name="author" type="id"/>
　　　　 <field name="publisher" type="id"/>
　　 </table>
</tables>