packages feed

pg-schema-0.9.0.0: src/PgSchema/DML.hs

-- |
-- Module: PgSchema.DML
-- Copyright: (c) Dmitry Olshansky
-- License: BSD-3-Clause
-- Maintainer: olshanskydr@gmail.com, dima@typeable.io
-- Stability: experimental
--
-- = A module to generate and safely execute SQL statements.
--
-- Among @pg-schema@’s library modules, this is the one meant for ordinary application
-- imports for database access (alongside your generated schema module).
--
-- == Example
--
-- A schema module @MySch@ is generated (see "PgSchema.Generation") for tables
-- @shop.ord@, @shop.ord_pos@ and @shop.article@, where @ord_pos@ references
-- @ord@ and @article@. The foreign keys have the names that PostgreSQL gives
-- them by default: @ord_pos_ord_id_fkey@ and @ord_pos_article_id_fkey@.
--
-- > type ShopAnn tabName = 'Ann RenamerId MySch 5 ("shop" ->> tabName)
-- >
-- > -- An order with its positions to insert
-- > type OrdIn = "num" := Text :. "ord_pos_ord_id_fkey" := [OrdPosIn]
-- > type OrdPosIn = "num" := Int32 :. "article_id" := Int32 :. "price" := Double
-- >
-- > -- Orders to select: with positions and their articles
-- > data Order = Order { num :: Text, created_at :: Day, ord_pos_ord_id_fkey :: [OrdPos] } deriving Generic
-- > data OrdPos = OrdPos { num :: Int32, ord_pos_article_id_fkey :: Article, price :: Double } deriving Generic
-- > data Article = Article { name :: Text } deriving Generic
-- >
-- > example :: Connection -> Day -> IO [Order]
-- > example conn someDay = do
-- >   void $ insertJSON_ (ShopAnn "ord") conn $ rowsOf @OrdIn
-- >     [ "num1" :. [ 1 :. 42 :. 10, 2 :. 41 :. 15 ] ]
-- >   fst <$> selectSch (ShopAnn "ord") conn (qRoot do
-- >     qWhere $ "created_at" >=? someDay
-- >     qOrderBy [descf "created_at", descf "num"]
-- >     qPath "ord_pos_ord_id_fkey" do
-- >       qWhere $ "price" >? (12 :: Double)
-- >       qOrderBy [ascf "num"])
--
-- Here we insert an order with two positions and then get the orders created on
-- or after @someDay@, each with its positions priced above 12 and their articles.
--
-- Field names are the database names here, since 'RenamerId' does not rename
-- anything. A field of a relation is named after its foreign key.
--
-- Rows can be given as records ('GHC.Generics.Generic'), as rows of fields
-- built with '(=:)' and chained with t'(:.)' (e.g. @"num" =: "num1" :. …@), or,
-- as here, as plain values: 'rowsOf' takes field names and types from the row
-- type, so the values need neither names nor type annotations. See also 'named'
-- and 'plains'.
--
-- Conditions on related tables are made with 'pchild' and 'pparent'.
--
-- The 'insertJSON_' call in the example inserts one root row (here an order) and nested
-- /child/ rows (here order positions) in a single database operation, using
-- JSON internally. Child data is carried in list fields: the field’s name (after the
-- 'Renamer') names the FK constraint in the database and thus selects the child table
-- and link; each list element supplies one child row’s columns, with nested lists for
-- further children in the same way.
--
-- * 'insertJSON' / 'insertJSON_' require every mandatory column and emit plain
--   @INSERT@ only (duplicates fail in the database).
-- * 'upsertJSON' / 'upsertJSON_' relax mandatory requirements and may @UPDATE@ or
--   @INSERT … ON CONFLICT …@ (see 'upsertJSON').
-- * 'updateJSON' / 'updateJSON_' update existing rows only (never @INSERT@).
--
-- Flat (single-table) 'insertSch', 'upsertByKey' and 'updateByKey' may return any
-- plain columns of the table; the returning row itself must not be wrapped in
-- 'Maybe'. Tree @*JSON@ operations check the returning type with
-- 'ReturningMatchesInsert', 'ReturningMatchesUpsert' and 'ReturningMatchesUpdate'
-- instead. 'upsertByKey' requires all mandatory columns and a full key;
-- 'updateByKey' requires only a full key and returns @IO ([Maybe r'], Text)@.
--
-- 'selectSch' decodes each row into a Haskell type @r@ whose fields describe the
-- root table columns and nested data for relations (multiple list-shaped children
-- and nested parents/'Maybe' as appropriate). The type drives the generated SQL
-- shape; monadic t'QueryParam' only adds what this call needs: filters, ordering on a
-- branch, limits, and so on.
--
-- All string literals at the type level are 'GHC.TypeLits.Symbol' thanks to @RequiredTypeArguments@.
-- Operations are safe when the live database schema matches the generated schema types.
--
-- The @INSERT@ and @SELECT@ shown here each run as a single, fast database round-trip (JSON inside PostgreSQL).
--
-- A 'Renamer' maps Haskell field names to database names. 'RenamerId' keeps
-- them as they are; to use e.g. @createdAt@ for @created_at@ or @items@ for
-- @ord_pos_ord_id_fkey@, declare your own renamer (see 'ApplyRenamer'). 'CamelToSnake'
-- converts @camelCase@ to @snake_case@ and is a convenient default case for it.
-- The renamer is applied to all names: record fields, column names in conditions
-- and ordering, relation names in 'qPath', 'pchild' and 'pparent'. So with a
-- renamer based on 'CamelToSnake' you can write either @"ownerId"@ or @"owner_id"@.
--
module PgSchema.DML
  (
  -- * Select
    selectSch, selectText
  -- ** Query Params
  , QueryParamAnn, QueryParam(..), qpEmpty
  , qRoot, qPath, qPathFromHere, qPathToHere, qWhere, qOrderBy
  , qDistinct, qDistinctOn, qLimit, qOffset
  -- ** Conditions
  -- | Example: @"name" =? "John"@
  , (<?),(>?),(<=?),(>=?),(=?)
  -- | Null-safe equality on nullable columns (@IS NOT DISTINCT FROM@).
  -- Example: @"suffix" =?? (Just "a")@
  , (=??)
  -- | @LIKE@ ('~=?') and @ILIKE@ ('~~?'). Example: @"name" ~~? "%joh%"@
  ,(~=?),(~~?)
  , (|||), (&&&), pnot, pnull, pin, pinArr
  , pparent, pchild
  , pUnsafeCond
  -- ** Order By and Limit/Offset
  , ordf, ascf, descf, defLO, lo1
  -- ** Internals
  -- | Types and functions that appear in the signatures above, and the building
  -- blocks for unsafe conditions and ordering ('pUnsafeCond', 'UnsafeOrd').
  , Selectable, CRecInfo(..)
  , MonadQP, CondWithPath(..), OrdWithPath(..)
  , Cond(..), CondAnn, Cmp(..), BoolOp(..), TabParam(..), defTabParam
  , CondMonad, SomeToField(..), showCmp, tabPref, qual
  , CDBField, CDBValue, CDBFieldNullable, CRelDef
  , LimOffWithPath(..), DistWithPath(..)
  , OrdDirection(..), OrdFld(..), Dist(..), LO(..)
  -- * Plain DML
  -- | You can insert, update or upsert data.
  --
  -- - For insert you need all mandatory fields.
  -- - For key-based update you need a full primary or unique key.
  -- - For upsert you need all mandatory fields /and/ some key.
  --
  -- You can get in return any subset of the columns of the table which was affected by the operation.
  -- For key-based update each returned row is @Maybe r'@: it is 'Nothing' if the row was not found by the key.
  --
  -- All these constraints are checked at compile time.
  --
  -- There are also operations to update or delete data by a condition.
  --
  -- Input rows can be records, rows of fields built with '(=:)', or plain values
  -- made into rows with 'rowsOf' or 'named'; 'plains' gives plain values of the
  -- returned rows.
  --
  , insertSch, insertSch_, insertText, insertText_
  , upsertByKey, upsertByKey_, upsertByKeyText, upsertByKeyText_
  , updateByKey, updateByKey_, updateByKeyText, updateByKeyText_
  , updateByCond, updateByCond_, updateText, updateText_
  , deleteByCond, deleteText
  -- ** Constraints
  , AllPlain, InsertNonReturning, InsertReturning
  , UpsertByKeyNonReturning, UpsertByKeyReturning
  , UpdateByKeyNonReturning, UpdateByKeyReturning
  , UpdateReturning, UpdateNonReturning
  -- * Tree-based DML
  -- | You can insert, update or upsert data right into the tree structure
  -- (to the several related tables at once).
  --
  -- - For insert you need all mandatory fields at every node.
  -- - For update you need a full primary or unique key at every node.
  -- - For upsert you need all mandatory fields /or/ some key at every node.
  --
  --     At each node:
  --
  --     - the operation works like an insert if there is no key;
  --     - the operation works like an update if not all mandatory fields are present;
  --     - the operation works like an upsert if all mandatory fields and a key are present.
  --
  -- You don't need foreign keys for children in your data (and often even keys for parents).
  -- They will be determined automatically.
  --
  -- You can get in return any subset of the columns of the tables which were affected by the operation.
  -- All rows are returned in the same order as in the input tree.
  -- For update or some nodes of upsert (where not all mandatory fields are present),
  -- the returning type is @Maybe r'@. It is 'Nothing' if the row was not found by the key.
  --
  -- All these constraints are checked at compile time.
  --
  -- For all these operations you should use types that can be converted to JSON (and from JSON for returning).
  -- You don't need instances but all fields should be serializable into JSON.
  -- E.g. you can't use 'Data.ByteString.ByteString' fields here.
  --
  -- Input trees are easy to write as plain values with 'rowsOf' (see the
  -- example at the top of this module).
  , insertJSON, insertJSON_, upsertJSON, upsertJSON_
  , updateJSON, updateJSON_, insertJSONText, insertJSONText_
  , upsertJSONText, upsertJSONText_, updateJSONText, updateJSONText_
  -- ** Constraints
  , TreeIn, TreeOut, AllMandatoryTree, AllMandatoryOrHasKeyTree, AllHasKeyTree
  , HasSchema
  , InsertTreeNonReturning, InsertTreeReturning
  , UpsertTreeNonReturning, UpsertTreeReturning
  , UpdateTreeNonReturning, UpdateTreeReturning, TRecordInfo
  , ReturningMatchesInsert, ReturningMatchesUpsert, ReturningMatchesUpdate
  -- * Types
  , Ann(..), ToStar
  -- ** Renamers
  , RenamerId, CamelToSnake, Renamer, ApplyRenamer, ApplyRenamerNS
  -- ** PgTag types
  , type (:=), (=:), PgTag(..), named, Named,
  -- ** Plain values of rows
  rowsOf, rowOf, plains, plain, Plain, PlainRow,
  -- | Re-export from postgresql-simple
  (:.)(..)
  -- ** Enum
  , PGEnum
  -- ** Aggregates
  , Aggr(..), Aggr'(..), AggrFun(..)
  -- ** Unsafe
  , UnsafeCol(..)
  -- ** Arrays
  , PgArr(..), pgArr', unPgArr'
  -- ** Conversion checks
  , CanConvert, CanConvert1, CheckAggr, TypDef'(..)
  -- ** Other types
  , PgChar(..)
  , NameNS'(..), type (->>), NameNSK, (->>)
  , PathElem'(..), PathElem, PathElemK, PathKind(..)
  , TRelDef, RelDef'(..), FldDef', CTabDef(..)
  -- | Type-level selectors (promoted record fields): 'RdFrom' and 'RdTo' give
  -- the child and the parent table of a t'RelDef'', 'FdType' and 'FdNullable'
  -- give the type and the nullability of a t'FldDef''.
  , RdFrom, RdTo, FdType, FdNullable
  ) where


import PgSchema.Ann
import PgSchema.Plain
import PgSchema.DML.Select as S
import PgSchema.DML.Select.Types as S
import PgSchema.DML.Insert as I
import PgSchema.DML.Insert.Types as I
import PgSchema.DML.InsertJSON as I
import PgSchema.DML.UpsertByKey as UBK
import PgSchema.DML.Update as U
import PgSchema.DML.Delete as D
import PgSchema.Schema as S
import PgSchema.Types as T
import PgSchema.Utils.CamelToSnake
import PgSchema.Utils.Internal as T (ToStar)