LDS Scriptures - Database and Text Exports

User Tools

Site Tools

Trace:
database_schema

Differences

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

Link to this comparison view

database_schema [2019/03/24 21:28] – created stevedatabase_schema [2019/03/24 21:32] (current) – steve
Line 1: Line 1:
 ====== Database Schema ====== ====== Database Schema ======
 +
 +===== PostgreSQL =====
 +
 +<code>
 +pg_dump scriptures --schema-only --no-owner
 +</code>
 +
 +<code>
 +--
 +-- PostgreSQL database dump
 +--
 +
 +SET statement_timeout = 0;
 +SET lock_timeout = 0;
 +SET client_encoding = 'UTF8';
 +SET standard_conforming_strings = on;
 +SET check_function_bodies = false;
 +SET client_min_messages = warning;
 +
 +--
 +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
 +--
 +
 +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
 +
 +
 +--
 +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
 +--
 +
 +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
 +
 +
 +SET search_path = public, pg_catalog;
 +
 +SET default_tablespace = '';
 +
 +SET default_with_oids = false;
 +
 +--
 +-- Name: books; Type: TABLE; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +CREATE TABLE books (
 +    id integer NOT NULL,
 +    volume_id smallint NOT NULL,
 +    book_title character varying(22) DEFAULT ''::character varying NOT NULL,
 +    book_long_title character varying(59) DEFAULT ''::character varying NOT NULL,
 +    book_subtitle character varying(80) DEFAULT ''::character varying NOT NULL,
 +    book_short_title character varying(8) DEFAULT ''::character varying NOT NULL,
 +    book_lds_url character varying(6) DEFAULT ''::character varying NOT NULL
 +);
 +
 +
 +--
 +-- Name: chapters; Type: TABLE; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +CREATE TABLE chapters (
 +    id integer NOT NULL,
 +    book_id integer NOT NULL,
 +    chapter_number smallint NOT NULL
 +);
 +
 +
 +--
 +-- Name: pilcrow; Type: TABLE; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +CREATE TABLE pilcrow (
 +    verse_id integer
 +);
 +
 +
 +--
 +-- Name: verses; Type: TABLE; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +CREATE TABLE verses (
 +    id integer NOT NULL,
 +    chapter_id smallint NOT NULL,
 +    verse_number smallint NOT NULL,
 +    scripture_text text DEFAULT ''::text NOT NULL
 +);
 +
 +
 +--
 +-- Name: volumes; Type: TABLE; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +CREATE TABLE volumes (
 +    id integer NOT NULL,
 +    volume_title character varying(22) DEFAULT ''::character varying NOT NULL,
 +    volume_long_title character varying(26) DEFAULT ''::character varying NOT NULL,
 +    volume_subtitle character varying(36) DEFAULT ''::character varying NOT NULL,
 +    volume_short_title character varying(3) DEFAULT ''::character varying NOT NULL,
 +    volume_lds_url character varying(3) DEFAULT ''::character varying NOT NULL
 +);
 +
 +
 +--
 +-- Name: scriptures; Type: VIEW; Schema: public; Owner: -
 +--
 +
 +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;
 +
 +
 +--
 +-- Name: pkey_books; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +ALTER TABLE ONLY books
 +    ADD CONSTRAINT pkey_books PRIMARY KEY (id);
 +
 +ALTER TABLE books CLUSTER ON pkey_books;
 +
 +
 +--
 +-- Name: pkey_chapters; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +ALTER TABLE ONLY chapters
 +    ADD CONSTRAINT pkey_chapters PRIMARY KEY (id);
 +
 +ALTER TABLE chapters CLUSTER ON pkey_chapters;
 +
 +
 +--
 +-- Name: pkey_verses; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +ALTER TABLE ONLY verses
 +    ADD CONSTRAINT pkey_verses PRIMARY KEY (id);
 +
 +ALTER TABLE verses CLUSTER ON pkey_verses;
 +
 +
 +--
 +-- Name: pkey_volumes; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +ALTER TABLE ONLY volumes
 +    ADD CONSTRAINT pkey_volumes PRIMARY KEY (id);
 +
 +ALTER TABLE volumes CLUSTER ON pkey_volumes;
 +
 +
 +--
 +-- Name: idx_pilcrows; Type: INDEX; Schema: public; Owner: -; Tablespace: 
 +--
 +
 +CREATE UNIQUE INDEX idx_pilcrows ON pilcrow USING btree (verse_id);
 +
 +ALTER TABLE pilcrow CLUSTER ON idx_pilcrows;
 +
 +
 +--
 +-- Name: fkey_books_volumes; Type: FK CONSTRAINT; Schema: public; Owner: -
 +--
 +
 +ALTER TABLE ONLY books
 +    ADD CONSTRAINT fkey_books_volumes FOREIGN KEY (volume_id) REFERENCES volumes(id) ON DELETE CASCADE;
 +
 +
 +--
 +-- Name: fkey_chapters_books; Type: FK CONSTRAINT; Schema: public; Owner: -
 +--
 +
 +ALTER TABLE ONLY chapters
 +    ADD CONSTRAINT fkey_chapters_books FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE;
 +
 +
 +--
 +-- Name: fkey_verses_chapter; Type: FK CONSTRAINT; Schema: public; Owner: -
 +--
 +
 +ALTER TABLE ONLY verses
 +    ADD CONSTRAINT fkey_verses_chapter FOREIGN KEY (chapter_id) REFERENCES chapters(id) ON DELETE CASCADE;
 +
 +
 +--
 +-- Name: public; Type: ACL; Schema: -; Owner: -
 +--
 +
 +REVOKE ALL ON SCHEMA public FROM PUBLIC;
 +REVOKE ALL ON SCHEMA public FROM postgres;
 +GRANT ALL ON SCHEMA public TO postgres;
 +GRANT ALL ON SCHEMA public TO PUBLIC;
 +
 +
 +--
 +-- PostgreSQL database dump complete
 +--
 +</code>
  
 ===== SQLite ===== ===== SQLite =====
database_schema.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