Deployment Scenarios

A Warehouse Application

A data collection specialist describes how he uses HSQLDB to power an ordering and warehouse application:

I've deployed HSQLDB (initially version 1.8, now already 2.0) as a server for warehouse applications for multiple customers.

Such installations usually service about 30 JDBC connections, of which, during daytime, well over 25 may be actively processing data, and at least one (belonging to a server-based data exchange and archiving application) does so constantly and with notable intensity.

Data volumes have ranged as high as 250,000 articles and 50,000 saldos. Most of workload is generated by sales orders, of which about 50,000 are kept in the active database ,together with about 500,000 associated order and picking batch rows. About 500 orders tend to be processed daily. For our typical customers, this amounts to about 3 months' worth of data kept in a database schema for everyday work, while an archive schema hosts more historical information.

Systems usually consist of a Linux-based database server, a number of portable data collection terminals operating over wireless LAN, running either Windows CE, Windows Mobile or compact distributions of Linux, somewhat less of fixed warehouse workstations, and a couple of workstations for using a management interface built on OpenOffice Base.

I preferred HSQLDB since its JDBC driver worked well with the MySaifu JVM (which is a good choice for Windows CE), and since it was already the internal database engine of OpenOffice (though upgrading to a version which bypasses OpenOffice required solving some puzzles). Ability to address some tables with schema-relative names and others with absolute names, and define views pointing from one schema to another proved handy for implementing an archive database. Ability to write custom Java functions, put them in the server classpath and address them from SQL was quite helpful on occasions. Overall, HSQLDB has proven a good choice for me.