# SQuirreL SQL Client Programmers Guide

### Contents

Introduction
What Licence should I Use?
The Internal Name
What Package can I put my Plugin into?
Files and Directories
How Plugins are Loaded
Plugin Classes
Plugin API
| Logging
| Loading Libraries
| Loading Resources
| Adding Menu Items
| Adding a Panel to Application Preferences/Session Properties
| Hooking into SQL Execution
| XML
| Loading and Saving Settings
| Object Caches
| Adding a Tab to the Main, Table or Procedure Tabbed Panel
| SQL Results Data Model
| SQL Results Tab API
| Custom Query Tokenizers
| Custom Data Type Components
Installing Plugins

### Introduction

A plugin is an application written in Java that runs within SQuirreL. This allows developers to enhance the functionality of SQuirreL without having to rebuild the SQuirreL application itself.

This document (like the plugin API itself) is in a very early stage and is lagging behind the plugin API. Its suggested that you check the existing plugins in CVS to see how they use the API.

### What Licence should I Use?

Please bear in mind when you are reading this section that I am not a lawyer. All my comments reflect how I believe licencing of free and open source software works. If you have any questions I suggest you consult a lawyer.

If you're just writing a plugin for your personal use then licencing doesn't matter but if you intend to distibute your plugin (either through the SourceForge release system in the SQuirreL SQL Client project or in any other way) then the licence you select should be compatible with the GNU General Public Licence that SQuirreL is distributed under.

I believe (although as I said I am not a lawyer) that as long as your plugin is dynamically linked into SQuirreL (as it will be because thats how the plugin API works) then any OSI approved licence should be appropriate.

Unless you have a philosophical dislike of the GNU GPL I suggest you release your plugin under it so that I don't wonder if your choice of licence is going to cause me any problems <grin/>.

For more information about licences please see The Free Software Foundation, the Open Source Initiative or the documents at SouceForge about selecting a licence.

### The Internal Name

A plugins internal name is used to uniquely identify it and so must be different to that of any other plugin. It is supplied by implementing the `IPlugin.getInternalName()` method. As the internal name is used to name files and directories it should only consist of characters valid on the different platforms that SQuirreL can run on. As well the character "-" is reserved for internal use. The internal name "app" is reserved for the use of the core SQuirrel code.

### What Package can I put my Plugin into?

If you already have a domain name that you use for naming java packages and you'd like to use that then by all means do so. If you don't have a domain name then as a convienience the package `net.sourceforge.squirrel_sql.plugins.<internal_name>` is available. Using this package name does not in any way imply my ownership of your code. This resides with you under the conditions of the licence that you have selected for your plugin.

As an example the internal name for the Look and Feel plugin is `laf`.

