From 8f432de3cc8c9a3217459be9d1b4a491a2d19d3f Mon Sep 17 00:00:00 2001 From: rakeshkky <12475069+rakeshkky@users.noreply.github.com> Date: Thu, 3 Oct 2019 19:35:57 +0530 Subject: [PATCH 1/4] support where clause in on_conflict of insert mutation, close #2795 --- .../src-lib/Hasura/GraphQL/Resolve/Insert.hs | 19 ++++++-- .../Hasura/GraphQL/Schema/Mutation/Insert.hs | 7 ++- .../onconflict/order_on_conflict_where.yaml | 47 +++++++++++++++++++ .../insert/onconflict/values_setup.yaml | 6 +++ .../resident_on_conflict_where.yaml | 36 ++++++++++++++ server/tests-py/test_graphql_mutations.py | 6 +++ 6 files changed, 115 insertions(+), 6 deletions(-) create mode 100644 server/tests-py/queries/graphql_mutation/insert/onconflict/order_on_conflict_where.yaml create mode 100644 server/tests-py/queries/graphql_mutation/insert/permissions/resident_on_conflict_where.yaml diff --git a/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs b/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs index 425fb22fc8288..218aa0a4f1195 100644 --- a/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs +++ b/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs @@ -21,6 +21,7 @@ import qualified Hasura.RQL.DML.Returning as RR import qualified Hasura.SQL.DML as S +import Hasura.GraphQL.Resolve.BoolExp import Hasura.GraphQL.Resolve.Context import Hasura.GraphQL.Resolve.InputValue import Hasura.GraphQL.Resolve.Mutation @@ -85,7 +86,7 @@ data AnnInsObj } deriving (Show, Eq) mkAnnInsObj - :: (MonadResolve m, Has InsCtxMap r, MonadReader r m) + :: (MonadResolve m, Has InsCtxMap r, MonadReader r m, Has FieldMap r) => RelationInfoMap -> PGColGNameMap -> AnnGObject @@ -96,7 +97,7 @@ mkAnnInsObj relInfoMap allColMap annObj = emptyInsObj = AnnInsObj [] [] [] traverseInsObj - :: (MonadResolve m, Has InsCtxMap r, MonadReader r m) + :: (MonadResolve m, Has InsCtxMap r, MonadReader r m, Has FieldMap r) => RelationInfoMap -> PGColGNameMap -> (G.Name, AnnInpVal) @@ -159,7 +160,7 @@ traverseInsObj rim allColMap (gName, annVal) defVal@(AnnInsObj cols objRels arrR bool withNonEmptyArrData (return defVal) $ null arrDataVals parseOnConflict - :: (MonadResolve m) + :: (MonadResolve m, MonadReader r m, Has FieldMap r) => QualifiedTable -> Maybe UpdPermForIns -> PGColGNameMap @@ -178,8 +179,10 @@ parseOnConflict tn updFiltrM allColMap val = withPathK "on_conflict" $ updFltrRes <- traverseAnnBoolExp (convPartialSQLExp sessVarFromCurrentSetting) updFiltr - return $ RI.CP1Update constraint updCols preSetRes $ - toSQLBoolExp (S.mkQual tn) updFltrRes + whereExp <- parseWhereExp obj + let updateBoolExp = toSQLBoolExp (S.mkQual tn) updFltrRes + whereCondition = S.BEBin S.AndOp updateBoolExp whereExp + return $ RI.CP1Update constraint updCols preSetRes whereCondition where getUpdCols o = do @@ -193,6 +196,12 @@ parseOnConflict tn updFiltrM allColMap val = withPathK "on_conflict" $ (_, enumVal) <- asEnumVal v return $ ConstraintName $ G.unName $ G.unEnumValue enumVal + parseWhereExp o = do + whereExpM <- forM (OMap.lookup "where" o) $ \v -> do + unresolved <- parseBoolExp v + traverse (traverse resolveValTxt) unresolved + pure $ maybe (S.BELit True) (toSQLBoolExp (S.mkQual tn)) whereExpM + toSQLExps :: (MonadError QErr m, MonadState PrepArgs m) => [PGColWithValue] diff --git a/server/src-lib/Hasura/GraphQL/Schema/Mutation/Insert.hs b/server/src-lib/Hasura/GraphQL/Schema/Mutation/Insert.hs index d0f5c22f36d4b..8bc581fddf5dc 100644 --- a/server/src-lib/Hasura/GraphQL/Schema/Mutation/Insert.hs +++ b/server/src-lib/Hasura/GraphQL/Schema/Mutation/Insert.hs @@ -10,6 +10,7 @@ import qualified Data.HashMap.Strict as Map import qualified Language.GraphQL.Draft.Syntax as G import Hasura.GraphQL.Resolve.Types +import Hasura.GraphQL.Schema.BoolExp import Hasura.GraphQL.Schema.Common import Hasura.GraphQL.Schema.Mutation.Common import Hasura.GraphQL.Validate.Types @@ -124,6 +125,7 @@ mkInsInp tn insCols relInfoMap = input table_on_conflict { constraint: table_constraint! update_columns: [table_column!] + where: table_bool_exp } -} @@ -131,7 +133,7 @@ input table_on_conflict { mkOnConflictInp :: QualifiedTable -> InpObjTyInfo mkOnConflictInp tn = mkHsraInpTyInfo (Just desc) (mkOnConflictInpTy tn) $ fromInpValL - [constraintInpVal, updateColumnsInpVal] + [constraintInpVal, updateColumnsInpVal, whereInpVal] where desc = G.Description $ "on conflict condition type for table " <>> tn @@ -141,6 +143,9 @@ mkOnConflictInp tn = updateColumnsInpVal = InpValInfo Nothing (G.Name "update_columns") Nothing $ G.toGT $ G.toNT $ G.toLT $ G.toNT $ mkUpdColumnInpTy tn + + whereInpVal = InpValInfo Nothing (G.Name "where") Nothing $ + G.toGT $ mkBoolExpTy tn {- insert_table( diff --git a/server/tests-py/queries/graphql_mutation/insert/onconflict/order_on_conflict_where.yaml b/server/tests-py/queries/graphql_mutation/insert/onconflict/order_on_conflict_where.yaml new file mode 100644 index 0000000000000..16582e02a8975 --- /dev/null +++ b/server/tests-py/queries/graphql_mutation/insert/onconflict/order_on_conflict_where.yaml @@ -0,0 +1,47 @@ +- description: Upsert into orders only if new value is greater than old value + url: /v1/graphql + status: 200 + response: + data: + insert_orders: + affected_rows: 0 + query: + variables: + placed: '2017-08-18 14:22:11.802755+02' + query: | + mutation ($placed: timestamptz) { + insert_orders( + objects: [{id: 1, placed: $placed}] + on_conflict: { + constraint:orders_pkey + update_columns: [placed] + where: {placed: {_lt: $placed}} + } + ){ + affected_rows + } + } + +- description: Upsert into orders only if new value is greater than old value + url: /v1/graphql + status: 200 + response: + data: + insert_orders: + affected_rows: 1 + query: + variables: + placed: '2017-08-20 14:22:11.802755+02' + query: | + mutation ($placed: timestamptz) { + insert_orders( + objects: [{id: 1, placed: $placed}] + on_conflict: { + constraint:orders_pkey + update_columns: [placed] + where: {placed: {_lt: $placed}} + } + ){ + affected_rows + } + } diff --git a/server/tests-py/queries/graphql_mutation/insert/onconflict/values_setup.yaml b/server/tests-py/queries/graphql_mutation/insert/onconflict/values_setup.yaml index 53d8fd4085988..b9bab05aa8ba9 100644 --- a/server/tests-py/queries/graphql_mutation/insert/onconflict/values_setup.yaml +++ b/server/tests-py/queries/graphql_mutation/insert/onconflict/values_setup.yaml @@ -21,3 +21,9 @@ args: - content: Sample article content title: Article 3 +#Insert into orders +- type: insert + args: + table: orders + objects: + - placed: '2017-08-19 14:22:11.802755+02' diff --git a/server/tests-py/queries/graphql_mutation/insert/permissions/resident_on_conflict_where.yaml b/server/tests-py/queries/graphql_mutation/insert/permissions/resident_on_conflict_where.yaml new file mode 100644 index 0000000000000..4c7e501fb45c4 --- /dev/null +++ b/server/tests-py/queries/graphql_mutation/insert/permissions/resident_on_conflict_where.yaml @@ -0,0 +1,36 @@ +description: Upsert into resident table only if age is greater +url: /v1/graphql +status: 200 +response: + data: + insert_resident: + affected_rows: 1 + returning: + - id: 6 + age: 23 +query: + variables: + age: 23 + query: | + mutation ($age:Int){ + insert_resident( + objects: [ + { + id: 6 + age: $age + name: "Resident 6" + } + ] + on_conflict:{ + constraint: resident_pkey + update_columns: [age] + where: {age: {_lt: $age}} + } + ){ + affected_rows + returning{ + id + age + } + } + } diff --git a/server/tests-py/test_graphql_mutations.py b/server/tests-py/test_graphql_mutations.py index dbf57ca47f053..7dbe4bd03ef8c 100644 --- a/server/tests-py/test_graphql_mutations.py +++ b/server/tests-py/test_graphql_mutations.py @@ -58,6 +58,9 @@ def test_err_unexpected_action(self, hge_ctx, transport): def test_err_unexpected_constraint(self, hge_ctx, transport): check_query_f(hge_ctx, self.dir() + "/article_unexpected_on_conflict_constraint_error.yaml") + def test_order_on_conflict_where(self, hge_ctx, transport): + check_query_f(hge_ctx, self.dir() + '/order_on_conflict_where.yaml') + @classmethod def dir(cls): return "queries/graphql_mutation/insert/onconflict" @@ -120,6 +123,9 @@ def test_resident_infant_role_insert_fail(self, hge_ctx, transport): def test_resident_5_modifies_resident_6_upsert(self, hge_ctx, transport): check_query_f(hge_ctx, self.dir() + "/resident_5_modifies_resident_6_upsert.yaml") + def test_resident_on_conflict_where(self, hge_ctx, transport): + check_query_f(hge_ctx, self.dir() + "/resident_on_conflict_where.yaml") + def test_blog_on_conflict_update_preset(self, hge_ctx, transport): check_query_f(hge_ctx, self.dir() + "/blog_on_conflict_update_preset.yaml") From b0b8c9726db74c62ee0a555e2493ce55a343db84 Mon Sep 17 00:00:00 2001 From: rakeshkky <12475069+rakeshkky@users.noreply.github.com> Date: Fri, 4 Oct 2019 16:23:46 +0530 Subject: [PATCH 2/4] add docs --- .../api-reference/graphql-api/mutation.rst | 16 +++--- docs/graphql/manual/mutations/upsert.rst | 51 ++++++++++++++++++- 2 files changed, 58 insertions(+), 9 deletions(-) diff --git a/docs/graphql/manual/api-reference/graphql-api/mutation.rst b/docs/graphql/manual/api-reference/graphql-api/mutation.rst index 75f9a12833a9f..e7cc9e21faf3a 100644 --- a/docs/graphql/manual/api-reference/graphql-api/mutation.rst +++ b/docs/graphql/manual/api-reference/graphql-api/mutation.rst @@ -52,7 +52,7 @@ Insert / upsert syntax **E.g. INSERT**: .. code-block:: graphql - + mutation insert_article { insert_article( objects: [ @@ -73,7 +73,7 @@ Insert / upsert syntax **E.g. UPSERT**: .. code-block:: graphql - + mutation upsert_author { insert_author ( objects: [ @@ -161,7 +161,7 @@ Update syntax **E.g. UPDATE**: .. code-block:: graphql - + mutation update_author{ update_author( where: {id: {_eq: 3}}, @@ -212,7 +212,7 @@ Delete syntax **E.g. DELETE**: .. code-block:: graphql - + mutation delete_articles { delete_article( where: {author: {id: {_eq: 7}}} @@ -226,7 +226,7 @@ Delete syntax .. note:: - + For more examples and details of usage, please see :doc:`this <../../mutations/index>`. Syntax definitions @@ -287,7 +287,7 @@ E.g.: E.g.: .. code-block:: graphql - + objects: [ { title: "Software is eating the world", @@ -310,10 +310,11 @@ permissions before editing an existing row in case of a conflict. Hence the conf table has *update* permissions defined. .. code-block:: none - + on_conflict: { constraint: table_constraint! update_columns: [table_update_column!]! + where: table_bool_exp } E.g.: @@ -323,6 +324,7 @@ E.g.: on_conflict: { constraint: author_name_key update_columns: [name] + where: {id: {_lt: 1}} } .. _whereArgExp: diff --git a/docs/graphql/manual/mutations/upsert.rst b/docs/graphql/manual/mutations/upsert.rst index 3ddac8d5a9384..b30753f6f8114 100644 --- a/docs/graphql/manual/mutations/upsert.rst +++ b/docs/graphql/manual/mutations/upsert.rst @@ -44,11 +44,11 @@ The upsert functionality is sometimes confused with the update functionality. Ho differently. An upsert mutation is used in the case when it's not clear if the respective row is already present in the database. If it's known that the row is present in the database, ``update`` is the functionality to use. -For an upsert, **all columns need to be passed**. +For an upsert, **all columns need to be passed**. **How it works** -1. Postgres tries to insert a row (hence all the columns need to be present) +1. Postgres tries to insert a row (hence all the columns need to be present) 2. If this fails because of some constraint, it updates the specified columns @@ -104,6 +104,53 @@ the columns specified in ``update_columns``: The ``published_on`` column is left unchanged as it wasn't present in ``update_columns``. +Update selected columns on conflict using a filter +-------------------------------------------------- +Insert a new object in the ``article`` table or, if the primary key constraint ``article_pkey`` is violated, update +the columns specified in ``update_columns`` only if provided ``where`` condition is met: + + +.. graphiql:: + :view_only: + :query: + mutation upsert_article { + insert_article ( + objects: [ + { + id: 2, + published_on: "2018-10-12" + } + ], + on_conflict: { + constraint: article_pkey, + update_columns: [published_on], + where: { + published_on: {_lt: "2018-10-12"} + } + } + ) { + returning { + id + published_on + } + } + } + :response: + { + "data": { + "insert_article": { + "returning": [ + { + "id": 2, + "published_on": "2018-10-12" + } + ] + } + } + } + +The ``published_on`` column is updated only if the new value is greater than old value. + Ignore request on conflict -------------------------- If ``update_columns`` is an **empty array** then the GraphQL engine ignores changes on conflict. Insert a new object into From 879d53b59495d36f8f9f898ac454d1b2032f316d Mon Sep 17 00:00:00 2001 From: rakeshkky <12475069+rakeshkky@users.noreply.github.com> Date: Wed, 9 Oct 2019 11:57:20 +0530 Subject: [PATCH 3/4] no-op refactor Requested by @lexi-lambda --- docs/graphql/manual/mutations/upsert.rst | 6 +++--- server/src-lib/Hasura/GraphQL/Resolve/Insert.hs | 10 +++++----- 2 files changed, 8 insertions(+), 8 deletions(-) diff --git a/docs/graphql/manual/mutations/upsert.rst b/docs/graphql/manual/mutations/upsert.rst index b30753f6f8114..af173b564c32f 100644 --- a/docs/graphql/manual/mutations/upsert.rst +++ b/docs/graphql/manual/mutations/upsert.rst @@ -106,8 +106,8 @@ The ``published_on`` column is left unchanged as it wasn't present in ``update_c Update selected columns on conflict using a filter -------------------------------------------------- -Insert a new object in the ``article`` table or, if the primary key constraint ``article_pkey`` is violated, update -the columns specified in ``update_columns`` only if provided ``where`` condition is met: +Insert a new object in the ``article`` table, or if the primary key constraint ``article_pkey`` is violated, update +the columns specified in ``update_columns`` only if provided the ``where`` condition is met: .. graphiql:: @@ -149,7 +149,7 @@ the columns specified in ``update_columns`` only if provided ``where`` condition } } -The ``published_on`` column is updated only if the new value is greater than old value. +The ``published_on`` column is updated only if the new value is greater than the old value. Ignore request on conflict -------------------------- diff --git a/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs b/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs index 218aa0a4f1195..25c3dc1926f73 100644 --- a/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs +++ b/server/src-lib/Hasura/GraphQL/Resolve/Insert.hs @@ -2,6 +2,7 @@ module Hasura.GraphQL.Resolve.Insert (convertInsert) where +import Control.Arrow ((>>>)) import Data.Has import Hasura.EncJSON import Hasura.Prelude @@ -196,11 +197,10 @@ parseOnConflict tn updFiltrM allColMap val = withPathK "on_conflict" $ (_, enumVal) <- asEnumVal v return $ ConstraintName $ G.unName $ G.unEnumValue enumVal - parseWhereExp o = do - whereExpM <- forM (OMap.lookup "where" o) $ \v -> do - unresolved <- parseBoolExp v - traverse (traverse resolveValTxt) unresolved - pure $ maybe (S.BELit True) (toSQLBoolExp (S.mkQual tn)) whereExpM + parseWhereExp = + OMap.lookup "where" + >>> traverse (parseBoolExp >=> traverse (traverse resolveValTxt)) + >>> fmap (maybe (S.BELit True) (toSQLBoolExp (S.mkQual tn))) toSQLExps :: (MonadError QErr m, MonadState PrepArgs m) From 4d4b50603b04738011e4adec2fcdd16bffdae55e Mon Sep 17 00:00:00 2001 From: Alexis King Date: Wed, 9 Oct 2019 01:41:23 -0500 Subject: [PATCH 4/4] Fix minor documentation typo --- docs/graphql/manual/mutations/upsert.rst | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/docs/graphql/manual/mutations/upsert.rst b/docs/graphql/manual/mutations/upsert.rst index af173b564c32f..d61c8481460ef 100644 --- a/docs/graphql/manual/mutations/upsert.rst +++ b/docs/graphql/manual/mutations/upsert.rst @@ -107,7 +107,7 @@ The ``published_on`` column is left unchanged as it wasn't present in ``update_c Update selected columns on conflict using a filter -------------------------------------------------- Insert a new object in the ``article`` table, or if the primary key constraint ``article_pkey`` is violated, update -the columns specified in ``update_columns`` only if provided the ``where`` condition is met: +the columns specified in ``update_columns`` only if the provided ``where`` condition is met: .. graphiql::