[jira] [Work logged] (KNOX-2128) Custom DataSource and SQL Commands for KnoxShell and KnoxShellTable

["ASF GitHub Bot (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/KNOX-2128?focusedWorklogId=365943&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-365943 ]

ASF GitHub Bot logged work on KNOX-2128:
----------------------------------------

                Author: ASF GitHub Bot
            Created on: 03/Jan/20 18:49
            Start Date: 03/Jan/20 18:49
    Worklog Time Spent: 10m 
      Work Description: lmccay commented on pull request #231: KNOX-2128 - Custom DataSource and SQL Commands for KnoxShell and KnoxShellTable
URL: https://github.com/apache/knox/pull/231
 
 
   (It is very **important** that you created an Apache Knox JIRA for this change and that the PR title/commit message includes the Apache Knox JIRA ID!)
   
   ## What changes were proposed in this pull request?
   As described in KIP-14 , GroovyShell allows for the extension of the shell itself with custom commands. By providing commands for the management of DataSource configuration and use in SQL queries, we can simplify the interaction with SQL engines and databases for JDBC-based database access in KnoxShell to make for a powerful scripting and shell environment.
   
   (Please fill in changes proposed in this fix)
   DataSource and SQL commands really go hand in hand here and will be done at the same time.
   
       * Datasources (:datasource|:ds) CRUD and select operations for a set of JDBC datasources that are persisted to disk in the user home directory
       * SQL (:SQL|:sql) SQL query execution with persisted SQL history per datasource
   
   ## How was this patch tested?
    Primarily tested new custom groovysh commands manually as writing tests is a bit challenging due to the lack of the groovysh environment within the tests. Will follow up with a patch for mocking that environment if possible.
   
   In order to test there are a few required steps that will need to be documented for general use as well:
   
   * download JDBC drivers and place in the {gateway-shell-home}/lib directory
   * add datasources for JDBC endpoints using ":ds add ds-name connectStr driverClass authn_type" where authn_type is "none" or "basic"
   * selecting the desired datasource is required only when there are more than one defined and is accomplished via ":ds select ds-name"
   * submitting a SQL query is done via the :SQL command ":sql". You may also include an "assign table-name" pair in the command line to indicate what variable name to set the resulting table to within the environment. This will launch a swing-based SQL Dialog for submitting the Query. This is necessary due to how GroovySh Custom Commands parse params from the command line. Where clauses or anything else that would require quotes don't work from the command line alone as the quotes are stripped and each word ends up being a separate param.
   * After submitting the query, the datasource authn_type is interrogated and the user is challenged for username and password when it is set as "basic" with another swing-based dialog box due to similar environment issues.
   
   Please review [Knox Contributing Process](https://cwiki.apache.org/confluence/display/KNOX/Contribution+Process#ContributionProcess-GithubWorkflow) before opening a pull request.
   
 
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Issue Time Tracking
-------------------

            Worklog Id:     (was: 365943)
    Remaining Estimate: 0h
            Time Spent: 10m

> Custom DataSource and SQL Commands for KnoxShell and KnoxShellTable
> -------------------------------------------------------------------
>
>                 Key: KNOX-2128
>                 URL: https://issues.apache.org/jira/browse/KNOX-2128
>             Project: Apache Knox
>          Issue Type: New Feature
>          Components: KnoxShell
>            Reporter: Larry McCay
>            Assignee: Larry McCay
>            Priority: Major
>              Labels: kip-14
>             Fix For: 1.4.0
>
>          Time Spent: 10m
>  Remaining Estimate: 0h
>
> As described in [KIP-14|https://cwiki.apache.org/confluence/display/KNOX/KIP-14+-+KnoxShell+Improvements+for+Tabular+Data] , GroovyShell allows for the extension of the shell itself with custom commands. By providing commands for the management of DataSource configuration and use in SQL queries, we can simplify the interaction with SQL engines and databases for JDBC-based database access in KnoxShell to make for a powerful scripting and shell environment.
> DataSource and SQL commands really go hand in hand here and will be done at the same time.
>  * Datasources (:datasource|:ds) CRUD and select operations for a set of JDBC datasources that are persisted to disk in the user home directory
>  * SQL (:SQL|:sql) SQL query execution with persisted SQL history per datasource
> Examples:
> {code}
> :ds add test_ds connectStr driverClass authn_type
> {code}
> The above will add a new datasource to the KnoxShell environment with the name test_ds the required connectStr, the classname of the driver to use and either "basic" or "none" for authentication requirements.
> Invoking the above will actually result in a table rendering of all the currently configured datasources.
> When there are more than one, the following command must be used to select the datasource to use for SQL commands:
> {code}
> :ds select test_ds
> {code}
> This command selects the desired datasource by name and sets it as the select datasource context within the environment.
> If there is only one datasource in the environment, its selection is implied and the explicit selection isn't required with the above command.
> Once a datasource is selected the SQL command may be used to interact with the datasource.
> {code}
> :sql assign books
> {code}
> The above command will present the user with a Java Swing dialog to prompt for a SQL statement, challenge the user for authentication if required and leverage the KnoxShellTable JDBC builder API to submit the SQL query to the selected datasource and return the resultset in a KnoxShellTable variable in KnoxShell environment with the name books. It will also result in the rendering of the tablular result.
> The KnoxShellTable variable called "books" may then be used directly within the KnoxShell environment using the KnoxShellTable fluent API to select, filter, sort and join with other tables - as desired.
> {code}
> :ds remove test_ds
> {code}
> The above command invocation will remove the configured datasource and deselect it - if selected and persist the changes.
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)