To reserve an internal name just email a request to the SQuirreL developers mailing list (details at http://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop) specifying the internal name that you'd like and a quick description of your plugin.

### Files and Directories

Plugins are installed as a jar file within the `<squirrel_app>/plugins` directory. The name of the jar file should be the same as the internal name of one of the plugins within it plus a `.jar` extension. E.G. if the internal name of the plugin is `laf` then the jar file should be named `laf.jar`. No other files should be placed within the `<squirrel_app>/plugins` directory.

The directory `<squirrel_app>/plugins/<internal_name>` is reserved for the exclusive use of the plugin. All non-user specific other files required by the plugin should be placed in here.

The directory `<user_home>/.squirrel-sql/plugins/<internal_name>` is also reserved for the exclusive use of the plugin. All "per-user" files should be placed in here.

### How Plugins are Loaded

Plugins are loaded very early in the SQuirreL startup process. Once the application logging factory has been created the plugins are loaded. All jars in the `<squirrel_app>/plugins` directory are loaded by the same custom class loader. The jars are searched for all classes that implement the `IPlugin` interface. Then `newInstance` is used to create an instance of each of these plugin classes. Once created the `IPlugin.load()` method is called. Because most of the SQuirreL environment hasn't yet been setup try not to use the plugin constuctor and `load()`method to do initialisation. Instead use the `IPlugin.initialize()` method.

`IPlugin.initialize()` is called as the last part of the SQuirreL startup prior to showing the main window. This is where initialization of the plugin should occur. Please try to keep the amount of code executed here to a minimum as it will affect the startup time of SQuirreL. If possible do plugin initialization when a plugin is first requested by the user.

### Plugin Classes

All plugin jars should contain at least one class that implements the interface `net.sourceforge.squirrel_sql.client.plugin.IPugin`. If your plugin is applicable to a session then it should implement the descendent of `IPlugin`, `net.sourceforge.squirrel_sql.client.plugin.ISessionPlugin`. As a convienence two abstract base classes have been supplied: `net.sourceforge.squirrel_sql.client.plugin.DefaultPlugin` and `net.sourceforge.squirrel_sql.client.plugin.DefaultSessionPlugin`. As well as supplying some useful base behaviour these base classes should guard against additions to the base interfaces breaking existing implementations. Every attempt will be made to supply appropriate default behaviour (usually an empty method) to the base classes for every method added to the interfaces.

### Plugin API

There are two main ways for a plugin to communicate with SQuirreL. These are via the `net.sourceforge.squirrel_sql.client.IApplication` interface which specifies the application level API and `net.sourceforge.squirrel_sql.client.session.ISession` which specifies the session level API. From within a descendent of `BasePlugin` you can call `getApplication()` to get the `IApplication` object and descendents of `BaseSessionPlugin` will have `ISession` passed on requests.

#### Logging

Logging is done via the log4j API from the Apache Foundation.

The configuration file for logging is passed in via the `-loggingConfigFile` application argument. E.G. `-loggingConfigFile=log4j.properties`. The standard configuration file specifies the appender `SquirrelAppender` which writes out to the text file `<user-home>\squirrel-sql\squirrel-sql.log`

To use logging in your classes you need to import the logging classes:

    import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
    import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;

Create a static logging object:

    private static ILogger s_log = LoggerController.createLogger(<your-class>.class);

And then you can use the logger:

    s_log.error("Error occured loading Look and Feel: " + lafClassName, ex);

Giving the output:

    12468 [main] ERROR net.sourceforge.squirrel_sql.plugins.laf.LAFRegister  - Error occured loading Look and Feel: com.l2fprod.gui.plaf.skin.LinuxLookAndFeel
    com.l2fprod.gui.plaf.skin.impl.gtk.GtkSkinNotFoundException
    	at com.l2fprod.gui.plaf.skin.impl.gtk.GtkSkin.getDefaultSkinLocation(GtkSkin.java)
    	at com.l2fprod.gui.plaf.skin.LinuxLookAndFeel.(LinuxLookAndFeel.java)
    	at java.lang.Class.newInstance0(Native Method)
    	at java.lang.Class.newInstance(Class.java:254)
    	at net.sourceforge.squirrel_sql.plugins.laf.LAFRegister.installLookAndFeels(LAFRegister.java:289)
    	at net.sourceforge.squirrel_sql.plugins.laf.LAFRegister.(LAFRegister.java:147)
    	at net.sourceforge.squirrel_sql.plugins.laf.LAFPlugin.load(LAFPlugin.java:140)
    	at net.sourceforge.squirrel_sql.client.plugin.PluginManager.loadPlugin(PluginManager.java:243)
    	at net.sourceforge.squirrel_sql.client.plugin.PluginManager.loadPlugins(PluginManager.java:206)
    	at net.sourceforge.squirrel_sql.client.Application.startup(Application.java:122)
    	at net.sourceforge.squirrel_sql.client.Main.main(Main.java:41)

#### Loading Libraries

??TODO??

#### Loading Resources

??TODO?? (Be sure to talk about the difference between IPlugin.getPluginUserSettingsFolder and IPlugin.getPluginAppSettingsFolder)

#### Adding Menu Items

There are several places where plugins may want to add menu items to allow the user to activate plugin-specific behaviors. The Object Tree is one of these places. It displays objects of various types as defined in the class:

        net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectType

The Object tree displays a popup menu when the user right-clicks (ctrl-click on Mac OS) on an object in the tree. The SQuirreL API that allows plugins to add items to this menu for all objects (or objects of a specific type) is found in the IObjectTreeAPI interface for which an implementation can be obtained as follows:

        IObjectTreeAPI api = session.getObjectTreeAPIOfActiveSessionWindow();

You can find examples in the existing plugins where this is done in the method:

        PluginSessionCallback sessionStarted(ISession session);

The sessionStarted method is called on all loaded plugins each time a new database session window is created (by the user clicking the "Connect" button while selecting an alias to connect to). The `net.sourceforge.squirrel_sql.client.plugin.PluginManager` class is responsible for providing this notification to the loaded plugins.

Some useful methods for adding menu items in the IObjectTreeAPI interface are:

    	void addToPopup(DatabaseObjectType dboType, Action action);

    	void addToPopup(DatabaseObjectType dboType, JMenu menu);

These methods both take a DatabaseObjectType to determine which type of object to add the menu item to. DatabaseObjectType defines all public constants that are used to identify types of objects found in the object tree. For example there are:

    	DatabaseObjectType.FOREIGN_KEY
    	DatabaseObjectType.FUNCTION
    	DatabaseObjectType.INDEX
    	DatabaseObjectType.PRIMARY_KEY
    	DatabaseObjectType.PROCEDURE
    	DatabaseObjectType.SCHEMA
    	DatabaseObjectType.SEQUENCE
    	DatabaseObjectType.TABLE
    	DatabaseObjectType.UDT
    	DatabaseObjectType.VIEW

These are the ones most commonly used by plugins, but there are still more. Any object shown in the tree has a corresponding DatabaseObjectType constant that represents it type.

#### Adding a Panel to Application Preferences/Session Properties

To add a panel to the Application Preferences or to the Session Properties dialog you need to create a class that implements either `net.sourceforge.squirrel_sql.client.preferences.IGlobalPreferencesPanel` or `net.sourceforge.squirrel_sql.client.session.properties.ISessionPropertiesPanel` and override `IPlugin.getGlobalPreferencePanels()` or `ISessionPlugin.getSessionPropertiesPanels()`.

`IGlobalPreferencesPanel.initialize(IApplication)` or `ISessionPropertiesPanel.initialize(IApplication, ISession)` are called so that the options panel can initialise itself.

`String getTitle()` is called to retrieve the title for the panel.

`String getHint()` is called to get the tooltip hint for the panel.

`Component getPanelComponent()` is called to get the component that is actually placed in the dialog.

`void applyChanges()`

is called when the user presses the OK button in the dialog. You should save your changes at this point.

#### Hooking into SQL Execution

The method `ISession.addSQLExecutionListener(ISQLExecutionListener`) allows you to "listen" for SQL being executed in the SQL panel and modify it. Remember to use `ISession.removeSQLExecutionListener(ISQLExecutionListener)` to remove the listener when you are finished with it.

