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 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