VisualSQLEditorFunctionalSpecification

Visual SQL Editor Functional Specification

Contents


Introduction

The Visual SQL Editor (VSE) supports the visual construction of SQL queries. It can also help experienced users by eliminating possible typographic errors when entering SQL statements.

The initial version was loosely based on the Microsoft Query and View Designer, but it has evolved since then.

The VSE supports a subset of SQL that can be rendered visually. SQL that is outside the supported scope is still supported in the editor (for text editing), but the visual editing capabilities are disabled. In the current version, only SELECT statements are supported. Other statements (INSERT/UPDATE/DELETE) are under consideration for future releases.

Relationship to the Netbeans and VisualWeb Data Architecture

The VSE is used to produce or modify a SQL query. In the VisualWebSuite, this is used to edit the "command" property of a RowSet which has been added to a Visual Web Project for purposes of # databinding.

The VSE module is part of the db cluster, in db/visualsqleditor. When the API is approved, the VSE will be available to other Netbeans modules that may need a SQL editor. See the API section for more information on this.


Visual SQL Editor Interface

The VSE consists of four panes, positioned vertically on the screen:

  • Diagram -- a visual representation of a SQL query
  • Grid -- a spreadsheet representation of a query
  • SQL -- a text representation
  • Results -- a spreadsheet representation of a query result

Image:VisualSQLEditor_VisualSQLEditorFunctionalSpecification.png

The Diagram Pane and Grid Pane are collectively referred to as the visual panes. The Diagram Pane, Grid Pane, and SQL Pane are collectively referred to as the input panes.

The user can drag and resize all the four panes in the query editor.

The general usage model is that queries can be entered and modified in any of the input panes, which will be kept in synch with each other when possible. This is discussed in further detail below. The result pane will be used to display query output if desired.

Only the SQL text is persisted or accessible outside the VSE. When an instance of the VSE starts up, the initial SQL query is parsed and the results used to populate the visual panes.

Diagram Pane

The Diagram pane provide an entity-relationship style graphical representation of a SQL query. Nodes of the graph represent tables in the database. Arcs in the graph represent relationships between tables.

Image:DiagramPane_VisualSQLEditorFunctionalSpecification.png

Nodes include various information about the associated database table:

  • column names (or expressions)
  • primary and foreign keys
  • indication of which columns are selected for output in the query

Join Arcs represent relationships between tables, based on database schema, constraints, and the query. Arcs will be added automatically by the Query Editor when appropriate -- i.e., when a relationship is inferred. The relationships will be inferred when a table is added to the query, or the query is modified to express a relationship. Selecting a Join Arc will bring up its properties: the tables, join columns, and join operator.

If the join that is inferred by the VSE is not the correct one, the user can modify it by modifying its join arc properties.

Certain aspects of the Diagram pane will be fixed (column names, primary and foreign keys) while others are changeable (add/remove relationships, select/unselect columns for retrieval in a query).

Tables and relationships will have properties which can be viewed (and in some cases edited). Other objects like columns may have properties.

The query in the Diagram pane can be modified and extended. In particular, additional tables can be added, and the existing tables and relationships can be modified. Tables can be added via popup menu such as "Add Table" (allows one or more tables to be added) or by dragging them from the Services tab.

A table or view may be removed from a query by selecting the table or view, right-clicking, and choosing Remove Table, or press the Delete key or Edit > Delete from the main menu.

Some operations that can be performed on the Diagram Pane:

  • View the types of columns included in the query. A key icon indicates that the column is a primary key. A key with an arrow indicates that the column is a foreign key, which links an item in a row back to a primary key in another table.
  • Add or remove columns from the query. A column is added by selecting the check box and is removed by clearing the check box.
  • Add a Group By clause by right-clicking the pane and choosing Group By. Group By groups the results by row.
  • Add additional tables, via the Add Table popup menu, or by dragging table nodes from the Services tab.

If the SQL query contains code that is not supported by the diagram pane, the diagram pane becomes blank, but user may still be able to execute the query. A proper error message is displayed.

Grid Pane

