packages feed

pg-schema-0.9.0.0: test-pgs/Tests/KeyedDML.hs

{-# LANGUAGE BlockArguments #-}
-- |
-- Flat keyed DML: 'upsertByKey' needs mandatory fields and a full key
-- ('INSERT … ON CONFLICT'); key-only patches use 'updateByKey' only.
module Tests.KeyedDML where

import Control.Monad (void, when, unless)
import Data.Int (Int32)
import Data.List qualified as L
import Data.Pool as Pool
import Data.Text (Text)
import Data.Text qualified as T
import Data.Time (Day, fromGregorian)
import Database.PostgreSQL.Simple (Connection)
import Hedgehog
import Hedgehog.Gen qualified as Gen
import Hedgehog.Range qualified as Range
import PgSchema.DML
import Utils

type RootRec =
  "code" := Text :. "grp" := Int32 :. "name" := Text :. "someEmpty" := ()

type RootRetBare = "id" := Int32 :. RootRec

type RootKeyPatch =
  "code" := Text :. "grp" := Int32 :. "name" := Text :. "someEmpty" := ()

type RootKeyRet = "id" := Int32 :. "name" := Text

type RootSel = "code" := Text :. "grp" := Int32 :. "name" := Text

type NullableRow =
  "code" := Text :. "suffix" := Maybe Text :. "name" := Text
    :. "note" := Maybe Text :. "someEmpty" := ()

type NullableKeyPatch =
  "code" := Text :. "suffix" := Maybe Text :. "note" := Maybe Text
    :. "someEmpty" := ()

type NullableKeyRet = "id" := Int32 :. "note" := Maybe Text

type RootPatchOnly =
  "id" := Int32 :. "code" := Text :. "grp" := Int32 :. "name" := Text
    :. "someEmpty" := ()

type RootKeyOnlyPatch = "id" := Int32 :. "name" := Text

type RootUpsOutMaybe = Maybe ("id" := Int32 :. "name" := Text)

type RootUpdOutMaybe = RootUpsOutMaybe

type RootUpsBare = "id" := Int32 :. RootRec

type Mid1Rec =
  "flag" := Bool :. "pos" := Int32 :. "sortKey" := Int32 :. "payload" := Maybe Text

type RootWithMid1 = "mid1_root_fk" := [Mid1Rec] :. RootRec

type RootInsWithMid =
  "id" := Int32 :. "mid1_root_fk" := ["id" := Int32 :. Mid1Rec]

type Mid1KeyPatch = "id" := Int32 :. Mid1Rec

type Mid1KeyRet = "id" := Int32 :. "flag" := Bool

type TagLinkKey = "ownerId" := Int32 :. "tag" := Text

type TagLinkRow = "ownerId" := Int32 :. "tag" := Text :. "note" := Maybe Text

expectAbsent :: Show a => [Maybe a] -> IO ()
expectAbsent [Nothing] = pure ()
expectAbsent xs = fail $ "expected [Nothing], got " <> show xs

expectUpsertSql :: Text -> IO ()
expectUpsertSql sql = do
  let lower = T.toLower sql
  unless ("insert into" `T.isInfixOf` lower) $
    fail $ "expected INSERT upsert SQL, got: " <> T.unpack sql
  unless ("on conflict" `T.isInfixOf` lower) $
    fail $ "expected ON CONFLICT in upsert SQL, got: " <> T.unpack sql
  when (T.stripPrefix "update " (T.stripStart lower) == Just "") $
    fail $ "upsertByKey must not emit UPDATE-only SQL: " <> T.unpack sql

prop_upsert_by_key_insert_then_update :: Pool Connection -> Property
prop_upsert_by_key_insert_then_update pool = withTests 10 $ property do
  code <- forAll (Gen.text (Range.linear 3 20) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 10000))
  name1 <- forAll (Gen.text (Range.linear 1 20) Gen.alphaNum)
  name2 <- forAll (Gen.text (Range.linear 1 20) Gen.alphaNum)
  when (name1 == name2) discard
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn ("code" =? code)
    let
      full :: RootRec
      full = "code" =: code :. "grp" =: grp :. "name" =: name1 :. "someEmpty" =: ()
      row2 :: RootRec
      row2 = "code" =: code :. "grp" =: grp :. "name" =: name2 :. "someEmpty" =: ()
    ([_ :. _], sql1) <- upsertByKey (AnnSch "root") @RootRec @RootRetBare conn [full]
    expectUpsertSql sql1
    (ret, sql2) <- upsertByKey (AnnSch "root") @RootRec @RootKeyRet conn [row2]
    expectUpsertSql sql2
    case ret of
      [_ :. n] -> when (unPgTag n /= name2) $
        fail $ "expected name " <> show name2
      _ -> fail "expected one row from upsertByKey on conflict update"
    (rows :: [RootSel], _) <- selSch "root" conn $
      qRoot $ qWhere $ "code" =? code &&& "grp" =? grp
    case rows of
      [_ :. _ :. n] -> when (unPgTag n /= name2) $ fail "DB name not updated"
      _ -> fail "expected one row"

