diff options
| -rw-r--r-- | rust/migrations/2018-05-12-001226_init/up.sql | 37 |
1 files changed, 37 insertions, 0 deletions
diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql index 165bf123..1ee813ef 100644 --- a/rust/migrations/2018-05-12-001226_init/up.sql +++ b/rust/migrations/2018-05-12-001226_init/up.sql @@ -1,4 +1,5 @@ -- written for Postgres 9.6 with OSSP extension for UUIDs +-- ... but actually runs on Postgres 10 in qa/production CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; @@ -32,6 +33,9 @@ CREATE TABLE changelog ( timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL ); +-- for "is this editgroup merged" queries +CREATE INDEX changelog_editgroup_idx ON changelog(editgroup_id); + -------------------- Creators ----------------------------------------------- CREATE TABLE creator_rev ( id BIGSERIAL PRIMARY KEY, @@ -56,6 +60,8 @@ CREATE TABLE creator_ident ( redirect_id UUID REFERENCES creator_ident(id) ); +CREATE INDEX creator_ident_rev_idx ON creator_ident(rev_id); + CREATE TABLE creator_edit ( id BIGSERIAL PRIMARY KEY, editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, @@ -65,6 +71,8 @@ CREATE TABLE creator_edit ( extra_json JSON ); +CREATE INDEX creator_edit_idx ON creator_edit(editgroup_id); + -------------------- Containers -------------------------------------------- CREATE TABLE container_rev ( id BIGSERIAL PRIMARY KEY, @@ -86,6 +94,8 @@ CREATE TABLE container_ident ( redirect_id UUID REFERENCES container_ident(id) ); +CREATE INDEX container_ident_rev_idx ON container_ident(rev_id); + CREATE TABLE container_edit ( id BIGSERIAL PRIMARY KEY, editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, @@ -95,6 +105,8 @@ CREATE TABLE container_edit ( extra_json JSON ); +CREATE INDEX container_edit_idx ON container_edit(editgroup_id); + -------------------- Files ------------------------------------------------- CREATE TABLE file_rev ( id BIGSERIAL PRIMARY KEY, @@ -108,6 +120,10 @@ CREATE TABLE file_rev ( mimetype TEXT ); +CREATE INDEX file_rev_sha1_idx ON file_rev(sha1) WHERE sha1 IS NOT NULL; +CREATE INDEX file_rev_md5_idx ON file_rev(md5) WHERE md5 IS NOT NULL; +CREATE INDEX file_rev_sha256_idx ON file_rev(sha256) WHERE sha256 IS NOT NULL; + CREATE TABLE file_ident ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), is_live BOOL NOT NULL DEFAULT false, @@ -115,6 +131,8 @@ CREATE TABLE file_ident ( redirect_id UUID REFERENCES file_ident(id) ); +CREATE INDEX file_ident_rev_idx ON file_ident(rev_id); + CREATE TABLE file_edit ( id BIGSERIAL PRIMARY KEY, editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, @@ -124,6 +142,8 @@ CREATE TABLE file_edit ( extra_json JSON ); +CREATE INDEX file_edit_idx ON file_edit(editgroup_id); + -------------------- Release ----------------------------------------------- CREATE TABLE release_rev ( id BIGSERIAL PRIMARY KEY, @@ -146,6 +166,8 @@ CREATE TABLE release_rev ( ); CREATE INDEX release_rev_doi_idx ON release_rev(doi) WHERE doi IS NOT NULL; +CREATE INDEX release_rev_isbn13_idx ON release_rev(isbn13) WHERE isbn13 IS NOT NULL; +CREATE INDEX release_rev_work_idx ON release_rev(work_ident_id) WHERE work_ident_id IS NOT NULL; CREATE TABLE release_ident ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), @@ -154,6 +176,8 @@ CREATE TABLE release_ident ( redirect_id UUID REFERENCES release_ident(id) ); +CREATE INDEX release_ident_rev_idx ON release_ident(rev_id); + CREATE TABLE release_edit ( id BIGSERIAL PRIMARY KEY, editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, @@ -163,6 +187,8 @@ CREATE TABLE release_edit ( extra_json JSON ); +CREATE INDEX release_edit_idx ON release_edit(editgroup_id); + -------------------- Works -------------------------------------------------- CREATE TABLE work_rev ( id BIGSERIAL PRIMARY KEY, @@ -180,6 +206,8 @@ CREATE TABLE work_ident ( redirect_id UUID REFERENCES work_ident(id) ); +CREATE INDEX work_ident_rev_idx ON work_ident(rev_id); + CREATE TABLE work_edit ( id BIGSERIAL PRIMARY KEY, editgroup_id BIGINT REFERENCES editgroup(id) NOT NULL, @@ -189,6 +217,7 @@ CREATE TABLE work_edit ( extra_json JSON ); +CREATE INDEX work_edit_idx ON work_edit(editgroup_id); ALTER TABLE release_rev ADD CONSTRAINT release_containeridentid_fkey FOREIGN KEY (work_ident_id) @@ -205,6 +234,9 @@ CREATE TABLE release_contrib ( raw TEXT ); +CREATE INDEX release_contrib_rev_idx ON release_contrib(release_rev); +CREATE INDEX release_contrib_creator_idx ON release_contrib(creator_ident_id); + CREATE TABLE release_ref ( id BIGSERIAL PRIMARY KEY, release_rev BIGSERIAL REFERENCES release_rev(id) NOT NULL, @@ -218,12 +250,17 @@ CREATE TABLE release_ref ( locator TEXT ); +CREATE INDEX release_ref_rev_idx ON release_ref(release_rev); +CREATE INDEX release_ref_target_release_idx ON release_ref(target_release_ident_id); + CREATE TABLE file_release ( file_rev BIGSERIAL REFERENCES file_rev(id) NOT NULL, target_release_ident_id UUID REFERENCES release_ident(id) NOT NULL, PRIMARY KEY (file_rev, target_release_ident_id) ); +CREATE INDEX file_release_target_release_idx ON file_release(target_release_ident_id); + --------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- |