User Tools

Site Tools


database_views

Table of Contents

Database Views

On all the SQL database schemas, the data is normalized. However, I realize most people don't want to write a lot of JOINs just to get to what they want, and so there is scriptures VIEW that runs the query for you to return a non-normalized result set.

Each database is different, but for the sake of simplicity, here's the schema for some:

PostgreSQL

CREATE VIEW scriptures AS
 SELECT volumes.id AS volume_id,
    books.id AS book_id,
    chapters.id AS chapter_id,
    verses.id AS verse_id,
    volumes.volume_title,
    books.book_title,
    volumes.volume_long_title,
    books.book_long_title,
    volumes.volume_subtitle,
    books.book_subtitle,
    volumes.volume_short_title,
    books.book_short_title,
    volumes.volume_lds_url,
    books.book_lds_url,
    chapters.chapter_number,
    verses.verse_number,
    verses.scripture_text,
    concat(books.book_title, ' ', chapters.chapter_number, ':', verses.verse_number) AS verse_title,
    concat(books.book_short_title, ' ', chapters.chapter_number, ':', verses.verse_number) AS verse_short_title
   FROM (((volumes
     JOIN books ON ((books.volume_id = volumes.id)))
     JOIN chapters ON ((chapters.book_id = books.id)))
     JOIN verses ON ((verses.chapter_id = chapters.id)))
  ORDER BY volumes.id, books.id, chapters.id, verses.id;

SQLite

CREATE VIEW scriptures AS SELECT volumes.id AS volume_id, books.id AS book_id, chapters.id AS chapter_id, verses.id AS verse_id, volume_title, book_title, volume_long_title, book_long_title, volume_subtitle, book_subtitle, volume_short_title, book_short_title, volume_lds_url, book_lds_url, chapter_number, verse_number, scripture_text, book_title || ' ' || chapter_number || ':' || verse_number AS verse_title, book_short_title || ' ' || chapter_number || ':' || verse_number AS verse_short_title FROM volumes INNER JOIN books ON books.volume_id = volumes.id INNER JOIN chapters ON chapters.book_id = books.id INNER JOIN verses ON verses.chapter_id = chapters.id ORDER BY volumes.id, books.id, chapters.id, verses.id;

To make a clean illustration of the format, here's running a query on the SQLite database printing each column one per line:

sqlite> .mode line
sqlite> SELECT * FROM scriptures WHERE verse_title = '1 Nephi 1:1';
         volume_id = 3
           book_id = 67
        chapter_id = 1190
          verse_id = 31103
      volume_title = Book of Mormon
        book_title = 1 Nephi
 volume_long_title = The Book of Mormon
   book_long_title = The First Book of Nephi
   volume_subtitle = Another Testament of Jesus Christ
     book_subtitle = His Reign and Ministry
volume_short_title = BoM
  book_short_title = 1 Ne.
    volume_lds_url = bm
      book_lds_url = 1-ne
    chapter_number = 1
      verse_number = 1
    scripture_text = I, Nephi, having been born of goodly parents, therefore I was taught somewhat in all the learning of my father; and having seen many afflictions in the course of my days, nevertheless, having been highly favored of the Lord in all my days; yea, having had a great knowledge of the goodness and the mysteries of God, therefore I make a record of my proceedings in my days.
       verse_title = 1 Nephi 1:1
 verse_short_title = 1 Ne. 1:1
database_views.txt · Last modified: 2019/03/24 16:52 by steve