`ISQLExecutionListener` specifies one method:

    public String statementExecuting(String sql)

This method is called for every statement to be executed in the SQL panel. E.G. If the user enters the following in the SQL panel:

    select * from table1;
    select * from table2

and requests them both to be executed then `statementExecuting()` will be called twice. Once with `"select * from table1"` passed and the second time with `"select * from table2"`.

Whatever you return from the method (allowing for any changes that other plugins may make) is what will be executed. If you return `null` then no other listeners will be called and the statement will not be executed.

This would be useful for macro expansion etc.

#### XML

SQuirreL now comes with the XML parser NanoXML. Previously it came with JDOM but as I was using almost none of the available functionality I replaced it with with a smaller one.

#### Loading and Saving Settings

The package `net.sourceforge.squirrel_sql.fw.xml` contains classes that will take a javabean and turn it into XML and load a javabean from XML. SQuirreL uses this for saving global preferences, driver and alias configuration information.

This is an example of the XML file that stores JDBC driver configuration information.:

    <Beans>
        <Bean Class="net.sourceforge.squirrel_sql.fw.sql.SQLDriver">
            <jarFileName/>
            <url>jdbc:postgresql:<//host>:<port>/<database></url>
            <identifier Class="net.sourceforge.squirrel_sql.fw.id.UidIdentifier">
                <string>-7</string>
            </identifier>
            <name>PostgreSQL</name>
            <driverClassName>org.postgresql.Driver</driverClassName>
            <usesClassPath>true</usesClassPath>
        </Bean>
    </Beans>

The following example shows how two javabeans can be saved to an XML file.

        XMLBeanWriter wtr = new XMLBeanWriter(bean1);
        wtr.addToRoot(bean2);
        wtr.save("javabeans.xml");