The Grid pane provides a spreadsheet representation of a SQL query. Rows in the Grid pane correspond to columns of the database. Only those columns that are selected for output, or otherwise appear in SQL query, will be displayed in the Grid pane. Columns of the Grid pane will contain information relevant to individual database columns, such as whether the column is selected for output, what criteria are applied to the column in the query, and whether the column is used for sorting or grouping.

Image:GridPane_VisualSQLEditorFunctionalSpecification.png

The Grid pane may be edited directly, and is in turn updated if either of the other panes changes

Any expressions added to the query (e.g., a function on a database column) are listed here.

Some operations that can be performed on the grid pane:

  • View the columns in the query and the table each column belongs to.
  • Enter an alias for a field in the Alias field.
  • Add columns to the query by selecting the Output column or remove columns from the query by clearing the Output column. Once user removes a column, he/she must select the column in the diagram pane or modify it in the SQL pane to add the column back to the query.
  • Sort the fields in a column by selecting the Sort Type and specifying the Sort Order.
  • Add criteria to a column by right-clicking on the column and choosing Add Query Criteria.

Changes user makes to the Grid Pane appear in the Diagram Pane and SQL pane.

SQL Pane

The SQL pane contains the current SQL query. This is either be generated from the contents of the visual panes, or typed by the user. The SQL pane will support arbitrary text expressions, since they will be passed through to the underlying database engine. If the SQL pane is modified directly, the visual panes will be updated to reflect the resultant query if possible.

Image:SqlPane_VisualSQLEditorFunctionalSpecification.png

Some operations that can be performed in the SQL pane:

  • Type and edit SQL code directly.
  • Test the SQL query by right-clicking and choosing Run Query. A representation of the results appear in the results pane.
  • Parse the SQL query by right-clicking and choosing Parse Query. If there are any errors an error message is displayed.

If the SQL pane contains code that is not supported by the diagram pane or the grid pane, the diagram and grid panes become blank, but user may still be able to execute the query.

Test Execution and Results Pane

The Results pane can be used to display the results of query execution. This behavior will be invoked by explicitly executing the query in the designer. This is applicable only if the current query is a SELECT query.

At execution, any needed run time parameters will be prompted for. If the datatype is not supported for test execution (e.g., ARRAY), the test execution will not run.

The Results pane is a spreadsheet-style display, in which the rows represent rows returned by the SELECT query, and columns represent columns selected in the query. The Results pane will display some small number of rows, with the option of retrieving all the rows. If the query is parameterized, QE will prompt for appropriate values for each parameter, before executing the query.


Usage Model

At any time, the Visual SQL Editor will have a "current query", which is represented in all three input panes. The three input panes will be kept in synch with each other. Any of them can be used to define or modify the query, with the others being updated to reflect that.

When changes are made to either of the visual panes, the SQL pane will be updated automatically, and vice versa.

Two-Way Editing

As mentioned, the query can be modified in any of the three input panes, with the other panes kept in synch.

If the query is modified in the SQL pane, it will need to be parsed by the VSE. A parser based on JavaCC (Java Compiler Compiler) is currently used for this purpose.

If the VSE is unable to parse the query (either because it is not valid, or because it exceeds the capabilities that can be represented in the visual panes) the VSE reports this, and disables the visual panes.

Parsing of the query will occur only when the parser is explicitly invoked. This will be triggered by a context menu item within the SQL pane or when the SQL command is first brought into the query editor.

API

The current API was adopted when the VSE was a part of the closed-source Java Studio Creator architecture. As the VSE is moved into the open-source Netbeans DB cluster, we propose the following API, which includes two classes and an interface. This is described in more detail in a separate document.

For now, the only user of this API will be the dataconnectivity (RowSet) support in the visualwebsuite. In future, other modules can make use of it.

Note that a client that uses this API will have a dependency on the VisualSQLEditor module, since it needs to instantiate an interface and invoke classes. In order to allow a client to use the VSE without such a dependency (necessary for the DB Explorer), we will need to expose and implement an appropriate SPI. Such an SPI is under developement, but will be reviewed separately.

package org.netbeans.modules.db.sql.visualeditor.api;

VisualSQLEditorFactory

