SQL Query Builder Component API

← Back to SQL Development Tools page

Introduction

The SQL Query Builder is a component for visually editing SQL DML statements. It can be consumed by other UI components such as editors, dialogs and wizards. As input, it can accept .sql files created by the DTP SQL File Editor or it can accept SQL statements passed in the form of strings.

The API described here is currently provisional and is subject to change until this notice of provisional status is removed.

CVS Location

The SQL Query Builder is located in the datatools part of the Eclipse CVS repository

Host: dev.eclipse.org

Repository path: /cvsroot/datatools

The SQL Query Builder plugin is in

org.eclipse.datatools.sqltools/plugins/org.eclipse.datatools.sqltools.sqlbuilder

There is also an examples plugin in

org.eclipse.datatools.sqltools/examples/org.eclipse.datatools.sqltools.sqlbuilder.examples

For up to date information about how to use the SQL Builder Editor, see

org.eclipse.datatools.sqltools/prototype/devnotes/sqlbuilder_notes.txt

SQLBuilder Component and Example Consumers

SQLBuilder component

The main component class is

org.eclipse.datatools.sqltools.sqlbuilder.SQLBuilder

This class is a graphical editing component which can be used to create new SQL statements or edit existing statements. The SQLBuilder component can be hosted in an editor, a dialog or a wizard. Examples of the SQLBuilder class hosted in a dialog and an editor are described below. These examples demonstrate the API classes described in this document.

The sequence of function calls required to host the SQLBuilder is as follows:

_sqlBuilder = new SQLBuilder();

_sqlBuilder.addContentChangeListener(this);

_sqlBuilder.setInput(_editorInput);

_sqlBuilder.createClient(editComposite);

where _sqlBuilder is a variable of type SQLBuilder, this is the containing class, _editorInput is the input and editComposite is the composite which will contain the SQLBuilder.

Depending on the consumer, these function calls might not all be called in a single sequence, but be split into groups called from other functions. For example if the consumer is an EditorPart, setInput should be called during the editor's init(IEditorSite, IEditorInput) method and createClient should be called during createPartControl(Composite). See Example SQLBuilder editor below.

Constructor

The constructor takes no parameters.

addContentChangeListener(IContentChangeListener)

addContentChangeListener should be passed an object which implements the interface

org.eclipse.datatools.sqltools.sqlbuilder.IContentChangeListener.

This interface has a single method notifyContentChange which is invoked when the content model being edited by the SQLBuilder changes. This allows the client to be notified of changes to the SQL statement being edited so that it can update its own dirty status and handle user requests to save the statement.

setInput(ISQLBuilderEditorInput) throws PartInitException, ParseException

Passes an EditorInput to the SQLBuilder. See Input Types below.

Throws a PartInitException if the SQLBuilder cannot be initialized. The caller should not continue to open the SQLBuilder if this exception is thrown.

Throws a org.eclipse.datatools.sqltools.sqlbuilder.ParseException if the SQL statement being passed to the SQLBuilder could not be parsed. If this happens, then it is up to the caller to decide whether to continue opening the SQLBuilder or not. If the caller does continue, then the SQLBuilder will be opened with a new empty statement. The statement type is determined by the SQL passed to the SQLBuilder. If the SQL begins with SELECT, INSERT, UPDATE or DELETE, then an empty statement of the appropriate type will be created. If not, then a new empty SELECT statement will be created.

createClient(Composite)

Constructs the SQLBuilder UI inside the Composite passed as a parameter.

Other functions

A further two functions are useful for editors. These are:

setLoadOnConnection(boolean);

connectIfNeeded(IWorkbenchPart)

The problem these functions are designed to solve is that of the workbench opening when its saved state includes an instance of an editor which hosts the SQLBuilder. When the Workbench opens it will try to open the editor and the SQLBuilder will need to make a connection to a database. setLoadOnConnection tells the SQLBuilder to delay loading the input until a database connection has been obtained. connectIfNeeded should be invoked as soon as possible after the editor has initialized inside the Workbench.

