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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
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"},
},
{
Version: 201805101000,
Up: []string{
"CREATE TYPE market_config_status AS ENUM ('enabled', 'disabled', 'invalid_credentials')",
"ALTER TABLE market_configs ADD status market_config_status NOT NULL DEFAULT 'disabled'",
},
Down: []string{
"ALTER TABLE market_configs DROP COLUMN status",
"DROP TYPE market_config_status",
},
},
{
Version: 201805131000,
Up: []string{
"CREATE TYPE user_role AS ENUM ('admin', 'user')",
"ALTER TABLE users ADD role user_role NOT NULL DEFAULT 'user'",
},
Down: []string{
"ALTER TABLE users DROP COLUMN role",
"DROP TYPE user_role",
},
},
{
Version: 201807311000,
Up: []string{
"CREATE TYPE portfolio_profile AS ENUM ('high-liquidity', 'medium-liquidity')",
`CREATE TABLE bot_settings (
id BIGSERIAL PRIMARY KEY,
user_id bigint REFERENCES users(id) ON DELETE CASCADE,
portfolio_profile portfolio_profile
)`,
`CREATE INDEX IF NOT EXISTS bot_settings_user_id ON bot_settings (user_id)`,
`CREATE UNIQUE INDEX bot_settings_unique_null_user_id ON bot_settings (coalesce(user_id, 0))`,
},
Down: []string{
"DROP TABLE bot_settings",
"DROP TYPE portfolio_profile",
},
},
{
Version: 201808031000,
Up: []string{
`CREATE VIEW market_configs_augmented AS
SELECT market_configs.*,
COALESCE(u.portfolio_profile, g.portfolio_profile)
AS portfolio_profile
FROM market_configs
LEFT JOIN bot_settings AS u
ON u.user_id = market_configs.user_id,
(
SELECT portfolio_profile
FROM bot_settings
WHERE user_id IS NULL
) AS g`,
},
Down: []string{
"DROP VIEW market_configs_augmented",
},
},
}
|