-- | All fields of the record belong to the key: there is nothing to update, but
-- existing rows must be returned too (and left as is).
prop_upsert_by_key_key_only_returns_all :: Pool Connection -> Property
prop_upsert_by_key_key_only_returns_all pool = withTests 5 $ property do
  owner <- forAll (Gen.int32 (Range.linear 1 100000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "tag_link" conn ("ownerId" =? owner)
    let
      seed :: TagLinkRow
      seed = "ownerId" =: owner :. "tag" =: "a" :. "note" =: Just "kept"
      keys = rowsOf @TagLinkKey [owner :. "a", owner :. "b"]
      expected = [owner :. ("a" :. Just "kept"), owner :. ("b" :. Nothing)]
    void $ insSch_ "tag_link" conn [seed]
    (ret, sql) <- upsertByKey (AnnSch "tag_link") @TagLinkKey @TagLinkRow conn keys
    expectUpsertSql sql
    when (plains ret /= expected) $
      fail $ "expected all rows returned, got " <> show ret <> "\nSQL: " <> T.unpack sql
    (n, sql_) <- upsertByKey_ (AnnSch "tag_link") conn keys
    expectUpsertSql sql_
    when (n /= 0) $ fail $ "expected no changed rows, got " <> show n
    (rows :: [TagLinkRow], _) <- selSch "tag_link" conn $ qRoot do
      qWhere $ "ownerId" =? owner
      qOrderBy [ascf "tag"]
    when (plains rows /= expected) $
      fail $ "expected rows left as is, got " <> show rows

prop_upsert_by_key_composite_unique :: Pool Connection -> Property
prop_upsert_by_key_composite_unique pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 24) Gen.alphaNum)
  name1 <- forAll (Gen.text (Range.linear 1 24) Gen.alphaNum)
  name2 <- forAll (Gen.text (Range.linear 1 24) Gen.alphaNum)
  when (name1 == name2) discard
  let grp = 5151 :: Int32
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn ("code" =? code)
    let
      row1 :: RootRec
      row1 = "code" =: code :. "grp" =: grp :. "name" =: name1 :. "someEmpty" =: ()
      row2 :: RootRec
      row2 = "code" =: code :. "grp" =: grp :. "name" =: name2 :. "someEmpty" =: ()
    (_, sql1) <- upsByKey_ "root" conn [row1]
    expectUpsertSql sql1
    (_, sql2) <- upsByKey_ "root" conn [row2]
    expectUpsertSql sql2
    (rows :: [RootSel], _) <- selSch "root" conn $
      qRoot $ qWhere $ "code" =? code &&& "grp" =? grp
    case rows of
      [_ :. _ :. n] ->
        when (unPgTag n /= name2) $
          fail $ "expected updated name " <> show name2
      _ -> fail $ "expected 1 row, got " <> show (L.length rows)

prop_upsert_by_key_never_pure_update :: Pool Connection -> Property
prop_upsert_by_key_never_pure_update pool = withTests 3 $ property do
  code <- forAll (Gen.text (Range.linear 3 20) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 10000))
  name <- forAll (Gen.text (Range.linear 1 20) Gen.alphaNum)
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn ("code" =? code)
    let
      row :: RootRec
      row = "code" =: code :. "grp" =: grp :. "name" =: name :. "someEmpty" =: ()
    (_, sqlExec) <- upsByKey_ "root" conn [row]
    expectUpsertSql sqlExec
    let sqlText = upsertByKeyText_ (AnnSch "root") @RootRec
    expectUpsertSql sqlText

