summaryrefslogtreecommitdiffstats
path: root/rust/migrations/2018-05-12-001226_init
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2018-06-27 19:53:42 -0700
committerBryan Newbold <bnewbold@robocracy.org>2018-06-27 19:53:42 -0700
commit794aa05969238517d3f21780c5450e1ca5c98a90 (patch)
tree1ee330951976e2a6e1c776e37fdedf2be799a8e4 /rust/migrations/2018-05-12-001226_init
parent7ac552af63a14ed2ca36a767e762399904652d44 (diff)
downloadfatcat-794aa05969238517d3f21780c5450e1ca5c98a90.tar.gz
fatcat-794aa05969238517d3f21780c5450e1ca5c98a90.zip
add a bunch of database indexes
Diffstat (limited to 'rust/migrations/2018-05-12-001226_init')
-rw-r--r--rust/migrations/2018-05-12-001226_init/up.sql37
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);
+
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------