Add missing index and view

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{
                         `CREATE TABLE market_configs (
                                 id          BIGSERIAL PRIMARY KEY,
                                 market_name text NOT NULL,
-                                user_id     bigint NOT NULL REFERENCES users(id),
+                                user_id     bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                                 config      jsonb,
                                 UNIQUE(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,
@@ -36,13 +37,45 @@ var migrations []Migration = []Migration{
                         `CREATE TABLE report_lines (
                                 id        BIGSERIAL PRIMARY KEY,
                                 date      timestamp with time zone NOT NULL,
-                                report_id bigint NOT NULL REFERENCES reports(id),
+                                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 TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"},
+                Down: []string{"DROP VIEW view_report_lines_by_user", "DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"},
+        },
+        {
+                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"},
         },
 }