User Tools

Site Tools


database_schema

Table of Contents

Database Schema

PostgreSQL

pg_dump scriptures --schema-only --no-owner
--
-- 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
--

SQLite

sqlite lds-scriptures-sqlite3.db .schema
CREATE TABLE books (
	id INTEGER PRIMARY KEY,
	volume_id INTEGER REFERENCES volumes(id) ON DELETE CASCADE,
	book_title TEXT,
	book_long_title TEXT,
	book_subtitle TEXT,
	book_short_title TEXT,
	book_lds_url TEXT
);
CREATE TABLE chapters (
	id INTEGER PRIMARY KEY,
	book_id INTEGER REFERENCES books(id) ON DELETE CASCADE,
	chapter_number INTEGER
);
CREATE TABLE verses (
	id INTEGER PRIMARY KEY,
	chapter_id INTEGER REFERENCES chapters(id) ON DELETE CASCADE,
	verse_number INTEGER,
	scripture_text TEXT
);
CREATE TABLE volumes (
	id INTEGER PRIMARY KEY,
	volume_title TEXT,
	volume_long_title TEXT,
	volume_subtitle TEXT,
	volume_short_title TEXT,
	volume_lds_url TEXT
);
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, volume_title, book_title, volume_long_title, book_long_title, volume_subtitle, book_subtitle, volume_short_title, book_short_title, volume_lds_url, book_lds_url, chapter_number, verse_number, scripture_text, book_title || ' ' || chapter_number || ':' || verse_number AS verse_title, book_short_title || ' ' || chapter_number || ':' || verse_number AS verse_short_title FROM volumes INNER JOIN books ON books.volume_id = volumes.id INNER JOIN chapters ON chapters.book_id = books.id INNER JOIN verses ON verses.chapter_id = chapters.id ORDER BY volumes.id, books.id, chapters.id, verses.id
database_schema.txt · Last modified: 2019/03/24 15:32 by steve