1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
package db
type Migration struct {
Version int64
Up []string
Down []string
}
var migrations []Migration = []Migration{
{
Version: 1,
Up: []string{
`CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email text NOT NULL,
password_hash text NOT NULL,
otp_secret text,
is_otp_setup boolean,
status smallint,
UNIQUE(email)
)`,
`CREATE TABLE market_configs (
id BIGSERIAL PRIMARY KEY,
market_name text NOT NULL,
user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
config jsonb
)`,
`CREATE UNIQUE INDEX IF NOT EXISTS market_name_user_id_idx ON public.market_configs (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,
market_config_id bigint NOT NULL REFERENCES market_configs(id),
debug boolean
)`,
"CREATE INDEX IF NOT EXISTS reports_market_config_id ON reports (market_config_id)",
`CREATE TABLE report_lines (
id BIGSERIAL PRIMARY KEY,
date timestamp with time zone NOT NULL,
report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
type text,
payload jsonb
)`,
"CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)",
"CREATE INDEX IF NOT EXISTS report_lines_type ON report_lines (type)",
`CREATE VIEW view_report_lines_by_user 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 public.report_lines
JOIN public.reports ON reports.id = report_lines.report_id
JOIN public.market_configs ON reports.market_config_id = market_configs.id
JOIN public.users ON market_configs.user_id = users.id`,
},
Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE report_lines", "DROP TABLE reports", "DROP TABLE market_configs", "DROP TABLE users"},
},
{
Version: 201805061000,
Up: []string{
`CREATE VIEW "view_balances" AS
SELECT report_lines.id,
reports.id AS report_id,
market_configs.market_name,
market_configs.id AS market_id,
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
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"},
},
}
|
