From cf28f344ed2a032d14036e3f1e54db4f9d3b8a68 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Isma=C3=ABl=20Bouya?= Date: Sun, 6 May 2018 10:03:23 +0200 Subject: Add missing index and view --- db/migrations.go | 39 ++++++++++++++++++++++++++++++++++++--- 1 file changed, 36 insertions(+), 3 deletions(-) (limited to 'db/migrations.go') diff --git a/db/migrations.go b/db/migrations.go index 38ea8aa..ada0296 100644 --- a/db/migrations.go +++ b/db/migrations.go @@ -22,10 +22,11 @@ var migrations []Migration = []Migration{ `CREATE TABLE market_configs ( id BIGSERIAL PRIMARY KEY, market_name text NOT NULL, - user_id bigint NOT NULL REFERENCES users(id), + user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, config jsonb, UNIQUE(user_id, market_name) )`, + `CREATE INDEX IF NOT EXISTS market_configs_user_id ON market_configs (user_id)`, `CREATE TABLE reports ( id BIGSERIAL PRIMARY KEY, date timestamp with time zone NOT NULL, @@ -36,13 +37,45 @@ var migrations []Migration = []Migration{ `CREATE TABLE report_lines ( id BIGSERIAL PRIMARY KEY, date timestamp with time zone NOT NULL, - report_id bigint NOT NULL REFERENCES reports(id), + report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE, type text, payload jsonb )`, "CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)", "CREATE INDEX IF NOT EXISTS report_lines_type ON report_lines (type)", + `CREATE VIEW view_report_lines_by_user AS + SELECT report_lines.id, + reports.id AS report_id, + market_configs.market_name, + users.id AS user_id, + users.email AS user_email, + reports.date AS report_date, + report_lines.date, + report_lines.payload + FROM (((public.report_lines + JOIN public.reports ON ((reports.id = report_lines.report_id))) + JOIN public.market_configs ON ((reports.market_config_id = market_configs.id))) + JOIN public.users ON ((market_configs.user_id = users.id)))`, }, - Down: []string{"DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"}, + Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"}, + }, + { + Version: 201805061000, + Up: []string{ + `CREATE VIEW "view_balances" AS + SELECT report_lines.id, + reports.id AS report_id, + market_configs.market_name, + market_configs.id AS market_id, + reports.date AS report_date, + report_lines.date, + report_lines.payload + FROM (((report_lines + JOIN reports ON ((reports.id = report_lines.report_id))) + JOIN market_configs ON ((reports.market_config_id = market_configs.id))) + WHERE report_lines.payload::jsonb->'checkpoint' IS NOT NULL`, + `CREATE INDEX checkpoints_idx ON report_lines ((payload->>'checkpoint'))`, + }, + Down: []string{"DROP VIEW view_balances", "DROP INDEX checkpoints_idx"}, }, } -- cgit v1.2.3