The PostgreSQL export ships with multiple files:
lds-scriptures-postgresql.sql
- the database exportschema.txt
- the schema used in the databaseexport.sh
- how the exports were created
To import the file, create your scriptures
database first. Then, import the file into your database. If using command line, here is what it would look like:
$ psql -c "CREATE DATABASE scriptures" postgres $ psql scriptures < lds-scriptures-postgresql.sql
You can look at the schema of the export by looking at the schema.txt
file.
There are two ways to reference the data. One is four tables which are normalized that are joined together by foreign keys.
The tables in the database are:
The other is a view named scriptures
that is not normalized that joins all the above tables into one row per verse. It also adds helper columns verse_title
and verse_short_title
to make querying simpler.
A sample query to fetch data from the tables for John 3:16 would look like this:
SELECT vol.volume_title, b.book_title, c.chapter_number, v.scripture_text FROM volumes vol JOIN books b on b.volume_id = vol.id JOIN chapters c ON c.book_id = b.id JOIN verses v ON v.chapter_id = c.id WHERE b.book_title = 'John' AND c.chapter_number = 3 AND v.verse_number = 16;
volume_title | book_title | chapter_number | scripture_text ---------------+------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------- New Testament | John | 3 | For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. (1 row)
Or, you can use the scriptures
view and not worry about joining tables. This one pulls in all the data from all the tables into one convenient way to access them. If you are not familiar with databases, then this is the one recommended to start with. The schema of the view is found in schema.txt
.
A sample query using this view could be simplified like this:
SELECT scripture_text FROM scriptures WHERE verse_title = 'John 3:16';
scripture_text ----------------------------------------------------------------------------------------------------------------------------------------------- For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. (1 row)
Understandably, you may want only the King James Version of the Bible in your database.
The database's foreign keys are set up with CASCADE DELETE
so deleting a volume will remove all the data down to the verses.
To delete the LDS Scriptures, simply run this query:
DELETE FROM volumes WHERE id NOT IN (1,2);