OpenOffice.org (OOo) is an open source "Office" suite of
applications that offers platform-independence and zero license fees. It
has a very promising database interface that allows the user to define database
tables, modify the data in tabular form and access it via forms and reports - a
RAD environment.
Last year, while developing HSQLDB 1.7.2, we were contacted on our dev list by Hermann Kienlein who suggested that HSQLDB could become accessible from OOo by changing a few JDBC parameters. As a result, HSQLDB 1.7.2 can be used with OOo 1.1, although speed of access seems to be slow as no extra work was done to optimise and enhance interoperation.
Earlier this year the maintainer of OOo's database access enquired if we could co-operate and stream HSQLDB's file access into their file format. The OOo team had done their own technical studies on this. After some exchanges and clarification of technical issues, we came to the conclusion that this was practical and relatively easy to achieve. I am pleased to say that we are currently working together towards this goal for OOo 2 and HSQLDB 1.7.3.
HSQLDB is well suited for integration in RAD environments. For example, to ensure that a date entered into a given field is later than today, you just write "mydate > CURRENT_DATE" in a check constraints. Other features such as cascading deletes and updates allow you to automate dependency management among a group of tables or rows in the same table. Another RAD feature that we support is the ability to add and remove columns and constraints as and when necessary. A highly advanced *standards compliant* SQL grammar in SELECT statements which includes correlated subselects, nested union, intersect and difference set operations, advanced aggregate functions that can be combined or nested in function calls and many more, allows complex database applications to be written with little effort. Very extensive metadata reporting of tables, views, constraints, indexes etc. makes it easy to integrate into a database design application.
Last year, while developing HSQLDB 1.7.2, we were contacted on our dev list by Hermann Kienlein who suggested that HSQLDB could become accessible from OOo by changing a few JDBC parameters. As a result, HSQLDB 1.7.2 can be used with OOo 1.1, although speed of access seems to be slow as no extra work was done to optimise and enhance interoperation.
Earlier this year the maintainer of OOo's database access enquired if we could co-operate and stream HSQLDB's file access into their file format. The OOo team had done their own technical studies on this. After some exchanges and clarification of technical issues, we came to the conclusion that this was practical and relatively easy to achieve. I am pleased to say that we are currently working together towards this goal for OOo 2 and HSQLDB 1.7.3.
HSQLDB is well suited for integration in RAD environments. For example, to ensure that a date entered into a given field is later than today, you just write "mydate > CURRENT_DATE" in a check constraints. Other features such as cascading deletes and updates allow you to automate dependency management among a group of tables or rows in the same table. Another RAD feature that we support is the ability to add and remove columns and constraints as and when necessary. A highly advanced *standards compliant* SQL grammar in SELECT statements which includes correlated subselects, nested union, intersect and difference set operations, advanced aggregate functions that can be combined or nested in function calls and many more, allows complex database applications to be written with little effort. Very extensive metadata reporting of tables, views, constraints, indexes etc. makes it easy to integrate into a database design application.
Other features also make HSQLDB very suitable for integration
into an "office" application. HSQLDB has always had crash-proof features in
the form a redo log. In version 1.7.2 this has become even more solid and
no committed data is lost if the machine is turned off at any time. The
compact persistence scheme for disk-resident (cached) tables is another
example. As HSQLDB does not pre-allocate fixed blocks for rows or data, there is
no waste when some rows need only 100 bytes while others take 1000 or 10000
bytes. The amount of cache memory can be fully controlled from inside the
application for different purposes and platforms. Another features is the high
speed, comparable to the fastest commercial and open-source (non-Java) SQL
engines in many tests. Changes that affect a few rows are always
instantaneous. For typical desktop database applications, which are not
likely to contain more than 50000 rows per table or more than two or three
times that in the database, even operations that modify all the rows of a
table can be performed in a few seconds. (create and populate test database with
50,000 rows [4 sec]; alter table test add column fullname varchar [2 sec];
update test set fullname = firstname || ' ' || lastname [4 sec]; update test set
zip = 2222 where zip > 2000 and zip <3000 [12131 rows updated in 1
sec]; [tests were done in our Database Manager using a table produced with our
TestCacheSize test class, with an average 100 characters of data per
row).
HSQLDB meets the technical requirements for a database
engine that can be used by individuals and in scientific, corporate, government
and non-profit organisations. It allows sophisticated desktop applications
to be developed for smaller data sets while the SQL and internal treatment of
data is so standards compliant that the applications can be scaled up easily to
multi-gigabyte data sets and served by central RDBMS servers such as Firebird,
PostgreSQL or commercial alternatives.
Providing reliable support for these features was not an easy task -- we worked over two years on both the engine core and high level commands and interfaces and the software has undergone very extensive tests and bug fixes. For the next version we will consolidate the existing features by measures such as automatic reuse of disk space after a table is dropped or altered, allowing primary key and identity columns to be added and dropped (in addition to existing ALTER commands for constraints and columns), and even more extensive support for advanced SQL language features.
The use of HSQLDB will be spreading in other directions too. The latest
1.7.2 can be compiled with gcj and accessed from
C++ via the CNI interface. This will allow direct access by
non-Java tools and programming languages such as Perl and PhP, while
still maintaining full database file compatibility in all platforms. Developing
a dedicated ODBC/JDBC bridge is not such a big task. We will invite
developers with ODBC (and other database connectivity) experience to work with
us and develop the connectivity.
Fred Toussi
Maintainer, HSQLDB Project