setLoadOnConnection should be called before calling setInput and connectIfNeeded should be called after the editor has been initialized in the Workbench. In the example editor described below, setLoadOnConnection is called during setFocus.

Input Types

SQLBuilder.setInput takes an org.eclipse.datatools.sqltools.sqlbuilder.ISQLBuilderEditorInput parameter, for which three implementations are provided:

org.eclipse.datatools.sqltools.sqlbuilder.SQLBuilderFileEditorInput

org.eclipse.datatools.sqltools.sqlbuilder.SQLBuilderStorageEditorInput

org.eclipse.datatools.sqltools.sqlbuilder.SQLBuilderEditorInput

These input types are EditorInputs supplemented with two extra pieces of information required by the SQLBuilder - connection information and OmitSchemaInfo (see Omit Current Schema ). Connection information is a reference to a database connection profile. It is represented either by the SQLEditor class ISQLEditorConnectionInfo or IConnectionProfile, depending on the editor input class used. OmitSchemaInfo information specifies whether the current schema name should be omitted from SQL generated by the SQLBuilder. It is represented by the SQLBuilder interface and class org.eclipse.datatools.sqltools.sqlbuilder.model.IOmitSchemaInfo and OmitSchemaInfo.

The contents of the editor input are the SQL statement. Connection information and OmitSchemaInfo are properties associated with the input.

In inputs passed to the SQLBuilder, the ConnectionInfo part must be populated with a reference to a connection profile, but the OmitSchemaInfo part may be empty. If it is empty, a new OmitSchemaInfo object will be created using the Eclipse Preference settings defined for the SQL Builder.

File inputs: SQLBuilderFileEditorInput

SQLBuilderFileEditorInput sub-classes FileEditorInput. Connection Information and OmitSchemaInfo information are stored as Eclipse persistent properties associated with the .sql resource.

For reference, after creating a .sql file in the SQL File Editor and editing it in the SQL Query Builder, these properties can be found in

.metadata\.plugins\org.eclipse.core.resources\.projects\<proj_name>\.indexes\properties.index

For examples of file inputs, see the Example Dialog or Editor described below.

IStorage based inputs: SQLBuilderStorageEditorInput

SQLBuilderStorageEditorInput is a class designed so that the SQL Builder can accept non file-based inputs. It allows clients to create SQLBuilder inputs from strings serialized to XMLMementos.

SQLBuilderStorageEditorInput persists itself as an XMLMemento which is an XML document containing the SQL Statement, ConnectionInfo and OmitSchemaInfo. The XMLMemento can be serialized or loaded to/from a string.

The class

org.eclipse.datatools.sqltools.sqlbuilder.util.SQLBuilderEditorInputUtil

contains utility functions for creating SQLBuilderStorageEditorInput objects from strings and files, saving them to XMLMementos and serializing XMLMementos to strings.

See the Example Dialog below for examples of how SQLBuilderStorageEditorInput and SQLBuilderEditorInputUtil are used.

String based inputs: SQLBuilderEditorInput

SQLBuilderEditorInput allows users to create editor inputs from SQL strings or to create new SQL statements. It is the most flexible of the SQLBuilder editor input classes.

• SQLBuilderEditorInput has constructors to allow IConnectionProfile objects to be passed as well as ISQLEditorConnectionInfo.
• SQLBuilderEditorInput has constructors to allow existing SQL statements to be passed in to the SQLBuilder as strings. This is done through the SQLStatementInfo object which is passed to a SQLBuilderEditorInput constructor.

• SQLBuilderEditorInput has constructors to allow you to specify the dialect of a statement being passed. SQLStatements are passed to the SQLBuilderEditorInput constructors as SQLStatementInfo objects (which implement ISQLStatementInfo) and SQLStatementInfo has an optional SQLDialectInfo object which allows you to specify the dialect. If the statement's dialect is different from that of the ConnectionInfo or ConnectionProfile used in the SQLBuilderEditorInput, the DialectInfo should be populated. Otherwise the SQLDialectInfo part of SQLStatementInfo should not be set.

• SQLBuilderEditorInput has constructors to allow new statements to be created in the SQLBuilder. The constructors with an int statementType parameter allow this. The statementType parameter must be one of the STATEMENT_TYPE constants defined in org.eclipse.datatools.modelbase.sql.query.helper.StatementHelper:

StatementHelper.STATEMENT_TYPE_SELECT,

StatementHelper.STATEMENT_TYPE_INSERT,

StatementHelper.STATEMENT_TYPE_UPDATE,

StatementHelper.STATEMENT_TYPE_DELETE,

StatementHelper.STATEMENT_TYPE_FULLSELECT,

StatementHelper.STATEMENT_TYPE_WITH

Listening for SQL Statement Execution

The SQLBuilder defines an interface IExecuteSQLListener which allows users of the SQLBuilder component to listen for the SQLBuilder's SQL statement being executed. If a client implements this interface and provides its implementation to the SQLBuilder through

SQLBuilder.addExecuteSQLListener(IExecuteSQLListener);

then the client will be notified whenever the SQL Builder executes its SQL. This allows clients to update their UI when the SQL is executed, for example, by showing the Results View. When the client is finished, it should remove its listener from the SQLBuilder by calling:

SQLBuilder.removeExecuteSQLListener(IExecuteSQLListener);

Example SQLBuilder Editor

The example of an editor which consumes the SQLBuilder class is in the main org.eclipse.datatools.sqltools.sqlbuilder plugin. The main class is:

org.eclipse.datatools.sqltools.sqlbuilder.SQLBuilderEditor

This editor is invoked when a .sql file is opened using the Open with ->SQL Query Builder popup menu. It demonstrates how to embed the SQLBuilder component in an EditorPart. The SQLBuilderEditor opens .sql files which have previously been created in the DTP SQL File Editor and it relies on connection profile information having previously been persisted by the SQL File Editor as SQLEditorConnectionInfo. To use the SQLBuilder Editor, see

org.eclipse.datatools.sqltools/prototype/devnotes/sqlbuilder_notes.txt

The Editor

Example SQLBuilder Dialog

In the org.eclipse.datatools.sqltools.sqlbuilder.examples plugin, there is an example of a dialog which hosts the SQLBuilder component:

org.eclipse.datatools.sqltools.sqlbuilder.examples.dialogs.SQLBuilderDialog

When this plugin is loaded, the popup menu for .sql files in the Navigator or Project Explorer View has a new entry SQLBuilder Dialog. Under this menu item are a number of sub-entries including Open SQLBuilderFileEditorInput, Open SQLBuilderEditorInput and Open SQLBuilderStorageEditorInput.

• Open SQLBuilderFileEditorInput loads the currently selected .sql file in SQLBuilderDialog. This is done in the class

SQLBuilderDialogFileAction.

• Open SQLBuilderEditorInput also loads the currently selected .sql file, but it does so in a roundabout way to demonstrate how to use the SQLBuilderEditorInput class to create an input based on a string. It creates a SQLBuilderEditorInput based on the file's contents and passes it to the SQLBuilderDialog. This is done in the class

SQLBuilderDialogStorageAction

• Open SQLBuilderStorageEditorInput loads the currently selected .sql file, but it does so in a roundabout way to demonstrate how to use the SQLBuilderStorageEditorInput class. It creates a SQLBuilderStorageEditorInput based on the file, serializes the SQLBuilderStorageEditorInput to a string via an XMLMemento, then creates a new SQLBuilderStorageEditorInput from the string. This is done in the class

SQLBuilderDialogStorageAction

in the method call

EditorInputUtil.createSQLBuilderEditorInputFromStringViaFile(IFile)

SQLBuilderDialog has a Save button which demonstrates how to save the dialog's data, i.e. the SQL Statement, ConnectionInfo and OmitSchemaInfo. When the input was a SQLBuilderFileEditorInput, the file is saved. When the input was one of the other editor input classes, the data is serialized to strings and displayed in a MessageBox.

There are two further menu options below SQLBuilder Dialog: Create new SELECT statement using SQLBuilderEditorInput and Open statement created for different dialect. The first creates a new SELECT statement using the Connection Profile of the currently selected .sql file. The second works when you select two .sql files in the Project explorer. It opens the SQL statement in the first file using the connection profile for the second.

