Hacking the Database Part 2 (Extracting Groups)

In the previous tutorial we were able to transform a Toolkit project file to extract all the C-Bus units defined in the project.

This time we'll extract a list of all the Applications and Groups defined in the project.

The first few steps are the same as the previous tutorial, so we'll just run through them briefly here :

1. Copy your project database ( from C:\Clipsal\C-Gate\tag\ ) to another directory.

2. Launch Altova XML Spy Home 2004 (or similar editor)

3. Select File | Open, and open the xml file (eg. COPY002.xml).

4. Change <Installation xmlns="http://www.clipsal.com/cis/schema/2001/cbus.xsd"> to <Installation xmlns="">

5. Select File | New, choose the XSL Stylesheet option. On the next dialog choose the third option, Generic XSL/XSLT Transformation. You now have an editing window with your XSL file in it. Save this new file. File | Save and enter a name, eg. COPY002.xsl

7. Click the XML file tab now to return to that window. Ensure the Text mini-tab is selected.

8. Select XSL | Assign XSL... and accept the prompt that suggests your XML will be reformatted.

9. On the file selection dialog select "Make path relative" and click "Browse". You should see your saved XSL file, select it and click OK, OK, and OK again to return to the main window.

10. In the Text mini-tab, the line : <?xml-stylesheet type="text/xsl" href="COPY002.xsl"?> has been added to your COPY002.xml file.


Now it's time to enter the XSLT Transform which will produce a list of all the Applications and Groups defined in every Network in the project :

11. Select the XSL file tab, and copy and paste this code in the space between the <xsl:stylesheet ..> line and the </xsl:stylesheet> line.

Code:

<xsl:template match="Installation">
    <xsl:apply-templates select="InstallationDetail"/>
    <xsl:apply-templates select="Project"/>
    </xsl:template>

    <xsl:template match="InstallationDetail">
    INSTALLATION: <xsl:value-of select="Hostname" />
    <p/>
    </xsl:template>

    <xsl:template match="Project">
    Project: <xsl:value-of select="TagName" />
    <p/>
    <xsl:apply-templates select="Network"/>
    </xsl:template>

    <xsl:template match="Network">
    <p/>
    Network: <xsl:value-of select="Address" />
    <br/>
    Name: <xsl:value-of select="TagName" />
    <br/>
    <xsl:apply-templates select="Interface"/>

    <xsl:apply-templates select="Application">
    </xsl:apply-templates>
    </xsl:template>

    <xsl:template match="Interface">
    Type:<xsl:value-of select="InterfaceType" />,<xsl:value-of select="InterfaceAddress"/>
    <p/>
    </xsl:template>

    <xsl:template match="Application">
    <xsl:apply-templates select="Group"/>
    </xsl:template>

    <xsl:template match="Group">
    <xsl:value-of select="../Address"/>,
    "<xsl:value-of select="../TagName"/>",
    <xsl:value-of select="Address"/>,
    "<xsl:value-of select="TagName"/>"<br/>
    </xsl:template>

12. Save all your changes, using File | Save All .

13. Go back to the XML tab, and then click on the "Browser" mini-tab to view your transform. You should see output a lot like this :

INSTALLATION: ke-bw32c
Project: CIS_TEST

Network: 254
Name: NET_A
Type:cni,10.176.136.139:14000

56, "Lighting", 255, "<Unused>"
56, "Lighting", 1, "Group 1"
56, "Lighting", 2, "Group 2"
56, "Lighting", 3, "Group 3"
56, "Lighting", 4, "Group 4"
56, "Lighting", 5, "Group 5"
56, "Lighting", 6, "Group 6"
56, "Lighting", 7, "Group 7"
56, "Lighting", 8, "Group 8"
224, "Telephony", 255, "<Unused>"
202, "Trigger Control", 255, "<Unused>"
203, "Enable Control", 255, "<Unused>"
136, "Heating", 255, "<Unused>"
255, "Unused", 255, "<Unused>"

Network: 253
Name: NET_B
Type:cni,10.176.143.141:14000

56, "Lighting", 255, "<Unused>"
56, "Lighting", 10, "Group 10"
56, "Lighting", 11, "Group 11"
56, "Lighting", 19, "Group 19"
56, "Lighting", 12, "Group 12"
56, "Lighting", 13, "Group 13"​

You'll notice the first half of the transform is exactly the same as the previous tutorial. This iterates through every network in the project database file, producing the network name and type. Towards the end of the transform you'll see it's now matching on match="Application" and not match="Unit". It's at this point that the details of the Application Address and Description and the Group Address and Description are generated, one group per line.

Again, this is merely a flexible starting point from which you can configure the output to your exact requirements.