From d8025ff93d66ec1ff7a6ffd90783e877e9eaafff Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Sun, 13 Jun 2021 02:42:26 +0100
Subject: Support creating a PostgreSQL mock DB and filling it with a schema
script
---
db/schema.sql | 187 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 187 insertions(+)
create mode 100644 db/schema.sql
(limited to 'db')
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..667fc48
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,187 @@
+--
+-- PostgreSQL database dump
+--
+
+-- Dumped from database version 12.7
+-- Dumped by pg_dump version 13.3
+
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET idle_in_transaction_session_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SELECT pg_catalog.set_config('search_path', '', false);
+SET check_function_bodies = false;
+SET xmloption = content;
+SET client_min_messages = warning;
+SET row_security = off;
+
+--
+-- Name: mygrate; Type: SCHEMA; Schema: -; Owner: -
+--
+
+CREATE SCHEMA mygrate;
+
+
+SET default_tablespace = '';
+
+SET default_table_access_method = heap;
+
+--
+-- Name: source; Type: TABLE; Schema: mygrate; Owner: -
+--
+
+CREATE TABLE mygrate.source (
+ source_id integer NOT NULL,
+ host text NOT NULL,
+ username text NOT NULL,
+ password text NOT NULL,
+ port integer NOT NULL,
+ filename text NOT NULL,
+ "position" bigint NOT NULL,
+ serverid integer NOT NULL,
+ table_schema name NOT NULL COLLATE pg_catalog."default"
+);
+
+
+--
+-- Name: source_id_seq; Type: SEQUENCE; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE mygrate.source ALTER COLUMN source_id ADD GENERATED BY DEFAULT AS IDENTITY (
+ SEQUENCE NAME mygrate.source_id_seq
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+--
+-- Name: table_columns; Type: TABLE; Schema: mygrate; Owner: -
+--
+
+CREATE TABLE mygrate.table_columns (
+ column_id integer NOT NULL,
+ column_name name NOT NULL,
+ mysql_ordinal integer NOT NULL,
+ table_id integer NOT NULL
+);
+
+
+--
+-- Name: table_columns_column_id_seq1; Type: SEQUENCE; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE mygrate.table_columns ALTER COLUMN column_id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME mygrate.table_columns_column_id_seq1
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+--
+-- Name: tables; Type: TABLE; Schema: mygrate; Owner: -
+--
+
+CREATE TABLE mygrate.tables (
+ table_id integer NOT NULL,
+ table_name name NOT NULL,
+ source_id integer NOT NULL
+);
+
+
+--
+-- Name: tables_table_id_seq1; Type: SEQUENCE; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE mygrate.tables ALTER COLUMN table_id ADD GENERATED ALWAYS AS IDENTITY (
+ SEQUENCE NAME mygrate.tables_table_id_seq1
+ START WITH 1
+ INCREMENT BY 1
+ NO MINVALUE
+ NO MAXVALUE
+ CACHE 1
+);
+
+
+--
+-- Name: table_columns pk_table_columns; Type: CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.table_columns
+ ADD CONSTRAINT pk_table_columns PRIMARY KEY (column_id);
+
+
+--
+-- Name: tables pk_tables; Type: CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.tables
+ ADD CONSTRAINT pk_tables PRIMARY KEY (table_id);
+
+
+--
+-- Name: source source_pkey; Type: CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.source
+ ADD CONSTRAINT source_pkey PRIMARY KEY (source_id);
+
+
+--
+-- Name: source uni_schema; Type: CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.source
+ ADD CONSTRAINT uni_schema UNIQUE (table_schema);
+
+
+--
+-- Name: tables uni_source_table; Type: CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.tables
+ ADD CONSTRAINT uni_source_table UNIQUE (source_id, table_name);
+
+
+--
+-- Name: table_columns uni_table_columnname; Type: CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.table_columns
+ ADD CONSTRAINT uni_table_columnname UNIQUE (table_id, column_name);
+
+
+--
+-- Name: table_columns uni_table_columnord; Type: CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.table_columns
+ ADD CONSTRAINT uni_table_columnord UNIQUE (table_id, mysql_ordinal);
+
+
+--
+-- Name: table_columns fk_table_columns_table; Type: FK CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.table_columns
+ ADD CONSTRAINT fk_table_columns_table FOREIGN KEY (table_id) REFERENCES mygrate.tables(table_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- Name: tables fk_table_source; Type: FK CONSTRAINT; Schema: mygrate; Owner: -
+--
+
+ALTER TABLE ONLY mygrate.tables
+ ADD CONSTRAINT fk_table_source FOREIGN KEY (table_id) REFERENCES mygrate.source(source_id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+
+--
+-- PostgreSQL database dump complete
+--
+
--
cgit v1.2.3