esqueleto 3.5.13.2 → 3.5.14.0

raw patch · 8 files changed

+290/−19 lines, 8 filesPVP: major bump suggested

API removals or changes: PVP suggests a major version bump

API changes (from Hackage documentation)

Files

• changelog.md +17/−0
• esqueleto.cabal +1/−1
• src/Database/Esqueleto/Experimental.hs +1/−0
• src/Database/Esqueleto/Experimental/From/CommonTableExpression.hs +5/−3
• src/Database/Esqueleto/Internal/Internal.hs +20/−11
• src/Database/Esqueleto/PostgreSQL.hs +116/−1
• test/MySQL/Test.hs +5/−2
• test/PostgreSQL/Test.hs +125/−1

@@ -1,3 +1,20 @@+3.5.14.0+========+- @parsonsmatt+    - [#415](https://github.com/bitemyapp/esqueleto/pull/415)+        - Export the `SqlSelect` type from `Database.Esqueleto.Experimental`+    - [#414](https://github.com/bitemyapp/esqueleto/pull/414)+        - Derive `Foldable` and `Traversable` for `Value`.+    - [#416](https://github.com/bitemyapp/esqueleto/pull/416)+        - Derive `Functor` and `Bifunctor` for `:&`+- @matthewbauer+    - [#341](https://github.com/bitemyapp/esqueleto/pull/341/)+        - Add functions for `NULLS FIRST` and `NULLS LAST` in the Postgresql+          module+- @JoelMcCracken+    - [#354](https://github.com/bitemyapp/esqueleto/pull/354), [#417](https://github.com/bitemyapp/esqueleto/pull/417)+      - Add `withMaterialized`, `withNotMaterialized` to the PostgreSQL module+ 3.5.13.2 ======== - @blujupiter32

@@ -2,7 +2,7 @@  name:           esqueleto -version:        3.5.13.2+version:        3.5.14.0 synopsis:       Type-safe EDSL for SQL queries on persistent backends. description:    @esqueleto@ is a bare bones, type-safe EDSL for SQL queries that works with unmodified @persistent@ SQL backends.  Its language closely resembles SQL, so you don't have to learn new concepts, just new syntax, and it's fairly easy to predict the generated SQL and optimize it for your backend. Most kinds of errors committed when writing SQL are caught as compile-time errors---although it is possible to write type-checked @esqueleto@ queries that fail at runtime.                 .

@@ -61,6 +61,7 @@     , ToAliasReference(..)     , ToAliasReferenceT     , ToSqlSetOperation(..)+    , SqlSelect      -- * The Normal Stuff     , where_

@@ -38,7 +38,8 @@ -- PostgreSQL 12, non-recursive and side-effect-free queries may be inlined and -- optimized accordingly if not declared @MATERIALIZED@ to get the previous -- behaviour. See [the PostgreSQL CTE documentation](https://www.postgresql.org/docs/current/queries-with.html#id-1.5.6.12.7),--- section Materialization, for more information.+-- section Materialization, for more information. To use a @MATERIALIZED@ query+-- in Esquelto, see functions 'withMaterialized' and 'withRecursiveMaterialized'. -- -- /Since: 3.4.0.0/ with :: ( ToAlias a@@ -50,7 +51,7 @@     aliasedValue <- toAlias ret     let aliasedQuery = Q $ W.WriterT $ pure (aliasedValue, sideData)     ident <- newIdentFor (DBName "cte")-    let clause = CommonTableExpressionClause NormalCommonTableExpression ident (\info -> toRawSql SELECT info aliasedQuery)+    let clause = CommonTableExpressionClause NormalCommonTableExpression (\_ _ -> "")  ident (\info -> toRawSql SELECT info aliasedQuery)     Q $ W.tell mempty{sdCteClause = [clause]}     ref <- toAliasReference ident aliasedValue     pure $ From $ do@@ -107,7 +108,8 @@     ref <- toAliasReference ident aliasedValue     let refFrom = From (pure (ref, (\_ info -> (useIdent info ident, mempty))))     let recursiveQuery = recursiveCase refFrom-    let clause = CommonTableExpressionClause RecursiveCommonTableExpression ident+    let noModifier _ _ = ""+    let clause = CommonTableExpressionClause RecursiveCommonTableExpression noModifier ident                  (\info -> (toRawSql SELECT info aliasedQuery)                         <> ("\n" <> (unUnionKind unionKind)  <> "\n", mempty)                         <> (toRawSql SELECT info recursiveQuery)

@@ -1,4 +1,5 @@ {-# LANGUAGE CPP #-}+{-# LANGUAGE DeriveFunctor #-} {-# LANGUAGE DataKinds #-} {-# LANGUAGE TypeOperators #-} {-# LANGUAGE TypeApplications #-}@@ -6,6 +7,7 @@  {-# LANGUAGE ConstraintKinds #-} {-# LANGUAGE DeriveDataTypeable #-}+{-# LANGUAGE DeriveTraversable #-} {-# LANGUAGE DerivingStrategies #-} {-# LANGUAGE EmptyDataDecls #-} {-# LANGUAGE FlexibleContexts #-}@@ -53,6 +55,7 @@ import qualified Control.Monad.Trans.State as S import qualified Control.Monad.Trans.Writer as W import qualified Data.ByteString as B+import Data.Bifunctor (Bifunctor, bimap) import Data.Coerce (coerce) import qualified Data.Conduit as C import qualified Data.Conduit.List as CL@@ -1270,11 +1273,8 @@  -- | A single value (as opposed to a whole entity).  You may use -- @('^.')@ or @('?.')@ to get a 'Value' from an 'Entity'.-newtype Value a = Value { unValue :: a } deriving (Eq, Ord, Show, Typeable)---- | @since 1.4.4-instance Functor Value where-    fmap f (Value a) = Value (f a)+newtype Value a = Value { unValue :: a }+    deriving (Eq, Ord, Show, Typeable, Functor, Foldable, Traversable)  instance Applicative Value where   (<*>) (Value f) (Value a) = Value (f a)@@ -1553,9 +1553,15 @@ -- See the examples at the beginning of this module to see how this -- operator is used in 'JOIN' operations. data (:&) a b = a :& b-    deriving (Eq, Show)+    deriving (Eq, Show, Functor) infixl 2 :& +-- |+--+-- @since 3.5.14.0+instance Bifunctor (:&) where+    bimap f g (a :& b) = f a :& g b+ -- | Different kinds of locking clauses supported by 'locking'. -- -- Note that each RDBMS has different locking support.  The@@ -1992,9 +1998,11 @@     | NormalCommonTableExpression     deriving Eq -data CommonTableExpressionClause =-    CommonTableExpressionClause CommonTableExpressionKind Ident (IdentInfo -> (TLB.Builder, [PersistValue]))+type CommonTableExpressionModifierAfterAs = CommonTableExpressionClause -> IdentInfo -> TLB.Builder +data CommonTableExpressionClause+  = CommonTableExpressionClause CommonTableExpressionKind CommonTableExpressionModifierAfterAs Ident (IdentInfo -> (TLB.Builder, [PersistValue]))+ data SubQueryType     = NormalSubQuery     | LateralSubQuery@@ -3206,14 +3214,15 @@         | hasRecursive = "WITH RECURSIVE "         | otherwise = "WITH "       where+         hasRecursive =             elem RecursiveCommonTableExpression-            $ fmap (\(CommonTableExpressionClause cteKind _ _) -> cteKind)+            $ fmap (\(CommonTableExpressionClause cteKind _ _ _) -> cteKind)             $ cteClauses -    cteClauseToText (CommonTableExpressionClause _ cteIdent cteFn) =+    cteClauseToText clause@(CommonTableExpressionClause _ cteModifier cteIdent cteFn) =         first-            (\tlb -> useIdent info cteIdent <> " AS " <> parens tlb)+            (\tlb -> useIdent info cteIdent <> " AS " <> cteModifier clause info <> parens tlb)             (cteFn info)      cteBody =

@@ -36,6 +36,12 @@     , forKeyShareOf     , filterWhere     , values+    , withMaterialized+    , withNotMaterialized+    , ascNullsFirst+    , ascNullsLast+    , descNullsFirst+    , descNullsLast     -- * Internal     , unsafeSqlAggregateFunction     ) where@@ -48,15 +54,22 @@ import Control.Monad (void) import Control.Monad.IO.Class (MonadIO(..)) import qualified Control.Monad.Trans.Reader as R+import qualified Control.Monad.Trans.Writer as W import Data.Int (Int64) import qualified Data.List.NonEmpty as NE import Data.Maybe import Data.Proxy (Proxy(..)) import qualified Data.Text.Internal.Builder as TLB import qualified Data.Text.Lazy as TL+import qualified Data.Text.Lazy.Builder as TLB import Data.Time.Clock (UTCTime) import qualified Database.Esqueleto.Experimental as Ex-import Database.Esqueleto.Internal.Internal hiding (random_)+import qualified Database.Esqueleto.Experimental.From as Ex+import Database.Esqueleto.Experimental.From.CommonTableExpression+import Database.Esqueleto.Experimental.From.SqlSetOperation+import Database.Esqueleto.Experimental.ToAlias+import Database.Esqueleto.Experimental.ToAliasReference+import Database.Esqueleto.Internal.Internal hiding (From(..), from, on, random_) import Database.Esqueleto.Internal.PersistentImport hiding        (uniqueFields, upsert, upsertBy) import Database.Persist.SqlBackend@@ -494,3 +507,105 @@ forKeyShareOf :: LockableEntity a => a -> OnLockedBehavior -> SqlQuery () forKeyShareOf lockableEntities onLockedBehavior =   putLocking $ PostgresLockingClauses [PostgresLockingKind PostgresForKeyShare (Just $ LockingOfClause lockableEntities) onLockedBehavior]++-- | @WITH@ @MATERIALIZED@ clause is used to introduce a+-- [Common Table Expression (CTE)](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression)+-- with the MATERIALIZED keyword. The MATERIALIZED keyword is only supported in PostgreSQL >= version 12.+-- In Esqueleto, CTEs should be used as a subquery memoization tactic. PostgreSQL treats a materialized CTE as an optimization fence.+-- A materialized CTE is always fully calculated, and is not "inlined" with other table joins.+-- Without the MATERIALIZED keyword, PostgreSQL >= 12 may "inline" the CTE as though it was any other join.+-- You should always verify that using a materialized CTE will in fact improve your performance+-- over a regular subquery.+--+-- @+-- select $ do+-- cte <- withMaterialized subQuery+-- cteResult <- from cte+-- where_ $ cteResult ...+-- pure cteResult+-- @+--+--+-- For more information on materialized CTEs, see the PostgreSQL manual documentation on+-- [Common Table Expression Materialization](https://www.postgresql.org/docs/14/queries-with.html#id-1.5.6.12.7).+--+-- @since 3.5.14.0+withMaterialized :: ( ToAlias a+                    , ToAliasReference a+                    , SqlSelect a r+                    ) => SqlQuery a -> SqlQuery (Ex.From a)+withMaterialized query = do+    (ret, sideData) <- Q $ W.censor (\_ -> mempty) $ W.listen $ unQ query+    aliasedValue <- toAlias ret+    let aliasedQuery = Q $ W.WriterT $ pure (aliasedValue, sideData)+    ident <- newIdentFor (DBName "cte")+    let clause = CommonTableExpressionClause NormalCommonTableExpression (\_ _ -> "MATERIALIZED ") ident (\info -> toRawSql SELECT info aliasedQuery)+    Q $ W.tell mempty{sdCteClause = [clause]}+    ref <- toAliasReference ident aliasedValue+    pure $ Ex.From $ pure (ref, (\_ info -> (useIdent info ident, mempty)))++-- | @WITH@ @NOT@ @MATERIALIZED@ clause is used to introduce a+-- [Common Table Expression (CTE)](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL#Common_table_expression)+-- with the NOT MATERIALIZED keywords. These are only supported in PostgreSQL >=+-- version 12. In Esqueleto, CTEs should be used as a subquery memoization+-- tactic. PostgreSQL treats a materialized CTE as an optimization fence. A+-- MATERIALIZED CTE is always fully calculated, and is not "inlined" with other+-- table joins. Sometimes, this is undesirable, so postgres provides the NOT+-- MATERIALIZED modifier to prevent this behavior, thus enabling it to possibly+-- decide to treat the CTE as any other join.+--+-- Given the above, it is unlikely that this function will be useful, as a+-- normal join should be used instead, but is provided for completeness.+--+-- @+-- select $ do+-- cte <- withNotMaterialized subQuery+-- cteResult <- from cte+-- where_ $ cteResult ...+-- pure cteResult+-- @+--+--+-- For more information on materialized CTEs, see the PostgreSQL manual documentation on+-- [Common Table Expression Materialization](https://www.postgresql.org/docs/14/queries-with.html#id-1.5.6.12.7).+--+-- @since 3.5.14.0+withNotMaterialized :: ( ToAlias a+                    , ToAliasReference a+                    , SqlSelect a r+                    ) => SqlQuery a -> SqlQuery (Ex.From a)+withNotMaterialized query = do+    (ret, sideData) <- Q $ W.censor (\_ -> mempty) $ W.listen $ unQ query+    aliasedValue <- toAlias ret+    let aliasedQuery = Q $ W.WriterT $ pure (aliasedValue, sideData)+    ident <- newIdentFor (DBName "cte")+    let clause = CommonTableExpressionClause NormalCommonTableExpression (\_ _ -> "NOT MATERIALIZED ") ident (\info -> toRawSql SELECT info aliasedQuery)+    Q $ W.tell mempty{sdCteClause = [clause]}+    ref <- toAliasReference ident aliasedValue+    pure $ Ex.From $ pure (ref, (\_ info -> (useIdent info ident, mempty)))++-- | Ascending order of this field or SqlExpression with nulls coming first.+--+-- @since 3.5.14.0+ascNullsFirst :: PersistField a => SqlExpr (Value a) -> SqlExpr OrderBy+ascNullsFirst = orderByExpr " ASC NULLS FIRST"++-- | Ascending order of this field or SqlExpression with nulls coming last.+-- Note that this is the same as normal ascending ordering in Postgres, but it has been included for completeness.+--+-- @since 3.5.14.0+ascNullsLast :: PersistField a => SqlExpr (Value a) -> SqlExpr OrderBy+ascNullsLast = orderByExpr " ASC NULLS LAST"++-- | Descending order of this field or SqlExpression with nulls coming first.+-- Note that this is the same as normal ascending ordering in Postgres, but it has been included for completeness.+--+-- @since 3.5.14.0+descNullsFirst :: PersistField a => SqlExpr (Value a) -> SqlExpr OrderBy+descNullsFirst = orderByExpr " DESC NULLS FIRST"++-- | Descending order of this field or SqlExpression with nulls coming last.+--+-- @since 3.5.14.0+descNullsLast :: PersistField a => SqlExpr (Value a) -> SqlExpr OrderBy+descNullsLast = orderByExpr " DESC NULLS LAST"

@@ -23,14 +23,16 @@        , connectPassword        , connectPort        , connectUser+       , createMySQLPool        , defaultConnectInfo        , withMySQLConn-       , createMySQLPool        )  import Test.Hspec  import Common.Test+import Data.Maybe (fromMaybe)+import System.Environment (lookupEnv)  testMysqlSum :: SpecDb testMysqlSum = do@@ -189,6 +191,7 @@ mkConnectionPool :: IO ConnectionPool mkConnectionPool = do     ci <- isCI+    mysqlHost <- (fromMaybe "localhost" <$> lookupEnv "MYSQL_HOST")     let connInfo             | ci =                 defaultConnectInfo@@ -200,7 +203,7 @@                     }             | otherwise =                 defaultConnectInfo-                    { connectHost     = "localhost"+                    { connectHost     = mysqlHost                     , connectUser     = "travis"                     , connectPassword = "esqutest"                     , connectDatabase = "esqutest"

@@ -40,7 +40,8 @@ import Database.Esqueleto.Experimental hiding (from, on, random_) import qualified Database.Esqueleto.Experimental as Experimental import qualified Database.Esqueleto.Internal.Internal as ES-import Database.Esqueleto.PostgreSQL (random_)+import Database.Esqueleto.PostgreSQL+       (random_, withMaterialized, withNotMaterialized) import qualified Database.Esqueleto.PostgreSQL as EP import Database.Esqueleto.PostgreSQL.JSON hiding ((-.), (?.), (||.)) import qualified Database.Esqueleto.PostgreSQL.JSON as JSON@@ -1232,6 +1233,80 @@             pure res         asserting $ vals `shouldBe` fmap Value [2..11] +    describe "MATERIALIZED CTEs" $ do+      describe "withNotMaterialized" $ do+        itDb "successfully executes query" $ do+          void $ select $ do+            limitedLordsCte <-+                withNotMaterialized $ do+                    lords <- Experimental.from $ Experimental.table @Lord+                    limit 10+                    pure lords+            lords <- Experimental.from limitedLordsCte+            orderBy [asc $ lords ^. LordId]+            pure lords++          asserting noExceptions++        itDb "generates the expected SQL" $ do+          (sql, _) <- showQuery ES.SELECT $ do+                  limitedLordsCte <-+                      withNotMaterialized $ do+                          lords <- Experimental.from $ Experimental.table @Lord+                          limit 10+                          pure lords+                  lords <- Experimental.from limitedLordsCte+                  orderBy [asc $ lords ^. LordId]+                  pure lords++          asserting $ sql `shouldBe` T.unlines+            [ "WITH \"cte\" AS NOT MATERIALIZED (SELECT \"Lord\".\"county\" AS \"v_county\", \"Lord\".\"dogs\" AS \"v_dogs\""+            , "FROM \"Lord\""+            , " LIMIT 10"+            , ")"+            , "SELECT \"cte\".\"v_county\", \"cte\".\"v_dogs\""+            , "FROM \"cte\""+            , "ORDER BY \"cte\".\"v_county\" ASC"+            ]+          asserting noExceptions+++      describe "withMaterialized" $ do+        itDb "generates the expected SQL" $ do+          (sql, _) <- showQuery ES.SELECT $ do+                  limitedLordsCte <-+                      withMaterialized $ do+                          lords <- Experimental.from $ Experimental.table @Lord+                          limit 10+                          pure lords+                  lords <- Experimental.from limitedLordsCte+                  orderBy [asc $ lords ^. LordId]+                  pure lords++          asserting $ sql `shouldBe` T.unlines+            [ "WITH \"cte\" AS MATERIALIZED (SELECT \"Lord\".\"county\" AS \"v_county\", \"Lord\".\"dogs\" AS \"v_dogs\""+            , "FROM \"Lord\""+            , " LIMIT 10"+            , ")"+            , "SELECT \"cte\".\"v_county\", \"cte\".\"v_dogs\""+            , "FROM \"cte\""+            , "ORDER BY \"cte\".\"v_county\" ASC"+            ]+          asserting noExceptions++        itDb "successfully executes query" $ do+            void $ select $ do+                  limitedLordsCte <-+                      withMaterialized $ do+                          lords <- Experimental.from $ Experimental.table @Lord+                          limit 10+                          pure lords+                  lords <- Experimental.from limitedLordsCte+                  orderBy [asc $ lords ^. LordId]+                  pure lords++            asserting noExceptions+ testPostgresqlLocking :: SpecDb testPostgresqlLocking = do     describe "Monoid instance" $ do@@ -1546,7 +1621,55 @@                     pure (str, val @Int 1)             asserting noExceptions +testPostgresqlNullsOrdering :: SpecDb+testPostgresqlNullsOrdering = do+  describe "Postgresql NULLS orderings work" $ do+      itDb "ASC NULLS FIRST works" $ do+        p1e <- insert' p1+        p2e <- insert' p2 -- p2 has a null age+        p3e <- insert' p3+        p4e <- insert' p4+        ret <- select $+               from $ \p -> do+               orderBy [EP.ascNullsFirst (p ^. PersonAge), EP.ascNullsFirst (p ^. PersonFavNum)]+               return p+        -- nulls come first+        asserting $ ret `shouldBe` [ p2e, p3e, p4e, p1e ]+      itDb "ASC NULLS LAST works" $ do+        p1e <- insert' p1+        p2e <- insert' p2 -- p2 has a null age+        p3e <- insert' p3+        p4e <- insert' p4+        ret <- select $+               from $ \p -> do+               orderBy [EP.ascNullsLast (p ^. PersonAge), EP.ascNullsLast (p ^. PersonFavNum)]+               return p+        -- nulls come last+        asserting $ ret `shouldBe` [ p3e, p4e, p1e, p2e ]+      itDb "DESC NULLS FIRST works" $ do+        p1e <- insert' p1+        p2e <- insert' p2 -- p2 has a null age+        p3e <- insert' p3+        p4e <- insert' p4+        ret <- select $+               from $ \p -> do+               orderBy [EP.descNullsFirst (p ^. PersonAge), EP.descNullsFirst (p ^. PersonFavNum)]+               return p+        -- nulls come first+        asserting $ ret `shouldBe` [ p2e, p1e, p4e, p3e ]+      itDb "DESC NULLS LAST works" $ do+        p1e <- insert' p1+        p2e <- insert' p2 -- p2 has a null age+        p3e <- insert' p3+        p4e <- insert' p4+        ret <- select $+               from $ \p -> do+               orderBy [EP.descNullsLast (p ^. PersonAge), EP.descNullsLast (p ^. PersonFavNum)]+               return p+        -- nulls come last+        asserting $ ret `shouldBe` [ p1e, p4e, p3e, p2e ] + type JSONValue = Maybe (JSONB A.Value)  createSaneSQL :: (PersistField a, MonadIO m) => SqlExpr (Value a) -> T.Text -> [PersistValue] -> SqlPersistT m ()@@ -1642,6 +1765,7 @@         testValuesExpression         testSubselectAliasingBehavior         testPostgresqlLocking+        testPostgresqlNullsOrdering  insertJsonValues :: SqlPersistT IO () insertJsonValues = do