diff options
Diffstat (limited to 'rust/migrations/2019-01-01-000000_init')
| -rw-r--r-- | rust/migrations/2019-01-01-000000_init/up.sql | 86 |
1 files changed, 43 insertions, 43 deletions
diff --git a/rust/migrations/2019-01-01-000000_init/up.sql b/rust/migrations/2019-01-01-000000_init/up.sql index d2fe111b..2bb3f4ec 100644 --- a/rust/migrations/2019-01-01-000000_init/up.sql +++ b/rust/migrations/2019-01-01-000000_init/up.sql @@ -48,7 +48,7 @@ CREATE TABLE editgroup ( created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, submitted TIMESTAMP WITH TIME ZONE, is_accepted BOOLEAN DEFAULT false NOT NULL, - description TEXT, + description TEXT CHECK (octet_length(description) >= 1), extra_json JSONB ); @@ -61,7 +61,7 @@ CREATE TABLE editgroup_annotation ( editgroup_id UUID REFERENCES editgroup(id) NOT NULL, editor_id UUID REFERENCES editor(id) NOT NULL, created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL, - comment_markdown TEXT, + comment_markdown TEXT CHECK (octet_length(comment_markdown) >= 1), extra_json JSONB ); @@ -80,7 +80,7 @@ CREATE INDEX changelog_editgroup_idx ON changelog(editgroup_id); CREATE TABLE abstracts ( -- fixed size hash (in hex). TODO: switch to bytes sha1 TEXT PRIMARY KEY CHECK (octet_length(sha1) = 40), - content TEXT NOT NULL + content TEXT NOT NULL CHECK (octet_length(content) >= 8) ); CREATE TABLE refs_blob ( @@ -94,9 +94,9 @@ CREATE TABLE creator_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), extra_json JSONB, - display_name TEXT NOT NULL, - given_name TEXT, - surname TEXT, + display_name TEXT NOT NULL CHECK (octet_length(display_name) >= 1), + given_name TEXT CHECK (octet_length(given_name) >= 1), + surname TEXT CHECK (octet_length(surname) >= 1), -- fixed size identifier orcid TEXT CHECK(octet_length(orcid) = 19), -- limited size for data quality @@ -139,9 +139,9 @@ CREATE TABLE container_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), extra_json JSONB, - name TEXT NOT NULL, + name TEXT NOT NULL CHECK (octet_length(name) >= 1), container_type TEXT, - publisher TEXT, + publisher TEXT CHECK (octet_length(publisher) >= 1), -- fixed size identifier issnl TEXT CHECK(octet_length(issnl) = 9), -- limited size for data quality @@ -180,10 +180,10 @@ CREATE TABLE file_rev ( size_bytes BIGINT, -- fixed size hashes (in hex). TODO: switch to binary type type - sha1 TEXT CHECK(octet_length(sha1) = 40), - sha256 TEXT CHECK(octet_length(sha256) = 64), - md5 TEXT CHECK(octet_length(md5) = 32), - mimetype TEXT + sha1 TEXT CHECK (octet_length(sha1) = 40), + sha256 TEXT CHECK (octet_length(sha256) = 64), + md5 TEXT CHECK (octet_length(md5) = 32), + mimetype TEXT CHECK (octet_length(mimetype) >= 3) ); CREATE INDEX file_rev_sha1_idx ON file_rev(sha1); @@ -193,8 +193,8 @@ CREATE INDEX file_rev_sha256_idx ON file_rev(sha256); CREATE TABLE file_rev_url ( id BIGSERIAL PRIMARY KEY, file_rev UUID REFERENCES file_rev(id) NOT NULL, - rel TEXT NOT NULL, -- TODO: enum? web, webarchive, repo, etc - url TEXT NOT NULL + rel TEXT NOT NULL CHECK (octet_length(rel) >= 1), -- TODO: enum? web, webarchive, repo, etc + url TEXT NOT NULL CHECK (octet_length(url) >= 1) ); CREATE INDEX file_rev_url_rev_idx ON file_rev_url(file_rev); @@ -230,8 +230,8 @@ CREATE TABLE fileset_rev ( CREATE TABLE fileset_rev_url ( id BIGSERIAL PRIMARY KEY, fileset_rev UUID REFERENCES fileset_rev(id) NOT NULL, - rel TEXT NOT NULL, -- TODO: enum? web, webarchive, repo, etc - url TEXT NOT NULL + rel TEXT NOT NULL CHECK (octet_length(rel) >= 1), -- TODO: enum? web, webarchive, repo, etc + url TEXT NOT NULL CHECK (octet_length(url) >= 1) ); CREATE INDEX fileset_rev_url_rev_idx ON fileset_rev_url(fileset_rev); @@ -239,7 +239,7 @@ CREATE INDEX fileset_rev_url_rev_idx ON fileset_rev_url(fileset_rev); CREATE TABLE fileset_rev_file ( id BIGSERIAL PRIMARY KEY, fileset_rev UUID REFERENCES fileset_rev(id) NOT NULL, - path_name TEXT NOT NULL, + path_name TEXT NOT NULL CHECK (octet_length(path_name) >= 1), size_bytes BIGINT NOT NULL, md5 TEXT CHECK(octet_length(md5) = 32), sha1 TEXT CHECK(octet_length(sha1) = 40), @@ -275,15 +275,15 @@ CREATE TABLE webcapture_rev ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), extra_json JSONB, - original_url TEXT NOT NULL, + original_url TEXT NOT NULL CHECK (octet_length(original_url) >= 1), timestamp TIMESTAMP WITH TIME ZONE NOT NULL ); CREATE TABLE webcapture_rev_url ( id BIGSERIAL PRIMARY KEY, webcapture_rev UUID REFERENCES webcapture_rev(id) NOT NULL, - rel TEXT NOT NULL, -- TODO: enum? web, webarchive, repo, etc - url TEXT NOT NULL + rel TEXT NOT NULL CHECK (octet_length(rel) >= 1), -- TODO: enum? web, webarchive, repo, etc + url TEXT NOT NULL CHECK (octet_length(url) >= 1) ); CREATE INDEX webcapture_rev_url_rev_idx ON webcapture_rev_url(webcapture_rev); @@ -291,10 +291,10 @@ CREATE INDEX webcapture_rev_url_rev_idx ON webcapture_rev_url(webcapture_rev); CREATE TABLE webcapture_rev_cdx ( id BIGSERIAL PRIMARY KEY, webcapture_rev UUID REFERENCES webcapture_rev(id) NOT NULL, - surt TEXT NOT NULL, + surt TEXT NOT NULL CHECK (octet_length(surt) >= 1), timestamp TIMESTAMP WITH TIME ZONE NOT NULL, - url TEXT NOT NULL, - mimetype TEXT, + url TEXT NOT NULL CHECK (octet_length(url) >= 1), + mimetype TEXT CHECK (octet_length(mimetype) >= 1), status_code BIGINT, sha1 TEXT CHECK(octet_length(sha1) = 40) NOT NULL, sha256 TEXT CHECK(octet_length(sha256) = 64) @@ -332,27 +332,27 @@ CREATE TABLE release_rev ( work_ident_id UUID NOT NULL, -- FOREIGN KEY; see ALRTER below container_ident_id UUID REFERENCES container_ident(id), refs_blob_sha1 TEXT REFERENCES refs_blob(sha1), - title TEXT NOT NULL, - original_title TEXT, + title TEXT NOT NULL CHECK (octet_length(title) >= 1), + original_title TEXT CHECK (octet_length(original_title) >= 1), release_type TEXT, -- TODO: enum release_status TEXT, -- TODO: enum release_date DATE, release_year BIGINT, - doi TEXT, + doi TEXT CHECK (octet_length(doi) >= 7), -- CHECK for length limit for data quality - pmid TEXT CHECK(octet_length(pmid) <= 12), - pmcid TEXT CHECK(octet_length(pmcid) <= 12), - wikidata_qid TEXT CHECK(octet_length(wikidata_qid) <= 12), - isbn13 TEXT CHECK(octet_length(isbn13) = 17), - core_id TEXT CHECK(octet_length(core_id) <= 12), - arxiv_id TEXT CHECK(octet_length(arxiv_id) <= 12), - jstor_id TEXT CHECK(octet_length(jstor_id) <= 12), - volume TEXT, - issue TEXT, - pages TEXT, - publisher TEXT, -- for books, NOT if container exists - language TEXT, -- primary language of the work's fulltext; RFC1766/ISO639-1 - license_slug TEXT + pmid TEXT CHECK (octet_length(pmid) <= 12), + pmcid TEXT CHECK (octet_length(pmcid) <= 12), + wikidata_qid TEXT CHECK (octet_length(wikidata_qid) <= 12), + isbn13 TEXT CHECK (octet_length(isbn13) = 17), + core_id TEXT CHECK (octet_length(core_id) <= 12), + arxiv_id TEXT CHECK (octet_length(arxiv_id) <= 12), + jstor_id TEXT CHECK (octet_length(jstor_id) <= 12), + volume TEXT CHECK (octet_length(volume) >= 1), + issue TEXT CHECK (octet_length(issue) >= 1), + pages TEXT CHECK (octet_length(pages) >= 1), + publisher TEXT CHECK (octet_length(publisher) >= 1), -- for books, NOT if container exists + language TEXT CHECK (octet_length(language) >= 1), -- primary language of the work's fulltext; RFC1766/ISO639-1 + license_slug TEXT CHECK (octet_length(license_slug) >= 1) -- TODO: oclc_ocn (TEXT or BIGINT) -- TODO: identifier table? ); @@ -371,8 +371,8 @@ CREATE TABLE release_rev_abstract ( id BIGSERIAL PRIMARY KEY, release_rev UUID REFERENCES release_rev(id) NOT NULL, abstract_sha1 TEXT REFERENCES abstracts(sha1) NOT NULL, - mimetype TEXT, - lang TEXT + mimetype TEXT CHECK (octet_length(mimetype) >= 1), + lang TEXT CHECK (octet_length(lang) >= 1) ); CREATE INDEX release_rev_abstract_rev_idx ON release_rev_abstract(release_rev); @@ -438,9 +438,9 @@ CREATE TABLE release_contrib ( id BIGSERIAL PRIMARY KEY, release_rev UUID REFERENCES release_rev(id) NOT NULL, creator_ident_id UUID REFERENCES creator_ident(id), - raw_name TEXT, + raw_name TEXT CHECK (octet_length(raw_name) >= 1), role TEXT, -- TODO: enum? - raw_affiliation TEXT, + raw_affiliation TEXT CHECK (octet_length(raw_affiliation) >= 1), index_val INTEGER, extra_json JSONB ); |