To Do List Plus XML dbrs2xml erubis MySQL FastCGI

August 8th, 2009

Here's a few items on my to-do list:

  • Write a module for NGINX
  • Switch to using Ruby as my primary high-level programming language
  • Create a qDecoder debian package for my own use (I have no clue how to get a package into the official repositories)
  • Examine various non-XSL template syntax for plain-text output (ERB, Lesscss, TT, macros?)
The last item is an interesting thought, and I've just spent the last thirty minutes thinking and researching it. I don't like the idea of moving away from XML, but I don't think I will. While it is certainly possible to output plain text from XSL, it is isn't very elegant.

One idea would be to use XSLT to convert an XML source document to a language specific data file. This worked well for lua, and some languages have support for "simplexml", which quickly convert an XML document to a language specific data structure.

OK, enough technical garbledeegook... how about a real world example?

I have a large database of banks, and I'm trying to make the data more navigable, as well as potentially render it as static content. So, I've come up with a plan:

  • create an xml file of every state (440 / state average)
  • create an xml file of each starting letter, f would be huge
  • number of branches:
    • 1 - 10
    • 11 - 100
    • 101 - 500
    • 501 - 1000
    • 1001 +

      Simple enough, but there is a fair amount of work involved. Thanks to programming, it shouldn't be that bad - it might even be fun!

      First off, I'm using dbrs2xml to convert data from a MySQL result set to XML. Its a FastCGI binary coded in C, but it can run as a command line application with two environment variables: SQL_NAME and SQL_QUERY. The SQL_NAME is used for the XML document, and SQL_QUERY is the SQL_QUERY. I have an XML document of all fifty states in the US, so I'm going to use Ruby's simplexml to parse it. Here's a snibbet of the states.xml file:

      <state label="Alabama" value="AL">Alabama</state>
      <state label="Alaska" value="AK">Alaska</state></states>

      Simple enough, now let's create some commands from that data set with Ruby and an eRuby template.

      sqlcommands.eruby template

      <% for item in list %>
      SQL_NAME="banks_get_all" SQL_QUERY="SELECT name,address_line_1,city,state,zip,offices,website FROM banks WHERE state='<%= item['value'] %>'"  ./dbrs2xml.fcgi > <%= item['value'].downcase %>.xml
      <% end %>

      myprocessor.rb Ruby code:

      require 'xmlsimple'
      require 'erubis'
      list = XmlSimple.xml_in('states.xml', { })['state']
      input ='sqlcommands.eruby')
      eruby =
      puts eruby.result(binding())

      The result is a shell script which when run will produce 50 xml files, one for each state. Cool huh? I can then use xsltproc to convert those to html/xhtml if I like - I can even include the xsltproc commands in the eruby template. And using some similar templates and processes, I'll be able to finish the rest of the goals in my plan. Not bad at all!


Yearly Indexes: 2003 2004 2006 2007 2008 2009 2010 2011 2012 2013 2015 2019 2020 2022