From c22a2c3ebc334fd7a891370e43a841d914893d47 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 14 Apr 2024 15:29:17 +0200 Subject: Merging upstream version 1.0.1. Signed-off-by: Daniel Baumann --- schema/mysql-migrations/v0.10.0.sql | 12 ---- schema/mysql-migrations/v0.9.1.sql | 9 --- schema/mysql-upgrades/0.10.0.sql | 12 ++++ schema/mysql-upgrades/0.9.1.sql | 9 +++ schema/mysql-upgrades/1.0.0.sql | 64 +++++++++++++++++++++ schema/mysql.schema.sql | 108 ++++++++++++++++++++++++++++++++++++ schema/mysql.sql | 96 -------------------------------- schema/pgsql-upgrades/1.0.0.sql | 44 +++++++++++++++ schema/pgsql.schema.sql | 95 +++++++++++++++++++++++++++++++ schema/postgresql.sql | 83 --------------------------- 10 files changed, 332 insertions(+), 200 deletions(-) delete mode 100644 schema/mysql-migrations/v0.10.0.sql delete mode 100644 schema/mysql-migrations/v0.9.1.sql create mode 100644 schema/mysql-upgrades/0.10.0.sql create mode 100644 schema/mysql-upgrades/0.9.1.sql create mode 100644 schema/mysql-upgrades/1.0.0.sql create mode 100644 schema/mysql.schema.sql delete mode 100644 schema/mysql.sql create mode 100644 schema/pgsql-upgrades/1.0.0.sql create mode 100644 schema/pgsql.schema.sql delete mode 100644 schema/postgresql.sql (limited to 'schema') diff --git a/schema/mysql-migrations/v0.10.0.sql b/schema/mysql-migrations/v0.10.0.sql deleted file mode 100644 index 638135b..0000000 --- a/schema/mysql-migrations/v0.10.0.sql +++ /dev/null @@ -1,12 +0,0 @@ -CREATE TABLE template ( - id int(10) unsigned NOT NULL AUTO_INCREMENT, - author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, - name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, - settings longblob NOT NULL, - ctime bigint(20) unsigned NOT NULL, - mtime bigint(20) unsigned NOT NULL, - PRIMARY KEY(id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; - -ALTER TABLE report ADD COLUMN template_id int(10) unsigned NULL DEFAULT NULL AFTER timeframe_id; -ALTER TABLE report ADD CONSTRAINT report_template FOREIGN KEY (template_id) REFERENCES template (id); diff --git a/schema/mysql-migrations/v0.9.1.sql b/schema/mysql-migrations/v0.9.1.sql deleted file mode 100644 index bd71b37..0000000 --- a/schema/mysql-migrations/v0.9.1.sql +++ /dev/null @@ -1,9 +0,0 @@ -UPDATE timeframe SET start = 'first day of January this year midnight' WHERE name = 'Current Year'; -UPDATE timeframe SET start = 'first day of January last year midnight' WHERE name = 'Last Year'; -UPDATE timeframe SET ctime = UNIX_TIMESTAMP() * 1000, mtime = UNIX_TIMESTAMP() * 1000; - -ALTER TABLE timeframe MODIFY COLUMN name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci; -ALTER TABLE timeframe ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=default; - -ALTER TABLE report MODIFY COLUMN name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci; -ALTER TABLE report ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=default; diff --git a/schema/mysql-upgrades/0.10.0.sql b/schema/mysql-upgrades/0.10.0.sql new file mode 100644 index 0000000..638135b --- /dev/null +++ b/schema/mysql-upgrades/0.10.0.sql @@ -0,0 +1,12 @@ +CREATE TABLE template ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, + settings longblob NOT NULL, + ctime bigint(20) unsigned NOT NULL, + mtime bigint(20) unsigned NOT NULL, + PRIMARY KEY(id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + +ALTER TABLE report ADD COLUMN template_id int(10) unsigned NULL DEFAULT NULL AFTER timeframe_id; +ALTER TABLE report ADD CONSTRAINT report_template FOREIGN KEY (template_id) REFERENCES template (id); diff --git a/schema/mysql-upgrades/0.9.1.sql b/schema/mysql-upgrades/0.9.1.sql new file mode 100644 index 0000000..bd71b37 --- /dev/null +++ b/schema/mysql-upgrades/0.9.1.sql @@ -0,0 +1,9 @@ +UPDATE timeframe SET start = 'first day of January this year midnight' WHERE name = 'Current Year'; +UPDATE timeframe SET start = 'first day of January last year midnight' WHERE name = 'Last Year'; +UPDATE timeframe SET ctime = UNIX_TIMESTAMP() * 1000, mtime = UNIX_TIMESTAMP() * 1000; + +ALTER TABLE timeframe MODIFY COLUMN name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci; +ALTER TABLE timeframe ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=default; + +ALTER TABLE report MODIFY COLUMN name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci; +ALTER TABLE report ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=default; 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.schema.sql b/schema/mysql.schema.sql new file mode 100644 index 0000000..bd231bc --- /dev/null +++ b/schema/mysql.schema.sql @@ -0,0 +1,108 @@ +CREATE TABLE template ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, + settings longblob NOT NULL, + ctime bigint(20) unsigned NOT NULL, + mtime bigint(20) unsigned NOT NULL, + PRIMARY KEY(id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + +CREATE TABLE timeframe ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, + title varchar(255) NULL DEFAULT NULL COLLATE utf8mb4_unicode_ci, + start varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + end varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + ctime bigint(20) unsigned NOT NULL, + mtime bigint(20) unsigned NOT NULL, + PRIMARY KEY(id), + UNIQUE KEY timeframe (name) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + +INSERT INTO timeframe (name, title, start, end, ctime, mtime) VALUES + ('4 Hours', null, '-4 hours', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('25 Hours', null, '-25 hours', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('One Week', null, '-1 week', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('One Month', null, '-1 month', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('One Year', null, '-1 year', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Current Day', null, 'midnight', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Last Day', null, 'yesterday midnight', 'yesterday 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Current Week', null, 'monday this week midnight', 'sunday this week 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Last Week', null, 'monday last week midnight', 'sunday last week 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Current Month', null, 'first day of this month midnight', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Last Month', null, 'first day of last month midnight', 'last day of last month 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Current Year', null, 'first day of January this year midnight', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), + ('Last Year', null, 'first day of January last year midnight', 'last day of December last year 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000); + +CREATE TABLE report ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + timeframe_id int(10) unsigned NOT NULL, + template_id int(10) unsigned NULL DEFAULT NULL, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, + ctime bigint(20) unsigned NOT NULL, + mtime bigint(20) unsigned NOT NULL, + PRIMARY KEY(id), + UNIQUE KEY report (name), + CONSTRAINT report_timeframe FOREIGN KEY (timeframe_id) REFERENCES timeframe (id), + CONSTRAINT report_template FOREIGN KEY (template_id) REFERENCES template (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + +CREATE TABLE reportlet ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + report_id int(10) unsigned NOT NULL, + class varchar(255) NOT NULL, + ctime bigint(20) unsigned NOT NULL, + mtime bigint(20) unsigned NOT NULL, + PRIMARY KEY(id), + CONSTRAINT reportlet_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + +CREATE TABLE config ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + reportlet_id int(10) unsigned NOT NULL, + name varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + value text NULL DEFAULT NULL, + ctime bigint(20) unsigned NOT NULL, + mtime bigint(20) unsigned NOT NULL, + PRIMARY KEY(id), + CONSTRAINT config_reportlet FOREIGN KEY (reportlet_id) REFERENCES reportlet (id) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; + +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, + action varchar(255) NOT NULL, + config text NULL DEFAULT NULL, + ctime bigint(20) unsigned NOT NULL, + mtime bigint(20) unsigned NOT NULL, + PRIMARY KEY(id), + 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, +-- username varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, +-- restriction enum('none', 'owner', 'consumer'), +-- ctime bigint(20) unsigned NOT NULL, +-- mtime bigint(20) unsigned NOT NULL, +-- PRIMARY KEY(id), +-- CONSTRAINT share_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE +-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; diff --git a/schema/mysql.sql b/schema/mysql.sql deleted file mode 100644 index 5f70481..0000000 --- a/schema/mysql.sql +++ /dev/null @@ -1,96 +0,0 @@ -CREATE TABLE template ( - id int(10) unsigned NOT NULL AUTO_INCREMENT, - author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, - name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, - settings longblob NOT NULL, - ctime bigint(20) unsigned NOT NULL, - mtime bigint(20) unsigned NOT NULL, - PRIMARY KEY(id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; - -CREATE TABLE timeframe ( - id int(10) unsigned NOT NULL AUTO_INCREMENT, - name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, - title varchar(255) NULL DEFAULT NULL COLLATE utf8mb4_unicode_ci, - start varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, - end varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, - ctime bigint(20) unsigned NOT NULL, - mtime bigint(20) unsigned NOT NULL, - PRIMARY KEY(id), - UNIQUE KEY timeframe (name) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; - -INSERT INTO timeframe (name, title, start, end, ctime, mtime) VALUES - ('4 Hours', null, '-4 hours', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('25 Hours', null, '-25 hours', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('One Week', null, '-1 week', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('One Month', null, '-1 month', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('One Year', null, '-1 year', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Current Day', null, 'midnight', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Last Day', null, 'yesterday midnight', 'yesterday 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Current Week', null, 'monday this week midnight', 'sunday this week 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Last Week', null, 'monday last week midnight', 'sunday last week 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Current Month', null, 'first day of this month midnight', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Last Month', null, 'first day of last month midnight', 'last day of last month 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Current Year', null, 'first day of January this year midnight', 'now', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000), - ('Last Year', null, 'first day of January last year midnight', 'last day of December last year 23:59:59', UNIX_TIMESTAMP() * 1000, UNIX_TIMESTAMP() * 1000); - -CREATE TABLE report ( - id int(10) unsigned NOT NULL AUTO_INCREMENT, - timeframe_id int(10) unsigned NOT NULL, - template_id int(10) unsigned NULL DEFAULT NULL, - author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, - name varchar(128) NOT NULL COLLATE utf8mb4_unicode_ci, - ctime bigint(20) unsigned NOT NULL, - mtime bigint(20) unsigned NOT NULL, - PRIMARY KEY(id), - UNIQUE KEY report (name), - CONSTRAINT report_timeframe FOREIGN KEY (timeframe_id) REFERENCES timeframe (id), - CONSTRAINT report_template FOREIGN KEY (template_id) REFERENCES template (id) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; - -CREATE TABLE reportlet ( - id int(10) unsigned NOT NULL AUTO_INCREMENT, - report_id int(10) unsigned NOT NULL, - class varchar(255) NOT NULL, - ctime bigint(20) unsigned NOT NULL, - mtime bigint(20) unsigned NOT NULL, - PRIMARY KEY(id), - CONSTRAINT reportlet_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; - -CREATE TABLE config ( - id int(10) unsigned NOT NULL AUTO_INCREMENT, - reportlet_id int(10) unsigned NOT NULL, - name varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, - value text NULL DEFAULT NULL, - ctime bigint(20) unsigned NOT NULL, - mtime bigint(20) unsigned NOT NULL, - PRIMARY KEY(id), - CONSTRAINT config_reportlet FOREIGN KEY (reportlet_id) REFERENCES reportlet (id) ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; - -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, - mtime bigint(20) unsigned NOT NULL, - PRIMARY KEY(id), - 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 share ( --- id int(10) unsigned NOT NULL AUTO_INCREMENT, --- report_id int(10) unsigned NOT NULL, --- username varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, --- restriction enum('none', 'owner', 'consumer'), --- ctime bigint(20) unsigned NOT NULL, --- mtime bigint(20) unsigned NOT NULL, --- PRIMARY KEY(id), --- CONSTRAINT share_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE --- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; 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/pgsql.schema.sql b/schema/pgsql.schema.sql new file mode 100644 index 0000000..d20289c --- /dev/null +++ b/schema/pgsql.schema.sql @@ -0,0 +1,95 @@ +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 TABLE template ( + id serial PRIMARY KEY, + author varchar(255) NOT NULL, + name varchar(128) NOT NULL, + settings text NOT NULL, + ctime bigint NOT NULL, + mtime bigint NOT NULL +); + +CREATE TABLE timeframe ( + id serial PRIMARY KEY, + name varchar(128) NOT NULL UNIQUE, + title varchar(255) DEFAULT NULL, + start varchar(255) NOT NULL, + "end" varchar(255) NOT NULL, + ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, + mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000 +); + +INSERT INTO timeframe (name, title, start, "end") VALUES + ('4 Hours', null, '-4 hours', 'now'), + ('25 Hours', null, '-25 hours', 'now'), + ('One Week', null, '-1 week', 'now'), + ('One Month', null, '-1 month', 'now'), + ('One Year', null, '-1 year', 'now'), + ('Current Day', null, 'midnight', 'now'), + ('Last Day', null, 'yesterday midnight', 'yesterday 23:59:59'), + ('Current Week', null, 'monday this week midnight', 'sunday this week 23:59:59'), + ('Last Week', null, 'monday last week midnight', 'sunday last week 23:59:59'), + ('Current Month', null, 'first day of this month midnight', 'now'), + ('Last Month', null, 'first day of last month midnight', 'last day of last month 23:59:59'), + ('Current Year', null, 'first day of January this year midnight', 'now'), + ('Last Year', null, 'first day of January last year midnight', 'last day of December last year 23:59:59'); + +CREATE TABLE report ( + id serial PRIMARY KEY, + timeframe_id int NOT NULL, + template_id int NULL DEFAULT NULL, + author varchar(255) NOT NULL, + name varchar(128) NOT NULL UNIQUE, + ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, + mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000, + CONSTRAINT report_timeframe FOREIGN KEY (timeframe_id) REFERENCES timeframe (id), + CONSTRAINT report_template FOREIGN KEY (template_id) REFERENCES template (id) +); + +CREATE TABLE reportlet ( + id serial PRIMARY KEY, + report_id int NOT NULL, + class varchar(255) NOT NULL, + ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, + mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000, + CONSTRAINT reportlet_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE TABLE config ( + id serial PRIMARY KEY, + reportlet_id int NOT NULL, + name varchar(255) NOT NULL, + value text DEFAULT NULL, + ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, + mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000, + CONSTRAINT config_reportlet FOREIGN KEY (reportlet_id) REFERENCES reportlet (id) ON DELETE CASCADE ON UPDATE CASCADE +); + +CREATE TABLE schedule ( + id serial PRIMARY KEY, + report_id int NOT NULL, + author varchar(255) NOT NULL, + 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'); diff --git a/schema/postgresql.sql b/schema/postgresql.sql deleted file mode 100644 index 329a65f..0000000 --- a/schema/postgresql.sql +++ /dev/null @@ -1,83 +0,0 @@ -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, - name varchar(128) NOT NULL, - settings text NOT NULL, - ctime bigint NOT NULL, - mtime bigint NOT NULL -); - -CREATE TABLE timeframe ( - id serial PRIMARY KEY, - name varchar(128) NOT NULL UNIQUE, - title varchar(255) DEFAULT NULL, - start varchar(255) NOT NULL, - "end" varchar(255) NOT NULL, - ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, - mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000 -); - -INSERT INTO timeframe (name, title, start, "end") VALUES - ('4 Hours', null, '-4 hours', 'now'), - ('25 Hours', null, '-25 hours', 'now'), - ('One Week', null, '-1 week', 'now'), - ('One Month', null, '-1 month', 'now'), - ('One Year', null, '-1 year', 'now'), - ('Current Day', null, 'midnight', 'now'), - ('Last Day', null, 'yesterday midnight', 'yesterday 23:59:59'), - ('Current Week', null, 'monday this week midnight', 'sunday this week 23:59:59'), - ('Last Week', null, 'monday last week midnight', 'sunday last week 23:59:59'), - ('Current Month', null, 'first day of this month midnight', 'now'), - ('Last Month', null, 'first day of last month midnight', 'last day of last month 23:59:59'), - ('Current Year', null, 'first day of January this year midnight', 'now'), - ('Last Year', null, 'first day of January last year midnight', 'last day of December last year 23:59:59'); - -CREATE TABLE report ( - id serial PRIMARY KEY, - timeframe_id int NOT NULL, - template_id int NULL DEFAULT NULL, - author varchar(255) NOT NULL, - name varchar(128) NOT NULL UNIQUE, - ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, - mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000, - CONSTRAINT report_timeframe FOREIGN KEY (timeframe_id) REFERENCES timeframe (id), - CONSTRAINT report_template FOREIGN KEY (template_id) REFERENCES template (id) -); - -CREATE TABLE reportlet ( - id serial PRIMARY KEY, - report_id int NOT NULL, - class varchar(255) NOT NULL, - ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, - mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000, - CONSTRAINT reportlet_report FOREIGN KEY (report_id) REFERENCES report (id) ON DELETE CASCADE ON UPDATE CASCADE -); - -CREATE TABLE config ( - id serial PRIMARY KEY, - reportlet_id int NOT NULL, - name varchar(255) NOT NULL, - value text DEFAULT NULL, - ctime bigint NOT NULL DEFAULT unix_timestamp() * 1000, - mtime bigint NOT NULL DEFAULT unix_timestamp() * 1000, - CONSTRAINT config_reportlet FOREIGN KEY (reportlet_id) REFERENCES reportlet (id) ON DELETE CASCADE ON UPDATE CASCADE -); - -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 -); -- cgit v1.2.3