packages feed

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

{-# LANGUAGE OverloadedRecordDot #-}
module PgSchema.DML.UpsertByKey
  ( upsertByKey, upsertByKey_, upsertByKeyText, upsertByKeyText_ ) where

import Data.Maybe
import Data.String
import Data.Text as T
import Database.PostgreSQL.Simple
import GHC.Int
import PgSchema.Ann
import PgSchema.DML.Insert.Types
import PgSchema.DML.KeyedWrite
import Data.Map qualified as M
import PgSchema.Schema (qualName, tabInfoMap)
import PgSchema.Types
import PgSchema.Utils.Internal
import Prelude as P


-- | Insert rows into one table or update them if a row with the same key exists
-- (@INSERT … ON CONFLICT (key) DO UPDATE@).
--
-- @r@ must contain all mandatory columns and a full primary or unique key
-- ('UpsertByKeyReturning'). The key is the first fully covered one of: the
-- primary key, unique keys over @NOT NULL@ columns, other unique keys.
-- On conflict all other fields of @r@ are updated.
--
-- If all fields of @r@ belong to the key, there is nothing to update: an
-- existing row is left as is, but it is returned too (the @ON CONFLICT@ clause is
-- a no-op @DO UPDATE@ of a key column).
--
-- A row with @NULL@ in a column of a unique key never conflicts (unless the
-- constraint is @NULLS NOT DISTINCT@), so it is always inserted. The same key must not occur twice in one call
-- (PostgreSQL can't update a row twice in one statement).
--
-- Returning type @r'@ may name any plain columns of the table.
-- Returns one returned row per input row (in the same order) and the SQL text.
upsertByKey
  :: forall ann -> forall r r'. UpsertByKeyReturning ann r r'
  => Connection -> [r] -> IO ([r'], Text)
upsertByKey ann @r @r' conn recs = let sql = upsertByKeyText ann @r @r' in
  trace' (T.unpack sql) do
    (, sql)
      <$> fmap (fmap (unPgTag @ann @r'))
      ( returning conn (fromString $ T.unpack sql)
      $ fmap (PgTag @ann @r) recs )

-- | Like 'upsertByKey', but without @RETURNING@.
--
-- If all fields of @r@ belong to the key, existing rows are left as is
-- (@ON CONFLICT (key) DO NOTHING@).
--
-- Returns the number of inserted or updated rows and the SQL text.
upsertByKey_
  :: forall ann -> forall r. UpsertByKeyNonReturning ann r
  => Connection -> [r] -> IO (Int64, Text)
upsertByKey_ ann @r conn recs = let sql = upsertByKeyText_ ann @r in
  trace' (T.unpack sql)
    $ fmap (, sql)
    . executeMany conn (fromString $ T.unpack sql)
    . fmap (PgTag @ann @r) $ recs

-- | SQL text of 'upsertByKey' for one row, with a @?@ placeholder for each field of @r@.
upsertByKeyText
  :: forall ann -> forall r r' s
  . (UpsertByKeyReturning ann r r', IsString s, Monoid s) => s
upsertByKeyText ann @r @r' =
  fromString (T.unpack $ upsertByKeyStmt ann @r True) <> " returning " <> fs'
  where
    ri' = getRecordInfo @ann @r'
    fs' = fromText $ T.intercalate "," [fi.fieldDbName | fi <- ri'.fields]

-- | SQL text of 'upsertByKey_' for one row, with a @?@ placeholder for each field of @r@.
upsertByKeyText_
  :: forall ann -> forall r s
  . (UpsertByKeyNonReturning ann r, IsString s, Monoid s) => s
upsertByKeyText_ ann @r = fromString $ T.unpack $ upsertByKeyStmt ann @r False

-- | @INSERT … ON CONFLICT …@ statement without @RETURNING@; the flag tells
-- whether @RETURNING@ will be added (see 'upsertOnConflict').
upsertByKeyStmt
  :: forall ann -> forall r. UpsertByKeyNonReturning ann r => Bool -> Text
upsertByKeyStmt ann @r withReturning =
  ins <> upsertOnConflict withReturning plainsOthers keyNames
  where
    ri = getRecordInfo @ann @r
    ti = tabInfoMap @(AnnSch ann) M.! ri.tabName
    flds = [ (fromText fi.fieldDbName, "?") | fi <- ri.fields ]
    srcFlds = fst <$> flds
    keyNames = fromMaybe [] $ pickKeyNames ti srcFlds
    (_, plainsOthers) = keyedUpdateSetAndKeys ti flds
    ins =
      "insert into " <> qualName ri.tabName
      <> "(" <> intercalate' ", " srcFlds <> ") values ("
      <> intercalate' ", " (snd <$> flds) <> ")"