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)
handler
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 = B.map f fn
f '/' = '-'
f '"' = '\''
f x = x
-- (b:c) = map B.unpack $ B.words a
in
-- 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
in
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 ()