mirror of
https://github.com/Icinga/icingaweb2.git
synced 2025-08-16 23:38:11 +02:00
80e4e419e2
This does not seem to require any special privileges since we're using SQL as language and no unusual data types. If this proves false though, feel free to fix this. refs #8826
103 lines
2.4 KiB
SQL
103 lines
2.4 KiB
SQL
/* Icinga Web 2 | (c) 2013-2015 Icinga Development Team | 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(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)
|
|
);
|
|
|
|
CREATE TABLE "icingaweb_user" (
|
|
"name" character varying(64) 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(64) 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)
|
|
);
|