SQLBuilderDialog illustrates the use of IExecuteSQLListener by implementing this interface and switching to a tab which hosts the ResultsViewControl from the org.eclipse.datatools.sqltools.result.ui.view package when IExecuteSQLListener.executedSQL is called.

Also of interest in the SQLBuilderDialog's use of the ResultsViewControl class is its definition of a results view filter in the class ResultsHistoryFilter. This filter ensures that only those results created by the SQLBuilderDialog are shown in the SQLBuilderDialog's results view tab.

The Dialog

The example dialog has two tabs.

Edit tab

The first tab is the edit tab, hosting the SQLQueryBuilder component.

SQL Results tab

The second tab is the SQL Results tab, hosting the ResultsViewControl:

Comments / Suggestions

Lchan.actuate.com 22:57, 23 October 2007 (EDT)

RE: the API for the Input Types, additional API may be needed to support the following use cases:

• Use Case 1: Opens the SQB to build a *new* SQL query, with only a connection profile in its input.
• Use Case 2: Opens the SQB to edit a SQL query text, previously saved in a different dialect than the dbms specified in current session's connection profile.
• Use Case 3: A consumer application may have stored a SQL query text, its dialect, and a connection profile in its own manner. The API would be more clean if it allows a consumer to directly specify those editor input info, without using an interim SQLEditorStorage.

It looks like ISQLBuilderEditorInput interface has most of the methods to support the above use cases. It is thus mainly the implementation class(es) that would need related API support.

See proposed API protocols below, which are intended to illustrate the type of API changes to support the above use cases. They are not necessarily the actual names and protocols to use.

• A new class / interface: (I)SQLStatementTextInfo to include the "dialect" of the corresponding SQL statement text.

This should also be supported by the existing implementation SQLBuilderFileEditorInput and SQLBuilderStorageEditorInput:

     public SQLStatementTextInfo( String stmtText );
     public SQLStatementTextInfo( String stmtText, <type> sqlDialectType );

     public String getSQLStatementText();
     public <type> getSQLDialectType();   

<type> is whatever type is currently used to represent the SQL dialect type info. It will be used to identify which db-specific parser to use to re-generate the query's model from the statement text. This attribute is optional. If not specified, default to that of the current connection used in the Builder.

• Add methods to ISQLBuilderEditorInput interface:

  public void setSQLStatementTextInfo( ISQLStatementTextInfo stmtInfo );

• A new class SQLBuilderEditorInput that implements ISQLBuilderEditorInput:

  public SQLBuilderEditorInput( IConnectionProfile profileInstance );
  public SQLBuilderEditorInput( IConnectionProfile profileInstance, ISQLStatementTextInfo stmtInfo );
  public SQLBuilderEditorInput( IConnectionProfile profileInstance, ISQLStatementTextInfo stmtInfo, IOmitSchemaInfo );

jeremyl.sybase.com 15:30, 24 October 2007 (EDT)

I like these suggestions. I've been looking into the type to use to represent the dialect and unless someone can tell me of an existing class that should be used, I suggest we make a new one SQLBuilderDialectInfo which has 2 string fields for the Product and the Version. These should be populated from the org.eclipse.datatools.modelbase.sql.schema.Database object which has Product and Version fields. I believe these are the fields used by the ParserManager to find the right parser.

For the first use case, (opening the SQLBuilder for a new statement) I think we should allow the user to specify the DML statement type - SELECT / UPDATE / INSERT / DELETE. To do this, we could define integer constants for the statement types and create another constructor:

  public SQLBuilderEditorInput( IConnectionProfile profileInstance, int stmtType );

jeremyl.sybase.com 14:26, 31 October 2007 (GMT)

The API has been updated to implement the suggestions above. This wiki page has also been updated accordingly.

jeremyl.sybase.com 10:00, 17 December 2007 (GMT) This page has been updated with the following information:

• setInput exceptions.
• addition of IExecuteSQLListener interface
• use of ResultsHistoryFilter in the example dialog
• images of the example editor and dialog have been added