Differences

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

--- database_views [2019/03/24 21:35] – [SQLite] steve
+++ database_views [2019/03/24 22:52] (current) – steve
@@ Line 4: / Line 4: @@
 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 =====