Sunday, September 26, 2010

WshBuild(ing) Relationships

So far our meta-data file contains some very basic information about the databases type, name, properties, and linked tables The next item on my agenda was relationships. Now I was of two minds about this, and I still am. But here is how I went about implementing it anyway.
My first thought, and the way that I actually ended up implementing, was to simply add the relationship data to the Project.xml file. My second thought was that relationships really are complex enough to stand on their own, putting each one in it’s own file, much as I do forms, reports or tables. The last idea I had was a separate file that contained all of the relationships.
The last option I dismissed immediately. I had no need of a special file just for relationships. It didn’t seem to serve any purpose. So the real question is: Are relationships objects unto themselves, or just additional data about the tables? Well, the indices and keys for tables are considered meta data that is stored within the XSD for the table. And that decided me. This was just meta data about the tables. I couldn’t add the information to the table schema files as the XSD format doesn’t supply any means of doing so*. So I decided to store the relationship data in the Project.xml file.
Doing this was a piece of cake once I had decided to. After a little bit of playing around to find the best output I could come up with, I ended up with the following code:

Private Sub ExportRelationships(ByVal Relations, ByVal OutputFilePath)
  Dim XmlDocument, RelationshipsElement
  
  Set XmlDocument = CreateObject("Msxml2.DomDocument.6.0")
  XmlDocument.Load OutputFilePath
  
  Set RelationshipsElement = XmlDocument.selectSingleNode("database/relationships")
  If RelationshipsElement Is Nothing Then
    Set RelationshipsElement = XmlDocument.createElement("relationships")
    XmlDocument.documentElement.appendChild RelationshipsElement
  End If
    
  Dim Relation
  For Each Relation In Relations
    Dim RelationElement: Set RelationElement = XmlDocument.createElement("relation")
    RelationshipsElement.appendChild RelationElement
      
    RelationElement.setAttribute "name", Relation.Name
    RelationElement.setAttribute "table", Relation.Table
    RelationElement.setAttribute "foreign-table", Relation.ForeignTable
    RelationElement.setAttribute "attributes", Relation.Attributes
      
    If Relation.Fields.Count > 0 Then
      Dim FieldsElement: Set FieldsElement = XmlDocument.createElement("fields")
      RelationElement.appendChild FieldsElement
      
      Dim Field, FieldElement
      For Each Field In Relation.Fields
        Set FieldElement = XmlDocument.createElement("field")
        FieldsElement.appendChild FieldElement
        
        FieldElement.setAttribute "name", Field.Name
        FieldElement.setAttribute "foreign-name", Field.ForeignName
      Next
    End If
  Next
  
  WriteXmlDocument XmlDocument, OutputFilePath
  Set XmlDocument = Nothing
End Sub
The end result was rather gratifying. I can now see all of the defined relationships for the database in the Project.xml file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<database name="Example.mdb" type="2">
    <properties>
        <property name="AllowByPassKey" value="-1" />
        <property name="AppVersion" value="1.0" />
    </properties>
    <linked-tables>
        <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" />
    </linked-tables>
    <relationships>
        <relation name="MasterCache" table="Master" foreign-table="Cache" attributes="1024">
            <fields>
                <field name="id" foreign-name="id" />
            </fields>
        </relation>
        <relation name="MasterDetail" table="Master" foreign-table="Detail" attributes="42">
            <fields>
                <field name="id" foreign-name="master_id" />
            </fields>
        </relation>
    </relationships>
</database>
And with that, we now have the ability to export table relationships. Next time: Reversing the process.
*At least as far as I know. If I'm wrong here, feel free to ping me. I'd be more than happy to modify the code. :-)

No comments: