[
https://issues.apache.org/jira/browse/LENS-742?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14726795#comment-14726795
]
Amruth S commented on LENS-742:
-------------------------------
Broadly saved query features
- saving a query
- parameterizing parts of it
How is it different from prepared queries and why is it needed?
- Saving a query.
Prepared queries are datastore level construct that helps bypassing compilation
and execution plan of a query. Some of the datastore might not even support
preparation of a query. (Eg. elastic search).
- parameterisation support.
Again (1) prepared queries might not available for all datastores (2) even if
it does, there might not be uniformity in definition of the parameters.
On the other hand, Saved queries and parameterisation will sit at a much higher
level (above the drivers) so that the uniformity is guaranteed.
Also the grammar of parameters can be improved as necessary and is not
restricted to the datastore. Some of the bind types pointed out in the
description here are not feasible in prepared queries at all. Had we have to
use prepared queries for this (and assume that all datastore support one), we
might have to end up exposing only the LCD of the feature provided by all the
datastores (as a lens user is agnostic of the underlying datastore where the
query is running)
> 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)
