diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-14 13:28:59 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-14 13:28:59 +0000 |
commit | 978a1651bac3faf5e91ddba327bf39aeb6a4cb63 (patch) | |
tree | f0d1fee61877df200ccfb1c0af58a39cd551fb46 /schema | |
parent | Initial commit. (diff) | |
download | icingaweb2-module-reporting-978a1651bac3faf5e91ddba327bf39aeb6a4cb63.tar.xz icingaweb2-module-reporting-978a1651bac3faf5e91ddba327bf39aeb6a4cb63.zip |
Adding upstream version 0.10.0.upstream/0.10.0
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | schema/mysql-migrations/v0.10.0.sql | 12 | ||||
-rw-r--r-- | schema/mysql-migrations/v0.9.1.sql | 9 | ||||
-rw-r--r-- | schema/mysql.sql | 96 | ||||
-rw-r--r-- | schema/postgresql.sql | 83 |
4 files changed, 200 insertions, 0 deletions
diff --git a/schema/mysql-migrations/v0.10.0.sql b/schema/mysql-migrations/v0.10.0.sql new file mode 100644 index 0000000..638135b --- /dev/null +++ b/schema/mysql-migrations/v0.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-migrations/v0.9.1.sql b/schema/mysql-migrations/v0.9.1.sql new file mode 100644 index 0000000..bd71b37 --- /dev/null +++ b/schema/mysql-migrations/v0.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.sql b/schema/mysql.sql new file mode 100644 index 0000000..5f70481 --- /dev/null +++ b/schema/mysql.sql @@ -0,0 +1,96 @@ +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/postgresql.sql b/schema/postgresql.sql new file mode 100644 index 0000000..329a65f --- /dev/null +++ b/schema/postgresql.sql @@ -0,0 +1,83 @@ +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 +); |