Contents
A while back, the company I work for instituted a daily build process which included automated testing of SQL deployment scripts. The process restored a recent copy of each customer's database, ran batches of change scripts on the database, and saved the results to SQL output files (one per database).
This article discusses how these raw output files were converted into a customized HTML error report per developer (containing only the errors found in their SQL scripts) and automatically e-mailed to the developers.
The attached code demonstrates this process in action. It also serves as a demo for three previous C# utilities (RegexToXml, TransformXml, and SendSMTP).
Extensive use was made of XSL transforms. I learnt quite a bit about XSLT, and I will be sharing some of my discoveries and tips in this article.
A while back, a colleague created a daily build process using FinalBuilder. As well as building the executable, this also tested the SQL deployment scripts on a recent copy of each customer's database.
Testing the deployment scripts on all customer databases was a great idea, as it significantly reduced the risk of on-site errors during deployment. When errors occur, a developer installing an upgrade on-site could be tempted to ignore non-critical script errors, or to create their own ad hoc fixes for the errors. In this way, unexpected errors in deployment scripts could lead to unnecessary schema divergence (as opposed to unavoidable schema divergence caused by customizations, country-specific legislation, etc.) So there is huge value in having rock solid deployment scripts.
My colleague's original intention was to do a crude check for errors in each output file. All output files with errors would be e-mailed to all the developers. So all developers would have to open each file individually and search for the error messages to see if they were responsible for any of the errors. This would obviously waste the developers' time and be somewhat of an irritation - particularly since errors will often occur in all of the databases, resulting in a large number of files to check.
My wife was going away for a 10 day business trip. So I decided to tackle this problem during the evenings and weekends while she was away.
My solution was to write three general purpose command line utilities, then use these to generate and e-mail a personalized error report to each affected developer.
The three utilities were:
- RegexToXml: to parse the SQL output files for errors and warnings, and output the results as a separate XML file for each database.
- TransformXml: a wrapper around .NET's
XslCompiledTransform class. XSL transforms were written to:
- Generate various batch files piecing together the process (batch files with a command per customer or developer were generated from the central Databases.xml and Developers.xml files.)
- Concatenate the XML files for the various customer databases.
- Re-order the XML nodes by developer, then by database, then by change script.
- Generate an HTML file per developer with details of the errors in that developer's change scripts (grouped by database).
- SendSMTP: used to e-mail the HTML file (as the body of the e-mail) to each affected developer.
I have already posted separate articles on each of these utilities (just follow the links above).
The current article pieces together the process. You can see the process in action using the attached demo code.
All batch files should be run from the \BatchFiles sub-folder.
Run GenerateChangeScriptErrorsToEMail.bat to see the HTML error reports being generated.
Run GenerateAndSendChangeScriptErrorReports.bat to see the entire process in action, including the e-mailing of the generated reports. But note... you will see .NET error messages appearing in the console window when the system tries to send the e-mails. This is because the imaginary developers in the demo all have imaginary e-mail addresses, and because the SMTP host address is equally imaginary! Further down, you will find instructions on how to customize the process for your own system. This will get rid of these error messages.
\Metadata
- MetaData\Databases.xml lists four imaginary databases: Alpha, Beta, Gamma, and Delta.
- MetaData\Developers.xml lists five imaginary developers: Andrew, Barbara, Chris, Diane, and Ed.
Note that the developers' names must be alpha-numeric with no spaces. [This is because the regular expression uses \w+ to match the developer's name.]
Developers.xml also contains an e-mail address for each developer.
\ChangeScriptResults
This contains the output files produced by running the SQL deployment scripts on each of the databases (well, to be honest, I put these files together by hand... but they are loosely based on parts of actual output files):
- AlphaResults.txt
- BetaResults.txt
- GammaResults.txt
- DeltaResults.txt
Here's a short extract from one of these files...
Andrew\Feb06\Feb20_ChartMetaDataColumn.sql
(49 rows affected)
Msg 2714, Level 16, State 4, Server dbserver, Line 2
There is already an object named 'CK_ChartMetaDataColumn_Side'
in the database.
Msg 1750, Level 16, State 1, Server dbserver, Line 2
Could not create constraint. See previous errors.
Andrew\Feb06\Feb20a_AddChartMetaDataColumn.sql
Chris\Feb06\Feb20a_SampleNumber.sql
(5236 rows affected)
Diane\Feb06\Feb20a_OperationsMenuItems.sql
(6 rows affected)
(1 row affected)
(25 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)
(0 rows affected)
(1 row affected)
(0 rows affected)
Ed\Mar06\Mar06_DelUpdateTrigger.sql
SQR Software has an in-house utility which concatenates developers' change scripts in the correct order to form the SQL deployment script. It also adds a PRINT statement before each change script, with the sub-path to the script (which is why you see the name of the script in the text file above). The sub-path starts with the developer's name, making it possible to know who to route the error message to.
\RegularExpressions
- ChangeScriptErrorsRegex.txt
This file contains the regular expression which extracts developer names, and change script file names and error messages from the output files. The unit tests included with the separate RegexToXml article demonstrate how this regular expression was constructed.
\Tools
Contains the three command line utilities (written in C#) with their supporting files:
- AndrewTweddle.Tools.RegexToXml.Core.dll
- RegexToXml.exe
- RegexToXml.exe.config
- TransformXml.exe
- SendSmtp.exe
Note that you will need the .NET 2.0 framework to install them.
\XslTransforms
- CreateBatchFileToGenerateChangeScriptErrorsPerDatabase.xsl
- MergeDatabaseChangeScriptErrors.xsl
- GenerateChangeScriptErrorsByDeveloper.xsl
- CreateBatchFileToGenerateChangeScriptErrorsForDevelopers.xsl
- HtmlMessageStyle.xsl
- GenerateChangeScriptErrorsForDeveloperAsHtml.xsl
- CreateBatchFileToSendChangeScriptErrorReports.xsl
\BatchFiles
The main batch file is GenerateAndSendChangeScriptErrorReports.bat which calls the other batch files and EXEs in the correct order.
There are 13 batch files in this folder. Why so many?
The main reason is not just for modularity, as you might have suspected. Instead, it's for ease of customization. Certain parts of the process depend on the list of developers and customers. These steps are placed in separate batch files, which are re-generated every time the process runs (based on the contents of the Developers.xml and Databases.xml files).
GenerateChangeScriptErrorsToEMail.bat is another important batch file. This generates the HTML reports without trying to e-mail them as well.
\WIP
The intermediate XML files are generated into this folder.
\Reports
The HTML files for each developer are generated into this folder, as well as a master report for the build administrator.
- ChangeScriptErrorsForAndrew.htm
- ...
- MasterChangeScriptErrorsReport.htm
\Notes
Contains some text files which may be useful for understanding the process better:
- BatchFileSequence.txt
- CustomizingTheProcess.txt
[Note: Also refer to Notes\CustomizingTheProcess.txt for more information]
If you simply run BatchFiles\GenerateAndSendChangeScriptErrorReports.bat, you will get a list of .NET error messages in the console window. These occur when the system attempts to e-mail the reports to the developers. This is because the SMTP server address is incorrect for your system. (Additionally, the e-mail addresses in MetaData\Developers.xml are all imaginary.)
MetaData\Developers.xml is the central place for updating the list of developers and their e-mail addresses. For demonstration purposes, you will probably want to change these addresses to point to your own e-mail address.
There is no centralized metadata file where you can update the SMTP server address though. Instead, you will need to open XslTransforms\CreateBatchFileToSendChangeScriptErrorReports.xsl and edit the following line to change the default value of the SmtpServerAddress parameter...
<xsl:param name="SmtpServerAddress" select="'MySmtpHost'" />
Alternatively, you can simply pass a value for this parameter on the command line. To do this, simply append the following text...
-a SmtpServerAddress=<YourSmtpServerAddressHere>
... to the single line in CreateBatchFileToSendChangeScriptErrorReports.bat.
The SMTP host address is also hard-coded in BatchFiles\SendMasterChangeScriptErrorReportToAdministrator.bat. Note that this batch file contains a hard-coded name and e-mail address for the build administrator. Fix both these issues at the same time by modifying the following text:
if exist "..\Reports\MasterChangeScriptErrorsReport.htm"
..\Tools\SendSmtp
-h MySmtpHost
-S "Master change script errors report"
-w "..\Reports\MasterChangeScriptErrorsReport.htm"
-t "Andrew@NoPlaceOnEarth.com|Andrew Tweddle"
-f AutomatedBuild@NoPlaceOnEarth.com
[Note that -h sets up the SMTP host/server, -S the subject line, -w the e-mail body in the HTML format, -t the "to" address, and -f the "from" address.]
Your SMTP host might require additional parameters, such as a user name and password. SendSmtp might not support all the parameters you require. If it doesn't support a particular parameter, you can create a config file (SendSmtp.exe.config) and add a section similar to the following:
<configuration>
<system.net>
<mailSettings>
<smtp ...>
<network>
...
</network>
</smtp>
</mailSettings>
</system.net>
</configuration>
For more information, look up the mailSettings element, SmtpSection, and SmtpNetworkElement in the MSDN library.
These steps should get the e-mailing part of the demo working. But a few more steps will be required if you wish to adapt this code to be part of your own daily build process.
Firstly, you will need to populate Developers.xml and Databases.xml in the MetaData sub-folder.
Secondly, the HTML reports contain links to the source code next to each SQL script. You will need to modify the process so that these links point to the correct place. Alternatively, you may need to remove one or both of these links if they aren't appropriate for your situation.
One link is to a web-based copy of the source code (currently WebSVN, a popular web client front-end to a Subversion repository).
The other link is to the file on the local hard drive (assuming all developers use the same folder structure - although there are ways around this if they don't).
You can find more details on modifying or removing these links in Notes\CustomizingTheProcess.txt.
See Notes\BatchFileSequence.txt for a consolidated breakdown of the contents of the batch files in the sequence in which they are called.
The following two diagrams also show the sequence in which the batch files are called, as well as the inputs and outputs to each step in the process. Solid arrows indicate the flow of control, while dotted lines show the flow of data.
[Yes, yes, I know I'm not using the flowchart symbols correctly! Although I am using standard flowcharting symbols, this is not meant to be a standard flowchart. To paraphrase Humpty Dumpty: "When I use a symbol, it means exactly what I choose it to mean - nothing more nor less." If this bothers you, then simply ignore the diagrams!]
The first diagram shows the steps in BatchFiles\GenerateAndSendChangeScriptErrorReports.bat:
And the second diagram drills down into GenerateChangeScriptErrorsToEMail.bat:
This was my first foray into XSLT - so I am still very much a beginner myself. But I'd still like to share some of the things I learnt about XSLT...
My favourite online tutorial is on ZVON. This is a great way of getting up to speed very quickly. Best of all, you can download the entire tutorial to your hard drive and use it as an ongoing reference.
There are also lots of excellent code samples at TopXml. I used these extensively to tackle some of the harder aspects of XSLT.
The MSDN library also has some useful reference material on both XPath and XSLT syntax. [Hint: search for "XPath" or "xsl:" in the index tab of the MSDN library.]
Visual Studio 2005 has great support for editing, and particularly debugging, XSLT.
XML Notepad is a free tool from Microsoft which I've only recently discovered. A particularly nice feature is its Find dialog box which can take an XPath expression. This can obviously be used to test your XPath. XML Notepad also has support for applying XSL transforms to an XML file, and for doing a diff of two XML files. Very useful!
While developing this code, I found I could operate quite comfortably with TransformXml, a batch file, and Notepad. But on occasions, I would also run TransformXml from within Visual Studio so that I could step through the XSLT file. The trick for stepping through the file is to:
- Open up the TransformXml solution in the IDE.
- Open up the property edit form for TransformXml.
- Go to the Debug tab.
- Edit the "Command line arguments" setting to call TransformXml with the "-g+" switch (which enables XSLT debugging mode).
- Place a breakpoint on the call to the
Transform method of XslCompiledTransform.
- Run TransformXml in debug mode.
- Step into the XSLT transform (e.g., using the F11 key).
Conditionals in an XPath expression are placed between square brackets. If you put a slash at the start of the conditional, it will search from the root of the XML document, not from your current context. So, as a general rule, don't start your conditionals with a slash!
Here's a small extract from WIP\MergedChangeScriptErrors.xml:
="1.0"="utf-8"
<ScriptErrors xmlns:file="urn:mscorlib">
<Alpha>
<ChangeScript>
<FilePath>
<Text>Chris\May05\May24b_GetVehiclecostingManagementData.sql
</Text>
<Developer>Chris</Developer>
</FilePath>
<ErrorOutput>
...
An XPath expression to retrieve all Developer nodes would look like this:
/ScriptErrors/*/ChangeScript/FilePath/Developer
But the path to databases which have a Developer element lower down would look like this:
/ScriptErrors/*[ChangeScript/FilePath/Developer]
Note that the slash (/) before ChangeScript has disappeared. This always catches me when I haven't done XPath for a while!
There's an example of this in XslTransforms\GenerateChangeScriptErrorsByDeveloper.xsl:
<xsl:template name="goToNextDatabase" >
<xsl:param name="developer" select="''" />
<xsl:param name="prevDatabase" select="''" />
<xsl:for-each
select="/ScriptErrors/*
[ms:string-compare(name(.), $prevDatabase, '', 'i') > 0]
[ChangeScript/FilePath/Developer=$developer]">
<xsl:sort order="ascending" select="name(.)"/>
<xsl:if test="position()=1">
This piece of code finds the next database (in alphabetical order, after $prevDatabase) which has a change script error for the given developer ($developer). Strip away all the complex string comparison code, and it's essentially the same XPath as the earlier example.
One of the harder problems I faced was to re-order the nodes, so that instead of having an XML file ordered by Database, then by ChangeScript, then by Developer, like this...
="1.0"="utf-8"
<ScriptErrors>
<Alpha>
<ChangeScript>
<FilePath>
<Text>Chris\May05\May24b_GetVehiclecostingManagementData.sql
</Text>
<Developer>Chris</Developer>
</FilePath>
<ErrorOutput>
...
...I would have the nodes in a hierarchy with Developer at the top, then Database, then ChangeScript. Like this:
="1.0"="utf-8"
<ScriptErrors>
<Developer name="Andrew">
<Database name="Alpha">
<ChangeScript name="Andrew\Nov05\Nov01_Scenario.sql">
...
The key constraint is that each developer must have only one Developer element. This cuts out some of the simpler approaches, which result in duplicates.
This must be a very common situation. Yet, I failed to find any discussion of it online, or in any of the samples I downloaded. In the end, I had to find my own solution. You can find it in XslTransforms\GenerateChangeScriptErrorsByDeveloper.xsl. Here's how it works...
XSLT has two separate uses for the <xsl:template> element.
If the element has a match="SomeXPathExpression" attribute, then the contents of the template are applied to nodes which match the XPath expression.
On the other hand, if it only has a name="SomeTemplateName" attribute, then it acts like a function call. Like normal function calls, this type of templates can call other templates. Templates can also call themselves recursively.
To solve the re-ordering problem, I created two templates which call themselves recursively:
goToNextDeveloper
goToNextDatabase
Here's the definition of goToNextDeveloper:
<xsl:template name="goToNextDeveloper" >
<xsl:param name="prevDeveloper" select="''" />
<xsl:for-each
select="/ScriptErrors/*/ChangeScript/FilePath/Developer
[ms:string-compare(., $prevDeveloper, '', 'i') > 0]">
<xsl:sort order="ascending" select="."/>
<xsl:if test="position()=1">
<xsl:call-template name="developerSection">
<xsl:with-param name="developer" select="." />
</xsl:call-template>
<xsl:call-template name="goToNextDeveloper">
<xsl:with-param name="prevDeveloper" select="." />
</xsl:call-template>
</xsl:if>
</xsl:for-each>
</xsl:template>
This finds all Developer elements where the developer's name comes after $prevDeveloper. These elements are sorted alphabetically. Then only the first node is processed. This way it doesn't matter if there are multiple nodes for a particular developer.
The code to process this first node works as follows:
- It calls the
developerSection template. This creates the Developer element and calls goToNextDatabase, which creates the Database child elements using a very similar pattern to goToNextDeveloper.
- Then it calls itself recursively, passing the current node's developer name as the
$prevDeveloper parameter.
This solves the problem of inverting the hierarchy... but if you know of a simpler way, please let me know!
XslTransforms\GenerateChangeScriptErrorsForDeveloperAsHtml.xsl generates the HTML reports. This transform takes a $developer parameter. This can either be the name of a specific developer, or it can be blank, in which case all developers are included (this is used to generate the master report which is sent to the build administrator.)
This transform file contains some useful and interesting snippets...
A named anchor is a point within an HTML document, which can be navigated to (using the # symbol and the name of the anchor). These can be very useful for creating a navigable table of contents. The following snippet can be used to generate a named anchor:
<xsl:template name="CreateNamedAnchor">
<xsl:param name="AnchorName" />
<xsl:text disable-output-escaping="yes"><a name="</xsl:text>
<xsl:value-of select="$AnchorName" />
<xsl:text disable-output-escaping="yes">" /></xsl:text>
</xsl:template>
[Note the disable-output-escaping attribute. This is essential for preventing the XSLT processor from automatically replacing symbols like "<" with "<" (which would obviously ruin the HTML).]
This template is called in a variety of places. For example, here is the snippet which generates the heading for a particular developer:
<xsl:template match="Developer">
<xsl:if test="(@name=$developer)or($developer='')" >
<xsl:call-template name="CreateNamedAnchor">
<xsl:with-param name="AnchorName" select="@name" />
</xsl:call-template>
<h1>Change script errors for <xsl:value-of select="@name"/></h1>
<xsl:apply-templates/>
</xsl:if>
</xsl:template>
The following snippet is useful for creating hyperlinks, either to external documents (such as the WebSvn web page for the source code file), or to the named anchor:
<xsl:template name="CreateLink">
<xsl:param name="LinkRef" />
<xsl:param name="LinkText" />
<xsl:text disable-output-escaping="yes"><a href="</xsl:text>
<xsl:value-of select="$LinkRef" />
<xsl:text disable-output-escaping="yes">"></xsl:text>
<xsl:value-of select="$LinkText" />
<xsl:text disable-output-escaping="yes"></a></xsl:text>
</xsl:template>
A major challenge I encountered was in copying the SQL output results to the web page. It was possible that there would be new line characters in the outputs. Since HTML ignores these characters, one would find extracted text such as the following...
(4 rows affected)
(1 row affected)
(10 rows affected)
(1 row affected)
... being displayed like this in the HTML page ...
(4 rows affected) (1 row affected) (10 rows affected) (1 row affected)
Fortunately, I found a very elegant solution in Mike Brown's lf2br template. This replaces line feed characters in an input string with <br/> tags. This solved the problem.
lf2br also helped me to solve another problem. First, I adapted it to create a general purpose replaceText template...
<xsl:template name="replaceText">
<xsl:param name="StringToTransform"/>
<xsl:param name="TextToReplace" />
<xsl:param name="ReplacementText" />
<xsl:choose>
<xsl:when test="$TextToReplace=''">
<xsl:value-of select="$StringToTransform"/>
</xsl:when>
<xsl:when test="$TextToReplace=$ReplacementText">
<xsl:value-of select="$StringToTransform"/>
</xsl:when>
<xsl:when test="contains($StringToTransform,
$TextToReplace)">
<xsl:value-of
select="substring-before($StringToTransform,
$TextToReplace)"/>
<xsl:value-of select="$ReplacementText" />
<xsl:call-template name="replaceText">
<xsl:with-param name="StringToTransform">
<xsl:value-of
select="substring-after($StringToTransform,
$TextToReplace)"/>
</xsl:with-param>
<xsl:with-param name="TextToReplace">
<xsl:value-of select="$TextToReplace" />
</xsl:with-param>
<xsl:with-param name="ReplacementText">
<xsl:value-of select="$ReplacementText" />
</xsl:with-param>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$StringToTransform"/>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
...then I used replaceText to help generate links to the source code in WebSvn (a popular web-based front-end to Subversion repositories).
Folders in the subpath to the change script file are separated by backslashes (\), but web folders are separated by forward slashes (/). replaceText was used to replace the backslashes with forward slashes.
It also proved useful for substituting the actual file name for the placeholder text (FILEPATH) in two strings which define the format for WebSVN addresses and for the local working copy file path.
The following code snippet demonstrates calling replaceText (as well as CreateLink, which was discussed earlier)...
<xsl:template name="CreateLinkToSourceCode">
<xsl:param name="ChangeScriptName" />
<xsl:text> </xsl:text>
<xsl:variable name="subPathToChangeScript">
<xsl:call-template name="replaceText">
<xsl:with-param name="StringToTransform"
select="$ChangeScriptName" />
<xsl:with-param name="TextToReplace" select="'\'" />
<xsl:with-param name="ReplacementText" select="'/'" />
</xsl:call-template>
</xsl:variable>
<xsl:call-template name="CreateLink">
<xsl:with-param name="LinkRef">
<xsl:call-template name="replaceText">
<xsl:with-param name="StringToTransform"
select="$ChangeScriptUri" />
<xsl:with-param name="TextToReplace"
select="'FILEPATH'" />
<xsl:with-param name="ReplacementText"
select="$subPathToChangeScript" />
</xsl:call-template>
</xsl:with-param>
<xsl:with-param name="LinkText" select="'WebSvn'" />
</xsl:call-template>
<xsl:text> </xsl:text>
<xsl:call-template name="CreateLink">
<xsl:with-param name="LinkRef">
<xsl:call-template name="replaceText">
<xsl:with-param name="StringToTransform"
select="$WorkingCopyChangeScriptUriTemplate" />
<xsl:with-param name="TextToReplace"
select="'FILEPATH'" />
<xsl:with-param name="ReplacementText"
select="$subPathToChangeScript" />
</xsl:call-template>
</xsl:with-param>
<xsl:with-param name="LinkText"
select="'Working copy'" />
</xsl:call-template>
</xsl:template>
The <xsl:output method="text"> tag can be used to generate a text file, such as a batch file, instead of an XML or HTML file.
There are three transform files which generate batch files in this manner. One of these is CreateBatchFileToGenerateChangeScriptErrorsPerDatabase.xsl. This is used to generate the batch file which calls RegexToXml for each SQL output file (i.e., to create the initial XML file for each database.) The input file is MetaData\Databases.xml. Here are the contents of the XSL file:
='1.0'='utf-8'
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />
<xsl:template match="Databases">
<xsl:for-each select="Database">
<xsl:text>if exist "..\ChangeScriptResults\</xsl:text>
<xsl:value-of select="@name"/>
<xsl:text>Results.txt" </xsl:text>
<xsl:text>..\Tools\regextoxml -d Matches=</xsl:text>
<xsl:value-of select="@name"/>
<xsl:text> Match=ChangeScript -p- -m+ -w- -c- -r </xsl:text>
<xsl:text> -r "..\RegularExpressions\ChangeScriptErrorsRegex.txt"
</xsl:text>
<xsl:text> -i "..\ChangeScriptResults\</xsl:text>
<xsl:value-of select="@name"/>
<xsl:text>Results.txt" -o "..\WIP\</xsl:text>
<xsl:value-of select="@name"/>
<xsl:text>ChangeScriptErrors.xml"
</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
The <xsl:text> element is used for outputting text "as is". It is particularly useful for starting a new line and for indentations, because leading and trailing whitespace isn't stripped out (as generally happens with other elements).
GenerateChangeScriptErrorsPerDatabase.bat creates a separate XML file to change script errors for each database. These separate files need to be merged into a single XML file before further processing can take place.
This turns out to be remarkably easy to do:
='1.0'='utf-8'
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:file="urn:mscorlib"
>
<xsl:template match="Databases">
<xsl:text>
</xsl:text>
<ScriptErrors>
<xsl:for-each select="Database">
<xsl:variable
name="ChangeScriptErrorsFile"
select="concat('..\WIP\',
concat(@name, 'ChangeScriptErrors.xml'))"
/>
<xsl:if test="file:Exists($ChangeScriptErrorsFile)">
<xsl:text>
</xsl:text>
<xsl:copy-of select="document($ChangeScriptErrorsFile)" />
</xsl:if>
</xsl:for-each>
<xsl:text>
</xsl:text>
</ScriptErrors>
</xsl:template>
</xsl:stylesheet>
Note that file:exists maps to System.IO.File.Exists(). This uses a feature of Microsoft's XSL implementation, known as extension objects. The XslCompiledTransform class can have .NET objects registered as extension objects within their own XML namespace. Suitable methods of those objects can then be called from within the XSL document.
TransformXml wraps XslCompiledTransform, and adds an additional feature allowing static classes to also be registered as extension objects. See my article on TransformXml for interesting details of how CodeDOM was used to accomplish this.
The call to TransformXml is found in MergeDatabaseChangeScriptErrors.bat:
..\Tools\TransformXml
-i "..\MetaData\Databases.xml"
-s "..\XslTransforms\MergeDatabaseChangeScriptErrors.xsl"
-o "..\WIP\MergedChangeScriptErrors.xml"
-x urn:mscorlib;mscorlib.dll;System.IO.File
-d+
The -x switch is followed by XML namespace;Assembly name;Class name. The class must either be static, have static methods, or have a default constructor.
The -d+ switch is required to allow the document() function to be called. For security reasons, this is not allowed by default.
[Readers who have had an in-depth look at RegexToXml might wonder why I didn't use its -a and -f switches to append the XML documents to one another when they were being generated. This would have done away with the need for this XSL transform. I agree that this would have been a better solution. The reason I didn't do it is quite simple... I only added these switches to RegexToXml some time after I wrote the build error reporting utilities!]
I would love to add some of the following features:
- Create a metadata file for configuring things like the SMTP host address and the administrator's address. This would enable the process to be completely customizable without editing the source code.
- Write replacements for the parts of the build process which generate the SQL output files (currently in FinalBuilder), thus providing a freely available end-to-end database build process.
- Rewrite as a single C# application, rather than using batch files, to compare the speed of development, readability, and maintainability of the two approaches.
- Rewrite as an SSIS (SQL Server 2005 Integration Services) package. SSIS has built-in support for XML and XSL transforms, as well as a very neat graphical designer for stitching the whole process together.
- Re-arrange the folder structure so that code to generate batch files is kept separate from the reporting code.
- Add support for multiple products in a Products.xml file.
However, I can't see that any of these features will add much value to the guys I work for, or to myself (e.g., by providing opportunities to experiment with and learn new technologies). So it's unlikely that I am going to add any of these features.
2 December 2006
- Initial version submitted.
