User roles.

[perso/Immae/Projets/Cryptomonnaies/Cryptoportfolio/Front.git] / db / migrations.go

diff --git a/db/migrations.go b/db/migrations.go
index ce5caf6c2ef6db08d2fc7bb86d69c5c392066a76..e8fc40d12762934caa1222995879a0ab2f973b3c 100644 (file)
--- a/db/migrations.go
+++ b/db/migrations.go
@@ -22,20 +22,82 @@ 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),
-                               config      jsonb,
-                               UNIQUE(user_id, market_name)
+                               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,
+                               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",
                },
-               Down: []string{"DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines"},
        },
 }