aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2018-05-09 13:22:19 -0700
committerBryan Newbold <bnewbold@robocracy.org>2018-05-09 13:22:19 -0700
commitcb37df1888f77654d0e2d7f0a96093a26014ed64 (patch)
tree8681894d1950ed391432ba946f6d59853c74edd8
parent5af7dc8d208ed7f6fc61fef13b81d636e6e0dfcd (diff)
downloadfatcat-cb37df1888f77654d0e2d7f0a96093a26014ed64.tar.gz
fatcat-cb37df1888f77654d0e2d7f0a96093a26014ed64.zip
experimenting with raw SQL
-rw-r--r--fatcat-go/fatcat-schema.sql59
1 files changed, 59 insertions, 0 deletions
diff --git a/fatcat-go/fatcat-schema.sql b/fatcat-go/fatcat-schema.sql
new file mode 100644
index 00000000..4a5973ec
--- /dev/null
+++ b/fatcat-go/fatcat-schema.sql
@@ -0,0 +1,59 @@
+
+-- 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 UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ 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 BIGINT REFERENCES(editor.id) NOT NULL,
+ description TEXT,
+)
+
+ALTER TABLE editor ADD CONSTRAINT editor_eg_id FOREIGN KEY (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 BIGINT REFERENCES(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,
+)