Okay, the XBRuLe is a bit laboured, should be SAX & bugs & XBRL, but any excuse to play some Ian Dury 
Bugs (the programming type, not the creepy-crawlies), Simple API for XML and Extended Business Reporting Language; these represented the trinity of my concerns for the last three weeks or so.
First, the bugs:
Several weeks back, I decided that the C portion of xLite needed an overhaul. The codebase contained a lot of stuff that I no longer used and also contained code that I’d written when I first re-started using C (after a lapse of 20 years or so, the phrase “I’d forgotten more than I’d ever known” sums up the experience best); some of this code was memory-leaking like a sieve.
Also, the original Pivotal Solutions code was not UTF-8 enabled, instead it used the host pc’s default character set codepage, this needed to change (if you don’t know what I’m talking about see Joel Spolsky’s lecture to the developers of the world – well actually, primarily to those of us in the Anglo-Saxon “ascii-will-do-fine” world).
The bugs I introduced as a result of this upgrade were not of the logical kind but of a much nastier type, peculiar to the low-level world of C, “bad free()” bugs!
Excel was no longer leaking memory (well no more than it normally does) but it was crashing randomly (usually in a DDL called VB7, 1st upgrade to classic VB in over a decade!), a sure sign I was freeing memory that was not mine to free. Two days later, I’d tracked the bugs down, but only by a painful line by line code walk-through. If you’ve no idea what I’m talking about here, count your blessings and move on.
The other major change I added to xLite is the ability to code SQLite Virtual Tables in VBA. The Python side of xLite has always had that facility, but I look on Python as a nice-to-have add-on, not as a core component. The growing need for “core” virtual tables meant either coding them in C or in VBA, see previous paragraphs for why VBA won the day.
The immediate driver for adding both UTF-8 support and quick-to-build virtual tables was the need to better handle XML data within xLite.
… then the SAX:
For small XML/HTML datasets, I, like the rest of the world, use DOM manipulation; but for larger sets I’ve tended to go down the brute force and ignorance approach of hand coded File I/O combined with regular expressions to efficiently parse out the data required.
Last week, an email from a datasmith name Cathy prompted me to look into using Sax for loading XML. Cathy, like most datasmiths, is not a professional programmer, she has a “real job”; part of that job is analysing large datasets, she’s learned enough programming (mainly Access & Excel) to do that job more efficiently. The data she needed to parse this time was encoded in XML, but being very large and built on a schema that constantly changed, the default DOM approach overpowered both Access and Excel.
Cathy had originally contacted me looking for information on using Talend to read the data and it looked like she was about to start a new side-career as a Java programmer. I figured there must be a way for her to leverage her existing skill-set (VBA) & this led me to MSXML’s implementation of SAX2. She was delighted; although many of the concepts would have been new to her, at least they were bounded within a world she was already comfortable with (the basis of how we all manage to incrementally expand our knowledge).
The only problem was, the example code no longer existed (it would have been in VB6, but who uses that these days, other than a few million VBA para-programmers, let them eat the .NET cake). So I coded up the first example in Excel/VBA and here it is if you need a quick-start to the joys of SAX2.
Which leads me on to XBRuLe:
“One XML to rule them all, One XML to find them, One XML to bring them all and in the darkness bind them…”
A former colleague of mine when explaining his computer science studies to Meath farmers whom he regularly met while hitch-hiking home from college (mid-1970s) was usually met with the response: “Ah computers, dere de comin‘ t’ing“. XBRL has been the coming thing for quite a while now.
Being in the business of Business Reporting, XBRL has always been on the radar, and of late, the radar is showing incoming fire. First the SEC, and now the UK’s HMRC, are mandating it as a filing method. Whether this is a good thing or not, is open to question. As this article puts it: “XBRL is a case study in complexity” http://bit.ly/951oEY “the producer of the sample must have suffered a polymorphic recursive brain meltdown”.
But needs be; I’m in the business of shaping difficult data, so I’ve started to re-acquaint myself with the subject (last time I looked at XBRL in any depth was 2004). Part of that process will be to beef-up xLite’s XML capability, which, with me being on the table side of the “Tables Vs. XML; the data lingua franca debate“, will involve getting the data into a relational form at the earliest possible moment. For example, for discovery I would use a classic parent-child recursive structure, but not having something like Oracle’s Start-with, adding a virtual table to make navigating such hierarchies easier with SQLite.
If anybody is peddling to you the concept that this brave new word of XBRL powered reporting will make your business reporting life easier, they’re either lying or don’t fully understand what they’re selling. As 19th century industrialists were wont to say: “Where there’s muck, there’s brass”; and with XBRL, you’ll be up to your oxters in muck, but with the brass all flowing to others, perhaps even to me!
