View source for SQL Query Builder Component API

&larr;[[SQL Development Tools | 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 [http://wiki.eclipse.org/SQL_Query_Builder_Component_API#Input_Types 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 [[ SQL_Query_Builder_-_Omit_Current_Schema | 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 ===
[[Image:DTPSQLQueryBuilderEditor.JPG]]

== 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.
[[Image:DTPSQLQueryBuilderDlgEdit.JPG]]
=== SQL Results tab ===
The second tab is the SQL Results tab, hosting the ResultsViewControl:
[[Image:DTPSQLQueryBuilderDlgResults.JPG]]

= Comments / Suggestions =

'''[[User:Lchan.actuate.com|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 );

'''[[User:jeremyl.sybase.com|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 );

'''[[User:jeremyl.sybase.com|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.

'''[[User:jeremyl.sybase.com|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