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 revisionPrevious revision
database_views [2019/03/24 21:35] – [SQLite] stevedatabase_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: 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: by steve

Except where otherwise noted, content on this wiki is licensed under the following license: Public Domain
Public Domain Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki