content: {-# LANGUAGE CPP #-}
  {-# LANGUAGE OverloadedLabels #-}
  {-# LANGUAGE ConstraintKinds #-}
  {-# LANGUAGE DataKinds #-}
  {-# LANGUAGE DeriveGeneric #-}
  {-# LANGUAGE DerivingStrategies #-}
  {-# LANGUAGE EmptyDataDecls #-}
  {-# LANGUAGE FlexibleContexts #-}
  {-# LANGUAGE FlexibleInstances #-}
  {-# LANGUAGE GADTs #-}
  {-# LANGUAGE GeneralizedNewtypeDeriving #-}
  {-# LANGUAGE MultiParamTypeClasses #-}
  {-# LANGUAGE OverloadedStrings #-}
  {-# LANGUAGE PartialTypeSignatures #-}
  {-# LANGUAGE QuasiQuotes #-}
  {-# LANGUAGE Rank2Types #-}
  {-# LANGUAGE ScopedTypeVariables #-}
  {-# LANGUAGE StandaloneDeriving #-}
  {-# LANGUAGE TemplateHaskell #-}
  {-# LANGUAGE TypeApplications #-}
  {-# LANGUAGE TypeFamilies #-}
  {-# LANGUAGE TypeSynonymInstances #-}
  {-# LANGUAGE UndecidableInstances #-}
  {-# LANGUAGE ViewPatterns #-}
  
  #if __GLASGOW_HASKELL__ >= 902
  {-# LANGUAGE OverloadedRecordDot #-}
  #endif
  
  {-# OPTIONS_GHC -fno-warn-unused-binds #-}
  {-# OPTIONS_GHC -fno-warn-deprecations #-}
  module Common.Test
      ( tests
      , testLocking
      , testAscRandom
      , migrateAll
      , migrateUnique
      , cleanDB
      , cleanUniques
      , updateRethrowingQuery
      , selectRethrowingQuery
      , p1, p2, p3, p4, p5
      , l1, l2, l3
      , u1, u2, u3, u4
      , insert'
      , EntityField (..)
      , Foo (..)
      , Bar (..)
      , Person (..)
      , BlogPost (..)
      , Lord (..)
      , Deed (..)
      , Follow (..)
      , CcList (..)
      , Frontcover (..)
      , Article (..)
      , Tag (..)
      , ArticleTag (..)
      , Article2 (..)
      , Point (..)
      , Circle (..)
      , Numbers (..)
      , OneUnique(..)
      , Unique(..)
      , DateTruncTest(..)
      , DateTruncTestId
      , Key(..)
      , assertJust
      ) where
  
  import Common.Test.Import hiding (from, on)
  
  import Control.Monad (forM_, replicateM, replicateM_, void)
  import qualified Data.Attoparsec.Text as AP
  import Data.Char (toLower, toUpper)
  import Data.Either
  import Database.Esqueleto
  import qualified Database.Esqueleto.Experimental as Experimental
  
  import Data.Conduit (ConduitT, runConduit, (.|))
  import qualified Data.Conduit.List as CL
  import qualified Data.List as L
  import qualified Data.Set as S
  import qualified Data.Text as Text
  import qualified Data.Text.Internal.Lazy as TL
  import qualified Data.Text.Lazy.Builder as TLB
  import qualified Database.Esqueleto.Internal.ExprParser as P
  import qualified Database.Esqueleto.Internal.Internal as EI
  import Database.Esqueleto.PostgreSQL as EP
  import Database.Persist.Class.PersistEntity
  import qualified UnliftIO.Resource as R
  
  import Common.Record (testDeriveEsqueletoRecord)
  import Common.Test.Select
  import qualified Common.Test.CTE as CTESpec
  
  -- Test schema
  -- | this could be achieved with S.fromList, but not all lists
  --   have Ord instances
  sameElementsAs :: Eq a => [a] -> [a] -> Bool
  sameElementsAs l1' l2' = null (l1' L.\\ l2')
  
  -- | Helper for rounding to a specific digit
  --   Prelude> map (flip roundTo 12.3456) [0..5]
  --   [12.0, 12.3, 12.35, 12.346, 12.3456, 12.3456]
  roundTo :: (Fractional a, RealFrac a1, Integral b) => b -> a1 -> a
  roundTo n f =
      (fromInteger $ round $ f * (10^n)) / (10.0^^n)
  
  p1 :: Person
  p1 = Person "John"   (Just 36) Nothing   1
  
  p2 :: Person
  p2 = Person "Rachel" Nothing   (Just 37) 2
  
  p3 :: Person
  p3 = Person "Mike"   (Just 17) Nothing   3
  
  p4 :: Person
  p4 = Person "Livia"  (Just 17) (Just 18) 4
  
  p5 :: Person
  p5 = Person "Mitch"  Nothing   Nothing   5
  
  l1 :: Lord
  l1 = Lord "Cornwall" (Just 36)
  
  l2 :: Lord
  l2 = Lord "Dorset" Nothing
  
  l3 :: Lord
  l3 = Lord "Chester" (Just 17)
  
  u1 :: OneUnique
  u1 = OneUnique "First" 0
  
  u2 :: OneUnique
  u2 = OneUnique "Second" 1
  
  u3 :: OneUnique
  u3 = OneUnique "Third" 0
  
  u4 :: OneUnique
  u4 = OneUnique "First" 2
  
  testSubSelect :: SpecDb
  testSubSelect = do
      let setup :: MonadIO m => SqlPersistT m ()
          setup = do
              _ <- insert $ Numbers 1 2
              _ <- insert $ Numbers 2 4
              _ <- insert $ Numbers 3 5
              _ <- insert $ Numbers 6 7
              pure ()
  
      describe "subSelect" $ do
          itDb "is safe for queries that may return multiple results" $ do
              let query =
                    from $ \n -> do
                    orderBy [asc (n ^. NumbersInt)]
                    pure (n ^. NumbersInt)
              setup
              res <- select $ pure $ subSelect query
              asserting $ do
                  res `shouldBe` [Value (Just 1)]
  
          itDb "is safe for queries that may not return anything" $ do
              let query =
                      from $ \n -> do
                      orderBy [asc (n ^. NumbersInt)]
                      limit 1
                      pure (n ^. NumbersInt)
              setup
              res <- select $ pure $ subSelect query
              transactionUndo
  
              asserting $ do
                  res `shouldBe` [Value $ Just 1]
  
      describe "subSelectList" $ do
          itDb "is safe on empty databases as well as good databases" $ do
              let query =
                      from $ \n -> do
                      where_ $ n ^. NumbersInt `in_` do
                          subSelectList $
                              from $ \n' -> do
                              where_ $ n' ^. NumbersInt >=. val 3
                              pure (n' ^. NumbersInt)
                      pure n
              empty <- select query
  
              full <- do
                  setup
                  select query
  
              asserting $ do
                  empty `shouldBe` []
                  full `shouldSatisfy` (not . null)
  
      describe "subSelectMaybe" $ do
          itDb "is equivalent to joinV . subSelect" $ do
              let query
                      :: (SqlQuery (SqlExpr (Value (Maybe Int))) -> SqlExpr (Value (Maybe Int)))
                      -> SqlQuery (SqlExpr (Value (Maybe Int)))
                  query selector =
                      from $ \n -> do
                      pure $
                          selector $
                          from $ \n' -> do
                          where_ $ n' ^. NumbersDouble >=. n ^. NumbersDouble
                          pure (max_ (n' ^. NumbersInt))
  
              setup
              a <- select (query subSelectMaybe)
              b <- select (query (joinV . subSelect))
              asserting $ a `shouldBe` b
  
      describe "subSelectCount" $ do
          itDb "is a safe way to do a countRows" $ do
              setup
              xs0 <-
                  select $
                      from $ \n -> do
                      pure $ (,) n $
                          subSelectCount @Int $
                          from $ \n' -> do
                          where_ $ n' ^. NumbersInt >=. n ^. NumbersInt
  
              xs1 <-
                  select $
                      from $ \n -> do
                      pure $ (,) n $
                          subSelectUnsafe $
                          from $ \n' -> do
                          where_ $ n' ^. NumbersInt >=. n ^. NumbersInt
                          pure (countRows :: SqlExpr (Value Int))
  
              let getter (Entity _ a, b) = (a, b)
              asserting $
                  map getter xs0 `shouldBe` map getter xs1
  
      describe "subSelectUnsafe" $ do
          itDb "throws exceptions on multiple results" $ do
              setup
              eres <- try $ do
                  bad <- select $
                      from $ \n -> do
                      pure $ (,) (n ^. NumbersInt) $
                          subSelectUnsafe $
                          from $ \n' -> do
                          pure (just (n' ^. NumbersDouble))
                  good <- select $
                      from $ \n -> do
                      pure $ (,) (n ^. NumbersInt) $
                          subSelect $
                          from $ \n' -> do
                          pure (n' ^. NumbersDouble)
                  pure (bad, good)
              asserting $ case eres of
                  Left (SomeException _) ->
                      -- Must use SomeException because the database libraries throw their
                      -- own errors.
                      pure ()
                  Right (bad, good) -> do
                      -- SQLite just takes the first element of the sub-select. lol.
                      bad `shouldBe` good
  
          itDb "throws exceptions on null results" $ do
              setup
              eres <- try $ do
                  select $
                      from $ \n -> do
                      pure $ (,) (n ^. NumbersInt) $
                          subSelectUnsafe $
                          from $ \n' -> do
                          where_ $ val False
                          pure (n' ^. NumbersDouble)
              asserting $ case eres of
                  Left (_ :: PersistException) ->
                      pure ()
                  Right xs ->
                      xs `shouldBe` []
  
  testSelectOne :: SpecDb
  testSelectOne =
      describe "selectOne" $ do
          let personQuery =
                  selectOne $ do
                      person <- Experimental.from $ Experimental.table @Person
                      where_ $ person ^. PersonFavNum >=. val 1
                      orderBy [asc (person ^. PersonId)]
                      return $ person ^. PersonId
          itDb "returns Just" $ do
              person <- insert' p1
              _ <- insert' p2
              res <- personQuery
              asserting $
                  res `shouldBe` Just (Value $ entityKey person)
  
          itDb "returns Nothing" $ do
              res <- personQuery
              asserting $
                  res `shouldBe` (Nothing :: Maybe (Value PersonId))
  
  testSelectSource :: SpecDb
  testSelectSource = do
      describe "selectSource" $ do
          itDb "works for a simple example" $ do
              let query
                      :: ConduitT () (Entity Person) (SqlPersistT (R.ResourceT IO)) ()
                  query =
                      selectSource $
                      from $ \person ->
                      return person
              p1e <- insert' p1
              ret <- mapReaderT R.runResourceT $ runConduit $ query .| CL.consume
              asserting $ ret `shouldBe` [ p1e ]
  
          itDb "can run a query many times" $ do
              let query
                      :: ConduitT () (Entity Person) (SqlPersistT (R.ResourceT IO)) ()
                  query =
                      selectSource $
                      from $ \person ->
                      return person
              p1e <- insert' p1
              ret0 <- mapReaderT R.runResourceT $ runConduit $ query .| CL.consume
              ret1 <- mapReaderT R.runResourceT $ runConduit $ query .| CL.consume
              asserting $ do
                  ret0 `shouldBe` [ p1e ]
                  ret1 `shouldBe` [ p1e ]
  
          itDb "works on repro" $ do
              let selectPerson :: R.MonadResource m => String -> ConduitT () (Key Person) (SqlPersistT m) ()
                  selectPerson name = do
                      let source =
                              selectSource $ from $ \person -> do
                              where_ $ person ^. PersonName ==. val name
                              return $ person ^. PersonId
                      source .| CL.map unValue
              p1e <- insert' p1
              p2e <- insert' p2
              r1 <- mapReaderT R.runResourceT $ runConduit $ selectPerson (personName p1) .| CL.consume
              r2 <- mapReaderT R.runResourceT $ runConduit $ selectPerson (personName p2) .| CL.consume
              asserting $ do
                  r1 `shouldBe` [ entityKey p1e ]
                  r2 `shouldBe` [ entityKey p2e ]
  
  testSelectFrom :: SpecDb
  testSelectFrom = do
      describe "select/from" $ do
          itDb "works for a simple example" $ do
              p1e <- insert' p1
              ret <-
                  select $
                  from $ \person ->
                  return person
              asserting $ ret `shouldBe` [ p1e ]
  
          itDb "works for a simple self-join (one entity)" $ do
              p1e <- insert' p1
              ret <-
                  select $
                  from $ \(person1, person2) ->
                  return (person1, person2)
              asserting $ ret `shouldBe` [ (p1e, p1e) ]
  
          itDb "works for a simple self-join (two entities)" $ do
              p1e <- insert' p1
              p2e <- insert' p2
              ret <-
                  select $
                  from $ \(person1, person2) ->
                  return (person1, person2)
              asserting $
                  ret
                      `shouldSatisfy`
                          sameElementsAs
                              [ (p1e, p1e)
                              , (p1e, p2e)
                              , (p2e, p1e)
                              , (p2e, p2e)
                              ]
  
          itDb "works for a self-join via subSelect" $ do
              p1k <- insert p1
              p2k <- insert p2
              _f1k <- insert (Follow p1k p2k)
              _f2k <- insert (Follow p2k p1k)
              ret <- select $
                     from $ \followA -> do
                     let subquery =
                           from $ \followB -> do
                           where_ $ followA ^. FollowFollower ==. followB ^. FollowFollowed
                           return $ followB ^. FollowFollower
                     where_ $ just (followA ^. FollowFollowed) ==. subSelect subquery
                     return followA
              asserting $ length ret `shouldBe` 2
  
          itDb "works for a self-join via exists" $ do
              p1k <- insert p1
              p2k <- insert p2
              _f1k <- insert (Follow p1k p2k)
              _f2k <- insert (Follow p2k p1k)
              ret <- select $
                     from $ \followA -> do
                     where_ $ exists $
                              from $ \followB ->
                              where_ $ followA ^. FollowFollower ==. followB ^. FollowFollowed
                     return followA
              asserting $ length ret `shouldBe` 2
  
  
          itDb "works for a simple projection" $ do
              p1k <- insert p1
              p2k <- insert p2
              ret <- select $
                     from $ \p ->
                     return (p ^. PersonId, p ^. PersonName)
              asserting $ ret `shouldBe` [ (Value p1k, Value (personName p1))
                                      , (Value p2k, Value (personName p2)) ]
  
          itDb "works for a simple projection with a simple implicit self-join" $ do
              _ <- insert p1
              _ <- insert p2
              ret <- select $
                     from $ \(pa, pb) ->
                     return (pa ^. PersonName, pb ^. PersonName)
              asserting $ ret `shouldSatisfy` sameElementsAs
                                      [ (Value (personName p1), Value (personName p1))
                                      , (Value (personName p1), Value (personName p2))
                                      , (Value (personName p2), Value (personName p1))
                                      , (Value (personName p2), Value (personName p2)) ]
  
          itDb "works with many kinds of LIMITs and OFFSETs" $ do
              [p1e, p2e, p3e, p4e] <- mapM insert' [p1, p2, p3, p4]
              let people =
                      from $ \p -> do
                      orderBy [asc (p ^. PersonName)]
                      return p
              ret1 <-
                  select $ do
                  p <- people
                  limit 2
                  limit 1
                  return p
              asserting $ ret1 `shouldBe` [ p1e ]
              ret2 <-
                  select $ do
                  p <- people
                  limit 1
                  limit 2
                  return p
              asserting $ ret2 `shouldBe` [ p1e, p4e ]
              ret3 <-
                  select $ do
                  p <- people
                  offset 3
                  offset 2
                  return p
              asserting $ ret3 `shouldBe` [ p3e, p2e ]
              ret4 <-
                  select $ do
                  p <- people
                  offset 3
                  limit 5
                  offset 2
                  limit 3
                  offset 1
                  limit 2
                  return p
              asserting $ ret4 `shouldBe` [ p4e, p3e ]
              ret5 <-
                  select $ do
                  p <- people
                  offset 1000
                  limit  1
                  limit  1000
                  offset 0
                  return p
              asserting $ ret5 `shouldBe` [ p1e, p4e, p3e, p2e ]
  
          itDb "works with non-id primary key" $ do
              let fc = Frontcover number ""
                  number = 101 :: Int
                  Right thePk = keyFromValues [toPersistValue number]
              fcPk <- insert fc
              [Entity _ ret] <- select $ from return
              asserting $ do
                  ret `shouldBe` fc
                  fcPk `shouldBe` thePk
  
          itDb "works when returning a custom non-composite primary key from a query" $ do
              let name = "foo"
                  t = Tag name
                  Right thePk = keyFromValues [toPersistValue name]
              tagPk <- insert t
              [Value ret] <- select $ from $ \t' -> return (t'^.TagId)
              asserting $ do
                  ret `shouldBe` thePk
                  thePk `shouldBe` tagPk
  
          itDb "works when returning a composite primary key from a query" $ do
              let p = Point 10 20 ""
              thePk <- insert p
              [Value ppk] <- select $ from $ \p' -> return (p'^.PointId)
              asserting $ ppk `shouldBe` thePk
  
  testSelectJoin :: SpecDb
  testSelectJoin = do
    describe "select:JOIN" $ do
      itDb "works with a LEFT OUTER JOIN" $
        do
          p1e <- insert' p1
          p2e <- insert' p2
          p3e <- insert' p3
          p4e <- insert' p4
          b12e <- insert' $ BlogPost "b" (entityKey p1e)
          b11e <- insert' $ BlogPost "a" (entityKey p1e)
          b31e <- insert' $ BlogPost "c" (entityKey p3e)
          ret <- select $
                 from $ \(p `LeftOuterJoin` mb) -> do
                 on (just (p ^. PersonId) ==. mb ?. BlogPostAuthorId)
                 orderBy [ asc (p ^. PersonName), asc (mb ?. BlogPostTitle) ]
                 return (p, mb)
          asserting $ ret `shouldBe` [ (p1e, Just b11e)
                                  , (p1e, Just b12e)
                                  , (p4e, Nothing)
                                  , (p3e, Just b31e)
                                  , (p2e, Nothing) ]
  
      itDb "typechecks (A LEFT OUTER JOIN (B LEFT OUTER JOIN C))" $
          let
              _x :: SqlPersistT IO _
              _x =
                  select $
                  from $ \(a `LeftOuterJoin` (b `LeftOuterJoin` c)) ->
                  let _ = [a, b, c] :: [ SqlExpr (Entity Person) ]
                  in return a
        in asserting noExceptions
  
      itDb "typechecks ((A LEFT OUTER JOIN B) LEFT OUTER JOIN C)" $
          let _x :: SqlPersistT IO _
              _x =
                  select $
                  from $ \((a `LeftOuterJoin` b) `LeftOuterJoin` c) ->
                  let _ = [a, b, c] :: [ SqlExpr (Entity Person) ]
                  in return a
          in asserting noExceptions
  
      itDb "throws an error for using on without joins" $ do
        eres <- try $ select $
              from $ \(p, mb) -> do
             on (just (p ^. PersonId) ==. mb ?. BlogPostAuthorId)
             orderBy [ asc (p ^. PersonName), asc (mb ?. BlogPostTitle) ]
             return (p, mb)
        asserting $ shouldBeOnClauseWithoutMatchingJoinException eres
  
      itDb "throws an error for using too many ons" $ do
        eres <- try $ select $
             from $ \(p `FullOuterJoin` mb) -> do
             on (just (p ^. PersonId) ==. mb ?. BlogPostAuthorId)
             on (just (p ^. PersonId) ==. mb ?. BlogPostAuthorId)
             orderBy [ asc (p ^. PersonName), asc (mb ?. BlogPostTitle) ]
             return (p, mb)
        asserting $ shouldBeOnClauseWithoutMatchingJoinException eres
  
      itDb "works with ForeignKey to a non-id primary key returning one entity" $
        do
          let fc = Frontcover number ""
              article = Article "Esqueleto supports composite pks!" number
              number = 101
              Right thePk = keyFromValues [toPersistValue number]
          fcPk <- insert fc
          insert_ article
          [Entity _ retFc] <- select $
            from $ \(a `InnerJoin` f) -> do
              on (f^.FrontcoverNumber ==. a^.ArticleFrontcoverNumber)
              return f
          asserting $ do
            retFc `shouldBe` fc
            fcPk `shouldBe` thePk
      itDb "allows using a primary key that is itself a key of another table" $
        do
          let number = 101
          insert_ $ Frontcover number ""
          articleId <- insert $ Article "title" number
          articleMetaE <- insert' (ArticleMetadata articleId)
          result <- select $ from $ \articleMetadata -> do
            where_ $ (articleMetadata ^. ArticleMetadataId) ==. (val ((ArticleMetadataKey articleId)))
            pure articleMetadata
          asserting $ [articleMetaE] `shouldBe` result
      itDb "allows joining between a primary key that is itself a key of another table, using ToBaseId" $ do
        do
          let number = 101
          insert_ $ Frontcover number ""
          articleE@(Entity articleId _) <- insert' $ Article "title" number
          articleMetaE <- insert' (ArticleMetadata articleId)
  
          articlesAndMetadata <- select $
            from $ \(article `InnerJoin` articleMetadata) -> do
            on (toBaseId (articleMetadata ^. ArticleMetadataId) ==. article ^. ArticleId)
            return (article, articleMetadata)
          asserting $ [(articleE, articleMetaE)] `shouldBe` articlesAndMetadata
  
      itDb "works with a ForeignKey to a non-id primary key returning both entities" $
        do
          let fc = Frontcover number ""
              article = Article "Esqueleto supports composite pks!" number
              number = 101
              Right thePk = keyFromValues [toPersistValue number]
          fcPk <- insert fc
          insert_ article
          [(Entity _ retFc, Entity _ retArt)] <- select $
            from $ \(a `InnerJoin` f) -> do
              on (f^.FrontcoverNumber ==. a^.ArticleFrontcoverNumber)
              return (f, a)
          asserting $ do
            retFc `shouldBe` fc
            retArt `shouldBe` article
            fcPk `shouldBe` thePk
            articleFkfrontcover retArt `shouldBe` thePk
  
      itDb "works with a non-id primary key returning one entity" $
        do
          let fc = Frontcover number ""
              article = Article2 "Esqueleto supports composite pks!" thePk
              number = 101
              Right thePk = keyFromValues [toPersistValue number]
          fcPk <- insert fc
          insert_ article
          [Entity _ retFc] <- select $
            from $ \(a `InnerJoin` f) -> do
              on (f^.FrontcoverId ==. a^.Article2FrontcoverId)
              return f
          asserting $ do
            retFc `shouldBe` fc
            fcPk `shouldBe` thePk
  
      it "works with a composite primary key" $ \_ ->
        pendingWith "Persistent does not create the CircleFkPoint constructor. See: https://github.com/yesodweb/persistent/issues/341"
        {-
        do
          let p = Point x y ""
              c = Circle x y ""
              x = 10
              y = 15
              Right thePk = keyFromValues [toPersistValue x, toPersistValue y]
          pPk <- insert p
          insert_ c
          [Entity _ ret] <- select $ from $ \(c' `InnerJoin` p') -> do
            on (p'^.PointId ==. c'^.CircleFkpoint)
            return p'
          asserting $ do
            ret `shouldBe` p
            pPk `shouldBe` thePk
       -}
  
      itDb "works when joining via a non-id primary key" $
        do
          let fc = Frontcover number ""
              article = Article "Esqueleto supports composite pks!" number
              tag = Tag "foo"
              otherTag = Tag "ignored"
              number = 101
          insert_ fc
          insert_ otherTag
          artId <- insert article
          tagId <- insert tag
          insert_ $ ArticleTag artId tagId
          [(Entity _ retArt, Entity _ retTag)] <- select $
            from $ \(a `InnerJoin` at `InnerJoin` t) -> do
              on (t^.TagId ==. at^.ArticleTagTagId)
              on (a^.ArticleId ==. at^.ArticleTagArticleId)
              return (a, t)
          asserting $ do
            retArt `shouldBe` article
            retTag `shouldBe` tag
  
      itDb "respects the associativity of joins" $
        do
            void $ insert p1
            ps <- select $ from $
                      \((p :: SqlExpr (Entity Person))
                       `LeftOuterJoin`
                        ((_q :: SqlExpr (Entity Person))
                         `InnerJoin` (_r :: SqlExpr (Entity Person)))) -> do
                on (val False) -- Inner join is empty
                on (val True)
                return p
            asserting $ (entityVal <$> ps) `shouldBe` [p1]
  
  testSelectSubQuery :: SpecDb
  testSelectSubQuery = describe "select subquery" $ do
      itDb "works" $ do
          _ <- insert' p1
          let q = do
                  p <- Experimental.from $ Table @Person
                  return ( p ^. PersonName, p ^. PersonAge)
          ret <- select $ Experimental.from q
          asserting $ ret `shouldBe` [ (Value $ personName p1, Value $ personAge p1) ]
  
      itDb "supports sub-selecting Maybe entities" $ do
          l1e <- insert' l1
          l3e <- insert' l3
          l1Deeds <- mapM (\k -> insert' $ Deed k (entityKey l1e)) (map show [1..3 :: Int])
          let l1WithDeeds = do d <- l1Deeds
                               pure (l1e, Just d)
          let q = Experimental.from $ do
                    (lords :& deeds) <-
                        Experimental.from $ Table @Lord
                        `LeftOuterJoin` Table @Deed
                        `Experimental.on` (\(l :& d) -> just (l ^. LordId) ==. d ?. DeedOwnerId)
                    pure (lords, deeds)
  
          ret <- select q
          asserting $ ret `shouldMatchList` ((l3e, Nothing) : l1WithDeeds)
  
      itDb "lets you order by alias" $ do
          _ <- insert' p1
          _ <- insert' p3
          let q = do
                  (name, age) <-
                    Experimental.from $ do
                        p <- Experimental.from $ Table @Person
                        return ( p ^. PersonName, p ^. PersonAge)
                  orderBy [ asc age ]
                  pure name
          ret <- select q
          asserting $ ret `shouldBe` [ Value $ personName p3, Value $ personName p1 ]
  
      itDb "supports groupBy" $ do
          l1k <- insert l1
          l3k <- insert l3
          mapM_ (\k -> insert $ Deed k l1k) (map show [1..3 :: Int])
  
          mapM_ (\k -> insert $ Deed k l3k) (map show [4..10 :: Int])
          let q = do
                  (lord :& deed) <- Experimental.from $ Table @Lord
                                          `InnerJoin` Table @Deed
                                    `Experimental.on` (\(lord :& deed) ->
                                                         lord ^. LordId ==. deed ^. DeedOwnerId)
                  return (lord ^. LordId, deed ^. DeedId)
              q' = do
                   (lordId, deedId) <- Experimental.from q
                   groupBy (lordId)
                   return (lordId, count deedId)
          (ret :: [(Value (Key Lord), Value Int)]) <- select q'
  
          asserting $ ret `shouldMatchList` [ (Value l3k, Value 7)
                                         , (Value l1k, Value 3) ]
  
      itDb "Can count results of aggregate query" $ do
          l1k <- insert l1
          l3k <- insert l3
          mapM_ (\k -> insert $ Deed k l1k) (map show [1..3 :: Int])
  
          mapM_ (\k -> insert $ Deed k l3k) (map show [4..10 :: Int])
          let q = do
                  (lord :& deed) <- Experimental.from $ Table @Lord
                                          `InnerJoin` Table @Deed
                                    `Experimental.on` (\(lord :& deed) ->
                                                        lord ^. LordId ==. deed ^. DeedOwnerId)
                  groupBy (lord ^. LordId)
                  return (lord ^. LordId, count (deed ^. DeedId))
  
          (ret :: [(Value Int)]) <- select $ do
                   (lordId, deedCount) <- Experimental.from q
                   where_ $ deedCount >. val (3 :: Int)
                   return (count lordId)
  
          asserting $ ret `shouldMatchList` [ (Value 1) ]
  
      itDb "joins on subqueries" $ do
          l1k <- insert l1
          l3k <- insert l3
          mapM_ (\k -> insert $ Deed k l1k) (map show [1..3 :: Int])
  
          mapM_ (\k -> insert $ Deed k l3k) (map show [4..10 :: Int])
          let q = do
                  (lord :& deed) <- Experimental.from $ Table @Lord
                          `InnerJoin` (Experimental.from $ Table @Deed)
                          `Experimental.on` (\(lord :& deed) ->
                                               lord ^. LordId ==. deed ^. DeedOwnerId)
                  groupBy (lord ^. LordId)
                  return (lord ^. LordId, count (deed ^. DeedId))
          (ret :: [(Value (Key Lord), Value Int)]) <- select q
          asserting $ ret `shouldMatchList` [ (Value l3k, Value 7)
                                         , (Value l1k, Value 3) ]
  
      itDb "flattens maybe values" $ do
          l1k <- insert l1
          l3k <- insert l3
          let q = do
                  (lord :& (_, dogCounts)) <- Experimental.from $ Table @Lord
                          `LeftOuterJoin` do
                              lord <- Experimental.from $ Table @Lord
                              pure (lord ^. LordId, lord ^. LordDogs)
                          `Experimental.on` (\(lord :& (lordId, _)) ->
                                               just (lord ^. LordId) ==. lordId)
                  groupBy (lord ^. LordId, dogCounts)
                  return (lord ^. LordId, dogCounts)
          (ret :: [(Value (Key Lord), Value (Maybe Int))]) <- select q
          asserting $ ret `shouldMatchList` [ (Value l3k, Value (lordDogs l3))
                                         , (Value l1k, Value (lordDogs l1)) ]
      itDb "unions" $ do
            _ <- insert p1
            _ <- insert p2
            let q = Experimental.from $
                    (do
                      p <- Experimental.from $ Table @Person
                      where_ $ not_ $ isNothing $ p ^. PersonAge
                      return (p ^. PersonName))
                    `union_`
                    (do
                      p <- Experimental.from $ Table @Person
                      where_ $ isNothing $ p ^. PersonAge
                      return (p ^. PersonName))
                    `union_`
                    (do
                      p <- Experimental.from $ Table @Person
                      where_ $ isNothing $ p ^. PersonAge
                      return (p ^. PersonName))
            names <- select q
            asserting $ names `shouldMatchList` [ (Value $ personName p1)
                                             , (Value $ personName p2) ]
  testSelectWhere :: SpecDb
  testSelectWhere = describe "select where_" $ do
      itDb "works for a simple example with (==.)" $ do
          p1e <- insert' p1
          _   <- insert' p2
          _   <- insert' p3
          ret <- select $
                 from $ \p -> do
                 where_ (p ^. PersonName ==. val "John")
                 return p
          asserting $ ret `shouldBe` [ p1e ]
  
      itDb "works for a simple example with (==.) and (||.)" $ do
          p1e <- insert' p1
          p2e <- insert' p2
          _   <- insert' p3
          ret <- select $
                 from $ \p -> do
                 where_ (p ^. PersonName ==. val "John" ||. p ^. PersonName ==. val "Rachel")
                 return p
          asserting $ ret `shouldBe` [ p1e, p2e ]
  
      itDb "works for a simple example with (>.) [uses val . Just]" $ do
          p1e <- insert' p1
          _   <- insert' p2
          _   <- insert' p3
          ret <- select $
                 from $ \p -> do
                 where_ (p ^. PersonAge >. val (Just 17))
                 return p
          asserting $ ret `shouldBe` [ p1e ]
  
      describe "when using between" $ do
          itDb "works for a simple example with [uses just . val]" $ do
              p1e  <- insert' p1
              _    <- insert' p2
              _    <- insert' p3
              ret  <- select $
                from $ \p -> do
                  where_ ((p ^. PersonAge) `between` (just $ val 20, just $ val 40))
                  return p
              asserting $ ret `shouldBe` [ p1e ]
          itDb "works for a proyected fields value" $ do
              _ <- insert' p1 >> insert' p2 >> insert' p3
              ret <-
                select $
                from $ \p -> do
                where_ $
                  just (p ^. PersonFavNum)
                    `between`
                      (p ^. PersonAge, p ^.  PersonWeight)
              asserting $ ret `shouldBe` []
          describe "when projecting composite keys" $ do
              itDb "works when using composite keys with val" $ do
                  insert_ $ Point 1 2 ""
                  ret <-
                    select $
                    from $ \p -> do
                    where_ $
                      p ^. PointId
                        `between`
                          ( val $ PointKey 1 2
                          , val $ PointKey 5 6 )
                  asserting $ ret `shouldBe` [()]
  
      describe "when using not_" $ do
          itDb "works for a single expression" $ do
              ret <-
                  select $
                  pure $ not_ $ val True
              asserting $ do
                  ret `shouldBe` [Value False]
  
          itDb "works for a simple example with (>.) [uses just . val]" $ do
              _   <- insert' p1
              _   <- insert' p2
              p3e <- insert' p3
              ret <- select $
                     from $ \p -> do
                     where_ (not_ $ p ^. PersonAge >. just (val 17))
                     return p
              asserting $ ret `shouldBe` [ p3e ]
          itDb "works with (==.) and (||.)" $ do
              _   <- insert' p1
              _   <- insert' p2
              p3e <- insert' p3
              ret <- select $
                     from $ \p -> do
                     where_ (not_ $ p ^. PersonName ==. val "John" ||. p ^. PersonName ==. val "Rachel")
                     return p
              asserting $ ret `shouldBe` [ p3e ]
          itDb "works with (>.), (<.) and (&&.) [uses just . val]" $ do
              p1e <- insert' p1
              _   <- insert' p2
              _   <- insert' p3
              ret <- select $
                     from $ \p -> do
                     where_ (not_ $ (p ^. PersonAge >. just (val 10)) &&. (p ^. PersonAge <. just (val 30)))
                     return p
              asserting $ ret `shouldBe` [ p1e ]
          itDb "works with between [uses just . val]" $ do
              _   <- insert' p1
              _   <- insert' p2
              p3e <- insert' p3
              ret <- select $
                     from $ \p -> do
                     where_ (not_ $ (p ^. PersonAge) `between` (just $ val 20, just $ val 40))
                     return p
              asserting $ ret `shouldBe` [ p3e ]
  
      itDb "works with avg_" $ do
          _ <- insert' p1
          _ <- insert' p2
          _ <- insert' p3
          _ <- insert' p4
          ret <- select $
                 from $ \p->
                 return $ joinV $ avg_ (p ^. PersonAge)
          let testV :: Double
              testV = roundTo (4 :: Integer) $ (36 + 17 + 17) / (3 :: Double)
  
              retV :: [Value (Maybe Double)]
              retV = map (Value . fmap (roundTo (4 :: Integer)) . unValue) (ret :: [Value (Maybe Double)])
          asserting $ retV `shouldBe` [ Value $ Just testV ]
  
      itDb "works with min_" $
        do
          _ <- insert' p1
          _ <- insert' p2
          _ <- insert' p3
          _ <- insert' p4
          ret <- select $
                 from $ \p->
                 return $ joinV $ min_ (p ^. PersonAge)
          asserting $ ret `shouldBe` [ Value $ Just (17 :: Int) ]
  
      itDb "works with max_" $ do
          _ <- insert' p1
          _ <- insert' p2
          _ <- insert' p3
          _ <- insert' p4
          ret <- select $
                 from $ \p->
                 return $ joinV $ max_ (p ^. PersonAge)
          asserting $ ret `shouldBe` [ Value $ Just (36 :: Int) ]
  
      itDb "works with lower_" $ do
          p1e <- insert' p1
          p2e@(Entity _ bob) <- insert' $ Person "bob" (Just 36) Nothing   1
  
          -- lower(name) == 'john'
          ret1 <- select $
                  from $ \p-> do
                  where_ (lower_ (p ^. PersonName) ==. val (map toLower $ personName p1))
                  return p
          asserting $ ret1 `shouldBe` [ p1e ]
  
          -- name == lower('BOB')
          ret2 <- select $
                  from $ \p-> do
                  where_ (p ^. PersonName ==. lower_ (val $ map toUpper $ personName bob))
                  return p
          asserting $ ret2 `shouldBe` [ p2e ]
  
      itDb "works with round_" $ do
          ret <- select $ return $ round_ (val (16.2 :: Double))
          asserting $ ret `shouldBe` [ Value (16 :: Double) ]
  
      itDb "works with isNothing" $ do
          _   <- insert' p1
          p2e <- insert' p2
          _   <- insert' p3
          ret <- select $
                 from $ \p -> do
                 where_ $ isNothing (p ^. PersonAge)
                 return p
          asserting $ ret `shouldBe` [ p2e ]
  
      itDb "works with not_ . isNothing" $ do
          p1e <- insert' p1
          _   <- insert' p2
          ret <- select $
                 from $ \p -> do
                 where_ $ not_ (isNothing (p ^. PersonAge))
                 return p
          asserting $ ret `shouldBe` [ p1e ]
  
      itDb "works for a many-to-many implicit join" $
        do
          p1e@(Entity p1k _) <- insert' p1
          p2e@(Entity p2k _) <- insert' p2
          _                  <- insert' p3
          p4e@(Entity p4k _) <- insert' p4
          f12 <- insert' (Follow p1k p2k)
          f21 <- insert' (Follow p2k p1k)
          f42 <- insert' (Follow p4k p2k)
          f11 <- insert' (Follow p1k p1k)
          ret <- select $
                 from $ \(follower, follows, followed) -> do
                 where_ $ follower ^. PersonId ==. follows ^. FollowFollower &&.
                          followed ^. PersonId ==. follows ^. FollowFollowed
                 orderBy [ asc (follower ^. PersonName)
                         , asc (followed ^. PersonName) ]
                 return (follower, follows, followed)
          asserting $ ret `shouldBe` [ (p1e, f11, p1e)
                                  , (p1e, f12, p2e)
                                  , (p4e, f42, p2e)
                                  , (p2e, f21, p1e) ]
  
      itDb "works for a many-to-many explicit join" $ do
          p1e@(Entity p1k _) <- insert' p1
          p2e@(Entity p2k _) <- insert' p2
          _                  <- insert' p3
          p4e@(Entity p4k _) <- insert' p4
          f12 <- insert' (Follow p1k p2k)
          f21 <- insert' (Follow p2k p1k)
          f42 <- insert' (Follow p4k p2k)
          f11 <- insert' (Follow p1k p1k)
          ret <- select $
                 from $ \(follower `InnerJoin` follows `InnerJoin` followed) -> do
                 on $ followed ^. PersonId ==. follows ^. FollowFollowed
                 on $ follower ^. PersonId ==. follows ^. FollowFollower
                 orderBy [ asc (follower ^. PersonName)
                         , asc (followed ^. PersonName) ]
                 return (follower, follows, followed)
          asserting $ ret `shouldBe` [ (p1e, f11, p1e)
                                  , (p1e, f12, p2e)
                                  , (p4e, f42, p2e)
                                  , (p2e, f21, p1e) ]
  
      itDb "works for a many-to-many explicit join and on order doesn't matter" $ do
        void $
          selectRethrowingQuery $
          from $ \(person `InnerJoin` blog `InnerJoin` comment) -> do
          on $ person ^. PersonId ==. blog ^. BlogPostAuthorId
          on $ blog ^. BlogPostId ==. comment ^. CommentBlog
          pure (person, comment)
  
        -- we only care that we don't have a SQL error
        asserting noExceptions
  
      itDb "works for a many-to-many explicit join with LEFT OUTER JOINs" $ do
          p1e@(Entity p1k _) <- insert' p1
          p2e@(Entity p2k _) <- insert' p2
          p3e                <- insert' p3
          p4e@(Entity p4k _) <- insert' p4
          f12 <- insert' (Follow p1k p2k)
          f21 <- insert' (Follow p2k p1k)
          f42 <- insert' (Follow p4k p2k)
          f11 <- insert' (Follow p1k p1k)
          ret <- select $
                 from $ \(follower `LeftOuterJoin` mfollows `LeftOuterJoin` mfollowed) -> do
                 on $      mfollowed ?. PersonId  ==. mfollows ?. FollowFollowed
                 on $ just (follower ^. PersonId) ==. mfollows ?. FollowFollower
                 orderBy [ asc ( follower ^. PersonName)
                         , asc (mfollowed ?. PersonName) ]
                 return (follower, mfollows, mfollowed)
          asserting $ ret `shouldBe` [ (p1e, Just f11, Just p1e)
                                  , (p1e, Just f12, Just p2e)
                                  , (p4e, Just f42, Just p2e)
                                  , (p3e, Nothing,  Nothing)
                                  , (p2e, Just f21, Just p1e) ]
  
      itDb "works with a composite primary key" $ do
          let p = Point x y ""
              x = 10
              y = 15
              Right thePk = keyFromValues [toPersistValue x, toPersistValue y]
          pPk <- insert p
          [Entity _ ret] <- select $ from $ \p' -> do
            where_ (p'^.PointId ==. val pPk)
            return p'
          asserting $ do
            ret `shouldBe` p
            pPk `shouldBe` thePk
  
  testSelectOrderBy :: SpecDb
  testSelectOrderBy = describe "select/orderBy" $ do
      itDb "works with a single ASC field" $ do
          p1e <- insert' p1
          p2e <- insert' p2
          p3e <- insert' p3
          ret <- select $
                 from $ \p -> do
                 orderBy [asc $ p ^. PersonName]
                 return p
          asserting $ ret `shouldBe` [ p1e, p3e, p2e ]
  
      itDb "works with a subSelect" $ do
          [p1k, p2k, p3k, p4k] <- mapM insert [p1, p2, p3, p4]
          [b1k, b2k, b3k, b4k] <- mapM (insert . BlogPost "") [p1k, p2k, p3k, p4k]
          ret <- select $
                 from $ \b -> do
                 orderBy [desc $ subSelect $
                                 from $ \p -> do
                                 where_ (p ^. PersonId ==. b ^. BlogPostAuthorId)
                                 return (p ^. PersonName)
                         ]
                 return (b ^. BlogPostId)
          asserting $ ret `shouldBe` (Value <$> [b2k, b3k, b4k, b1k])
  
      itDb "works on a composite primary key" $ do
          let ps = [Point 2 1 "", Point 1 2 ""]
          mapM_ insert ps
          eps <- select $
            from $ \p' -> do
              orderBy [asc (p'^.PointId)]
              return p'
          asserting $ map entityVal eps `shouldBe` reverse ps
  
  testAscRandom :: SqlExpr (Value Double) -> SpecDb
  testAscRandom rand' = describe "random_" $
      itDb "asc random_ works" $ do
          _p1e <- insert' p1
          _p2e <- insert' p2
          _p3e <- insert' p3
          _p4e <- insert' p4
          rets <-
            fmap S.fromList $
            replicateM 11 $
            select $
            from $ \p -> do
            orderBy [asc (rand' :: SqlExpr (Value Double))]
            return (p ^. PersonId :: SqlExpr (Value PersonId))
          -- There are 2^4 = 16 possible orderings.  The chance
          -- of 11 random samplings returning the same ordering
          -- is 1/2^40, so this test should pass almost everytime.
          asserting $ S.size rets `shouldSatisfy` (>2)
  
  testSelectDistinct :: SpecDb
  testSelectDistinct = do
    describe "SELECT DISTINCT" $ do
      let selDistTest
            ::
            ( SqlQuery (SqlExpr (Value String))
                -> SqlPersistT IO [Value String]
                )
            -> SqlPersistT IO ()
          selDistTest q = do
            p1k <- insert p1
            let (t1, t2, t3) = ("a", "b", "c")
            mapM_ (insert . flip BlogPost p1k) [t1, t3, t2, t2, t1]
            ret <- q $
                   from $ \b -> do
                   let title = b ^. BlogPostTitle
                   orderBy [asc title]
                   return title
            asserting $ ret `shouldBe` [ Value t1, Value t2, Value t3 ]
  
      itDb "works on a simple example (select . distinct)" $
        selDistTest (\a -> select $ distinct a)
  
      itDb "works on a simple example (distinct (return ()))" $
        selDistTest (\act -> select $ distinct (return ()) >> act)
  
  
  
  testCoasleceDefault :: SpecDb
  testCoasleceDefault = describe "coalesce/coalesceDefault" $ do
      itDb "works on a simple example" $ do
          mapM_ insert' [p1, p2, p3, p4, p5]
          ret1 <- select $
                  from $ \p -> do
                  orderBy [asc (p ^. PersonId)]
                  return (coalesce [p ^. PersonAge, p ^. PersonWeight])
          asserting $ ret1 `shouldBe` [ Value (Just (36 :: Int))
                                   , Value (Just 37)
                                   , Value (Just 17)
                                   , Value (Just 17)
                                   , Value Nothing
                                   ]
  
          ret2 <- select $
                  from $ \p -> do
                  orderBy [asc (p ^. PersonId)]
                  return (coalesceDefault [p ^. PersonAge, p ^. PersonWeight] (p ^. PersonFavNum))
          asserting $ ret2 `shouldBe` [ Value (36 :: Int)
                                   , Value 37
                                   , Value 17
                                   , Value 17
                                   , Value 5
                                   ]
  
      itDb "works with sub-queries" $ do
          p1id <- insert p1
          p2id <- insert p2
          p3id <- insert p3
          _    <- insert p4
          _    <- insert p5
          _ <- insert $ BlogPost "a" p1id
          _ <- insert $ BlogPost "b" p2id
          _ <- insert $ BlogPost "c" p3id
          ret <- select $
                 from $ \b -> do
                   let sub =
                           from $ \p -> do
                           where_ (p ^. PersonId ==. b ^. BlogPostAuthorId)
                           pure $ p ^. PersonAge
                   return $ coalesceDefault [joinV $ subSelect sub] (val (42 :: Int))
          asserting $ ret `shouldBe` [ Value (36 :: Int)
                                  , Value 42
                                  , Value 17
                                  ]
  
  
  testDelete :: SpecDb
  testDelete = describe "delete" $ do
      itDb "works on a simple example" $ do
          p1e <- insert' p1
          p2e <- insert' p2
          p3e <- insert' p3
          let getAll = select $
                       from $ \p -> do
                       orderBy [asc (p ^. PersonName)]
                       return p
          ret1 <- getAll
          asserting $ ret1 `shouldBe` [ p1e, p3e, p2e ]
          ()   <- delete $
                  from $ \p ->
                  where_ (p ^. PersonName ==. val (personName p1))
          ret2 <- getAll
          asserting $ ret2 `shouldBe` [ p3e, p2e ]
          n    <- deleteCount $
                  from $ \p ->
                  return ((p :: SqlExpr (Entity Person)) `seq` ())
          ret3 <- getAll
          asserting $ (n, ret3) `shouldBe` (2, [])
  
  testUpdate :: SpecDb
  testUpdate = describe "update" $ do
      itDb "works with a subexpression having COUNT(*)" $ do
          p1k <- insert p1
          p2k <- insert p2
          p3k <- insert p3
          replicateM_ 3 (insert $ BlogPost "" p1k)
          replicateM_ 7 (insert $ BlogPost "" p3k)
          let blogPostsBy p =
                from $ \b -> do
                where_ (b ^. BlogPostAuthorId ==. p ^. PersonId)
                return countRows
          ()  <- update $ \p -> do
                 set p [ PersonAge =. subSelect (blogPostsBy p) ]
          ret <- select $
                 from $ \p -> do
                 orderBy [ asc (p ^. PersonName) ]
                 return p
          asserting $ ret `shouldBe` [ Entity p1k p1 { personAge = Just 3 }
                                  , Entity p3k p3 { personAge = Just 7 }
                                  , Entity p2k p2 { personAge = Just 0 } ]
  
      it "works with a composite primary key" $ \_ ->
          pendingWith "Need refactor to support composite pks on ESet"
        {-
        do
          let p = Point x y ""
              x = 10
              y = 15
              newX = 20
              newY = 25
              Right newPk = keyFromValues [toPersistValue newX, toPersistValue newY]
          insert_ p
          () <- update $ \p' -> do
                set p' [PointId =. val newPk]
          [Entity _ ret] <- select $ from $ return
          asserting $ do
            ret `shouldBe` Point newX newY []
        -}
  
      itDb "GROUP BY works with COUNT" $ do
          p1k <- insert p1
          p2k <- insert p2
          p3k <- insert p3
          replicateM_ 3 (insert $ BlogPost "" p1k)
          replicateM_ 7 (insert $ BlogPost "" p3k)
          ret <- select $
                 from $ \(p `LeftOuterJoin` b) -> do
                 on (p ^. PersonId ==. b ^. BlogPostAuthorId)
                 groupBy (p ^. PersonId)
                 let cnt = count (b ^. BlogPostId)
                 orderBy [ asc cnt ]
                 return (p, cnt)
          asserting $ ret `shouldBe` [ (Entity p2k p2, Value (0 :: Int))
                                  , (Entity p1k p1, Value 3)
                                  , (Entity p3k p3, Value 7) ]
  
      itDb "GROUP BY works with composite primary key" $ do
          p1k <- insert $ Point 1 2 "asdf"
          p2k <- insert $ Point 2 3 "asdf"
          ret <-
              selectRethrowingQuery $
              from $ \point -> do
              where_ $ point ^. PointName ==. val "asdf"
              groupBy (point ^. PointId)
              pure (point ^. PointId)
          asserting $ do
              ret `shouldMatchList`
                  map Value [p1k, p2k]
  
  
  
      itDb "GROUP BY works with COUNT and InnerJoin" $ do
          l1k <- insert l1
          l3k <- insert l3
          mapM_ (\k -> insert $ Deed k l1k) (map show [1..3 :: Int])
  
          mapM_ (\k -> insert $ Deed k l3k) (map show [4..10 :: Int])
  
          (ret :: [(Value (Key Lord), Value Int)]) <- select $ from $
            \ ( lord `InnerJoin` deed ) -> do
            on $ lord ^. LordId ==. deed ^. DeedOwnerId
            groupBy (lord ^. LordId)
            return (lord ^. LordId, count $ deed ^. DeedId)
          asserting $ ret `shouldMatchList` [ (Value l3k, Value 7)
                                         , (Value l1k, Value 3) ]
  
      itDb "GROUP BY works with nested tuples" $ do
          l1k <- insert l1
          l3k <- insert l3
          mapM_ (\k -> insert $ Deed k l1k) (map show [1..3 :: Int])
  
          mapM_ (\k -> insert $ Deed k l3k) (map show [4..10 :: Int])
  
          (ret :: [(Value (Key Lord), Value Int)]) <- select $ from $
            \ ( lord `InnerJoin` deed ) -> do
            on $ lord ^. LordId ==. deed ^. DeedOwnerId
            groupBy ((lord ^. LordId, lord ^. LordDogs), deed ^. DeedContract)
            return (lord ^. LordId, count $ deed ^. DeedId)
          asserting $ length ret `shouldBe` 10
  
      itDb "GROUP BY works with HAVING" $ do
          p1k <- insert p1
          _p2k <- insert p2
          p3k <- insert p3
          replicateM_ 3 (insert $ BlogPost "" p1k)
          replicateM_ 7 (insert $ BlogPost "" p3k)
          ret <- select $
                 from $ \(p `LeftOuterJoin` b) -> do
                 on (p ^. PersonId ==. b ^. BlogPostAuthorId)
                 let cnt = count (b ^. BlogPostId)
                 groupBy (p ^. PersonId)
                 having (cnt >. (val 0))
                 orderBy [ asc cnt ]
                 return (p, cnt)
          asserting $ ret `shouldBe` [ (Entity p1k p1, Value (3 :: Int))
                                  , (Entity p3k p3, Value 7) ]
  
  -- we only care that this compiles. check that SqlWriteT doesn't fail on
  -- updates.
  testSqlWriteT :: MonadIO m => SqlWriteT m ()
  testSqlWriteT =
    update $ \p -> do
      set p [ PersonAge =. just (val 6) ]
  
  -- we only care that this compiles. checks that the SqlWriteT monad can run
  -- select queries.
  testSqlWriteTRead :: MonadIO m => SqlWriteT m [(Value (Key Lord), Value Int)]
  testSqlWriteTRead =
    select $
    from $ \ ( lord `InnerJoin` deed ) -> do
    on $ lord ^. LordId ==. deed ^. DeedOwnerId
    groupBy (lord ^. LordId)
    return (lord ^. LordId, count $ deed ^. DeedId)
  
  -- we only care that this compiles checks that SqlReadT allows
  testSqlReadT :: MonadIO m => SqlReadT m [(Value (Key Lord), Value Int)]
  testSqlReadT =
    select $
    from $ \ ( lord `InnerJoin` deed ) -> do
    on $ lord ^. LordId ==. deed ^. DeedOwnerId
    groupBy (lord ^. LordId)
    return (lord ^. LordId, count $ deed ^. DeedId)
  
  testListOfValues :: SpecDb
  testListOfValues = describe "lists of values" $ do
      itDb "IN works for valList" $ do
          p1k <- insert p1
          p2k <- insert p2
          _p3k <- insert p3
          ret <- select $
                 from $ \p -> do
                 where_ (p ^. PersonName `in_` valList (personName <$> [p1, p2]))
                 return p
          asserting $ ret `shouldBe` [ Entity p1k p1
                                  , Entity p2k p2 ]
  
      itDb "IN works for valList (null list)" $ do
          _p1k <- insert p1
          _p2k <- insert p2
          _p3k <- insert p3
          ret <- select $
                 from $ \p -> do
                 where_ (p ^. PersonName `in_` valList [])
                 return p
          asserting $ ret `shouldBe` []
  
      itDb "IN works for subList_select" $ do
          p1k <- insert p1
          _p2k <- insert p2
          p3k <- insert p3
          _ <- insert (BlogPost "" p1k)
          _ <- insert (BlogPost "" p3k)
          ret <- select $
                 from $ \p -> do
                 let subquery =
                       from $ \bp -> do
                       orderBy [ asc (bp ^. BlogPostAuthorId) ]
                       return (bp ^. BlogPostAuthorId)
                 where_ (p ^. PersonId `in_` subList_select subquery)
                 return p
          asserting $ L.sort ret `shouldBe` L.sort [Entity p1k p1, Entity p3k p3]
  
      itDb "NOT IN works for subList_select" $ do
          p1k <- insert p1
          p2k <- insert p2
          p3k <- insert p3
          _ <- insert (BlogPost "" p1k)
          _ <- insert (BlogPost "" p3k)
          ret <- select $
                 from $ \p -> do
                 let subquery =
                       from $ \bp ->
                       return (bp ^. BlogPostAuthorId)
                 where_ (p ^. PersonId `notIn` subList_select subquery)
                 return p
          asserting $ ret `shouldBe` [ Entity p2k p2 ]
  
      itDb "NOT IN works for valList (null list)" $ do
          p1k <- insert p1
          p2k <- insert p2
          p3k <- insert p3
          ret <- select $
                 from $ \p -> do
                 where_ (p ^. PersonName `notIn` valList [])
                 return p
          asserting $ ret `shouldMatchList` [ Entity p1k p1
                                            , Entity p2k p2
                                            , Entity p3k p3
                                            ]
  
      itDb "EXISTS works for subList_select" $ do
          p1k <- insert p1
          _p2k <- insert p2
          p3k <- insert p3
          _ <- insert (BlogPost "" p1k)
          _ <- insert (BlogPost "" p3k)
          ret <- select $
                 from $ \p -> do
                 where_ $ exists $
                          from $ \bp -> do
                          where_ (bp ^. BlogPostAuthorId ==. p ^. PersonId)
                 orderBy [asc (p ^. PersonName)]
                 return p
          asserting $ ret `shouldBe` [ Entity p1k p1
                                  , Entity p3k p3 ]
  
      itDb "EXISTS works for subList_select" $ do
          p1k <- insert p1
          p2k <- insert p2
          p3k <- insert p3
          _ <- insert (BlogPost "" p1k)
          _ <- insert (BlogPost "" p3k)
          ret <- select $
                 from $ \p -> do
                 where_ $ notExists $
                          from $ \bp -> do
                          where_ (bp ^. BlogPostAuthorId ==. p ^. PersonId)
                 return p
          asserting $ ret `shouldBe` [ Entity p2k p2 ]
  
  testListFields :: SpecDb
  testListFields = describe "list fields" $ do
      -- <https://github.com/prowdsponsor/esqueleto/issues/100>
      itDb "can update list fields" $ do
          cclist <- insert $ CcList []
          update $ \p -> do
              set p [ CcListNames =. val ["fred"]]
              where_ (p ^. CcListId ==. val cclist)
          asserting noExceptions
  
  testInsertsBySelect :: SpecDb
  testInsertsBySelect = do
    describe "inserts by select" $ do
      itDb "IN works for insertSelect" $
        do
          _ <- insert p1
          _ <- insert p2
          _ <- insert p3
          insertSelect $ from $ \p -> do
            return $ BlogPost <# val "FakePost" <&> (p ^. PersonId)
          ret <- select $ from (\(_::(SqlExpr (Entity BlogPost))) -> return countRows)
          asserting $ ret `shouldBe` [Value (3::Int)]
  
  
  
  
  
  testInsertsBySelectReturnsCount :: SpecDb
  testInsertsBySelectReturnsCount = do
    describe "inserts by select, returns count" $ do
      itDb "IN works for insertSelectCount" $
        do
          _ <- insert p1
          _ <- insert p2
          _ <- insert p3
          cnt <- insertSelectCount $ from $ \p -> do
            return $ BlogPost <# val "FakePost" <&> (p ^. PersonId)
          ret <- select $ from (\(_::(SqlExpr (Entity BlogPost))) -> return countRows)
          asserting $ ret `shouldBe` [Value (3::Int)]
          asserting $ cnt `shouldBe` 3
  
  testMathFunctions :: SpecDb
  testMathFunctions = do
    describe "Math-related functions" $ do
      itDb "castNum works for multiplying Int and Double" $
        do
          mapM_ insert [Numbers 2 3.4, Numbers 7 1.1]
          ret <-
            select $
            from $ \n -> do
            let r = castNum (n ^. NumbersInt) *. n ^. NumbersDouble
            orderBy [asc r]
            return r
          asserting $ length ret `shouldBe` 2
          let [Value a, Value b] = ret
          asserting $ max (abs (a - 6.8)) (abs (b - 7.7)) `shouldSatisfy` (< 0.01)
  
  
  
  
  
  testCase :: SpecDb
  testCase = do
    describe "case" $ do
      itDb "Works for a simple value based when - False" $
        do
          ret <- select $
            return $
              case_
                [ when_ (val False) then_ (val (1 :: Int)) ]
                (else_ (val 2))
  
          asserting $ ret `shouldBe` [ Value 2 ]
  
      itDb "Works for a simple value based when - True" $
        do
          ret <- select $
            return $
              case_
                [ when_ (val True) then_ (val (1 :: Int)) ]
                (else_ (val 2))
  
          asserting $ ret `shouldBe` [ Value 1 ]
  
      itDb "works for a semi-complicated query" $
        do
          _ <- insert p1
          _ <- insert p2
          _ <- insert p3
          _ <- insert p4
          _ <- insert p5
          ret <- select $
            return $
              case_
                [ when_
                    (exists $ from $ \p -> do
                        where_ (p ^. PersonName ==. val "Mike"))
                  then_
                    (subSelect $ from $ \v -> do
                        let sub =
                                from $ \c -> do
                                where_ (c ^. PersonName ==. val "Mike")
                                return (c ^. PersonFavNum)
                        where_ (just (v ^. PersonFavNum) >. subSelect sub)
                        return $ count (v ^. PersonName) +. val (1 :: Int)) ]
                (else_ $ just $ val (-1))
  
          asserting $ ret `shouldBe` [ Value (Just 3) ]
  
  testLocking :: SpecDb
  testLocking = do
    let toText conn q =
          let (tlb, _) = EI.toRawSql EI.SELECT (conn, EI.initialIdentState) q
           in TLB.toLazyText tlb
        complexQuery =
          from $ \(p1' `InnerJoin` p2') -> do
          on (p1' ^. PersonName ==. p2' ^. PersonName)
          where_ (p1' ^. PersonFavNum >. val 2)
          orderBy [desc (p2' ^. PersonAge)]
          limit 3
          offset 9
          groupBy (p1' ^. PersonId)
          having (countRows <. val (0 :: Int))
          return (p1', p2')
    describe "locking" $ do
      -- The locking clause is the last one, so try to use many
      -- others to test if it's at the right position.  We don't
      -- care about the text of the rest, nor with the RDBMS'
      -- reaction to the clause.
      let sanityCheck kind syntax = do
            let queryWithClause1 = do
                   r <- complexQuery
                   locking kind
                   return r
                queryWithClause2 = do
                  locking ForUpdate
                  r <- complexQuery
                  locking ForShare
                  locking kind
                  return r
                queryWithClause3 = do
                  locking kind
                  complexQuery
            conn <- ask
            [complex, with1, with2, with3] <-
              return $
                map (toText conn) [complexQuery, queryWithClause1, queryWithClause2, queryWithClause3]
            let expected = complex <> syntax <> "\n"
            asserting $ do
              with1 `shouldBe` expected
              with2 `shouldBe` expected
              with3 `shouldBe` expected
      itDb "looks sane for ForUpdate"           $ sanityCheck ForUpdate           "FOR UPDATE"
      itDb "looks sane for ForUpdateSkipLocked" $ sanityCheck ForUpdateSkipLocked "FOR UPDATE SKIP LOCKED"
      itDb "looks sane for ForShare"            $ sanityCheck ForShare            "FOR SHARE"
      itDb "looks sane for LockInShareMode"     $ sanityCheck LockInShareMode     "LOCK IN SHARE MODE"
  
    describe "Monoid instance" $ do
      let
          multiplePostgresLockingClauses p = do
              EP.forUpdateOf p EP.skipLocked
              EP.forUpdateOf p EP.skipLocked
              EP.forShareOf p EP.skipLocked
  
          multipleLegacyLockingClauses = do
              locking ForShare
              locking ForUpdate
  
          multipleLockingQueryPostgresLast = do
              p <- Experimental.from $ table @Person
              multipleLegacyLockingClauses
              multiplePostgresLockingClauses p
  
          multipleLockingQueryLegacyLast = do
              p <- Experimental.from $ table @Person
              multiplePostgresLockingClauses p
              multipleLegacyLockingClauses
  
          expectedPostgresQuery = do
              p <- Experimental.from $ table @Person
              EP.forUpdateOf p EP.skipLocked
              EP.forUpdateOf p EP.skipLocked
              EP.forShareOf p EP.skipLocked
  
          expectedLegacyQuery = do
              p <- Experimental.from $ table @Person
              locking ForUpdate
  
      itDb "prioritizes last grouping of locks when mixing legacy and postgres specific locks" $ do
  
          conn <- ask
          let resPostgresLast = toText conn multipleLockingQueryPostgresLast
              resLegacyLast = toText conn multipleLockingQueryLegacyLast
              resExpectedPostgres = toText conn expectedPostgresQuery
              resExpectedLegacy = toText conn expectedLegacyQuery
  
          asserting $ resPostgresLast `shouldBe` resExpectedPostgres
          asserting $ resLegacyLast `shouldBe` resExpectedLegacy
  
  testCountingRows :: SpecDb
  testCountingRows = do
    describe "counting rows" $ do
      forM_ [ ("count (test A)",    count . (^. PersonAge),         4)
            , ("count (test B)",    count . (^. PersonWeight),      5)
            , ("countRows",         const countRows,                5)
            , ("countDistinct",     countDistinct . (^. PersonAge), 2) ] $
        \(title, countKind, expected) ->
        itDb (title ++ " works as expected") $
          do
            mapM_ insert
              [ Person "" (Just 1) (Just 1) 1
              , Person "" (Just 2) (Just 1) 1
              , Person "" (Just 2) (Just 1) 1
              , Person "" (Just 2) (Just 2) 1
              , Person "" Nothing  (Just 3) 1]
            [Value n] <- select $ from $ return . countKind
            asserting $ (n :: Int) `shouldBe` expected
  
  testRenderSql :: SpecDb
  testRenderSql = do
    describe "testRenderSql" $ do
      itDb "works" $ do
        (queryText, queryVals) <- renderQuerySelect $
          from $ \p -> do
          where_ $ p ^. PersonName ==. val "Johhny Depp"
          pure (p ^. PersonName, p ^. PersonAge)
        -- the different backends use different quote marks, so I filter them out
        -- here instead of making a duplicate test
        asserting $ do
            Text.filter (\c -> c `notElem` ['`', '"']) queryText
              `shouldBe`
                Text.unlines
                  [ "SELECT Person.name, Person.age"
                  , "FROM Person"
                  , "WHERE Person.name = ?"
                  ]
            queryVals
              `shouldBe`
                [toPersistValue ("Johhny Depp" :: TL.Text)]
  
    describe "renderExpr" $ do
      itDb "renders a value" $ do
        (c, expr) <- do
          conn <- ask
          let Right c = P.mkEscapeChar conn
          let user = EI.unsafeSqlEntity (EI.I "user")
              blogPost = EI.unsafeSqlEntity (EI.I "blog_post")
          pure $ (,) c $ EI.renderExpr conn $
            user ^. PersonId ==. blogPost ^. BlogPostAuthorId
        asserting $ do
            expr
              `shouldBe`
                Text.intercalate (Text.singleton c) ["", "user", ".", "id", ""]
                <>
                " = "
                <>
                Text.intercalate (Text.singleton c) ["", "blog_post", ".", "authorId", ""]
  
      itDb "renders ? for a val" $ do
        expr <- ask >>= \c -> pure $ EI.renderExpr c (val (PersonKey 0) ==. val (PersonKey 1))
        asserting $ expr `shouldBe` "? = ?"
  
    beforeWith (\_ -> pure ()) $ describe "ExprParser" $ do
      let parse parser = AP.parseOnly (parser '#')
      describe "parseEscapedChars" $ do
        let subject = parse P.parseEscapedChars
        it "parses words" $ do
          subject "hello world"
            `shouldBe`
              Right "hello world"
        it "only returns a single escape-char if present" $ do
          subject "i_am##identifier##"
            `shouldBe`
              Right "i_am#identifier#"
      describe "parseEscapedIdentifier" $ do
        let subject = parse P.parseEscapedIdentifier
        it "parses the quotes out" $ do
          subject "#it's a me, mario#"
            `shouldBe`
              Right "it's a me, mario"
        it "requires a beginning and end quote" $ do
          subject "#alas, i have no end"
            `shouldSatisfy`
              isLeft
      describe "parseTableAccess" $ do
        let subject = parse P.parseTableAccess
        it "parses a table access" $ do
          subject "#foo#.#bar#"
            `shouldBe`
              Right P.TableAccess
                { P.tableAccessTable = "foo"
                , P.tableAccessColumn = "bar"
                }
      describe "onExpr" $ do
        let subject = parse P.onExpr
        it "works" $ do
          subject "#foo#.#bar# = #bar#.#baz#"
            `shouldBe` do
              Right $ S.fromList
                [ P.TableAccess
                  { P.tableAccessTable = "foo"
                  , P.tableAccessColumn = "bar"
                  }
                , P.TableAccess
                  { P.tableAccessTable = "bar"
                  , P.tableAccessColumn = "baz"
                  }
                ]
        it "also works with other nonsense" $ do
          subject "#foo#.#bar# = 3"
            `shouldBe` do
              Right $ S.fromList
                [ P.TableAccess
                  { P.tableAccessTable = "foo"
                  , P.tableAccessColumn = "bar"
                  }
                ]
        it "handles a conjunction" $ do
          subject "#foo#.#bar# = #bar#.#baz# AND #bar#.#baz# > 10"
            `shouldBe` do
              Right $ S.fromList
                [ P.TableAccess
                  { P.tableAccessTable = "foo"
                  , P.tableAccessColumn = "bar"
                  }
                , P.TableAccess
                  { P.tableAccessTable = "bar"
                  , P.tableAccessColumn = "baz"
                  }
                ]
        it "handles ? okay" $ do
          subject "#foo#.#bar# = ?"
            `shouldBe` do
              Right $ S.fromList
                [ P.TableAccess
                  { P.tableAccessTable = "foo"
                  , P.tableAccessColumn = "bar"
                  }
                ]
        it "handles degenerate cases" $ do
          subject "false" `shouldBe` pure mempty
          subject "true" `shouldBe` pure mempty
          subject "1 = 1" `shouldBe` pure mempty
        it "works even if an identifier isn't first" $ do
          subject "true and #foo#.#bar# = 2"
            `shouldBe` do
              Right $ S.fromList
                [ P.TableAccess
                  { P.tableAccessTable = "foo"
                  , P.tableAccessColumn = "bar"
                  }
                ]
  
  testOnClauseOrder :: SpecDb
  testOnClauseOrder = describe "On Clause Ordering" $ do
      let
          setup :: MonadIO m => SqlPersistT m ()
          setup = do
              ja1 <- insert (JoinOne "j1 hello")
              ja2 <- insert (JoinOne "j1 world")
              jb1 <- insert (JoinTwo ja1 "j2 hello")
              jb2 <- insert (JoinTwo ja1 "j2 world")
              jb3 <- insert (JoinTwo ja2 "j2 foo")
              _ <- insert (JoinTwo ja2 "j2 bar")
              jc1 <- insert (JoinThree jb1 "j3 hello")
              jc2 <- insert (JoinThree jb1 "j3 world")
              _ <- insert (JoinThree jb2 "j3 foo")
              _ <- insert (JoinThree jb3 "j3 bar")
              _ <- insert (JoinThree jb3 "j3 baz")
              _ <- insert (JoinFour "j4 foo" jc1)
              _ <- insert (JoinFour "j4 bar" jc2)
              jd1 <- insert (JoinOther "foo")
              jd2 <- insert (JoinOther "bar")
              _ <- insert (JoinMany "jm foo hello" jd1 ja1)
              _ <- insert (JoinMany "jm foo world" jd1 ja2)
              _ <- insert (JoinMany "jm bar hello" jd2 ja1)
              _ <- insert (JoinMany "jm bar world" jd2 ja2)
              pure ()
      describe "identical results for" $ do
          itDb "three tables" $ do
              setup
              abcs <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c) -> do
                  on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                  on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                  pure (a, b, c)
              acbs <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c) -> do
                  on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                  on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                  pure (a, b, c)
  
              asserting $ do
                  listsEqualOn abcs acbs $ \(Entity _ j1, Entity _ j2, Entity _ j3) ->
                    (joinOneName j1, joinTwoName j2, joinThreeName j3)
  
          itDb "four tables" $ do
              setup
              xs0 <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c `InnerJoin` d) -> do
                  on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                  on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                  on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                  pure (a, b, c, d)
              xs1 <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c `InnerJoin` d) -> do
                  on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                  on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                  on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                  pure (a, b, c, d)
              xs2 <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c `InnerJoin` d) -> do
                  on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                  on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                  on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                  pure (a, b, c, d)
              xs3 <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c `InnerJoin` d) -> do
                  on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                  on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                  on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                  pure (a, b, c, d)
              xs4 <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c `InnerJoin` d) -> do
                  on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                  on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                  on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                  pure (a, b, c, d)
  
              let
                  getNames (j1, j2, j3, j4) =
                      ( joinOneName (entityVal j1)
                      , joinTwoName (entityVal j2)
                      , joinThreeName (entityVal j3)
                      , joinFourName (entityVal j4)
                      )
              asserting $ do
                  listsEqualOn xs0 xs1 getNames
                  listsEqualOn xs0 xs2 getNames
                  listsEqualOn xs0 xs3 getNames
                  listsEqualOn xs0 xs4 getNames
  
          itDb "associativity of innerjoin" $ do
            setup
            xs0 <-
              select $
                from $ \(a `InnerJoin` b `InnerJoin` c `InnerJoin` d) -> do
                on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                pure (a, b, c, d)
  
            xs1 <-
              select $
                from $ \(a `InnerJoin` b `InnerJoin` (c `InnerJoin` d)) -> do
                on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                pure (a, b, c, d)
  
            xs2 <-
              select $
                from $ \(a `InnerJoin` (b `InnerJoin` c) `InnerJoin` d) -> do
                on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                pure (a, b, c, d)
  
            xs3 <-
              select $
                from $ \(a `InnerJoin` (b `InnerJoin` c `InnerJoin` d)) -> do
                on (a ^. JoinOneId ==. b ^. JoinTwoJoinOne)
                on (b ^. JoinTwoId ==. c ^. JoinThreeJoinTwo)
                on (c ^. JoinThreeId ==. d ^. JoinFourJoinThree)
                pure (a, b, c, d)
  
            let getNames (j1, j2, j3, j4) =
                  ( joinOneName (entityVal j1)
                  , joinTwoName (entityVal j2)
                  , joinThreeName (entityVal j3)
                  , joinFourName (entityVal j4)
                  )
            asserting $ do
                listsEqualOn xs0 xs1 getNames
                listsEqualOn xs0 xs2 getNames
                listsEqualOn xs0 xs3 getNames
  
          itDb "inner join on two entities" $ do
            (xs0, xs1) <- do
              pid <- insert $ Person "hello" Nothing Nothing 3
              _ <- insert $ BlogPost "good poast" pid
              _ <- insert $ Profile "cool" pid
              xs0 <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr) -> do
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                pure (p, b, pr)
              xs1 <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr) -> do
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                pure (p, b, pr)
              pure (xs0, xs1)
            asserting $ listsEqualOn xs0 xs1 $ \(Entity _ p, Entity _ b, Entity _ pr) ->
              (personName p, blogPostTitle b, profileName pr)
          itDb "inner join on three entities" $ do
            res <- do
              pid <- insert $ Person "hello" Nothing Nothing 3
              _ <- insert $ BlogPost "good poast" pid
              _ <- insert $ BlogPost "good poast #2" pid
              _ <- insert $ Profile "cool" pid
              _ <- insert $ Reply pid "u wot m8"
              _ <- insert $ Reply pid "how dare you"
  
              bprr <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr `InnerJoin` r) -> do
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                on $ p ^. PersonId ==. r ^. ReplyGuy
                pure (p, b, pr, r)
  
              brpr <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr `InnerJoin` r) -> do
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                on $ p ^. PersonId ==. r ^. ReplyGuy
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                pure (p, b, pr, r)
  
              prbr <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr `InnerJoin` r) -> do
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                on $ p ^. PersonId ==. r ^. ReplyGuy
                pure (p, b, pr, r)
  
              prrb <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr `InnerJoin` r) -> do
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                on $ p ^. PersonId ==. r ^. ReplyGuy
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                pure (p, b, pr, r)
  
              rprb <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr `InnerJoin` r) -> do
                on $ p ^. PersonId ==. r ^. ReplyGuy
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                pure (p, b, pr, r)
  
              rbpr <- selectRethrowingQuery $
                from $ \(p `InnerJoin` b `InnerJoin` pr `InnerJoin` r) -> do
                on $ p ^. PersonId ==. r ^. ReplyGuy
                on $ p ^. PersonId ==. b ^. BlogPostAuthorId
                on $ p ^. PersonId ==. pr ^. ProfilePerson
                pure (p, b, pr, r)
  
              pure [bprr, brpr, prbr, prrb, rprb, rbpr]
            asserting $ forM_ (zip res (drop 1 (cycle res))) $ \(a, b) -> a `shouldBe` b
  
          itDb "many-to-many" $ do
              setup
              ac <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c) -> do
                  on (a ^. JoinOneId ==. b ^. JoinManyJoinOne)
                  on (c ^. JoinOtherId ==. b ^. JoinManyJoinOther)
                  pure (a, c)
  
              ca <-
                  select $
                  from $ \(a `InnerJoin` b `InnerJoin` c) -> do
                  on (c ^. JoinOtherId ==. b ^. JoinManyJoinOther)
                  on (a ^. JoinOneId ==. b ^. JoinManyJoinOne)
                  pure (a, c)
  
              asserting $ listsEqualOn ac ca $ \(Entity _ a, Entity _ b) ->
                (joinOneName a, joinOtherName b)
  
          itDb "left joins on order" $ do
              setup
              ca <-
                  select $
                  from $ \(a `LeftOuterJoin` b `InnerJoin` c) -> do
                  on (c ?. JoinOtherId ==. b ?. JoinManyJoinOther)
                  on (just (a ^. JoinOneId) ==. b ?. JoinManyJoinOne)
                  orderBy [asc $ a ^. JoinOneId, asc $ c ?. JoinOtherId]
                  pure (a, c)
              ac <-
                  select $
                  from $ \(a `LeftOuterJoin` b `InnerJoin` c) -> do
                  on (just (a ^. JoinOneId) ==. b ?. JoinManyJoinOne)
                  on (c ?. JoinOtherId ==. b ?. JoinManyJoinOther)
                  orderBy [asc $ a ^. JoinOneId, asc $ c ?. JoinOtherId]
                  pure (a, c)
  
              asserting $ listsEqualOn ac ca $ \(Entity _ a, b) ->
                  (joinOneName a, maybe "NULL" (joinOtherName . entityVal) b)
  
          itDb "doesn't require an on for a crossjoin" $ do
              void $
                  select $
                  from $ \(a `CrossJoin` b) -> do
                  pure (a :: SqlExpr (Entity JoinOne), b :: SqlExpr (Entity JoinTwo))
              asserting noExceptions
  
          itDb "errors with an on for a crossjoin" $ do
              eres <-
                try $
                select $
                from $ \(a `CrossJoin` b) -> do
                on $ a ^. JoinOneId ==. b ^. JoinTwoJoinOne
                pure (a, b)
              asserting $
                  case eres of
                      Left (OnClauseWithoutMatchingJoinException _) ->
                          pure ()
                      Right _ ->
                          expectationFailure "Expected OnClause exception"
  
          itDb "left joins associativity" $ do
            setup
            ca <-
                select $
                from $ \(a `LeftOuterJoin` (b `InnerJoin` c)) -> do
                on (c ?. JoinOtherId ==. b ?. JoinManyJoinOther)
                on (just (a ^. JoinOneId) ==. b ?. JoinManyJoinOne)
                orderBy [asc $ a ^. JoinOneId, asc $ c ?. JoinOtherId]
                pure (a, c)
            ca' <-
                select $
                from $ \(a `LeftOuterJoin` b `InnerJoin` c) -> do
                on (c ?. JoinOtherId ==. b ?. JoinManyJoinOther)
                on (just (a ^. JoinOneId) ==. b ?. JoinManyJoinOne)
                orderBy [asc $ a ^. JoinOneId, asc $ c ?. JoinOtherId]
                pure (a, c)
  
            asserting $ listsEqualOn ca ca' $ \(Entity _ a, b) ->
              (joinOneName a, maybe "NULL" (joinOtherName . entityVal) b)
  
          itDb "composes queries still" $ do
              let
                  query1 =
                      from $ \(foo `InnerJoin` bar) -> do
                      on (foo ^. FooId ==. bar ^. BarQuux)
                      pure (foo, bar)
                  query2 =
                      from $ \(p `LeftOuterJoin` bp) -> do
                      on (p ^. PersonId ==. bp ^. BlogPostAuthorId)
                      pure (p, bp)
              fid <- insert $ Foo 5
              _ <- insert $ Bar fid
              pid <- insert $ Person "hey" Nothing Nothing 30
              _ <- insert $ BlogPost "WHY" pid
              a <- select ((,) <$> query1 <*> query2)
              b <- select (flip (,) <$> query1 <*> query2)
              asserting $ listsEqualOn a (map (\(x, y) -> (y, x)) b) id
  
          itDb "works with joins in subselect" $ do
              select $
                  from $ \(p `InnerJoin` r) -> do
                  on $ p ^. PersonId ==. r ^. ReplyGuy
                  pure . (,) (p ^. PersonName) $
                    subSelect $
                    from $ \(c `InnerJoin` bp) -> do
                    on $ bp ^. BlogPostId ==. c ^. CommentBlog
                    pure (c ^. CommentBody)
              asserting noExceptions
  
          describe "works with nested joins" $ do
            itDb "unnested" $ do
                selectRethrowingQuery $
                    from $ \(f `InnerJoin` b `LeftOuterJoin` baz `InnerJoin` shoop) -> do
                    on $ f ^. FooId ==. b ^. BarQuux
                    on $ f ^. FooId ==. baz ^. BazBlargh
                    on $ baz ^. BazId ==. shoop ^. ShoopBaz
                    pure ( f ^. FooName)
                asserting noExceptions
  
            itDb "leftmost nesting" $ do
                selectRethrowingQuery $
                    from $ \((f `InnerJoin` b) `LeftOuterJoin` baz `InnerJoin` shoop) -> do
                    on $ f ^. FooId ==. b ^. BarQuux
                    on $ f ^. FooId ==. baz ^. BazBlargh
                    on $ baz ^. BazId ==. shoop ^. ShoopBaz
                    pure ( f ^. FooName)
                asserting noExceptions
            describe "middle nesting" $ do
              itDb "direct association" $ do
                  selectRethrowingQuery $
                      from $ \(p `InnerJoin` (bp `LeftOuterJoin` c) `LeftOuterJoin` cr) -> do
                      on $ p ^. PersonId ==. bp ^. BlogPostAuthorId
                      on $ just (bp ^. BlogPostId) ==. c ?. CommentBlog
                      on $ c ?. CommentId ==. cr ?. CommentReplyComment
                      pure (p,bp,c,cr)
                  asserting noExceptions
              itDb "indirect association" $ do
                  selectRethrowingQuery $
                      from $ \(f `InnerJoin` b `LeftOuterJoin` (baz `InnerJoin` shoop) `InnerJoin` asdf) -> do
                      on $ f ^. FooId ==. b ^. BarQuux
                      on $ f ^. FooId ==. baz ^. BazBlargh
                      on $ baz ^. BazId ==. shoop ^. ShoopBaz
                      on $ asdf ^. AsdfShoop ==. shoop ^. ShoopId
                      pure (f ^. FooName)
                  asserting noExceptions
              itDb "indirect association across" $ do
                  selectRethrowingQuery $
                      from $ \(f `InnerJoin` b `LeftOuterJoin` (baz `InnerJoin` shoop) `InnerJoin` asdf `InnerJoin` another `InnerJoin` yetAnother) -> do
                      on $ f ^. FooId ==. b ^. BarQuux
                      on $ f ^. FooId ==. baz ^. BazBlargh
                      on $ baz ^. BazId ==. shoop ^. ShoopBaz
                      on $ asdf ^. AsdfShoop ==. shoop ^. ShoopId
                      on $ another ^. AnotherWhy ==. baz ^. BazId
                      on $ yetAnother ^. YetAnotherArgh ==. shoop ^. ShoopId
                      pure (f ^. FooName)
                  asserting noExceptions
  
            describe "rightmost nesting" $ do
              itDb "direct associations" $ do
                  selectRethrowingQuery $
                      from $ \(p `InnerJoin` bp `LeftOuterJoin` (c `LeftOuterJoin` cr)) -> do
                      on $ p ^. PersonId ==. bp ^. BlogPostAuthorId
                      on $ just (bp ^. BlogPostId) ==. c ?. CommentBlog
                      on $ c ?. CommentId ==. cr ?. CommentReplyComment
                      pure (p,bp,c,cr)
                  asserting noExceptions
  
              itDb "indirect association" $ do
                  selectRethrowingQuery $
                      from $ \(f `InnerJoin` b `LeftOuterJoin` (baz `InnerJoin` shoop)) -> do
                      on $ f ^. FooId ==. b ^. BarQuux
                      on $ f ^. FooId ==. baz ^. BazBlargh
                      on $ baz ^. BazId ==. shoop ^. ShoopBaz
                      pure (f ^. FooName)
                  asserting noExceptions
  
  testExperimentalFrom :: SpecDb
  testExperimentalFrom = do
    describe "Experimental From" $ do
      itDb "supports basic table queries" $ do
          p1e <- insert' p1
          _   <- insert' p2
          p3e <- insert' p3
          peopleWithAges <- select $ do
            people <- Experimental.from $ Table @Person
            where_ $ not_ $ isNothing $ people ^. PersonAge
            return people
          asserting $ peopleWithAges `shouldMatchList` [p1e, p3e]
  
      itDb "supports inner joins" $ do
          l1e <- insert' l1
          _   <- insert  l2
          d1e <- insert' $ Deed "1" (entityKey l1e)
          d2e <- insert' $ Deed "2" (entityKey l1e)
          lordDeeds <- select $ do
            (lords :& deeds) <-
              Experimental.from $ Table @Lord
                      `InnerJoin` Table @Deed
                `Experimental.on` (\(l :& d) -> l ^. LordId ==. d ^. DeedOwnerId)
            pure (lords, deeds)
          asserting $ lordDeeds `shouldMatchList` [ (l1e, d1e)
                                               , (l1e, d2e)
                                               ]
  
      itDb "supports outer joins" $ do
          l1e <- insert' l1
          l2e <- insert' l2
          d1e <- insert' $ Deed "1" (entityKey l1e)
          d2e <- insert' $ Deed "2" (entityKey l1e)
          lordDeeds <- select $ do
            (lords :& deeds) <-
              Experimental.from $ Table @Lord
                  `LeftOuterJoin` Table @Deed
                    `Experimental.on` (\(l :& d) -> just (l ^. LordId) ==. d ?. DeedOwnerId)
  
            pure (lords, deeds)
          asserting $ lordDeeds `shouldMatchList` [ (l1e, Just d1e)
                                               , (l1e, Just d2e)
                                               , (l2e, Nothing)
                                               ]
      itDb "supports delete" $ do
          insert_ l1
          insert_ l2
          insert_ l3
          delete $ void $ Experimental.from $ Table @Lord
          lords <- select $ Experimental.from $ Table @Lord
          asserting $ lords `shouldMatchList` []
  
      itDb "supports implicit cross joins" $ do
          l1e <- insert' l1
          l2e <- insert' l2
          ret <- select $ do
            lords1 <- Experimental.from $ Table @Lord
            lords2 <- Experimental.from $ Table @Lord
            pure (lords1, lords2)
          ret2 <- select $ do
            (lords1 :& lords2) <- Experimental.from $ Table @Lord `CrossJoin` Table @Lord
            pure (lords1,lords2)
          asserting $ ret `shouldMatchList` ret2
          asserting $ ret `shouldMatchList` [ (l1e, l1e)
                                         , (l1e, l2e)
                                         , (l2e, l1e)
                                         , (l2e, l2e)
                                         ]
  
      itDb "compiles" $ do
          let q = do
                (persons :& profiles :& posts) <-
                  Experimental.from $  Table @Person
                           `InnerJoin` Table @Profile
                     `Experimental.on` (\(people :& profiles) ->
                                          people ^. PersonId ==. profiles ^. ProfilePerson)
                       `LeftOuterJoin` Table @BlogPost
                     `Experimental.on` (\(people :& _ :& posts) ->
                                          just (people ^. PersonId) ==. posts ?. BlogPostAuthorId)
                pure (persons, posts, profiles)
          asserting noExceptions
  
      itDb "can call functions on aliased values" $ do
          insert_ p1
          insert_ p3
          -- Pretend this isnt all posts
          upperNames <- select $ do
            author <- Experimental.from $ Experimental.from $ Table @Person
            pure $ upper_ $ author ^. PersonName
  
          asserting $ upperNames `shouldMatchList` [ Value "JOHN"
                                                , Value "MIKE"
                                                ]
      itDb "allows re-using (:&) joined tables" $ do
        let q = do
                result@(persons :& profiles :& posts) <-
                  Experimental.from $  Table @Person
                           `InnerJoin` Table @Profile
                     `Experimental.on` (\(people :& profiles) ->
                                          people ^. PersonId ==. profiles ^. ProfilePerson)
                       `InnerJoin` Table @BlogPost
                     `Experimental.on` (\(people :& _ :& posts) ->
                                          people ^. PersonId ==. posts ^. BlogPostAuthorId)
                pure result
        rows <- select $ do
          (persons :& profiles :& posts) <- Experimental.from $ q
          pure (persons ^. PersonId, profiles ^. ProfileId, posts ^. BlogPostId)
        let result = rows :: [(Value PersonId, Value ProfileId, Value BlogPostId)]
        -- We don't care about the result of the query, only that it
        -- rendered & executed.
        asserting noExceptions
  
  listsEqualOn :: (HasCallStack, Show a1, Eq a1) => [a2] -> [a2] -> (a2 -> a1) -> Expectation
  listsEqualOn a b f = map f a `shouldBe` map f b
  
  tests :: SpecDb
  tests =
      describe "Esqueleto" $ do
          testSelect
          testGetTable
          testSubSelect
          testSelectOne
          testSelectSource
          testSelectFrom
          testSelectJoin
          testSelectSubQuery
          testSelectWhere
          testSelectOrderBy
          testSelectDistinct
          testCoasleceDefault
          testDelete
          testUpdate
          testListOfValues
          testListFields
          testInsertsBySelect
          testMathFunctions
          testCase
          testCountingRows
          testRenderSql
          testOnClauseOrder
          testExperimentalFrom
          testLocking
          testOverloadedRecordDot
          testDeriveEsqueletoRecord
          CTESpec.testCTE
  
  insert' :: ( Functor m
             , BaseBackend backend ~ PersistEntityBackend val
             , PersistStore backend
             , MonadIO m
  #if MIN_VERSION_persistent(2,14,0)
             , SafeToInsert val
  #endif
             , PersistEntity val )
          => val -> ReaderT backend m (Entity val)
  insert' v = flip Entity v <$> insert v
  
  
  -- With SQLite and in-memory databases, a separate connection implies a
  -- separate database. With 'actual databases', the data is persistent and
  -- thus must be cleaned after each test.
  -- TODO: there is certainly a better way...
  cleanDB
      :: forall m. _
      => SqlPersistT m ()
  cleanDB = do
    delete $ from $ \(_ :: SqlExpr (Entity Bar))  -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Foo))  -> return ()
  
    delete $ from $ \(_ :: SqlExpr (Entity Reply)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Comment)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Profile)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity BlogPost)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Follow)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Person)) -> return ()
  
    delete $ from $ \(_ :: SqlExpr (Entity Deed)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Lord)) -> return ()
  
    delete $ from $ \(_ :: SqlExpr (Entity CcList))  -> return ()
  
    delete $ from $ \(_ :: SqlExpr (Entity ArticleTag)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity ArticleMetadata)) -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Article))    -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Article2))   -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Tag))        -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Frontcover)) -> return ()
  
    delete $ from $ \(_ :: SqlExpr (Entity Circle))     -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity Point))      -> return ()
  
    delete $ from $ \(_ :: SqlExpr (Entity Numbers))    -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity JoinMany))    -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity JoinFour))    -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity JoinThree))    -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity JoinTwo))    -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity JoinOne))    -> return ()
    delete $ from $ \(_ :: SqlExpr (Entity JoinOther))    -> return ()
  
    delete $ from $ \(_ :: SqlExpr (Entity DateTruncTest)) -> pure ()
  
  
  cleanUniques
      :: forall m. MonadIO m
      => SqlPersistT m ()
  cleanUniques =
      delete $ from $ \(_ :: SqlExpr (Entity OneUnique))    -> return ()
  
  selectRethrowingQuery
    :: (MonadIO m, EI.SqlSelect a r, MonadUnliftIO m)
    => SqlQuery a
    -> SqlPersistT m [r]
  selectRethrowingQuery query =
    select query
      `catch` \(SomeException e) -> do
        (text, _) <- renderQuerySelect query
        liftIO . throwIO . userError $ Text.unpack text <> "\n\n" <> show e
  
  updateRethrowingQuery
      ::
      ( MonadUnliftIO  m
      , PersistEntity val
      , BackendCompatible SqlBackend (PersistEntityBackend val)
      )
      => (SqlExpr (Entity val) -> SqlQuery ())
      -> SqlWriteT m ()
  updateRethrowingQuery k =
      update k
          `catch` \(SomeException e) -> do
              (text, _) <- renderQueryUpdate (from k)
              liftIO . throwIO . userError $ Text.unpack text <> "\n\n" <> show e
  
  shouldBeOnClauseWithoutMatchingJoinException
      :: (HasCallStack, Show a)
      => Either SomeException a
      -> Expectation
  shouldBeOnClauseWithoutMatchingJoinException ea =
      case ea of
          Left (fromException -> Just OnClauseWithoutMatchingJoinException {}) ->
              pure ()
          _ ->
              expectationFailure $ "Expected OnClauseWithMatchingJoinException, got: " <> show ea
  
  testOverloadedRecordDot :: SpecDb
  testOverloadedRecordDot = describe "OverloadedRecordDot" $ do
  #if __GLASGOW_HASKELL__ >= 902
      describe "with SqlExpr (Entity rec)" $ do
          itDb "lets you project from a record" $ do
              select $ do
                  bp <- Experimental.from $ table @BlogPost
                  pure bp.title
      describe "with SqlExpr (Maybe (Entity rec))" $ do
          itDb "lets you project from a Maybe record" $ do
              void $ select $ do
                  p :& mbp <- Experimental.from $
                      table @Person
                      `leftJoin` table @BlogPost
                          `Experimental.on` do
                              \(p :& mbp) ->
                                  just p.id ==. mbp.authorId
                  pure (p.id, mbp.title)
  
      itDb "joins Maybe together" $ do
          void $ select $ do
              deed :& lord <-
                  Experimental.from $
                      table @Deed
                      `leftJoin` table @Lord
                          `Experimental.on` do
                              \(deed :& lord) ->
                                  lord.id ==. just deed.ownerId
              where_ $ lord.dogs >=. just (val 10)
              where_ $ joinV lord.dogs >=. just (just (val 10))
              where_ $ lord.dogs >=. just (val (Just 10))
  
      itDb "i didn't bork ?." $ do
          weights <- select $ do
              (pro :& per) <- Experimental.from $
                  table @Profile
                      `leftJoin` table @Person
                          `Experimental.on` do
                              \(pro :& per) ->
                                  just (pro ^. #person) ==. per ?. #id
                                  &&. just pro.person ==. per ?. PersonId
              pure $ per ?. #weight
          asserting $ do
              weights `shouldBe` ([] :: [Value (Maybe Int)])
  
  
  
  #else
      it "is only supported in GHC 9.2 or above" $ \_ -> do
          pending
  #endif
  
  testGetTable :: SpecDb
  testGetTable =
      describe "GetFirstTable" $ do
          itDb "works to make long join chains easier" $ do
              select $ do
                  (person :& blogPost :& profile :& reply) <-
                      Experimental.from $
                          table @Person
                          `leftJoin` table @BlogPost
                              `Experimental.on` do
                                  \(p :& bp) ->
                                      just (p ^. PersonId) ==. bp ?. BlogPostAuthorId
                          `leftJoin` table @Profile
                              `Experimental.on` do
                                  \((getTable @Person -> p) :& profile) ->
                                      just (p ^. PersonId) ==. profile ?. ProfilePerson
                          `leftJoin` table @Reply
                              `Experimental.on` do
                                  \((getTable @Person -> p) :& reply) ->
                                      just (p ^. PersonId) ==. reply ?. ReplyGuy
                  pure (person, blogPost, profile, reply)
              asserting noExceptions
  
  assertJust :: HasCallStack => Maybe a -> IO a
  assertJust = maybe (expectationFailure "Expected Just, got Nothing" >> error "asdf") pure
  