Overview

The SQL Editor is a high-fidelity data-driven APP built using ONE DATA App Builder module. It gives the user the ability to execute SQL queries on multiple data source options (Data tables and Connections). The SQL Editor APP comes with direct and flexible query manipulation and execution, along with different integrated features.

The App presents two different editors supporting both regular SQL and PL/SQL with further queries (CRUD).

The SQL Editor currently supports the following database connection types:

• oracle
• postgres
• mssql server
• mysql
• impala

Furthermore, it supports the following datatable types:

• parquet/csv stored inside of ONE DATA
• oracle connection
• postgres connection
• mssql server connection
• mysql connection
• impala connection

Currently there is no support for:

• db2
• hana
• legacy datatables (in the list of datatables the type contains the word legacy), e.g. from file system connections or csv upload

This article contains insights first on the general structure and functionality of the front-end of the SQL editor, then explanations will be given about how it can be configured in the APP Builder.

SQL Editor Structure and Functionality

Basically, the custom component of the SQL Editor APP is built up from several smaller components, each of which can be configured independently to your needs. The following figure shows a possible structure of the SQL Editor:

SQL Query Editor

The query editor is the main component of the SQL Editor, allowing to write and execute queries that are based on the supported database connection (this will be shown in the configuration below).

The query editor interface supports the following functionalities:

1. Click on the info icon to see shortcuts that are supported by the editor, which can speed up your query writing process. The following figure presents some examples.
2. The text editor for SQL statements. See the next section about Helpful Features, which are implemented in the query editor for SQL statements in order to raise convenience of query writing.
3. The Refresh Schema button fetches the underlying schema if it has been changed, so all tables/columns can be properly used in the auto-completion. 
4. Import a text file with SQL code. The file can also be dragged into the text editor.
5. Download a ".sql" text file containing the query within the text editor. 
6. Button to execute the SQL code. A shortcut to execute queries is also available (Default is F8, see the Configuration: SQL Query Editor section to see how this can be changed).

Helpful Features

The following features are included in the SQL Query Editor.

Highlighted Syntax

Special keywords related to the SQL query language are highlighted in the SQL Query Editor.

Interpreted SQL Statements

The SQL Editor is able to locate and point to false statements as the user types in the SQL code without clicking the execute button.

Custom Execution

The user is able to execute parts of the code by selecting the target part of the code and then clicking on the "Execute Query" button.

Editor Assistance

As the user types in the SQL query, the editor suggests available options in a list with clickable alternatives. The user can access the list by pressing (CTRL+Space). If the list contains only one option, it is automatically inserted.

The list is automatically shown if the user is selecting a column from the input table using the syntax "inputTable." (the list is shown upon entering the period). 

The auto completion includes available schemas, tables, keywords and also a list of supported functions in alphabetical order.

Also, when hovering over a function with the cursor, additional information like a description and the link to the official documentation is shown.

Result Preview Component

The following figure shows the main component of the result preview section:

1. The query result table, it shows information regarding the result data, such as total number of rows, number rows per page, current page and column data type. It also allows actions such as filtering and sorting the result data along with the pagination.  
2. Download an excel file containing the queried result. This can be altered in order to query all retrieved data or only the first (shown) page of it. Further sheets can be added to the excel file as well, which can be configured independently (see the Configuration: Result Component for the SQL Editor section how to do this).

The user is also able to run a workflow saving the result data into a data table in the corresponding project, which is connected to the editor via its defined connection.

       3. Choose a suffix for the data table name.

       4. Run workflow and save data table.

       5. Navigate to the data table location. the result data is saved in the same project where the executed workflow is located (triggered by the workflow ID in the configuration).

Table Overview Supported by The Underlying Connection (Optional)

The following section shows an optional element, which is included in this application of the overall SQL Editor, as it can be very helpful for query writing. In this case, a table is shown which enlists all the existing tables in the defined database connection that can be queried with the SQL statements defined in the SQL Query Editor. In this example, the table names, creation dates, last modification dates, and comments are enlisted - but this can be configured and fine-tuned to the use case's needs!

PL/SQL Editor Structure and Functionality

As mentioned above, the APP also supports PL/SQL programming language via the PL/SQL Editor. 

The following figure shows the different editor components:

1. Click on the info icon to see shortcuts that are supported by the editor, which can speed up your query writing process.
2. The text editor for PL/SQL statements.
3. Import a text file with PL/SQL code. The file can also be dragged into the text editor.
4. Download a ".sql" text file containing the query within the text editor. 
5. Button to execute the SQL code. A shortcut to execute queries is also available (Default is F8, see the Configuration: PL/SQL Query Editor section to see how this can be changed).
6. In case the query contains a code that's supposed to deliver an output, it can be viewed in the output section.

