Extend DeZign's reporting capabilities using XSL transformations

Article contributed by Robert Allen of SlaphappyGeeks.

XSLT: The Connection Part 1

XSLT provides an easy yet powerful way to get additional value from your Dezign for Databases projects! This article will help show you how.

What is XSLT?

XSLT is XSL Transformations, a language specification for transforming XML documents into other forms, including other XML documents. Find the complete specification at W3C.ORG. From that page: "...one of the important roles of XSLT is to add styling information to an XML source document, by transforming it into a document consisting of XSL formatting objects… or into another presentation-oriented format such as HTML, XHTML, or SVG. However, XSLT is used for a wide range of transformation tasks, not exclusively for formatting and presentation applications."

The key idea to understand is this: Any XML document may be transformed into another document, XSLT is the language used to describe those transformations.

What is the connection between XSLT and Dezign for Databases?

When you create a project using Dezign For Databases your project data are stored in XML form in a file with the filename extension .dez, we will call this the project file, or dez file if you prefer.

Because the project file is XML it may be transformed into another document using XSLT. For example, you may transform, or generate custom reports directly from your project files. And because the XSLT is reusable you may use it to produce the same report for any of your Dezign For Databases projects.

Why use XSLT?

Dezign For Databases has really good report generation capability built in, so why might you want to use XSLT? XSLT allows you to extend the usefulness of your project’s design data to meet your own unique requirements. For example you may want to:

• Generate reports in your own presentation format
• Generate reports of a specific subset or view of your project data
• Generate reports that include data from multiple projects
• Insert project data directly into a different overall context
• Allow batched generation of snapshot reports across multiple projects
• Perform other, non-report transformations of your project data

In short, Dezign For Databases is the most useful application available for graphical database design – XSLT is a tool for extending the usefulness of your design data even more!

XSLT: Exploring The Schema

In order to write an XSLT (often called an XSL Stylesheet) to transform a Dezign for Databases project file, you must know something about the schema, or structure of the project file itself.

Datanamic does not have complete documentation available for the project file schema at this time, but it is not difficult to construct a working schema by looking at an example project file.

In this article we will take a first look at project file structure. In doing so we will mention just enough of the rules of XML to make this page complete so that you will be able to follow the discussion. But teaching XML is beyond our present scope, so if you are not already familiar with XML notation you should learn more about it from online sources, or from any of the excellent print resources available.

Project file structure

All XML documents have a tree structure, and at the root or outermost level they have one and only one document element. All other elements are children of this document element. All Dezign for Databases project files have an element named dezign as the document element. Note also that element names are case sensitive.

Below is a graphic of a somewhat simplified tree structure found in an actual Dezign for Databases 5 project file. By convention it is inverted so that the root is at the top of the diagram.

Connected elements are child elements if lower, parent elements if higher in the diagram. Immediate child elements of the same parent are sibling elements. Grey elements represent additional elements which may or may not be present in any given project file.

You should examine one of your own project files to learn the complete structure, but here are a few quick notes to get you started.

The first VERSION element in a project file is always the current project version. If you have archived other versions of your project they will be found in sibling VERSION elements lower in the file. Archived versions will also have a VERSIONINFO element which the current version will not have.

The most useful Data Dictionary information from your project will be found within the DATADICT child of each VERSION element. The children of the DATADICT element include DOMAINS, ENTITIES, SEQUENCES, PROCEDURES, RELATIONSHIPS, VIEWS and others, only a few of which are shown in the graphic above.

You probably noticed that most elements have names that reflect their use. Some are a little more cryptic. For example, ENT elements (project entities) will usually have a PKCON element which describes the primary key constraint. ATTR elements (entity attributes) may have an NNCON child element which describes a Not NULL constraint.

Many elements such as DOMAIN, ENT, ATTR and others will have a number-valued ID element which is used to establish relationships and to define subcategory connections, among other things. You will also find other elements such as DTLISTNAME (which refers to a data type defined in an external list for each target RDBMS).

Exploring the XML structure

When trying to identify the element associated with a particular project parameter you will find two things to be very helpful – and you already have the tools!

• Create a simple example project with only the items you want to identify
• Open the example project file in an XML editor or a web browser

Creating a simplified example project, will allow you to quickly identify specific elements associated with project parameters, and to understand the relationships between XML elements. For example, to understand how relationships are represented in the project file, create an example project with a few simple entities and relationships, then look at the project file. Change the relationship type and see how the XML changes. Simple!

