The Enterprise

Building a Personal Database with SQLite and Haskell (part 2)

Michael Pankov •

Last time we've built a nice podcast scheduling tool with Haskell, HDBC, and SQLite. Unfortunately, currently it only supports saving podcasts to the database, but not showing them. Today we're going to fix that.

In case you're interested in the final code — it's available on GitHub.

Firstly, we need to add a command to allow passing a number of podcasts to show:

data Commands = ...
| ShowPodcasts { numberToShow :: Int }
deriving (Typeable, Data, Eq, Show)

What we do is just add another variant with an Int argument to our sum type Commands.

As you remember, Commands is used by dispatchR function to parse the command line.

We also add the description of the new command to the Attributes instance:

instance Attributes Commands where
attributes _ = group "Noname" [
group "Showing the podcasts" [
numberToShow %> [ Help "How much podcasts to display"
, Default (1 :: Int) ]

We use the default value of one podcast to show.

And we then add a description of the mode to RecordCommand instance:

instance RecordCommand Commands where
run' cmd@(ShowPodcasts {}) _ = showPodcasts cmd
mode_summary (ShowPodcasts {}) = "show podcasts"

Our showPodcasts function looks like this:

showPodcasts :: Commands -> IO ()
showPodcasts p = do
let number = numberToShow p
conn <- connectSqlite3 databaseFilePath
rows <- quickQuery conn ("SELECT number, topics, guests, start_ " ++
" FROM podcasts ORDER BY start_ DESC LIMIT ?;")
[toSql number]
mapM_ showPodcast rows
disconnect conn
return ()

Nothing complex. First we pull the number of entities to show from the Commands record p, passed to the function by dispatchR. Then we establish a connection, and do a query with the only parameter — LIMIT, to only show as many podcasts as requested. We also sort the results by start date descending, so that newer podcasts are on top. The showPodcast function (shown below) is then mapped on the results, and the database is disconnected.

Additional challenge: you can try disconnecting before mapping.

showPodcast :: [SqlValue] -> IO ()
showPodcast values = do
let number : topics : guests : start_ : [] = values
putStrLn $ "Episode " ++ show (fromSql number :: Int)
putStrLn $ "Topics:\n " ++ (
concat $ intersperse "\n " (lines $ (fromSql topics :: String)))
putStrLn $ "Guests:\n " ++ (
concat $ intersperse ", " (lines $ (fromSql guests :: String)))
putStrLn $ "Start: " ++ show (fromSql start_ :: UTCTime)
putStrLn ""

Here we decompose a list into four values, then convert them using fromSql and output them to the terminal. The only interesting part is the concat $ intersperse "\n  " — it allows as to have nice indentation of contents relative to the headers.

See example below to know what I mean:

➜  haskpod git:(master) ✗ cabal run -- show-podcasts --number-to-show=2
Preprocessing executable 'haskpod' for haskpod-
[1 of 1] Compiling Main ( Main.hs, dist/build/haskpod/haskpod-tmp/Main.o )
Linking dist/build/haskpod/haskpod ...
Episode 2
A, B, C.Zugge
Start: 2014-04-06 12:14:00 UTC

That's it! We developed a simple command-line utility with a database in under two hours. It's pretty concise (111 lines) and robust, i.e. it reacts to errors adequately:

➜  haskpod git:(master) ✗ cabal run --                                   
Preprocessing executable 'haskpod' for haskpod-
help show help for a command or commands overview
add-podcast add a podcast
show-podcasts show podcasts
FATAL: Command required.

It's also easy to extend, keeping everything working at the same time — thanks to the strong static typing.

As a homework, I suggest adding support of custom ordering when showing podcast. User should be able to choose by which column to sort. This should take no more than 10 lines of code ;)

I think I'll continue posting some code in Haskell. For the next post, however, we're going to dive deep into the operating system internals and start a virtual memory & dynamic libraries detour.
comments powered by Disqus