WshBuild Progress – Sort of…

This past week I decided that I would indeed modify WshBuild to be able to regenerate a database from it’s exported source. Some of the things I came across were easier than anticipated, but others are turning out to be more difficult than I had initially imagined.

Exporting additional information

My original export source code didn’t quite export everything. It mainly only exported the things I thought truly likely to change over time. That is just Tables, Queries, Forms, Reports, Macros, and Modules. Trouble is, I would need a lot more if I was going to rebuild the database from scratch. A lot of this information is not very useful on its own either. So I decided the way to go was to create a simple Project.xml file that would contain a lot of this meta-data.
I know what your thinking: XML? Yuck! Well, I’ve come to realize why XML was so popular once upon a time. Not because it is easy to type, it isn’t. And not because it is easy to read either, because it does take a mental step to process. No, it was popular because it is very easy to generate and parse programmatically, especially with a good library.  Enter the MSXML: not great, but very easy to automate.

MSXML2 Library

The first thing I noticed was that I had the easy IXMLDOMDocument.load and IXMLDOMDocument.save methods. Perfect, I thought. Load worked exactly as I had both hoped and needed. Save, on the other hand, was an entirely different story. My first try was to simply generate a simple XML file with a <database> root element and a <properties> child element. The resulting output looked like this:

<database><properties /></database>

See the problem? For starters, it is all on one line, not nicely formatted. That would be an issue for source control. Second, it doesn’t contain the XML document directive. Not a major issue, but I like explicitly declaring the files encoding. So I needed a way to format my output for my new file. That is when I came across the ISAXXMLReader and IMXXMLWriter interfaces. Turns out these can be used format XML from an IXMLDOMDocument very nicely. After a couple of iterations, I had this nice little helper method to save the document:

  Private Sub WriteXmlDocument(ByVal XmlDocument, ByVal FilePath)
    Dim Reader, Writer, Stream

    Set Stream = CreateObject("ADODB.Stream")
    Set Writer = CreateObject("Msxml2.MXXMLWriter.6.0")
    Set Reader = CreateObject("Msxml2.SAXXMLReader.6.0")

    Stream.Open
    Stream.Charset = "UTF-8"

    Writer.encoding = "UTF-8"
    Writer.indent = True
    Writer.omitXMLDeclaration = False
    Writer.output = Stream

    Set Reader.contentHandler = Writer
    Set Reader.dtdHandler = Writer
    Set Reader.errorHandler = Writer
    Reader.putProperty "http://xml.org/sax/properties/declaration-handler", Writer
    Reader.putProperty "http://xml.org/sax/properties/lexical-handler", Writer

    Reader.parse XmlDocument.XML
    Writer.flush

    Stream.SaveToFile FilePath, adSaveCreateOverWrite
    Stream.Close

    Set Reader = Nothing
    Set Writer = Nothing
    Set Stream = Nothing
  End Sub

And now I can output the same XML document as above with the following results:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<database>
    <properties />
</database>

Pretty nice, huh? Next time: Outputting Properties and Removing the imaginary table.