prop_update_by_key_found :: Pool Connection -> Property
prop_update_by_key_found pool = withTests 10 $ property do
  code <- forAll (Gen.text (Range.linear 3 20) Gen.alphaNum)
  note1 <- forAll (Gen.text (Range.linear 1 20) Gen.alphaNum)
  note2 <- forAll (Gen.text (Range.linear 1 20) Gen.alphaNum)
  when (note1 == note2) discard
  let sfx = Just ("sfx" :: Text)
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "nullable_uq_row" conn ("code" =? code)
    let
      seed :: NullableRow
      seed =
        "code" =: code :. "suffix" =: sfx :. "name" =: "n"
          :. "note" =: Just note1 :. "someEmpty" =: ()
      patch :: NullableKeyPatch
      patch =
        "code" =: code :. "suffix" =: sfx :. "note" =: Just note2
          :. "someEmpty" =: ()
    void $ insSch_ "nullable_uq_row" conn [seed]
    (ret, _) <- updateByKey (AnnSch "nullable_uq_row") @NullableKeyPatch
      @NullableKeyRet conn [patch]
    case ret of
      [Just (_ :. n)] -> when (unPgTag n /= Just note2) $ fail "note not updated"
      _ -> fail "expected [Just]"
    (rows :: ["note" := Maybe Text], _) <- selSch "nullable_uq_row" conn $
      qRoot $ qWhere $ "code" =? code &&& "suffix" =?? sfx
    case rows of
      [n] -> when (unPgTag n /= Just note2) $ fail "DB note mismatch"
      _ -> fail "expected one row"

prop_update_by_key_not_found :: Pool Connection -> Property
prop_update_by_key_not_found pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 20) Gen.alphaNum)
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "nullable_uq_row" conn ("code" =? code)
    let
      patch :: NullableKeyPatch
      patch =
        "code" =: code :. "suffix" =: Just "x" :. "note" =: Just "y"
          :. "someEmpty" =: ()
    (ret, _) <- updateByKey (AnnSch "nullable_uq_row") @NullableKeyPatch
      @NullableKeyRet conn [patch]
    expectAbsent ret
    (rows :: [NullableRow], _) <- selSch "nullable_uq_row" conn $
      qRoot $ qWhere $ "code" =? code
    unless (null rows) $ fail $ "expected 0 rows, got " <> show rows

prop_update_by_key_never_inserts :: Pool Connection -> Property
prop_update_by_key_never_inserts pool = withTests 3 $ property do
  code <- forAll (Gen.text (Range.linear 3 20) Gen.alphaNum)
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    let
      patch :: RootKeyPatch
      patch =
        "code" =: code :. "grp" =: (1 :: Int32) :. "name" =: "x"
          :. "someEmpty" =: ()
    (ret, _) <- updateByKey (AnnSch "root") @RootKeyPatch @RootKeyRet conn [patch]
    expectAbsent ret
    (rows :: [RootSel], _) <- selSch "root" conn qpEmpty
    unless (null rows) $ fail $ "expected 0 rows, got " <> show rows

prop_update_json_not_found_returns_nothing :: Pool Connection -> Property
prop_update_json_not_found_returns_nothing pool = withTests 5 $ property do
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    let
      bad :: RootPatchOnly
      bad =
        "id" =: (999999999 :: Int32) :. "code" =: "x" :. "grp" =: 1
          :. "name" =: "y" :. "someEmpty" =: ()
    (rets, _) <- updateJSON (AnnSch "root") @RootPatchOnly @RootUpdOutMaybe conn [bad]
    expectAbsent rets

prop_update_json_found :: Pool Connection -> Property
prop_update_json_found pool = withTests 10 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 5000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    let
      row :: RootRec
      row = "code" =: code :. "grp" =: grp :. "name" =: "seed" :. "someEmpty" =: ()
    (insOut, _) <- insertJSON (AnnSch "root") @RootRec @RootRetBare conn [row]
    insHead <- case insOut of
      (x : _) -> pure x
      [] -> fail "insertJSON returned no rows"
    let
      (PgTag rid :. _) = insHead
      patch :: RootPatchOnly
      patch =
        "id" =: rid :. "code" =: code :. "grp" =: grp :. "name" =: "patched"
          :. "someEmpty" =: ()
    (ret, _) <- updateJSON (AnnSch "root") @RootPatchOnly @RootUpdOutMaybe conn [patch]
    case ret of
      [Just (_ :. PgTag n)] -> when (n /= "patched") $ fail "name not updated"
      _ -> fail $ "unexpected updateJSON result: " <> show ret

