summaryrefslogtreecommitdiffstats
path: root/schema
diff options
context:
space:
mode:
Diffstat (limited to 'schema')
-rw-r--r--schema/mysql-upgrades/2.0.0beta3-2.0.0rc1.sql26
-rw-r--r--schema/mysql-upgrades/2.11.0.sql33
-rw-r--r--schema/mysql-upgrades/2.5.0.sql5
-rw-r--r--schema/mysql-upgrades/2.9.0.sql11
-rw-r--r--schema/mysql-upgrades/2.9.1.sql2
-rw-r--r--schema/mysql.schema.sql65
-rw-r--r--schema/pgsql-upgrades/2.0.0beta3-2.0.0rc1.sql60
-rw-r--r--schema/pgsql-upgrades/2.11.0.sql10
-rw-r--r--schema/pgsql-upgrades/2.5.0.sql5
-rw-r--r--schema/pgsql-upgrades/2.9.0.sql16
-rw-r--r--schema/pgsql-upgrades/2.9.1.sql2
-rw-r--r--schema/pgsql.schema.sql130
12 files changed, 365 insertions, 0 deletions
diff --git a/schema/mysql-upgrades/2.0.0beta3-2.0.0rc1.sql b/schema/mysql-upgrades/2.0.0beta3-2.0.0rc1.sql
new file mode 100644
index 0000000..e9d4f86
--- /dev/null
+++ b/schema/mysql-upgrades/2.0.0beta3-2.0.0rc1.sql
@@ -0,0 +1,26 @@
+# Icinga Web 2 | (c) 2014 Icinga Development Team | GPLv2+
+
+DROP TABLE `icingaweb_group_membership`;
+DROP TABLE `icingaweb_group`;
+
+CREATE TABLE `icingaweb_group`(
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
+ `parent` int(10) unsigned NULL DEFAULT NULL,
+ `ctime` timestamp NULL DEFAULT NULL,
+ `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `idx_name` (`name`),
+ CONSTRAINT `fk_icingaweb_group_parent_id` FOREIGN KEY (`parent`)
+ REFERENCES `icingaweb_group` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE `icingaweb_group_membership`(
+ `group_id` int(10) unsigned NOT NULL,
+ `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
+ `ctime` timestamp NULL DEFAULT NULL,
+ `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`group_id`,`username`),
+ CONSTRAINT `fk_icingaweb_group_membership_icingaweb_group` FOREIGN KEY (`group_id`)
+ REFERENCES `icingaweb_group` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
diff --git a/schema/mysql-upgrades/2.11.0.sql b/schema/mysql-upgrades/2.11.0.sql
new file mode 100644
index 0000000..99f18cf
--- /dev/null
+++ b/schema/mysql-upgrades/2.11.0.sql
@@ -0,0 +1,33 @@
+ALTER TABLE `icingaweb_group` ROW_FORMAT=DYNAMIC;
+ALTER TABLE `icingaweb_group_membership` ROW_FORMAT=DYNAMIC;
+ALTER TABLE `icingaweb_user` ROW_FORMAT=DYNAMIC;
+ALTER TABLE `icingaweb_user_preference` ROW_FORMAT=DYNAMIC;
+ALTER TABLE `icingaweb_rememberme` ROW_FORMAT=DYNAMIC;
+
+ALTER TABLE `icingaweb_group` CONVERT TO CHARACTER SET utf8mb4;
+ALTER TABLE `icingaweb_group_membership` CONVERT TO CHARACTER SET utf8mb4;
+ALTER TABLE `icingaweb_user` CONVERT TO CHARACTER SET utf8mb4;
+ALTER TABLE `icingaweb_user_preference` CONVERT TO CHARACTER SET utf8mb4;
+
+ALTER TABLE `icingaweb_group`
+ MODIFY COLUMN `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL;
+ALTER TABLE `icingaweb_group_membership`
+ MODIFY COLUMN `username` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL;
+ALTER TABLE `icingaweb_user`
+ MODIFY COLUMN `name` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL;
+
+ALTER TABLE `icingaweb_user_preference`
+ MODIFY COLUMN `username` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
+ MODIFY COLUMN `section` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+ MODIFY COLUMN `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL;
+
+CREATE TABLE icingaweb_schema (
+ id int unsigned NOT NULL AUTO_INCREMENT,
+ version smallint unsigned NOT NULL,
+ timestamp int unsigned NOT NULL,
+
+ PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
+
+INSERT INTO icingaweb_schema (version, timestamp)
+ VALUES (6, UNIX_TIMESTAMP());
diff --git a/schema/mysql-upgrades/2.5.0.sql b/schema/mysql-upgrades/2.5.0.sql
new file mode 100644
index 0000000..08a05c0
--- /dev/null
+++ b/schema/mysql-upgrades/2.5.0.sql
@@ -0,0 +1,5 @@
+# Icinga Web 2 | (c) 2017 Icinga Development Team | GPLv2+
+
+ALTER TABLE `icingaweb_group_membership` MODIFY COLUMN `username` varchar(254) COLLATE utf8_unicode_ci NOT NULL;
+ALTER TABLE `icingaweb_user` MODIFY COLUMN `name` varchar(254) COLLATE utf8_unicode_ci NOT NULL;
+ALTER TABLE `icingaweb_user_preference` MODIFY COLUMN `username` varchar(254) COLLATE utf8_unicode_ci NOT NULL;
diff --git a/schema/mysql-upgrades/2.9.0.sql b/schema/mysql-upgrades/2.9.0.sql
new file mode 100644
index 0000000..7ed7ad6
--- /dev/null
+++ b/schema/mysql-upgrades/2.9.0.sql
@@ -0,0 +1,11 @@
+CREATE TABLE `icingaweb_rememberme`(
+ id int(10) unsigned NOT NULL AUTO_INCREMENT,
+ username varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
+ passphrase varchar(256) NOT NULL,
+ random_iv varchar(24) NOT NULL,
+ http_user_agent text NOT NULL,
+ expires_at timestamp NULL DEFAULT NULL,
+ ctime timestamp NULL DEFAULT NULL,
+ mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
diff --git a/schema/mysql-upgrades/2.9.1.sql b/schema/mysql-upgrades/2.9.1.sql
new file mode 100644
index 0000000..a7af6ce
--- /dev/null
+++ b/schema/mysql-upgrades/2.9.1.sql
@@ -0,0 +1,2 @@
+ALTER TABLE `icingaweb_rememberme`
+ MODIFY random_iv varchar(32) NOT NULL;
diff --git a/schema/mysql.schema.sql b/schema/mysql.schema.sql
new file mode 100644
index 0000000..1600618
--- /dev/null
+++ b/schema/mysql.schema.sql
@@ -0,0 +1,65 @@
+# Icinga Web 2 | (c) 2014 Icinga GmbH | GPLv2+
+
+CREATE TABLE `icingaweb_group`(
+ `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+ `parent` int(10) unsigned NULL DEFAULT NULL,
+ `ctime` timestamp NULL DEFAULT NULL,
+ `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ UNIQUE KEY `idx_name` (`name`),
+ CONSTRAINT `fk_icingaweb_group_parent_id` FOREIGN KEY (`parent`)
+ REFERENCES `icingaweb_group` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
+
+CREATE TABLE `icingaweb_group_membership`(
+ `group_id` int(10) unsigned NOT NULL,
+ `username` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
+ `ctime` timestamp NULL DEFAULT NULL,
+ `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`group_id`,`username`),
+ CONSTRAINT `fk_icingaweb_group_membership_icingaweb_group` FOREIGN KEY (`group_id`)
+ REFERENCES `icingaweb_group` (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
+
+CREATE TABLE `icingaweb_user`(
+ `name` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
+ `active` tinyint(1) NOT NULL,
+ `password_hash` varbinary(255) NOT NULL,
+ `ctime` timestamp NULL DEFAULT NULL,
+ `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
+
+CREATE TABLE `icingaweb_user_preference`(
+ `username` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
+ `section` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+ `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
+ `value` varchar(255) NOT NULL,
+ `ctime` timestamp NULL DEFAULT NULL,
+ `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`username`,`section`,`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
+
+CREATE TABLE `icingaweb_rememberme`(
+ id int(10) unsigned NOT NULL AUTO_INCREMENT,
+ username varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
+ passphrase varchar(256) NOT NULL,
+ random_iv varchar(32) NOT NULL,
+ http_user_agent text NOT NULL,
+ expires_at timestamp NULL DEFAULT NULL,
+ ctime timestamp NULL DEFAULT NULL,
+ mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
+
+CREATE TABLE icingaweb_schema (
+ id int unsigned NOT NULL AUTO_INCREMENT,
+ version smallint unsigned NOT NULL,
+ timestamp int unsigned NOT NULL,
+
+ PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
+
+INSERT INTO icingaweb_schema (version, timestamp)
+ VALUES (6, UNIX_TIMESTAMP());
diff --git a/schema/pgsql-upgrades/2.0.0beta3-2.0.0rc1.sql b/schema/pgsql-upgrades/2.0.0beta3-2.0.0rc1.sql
new file mode 100644
index 0000000..7b5b575
--- /dev/null
+++ b/schema/pgsql-upgrades/2.0.0beta3-2.0.0rc1.sql
@@ -0,0 +1,60 @@
+/* Icinga Web 2 | (c) 2014 Icinga Development Team | GPLv2+ */
+
+DROP TABLE "icingaweb_group_membership";
+DROP TABLE "icingaweb_group";
+
+CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS bigint AS '
+ SELECT EXTRACT(EPOCH FROM $1)::bigint AS result
+' LANGUAGE sql;
+
+CREATE TABLE "icingaweb_group" (
+ "id" serial,
+ "name" character varying(64) NOT NULL,
+ "parent" int NULL DEFAULT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_group"
+ ADD CONSTRAINT pk_icingaweb_group
+ PRIMARY KEY (
+ "id"
+);
+
+CREATE UNIQUE INDEX idx_icingaweb_group
+ ON "icingaweb_group"
+ USING btree (
+ lower((name)::text)
+);
+
+ALTER TABLE ONLY "icingaweb_group"
+ ADD CONSTRAINT fk_icingaweb_group_parent_id
+ FOREIGN KEY (
+ "parent"
+ )
+ REFERENCES "icingaweb_group" (
+ "id"
+);
+
+CREATE TABLE "icingaweb_group_membership" (
+ "group_id" int NOT NULL,
+ "username" character varying(64) NOT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_group_membership"
+ ADD CONSTRAINT pk_icingaweb_group_membership
+ FOREIGN KEY (
+ "group_id"
+ )
+ REFERENCES "icingaweb_group" (
+ "id"
+);
+
+CREATE UNIQUE INDEX idx_icingaweb_group_membership
+ ON "icingaweb_group_membership"
+ USING btree (
+ group_id,
+ lower((username)::text)
+);
diff --git a/schema/pgsql-upgrades/2.11.0.sql b/schema/pgsql-upgrades/2.11.0.sql
new file mode 100644
index 0000000..9e57ce7
--- /dev/null
+++ b/schema/pgsql-upgrades/2.11.0.sql
@@ -0,0 +1,10 @@
+CREATE TABLE "icingaweb_schema" (
+ "id" serial,
+ "version" smallint NOT NULL,
+ "timestamp" int NOT NULL,
+
+ CONSTRAINT pk_icingaweb_schema PRIMARY KEY ("id")
+);
+
+INSERT INTO icingaweb_schema ("version", "timestamp")
+ VALUES (6, extract(epoch from now()));
diff --git a/schema/pgsql-upgrades/2.5.0.sql b/schema/pgsql-upgrades/2.5.0.sql
new file mode 100644
index 0000000..8139281
--- /dev/null
+++ b/schema/pgsql-upgrades/2.5.0.sql
@@ -0,0 +1,5 @@
+/* Icinga Web 2 | (c) 2017 Icinga Development Team | GPLv2+ */
+
+ALTER TABLE "icingaweb_group_membership" ALTER COLUMN "username" TYPE character varying(254);
+ALTER TABLE "icingaweb_user" ALTER COLUMN "name" TYPE character varying(254);
+ALTER TABLE "icingaweb_user_preference" ALTER COLUMN "username" TYPE character varying(254);
diff --git a/schema/pgsql-upgrades/2.9.0.sql b/schema/pgsql-upgrades/2.9.0.sql
new file mode 100644
index 0000000..c017b91
--- /dev/null
+++ b/schema/pgsql-upgrades/2.9.0.sql
@@ -0,0 +1,16 @@
+CREATE TABLE "icingaweb_rememberme" (
+ "id" serial,
+ "username" character varying(254) NOT NULL,
+ "passphrase" character varying(256) NOT NULL,
+ "random_iv" character varying(24) NOT NULL,
+ "http_user_agent" text NOT NULL,
+ "expires_at" timestamp NULL DEFAULT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_rememberme"
+ ADD CONSTRAINT pk_icingaweb_rememberme
+ PRIMARY KEY (
+ "id"
+);
diff --git a/schema/pgsql-upgrades/2.9.1.sql b/schema/pgsql-upgrades/2.9.1.sql
new file mode 100644
index 0000000..02c683f
--- /dev/null
+++ b/schema/pgsql-upgrades/2.9.1.sql
@@ -0,0 +1,2 @@
+ALTER TABLE ONLY "icingaweb_rememberme"
+ ALTER COLUMN random_iv type character varying(32);
diff --git a/schema/pgsql.schema.sql b/schema/pgsql.schema.sql
new file mode 100644
index 0000000..8bf4ca0
--- /dev/null
+++ b/schema/pgsql.schema.sql
@@ -0,0 +1,130 @@
+/* Icinga Web 2 | (c) 2014 Icinga GmbH | GPLv2+ */
+
+CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS bigint AS '
+ SELECT EXTRACT(EPOCH FROM $1)::bigint AS result
+' LANGUAGE sql;
+
+CREATE TABLE "icingaweb_group" (
+ "id" serial,
+ "name" character varying(64) NOT NULL,
+ "parent" int NULL DEFAULT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_group"
+ ADD CONSTRAINT pk_icingaweb_group
+ PRIMARY KEY (
+ "id"
+);
+
+CREATE UNIQUE INDEX idx_icingaweb_group
+ ON "icingaweb_group"
+ USING btree (
+ lower((name)::text)
+);
+
+ALTER TABLE ONLY "icingaweb_group"
+ ADD CONSTRAINT fk_icingaweb_group_parent_id
+ FOREIGN KEY (
+ "parent"
+ )
+ REFERENCES "icingaweb_group" (
+ "id"
+);
+
+CREATE TABLE "icingaweb_group_membership" (
+ "group_id" int NOT NULL,
+ "username" character varying(254) NOT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_group_membership"
+ ADD CONSTRAINT pk_icingaweb_group_membership
+ FOREIGN KEY (
+ "group_id"
+ )
+ REFERENCES "icingaweb_group" (
+ "id"
+);
+
+CREATE UNIQUE INDEX idx_icingaweb_group_membership
+ ON "icingaweb_group_membership"
+ USING btree (
+ group_id,
+ lower((username)::text)
+);
+
+CREATE TABLE "icingaweb_user" (
+ "name" character varying(254) NOT NULL,
+ "active" smallint NOT NULL,
+ "password_hash" bytea NOT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_user"
+ ADD CONSTRAINT pk_icingaweb_user
+ PRIMARY KEY (
+ "name"
+);
+
+CREATE UNIQUE INDEX idx_icingaweb_user
+ ON "icingaweb_user"
+ USING btree (
+ lower((name)::text)
+);
+
+CREATE TABLE "icingaweb_user_preference" (
+ "username" character varying(254) NOT NULL,
+ "name" character varying(64) NOT NULL,
+ "section" character varying(64) NOT NULL,
+ "value" character varying(255) NOT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_user_preference"
+ ADD CONSTRAINT pk_icingaweb_user_preference
+ PRIMARY KEY (
+ "username",
+ "section",
+ "name"
+);
+
+CREATE UNIQUE INDEX idx_icingaweb_user_preference
+ ON "icingaweb_user_preference"
+ USING btree (
+ lower((username)::text),
+ lower((section)::text),
+ lower((name)::text)
+);
+
+CREATE TABLE "icingaweb_rememberme" (
+ "id" serial,
+ "username" character varying(254) NOT NULL,
+ "passphrase" character varying(256) NOT NULL,
+ "random_iv" character varying(32) NOT NULL,
+ "http_user_agent" text NOT NULL,
+ "expires_at" timestamp NULL DEFAULT NULL,
+ "ctime" timestamp NULL DEFAULT NULL,
+ "mtime" timestamp NULL DEFAULT NULL
+);
+
+ALTER TABLE ONLY "icingaweb_rememberme"
+ ADD CONSTRAINT pk_icingaweb_rememberme
+ PRIMARY KEY (
+ "id"
+);
+
+CREATE TABLE "icingaweb_schema" (
+ "id" serial,
+ "version" smallint NOT NULL,
+ "timestamp" int NOT NULL,
+
+ CONSTRAINT pk_icingaweb_schema PRIMARY KEY ("id")
+);
+
+INSERT INTO icingaweb_schema (version, timestamp)
+ VALUES (6, extract(epoch from now()));
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-11 20:38:11 +0000