Splitting strings using SQL regexp functions in HSQL 2.3.3

[Villeroy]

Download link: http://www.mediafire.com/download/ofdk7 ... Verses.zip

Installation: Extract to a trusted directory and open the database document which will run an installaton routine connecting itself with the external database and driver.

Initial topic: viewtopic.php?f=9&t=80647 (the Calc forum, of course)

Background: A German brotherhood publishes yearly calendars with daily verses from the New Testament: http://www.losungen.de/download/
User "sayhello" wanted a calendar on a Calc sheet with a "translation" (lookup) to the corresponding verses in anchient Greek. His edition of the Greek bible is tainted by intellectual properties so I replaced it with an English edition ("King George" bible from http://www.gutenberg.org).
The interesting task was splitting a textual reference with German book names and groups of digits into the book name, a chapter number and one or more verse numbers, then look up the book-ID by a regex match and then the referenced NT verses by book-ID, chapter-ID, verse-IDs and edition-ID. I prepared the database for multiple editions of the NT. So far it has only the English verses plus one "copyright verse" for the Greek NT.

Examples of German text refs and how they are split:
"Judas 3" --> Book #26 (Jude) Chapter 1, Verses 3 to 3 for the selected year from the selected NT edition
"Judas 3-4" --> Book 26 (Jude) Chapter 1, Verses 3 to 4
"Römer 2,3" --> Book 6 (Romans) Chapter 2, Verses 3 to 3
"Römer 2,3-4" --> Book 6 (Romans) Chapter 2, Verses 3 to 4

Inventory:

Table BOOKS with German book names and ID numbers

Table NEW_TESTAMENT with one edition of the English bible, Edition-ID, Book-ID, Chapter-ID, Verse-ID and verse text. The 4 IDs compose the primary key.

Table EDITIONS: descriptive text for the above Edition-ID

Table GERMAN_VERSES: Source table with data copied from 3 spreadsheets with a date as primary key, a text reference ("Johannes 1,2-3") and German verse text.

A filter table to store a year number, the edition ID of the target edition and a test string for "qTest" and the "Test" form.

View REGEX returns a Book-ID matching a book name by regular expression and an array of 1 to 3 numeric strings. See "Query1".
The regular expressions are documented here: http://docs.oracle.com/javase/tutorial/ ... index.html

View SPLIT splits up the numeric strings into 3 numbers chapter, start verse, end verse. See "Query2".

View VGROUPCONCAT concatenates the verses from start to end verse. See "Query3".

Query "qMerge" (parsed mode) merges VGROUPCONCAT with the corresponding records from table GERMAN_VERSES.
Query "qMerge2" is a copy of "qMerge" but in direct mode used for the report which fails in parsed mode.

"Query1" is the editable version of view REGEX.
"Query2" is the editable version of view SPLIT.
"Query3" is the editable version of view VGROUPCONCAT.

Query "qTest" is very close to a merge of Query1-3 but starting with the filter table's test string instead of the German table. It returns some debugging info. See input form "Test".

Input form "No Match" sets the stored filter for the selected year number and NT edition. It is loaded with an additional toggle filter showing records where no book, chapter or verse matches. This filter can be toggled on/off by a button on the navigation bar. A wrong input string for the selected record can be fixed in a yellow subform. A blue subform shows all chapters and verses for a matching book (if any book matches).

Input form "Test" tests any test string entered into the filter table returning some debug values in a subform

Report "qMerge2" is an old style report from direct SQL "qMerge2" made in OpenOffice with Report Builder Extension disabled.

A README.odt outside the database document describes how to install the database package and how to copy new data from a spreadsheet into the database.

Kalender.ods is an ugly quick draft for a print-out calendar (blocks of days in columns of weeks starting at the first Monday of the week that includes 1st of Jan.)

Subfolder "database" contains the HSQL backend (the actual database).
Subfolder "driver" contains a HSQL 2.3.3 driver.