Switch to using SQL as the schema format

This is recommended by the Que library, but may come in useful
elsewhere for PostgreSQL specific stuff.

2 files changed, 500 insertions, 96 deletions

diff --git a/db/schema.rb b/db/schema.rb
deleted file mode 100644
index 420b048..0000000
--- a/db/schema.rb
+++ /dev/null
@@ -1,96 +0,0 @@
-# This file is auto-generated from the current state of the database. Instead
-# of editing this file, please use the migrations feature of Active Record to
-# incrementally modify your database, and then regenerate this schema definition.
-#
-# Note that this schema.rb definition is the authoritative source for your
-# database schema. If you need to create the application database on another
-# system, you should be using db:schema:load, not running all the migrations
-# from scratch. The latter is a flawed and unsustainable approach (the more migrations
-# you'll amass, the slower it'll run and the greater likelihood for issues).
-#
-# It's strongly recommended that you check this file into your version control system.
-
-ActiveRecord::Schema.define(version: 20180406082851) do
-
-  # These are extensions that must be enabled in order to support this database
-  enable_extension "plpgsql"
-
-  create_table "finished_terraform_jobs", force: :cascade do |t|
-    t.integer "model_id"
-    t.string "job_class"
-    t.datetime "created_at", null: false
-    t.datetime "updated_at", null: false
-  end
-
-  create_table "govuk_guix_revisions", primary_key: "commit_hash", id: :string, force: :cascade do |t|
-    t.string "store_path"
-    t.datetime "created_at", null: false
-    t.datetime "updated_at", null: false
-    t.boolean "archived", default: false, null: false
-    t.index ["commit_hash"], name: "index_govuk_guix_revisions_on_commit_hash", unique: true
-  end
-
-  create_table "mini_environments", force: :cascade do |t|
-    t.string "name"
-    t.datetime "created_at", null: false
-    t.datetime "updated_at", null: false
-    t.jsonb "info"
-    t.string "govuk_guix_revision_id"
-    t.string "backend_type"
-    t.bigint "backend_id"
-    t.jsonb "backend_data"
-    t.jsonb "signon_users"
-    t.index ["backend_type", "backend_id"], name: "index_mini_environments_on_backend_type_and_backend_id"
-    t.index ["govuk_guix_revision_id"], name: "index_mini_environments_on_govuk_guix_revision_id"
-  end
-
-  create_table "que_jobs", primary_key: ["queue", "priority", "run_at", "job_id"], force: :cascade, comment: "3" do |t|
-    t.integer "priority", limit: 2, default: 100, null: false
-    t.datetime "run_at", default: -> { "now()" }, null: false
-    t.bigserial "job_id", null: false
-    t.text "job_class", null: false
-    t.json "args", default: [], null: false
-    t.integer "error_count", default: 0, null: false
-    t.text "last_error"
-    t.text "queue", default: "", null: false
-  end
-
-  create_table "terraform_aws_backends", force: :cascade do |t|
-    t.string "label"
-    t.string "aws_region"
-    t.string "aws_access_key_id"
-    t.string "aws_secret_access_key"
-    t.datetime "created_at", null: false
-    t.datetime "updated_at", null: false
-    t.string "domain"
-  end
-
-  create_table "terraform_libvirt_backends", force: :cascade do |t|
-    t.string "label"
-    t.string "uri"
-    t.datetime "created_at", null: false
-    t.datetime "updated_at", null: false
-    t.string "domain"
-  end
-
-  create_table "terraform_states", force: :cascade do |t|
-    t.json "data"
-    t.datetime "created_at", null: false
-    t.datetime "updated_at", null: false
-    t.integer "mini_environment_id"
-  end
-
-  create_table "users", force: :cascade do |t|
-    t.string "name"
-    t.string "email"
-    t.string "uid"
-    t.string "organisation_slug"
-    t.string "organisation_content_id"
-    t.text "permissions"
-    t.boolean "remotely_signed_out", default: false
-    t.boolean "disabled", default: false
-  end
-
-  add_foreign_key "mini_environments", "govuk_guix_revisions", primary_key: "commit_hash"
-  add_foreign_key "terraform_states", "mini_environments", on_delete: :cascade
-end
diff --git a/db/structure.sql b/db/structure.sql
new file mode 100644
index 0000000..602948d
--- /dev/null
+++ b/db/structure.sql
@@ -0,0 +1,500 @@
+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 client_min_messages = warning;
+SET row_security = off;
+
+--
+-- 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 default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: ar_internal_metadata; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.ar_internal_metadata (
+    key character varying NOT NULL,
+    value character varying,
+    created_at timestamp without time zone NOT NULL,
+    updated_at timestamp without time zone NOT NULL
+);
+
+
+--
+-- Name: finished_terraform_jobs; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.finished_terraform_jobs (
+    id bigint NOT NULL,
+    model_id integer,
+    job_class character varying,
+    created_at timestamp without time zone NOT NULL,
+    updated_at timestamp without time zone NOT NULL
+);
+
+
+--
+-- Name: finished_terraform_jobs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE public.finished_terraform_jobs_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: finished_terraform_jobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE public.finished_terraform_jobs_id_seq OWNED BY public.finished_terraform_jobs.id;
+
+
+--
+-- Name: govuk_guix_revisions; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.govuk_guix_revisions (
+    commit_hash character varying NOT NULL,
+    store_path character varying,
+    created_at timestamp without time zone NOT NULL,
+    updated_at timestamp without time zone NOT NULL,
+    archived boolean DEFAULT false NOT NULL
+);
+
+
+--
+-- Name: mini_environments; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.mini_environments (
+    id bigint NOT NULL,
+    name character varying,
+    created_at timestamp without time zone NOT NULL,
+    updated_at timestamp without time zone NOT NULL,
+    info jsonb,
+    govuk_guix_revision_id character varying,
+    backend_type character varying,
+    backend_id bigint,
+    backend_data jsonb,
+    signon_users jsonb
+);
+
+
+--
+-- Name: mini_environments_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE public.mini_environments_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: mini_environments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE public.mini_environments_id_seq OWNED BY public.mini_environments.id;
+
+
+--
+-- Name: que_jobs; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.que_jobs (
+    priority smallint DEFAULT 100 NOT NULL,
+    run_at timestamp with time zone DEFAULT now() NOT NULL,
+    job_id bigint NOT NULL,
+    job_class text NOT NULL,
+    args json DEFAULT '[]'::json NOT NULL,
+    error_count integer DEFAULT 0 NOT NULL,
+    last_error text,
+    queue text DEFAULT ''::text NOT NULL
+);
+
+
+--
+-- Name: TABLE que_jobs; Type: COMMENT; Schema: public; Owner: -
+--
+
+COMMENT ON TABLE public.que_jobs IS '3';
+
+
+--
+-- Name: que_jobs_job_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE public.que_jobs_job_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: que_jobs_job_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE public.que_jobs_job_id_seq OWNED BY public.que_jobs.job_id;
+
+
+--
+-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.schema_migrations (
+    version character varying NOT NULL
+);
+
+
+--
+-- Name: terraform_aws_backends; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.terraform_aws_backends (
+    id bigint NOT NULL,
+    label character varying,
+    aws_region character varying,
+    aws_access_key_id character varying,
+    aws_secret_access_key character varying,
+    created_at timestamp without time zone NOT NULL,
+    updated_at timestamp without time zone NOT NULL,
+    domain character varying
+);
+
+
+--
+-- Name: terraform_aws_backends_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE public.terraform_aws_backends_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: terraform_aws_backends_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE public.terraform_aws_backends_id_seq OWNED BY public.terraform_aws_backends.id;
+
+
+--
+-- Name: terraform_libvirt_backends; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.terraform_libvirt_backends (
+    id bigint NOT NULL,
+    label character varying,
+    uri character varying,
+    created_at timestamp without time zone NOT NULL,
+    updated_at timestamp without time zone NOT NULL,
+    domain character varying
+);
+
+
+--
+-- Name: terraform_libvirt_backends_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE public.terraform_libvirt_backends_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: terraform_libvirt_backends_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE public.terraform_libvirt_backends_id_seq OWNED BY public.terraform_libvirt_backends.id;
+
+
+--
+-- Name: terraform_states; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.terraform_states (
+    id bigint NOT NULL,
+    data json,
+    created_at timestamp without time zone NOT NULL,
+    updated_at timestamp without time zone NOT NULL,
+    state_id character varying DEFAULT ''::character varying NOT NULL
+);
+
+
+--
+-- Name: terraform_states_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE public.terraform_states_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: terraform_states_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE public.terraform_states_id_seq OWNED BY public.terraform_states.id;
+
+
+--
+-- Name: users; Type: TABLE; Schema: public; Owner: -
+--
+
+CREATE TABLE public.users (
+    id bigint NOT NULL,
+    name character varying,
+    email character varying,
+    uid character varying,
+    organisation_slug character varying,
+    organisation_content_id character varying,
+    permissions text,
+    remotely_signed_out boolean DEFAULT false,
+    disabled boolean DEFAULT false
+);
+
+
+--
+-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE public.users_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+
+--
+-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;
+
+
+--
+-- Name: finished_terraform_jobs id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.finished_terraform_jobs ALTER COLUMN id SET DEFAULT nextval('public.finished_terraform_jobs_id_seq'::regclass);
+
+
+--
+-- Name: mini_environments id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.mini_environments ALTER COLUMN id SET DEFAULT nextval('public.mini_environments_id_seq'::regclass);
+
+
+--
+-- Name: que_jobs job_id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.que_jobs ALTER COLUMN job_id SET DEFAULT nextval('public.que_jobs_job_id_seq'::regclass);
+
+
+--
+-- Name: terraform_aws_backends id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.terraform_aws_backends ALTER COLUMN id SET DEFAULT nextval('public.terraform_aws_backends_id_seq'::regclass);
+
+
+--
+-- Name: terraform_libvirt_backends id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.terraform_libvirt_backends ALTER COLUMN id SET DEFAULT nextval('public.terraform_libvirt_backends_id_seq'::regclass);
+
+
+--
+-- Name: terraform_states id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.terraform_states ALTER COLUMN id SET DEFAULT nextval('public.terraform_states_id_seq'::regclass);
+
+
+--
+-- Name: users id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.users ALTER COLUMN id SET DEFAULT nextval('public.users_id_seq'::regclass);
+
+
+--
+-- Name: ar_internal_metadata ar_internal_metadata_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.ar_internal_metadata
+    ADD CONSTRAINT ar_internal_metadata_pkey PRIMARY KEY (key);
+
+
+--
+-- Name: finished_terraform_jobs finished_terraform_jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.finished_terraform_jobs
+    ADD CONSTRAINT finished_terraform_jobs_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: govuk_guix_revisions govuk_guix_revisions_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.govuk_guix_revisions
+    ADD CONSTRAINT govuk_guix_revisions_pkey PRIMARY KEY (commit_hash);
+
+
+--
+-- Name: mini_environments mini_environments_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.mini_environments
+    ADD CONSTRAINT mini_environments_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: que_jobs que_jobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.que_jobs
+    ADD CONSTRAINT que_jobs_pkey PRIMARY KEY (queue, priority, run_at, job_id);
+
+
+--
+-- Name: schema_migrations schema_migrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.schema_migrations
+    ADD CONSTRAINT schema_migrations_pkey PRIMARY KEY (version);
+
+
+--
+-- Name: terraform_aws_backends terraform_aws_backends_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.terraform_aws_backends
+    ADD CONSTRAINT terraform_aws_backends_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: terraform_libvirt_backends terraform_libvirt_backends_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.terraform_libvirt_backends
+    ADD CONSTRAINT terraform_libvirt_backends_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: terraform_states terraform_states_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.terraform_states
+    ADD CONSTRAINT terraform_states_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.users
+    ADD CONSTRAINT users_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: index_govuk_guix_revisions_on_commit_hash; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE UNIQUE INDEX index_govuk_guix_revisions_on_commit_hash ON public.govuk_guix_revisions USING btree (commit_hash);
+
+
+--
+-- Name: index_mini_environments_on_backend_type_and_backend_id; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_mini_environments_on_backend_type_and_backend_id ON public.mini_environments USING btree (backend_type, backend_id);
+
+
+--
+-- Name: index_mini_environments_on_govuk_guix_revision_id; Type: INDEX; Schema: public; Owner: -
+--
+
+CREATE INDEX index_mini_environments_on_govuk_guix_revision_id ON public.mini_environments USING btree (govuk_guix_revision_id);
+
+
+--
+-- Name: mini_environments fk_rails_12ab275069; Type: FK CONSTRAINT; Schema: public; Owner: -
+--
+
+ALTER TABLE ONLY public.mini_environments
+    ADD CONSTRAINT fk_rails_12ab275069 FOREIGN KEY (govuk_guix_revision_id) REFERENCES public.govuk_guix_revisions(commit_hash);
+
+
+--
+-- PostgreSQL database dump complete
+--
+
+SET search_path TO "$user", public;
+
+INSERT INTO "schema_migrations" (version) VALUES
+('20180127201423'),
+('20180127201504'),
+('20180127220312'),
+('20180127222948'),
+('20180128125638'),
+('20180206203924'),
+('20180216231420'),
+('20180217104954'),
+('20180217105604'),
+('20180217110041'),
+('20180217131053'),
+('20180227075519'),
+('20180305202300'),
+('20180305202557'),
+('20180305222157'),
+('20180311125442'),
+('20180311125615'),
+('20180326201857'),
+('20180327204244'),
+('20180327204322'),
+('20180329035512'),
+('20180406082851'),
+('20180406123612'),
+('20180406124443');
+
+