User Tools

Site Tools


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:


CREATE VIEW scriptures AS
 SELECT AS volume_id, AS book_id, AS chapter_id, AS verse_id,
    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 =
     JOIN chapters ON ((chapters.book_id =
     JOIN verses ON ((verses.chapter_id =
  ORDER BY,,,;


CREATE VIEW scriptures AS SELECT AS volume_id, AS book_id, AS chapter_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 = INNER JOIN chapters ON chapters.book_id = INNER JOIN verses ON verses.chapter_id = ORDER BY,,,;

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