From d48a4bd807e70269ceb8b4cf5802cab657eb7d80 Mon Sep 17 00:00:00 2001 From: rakeshkky Date: Mon, 27 May 2019 13:04:00 +0530 Subject: [PATCH 1/5] support nullable inputs for SQL function args, close #2176, #2250 => If a parameter is not provided, it's default value is considered in SQL function => If a parameter is provided with 'null' value, then SQL function executed with 'NULL' value --- .../src-lib/Hasura/GraphQL/Resolve/Select.hs | 27 +++++++++---------- server/src-lib/Hasura/GraphQL/Schema.hs | 4 +-- 2 files changed, 15 insertions(+), 16 deletions(-) diff --git a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs index 0fd284b1ab953..506a90011da8c 100644 --- a/server/src-lib/Hasura/GraphQL/Resolve/Select.hs +++ b/server/src-lib/Hasura/GraphQL/Resolve/Select.hs @@ -305,7 +305,7 @@ convertSelect ) => SelOpCtx -> Field -> m QueryRootFldUnresolved convertSelect opCtx fld = - withPathK "selectionSet" $ fmap QRFSimple $ + withPathK "selectionSet" $ QRFSimple <$> fromField qt permFilter permLimit fld where SelOpCtx qt _ permFilter permLimit = opCtx @@ -316,7 +316,7 @@ convertSelectByPKey ) => SelPkOpCtx -> Field -> m QueryRootFldUnresolved convertSelectByPKey opCtx fld = - withPathK "selectionSet" $ fmap QRFPk $ + withPathK "selectionSet" $ QRFPk <$> fromFieldByPKey qt colArgMap permFilter fld where SelPkOpCtx qt _ permFilter colArgMap = opCtx @@ -400,7 +400,7 @@ convertAggSelect ) => SelOpCtx -> Field -> m QueryRootFldUnresolved convertAggSelect opCtx fld = - withPathK "selectionSet" $ fmap QRFAgg $ + withPathK "selectionSet" $ QRFAgg <$> fromAggField qt permFilter permLimit fld -- return $ RS.selectAggQuerySQL selData where @@ -408,14 +408,13 @@ convertAggSelect opCtx fld = parseFunctionArgs ::( MonadError QErr m) - => FuncArgSeq -> AnnInpVal -> m [AnnPGVal] -parseFunctionArgs argSeq val = - flip withObject val $ \nTy obj -> - fmap toList $ forM argSeq $ \(FuncArgItem argName) -> do - argVal <- onNothing (OMap.lookup argName obj) $ throw500 $ - "argument " <> showName argName <> " required in input type " - <> showNamedTy nTy - asPGColVal argVal + => FuncArgSeq -> AnnInpVal -> m [UnresolvedVal] +parseFunctionArgs argSeq val = fmap catMaybes $ + flip withObject val $ \_ obj -> + fmap toList $ forM argSeq $ \(FuncArgItem argName) -> + forM (OMap.lookup argName obj) $ fmap (maybe nullSQL UVPG) . asPGColValM + where + nullSQL = UVSQL $ S.SEUnsafe "NULL" fromFuncQueryField :: (MonadError QErr m) @@ -425,7 +424,7 @@ fromFuncQueryField -> m (RS.AnnFnSelG s UnresolvedVal) fromFuncQueryField fn qf argSeq fld = fieldAsPath fld $ do funcArgsM <- withArgM (_fArguments fld) "args" $ parseFunctionArgs argSeq - let funcArgs = maybe [] (map UVPG) funcArgsM + let funcArgs = fromMaybe [] funcArgsM RS.AnnFnSel qf funcArgs <$> fn fld convertFuncQuerySimple @@ -437,7 +436,7 @@ convertFuncQuerySimple ) => FuncQOpCtx -> Field -> m QueryRootFldUnresolved convertFuncQuerySimple funcOpCtx fld = - withPathK "selectionSet" $ fmap QRFFnSimple $ + withPathK "selectionSet" $ QRFFnSimple <$> fromFuncQueryField (fromField qt permFilter permLimit) qf argSeq fld where FuncQOpCtx qt _ permFilter permLimit qf argSeq = funcOpCtx @@ -451,7 +450,7 @@ convertFuncQueryAgg ) => FuncQOpCtx -> Field -> m QueryRootFldUnresolved convertFuncQueryAgg funcOpCtx fld = - withPathK "selectionSet" $ fmap QRFFnAgg $ + withPathK "selectionSet" $ QRFFnAgg <$> fromFuncQueryField (fromAggField qt permFilter permLimit) qf argSeq fld where FuncQOpCtx qt _ permFilter permLimit qf argSeq = funcOpCtx diff --git a/server/src-lib/Hasura/GraphQL/Schema.hs b/server/src-lib/Hasura/GraphQL/Schema.hs index 36f29e6efe67c..541ae1fd82b67 100644 --- a/server/src-lib/Hasura/GraphQL/Schema.hs +++ b/server/src-lib/Hasura/GraphQL/Schema.hs @@ -592,8 +592,8 @@ mkFuncArgsInp funcInfo = argInps = procFuncArgs funcArgs mkInpVal mkInpVal ty t = - InpValInfo Nothing (G.Name t) Nothing $ G.toGT $ - G.toNT $ mkScalarTy ty + InpValInfo Nothing (G.Name t) Nothing $ + G.toGT $ mkScalarTy ty -- table_set_input mkUpdSetTy :: QualifiedTable -> G.NamedType From cf81419d8900e96aa1abccc8b05950eb05ca0657 Mon Sep 17 00:00:00 2001 From: rakeshkky Date: Mon, 27 May 2019 15:54:23 +0530 Subject: [PATCH 2/5] add a test case to test null and default values to functions --- .../graphql_query/functions/query_my_add.yaml | 33 +++++++++++++++++++ .../graphql_query/functions/setup.yaml | 21 ++++++++++++ .../graphql_query/functions/teardown.yaml | 1 + server/tests-py/test_graphql_queries.py | 3 ++ 4 files changed, 58 insertions(+) create mode 100644 server/tests-py/queries/graphql_query/functions/query_my_add.yaml diff --git a/server/tests-py/queries/graphql_query/functions/query_my_add.yaml b/server/tests-py/queries/graphql_query/functions/query_my_add.yaml new file mode 100644 index 0000000000000..791512d25bcef --- /dev/null +++ b/server/tests-py/queries/graphql_query/functions/query_my_add.yaml @@ -0,0 +1,33 @@ +description: Run queries on SQL my_add function with null input values +url: /v1/graphql +status: 200 +response: + data: + my_add: + - result: 5 + my_add_null: + - result: + my_add_default: + - result: 12 +query: + query: | + query { + my_add( + args: {first: 2 second: 3} + ){ + result + } + # input null value + my_add_null: my_add( + args: {first: null second: 2} + ){ + result + } + # vomiting 'second' parameter to consider + # it's default value in postgres + my_add_default: my_add( + args: {first: 2} + ){ + result + } + } diff --git a/server/tests-py/queries/graphql_query/functions/setup.yaml b/server/tests-py/queries/graphql_query/functions/setup.yaml index 27dd724274cc0..2083977ecfe1d 100644 --- a/server/tests-py/queries/graphql_query/functions/setup.yaml +++ b/server/tests-py/queries/graphql_query/functions/setup.yaml @@ -71,5 +71,26 @@ args: name: get_test schema: public +# custom add sql function +- type: run_sql + args: + sql: | + CREATE TABLE integer_column + ( + result integer + ); + CREATE FUNCTION my_add (first integer, second integer DEFAULT 10) + RETURNS SETOF integer_column as $$ + SELECT q.* FROM (VALUES (first + second)) as q + $$ LANGUAGE SQL STABLE; +- type: track_table + args: + name: integer_column + schema: public + +- type: track_function + args: + name: my_add + schema: public diff --git a/server/tests-py/queries/graphql_query/functions/teardown.yaml b/server/tests-py/queries/graphql_query/functions/teardown.yaml index 1914b25c2af1a..a3b9a0c4e981b 100644 --- a/server/tests-py/queries/graphql_query/functions/teardown.yaml +++ b/server/tests-py/queries/graphql_query/functions/teardown.yaml @@ -6,4 +6,5 @@ args: sql: | DROP TABLE post cascade; DROP TABLE test cascade; + DROP TABLE integer_column cascade; cascade: true diff --git a/server/tests-py/test_graphql_queries.py b/server/tests-py/test_graphql_queries.py index bb94635750e6e..1156a07c0d95d 100644 --- a/server/tests-py/test_graphql_queries.py +++ b/server/tests-py/test_graphql_queries.py @@ -379,6 +379,9 @@ def test_overloading_function_error(self, hge_ctx): def test_query_get_test_uuid(self, hge_ctx): check_query_f(hge_ctx, self.dir() + '/query_get_test_uuid.yaml') + def test_query_my_add(self, hge_ctx): + check_query_f(hge_ctx, self.dir() + '/query_my_add.yaml') + @classmethod def dir(cls): return 'queries/graphql_query/functions' From 6e8abec225e211f81fe30aff2befb4da299433d0 Mon Sep 17 00:00:00 2001 From: rakeshkky Date: Mon, 27 May 2019 19:17:17 +0530 Subject: [PATCH 3/5] add docs --- .../manual/queries/custom-functions.rst | 72 ++++++++++++++++--- 1 file changed, 62 insertions(+), 10 deletions(-) diff --git a/docs/graphql/manual/queries/custom-functions.rst b/docs/graphql/manual/queries/custom-functions.rst index 79a633e7f1d17..98e4d58a7d958 100644 --- a/docs/graphql/manual/queries/custom-functions.rst +++ b/docs/graphql/manual/queries/custom-functions.rst @@ -115,8 +115,8 @@ First install the `pg_trgm Next create a GIN (or GIST) index in your database for the columns you'll be querying: .. code-block:: sql - - CREATE INDEX address_gin_idx ON property + + CREATE INDEX address_gin_idx ON property USING GIN ((unit || ' ' || num || ' ' || street || ' ' || city || ' ' || region || ' ' || postcode) gin_trgm_ops); And finally create the custom SQL function in the Hasura console: @@ -127,7 +127,7 @@ And finally create the custom SQL function in the Hasura console: RETURNS SETOF property AS $$ SELECT * FROM property - WHERE + WHERE search <% (unit || ' ' || num || ' ' || street || ' ' || city || ' ' || region || ' ' || postcode) ORDER BY similarity(search, (unit || ' ' || num || ' ' || street || ' ' || city || ' ' || region || ' ' || postcode)) DESC @@ -187,7 +187,7 @@ Say you have 2 tables, for user and landmark location data, with the following d popular spatial database extension,* `PostGIS `__): .. code-block:: sql - + -- User location data CREATE TABLE user_location ( user_id INTEGER PRIMARY KEY, @@ -211,7 +211,7 @@ doesn't exist, let's first create this table and then create our location search - create and track the following table: .. code-block:: sql - + -- SETOF table CREATE TABLE user_landmarks ( @@ -224,10 +224,10 @@ doesn't exist, let's first create this table and then create our location search .. code-block:: plpgsql - -- function returns a list of landmarks near a user based on the - -- input arguments distance_kms and userid + -- function returns a list of landmarks near a user based on the + -- input arguments distance_kms (default: 2) and userid - CREATE FUNCTION search_landmarks_near_user(userid integer, distance_kms integer) + CREATE FUNCTION search_landmarks_near_user(userid integer, distance_kms integer default 2) RETURNS SETOF user_landmarks AS $$ SELECT A.user_id, A.location, (SELECT json_agg(row_to_json(B)) FROM landmark B @@ -294,6 +294,58 @@ function in our GraphQL API as follows: } } +.. note:: + + If you Omit an argument in ``args`` input field then server executes SQL function without the argument. + Hence, the function uses default value of that argument. + +Search nearby landmarks with ``distance_kms`` default value which is 2 kms. + + +.. graphiql:: + :view_only: + :query: + query { + search_landmarks_near_user( + args: {userid: 3} + ){ + user_id + location + nearby_landmarks + } + } + :response: + { + "data": { + "search_landmarks_near_user": [ + { + "user_id": 3, + "location": { + "type": "Point", + "crs": { + "type": "name", + "properties": { + "name": "urn:ogc:def:crs:EPSG::4326" + } + }, + "coordinates": [ + 12.9406589, + 77.6185572 + ] + }, + "nearby_landmarks": [ + { + "id": 3, + "name": "blue tokai", + "type": "coffee shop", + "location": "0101000020E61000004E74A785DCF22940BE44060399665340" + } + ] + } + ] + } + } + Aggregations on custom functions ******************************** @@ -320,7 +372,7 @@ As with tables, arguments like ``where``, ``limit``, ``order_by``, ``offset``, e function-based queries. **For example**, limit the number of articles returned by the function defined in the text-search example above: - + .. code-block:: graphql query { @@ -341,4 +393,4 @@ Access control permissions configured for the ``SETOF`` table of a function are **For example**, in our text-search example above, if the role ``user`` doesn't have the requisite permissions to view the table ``article``, a validation error will be thrown if the ``search_articles`` query is run using the ``user`` -role. \ No newline at end of file +role. From f1dbe4b168a82085aefad6a8eca54704c29c4c32 Mon Sep 17 00:00:00 2001 From: Vamshi Surabhi <0x777@users.noreply.github.com> Date: Tue, 4 Jun 2019 15:12:35 +0530 Subject: [PATCH 4/5] Update query_my_add.yaml --- .../tests-py/queries/graphql_query/functions/query_my_add.yaml | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/server/tests-py/queries/graphql_query/functions/query_my_add.yaml b/server/tests-py/queries/graphql_query/functions/query_my_add.yaml index 791512d25bcef..e545b06666095 100644 --- a/server/tests-py/queries/graphql_query/functions/query_my_add.yaml +++ b/server/tests-py/queries/graphql_query/functions/query_my_add.yaml @@ -23,7 +23,7 @@ query: ){ result } - # vomiting 'second' parameter to consider + # omiting 'second' parameter to consider # it's default value in postgres my_add_default: my_add( args: {first: 2} From f0dc399b82f562d4974e130e1cc502a91d998437 Mon Sep 17 00:00:00 2001 From: rikinsk Date: Tue, 4 Jun 2019 17:06:46 +0530 Subject: [PATCH 5/5] update docs --- .../manual/queries/custom-functions.rst | 103 ++++++++++-------- 1 file changed, 56 insertions(+), 47 deletions(-) diff --git a/docs/graphql/manual/queries/custom-functions.rst b/docs/graphql/manual/queries/custom-functions.rst index 98e4d58a7d958..3351334d8350c 100644 --- a/docs/graphql/manual/queries/custom-functions.rst +++ b/docs/graphql/manual/queries/custom-functions.rst @@ -178,6 +178,8 @@ Assuming the ``property`` table is being tracked, you can use the custom functio } } +.. _custom_functions_postgis: + Example: PostGIS functions ************************** @@ -213,7 +215,6 @@ doesn't exist, let's first create this table and then create our location search .. code-block:: sql -- SETOF table - CREATE TABLE user_landmarks ( user_id INTEGER, location GEOGRAPHY(Point), @@ -225,9 +226,8 @@ doesn't exist, let's first create this table and then create our location search .. code-block:: plpgsql -- function returns a list of landmarks near a user based on the - -- input arguments distance_kms (default: 2) and userid - - CREATE FUNCTION search_landmarks_near_user(userid integer, distance_kms integer default 2) + -- input arguments distance_kms and userid + CREATE FUNCTION search_landmarks_near_user(userid integer, distance_kms integer) RETURNS SETOF user_landmarks AS $$ SELECT A.user_id, A.location, (SELECT json_agg(row_to_json(B)) FROM landmark B @@ -294,13 +294,61 @@ function in our GraphQL API as follows: } } -.. note:: +Aggregations on custom functions +******************************** + +You can query aggregations on a function result using ``_aggregate`` field. + +**For example**, count the number of articles returned by the function defined in the text-search example above: + +.. code-block:: graphql + + query { + search_articles_aggregate( + args: {search: "hasura"} + ){ + aggregate { + count + } + } + } + +Using arguments with custom functions +************************************* + +As with tables, arguments like ``where``, ``limit``, ``order_by``, ``offset``, etc. are also available for use with +function-based queries. + +**For example**, limit the number of articles returned by the function defined in the text-search example above: + +.. code-block:: graphql + + query { + search_articles( + args: {search: "hasura"}, + limit: 5 + ){ + id + title + content + } + } - If you Omit an argument in ``args`` input field then server executes SQL function without the argument. - Hence, the function uses default value of that argument. +Using argument default values for custom functions +************************************************** -Search nearby landmarks with ``distance_kms`` default value which is 2 kms. +If you omit an argument in ``args`` input field then GraphQL Engine executes the SQL function without the argument. +Hence, the function will use the default value of that argument set in its definition. +**For example:** In the above :ref:`PostGIS functions example `, the function +definition can be updated as follows: + +.. code-block:: plpgsql + + -- input arguments distance_kms (default: 2) and userid + CREATE FUNCTION search_landmarks_near_user(userid integer, distance_kms integer default 2) + +Search nearby landmarks with ``distance_kms`` default value which is 2 kms: .. graphiql:: :view_only: @@ -346,45 +394,6 @@ Search nearby landmarks with ``distance_kms`` default value which is 2 kms. } } -Aggregations on custom functions -******************************** - -You can query aggregations on a function result using ``_aggregate`` field. - -**For example**, count the number of articles returned by the function defined in the text-search example above: - -.. code-block:: graphql - - query { - search_articles_aggregate( - args: {search: "hasura"} - ){ - aggregate { - count - } - } - } - -Using arguments with custom functions -************************************* - -As with tables, arguments like ``where``, ``limit``, ``order_by``, ``offset``, etc. are also available for use with -function-based queries. - -**For example**, limit the number of articles returned by the function defined in the text-search example above: - -.. code-block:: graphql - - query { - search_articles( - args: {search: "hasura"}, - limit: 5 - ){ - id - title - content - } - } Permissions for custom function queries ---------------------------------------