blob: 7acd7153f0ce69314e1a783743e0de1e3a5fb89f (
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
|
-- 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;
-------------------- 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);
|
