fix books with both greek and hebrew chapters

[JohnRDOrazio]

Catholic versions of the Bible usually include both the Greek and the Hebrew versions of certain books, such as Esther and Daniel.

The database needs to account for this, and so do client programs. When quoting "Esther 1" it becomes necessary to specify if we are quoting from the Greek version of chapter 1 or from the Hebrew version. However it may not always be that simple:

• in the Italian CEI2008 version, the Greek and Hebrew versions of Esther are put next to each other chapter by chapter, with the same chapter numbers: Chapter 1 Greek version and Chapter 1 Hebrew version. Where the Greek has extra verses, they are indicated with the same verse number as the Hebrew, with just the addition of a letter (1a, 1b, 1c etc.). So that is fairly simple to handle, just add a metadata column to the database that will indicate whether we are dealing with the "GREEK" or "HEBREW" verses. When a client program asks for "Esther 1,1" and specifies the Greek version, we should get back Esther 1,1a-1r. Not too difficult a scenario to handle.

• in the English USCCB version, the regular chapter numbers in the Book of Esther apply to the Hebrew text, whereas the Greek additions are indicated by the letters A through F: can we simply indicate them with the same chapter numbers as the Greek? Do the verse numbers correspond? Need to check this, even though the website is currently down as they are transitioning to a new website...

• in the Spanish Libro del Pueblo de Dios version, the Greek additions are included as supplemental books, with indications as to where they should be inserted: e.g. Esther 3 (Greek, verses 1-7) should be inserted between Esther 4:8 (Hebrew) and Esther 4:9 (Hebrew). So in this case we don't have either chapter or verse numbers that correspond with each other. But I guess as long as anyone who is quoting from these versions knows about these differences, they would know (or at least the client program would need to know) that if you quote Esther 3 Greek it will not correspond with Esther 3 Hebrew, if you want the full version of Esther 4 with Hebrew and Greek you would have to quote Esther 4:1-8 (Hebrew) + Esther 3:1-7 (Greek) + Esther 4:9-17 (Hebrew).

And what is the situation with the Book of Daniel in each of these versions?

[JohnRDOrazio]

I opened this as a bug, because I haven't really dealt with this at all, so I'm not even 100% sure what exactly I have in each database or what a user will get when they try to quote from the books of Esther or Daniel in one version of the Bible or another. This really needs to be looked into and dealt with in a coherent and predictable manner. Schemas need to be defined, interactions with client applications need to be defined, the API needs further definitions to handle these scenarios. And since we will be extending the API to deal with this, I also tagged the issue as an enhancement. Once there is a clear definition in the API and the underlying database, client applications will need to be updated in order to deal with this.

[JohnRDOrazio]

First let's establish the current database schema:
CEI2008.sql.zip
NABRE.sql.zip

Here are two schemas. In the CEI2008 schema I tried to start dealing with this.

One thing to keep in mind, besides the question of Esther and Daniel, there are verses that have letters in some other books of the Bible, so the question arises whether the verse column should be int or varchar. Being int means that it will be ordered in a coherent manner in the database view and will surely be ordered coherently when pulling results from the database. Being varchar can open up to ordering issues, where natural language ordering is different from numeric ordering, like 10 coming after 1 instead of after 9.
So there's a question where we might have two different columns, one for the numeric ordering, and another for the visual representation of a verse. I believe that was my original intention in creating the verseequiv column which is of type varchar (with the meaning of "verse equivalent"). This would mean that verse '1a' would be represented as (int) 1 in the verse column, and as (varchar) 1a in the versequiv column. The verse column will be used for ordering results (in combination with the verseequiv column when there is more than one result for verse), and the verseequiv column will be used for displaying results.

A unique index has been created on the columns book - chapter - verse in combination. The idea was that the smallest possible unit that can be quoted is a single verse, which can be uniquely represented by the book-chapter-verse combination. However this does not seem to be enough, in those cases where there are sequential verses with the same number but with different letters, such as 1a and 1b. If 1a and 1b are in the verseequiv column and not in the verse column, being represented in the verse column as (int) 1, then we will have illegal duplicates in the book-chapter-verse unique index. In order to uniquely identify these verses we have to extend our unique index to include the verseequiv column (which is however allowed to be null). So now our unique index is book - chapter - verse - verseequiv . I have just now implemented this in the CEI2008 database structure, I have not yet implemented it in the NABRE database structure (where currently both verse and verseequiv are of type varchar: how is this handled in fact? Is it taken care of in a coherent manner? Are results ordered correctly? Are the client applications dealing with this?).

However this perhaps is not even enough for our unique index. If we have situations where the same verse numbering is used twice, once for the Greek version and once for the Hebrew version of a text, then we again run into a problem in our unique index. Take for example the Italian CEI2008 version, where there are two versions of Esther 1,2, one Greek and one Hebrew. The versequiv column is null in both cases, so it doesn't help to further identify this single verse. We will have to again extend our unique index to include another column which I have now defined as verseorigin of type enum = ["GREEK","HEBREW"] (and which can also be null). So now if we extend our unique index as such: book - chapter - verse - verseequiv - verseorigin , we should be able to include and uniquely identify all types of verses whether they are based on the Greek or the Hebrew, and whether they have a letter associated with them or not.

[JohnRDOrazio]

Based on this type of schema, client applications should know and expect verse to be of type int, and verseequiv to be of type String. They should use verse for ordering results, and verseequiv for displaying results (when present, i.e. if not null or empty). In MySQL I believe ENUM types are transformed into String with the name of the enum value, so verseorigin in the JSON representation will either be null or have a String value of one of the following: "GREEK", "HEBREW", "".

[JohnRDOrazio]

TODO: fix the Greek verses in the CEI2008 table. I have created a CEI2008_temp table to fix the book of Esther. As of Nov. 25 2020 I have done up to Esther 5,2b. Have to pick up again from Esther 5,3

[JohnRDOrazio]

• CEI2008 table completed
• NVBSE table completed
• NABRE table completed
• VGCL table completed

[JohnRDOrazio]

• DRB table completed, used same mapping as VGCL (Esther chapters 11-16 will map to relative Greek verses in chapters 1-10)
• table for the Spanish BLPD version has now been created and needs to have the Greek portion of Esther created