]>
Commit | Line | Data |
---|---|---|
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, | |
25 | user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE, | |
26 | config jsonb | |
27 | )`, | |
28 | `CREATE UNIQUE INDEX IF NOT EXISTS market_name_user_id_idx ON public.market_configs (user_id, market_name)`, | |
29 | `CREATE INDEX IF NOT EXISTS market_configs_user_id ON market_configs (user_id)`, | |
30 | `CREATE TABLE reports ( | |
31 | id BIGSERIAL PRIMARY KEY, | |
32 | date timestamp with time zone NOT NULL, | |
33 | market_config_id bigint NOT NULL REFERENCES market_configs(id), | |
34 | debug boolean | |
35 | )`, | |
36 | "CREATE INDEX IF NOT EXISTS reports_market_config_id ON reports (market_config_id)", | |
37 | `CREATE TABLE report_lines ( | |
38 | id BIGSERIAL PRIMARY KEY, | |
39 | date timestamp with time zone NOT NULL, | |
40 | report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE, | |
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)", | |
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`, | |
59 | }, | |
60 | Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE report_lines", "DROP TABLE reports", "DROP TABLE market_configs", "DROP TABLE users"}, | |
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"}, | |
80 | }, | |
81 | } |