Opening a project file in an XML editor or web browser, will allow you to readily visualize and explore the XML structure. If you do not have an XML editor, all recent web browsers will display XML in a useful, navigable format and make an easy to use development tool!

XSLT: Writing A Transform

In this article we will explore the general method of writing an XSLT for a Dezign for Databases project file. In doing so we will introduce just enough of the syntax of XSLT (and XPath*) to allow you to follow the discussion, but certainly not enough to teach it to you. We recommend that you learn XSLT and XPATH from an online source, or from one of the many excellent print resources now available.

(* XPath is the syntax used to navigate the tree structure of XML documents, find it at W3C.ORG)

Project and version summary report

For a relatively simple but non-trivial example, we will write an XSLT to generate a plain text summary of project versions from a Dezign for Databases project file.

To begin, we need to know how this information is structured in the project file XML. Looking at our example file we find the tree structure represented graphically below (rooted at the left edge for better formatting). The blue elements represent the current version and the named grey elements represent one or more archived versions. The unnamed grey elements show other branches of the tree not of interest to us here.

Writing the XSLT

We will begin by creating an empty transform, or stylesheet, then adding our processing instructions.

Every XSLT must, itself, be a valid XML document with XML declaration and an xsl:stylesheet element as the one and only document element. We will also need to set the output parameters with an xsl:output element. Everything else will be a child of the xsl:stylesheet element. So our valid, empty, stylesheet looks like this…

Transformation instructions consist primarily of template elements which correspond to the elements of the document to be transformed. XSLT processing begins with the document element of the XML source document, dezign in our case. So we must add an xsl:template element to handle processing of the content of the dezign element of our project file.

We have included an xsl:choose element to test for the version of our project file. If it is ‘41’, the correct value for Dezign For Databases 5 projects, the transform will continue by processing of any child VERSION elements. Otherwise it will write an invalid file format message to the output and end.

Now we need to add a handler for those VERSION elements. Here, we need to choose between the current version and any archived versions in the project file. To do this we check each VERSION for a child VERSIONINFO element. If it has one, it is an archived version, otherwise it is the current version.

For archived versions we process the VERSIONINFO element first, then the PROJECTSETTINGS element. For the current version we simply process the PROJECTSETTINGS element after writing a current version heading.

Finally, we add the template elements to handle the VERSIONINFO and PROJECTSETTINGS elements.

That is it! We are now ready to generate a formatted, plain text summary of the current and archived versions of any Dezign For Databases 5 project!

If you are following along with this example simply save the XSLT text described above to a file named versions.xsl and proceed to the next article in this series to see how it all works!

XSLT: Using the Transform

In this final article in the series we will use the XSLT stylesheet we created in the previous article to generate a report from a Dezign For Databases project file.

The XSLT processor

Transformation using an XSLT stylesheet will require that you have an XSLT processor. If you do not already have one there are several available in both commercial and free versions. For convenience, here are a few links to get you started…

• Saxonica, home of the commercial Saxon-SA XSLT processor
• Saxon-B, home of the free Saxon-B XSLT processor
• Xalan J, home of the free Xalan Java XSLT processor

We will use the Xalan command line syntax in the examples that follow, but consult the documentation for your own version to use the correct commands for your system.

Transformation

To transform a project file named example.dez using our versions.xsl XSLT, and save the result to report.txt, our command line syntax would look something like this…

shell> [ path to xalan ] example.dez versions.xsl report.txt

... or, depending on system configuration…

shell> java [ path to xalan ] example.dez versions.xsl report.txt

That is all!

The report

What does the result look like? Here is a versions report generated from an actual example Dezign For Databases 5 project file using the versions.xsl XSLT from our previous article:

Conclusions

As stated in the first article in this series, “Dezign for Databases is the most useful application available for graphical database design – XSLT is a tool for extending the usefulness of your design data even more!”

We hope that we have demonstrated that in a clear way.

We have given only a simple example in this series of articles, but doubtless your knowledge of your own project requirements, and your imagination, will allow you to see many other uses for this simple technique!

Article contributed by Robert Allen of SlaphappyGeeks

---

Resources

Learn

• DeZign for Databases: Learn more about DeZign for Databases.
• Brighten up your data model diagrams: give them color.
• Getting started with DeZign for Databases

Get products and technologies

• Build your next data model with DeZign for Databases trial software, available for download directly from Datanamic's download section.