flesh out SQL schema

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
+);