Friday, September 24, 2010

How to output properties and remove imaginary tables

After my work getting the WshBuild to output a Project.xml file, I needed to start populating the data in it. There were three things that I wanted to do from the very start in this file. First, I wanted information about the database itself: it’s name, type, etc. Second, I needed the project properties. Finally, I wanted to eliminate the linked tables from the source export.
Adding the file information to the document was the easiest. All I needed was to add a couple attributes to my database element. A couple of simple calls…

XmlDocument.documentElement.setAttribute "name", Application.CurrentProject.Name
XmlDocument.documentElement.setAttribute "type", Application.CurrentProject.ProjectType
and the result…
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<database name="Example.mdb" type="2">
    <properties />

Project properties

The project properties were not much more difficult. This was a simple matter of looping through all of the project’s properties and creating an XML element for each. The code to do this is very terse indeed:
Dim Parent, Child, Property
Set Parent = XmlDocument.createElement("properties")
XmlDocument.documentElement.appendChild Parent
For Each Property In Project.Properties
  Set Child = XmlDocument.createElement("property")
  Child.setAttribute "name", Property.Name
  Child.setAttribute "value", Property.Value
  Parent.appendChild Child
Now we have the following in the Project.xml file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<database name="Example.mdb" type="2">
        <property name="AllowByPassKey" value="-1" />
        <property name="AppVersion" value="1.0" />

Removing linked tables

This was another minor feat, but it was somewhat more complicated than the properties. The reason is that I export the tables at a different point than I was doing the properties.  As it turns out, though, it was not that bad.
 Private Sub ExportLinkedTables(ByVal Tables, ByVal DatabasePath, ByVal OutputFilePath)
  Dim XmlDocument, XmlLinkedTables, XmlLink
  Dim Table, LinkPath, LocalName, SourceName

  Set XmlDocument = CreateObject("Msxml2.DomDocument.6.0")
  XmlDocument.load OutputFilePath

  Set XmlLinkedTables = XmlDocument.selectSingleNode("database/linked-tables")
  If XmlLinkedTables Is Nothing Then 
    Set XmlLinkedTables = XmlDocument.createElement("linked-tables")
    XmlDocument.documentElement.appendChild XmlLinkedTables
  End If

  For Each Table In Tables
    If IsLinkedTable(Table) Then
      LocalName = Table.Name
      SourceName = Table.SourceTableName
      LinkPath = Mid(Table.Connect, 11)
      If InStr(LinkPath, DatabasePath) = 1 Then 
        LinkPath = Replace(LinkPath, DatabasePath, "")
        LinkPath = FileSystem.BuildPath(".", LinkPath)
      End If

      Set XmlLink = XmlDocument.createElement("link")
      XmlLink.setAttribute "name", LocalName
      XmlLink.setAttribute "source", SourceName
      XmlLink.setAttribute "database", LinkPath
      XmlLinkedTables.appendChild XmlLink
    End If

  WriteXmlDocument XmlDocument, OutputFilePath

  Set XmlLink = Nothing
  Set XmlLinkedTables = Nothing
  Set XmlDocument = Nothing
End Sub
All this does is reopen the XML document and loop through all the tables in the database. Every time it comes across a linked table, it will output an element with the name of the table, the remote table name, and the file name. One difference, though, is that it outputs a semi relative path to the remote database. I haven’t come up with a good solution to getting truly relative paths, yet.  A couple of things to note: the IsLinkedTable method is a simple helper that just checks to see if the Table.Connect property is empty or not. If it isn’t, then the table is a linked table. The other thing is that that method is also used when I output the tables now. Only there, it is used to ignore  outputting the table so I no longer export the XSD for linked tables, only real tables. Finally, I have this:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<database name="Example.mdb" type="2">
        <property name="AllowByPassKey" value="-1" />
        <property name="AppVersion" value="1.0" />
        <link name="Logs" source="Logs" database=".\Archives.mdb" />
        <link name="Cache" source="Master" database="C:\Temp\Cache.mdb" />
        <link name="Archive" source="Master" database=".\Archives.mdb" />
        <link name="Master" source="Master" database=".\Data.mdb" />
And now, I’ve reduced a lot of needless source since the linked tables where having their XSD’s defined twice: once for the real table and once for the link. Next time: Relationships, friend or foe.

No comments: