Wow! Thanks for all that. It defintely gives me areas to think about... By Java database I meant a RDBMS written in Java (Java/Apache Derby), configured in our case to run as part of the same JVM as our app. What I meant earlier, is that with Derby, you basically declare new procedures as static java methods, and then when it comes to executing your procedure, it uses reflection to invoke your static method.<br>
<br>And thanks for the code sample pointers in the limbo area! I'll chew through those...<br>Chris<br><br><div class="gmail_quote">2009/11/9 DM Smith <span dir="ltr"><<a href="mailto:dmsmith@crosswire.org">dmsmith@crosswire.org</a>></span><br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="im"><br>
On Nov 8, 2009, at 8:35 AM, Chris Burrell wrote:<br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi all<br>
<br>
I'm on a project using JSword and we're linking Scripture to various reference materials. For various reasons, we will need to store references to our material in the database and not in a Sword module. Does anyone have any views as to the best way of storing the verse/passage reference?<br>
</blockquote>
<br></div>
JSword has the notion of BookDriver being the storage representation of a Book. If you haven't found it yet, look in the JSword "limbo project", which holds nascent and obsolete code (<a href="http://crosswire.org/svn/jsword/trunk/jsword-limbo" target="_blank">http://crosswire.org/svn/jsword/trunk/jsword-limbo</a>) you see some various starts of BookDrivers. For example, under src/main/java, the package o.c.j.book.jdbc has a nascent implementation of a JDBC driver.<br>
<br>
It should give you an idea of where to start.<div class="im"><br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
I've come up with a few solutions, but not sure which would be the most efficient, speed, space, etc.-wise... It will be used mainly to serve the questions:<br>
"Does the bible reference provided by the user match any of our material?", or more simply, "Does passage A overlap with passage B"?<br>
<br>
Our material will be keyed by passage as opposed to per verse, ie. ranges of verses...<br>
<br>
1- Storing the reference as is in the database, say 1Kings.2:10-15;3:2-3 would mean we'd have to do lots of string manipulations to figure out overlap of 2 references<br>
</blockquote>
<br></div>
In JSword a Verse is a single point in a document, a VerseRange is a collection of adjacent Verses and a Passage is a collection of VerseRanges that may or may not be adjacent. The degenerative case for a VerseRange is a single Verse and the degenerative case of a Passage is a VerseRange (thus a Verse).<br>
<br>
JSword at this point has a notion of a Verse being a specialized Key and there is no notion of a KeyRange or of a Passage being a collection of Keys. At some point we need to unify this. Then everything will be a Key.<br>
<br>
Regarding Passage, there are several different types, each of which has it's own performance characteristic. Two to look at are BitwisePassage and PassageTally.<br>
<br>
RocketPassage is essentially a bit map where each Verse is represented by a bit. Obviously there needs to be a two way mapping between the bits and the Verse reference. This is the most frequently used Passage in JSword and is the best representation for canonically ordered verses.<br>
<br>
PassageTally is a list of weighted Verses. This is used for a best-match, prioritized search result.<br>
<br>
Internally, SWORD has the notion of linking but this is not yet exposed in Key or Verse, but it is present in JSword's drivers for SWORD modules. We'll be exposing this before too long. JSword will have routines in Key and Verse to determine whether a Verse is linked to the another Verse and whether a Verse is part of a set of linked verses. In JSword lingo, a link is an Alias.<br>
<br>
Currently, a SWORD commentary is laid out as a Bible, where an entry may be a range of verses. JSword hasn't exposed the notion of aliasing. You can see this in BibleDesktop by setting the Option/Preference to show Commentaries as Bibles and pick a commentary in the list of Bibles. You'll see that the content repeats. That is, if Gen 1:1-3 is shown and this is stored in the SWORD module as a linked set, then it is shown 3 times, once for each of Gen 1:1, Gen 1:2 and Gen 1:3. It should not do this, but there are no methods in Key that will help determine that Gen 1:1 is part of a link set that encompasses verses 2 and 3. Then in parallel view we would set the colspan attribute to 3 for Gen 1.1 and when showing the verse number we'd show 1-3. Then when processing 2 and 3 we'd skip them as they link back to 1.<br>
<br>
So to answer your bullet, JSword at a minimum needs (the names and signatures can change)<br>
/**<br>
* Determine whether this and another reference are aliases of each other. That is do the have the same getRawText().<br>
* @param Key the key to compare.<br>
* @return true of this is an alias of that<br>
abstract boolean isAliased(Key that);<br>
/**<br>
* Get the Key representing a range of adjacent, aliased keys for a single Key.<br>
* This operation is potentially very expensive.<br>
* @return the aliased set.<br>
* @throws IllegalArgumentException if this Key has a cardinality > 1.<br>
*/<br>
abstract Key getAliases();<br>
<br>
Then with these primitives, one can determine overlap.<div class="im"><br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
2- Storing the beginning and end of each section say as a group of sub references { (book, start_chapter, start_verse, end_chapter, end_verse)+ }.<br>
In this case a reference would be many of the previous definition. I can see how we could work it out here, but i can see also having to do lots of index range scans on our database<br>
</blockquote>
<br></div>
Working backwards, there needs to be a table to hold referenced pieces of text. Something like:<br>
create table BiblicalText (<br>
location NUMBER NOT NULL,<br>
text CLOB NOT NULL,<br>
CONSTRAINT BT_pk PRIMARY KEY (location)<br>
);<br>
<br>
Then there needs to be a way to get a BiblicalText getting a Verse. Something like:<br>
create table VerseReference (<br>
ordinal NUMBER NOT NULL, -- Represents the canonical order of the verse in a Bible, where 1 is the first verse, 2 is the next and so on.<br>
reference STRING NOT NULL, -- a normalized reference for the Verse, e.g. an osisID or a tuple(t,b,c,v)<br>
location NUMBER NOT NULL,<br>
CONSTRAINT bt_fk FOREIGN KEY (location) REFERENCES BiblicalText(location)<br>
);<br>
<br>
While JSword has not exposed it yet, you might want to represent testament, book and chapter introductions as specially named references. SWORD uses the tuple approach with something like:<br>
(Testament, Book, Chapter, Verse)<br>
(0,x,x,x) -- OT Introduction<br>
(1,0,x,x) -- NT Intro<br>
(1,1,0,x) -- Book intro to Matt<br>
(1,1,1,0) -- Chapter intro to Matt 1<br>
Note, The x values should be 0, but really are don't care values. JSword will be following this notion, which will squeeze in more bits into the bit maps.<br>
<br>
I think versification needs to be held separately from storage and essentially is an optimization of it. If you look at o.c.j.book.versification.BookInfo you'll see the fundamental way JSword maps a number to a verse reference.<br>
This will be extended to other fixed canons. Essentially one needs to know the order and names of books, the number of chapters per book and the number of verses per chapter. Not as actuals but as potentials. That is, a Book which is a translation of an old, damaged scroll might not have all verses in a chapter or even all chapters. The map should represent the undamaged scroll.<br>
<br>
This argues for an ordered list of Books and the number of chapters in each book.<br>
create table BookNames (<br>
bookNumber NUMBER NOT NULL,<br>
bookName STRING NOT NULL,<br>
chapterCount NUMBER NOT NULL,<br>
CONSTRAINT bn_pk PRIMARY KEY (bookNumber)<br>
);<br>
<br>
And the number of verses in each chapter:<br>
create table Chapter (<br>
bookNumber NUMBER NOT NULL,<br>
chapterNumber NUMBER NOT NULL,<br>
chapterSize NUMBER NOT NULL,<br>
CONSTRAINT ch_bn_fk FOREIGN KEY (bookNumber) REFERENCES BookNames(bookNumber)<br>
);<br>
<br>
At this time JSword is stuck on the KJV versification, so going beyond that will be a re-implementation of BookInfo. This is planned, but has not happened yet. Basically, we'll have file representation that JSword can read in to answer the questions. In essence, we'll use a file as the database. The form probably will be a serialization of a populated BookInfo object.<div class="im">
<br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
3- Number each verse of the bible from 1 to 30000 or something like that, and then workout and store each verse that is included in the reference in a table somewhere in the database<br>
The benefit here seems to be that we would get lots of unique index lookups, but maybe the number of lookups would actually be better off doing range scans... Also, we would have quite a bit more disk space overhead, if we're storing a row for each verse.<br>
<br>
4- Number each verse of the bible from 1 to 30000 or something like that, but only store the ranges say verses 30-140 + verses 1500-1512<br>
<br>
5- Numbering each verse, but keying the numbers by book, say Exodus verse 750, 751, 752, etc.<br>
<br>
6- We have the benefit of working in Java with a Java database, and so could write a Java stored procedure to parse whatever solution comes out of here... It would have to be fast though, given the end product is the web, and the main activity will searching across scripture references...<br>
</blockquote>
<br></div>
I'm a bit confused here. Your discussion is that of a relational database, but you refer to a Java database. By a Java database, do you mean a relational DBMS that is addressed via JDBC or do you mean an OODBMS, such as Objectivity or ObjectStore? These days, next to no one uses an OODBMS.<div class="im">
<br>
<br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
We'll be doing many searches on our data and updating it only very rarely. I'm a bit at a loss as to best way of doing this... How does JSword cope with this? or does it uniquely do scripture lookups and not scripture overlap? (ie. working out whether two portions of scripture overlap with each other).<br>
</blockquote>
<br></div>
Having said all that. I have another idea. It is not well thought out. How about creating a JDBC driver for a SWORD module? Still use Lucene to create a secondary index for searching. The trick would be to map prepared statements to JSword calls.<br>
<br>
SWORD is a static database of all kinds of books. It is highly optimized for lookups. It consists of index and data files per testament.<br>
<br>
The index can be thought of an array indexed by verse number (This is the same as VerseReference.ordinal in the above table), holding a pointer (offset and size) into the data file. If two entries in the index point to the same location in the data file, then they are aliases/links.<br>
<br>
Since updates are infrequent, this works well. When a verse is updated, it is appended to the data file and all the references to it are updated to point to the new location. Over time, the data file will grow and should be rebuilt from scratch when it gets "too big".<br>
<br>
<br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
Any ideas anyone?<br>
Chris<br>
<br>
_______________________________________________<br>
jsword-devel mailing list<br>
<a href="mailto:jsword-devel@crosswire.org" target="_blank">jsword-devel@crosswire.org</a><br>
<a href="http://www.crosswire.org/mailman/listinfo/jsword-devel" target="_blank">http://www.crosswire.org/mailman/listinfo/jsword-devel</a><br>
</blockquote>
<br>
</blockquote></div><br>