User Tools

Site Tools


database_views

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
database_views [2019/03/24 15:35]
steve [SQLite]
database_views [2019/03/24 16:52] (current)
steve
Line 4: Line 4:
  
 Each database is different, but for the sake of simplicity, here's the schema for some: Each database is different, but for the sake of simplicity, here's the schema for some:
 +
 +===== PostgreSQL =====
 +
 +<​code>​
 +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;
 +</​code>​
  
 ===== SQLite ===== ===== SQLite =====
database_views.txt ยท Last modified: 2019/03/24 16:52 by steve