Extending SQuirreL with plugins

SQuirreL is a 100% pure-Java implementation of a generic SQL client. You can browse and analyse any database which provides a JDBC driver. You can execute any kind of SQL statements. SQuirreL provides a plugin mechanism to hook in additional features. The default installation comes with a lot of plugins, for example MySQL-specific plugins, code completion, data import plugins and more.



In this article, I'd like to show you how to write your own plugin for SQuirreL and what you need to do to set up your development environment. I'll implement two features for indexing tables and their content into an Apache Lucene index and to make it searchable using full-text search.
First, you need to check out SQuirreL from version control. They're using CVS and you can access the repository at the following location:

:pserver:anonymous@squirrel-sql.cvs.sourceforge.net:/cvsroot/squirrel-sql

You can do this in the Eclipse CVS Repository perspective. Right-click on the sql12 project and check it out as Java Project. Then, if the source folders are missing, you need to add them manuall using right-click - build path - use as source folder. Don't add the source folders which require additional libraries, such as specific JDBC driver implementations.



Next, to resolve all the compile errors, add the jar files as libraries to your build path. Look in the project for folders called lib/. All necessary libraries are somewhere in the project. Just add them by right-clicking - build path - add to build path.

For your own plugin, you want to create a new source folder. It's recommended to use the same folder structure as the existing plugins. My workspace looks like this:



Create an implementation of IPlugin or extend DefaultSessionPlugin:

net.sourceforge.squirrel_sql.client.plugin.IPlugin
net.sourceforge.squirrel_sql.client.plugin.DefaultSessionPlugin (that's what I did)

Currently, SQuirreL wants you to build your plugin as jar file before you can test it. I wanted to have it integrated in the normal classpath, so I can use hotcode replacement while developing and don't need to restart the application all the time. I needed to patch the IPluginManager implementation of SQuirreL to find plugins also in the classpath instead of using a dynamic class loader which is looking only for bundled jar files. See the attached patch file for the changes to the plugin manager. Basically, I'm reading in a text file which contains all the class names of the plugin implementations. For development, that's okay and goes well enough.

Finally, you can get to the meat and implement the plugin (I left out some clutter in the following snippet):

package net.sourceforge.squirrel_sql.plugins.fulltextsearch;

import net.sourceforge.squirrel_sql.client.plugin.DefaultSessionPlugin;
import net.sourceforge.squirrel_sql.client.session.IObjectTreeAPI;
import net.sourceforge.squirrel_sql.client.session.ISession;

import org.apache.lucene.analysis.standard.StandardAnalyzer;
import org.apache.lucene.index.IndexWriter;
import org.apache.lucene.search.IndexSearcher;

public class FulltextsearchPlugin extends DefaultSessionPlugin {
	private final Analyzer analyzer = new StandardAnalyzer();
	private final String path = "c:/temp/lucene/squirrel/";
	private final IndexWriter writer = createIndexWriter();

	@Override
	public String getAuthor() {
		return "Mike Haller";
	}

	@Override
	public String getDescriptiveName() {
		return "Full-Text Search Plugin";
	}

	@Override
	public String getInternalName() {
		return "fulltextsearchplugin";
	}

	@Override
	public String getVersion() {
		return "0.0.1";
	}

	@Override
	public PluginSessionCallback sessionStarted(ISession session) {
		// Add context menu items to the object tree's view and procedure nodes.
		IObjectTreeAPI otApi = session.getSessionInternalFrame()
				.getObjectTreeAPI();
		otApi.addToPopup(DatabaseObjectType.TABLE, new FulltextsearchMenu(this,
				session));
		return new PluginSessionCallbackAdaptor(this);
	}
}

Implement menu and your Swing actions:

public class FulltextsearchMenu extends JMenu {

	public FulltextsearchMenu(FulltextsearchPlugin fulltextsearchPlugin, ISession session) {
		super("Full-Text Search");
		add(new ReindexAction(fulltextsearchPlugin, session));
		add(new SearchAction(fulltextsearchPlugin, session));
	}

}

Attaching the actions to the context menu happens in the sessionStarted() method, which is called by SQuirreL on your plugin at boot time. Note the ISession object: That's the one where you get all the interesting objects from, such as new SQL Connections or GUI components.

	@Override
	public PluginSessionCallback sessionStarted(ISession session) {
		// Add context menu items to the object tree's view and procedure nodes.
		IObjectTreeAPI otApi = session.getSessionInternalFrame()
				.getObjectTreeAPI();
		otApi.addToPopup(DatabaseObjectType.TABLE,new FulltextsearchMenu(this, session));
		return new PluginSessionCallbackAdaptor(this);
	}

Finally run the Main class to start up the application.
net.sourceforge.squirrel_sql.client.Main

To test your plugin, create a new database alias and connect to it. You need a running database for that.



Connect to alias, choose the catalog, open the list of tables. Right-click on a table and find your custom menu actions:



My implementations for indexing tables basically looks like this:

IndexWriter indexWriter = fulltextsearchPlugin.getIndexWriter();
List<ITableInfo> selectedTables = session.getObjectTreeAPIOfActiveSessionWindow().getSelectedTables();
// TODO: Iterate over selectedTables and get schema and tableName
ISQLConnection connection = session.getSQLConnection();
PreparedStatement statement = connection.
prepareStatement("SELECT ... FROM `" + schema + "`.`" + tableName + "`"); // Replace ...
ResultSet resultSet = statement.executeQuery();
// Create a Lucene Document for each database record
while (resultSet.next()) {
  Document document = new Document();
  String value = resultSet.getString(i);
  document.add(new Field("row", Long.toString(ctr), Store.YES, Index.NOT_ANALYZED));
  document.add(new Field("table", tableName, Store.YES, Index.NOT_ANALYZED));
  document.add(new Field("value", value, Store.NO, Index.ANALYZED));
  indexWriter.addDocument(document);
}
indexWriter.commit();

I'm creating an IndexWriter, this is the access to the Lucene Index database. Then, i'm asking SQuirreL for all the tables the user has selected and iterator over them. For each table, I'm executing a naive SELECT * FROM table statement. All the records returned by this query are indexed, row by row. For large databases, this of course would be very dumb. It's up to the reader to find a more sophisticated way of storing values for large tables

After creating the Lucene Document, which serves as a container object for multiple search terms (called Fields), I add it to the Lucene Index using the IndexWriter and commit() the new document to the index, so Lucene can start merging the new information into the existing database. Note the Store and Index modifiers, which basically tell Lucene whether the value should be stored in the index or not and whether the value should be searchable or not. Usually, values are analyzed using an Analyzer, which for example splits up text into multiple words (tokens), removes clutter and whitespace etc. Using the keyword NOT_ANALYZED, i'm telling Lucene to store the value as-is, as it's an identifier which should not be split up into multiple words or terms. Each field which is stored in the index using Store.YES can be retrieved later at search time directly from Lucene. Usually, you would want to store only an URI or some other unique identifier for a document. All real content should use Store.NO to keep the index small.

The implementation for searching the index:

IndexSearcher indexSearcher = fulltextsearchPlugin.getIndexSearcher();
// You can search for all fields where Index.ANALYZED or Index.NOT_ANALYZED is used
QueryParser queryParser = new QueryParser("value", fulltextsearchPlugin.getAnalyzer());
Query query = queryParser.parse(searchPattern);
TopDocs search = indexSearcher.search(query, 1000);
ScoreDoc[] scoreDocs = search.scoreDocs;
for (ScoreDoc scoreDoc : scoreDocs) {
 Document doc = indexSearcher.doc(documentId);
 // You can access all fields with Store.YES here
 String table = doc.get("table");
 String row = doc.get("row");
 // TODO: Print search result
}

To sum up, searching the Lucene Index is quite easy using an IndexSearcher and a QueryParser. The QueryParser splits up a query like +fruits +apple -peach into programmatic TermQueries and Boolean operators.

Hopefully this will be a quick start for someone who wants to develop new and creative plugins for SQuirreL.

[1] SQuirreL Homepage
[2] Wiki: SQuirreL SQL Client Plugin API
[3] Apache Lucene Search/Index Engine

• Comments (0)
• Trackbacks (0)