dani-sqlite-0.1.1.0: test-query/Query.hs

{-# LANGUAGE CPP #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE RecordWildCards #-}

module Query
  ( testSimpleOnePlusOne,
    testSimpleSelect,
    testOutOfRangeParserSelect,
    testSimpleParams,
    testSimpleInsertId,
    testSimpleMultiInsert,
    testSimpleQueryCov,
    testSimpleStrings,
    testSimpleChanges,
  )
where

-- orphan IsString instance in older byteString

import Common
import Control.Exception
import Data.ByteString qualified as BS
import Data.ByteString.Lazy qualified as LBS
import Data.ByteString.Lazy.Char8 ()
import Data.Text qualified as T
import Data.Text.Lazy qualified as LT
import Test.Tasty.HUnit
import Test.Tasty.HUnit qualified as Tasty

-- Simplest SELECT
testSimpleOnePlusOne :: IO TestEnv -> Tasty.Assertion
testSimpleOnePlusOne ioenv = do
  TestEnv {..} <- ioenv
  rows <- select_ conn "SELECT 1+1" :: IO [Solo Int]
  Tasty.assertEqual "row count" 1 (length rows)
  Tasty.assertEqual "value" (MkSolo 2) (head rows)

testSimpleSelect :: IO TestEnv -> Tasty.Assertion
testSimpleSelect ioenv = do
  TestEnv {..} <- ioenv
  execute_ conn "CREATE TABLE test1 (id INTEGER PRIMARY KEY, t TEXT)"
  execute_ conn "INSERT INTO test1 (t) VALUES ('test string')"
  rows <- select_ conn "SELECT t FROM test1" :: IO [Solo String]
  Tasty.assertEqual "row count" 1 (length rows)
  Tasty.assertEqual "string" (MkSolo "test string") (head rows)
  rows <- select_ conn "SELECT id,t FROM test1" :: IO [(Int, String)]
  Tasty.assertEqual "int,string" (1, "test string") (head rows)
  -- Add another row
  execute_ conn "INSERT INTO test1 (t) VALUES ('test string 2')"
  rows <- select_ conn "SELECT id,t FROM test1" :: IO [(Int, String)]
  Tasty.assertEqual "row count" 2 (length rows)
  Tasty.assertEqual "int,string" (1, "test string") (rows !! 0)
  Tasty.assertEqual "int,string" (2, "test string 2") (rows !! 1)
  [MkSolo r] <- select_ conn "SELECT NULL" :: IO [Solo (Maybe Int)]
  Tasty.assertEqual "nulls" Nothing r
  [MkSolo r] <- select_ conn "SELECT 1" :: IO [Solo (Maybe Int)]
  Tasty.assertEqual "nulls" (Just 1) r
  [MkSolo r] <- select_ conn "SELECT 1.0" :: IO [Solo Double]
  Tasty.assertEqual "doubles" 1.0 r
  [MkSolo r] <- select_ conn "SELECT 1.0" :: IO [Solo Float]
  Tasty.assertEqual "floats" 1.0 r

testOutOfRangeParserSelect :: IO TestEnv -> Tasty.Assertion
testOutOfRangeParserSelect ioenv = do
  TestEnv {..} <- ioenv
  e <- try @ResultError (select_ conn "SELECT 1" :: IO [(Int, Int)])
  case e of
    Left (ConversionFailed {}) -> pure ()
    Left _ -> assertFailure "Actually, we expected another type of error here!"
    Right _ -> assertFailure "Actually, we expected an error here!"

testSimpleParams :: IO TestEnv -> Tasty.Assertion
testSimpleParams ioenv = do
  TestEnv {..} <- ioenv
  execute_ conn "CREATE TABLE testparams (id INTEGER PRIMARY KEY, t TEXT)"
  execute_ conn "CREATE TABLE testparams2 (id INTEGER, t TEXT, t2 TEXT)"
  [MkSolo i] <- select conn "SELECT ?" (MkSolo (42 :: Int)) :: IO [Solo Int]
  Tasty.assertEqual "select int param" 42 i
  execute conn "INSERT INTO testparams (t) VALUES (?)" (MkSolo ("test string" :: String))
  rows <- select conn "SELECT t FROM testparams WHERE id = ?" (MkSolo (1 :: Int)) :: IO [Solo String]
  Tasty.assertEqual "row count" 1 (length rows)
  Tasty.assertEqual "string" (MkSolo "test string") (head rows)
  execute_ conn "INSERT INTO testparams (t) VALUES ('test2')"
  [MkSolo row] <- select conn "SELECT t FROM testparams WHERE id = ?" (MkSolo (1 :: Int)) :: IO [Solo String]
  Tasty.assertEqual "select params" "test string" row
  [MkSolo row] <- select conn "SELECT t FROM testparams WHERE id = ?" (MkSolo (2 :: Int)) :: IO [Solo String]
  Tasty.assertEqual "select params" "test2" row
  [MkSolo r1, MkSolo r2] <- select conn "SELECT t FROM testparams WHERE (id = ? OR id = ?)" (1 :: Int, 2 :: Int) :: IO [Solo String]
  Tasty.assertEqual "select params" "test string" r1
  Tasty.assertEqual "select params" "test2" r2
  [MkSolo i] <- select conn "SELECT ?+?" [42 :: Int, 1 :: Int] :: IO [Solo Int]
  Tasty.assertEqual "select int param" 43 i
  [MkSolo d] <- select conn "SELECT ?" [2.0 :: Double] :: IO [Solo Double]
  Tasty.assertEqual "select double param" 2.0 d
  [MkSolo f] <- select conn "SELECT ?" [4.0 :: Float] :: IO [Solo Float]
  Tasty.assertEqual "select double param" 4.0 f

testSimpleInsertId :: IO TestEnv -> Tasty.Assertion
testSimpleInsertId ioenv = do
  TestEnv {..} <- ioenv
  execute_ conn "CREATE TABLE test_row_id (id INTEGER PRIMARY KEY, t TEXT)"
  execute conn "INSERT INTO test_row_id (t) VALUES (?)" (MkSolo ("test string" :: String))
  id1 <- lastInsertRowId conn
  execute_ conn "INSERT INTO test_row_id (t) VALUES ('test2')"
  id2 <- lastInsertRowId conn
  1 @=? id1
  2 @=? id2
  rows <- select conn "SELECT t FROM test_row_id WHERE id = ?" (MkSolo (1 :: Int)) :: IO [Solo String]
  1 @=? (length rows)
  (MkSolo "test string") @=? (head rows)
  [MkSolo row] <- select conn "SELECT t FROM test_row_id WHERE id = ?" (MkSolo (2 :: Int)) :: IO [Solo String]
  "test2" @=? row

testSimpleMultiInsert :: IO TestEnv -> Tasty.Assertion
testSimpleMultiInsert ioenv = do
  TestEnv {..} <- ioenv
  execute_ conn "CREATE TABLE test_multi_insert (id INTEGER PRIMARY KEY, t1 TEXT, t2 TEXT)"
  executeMany conn "INSERT INTO test_multi_insert (t1, t2) VALUES (?, ?)" ([("foo", "bar"), ("baz", "bat")] :: [(String, String)])
  id2 <- lastInsertRowId conn
  2 @=? id2

  rows <- select_ conn "SELECT id,t1,t2 FROM test_multi_insert" :: IO [(Int, String, String)]
  [(1, "foo", "bar"), (2, "baz", "bat")] @=? rows

testSimpleQueryCov :: IO TestEnv -> Tasty.Assertion
testSimpleQueryCov ioenv = do
  TestEnv {..} <- ioenv
  let str = "SELECT 1+1" :: T.Text
      q = "SELECT 1+1" :: Sql
  sqlText q @=? str
  show str @=? show q
  q @=? ((read . show $ q) :: Sql)
  q @=? q
  q @=? (Sql "SELECT 1" <> Sql "+1")
  q @=? foldr mappend mempty ["SELECT ", "1", "+", "1"]
  True @=? q <= q

testSimpleStrings :: IO TestEnv -> Tasty.Assertion
testSimpleStrings ioenv = do
  TestEnv {..} <- ioenv
  [MkSolo s] <- select_ conn "SELECT 'str1'" :: IO [Solo T.Text]
  s @=? "str1"
  [MkSolo s] <- select_ conn "SELECT 'strLazy'" :: IO [Solo LT.Text]
  s @=? "strLazy"
  [MkSolo s] <- select conn "SELECT ?" (MkSolo ("strP" :: T.Text)) :: IO [Solo T.Text]
  s @=? "strP"
  [MkSolo s] <- select conn "SELECT ?" (MkSolo ("strPLazy" :: LT.Text)) :: IO [Solo T.Text]
  s @=? "strPLazy"
  -- ByteStrings are blobs in sqlite storage, so use ByteString for
  -- both input and output
  [MkSolo s] <- select conn "SELECT ?" (MkSolo ("strBsP" :: BS.ByteString)) :: IO [Solo BS.ByteString]
  s @=? "strBsP"
  [MkSolo s] <- select conn "SELECT ?" (MkSolo ("strBsPLazy" :: LBS.ByteString)) :: IO [Solo BS.ByteString]
  s @=? "strBsPLazy"
  [MkSolo s] <- select conn "SELECT ?" (MkSolo ("strBsPLazy2" :: BS.ByteString)) :: IO [Solo LBS.ByteString]
  s @=? "strBsPLazy2"

testSimpleChanges :: IO TestEnv -> Tasty.Assertion
testSimpleChanges ioenv = do
  TestEnv {..} <- ioenv
  execute_ conn "CREATE TABLE testchanges (id INTEGER PRIMARY KEY, t TEXT)"
  execute conn "INSERT INTO testchanges(t) VALUES (?)" (MkSolo ("test string" :: String))
  numChanges <- changes conn
  Tasty.assertEqual "changed/inserted rows" 1 numChanges
  execute conn "INSERT INTO testchanges(t) VALUES (?)" (MkSolo ("test string 2" :: String))
  numChanges <- changes conn
  Tasty.assertEqual "changed/inserted rows" 1 numChanges
  execute_ conn "UPDATE testchanges SET t = 'foo' WHERE id = 1"
  numChanges <- changes conn
  Tasty.assertEqual "changed/inserted rows" 1 numChanges
  execute_ conn "UPDATE testchanges SET t = 'foo' WHERE id = 100"
  numChanges <- changes conn
  Tasty.assertEqual "changed/inserted rows" 0 numChanges
  execute_ conn "UPDATE testchanges SET t = 'foo'"
  numChanges <- changes conn
  Tasty.assertEqual "changed/inserted rows" 2 numChanges