aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/migrations.go39
1 files changed, 36 insertions, 3 deletions
diff --git a/db/migrations.go b/db/migrations.go
index 38ea8aa..ada0296 100644
--- a/db/migrations.go
+++ b/db/migrations.go
@@ -22,10 +22,11 @@ var migrations []Migration = []Migration{
22 `CREATE TABLE market_configs ( 22 `CREATE TABLE market_configs (
23 id BIGSERIAL PRIMARY KEY, 23 id BIGSERIAL PRIMARY KEY,
24 market_name text NOT NULL, 24 market_name text NOT NULL,
25 user_id bigint NOT NULL REFERENCES users(id), 25 user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
26 config jsonb, 26 config jsonb,
27 UNIQUE(user_id, market_name) 27 UNIQUE(user_id, market_name)
28 )`, 28 )`,
29 `CREATE INDEX IF NOT EXISTS market_configs_user_id ON market_configs (user_id)`,
29 `CREATE TABLE reports ( 30 `CREATE TABLE reports (
30 id BIGSERIAL PRIMARY KEY, 31 id BIGSERIAL PRIMARY KEY,
31 date timestamp with time zone NOT NULL, 32 date timestamp with time zone NOT NULL,
@@ -36,13 +37,45 @@ var migrations []Migration = []Migration{
36 `CREATE TABLE report_lines ( 37 `CREATE TABLE report_lines (
37 id BIGSERIAL PRIMARY KEY, 38 id BIGSERIAL PRIMARY KEY,
38 date timestamp with time zone NOT NULL, 39 date timestamp with time zone NOT NULL,
39 report_id bigint NOT NULL REFERENCES reports(id), 40 report_id bigint NOT NULL REFERENCES reports(id) ON DELETE CASCADE,
40 type text, 41 type text,
41 payload jsonb 42 payload jsonb
42 )`, 43 )`,
43 "CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)", 44 "CREATE INDEX IF NOT EXISTS report_lines_report_id ON report_lines (report_id)",
44 "CREATE INDEX IF NOT EXISTS report_lines_type ON report_lines (type)", 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)))`,
45 }, 59 },
46 Down: []string{"DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"}, 60 Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"},
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"},
47 }, 80 },
48} 81}