prop_upsert_json_returning_positions :: Pool Connection -> Property
prop_upsert_json_returning_positions pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 5000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    let
      row :: RootRec
      row = "code" =: code :. "grp" =: grp :. "name" =: "n" :. "someEmpty" =: ()
    (insOut, _) <- insertJSON (AnnSch "root") @RootRec @RootRetBare conn [row]
    insHead <- case insOut of
      (x : _) -> pure x
      [] -> fail "insertJSON returned no rows"
    let
      (PgTag rid :. _) = insHead
      good :: RootKeyOnlyPatch
      good = "id" =: rid :. "name" =: "ok"
      bad :: RootKeyOnlyPatch
      bad = "id" =: (888888888 :: Int32) :. "name" =: "z"
    (rets, _) <- upsertJSON (AnnSch "root") @RootKeyOnlyPatch @RootUpsOutMaybe conn [good, bad]
    when (length rets /= 2) $ fail $ "expected 2 results, got " <> show rets
    case rets of
      [Just _, Nothing] -> pure ()
      _ -> fail $ "expected [Just, Nothing], got " <> show rets

prop_upsert_json_full_row_bare_returning :: Pool Connection -> Property
prop_upsert_json_full_row_bare_returning pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 5000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    let
      row :: RootRec
      row = "code" =: code :. "grp" =: grp :. "name" =: "n" :. "someEmpty" =: ()
    (rets, _) <- upsertJSON (AnnSch "root") @RootRec @RootUpsBare conn [row]
    case rets of
      [PgTag _ :. _] -> pure ()
      _ -> fail "expected bare upsert returning row"

-- | Root @Just@, key-only upsert patch on child table via flat @updateByKey@.
prop_update_json_child_maybe :: Pool Connection -> Property
prop_update_json_child_maybe pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 5000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "mid1" conn mempty
    void $ delByCond "root" conn mempty
    let
      row :: RootRec
      row = "code" =: code :. "grp" =: grp :. "name" =: "n" :. "someEmpty" =: ()
    ([_ :. PgTag mids], _) <- insertJSON (AnnSch "root") @RootWithMid1 @RootInsWithMid conn
      [ "mid1_root_fk" =: [ "flag" =: True :. "pos" =: 1 :. "sortKey" =: 1
          :. "payload" =: Nothing ] :. row ]
    let
      midId = case mids of [PgTag i :. _] -> i; _ -> error "one mid1"
      patch :: Mid1KeyPatch
      patch = "id" =: (midId + 99999) :. "flag" =: False :. "pos" =: 1
        :. "sortKey" =: 1 :. "payload" =: Nothing
    (ret, _) <- updateByKey (AnnSch "mid1") @Mid1KeyPatch @Mid1KeyRet conn [patch]
    expectAbsent ret

type RootMid1KeyPatch = "id" := Int32 :. "mid1_root_fk" := [Mid1KeyRet]

type RootMid1UpdOut =
  Maybe ("id" := Int32 :. "mid1_root_fk" := [Maybe ("id" := Int32 :. "flag" := Bool)])

-- | Insert a root with one @mid1@ child; return ids of the root and the child.
insertRootWithMid1 :: Connection -> Text -> Int32 -> IO (Int32, Int32)
insertRootWithMid1 conn code grp = do
  let
    row :: RootRec
    row = "code" =: code :. "grp" =: grp :. "name" =: "n" :. "someEmpty" =: ()
  res <- insertJSON (AnnSch "root") @RootWithMid1 @RootInsWithMid conn
    [ "mid1_root_fk" =: [ "flag" =: True :. "pos" =: 1 :. "sortKey" =: 1
        :. "payload" =: Nothing ] :. row ]
  case res of
    ([PgTag rid :. PgTag [PgTag mid :. _]], _) -> pure (rid, mid)
    _ -> fail "expected one root with one mid1"

-- | A child that is not found by its key comes back as @Nothing@ (JSON @null@);
-- the returning type must have @Maybe@ on child list elements.
prop_update_json_missing_child :: Pool Connection -> Property
prop_update_json_missing_child pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 5000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    (rid, mid) <- insertRootWithMid1 conn code grp
    let
      patch :: RootMid1KeyPatch
      patch = "id" =: rid :. "mid1_root_fk" =:
        [ "id" =: mid :. "flag" =: False, "id" =: (mid + 99999) :. "flag" =: False ]
    (ret, _) <- updateJSON (AnnSch "root") @RootMid1KeyPatch @RootMid1UpdOut conn [patch]
    case ret of
      [Just (PgTag rid' :. PgTag [Just (PgTag mid' :. PgTag False), Nothing])]
        | rid' == rid && mid' == mid -> pure ()
      _ -> fail $ "expected [Just (_ :. [Just _, Nothing])], got " <> show ret

