April 2009

Migrating rails app from sqlite to mysql

I run a pretty simple rails site for pickup ultimate games in Fort Collins, hosted at Dreamhost. For many months, it had been working just fine using sqlite for the database. Somehow in the last few weeks, it developed a locking issue and has just not worked right since.

Why the locking issue all of a sudden? Perhaps it experienced concurrent requests for the first time? Maybe some change to dreamhost’s NFS setup, or the mod_rails/passenger setup? Who knows. But whatever the reason, sqlite was no longer working, so I migrated the site to mysql.

Two steps involved: create the mysql databases, and transfer the data from the sqlite dbs to the mysql db’s. I was using sqlite because it was easy, essentially no overhead for setup / admin (at a cost, obviously). I created a devel and test db on my mysql instance at home, and the production db on the dreamhost-provided mysql server. Updated the rails config to match.

Migrating the data was the only part with any gotchas. In theory, something like this should work:

echo .dump | sqlite3 production.sqlite3 |  \
    mysql -u username --password="passwd" -h myhost production

Dump from sqlite, import directly into mysql. The issue is some differences in the SQL dialect between sqlite and mysql. See that article at StackOverflow for the details, but it ended up being a matter of a few minor edits to be able to import the dump into the new mysql production database.

I expect many more months of smooth sailing from here…


Comments (0)


Outlook VBA macro to post email body to the web

At the request of one of my managers, I looked into the possibility of using Outlook to automatically post the contents of an incoming email to a website (an internal group wiki in this case). We previously used procmail to accomplish this task, but now that all email has migrated to Outlook/Exchange, that’s no longer an option. It turns out this is pretty easy to accomplish using the VisualBasic for Applications (VBA) macro capabilities built-in to Outlook.

The basic pieces to make this work:

  1. A Rule in Outlook that matches the specific email criteria, and runs a script (Outlook Macro)
  2. The Outlook macro which pulls out the message body and posts to a web site
  3. The web site / CGI to handle the incoming message

Setup the Outlook Rule

The first part is the easiest – define a rule with filters to match specific messages that should be forwarded onto the web site. The filter criteria should be as specific as possible — not generally a good idea to be posting arbitrary email messages onto a website. The action should be “run a script”. The ‘script’ has to be a macro with a specific signature, like this:

Sub PublishMsgToWeb(Msg As Outlook.MailItem)

That defines a macro specifically intended to handle Outlook message (Outlook.MailItem instances).

Define the macro to post to the web

The macro is pretty simple: ready the message body and any other data of interest from the Outlook.MailItem object, package the data up into an HTTP POST request, and send it off.

Sub PublishMsgToWeb(Msg As Outlook.MailItem)
    Dim URL As String
    URL = "http://example.com/post-msg-from-outlook.cgi"

    Dim Subj As String, Body As String
    Subj = Msg.Subject
    Body = Msg.HTMLBody

    PublishMsgToURL URL, Subj, Body
End Sub

Sub PublishMsgToURL(ByVal URL As String, Subject As String, Body As String)
    Set xhr = CreateObject("MSXML2.XMLHTTP")
    xhr.Open "POST", URL, False
    xhr.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

    data = "subj=" & URLEncode(Subject) & "&" & "body=" & URLEncode(Body)
    xhr.Send data
End Sub

I split the macro into two different pieces for testability. The HTTP POST request is done by instantiating an MSXML2.XMLHTTP request. Web services in Outlook seems to be a bit of a muddy subject, with multiple flavors of XmlHttpRequest. MSXML2.XMLHTTP is what I found to work in my version of Outlook 2003. YMMV.

The only real trick to putting the request together is in encoding the data as “application/x-www-form-urlencoded”, the format typically used for POST requests. I used the URLEncode() VBA function to encoded each piece, then joined them together into a normal query string


This example just sends the message subject and body. Obviously there has to be an agreeement between the macro and the receiving website as to what data the request body should contain. Date, sender, and others are readily accessible from the Outlook.MailItem.

Define the CGI on the website to receive the message

For the purpose of this example, I just put together a simple CGI script in Ruby:

#!/usr/env/bin ruby
require 'cgi'
cgi = CGI.new

subj = cgi['subj']
body = cgi['body']

File.open("msg.html",'w') do |fh|
fh << <<END
<title>Outlook msg: #{subj}</title>

print cgi.header( "type" => 'text/plain' )
print "ok\n"

This just saves the message content to a static html file that can then be viewed in a browser. The script just returns a simple ‘ok’ message back to the caller, but that will not actually be seen by anyone since the Outlook macro handles this transaction behind the scene.

So there’s the basic framework for posting email to the web using the built-in Outlook rules and macro capabilities. Whether or not this is actually a good idea is another question altogether…


Comments (4)