[LENS-742] Saved query and parameterization

• Type: New Feature
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 2.4
• Assignee: Amruth Sampath
• Reporter: Amruth Sampath
• Created: 19/Aug/15 08:25
• Updated: 17/Nov/15 11:49
• Resolved: 15/Sep/15 12:36

Description

• 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)