malvasia bianca

My current programming project at home is to port my dbcdb code from Java to Ruby. So far, I’m working on porting over the CLI tool, which lets me update the database to add books that I’m reading, update information about them, etc.

Until today, I’d been using a fake database abstraction that I made up; today, I started plugging in the real MySQL stuff. Looking at my svn commit history, I see it took me an hour and 20 minutes to get the first bits working with MySQL, which I think is pretty good given my vast ignorance of SQL. It would have been faster if I’d had an interface to work with that was closer to the JDBC interface, because I’m a little familiar with the latter (and in particular it had affected my fake database abstraction), while I have to look up the syntax every time that I have to write raw SQL to add rows / modify rows in a table. I had a particularly fun 15 minutes where I was getting an SQL syntax error stemming from the fact that I’d used “order”, which is a reserved word in SQL, as one of my column names. Eventually I noticed that I’d enclosed the column name in backticks elsewhere, at which point that mystery got resolved.

I had plans to unit test my SQL layer, using an in-memory database, but I couldn’t find a convenient way to do that, so I ended up leaving it without unit tests. There’s a good set of acceptance tests, so I’m not particularly worried about things breaking; for now, typing things in by hand is working fine in getting me to a state where I can run the acceptance tests. The problem with running the acceptance tests right now is that they’re mostly an all-or-nothing thing; I decided to implement the SQL glue necessary to add entries before implementing the SQL glue necessary to modify entries, and unfortunately that’s all jumbled together in the acceptance tests.

It was really a lot of fun: once I had things to a state where I was ready to write a command-line script, I spent maybe 15 minutes correcting stupid syntax problem after stupid msyntax problem istake just to get as far as issuing the first SQL command, but when I got that far, magically all sorts of things just worked, and I could go over to the mysql command line and see the data just sitting there in the table! Way cool.

I suppose I might as well share a bit of code. The string for inserting data into a table is this:

    def insert_string
      "INSERT into `#{@name}` #{@row.fields} VALUES #{@row.values};"
    end

Here are the definitions of the fields and values methods on the row class:

    def fields
      parenthesized_list(@values.keys) { |key| "`#{key.to_s}`" }
    end

    def values
      parenthesized_list(@values.values) do |value|
        "'#{@connection.quote(value.to_s)}'"
      end
    end

Which is nice and pretty. The definition of parenthesized_list, though, I’m not so thrilled about:

    def parenthesized_list(array)
      list = "("
      first = false

      array.each do |element|
        if (first)
          list += ","
        else
          first = true
        end

        list += yield element
      end

      list + ")"
    end

I looked through the Array and Enumerable interfaces, but I didn’t see any way to really improve that. Which seems odd – am I missing something? If not, I should do some refactoring: it wouldn’t surprise me if that’s the longest method in my code base right now. (When the body of a Ruby method gets above 3 lines, I usually start to get nervous…)

I’m really excited about this. I’d been putting this step off for a while, and it was by far the biggest unknown in my current work. So for me to have made a concrete step towards the SQL integration in a single not particularly long programming session was a very pleasant surprise indeed. If I can find time tomorrow (which I may or may not be able to do – we’re going to a performance of H.M.S. Pinafore), I should be able to finish off the CLI tool: the remaining step should be a little smaller than this one.

After which I’ll have to start thinking about other part of the project, namely the part that generates web pages. I suppose one big decision is whether to roll my own XML creation library or to use an existing one. The former sounds a little more fun, and will probably make it easier to generate output that matches my acceptance tests, but I certainly want to stop reinventing the wheel at some point.