Hacking J-Bird (Version 0.5.2): Alternative Database Engines

You can use database engines other than the engine that is distributed with J-Bird (MckoiDB). Unfortunately, compatibility between engines is less than ideal. Not all engines implement SQL in the same manner, and not all JDBC drivers implement the entire standard. If you really want to pursue an alternative driver, you are in for an adventure that may be frustrating. Plan on compiling J-Bird for yourself and be willing to get into the guts of the code. A Number of engines have been tried with J-Bird. Some comments follow. Note that in the list below, only Postgres is likely to work.

• Firebird is free and open-source, but it is currently incompatible with J-Bird because it does not support XXX. Testing stopped with that incompatibility and other incompatibilities may exist. Firebird was last evaluated at about the time of J-Bird 0.1.0. Firebird advanced considerably since then but has not been evaluated again.



• HSQLDB is a free, open-source, light-weight engine. It is not currently compatible with J-Bird (J-Bird 0.4.0 and HSQLDB 1.7.2 Release Candidate 5). Briefly, query ResultSets are not compatible. To obtain ResultSets that are scrollable in both directions, HSQLDB requires that they be explicitly requested. J-Bird does not make explicit requests because that is unnecessary in MckoiDB, MySQL and Postgres SQL. The issue is fixable and should be resolved in the distant future. It is unknown whether other incompatibilities exist.



• InstantDB is really fast, and it is written in Java. InstantDB is neither free nor open-source. It is incompatible with J-Bird because it does not support joins of more than two tables.



• Microsoft Access is not free, but it is popular. It is incompatible with J-Bird if accessed through Sun's JDBC- to-ODBC bridge because the bridge does not support some methods of ResultSet. I have no experience with commercial bridges or Access drivers.



• Microsoft SQL Server has not been tested with J-Bird.



• MySQL is a popular free, open-source database engine. It is incompatible with J-Bird because it does not yet support sub-queries. MySQL was expected to support sub-queries in version 3, but queries that work fine with Mckoi DB and Postgres SQL in J-Bird 0.4.0 do not work with MySQL version 4.0.18. Although the MySQL documentation suggests that most sub-queries can be avoided through creative use of joins, I haven't yet figured out how to do that and want to work on other things.



• Postgres SQL is popular, free, and open-source. It seems to be compatible with J-Bird although it has not been thoroughly tested. It was last evaluated with J-Bird 0.4.0.

To facilitate evaluation of drivers the class net.sourceforge.jbird.TestDB is under development. It is not packaged with the binary version of J-Bird, but it is part of the CVS tree. TestDB attempts to try JDBC classes and methods that are used with J-Bird as well as SQL. This is done in a very ad hoc manner and needs to be formalized. Use of Junit is under consideration.

Variation in SQL syntax among database engines is accommodated by using resources that are specific to particular database engines. Variable syntax is provided in a series of files called sql-name_of_driver in the J-Bird install directory. So far, use of these resources has been restricted to SQL create statements used by the database builder. It has not been necessary to support idiosyncrasies of SQL syntax in J-Bird itself, and use of resources has not yet been implemented in J-Bird. The need will undoubtedly arrive.

SQL resources work as follows. When the database builder starts, it looks for a serialized version of resources for the driver in use. If it finds serialized resources, it loads them. Otherwise, it loads and parses the non-serialized version and then saves a serialized version for future use. Serialized versions of SQL resources are stored in files named sql-ser-name_of_driver in the J-Bird directory.

Modify J-Bird.prefs to configure J-Bird and the database builder to use a particular database engine. Included in J-Bird.prefs are the driver class, database URL, username, and password. Sample J-Bird.prefs files are available in the CVS tree for a few drivers.

You are on your own for setting up database engines.

It is a good idea to place the jar file(s) for the JDBC driver somewhere in the J-Bird directory tree. Since J-Bird 0.4.1, the installer can dump the contents of the database to CSV files automatically before upgrades from J-Bird versions 0.4.0 or later. This will work with alternative database engines if the jar files (or symbolic links to them) are anywhere in the J-Bird directory tree. Also, the installer may also decide to rebuild the database from CSV values after the upgrade or to modify the database schema. It will be able to do these things if the JDBC drivers can be found in the J-Bird directory tree.