Feature Addition For Apache Derby: Sequence Generators

Abstract

The feature rich open source RDBMS Apache Derby, is developed completely using the Java programming language and is JDBC and SQL standards compliant, and boasts of extreme ease of use and a small memory footprint. As a means of obtaining a unique numerical value (e.g. to be used in a primary key field), Apache Derby earlier provided identity column generators only. Since many users found this insufficient for their needs (as can be seen by feature requests DERBY-712 [WWW1] and DERBY-103 [WWW2]), the aim of this project was to add support for ANSI/ISO compliant sequence generators to the Derby RDBMS. Sequence generators, as described in the SQL specification, are named database objects that produce sequential numbers, and are manipulated through SQL statements. As a result of this project, with the addition of new non-reserved keywords and syntax, the implemented features now allow the users of Derby to do the following. 1. Create Sequence generators with optional clauses 2. Drop existing sequence generators 3. Invoke a sequence generator to retrieve the next value in the sequence This project also saw the addition of two new system catalogs to store sequence descriptions and sequence usage permission records. Apart from the sequence generator implementation, JUnit testcases that execute within Derby’s regression test framework were added.