Eclipse and HSQLDB: Embedding a relational database server into Eclipse, Part 1

How to write a plug-in that integrates the HSQLDB database server into the Eclipse Workbench

This article shows how to develop a plug-in that embeds the HSQLDB pure-Java relational database server into the Eclipse Workbench. Although not as powerful as DB2 and not as popular as MySQL, HSQLDB (the hypersonic SQL database) can satisfy the needs of a wide range of Java applications, because of its extensibility and low memory/processor requirements.

Share:

Fernando Lozano, Independent consultant

Fernando Lozano is a long-time open source zealot and Java developer, author of the book Java and GNU/Linux (available in Portuguese only). Among other pursuits, he sells services to organizations developing J2EE applications using Eclipse and other open source tools, helps maintain the Portuguese translation of the GNU Project Web site, and is part of the Linux Professional Institute Brazil council. You can reach Fernando at fernando at lozano.eti.br.



30 September 2003

Also available in Japanese Spanish

The hypersonic SQL database, whose official name was later changed to HSQLDB, is a pure-Java embedded relational database server that you can use in stand-alone mode (using direct file access) or in client/server mode, accepting many concurrent users. Although not as powerful as DB2 and not as popular as MySQL, HSQLDB can satisfy the needs of a wide range of Java applications, because of its extensibility and low memory/processor requirements.

HSQLDB is a convenient Java development database because it features a rich subset of Structured Query Language (SQL), and because Java programmers won't need to install a processor, memory, and disk-hungry database server into their development workstation. It's an ideal tool for integration into the Eclipse IDE, providing useful tools for both novice and experienced developers.

This article and the ones to follow in this series will show you how to build a set of Eclipse plug-ins that bring HSQLDB into the Eclipse Workbench. You'll see a real-world example of how to develop such a plug-in, considering API and user interface (UI) issues, and also how to evaluate alternative ways to bring desired functionality to a user. This article assumes you are using the Eclipse SDK distribution, not the Platform Runtime-Binary plus JDT. The latter would be well suited if the goal were just to develop a regular Java application.

In this series, we'll create and expand the plug-in set using three steps, according to the rationale described in "Levels of Integration" (see the Resources later in this article for a link):

  1. Launching existing tools from Eclipse, providing easy access to desired pre-existing tools from Workbench menus
  2. Exploring how other Eclipse features can be used to add value to the pre-existing set of tools, improving Java developer productivity
  3. Rewriting the tools using SWT for seamless integration into the Eclipse Workbench

Get to know HSQLDB

You can download HSQLDB, including sources and documentation, from SourceForge (hsqldb.sourceforge.net; see the link in Resources). This should not be confused with the original SourceForge project, hsql.sourceforge.net, which has been frozen.

The binary distribution is a standard ZIP file, and all you need to do is to unzip it somewhere on your hard drive. All HSQLDB components -- the database engine, server processes, JDBC driver, documentation, and a handful of utilities -- are provided in a single JAR package, installed in lib/hsqldb.jar, and about 260 KB. The database engine needs just 170 KB of RAM to run, making it adequate for PDAs, such as Zaurus from Sharp, and the entire download, including sources and documentation, is small enough to fit on a standard 1.44 MB floppy disk.

You can start the database server and utilities from the command prompt by invoking convenience classes such as org.hsqldb.Server and org.hsqldb.util.DatabaseManager, each of which accepts a small set of command-line options like "-url" (for remote connections), "-database" (for direct file access), and "-user". Also accepted is the "-?" option to provide help on valid command-line syntax.

The key to HSQLDB's simplicity is the serialization of SQL statement execution. That is, although many concurrent users can be connected to the database (when running in server mode), all SQL statements are placed in a queue and executed one at a time. Thus, there is no need to implement complicated locking and synchronization algorithms. In spite of that, HSQLB implements ACID (Atomicity, Consistency, Isolation, and Durability) semantics. In other words, it is a transactional database, but only at the read uncommitted level, without transaction isolation. HSQLDB was really created for embedded applications, not for the corporate data center.