type Mid2Ins =
  "seq" := Int32 :. "kind" := Text :. "flag" := Bool :. "priority" := Int32
    :. "leaf_mid2_fk" := ["leaf_no" := Int32 :. "value" := Double]

type RootMid2LeafPatch =
  "id" := Int32 :. "mid2_root_fk" :=
    ["seq" := Int32 :. "leaf_mid2_fk" := ["leaf_no" := Int32 :. "value" := Double]]

type RootMid2LeafUpdOut =
  Maybe ("id" := Int32 :. "mid2_root_fk" :=
    [Maybe ("seq" := Int32 :. "leaf_mid2_fk" :=
      [Maybe ("leaf_no" := Int32 :. "value" := Double)])])

prop_update_json_missing_grandchild :: Pool Connection -> Property
prop_update_json_missing_grandchild pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 5000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    let
      row :: RootRec
      row = "code" =: code :. "grp" =: grp :. "name" =: "n" :. "someEmpty" =: ()
      mid2 :: Mid2Ins
      mid2 = "seq" =: 1 :. "kind" =: "k" :. "flag" =: True :. "priority" =: 1
        :. "leaf_mid2_fk" =: ["leaf_no" =: 1 :. "value" =: 1]
    rid <- insertJSON (AnnSch "root") @("mid2_root_fk" := [Mid2Ins] :. RootRec)
      @("id" := Int32) conn ["mid2_root_fk" =: [mid2] :. row] >>= \case
        ([PgTag rid], _) -> pure rid
        _ -> fail "expected one root"
    let
      patch :: RootMid2LeafPatch
      patch = "id" =: rid :. "mid2_root_fk" =:
        [ "seq" =: 1 :. "leaf_mid2_fk" =:
          ["leaf_no" =: 1 :. "value" =: 2, "leaf_no" =: 2 :. "value" =: 3]
        , "seq" =: 2 :. "leaf_mid2_fk" =: ["leaf_no" =: 1 :. "value" =: 4] ]
    (ret, _) <- updateJSON (AnnSch "root") @RootMid2LeafPatch @RootMid2LeafUpdOut
      conn [patch]
    case ret of
      [Just (_ :. PgTag
        [ Just (PgTag 1 :. PgTag [Just (PgTag 1 :. PgTag 2), Nothing])
        , Nothing ])] -> pure ()
      _ -> fail $ "unexpected updateJSON result: " <> show ret

-- | Key-only child in 'upsertJSON' is updated (never inserted); a missing one is
-- returned as @Nothing@.
prop_upsert_json_key_only_missing_child :: Pool Connection -> Property
prop_upsert_json_key_only_missing_child pool = withTests 5 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  grp <- forAll (Gen.int32 (Range.linear 1 5000))
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    (rid, mid) <- insertRootWithMid1 conn code grp
    let
      patch :: RootMid1KeyPatch
      patch = "id" =: rid :. "mid1_root_fk" =:
        [ "id" =: mid :. "flag" =: False, "id" =: (mid + 99999) :. "flag" =: False ]
    (ret, _) <- upsertJSON (AnnSch "root") @RootMid1KeyPatch @RootMid1UpdOut conn [patch]
    case ret of
      [Just (PgTag rid' :. PgTag [Just (PgTag mid' :. PgTag False), Nothing])]
        | rid' == rid && mid' == mid -> pure ()
      _ -> fail $ "expected [Just (_ :. [Just _, Nothing])], got " <> show ret

type ArrDates = "dates_nullable" := Maybe (PgArr Day)

type RootArrIns = "arrays_root_fk" := [ArrDates] :. RootRec

type RootArrInsOut = "id" := Int32 :. "arrays_root_fk" := ["id" := Int32]

-- | Key-only patch of @arrays@ children: the parent FK @root_id@ is outside the
-- key @id@, so the child is upserted with @ON CONFLICT DO NOTHING@.
type RootArrKeyPatch = "id" := Int32 :. "arrays_root_fk" := ["id" := Int32]

