Hacking the Database

Catchy title isn't it?

Obviously I don't mean hacking in the Hollywood sense. Instead, I'll be looking at ways for the more innovative installers out there to extract the information they need from their project databases in C-Bus Toolkit. In this case, let's generate a comma-separated values text file (CSV) with a list of all the units in each network.

Right off the bat, we have a significant advantage over V2 in that the project databases are stored in standard human-readable XML format. XML as a platform comes with a raft of tools to transform data into other formats. The one we'll use here is XSLT, or "XSL Transforms".

1. First, let's assume you've installed and are familiar with the latest C-Bus Toolkit Beta. If you don't already have a suitable project handy, you can quickly create one using this tutorial.

2. You'll need an XSLT Editor. I use and recommend :

Publisher : Altova (www.altova.com)
Product : Altova XML Spy Home 2004 ( download here)

3. Back up your project database! Go to C:\Clipsal\C-Gate\tag\ and copy your project database (eg PROJ001.xml) to another working directory. It might be a good idea to rename it too, call it COPY001.xml. From now on, ensure you use this other copy.

4. Launch XML Spy.

5. Select File | Open, and open the xml file (eg. COPY001.xml).

6. There's a quick little hack you need to do to the file first... at the top you'll see an <Installation xmlns="http://www.clipsal.com/cis/schema/2001/cbus.xsd"> . Change this to <Installation xmlns=""> , ie. delete the content between the quotes.

7. Now it's time to create the Style Sheet which will transform your XML. Select the menu option File | New and choose the XSL Stylesheet option and click Ok. On the next dialog choose the third option, Generic XSL/XSLT Transformation, and click Ok. You now have an editing window with your XSL file in it.

8. Save this new file. File | Save and enter a name, eg. COPY001.xsl. Note the XSL, not XML, extension.

9. You can switch between your XML and XSL files using the file tabs at the very bottom of the XML Spy screen. Within each file tab, you'll see four mini tabs, Text, Schema/WSDL, Authentic, and Browser.

10. Click the XML file tab now to return to that window. Also ensure you have the Text mini-tab selected, you need to be viewing the XML project file for the next step.

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

12. You'll see a file selection dialog. Select the "Make path relative" option, and click the Browse option. You should see your saved XSL file, select it and click OK, OK, and OK again to return to the main window.

13. In the Text mini-tab, the line : <?xml-stylesheet type="text/xsl" href="COPY001.xsl"?> has been added to your COPY001.xml file. This is the association between the two files.

14. Now you're ready to add some XSL code, and view the transform. 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:

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <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="Unit">
    </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="Unit">
    <xsl:value-of select="Address"/>,
    <xsl:value-of select="UnitName"/>,
    <xsl:value-of select="UnitType"/>,
    <xsl:value-of select="FirmwareVersion"/>,
    <xsl:apply-templates select="PP"/>
    <br/>
  </xsl:template>

  <xsl:template match="PP[@Name = 'Application']">
    <xsl:value-of select="substring-before( @Value, ' ')"/>,
  </xsl:template>

  <xsl:template match="PP[@Name = 'AreaGroupAddress']">
    <xsl:value-of select="@Value"/>,
  </xsl:template>

  <xsl:template match="PP[@Name = 'GroupAddress']">
    <xsl:value-of select="substring(@Value,1,4)"/>,
    <xsl:value-of select="substring(@Value,6,4)"/>,
    <xsl:value-of select="substring(@Value,11,4)"/>,
    <xsl:value-of select="substring(@Value,16,4)"/>,
    <xsl:value-of select="substring(@Value,21,4)"/>,
    <xsl:value-of select="substring(@Value,26,4)"/>,
    <xsl:value-of select="substring(@Value,31,4)"/>,
    <xsl:value-of select="substring(@Value,36,4)"/>,
  </xsl:template>

  <xsl:template match="PP[@Name = 'SerialNo']">
    <xsl:value-of select="@Value"/>
  </xsl:template>

</xsl:stylesheet>

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

16. Phew! Now it's time to see what we've wrought. Go back to the XML tab, and then click on the "Browser" mini-tab to view your transform. After a short delay you'll (hopefully!) see something like the following.

INSTALLATION: MYCOMP

Project: MyHouse

Network: 251
Name: My Network
Type: Serial, COM1

11, LNG1 , KEY2, 1.2.67, 0x38, 0x4 , xff , xff , xff , xff , xff , xff , xff,
12, LNG2 , KEY4, 1.2.67, 0x38, 0x6 , xff , xff , xff , xff , xff , xff , xff,
13, KIT1 , KEY4, 1.2.67, 0x38, 0x5 , x7 0, 47 0, 23 0, ff 0, ff 0, ff 0, ff,
14, PORCH1 , SENPIRSS, 1.2.67, 0x38, 0xff, 0xff, 0xff, 0xff, , , , ,
14, PORCH2 , SENPIRSS, 1.2.67, 0x38, 0xff, 0xff, 0xff, 0xff, , , , ,
21, LIVING1 , KEYA8, 1.4.0, 0x38, 0xff, 0xff, 0x6f, 0x6d, 0xff, 0xff, 0xd5, 0xe5, 0xff 0xff 0xff 0xff
180, DIMMER1 , RELDN12, 1.3.0, 0x38, 0x39, 0x39, 0x3a, 0x3a, 0x39, 0x44, 0x3b, 0x3b, 0x0 0x0 0x0 0x0
181, DIMMER2 , RELDN12, 1.3.0, 0x38, 0x3a, 0x3c, 0x3d, 0x1d, 0x3a, 0x3e, 0x3f, 0x40, 0x0 0x0 0x0 0x0

​

A list of all the units in each network in the project database file, and for each unit, the address, the unit name, unit type, version number, application, the first 8 groups, and the serial number.

Of course, our job isn't done. Some of the values are in hexadecimal format, and we may want to extract other kinds of information from the database. XML stylesheets can be a daunting field to explore but there's a wealth of resources out there to get you started, and hopefully this snippet of XSL code will give you some ideas. For example, you can run an XSLT parser from the command-line, and pipe the output into perl scripts or other programs to further refine it into any possible format you desire!

So... have fun!