If you want triggers, aggregated functions, outer joins, views, and other SQL features, they are all there (something you won't get from most lightweight relational databases). You can implement stored procedures by adding your Java classes do the HSQLDB classpath. Then you issue a CREATE FUNCTION statement and you're done. In fact, many standard SQL functions like SQRT and ABS are implemented as direct mappings to standard Java classes such as java.lang.Math.

HSQLDB modes of operation

The HSQLDB engine can be run in many modes to suit different application scenarios:

In-memory mode
All database tables and indexes are kept in memory and never saved to disk. Before you ask why anyone would want a database that is lost at application termination, think about having a local cache for database data that you can query, sort, group, and update using standard SQL statements.

Stand-alone mode
The application creates a database connection using JDBC, and the HSQLDB engine runs inside the application, accessing the database files directly. There can be no concurrent users (the application has exclusive access to the database files), but there are no additional threads nor TCP connection overhead. Stand-alone is the preferred mode for many embedded applications.

Server mode
This is the standard client/server database configuration similar to other relational databases, allowing concurrent connections using TCP sockets. Most developers will want this mode as it allows any JDBC client to connect and query/update tables while the main application is still running.

Web server mode
HSQLDB can act as a Web server, accepting SQL queries though HTTP, or can run as a servlet inside any standard Web container, passing though firewalls or installed on a Web hosting service without involving the provider support team (and expensive database hosting options). As HTTP is stateless, there are no transactions in this mode.

HSQLDB database file structure

HSQLDB keeps all table and index data in memory, saving all SQL statements issued into a file named database.script, which also acts as the transaction log. When the engine is initialized, this file is read and all SQL statements are processed, thus recreating the entire database. During shutdown, the HSQLDB engine will generate a new database.script file with the minimum set of statements for fast database startup.

Besides the default memory tables, HSQLDB also supports "cached" and "text" tables. Data for all cached tables is kept in a file named database.data, while text table data is kept in any delimited text file (like CSV files) named by the set table source non-standard SQL statement. While cached tables provide support for data sets bigger than available RAM, text tables are a convenient way to import and export data.

Besides the database.script and database.data files, any HSQLDB database may include a database.properties file, on which the administrator can set many parameters that affect ANSI SQL compatibility. All database files (except for text table data files) must be kept in the same directory.

There's no explicit way to create HSQLDB databases. If you ask the engine to open a non-existent database file (using either server mode -database option or a stand-alone mode JDBC URL), the file and containing directory will be created. So, if you are sure there was data in that empty database, check for typing errors.

Let's now start developing the plug-in!


Creating the HSQLDB Eclipse set of plug-ins

Putting an existing application inside a powerful tool like Eclipse is not a trivial task. Fortunately, both HSQLDB and Eclipse ease the task, since HSQLDB is embeddable in other applications, and since Eclipse provides a clean, easy-to-understand plug-in infrastructure and a robust development environment for creating new plug-ins, the PDE. Even if you have no previous exposure to Eclipse plug-in development, the PDE makes the task very easy. See the articles covering Eclipse basics in the Resources section later in this article.

These instructions assume you are using the Eclipse SDK distribution, not the Platform Runtime-Binary plus JDT. The latter would be well suited if your goal were just to develop a regular Java application. You can use your preferred operating system, as we'll only use Java code and no native code at all.

We'll start with the minimum effort to create a useful set of plug-ins and write the smallest amount of code. Later, in the next parts of this series, we'll see how Eclipse features can be leveraged to provide added value to HSQLDB.

In this article, we'll focus on the following functionality for our code:

  1. Start the HSQLDB engine in server mode, so both the user application and an SQL console (like HSQLDB's own DatabaseManager utility) can run SQL statements at the same time.
  2. Stop the HSQLDB database cleanly.
  3. Invoke the DatabaseManager utility so developers can enter SQL statements interactively from the Workbench.
  4. Run SQL script files using the HSQLDB ScriptTool utility. Over the years, I have kept many *.sql files in my project folders, to create database tables and insert test data, and have long wished for an easy way to run them.
  5. Configure HSQLDB connection properties, such as TCP port and administrator password.

How will these functions be made available to the Workbench? The easiest way to accomplish the first three is to provide an actionSet, added to a new top-level menu and also accessible from its own toolbar. The action to run SQL script files must be tied only to files with a "*.sql" extension, so this will be an objectContribution, added by the Workbench to the pop-up menus on any views that display these files. Finally, the connection parameters fit nicely into a plug-in preferences page, accessible from the Workbench Window menu.

Figure 1 shows the new menu and toolbar, while Figure 2 shows the new entry in the pop-up menu for the Navigator view, and Figure 3 shows the property page, so you can get a glimpse of what the first release of our plug-in set will look like.

Figure 1. HSQLDB menu and associated toolbar
HSQLDB menu and associated toolbar
Figure 2. Pop-up menu item added to *.sql files
Popup menu item added to *.sql files
Figure 3. HSQLDB connection properties
HSQLDB connection properties

Turning HSQLDB into an Eclipse plug-in

The first step in building our set of plug-ins is to package HSQLDB itself as an Eclipse plug-in. This plug-in will contain only hsqldb.jar and the needed plugin.xml file required by the Workbench. If you've browsed the standard Eclipse plug-ins directory, you've already found that JUnit, Xerces, Tomcat, and other popular Java packages are isolated in their own plug-ins, unchanged, and that all Eclipse specifics are encapsulated in other plug-ins. This compartmentalization makes it easy to update these third-party tools, which don't necessarily require changes to Eclipse itself. Another benefit is the ease of sharing these common libraries with many plug-ins.

Open your Eclipse SDK installation and create a new plug-in project; name it hsqldb.core. (I know the recommended name would be org.hsqldb.core, but I won't pretend to take the HSQLDB namespace. Perhaps HSQLDB developers reading this will like this idea and recommend it as the "official" Eclipse integration plug-in; in that case the name would probably be changed.) Be sure to select the "Empty Plugin" option instead of any plug-in template; otherwise, you'll get a useless top-level plug-in class, which can be safely deleted if you were anxious and already created it. Copy hsqldb.jar from your HSQLDB installation to the project directory and add it to the Runtime of the project. You can do this by using the PDE Plugin Manifest Editor or by simply copying the contents from Listing 1.

Listing 1. plugin.xml manifest file for the hsqldb.core plug-in
<?xml version="1.0" encoding="UTF-8"?>
<plugin
   id="hsqldb.core"
   name="Hsqldb Core Plug-in"
   version="0.0.1"
   provider-name="Fernando Lozano (www.lozano.eti.br)">

   <runtime>
      <library name="hsqldb.jar">
         <export name="*"/>
      </library>
   </runtime>

</plugin>

Adding Workbench extensions

Next, create a second plug-in project named hsqldb.ui. This will contain all Eclipse extensions for the first revision of the plug-in set: an action set containing three actions, an object contribution associated to *.sql files, and a property page. Name its main class (Plugin class) PluginUi, accepting the default package name hsqldb.ui.

Open the plugin.xml file using the Plugin Manifest Editor, and select the Extensions tab. Rename the contributed menu to HSQLDB and change the sample action so it displays the label "Runs HSQLDB Database Manager". Add two other actions to the same actionSet with labels "Stops HSQLDB database server" and "Starts HSQLDB database server", providing each with a unique action id and implementation class. Please note that the actions will be displayed on the menu and toolbar in the reverse order of creation (that is the reverse order of appearance in the plugin manifest file).

Click the Add button to add two new extensions using the Extension templates, a pop-up menu and a property page. The pop-up menu should be associated with the *.sql file pattern, but the property page fields will be set programmatically.

Figure 4 shows how the Plugin Manifest editor should appear at the end, displaying all plug-in extensions; Figure 5 displays the properties for the object contribution (note the filter for *.sql files), and Figure 6 shows the properties for the "Run SQL Script" action for the file resources pop-up menu. The icons are in the source code for this article (see Resources), but I'm sure you know a graphics expert who can draw better ones!

Figure 4. HSQLDB actions on the manifest editor
HSQLDB actions on the manifest editor
Figure 5. HSQLDB object contribution on the manifest editor
HSQLDB object contribution on the manifest editor
Figure 6. Run SQL Script action on the manifest editor
Run SQL Script action on the manifest editor

Before code can be added to our actions, we need to tell this UI plug-in about the corresponding core plug-in, otherwise it won't have access to HSQLDB classes. Change to the "Dependencies" page on the Plugin Manifest Editor and add a plug-in dependency, as shown in Figure 7. Some dependencies, like "eclipse.ui", were configured automagically by the PDE, while others, like "org.eclipse.debug.core" will be added when the actions are coded. If you prefer to edit XML code directly, Listing 2 shows the complete plugin.xml file for the hsqldb.ui plug-in.

To complete the plug-in setup, add a new Class to the hsqldb.ui package, and name it HsqldbUtil. This class will contain all code that deals directly with HSQLDB, keeping the contributed extensions code simple.

Figure 7. hsqldb.ui plug-in dependencies
hsqldb.ui plug-in main proprieties
Listing 2. plugin.xml manifest file for the hsqldb.ui plug-in
<?xml version="1.0" encoding="UTF-8"?>
<plugin
   id="hsqldb.ui"
   name="Hsqldb Ui Plug-in"
   version="0.0.1"
   provider-name="Fernando Lozano (www.lozano.eti.br)"
   class="hsqldb.ui.PluginUi">

   <runtime>
      <library name="ui.jar"/>
   </runtime>
   <requires>
      <import plugin="org.eclipse.core.resources"/>
      <import plugin="org.eclipse.ui"/>
      <import plugin="hsqldb.core"/>
      <import plugin="org.eclipse.debug.core"/>
      <import plugin="org.eclipse.jdt.launching"/>
      <import plugin="org.eclipse.debug.ui"/>
   </requires>

   <extension
         point="org.eclipse.ui.actionSets">
      <actionSet
            label="Hsqldb"
            visible="true"
            id="hsqldb.ui.actionSet">
         <menu
               label="Hsql&db"
               id="hsqldbMenu">
            <separator
                  name="dbServerGroup">
            </separator>
         </menu>
         <action
               label="Run Hsql &Database Manager"
               icon="icons/dbman.gif"
               tooltip="Runs the Hsql database manager"
               class="hsqldb.ui.actions.HsqldbDatabaseManagerAction"
               menubarPath="hsqldbMenu/dbServerGroup"
               toolbarPath="dbServerGroup"
               id="hsqldb.ui.actions.HsqldbDatabaseManagerAction">
            <enablement>
               <pluginState
                     value="activated"
                     id="hsqldb.ui">
               </pluginState>
            </enablement>
         </action>
         <action
               label="S&top Hsqldb"
               icon="icons/stop.gif"
               tooltip="Stops the Hsql database server"
               class="hsqldb.ui.actions.HsqldbStopAction"
               menubarPath="hsqldbMenu/dbServerGroup"
               toolbarPath="dbServerGroup"
               id="hsqldb.ui.actions.HsqldbStopAction">
            <enablement>
               <pluginState
                     value="activated"
                     id="hsqldb.ui">
               </pluginState>
            </enablement>
         </action>
         <action
               label="&Start Hsqldb"
               icon="icons/start.gif"
               tooltip="Starts the Hsql database server"
               class="hsqldb.ui.actions.HsqldbStartAction"
               menubarPath="hsqldbMenu/dbServerGroup"
               toolbarPath="dbServerGroup"
               id="hsqldb.ui.actions.HsqldbStartAction">
            <enablement>
               <pluginState
                     value="installed"
                     id="hsqldb.ui">
               </pluginState>
            </enablement>
         </action>
      </actionSet>
   </extension>
   <extension
         point="org.eclipse.ui.perspectiveExtensions">
      <perspectiveExtension
            targetID="org.eclipse.ui.resourcePerspective">
         <actionSet
               id="hsqldb.ui.actionSet">
         </actionSet>
      </perspectiveExtension>
   </extension>
   <extension
         point="org.eclipse.ui.popupMenus">
      <objectContribution
            objectClass="org.eclipse.core.resources.IFile"
            nameFilter="*.sql"
            id="hsqldb.ui.SQLScriptFiles">
         <action
               label="Run SQL Script"
               class="hsqldb.ui.popup.actions.HsqldbRunScript"
               menubarPath="additions"
               enablesFor="1"
               id="hsqldb.ui.HsqldbRunScript">
            <enablement>
               <pluginState
                     value="activated"
                     id="hsqldb.ui">
               </pluginState>
            </enablement>
         </action>
      </objectContribution>
   </extension>
   <extension
         id="hsqldb.ui.preferences"
         point="org.eclipse.ui.preferencePages">
      <page
            name="HSQLDB Server"
            class="hsqldb.ui.preferences.HSQLDBPreferencePage"
            id="hsqldb.ui.preferences.HSQLDBPreferencePage">
      </page>
   </extension>
</plugin>

Starting HSQLDB

It's easy to start the HSQLDB engine in server mode using the class org.hsqldb.Server. As command-line arguments, it gets the database name (path + base name for database files), TCP port to listen for connection requests, and a flag telling if it should call System.exit() at shutdown. The following command line would be typical of running HSQLDB:

java -cp /opt/hsqldb/hsqldb.jar org.hsqldb.Server -database /tmp/bd -port 9001 -system_exit=true

The above line creates database files /tmp/db.script, /tmp/db.properties, and /tmp/db.data.

We can just use the Server class main method and pass the arguments as an array of Strings. But we have to do this inside a new thread; otherwise, we'll lock the entire Workbench. The plugin class, which is a singleton, keeps a reference to this thread so it can check if there's a server that started running before trying to connect to it, and also to check if it's actually running, because any client can connect and submit the SHUTDOWN statement, terminating the server.

The code in Listing 3 shows the run method for hsqldb.ui.actions.HsqldbStartAction, and Listing 4 shows the code for the startHsqldb from hsqldb.ui.HsqldbUtil, which actually starts the server. Later, we'll discuss the techniques for managing user feedback.

The most interesting part of this code is how we find a location to keep the database files. A project named .hsqldb is created, if it does not exist, and inside it the engine will look for database.script and other database files.

Eclipse provides all plug-ins in a standard directory where any configuration file can be saved. Such a directory can be obtained by calling plugin.getStateLocation, but I don't feel that database files are actually plug-in configuration files. They look more like user data files, and, as such, they should be inside a project in the developer workspace.

Listing 3. Action to start HSQLDB in server mode, from hsqldb.ui.actions.HsqldbStartAction
    public void run(IAction action) {
        // check a database was really started by the plug-in
        PluginUi plugin = PluginUi.getDefault();
        if (plugin.getHsqldbServer() != null) {
            ((ApplicationWindow)window).setStatus(
                "HSQLDB Server already running.");
        }
        else {
            Cursor waitCursor = new Cursor(window.getShell().getDisplay(),
                SWT.CURSOR_WAIT);
            window.getShell().setCursor(waitCursor);
            try {
                HsqldbUtil.startHsqldb();
                ((ApplicationWindow)window).setStatus("HSQLDB Server started.");
            }
            catch (CoreException e) {
                MessageDialog.openError(window.getShell(),
                    "Hsqldb Plugin",
                    "Could not create HSQLDB database project.");
                e.printStackTrace(System.err);
            }
            finally {
                window.getShell().setCursor(null);
                waitCursor.dispose();
            }
        }
    }
Listing 4. Code that really starts HSQLDB in server mode, from hsqldb.ui.HsqldbUtil
    public static void startHsqldb() throws CoreException {
        PluginUi plugin = PluginUi.getDefault();
        // finds project local path for database files
        IWorkspaceRoot root = PluginUi.getWorkspace().getRoot();
        IProject hsqldbProject = root.getProject(".hsqldb");
        if (!hsqldbProject.exists()) {
            hsqldbProject.create(null);
        }
        hsqldbProject.open(null);
        IPath dbPath = hsqldbProject.getLocation();
        final String database = dbPath.toString() + "/database";
        // starts a new thread to run the database server
        final HsqldbParams params = getConnectionParams();
        Thread server = new Thread() {
            public void run() {
                String[] args = { "-database", database,
                    "-port", String.valueOf(params.port),
                    "-no_system_exit", "true" };
                Server.main(args);
            }
        };
        plugin.setHsqldbServer(server);
        server.start();
    }

Stopping HSQLDB

To stop the HSQLDB server, all that's needed is a SHUTDOWN command as an SQL statement. The method stopHsqldb from hsqldb.ui.HsqldbUtil, shown in Listing 5, does just that. The code for the corresponding action will be much the same as that described for starting the server, so it won't be listed here. Exceptions ClassNotFoundException (from Class.forName) and SQLException are thrown so that the action code can provide adequate feedback to the user.

Listing 5. Code to stop the HSQLDB server
    public static void stopHsqldb() throws ClassNotFoundException,
                SQLException {
        PluginUi plugin = PluginUi.getDefault();
        HsqldbParams params = getConnectionParams();
        // submits the SHUTDOWN statement
        Class.forName("org.hsqldb.jdbcDriver");
        String url = "jdbc:hsqldb:hsql://127.0.0.1:" + params.port;
        Connection con = DriverManager.getConnection(url, params.user,
            params.passwd);
        String sql = "SHUTDOWN";
        Statement stmt = con.createStatement();
        stmt.executeUpdate(sql);
        stmt.close();
        // no need to close a dead connection!
        plugin.setHsqldbServer(null);
    }

Running the HSQL Database Manager

Running the Database Manager utility from HSQLDB is a bit more involved than running the server itself. While the server was created to be embeddable in other applications, the utility was intended to be run as a stand-alone application or as a Java applet. Although both modes accept command-line arguments (or applet parameters), we do not want the extra overhead of starting a new Java VM just to get a Window where the user can type SQL statements. Calling the class static void main(String[] args) directly won't work as expected, because it would invoke System.exit() and so terminate the Workbench.

Browsing the org.hsqldb.util.DatabaseManager source code, we find that it could be easily instantiated and initialized with a JDBC connection, but the required methods are not public. So we can create a class named org.hsqldb.util.PatchedDatabaseManager inside the HSQLDB source tree and use the supplied Ant build script to generate a new hsqldb.jar containing our patched Database Manager. Just two methods need to have their visibility changed to public: void main() and void connect(Connection con). Using them, Listing 6 shows how the plug-in runs the patched class.

The Database Manager is an AWT application (see Figure 8), which will create its own event thread (independent of the Eclipse SWT one) and be terminated by closing the Workbench, which calls System.exit(). There's no problem running multiple instances of the utility, except for the lack of ties to the Workbench window. That's fine for the first revision of our HSQLDB plug-in, but before this series is finished, we'll have to change it to be an SWT application, hosted as an Eclipse view.

Figure 8. HSQLDB Database Manager
HSQLDB Database Manager
Listing 6. Starting the patched Database Manager utility
    public static void runDatabaseManager() throws ClassNotFoundException,
                SQLException {
        PluginUi plugin = PluginUi.getDefault();
        HsqldbParams params = getConnectionParams();
        // creates a connection to the internal database 
        String url = "jdbc:hsqldb:hsql://127.0.0.1:" + params.port;
        Class.forName("org.hsqldb.jdbcDriver");
        Connection con = DriverManager.getConnection(url, params.user,
            params.passwd);
        if (con != null) {
            // needed to patch DatabaseManager so it could
            // be initialized and use the supplied connection 
            PatchedDatabaseManager dm = new PatchedDatabaseManager();
            dm.main();
            dm.connect(con);
        }        
    }

Running SQL scripts

The HSQLDB Script Tool reads plain text files and executes the included SQL statements against a provided JDBC URL. Batches of SQL statements are separated by the go command, and scripts can also use the print command to write messages from the script.

Developers familiar with other database systems may create scripts containing just SQL statements delimited by semicolons (;), but this makes HSQLDB run all scripts in a single batch, returning just the last statement results. You need to include the go command between SQL statement to receive results from each one.

The easiest way to let the user browse script results is to put then in a console view, just like Java applications and Ant build scripts invoked from the Workbench. This requires creating a Java Launch configuration, which in turn creates another Java VM. As SQL scripts are short-lived, we can consider the overhead to be acceptable, and if you think the Database Manager should also be invoked in its own VM, you can use the runScriptTool method presented in Listing 7 as an example.

Listing 7 is the longest listing in this first article, and most of it is related to setting up a classpath containing the correct JRE bootstrap classes (which must be set explicitly) and hsqldb.jar from the hsqldb.core plug-in. Check the Resources section at the end of this article for more information about the launch framework provided by Eclipse and extensions provided by the JDT.

To developers familiar with other GUI toolkits, it's not obvious how to get the path for the selected SQL script file. The IObjectActionDelegate interface, which is implemented by object contributions that extend resource pop-up menus, receives on its run method just a reference to the action itself, just like a top-level menu action, containing no information about the selected resource or the originating control. The resource reference is actually provided to selectionChanged method and has to be saved as an instance variable so it can be used used later -- see Listing 8.

Listing 7. Running the HSQLDB Script Tool
    public static void runScriptTool(IFile currentScript) throws CoreException {
        PluginUi plugin = PluginUi.getDefault();
        // destroys any preexisting configuration and create a new one
        ILaunchManager manager = DebugPlugin.getDefault().getLaunchManager();
        ILaunchConfigurationType type = manager.getLaunchConfigurationType(
            IJavaLaunchConfigurationConstants.ID_JAVA_APPLICATION);
        ILaunchConfiguration[] configurations = manager.getLaunchConfigurations(
            type);
        for (int i = 0; i > configurations.length; i++) {
            ILaunchConfiguration config = configurations[i];
            if (config.getName().equals("SQL Script")) {
                config.delete();
            }
        }
        ILaunchConfigurationWorkingCopy wc = type.newInstance(null,
            "SQL Script");
        // constructs a classpath from the default JRE...
        IPath systemLibs = new Path(JavaRuntime.JRE_CONTAINER);
        IRuntimeClasspathEntry systemLibsEntry =
            JavaRuntime.newRuntimeContainerClasspathEntry(
                systemLibs, IRuntimeClasspathEntry.STANDARD_CLASSES);
        systemLibsEntry.setClasspathProperty(
            IRuntimeClasspathEntry.BOOTSTRAP_CLASSES);
        //... plus hsqldb.core plugin
        IPluginRegistry registry = Platform.getPluginRegistry();
        IPluginDescriptor hsqldbCore = registry.getPluginDescriptor(
            "hsqldb.core");
        ILibrary[] libs = hsqldbCore.getRuntimeLibraries();
        String installDir = hsqldbCore.getInstallURL().toExternalForm();
        URL hsqldbJar = null;
        try {
            hsqldbJar = Platform.asLocalURL(new URL(installDir +
                libs[0].getPath()));
        }
        catch(Exception e) {
            // ignore URL exceptions
        }
        IRuntimeClasspathEntry hsqldbEntry =
            JavaRuntime.newArchiveRuntimeClasspathEntry(new Path(
                hsqldbJar.getPath()));
        hsqldbEntry.setClasspathProperty(IRuntimeClasspathEntry.USER_CLASSES);
        // sets the launch configuration classpath
        List classpath = new ArrayList();
        classpath.add(systemLibsEntry.getMemento());
        classpath.add(hsqldbEntry.getMemento());
        wc.setAttribute(IJavaLaunchConfigurationConstants.ATTR_CLASSPATH,
            classpath);
        wc.setAttribute(IJavaLaunchConfigurationConstants.ATTR_DEFAULT_CLASSPATH,
            false);
        // current directory should be the script container
        IPath dir = currentScript.getParent().getLocation();
        wc.setAttribute(IJavaLaunchConfigurationConstants.ATTR_WORKING_DIRECTORY,
            dir.toString());
        // gets the path for the selected SQL script file
        wc.setAttribute(IJavaLaunchConfigurationConstants.ATTR_MAIN_TYPE_NAME,
            "org.hsqldb.util.ScriptTool");
        // builds ScriptTool command line
        HsqldbParams params = getConnectionParams();
        String args = "-driver org.hsqldb.jdbcDriver " +
            "-url jdbc:hsqldb:hsql: " +
            "-database //127.0.0.1:" + params.port + " " +
            "-user " + params.user + " " +
            "-script " + currentScript.getName();
        if (params.passwd.length() > 0)
            args += "-password " + params.passwd + " ";
        wc.setAttribute(IJavaLaunchConfigurationConstants.ATTR_PROGRAM_ARGUMENTS,
            args);
        // saves the new config and launches it
        ILaunchConfiguration config = wc.doSave();
        DebugUITools.launch(config, ILaunchManager.RUN_MODE);
    }
Listing 8. How to get the SQL Script to be executed, from hsqldb.ui.popup.actions.HsqldbRunScript
    public void selectionChanged(IAction action, ISelection selection) {
        currentScript = null;
        if (selection != null) {
            if (selection instanceof IStructuredSelection) {
                IStructuredSelection ss = (IStructuredSelection)selection;
                // as this action is enabled only for a single selection,
                // it's enough to get the first element
                Object obj = ss.getFirstElement();
                if (obj instanceof IFile) {
                    currentScript = (IFile)obj;
                }
            }
        }
    }

HSQLDB properties

With most of the functionality of our plug-in set now in place, it's only missing a way to configure server connection parameters: the TCP port it listens to, administrator user name, and administrator user password. The TCP port may have to be changed so it won't conflict with other applications installed on the developer's machine; the user and password can be changed by SQL statements from any client. These parameters can be put into a C-like structure (or pascal-like record) class named HsqldbParams, which is shown in Listing 9. It also declares constants used by the Plugin Preferences Store and Property Page to fetch and save the parameters (preferences) values.

Listing 9. Preferences structure for HSQLDB server connection parameters
package hsqldb.ui;

public class HsqldbParams {
    // preference names for the plugin
    public static final String P_PORT = "serverPort";
    public static final String P_USER = "serverUser";
    public static final String P_PASSWD = "serverPasswd";
    
    public int port = 9001;
    public String user = "sa";
    public String passwd = "";
}

Unfortunately, the preference page class generated by the PDE New Extension Wizard is a bit misleading, including a method to set default preference values that won't be used by the plugin preferences store. The correct place for initializing defaults is the main Plugin class itself. Otherwise, all preferences that are at their default values will be returned by the preferences store as 0 or null.

Thus, the preferences page class becomes much simpler, as shown in Listing 10, and the method initializeDefaultPreferences is added to the PluginUi class as shown in Listing 11. Note the default values for each preference are defined by the preferences structure, not the plugin class nor the preference page class.

Listing 10. Preferences page for HSQLDB plug-in
public class HSQLDBPreferencePage
    extends FieldEditorPreferencePage
    implements IWorkbenchPreferencePage {

    public HSQLDBPreferencePage() {
        super(GRID);
        setPreferenceStore(PluginUi.getDefault().getPreferenceStore());
        setDescription("Connection parameters for the embedded HSQLDB server");
    }
    
    public void createFieldEditors() {
        addField(new IntegerFieldEditor(HsqldbParams.P_PORT, 
                "&TCP Port:",
                getFieldEditorParent()));
        addField(new StringFieldEditor(HsqldbParams.P_USER,
                "Administrator &User:",
                getFieldEditorParent()));
        addField(new StringFieldEditor(HsqldbParams.P_PASSWD,
                "Administrator &Password:",
                getFieldEditorParent()));
    }
    
    public void init(IWorkbench Workbench) {
    }
}
Listing 11. Changed methods from the generated Plugin class
    public void shutdown() throws CoreException {
        // shuts down the server if running
        Thread server = getHsqldbServer();
        if (server != null && server.isAlive()) {
        		 try {
                HsqldbUtil.stopHsqldb();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
        }
        super.shutdown();
    }

    protected void initializeDefaultPreferences(IPreferenceStore store) {
        super.initializeDefaultPreferences(store);
        HsqldbParams params = new HsqldbParams();
        store.setDefault(HsqldbParams.P_PORT, params.port);
        store.setDefault(HsqldbParams.P_USER, params.user);
        store.setDefault(HsqldbParams.P_PASSWD, params.passwd);
    }

Providing feedback to the user

As the plug-in set provides neither a view nor an editor to the Workbench, there are limited ways to provide user feedback on the actions. We can use the status line of the Workbench window, and also an SWT MessageDialog so that important events (mostly errors) aren't missed by the user.

The visibility and enablement model for Workbench actions is focused on workspace resource selection, but most plug-in actions (starting HSQLDB server, stopping it, and starting the Database Manager) don't depend on resource selection, but rather on whether the server is running or not running -- conditions that have to be explicitly checked by each action run method.

Caveat: plugin and action classes are not instantiated until absolutely required to lower Workbench memory requirements. The manifest file contents are used to present menu choices and to enable/disable them, but as the HSQLDB server is not a workspace resource, it can't enable actions. You can, as described for the manifest code in Listing 2, (see the <enablement> element) enable/disable an action based on plug-in activation, so at startup just the "Start HSQLDB server" action is enabled, but after that there's no easy way to, say, disable this action if the server is already running, and then re-enable it when it's stopped.

Note the code used to put an hourglass cursor on the Workbench window, and the code used to set the status line message. These are not easy to find in PDE documentation or Eclipse.org articles.


Final touches

The plugin class overrides the shutdown method so it can cleanly shut down the server when the Workbench is closed, and we're finished. Of course, a complete plug-in set would require additional tasks not covered here, such as:

  • Package the two plug-ins as a feature, so that all are installed, removed, enabled, and disabled as a unit.
  • Provide help docs for the plug-in itself, and include HSQLDB docs in a format suitable for the Workbench help manager.

Conclusion

This article showed how to create a set of plug-ins that bring the HSQLDB database into the Eclipse Workbench, adding the ability to start and stop the database server, as well as running SQL statements and scripts. We've seen how the PDE makes creating this plug-in easy, via wizards and editors for most tasks, leaving the developer with little more than the task of creating the code that actually implements the desired functionality.

In the next part of this series, you'll learn how to leverage the Workbench features to add value to HSQLDB development, instead of simply running pre-existing tools.


Download

DescriptionNameSize
Code sampleos-echsql/hsqldbplugin1.zip---

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Open source on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Open source, Java technology
ArticleID=10875
ArticleTitle=Eclipse and HSQLDB: Embedding a relational database server into Eclipse, Part 1
publish-date=09302003