An invitation form (15)

I'm expecting most invitation information for this system to come from a separate database, but I'm expecting to want to be able to manually invite people too - for example if they aren't in the database, or are tagged incorrectly in the database. Having a manual invitation facility will be useful for testing too.

So I'm going to build an invitation form: that will take basic information (but not everything - just enough to make an invitation), and on submission, generate a URL, and send it in email. Unlike the registration form, it won't be directly backed by its own table: this is more of a form used to issue a command, rather than a form used to edit a database record.

The invitation form is going to collect name, and email address; somewhere in here, a nonce needs generating too.

I'm going to model the form contents a Haskell data type a bit like the Registration datatype. In src/Invitation.hs, write:

module Invitation where

data Invitation = Invitation {
  firstname :: String,
  lastname :: String,
  email :: String
}

Unlike Registration, we won't need any typeclass instances for this because we aren't expecting to send it into the database.

When we import this into app/Main.hs, we run into a problem: each of the field names defined in Invitation define a function to access those field names. So there is a function:

firstname :: Invitation -> String

... which is how you can write things like firstname someinvitation to extract the first name field.

But. the same applies for everything else, including Registration which also implicitly defines a function:

firstname :: Registration -> String

Those are two different functions, and so with a plain import, it is ambiguous which one you mean. (although if the input type is known, it isn't actually ambiguous. Have a look at duplicate record fields if you'd like to see more; the Haskell-like language Idris can also disambiguate this properly but it has its own problems).

So instead, lets import Invitation qualified with an short identifier:

import qualified Invitation as I

That way, firstname on its own refers to only the Registration version; if we mean the invitation version, we have to write I.firstname. We could also qualify the import of Registration as R and then we'd have to put the R. prefix in front of everything to do with registrations. Avoiding potential overlapping names is half of the reason why most of the library imports I've done so far have been qualified like this.

We'll need to define GET and POST API endpoints to get access to our form - GET to provide an initial form, and POST to send in the completed form. Unlike the registration form, there is no identifier in the URL. An invitation form doesn't have any long term presence in the server environment that can be identified: if you close your browser before you've used it to perform an actual invitation, you lose what you've typed in.

type InvitationGetAPI = "admin" :> "invite" :> S.Get '[SB.HTML] B.Html
type InvitationPostAPI = "admin" :> "invite" :> S.ReqBody '[S.FormUrlEncoded] [(String,String)] :> S.Post '[SB.HTML] B.Html

... and add them into the main API:

type API = 
...
      :<|> InvitationGetAPI
      :<|> InvitationPostAPI

... make sure that the server handles them:

server :: S.Server API
server = handlePing :<|> handleHtmlPing :<|> handleRegistration
    :<|> handleRegistrationPost :<|> handleCSV
    :<|> handleInvitationGet :<|> handleInvitationPost

... and (later in the post) provide implementations for the GET and POST methods.

This form will use digestive-functors so we'll need:

invitationDigestiveForm :: Monad m => DF.Form B.Html m I.Invitation
invitationDigestiveForm =
  I.Invitation
    <$> "firstname" .: nonEmptyString Nothing
    <*> "lastname" .: nonEmptyString Nothing
    <*> "email" .: nonEmptyString Nothing

invitationDigestiveForm doesn't take an initialising parameter - this is another difference from registrationDigestiveForm, and again is because we aren't ever loading/restoring form state from somewhere else. You can only ever start with a blank invitation.

Most of the basic mechanics of getting this form rendered to HTML and interacting with GET and POST are the same as with registrations: we need handleInvitationGet and handleInvitationPost to respond, and two helpers: htmlForInvitation which will give an HTML rendering of a form, and doInvitation which is going to actually do whatever needs to be done when an invitation command is submitted. The first three I've pasted below without comment.

