From fff99e6fb1c03235e219a94ce52acf5a50d3fb62 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sun, 5 Jan 2020 16:03:48 +0100
Subject: Use named parameters instead of positional parameters in persistence
 queries

---
 server/src/Persistence/Income.hs | 56 ++++++++++++++++++++++++----------------
 server/src/Persistence/User.hs   |  9 ++++---
 2 files changed, 40 insertions(+), 25 deletions(-)

(limited to 'server/src')

diff --git a/server/src/Persistence/Income.hs b/server/src/Persistence/Income.hs
index cd98814..76cb952 100644
--- a/server/src/Persistence/Income.hs
+++ b/server/src/Persistence/Income.hs
@@ -17,8 +17,7 @@ import qualified Data.Text              as T
 import           Data.Time.Calendar     (Day)
 import           Data.Time.Clock        (UTCTime)
 import           Data.Time.Clock        (getCurrentTime)
-import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)),
-                                         Only (Only))
+import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)))
 import qualified Database.SQLite.Simple as SQLite
 import           Prelude                hiding (id, until)
 
@@ -55,63 +54,76 @@ list :: Int -> Int -> Query [Income]
 list page perPage =
   Query (\conn ->
     map (\(Row i) -> i) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
-        "SELECT * FROM income WHERE deleted_at IS NULL ORDER BY date DESC LIMIT ? OFFSET ?"
-        (perPage, (page - 1) * perPage)
+        "SELECT * FROM income WHERE deleted_at IS NULL ORDER BY date DESC LIMIT :limit OFFSET :offset"
+        [ ":limit" := perPage
+        , ":offset" := (page - 1) * perPage
+        ]
   )
 
 listModifiedSince :: UTCTime -> Query [Income]
 listModifiedSince since =
   Query (\conn ->
     map (\(Row i) -> i) <$>
-      SQLite.query
+      SQLite.queryNamed
         conn
         (SQLite.Query . T.intercalate " " $
           [ "SELECT *"
           , "FROM income"
           , "WHERE"
-          ,   "created_at >= ?"
-          ,   "OR edited_at >= ?"
-          ,   "OR deleted_at >= ?"
+          ,   "created_at >= :since"
+          ,   "OR edited_at >= :since"
+          ,   "OR deleted_at >= :since"
           ])
-        (since, since, since)
+        [ ":since" := since ]
   )
 
 create :: UserId -> Day -> Int -> Query ()
 create userId date amount =
   Query (\conn -> do
     createdAt <- getCurrentTime
-    SQLite.execute
+    SQLite.executeNamed
       conn
-      "INSERT INTO income (user_id, date, amount, created_at) VALUES (?, ?, ?, ?)"
-      (userId, date, amount, createdAt)
+      "INSERT INTO income (user_id, date, amount, created_at) VALUES (:userId, :date, :amount, :createdAt)"
+      [ ":userId" := userId
+      , ":date" := date
+      , ":amount" := amount
+      , ":createdAt" := createdAt
+      ]
   )
 
 edit :: UserId -> IncomeId -> Day -> Int -> Query Bool
-edit userId incomeId incomeDate incomeAmount =
+edit userId id date amount =
   Query (\conn -> do
     income <- fmap (\(Row i) -> i) . Maybe.listToMaybe <$>
-      SQLite.query conn "SELECT * FROM income WHERE id = ?" (Only incomeId)
+      SQLite.queryNamed conn "SELECT * FROM income WHERE id = :id" [ ":id" := id ]
     if Maybe.isJust income then
       do
         currentTime <- getCurrentTime
-        SQLite.execute
+        SQLite.executeNamed
           conn
-          "UPDATE income SET edited_at = ?, date = ?, amount = ? WHERE id = ? AND user_id = ?"
-          (currentTime, incomeDate, incomeAmount, incomeId, userId)
+          "UPDATE income SET edited_at = :editedAt, date = :date, amount = :amount WHERE id = :id AND user_id = :userId"
+          [ ":editedAt" := currentTime
+          , ":date" := date
+          , ":amount" := amount
+          , ":id" := id
+          , ":userId" := userId
+          ]
         return True
     else
       return False
   )
 
 delete :: UserId -> PaymentId -> Query ()
-delete userId paymentId =
+delete userId id =
   Query (\conn ->
-    SQLite.execute
+    SQLite.executeNamed
       conn
-      "UPDATE income SET deleted_at = datetime('now') WHERE id = ? AND user_id = ?"
-      (paymentId, userId)
+      "UPDATE income SET deleted_at = datetime('now') WHERE id = :id AND user_id = :userId"
+      [ ":id" := id
+      , ":userId" := userId
+      ]
   )
 
 data UserDayRow = UserDayRow (UserId, Day)
diff --git a/server/src/Persistence/User.hs b/server/src/Persistence/User.hs
index 3c3a2b1..89eb57d 100644
--- a/server/src/Persistence/User.hs
+++ b/server/src/Persistence/User.hs
@@ -5,7 +5,7 @@ module Persistence.User
 
 import qualified Data.Maybe             as Maybe
 import           Data.Text              (Text)
-import           Database.SQLite.Simple (FromRow (fromRow), Only (Only))
+import           Database.SQLite.Simple (FromRow (fromRow), NamedParam ((:=)))
 import qualified Database.SQLite.Simple as SQLite
 import           Prelude                hiding (id)
 
@@ -30,8 +30,11 @@ list =
   )
 
 get :: Text -> Query (Maybe User)
-get userEmail =
+get email =
   Query (\conn -> do
     fmap (\(Row u) -> u) . Maybe.listToMaybe <$>
-      SQLite.query conn "SELECT * FROM user WHERE email = ? LIMIT 1" (Only userEmail)
+      SQLite.queryNamed
+        conn
+        "SELECT * FROM user WHERE email = :email LIMIT 1"
+        [ ":email" := email ]
   )
-- 
cgit v1.2.3