APP Configuration for Single Components

The following sections will give insights on configuration possibilities that are available for all the components related to the SQL and PL/SQL editors in the APP Builder.

These will focus on the properties that are more component specific rather than classic APP Builder properties (like "id", "type", etc.).

For every component, a basic APP code is shown (which constructs the examples that have been shown in the screenshots above). Also, the specific parameters to that component are explained.

You may have to copy the different parts of the code below to have a complete app without the need to add any other lines.

Configuration: SQL Query Editor

{
    "id": "codeEditor",
    "type": "custom",
    "dontRefreshOnDependencyConfigUpdate": true,
    "renderer": "OdSqlEditor",
    "source": "Connection_Source",
    "sourceOptions": {
        "errors": {
            "passErrors": true
        }
    },
    "config": {
        "variable": "CustomSql_Variable",
        "enableRefreshSchema": true,
        "enableDownloadSql": true,
        "enableImportSql": true,
        "executeButtonLabel": "Execute",
        "hideDefaultQuery": true,
                "dbmsSpecificSchemaRetrievalProperties": {
                        "type": "ORACLE",
                        "defaultSchema": "ONEDATA"
                },
        "contextMenuConfig": {
            "dataSourceId": "dsForContextMenu",
            "variable": "contextMenuVariable",
            "menuItems": [
                {
                    "label": "Show Column Information",
                    "entities": [
                        "VIEW",
                        "TABLE"
                    ],
                    "query": "select col.column_name, col.data_type as type, col.nullable, col.data_default, com.comments from sys.all_tab_columns col inner join sys.ALL_OBJECTS t on col.owner = t.owner and col.table_name = t.object_name inner join sys.all_col_comments com on com.owner = col.owner and com.table_name = col.table_name and com.column_name = col.column_name where col.table_name = '${User_Content}' order by col.column_id"
                },
                {
                    "label": "Show Table Properties",
                    "entities": [
                        "VIEW",
                        "TABLE"
                    ],
                    "query": "select col.column_name, col.data_type as type, col.nullable, col.data_default, com.comments from sys.all_tab_columns col inner join sys.ALL_OBJECTS t on col.owner = t.owner and col.table_name = t.object_name inner join sys.all_col_comments com on com.owner = col.owner and com.table_name = col.table_name and com.column_name = col.column_name where col.table_name = '${User_Content}' order by col.column_id"
                }
            ]
        }
    }
}

• dontRefreshOnDependencyConfigUpdate: This flag deals with the internal communication between variables (used by the editor) and the surrounding APP. 

Always set this flag to "true", otherwise created SQL queries might get lost!

• renderer: The basic renderer for this component; always set to "OdSqlEditor".
• source: The important connection to a database on which the queries should be executed. See the Configuration: Connection for the Queries section as related.
• sourceOptions/errors/passErrors: This flag allows the passing of database-related errors to the APP in order to give more sophisticated error descriptions if a database error occurs. This should generally always be set to "true".
• config/
  • mode: The mode for the editor. The default value is "sql". For PL/SQL, this should be set to "plsql". The newly supported mode is "sparksql", which enables auto-complete for SparkSQL functions (the supported version of Spark is 2.4.5). This is also the recommended mode when working with data tables.
    
  • variable: The ID of the variable that firstly contains the default query for the editor, which is shown when the APP gets opened initially. Afterwards, when custom queries are created, this is the placeholder inside the APP for the created query and therefore is and can be used elsewhere.
  • enableRefreshSchema: If true, the button for refreshing the schema is shown.
  • enableDownloadSql: If true, the button and functionality for downloading written SQL queries from the editor are enabled.
  • enableImportSql: If true, the button and functionality for allowing to import SQL statements via upload are enabled.
  • executeButtonLabel: The button label of the query execute button.
  • hideDefaultQuery: If true, no default query is displayed in the editor when the APP is opened.
  • executeShortcutKey: This parameter is not shown in the snippet above, but it helps define a shortcut for executing the created SQL statements. For example, it can be set to "ctrl+e". Default button is F8. Please be aware that this only works if the defined shortcut is not already used and thereby overwritten by other software like the operating system.
  • dbmsSpecificSchemaRetrievalProperties/ : This schema is exclusively used for the auto-completion, thereby this schema must comply with the schema that is configured for the underlying ONE DATA connection defined for the SQL Editor.
    • type: The type of connected database (currently, only oracle is possible).
    • defaultSchema: The name of the schema that is to be applied.
  • contextMenuConfig/ : A context menu that allows to give further information about the query elements by right-clicking on the defined "entity" in the "menuItems" (possibilities are schemata, tables, views, and columns). Please note that you can always only select and right-click one of the "entities" that are possible for context menus - having multiple of them selected does not deliver any results. For example, you can configure context menus for tables and schemata, allowing you to get further info by right-clicking on a table or a schema, but not both at the same time.
    • dataSourceId: The ID of the connection on which the queries for the context menu are executed on.
    • variable: The local variable that is used for the context menu.
    • menuItems: Defines the menu items available for the context menu. The "label" defines the heading, the "entities" define the entities that can be selected and then right-clicked in the query editor (possibilities are "SCHEMA" , "TABLE", "VIEW" and "COLUMN"), and the "query" parameter defines the query triggered in the background by the context menu. An important role is played here by the '${User_Content}' variable: using this variable the selected statement part of the user in the SQL Query Editor gets directly injected into the query for the context menu. Please note here, that only single words or parts of the statement can be selected!