This example shows the loading of javabeans from an XML File:

        XMLBeanReader rdr = new XMLBeanReader();
        rdr.load("javabeans.xml", getClass().getClassLoader());
        for (Iterator it = rdr.iterator(); it.hasNext();) {
            final Object Bean = it.next();
            ...
        }

Because the `XMLBeanReader` has to instantiate the javabean it needs to know the class loader that it used for the javabean's class files. As plugins are loaded by a custom class loader this would normally be the class loader used for the plugin classes.

The processing supplied by the classes in `net.sourceforge.squirrel_sql.fw.xml` is fairly rudimentary and has some limitations. Chief amongst them is that is has no concept of multiple references to the same object, it will save and restore multiple objects in this case.

The properties of the javabean to be saved/restored can be builtin types, instances of `String` or other javabeans. If you have a property that doesn't fit this list then you can write a wrapper class to turn it into a javabean. See the package `net.sourceforge.squirrel_sql.fw.util.beanwrapper` for some examples.

Array properties are handled but currently not for builtin types nor `String` objects. For these you need to use a wrapper class.

#### Object Caches

??TODO??

#### Adding a Tab to the Main, Table or Procedure Tabbed Panel

To add a tab to the main tabbed panel (the one containing the "Objects" and "SQL" tabs) use the `ISession.addMainTab(String title, Icon icon, Component comp, String tip)` method. In the future this method will be deprecated by being replaced by one similar to the one for the table tabbed panel.

To add a tab to the Table tabbed panel (the one displayed when you select a table in the object tree) use the method `ISession.addTablePanelTab(ITablePanelTab)`. For the Procedure tabbed panel use the method `ISession.addProcedurePanelTab(IProcedurePanelTab)`.

You should create a class that implements either `net.sourceforge.squirrel_sql.client.session.objectstree.tablepanel.ITablePanelTab` or `net.sourceforge.squirrel_sql.client.session.objectstree.procedurepanel.IProcedurePanelTab` in order to create a tab. The methods `getTitle()` and `getHint()` define the title and the flyover hint respectively. Override `getComponent()` to provide the actual component to be displayed in the tab (remember to wrap it in a `javax.swing.JScrollPane` if the contents could be larger than the tab).

`ITablePanelTab.setTableInfo(ITableInfo)` will be called whenever the currently selected table changes. For the procedure tab `IProcedurePanelTabsetProcedureInfo(IProcedureInfo)` will be called when the procedure changes. For performance reasons please don't refresh the panel at this point. Use `select()` for this. `select()` is called whenever the your tab is to be displayed. Remember that this could be called multiple times without a different table or procedure being selected in the object tree so you only need to refresh your component the first time it is called.

As a convenience the classes `net.sourceforge.squirrel_sql.client.session.objectstree.tablepanel.BaseTableTab` and `net.sourceforge.squirrel_sql.client.session.objectstree.procedurepanel.BaseProcedureTab` have been provided. You would normally use these classes (which implement `ITablePanelTab` and `IProcedureTab`) as the base for any tabs you create.

`BaseTableTab` and `BaseProcedureTab` provides the methods `getSession()`, and `getTableInfo()/getProcedureInfo()`. They also provide the method `refreshComponent` which will be called the first time a tab is displayed for a table or procedure and is the appropriate place to load the information to be displayed.

#### SQL Results Data Model

A data model `net.sourceforge.squirrel_sql.fw.datasetviewer.IDataSetModel` is available for the SQL results. You can get it from `ResultTab::getResultSetDataModel()` In the near future it will be added to the content tab for the tables as well.

#### SQL Results Tab API

An API in ISession allows a plugin to listen to events in the Result Tabs.

    void addResultTabListener(IResultTabListener lis);
    void removeResultTabListener(IResultTabListener lis);

`IResultTabListener` gives you:

    void resultTabAdded(ResultTabEvent evt);
    void resultTabRemoved(ResultTabEvent evt);
    void resultTabTornOff(ResultTabEvent evt);
    void tornOffResultTabReturned(ResultTabEvent evt); /** Not yet implemented*/

### Custom Query Tokenizers

??TODO??

### Custom Data Type Components

??TODO??

### Installing Plugins

??TODO??