]>
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 | |
400ff25b | 76 | WHERE report_lines.payload::jsonb->>'checkpoint' IS NOT NULL`, |
d4fdccf2 | 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 | }, |
299b6b6d | 81 | { |
82 | Version: 201805101000, | |
83 | Up: []string{ | |
84 | "CREATE TYPE market_config_status AS ENUM ('enabled', 'disabled', 'invalid_credentials')", | |
85 | "ALTER TABLE market_configs ADD status market_config_status NOT NULL DEFAULT 'disabled'", | |
86 | }, | |
87 | Down: []string{ | |
88 | "ALTER TABLE market_configs DROP COLUMN status", | |
89 | "DROP TYPE market_config_status", | |
90 | }, | |
91 | }, | |
cf5bb85c | 92 | { |
93 | Version: 201805131000, | |
94 | Up: []string{ | |
95 | "CREATE TYPE user_role AS ENUM ('admin', 'user')", | |
96 | "ALTER TABLE users ADD role user_role NOT NULL DEFAULT 'user'", | |
97 | }, | |
98 | Down: []string{ | |
99 | "ALTER TABLE users DROP COLUMN role", | |
100 | "DROP TYPE user_role", | |
101 | }, | |
102 | }, | |
4215df47 | 103 | } |