Notes‎ > ‎

Connecting to MySQL with Haskell

posted Jul 31, 2008, 11:21 AM by Wei Hu   [ updated Jan 24, 2009, 12:45 AM ]
UPDATE: HDBC-mysql, a native driver for MySQL.

People [1, 2] seem to agree the best library for writing Haskell database programs is HDBC. HDBC has native support for PostgreSQL and SQLlte, and ODBC support for MySQL and other common databases. So, to talk to MySQL I have to get ODBC working on Linux first.

1. Installing unixODBC, MySQL driver, and HDBC

There are 3 DSNs defined in odbc.ini. We can remove the first two and only keep Default. Next, edit the Default DSN's Server and Database lines properly. You can either set the User and Password lines here, or set them in the connection program. Besides editing the ini files, we can also use the GUI program ODBCConfig from the deb package unixodbc-bin. To test our settings, we can either use the command line tool isql/iusql, or the GUI tool DataManager.
$ isql Default wh5a wh5a_s_pwd
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
SQL> select * from wp_posts

2. Working with HDBC

The best tutorial can be found from Real World Haskell. To use it well you must understand dynamic exceptions.

import Control.Exception
import Database.HDBC.ODBC
import Database.HDBC

-- The ending ';' is required! You can append other options like:                                                           
-- connectionString' = connectionString ++ "Password=blahblah;"                                                             
connectionString =  "DSN=Default;"

main =
  catchDyn (do
    c <- connectODBC connectionString
    disconnect c)
  where handler :: SqlError -> IO ()
        handler err = print err

3. Coding for the Task

My original Wordpress blog was shut down by the system admin due to some web server vulnerabilities which are presumably not even relevant to my blog. However, over two months have passed and they didn't bother to send me any updates. Finally, I was like, fuck them, I'm going to backup my blog on my own. I had three options to do the backup:

1. I had installed the wp-db-backup plugin which sent me an SQL file weekly. I can import this SQL into other database servers and get an exact clone. I don't like this idea since I've switched to Drupal for my blogging needs.

2. I can write a program to process this SQL file.

3. The admin only blocked accesses to my blog directory but didn't shut down my database accesses. So, basically everything is still their in the database. So I wrote a little Haskell program to talk to the MySQL server and dumped a bunch of static HTML files to the disk.Dirty and quick. The tricky thing is that I must support UTF-8, which I'm totally unfamiliar with. To support UTF-8, we extract information from the database and store it into byte vectors using ByteString instead of String. Because we want to mix ByteString's with String's, we need a way to convert between them. At first I didn't know of the Data.ByteString.Char8 module and implemented the conversions the awkward way: unpack a Bytestring into a list of Word8's, then to a list of Int's, and finally to a list of Char's. Turns out we don't need any of this.
import Data.Char
import Database.HDBC.ODBC
import Database.HDBC
import qualified Data.ByteString.Char8 as B
-- The ending ';' is required! You can append other options like:
-- connectionString' = connectionString ++ "Password=blahblah;"
connectionString = "DSN=Default;"
outPath = "/tmp/blog/"
indexName = outPath ++ "index.html"
indexHead = "<head> <META http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"> <title>INDEX</title></head>\n"
main =
  handleSqlError $ do
    c <- connectODBC connectionString
    s <- quickQuery c "SELECT post_title, post_content FROM wp_posts" []
    let s' = map (map fromSql) s :: [[B.ByteString]]
    writeFile indexName indexHead
    mapM_ process s'
    disconnect c
-- replace / with -, replace " with ', and replace spaces with _
-- turns out this function is unsafe because it works on ASCII without taking into account utf-8 encodings!
-- it's therefore possible to garble the file name.
clean :: B.ByteString -> String
clean fn = let a = f fn
               f '/' = '-'
               f '"' = '\''
               f x = x
--               (b:c) = map B.unpack $ B.words a
--             b ++ (concatMap ('_':) c)
             B.unpack a
htmlHead = "<head> <META http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"> <title>"
htmlTitle = " -- Wei Hu</title> </head>"
linkHead = "<p><a href=\""
process :: [B.ByteString] -> IO ()
process [fn,fc] = let fn' = clean fn
                      ufn = B.unpack fn
                      clean_fn = outPath ++ fn' ++ ".html"
                      content = B.concat (map B.pack [htmlHead, ufn, htmlTitle] ++ [fc'])
                      link = B.concat $ map B.pack [linkHead, fn' ++ ".html\">", ufn, "</a>\n"]
                      -- insert a <br/> at the end of each line
                      fc' = let x = B.lines fc
                                y = map ((flip B.append) $ B.pack "<br/>") x
                            in B.concat y
                    if ufn /= "" then do
                                   putStrLn "******************"
                                   B.putStrLn fn
                                   putStrLn "******************"
                                   B.putStrLn fc
                                   print "Keep this file?(Y/n)"
                                   ans <- getLine
                                   if ans == "" || ans == "y" then do
                                                                B.appendFile indexName link
                                                                B.writeFile clean_fn content
                                    else return ()
                    else return ()