1 files changed, 44 insertions, 0 deletions
diff --git a/schema/pgsql-upgrades/1.0.0.sql b/schema/pgsql-upgrades/1.0.0.sql
new file mode 100644
index 0000000..0bf3c35
--- /dev/null
+++ b/schema/pgsql-upgrades/1.0.0.sql
@@ -0,0 +1,44 @@
+CREATE OR REPLACE PROCEDURE migrate_schedule_config()
+  LANGUAGE plpgsql
+  AS $$
+  DECLARE
+    row record;
+    frequency_json text;
+  BEGIN
+    FOR row IN (SELECT id, start, frequency, config FROM schedule)
+      LOOP
+        IF NOT CAST(POSITION('frequencyType' IN row.config) AS bool) THEN
+          frequency_json = CONCAT(
+              ',"frequencyType":"\\ipl\\Scheduler\\Cron","frequency":"{',
+              '\"expression\":\"@', row.frequency,
+              '\",\"start\":\"', TO_CHAR(TO_TIMESTAMP(row.start / 1000) AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US UTC'),
+              '\"}"'
+            );
+          UPDATE schedule SET config = OVERLAY(row.config PLACING frequency_json FROM LENGTH(row.config) FOR 0) WHERE id = row.id;
+        END IF;
+      END LOOP;
+  END;
+  $$;
+
+CALL migrate_schedule_config();
+DROP PROCEDURE migrate_schedule_config;
+
+ALTER TABLE schedule
+  DROP COLUMN start,
+  DROP COLUMN frequency;
+
+CREATE TYPE boolenum AS ENUM ('n', 'y');
+
+CREATE TABLE reporting_schema (
+  id serial,
+  version varchar(64) NOT NULL,
+  timestamp bigint NOT NULL,
+  success boolenum DEFAULT NULL,
+  reason text DEFAULT NULL,
+
+  CONSTRAINT pk_reporting_schema PRIMARY KEY (id),
+  CONSTRAINT idx_reporting_schema_version UNIQUE (version)
+);
+
+INSERT INTO reporting_schema (version, timestamp, success, reason)
+  VALUES ('1.0.0', unix_timestamp() * 1000, 'y', NULL);