-- | Returning a column that is not in the input: it is read from the table.
type RootArrUpsOut =
  Maybe ("id" := Int32 :. "arrays_root_fk" := [Maybe ("id" := Int32 :. ArrDates)])

-- | Returning only columns from the input.
type RootArrUpsOutKey =
  Maybe ("id" := Int32 :. "arrays_root_fk" := [Maybe ("id" := Int32)])

-- | Insert roots A and B with one @arrays@ child each; return
-- @(rootA, childA, rootB, childB)@.
insertRootsWithArrays :: Connection -> Text -> IO (Int32, Int32, Int32, Int32)
insertRootsWithArrays conn code = do
  let
    root :: Int32 -> Day -> RootArrIns
    root grp d = "arrays_root_fk" =: ["dates_nullable" =: Just (pgArr' [d])]
      :. "code" =: code :. "grp" =: grp :. "name" =: "n" :. "someEmpty" =: ()
  res <- insertJSON (AnnSch "root") @RootArrIns @RootArrInsOut conn
    [root 1 dayA, root 2 dayB]
  case res of
    ( [ PgTag ra :. PgTag [PgTag ca], PgTag rb :. PgTag [PgTag cb] ], _ ) ->
      pure (ra, ca, rb, cb)
    _ -> fail $ "expected two roots with one child each, got " <> show (fst res)

dayA, dayB :: Day
dayA = fromGregorian 2020 1 1
dayB = fromGregorian 2021 2 2

-- | @ON CONFLICT DO NOTHING@ child whose key belongs to another parent is not
-- found under this parent: @Nothing@, not a row of nulls.
prop_upsert_json_do_nothing_other_parent :: Pool Connection -> Property
prop_upsert_json_do_nothing_other_parent pool = withTests 3 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    (_, ca, rb, cb) <- insertRootsWithArrays conn code
    let
      patch :: RootArrKeyPatch
      patch = "id" =: rb :. "arrays_root_fk" =: ["id" =: cb, "id" =: ca]
    (ret, _) <- upsertJSON (AnnSch "root") @RootArrKeyPatch @RootArrUpsOut conn [patch]
    case ret of
      [Just (PgTag rb' :. PgTag [Just (PgTag cb' :. PgTag (Just ds)), Nothing])]
        | rb' == rb && cb' == cb && unPgArr' ds == [dayB] -> pure ()
      _ -> fail $ "expected [Just (_ :. [Just _, Nothing])], got " <> show ret
    (ret', _) <- upsertJSON (AnnSch "root") @RootArrKeyPatch @RootArrUpsOutKey conn [patch]
    case ret' of
      [Just (_ :. PgTag [Just (PgTag cb'), Nothing])] | cb' == cb -> pure ()
      _ -> fail $ "expected [Just (_ :. [Just _, Nothing])], got " <> show ret'
    -- the child of root A is not moved to root B
    (rows :: ["id" := Int32 :. "root_id" := Maybe Int32], _) <-
      selSch "arrays" conn $ qRoot $ qWhere $ "id" =? ca
    case rows of
      [_ :. PgTag rootId] | rootId /= Just rb -> pure ()
      _ -> fail $ "unexpected arrays row: " <> show rows

-- | @ON CONFLICT DO NOTHING@: a new child is inserted, an existing one under
-- this parent is returned.
prop_upsert_json_do_nothing_insert_or_found :: Pool Connection -> Property
prop_upsert_json_do_nothing_insert_or_found pool = withTests 3 $ property do
  code <- forAll (Gen.text (Range.linear 3 16) Gen.alphaNum)
  evalIO $ Pool.withResource pool \conn -> do
    void $ delByCond "root" conn mempty
    (_, ca, rb, cb) <- insertRootsWithArrays conn code
    let
      newId = max ca cb + 1000
      patch :: RootArrKeyPatch
      patch = "id" =: rb :. "arrays_root_fk" =: ["id" =: newId, "id" =: cb]
    (ret, _) <- upsertJSON (AnnSch "root") @RootArrKeyPatch @RootArrUpsOut conn [patch]
    case ret of
      [Just (_ :. PgTag
        [ Just (PgTag n :. PgTag Nothing)
        , Just (PgTag cb' :. PgTag (Just ds)) ])]
        | n == newId && cb' == cb && unPgArr' ds == [dayB] -> pure ()
      _ -> fail $ "unexpected upsertJSON result: " <> show ret