/**
 * Factory class for creating VisualSQLEditor instances.
 * @author Jim Davidson
 */
public final class VisualSQLEditorFactory {

    /**
     * Creates and returns a new VisualSQLEditor.
     *
     * @param dbconn the DatabaseConnection
     * @param statement the initial SQL query to be loaded into the editor
     * @param metadata metadata cache maintained by the client, or null.  If null, the 
     * VisualSQLEditor will fetch and manage its own metadata, using the DatabaseConnection
     * @return the new VisualSQLEditor instance
     *
     */
    public static VisualSQLEditor createVisualSQLEditor(DatabaseConnection dbconn, String statement,
                                                        VisualSQLEditorMetaData metadata)
    {    }
}

VisualSQLEditor

:*
 * Class to encapsulate a visual SQL editor.
 * @author Jim Davidson
 */
public final class VisualSQLEditor {

    /**
     * Property corresponding to the SQL statement; for listening.
     */
    public static final String        PROP_STATEMENT;

    /**
     * Create and open the QueryBuilder that backs up this VisualSQLEditor instance.
     * @return the new QueryBuilder component
     */
    public Component open() {}

    /**
     * Returns the current value of the SQL statement
     * @return the statement
     */
    public String getStatement(){    }

    /**
     * Sets the value of the SQL statement
     * @param statement - the new statement value
     */
    public void setStatement(String statement) {    }

    /**
     * Adds a property change listener.  The only property of interest is PROP_STATEMENT,
     * which contains the SQL query.
     * @param listener The listener to add.
     */
    public void addPropertyChangeListener(PropertyChangeListener listener){   }

    /**
     * Remove a PropertyChangeListener from the listener list. 
     * @param listener - the listener to remove.
     */
    public void removePropertyChangeListener(PropertyChangeListener listener){    }
}

VisualSQLEditorMetaData

/**
 * Implements a cache for metadata that is supplied to the Visual SQL Editor
 * @author Joel Brown, Jim Davidson
 */
public interface VisualSQLEditorMetaData {

    /**
     * Returns the schemas that are included in this data source.
     * Used during Add Table and similar operations.
     * @return the List of schemas
     */
    public List<String> getSchemas() ;

    /**
     * Returns the tables (and views) in the specified schema
     * @return the List of tables/views, each in the form of a List<schema, table>
     */
    public List<List<String>> getTables() throws SQLException ;

    /****
     * Returns the primary key columns for the given schema/table combination.
     * @return the List of columns
     */
    public List<String> getPrimaryKeys(String schema, String table) throws SQLException ;

    /***
     * Returns the imported keys for the given schema/table.
     * @return the List of imported Keys.  Each key is a List of the form
     * <foreign schema, foreign table, foreign column, primary schema, primary table, primary column>
     */
    public List<List<String>> getImportedKeys(String schema, String table) throws SQLException ;

    /***
     * Returns the exported keys for the given schema/table.
     * @return the List of exported keys.  Each key is a List of the form
     * <foreign schema, foreign table, foreign column, primary schema, primary table, primary column>
     */
    public List<List<String>> getExportedKeys(String schema, String table) throws SQLException ;

    /**
     * Returns the columns in the specified schema/table.
     * @return a List of column names.
     */
    public List<String> getColumns(String schema, String table) throws SQLException ;

}


Technical Details

SELECT statements to be created may look like this:

