X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=db%2Fmigrations.go;h=f0df49c6b9c4339965f783abd24c6111f85e7164;hb=299b6b6d9fb879c06e675ef240f361348629ff6c;hp=286fe17eb334427adcef19bad5aa2189c8825c9d;hpb=2b2fd737b4b6e530e67de743e58b630d69228c57;p=perso%2FImmae%2FProjets%2FCryptomonnaies%2FCryptoportfolio%2FFront.git

diff --git a/db/migrations.go b/db/migrations.go
index 286fe17..f0df49c 100644
--- a/db/migrations.go
+++ b/db/migrations.go
@@ -22,27 +22,71 @@ 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 TABLE reports (
-				id               BIGSERIAL PRIMARY KEY,
-				date             timestamp with time zone NOT NULL,
-				market_config_id bigint NOT NULL,
-				debug            boolean
-			)`,
-			"CREATE INDEX IF NOT EXISTS reports_market_config_id ON reports (market_config_id)",
+			`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",
 		},
-		Down: []string{"DROP TABLE users", "DROP TABLE market_configs", "DROP TABLE report_lines", "DROP TABLE reports"},
 	},
 }