{-# LANGUAGE DataKinds #-}
{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE DeriveGeneric #-}
{-# LANGUAGE DerivingStrategies #-}
{-# LANGUAGE DuplicateRecordFields #-}
{-# LANGUAGE ExplicitForAll #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE QuasiQuotes #-}
{-# LANGUAGE TypeApplications #-}
{-# LANGUAGE TypeOperators #-}
-- {-# OPTIONS_GHC -ddump-splices #-}
module Main (main) where
import Data.Aeson (Value)
import Data.Int (Int32, Int64)
import Data.Text (Text)
import Data.Time (Day, UTCTime)
import Data.UUID (UUID)
import Data.Word (Word64)
import GHC.Generics (Generic)
import Prelude
( Applicative(pure), Eq((==)), Maybe(Just, Nothing), MonadFail(fail)
, Semigroup((<>)), Show(show), ($), (.), Bool, IO, putStrLn
)
import Squeal.PostgreSQL
( NullType(NotNull, Null), Optionality(Def, NoDef)
, PGType(PGint4, PGjson, PGjsonb, PGtext, PGuuid), RenderSQL(renderSQL)
, SchemumType(Table), TableConstraint(ForeignKey, PrimaryKey), (:::), (:=>)
, Json, Jsonb, Only, Statement
)
import Squeal.QuasiQuotes (Field, ssql)
import Test.Hspec (describe, hspec, it)
import qualified Data.Text as T
import qualified Data.Text.Encoding as TE
import qualified Generics.SOP as SOP
{- FOURMOLU_DISABLE -}
{- Copied mostly from the squeal documentation: -}
type UsersColumns =
'[ "id" ::: 'Def :=> 'NotNull 'PGtext
, "name" ::: 'NoDef :=> 'NotNull 'PGtext
, "employee_id" ::: 'NoDef :=> 'NotNull 'PGuuid
, "bio" ::: 'NoDef :=> 'Null 'PGtext
]
type UsersConstraints = '[ "pk_users" ::: 'PrimaryKey '["id"] ]
type EmailsColumns =
'[ "id" ::: 'Def :=> 'NotNull 'PGint4
, "user_id" ::: 'NoDef :=> 'NotNull 'PGtext
, "email" ::: 'NoDef :=> 'Null 'PGtext
]
type EmailsConstraints =
'[ "pk_emails" ::: 'PrimaryKey '["id"]
, "fk_user_id" ::: 'ForeignKey '["user_id"] "public" "users" '["id"]
]
type Schema =
'[ "users" ::: 'Table (UsersConstraints :=> UsersColumns)
, "emails" ::: 'Table (EmailsConstraints :=> EmailsColumns)
, "jsonb_test" ::: 'Table ('[] :=> '["data" ::: 'NoDef :=> 'NotNull 'PGjsonb])
, "json_test" ::: 'Table ('[] :=> '["data" ::: 'NoDef :=> 'NotNull 'PGjson])
, "users_copy" ::: 'Table (UsersCopyConstraints :=> UsersCopyColumns)
]
type UsersCopyColumns =
'[ "id" ::: 'NoDef :=> 'NotNull 'PGtext
, "name" ::: 'NoDef :=> 'NotNull 'PGtext
, "bio" ::: 'NoDef :=> 'Null 'PGtext
]
type UsersCopyConstraints = '[ "pk_users_copy" ::: 'PrimaryKey '["id"] ]
type DB =
'[ "public" ::: Schema
, "other" ::: Schema
]
{- FOURMOLU_ENABLE -}
data User = User
{ id :: Text
, name :: Text
}
deriving stock (Generic, Show)
deriving anyclass (SOP.Generic, SOP.HasDatatypeInfo)
main :: IO ()
main =
hspec $ do
describe "queries" $ do
it "select * from users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select * from public.users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from public.users |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "SELECT * FROM \"users\" AS \"users\"" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| SELECT * FROM "users" AS "users" |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select * from users where name = 'bob'" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users where name = 'bob' |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" WHERE (\"name\" = (E'bob' :: text))"
checkStatement squealRendering statement
it "select * from users where id = $1" $ do
let
statement
:: Statement
DB
(Only Text)
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users where id = $1 |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" WHERE (\"id\" = ($1 :: text))"
checkStatement squealRendering statement
it "select users.name from users" $ do
let
statement :: Statement DB () (Field "name" Text, ())
statement = [ssql| select users.name from users |]
squealRendering :: Text
squealRendering = "SELECT \"users\".\"name\" AS \"name\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select name from users" $ do
let
statement :: Statement DB () (Field "name" Text, ())
statement = [ssql| select name from users |]
squealRendering :: Text
squealRendering = "SELECT \"name\" AS \"name\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select count(*) from users group by ()" $ do
let
statement :: Statement DB () (Field "_col1" Int64, ())
statement = [ssql| select count(*) from users group by () |]
squealRendering :: Text
squealRendering = "SELECT count(*) AS \"_col1\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select name, id from users" $ do
let
statement
:: Statement
DB
()
( Field "name" Text
, ( Field "id" Text
, ()
)
)
statement = [ssql| select name, id from users |]
squealRendering :: Text
squealRendering = "SELECT \"name\" AS \"name\", \"id\" AS \"id\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select id, name from users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ()
)
)
statement = [ssql| select id, name from users |]
squealRendering :: Text
squealRendering = "SELECT \"id\" AS \"id\", \"name\" AS \"name\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select users.id, employee_id from users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "employee_id" UUID
, ()
)
)
statement = [ssql| select users.id, employee_id from users |]
squealRendering :: Text
squealRendering =
"SELECT \"users\".\"id\" AS \"id\", \"employee_id\" AS \"employee_id\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select users.* from users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select users.* from users |]
squealRendering :: Text
squealRendering = "SELECT \"users\".* FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select users.* from other.users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select users.* from other.users |]
squealRendering :: Text
squealRendering = "SELECT \"users\".* FROM \"other\".\"users\" AS \"users\""
checkStatement squealRendering statement
it "select * from users limit 3" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users limit 3 |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" LIMIT 3"
checkStatement squealRendering statement
it "select * from users limit inline(lim)" $ do
let
mkStatement
:: Word64
-> Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
mkStatement lim =
[ssql| select * from users limit inline(lim) |]
squealRendering1 :: Text
squealRendering1 = "SELECT * FROM \"users\" AS \"users\" LIMIT 10"
squealRendering2 :: Text
squealRendering2 = "SELECT * FROM \"users\" AS \"users\" LIMIT 20"
checkStatement squealRendering1 (mkStatement 10)
checkStatement squealRendering2 (mkStatement 20)
it "select * from users offset inline(off)" $ do
let
mkStatement
:: Word64
-> Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
mkStatement off =
[ssql| select * from users offset inline(off) |]
squealRendering1 :: Text
squealRendering1 = "SELECT * FROM \"users\" AS \"users\" OFFSET 5"
squealRendering2 :: Text
squealRendering2 = "SELECT * FROM \"users\" AS \"users\" OFFSET 15"
checkStatement squealRendering1 (mkStatement 5)
checkStatement squealRendering2 (mkStatement 15)
it "select * from users offset 1" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users offset 1 |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" OFFSET 1"
checkStatement squealRendering statement
it "select users.id, employee_id as emp_id from users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "emp_id" UUID
, ()
)
)
statement = [ssql| select users.id, employee_id as emp_id from users |]
squealRendering :: Text
squealRendering =
"SELECT \"users\".\"id\" AS \"id\", \"employee_id\" AS \"emp_id\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select users.id as user_id, employee_id from users" $ do
let
statement
:: Statement
DB
()
( Field "user_id" Text
, ( Field "employee_id" UUID
, ()
)
)
statement = [ssql| select users.id as user_id, employee_id from users |]
squealRendering :: Text
squealRendering =
"SELECT \"users\".\"id\" AS \"user_id\", \"employee_id\" AS \"employee_id\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it
"select users.id from users left outer join emails on emails.user_id = users.id"
$ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ()
)
statement =
[ssql|
select users.id
from users
left outer join emails
on emails.user_id = users.id
|]
squealRendering :: Text
squealRendering =
"SELECT \"users\".\"id\" AS \"id\" FROM \"users\" AS \"users\" LEFT OUTER JOIN \"emails\" AS \"emails\" ON (\"emails\".\"user_id\" = \"users\".\"id\")"
checkStatement squealRendering statement
it
"select users.id, users.name, emails.email from users left outer join emails on emails.user_id = users.id where emails.email = inline(\"targetEmail\")"
$ do
let
targetEmail :: Text
targetEmail = "foo@bar.com"
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "email" (Maybe Text)
, ()
)
)
)
statement =
[ssql|
select users.id, users.name, emails.email
from users
left outer join emails
on emails.user_id = users.id
where emails.email = inline("targetEmail")
|]
squealRendering :: Text
squealRendering =
"SELECT \"users\".\"id\" AS \"id\", \"users\".\"name\" AS \"name\", \"emails\".\"email\" AS \"email\" FROM \"users\" AS \"users\" LEFT OUTER JOIN \"emails\" AS \"emails\" ON (\"emails\".\"user_id\" = \"users\".\"id\") WHERE (\"emails\".\"email\" = (E'foo@bar.com' :: text))"
checkStatement squealRendering statement
it "select 'text_val'" $ do
let
statement
:: Statement
DB
()
( Field "_col1" Text
, ()
)
statement = [ssql| select 'text_val' |]
squealRendering :: Text
squealRendering = "SELECT * FROM (VALUES ((E'text_val' :: text))) AS t (\"_col1\")"
checkStatement squealRendering statement
it "select 1" $ do
let
statement
:: Statement
DB
()
( Field "_col1" Int64
, ()
)
statement = [ssql| select 1 |]
squealRendering :: Text
squealRendering = "SELECT * FROM (VALUES (1)) AS t (\"_col1\")"
checkStatement squealRendering statement
it "select 1 AS num, 'text_val' AS txt" $ do
let
statement
:: Statement
DB
()
( Field "num" Int64
, ( Field "txt" Text
, ()
)
)
statement = [ssql| select 1 AS num, 'text_val' AS txt |]
squealRendering :: Text
squealRendering = "SELECT * FROM (VALUES (1, (E'text_val' :: text))) AS t (\"num\", \"txt\")"
checkStatement squealRendering statement
describe "group by" $ do
it "select name from users group by name" $ do
let
statement :: Statement DB () (Field "name" Text, ())
statement = [ssql| select name from users group by name |]
squealRendering :: Text
squealRendering = "SELECT \"name\" AS \"name\" FROM \"users\" AS \"users\" GROUP BY \"name\""
checkStatement squealRendering statement
it "select employee_id, count(id) from users group by employee_id" $ do
let
statement
:: Statement
DB
()
( Field "employee_id" UUID
, ( Field "_col2" Int64 -- Assuming count returns Int64
, ()
)
)
statement = [ssql| select employee_id, count(id) from users group by employee_id |]
squealRendering :: Text
squealRendering =
"SELECT \"employee_id\" AS \"employee_id\", count(ALL \"id\") AS \"_col2\" FROM \"users\" AS \"users\" GROUP BY \"employee_id\""
checkStatement squealRendering statement
it "select employee_id, name, count(id) from users group by employee_id, name" $ do
let
statement
:: Statement
DB
()
( Field "employee_id" UUID
, ( Field "name" Text
, ( Field "_col3" Int64
, ()
)
)
)
statement =
[ssql| select employee_id, name, count(id) from users group by employee_id, name |]
squealRendering :: Text
squealRendering =
"SELECT \"employee_id\" AS \"employee_id\", \"name\" AS \"name\", count(ALL \"id\") AS \"_col3\" FROM \"users\" AS \"users\" GROUP BY \"employee_id\", \"name\""
checkStatement squealRendering statement
describe "common table expressions" $ do
it "with users_cte as (select * from users) select * from users_cte" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| with users_cte as (select * from users) select * from users_cte |]
squealRendering :: Text
squealRendering =
"WITH \"users_cte\" AS (SELECT * FROM \"users\" AS \"users\") SELECT * FROM \"users_cte\" AS \"users_cte\""
checkStatement squealRendering statement
it
"with users_cte as (select * from users), emails_cte as (select * from emails) select users_cte.*, emails_cte.email from users_cte join emails_cte on users_cte.id = emails_cte.user_id"
$ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ( Field "email" (Maybe Text)
, ()
)
)
)
)
)
statement =
[ssql|
with
users_cte as (select * from users),
emails_cte as (select * from emails)
select users_cte.*, emails_cte.email
from users_cte
join emails_cte on users_cte.id = emails_cte.user_id
|]
squealRendering :: Text
squealRendering =
"WITH \"users_cte\" AS (SELECT * FROM \"users\" AS \"users\"), \"emails_cte\" AS (SELECT * FROM \"emails\" AS \"emails\") SELECT \"users_cte\".*, \"emails_cte\".\"email\" AS \"email\" FROM \"users_cte\" AS \"users_cte\" INNER JOIN \"emails_cte\" AS \"emails_cte\" ON (\"users_cte\".\"id\" = \"emails_cte\".\"user_id\")"
checkStatement squealRendering statement
describe "inserts" $ do
it "insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar')" $ do
let
statement
:: Statement DB () ()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (1, 'user-1', 'foo@bar')
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (1, (E'user-1' :: text), (E'foo@bar' :: text))"
checkStatement squealRendering statement
it "insert into emails (id, user_id, email) values (1, 'user-1', $1)" $ do
let
statement
:: Statement
DB
(Only (Maybe Text))
()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (1, 'user-1', $1)
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (1, (E'user-1' :: text), ($1 :: text))"
checkStatement squealRendering statement
it "insert into emails (id, user_id, email) values (1, $2, $1)" $ do
let
statement
:: Statement
DB
(Maybe Text, Text)
()
statement =
{-
Note the parameters are backwards (i.e. $2 comes before $1),
to test that you can do this kind of thing out of order.
-}
[ssql|
insert into
emails (id, user_id, email)
values (1, $2, $1)
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (1, ($2 :: text), ($1 :: text))"
checkStatement squealRendering statement
it "insert into users_copy (id, name, bio) values ($1, $2, $3)" $ do
let
statement
:: Statement
DB
(Text, Text, Maybe Text)
()
statement =
[ssql|
insert into
users_copy (id, name, bio)
values ($1, $2, $3)
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"users_copy\" AS \"users_copy\" (\"id\", \"name\", \"bio\") VALUES (($1 :: text), ($2 :: text), ($3 :: text))"
checkStatement squealRendering statement
it
"insert into emails (id, user_id, email) values (inline(i), inline(uid), inline_param(e))"
$ do
let
mkStatement :: Int32 -> Text -> Maybe Text -> Statement DB () ()
mkStatement i uid e =
[ssql|
insert into
emails (id, user_id, email)
values (inline(i), inline(uid), inline_param(e))
|]
checkStatement
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES ((1 :: int4), (E'user-1' :: text), NULL)"
(mkStatement 1 "user-1" Nothing)
checkStatement
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES ((1 :: int4), (E'user-1' :: text), (E'foo@bar.com' :: text))"
(mkStatement 1 "user-1" (Just "foo@bar.com"))
describe "default keyword" $ do
it "insert into emails (id, user_id, email) values (default, 'foo', 'bar')" $ do
let
statement
:: Statement
DB
(Maybe Text, Text)
()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (default, 'foo', 'bar')
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (DEFAULT, (E'foo' :: text), (E'bar' :: text))"
checkStatement squealRendering statement
it "insert into emails (id, user_id, email) values (deFault, 'foo', 'bar')" $ do
let
statement
:: Statement
DB
(Maybe Text, Text)
()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (deFault, 'foo', 'bar')
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (DEFAULT, (E'foo' :: text), (E'bar' :: text))"
checkStatement squealRendering statement
it "insert into emails (id, user_id, email) values (DEFAULT, 'foo', 'bar')" $ do
let
statement
:: Statement
DB
(Maybe Text, Text)
()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (DEFAULT, 'foo', 'bar')
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (DEFAULT, (E'foo' :: text), (E'bar' :: text))"
checkStatement squealRendering statement
describe "null keyword" $ do
it "insert into emails (id, user_id, email) values (DEFAULT, 'foo', null)" $ do
let
statement
:: Statement
DB
(Maybe Text, Text)
()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (DEFAULT, 'foo', null)
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (DEFAULT, (E'foo' :: text), NULL)"
checkStatement squealRendering statement
it "insert into emails (id, user_id, email) values (DEFAULT, 'foo', NULL)" $ do
let
statement
:: Statement
DB
(Maybe Text, Text)
()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (DEFAULT, 'foo', NULL)
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (DEFAULT, (E'foo' :: text), NULL)"
checkStatement squealRendering statement
it "insert into emails (id, user_id, email) values (DEFAULT, 'foo', NuLL)" $ do
let
statement
:: Statement
DB
(Maybe Text, Text)
()
statement =
[ssql|
insert into
emails (id, user_id, email)
values (DEFAULT, 'foo', NuLL)
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (DEFAULT, (E'foo' :: text), NULL)"
checkStatement squealRendering statement
describe "insert ... select ..." $ do
it "insert into emails select id, user_id, email from emails where id = 1" $ do
let
statement :: Statement DB () ()
statement =
[ssql|
insert into emails
select id, user_id, email from emails where id = 1
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" SELECT \"id\" AS \"id\", \"user_id\" AS \"user_id\", \"email\" AS \"email\" FROM \"emails\" AS \"emails\" WHERE (\"id\" = 1)"
checkStatement squealRendering statement
it "insert into emails select id, user_id, email from emails where id = $1" $ do
let
statement :: Statement DB (Only Int32) ()
statement =
[ssql|
insert into emails
select id, user_id, email from emails where id = $1
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" SELECT \"id\" AS \"id\", \"user_id\" AS \"user_id\", \"email\" AS \"email\" FROM \"emails\" AS \"emails\" WHERE (\"id\" = ($1 :: int4))"
checkStatement squealRendering statement
it
"insert into users_copy select id, name, bio from users where users.id = 'uid1'"
$ do
let
statement :: Statement DB () ()
statement =
[ssql|
insert into users_copy
select id, name, bio from users where users.id = 'uid1'
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"users_copy\" AS \"users_copy\" SELECT \"id\" AS \"id\", \"name\" AS \"name\", \"bio\" AS \"bio\" FROM \"users\" AS \"users\" WHERE (\"users\".\"id\" = (E'uid1' :: text))"
checkStatement squealRendering statement
describe "returning clause" $ do
it
"insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar') returning id"
$ do
let
statement
:: Statement DB () (Field "id" Int32, ())
statement =
[ssql|
insert into
emails (id, user_id, email)
values (1, 'user-1', 'foo@bar')
returning id
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (1, (E'user-1' :: text), (E'foo@bar' :: text)) RETURNING \"id\" AS \"id\""
checkStatement squealRendering statement
it
"insert into emails (id, user_id, email) values (1, 'user-1', 'foo@bar') returning *"
$ do
let
statement
:: Statement
DB
()
( Field "id" Int32
, ( Field "user_id" Text
, ( Field "email" (Maybe Text)
, ()
)
)
)
statement =
[ssql|
insert into
emails (id, user_id, email)
values (1, 'user-1', 'foo@bar')
returning *
|]
squealRendering :: Text
squealRendering =
"INSERT INTO \"emails\" AS \"emails\" (\"id\", \"user_id\", \"email\") VALUES (1, (E'user-1' :: text), (E'foo@bar' :: text)) RETURNING *"
checkStatement squealRendering statement
describe "with common table expressions" $ do
it
"with new_user (id, name, bio) as (values ('id_new', 'new_name', 'new_bio')) insert into users_copy select * from new_user"
$ do
let
statement :: Statement DB () ()
statement =
[ssql|
with new_user (id, name, bio) as (values ('id_new', 'new_name', 'new_bio'))
insert into users_copy
select * from new_user
|]
squealRendering :: Text
squealRendering =
"WITH \"new_user\" AS (SELECT * FROM (VALUES ((E'id_new' :: text), (E'new_name' :: text), (E'new_bio' :: text))) AS t (\"id\", \"name\", \"bio\")) INSERT INTO \"users_copy\" AS \"users_copy\" SELECT * FROM \"new_user\" AS \"new_user\""
checkStatement squealRendering statement
describe "deletes" $ do
it "delete from users where true" $ do
let
statement :: Statement DB () ()
statement = [ssql| delete from users where true |]
squealRendering :: Text
squealRendering = "DELETE FROM \"users\" AS \"users\" WHERE TRUE"
checkStatement squealRendering statement
it "delete from emails where id = 1" $ do
let
statement :: Statement DB () ()
statement = [ssql| delete from emails where id = 1 |]
squealRendering :: Text
squealRendering =
"DELETE FROM \"emails\" AS \"emails\" WHERE (\"id\" = 1)"
checkStatement squealRendering statement
it "delete from emails where id = $1" $ do
let
statement :: Statement DB (Only Int32) ()
statement = [ssql| delete from emails where id = $1 |]
squealRendering :: Text
squealRendering = "DELETE FROM \"emails\" AS \"emails\" WHERE (\"id\" = ($1 :: int4))"
checkStatement squealRendering statement
it "delete from emails where email = inline(e)" $ do
let
statement :: Statement DB () ()
statement = [ssql| delete from emails where email = inline(e) |]
e :: Text
e = "foo"
squealRendering :: Text
squealRendering =
"DELETE FROM \"emails\" AS \"emails\" WHERE (\"email\" = (E'foo' :: text))"
checkStatement squealRendering statement
it "delete from users where id = 'some-id' returning id" $ do
let
statement :: Statement DB () (Field "id" Text, ())
statement = [ssql| delete from users where id = 'some-id' returning id |]
squealRendering :: Text
squealRendering =
"DELETE FROM \"users\" AS \"users\" WHERE (\"id\" = (E'some-id' :: text)) RETURNING \"id\" AS \"id\""
checkStatement squealRendering statement
describe "with common table expressions" $ do
it
"with to_delete as (select id from users where name = 'Alice') delete from users where id in (select to_delete.id from to_delete)"
$ do
let
statement :: Statement DB () ()
statement =
[ssql|
with to_delete as (select id from users where name = 'Alice')
delete from users
where id in (select to_delete.id from to_delete)
|]
squealRendering :: Text
squealRendering =
"WITH \"to_delete\" AS (SELECT \"id\" AS \"id\" FROM \"users\" AS \"users\" WHERE (\"name\" = (E'Alice' :: text))) DELETE FROM \"users\" AS \"users\" WHERE (\"id\" = ANY (SELECT \"to_delete\".\"id\" AS \"id\" FROM \"to_delete\" AS \"to_delete\"))"
checkStatement squealRendering statement
it
"with to_delete as (select id from users where name = 'Alice') delete from users using to_delete where users.id = to_delete.id"
$ do
let
statement :: Statement DB () ()
statement =
[ssql|
with to_delete as (select id from users where name = 'Alice')
delete from users
using to_delete
where users.id = to_delete.id
|]
squealRendering :: Text
squealRendering =
"WITH \"to_delete\" AS (SELECT \"id\" AS \"id\" FROM \"users\" AS \"users\" WHERE (\"name\" = (E'Alice' :: text))) DELETE FROM \"users\" AS \"users\" USING \"to_delete\" AS \"to_delete\" WHERE (\"users\".\"id\" = \"to_delete\".\"id\")"
checkStatement squealRendering statement
describe "updates" $ do
it "update users set name = 'new name' where id = 'some-id'" $ do
let
statement :: Statement DB () ()
statement = [ssql| update users set name = 'new name' where id = 'some-id' |]
squealRendering :: Text
squealRendering =
"UPDATE \"users\" AS \"users\" SET \"name\" = (E'new name' :: text) WHERE (\"id\" = (E'some-id' :: text))"
checkStatement squealRendering statement
it "update users set name = 'new name', bio = 'new bio' where id = 'some-id'" $ do
let
statement :: Statement DB () ()
statement =
[ssql| update users set name = 'new name', bio = 'new bio' where id = 'some-id' |]
squealRendering :: Text
squealRendering =
"UPDATE \"users\" AS \"users\" SET \"name\" = (E'new name' :: text), \"bio\" = (E'new bio' :: text) WHERE (\"id\" = (E'some-id' :: text))"
checkStatement squealRendering statement
it "update users set name = inline(n) where id = 'some-id'" $ do
let
n :: Text
n = "new name"
statement :: Statement DB () ()
statement = [ssql| update users set name = inline(n) where id = 'some-id' |]
squealRendering :: Text
squealRendering =
"UPDATE \"users\" AS \"users\" SET \"name\" = (E'new name' :: text) WHERE (\"id\" = (E'some-id' :: text))"
checkStatement squealRendering statement
it "update users set name = $1 where id = $2" $ do
let
statement :: Statement DB (Text, Text) ()
statement = [ssql| update users set name = $1 where id = $2 |]
squealRendering :: Text
squealRendering =
"UPDATE \"users\" AS \"users\" SET \"name\" = ($1 :: text) WHERE (\"id\" = ($2 :: text))"
checkStatement squealRendering statement
it "update users set name = 'new name' where id = 'some-id' returning id" $ do
let
statement :: Statement DB () (Field "id" Text, ())
statement =
[ssql| update users set name = 'new name' where id = 'some-id' returning id |]
squealRendering :: Text
squealRendering =
"UPDATE \"users\" AS \"users\" SET \"name\" = (E'new name' :: text) WHERE (\"id\" = (E'some-id' :: text)) RETURNING \"id\" AS \"id\""
checkStatement squealRendering statement
describe "with common table expressions" $ do
it
"with to_update as (select id from users where name = 'Alice') update users set name = 'Alicia' from to_update where users.id = to_update.id"
$ do
let
statement :: Statement DB () ()
statement =
[ssql|
with to_update as (select id from users where name = 'Alice')
update users
set name = 'Alicia'
from to_update
where users.id = to_update.id
|]
squealRendering :: Text
squealRendering =
"WITH \"to_update\" AS (SELECT \"id\" AS \"id\" FROM \"users\" AS \"users\" WHERE (\"name\" = (E'Alice' :: text))) UPDATE \"users\" AS \"users\" SET \"name\" = (E'Alicia' :: text) FROM \"to_update\" AS \"to_update\" WHERE (\"users\".\"id\" = \"to_update\".\"id\")"
checkStatement squealRendering statement
describe "scalar expressions" $ do
-- Binary Operators
it "select users.id != 'no-such-user' as neq from users" $ do
let
stmt :: Statement DB () (Field "neq" (Maybe Bool), ())
stmt = [ssql| select users.id != 'no-such-user' as neq from users |]
squealRendering :: Text
squealRendering =
"SELECT (\"users\".\"id\" <> (E'no-such-user' :: text)) AS "
<> "\"neq\" FROM \"users\" AS \"users\""
checkStatement squealRendering stmt
it "select * from users where users.id <> 'no-such-user'" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
stmt = [ssql| select * from users where users.id <> 'no-such-user' |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE (\"users\".\"id\" <> (E'no-such-user' :: text))"
checkStatement squealRendering stmt
it "select * from emails where emails.id > 0" $ do
let
stmt
:: Statement
DB
()
(Field "id" Int32, (Field "user_id" Text, (Field "email" (Maybe Text), ())))
stmt = [ssql| select * from emails where emails.id > 0 |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"emails\" AS \"emails\" WHERE (\"emails\".\"id\" > 0)"
checkStatement squealRendering stmt
it "select * from emails where emails.id >= 0" $ do
let
stmt
:: Statement
DB
()
(Field "id" Int32, (Field "user_id" Text, (Field "email" (Maybe Text), ())))
stmt = [ssql| select * from emails where emails.id >= 0 |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"emails\" AS \"emails\" WHERE (\"emails\".\"id\" >= 0)"
checkStatement squealRendering stmt
it "select * from emails where emails.id < 10" $ do
let
stmt
:: Statement
DB
()
(Field "id" Int32, (Field "user_id" Text, (Field "email" (Maybe Text), ())))
stmt = [ssql| select * from emails where emails.id < 10 |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"emails\" AS \"emails\" WHERE (\"emails\".\"id\" < 10)"
checkStatement squealRendering stmt
it "select * from emails where emails.id <= 10" $ do
let
stmt
:: Statement
DB
()
(Field "id" Int32, (Field "user_id" Text, (Field "email" (Maybe Text), ())))
stmt = [ssql| select * from emails where emails.id <= 10 |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"emails\" AS \"emails\" WHERE (\"emails\".\"id\" <= 10)"
checkStatement squealRendering stmt
it "select emails.id + 1 as plus_one from emails" $ do
let
stmt :: Statement DB () (Field "plus_one" Int32, ())
stmt = [ssql| select emails.id + 1 as plus_one from emails |]
squealRendering :: Text
squealRendering =
"SELECT (\"emails\".\"id\" + 1) AS \"plus_one\" FROM \"emails\" AS \"emails\""
checkStatement squealRendering stmt
it "select emails.id - 1 as minus_one from emails" $ do
let
stmt :: Statement DB () (Field "minus_one" Int32, ())
stmt = [ssql| select emails.id - 1 as minus_one from emails |]
squealRendering :: Text
squealRendering =
"SELECT (\"emails\".\"id\" - 1) AS \"minus_one\" FROM \"emails\" AS \"emails\""
checkStatement squealRendering stmt
it "select emails.id * 2 as times_two from emails" $ do
let
stmt :: Statement DB () (Field "times_two" Int32, ())
stmt = [ssql| select emails.id * 2 as times_two from emails |]
squealRendering :: Text
squealRendering =
"SELECT (\"emails\".\"id\" * 2) AS \"times_two\" FROM \"emails\" AS \"emails\""
checkStatement squealRendering stmt
it "select * from users where users.id = 'a' and users.name = 'b'" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.id = 'a' and users.name = 'b' |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE ((\"users\".\"id\" = (E'a' :: text)) AND (\"users\".\"name\" = (E'b' :: text)))"
checkStatement squealRendering stmt
it "select * from users where users.id = 'a' or users.name = 'b'" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.id = 'a' or users.name = 'b' |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE ((\"users\".\"id\" = (E'a' :: text)) OR (\"users\".\"name\" = (E'b' :: text)))"
checkStatement squealRendering stmt
it "select * from users where users.name like 'A%'" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.name like 'A%' |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE (\"users\".\"name\" LIKE (E'A%' :: text))"
checkStatement squealRendering stmt
it "select * from users where users.name ilike 'a%'" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.name ilike 'a%' |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE (\"users\".\"name\" ILIKE (E'a%' :: text))"
checkStatement squealRendering stmt
-- Prefix Operators
it "select * from users where not (users.name = 'no-one')" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where not (users.name = 'no-one') |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE (NOT (\"users\".\"name\" = (E'no-one' :: text)))"
checkStatement squealRendering stmt
it "select -emails.id as neg_id from emails" $ do
let
stmt :: Statement DB () (Field "neg_id" Int32, ())
stmt = [ssql| select -emails.id as neg_id from emails |]
squealRendering :: Text
squealRendering =
"SELECT ((0 :: int4) - \"emails\".\"id\") AS \"neg_id\" FROM \"emails\" AS \"emails\""
checkStatement squealRendering stmt
-- Postfix Operators
it "select * from users where users.bio is null" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.bio is null |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" WHERE \"users\".\"bio\" IS NULL"
checkStatement squealRendering stmt
it "select * from users where users.bio is not null" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.bio is not null |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" WHERE \"users\".\"bio\" IS NOT NULL"
checkStatement squealRendering stmt
describe "function calls" $ do
-- Function Calls
it "select coalesce(users.bio, 'no bio') as bio from users" $ do
let
stmt :: Statement DB () (Field "bio" Text, ())
stmt = [ssql| select coalesce(users.bio, 'no bio') as bio from users |]
squealRendering :: Text
squealRendering =
"SELECT COALESCE(\"users\".\"bio\", (E'no bio' :: text)) AS \"bio\" FROM \"users\" AS \"users\""
checkStatement squealRendering stmt
it "select lower(users.name) as lower_name from users" $ do
let
stmt :: Statement DB () (Field "lower_name" Text, ())
stmt = [ssql| select lower(users.name) as lower_name from users |]
squealRendering :: Text
squealRendering =
"SELECT lower(\"users\".\"name\") AS \"lower_name\" FROM \"users\" AS \"users\""
checkStatement squealRendering stmt
it "select char_length(users.name) as name_len from users" $ do
let
stmt :: Statement DB () (Field "name_len" Int32, ())
stmt = [ssql| select char_length(users.name) as name_len from users |]
squealRendering :: Text
squealRendering =
"SELECT char_length(\"users\".\"name\") AS \"name_len\" FROM \"users\" AS \"users\""
checkStatement squealRendering stmt
it "select character_length(users.name) as name_len_alias from users" $ do
let
stmt :: Statement DB () (Field "name_len_alias" Int32, ())
stmt = [ssql| select character_length(users.name) as name_len_alias from users |]
squealRendering :: Text
squealRendering =
"SELECT char_length(\"users\".\"name\") AS \"name_len_alias\" FROM \"users\" AS \"users\""
checkStatement squealRendering stmt
it "select \"upper\"(users.name) as upper_name from users" $ do
let
stmt :: Statement DB () (Field "upper_name" Text, ())
stmt = [ssql| select "upper"(users.name) as upper_name from users |]
squealRendering :: Text
squealRendering =
"SELECT upper(\"users\".\"name\") AS \"upper_name\" FROM \"users\" AS \"users\""
checkStatement squealRendering stmt
it "select now() as current_time" $ do
let
stmt :: Statement DB () (Field "current_time" UTCTime, ())
stmt = [ssql| select now() as current_time |]
squealRendering :: Text
squealRendering = "SELECT * FROM (VALUES (now())) AS t (\"current_time\")"
checkStatement squealRendering stmt
it "select current_date as today" $ do
let
stmt :: Statement DB () (Field "today" Day, ())
stmt = [ssql| select current_date as today |]
squealRendering :: Text
squealRendering = "SELECT * FROM (VALUES (CURRENT_DATE)) AS t (\"today\")"
checkStatement squealRendering stmt
it "haskell variables in expressions" $ do
let
mkStatement
:: Text
-> Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
mkStatement haskellVariable =
[ssql| select * from users where name = inline("haskellVariable") |]
squealRendering1 :: Text
squealRendering1 = "SELECT * FROM \"users\" AS \"users\" WHERE (\"name\" = (E'Alice' :: text))"
squealRendering2 :: Text
squealRendering2 = "SELECT * FROM \"users\" AS \"users\" WHERE (\"name\" = (E'Bob' :: text))"
checkStatement squealRendering1 (mkStatement "Alice")
checkStatement squealRendering2 (mkStatement "Bob")
-- PARENS (implicitly tested by complex expressions)
it "select (emails.id + 1) * 2 as calc from emails" $ do
let
stmt :: Statement DB () (Field "calc" Int32, ())
stmt = [ssql| select (emails.id + 1) * 2 as calc from emails |]
squealRendering :: Text
squealRendering =
"SELECT ((\"emails\".\"id\" + 1) * 2) AS \"calc\" FROM \"emails\" AS \"emails\""
checkStatement squealRendering stmt
-- IN / NOT IN
it "select * from users where users.name in ('Alice', 'Bob')" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.name in ('Alice', 'Bob') |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE \"users\".\"name\" IN ((E'Alice' :: text), (E'Bob' :: text))"
checkStatement squealRendering stmt
it "select * from users where users.id in (select emails.user_id from emails)" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt =
[ssql| select * from users where users.id in (select emails.user_id from emails) |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE (\"users\".\"id\" = ANY (SELECT \"emails\".\"user_id\" AS \"user_id\" FROM \"emails\" AS \"emails\"))"
checkStatement squealRendering stmt
it "select * from users where users.name not in ('Alice', 'Bob')" $ do
let
stmt
:: Statement
DB
()
( Field "id" Text
, (Field "name" Text, (Field "employee_id" UUID, (Field "bio" (Maybe Text), ())))
)
stmt = [ssql| select * from users where users.name not in ('Alice', 'Bob') |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"users\" AS \"users\" WHERE \"users\".\"name\" NOT IN ((E'Alice' :: text), (E'Bob' :: text))"
checkStatement squealRendering stmt
-- BETWEEN / NOT BETWEEN
it "select * from emails where emails.id between 0 and 10" $ do
let
stmt
:: Statement
DB
()
(Field "id" Int32, (Field "user_id" Text, (Field "email" (Maybe Text), ())))
stmt = [ssql| select * from emails where emails.id between 0 and 10 |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"emails\" AS \"emails\" WHERE \"emails\".\"id\" BETWEEN 0 AND 10"
checkStatement squealRendering stmt
it "select * from emails where emails.id not between 0 and 10" $ do
let
stmt
:: Statement
DB
()
(Field "id" Int32, (Field "user_id" Text, (Field "email" (Maybe Text), ())))
stmt = [ssql| select * from emails where emails.id not between 0 and 10 |]
squealRendering :: Text
squealRendering =
"SELECT * FROM \"emails\" AS \"emails\" WHERE \"emails\".\"id\" NOT BETWEEN 0 AND 10"
checkStatement squealRendering stmt
-- CAST
it "select (e.id :: text) as casted_id from emails as e" $ do
let
stmt :: Statement DB () (Field "casted_id" Text, ())
stmt = [ssql| select (e.id :: text) as casted_id from emails as e |]
squealRendering :: Text
squealRendering = "SELECT (\"e\".\"id\" :: text) AS \"casted_id\" FROM \"emails\" AS \"e\""
checkStatement squealRendering stmt
it "select * from users for update" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users for update |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" FOR UPDATE"
checkStatement squealRendering statement
it "select * from jsonb_test" $ do
let
statement
:: Statement
DB
()
( Field "data" (Jsonb Value)
, ()
)
statement = [ssql| select * from jsonb_test |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"jsonb_test\" AS \"jsonb_test\""
checkStatement squealRendering statement
it "select * from json_test" $ do
let
statement
:: Statement
DB
()
( Field "data" (Json Value)
, ()
)
statement = [ssql| select * from json_test |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"json_test\" AS \"json_test\""
checkStatement squealRendering statement
it "select distinct name from users" $ do
let
statement :: Statement DB () (Field "name" Text, ())
statement = [ssql| select distinct name from users |]
squealRendering :: Text
squealRendering = "SELECT DISTINCT \"name\" AS \"name\" FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select distinct * from users" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select distinct * from users |]
squealRendering :: Text
squealRendering = "SELECT DISTINCT * FROM \"users\" AS \"users\""
checkStatement squealRendering statement
it "select distinct on (employee_id) employee_id, name from users" $ do
let
statement
:: Statement
DB
()
( Field "employee_id" UUID
, ( Field "name" Text
, ()
)
)
statement = [ssql| select distinct on (employee_id) employee_id, name from users |]
squealRendering :: Text
squealRendering =
"SELECT DISTINCT ON (\"employee_id\") \"employee_id\" AS \"employee_id\", \"name\" AS \"name\" FROM \"users\" AS \"users\" ORDER BY \"employee_id\" ASC"
checkStatement squealRendering statement
it "select distinct on (employee_id, name) employee_id, name, id from users" $ do
let
statement
:: Statement
DB
()
( Field "employee_id" UUID
, ( Field "name" Text
, ( Field "id" Text
, ()
)
)
)
statement =
[ssql| select distinct on (employee_id, name) employee_id, name, id from users |]
squealRendering :: Text
squealRendering =
"SELECT DISTINCT ON (\"employee_id\", \"name\") \"employee_id\" AS \"employee_id\", \"name\" AS \"name\", \"id\" AS \"id\" FROM \"users\" AS \"users\" ORDER BY \"employee_id\" ASC, \"name\" ASC"
checkStatement squealRendering statement
describe "order by" $ do
it "select * from users order by name" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users order by name |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" ORDER BY \"name\" ASC"
checkStatement squealRendering statement
it "select * from users order by name asc" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users order by name asc |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" ORDER BY \"name\" ASC"
checkStatement squealRendering statement
it "select * from users order by name desc" $ do
let
statement
:: Statement
DB
()
( Field "id" Text
, ( Field "name" Text
, ( Field "employee_id" UUID
, ( Field "bio" (Maybe Text)
, ()
)
)
)
)
statement = [ssql| select * from users order by name desc |]
squealRendering :: Text
squealRendering = "SELECT * FROM \"users\" AS \"users\" ORDER BY \"name\" DESC"
checkStatement squealRendering statement
describe "having clause" $ do
it
"select employee_id, count(id) from users group by employee_id having count(id) > 1"
$ do
let
statement
:: Statement
DB
()
( Field "employee_id" UUID
, ( Field "_col2" Int64 -- Assuming count returns Int64
, ()
)
)
statement =
[ssql|
select employee_id, count(id)
from users
group by employee_id
having count(id) > 1
|]
squealRendering :: Text
squealRendering =
"SELECT \"employee_id\" AS \"employee_id\", count(ALL \"id\") AS \"_col2\" FROM \"users\" AS \"users\" GROUP BY \"employee_id\" HAVING (count(ALL \"id\") > 1)"
checkStatement squealRendering statement
_printQuery :: (RenderSQL a) => a -> IO ()
_printQuery = putStrLn . T.unpack . TE.decodeUtf8 . renderSQL
checkStatement
:: (RenderSQL sql)
=> Text
-> sql
-> IO ()
checkStatement expect statement =
let
rendered :: Text
rendered = TE.decodeUtf8 (renderSQL statement)
in
if rendered == expect
then
pure ()
else
fail $
"SQL statements do not match.\nExpected:\n"
<> show expect
<> "\nActual:\n"
<> show rendered
<> "\n"