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.
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
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:
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.
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.
The above command invocation will remove the configured datasource and deselect it - if selected and persist the changes.