User Tools

Site Tools


postgresql

PostgreSQL

Files

The PostgreSQL export ships with multiple files:

  • lds-scriptures-postgresql.sql - the database export
  • schema.txt - the schema used in the database
  • export.sh - how the exports were created

Importing Database

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

Database Structure

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:

  • books - Genesis, Exodus, Isaiah, Romans, 1 Nephi, etc.
  • chapters - table joining verses to books
  • verses - scripture text
  • volumes - Old Testament, New Testament, etc.

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.

Database Queries

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)

Dropping LDS Scriptures

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);
postgresql.txt · Last modified: 2021/01/07 05:16 by steve