summaryrefslogtreecommitdiffstats
path: root/rust/migrations/2018-05-12-001226_init/up.sql
diff options
context:
space:
mode:
Diffstat (limited to 'rust/migrations/2018-05-12-001226_init/up.sql')
-rw-r--r--rust/migrations/2018-05-12-001226_init/up.sql43
1 files changed, 28 insertions, 15 deletions
diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql
index d297f787..8b7e3176 100644
--- a/rust/migrations/2018-05-12-001226_init/up.sql
+++ b/rust/migrations/2018-05-12-001226_init/up.sql
@@ -1,5 +1,9 @@
-- written for Postgres 9.6 with OSSP extension for UUIDs -- ... but actually runs on Postgres 10 in qa/production
+-- Previously VARCHAR and fixed-size CHAR was used in this schema for specific
+-- columns (especially fixed-size external identifiers, and hashes). This was
+-- found to cause lookup problems, so switched to TEXT with CHECK constraints.
+
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
@@ -40,7 +44,8 @@ CREATE TABLE changelog (
CREATE INDEX changelog_editgroup_idx ON changelog(editgroup_id);
CREATE TABLE abstracts (
- sha1 VARCHAR(40) PRIMARY KEY, -- fixed size hash (in hex)
+ -- fixed size hash (in hex). TODO: switch to bytes
+ sha1 TEXT PRIMARY KEY CHECK (octet_length(sha1) == 40),
content TEXT NOT NULL
);
@@ -52,8 +57,11 @@ CREATE TABLE creator_rev (
display_name TEXT NOT NULL,
given_name TEXT,
surname TEXT,
- orcid VARCHAR(19), -- fixed size identifier
- wikidata_qid VARCHAR(12) -- limited size for data quality
+ -- fixed size identifier
+ orcid TEXT CHECK(octet_length(orcid) == 19),
+ -- limited size for data quality
+ wikidata_qid TEXT CHECK(octet_length(wikidata_qid) <= 12)
+
-- TODO: viaf TEXT,
-- TODO: aliases/alternatives
-- TODO: sortable name?
@@ -93,10 +101,13 @@ CREATE TABLE container_rev (
name TEXT NOT NULL,
publisher TEXT,
- issnl VARCHAR(9), -- fixed size identifier
- wikidata_qid VARCHAR(12), -- limited size for data quality
+ -- fixed size identifier
+ issnl TEXT CHECK(octet_length(issnl) == 9),
+ -- limited size for data quality
+ wikidata_qid TEXT CHECK(octet_length(wikidata_qid) <= 12),
abbrev TEXT,
- coden VARCHAR(12) -- limited size for data quality
+ -- limited size for data quality
+ coden TEXT CHECK(octet_length(coden) <= 6)
);
CREATE INDEX container_rev_issnl_idx ON container_rev(issnl);
@@ -130,9 +141,10 @@ CREATE TABLE file_rev (
extra_json JSONB,
size BIGINT,
- sha1 VARCHAR(40), -- fixed size hash (in hex)
- sha256 VARCHAR(64), -- fixed size hash (in hex)
- md5 VARCHAR(32), -- fixed size hash (in hex)
+ -- 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
);
@@ -183,11 +195,12 @@ CREATE TABLE release_rev (
release_status TEXT, -- TODO: enum
release_date DATE,
doi TEXT,
- pmid VARCHAR(12), -- limited for data quality
- pmcid VARCHAR(12), -- limited for data quality
- wikidata_qid VARCHAR(12), -- limited for data quality
- isbn13 VARCHAR(13), -- fixed size identifier
- core_id VARCHAR(12), -- limited for data quality
+ -- 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) == 13),
+ core_id TEXT CHECK(octet_length(core_id) <= 12),
volume TEXT,
issue TEXT,
pages TEXT,
@@ -208,7 +221,7 @@ CREATE INDEX release_rev_work_idx ON release_rev(work_ident_id);
CREATE TABLE release_rev_abstract (
id BIGSERIAL PRIMARY KEY,
release_rev UUID REFERENCES release_rev(id) NOT NULL,
- abstract_sha1 VARCHAR(40) REFERENCES abstracts(sha1) NOT NULL, -- fixed size hash (in hex)
+ abstract_sha1 TEXT REFERENCES abstracts(sha1) NOT NULL,
mimetype TEXT,
lang TEXT
);