From 18344354c5985373740b0e0c815b79a7936ef34e 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 | 22 ++++++++++++++++++++++ 1 file changed, 22 insertions(+) diff --git a/db/migrations.go b/db/migrations.go index 38ea8aa..e6a7f22 100644 --- a/db/migrations.go +++ b/db/migrations.go @@ -26,6 +26,7 @@ var migrations []Migration = []Migration{ 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, @@ -45,4 +46,25 @@ var migrations []Migration = []Migration{ }, Down: []string{"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, + users.id AS user_id, + users.email AS user_email, + 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))) + JOIN users ON ((market_configs.user_id = users.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