Configuration: Result Component for the SQL Editor

{
  "id": "tableDPO",
  "source": "Connection_Source",
  "sourceOptions": {
    "errors": {
      "passErrors": true
    }
  },
  "type": "table",
  "config": {
    "columnMinWidth": "200px",
    "download": {
      "type": "excel",
      "full": true,
      "paged": false,
      "excel": {
        "sheetName": "Query Result",
        "additionalSheets": [
          {
            "sheetName": "SQL Statement",
            "tableSchema": [
              {
                "name": "query",
                "index": 0,
                "type": "STRING"
              }
            ],
            "data": [
              {
                "$query": {
                  "value": "{{CustomSql_Variable}}"
                }
              }
            ]
          }
        ]
      }
    }
  }
}

• source: The important connection to a database on which the queries should be executed. See the Configuration: Connection for the Queries section as related.
• sourceOptions/errors/passErrors: This flag allows the passing of database-related errors to the APP in order to give more sophisticated error descriptions if a database error occurs. This should generally always be set to "true".
• config/
  • columnMinWidth: The minimal column width in the result table.
  • download/
    • full and paged: These two parameters alter the possibilities of downloading the respective table, in this case, the excel file. Therefore, either the currently shown data page or all data is downloaded.
    • excel/ : Here the excel download of the result table can be configured. Additional pages to the excel sheet can be added by adding objects to the "additionalSheets" array.
      • sheetName: The name for the main excel sheet.
      • additionalSheets/ : Every object contained here will add an additional sheet to the downloadable excel file. Thereby, the column titles and types are defined in the "tableSchema" array, while its content - so the filling of the rows - is determined by the "data" array.
        • sheetName: The name for the additional excel sheet.
        • tableSchema/
          • name: The heading of the column.
          • index: The index of the column, so the ordering of the columns can be defined here.
          • type: The type of the data that will be stored in this column, which is for example necessary for date formatting.
        • data/ : The data that should be included in the additional sheet. Alter the value of the variable accordingly. The key of the objects in this arrays defines the column to match, so in this case, "$query" will add the result of the defined query in the "query" column. In the example this is set to the query currently defined in the SQL Query Editor.

Configuration: Table Overview

As described above, the table overview is just an optional component that can help making writing queries easier, as available tables (respectively to the connected database) are eligible for querying. This is not an SQL Editor feature, but rather basic APPs. The following two code snippets show how the table in the screenshots above is created. In this example, the table names, creation date, modification date, as well as comments are selected and displayed.

{
  "id": "tableComments",
  "source": "ConnectionComment_Source",
  "type": "table",
  "config": {
    "columns": [
      {
        "name": "TABLE_NAME",
        "width": "25%",
        "label": "Used Tables"
      },
      {
        "name": "CREATED",
        "width": "20%",
        "label": "Created at"
      },
      {
        "name": "LAST_DDL_TIME",
        "width": "20%",
        "label": "Last modified"
      },
      {
        "name": "COMMENTS",
        "width": "35%",
        "label": "Comments"
      }
    ],
    "valueFilter": true,
    "rangeFilter": false
  }
}

Similarly to the SQL Editors, the Overview Table does also need a specific connection:

{
  "id": "ConnectionComment_Source",
  "origin": "connection",
  "config": {
    "schema": "table",
    "baseQuery": "SELECT TABLE_NAME, TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') AS CREATED, TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS LAST_DDL_TIME, COMMENTS FROM ((SELECT TABLE_NAME,COMMENTS FROM USER_TAB_COMMENTS) comments INNER JOIN (SELECT OBJECT_NAME, CREATED, LAST_DDL_TIME FROM ALL_OBJECTS) dates ON comments.TABLE_NAME = dates.OBJECT_NAME)",
    "connectionId": "e3d9d011-1ac4-432c-b784-804ee46d983b"
  }
}

Configuration: PL/SQL Query Editor

{
  "id": "codePLSQLEditor",
  "type": "custom",
  "renderer": "OdSqlEditor",
  "source": "Connection_Source",
  "dontRefreshOnDependencyConfigUpdate": true,
  "config": {
    "variable": "customPLSql_Variable",
    "mode": "plsql",
    "editorTitle": "Enter SQL Script or drag and drop code",
    "hideDefaultQuery": false,
    "enableDownloadSql": true,
    "enableImportSql": true,
    "executeButtonLabel": "Execute Script"
  }
}

• dontRefreshOnDependencyConfigUpdate: This flag deals with the internal communication between variables (which are used by the editor) and the surrounding APP. 

Always set this flag to "true", otherwise created SQL queries might get lost!

• renderer: The basic renderer for this component; always set to "OdSqlEditor".
• source: The important connection to a database on which the queries should be executed. See the Configuration: Connection for the Queries section as related.
• sourceOptions/errors/passErrors: This flag allows the passing of database-related errors to the APP in order to give more sophisticated error descriptions if a database error occurs. This should generally always be set to "true".
• config/
  • variable: The ID of the variable that firstly contains the default query for the editor, which is shown when the APP gets opened initially. Afterwards, when custom queries are created, this is the placeholder inside the APP for the created query and therefore is and can be used at other occasions.
  • mode: The mode for the editor. The default value is "sql". For PL/SQL, this should be set to "plsql".
  • editorTitle: The title of the editor component.
  • hideDefaultQuery: If set to true, no default query is displayed when the APP is started initially.
  • enableDownloadSql: If true, the button and functionality for downloading written SQL queries from the editor are enabled.
  • enableImportSql: If true, the button and functionality for allowing to import SQL statements via upload are enabled.
  • executeButtonLabel: The button label of the query execute button.
  • executeShortcutKey: This parameter is not shown in the snippet above, but it let's you define a shortcut for executing the created PL/SQL statements. For example, it can be set to "ctrl+e". Default button is F8. Please be aware that this only works if the defined shortcut is not already used and thereby overwritten by other software like the operating system.

Configuration: Result Component for the PL/SQL Editor

{
  "id": "plSqlOutput",
  "type": "custom",
  "renderer": "OdDatabaseStatementExecute",
  "config": {
    "variable": "customPLSql_Variable",
    "enablePrintOnDb": true,
    "numPrintOutputLinesLimit": 42,
    "databaseType": "ORACLE"
  },
  "source": "Connection_Source"
}

• renderer: The basic renderer for this component; always set to "OdDatabaseStatementExecute".
• config/
  • variable: The ID of the variable that firstly contains the default query for the editor, which is shown when the APP gets opened initially. Afterwards, when custom queries are created, this is the placeholder inside the APP for the created query and therefore is and can be used elsewhere.
  • enablePrintOnDb: If set to true, the print commands entered in the PL/SQL text editor are also shown on the connected database. This flag is also required as "true", when the output is to be shown inside the APP! 
  • numPrintOutputLinesLimit: The maximum number of printed lines can be limited with this parameter. If this parameter is not set, all lines will be printed.
  • databaseType: The database that is connected with the PL/SQL editor. This is important, as different database languages are available, which might differ in some language details.
• source: The important connection to a database on which the queries should be executed. See the Configuration: Connection for the Queries section as related.

Configuration: Connection for the Queries

The following snippet shows an example, which can be used for both the SQL Editor and PL/SQL Editor. The most important things here are, that the variable for the "CustomSql_Variable" is defined in the "$baseQuery" and that the "connectionId" is set to the underlying workflow. The base query is defined to show what is persistent in the "CustomSql_Variable" when the SQL Editor is opened initially.

{
  "id": "Connection_Source",
  "origin": "connection",
  "config": {
    "schema": "table",
    "errorFormat": "code_and_message",
    "$baseQuery": {
      "value": "{{CustomSql_Variable}}"
    },
    "baseQuery": "",
    "connectionId": "e3d9d011-1ac4-432c-b784-804ee46d98"
  }
}

Versioning Information and Dependencies

• sql editor: 1.8.0
• database-statement-execute: 0.2.1
• lsp server: 2.1.0