summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2018-05-14 14:42:19 -0700
committerBryan Newbold <bnewbold@robocracy.org>2018-05-14 14:42:19 -0700
commit5dfe6818c13c67a5fa62c2ff0c3ccc7044ae16f6 (patch)
tree46fe620f476ad735144ef8d694a8e6d6a36f85bd
parentb61f15156a000c3f156af3d0fc809ca32912af34 (diff)
downloadfatcat-5dfe6818c13c67a5fa62c2ff0c3ccc7044ae16f6.tar.gz
fatcat-5dfe6818c13c67a5fa62c2ff0c3ccc7044ae16f6.zip
flesh out SQL schema
-rw-r--r--rust/migrations/2018-05-12-001226_init/down.sql29
-rw-r--r--rust/migrations/2018-05-12-001226_init/up.sql140
2 files changed, 166 insertions, 3 deletions
diff --git a/rust/migrations/2018-05-12-001226_init/down.sql b/rust/migrations/2018-05-12-001226_init/down.sql
index 6c379b2f..087f02b2 100644
--- a/rust/migrations/2018-05-12-001226_init/down.sql
+++ b/rust/migrations/2018-05-12-001226_init/down.sql
@@ -1,7 +1,30 @@
-DROP TABLE IF EXISTS editor CASCADE;
-DROP TABLE IF EXISTS editgroup CASCADE;
-DROP TABLE IF EXISTS changelog CASCADE;
+-- in opposite order as up.sql
+
+DROP TABLE IF EXISTS release_contrib CASCADE;
+DROP TABLE IF EXISTS release_ref CASCADE;
+DROP TABLE IF EXISTS file_release CASCADE;
+
+DROP TABLE IF EXISTS work_rev CASCADE;
+DROP TABLE IF EXISTS work_ident CASCADE;
+DROP TABLE IF EXISTS work_edit CASCADE;
+
+DROP TABLE IF EXISTS release_rev CASCADE;
+DROP TABLE IF EXISTS release_ident CASCADE;
+DROP TABLE IF EXISTS release_edit CASCADE;
+
+DROP TABLE IF EXISTS file_rev CASCADE;
+DROP TABLE IF EXISTS file_ident CASCADE;
+DROP TABLE IF EXISTS file_edit CASCADE;
+
+DROP TABLE IF EXISTS container_rev CASCADE;
+DROP TABLE IF EXISTS container_ident CASCADE;
+DROP TABLE IF EXISTS container_edit CASCADE;
+
DROP TABLE IF EXISTS creator_rev CASCADE;
DROP TABLE IF EXISTS creator_ident CASCADE;
DROP TABLE IF EXISTS creator_edit CASCADE;
+
+DROP TABLE IF EXISTS editor CASCADE;
+DROP TABLE IF EXISTS editgroup CASCADE;
+DROP TABLE IF EXISTS changelog CASCADE;
diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql
index 2933deef..1a8c25b5 100644
--- a/rust/migrations/2018-05-12-001226_init/up.sql
+++ b/rust/migrations/2018-05-12-001226_init/up.sql
@@ -32,6 +32,7 @@ CREATE TABLE changelog (
timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now()
);
+-------------------- Creators -----------------------------------------------
CREATE TABLE creator_rev (
id BIGSERIAL PRIMARY KEY,
extra_json JSON,
@@ -59,3 +60,142 @@ CREATE TABLE creator_edit (
redirect_id UUID REFERENCES creator_ident(id),
editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL
);
+
+-------------------- Containers --------------------------------------------
+CREATE TABLE container_rev (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+
+ name TEXT,
+ parent_ident_id BIGINT REFERENCES container_rev(id),
+ publisher TEXT,
+ sortname TEXT,
+ issn TEXT -- TODO: char
+);
+
+CREATE TABLE container_ident (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ is_live BOOL NOT NULL DEFAULT false,
+ rev_id BIGINT REFERENCES container_rev(id),
+ redirect_id UUID REFERENCES container_ident(id)
+);
+
+CREATE TABLE container_edit (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+ ident_id UUID REFERENCES container_ident(id) NOT NULL,
+ rev_id BIGINT REFERENCES container_rev(id),
+ redirect_id UUID REFERENCES container_ident(id),
+ editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL
+);
+
+-------------------- Files -------------------------------------------------
+CREATE TABLE file_rev (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+
+ size INTEGER, -- TODO: uint64
+ sha1 TEXT, -- TODO: varchar or bytes
+ url TEXT -- TODO: URL table
+);
+
+CREATE TABLE file_ident (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ is_live BOOL NOT NULL DEFAULT false,
+ rev_id BIGINT REFERENCES file_rev(id),
+ redirect_id UUID REFERENCES file_ident(id)
+);
+
+CREATE TABLE file_edit (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+ ident_id UUID REFERENCES file_ident(id) NOT NULL,
+ rev_id BIGINT REFERENCES file_rev(id),
+ redirect_id UUID REFERENCES file_ident(id),
+ editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL
+);
+
+-------------------- Release -----------------------------------------------
+CREATE TABLE release_rev (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+
+ work_ident_id UUID, -- FOREIGN KEY; see ALRTER below
+ container_ident_id UUID REFERENCES container_ident(id),
+ license TEXT, -- TODO: ?
+ release_type TEXT, -- TODO: enum
+ date TEXT, -- XXX: datetime
+ doi TEXT, -- TODO: identifier table?
+ volume TEXT,
+ pages TEXT,
+ issue TEXT
+);
+
+CREATE TABLE release_ident (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ is_live BOOL NOT NULL DEFAULT false,
+ rev_id BIGINT REFERENCES release_rev(id),
+ redirect_id UUID REFERENCES release_ident(id)
+);
+
+CREATE TABLE release_edit (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+ ident_id UUID REFERENCES release_ident(id) NOT NULL,
+ rev_id BIGINT REFERENCES release_rev(id),
+ redirect_id UUID REFERENCES release_ident(id),
+ editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL
+);
+
+-------------------- Works --------------------------------------------------
+CREATE TABLE work_rev (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+
+ title TEXT,
+ work_type TEXT, -- TODO: enum?
+ primary_release_id UUID REFERENCES release_ident(id)
+);
+
+CREATE TABLE work_ident (
+ id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+ is_live BOOL NOT NULL DEFAULT false,
+ rev_id BIGINT REFERENCES work_rev(id),
+ redirect_id UUID REFERENCES work_ident(id)
+);
+
+CREATE TABLE work_edit (
+ id BIGSERIAL PRIMARY KEY,
+ extra_json JSON,
+ ident_id UUID REFERENCES work_ident(id) NOT NULL,
+ rev_id BIGINT REFERENCES work_rev(id),
+ redirect_id UUID REFERENCES work_ident(id),
+ editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL
+);
+
+
+ALTER TABLE release_rev
+ ADD CONSTRAINT release_containeridentid_fkey FOREIGN KEY (work_ident_id)
+ REFERENCES work_ident(id);
+
+-------------------- Inter-Entity Relations
+
+CREATE TABLE release_contrib (
+ release_rev BIGSERIAL REFERENCES release_rev(id) NOT NULL,
+ creator_ident_id UUID REFERENCES creator_ident(id),
+ stub TEXT,
+ contrib_type TEXT
+);
+
+CREATE TABLE release_ref (
+ id BIGSERIAL PRIMARY KEY,
+ release_rev BIGSERIAL REFERENCES release_rev(id) NOT NULL,
+ target_release_ident_id UUID REFERENCES creator_ident(id),
+ index INTEGER,
+ stub TEXT
+);
+
+CREATE TABLE file_release (
+ file_rev BIGSERIAL REFERENCES file_rev(id) NOT NULL,
+ target_release_ident_id UUID REFERENCES creator_ident(id) NOT NULL
+);