diff options
| -rw-r--r-- | fatcat-go/fatcat-schema.sql | 49 |
1 files changed, 29 insertions, 20 deletions
diff --git a/fatcat-go/fatcat-schema.sql b/fatcat-go/fatcat-schema.sql index 4a5973ec..f5983371 100644 --- a/fatcat-go/fatcat-schema.sql +++ b/fatcat-go/fatcat-schema.sql @@ -3,40 +3,49 @@ 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 UUID PRIMARY KEY DEFAULT uuid_generate_v4(), + 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 -) + active_editgroup_id BIGINT -- REFERENCES( editgroup(id) via ALTER below +); CREATE TABLE editgroup ( id BIGSERIAL PRIMARY KEY, extra_json JSON, - editor_id BIGINT REFERENCES(editor.id) NOT NULL, - description TEXT, -) + editor_id BIGSERIAL REFERENCES editor(id) NOT NULL, + description TEXT +); -ALTER TABLE editor ADD CONSTRAINT editor_eg_id FOREIGN KEY (editgroup.id) +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, -) + 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, -) + orcid TEXT +); -- Could denormalize a "is_live" flag into revision tables, to make indices -- more efficient @@ -45,15 +54,15 @@ 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 BIGINT REFERENCES(id), -) + 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 BIGINT REFERENCES(creator_ident.id) NOT NULL, - rev_id BIGINT REFERENCES(creator_rev.id), - redirect_id BIGINT REFERENCES(creator_ident.id), - editgroup_id BIGINT REFERENCES(editgroup.id) NOT NULL, -) + 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 +); |