[jira] [Assigned] (LENS-742) Saved query and parameterization

["Amruth S (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/LENS-742?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Amruth S reassigned LENS-742:
-----------------------------

    Assignee: Amruth S

> Saved query and parameterization
> --------------------------------
>
>                 Key: LENS-742
>                 URL: https://issues.apache.org/jira/browse/LENS-742
>             Project: Apache Lens
>          Issue Type: New Feature
>            Reporter: Amruth S
>            Assignee: Amruth S
>
> - User should be able to
>  * save a query parameterising parts of it.
>  * list all saved queries that are created by him and shared with him.
>  * share a query with other people with privileges.
>  * execute a saved query if his privilege allows (READ, EXECUTE).
>  * clone a saved query (READ).
> - All of these operations should be supported from CLI as well as service
> User flow (from UI)
> Saving
>  -> User authors a query and clicks on SAVE.
>  -> Client calls the helper api /parameters to get info about the parameters existing in the query. 
>  -> For each parameter in the response, these details are obtained from the user (from a pop up UI)
>   * DATA TYPE
>   * COLLECTION TYPE
> (scroll down to the end to see why we would need these details)
>  -> User enters all the details and clicks on SAVE again. A final payload containing the query and all the parameter details is sent.
> {
>   "name": "query_name",
>   "description": "description",
>   "query": "select * from table where col1 = :param1 and col2 in :param2 and col3 = :param3 and col4 in :param4 and col5 = :param5 and col6 in :param6",
>   "parameters": [
>     {
>       "name": "param1",
>       "displayName": "Param1",
>       "defaultValue": "x",
>       "dataType": "STRING",
>       "collectionType": "SINGLE"
>     },
>     {
>       "name": "param2",
>       "displayName": "Param2",
>       "defaultValue": "x",
>       "dataType": "STRING",
>       "collectionType": "MULTIPLE"
>     },
>     {
>       "name": "param3",
>       "displayName": "Param3",
>       "defaultValue": "1.0",
>       "dataType": "NUMBER",
>       "collectionType": "SINGLE"
>     },
>     {
>       "name": "param4",
>       "displayName": "Param4",
>       "defaultValue": "1.0",
>       "dataType": "NUMBER",
>       "collectionType": "MULTIPLE"
>     },
>     {
>       "name": "param5",
>       "displayName": "Param5",
>       "defaultValue": "true",
>       "dataType": "BOOLEAN",
>       "collectionType": "SINGLE"
>     },
>     {
>       "name": "param6",
>       "displayName": "Param6",
>       "defaultValue": "true",
>       "dataType": "BOOLEAN",
>       "collectionType": "MULTIPLE"
>     }
>   ]
> }
> Execution
> - User selects a saved query from a list of saved queries and clicks on RUN
> - A pop is shown asking for parameter values
> - User enters all param values (client side validation happens on datatype and collection type) and clicks RUN. A query handle is returned.
> User flow - From CLI
> Saving
> - create savedquery 'path to the final json payload'
> Executing
> - savedquery execute <id> <param values query string>
> Why do we need these DATATYPE and COLLECTION TYPE details in parameters??
> A parameterised query would look like this
> select col1, col2 from table where col1 = :param1 and col in :param2 limit :param3
> param1, param2, param3 are parameters here. 
> While resolving values for the parameters datatype would be required else SQL injection could happen.
> Eg. param1 value could be : 'val2 or param1 like '%''. We would not know if we can single quote it unless its a String.
> Data types could be inferred from the native tables but it is not applicable to all the drivers. 
> Also depending on the operator the parameter is associated with, the collection type could differ. Eg. IN versus =. Ideally this intelligence should exist with the code. This would require a great deal of query parsing. (The existing HQLParser - ASTNode is not able to parse an ANSI compatible parameterised query)



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)