summaryrefslogtreecommitdiffstats
path: root/rust/migrations/2019-05-09-051834_v0.3/up.sql
blob: d38e842aee30a3e5d4b55ae8cc68f22fcf1b0a62 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

-- Set of schema additions, tweaks, and missing indices
-- Part of v0.3.0 (May 2019) backwards-incompatible changes to the Fatcat API

-------------------- Release -----------------------------------------------

-- structured contrib author names (in addition to 'raw_name')
ALTER TABLE release_contrib 
ADD COLUMN given_name          TEXT CHECK (octet_length(raw_name) >= 1),
ADD COLUMN surname             TEXT CHECK (octet_length(surname) >= 1);

-- release_status => release_stage (clarifies meaning of field)
ALTER TABLE release_rev
RENAME COLUMN release_status TO release_stage;

-- additional fields; withdrawl tracking; additional external identifiers
ALTER TABLE release_rev
ADD COLUMN number                TEXT CHECK (octet_length(number) >= 1),
ADD COLUMN version               TEXT CHECK (octet_length(version) >= 1),
ADD COLUMN subtitle              TEXT CHECK (octet_length(subtitle) >= 1),
ADD COLUMN withdrawn_state       TEXT,  -- TODO: enum?
ADD COLUMN withdrawn_date        DATE,
ADD COLUMN withdrawn_year        BIGINT,
ADD COLUMN mag_id                TEXT CHECK (octet_length(mag_id) >= 1),
ADD COLUMN ark_id                TEXT CHECK (octet_length(ark_id) >= 5);

--CREATE INDEX IF NOT EXISTS release_rev_mag_idx ON release_rev(mag_id);
--CREATE INDEX IF NOT EXISTS release_rev_ark_idx ON release_rev(mag_id);

-------------------- Web Capture -------------------------------------------

ALTER TABLE webcapture_rev_cdx
ADD COLUMN size_bytes          BIGINT,
ADD COLUMN extra_json          JSONB;

-------------------- Edit Indices ------------------------------------------

-- there is already a UNIQ index on (editgroup_id, ident_id)
CREATE INDEX IF NOT EXISTS creator_edit_ident_idx ON creator_edit(ident_id);
CREATE INDEX IF NOT EXISTS container_edit_ident_idx ON container_edit(ident_id);
CREATE INDEX IF NOT EXISTS file_edit_ident_idx ON file_edit(ident_id);
CREATE INDEX IF NOT EXISTS fileset_edit_ident_idx ON fileset_edit(ident_id);
CREATE INDEX IF NOT EXISTS webcapture_edit_ident_idx ON webcapture_edit(ident_id);
CREATE INDEX IF NOT EXISTS release_edit_ident_idx ON release_edit(ident_id);
CREATE INDEX IF NOT EXISTS work_edit_ident_idx ON work_edit(ident_id);

-- TODO: not needed?
-- CREATE INDEX IF NOT EXISTS creator_edit_editgroup_idx ON creator_edit(editgroup_id);
-- CREATE INDEX IF NOT EXISTS container_edit_editgroup_idx ON container_edit(editgroup_id);
-- CREATE INDEX IF NOT EXISTS file_edit_editgroup_idx ON file_edit(editgroup_id);
-- CREATE INDEX IF NOT EXISTS fileset_edit_editgroup_idx ON fileset_edit(editgroup_id);
-- CREATE INDEX IF NOT EXISTS webcapture_edit_editgroup_idx ON webcapture_edit(editgroup_id);
-- CREATE INDEX IF NOT EXISTS release_edit_editgroup_idx ON release_edit(editgroup_id);
-- CREATE INDEX IF NOT EXISTS work_edit_editgroup_idx ON work_edit(editgroup_id);