jdeolive/geodb — pull request #19 by lonnyj (2014-03-09 14:22:56+00:00)
Title: Adding Apache Derby support to GeoDB

This is my first cut at adding Derby support to GeoDB. I'm creating an extension to Liquibase which required GeoDB for H2 support and I wanted to support Derby as well. Since Hatbox already supports Derby, I figured it wouldn't be too difficult to make GeoDB work with Derby as well.

Aside from aggregate functions (described below), I have successfully tested with Derby 10.8.2.2. Unit tests started failing older than that though the errors were strange enough that I didn't bother investigating.

## GeoDB
I made little or no changes to the generic spatial methods in the `GeoDB` class. However, anything that performed SQL required some modifications to make it compatible with both H2 and Derby. I must say that I really like H2's more flexible SQL parsing because sometimes I just needed to add or remove a word to make the SQL work in both database. Derby doesn't have some of the niceties (e.g. `IF EXISTS` clauses) found in H2 so I had to resort to either SQL or JDBC queries to work around this.

## Stored Procedures
Derby stored procedures don't support blob parameters so I used `LONG VARCHAR FOR BIT DATA` which has a maximum length of 32,700 bytes. This also means that blob geometry columns won't work in most if not all cases. Derby doesn't support stored function overloading so the alternate form of a couple functions (i.e. `ST_MakeBox2D` and `ST_Relate`) are omitted.

## Aggregate Functions
Starting in version 10.10.1.1, Derby supports aggregate SQL functions. I added the Derby-specific interface to the `GeoAggregateFunction` class and mapped Derby's methods to the H2 methods.

## Unit Tests
First off, good job on having so many unit tests! This made my initial work so much easier. I had to do quite a bit of work to restructure the unit tests so that they executed in both H2 and Derby. Creating and deleting a Derby database is sloooow. I added a lot of unit tests to test the Derby stored function creation statements. I left some out because I had questions on their implementation. These two are the most notable:
* `ST_Envelope` -- It looks like this should return a Polygon and not a JTS Envelope.
* `ST_MakeBox2D` -- I expected it to return WKB but I think it's returning EWKB.