database support

Database support

Because of the large amount of results involved Fable must support storing and querying data from databases. Database are ideally suited for doing rapid searches based on different criteria.
In order to make Fable easy to install and program it has been decided not to only store results in a database. The first versions of the fable suite of analysis programs will be file based. Database support will be an option. It must be possible to export results to a relational database and then execute queries on them. Extracting the results of queries into files and analysing these data will be provided in the future if necessary.

Mysql?

Mysql

In the original fable proposal it was foreseen to use MySQL as the database. This free database is performant widely used and available for the three platforms fable supports. However it must be installed and administered separately to fable. Although this is not a difficult task it is not a task most scientific users will be familiar with.
We therefore propose to start with an embedded database server called Derby.

Derby?

Derby

Derby is a pure java implementation of a relational database which has been developed as part of the Apache project. It requires no installation by the user and can be embedded into the fable applications. It offers an embedded driver and a JDBC driver.
AG proposes to start off with the embedded driver to export data to a relational database and provide the users with an SQL console (why not ij) for doing queries on their data. Databases will be created on the fly during a session. Derby stores data in files therefore data can be persistent between sessions.
IJ can be forked and run in a fable console. This will be the first level of access to searching data.
The next step would be to provide a graphical representation of results e.g. using BIRT or plain old java queries and JFreeChart.
Some links on using Derby in Eclipse :

1. Getting Started with the Derby plug-ins for Eclipse
2. Use Apache Derby to develop Eclipse plug-ins

In the future we could move to JDBC drivers and support other databases like MySQL or even Oracle.

HSQLDB?

HSQLDB

HSQLDB is another embedded database written in java. It looks very interesting because of its small footprint, speed and can handle up to 8 GB of data. It needs packaging for eclipse (at least the recent version). It should be looked into in the future. HSQLDB is used by some well known software packages (cf. 1).

Sqlite

You can convert ImageD11 columnfiles into sqlite databases via the script ImageD11/colfile2db.py which will create a single table named "peaks". Sqlite is widely supported and apparently works with C, Java, Java/JDBC, and matlab, and you still have the notion of a file, which can be copied to a disk etc.