diff options
Diffstat (limited to 'rust/migrations/2018-05-12-001226_init')
| -rw-r--r-- | rust/migrations/2018-05-12-001226_init/down.sql | 29 | ||||
| -rw-r--r-- | rust/migrations/2018-05-12-001226_init/up.sql | 140 |
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 +); |