diff options
author | Ismaël Bouya <ismael.bouya@normalesup.org> | 2018-05-06 10:03:23 +0200 |
---|---|---|
committer | Ismaël Bouya <ismael.bouya@normalesup.org> | 2018-05-06 16:55:28 +0200 |
commit | cf28f344ed2a032d14036e3f1e54db4f9d3b8a68 (patch) | |
tree | 9dfff03767a0e7315fdb013bbc72b2d3f98343f3 /db/migrations.go | |
parent | bc06de9b83959e2dbccbbe15960f5fc07093c3de (diff) | |
download | Front-cf28f344ed2a032d14036e3f1e54db4f9d3b8a68.tar.gz Front-cf28f344ed2a032d14036e3f1e54db4f9d3b8a68.tar.zst Front-cf28f344ed2a032d14036e3f1e54db4f9d3b8a68.zip |
Add missing index and view
Diffstat (limited to 'db/migrations.go')
-rw-r--r-- | db/migrations.go | 39 |
1 files changed, 36 insertions, 3 deletions
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{ | |||
22 | `CREATE TABLE market_configs ( | 22 | `CREATE TABLE market_configs ( |
23 | id BIGSERIAL PRIMARY KEY, | 23 | id BIGSERIAL PRIMARY KEY, |
24 | market_name text NOT NULL, | 24 | market_name text NOT NULL, |
25 | user_id bigint NOT NULL REFERENCES users(id), | 25 | user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
26 | config jsonb, | 26 | config jsonb, |
27 | UNIQUE(user_id, market_name) | 27 | UNIQUE(user_id, market_name) |
28 | )`, | 28 | )`, |
29 | `CREATE INDEX IF NOT EXISTS market_configs_user_id ON market_configs (user_id)`, | ||
29 | `CREATE TABLE reports ( | 30 | `CREATE TABLE reports ( |
30 | id BIGSERIAL PRIMARY KEY, | 31 | id BIGSERIAL PRIMARY KEY, |
31 | date timestamp with time zone NOT NULL, | 32 | date timestamp with time zone NOT NULL, |
@@ -36,13 +37,45 @@ var migrations []Migration = []Migration{ | |||
36 | `CREATE TABLE report_lines ( | 37 | `CREATE TABLE report_lines ( |
37 | id BIGSERIAL PRIMARY KEY, | 38 | id BIGSERIAL PRIMARY KEY, |
38 | date timestamp with time zone NOT NULL, | 39 | date timestamp with time zone NOT NULL, |
39 | report_id bigint NOT NULL REFERENCES reports(id), | 40 | report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE, |
40 | type text, | 41 | type text, |
41 | payload jsonb | 42 | payload jsonb |
42 | )`, | 43 | )`, |
43 | "CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)", | 44 | "CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)", |
44 | "CREATE INDEX IF NOT EXISTS report_lines_type ON report_lines (type)", | 45 | "CREATE INDEX IF NOT EXISTS report_lines_type ON report_lines (type)", |
46 | `CREATE VIEW view_report_lines_by_user AS | ||
47 | SELECT report_lines.id, | ||
48 | reports.id AS report_id, | ||
49 | market_configs.market_name, | ||
50 | users.id AS user_id, | ||
51 | users.email AS user_email, | ||
52 | reports.date AS report_date, | ||
53 | report_lines.date, | ||
54 | report_lines.payload | ||
55 | FROM (((public.report_lines | ||
56 | JOIN public.reports ON ((reports.id = report_lines.report_id))) | ||
57 | JOIN public.market_configs ON ((reports.market_config_id = market_configs.id))) | ||
58 | JOIN public.users ON ((market_configs.user_id = users.id)))`, | ||
45 | }, | 59 | }, |
46 | Down: []string{"DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"}, | 60 | Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"}, |
61 | }, | ||
62 | { | ||
63 | Version: 201805061000, | ||
64 | Up: []string{ | ||
65 | `CREATE VIEW "view_balances" AS | ||
66 | SELECT report_lines.id, | ||
67 | reports.id AS report_id, | ||
68 | market_configs.market_name, | ||
69 | market_configs.id AS market_id, | ||
70 | reports.date AS report_date, | ||
71 | report_lines.date, | ||
72 | report_lines.payload | ||
73 | FROM (((report_lines | ||
74 | JOIN reports ON ((reports.id = report_lines.report_id))) | ||
75 | JOIN market_configs ON ((reports.market_config_id = market_configs.id))) | ||
76 | WHERE report_lines.payload::jsonb->'checkpoint' IS NOT NULL`, | ||
77 | `CREATE INDEX checkpoints_idx ON report_lines ((payload->>'checkpoint'))`, | ||
78 | }, | ||
79 | Down: []string{"DROP VIEW view_balances", "DROP INDEX checkpoints_idx"}, | ||
47 | }, | 80 | }, |
48 | } | 81 | } |