6 files changed, 138 insertions, 6 deletions
diff --git a/schema/mysql-migrations/v0.10.0.sql b/schema/mysql-upgrades/0.10.0.sql
index 638135b..638135b 100644
--- a/schema/mysql-migrations/v0.10.0.sql
+++ b/schema/mysql-upgrades/0.10.0.sql
diff --git a/schema/mysql-migrations/v0.9.1.sql b/schema/mysql-upgrades/0.9.1.sql
index bd71b37..bd71b37 100644
--- a/schema/mysql-migrations/v0.9.1.sql
+++ b/schema/mysql-upgrades/0.9.1.sql
diff --git a/schema/mysql-upgrades/1.0.0.sql b/schema/mysql-upgrades/1.0.0.sql
new file mode 100644
index 0000000..5b1d2b5
--- /dev/null
+++ b/schema/mysql-upgrades/1.0.0.sql
@@ -0,0 +1,64 @@
+DROP PROCEDURE IF EXISTS migrate_schedule_config;
+DELIMITER //
+CREATE PROCEDURE migrate_schedule_config()
+BEGIN
+  DECLARE session_time_zone text;
+
+  DECLARE schedule_id int;
+  DECLARE schedule_start bigint;
+  DECLARE schedule_frequency enum('minutely', 'hourly', 'daily', 'weekly', 'monthly');
+  DECLARE schedule_config text;
+
+  DECLARE frequency_json text;
+
+  DECLARE done int DEFAULT 0;
+  DECLARE schedule CURSOR FOR SELECT id, start, frequency, config FROM schedule;
+  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
+
+  -- Determine the current session time zone name
+  SELECT IF(@@session.TIME_ZONE = 'SYSTEM', @@system_time_zone, @@session.TIME_ZONE) INTO session_time_zone;
+
+  IF session_time_zone NOT LIKE '+%:%' AND session_time_zone NOT LIKE '-%:%' AND CONVERT_TZ(FROM_UNIXTIME(1699903042), session_time_zone, '+00:00') IS NULL THEN
+    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'required named time zone information are not populated into mysql/mariadb';
+  END IF;
+
+  OPEN schedule;
+  read_loop: LOOP
+    FETCH schedule INTO schedule_id, schedule_start, schedule_frequency, schedule_config;
+    IF done THEN
+      LEAVE read_loop;
+    END IF;
+    IF NOT INSTR(schedule_config, 'frequencyType') THEN
+      SET frequency_json = CONCAT(
+        ',"frequencyType":"\\\\ipl\\\\Scheduler\\\\Cron","frequency":"{',
+        '\\"expression\\":\\"@', schedule_frequency,
+        '\\",\\"start\\":\\"', DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(schedule_start / 1000), session_time_zone, '+00:00'), '%Y-%m-%dT%H:%i:%s.%f UTC'),
+        '\\"}"'
+      );
+      UPDATE schedule SET config = INSERT(schedule_config, LENGTH(schedule_config), 0, frequency_json) WHERE id = schedule_id;
+    END IF;
+  END LOOP;
+  CLOSE schedule;
+END //
+DELIMITER ;
+
+CALL migrate_schedule_config();
+DROP PROCEDURE migrate_schedule_config;
+
+ALTER TABLE schedule
+  DROP COLUMN start,
+  DROP COLUMN frequency;
+
+CREATE TABLE reporting_schema (
+  id int unsigned NOT NULL AUTO_INCREMENT,
+  version varchar(64) NOT NULL,
+  timestamp bigint unsigned NOT NULL,
+  success enum ('n', 'y') DEFAULT NULL,
+  reason text DEFAULT NULL,
+
+  PRIMARY KEY (id),
+  CONSTRAINT idx_reporting_schema_version UNIQUE (version)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
+
+INSERT INTO reporting_schema (version, timestamp, success, reason)
+  VALUES ('1.0.0', UNIX_TIMESTAMP() * 1000, 'y', NULL);
diff --git a/schema/mysql.sql b/schema/mysql.schema.sql
index 5f70481..bd231bc 100644
--- a/schema/mysql.sql
+++ b/schema/mysql.schema.sql
@@ -74,8 +74,6 @@ CREATE TABLE schedule (
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   report_id int(10) unsigned NOT NULL,
   author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci,
-  start bigint(20) unsigned NOT NULL,
-  frequency enum('minutely', 'hourly', 'daily', 'weekly', 'monthly'),
   action varchar(255) NOT NULL,
   config text NULL DEFAULT NULL,
   ctime bigint(20) unsigned NOT NULL,
@@ -84,6 +82,20 @@ CREATE TABLE schedule (
   CONSTRAINT schedule_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
 
+CREATE TABLE reporting_schema (
+  id int unsigned NOT NULL AUTO_INCREMENT,
+  version varchar(64) NOT NULL,
+  timestamp bigint unsigned NOT NULL,
+  success enum ('n', 'y') DEFAULT NULL,
+  reason text DEFAULT NULL,
+
+  PRIMARY KEY (id),
+  CONSTRAINT idx_reporting_schema_version UNIQUE (version)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
+
+INSERT INTO reporting_schema (version, timestamp, success)
+  VALUES ('1.0.0', UNIX_TIMESTAMP() * 1000, 'y');
+
 -- CREATE TABLE share (
 --   id int(10) unsigned NOT NULL AUTO_INCREMENT,
 --   report_id int(10) unsigned NOT NULL,
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);
diff --git a/schema/postgresql.sql b/schema/pgsql.schema.sql
index 329a65f..d20289c 100644
--- a/schema/postgresql.sql
+++ b/schema/pgsql.schema.sql
@@ -1,9 +1,9 @@
+CREATE TYPE boolenum AS ENUM ('n', 'y');
+
 CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone DEFAULT NOW()) RETURNS bigint
   AS 'SELECT EXTRACT(EPOCH FROM $1)::bigint'
   LANGUAGE SQL;
 
-CREATE TYPE frequency AS ENUM ('minutely', 'hourly', 'daily', 'weekly', 'monthly');
-
 CREATE TABLE template (
   id serial PRIMARY KEY,
   author varchar(255) NOT NULL,
@@ -73,11 +73,23 @@ CREATE TABLE schedule (
   id serial PRIMARY KEY,
   report_id int NOT NULL,
   author varchar(255) NOT NULL,
-  start bigint NOT NULL,
-  frequency frequency,
   action varchar(255) NOT NULL,
   config text DEFAULT NULL,
   ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000,
   mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000,
   CONSTRAINT schedule_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE
 );
+
+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)
+  VALUES ('1.0.0', UNIX_TIMESTAMP() * 1000, 'y');