[GraphQL] Stored Procedure support for Engine

[yorek]

Following there is the proposal to support Stored Procedures in Data API Builder.

Developers will be able to define, via the configuration file, what of the CRUD operations are supported for each exposed stored procedure

For stored procedures Data API Builder will support all CRUD operations:

• GraphQL: queries and mutations
• REST: POST, GET, PUT, PATCH, DELETE methods

It will be up to the developer to limit what of the above operations are applicable to the stored procedure, by correctly configuring the permission associated with the entity connected to the stored procedure. For example, a stored procedure named get_users should only support the read permission, but that will be up to the developer to correctly configure the configuration file to do so. For example:

"entities": {
    "user": {
        "source": {
        "object": "web.get_users",
        "type": "stored-procedure",
        "parameters": {
            "param1": 123,
            "param2": "hello",
            "param3": true
        }
        },
        "permissions": [
        {
            "actions": [ "read" ],
            "role": "anonymous"
        }
        ]
    }
}

For what concern GraphQL support, a stored procedure will not support the following GraphQL and REST query features:

• pagination
• sorting
• filtering
• selection

Only the first resultset returned by the stored procedure will be returned to the end user.

If a stored procedure can accept parameters, those parameters will be available to be passed to the stored procedure both via GraphQL and REST. For REST they can be passed in the request body (for POST, PUT, PATCH and DELETE), by providing an JSON with the key-value pair related to each parameter name and valuem, or in the query string (for GET method). For GraphQL parameter can be passed as paramters of the query or the mutation.

[yorek]

Parameter should also support the ability to have values injected via the available claims. For example, I should be able to set the value of parameter "param3" (following the above sample) to the value available in a claim:

{
...
 "param3": "@claims.user_id"
}

[yorek]

If a parameter is specified both in the incoming request and in the configuration file, the value from the incoming request will take precedence.