July 20th, 2003

(no subject)

I'm almost done with a fun, lame little Lisp thing. phlux.net was originally done with PHP and MySQL. I'm trying to convert the MySQL stuff to PostgreSQL, and in the process I'm changing some table names and some field names and stuff like that.

The conversion has to be done by hand, on a table-by-table basis. I started to do this with perl, and it quickly became a massive pain in the ass, so to procrastinate I decided to do it in Lisp instead. Lisp books always brag about "code that writes code" and stuff like that, so I decided to give it a try.

The final result was a bunch of support functions and a macro that actually do most of the heavy lifting for converting table. Instead of writing a bunch of "select, reformat, insert" boilerplate code, I can write a transformation specification that says what the old and new names are, the column types, the defaults, and some other shit, and Lisp creates a function to do the work.

Here's the specification for the "concert" table, which is getting renamed to "concerts" and having a few fields changed:

(def-sql-transform convert-concerts
    (table (concert concerts)
           (concert_id int notnull)
           (venue_id int notnull)
           event_name
           (concert_day date notnull)
           concert_time
           comments
           ((comments_in_html_p comments_format)
            text notnull (default "html")
            (conversion (if (string= comments_in_html_p "t")
                            "html"
                            "text")))
           (public_p bool notnull (default "t"))
           ((null cancelled_p) notnull (default "f"))))

That expands into code that, when executed:

  • submits a query to the database with the old column names
  • fills in default values for missing fields
  • transforms values if needed (the conversion bit)
  • prints out a sequence of "insert" statements to the new table for each row in the old table

Here's the resulting automatically generated code:Collapse )

And another cool part: the resulting code gets compiled to native machine code, and runs really fast. Not that it needs to for my tiny tables, but it's still nice.