In most of the line-of-business applications I write, the same table description turns up in at least four places: the CREATE TABLE script, the read queries, the HTML insert form, and the CGI (Common Gateway Interface) code that validates and stores the data. Keeping these copies aligned by hand is the error I see come back most often. A column added to the schema but forgotten in the form, a NOT NULL constraint present in the database but unchecked by the application: trivial faults, frequent, and surfacing only once the program runs.

For a while now I have worked another way: I describe each table once, in a metadata file, and generate the other artefacts from there. Here I set out how the arrangement is built, where it is weak, and where generation stops.

Context

Generating code from a data description is not a new idea. CASE (Computer-Aided Software Engineering) tools and 4GLs have turned out screens and procedures from a data dictionary for years. What has changed is the availability of free, documented parts to build a generator of one’s own, without having to adopt a closed proprietary environment.

XML 1.0 has been a W3C Recommendation since 10 February 1998 and gives a neutral syntax for describing tree structures, with the DTD (Document Type Definition) as the validation grammar built into the specification itself. XSLT 1.0 has been a Recommendation since 16 November 1999 and is a language that transforms XML documents into other XML, into HTML or into text. The two together suffice to build a code generator without writing a parser: the table description lives in an XML document validated by a DTD, and each artefact to be produced is the result of an XSLT stylesheet applied to that document.

Architecture

The source file describes the tables declaratively. A realistic fragment:

<schema>
  <table name="customer">
    <column name="id"     type="integer" pk="yes"/>
    <column name="name"   type="varchar" size="120" null="no"/>
    <column name="vatno"  type="char"    size="11"  null="no"/>
    <column name="agent"  type="integer" fk="agent.id"/>
  </table>
</schema>

From this single document I derive three families of output, each with its own XSLT stylesheet:

  1. SQL DDL (Data Definition Language). One stylesheet walks the table and column elements and emits CREATE TABLE, types and the PRIMARY KEY, NOT NULL and FOREIGN KEY constraints. The match between the abstract types (integer, varchar) and the concrete database types is a mapping table inside the stylesheet: so the same schema produces DDL for different dialects by changing only the stylesheet.

  2. HTML forms and lists. A second stylesheet generates, for each table, the insert and edit form and the paginated list. A column’s type and size pick the HTML control: a short varchar becomes an input, a long text a textarea, an fk a drop-down menu filled from the referenced table.

  3. CGI access code. A third stylesheet emits the SELECT, INSERT and UPDATE functions with already-typed parameters, plus the server-side validation block drawn from the constraints (NOT NULL, maximum length, presence of the foreign key).

It is a separation between abstraction layers: the schema states the what, each XSLT stylesheet states the how for one target. To change the convention of every list — to put column sorting everywhere, say — I touch one stylesheet, not N already-generated pages.

Critical point

Full generation holds as long as the problem is regular. The CRUD (Create, Read, Update, Delete) part of a business application is: it shows rows, inserts them, edits them. Business rules almost never are. A due-date calculation that depends on the customer type, a validation that crosses three tables, an approval flow: none of this comes out of the data schema, because the schema does not contain it.

Hence the design decision that weighs most: keep generated code apart from hand-written code. If I mix them in the same file, the first regeneration loses my manual changes and in practice I stop regenerating — the generator shrinks to throwaway scaffolding. The two routes I have seen hold up are inheritance, where the written code extends a generated base class, and inclusion, where the generated file includes a manual file for the extension points. In both cases the rule is that the generated file is never edited by hand: you edit the schema or the stylesheet, and regenerate.

A second critical point is migration. Generating the day-one CREATE TABLE is easy; producing the ALTER TABLE that carries a schema from the old version to the new one means comparing two versions of the document, not transforming one. It is a difference computation, beyond the reach of a single XSLT stylesheet applied to a single input, and in my projects it stays manual work, reviewed at each release.

Implications

What you gain is consistency more than speed. If form, query and DDL come from the same document, a column added to the schema appears in all three after a regeneration, and the fault class “in the database, missing from the form” disappears by construction. It is the same consistency that normalisation gives a data model, carried over here to the artefacts that descend from that model.

Where the domain description lives changes too. The XML schema reads plainly, versions under an ordinary version-control system, diffs line by line, and is independent of the concrete database. It stops sitting hidden inside the code and becomes a document in its own right, one you cite and review.

Limits

Schema-driven generation resolves the structural layer and stops there. I would not know how to write a non-trivial business rule into the declarative schema, and I do not think it is worth trying: past a certain threshold, a rule language powerful enough to cover the real cases reads worse than the code it means to replace. Generation pays off where little specificity yields a lot of code — the CRUD and access layers — and stops paying off where that balance tips the other way.

It remains the case that generated code is worth exactly as much as the XSLT stylesheets. A badly written stylesheet produces badly written code wholesale, across every table, silently. The transformation stylesheet thus becomes the most critical artefact of the project, and deserves the same care as the code it generates: reviewed, versioned, and tried against a reference schema before being applied to real ones.

https://www.w3.org/TR/1998/REC-xml-19980210 https://www.w3.org/TR/xslt-10/ https://lists.w3.org/Archives/Public/w3c-news/1999OctDec/0003.html

Cover image: Flow diagram: an XML document and an XSLT stylesheet feed an XSLT processor that produces an output document — diagram by Dreftymac, CC BY-SA 3.0 — https://commons.wikimedia.org/wiki/File:XSLT_en.svg