Differences

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

--- database_views [2019/03/24 20:40] – [Database Views] steve
+++ database_views [2019/03/24 22:52] (current) – steve
@@ Line 1: / Line 1: @@
 ====== Database Views ======
-On all the SQL database schemas, the data is [[https://en.wikipedia.org/wiki/Database_normalization|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.
+On all the SQL database schemas, the data is [[https://en.wikipedia.org/wiki/Database_normalization|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:
-==== SQLite ====
+===== 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 =====
 <code>