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.