3 type Migration struct {
9 var migrations []Migration = []Migration{
14 id BIGSERIAL PRIMARY KEY,
16 password_hash text NOT NULL,
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,
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),
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,
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,
51 users.email AS user_email,
52 reports.date AS report_date,
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`,
60 Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE report_lines", "DROP TABLE reports", "DROP TABLE market_configs", "DROP TABLE users"},
63 Version: 201805061000,
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,
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'))`,
79 Down: []string{"DROP VIEW view_balances", "DROP INDEX checkpoints_idx"},
82 Version: 201805101000,
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'",
88 "ALTER TABLE market_configs DROP COLUMN status",
89 "DROP TYPE market_config_status",
93 Version: 201805131000,
95 "CREATE TYPE user_role AS ENUM ('admin', 'user')",
96 "ALTER TABLE users ADD role user_role NOT NULL DEFAULT 'user'",
99 "ALTER TABLE users DROP COLUMN role",
100 "DROP TYPE user_role",
104 Version: 201807311000,
106 "CREATE TYPE portfolio_profile AS ENUM ('high-liquidity', 'medium-liquidity')",
107 `CREATE TABLE bot_settings (
108 id BIGSERIAL PRIMARY KEY,
109 user_id bigint REFERENCES users(id) ON DELETE CASCADE,
110 portfolio_profile portfolio_profile
112 `CREATE INDEX IF NOT EXISTS bot_settings_user_id ON bot_settings (user_id)`,
113 `CREATE UNIQUE INDEX bot_settings_unique_null_user_id ON bot_settings (coalesce(user_id, 0))`,
116 "DROP TABLE bot_settings",
117 "DROP TYPE portfolio_profile",
121 Version: 201808031000,
123 `CREATE VIEW market_configs_augmented AS
124 SELECT market_configs.*,
125 COALESCE(u.portfolio_profile, g.portfolio_profile)
128 LEFT JOIN bot_settings AS u
129 ON u.user_id = market_configs.user_id,
131 SELECT portfolio_profile
133 WHERE user_id IS NULL
137 "DROP VIEW market_configs_augmented",