1-- 2-- PostgreSQL schema 3-- ================= 4-- 5-- You should normally not be required to care about schema handling. 6-- Director does all the migrations for you and guides you either in 7-- the frontend or provides everything you need for automated migration 8-- handling. Please find more related information in our documentation. 9 10CREATE TYPE enum_activity_action AS ENUM('create', 'delete', 'modify'); 11CREATE TYPE enum_boolean AS ENUM('y', 'n'); 12CREATE TYPE enum_property_format AS ENUM('string', 'expression', 'json'); 13CREATE TYPE enum_object_type_all AS ENUM('object', 'template', 'apply', 'external_object'); -- TODO: can we check for an invalid 14CREATE TYPE enum_object_type AS ENUM('object', 'template', 'external_object'); 15CREATE TYPE enum_timeperiod_range_type AS ENUM('include', 'exclude'); 16CREATE TYPE enum_merge_behaviour AS ENUM('set', 'add', 'substract', 'override'); 17CREATE TYPE enum_set_merge_behaviour AS ENUM('override', 'extend', 'blacklist'); 18CREATE TYPE enum_command_object_type AS ENUM('object', 'template', 'external_object'); 19CREATE TYPE enum_apply_object_type AS ENUM('object', 'template', 'apply', 'external_object'); 20CREATE TYPE enum_state_name AS ENUM('OK', 'Warning', 'Critical', 'Unknown', 'Up', 'Down'); 21CREATE TYPE enum_type_name AS ENUM('DowntimeStart', 'DowntimeEnd', 'DowntimeRemoved', 'Custom', 'Acknowledgement', 'Problem', 'Recovery', 'FlappingStart', 'FlappingEnd'); 22CREATE TYPE enum_sync_rule_object_type AS ENUM( 23 'host', 24 'service', 25 'command', 26 'user', 27 'hostgroup', 28 'servicegroup', 29 'usergroup', 30 'datalistEntry', 31 'endpoint', 32 'zone', 33 'timePeriod', 34 'serviceSet' 35); 36CREATE TYPE enum_sync_rule_update_policy AS ENUM('merge', 'override', 'ignore'); 37CREATE TYPE enum_sync_property_merge_policy AS ENUM('override', 'merge'); 38CREATE TYPE enum_sync_state AS ENUM( 39 'unknown', 40 'in-sync', 41 'pending-changes', 42 'failing' 43); 44CREATE TYPE enum_host_service AS ENUM('host', 'service'); 45CREATE TYPE enum_owner_type AS ENUM('user', 'usergroup', 'role'); 46 47 48CREATE OR REPLACE FUNCTION unix_timestamp(timestamp with time zone) RETURNS bigint AS ' 49 SELECT EXTRACT(EPOCH FROM $1)::bigint AS result 50' LANGUAGE sql; 51 52 53CREATE TABLE director_activity_log ( 54 id bigserial, 55 object_type character varying(64) NOT NULL, 56 object_name character varying(255) NOT NULL, 57 action_name enum_activity_action NOT NULL, 58 old_properties text DEFAULT NULL, 59 new_properties text DEFAULT NULL, 60 author character varying(64) NOT NULL, 61 change_time timestamp with time zone NOT NULL, 62 checksum bytea NOT NULL UNIQUE CHECK(LENGTH(checksum) = 20), 63 parent_checksum bytea DEFAULT NULL CHECK(parent_checksum IS NULL OR LENGTH(checksum) = 20), 64 PRIMARY KEY (id) 65); 66 67CREATE INDEX activity_log_sort_idx ON director_activity_log (change_time); 68CREATE INDEX activity_log_search_idx ON director_activity_log (object_name); 69CREATE INDEX activity_log_search_idx2 ON director_activity_log (object_type, object_name, change_time); 70CREATE INDEX activity_log_author ON director_activity_log (author); 71COMMENT ON COLUMN director_activity_log.old_properties IS 'Property hash, JSON'; 72COMMENT ON COLUMN director_activity_log.new_properties IS 'Property hash, JSON'; 73 74 75CREATE TABLE director_basket ( 76 uuid bytea CHECK(LENGTH(uuid) = 16) NOT NULL, 77 basket_name VARCHAR(64) NOT NULL, 78 owner_type enum_owner_type NOT NULL, 79 owner_value VARCHAR(255) NOT NULL, 80 objects text NOT NULL, -- json-encoded 81 PRIMARY KEY (uuid) 82); 83 84CREATE UNIQUE INDEX basket_basket_name ON director_basket (basket_name); 85 86 87CREATE TABLE director_basket_content ( 88 checksum bytea CHECK(LENGTH(checksum) = 20) NOT NULL, 89 summary VARCHAR(500) NOT NULL, -- json 90 content text NOT NULL, -- json 91 PRIMARY KEY (checksum) 92); 93 94 95CREATE TABLE director_basket_snapshot ( 96 basket_uuid bytea CHECK(LENGTH(basket_uuid) = 16) NOT NULL, 97 ts_create bigint NOT NULL, 98 content_checksum bytea CHECK(LENGTH(content_checksum) = 20) NOT NULL, 99 PRIMARY KEY (basket_uuid, ts_create), 100 CONSTRAINT basked_snapshot_basket 101 FOREIGN KEY (basket_uuid) 102 REFERENCES director_basket (uuid) 103 ON DELETE CASCADE 104 ON UPDATE RESTRICT, 105 CONSTRAINT basked_snapshot_content 106 FOREIGN KEY (content_checksum) 107 REFERENCES director_basket_content (checksum) 108 ON DELETE RESTRICT 109 ON UPDATE RESTRICT 110); 111 112CREATE INDEX basket_snapshot_sort_idx ON director_basket_snapshot (ts_create); 113 114 115CREATE TABLE director_generated_config ( 116 checksum bytea CHECK(LENGTH(checksum) = 20), 117 director_version character varying(64) DEFAULT NULL, 118 director_db_version integer DEFAULT NULL, 119 duration integer DEFAULT NULL, 120 first_activity_checksum bytea NOT NULL CHECK(LENGTH(first_activity_checksum) = 20), 121 last_activity_checksum bytea NOT NULL CHECK(LENGTH(last_activity_checksum) = 20), 122 PRIMARY KEY (checksum), 123 CONSTRAINT director_generated_config_activity 124 FOREIGN KEY (last_activity_checksum) 125 REFERENCES director_activity_log (checksum) 126 ON DELETE RESTRICT 127 ON UPDATE RESTRICT 128); 129 130CREATE INDEX activity_checksum ON director_generated_config (last_activity_checksum); 131COMMENT ON COLUMN director_generated_config.checksum IS 'SHA1(last_activity_checksum;file_path=checksum;file_path=checksum;...)'; 132COMMENT ON COLUMN director_generated_config.duration IS 'Config generation duration (ms)'; 133 134 135CREATE TABLE director_generated_file ( 136 checksum bytea CHECK(LENGTH(checksum) = 20), 137 content text DEFAULT NULL, 138 cnt_object SMALLINT NOT NULL DEFAULT 0, 139 cnt_template SMALLINT NOT NULL DEFAULT 0, 140 cnt_apply SMALLINT NOT NULL DEFAULT 0, 141 PRIMARY KEY (checksum) 142); 143 144COMMENT ON COLUMN director_generated_file.checksum IS 'SHA1(content)'; 145 146 147CREATE TABLE director_generated_config_file ( 148 config_checksum bytea CHECK(LENGTH(config_checksum) = 20), 149 file_checksum bytea CHECK(LENGTH(file_checksum) = 20), 150 file_path character varying(128) NOT NULL, 151 PRIMARY KEY (config_checksum, file_path), 152 CONSTRAINT director_generated_config_file_config 153 FOREIGN KEY (config_checksum) 154 REFERENCES director_generated_config (checksum) 155 ON DELETE CASCADE 156 ON UPDATE CASCADE, 157 CONSTRAINT director_generated_config_file_file 158 FOREIGN KEY (file_checksum) 159 REFERENCES director_generated_file (checksum) 160 ON DELETE RESTRICT 161 ON UPDATE RESTRICT 162); 163 164CREATE INDEX config ON director_generated_config_file (config_checksum); 165CREATE INDEX checksum ON director_generated_config_file (file_checksum); 166COMMENT ON COLUMN director_generated_config_file.file_path IS 'e.g. zones/nafta/hosts.conf'; 167 168 169CREATE TABLE director_deployment_log ( 170 id bigserial, 171 config_checksum bytea CHECK(LENGTH(config_checksum) = 20), 172 last_activity_checksum bytea CHECK(LENGTH(config_checksum) = 20), 173 peer_identity character varying(64) NOT NULL, 174 start_time timestamp with time zone NOT NULL, 175 end_time timestamp with time zone DEFAULT NULL, 176 abort_time timestamp with time zone DEFAULT NULL, 177 duration_connection integer DEFAULT NULL, 178 duration_dump integer DEFAULT NULL, 179 stage_name CHARACTER VARYING(96), 180 stage_collected enum_boolean DEFAULT NULL, 181 connection_succeeded enum_boolean DEFAULT NULL, 182 dump_succeeded enum_boolean DEFAULT NULL, 183 startup_succeeded enum_boolean DEFAULT NULL, 184 username character varying(64) DEFAULT NULL, 185 startup_log text DEFAULT NULL, 186 PRIMARY KEY (id), 187 CONSTRAINT config_checksum 188 FOREIGN KEY (config_checksum) 189 REFERENCES director_generated_config (checksum) 190 ON DELETE SET NULL 191 ON UPDATE RESTRICT 192); 193 194COMMENT ON COLUMN director_deployment_log.duration_connection IS 'The time it took to connect to an Icinga node (ms)'; 195COMMENT ON COLUMN director_deployment_log.duration_dump IS 'Time spent dumping the config (ms)'; 196COMMENT ON COLUMN director_deployment_log.username IS 'The user that triggered this deployment'; 197 198 199CREATE TABLE director_datalist ( 200 id serial, 201 list_name character varying(255) NOT NULL, 202 owner character varying(255) NOT NULL, 203 PRIMARY KEY (id) 204); 205 206CREATE UNIQUE INDEX datalist_list_name ON director_datalist (list_name); 207 208 209CREATE TABLE director_datalist_entry ( 210 list_id integer NOT NULL, 211 entry_name character varying(255) NOT NULL, 212 entry_value text DEFAULT NULL, 213 format enum_property_format, 214 allowed_roles character varying(255) DEFAULT NULL, 215 PRIMARY KEY (list_id, entry_name), 216 CONSTRAINT director_datalist_entry_datalist 217 FOREIGN KEY (list_id) 218 REFERENCES director_datalist (id) 219 ON DELETE CASCADE 220 ON UPDATE CASCADE 221); 222 223CREATE INDEX datalist_entry_datalist ON director_datalist_entry (list_id); 224 225 226CREATE TABLE director_datafield ( 227 id serial, 228 varname character varying(64) NOT NULL, 229 caption character varying(255) NOT NULL, 230 description text DEFAULT NULL, 231 datatype character varying(255) NOT NULL, 232-- datatype_param? multiple ones? 233 format enum_property_format, 234 PRIMARY KEY (id) 235); 236 237CREATE INDEX search_idx ON director_datafield (varname); 238 239 240CREATE TABLE director_datafield_setting ( 241 datafield_id integer NOT NULL, 242 setting_name character varying(64) NOT NULL, 243 setting_value text NOT NULL, 244 PRIMARY KEY (datafield_id, setting_name), 245 CONSTRAINT datafield_id_settings 246 FOREIGN KEY (datafield_id) 247 REFERENCES director_datafield (id) 248 ON DELETE CASCADE 249 ON UPDATE CASCADE 250); 251 252CREATE INDEX director_datafield_datafield ON director_datafield_setting (datafield_id); 253 254 255CREATE TABLE director_schema_migration ( 256 schema_version SMALLINT NOT NULL, 257 migration_time TIMESTAMP WITH TIME ZONE NOT NULL, 258 PRIMARY KEY(schema_version) 259); 260 261 262CREATE TABLE director_setting ( 263 setting_name character varying(64) NOT NULL, 264 setting_value character varying(255) NOT NULL, 265 PRIMARY KEY(setting_name) 266); 267 268 269CREATE TABLE icinga_zone ( 270 id serial, 271 parent_id integer DEFAULT NULL, 272 object_name character varying(255) NOT NULL UNIQUE, 273 object_type enum_object_type_all NOT NULL, 274 disabled enum_boolean NOT NULL DEFAULT 'n', 275 is_global enum_boolean NOT NULL DEFAULT 'n', 276 PRIMARY KEY (id), 277 CONSTRAINT icinga_zone_parent_zone 278 FOREIGN KEY (parent_id) 279 REFERENCES icinga_zone (id) 280 ON DELETE RESTRICT 281 ON UPDATE CASCADE 282); 283 284CREATE INDEX zone_parent ON icinga_zone (parent_id); 285 286 287CREATE TABLE icinga_zone_inheritance ( 288 zone_id integer NOT NULL, 289 parent_zone_id integer NOT NULL, 290 weight integer DEFAULT NULL, 291 PRIMARY KEY (zone_id, parent_zone_id), 292 CONSTRAINT icinga_zone_inheritance_zone 293 FOREIGN KEY (zone_id) 294 REFERENCES icinga_zone (id) 295 ON DELETE CASCADE 296 ON UPDATE CASCADE, 297 CONSTRAINT icinga_zone_inheritance_parent_zone 298 FOREIGN KEY (parent_zone_id) 299 REFERENCES icinga_zone (id) 300 ON DELETE RESTRICT 301 ON UPDATE CASCADE 302); 303 304CREATE UNIQUE INDEX zone_inheritance_unique_order ON icinga_zone_inheritance (zone_id, weight); 305CREATE INDEX zone_inheritance_zone ON icinga_zone_inheritance (zone_id); 306CREATE INDEX zone_inheritance_zone_parent ON icinga_zone_inheritance (parent_zone_id); 307 308 309CREATE TABLE icinga_timeperiod ( 310 id serial, 311 object_name character varying(255) NOT NULL, 312 display_name character varying(255) DEFAULT NULL, 313 update_method character varying(64) DEFAULT NULL, 314 zone_id integer DEFAULT NULL, 315 object_type enum_object_type_all NOT NULL, 316 disabled enum_boolean NOT NULL DEFAULT 'n', 317 prefer_includes enum_boolean DEFAULT NULL, 318 PRIMARY KEY (id), 319 CONSTRAINT icinga_timeperiod_zone 320 FOREIGN KEY (zone_id) 321 REFERENCES icinga_zone (id) 322 ON DELETE RESTRICT 323 ON UPDATE CASCADE 324); 325 326CREATE UNIQUE INDEX timeperiod_object_name ON icinga_timeperiod (object_name, zone_id); 327CREATE INDEX timeperiod_zone ON icinga_timeperiod (zone_id); 328COMMENT ON COLUMN icinga_timeperiod.update_method IS 'Usually LegacyTimePeriod'; 329 330 331CREATE TABLE icinga_timeperiod_inheritance ( 332 timeperiod_id integer NOT NULL, 333 parent_timeperiod_id integer NOT NULL, 334 weight integer DEFAULT NULL, 335 PRIMARY KEY (timeperiod_id, parent_timeperiod_id), 336 CONSTRAINT icinga_timeperiod_inheritance_timeperiod 337 FOREIGN KEY (timeperiod_id) 338 REFERENCES icinga_timeperiod (id) 339 ON DELETE CASCADE 340 ON UPDATE CASCADE, 341 CONSTRAINT icinga_timeperiod_inheritance_parent_timeperiod 342 FOREIGN KEY (parent_timeperiod_id) 343 REFERENCES icinga_timeperiod (id) 344 ON DELETE RESTRICT 345 ON UPDATE CASCADE 346); 347 348CREATE UNIQUE INDEX timeperiod_inheritance_unique_order ON icinga_timeperiod_inheritance (timeperiod_id, weight); 349CREATE INDEX timeperiod_inheritance_timeperiod ON icinga_timeperiod_inheritance (timeperiod_id); 350CREATE INDEX timeperiod_inheritance_timeperiod_parent ON icinga_timeperiod_inheritance (parent_timeperiod_id); 351 352 353CREATE TABLE icinga_timeperiod_range ( 354 timeperiod_id serial, 355 range_key character varying(255) NOT NULL, 356 range_value character varying(255) NOT NULL, 357 range_type enum_timeperiod_range_type NOT NULL DEFAULT 'include', 358 merge_behaviour enum_merge_behaviour NOT NULL DEFAULT 'set', 359 PRIMARY KEY (timeperiod_id, range_type, range_key), 360 CONSTRAINT icinga_timeperiod_range_timeperiod 361 FOREIGN KEY (timeperiod_id) 362 REFERENCES icinga_timeperiod (id) 363 ON DELETE CASCADE 364 ON UPDATE CASCADE 365); 366 367CREATE INDEX timeperiod_range_timeperiod ON icinga_timeperiod_range (timeperiod_id); 368COMMENT ON COLUMN icinga_timeperiod_range.range_key IS 'monday, ...'; 369COMMENT ON COLUMN icinga_timeperiod_range.range_value IS '00:00-24:00, ...'; 370COMMENT ON COLUMN icinga_timeperiod_range.range_type IS 'include -> ranges {}, exclude ranges_ignore {} - not yet'; 371COMMENT ON COLUMN icinga_timeperiod_range.merge_behaviour IS 'set -> = {}, add -> += {}, substract -> -= {}'; 372 373 374CREATE TABLE director_job ( 375 id serial, 376 job_name character varying(64) NOT NULL, 377 job_class character varying(72) NOT NULL, 378 disabled enum_boolean NOT NULL DEFAULT 'n', 379 run_interval integer NOT NULL, -- seconds 380 timeperiod_id integer DEFAULT NULL, 381 last_attempt_succeeded enum_boolean DEFAULT NULL, 382 ts_last_attempt timestamp with time zone DEFAULT NULL, 383 ts_last_error timestamp with time zone DEFAULT NULL, 384 last_error_message text NULL DEFAULT NULL, 385 CONSTRAINT director_job_period 386 FOREIGN KEY (timeperiod_id) 387 REFERENCES icinga_timeperiod (id) 388 ON DELETE RESTRICT 389 ON UPDATE CASCADE, 390 PRIMARY KEY (id) 391); 392 393CREATE UNIQUE INDEX director_job_unique_job_name ON director_job (job_name); 394 395 396CREATE TABLE director_job_setting ( 397 job_id integer NOT NULL, 398 setting_name character varying(64) NOT NULL, 399 setting_value text DEFAULT NULL, 400 PRIMARY KEY (job_id, setting_name), 401 CONSTRAINT director_job_setting_job 402 FOREIGN KEY (job_id) 403 REFERENCES director_job (id) 404 ON DELETE CASCADE 405 ON UPDATE CASCADE 406); 407 408CREATE INDEX director_job_setting_job ON director_job_setting (job_id); 409 410 411CREATE TABLE icinga_command ( 412 id serial, 413 object_name character varying(255) NOT NULL, 414 object_type enum_object_type_all NOT NULL, 415 disabled enum_boolean NOT NULL DEFAULT 'n', 416 methods_execute character varying(64) DEFAULT NULL, 417 command text DEFAULT NULL, 418-- env text DEFAULT NULL, 419 timeout smallint DEFAULT NULL, 420 zone_id integer DEFAULT NULL, 421 PRIMARY KEY (id), 422 CONSTRAINT icinga_command_zone 423 FOREIGN KEY (zone_id) 424 REFERENCES icinga_zone (id) 425 ON DELETE RESTRICT 426 ON UPDATE CASCADE 427); 428 429CREATE UNIQUE INDEX command_object_name ON icinga_command (object_name); 430CREATE INDEX command_zone ON icinga_command (zone_id); 431COMMENT ON COLUMN icinga_command.object_type IS 'external_object is an attempt to work with existing commands'; 432 433 434CREATE TABLE icinga_command_inheritance ( 435 command_id integer NOT NULL, 436 parent_command_id integer NOT NULL, 437 weight integer DEFAULT NULL, 438 PRIMARY KEY (command_id, parent_command_id), 439 CONSTRAINT icinga_command_inheritance_command 440 FOREIGN KEY (command_id) 441 REFERENCES icinga_command (id) 442 ON DELETE CASCADE 443 ON UPDATE CASCADE, 444 CONSTRAINT icinga_command_inheritance_parent_command 445 FOREIGN KEY (parent_command_id) 446 REFERENCES icinga_command (id) 447 ON DELETE RESTRICT 448 ON UPDATE CASCADE 449); 450 451CREATE UNIQUE INDEX command_inheritance_unique_order ON icinga_command_inheritance (command_id, weight); 452CREATE INDEX command_inheritance_command ON icinga_command_inheritance (command_id); 453CREATE INDEX command_inheritance_command_parent ON icinga_command_inheritance (parent_command_id); 454 455 456CREATE TABLE icinga_command_argument ( 457 id serial, 458 command_id integer NOT NULL, 459 argument_name character varying(64) NOT NULL, 460 argument_value text DEFAULT NULL, 461 argument_format enum_property_format DEFAULT NULL, 462 key_string character varying(64) DEFAULT NULL, 463 description text DEFAULT NULL, 464 skip_key enum_boolean DEFAULT NULL, 465 set_if character varying(255) DEFAULT NULL, -- (string expression, must resolve to a numeric value) 466 set_if_format enum_property_format DEFAULT NULL, 467 sort_order smallint DEFAULT NULL, -- -> order 468 repeat_key enum_boolean DEFAULT NULL, 469 required enum_boolean DEFAULT NULL, 470 PRIMARY KEY (id), 471 CONSTRAINT icinga_command_argument_command 472 FOREIGN KEY (command_id) 473 REFERENCES icinga_command (id) 474 ON DELETE CASCADE 475 ON UPDATE CASCADE 476); 477 478CREATE UNIQUE INDEX command_argument_unique_idx ON icinga_command_argument (command_id, argument_name); 479CREATE INDEX command_argument_sort_idx ON icinga_command_argument (command_id, sort_order); 480CREATE INDEX command_argument_command ON icinga_command_argument (command_id); 481COMMENT ON COLUMN icinga_command_argument.argument_name IS '-x, --host'; 482COMMENT ON COLUMN icinga_command_argument.key_string IS 'Overrides name'; 483COMMENT ON COLUMN icinga_command_argument.repeat_key IS 'Useful with array values'; 484 485 486CREATE TABLE icinga_command_field ( 487 command_id integer NOT NULL, 488 datafield_id integer NOT NULL, 489 is_required enum_boolean NOT NULL, 490 var_filter TEXT DEFAULT NULL, 491 PRIMARY KEY (command_id, datafield_id), 492 CONSTRAINT icinga_command_field_command 493 FOREIGN KEY (command_id) 494 REFERENCES icinga_command (id) 495 ON DELETE CASCADE 496 ON UPDATE CASCADE, 497 CONSTRAINT icinga_command_field_datafield 498 FOREIGN KEY (datafield_id) 499 REFERENCES director_datafield (id) 500 ON DELETE CASCADE 501 ON UPDATE CASCADE 502); 503 504 505CREATE TABLE icinga_command_var ( 506 command_id integer NOT NULL, 507 checksum bytea DEFAULT NULL UNIQUE CHECK(LENGTH(checksum) = 20), 508 varname character varying(255) NOT NULL, 509 varvalue text DEFAULT NULL, 510 format enum_property_format NOT NULL DEFAULT 'string', 511 PRIMARY KEY (command_id, varname), 512 CONSTRAINT icinga_command_var_command 513 FOREIGN KEY (command_id) 514 REFERENCES icinga_command (id) 515 ON DELETE CASCADE 516 ON UPDATE CASCADE 517); 518 519CREATE INDEX command_var_command ON icinga_command_var (command_id); 520CREATE INDEX command_var_search_idx ON icinga_command_var (varname); 521CREATE INDEX command_var_checksum ON icinga_command_var (checksum); 522 523 524CREATE TABLE icinga_apiuser ( 525 id BIGSERIAL, 526 object_name CHARACTER VARYING(255) NOT NULL, 527 object_type enum_object_type_all NOT NULL, 528 disabled enum_boolean NOT NULL DEFAULT 'n', 529 password CHARACTER VARYING(255) DEFAULT NULL, 530 client_dn CHARACTER VARYING(64) DEFAULT NULL, 531 permissions TEXT DEFAULT NULL, 532 PRIMARY KEY (id) 533); 534 535COMMENT ON COLUMN icinga_apiuser.permissions IS 'JSON-encoded permissions'; 536 537 538CREATE TABLE icinga_endpoint ( 539 id serial, 540 zone_id integer DEFAULT NULL, 541 object_name character varying(255) NOT NULL, 542 object_type enum_object_type_all NOT NULL, 543 disabled enum_boolean NOT NULL DEFAULT 'n', 544 host character varying(255) DEFAULT NULL, 545 port smallint DEFAULT NULL, 546 log_duration character varying(32) DEFAULT NULL, 547 apiuser_id INTEGER DEFAULT NULL, 548 PRIMARY KEY (id), 549 CONSTRAINT icinga_endpoint_zone 550 FOREIGN KEY (zone_id) 551 REFERENCES icinga_zone (id) 552 ON DELETE RESTRICT 553 ON UPDATE CASCADE, 554 CONSTRAINT icinga_apiuser 555 FOREIGN KEY (apiuser_id) 556 REFERENCES icinga_apiuser (id) 557 ON DELETE RESTRICT 558 ON UPDATE CASCADE 559); 560 561CREATE UNIQUE INDEX endpoint_object_name ON icinga_endpoint (object_name); 562CREATE INDEX endpoint_zone ON icinga_endpoint (zone_id); 563COMMENT ON COLUMN icinga_endpoint.host IS 'IP address / hostname of remote node'; 564COMMENT ON COLUMN icinga_endpoint.port IS '5665 if not set'; 565COMMENT ON COLUMN icinga_endpoint.log_duration IS '1d if not set'; 566 567 568CREATE TABLE icinga_endpoint_inheritance ( 569 endpoint_id integer NOT NULL, 570 parent_endpoint_id integer NOT NULL, 571 weight integer DEFAULT NULL, 572 PRIMARY KEY (endpoint_id, parent_endpoint_id), 573 CONSTRAINT icinga_endpoint_inheritance_endpoint 574 FOREIGN KEY (endpoint_id) 575 REFERENCES icinga_endpoint (id) 576 ON DELETE CASCADE 577 ON UPDATE CASCADE, 578 CONSTRAINT icinga_endpoint_inheritance_parent_endpoint 579 FOREIGN KEY (parent_endpoint_id) 580 REFERENCES icinga_endpoint (id) 581 ON DELETE RESTRICT 582 ON UPDATE CASCADE 583); 584 585CREATE UNIQUE INDEX endpoint_inheritance_unique_order ON icinga_endpoint_inheritance (endpoint_id, weight); 586CREATE INDEX endpoint_inheritance_endpoint ON icinga_endpoint_inheritance (endpoint_id); 587CREATE INDEX endpoint_inheritance_endpoint_parent ON icinga_endpoint_inheritance (parent_endpoint_id); 588 589 590CREATE TABLE icinga_host_template_choice ( 591 id serial, 592 object_name character varying(64) NOT NULL, 593 description text DEFAULT NULL, 594 min_required smallint NOT NULL DEFAULT 0, 595 max_allowed smallint NOT NULL DEFAULT 1, 596 required_template_id integer DEFAULT NULL, 597 allowed_roles character varying(255) DEFAULT NULL, 598 PRIMARY KEY (id) 599); 600 601CREATE UNIQUE INDEX host_template_choice_object_name ON icinga_host_template_choice (object_name); 602CREATE INDEX host_template_choice_required_template ON icinga_host_template_choice (required_template_id); 603 604CREATE TABLE icinga_host ( 605 id serial, 606 object_name character varying(255) NOT NULL, 607 object_type enum_object_type_all NOT NULL, 608 disabled enum_boolean NOT NULL DEFAULT 'n', 609 display_name CHARACTER VARYING(255) DEFAULT NULL, 610 address character varying(64) DEFAULT NULL, 611 address6 character varying(45) DEFAULT NULL, 612 check_command_id integer DEFAULT NULL, 613 max_check_attempts integer DEFAULT NULL, 614 check_period_id integer DEFAULT NULL, 615 check_interval character varying(8) DEFAULT NULL, 616 retry_interval character varying(8) DEFAULT NULL, 617 check_timeout smallint DEFAULT NULL, 618 enable_notifications enum_boolean DEFAULT NULL, 619 enable_active_checks enum_boolean DEFAULT NULL, 620 enable_passive_checks enum_boolean DEFAULT NULL, 621 enable_event_handler enum_boolean DEFAULT NULL, 622 enable_flapping enum_boolean DEFAULT NULL, 623 enable_perfdata enum_boolean DEFAULT NULL, 624 event_command_id integer DEFAULT NULL, 625 flapping_threshold_high smallint default null, 626 flapping_threshold_low smallint default null, 627 volatile enum_boolean DEFAULT NULL, 628 zone_id integer DEFAULT NULL, 629 command_endpoint_id integer DEFAULT NULL, 630 notes text DEFAULT NULL, 631 notes_url character varying(255) DEFAULT NULL, 632 action_url character varying(255) DEFAULT NULL, 633 icon_image character varying(255) DEFAULT NULL, 634 icon_image_alt character varying(255) DEFAULT NULL, 635 has_agent enum_boolean DEFAULT NULL, 636 master_should_connect enum_boolean DEFAULT NULL, 637 accept_config enum_boolean DEFAULT NULL, 638 api_key character varying(40) DEFAULT NULL, 639 template_choice_id int DEFAULT NULL, 640 PRIMARY KEY (id), 641 CONSTRAINT icinga_host_zone 642 FOREIGN KEY (zone_id) 643 REFERENCES icinga_zone (id) 644 ON DELETE RESTRICT 645 ON UPDATE CASCADE, 646 CONSTRAINT icinga_host_check_period 647 FOREIGN KEY (check_period_id) 648 REFERENCES icinga_timeperiod (id) 649 ON DELETE RESTRICT 650 ON UPDATE CASCADE, 651 CONSTRAINT icinga_host_check_command 652 FOREIGN KEY (check_command_id) 653 REFERENCES icinga_command (id) 654 ON DELETE RESTRICT 655 ON UPDATE CASCADE, 656 CONSTRAINT icinga_host_event_command 657 FOREIGN KEY (event_command_id) 658 REFERENCES icinga_command (id) 659 ON DELETE RESTRICT 660 ON UPDATE CASCADE, 661 CONSTRAINT icinga_host_command_endpoint 662 FOREIGN KEY (command_endpoint_id) 663 REFERENCES icinga_endpoint (id) 664 ON DELETE RESTRICT 665 ON UPDATE CASCADE, 666 CONSTRAINT icinga_host_template_choice 667 FOREIGN KEY (template_choice_id) 668 REFERENCES icinga_host_template_choice (id) 669 ON DELETE SET NULL 670 ON UPDATE CASCADE 671); 672 673 674CREATE UNIQUE INDEX object_name_host ON icinga_host (object_name, zone_id); 675CREATE UNIQUE INDEX host_api_key ON icinga_host (api_key); 676CREATE INDEX host_zone ON icinga_host (zone_id); 677CREATE INDEX host_timeperiod ON icinga_host (check_period_id); 678CREATE INDEX host_check_command ON icinga_host (check_command_id); 679CREATE INDEX host_event_command ON icinga_host (event_command_id); 680CREATE INDEX host_command_endpoint ON icinga_host (command_endpoint_id); 681CREATE INDEX host_template_choice ON icinga_host (template_choice_id); 682 683 684CREATE TABLE icinga_host_inheritance ( 685 host_id integer NOT NULL, 686 parent_host_id integer NOT NULL, 687 weight integer DEFAULT NULL, 688 PRIMARY KEY (host_id, parent_host_id), 689 CONSTRAINT icinga_host_inheritance_host 690 FOREIGN KEY (host_id) 691 REFERENCES icinga_host (id) 692 ON DELETE CASCADE 693 ON UPDATE CASCADE, 694 CONSTRAINT icinga_host_inheritance_parent_host 695 FOREIGN KEY (parent_host_id) 696 REFERENCES icinga_host (id) 697 ON DELETE RESTRICT 698 ON UPDATE CASCADE 699); 700 701CREATE UNIQUE INDEX host_inheritance_unique_order ON icinga_host_inheritance (host_id, weight); 702CREATE INDEX host_inheritance_host ON icinga_host_inheritance (host_id); 703CREATE INDEX host_inheritance_host_parent ON icinga_host_inheritance (parent_host_id); 704 705 706CREATE TABLE icinga_host_field ( 707 host_id integer NOT NULL, 708 datafield_id integer NOT NULL, 709 is_required enum_boolean NOT NULL, 710 var_filter TEXT DEFAULT NULL, 711 PRIMARY KEY (host_id, datafield_id), 712 CONSTRAINT icinga_host_field_host 713 FOREIGN KEY (host_id) 714 REFERENCES icinga_host (id) 715 ON DELETE CASCADE 716 ON UPDATE CASCADE, 717 CONSTRAINT icinga_host_field_datafield 718 FOREIGN KEY (datafield_id) 719 REFERENCES director_datafield (id) 720 ON DELETE CASCADE 721 ON UPDATE CASCADE 722); 723 724CREATE UNIQUE INDEX host_field_key ON icinga_host_field (host_id, datafield_id); 725CREATE INDEX host_field_host ON icinga_host_field (host_id); 726CREATE INDEX host_field_datafield ON icinga_host_field (datafield_id); 727COMMENT ON COLUMN icinga_host_field.host_id IS 'Makes only sense for templates'; 728 729 730CREATE TABLE icinga_host_var ( 731 host_id integer NOT NULL, 732 checksum bytea DEFAULT NULL UNIQUE CHECK(LENGTH(checksum) = 20), 733 varname character varying(255) NOT NULL, 734 varvalue text DEFAULT NULL, 735 format enum_property_format, -- immer string vorerst 736 PRIMARY KEY (host_id, varname), 737 CONSTRAINT icinga_host_var_host 738 FOREIGN KEY (host_id) 739 REFERENCES icinga_host (id) 740 ON DELETE CASCADE 741 ON UPDATE CASCADE 742); 743 744CREATE INDEX host_var_search_idx ON icinga_host_var (varname); 745CREATE INDEX host_var_host ON icinga_host_var (host_id); 746CREATE INDEX host_var_checksum ON icinga_host_var (checksum); 747 748 749ALTER TABLE icinga_host_template_choice 750 ADD CONSTRAINT host_template_choice_required_template 751 FOREIGN KEY (required_template_id) 752 REFERENCES icinga_host (id) 753 ON DELETE RESTRICT 754 ON UPDATE CASCADE; 755 756 757CREATE TABLE icinga_service_set ( 758 id serial, 759 host_id integer DEFAULT NULL, 760 object_name character varying(128) NOT NULL, 761 object_type enum_object_type_all NOT NULL, 762 description text DEFAULT NULL, 763 assign_filter text DEFAULT NULL, 764 PRIMARY KEY (id), 765 CONSTRAINT icinga_service_set_host 766 FOREIGN KEY (host_id) 767 REFERENCES icinga_host (id) 768 ON DELETE CASCADE 769 ON UPDATE CASCADE 770); 771 772CREATE UNIQUE INDEX service_set_name ON icinga_service_set (object_name, host_id); 773CREATE INDEX service_set_host ON icinga_service_set (host_id); 774 775 776CREATE TABLE icinga_service_template_choice ( 777 id serial, 778 object_name character varying(64) NOT NULL, 779 description text DEFAULT NULL, 780 min_required smallint NOT NULL DEFAULT 0, 781 max_allowed smallint NOT NULL DEFAULT 1, 782 required_template_id integer DEFAULT NULL, 783 allowed_roles character varying(255) DEFAULT NULL, 784 PRIMARY KEY (id) 785); 786 787CREATE UNIQUE INDEX service_template_choice_object_name ON icinga_service_template_choice (object_name); 788CREATE INDEX service_template_choice_required_template ON icinga_service_template_choice (required_template_id); 789 790 791CREATE TABLE icinga_service ( 792 id serial, 793 object_name character varying(255) NOT NULL, 794 object_type enum_object_type_all NOT NULL, 795 disabled enum_boolean DEFAULT 'n', 796 display_name character varying(255) DEFAULT NULL, 797 host_id INTEGER DEFAULT NULL, 798 service_set_id integer DEFAULT NULL, 799 check_command_id integer DEFAULT NULL, 800 max_check_attempts integer DEFAULT NULL, 801 check_period_id integer DEFAULT NULL, 802 check_interval character varying(8) DEFAULT NULL, 803 retry_interval character varying(8) DEFAULT NULL, 804 check_timeout smallint DEFAULT NULL, 805 enable_notifications enum_boolean DEFAULT NULL, 806 enable_active_checks enum_boolean DEFAULT NULL, 807 enable_passive_checks enum_boolean DEFAULT NULL, 808 enable_event_handler enum_boolean DEFAULT NULL, 809 enable_flapping enum_boolean DEFAULT NULL, 810 enable_perfdata enum_boolean DEFAULT NULL, 811 event_command_id integer DEFAULT NULL, 812 flapping_threshold_high smallint DEFAULT NULL, 813 flapping_threshold_low smallint DEFAULT NULL, 814 volatile enum_boolean DEFAULT NULL, 815 zone_id integer DEFAULT NULL, 816 command_endpoint_id integer DEFAULT NULL, 817 notes text DEFAULT NULL, 818 notes_url character varying(255) DEFAULT NULL, 819 action_url character varying(255) DEFAULT NULL, 820 icon_image character varying(255) DEFAULT NULL, 821 icon_image_alt character varying(255) DEFAULT NULL, 822 use_agent enum_boolean DEFAULT NULL, 823 apply_for character varying(255) DEFAULT NULL, 824 use_var_overrides enum_boolean DEFAULT NULL, 825 assign_filter text DEFAULT NULL, 826 template_choice_id int DEFAULT NULL, 827 PRIMARY KEY (id), 828-- UNIQUE INDEX object_name (object_name, zone_id), 829 CONSTRAINT icinga_service_host 830 FOREIGN KEY (host_id) 831 REFERENCES icinga_host (id) 832 ON DELETE CASCADE 833 ON UPDATE CASCADE, 834 CONSTRAINT icinga_service_zone 835 FOREIGN KEY (zone_id) 836 REFERENCES icinga_zone (id) 837 ON DELETE RESTRICT 838 ON UPDATE CASCADE, 839 CONSTRAINT icinga_service_check_period 840 FOREIGN KEY (check_period_id) 841 REFERENCES icinga_timeperiod (id) 842 ON DELETE RESTRICT 843 ON UPDATE CASCADE, 844 CONSTRAINT icinga_service_check_command 845 FOREIGN KEY (check_command_id) 846 REFERENCES icinga_command (id) 847 ON DELETE RESTRICT 848 ON UPDATE CASCADE, 849 CONSTRAINT icinga_service_event_command 850 FOREIGN KEY (event_command_id) 851 REFERENCES icinga_command (id) 852 ON DELETE RESTRICT 853 ON UPDATE CASCADE, 854 CONSTRAINT icinga_service_command_endpoint 855 FOREIGN KEY (command_endpoint_id) 856 REFERENCES icinga_endpoint (id) 857 ON DELETE RESTRICT 858 ON UPDATE CASCADE, 859 CONSTRAINT icinga_service_service_set 860 FOREIGN KEY (service_set_id) 861 REFERENCES icinga_service_set (id) 862 ON DELETE CASCADE 863 ON UPDATE CASCADE, 864 CONSTRAINT icinga_service_template_choice 865 FOREIGN KEY (template_choice_id) 866 REFERENCES icinga_service_template_choice (id) 867 ON DELETE SET NULL 868 ON UPDATE CASCADE 869); 870 871CREATE INDEX service_zone ON icinga_service (zone_id); 872CREATE INDEX service_timeperiod ON icinga_service (check_period_id); 873CREATE INDEX service_check_command ON icinga_service (check_command_id); 874CREATE INDEX service_event_command ON icinga_service (event_command_id); 875CREATE INDEX service_command_endpoint ON icinga_service (command_endpoint_id); 876CREATE INDEX service_template_choice ON icinga_service (template_choice_id); 877 878 879CREATE TABLE icinga_service_inheritance ( 880 service_id integer NOT NULL, 881 parent_service_id integer NOT NULL, 882 weight integer DEFAULT NULL, 883 PRIMARY KEY (service_id, parent_service_id), 884 CONSTRAINT icinga_service_inheritance_service 885 FOREIGN KEY (service_id) 886 REFERENCES icinga_service (id) 887 ON DELETE CASCADE 888 ON UPDATE CASCADE, 889 CONSTRAINT icinga_service_inheritance_parent_service 890 FOREIGN KEY (parent_service_id) 891 REFERENCES icinga_service (id) 892 ON DELETE RESTRICT 893 ON UPDATE CASCADE 894); 895 896CREATE UNIQUE INDEX service_inheritance_unique_order ON icinga_service_inheritance (service_id, weight); 897CREATE INDEX service_inheritance_service ON icinga_service_inheritance (service_id); 898CREATE INDEX service_inheritance_service_parent ON icinga_service_inheritance (parent_service_id); 899 900 901CREATE TABLE icinga_service_var ( 902 service_id integer NOT NULL, 903 checksum bytea DEFAULT NULL UNIQUE CHECK(LENGTH(checksum) = 20), 904 varname character varying(255) NOT NULL, 905 varvalue text DEFAULT NULL, 906 format enum_property_format, 907 PRIMARY KEY (service_id, varname), 908 CONSTRAINT icinga_service_var_service 909 FOREIGN KEY (service_id) 910 REFERENCES icinga_service (id) 911 ON DELETE CASCADE 912 ON UPDATE CASCADE 913); 914 915CREATE INDEX service_var_search_idx ON icinga_service_var (varname); 916CREATE INDEX service_var_service ON icinga_service_var (service_id); 917CREATE INDEX service_var_checksum ON icinga_service_var (checksum); 918 919 920CREATE TABLE icinga_service_field ( 921 service_id integer NOT NULL, 922 datafield_id integer NOT NULL, 923 is_required enum_boolean NOT NULL, 924 var_filter TEXT DEFAULT NULL, 925 PRIMARY KEY (service_id, datafield_id), 926 CONSTRAINT icinga_service_field_service 927 FOREIGN KEY (service_id) 928 REFERENCES icinga_service (id) 929 ON DELETE CASCADE 930 ON UPDATE CASCADE, 931 CONSTRAINT icinga_service_field_datafield 932 FOREIGN KEY (datafield_id) 933 REFERENCES director_datafield (id) 934 ON DELETE CASCADE 935 ON UPDATE CASCADE 936); 937 938CREATE UNIQUE INDEX service_field_key ON icinga_service_field (service_id, datafield_id); 939CREATE INDEX service_field_service ON icinga_service_field (service_id); 940CREATE INDEX service_field_datafield ON icinga_service_field (datafield_id); 941COMMENT ON COLUMN icinga_service_field.service_id IS 'Makes only sense for templates'; 942 943 944ALTER TABLE icinga_service_template_choice 945 ADD CONSTRAINT service_template_choice_required_template 946 FOREIGN KEY (required_template_id) 947 REFERENCES icinga_service (id) 948 ON DELETE RESTRICT 949 ON UPDATE CASCADE; 950 951 952CREATE TABLE icinga_host_service ( 953 host_id integer NOT NULL, 954 service_id integer NOT NULL, 955 PRIMARY KEY (host_id, service_id), 956 CONSTRAINT icinga_host_service_host 957 FOREIGN KEY (host_id) 958 REFERENCES icinga_host (id) 959 ON DELETE CASCADE 960 ON UPDATE CASCADE, 961 CONSTRAINT icinga_host_service_service 962 FOREIGN KEY (service_id) 963 REFERENCES icinga_service (id) 964 ON DELETE CASCADE 965 ON UPDATE CASCADE 966); 967 968CREATE INDEX host_service_host ON icinga_host_service (host_id); 969CREATE INDEX host_service_service ON icinga_host_service (service_id); 970 971 972CREATE TABLE icinga_host_service_blacklist( 973 host_id integer NOT NULL, 974 service_id integer NOT NULL, 975 PRIMARY KEY (host_id, service_id), 976 CONSTRAINT icinga_host_service__bl_host 977 FOREIGN KEY (host_id) 978 REFERENCES icinga_host (id) 979 ON DELETE CASCADE 980 ON UPDATE CASCADE, 981 CONSTRAINT icinga_host_service_bl_service 982 FOREIGN KEY (service_id) 983 REFERENCES icinga_service (id) 984 ON DELETE CASCADE 985 ON UPDATE CASCADE 986); 987 988CREATE INDEX host_service_bl_host ON icinga_host_service_blacklist (host_id); 989CREATE INDEX host_service_bl_service ON icinga_host_service_blacklist (service_id); 990 991 992CREATE TABLE icinga_service_set_inheritance ( 993 service_set_id integer NOT NULL, 994 parent_service_set_id integer NOT NULL, 995 weight integer DEFAULT NULL, 996 PRIMARY KEY (service_set_id, parent_service_set_id), 997 CONSTRAINT icinga_service_set_inheritance_set 998 FOREIGN KEY (service_set_id) 999 REFERENCES icinga_service_set (id) 1000 ON DELETE CASCADE 1001 ON UPDATE CASCADE, 1002 CONSTRAINT icinga_service_set_inheritance_parent 1003 FOREIGN KEY (parent_service_set_id) 1004 REFERENCES icinga_service_set (id) 1005 ON DELETE RESTRICT 1006 ON UPDATE CASCADE 1007); 1008 1009CREATE UNIQUE INDEX service_set_inheritance_unique_order ON icinga_service_set_inheritance (service_set_id, weight); 1010CREATE INDEX service_set_inheritance_set ON icinga_service_set_inheritance (service_set_id); 1011CREATE INDEX service_set_inheritance_parent ON icinga_service_set_inheritance (parent_service_set_id); 1012 1013 1014CREATE TABLE icinga_service_set_var ( 1015 service_set_id integer NOT NULL, 1016 checksum bytea DEFAULT NULL UNIQUE CHECK(LENGTH(checksum) = 20), 1017 varname character varying(255) NOT NULL, 1018 varvalue text DEFAULT NULL, 1019 format enum_property_format NOT NULL DEFAULT 'string', 1020 PRIMARY KEY (service_set_id, varname), 1021 CONSTRAINT icinga_service_set_var_service_set 1022 FOREIGN KEY (service_set_id) 1023 REFERENCES icinga_service_set (id) 1024 ON DELETE CASCADE 1025 ON UPDATE CASCADE 1026); 1027 1028CREATE INDEX service_set_var_service_set ON icinga_service_set_var (service_set_id); 1029CREATE INDEX service_set_var_search_idx ON icinga_service_set_var (varname); 1030CREATE INDEX service_set_var_checksum ON icinga_service_set_var (checksum); 1031 1032 1033CREATE TABLE icinga_hostgroup ( 1034 id serial, 1035 object_name character varying(255) NOT NULL, 1036 object_type enum_object_type_all NOT NULL, 1037 disabled enum_boolean NOT NULL DEFAULT 'n', 1038 display_name character varying(255) DEFAULT NULL, 1039 assign_filter text DEFAULT NULL, 1040 PRIMARY KEY (id) 1041); 1042 1043CREATE UNIQUE INDEX hostgroup_object_name ON icinga_hostgroup (object_name); 1044CREATE INDEX hostgroup_search_idx ON icinga_hostgroup (display_name); 1045 1046 1047-- -- TODO: probably useless 1048CREATE TABLE icinga_hostgroup_inheritance ( 1049 hostgroup_id integer NOT NULL, 1050 parent_hostgroup_id integer NOT NULL, 1051 weight integer DEFAULT NULL, 1052 PRIMARY KEY (hostgroup_id, parent_hostgroup_id), 1053 CONSTRAINT icinga_hostgroup_inheritance_hostgroup 1054 FOREIGN KEY (hostgroup_id) 1055 REFERENCES icinga_hostgroup (id) 1056 ON DELETE CASCADE 1057 ON UPDATE CASCADE, 1058 CONSTRAINT icinga_hostgroup_inheritance_parent_hostgroup 1059 FOREIGN KEY (parent_hostgroup_id) 1060 REFERENCES icinga_hostgroup (id) 1061 ON DELETE RESTRICT 1062 ON UPDATE CASCADE 1063); 1064 1065CREATE UNIQUE INDEX hostgroup_inheritance_unique_order ON icinga_hostgroup_inheritance (hostgroup_id, weight); 1066CREATE INDEX hostgroup_inheritance_hostgroup ON icinga_hostgroup_inheritance (hostgroup_id); 1067CREATE INDEX hostgroup_inheritance_hostgroup_parent ON icinga_hostgroup_inheritance (parent_hostgroup_id); 1068 1069 1070CREATE TABLE icinga_servicegroup ( 1071 id serial, 1072 object_name character varying(255) DEFAULT NULL, 1073 object_type enum_object_type_all NOT NULL, 1074 disabled enum_boolean NOT NULL DEFAULT 'n', 1075 display_name character varying(255) DEFAULT NULL, 1076 assign_filter text DEFAULT NULL, 1077 PRIMARY KEY (id) 1078); 1079 1080CREATE UNIQUE INDEX servicegroup_object_name ON icinga_servicegroup (object_name); 1081CREATE INDEX servicegroup_search_idx ON icinga_servicegroup (display_name); 1082 1083CREATE TABLE icinga_servicegroup_service_resolved ( 1084 servicegroup_id integer NOT NULL, 1085 service_id integer NOT NULL, 1086 PRIMARY KEY (servicegroup_id, service_id), 1087 CONSTRAINT icinga_servicegroup_service_resolved_service 1088 FOREIGN KEY (service_id) 1089 REFERENCES icinga_service (id) 1090 ON DELETE CASCADE 1091 ON UPDATE CASCADE, 1092 CONSTRAINT icinga_servicegroup_service_resolved_servicegroup 1093 FOREIGN KEY (servicegroup_id) 1094 REFERENCES icinga_servicegroup (id) 1095 ON DELETE CASCADE 1096 ON UPDATE CASCADE 1097); 1098 1099CREATE INDEX servicegroup_service_resolved_service ON icinga_servicegroup_service_resolved (service_id); 1100CREATE INDEX servicegroup_service_resolved_servicegroup ON icinga_servicegroup_service_resolved (servicegroup_id); 1101 1102 1103CREATE TABLE icinga_servicegroup_inheritance ( 1104 servicegroup_id integer NOT NULL, 1105 parent_servicegroup_id integer NOT NULL, 1106 weight integer DEFAULT NULL, 1107 PRIMARY KEY (servicegroup_id, parent_servicegroup_id), 1108 CONSTRAINT icinga_servicegroup_inheritance_servicegroup 1109 FOREIGN KEY (servicegroup_id) 1110 REFERENCES icinga_servicegroup (id) 1111 ON DELETE CASCADE 1112 ON UPDATE CASCADE, 1113 CONSTRAINT icinga_servicegroup_inheritance_parent_servicegroup 1114 FOREIGN KEY (parent_servicegroup_id) 1115 REFERENCES icinga_servicegroup (id) 1116 ON DELETE RESTRICT 1117 ON UPDATE CASCADE 1118); 1119 1120CREATE UNIQUE INDEX servicegroup_inheritance_unique_order ON icinga_servicegroup_inheritance (servicegroup_id, weight); 1121CREATE INDEX servicegroup_inheritance_servicegroup ON icinga_servicegroup_inheritance (servicegroup_id); 1122CREATE INDEX servicegroup_inheritance_servicegroup_parent ON icinga_servicegroup_inheritance (parent_servicegroup_id); 1123 1124 1125CREATE TABLE icinga_servicegroup_service ( 1126 servicegroup_id integer NOT NULL, 1127 service_id integer NOT NULL, 1128 PRIMARY KEY (servicegroup_id, service_id), 1129 CONSTRAINT icinga_servicegroup_service_service 1130 FOREIGN KEY (service_id) 1131 REFERENCES icinga_service (id) 1132 ON DELETE CASCADE 1133 ON UPDATE CASCADE, 1134 CONSTRAINT icinga_servicegroup_service_servicegroup 1135 FOREIGN KEY (servicegroup_id) 1136 REFERENCES icinga_servicegroup (id) 1137 ON DELETE CASCADE 1138 ON UPDATE CASCADE 1139); 1140 1141CREATE INDEX servicegroup_service_service ON icinga_servicegroup_service (service_id); 1142CREATE INDEX servicegroup_service_servicegroup ON icinga_servicegroup_service (servicegroup_id); 1143 1144 1145CREATE TABLE icinga_hostgroup_host ( 1146 hostgroup_id integer NOT NULL, 1147 host_id integer NOT NULL, 1148 PRIMARY KEY (hostgroup_id, host_id), 1149 CONSTRAINT icinga_hostgroup_host_host 1150 FOREIGN KEY (host_id) 1151 REFERENCES icinga_host (id) 1152 ON DELETE CASCADE 1153 ON UPDATE CASCADE, 1154 CONSTRAINT icinga_hostgroup_host_hostgroup 1155 FOREIGN KEY (hostgroup_id) 1156 REFERENCES icinga_hostgroup (id) 1157 ON DELETE CASCADE 1158 ON UPDATE CASCADE 1159); 1160 1161CREATE INDEX hostgroup_host_host ON icinga_hostgroup_host (host_id); 1162CREATE INDEX hostgroup_host_hostgroup ON icinga_hostgroup_host (hostgroup_id); 1163 1164 1165CREATE TABLE icinga_hostgroup_host_resolved ( 1166 hostgroup_id integer NOT NULL, 1167 host_id integer NOT NULL, 1168 PRIMARY KEY (hostgroup_id, host_id), 1169 CONSTRAINT icinga_hostgroup_host_resolved_host 1170 FOREIGN KEY (host_id) 1171 REFERENCES icinga_host (id) 1172 ON DELETE CASCADE 1173 ON UPDATE CASCADE, 1174 CONSTRAINT icinga_hostgroup_host_resolved_hostgroup 1175 FOREIGN KEY (hostgroup_id) 1176 REFERENCES icinga_hostgroup (id) 1177 ON DELETE CASCADE 1178 ON UPDATE CASCADE 1179); 1180 1181CREATE INDEX hostgroup_host_resolved_host ON icinga_hostgroup_host_resolved (host_id); 1182CREATE INDEX hostgroup_host_resolved_hostgroup ON icinga_hostgroup_host_resolved (hostgroup_id); 1183 1184 1185CREATE TABLE icinga_hostgroup_parent ( 1186 hostgroup_id integer NOT NULL, 1187 parent_hostgroup_id integer NOT NULL, 1188 PRIMARY KEY (hostgroup_id, parent_hostgroup_id), 1189 CONSTRAINT icinga_hostgroup_parent_hostgroup 1190 FOREIGN KEY (hostgroup_id) 1191 REFERENCES icinga_hostgroup (id) 1192 ON DELETE CASCADE 1193 ON UPDATE CASCADE, 1194 CONSTRAINT icinga_hostgroup_parent_parent 1195 FOREIGN KEY (parent_hostgroup_id) 1196 REFERENCES icinga_hostgroup (id) 1197 ON DELETE RESTRICT 1198 ON UPDATE CASCADE 1199); 1200 1201CREATE INDEX hostgroup_parent_hostgroup ON icinga_hostgroup_parent (hostgroup_id); 1202CREATE INDEX hostgroup_parent_parent ON icinga_hostgroup_parent (parent_hostgroup_id); 1203 1204 1205CREATE TABLE icinga_user ( 1206 id serial, 1207 object_name character varying(255) DEFAULT NULL, 1208 object_type enum_object_type_all NOT NULL, 1209 disabled enum_boolean NOT NULL DEFAULT 'n', 1210 display_name character varying(255) DEFAULT NULL, 1211 email character varying(255) DEFAULT NULL, 1212 pager character varying(255) DEFAULT NULL, 1213 enable_notifications enum_boolean DEFAULT NULL, 1214 period_id integer DEFAULT NULL, 1215 zone_id integer DEFAULT NULL, 1216 PRIMARY KEY (id), 1217 CONSTRAINT icinga_user_zone 1218 FOREIGN KEY (zone_id) 1219 REFERENCES icinga_zone (id) 1220 ON DELETE RESTRICT 1221 ON UPDATE CASCADE, 1222 CONSTRAINT icinga_user_period 1223 FOREIGN KEY (period_id) 1224 REFERENCES icinga_timeperiod (id) 1225 ON DELETE RESTRICT 1226 ON UPDATE CASCADE 1227); 1228 1229CREATE UNIQUE INDEX user_object_name ON icinga_user (object_name, zone_id); 1230CREATE INDEX user_zone ON icinga_user (zone_id); 1231 1232 1233CREATE TABLE icinga_user_inheritance ( 1234 user_id integer NOT NULL, 1235 parent_user_id integer NOT NULL, 1236 weight integer DEFAULT NULL, 1237 PRIMARY KEY (user_id, parent_user_id), 1238 CONSTRAINT icinga_user_inheritance_user 1239 FOREIGN KEY (user_id) 1240 REFERENCES icinga_user (id) 1241 ON DELETE CASCADE 1242 ON UPDATE CASCADE, 1243 CONSTRAINT icinga_user_inheritance_parent_user 1244 FOREIGN KEY (parent_user_id) 1245 REFERENCES icinga_user (id) 1246 ON DELETE RESTRICT 1247 ON UPDATE CASCADE 1248); 1249 1250CREATE UNIQUE INDEX user_inheritance_unique_order ON icinga_user_inheritance (user_id, weight); 1251CREATE INDEX user_inheritance_user ON icinga_user_inheritance (user_id); 1252CREATE INDEX user_inheritance_user_parent ON icinga_user_inheritance (parent_user_id); 1253 1254 1255CREATE TABLE icinga_user_states_set ( 1256 user_id integer NOT NULL, 1257 property enum_state_name NOT NULL, 1258 merge_behaviour enum_set_merge_behaviour NOT NULL DEFAULT 'override', 1259 PRIMARY KEY (user_id, property, merge_behaviour), 1260 CONSTRAINT icinga_user_filter_state_user 1261 FOREIGN KEY (user_id) 1262 REFERENCES icinga_user (id) 1263 ON DELETE CASCADE 1264 ON UPDATE CASCADE 1265); 1266 1267CREATE INDEX user_states_set_user ON icinga_user_states_set (user_id); 1268COMMENT ON COLUMN icinga_user_states_set.merge_behaviour IS 'override: = [], extend: += [], blacklist: -= []'; 1269 1270 1271CREATE TABLE icinga_user_types_set ( 1272 user_id integer NOT NULL, 1273 property enum_type_name NOT NULL, 1274 merge_behaviour enum_set_merge_behaviour NOT NULL DEFAULT 'override', 1275 PRIMARY KEY (user_id, property, merge_behaviour), 1276 CONSTRAINT icinga_user_filter_type_user 1277 FOREIGN KEY (user_id) 1278 REFERENCES icinga_user (id) 1279 ON DELETE CASCADE 1280 ON UPDATE CASCADE 1281); 1282 1283CREATE INDEX user_types_set_user ON icinga_user_types_set (user_id); 1284COMMENT ON COLUMN icinga_user_types_set.merge_behaviour IS 'override: = [], extend: += [], blacklist: -= []'; 1285 1286 1287CREATE TABLE icinga_user_var ( 1288 user_id integer NOT NULL, 1289 checksum bytea DEFAULT NULL UNIQUE CHECK(LENGTH(checksum) = 20), 1290 varname character varying(255) NOT NULL, 1291 varvalue text DEFAULT NULL, 1292 format enum_property_format NOT NULL DEFAULT 'string', 1293 PRIMARY KEY (user_id, varname), 1294 CONSTRAINT icinga_user_var_user 1295 FOREIGN KEY (user_id) 1296 REFERENCES icinga_user (id) 1297 ON DELETE CASCADE 1298 ON UPDATE CASCADE 1299); 1300 1301CREATE INDEX user_var_search_idx ON icinga_user_var (varname); 1302CREATE INDEX user_var_user ON icinga_user_var (user_id); 1303CREATE INDEX user_var_checksum ON icinga_user_var (checksum); 1304 1305 1306CREATE TABLE icinga_user_field ( 1307 user_id integer NOT NULL, 1308 datafield_id integer NOT NULL, 1309 is_required enum_boolean NOT NULL, 1310 var_filter TEXT DEFAULT NULL, 1311 PRIMARY KEY (user_id, datafield_id), 1312 CONSTRAINT icinga_user_field_user 1313 FOREIGN KEY (user_id) 1314 REFERENCES icinga_user (id) 1315 ON DELETE CASCADE 1316 ON UPDATE CASCADE, 1317 CONSTRAINT icinga_user_field_datafield 1318 FOREIGN KEY (datafield_id) 1319 REFERENCES director_datafield (id) 1320 ON DELETE CASCADE 1321 ON UPDATE CASCADE 1322); 1323 1324CREATE UNIQUE INDEX user_field_key ON icinga_user_field (user_id, datafield_id); 1325CREATE INDEX user_field_user ON icinga_user_field (user_id); 1326CREATE INDEX user_field_datafield ON icinga_user_field (datafield_id); 1327COMMENT ON COLUMN icinga_user_field.user_id IS 'Makes only sense for templates'; 1328 1329 1330CREATE TABLE icinga_usergroup ( 1331 id serial, 1332 object_name character varying(255) NOT NULL, 1333 object_type enum_object_type_all NOT NULL, 1334 disabled enum_boolean NOT NULL DEFAULT 'n', 1335 display_name character varying(255) DEFAULT NULL, 1336 zone_id integer DEFAULT NULL, 1337 PRIMARY KEY (id), 1338 CONSTRAINT icinga_usergroup_zone 1339 FOREIGN KEY (zone_id) 1340 REFERENCES icinga_zone (id) 1341 ON DELETE RESTRICT 1342 ON UPDATE CASCADE 1343); 1344 1345CREATE UNIQUE INDEX usergroup_search_idx ON icinga_usergroup (display_name); 1346CREATE INDEX usergroup_object_name ON icinga_usergroup (object_name); 1347CREATE INDEX usergroup_zone ON icinga_usergroup (zone_id); 1348 1349 1350CREATE TABLE icinga_usergroup_inheritance ( 1351 usergroup_id integer NOT NULL, 1352 parent_usergroup_id integer NOT NULL, 1353 weight integer DEFAULT NULL, 1354 PRIMARY KEY (usergroup_id, parent_usergroup_id), 1355 CONSTRAINT icinga_usergroup_inheritance_usergroup 1356 FOREIGN KEY (usergroup_id) 1357 REFERENCES icinga_usergroup (id) 1358 ON DELETE CASCADE 1359 ON UPDATE CASCADE, 1360 CONSTRAINT icinga_usergroup_inheritance_parent_usergroup 1361 FOREIGN KEY (parent_usergroup_id) 1362 REFERENCES icinga_usergroup (id) 1363 ON DELETE RESTRICT 1364 ON UPDATE CASCADE 1365); 1366 1367CREATE UNIQUE INDEX usergroup_inheritance_unique_order ON icinga_usergroup_inheritance (usergroup_id, weight); 1368CREATE INDEX usergroup_inheritance_usergroup ON icinga_usergroup_inheritance (usergroup_id); 1369CREATE INDEX usergroup_inheritance_usergroup_parent ON icinga_usergroup_inheritance (parent_usergroup_id); 1370 1371 1372CREATE TABLE icinga_usergroup_user ( 1373 usergroup_id integer NOT NULL, 1374 user_id integer NOT NULL, 1375 PRIMARY KEY (usergroup_id, user_id), 1376 CONSTRAINT icinga_usergroup_user_user 1377 FOREIGN KEY (user_id) 1378 REFERENCES icinga_user (id) 1379 ON DELETE CASCADE 1380 ON UPDATE CASCADE, 1381 CONSTRAINT icinga_usergroup_user_usergroup 1382 FOREIGN KEY (usergroup_id) 1383 REFERENCES icinga_usergroup (id) 1384 ON DELETE CASCADE 1385 ON UPDATE CASCADE 1386); 1387 1388CREATE INDEX usergroup_user_user ON icinga_usergroup_user (user_id); 1389CREATE INDEX usergroup_user_usergroup ON icinga_usergroup_user (usergroup_id); 1390 1391 1392CREATE TABLE icinga_usergroup_parent ( 1393 usergroup_id integer NOT NULL, 1394 parent_usergroup_id integer NOT NULL, 1395 PRIMARY KEY (usergroup_id, parent_usergroup_id), 1396 CONSTRAINT icinga_usergroup_parent_usergroup 1397 FOREIGN KEY (usergroup_id) 1398 REFERENCES icinga_usergroup (id) 1399 ON DELETE CASCADE 1400 ON UPDATE CASCADE, 1401 CONSTRAINT icinga_usergroup_parent_parent 1402 FOREIGN KEY (parent_usergroup_id) 1403 REFERENCES icinga_usergroup (id) 1404 ON DELETE RESTRICT 1405 ON UPDATE CASCADE 1406); 1407 1408CREATE INDEX usergroup_parent_usergroup ON icinga_usergroup_parent (usergroup_id); 1409CREATE INDEX usergroup_parent_parent ON icinga_usergroup_parent (parent_usergroup_id); 1410 1411 1412CREATE TABLE icinga_notification ( 1413 id serial, 1414 object_name CHARACTER VARYING(255) DEFAULT NULL, 1415 object_type enum_object_type_all NOT NULL, 1416 disabled enum_boolean NOT NULL DEFAULT 'n', 1417 apply_to enum_host_service NULL DEFAULT NULL, 1418 host_id integer DEFAULT NULL, 1419 service_id integer DEFAULT NULL, 1420 times_begin integer DEFAULT NULL, 1421 times_end integer DEFAULT NULL, 1422 notification_interval integer DEFAULT NULL, 1423 command_id integer DEFAULT NULL, 1424 period_id integer DEFAULT NULL, 1425 zone_id integer DEFAULT NULL, 1426 assign_filter text DEFAULT NULL, 1427 PRIMARY KEY (id), 1428 CONSTRAINT icinga_notification_host 1429 FOREIGN KEY (host_id) 1430 REFERENCES icinga_host (id) 1431 ON DELETE CASCADE 1432 ON UPDATE CASCADE, 1433 CONSTRAINT icinga_notification_service 1434 FOREIGN KEY (service_id) 1435 REFERENCES icinga_service (id) 1436 ON DELETE CASCADE 1437 ON UPDATE CASCADE, 1438 CONSTRAINT icinga_notification_command 1439 FOREIGN KEY (command_id) 1440 REFERENCES icinga_command (id) 1441 ON DELETE RESTRICT 1442 ON UPDATE CASCADE, 1443 CONSTRAINT icinga_notification_period 1444 FOREIGN KEY (period_id) 1445 REFERENCES icinga_timeperiod (id) 1446 ON DELETE RESTRICT 1447 ON UPDATE CASCADE, 1448 CONSTRAINT icinga_notification_zone 1449 FOREIGN KEY (zone_id) 1450 REFERENCES icinga_zone (id) 1451 ON DELETE RESTRICT 1452 ON UPDATE CASCADE 1453); 1454 1455 1456CREATE TABLE icinga_notification_user ( 1457 notification_id integer NOT NULL, 1458 user_id integer NOT NULL, 1459 PRIMARY KEY (notification_id, user_id), 1460 CONSTRAINT icinga_notification_user_user 1461 FOREIGN KEY (user_id) 1462 REFERENCES icinga_user (id) 1463 ON DELETE CASCADE 1464 ON UPDATE CASCADE, 1465 CONSTRAINT icinga_notification_user_notification 1466 FOREIGN KEY (notification_id) 1467 REFERENCES icinga_notification (id) 1468 ON DELETE CASCADE 1469 ON UPDATE CASCADE 1470); 1471 1472CREATE TABLE icinga_notification_usergroup ( 1473 notification_id integer NOT NULL, 1474 usergroup_id integer NOT NULL, 1475 PRIMARY KEY (notification_id, usergroup_id), 1476 CONSTRAINT icinga_notification_usergroup_usergroup 1477 FOREIGN KEY (usergroup_id) 1478 REFERENCES icinga_usergroup (id) 1479 ON DELETE CASCADE 1480 ON UPDATE CASCADE, 1481 CONSTRAINT icinga_notification_usergroup_notification 1482 FOREIGN KEY (notification_id) 1483 REFERENCES icinga_notification (id) 1484 ON DELETE CASCADE 1485 ON UPDATE CASCADE 1486); 1487 1488 1489CREATE TABLE import_source ( 1490 id serial, 1491 source_name character varying(64) NOT NULL, 1492 key_column character varying(64) NOT NULL, 1493 provider_class character varying(128) NOT NULL, 1494 import_state enum_sync_state NOT NULL DEFAULT 'unknown', 1495 last_error_message text NULL DEFAULT NULL, 1496 last_attempt timestamp with time zone NULL DEFAULT NULL, 1497 description text DEFAULT NULL, 1498 PRIMARY KEY (id) 1499); 1500 1501CREATE INDEX import_source_search_idx ON import_source (key_column); 1502CREATE UNIQUE INDEX import_source_name ON import_source (source_name); 1503 1504 1505CREATE TABLE import_source_setting ( 1506 source_id integer NOT NULL, 1507 setting_name character varying(64) NOT NULL, 1508 setting_value text NOT NULL, 1509 PRIMARY KEY (source_id, setting_name), 1510 CONSTRAINT import_source_settings_source 1511 FOREIGN KEY (source_id) 1512 REFERENCES import_source (id) 1513 ON DELETE CASCADE 1514 ON UPDATE CASCADE 1515); 1516 1517CREATE INDEX import_source_setting_source ON import_source_setting (source_id); 1518 1519 1520CREATE TABLE import_row_modifier ( 1521 id bigserial, 1522 source_id integer NOT NULL, 1523 property_name character varying(255) NOT NULL, 1524 target_property character varying(255) DEFAULT NULL, 1525 provider_class character varying(128) NOT NULL, 1526 priority integer NOT NULL, 1527 description text DEFAULT NULL, 1528 PRIMARY KEY (id), 1529 CONSTRAINT row_modifier_import_source 1530 FOREIGN KEY (source_id) 1531 REFERENCES import_source (id) 1532 ON DELETE CASCADE 1533 ON UPDATE CASCADE 1534); 1535 1536CREATE INDEX import_row_modifier_search_idx ON import_row_modifier (property_name); 1537CREATE UNIQUE INDEX import_row_modifier_prio 1538 ON import_row_modifier (source_id, priority); 1539 1540 1541CREATE TABLE import_row_modifier_setting ( 1542 row_modifier_id serial, 1543 setting_name character varying(64) NOT NULL, 1544 setting_value TEXT DEFAULT NULL, 1545 PRIMARY KEY (row_modifier_id, setting_name), 1546 CONSTRAINT row_modifier_settings 1547 FOREIGN KEY (row_modifier_id) 1548 REFERENCES import_row_modifier (id) 1549 ON DELETE CASCADE 1550 ON UPDATE CASCADE 1551); 1552 1553 1554CREATE TABLE imported_rowset ( 1555 checksum bytea CHECK(LENGTH(checksum) = 20), 1556 PRIMARY KEY (checksum) 1557); 1558 1559 1560CREATE TABLE import_run ( 1561 id serial, 1562 source_id integer NOT NULL, 1563 rowset_checksum bytea CHECK(LENGTH(rowset_checksum) = 20), 1564 start_time timestamp with time zone NOT NULL, 1565 end_time timestamp with time zone DEFAULT NULL, 1566 succeeded enum_boolean DEFAULT NULL, 1567 PRIMARY KEY (id), 1568 CONSTRAINT import_run_source 1569 FOREIGN KEY (source_id) 1570 REFERENCES import_source (id) 1571 ON DELETE CASCADE 1572 ON UPDATE RESTRICT, 1573 CONSTRAINT import_run_rowset 1574 FOREIGN KEY (rowset_checksum) 1575 REFERENCES imported_rowset (checksum) 1576 ON DELETE RESTRICT 1577 ON UPDATE CASCADE 1578); 1579 1580CREATE INDEX import_run_import_source ON import_run (source_id); 1581CREATE INDEX import_run_rowset ON import_run (rowset_checksum); 1582 1583 1584CREATE TABLE imported_row ( 1585 checksum bytea CHECK(LENGTH(checksum) = 20), 1586 object_name character varying(255) NOT NULL, 1587 PRIMARY KEY (checksum) 1588); 1589 1590COMMENT ON COLUMN imported_row.checksum IS 'sha1(object_name;property_checksum;...)'; 1591 1592 1593CREATE TABLE imported_rowset_row ( 1594 rowset_checksum bytea CHECK(LENGTH(rowset_checksum) = 20), 1595 row_checksum bytea CHECK(LENGTH(row_checksum) = 20), 1596 PRIMARY KEY (rowset_checksum, row_checksum), 1597 CONSTRAINT imported_rowset_row_rowset 1598 FOREIGN KEY (rowset_checksum) 1599 REFERENCES imported_rowset (checksum) 1600 ON DELETE CASCADE 1601 ON UPDATE CASCADE, 1602 CONSTRAINT imported_rowset_row_row 1603 FOREIGN KEY (row_checksum) 1604 REFERENCES imported_row (checksum) 1605 ON DELETE RESTRICT 1606 ON UPDATE CASCADE 1607); 1608 1609CREATE INDEX imported_rowset_row_rowset_checksum ON imported_rowset_row (rowset_checksum); 1610CREATE INDEX imported_rowset_row_row_checksum ON imported_rowset_row (row_checksum); 1611 1612 1613CREATE TABLE imported_property ( 1614 checksum bytea CHECK(LENGTH(checksum) = 20), 1615 property_name character varying(64) NOT NULL, 1616 property_value text NOT NULL, 1617 format enum_property_format, 1618 PRIMARY KEY (checksum) 1619); 1620 1621CREATE INDEX imported_property_search_idx ON imported_property (property_name); 1622 1623 1624CREATE TABLE imported_row_property ( 1625 row_checksum bytea CHECK(LENGTH(row_checksum) = 20), 1626 property_checksum bytea CHECK(LENGTH(property_checksum) = 20), 1627 PRIMARY KEY (row_checksum, property_checksum), 1628 CONSTRAINT imported_row_property_row 1629 FOREIGN KEY (row_checksum) 1630 REFERENCES imported_row (checksum) 1631 ON DELETE CASCADE 1632 ON UPDATE CASCADE, 1633 CONSTRAINT imported_row_property_property 1634 FOREIGN KEY (property_checksum) 1635 REFERENCES imported_property (checksum) 1636 ON DELETE RESTRICT 1637 ON UPDATE CASCADE 1638); 1639 1640CREATE INDEX imported_row_property_row_checksum ON imported_row_property (row_checksum); 1641CREATE INDEX imported_row_property_property_checksum ON imported_row_property (property_checksum); 1642 1643 1644CREATE TABLE sync_rule ( 1645 id serial, 1646 rule_name character varying(255) NOT NULL, 1647 object_type enum_sync_rule_object_type NOT NULL, 1648 update_policy enum_sync_rule_update_policy NOT NULL, 1649 purge_existing enum_boolean NOT NULL DEFAULT 'n', 1650 filter_expression text DEFAULT NULL, 1651 sync_state enum_sync_state NOT NULL DEFAULT 'unknown', 1652 last_error_message text NULL DEFAULT NULL, 1653 last_attempt timestamp with time zone NULL DEFAULT NULL, 1654 description text DEFAULT NULL, 1655 PRIMARY KEY (id) 1656); 1657 1658CREATE UNIQUE INDEX sync_rule_name ON sync_rule (rule_name); 1659 1660CREATE TABLE sync_property ( 1661 id serial, 1662 rule_id integer NOT NULL, 1663 source_id integer NOT NULL, 1664 source_expression character varying(255) NOT NULL, 1665 destination_field character varying(64), 1666 priority smallint NOT NULL, 1667 filter_expression text DEFAULT NULL, 1668 merge_policy enum_sync_property_merge_policy DEFAULT NULL, 1669 PRIMARY KEY (id), 1670 CONSTRAINT sync_property_rule 1671 FOREIGN KEY (rule_id) 1672 REFERENCES sync_rule (id) 1673 ON DELETE CASCADE 1674 ON UPDATE CASCADE, 1675 CONSTRAINT sync_property_source 1676 FOREIGN KEY (source_id) 1677 REFERENCES import_source (id) 1678 ON DELETE RESTRICT 1679 ON UPDATE CASCADE 1680); 1681 1682CREATE INDEX sync_property_rule ON sync_property (rule_id); 1683CREATE INDEX sync_property_source ON sync_property (source_id); 1684 1685 1686CREATE TABLE sync_run ( 1687 id bigserial, 1688 rule_id integer DEFAULT NULL, 1689 rule_name character varying(255) NOT NULL, 1690 start_time TIMESTAMP WITH TIME ZONE NOT NULL, 1691 duration_ms integer DEFAULT NULL, 1692 objects_deleted integer DEFAULT 0, 1693 objects_created integer DEFAULT 0, 1694 objects_modified integer DEFAULT 0, 1695 last_former_activity bytea DEFAULT NULL CHECK(LENGTH(last_former_activity) = 20), 1696 last_related_activity bytea DEFAULT NULL CHECK(LENGTH(last_related_activity) = 20), 1697 PRIMARY KEY (id), 1698 CONSTRAINT sync_run_rule 1699 FOREIGN KEY (rule_id) 1700 REFERENCES sync_rule (id) 1701 ON DELETE SET NULL 1702 ON UPDATE CASCADE 1703); 1704 1705 1706CREATE TABLE icinga_notification_states_set ( 1707 notification_id integer NOT NULL, 1708 property enum_state_name NOT NULL, 1709 merge_behaviour enum_set_merge_behaviour NOT NULL DEFAULT 'override', 1710 PRIMARY KEY (notification_id, property, merge_behaviour), 1711 CONSTRAINT icinga_notification_states_set_notification 1712 FOREIGN KEY (notification_id) 1713 REFERENCES icinga_notification (id) 1714 ON DELETE CASCADE 1715 ON UPDATE CASCADE 1716); 1717 1718COMMENT ON COLUMN icinga_notification_states_set.merge_behaviour IS 'override: = [], extend: += [], blacklist: -= []'; 1719 1720 1721CREATE TABLE icinga_notification_types_set ( 1722 notification_id integer NOT NULL, 1723 property enum_type_name NOT NULL, 1724 merge_behaviour enum_set_merge_behaviour NOT NULL DEFAULT 'override', 1725 PRIMARY KEY (notification_id, property, merge_behaviour), 1726 CONSTRAINT icinga_notification_types_set_notification 1727 FOREIGN KEY (notification_id) 1728 REFERENCES icinga_notification (id) 1729 ON DELETE CASCADE 1730 ON UPDATE CASCADE 1731); 1732 1733COMMENT ON COLUMN icinga_notification_types_set.merge_behaviour IS 'override: = [], extend: += [], blacklist: -= []'; 1734 1735 1736CREATE TABLE icinga_notification_var ( 1737 notification_id integer NOT NULL, 1738 checksum bytea DEFAULT NULL UNIQUE CHECK(LENGTH(checksum) = 20), 1739 varname VARCHAR(255) NOT NULL, 1740 varvalue TEXT DEFAULT NULL, 1741 format enum_property_format, 1742 PRIMARY KEY (notification_id, varname), 1743 CONSTRAINT icinga_notification_var_notification 1744 FOREIGN KEY (notification_id) 1745 REFERENCES icinga_notification (id) 1746 ON DELETE CASCADE 1747 ON UPDATE CASCADE 1748); 1749 1750CREATE INDEX notification_var_command ON icinga_notification_var (notification_id); 1751CREATE INDEX notification_var_search_idx ON icinga_notification_var (varname); 1752CREATE INDEX notification_var_checksum ON icinga_notification_var (checksum); 1753 1754CREATE TABLE icinga_notification_field ( 1755 notification_id integer NOT NULL, 1756 datafield_id integer NOT NULL, 1757 is_required enum_boolean NOT NULL, 1758 var_filter TEXT DEFAULT NULL, 1759 PRIMARY KEY (notification_id, datafield_id), 1760 CONSTRAINT icinga_notification_field_notification 1761 FOREIGN KEY (notification_id) 1762 REFERENCES icinga_notification (id) 1763 ON DELETE CASCADE 1764 ON UPDATE CASCADE, 1765 CONSTRAINT icinga_notification_field_datafield 1766 FOREIGN KEY (datafield_id) 1767 REFERENCES director_datafield (id) 1768 ON DELETE CASCADE 1769 ON UPDATE CASCADE 1770); 1771 1772CREATE UNIQUE INDEX notification_field_key ON icinga_notification_field (notification_id, datafield_id); 1773CREATE INDEX notification_field_notification ON icinga_notification_field (notification_id); 1774CREATE INDEX notification_field_datafield ON icinga_notification_field (datafield_id); 1775COMMENT ON COLUMN icinga_notification_field.notification_id IS 'Makes only sense for templates'; 1776 1777 1778CREATE TABLE icinga_notification_inheritance ( 1779 notification_id integer NOT NULL, 1780 parent_notification_id integer NOT NULL, 1781 weight integer DEFAULT NULL, 1782 PRIMARY KEY (notification_id, parent_notification_id), 1783 CONSTRAINT icinga_notification_inheritance_notification 1784 FOREIGN KEY (notification_id) 1785 REFERENCES icinga_notification (id) 1786 ON DELETE CASCADE 1787 ON UPDATE CASCADE, 1788 CONSTRAINT icinga_notification_inheritance_parent_notification 1789 FOREIGN KEY (parent_notification_id) 1790 REFERENCES icinga_notification (id) 1791 ON DELETE RESTRICT 1792 ON UPDATE CASCADE 1793); 1794 1795CREATE UNIQUE INDEX notification_inheritance ON icinga_notification_inheritance (notification_id, weight); 1796 1797 1798CREATE TABLE icinga_var ( 1799 checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1800 rendered_checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1801 varname character varying(255) NOT NULL, 1802 varvalue TEXT NOT NULL, 1803 rendered TEXT NOT NULL, 1804 PRIMARY KEY (checksum) 1805); 1806 1807CREATE INDEX var_search_idx ON icinga_var (varname); 1808 1809 1810CREATE TABLE icinga_flat_var ( 1811 var_checksum bytea NOT NULL CHECK(LENGTH(var_checksum) = 20), 1812 flatname_checksum bytea NOT NULL CHECK(LENGTH(flatname_checksum) = 20), 1813 flatname character varying(512) NOT NULL, 1814 flatvalue TEXT NOT NULL, 1815 PRIMARY KEY (var_checksum, flatname_checksum), 1816 CONSTRAINT flat_var_var 1817 FOREIGN KEY (var_checksum) 1818 REFERENCES icinga_var (checksum) 1819 ON DELETE CASCADE 1820 ON UPDATE CASCADE 1821); 1822 1823CREATE INDEX flat_var_var_checksum ON icinga_flat_var (var_checksum); 1824CREATE INDEX flat_var_search_varname ON icinga_flat_var (flatname); 1825CREATE INDEX flat_var_search_varvalue ON icinga_flat_var (flatvalue); 1826 1827 1828CREATE TABLE icinga_command_resolved_var ( 1829 command_id integer NOT NULL, 1830 varname character varying(255) NOT NULL, 1831 checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1832 PRIMARY KEY (command_id, checksum), 1833 CONSTRAINT command_resolved_var_command 1834 FOREIGN KEY (command_id) 1835 REFERENCES icinga_command (id) 1836 ON DELETE CASCADE 1837 ON UPDATE CASCADE, 1838 CONSTRAINT command_resolved_var_checksum 1839 FOREIGN KEY (checksum) 1840 REFERENCES icinga_var (checksum) 1841 ON DELETE RESTRICT 1842 ON UPDATE RESTRICT 1843); 1844 1845CREATE INDEX command_resolved_var_search_varname ON icinga_command_resolved_var (varname); 1846CREATE INDEX command_resolved_var_command_id ON icinga_command_resolved_var (command_id); 1847CREATE INDEX command_resolved_var_schecksum ON icinga_command_resolved_var (checksum); 1848 1849 1850CREATE TABLE icinga_host_resolved_var ( 1851 host_id integer NOT NULL, 1852 varname character varying(255) NOT NULL, 1853 checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1854 PRIMARY KEY (host_id, checksum), 1855 CONSTRAINT host_resolved_var_host 1856 FOREIGN KEY (host_id) 1857 REFERENCES icinga_host (id) 1858 ON DELETE CASCADE 1859 ON UPDATE CASCADE, 1860 CONSTRAINT host_resolved_var_checksum 1861 FOREIGN KEY (checksum) 1862 REFERENCES icinga_var (checksum) 1863 ON DELETE RESTRICT 1864 ON UPDATE RESTRICT 1865); 1866 1867CREATE INDEX host_resolved_var_search_varname ON icinga_host_resolved_var (varname); 1868CREATE INDEX host_resolved_var_host_id ON icinga_host_resolved_var (host_id); 1869CREATE INDEX host_resolved_var_schecksum ON icinga_host_resolved_var (checksum); 1870 1871 1872CREATE TABLE icinga_notification_resolved_var ( 1873 notification_id integer NOT NULL, 1874 varname character varying(255) NOT NULL, 1875 checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1876 PRIMARY KEY (notification_id, checksum), 1877 CONSTRAINT notification_resolved_var_notification 1878 FOREIGN KEY (notification_id) 1879 REFERENCES icinga_notification (id) 1880 ON DELETE CASCADE 1881 ON UPDATE CASCADE, 1882 CONSTRAINT notification_resolved_var_checksum 1883 FOREIGN KEY (checksum) 1884 REFERENCES icinga_var (checksum) 1885 ON DELETE RESTRICT 1886 ON UPDATE RESTRICT 1887); 1888 1889CREATE INDEX notification_resolved_var_search_varname ON icinga_notification_resolved_var (varname); 1890CREATE INDEX notification_resolved_var_notification_id ON icinga_notification_resolved_var (notification_id); 1891CREATE INDEX notification_resolved_var_schecksum ON icinga_notification_resolved_var (checksum); 1892 1893 1894CREATE TABLE icinga_service_set_resolved_var ( 1895 service_set_id integer NOT NULL, 1896 varname character varying(255) NOT NULL, 1897 checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1898 PRIMARY KEY (service_set_id, checksum), 1899 CONSTRAINT service_set_resolved_var_service_set 1900 FOREIGN KEY (service_set_id) 1901 REFERENCES icinga_service_set (id) 1902 ON DELETE CASCADE 1903 ON UPDATE CASCADE, 1904 CONSTRAINT service_set_resolved_var_checksum 1905 FOREIGN KEY (checksum) 1906 REFERENCES icinga_var (checksum) 1907 ON DELETE RESTRICT 1908 ON UPDATE RESTRICT 1909); 1910 1911CREATE INDEX service_set_resolved_var_search_varname ON icinga_service_set_resolved_var (varname); 1912CREATE INDEX service_set_resolved_var_service_set_id ON icinga_service_set_resolved_var (service_set_id); 1913CREATE INDEX service_set_resolved_var_schecksum ON icinga_service_set_resolved_var (checksum); 1914 1915 1916CREATE TABLE icinga_service_resolved_var ( 1917 service_id integer NOT NULL, 1918 varname character varying(255) NOT NULL, 1919 checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1920 PRIMARY KEY (service_id, checksum), 1921 CONSTRAINT service_resolved_var_service 1922 FOREIGN KEY (service_id) 1923 REFERENCES icinga_service (id) 1924 ON DELETE CASCADE 1925 ON UPDATE CASCADE, 1926 CONSTRAINT service_resolved_var_checksum 1927 FOREIGN KEY (checksum) 1928 REFERENCES icinga_var (checksum) 1929 ON DELETE RESTRICT 1930 ON UPDATE RESTRICT 1931); 1932 1933CREATE INDEX service_resolved_var_search_varname ON icinga_service_resolved_var (varname); 1934CREATE INDEX service_resolved_var_service_id ON icinga_service_resolved_var (service_id); 1935CREATE INDEX service_resolved_var_schecksum ON icinga_service_resolved_var (checksum); 1936 1937 1938CREATE TABLE icinga_user_resolved_var ( 1939 user_id integer NOT NULL, 1940 varname character varying(255) NOT NULL, 1941 checksum bytea NOT NULL CHECK(LENGTH(checksum) = 20), 1942 PRIMARY KEY (user_id, checksum), 1943 CONSTRAINT user_resolved_var_user 1944 FOREIGN KEY (user_id) 1945 REFERENCES icinga_user (id) 1946 ON DELETE CASCADE 1947 ON UPDATE CASCADE, 1948 CONSTRAINT user_resolved_var_checksum 1949 FOREIGN KEY (checksum) 1950 REFERENCES icinga_var (checksum) 1951 ON DELETE RESTRICT 1952 ON UPDATE RESTRICT 1953); 1954 1955CREATE INDEX user_resolved_var_search_varname ON icinga_user_resolved_var (varname); 1956CREATE INDEX user_resolved_var_user_id ON icinga_user_resolved_var (user_id); 1957CREATE INDEX user_resolved_var_schecksum ON icinga_user_resolved_var (checksum); 1958 1959 1960CREATE TABLE icinga_dependency ( 1961 id serial, 1962 object_name character varying(255) NOT NULL, 1963 object_type enum_object_type_all NOT NULL, 1964 disabled enum_boolean DEFAULT 'n', 1965 apply_to enum_host_service NULL DEFAULT NULL, 1966 parent_host_id integer DEFAULT NULL, 1967 parent_service_id integer DEFAULT NULL, 1968 child_host_id integer DEFAULT NULL, 1969 child_service_id integer DEFAULT NULL, 1970 disable_checks enum_boolean DEFAULT NULL, 1971 disable_notifications enum_boolean DEFAULT NULL, 1972 ignore_soft_states enum_boolean DEFAULT NULL, 1973 period_id integer DEFAULT NULL, 1974 zone_id integer DEFAULT NULL, 1975 assign_filter text DEFAULT NULL, 1976 parent_service_by_name character varying(255), 1977 PRIMARY KEY (id), 1978 CONSTRAINT icinga_dependency_parent_host 1979 FOREIGN KEY (parent_host_id) 1980 REFERENCES icinga_host (id) 1981 ON DELETE RESTRICT 1982 ON UPDATE CASCADE, 1983 CONSTRAINT icinga_dependency_parent_service 1984 FOREIGN KEY (parent_service_id) 1985 REFERENCES icinga_service (id) 1986 ON DELETE RESTRICT 1987 ON UPDATE CASCADE, 1988 CONSTRAINT icinga_dependency_child_host 1989 FOREIGN KEY (child_host_id) 1990 REFERENCES icinga_host (id) 1991 ON DELETE CASCADE 1992 ON UPDATE CASCADE, 1993 CONSTRAINT icinga_dependency_child_service 1994 FOREIGN KEY (child_service_id) 1995 REFERENCES icinga_service (id) 1996 ON DELETE CASCADE 1997 ON UPDATE CASCADE, 1998 CONSTRAINT icinga_dependency_period 1999 FOREIGN KEY (period_id) 2000 REFERENCES icinga_timeperiod (id) 2001 ON DELETE RESTRICT 2002 ON UPDATE CASCADE, 2003 CONSTRAINT icinga_dependency_zone 2004 FOREIGN KEY (zone_id) 2005 REFERENCES icinga_zone (id) 2006 ON DELETE RESTRICT 2007 ON UPDATE CASCADE 2008); 2009 2010CREATE INDEX dependency_parent_host ON icinga_dependency (parent_host_id); 2011CREATE INDEX dependency_parent_service ON icinga_dependency (parent_service_id); 2012CREATE INDEX dependency_child_host ON icinga_dependency (child_host_id); 2013CREATE INDEX dependency_child_service ON icinga_dependency (child_service_id); 2014CREATE INDEX dependency_period ON icinga_dependency (period_id); 2015CREATE INDEX dependency_zone ON icinga_dependency (zone_id); 2016 2017 2018CREATE TABLE icinga_dependency_inheritance ( 2019 dependency_id integer NOT NULL, 2020 parent_dependency_id integer NOT NULL, 2021 weight integer DEFAULT NULL, 2022 PRIMARY KEY (dependency_id, parent_dependency_id), 2023 CONSTRAINT icinga_dependency_inheritance_dependency 2024 FOREIGN KEY (dependency_id) 2025 REFERENCES icinga_dependency (id) 2026 ON DELETE CASCADE 2027 ON UPDATE CASCADE, 2028 CONSTRAINT icinga_dependency_inheritance_parent_dependency 2029 FOREIGN KEY (parent_dependency_id) 2030 REFERENCES icinga_dependency (id) 2031 ON DELETE RESTRICT 2032 ON UPDATE CASCADE 2033); 2034 2035CREATE UNIQUE INDEX dependency_inheritance_unique_order ON icinga_dependency_inheritance (dependency_id, weight); 2036CREATE INDEX dependency_inheritance_dependency ON icinga_dependency_inheritance (dependency_id); 2037CREATE INDEX dependency_inheritance_dependency_parent ON icinga_dependency_inheritance (parent_dependency_id); 2038 2039 2040CREATE TABLE icinga_dependency_states_set ( 2041 dependency_id integer NOT NULL, 2042 property enum_state_name NOT NULL, 2043 merge_behaviour enum_set_merge_behaviour NOT NULL DEFAULT 'override', 2044 PRIMARY KEY (dependency_id, property, merge_behaviour), 2045 CONSTRAINT icinga_dependency_states_set_dependency 2046 FOREIGN KEY (dependency_id) 2047 REFERENCES icinga_dependency (id) 2048 ON DELETE CASCADE 2049 ON UPDATE CASCADE 2050); 2051 2052CREATE INDEX dependency_states_set_dependency ON icinga_dependency_states_set (dependency_id); 2053COMMENT ON COLUMN icinga_dependency_states_set.merge_behaviour IS 'override: = [], extend: += [], blacklist: -= []'; 2054 2055CREATE TABLE icinga_timeperiod_include ( 2056 timeperiod_id integer NOT NULL, 2057 include_id integer NOT NULL, 2058 PRIMARY KEY (timeperiod_id, include_id), 2059 CONSTRAINT icinga_timeperiod_timeperiod_include 2060 FOREIGN KEY (include_id) 2061 REFERENCES icinga_timeperiod (id) 2062 ON DELETE RESTRICT 2063 ON UPDATE CASCADE, 2064 CONSTRAINT icinga_timeperiod_include 2065 FOREIGN KEY (timeperiod_id) 2066 REFERENCES icinga_timeperiod (id) 2067 ON DELETE CASCADE 2068 ON UPDATE CASCADE 2069); 2070 2071CREATE TABLE icinga_timeperiod_exclude ( 2072 timeperiod_id integer NOT NULL, 2073 exclude_id integer NOT NULL, 2074 PRIMARY KEY (timeperiod_id, exclude_id), 2075 CONSTRAINT icinga_timeperiod_timeperiod_exclude 2076 FOREIGN KEY (exclude_id) 2077 REFERENCES icinga_timeperiod (id) 2078 ON DELETE RESTRICT 2079 ON UPDATE CASCADE, 2080 CONSTRAINT icinga_timeperiod_exclude 2081 FOREIGN KEY (timeperiod_id) 2082 REFERENCES icinga_timeperiod (id) 2083 ON DELETE CASCADE 2084 ON UPDATE CASCADE 2085); 2086 2087INSERT INTO director_schema_migration 2088 (schema_version, migration_time) 2089 VALUES (158, NOW()); 2090