SELECT [ ALL column|expression[[[,... | [,...]]
FROM table alias [[[,... | [,...]]
[[ <tt>LEFT | RIGHT | FULL} OUTER} JOIN table
{ON condition | USING(col1,col2,...)} ]
[[[WHERE{condition...} | [ WHERE {condition ...}]]
[[[GROUPBYColumn[[,... | [ GROUP BY column ,... ]
[[[HAVINGCondition[[,... | [ HAVING condition ,... ]
[[DESC | [ ORDER BY {column} ASC [[[,... | [,...]] ]

The current functionality is listed below. Items marked "target" may not be implemented.

Functionality Example Notes
One table select, columns only select column, [[[,column... | [,column ...]] from table Can also use "*" instead of listing all columns. Modifiers after the "select": ALL / DISTINCT (default is ALL)
Add expression(s) to list select column, expression from table There is no validation of expressions - what gets entered passes directly to the database.
There must be one or more columns/expressions.
Target: special cases are the aggregate functions: AVG / MAX / MIN / SUM / COUNT.
Add a Table or View ... from table [[[,Table... | [, table ...]] There must be one or more tables listed.
When more than one table is selected, all the selected tables will be added to the graph and displayed in the diagram pane and the relationships (connecting line) will also be shown, if any.
When adding a table, the VSE assumes a connection if any foreign key constraints exist.
Users can alter the join type to be one of INNER, LEFT OUTER JOIN, RIGHT OUTER JOIN, or NONE (aka CROSS JOIN or Cartesian product).
Where clause conditions Conditions may be assigned to any columns in any table:
* column operator value (value is entered in query editor)
* column operator ?, where the ? parameter is to be assigned at run time.
* operator may be: < > <= >= <> = LIKE, IN
As part of the "Add Query Criteria" (which could be invoked by right clicking on a row of grid pane and selecting "Add Criteria") the user could specify "IN", "LIKE" and "<>" (not equals).
In the Grid Pane now there is an additional column to specify the order of the criteria.
UNION select not supported
ORDER BY users can specify which columns to order by, ascending or descending, and the order (which column first, second, ...)
GROUP BY users can specify which column(s) to group by
HAVING supported - see where clause conditions


Adding Tables and Views to a SELECT statement

Tables and views can be added to the query by:

  1. Dragging/dropping a table node from the Service's tab's DatabaseConnections to the Diagram Pane
  2. Selecting the context menu choice, "Add Table or View"
  3. Altering the SQL command by manually typing in the name.

For cases 1 and 2, the VSE attempts to link the new table to existing table(s) in the query via any known foreign keys. If none exist, the table will not be linked by default and the user must specify.

Views will behave just like tables whenever possible. Caveat: Database metadata describing views often don't provide the same detail as table meta data does, so default behavior (e.g., defaulting join columns, etc.) may differ compared to if you used actual tables.

Target: have a contextual menu choice for an existing table for "get foreign key tables". This will present a list of all tables that the table references and the user can pick one or more of the referenced tables.

Joining Tables

On adding table(s), the VSE will attempt to join to existing tables using foreign key metadata.

Table joins can be created manually - e.g., dragging a column of one table onto the column of another table in the graphical representation. If the user drags from A.x to B.y:If there's not already a WHERE clause, creates one of the form

 WHERE A.x = B.y

If there's already a WHERE clause, adds on a conjunct of the form

WHERE    ....AND A.x = B.y

An edge representing the newly created join will be drawn between the two tables. User can delete this join either by clicking the join line in the graph pane and hitting the “DELETE” key or from “Edit-Delete” menu.

Table Aliases

The easiest way to add a table alias (correlation name) is through the Properties editor. When the user selects a particular table and then types the table alias in the 'Table Alias' field and hits enter, the Graph pane, Grid Pane and SQL Pane are updated with the alias.

Note that an alias can also be added by typing it into the text pane, by typing a query that introduces it. E.g., "select name from person p".

As per the SQL 92, SQL syntax requires users to supply names for elements such as tables, aliases, views, cursors, and columns when they define them. SQL statements must use those names to refer to the table, view, or other element.

Logging

Logging to the Netbeans log file is provided, using the Java.util.logging.Logger() facility. This is controlled with a command-line property that can be set in the netbeans.conf file:

-J-Dorg.netbeans.modules.db.sql.visualeditor.querybuilder=FINEST

A11Y and I18N

Currently the VSE supports minimal accessibility ( a11y ). When the Query Editor is displayed the focus is in the SQL Pane. The user can add / delete / modify the SQL string in the SQL Pane. The user could move around all the panes using Alt-1 ( Graph Pane ), Alt-2 ( Grid Pane), Alt-3 (SQL Pane) and Alt-4 ( Result Pane). The popup menus can be invoked using Shift-F10. There is limited support to traverse through the tables / joins in the Graph Pane.

Runtime Parameters within the Query

JDBC allows values to be bound within a query at runtime, by including parameters in the query string.. E.g.,

   select * from CUSTOMER_TABLE where name = ?

Values for these parameters must be bound at runtime, e.g.,

   stmt1.setString(1, "Adam")

In a visual web application, this parameter binding must be written by the user, in the user's Java user code.

Parameterized queries can also be executed within the VSE, with the context menu item "Run Query". When such a query is run, the VSE displays a dialog box that prompts for values for each of the parameters, then binds the values and executes the query.

This feature depends on the information provided by the database driver, notably the ParameterMetaData results that are returned from PreparedStatement.getParameterMetaData()

Add Query Criteria Dialog Box

The Add Query Criteria dialog box enables user to refine the SQL query by creating or adding query criteria to the WHERE clause of the SQL statement. A query criterion contains the column on which to define the criterion, a comparison operator, and a value or parameter. After the user creates a criterion, it appears in the WHERE clause in the SQL pane. To open this dialog box, select a column in the grid pane of the Query Editor, right-click and choose Add Query Criteria.

This dialog box has the following fields and tabs:

Column Name. Displays the name of the column for which to define the query criteria.

Comparison. Use this dropdown to set the comparison operator to use in the criteria. The IDE supports the following:

Standard mathematical operators, including: = equal, < less than, <= less than or equal to, > greater than, >= greater than or equal to, and <> not equal.

IN: Use the IN operator to match one or more specific values. For example, type ('Smith', 'Jones') to retrieve columns that contain the names Smith or Jones.

LIKE: Use the LIKE operator to search for patterns. Enclose the patterns in single quotes. One can use the % sign as a wildcard both before and after the pattern. For example, type 'S%' to search for values beginning with the letter S, such as Smith, or '%th%' to find all values that contain the letters th, such as Smith or Cathay.

Value. Use this field to specify the static value to use in the comparison, such as 8 for a numeric field or 'Smith' for a text field.

Parameter. This field is used to specify a JDBC parameter from user program. Per the JDBC standard, a question mark is used as a placeholder for the value. Parameters allow the same SQL statement to be used with different data values. At runtime, the application prepares the query with the parameter and fills the parameter with a value before the query is executed. When the query is executed, the data values passed into the parameter are substituted for the parameter placeholder.

Future Upgrades

Certain features are under consideration for inclusion in future releases. Some of these are outlined below.

INSERT Statement Support

Inserts will support a single table only.

   insert into tablename ( column  ... ) values ( expression  ,...  )

Users can pick the column((s). For each column, the user can enter an expression or specify run-time parameter substitution (?)

By default, all column names will be included within the insert column list.

target: if the metadata for the table provides it, auto generated (e.g., identity) columns will not be listed.

User can insert one or more rows in a database in a single operation by using an INSERT query.

When user creates an INSERT query, the following need to be specified:

The table to INSERT rows into The columns whose contents user wants to add The value or expression to use to INSERT the individual columns

Note: User cannot undo the action of executing an INSERT query. A dialog box needs to be displayed cautioning the user and asking to back up the data before executing the query.

To create an INSERT query, add the table to be updated to the diagram pane.

Note: If more than one table is displayed in the diagram pane, the user should be prompted to choose the name of the table to update.

Convert the default query (SELECT) to an INSERT query by choosing Change Type from the popup menu of the diagram pane and then choosing INSERT.

The user should define the data columns to INSERT by adding them to the query. Columns will be INSERTed only if user adds them to the query. Otherwise default value of NULL is inserted in to the row.

The grid pane will look as follows.

       |        |           |
Column | Table  | New Value | 
       |        |           |

In the New Value column of the grid pane, the user can enter the INSERT value for the column. New value can be literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column the user is updating. Query Editor will not check the validity of the data. If the user provides invalid data there could be some unintended consequences. ( May be a warnig box ? )

When user executes an UPDATE query, no results are reported in the Results pane. Instead, a message appears indicating how many rows were changed.

UPDATE Statement Support

Updates will support update to a single table only based on values within only that table.

   update tablename set column = expression  , ...  where column = expression  , ...

By default:

  • the columns to update will be all non-primary key columns compared to "?"
  • the where clause will have any primary key(s) compared to "?".

Additional where clause conditions may be added.

User can change the contents of multiple rows in one operation by using an Update query.

When user creates an Update query, the following need to be specified:

The table to update The columns whose contents user wants to update The value or expression to use to update the individual columns Search conditions to define the rows to update

Note: User cannot undo the action of executing an Update query. A dialog box needs to be displayed cautioning the user and asking to back up the data before executing the query.

To create an Update query: Add the table to be updated to the diagram pane.

Note: If more than one table is displayed in the diagram pane, the user should be prompted to choose the name of the table to update.

Convert the default query (SELECT) to an Update query by choosing Change Type from the popup menu of the diagram pane and then choosing Update.

The user should define the data columns to update by adding them to the query. Columns will be updated only if user adds them to the query.

The grid pane will look as follows.

       |        |        |           |          |                |      | 
Column | Output | Table  | New Value | Criteria | Criteria Order | Or...| Or...
       |        |        |           |          |                |      | 

In the New Value column of the grid pane, the user can enter the update value for the column. New value can be literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column the user is updating. Query Editor will not check the validity of the data. If the user provides invalid data there could be some unintended consequences. e.g. The Query Editor will not check that a value fits within the length of the column the user is updating. If provided value is too long, it might be truncated without warning. For example, if a name column is 10 characters long but user specifies an update value of 18 characters, the last 8 characters might be truncated.


User can define the rows to update by entering search conditions in the Criteria column.

If user does not specify a criteria condition, all rows in the specified table will be updated.

Note: When user adds a column to the grid pane for use in a search condition, the Query Editor also adds it to the list of columns to be updated. If user wants to use a column for a criteria condition but not update it, he/she should clear the output check box next to the column name.

When user executes an UPDATE query, no results are reported in the Results pane. Instead, a message appears indicating how many rows were changed.

DELETE Statement Support

Deletes will support a delete from a single table only.

 delete from tablename where column = expression  ,...

By default, the where clause will have any primary key(s) compared to "?".

Additional where clause conditions may be added.

User can delete one or more rows in one operation by using a DELETE query. In a DELETE query, user needs to specify the database table to delete rows from and the search criteria to define the rows to be deleted.

The grid pane will look as follows.

       |       |          |                |       |
Column | Table | Criteria | Criteria Order | Or... | Or...
       |       |          |                |       |

Note: The user will not be able to undo the action of executing a Delete query. As a precaution, warning should be displayed asking user to back up data before executing a Delete query.

To create a Delete query: add the table to delete rows from to the Table Operations pane. Only one table should be in the diagram pane.

Convert the default query (SELECT) to an DELETE query by choosing Change Type from the popup menu of the diagram pane and then choosing DELETE.

Enter search conditions to define the rows to be deleted. If user does not specify a search condition, all rows in the specified table will be deleted.

When user executes a DELETE query, no results are reported in the Results pane. Instead, a message appears indicating how many rows were deleted.

Stored Procedure Support

Stored procedure execution is database dependent. We will attempt to follow consistent SQL92 or SQL99 syntax and try to avoid special db specific coding.

Stored procedures have parameters that may be of mode input or output or both. They may also return one or more result sets.

Capabilities may also be driver dependent. Support will be determined at design time.

Updated Graphing Package

The Diagram pane currently uses the jgraph package to represent the query graph. The plan is to convert this to the Netbeans Visual Library . This should have minimal effect on functionality.

Others (e.g. DDL, functions, ...)

Not currently supported.

History

}}small Query Editor Functional Specification

Version 1.0. 7/21/03 - initial version - Jim Davidson

Version 2.0 1/26/03 - revised for release 1 EA/FCS- Joel Brown

Version 3.0 10/25/04 – updated with new features / customer RFEs - Sanjay Dhamankar

Version 3.1 01/28/05 – updated with multiple AND/OR functionality, aggregate functions and INSERT/UPDATE/DELETE - Sanjay Dhamankar

Version 4.0 03/12/07 – updated for inclusion into Visual Web Pack and Netbeans, and moved to Netbeans Wiki - Jim Davidson }}

Not logged in. Log in, Register

By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo