Introduction

SQuirreL SQL Client uses the JDBC API to interact with a database, which for the most part makes it database implementation-agnostic. However, it can be extended to support implementation-specific features by writing a plugin that uses the Plugin API. For example, JDBC doesn't specify a generic way to obtain the source code that can be used to re-create database objects such as triggers, stored procedures, functions, views, etc. Yet this information is usually available in some data dictionary view that can be queried by object name. Although the SQL standard specifies a special optional schema (INFORMATION_SCHEMA) to store this information in, only a few database vendors have implemented this, while others have chosen a different schema to store this information in. A plugin can be written with the implementation-specific queries that are required to retrieve the source code for each type of database object. Additionally, other plugins that are not implementation-specific have been written using the Plugin API to implement features not available in the base SQuirreL software installation (for example, code completion, syntax highlighting, Look and Feel, query favorites, etc.)

Plugin Loading

A plugin is made available to SQuirreL as a set of classes that are packaged into a jar file which is located in the plugins directory in the installation directory. SQuirrel uses a custom classloader to find and load jars containing plugins. The PluginManager is responsible for reading prefs.xml (as an XML-Bean) and for each plugin found, noting whether or not to load it according to the attribute called "loadAtStartup". If a plugin is added to the plugins directory, SQuirreL must be restarted to pick up the new plugin (Updated plugin definitions require a restart as well).

Session Lifecycle

Plugins can register to listen for session start/end events by implementing the ISessionPlugin interface and/or DefaultSessionPlugin base class. Sessions can be started and stopped by the user at will. Therefore, it is important for plugins that implement the ISessionPlugin interface (including those that extend DefaultSessionPlugin) to use care when storing references to Sessions. Specifically, if a plugin stores a reference to an ISession instance that it received in a call to sessionStarted (ISession session), it should remove that reference when it's sessionEnding (ISession session) is called. Otherwise, the Session can not be garbage collected, which is a memory leak.

Providing Source Tabs

Source tabs can be added for specific node types by using the IObjectTreeAPI interface. What follows is example of an implementation of the ISessionPlugin interface method called sessionStarted (ISession session)


public PluginSessionCallback sessionStarted(final ISession session) {
IObjectTreeAPI treeApi = session.getSessionInternalFrame.getObjectTreeAPI;
_treeAPI.addDetailTab(DatabaseObjectType.VIEW, new ViewSourceTab("Source");
}



A reference to every newly created Session can be obtained by implementing the sessionStarted method in the ISessionPlugin interface. Notice that the "ViewSourceTab" is only installed for DatabaseObjectType.VIEW objects. This is so that our implementation of a "view" source tab can rely on the fact that it's always a view that the user has selected. In this way, different source tabs can be implemented for each type of object (table, view, trigger, UDT, sequence, procedure, function, etc.)

A source tab can then be implemented by extending the classes:


net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseSourceTab
net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseSourcePanel


In particular, BaseSourceTab has an abstract method called createStatement which is implemented to return a PreparedStatement by using BaseObjectTab.getSession and BaseObjectTab.getDatabaseObjectInfo. The method getDatabaseObjectInfo returns the node that is selected from which can be found the name, schema and catalog of the object to write an SQL statement that queries the data dictionary for the source for that object. The BaseSourcePanel.load method is then called with the ResultSet obtained by executing the PreparedStatement.

Custom data type implementations

Data Type classes are used by SQuirreL to read, render and display data stored in a column of a database table. Type codes are assigned (by JDBC as well as vendors) to represent standard and vendor-specific SQL types. The standard types are defined in the various SQL standards (SQL92, SQL:2003

SQL:2003

SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...

). These include types such as DATE, VARCHAR, CHAR, etc.

SQuirreL defines the IDataTypeComponent interface which encapsulates behavior necessary to read, write and render data of a given type. The SQuirreL framework module (located in sql12/fw/src) includes many implementations of this interface. Some examples are DataTypeBlob, DataTypeChar, DataTypeFloat and DataTypeDate. These implementations can be used as examples when a plugin writer wishes to support a data types that are only found in one database.

The CellComponentFactory class has a static method called registerDataType that allows plugin writers to install custom IDataTypeComponent implementations when the plugin is loaded.

Changing How SQuirreL Tokenizes a Script into Individual Statements

Native database tools that are used to run scripts must have a way of breaking a script into statements. Since statements can span lines, the end of line character (eol) normally isn't used for this purpose. For example, Oracle uses the semi-colon , whereas Sybase and MS SQL-Server use the word "GO" to distinguish multiple statements in a script. Users don't want to change their scripts to work with SQuirreL, so it is important for a plugin writer to consider customizing SQuirreL's default expectation of ";" if that is indeed not the statement separator used by the native database script interpreter. Other examples that require custom handling are:

• Stored Procedures (These can have embedded statement separators and may use other characters for delimiters - for example Oracle uses slash (/)


• References to external scripts (Oracle uses "@
  

Plugins should implement the interface IQueryTokenizer and call the method ISession.setQuerytokenizer to add this capability to SQuirreL.

Formatting Vendor-Extended SQLExceptions

With some JDBC drivers, SQLException is extended to provide "enhanced" reporting of errors. In this case, the SQLException that is thrown by the driver may be cast to the vendor-specific class and non-standard methods may be called to get more specific information about the problem that caused the SQLException to be thrown. For example, the DB2 Universal Database Driver provides a DB2Diagnosable exception which extends SQLException. In some cases, the database will provide information that is only available using the DB2Diagnosable interface methods. According to the documentation SQLException must be cast to a DB2Diagnosable, then call getSqlca to get a DB2Sqlca object upon which a call to getMessage will give the error message such as "Table foo does not exist" - when the exception results from selecting from a non-existent table.

In order for SQuirreL to facilitate this vendor-specific API, a new plugin API was introduced, known as the ExceptionFormatter. Each Session can have exactly one custom ExceptionFormatter (default and one custom ExceptionFormatter). The ExceptionFormatter is installed by calling ISession.setExceptionFormatter. The ExceptionFormatter interface looks as follows:


/**
* Returns a boolean indicating whether or not this formatter can format the
* specified exception.
*
* @param t the exception to determine formatting compatibility
*
* @return a boolean value to indicate whether format should be called on the
* given throwable
*/
boolean formatsException(Throwable t);

/**
*
* @param t the exception to be formatted
*
* @return the message
*/
String format(Throwable t);


A custom ExceptionFormatter implementation should be reflective in that it shouldn't reference any database vendor-specific classes directly, but indirectly using Class.forName. This is required so that merely installing the plugin without the requisite driver classes won't cause the app to experience ClassNotFoundExceptions while loading the plugin. It is not required that the ExceptionFormatter handle all Throwables. Whichever Throwables aren't handled by the custom ExceptionFormatter, will be handled by the default ExceptionFormatter called DefaultExceptionFormatter.

Getting the Currently Selected ResultTab

1. When a session starts, save off a reference to the SessionManager:


IApplication application = ISession.getApplication;
SessionManager sessionManager = application.getSessionManager;


2. At some later point to get a reference to the currently selected result tab do the following:


ISession currentSession = sessionManager.getActiveSession;
ISQLPanelAPI sqlPanelApi = currentSession.getSQLPanelAPIOfActiveSessionWindow;
ISQLResultExecuter executer = sqlPanelApi.getSQLResultExecuter;
IResultTab selectedResultTab = executer .getSelectedResultTab;