diff options
Diffstat (limited to 'rust/migrations')
4 files changed, 111 insertions, 0 deletions
diff --git a/rust/migrations/00000000000000_diesel_initial_setup/down.sql b/rust/migrations/00000000000000_diesel_initial_setup/down.sql new file mode 100644 index 00000000..a9f52609 --- /dev/null +++ b/rust/migrations/00000000000000_diesel_initial_setup/down.sql @@ -0,0 +1,6 @@ +-- This file was automatically created by Diesel to setup helper functions +-- and other internal bookkeeping. This file is safe to edit, any future +-- changes will be added to existing projects as new migrations. + +DROP FUNCTION IF EXISTS diesel_manage_updated_at(_tbl regclass); +DROP FUNCTION IF EXISTS diesel_set_updated_at(); diff --git a/rust/migrations/00000000000000_diesel_initial_setup/up.sql b/rust/migrations/00000000000000_diesel_initial_setup/up.sql new file mode 100644 index 00000000..3400c7c5 --- /dev/null +++ b/rust/migrations/00000000000000_diesel_initial_setup/up.sql @@ -0,0 +1,37 @@ +-- This file was automatically created by Diesel to setup helper functions +-- and other internal bookkeeping. This file is safe to edit, any future +-- changes will be added to existing projects as new migrations. + + + + +-- Sets up a trigger for the given table to automatically set a column called +-- `updated_at` whenever the row is modified (unless `updated_at` was included +-- in the modified columns) +-- +-- # Example +-- +-- ```sql +-- CREATE TABLE users (id SERIAL PRIMARY KEY, updated_at TIMESTAMP NOT NULL DEFAULT NOW()); +-- +-- SELECT diesel_manage_updated_at('users'); +-- ``` +CREATE OR REPLACE FUNCTION diesel_manage_updated_at(_tbl regclass) RETURNS VOID AS $$ +BEGIN + EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s + FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl); +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION diesel_set_updated_at() RETURNS trigger AS $$ +BEGIN + IF ( + NEW IS DISTINCT FROM OLD AND + NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at + ) THEN + NEW.updated_at := current_timestamp; + END IF; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + diff --git a/rust/migrations/2018-05-12-001226_init/down.sql b/rust/migrations/2018-05-12-001226_init/down.sql new file mode 100644 index 00000000..6c379b2f --- /dev/null +++ b/rust/migrations/2018-05-12-001226_init/down.sql @@ -0,0 +1,7 @@ + +DROP TABLE IF EXISTS editor CASCADE; +DROP TABLE IF EXISTS editgroup CASCADE; +DROP TABLE IF EXISTS changelog CASCADE; +DROP TABLE IF EXISTS creator_rev CASCADE; +DROP TABLE IF EXISTS creator_ident CASCADE; +DROP TABLE IF EXISTS creator_edit CASCADE; diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql new file mode 100644 index 00000000..2933deef --- /dev/null +++ b/rust/migrations/2018-05-12-001226_init/up.sql @@ -0,0 +1,61 @@ +-- written for Postgres 9.6 with OSSP extension for UUIDs + +CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; + + +-- uuid_generate_v1mc: timestamp ordered, random MAC address +-- uuid_generate_v4: totally random + +-- NB: could use LIKE clause, or "composite types" + +CREATE TABLE editor ( + id BIGSERIAL PRIMARY KEY, + username TEXT NOT NULL, + is_admin BOOLEAN NOT NULL DEFAULT false, + active_editgroup_id BIGINT -- REFERENCES( editgroup(id) via ALTER below +); + +CREATE TABLE editgroup ( + id BIGSERIAL PRIMARY KEY, + extra_json JSON, + editor_id BIGSERIAL REFERENCES editor(id) NOT NULL, + description TEXT +); + +ALTER TABLE editor + ADD CONSTRAINT editor_editgroupid_fkey FOREIGN KEY (active_editgroup_id) + REFERENCES editgroup(id); + +CREATE TABLE changelog ( + id BIGSERIAL PRIMARY KEY, + editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, + timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() +); + +CREATE TABLE creator_rev ( + id BIGSERIAL PRIMARY KEY, + extra_json JSON, + + name TEXT, + orcid TEXT +); + +-- Could denormalize a "is_live" flag into revision tables, to make indices +-- more efficient +CREATE INDEX creator_rev_orcid_idx ON creator_rev(orcid) WHERE orcid IS NOT NULL; + +CREATE TABLE creator_ident ( + id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), + is_live BOOL NOT NULL DEFAULT false, + rev_id BIGINT REFERENCES creator_rev(id), + redirect_id UUID REFERENCES creator_ident(id) +); + +CREATE TABLE creator_edit ( + id BIGSERIAL PRIMARY KEY, + extra_json JSON, + ident_id UUID REFERENCES creator_ident(id) NOT NULL, + rev_id BIGINT REFERENCES creator_rev(id), + redirect_id UUID REFERENCES creator_ident(id), + editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL +); |