From b9200623b0be3c8b663c1000b63c3ab581b7d34b Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Thu, 10 May 2018 10:34:47 -0700 Subject: basic database schema reload --- golang/sql/fatcat-schema.sql | 68 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 68 insertions(+) create mode 100644 golang/sql/fatcat-schema.sql (limited to 'golang/sql/fatcat-schema.sql') diff --git a/golang/sql/fatcat-schema.sql b/golang/sql/fatcat-schema.sql new file mode 100644 index 00000000..f5983371 --- /dev/null +++ b/golang/sql/fatcat-schema.sql @@ -0,0 +1,68 @@ + +-- written for Postgres 9.6 with OSSP extension for UUIDs + +CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; + +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; + +-- 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, + 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 +); -- cgit v1.2.3