handleInvitationGet :: S.Handler B.Html
handleInvitationGet = do
  view <- DF.getForm "Invitation" invitationDigestiveForm
  return $ B.docTypeHtml $ do
    B.body $ do
      B.h1 $ "New Invitation"
      htmlForInvitation view

handleInvitationPost :: [(String, String)] -> S.Handler B.Html
handleInvitationPost reqBody = do

  viewValue <- DF.postForm "Invitation" invitationDigestiveForm (servantPathEnv reqBody)

  case viewValue of
    (view, Nothing) -> 
      return $ B.docTypeHtml $ do
        B.body $ do
          B.h1 $ "New Invitation (there were errors): "
          htmlForInvitation view

    (_, Just newInvitation) -> liftIO $ doInvitation newInvitation

htmlForInvitation :: DF.View B.Html -> B.Html
htmlForInvitation view = 
  B.form
    ! BA.method "post"
    $ do
      B.p $ do  "First name: "
                DB.errorList "firstname" view
                DB.inputText "firstname" view
      B.p $ do  "Last name: "
                DB.errorList "lastname" view
                DB.inputText "lastname" view
      B.p $ do  "email: "
                DB.errorList "email" view
                DB.inputText "email" view
      B.p $     DB.inputSubmit "Save" 

Now, in doInvitation, for now, I want to: generate a Registration record, store it into the database, and return some HTML to the inviting administrator with a URL to send on. (another time, we'll make that URL be emailed, but not today).

We begin by constructing a Registration record: mostly by copying values from the invitation or setting them to a default. The nonce comes from a helper function generateNonce which I'll talk about later. It returns a random alphabetical string to use as a nonce value.

doInvitation :: I.Invitation -> IO B.Html
doInvitation invitation = do

  newNonce <- generateNonce

  let registration = Registration {
    firstname = I.firstname invitation,
    lastname = I.lastname invitation,
    dob = "",
    swim = False,
    nonce = Just newNonce,
    email = Just (I.email invitation),
    status = "N"
    
  }

Once we have a Registration, we can write it to the database:

  liftIO $ bracket
    (PG.connectPostgreSQL "user='postgres'")
    PG.close
    $ \conn -> do
      PGS.ginsertInto conn "registration" registration

... and then return a suitable HTML response:

  let url = "http://localhost:8080/registration/" ++ newNonce

  return $ B.docTypeHtml $ do
    B.head $ do
      B.title "Invitation Processed"
    B.body $ do
      B.h1 "Invitation processed."
      B.p $ "Please ask the participant to complete the form at "
            <> (B.a ! BA.href (fromString url)) (fromString url)

generateNonce uses the Haskell system random number generator in this one-liner:

import System.Random (randomRIO)
...
generateNonce :: IO String
generateNonce = sequence $ take 32 $ repeat $ randomRIO ('a', 'z')

randomRIO is an IO action that returns a character between a-z. We build an infinite list of such IO actions using repeat, and then take the first 32 elements, giving a list that holds 32 IO actions that each return a character. sequence then executes each IO action in turn, and returns the list of results: 32 random characters.

Now you can invite as many people to register as you want.

Here's the commit for this post.

Next, I'm going to add support for a configuration file: you'll see there's a hardcoded localhost above that I'd like to be able to configure; and when it is time to send email, there will be a few server parameters there that need configuring.

getting ready to invite people (14)

This booking system is for an invite-only event - a scout camp - which is why I've made a form for editing your details, but not for registering from scratch. I'm expecting the database to be loaded with basic details of each invitee (from an existing membership database), and then invitees to be emailed a link to fill out the form.

The registration links that exist now aren't suitable for sending out: registation/1 contains a number that is almost definitely sequential, and is trivial editable to view and modify other people's registrations.

I don't want to go down the road of building a username/password system that will need a lot of human support to deal with lost passwords, etc.

Instead, I'll use the technique username/password systems often use to let you reset your password when you can't remember it but when you do have access to your email: I want to send a link with a large random-looking value, intended to be unguessable, instead of a sequentially numbered one - something like registration/24f556856f4cfce0e08e17769465d92f.

So add a new string column to the database to store this key called nonce (although it arguably isn't a nonce).

The existing records need something done to them: either we need to make nonce nullable so those records can have no key (and be inaccessible); or we need to invent a value for them (This is doable in postgres but I'm not going to do it because this series is meant to be about Haskell); or we could do the migration in Haskell code (but postgresql-simple-migrations doesn't support that from the command line) or we can set them to some magic constant (such as the empty string "") which reeks of 1970s data processing.

If we were starting from a completely clean database, we wouldn't have this choice to make: we could insist every record has a nonce, and make the column NOT NULL. But I promised myself at least (and maybe you) that we wouldn't need to wipe out the database any more after implementing migrations.

So I'll make the nonce nullable, and the relevant bits of Haskell code will have to deal with that. (you make a field nullable by not writing NOT NULL as part of the type: well done SQL for supporting non-nullable values, but boo sql for making it non-default.

Here's a migration to add as migrations/0003-nonce.sql:

ALTER TABLE registration ADD COLUMN nonce TEXT;

TEXT columns in the registration have so far always been converted to/from Haskell String values inside the Registration type. That will sort-of work in some cases for nonce but not always: String doesn't have a way to represent a NULL - so we won't be able to write out NULL values to the database (which probably won't be a problem for us) and reading in NULL values will cause some kind of explosion (which will probably be a problem for us).

The obvious type to use instead is Maybe String, and indeed that works out of the box.

So onto the end of Registration in src/Registration.hs, add in a new field: nonce :: Maybe String

None of the SQL code needs changing now because of the generics magic in previous posts, but there's still that pesky applicative in registrationDigestiveForm which needs this sticking on the end (if you added the nonce field on the end of the Registration type):

...
  <*> "nonce" .: DF.optionalString (nonce initial)

We can't use DF.string here because that is for String, not Maybe String but digestive-functors has a suitable optionalString. If it didn't, it would be a very straightforward wrapper to build around DF.string.

If I'm going to invite people by email, I'm also going to need one other critical field: their email address. I'll call that email because I'm expecting that there might be other email addresses involved as user requirements change.

So migrations/0004-email.sql can be:

ALTER TABLE registration ADD COLUMN email TEXT;

... and we can add that new field onto the Registration type:

...
  email :: Maybe String
  }

... and onto registrationDigestiveForm:

...
    <*> "email" .: DF.optionalString (email initial)

We could stick some kind of email validation onto this, instead of directly using DF.optionalString but good luck.

While we're adding fields, let's add yet another field which is going to track the completion status of this form: I want to know if a registration is New, or if they have been Invited, or if they have Saved the form, or if they have Completed registration, or if their registration has been cancelled. Those capital letters are there to suggest that I'm going to use a single character field for this in the database.

Here are the relevant code fragments - hopefully you can figure out where they go:

ALTER TABLE registration ADD COLUMN status CHAR(1);
UPDATE registration SET status = 'N' WHERE status IS NULL;
ALTER TABLE registration ALTER COLUMN swim SET NOT NULL;

...

  status :: String
...
  <*> "status" .: nonEmptyString (Just $ status initial)

Luckily for this field, there's a meaningful default value: N for new, so the migration sets that status for every existing record, and we don't need to use Maybe.

In the next posts, I'll start using these fields: first to generate invitation URLs for new registrations, and then next to actually email those registrants.

Even more generic SQL (13)

The master branch of postgresql-simple-sop can do SELECT but not UPDATE.

(although if you dig hard enough, you *can* find an implementation in a branch in a different github repo with a sign on the door saying 'Beware of the Leopard')

I'm going to write an implementation, or rather adapt the SELECT implementation, gselectFrom which you can see online here.

What I want to do is generate an SQL statement that looks like this:

"UPDATE registration SET firstname = ?, lastname = ?, dob = ?, swim = ? WHERE id = ?"

... but where all those field = ? bits are generated from the names of the Haskell record data type (for exampl,e Registration).

There's a function fieldNames in Database.PostgreSQL.Simple.SOP...

fieldNames :: HasFieldNames a => GS.Proxy a -> [String]

... that will give us field names for a suitable type a, if we feed in some proxy thing. Luckily from the previous post, Registration indeed HasFieldNames.

We can use it like this:

> PGS.fieldNames (GS.Proxy :: GS.Proxy Registration)
["firstname","lastname","dob","swim"]

That Proxy stuff is because we're trying to write a function from a type to a value (feed in a type like Registration and get out a value, a list of the fields). Normal Haskell only lets you write functions from values to a values. So Proxy is some generics-sop magic that sort-of turns a type into a value, so that we can pass it to a Haskell function. Don't think about it too hard.

Given that list it's fairly to generate the desired SQL:

  let fns = PGS.fieldNames $ (GS.Proxy :: GS.Proxy Registration)

  let fieldSets = map (\name -> name <> " = ?") fns

  let sql = ("UPDATE " <> tbl
          <> " SET " <> (fromString $ intercalate ", " fieldSets)
          <> " WHERE " <> whereclause)

... and then execute the SQL:

  PG.execute conn sql (val PG.:. where_val)

I've put two sets of SQL parameters in there: one is the value that we're going to write (a Registration in our case), and the other is any parameters needed for the WHERE clause: for example to pick a row to update based on id column.

But I want this to live in a function that is not specialised to Registration. I want it to work for any (suitable) type. So I can't talk about Proxy Registration. I want to be able to write something like:

gupdateInto :: (PG.ToRow a, PGS.HasFieldNames a) => a -> stuff -> IO ()
...
  let fns = PGS.fieldNames $ (GS.Proxy :: a)
...

... which doesn't work by default - that final reference to type variable a doesn't get connected up with the type variable a used in the type signature.

We can turn on two language features, ScopedTypeVariables and RankNTypes, to make that work.

The type signature then ends up being about the same length as the body of the function:

gupdateInto :: forall r. forall s.
    (PG.ToRow r, PGS.HasFieldNames r,
     PG.ToRow s)
  => PG.Connection -> PG.Query -> PG.Query -> r -> s -> IO Int64
gupdateInto conn tbl whereclause val where_val = ...

Here, r is the type of data we're going to write (eg. Registration). We need to be convert it to an SQL row (ToRow) and to get the field names for it (HasFieldNames).

Then, s will be the type of whatever we're using to select the record(s) to update - for example an index Integer. We also need to be able to turn that into a row of data to send to PostgreSQL; but we don't need any field names for it as we're just using it as parameters for the UPDATE.

That PG.Query is actually something String-like, not a fully formed abstract database query of some sort - to generate those from string literals, we'll also need the OverloadedStrings language extension enabled. (That's also why the string munging above uses fromString - fieldNames returns text made out of String, but execute needs text made out of PG.Query.)

And finally the return value, Int64 is what PG.execute returns: the number of rows that were affected by the UPDATE.

So here's the whole massive pile, which I've put in its own file at src/Update.hs:

{-# Language OverloadedStrings #-}
{-# Language ScopedTypeVariables #-}
{-# Language RankNTypes #-}

module Update where

import Control.Monad.IO.Class (liftIO)

import Data.Int (Int64)
import Data.String (fromString)
import Data.List (intercalate)
import Data.Monoid ( (<>) )

import qualified Database.PostgreSQL.Simple as PG
import qualified Database.PostgreSQL.Simple.SOP as PGS

import qualified Generics.SOP as GS

gupdateInto :: forall r. forall s.
    (PG.ToRow r, PGS.HasFieldNames r,
     PG.ToRow s)
  => PG.Connection -> PG.Query -> PG.Query -> r -> s -> IO Int64
gupdateInto conn tbl whereclause val where_val = do
  let fieldNames = PGS.fieldNames $ (GS.Proxy :: GS.Proxy r)
  let fieldSets = map (\name -> name <> " = ?") fieldNames

  let sql = ("UPDATE " <> tbl
          <> " SET " <> (fromString $ intercalate ", " fieldSets)
          <> " WHERE " <> whereclause)

  PG.execute conn sql (val PG.:. where_val)

Now we can use this in app/Main.hs:

gupdateInto conn "registration" "id = ?" newRegistration (fromIntegral identifier)

instead of the previous:

PG.execute conn "UPDATE ...

As we're now passing a whole Registration into postgresql-simple, and to satisfy the gupdateInto type signature, we need to add another instance declaration for Registration, PG.ToRow. This is like the mirror image of PG.FromRow, and can be automatically derived.

So that's all the explicit field names removed from the SQL parts of codebase.

More Generic SQL (12)

In the last post, we made a bunch of changes throughout the codebase to support an additional field.

Some of those changes are fairly mechanical: for example, when we SELECT a Registration out of the database, we always need to request all of the fields in a Registration (else we can't give sensible values for every parameter of the constructor).

Some of this work is done already in postgresql-simple. As long as we include the all the fields in the right order in a SELECT, then the FromRow typeclass can figure out how to turn those fields into a single Registration value. The code already does that at every SELECT.

But we still need to include all the fields. This should be automatable. And indeed it is, using postgresql-simple-sop.

There isn't much to this: it gets the list of fields in a specified datatype, and sticks them in a SELECT statement for you.

This is the first package we're going to encounter that is neglectware: last updated on hackage in 2015, and until a few days ago, the git version wouldn't build against the stack snapshot that I'm building. That last problem is fixed as of last weekend, and so we can get the package from github (rather than from a curated stack snapshot). (actually there's been a bunch of other development buried in a branch of a fork in a previous employer's github repo, but it never got merged to release/master)

To do that, add a dependency for postgresql-simple-sop for the reg-exe executable. This will give a build error about the stack configuration has no specified version - because there isn't one in the stack snapshot I'm using.

So tell stack to download the dependency from github, in extra-deps in stack.yaml, alongside the two existing extra dependencies:

extra-deps:
- digestive-functors-0.8.3.0
- digestive-functors-blaze-0.6.2.0
- git: https://github.com/openbrainsrc/postgresql-simple-sop
  commit: d1834bda124081ad6a39fd4849a40d4b2fa74b3e

So in addition to (previously) telling stack that yes we want it to use those specific versions of two digestive functor packages, we've specified which package we want for postgres-simple-sop as a specific git version. (you could also write master there instead of a commit, to use the latest on a master branch. But magically updating dependencies is troublesome in the long run.

Now instead of this:

PG.query
  conn
  "SELECT firstname, lastname, dob, swim FROM registration WHERE id = ?"
  [identifier]

... we can instead write:

We can now write things like this:

import Database.PostgreSQL.Simple.SOP as PGS
...
PGS.gselectFrom conn "registration where id = ?" [identifier]

... although to allow this new library to work we have to add some more instance declarations on Registration:

import qualified Generics.SOP as GS
import qualified Database.PostgreSQL.Simple.SOP as PGS

...

instance GS.Generic Registration
instance GS.HasDatatypeInfo Registration
instance PGS.HasFieldNames Registration

... with generics-sop added as a dependency in package.yaml.

generics-sop is a different implementation of generics (different from GHC.Generics) that is nicer to use if you're writing generic code (rather than using it, as we are here).

HasFieldNames is a typeclass that says we can get an SQL field name for each entry in the Registration type. Because we don't specify any actual implementation code, the default will use generics-sop to generate them from the names used in the Haskell declaration. We don't have to do it that way though, and can specify a different implementation, if the names don't align. (I was expecting this all along so I was secretly careful to call my SQL field names the same as my Haskell field names).

There are three instances of SELECT to be replaced in the above style.

It would be nice if you could do this for the single UPDATE too, but the master version of postgres-simple-sop doesn't provide that. So next post, I'll dig into what is happening inside a bit more, and write my own UPDATE variation of gselectFrom.

Can you swim? (11)

The last post added a swim boolean column to the database, but that is ignored by everything on the Haskell side. The code will compile and run, but silently ignore this extra column.

You can try this out, but remember that we deleted all the registrations in the last post so you might need to INSERT some more, just like in this earlier post.

I want it to appear as a checkbox on the registration form.

So here's a list of things that need changing:

Modify the Registration data type in src/Registration.hs. Add on a new field there:

  swim :: Bool

This now breaks compilation - the resulting type errors will lead us towards some (but not all) of the places we need to make changes.

The first type error reports something like:

Couldn't match type `Bool -> Registration' with `Registration'

... in the definition for registrationDigestiveForm. This is where digestive-functors is told about all the fields in a Registration. The three existing fields have all been string based, using wrappers around DF.string. There's a fairly obviously named DF.bool that can be used in a similar fashion. There won't be any extra validation on this field, so we don't need any validation wrappers like dateLikeString.

The next compile error is related to CSV generation:

    * No instance for (CSV.ToField Bool)

That means that cassava, the CSV library, doesn't know how to render a Haskell bool into a CSV file.

So we can write this in a new module in src/Orphans.hs. add cassava as a library dependency in package.yaml, and then import Orphans in app/Main.hs.

module Orphans where

import qualified Data.Csv as CSV

instance CSV.ToField Bool
  where
    toField bool = CSV.toField (show bool)

... which defines an orphan instance, generally regarded as a bad thing. So don't do this unless you're lazy. We're going to be lazy. (a good thing to do is define this either where Bool is defined or where ToField is defined - which we can't do without editing the source for those - or define a new wrapper datatype and define the instance next to that). At least, I'm trying to keep any orphan instances in one place.

What the instance above does is turn the Bool into a String, and then uses the existing cassava-provided code to render that String into CSV. (render is a bit of a pretentious term for that - pretty much all it does is dump the string into the CSV file with a few escapes for " and ,).

So now the code compiles! And the server starts up!

But when we view a registration (for example http://localhost:8080/registration/1) there's a runtime server error - which I have formatted for easier reading:

ConversionFailed {
  errSQLType = "3 values: [\"text\",\"text\",\"text\"]",
  errSQLTableOid = Nothing,
  errSQLField = "",
  errHaskellType = "at least 4 slots in target type",
  errMessage = "mismatch between number of columns to convert and number in target type"
}

Where is your compile time static type checking god now?

What's happened is that the SQL SELECT statement asks for three fields (firstname, lastname and dob), and then tries to turn those into a Registration, which used to have three fields too. But now it has four.

The most immediate solution is to fix that SELECT, and all the others, and the one UPDATE too, to list all four fields.

But that *still* doesn't work. Yet another undetected type mismatch is that in a Haskell Registration, the swim boolean always has a value - no nulls. But the swim column in the database is nullable, as PostgreSQL columns are nullable by default. Whoops.

It's too late to go fix this in the last posts though so I'll use the lovely migration infrastructure to fix it now:

Create migrations/0002-swim-mandatory.sql:

UPDATE registration SET swim = FALSE WHERE swim IS NULL;
ALTER TABLE registration ALTER COLUMN swim SET DEFAULT FALSE;
ALTER TABLE registration ALTER COLUMN swim SET NOT NULL;

... and ensure that migration is applied:

$ stack exec migrate migrate user=postgres migrations/

There's a data modelling issue here: I might like partially completed registrations to be stored in the database without an answer for this question rather than defaulting one way or another. In that case, a better Haskell representation might be Maybe Bool. I'll ignore that for now and maybe come back to the issue much later.

At this point, we can view and edit registrations again. But the swim field doesn't appear in the HTML form. So here's another place where the type system doesn't detect that we're missing fields (although it's more reasonable to expect that a web form might not include all of the fields in a database row, so we can't be too annoyed).

digestive-functor's view of a form is gatewayed to HTML in the htmlForRegistration, and that's where we'll add in the new swim field. Previously we used DB.inputText for next. There's a similar inputCheckbox that will give a checkbox (surprise).

htmlForRegistration view =
...
      B.p $ do  "Date of Birth: "
                DB.errorList "dob" view
                DB.inputText "dob" view
      B.p $ do  "Can participant swim?: "
                DB.errorList "swim" view
                DB.inputCheckbox "swim" view
      B.p $     DB.inputSubmit "Save"

With this change, we now can edit the swim field, see it saved in the database, and see it successfully exported in CSV.

Next post I'm going to look at getting rid of some of the manual work / opportunities for mistakes around those SQL SELECT statements; and the changes for today's post are in commit e8848d2dd.

Changing the SQL schema (10)

The database so far is pretty simple: there are a couple of name fields, and a date of birth. Eventually, I'd like to add in a load of other fields.

One way to do this is to over-engineer an Inner Platform that allows some non-programmer admin to add in fields as they see fit. Another way is to hardcode the choice of fields into the codebase, and that is the way I'm going to proceed.That means to customise for a particular event, you need to fork the codebase and change stuff all the way through the code, which is icky, but this is really only for a couple of events for now.

As an example, I'd like to ask if a registrant can swim, and store the result as a PostgreSQL BOOLEAN column.

On the database side, there are a couple of ways to add this column: if you don't care about the data in your database, modify the appropriate CREATE statement in tables.sql, delete the database and create a new fresh database.

If you have any data, that's not so good - all the previously registered registrations will disappear.

Instead you can modify the schema of a database using PostgreSQL's ALTER command. For example:

postgres=# ALTER TABLE registration ADD COLUMN swim BOOLEAN;
ALTER TABLE
postgres=#

This doesn't lose existing registration, so is better.

But now for every deployment of the app, you need to remember to run the appropriate ALTER statements. (and yes there will be more than one deployment, even for such a small use case: there's a copy on my laptop for hacking on, and the copy people will use for real).

As a programmer, who cares? You throw the software over the wall to the lazy incompetant ops team, and leave it to them to screw up following your poorly written upgrade instructions. As an ops person, though, you'd much rather the programmers wrote their poorly written upgrade instructions in something that had actually been tested by the testing system before being passed on to you to discover the bugs.

Fear not! There's a pretty standard way of handling this, and there's a Haskell implementation called postgresql-simple-migration. Install that now with stack build postgresql-simple-migration.

What we do is write out each bit of SQL that needs to run in a series of files in a directory. When a new SQL statement needs running on all existing databases, put that in a new file in that directory. Let postgresql-simple-migration handle the tracking and executing of the commands when each installation of the application is upgraded.

To begin, set this up so that it will just create the same database schema as we've been using already: make a directory called migrations/ and rename the existing tables.sql to migrations/0000-create.sql. Then delete the existing database and let postgresql-simple-migrations take charge:

psql --user=postgres -c "DROP TABLE registration;"

stack exec migrate init user=postgres
Initializing schema

stack exec migrate migrate user=postgres migrations/
Execute:        0000-create.sql

If you run migrate again, it will report a different status for 0000-create:

stack exec migrate migrate user=postgres migrations/
Ok:     0000-create.sql

... which says that migrate didn't need to run that migration but that it is happy that it has been done already. So to get a nice mathsy term in, migrate is idempotent.

Now it's time to run that ALTER command to add the SWIM column.

Open your favourite SQL-editing text editor, or /bin/cat, and create a file called 0001-swim.sql containing the single line:

ALTER TABLE registration ADD COLUMN swim BOOLEAN;

... and run the migration command again:

stack exec migrate migrate user=postgres migrations/
Ok:     0000-create.sql
Execute:        0001-swim.sql

(Note the poor indentation. It's using \t tabs. We can have a debate about that. But we won't.)

migrate has seen that 0000-create has been run already, and that 0001-swim is new and so has executed it.

If this was a fresh database, both of the scripts would have been run, hopefully resulting in the same final schema. They're run in alphabetical order, so using a fixed-width sequence number gives an obvious way to put things in order until you run out of four digit integers. I've seen a date stamp commonly used too - I don't have a huge amount of opinion on the matter.

You need to be a bit careful here though. It is possible to add migrations out of order: for example, having added 0001-swim and had it deployed, I could add 0001-a which is *before* 0001-swim. Now, on a fresh database, they'll run in the order: 0000-create, 0001-a, 0001-swim. But in the database that has already been around a while, they'll have been applied in the order 0000-create, 0001-swim, 0001-a. This is the sort of thing that can happen with multiple developers working on different branches and merging their results without remembering to look out for this, although it's often the case that swapping the order doesn't matter much (for example, two column adds can happen in either order - the effect will be observable but shouldn't break properly written code).

Another degeneracy is when someone messes with existing migrations in version control to "fix" a problem that has happened. This can easily lead to database schemas in existence which can't be recreated by any one version of the software, an unfortunate circumstance.

And yet another downside is we now don't have a single set of CREATE statements to create the final database schema. Instead you have to run all the migrations and observe them in a live database.

Nevertheless, it is a pretty simple, and very common, solution to the problem of SQL schema upgrades.

It's also possible to make migrations happen at the beginning of reg-exe. That makes sense if there is one copy of reg-exe pointing at the database, and nothing else.

But did you know you can run *two* copies of reg-exe pointing at the same database? (or at least, I hope so - I haven't tried it). Magic scalability. In that case, it doesn't (always) make sense for an older version of the software to be talking to a database with a newer schema.

So although I'm not expecting to have super-scalability with this project, I still have a personal preference to manage the migrations separately from other processes which touch the database - even though it's one more deployment step to fuck up.

Here's the commit for this post - 599735f5. In the next post, I'll go over the changes to the Haskell code needed to support this new swim field.

ps. My notes for this post also say "some waffle about how the migrations are like a fold, done over database schema".

Suggesting a filename for a downloaded file (9)

Last post, I got the code generating CSVs but (at least in my case) my browser chooses a lame filename: just "csv". I'd much rather it chose something like "registrations.csv". (It decided on "csv" because that was the last component of the URL path.

One way to accomplish this is by adding on a new HTTP header called Content-Disposition.

Here's a change to the API to specify that header:

type CSVAPI = "admin" :> "csv" :> S.Get '[SC.CSV] (S.Headers '[S.Header "Content-Disposition" String] [Registration])

This changes the previous CSVAPI in one place - the type that servant will expect back from the handler code. Why? Because in addition to [Registration] to be serialised, we also need to return a value to go into this content-disposition header. servant is going to check at compile time that we have supplied headers as required by the API.

The updated handleCSV still needs to SELECT the registrations from the database, but needs to wrap those values with a call to addHeader.

handleCSV :: S.Handler (S.Headers '[S.Header "Content-Disposition" String] [Registration])
handleCSV = do
  rs <- liftIO $ bracket
    (PG.connectPostgreSQL "user='postgres'")
    PG.close
    $ \conn -> do
      PG.query
        conn
        "SELECT firstname, lastname, dob FROM registration" ()
  return $ S.addHeader "attachment;filename=\"registrations.csv\"" rs

That was a pretty small change, but now http://localhost:8080/admin/csv should result in a CSV file with a better filename. As the filename is specified as part of the return value of the handler, it should be straightforward to (for example) put a date/time stamp in the filename, or something else like that to disambiguate multiple downloads over time.