]>
Commit | Line | Data |
---|---|---|
4215df47 | 1 | package db |
2 | ||
3 | type Migration struct { | |
4 | Version int64 | |
5 | Up []string | |
6 | Down []string | |
7 | } | |
8 | ||
9 | var migrations []Migration = []Migration{ | |
10 | { | |
11 | Version: 1, | |
12 | Up: []string{ | |
13 | `CREATE TABLE users ( | |
14 | id BIGSERIAL PRIMARY KEY, | |
15 | email text NOT NULL, | |
16 | password_hash text NOT NULL, | |
17 | otp_secret text, | |
18 | is_otp_setup boolean, | |
19 | status smallint, | |
20 | UNIQUE(email) | |
21 | )`, | |
22 | `CREATE TABLE market_configs ( | |
23 | id BIGSERIAL PRIMARY KEY, | |
24 | market_name text NOT NULL, | |
cf28f344 | 25 | user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
fcb9f26c | 26 | config jsonb |
4215df47 | 27 | )`, |
fcb9f26c | 28 | `CREATE UNIQUE INDEX IF NOT EXISTS market_name_user_id_idx ON public.market_configs (user_id, market_name)`, |
d4fdccf2 | 29 | `CREATE INDEX IF NOT EXISTS market_configs_user_id ON market_configs (user_id)`, |
17b68539 | 30 | `CREATE TABLE reports ( |
31 | id BIGSERIAL PRIMARY KEY, | |
32 | date timestamp with time zone NOT NULL, | |
fcb9f26c | 33 | market_config_id bigint NOT NULL REFERENCES market_configs(id), |
17b68539 | 34 | debug boolean |
35 | )`, | |
36 | "CREATE INDEX IF NOT EXISTS reports_market_config_id ON reports (market_config_id)", | |
4215df47 | 37 | `CREATE TABLE report_lines ( |
38 | id BIGSERIAL PRIMARY KEY, | |
39 | date timestamp with time zone NOT NULL, | |
cf28f344 | 40 | report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE, |
4215df47 | 41 | type text, |
42 | payload jsonb | |
43 | )`, | |
44 | "CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)", | |
45 | "CREATE INDEX IF NOT EXISTS report_lines_type ON report_lines (type)", | |
cf28f344 IB |
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 | |
fcb9f26c IB |
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`, | |
4215df47 | 59 | }, |
fcb9f26c | 60 | Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE report_lines", "DROP TABLE reports", "DROP TABLE market_configs", "DROP TABLE users"}, |
cf28f344 IB |
61 | }, |
62 | { | |
d4fdccf2 | 63 | Version: 201805061000, |
cf28f344 IB |
64 | Up: []string{ |
65 | `CREATE VIEW "view_balances" AS | |
d4fdccf2 | 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'))`, | |
cf28f344 IB |
78 | }, |
79 | Down: []string{"DROP VIEW view_balances", "DROP INDEX checkpoints_idx"}, | |
4215df47 | 80 | }, |
81 | } |