This shows you the differences between two versions of the page.
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 ===== |