1-- 2-- MySQL 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 10SET sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO'; 11 12CREATE TABLE director_activity_log ( 13 id BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL, 14 object_type VARCHAR(64) NOT NULL, 15 object_name VARCHAR(255) NOT NULL, 16 action_name ENUM('create', 'delete', 'modify') NOT NULL, 17 old_properties TEXT DEFAULT NULL COMMENT 'Property hash, JSON', 18 new_properties TEXT DEFAULT NULL COMMENT 'Property hash, JSON', 19 author VARCHAR(64) NOT NULL, 20 change_time DATETIME NOT NULL, 21 checksum VARBINARY(20) NOT NULL, 22 parent_checksum VARBINARY(20) DEFAULT NULL, 23 PRIMARY KEY (id), 24 INDEX sort_idx (change_time), 25 INDEX search_idx (object_name), 26 INDEX search_idx2 (object_type(32), object_name(64), change_time), 27 INDEX search_author (author), 28 INDEX checksum (checksum) 29) ENGINE=InnoDB DEFAULT CHARSET=utf8; 30 31CREATE TABLE director_basket ( 32 uuid VARBINARY(16) NOT NULL, 33 basket_name VARCHAR(64) NOT NULL, 34 owner_type ENUM( 35 'user', 36 'usergroup', 37 'role' 38 ) NOT NULL, 39 owner_value VARCHAR(255) NOT NULL, 40 objects MEDIUMTEXT NOT NULL, -- json-encoded 41 PRIMARY KEY (uuid), 42 UNIQUE INDEX basket_name (basket_name) 43) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin; 44 45CREATE TABLE director_basket_content ( 46 checksum VARBINARY(20) NOT NULL, 47 summary VARCHAR(500) NOT NULL, -- json 48 content MEDIUMTEXT NOT NULL, -- json 49 PRIMARY KEY (checksum) 50) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin; 51 52CREATE TABLE director_basket_snapshot ( 53 basket_uuid VARBINARY(16) NOT NULL, 54 ts_create BIGINT(20) NOT NULL, 55 content_checksum VARBINARY(20) NOT NULL, 56 PRIMARY KEY (basket_uuid, ts_create), 57 INDEX sort_idx (ts_create), 58 CONSTRAINT basked_snapshot_basket 59 FOREIGN KEY director_basket_snapshot (basket_uuid) 60 REFERENCES director_basket (uuid) 61 ON DELETE CASCADE 62 ON UPDATE RESTRICT, 63 CONSTRAINT basked_snapshot_content 64 FOREIGN KEY content_checksum (content_checksum) 65 REFERENCES director_basket_content (checksum) 66 ON DELETE RESTRICT 67 ON UPDATE RESTRICT 68) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin; 69 70CREATE TABLE director_generated_config ( 71 checksum VARBINARY(20) NOT NULL COMMENT 'SHA1(last_activity_checksum;file_path=checksum;file_path=checksum;...)', 72 director_version VARCHAR(64) DEFAULT NULL, 73 director_db_version INT(10) DEFAULT NULL, 74 duration INT(10) UNSIGNED DEFAULT NULL COMMENT 'Config generation duration (ms)', 75 first_activity_checksum VARBINARY(20) NOT NULL, 76 last_activity_checksum VARBINARY(20) NOT NULL, 77 PRIMARY KEY (checksum), 78 CONSTRAINT director_generated_config_activity 79 FOREIGN KEY activity_checksum (last_activity_checksum) 80 REFERENCES director_activity_log (checksum) 81 ON DELETE RESTRICT 82 ON UPDATE RESTRICT 83) ENGINE=InnoDB DEFAULT CHARSET=utf8; 84 85CREATE TABLE director_generated_file ( 86 checksum VARBINARY(20) NOT NULL COMMENT 'SHA1(content)', 87 content MEDIUMTEXT NOT NULL, 88 cnt_object INT(10) UNSIGNED NOT NULL DEFAULT 0, 89 cnt_template INT(10) UNSIGNED NOT NULL DEFAULT 0, 90 cnt_apply INT(10) UNSIGNED NOT NULL DEFAULT 0, 91 PRIMARY KEY (checksum) 92) ENGINE=InnoDB DEFAULT CHARSET=utf8; 93 94CREATE TABLE director_generated_config_file ( 95 config_checksum VARBINARY(20) NOT NULL, 96 file_checksum VARBINARY(20) NOT NULL, 97 file_path VARCHAR(128) NOT NULL COMMENT 'e.g. zones/nafta/hosts.conf', 98 CONSTRAINT director_generated_config_file_config 99 FOREIGN KEY config (config_checksum) 100 REFERENCES director_generated_config (checksum) 101 ON DELETE CASCADE 102 ON UPDATE CASCADE, 103 CONSTRAINT director_generated_config_file_file 104 FOREIGN KEY checksum (file_checksum) 105 REFERENCES director_generated_file (checksum) 106 ON DELETE RESTRICT 107 ON UPDATE RESTRICT, 108 PRIMARY KEY (config_checksum, file_path), 109 INDEX search_idx (file_checksum) 110) ENGINE=InnoDB DEFAULT CHARSET=utf8; 111 112CREATE TABLE director_deployment_log ( 113 id BIGINT(20) UNSIGNED AUTO_INCREMENT NOT NULL, 114 config_checksum VARBINARY(20) DEFAULT NULL, 115 last_activity_checksum VARBINARY(20) NOT NULL, 116 peer_identity VARCHAR(64) NOT NULL, 117 start_time DATETIME NOT NULL, 118 end_time DATETIME DEFAULT NULL, 119 abort_time DATETIME DEFAULT NULL, 120 duration_connection INT(10) UNSIGNED DEFAULT NULL 121 COMMENT 'The time it took to connect to an Icinga node (ms)', 122 duration_dump INT(10) UNSIGNED DEFAULT NULL 123 COMMENT 'Time spent dumping the config (ms)', 124 stage_name VARCHAR(96) DEFAULT NULL, 125 stage_collected ENUM('y', 'n') DEFAULT NULL, 126 connection_succeeded ENUM('y', 'n') DEFAULT NULL, 127 dump_succeeded ENUM('y', 'n') DEFAULT NULL, 128 startup_succeeded ENUM('y', 'n') DEFAULT NULL, 129 username VARCHAR(64) DEFAULT NULL COMMENT 'The user that triggered this deployment', 130 startup_log MEDIUMTEXT DEFAULT NULL, 131 PRIMARY KEY (id), 132 CONSTRAINT config_checksum 133 FOREIGN KEY config_checksum (config_checksum) 134 REFERENCES director_generated_config (checksum) 135 ON DELETE SET NULL 136 ON UPDATE RESTRICT 137) ENGINE=InnoDB DEFAULT CHARSET=utf8; 138 139CREATE TABLE director_datalist ( 140 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 141 list_name VARCHAR(255) NOT NULL, 142 owner VARCHAR(255) NOT NULL, 143 PRIMARY KEY (id), 144 UNIQUE KEY list_name (list_name) 145) ENGINE=InnoDB DEFAULT CHARSET=utf8; 146 147CREATE TABLE director_datalist_entry ( 148 list_id INT(10) UNSIGNED NOT NULL, 149 entry_name VARCHAR(255) COLLATE utf8_bin NOT NULL, 150 entry_value TEXT DEFAULT NULL, 151 format enum ('string', 'expression', 'json'), 152 allowed_roles VARCHAR(255) DEFAULT NULL, 153 PRIMARY KEY (list_id, entry_name), 154 CONSTRAINT director_datalist_value_datalist 155 FOREIGN KEY datalist (list_id) 156 REFERENCES director_datalist (id) 157 ON DELETE CASCADE 158 ON UPDATE CASCADE 159) ENGINE=InnoDB DEFAULT CHARSET=utf8; 160 161CREATE TABLE director_datafield ( 162 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 163 varname VARCHAR(64) NOT NULL COLLATE utf8_bin, 164 caption VARCHAR(255) NOT NULL, 165 description TEXT DEFAULT NULL, 166 datatype varchar(255) NOT NULL, 167-- datatype_param? multiple ones? 168 format enum ('string', 'json', 'expression'), 169 PRIMARY KEY (id), 170 KEY search_idx (varname) 171) ENGINE=InnoDB DEFAULT CHARSET=utf8; 172 173CREATE TABLE director_datafield_setting ( 174 datafield_id INT(10) UNSIGNED NOT NULL, 175 setting_name VARCHAR(64) NOT NULL, 176 setting_value TEXT NOT NULL, 177 PRIMARY KEY (datafield_id, setting_name), 178 CONSTRAINT datafield_id_settings 179 FOREIGN KEY datafield (datafield_id) 180 REFERENCES director_datafield (id) 181 ON DELETE CASCADE 182 ON UPDATE CASCADE 183) ENGINE=InnoDB DEFAULT CHARSET=utf8; 184 185CREATE TABLE director_schema_migration ( 186 schema_version SMALLINT UNSIGNED NOT NULL, 187 migration_time DATETIME NOT NULL, 188 PRIMARY KEY(schema_version) 189) ENGINE=InnoDB DEFAULT CHARSET=utf8; 190 191CREATE TABLE director_setting ( 192 setting_name VARCHAR(64) NOT NULL, 193 setting_value VARCHAR(255) NOT NULL, 194 PRIMARY KEY(setting_name) 195) ENGINE=InnoDB DEFAULT CHARSET=utf8; 196 197CREATE TABLE icinga_zone ( 198 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 199 parent_id INT(10) UNSIGNED DEFAULT NULL, 200 object_name VARCHAR(255) NOT NULL, 201 object_type ENUM('object', 'template', 'external_object') NOT NULL, 202 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 203 is_global ENUM('y', 'n') NOT NULL DEFAULT 'n', 204 PRIMARY KEY (id), 205 UNIQUE INDEX object_name (object_name), 206 CONSTRAINT icinga_zone_parent 207 FOREIGN KEY parent_zone (parent_id) 208 REFERENCES icinga_zone (id) 209 ON DELETE RESTRICT 210 ON UPDATE CASCADE 211) ENGINE=InnoDB DEFAULT CHARSET=utf8; 212 213CREATE TABLE icinga_zone_inheritance ( 214 zone_id INT(10) UNSIGNED NOT NULL, 215 parent_zone_id INT(10) UNSIGNED NOT NULL, 216 weight MEDIUMINT UNSIGNED DEFAULT NULL, 217 PRIMARY KEY (zone_id, parent_zone_id), 218 UNIQUE KEY unique_order (zone_id, weight), 219 CONSTRAINT icinga_zone_inheritance_zone 220 FOREIGN KEY zone (zone_id) 221 REFERENCES icinga_zone (id) 222 ON DELETE CASCADE 223 ON UPDATE CASCADE, 224 CONSTRAINT icinga_zone_inheritance_parent_zone 225 FOREIGN KEY zone (parent_zone_id) 226 REFERENCES icinga_zone (id) 227 ON DELETE RESTRICT 228 ON UPDATE CASCADE 229) ENGINE=InnoDB DEFAULT CHARSET=utf8; 230 231CREATE TABLE icinga_timeperiod ( 232 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 233 object_name VARCHAR(255) NOT NULL, 234 display_name VARCHAR(255) DEFAULT NULL, 235 update_method VARCHAR(64) DEFAULT NULL COMMENT 'Usually LegacyTimePeriod', 236 zone_id INT(10) UNSIGNED DEFAULT NULL, 237 object_type ENUM('object', 'template') NOT NULL, 238 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 239 prefer_includes ENUM('y', 'n') DEFAULT NULL, 240 PRIMARY KEY (id), 241 UNIQUE INDEX object_name (object_name, zone_id), 242 CONSTRAINT icinga_timeperiod_zone 243 FOREIGN KEY zone (zone_id) 244 REFERENCES icinga_zone (id) 245 ON DELETE RESTRICT 246 ON UPDATE CASCADE 247) ENGINE=InnoDB DEFAULT CHARSET=utf8; 248 249CREATE TABLE icinga_timeperiod_inheritance ( 250 timeperiod_id INT(10) UNSIGNED NOT NULL, 251 parent_timeperiod_id INT(10) UNSIGNED NOT NULL, 252 weight MEDIUMINT UNSIGNED DEFAULT NULL, 253 PRIMARY KEY (timeperiod_id, parent_timeperiod_id), 254 UNIQUE KEY unique_order (timeperiod_id, weight), 255 CONSTRAINT icinga_timeperiod_inheritance_timeperiod 256 FOREIGN KEY host (timeperiod_id) 257 REFERENCES icinga_timeperiod (id) 258 ON DELETE CASCADE 259 ON UPDATE CASCADE, 260 CONSTRAINT icinga_timeperiod_inheritance_parent_timeperiod 261 FOREIGN KEY host (parent_timeperiod_id) 262 REFERENCES icinga_timeperiod (id) 263 ON DELETE RESTRICT 264 ON UPDATE CASCADE 265) ENGINE=InnoDB DEFAULT CHARSET=utf8; 266 267CREATE TABLE icinga_timeperiod_range ( 268 timeperiod_id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 269 range_key VARCHAR(255) NOT NULL COMMENT 'monday, ...', 270 range_value VARCHAR(255) NOT NULL COMMENT '00:00-24:00, ...', 271 range_type ENUM('include', 'exclude') NOT NULL DEFAULT 'include' 272 COMMENT 'include -> ranges {}, exclude ranges_ignore {} - not yet', 273 merge_behaviour ENUM('set', 'add', 'substract') NOT NULL DEFAULT 'set' 274 COMMENT 'set -> = {}, add -> += {}, substract -> -= {}', 275 PRIMARY KEY (timeperiod_id, range_type, range_key), 276 CONSTRAINT icinga_timeperiod_range_timeperiod 277 FOREIGN KEY timeperiod (timeperiod_id) 278 REFERENCES icinga_timeperiod (id) 279 ON DELETE CASCADE 280 ON UPDATE CASCADE 281) ENGINE=InnoDB DEFAULT CHARSET=utf8; 282 283CREATE TABLE director_job ( 284 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 285 job_name VARCHAR(64) NOT NULL, 286 job_class VARCHAR(72) NOT NULL, 287 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 288 run_interval INT(10) UNSIGNED NOT NULL, -- seconds 289 timeperiod_id INT(10) UNSIGNED DEFAULT NULL, 290 last_attempt_succeeded ENUM('y', 'n') DEFAULT NULL, 291 ts_last_attempt DATETIME DEFAULT NULL, 292 ts_last_error DATETIME DEFAULT NULL, 293 last_error_message TEXT DEFAULT NULL, 294 PRIMARY KEY (id), 295 UNIQUE KEY (job_name), 296 CONSTRAINT director_job_period 297 FOREIGN KEY timeperiod (timeperiod_id) 298 REFERENCES icinga_timeperiod (id) 299 ON DELETE RESTRICT 300 ON UPDATE CASCADE 301) ENGINE=InnoDB DEFAULT CHARSET=utf8; 302 303CREATE TABLE director_job_setting ( 304 job_id INT UNSIGNED NOT NULL, 305 setting_name VARCHAR(64) NOT NULL, 306 setting_value TEXT DEFAULT NULL, 307 PRIMARY KEY (job_id, setting_name), 308 CONSTRAINT job_settings 309 FOREIGN KEY director_job (job_id) 310 REFERENCES director_job (id) 311 ON DELETE CASCADE 312 ON UPDATE CASCADE 313) ENGINE=InnoDB DEFAULT CHARSET=utf8; 314 315CREATE TABLE icinga_command ( 316 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 317 object_name VARCHAR(255) NOT NULL, 318 object_type ENUM('object', 'template', 'external_object') NOT NULL 319 COMMENT 'external_object is an attempt to work with existing commands', 320 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 321 methods_execute VARCHAR(64) DEFAULT NULL, 322 command TEXT DEFAULT NULL, 323 -- env text DEFAULT NULL, 324 -- vars text DEFAULT NULL, 325 timeout SMALLINT UNSIGNED DEFAULT NULL, 326 zone_id INT(10) UNSIGNED DEFAULT NULL, 327 PRIMARY KEY (id), 328 UNIQUE INDEX object_name (object_name), 329 CONSTRAINT icinga_command_zone 330 FOREIGN KEY zone (zone_id) 331 REFERENCES icinga_zone (id) 332 ON DELETE RESTRICT 333 ON UPDATE CASCADE 334) ENGINE=InnoDB DEFAULT CHARSET=utf8; 335 336CREATE TABLE icinga_command_inheritance ( 337 command_id INT(10) UNSIGNED NOT NULL, 338 parent_command_id INT(10) UNSIGNED NOT NULL, 339 weight MEDIUMINT UNSIGNED DEFAULT NULL, 340 PRIMARY KEY (command_id, parent_command_id), 341 UNIQUE KEY unique_order (command_id, weight), 342 CONSTRAINT icinga_command_inheritance_command 343 FOREIGN KEY command (command_id) 344 REFERENCES icinga_command (id) 345 ON DELETE CASCADE 346 ON UPDATE CASCADE, 347 CONSTRAINT icinga_command_inheritance_parent_command 348 FOREIGN KEY command (parent_command_id) 349 REFERENCES icinga_command (id) 350 ON DELETE RESTRICT 351 ON UPDATE CASCADE 352) ENGINE=InnoDB DEFAULT CHARSET=utf8; 353 354CREATE TABLE icinga_command_argument ( 355 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 356 command_id INT(10) UNSIGNED NOT NULL, 357 argument_name VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '-x, --host', 358 argument_value TEXT DEFAULT NULL, 359 argument_format ENUM('string', 'expression', 'json') NULL DEFAULT NULL, 360 key_string VARCHAR(64) DEFAULT NULL COMMENT 'Overrides name', 361 description TEXT DEFAULT NULL, 362 skip_key ENUM('y', 'n') DEFAULT NULL, 363 set_if VARCHAR(255) DEFAULT NULL, -- (string expression, must resolve to a numeric value) 364 set_if_format ENUM('string', 'expression', 'json') DEFAULT NULL, 365 sort_order SMALLINT DEFAULT NULL, -- -> order 366 repeat_key ENUM('y', 'n') DEFAULT NULL COMMENT 'Useful with array values', 367 required ENUM('y', 'n') DEFAULT NULL, 368 PRIMARY KEY (id), 369 UNIQUE KEY unique_idx (command_id, argument_name), 370 INDEX sort_idx (command_id, sort_order), 371 CONSTRAINT icinga_command_argument_command 372 FOREIGN KEY command (command_id) 373 REFERENCES icinga_command (id) 374 ON DELETE CASCADE 375 ON UPDATE CASCADE 376) ENGINE=InnoDB DEFAULT CHARSET=utf8; 377 378CREATE TABLE icinga_command_field ( 379 command_id INT(10) UNSIGNED NOT NULL, 380 datafield_id INT(10) UNSIGNED NOT NULL, 381 is_required ENUM('y', 'n') NOT NULL, 382 var_filter TEXT DEFAULT NULL, 383 PRIMARY KEY (command_id, datafield_id), 384 CONSTRAINT icinga_command_field_command 385 FOREIGN KEY command_id (command_id) 386 REFERENCES icinga_command (id) 387 ON DELETE CASCADE 388 ON UPDATE CASCADE, 389 CONSTRAINT icinga_command_field_datafield 390 FOREIGN KEY datafield(datafield_id) 391 REFERENCES director_datafield (id) 392 ON DELETE CASCADE 393 ON UPDATE CASCADE 394) ENGINE=InnoDB DEFAULT CHARSET=utf8; 395 396CREATE TABLE icinga_command_var ( 397 command_id INT(10) UNSIGNED NOT NULL, 398 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 399 varvalue TEXT DEFAULT NULL, 400 format ENUM('string', 'expression', 'json') NOT NULL DEFAULT 'string', 401 checksum VARBINARY(20) DEFAULT NULL, 402 PRIMARY KEY (command_id, varname), 403 INDEX search_idx (varname), 404 INDEX checksum (checksum), 405 CONSTRAINT icinga_command_var_command 406 FOREIGN KEY command (command_id) 407 REFERENCES icinga_command (id) 408 ON DELETE CASCADE 409 ON UPDATE CASCADE 410) ENGINE=InnoDB DEFAULT CHARSET=utf8; 411 412CREATE TABLE icinga_apiuser ( 413 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 414 object_name VARCHAR(255) NOT NULL, 415 object_type ENUM('object', 'template', 'external_object') NOT NULL, 416 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 417 password VARCHAR(255) DEFAULT NULL, 418 client_dn VARCHAR(64) DEFAULT NULL, 419 permissions TEXT DEFAULT NULL COMMENT 'JSON-encoded permissions', 420 PRIMARY KEY (id) 421) ENGINE=InnoDB DEFAULT CHARSET=utf8; 422 423CREATE TABLE icinga_endpoint ( 424 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 425 zone_id INT(10) UNSIGNED DEFAULT NULL, 426 object_name VARCHAR(255) NOT NULL, 427 object_type ENUM('object', 'template', 'external_object') NOT NULL, 428 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 429 host VARCHAR(255) DEFAULT NULL COMMENT 'IP address / hostname of remote node', 430 port SMALLINT UNSIGNED DEFAULT NULL COMMENT '5665 if not set', 431 log_duration VARCHAR(32) DEFAULT NULL COMMENT '1d if not set', 432 apiuser_id INT(10) UNSIGNED DEFAULT NULL, 433 PRIMARY KEY (id), 434 UNIQUE INDEX object_name (object_name), 435 CONSTRAINT icinga_endpoint_zone 436 FOREIGN KEY zone (zone_id) 437 REFERENCES icinga_zone (id) 438 ON DELETE RESTRICT 439 ON UPDATE CASCADE, 440 CONSTRAINT icinga_apiuser 441 FOREIGN KEY apiuser (apiuser_id) 442 REFERENCES icinga_apiuser (id) 443 ON DELETE RESTRICT 444 ON UPDATE CASCADE 445) ENGINE=InnoDB DEFAULT CHARSET=utf8; 446 447CREATE TABLE icinga_endpoint_inheritance ( 448 endpoint_id INT(10) UNSIGNED NOT NULL, 449 parent_endpoint_id INT(10) UNSIGNED NOT NULL, 450 weight MEDIUMINT UNSIGNED DEFAULT NULL, 451 PRIMARY KEY (endpoint_id, parent_endpoint_id), 452 UNIQUE KEY unique_order (endpoint_id, weight), 453 CONSTRAINT icinga_endpoint_inheritance_endpoint 454 FOREIGN KEY endpoint (endpoint_id) 455 REFERENCES icinga_endpoint (id) 456 ON DELETE CASCADE 457 ON UPDATE CASCADE, 458 CONSTRAINT icinga_endpoint_inheritance_parent_endpoint 459 FOREIGN KEY endpoint (parent_endpoint_id) 460 REFERENCES icinga_endpoint (id) 461 ON DELETE RESTRICT 462 ON UPDATE CASCADE 463) ENGINE=InnoDB DEFAULT CHARSET=utf8; 464 465CREATE TABLE icinga_host_template_choice ( 466 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 467 object_name VARCHAR(64) NOT NULL, 468 description TEXT DEFAULT NULL, 469 min_required SMALLINT UNSIGNED NOT NULL DEFAULT 0, 470 max_allowed SMALLINT UNSIGNED NOT NULL DEFAULT 1, 471 required_template_id INT(10) UNSIGNED DEFAULT NULL, 472 allowed_roles VARCHAR(255) DEFAULT NULL, 473 PRIMARY KEY (id), 474 UNIQUE KEY (object_name) 475) ENGINE=InnoDB DEFAULT CHARSET=utf8; 476 477CREATE TABLE icinga_host ( 478 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 479 object_name VARCHAR(255) NOT NULL, 480 object_type ENUM('object', 'template') NOT NULL, 481 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 482 display_name VARCHAR(255) DEFAULT NULL, 483 address VARCHAR(64) DEFAULT NULL, 484 address6 VARCHAR(45) DEFAULT NULL, 485 check_command_id INT(10) UNSIGNED DEFAULT NULL, 486 max_check_attempts MEDIUMINT UNSIGNED DEFAULT NULL, 487 check_period_id INT(10) UNSIGNED DEFAULT NULL, 488 check_interval VARCHAR(8) DEFAULT NULL, 489 retry_interval VARCHAR(8) DEFAULT NULL, 490 check_timeout SMALLINT UNSIGNED DEFAULT NULL, 491 enable_notifications ENUM('y', 'n') DEFAULT NULL, 492 enable_active_checks ENUM('y', 'n') DEFAULT NULL, 493 enable_passive_checks ENUM('y', 'n') DEFAULT NULL, 494 enable_event_handler ENUM('y', 'n') DEFAULT NULL, 495 enable_flapping ENUM('y', 'n') DEFAULT NULL, 496 enable_perfdata ENUM('y', 'n') DEFAULT NULL, 497 event_command_id INT(10) UNSIGNED DEFAULT NULL, 498 flapping_threshold_high SMALLINT UNSIGNED default null, 499 flapping_threshold_low SMALLINT UNSIGNED default null, 500 volatile ENUM('y', 'n') DEFAULT NULL, 501 zone_id INT(10) UNSIGNED DEFAULT NULL, 502 command_endpoint_id INT(10) UNSIGNED DEFAULT NULL, 503 notes TEXT DEFAULT NULL, 504 notes_url VARCHAR(255) DEFAULT NULL, 505 action_url VARCHAR(255) DEFAULT NULL, 506 icon_image VARCHAR(255) DEFAULT NULL, 507 icon_image_alt VARCHAR(255) DEFAULT NULL, 508 has_agent ENUM('y', 'n') DEFAULT NULL, 509 master_should_connect ENUM('y', 'n') DEFAULT NULL, 510 accept_config ENUM('y', 'n') DEFAULT NULL, 511 api_key VARCHAR(40) DEFAULT NULL, 512 template_choice_id INT(10) UNSIGNED DEFAULT NULL, 513 PRIMARY KEY (id), 514 UNIQUE INDEX object_name (object_name), 515 UNIQUE INDEX api_key (api_key), 516 KEY search_idx (display_name), 517 CONSTRAINT icinga_host_zone 518 FOREIGN KEY zone (zone_id) 519 REFERENCES icinga_zone (id) 520 ON DELETE RESTRICT 521 ON UPDATE CASCADE, 522 CONSTRAINT icinga_host_check_period 523 FOREIGN KEY timeperiod (check_period_id) 524 REFERENCES icinga_timeperiod (id) 525 ON DELETE RESTRICT 526 ON UPDATE CASCADE, 527 CONSTRAINT icinga_host_check_command 528 FOREIGN KEY check_command (check_command_id) 529 REFERENCES icinga_command (id) 530 ON DELETE RESTRICT 531 ON UPDATE CASCADE, 532 CONSTRAINT icinga_host_event_command 533 FOREIGN KEY event_command (event_command_id) 534 REFERENCES icinga_command (id) 535 ON DELETE RESTRICT 536 ON UPDATE CASCADE, 537 CONSTRAINT icinga_host_command_endpoint 538 FOREIGN KEY command_endpoint (command_endpoint_id) 539 REFERENCES icinga_endpoint (id) 540 ON DELETE RESTRICT 541 ON UPDATE CASCADE, 542 CONSTRAINT icinga_host_template_choice 543 FOREIGN KEY choice (template_choice_id) 544 REFERENCES icinga_host_template_choice (id) 545 ON DELETE SET NULL 546 ON UPDATE CASCADE 547) ENGINE=InnoDB DEFAULT CHARSET=utf8; 548 549CREATE TABLE icinga_host_inheritance ( 550 host_id INT(10) UNSIGNED NOT NULL, 551 parent_host_id INT(10) UNSIGNED NOT NULL, 552 weight MEDIUMINT UNSIGNED DEFAULT NULL, 553 PRIMARY KEY (host_id, parent_host_id), 554 UNIQUE KEY unique_order (host_id, weight), 555 CONSTRAINT icinga_host_inheritance_host 556 FOREIGN KEY host (host_id) 557 REFERENCES icinga_host (id) 558 ON DELETE CASCADE 559 ON UPDATE CASCADE, 560 CONSTRAINT icinga_host_inheritance_parent_host 561 FOREIGN KEY host (parent_host_id) 562 REFERENCES icinga_host (id) 563 ON DELETE RESTRICT 564 ON UPDATE CASCADE 565) ENGINE=InnoDB DEFAULT CHARSET=utf8; 566 567CREATE TABLE icinga_host_field ( 568 host_id INT(10) UNSIGNED NOT NULL COMMENT 'Makes only sense for templates', 569 datafield_id INT(10) UNSIGNED NOT NULL, 570 is_required ENUM('y', 'n') NOT NULL, 571 var_filter TEXT DEFAULT NULL, 572 PRIMARY KEY (host_id, datafield_id), 573 CONSTRAINT icinga_host_field_host 574 FOREIGN KEY host(host_id) 575 REFERENCES icinga_host (id) 576 ON DELETE CASCADE 577 ON UPDATE CASCADE, 578 CONSTRAINT icinga_host_field_datafield 579 FOREIGN KEY datafield(datafield_id) 580 REFERENCES director_datafield (id) 581 ON DELETE CASCADE 582 ON UPDATE CASCADE 583) ENGINE=InnoDB DEFAULT CHARSET=utf8; 584 585CREATE TABLE icinga_host_var ( 586 host_id INT(10) UNSIGNED NOT NULL, 587 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 588 varvalue TEXT DEFAULT NULL, 589 format enum ('string', 'json', 'expression'), -- immer string vorerst 590 checksum VARBINARY(20) DEFAULT NULL, 591 PRIMARY KEY (host_id, varname), 592 INDEX search_idx (varname), 593 INDEX checksum (checksum), 594 CONSTRAINT icinga_host_var_host 595 FOREIGN KEY host (host_id) 596 REFERENCES icinga_host (id) 597 ON DELETE CASCADE 598 ON UPDATE CASCADE 599) ENGINE=InnoDB DEFAULT CHARSET=utf8; 600 601ALTER TABLE icinga_host_template_choice 602 ADD CONSTRAINT host_template_choice_required_template 603 FOREIGN KEY required_template (required_template_id) 604 REFERENCES icinga_host (id) 605 ON DELETE RESTRICT 606 ON UPDATE CASCADE; 607 608CREATE TABLE icinga_service_set ( 609 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 610 object_name VARCHAR(128) NOT NULL, 611 object_type ENUM('object', 'template', 'external_object') NOT NULL, 612 host_id INT(10) UNSIGNED DEFAULT NULL, 613 description TEXT DEFAULT NULL, 614 assign_filter TEXT DEFAULT NULL, 615 PRIMARY KEY (id), 616 UNIQUE KEY object_key (object_name, host_id), 617 CONSTRAINT icinga_service_set_host 618 FOREIGN KEY host (host_id) 619 REFERENCES icinga_host (id) 620 ON DELETE CASCADE 621 ON UPDATE CASCADE 622) ENGINE=InnoDB DEFAULT CHARSET=utf8; 623 624CREATE TABLE icinga_service_template_choice ( 625 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 626 object_name VARCHAR(64) NOT NULL, 627 description TEXT DEFAULT NULL, 628 min_required SMALLINT UNSIGNED NOT NULL DEFAULT 0, 629 max_allowed SMALLINT UNSIGNED NOT NULL DEFAULT 1, 630 required_template_id INT(10) UNSIGNED DEFAULT NULL, 631 allowed_roles VARCHAR(255) DEFAULT NULL, 632 PRIMARY KEY (id), 633 UNIQUE KEY (object_name) 634) ENGINE=InnoDB DEFAULT CHARSET=utf8; 635 636CREATE TABLE icinga_service ( 637 id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 638 object_name VARCHAR(255) NOT NULL, 639 object_type ENUM('object', 'template', 'apply') NOT NULL, 640 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 641 display_name VARCHAR(255) DEFAULT NULL, 642 host_id INT(10) UNSIGNED DEFAULT NULL, 643 service_set_id INT(10) UNSIGNED DEFAULT NULL, 644 check_command_id INT(10) UNSIGNED DEFAULT NULL, 645 max_check_attempts MEDIUMINT UNSIGNED DEFAULT NULL, 646 check_period_id INT(10) UNSIGNED DEFAULT NULL, 647 check_interval VARCHAR(8) DEFAULT NULL, 648 retry_interval VARCHAR(8) DEFAULT NULL, 649 check_timeout SMALLINT UNSIGNED DEFAULT NULL, 650 enable_notifications ENUM('y', 'n') DEFAULT NULL, 651 enable_active_checks ENUM('y', 'n') DEFAULT NULL, 652 enable_passive_checks ENUM('y', 'n') DEFAULT NULL, 653 enable_event_handler ENUM('y', 'n') DEFAULT NULL, 654 enable_flapping ENUM('y', 'n') DEFAULT NULL, 655 enable_perfdata ENUM('y', 'n') DEFAULT NULL, 656 event_command_id INT(10) UNSIGNED DEFAULT NULL, 657 flapping_threshold_high SMALLINT UNSIGNED DEFAULT NULL, 658 flapping_threshold_low SMALLINT UNSIGNED DEFAULT NULL, 659 volatile ENUM('y', 'n') DEFAULT NULL, 660 zone_id INT(10) UNSIGNED DEFAULT NULL, 661 command_endpoint_id INT(10) UNSIGNED DEFAULT NULL, 662 notes TEXT DEFAULT NULL, 663 notes_url VARCHAR(255) DEFAULT NULL, 664 action_url VARCHAR(255) DEFAULT NULL, 665 icon_image VARCHAR(255) DEFAULT NULL, 666 icon_image_alt VARCHAR(255) DEFAULT NULL, 667 use_agent ENUM('y', 'n') DEFAULT NULL, 668 apply_for VARCHAR(255) DEFAULT NULL, 669 use_var_overrides ENUM('y', 'n') DEFAULT NULL, 670 assign_filter TEXT DEFAULT NULL, 671 template_choice_id INT(10) UNSIGNED DEFAULT NULL, 672 PRIMARY KEY (id), 673 UNIQUE KEY object_key (object_name, host_id), 674 CONSTRAINT icinga_service_host 675 FOREIGN KEY host (host_id) 676 REFERENCES icinga_host (id) 677 ON DELETE CASCADE 678 ON UPDATE CASCADE, 679 CONSTRAINT icinga_service_zone 680 FOREIGN KEY zone (zone_id) 681 REFERENCES icinga_zone (id) 682 ON DELETE RESTRICT 683 ON UPDATE CASCADE, 684 CONSTRAINT icinga_service_check_period 685 FOREIGN KEY timeperiod (check_period_id) 686 REFERENCES icinga_timeperiod (id) 687 ON DELETE RESTRICT 688 ON UPDATE CASCADE, 689 CONSTRAINT icinga_service_check_command 690 FOREIGN KEY check_command (check_command_id) 691 REFERENCES icinga_command (id) 692 ON DELETE RESTRICT 693 ON UPDATE CASCADE, 694 CONSTRAINT icinga_service_event_command 695 FOREIGN KEY event_command (event_command_id) 696 REFERENCES icinga_command (id) 697 ON DELETE RESTRICT 698 ON UPDATE CASCADE, 699 CONSTRAINT icinga_service_command_endpoint 700 FOREIGN KEY command_endpoint (command_endpoint_id) 701 REFERENCES icinga_endpoint (id) 702 ON DELETE RESTRICT 703 ON UPDATE CASCADE, 704 CONSTRAINT icinga_service_service_set 705 FOREIGN KEY service_set (service_set_id) 706 REFERENCES icinga_service_set (id) 707 ON DELETE CASCADE 708 ON UPDATE CASCADE, 709 CONSTRAINT icinga_service_template_choice 710 FOREIGN KEY choice (template_choice_id) 711 REFERENCES icinga_service_template_choice (id) 712 ON DELETE SET NULL 713 ON UPDATE CASCADE 714) ENGINE=InnoDB DEFAULT CHARSET=utf8; 715 716CREATE TABLE icinga_service_inheritance ( 717 service_id INT(10) UNSIGNED NOT NULL, 718 parent_service_id INT(10) UNSIGNED NOT NULL, 719 weight MEDIUMINT UNSIGNED DEFAULT NULL, 720 PRIMARY KEY (service_id, parent_service_id), 721 UNIQUE KEY unique_order (service_id, weight), 722 CONSTRAINT icinga_service_inheritance_service 723 FOREIGN KEY host (service_id) 724 REFERENCES icinga_service (id) 725 ON DELETE CASCADE 726 ON UPDATE CASCADE, 727 CONSTRAINT icinga_service_inheritance_parent_service 728 FOREIGN KEY host (parent_service_id) 729 REFERENCES icinga_service (id) 730 ON DELETE RESTRICT 731 ON UPDATE CASCADE 732) ENGINE=InnoDB DEFAULT CHARSET=utf8; 733 734CREATE TABLE icinga_service_var ( 735 service_id INT(10) UNSIGNED NOT NULL, 736 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 737 varvalue TEXT DEFAULT NULL, 738 format enum ('string', 'json', 'expression'), 739 checksum VARBINARY(20) DEFAULT NULL, 740 PRIMARY KEY (service_id, varname), 741 INDEX search_idx (varname), 742 INDEX checksum (checksum), 743 CONSTRAINT icinga_service_var_service 744 FOREIGN KEY service (service_id) 745 REFERENCES icinga_service (id) 746 ON DELETE CASCADE 747 ON UPDATE CASCADE 748) ENGINE=InnoDB DEFAULT CHARSET=utf8; 749 750CREATE TABLE icinga_service_field ( 751 service_id INT(10) UNSIGNED NOT NULL COMMENT 'Makes only sense for templates', 752 datafield_id INT(10) UNSIGNED NOT NULL, 753 is_required ENUM('y', 'n') NOT NULL, 754 var_filter TEXT DEFAULT NULL, 755 PRIMARY KEY (service_id, datafield_id), 756 CONSTRAINT icinga_service_field_service 757 FOREIGN KEY service(service_id) 758 REFERENCES icinga_service (id) 759 ON DELETE CASCADE 760 ON UPDATE CASCADE, 761 CONSTRAINT icinga_service_field_datafield 762 FOREIGN KEY datafield(datafield_id) 763 REFERENCES director_datafield (id) 764 ON DELETE CASCADE 765 ON UPDATE CASCADE 766) ENGINE=InnoDB DEFAULT CHARSET=utf8; 767 768ALTER TABLE icinga_service_template_choice 769 ADD CONSTRAINT service_template_choice_required_template 770 FOREIGN KEY required_template (required_template_id) 771 REFERENCES icinga_service (id) 772 ON DELETE RESTRICT 773 ON UPDATE CASCADE; 774 775CREATE TABLE icinga_host_service ( 776 host_id INT(10) UNSIGNED NOT NULL, 777 service_id INT(10) UNSIGNED NOT NULL, 778 PRIMARY KEY (host_id, service_id), 779 CONSTRAINT icinga_host_service_host 780 FOREIGN KEY host (host_id) 781 REFERENCES icinga_host (id) 782 ON DELETE CASCADE 783 ON UPDATE CASCADE, 784 CONSTRAINT icinga_host_service_service 785 FOREIGN KEY service (service_id) 786 REFERENCES icinga_service (id) 787 ON DELETE CASCADE 788 ON UPDATE CASCADE 789) ENGINE=InnoDB; 790 791CREATE TABLE icinga_host_service_blacklist ( 792 host_id INT(10) UNSIGNED NOT NULL, 793 service_id INT(10) UNSIGNED NOT NULL, 794 PRIMARY KEY (host_id, service_id), 795 CONSTRAINT icinga_host_service_bl_host 796 FOREIGN KEY host (host_id) 797 REFERENCES icinga_host (id) 798 ON DELETE CASCADE 799 ON UPDATE CASCADE, 800 CONSTRAINT icinga_host_service_bl_service 801 FOREIGN KEY service (service_id) 802 REFERENCES icinga_service (id) 803 ON DELETE CASCADE 804 ON UPDATE CASCADE 805) ENGINE=InnoDB; 806 807CREATE TABLE icinga_service_set_inheritance ( 808 service_set_id INT(10) UNSIGNED NOT NULL, 809 parent_service_set_id INT(10) UNSIGNED NOT NULL, 810 weight MEDIUMINT UNSIGNED DEFAULT NULL, 811 PRIMARY KEY (service_set_id, parent_service_set_id), 812 UNIQUE KEY unique_order (service_set_id, weight), 813 CONSTRAINT icinga_service_set_inheritance_set 814 FOREIGN KEY host (service_set_id) 815 REFERENCES icinga_service_set (id) 816 ON DELETE CASCADE 817 ON UPDATE CASCADE, 818 CONSTRAINT icinga_service_set_inheritance_parent 819 FOREIGN KEY host (parent_service_set_id) 820 REFERENCES icinga_service_set (id) 821 ON DELETE RESTRICT 822 ON UPDATE CASCADE 823) ENGINE=InnoDB DEFAULT CHARSET=utf8; 824 825CREATE TABLE icinga_service_set_var ( 826 service_set_id INT(10) UNSIGNED NOT NULL, 827 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 828 varvalue TEXT DEFAULT NULL, 829 format ENUM('string', 'expression', 'json') NOT NULL DEFAULT 'string', 830 checksum VARBINARY(20) DEFAULT NULL, 831 PRIMARY KEY (service_set_id, varname), 832 INDEX search_idx (varname), 833 INDEX checksum (checksum), 834 CONSTRAINT icinga_service_set_var_service 835 FOREIGN KEY command (service_set_id) 836 REFERENCES icinga_service_set (id) 837 ON DELETE CASCADE 838 ON UPDATE CASCADE 839) ENGINE=InnoDB DEFAULT CHARSET=utf8; 840 841CREATE TABLE icinga_hostgroup ( 842 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 843 object_name VARCHAR(255) NOT NULL, 844 object_type ENUM('object', 'template', 'external_object') NOT NULL, 845 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 846 display_name VARCHAR(255) DEFAULT NULL, 847 assign_filter TEXT DEFAULT NULL, 848 PRIMARY KEY (id), 849 UNIQUE INDEX object_name (object_name), 850 KEY search_idx (display_name) 851) ENGINE=InnoDB DEFAULT CHARSET=utf8; 852 853-- TODO: probably useless 854CREATE TABLE icinga_hostgroup_inheritance ( 855 hostgroup_id INT(10) UNSIGNED NOT NULL, 856 parent_hostgroup_id INT(10) UNSIGNED NOT NULL, 857 weight MEDIUMINT UNSIGNED DEFAULT NULL, 858 PRIMARY KEY (hostgroup_id, parent_hostgroup_id), 859 UNIQUE KEY unique_order (hostgroup_id, weight), 860 CONSTRAINT icinga_hostgroup_inheritance_hostgroup 861 FOREIGN KEY host (hostgroup_id) 862 REFERENCES icinga_hostgroup (id) 863 ON DELETE CASCADE 864 ON UPDATE CASCADE, 865 CONSTRAINT icinga_hostgroup_inheritance_parent_hostgroup 866 FOREIGN KEY host (parent_hostgroup_id) 867 REFERENCES icinga_hostgroup (id) 868 ON DELETE RESTRICT 869 ON UPDATE CASCADE 870) ENGINE=InnoDB DEFAULT CHARSET=utf8; 871 872CREATE TABLE icinga_servicegroup ( 873 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 874 object_name VARCHAR(255) DEFAULT NULL, 875 object_type ENUM('object', 'template') NOT NULL, 876 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 877 display_name VARCHAR(255) DEFAULT NULL, 878 assign_filter TEXT DEFAULT NULL, 879 PRIMARY KEY (id), 880 UNIQUE INDEX object_name (object_name), 881 KEY search_idx (display_name) 882) ENGINE=InnoDB DEFAULT CHARSET=utf8; 883 884CREATE TABLE icinga_servicegroup_inheritance ( 885 servicegroup_id INT(10) UNSIGNED NOT NULL, 886 parent_servicegroup_id INT(10) UNSIGNED NOT NULL, 887 weight MEDIUMINT UNSIGNED DEFAULT NULL, 888 PRIMARY KEY (servicegroup_id, parent_servicegroup_id), 889 UNIQUE KEY unique_order (servicegroup_id, weight), 890 CONSTRAINT icinga_servicegroup_inheritance_servicegroup 891 FOREIGN KEY host (servicegroup_id) 892 REFERENCES icinga_servicegroup (id) 893 ON DELETE CASCADE 894 ON UPDATE CASCADE, 895 CONSTRAINT icinga_servicegroup_inheritance_parent_servicegroup 896 FOREIGN KEY host (parent_servicegroup_id) 897 REFERENCES icinga_servicegroup (id) 898 ON DELETE RESTRICT 899 ON UPDATE CASCADE 900) ENGINE=InnoDB DEFAULT CHARSET=utf8; 901 902CREATE TABLE icinga_servicegroup_service ( 903 servicegroup_id INT(10) UNSIGNED NOT NULL, 904 service_id INT(10) UNSIGNED NOT NULL, 905 PRIMARY KEY (servicegroup_id, service_id), 906 CONSTRAINT icinga_servicegroup_service_service 907 FOREIGN KEY service (service_id) 908 REFERENCES icinga_service (id) 909 ON DELETE CASCADE 910 ON UPDATE CASCADE, 911 CONSTRAINT icinga_servicegroup_service_servicegroup 912 FOREIGN KEY servicegroup (servicegroup_id) 913 REFERENCES icinga_servicegroup (id) 914 ON DELETE CASCADE 915 ON UPDATE CASCADE 916) ENGINE=InnoDB; 917 918CREATE TABLE icinga_servicegroup_service_resolved ( 919 servicegroup_id INT(10) UNSIGNED NOT NULL, 920 service_id INT(10) UNSIGNED NOT NULL, 921 PRIMARY KEY (servicegroup_id, service_id), 922 CONSTRAINT icinga_servicegroup_service_resolved_service 923 FOREIGN KEY service (service_id) 924 REFERENCES icinga_service (id) 925 ON DELETE CASCADE 926 ON UPDATE CASCADE, 927 CONSTRAINT icinga_servicegroup_service_resolved_servicegroup 928 FOREIGN KEY servicegroup (servicegroup_id) 929 REFERENCES icinga_servicegroup (id) 930 ON DELETE CASCADE 931 ON UPDATE CASCADE 932) ENGINE=InnoDB DEFAULT CHARSET=utf8; 933 934CREATE TABLE icinga_hostgroup_host ( 935 hostgroup_id INT(10) UNSIGNED NOT NULL, 936 host_id INT(10) UNSIGNED NOT NULL, 937 PRIMARY KEY (hostgroup_id, host_id), 938 CONSTRAINT icinga_hostgroup_host_host 939 FOREIGN KEY host (host_id) 940 REFERENCES icinga_host (id) 941 ON DELETE CASCADE 942 ON UPDATE CASCADE, 943 CONSTRAINT icinga_hostgroup_host_hostgroup 944 FOREIGN KEY hostgroup (hostgroup_id) 945 REFERENCES icinga_hostgroup (id) 946 ON DELETE CASCADE 947 ON UPDATE CASCADE 948) ENGINE=InnoDB DEFAULT CHARSET=utf8; 949 950CREATE TABLE icinga_hostgroup_host_resolved ( 951 hostgroup_id INT(10) UNSIGNED NOT NULL, 952 host_id INT(10) UNSIGNED NOT NULL, 953 PRIMARY KEY (hostgroup_id, host_id), 954 CONSTRAINT icinga_hostgroup_host_resolved_host 955 FOREIGN KEY host (host_id) 956 REFERENCES icinga_host (id) 957 ON DELETE CASCADE 958 ON UPDATE CASCADE, 959 CONSTRAINT icinga_hostgroup_host_resolved_hostgroup 960 FOREIGN KEY hostgroup (hostgroup_id) 961 REFERENCES icinga_hostgroup (id) 962 ON DELETE CASCADE 963 ON UPDATE CASCADE 964) ENGINE=InnoDB DEFAULT CHARSET=utf8; 965 966CREATE TABLE icinga_hostgroup_parent ( 967 hostgroup_id INT(10) UNSIGNED NOT NULL, 968 parent_hostgroup_id INT(10) UNSIGNED NOT NULL, 969 PRIMARY KEY (hostgroup_id, parent_hostgroup_id), 970 CONSTRAINT icinga_hostgroup_parent_hostgroup 971 FOREIGN KEY hostgroup (hostgroup_id) 972 REFERENCES icinga_hostgroup (id) 973 ON DELETE CASCADE 974 ON UPDATE CASCADE, 975 CONSTRAINT icinga_hostgroup_parent_parent 976 FOREIGN KEY parent (parent_hostgroup_id) 977 REFERENCES icinga_hostgroup (id) 978 ON DELETE RESTRICT 979 ON UPDATE CASCADE 980) ENGINE=InnoDB DEFAULT CHARSET=utf8; 981 982CREATE TABLE icinga_user ( 983 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 984 object_name VARCHAR(255) DEFAULT NULL, 985 object_type ENUM('object', 'template') NOT NULL, 986 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 987 display_name VARCHAR(255) DEFAULT NULL, 988 email VARCHAR(255) DEFAULT NULL, 989 pager VARCHAR(255) DEFAULT NULL, 990 enable_notifications ENUM('y', 'n') DEFAULT NULL, 991 period_id INT(10) UNSIGNED DEFAULT NULL, 992 zone_id INT(10) UNSIGNED DEFAULT NULL, 993 PRIMARY KEY (id), 994 UNIQUE INDEX object_name (object_name, zone_id), 995 CONSTRAINT icinga_user_zone 996 FOREIGN KEY zone (zone_id) 997 REFERENCES icinga_zone (id) 998 ON DELETE RESTRICT 999 ON UPDATE CASCADE, 1000 CONSTRAINT icinga_user_period 1001 FOREIGN KEY period (period_id) 1002 REFERENCES icinga_timeperiod (id) 1003 ON DELETE RESTRICT 1004 ON UPDATE CASCADE 1005) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1006 1007CREATE TABLE icinga_user_inheritance ( 1008 user_id INT(10) UNSIGNED NOT NULL, 1009 parent_user_id INT(10) UNSIGNED NOT NULL, 1010 weight MEDIUMINT UNSIGNED DEFAULT NULL, 1011 PRIMARY KEY (user_id, parent_user_id), 1012 UNIQUE KEY unique_order (user_id, weight), 1013 CONSTRAINT icinga_user_inheritance_user 1014 FOREIGN KEY host (user_id) 1015 REFERENCES icinga_user (id) 1016 ON DELETE CASCADE 1017 ON UPDATE CASCADE, 1018 CONSTRAINT icinga_user_inheritance_parent_user 1019 FOREIGN KEY host (parent_user_id) 1020 REFERENCES icinga_user (id) 1021 ON DELETE RESTRICT 1022 ON UPDATE CASCADE 1023) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1024 1025CREATE TABLE icinga_user_states_set ( 1026 user_id INT(10) UNSIGNED NOT NULL, 1027 property ENUM( 1028 'OK', 1029 'Warning', 1030 'Critical', 1031 'Unknown', 1032 'Up', 1033 'Down' 1034 ) NOT NULL, 1035 merge_behaviour ENUM('override', 'extend', 'blacklist') NOT NULL DEFAULT 'override' 1036 COMMENT 'override: = [], extend: += [], blacklist: -= []', 1037 PRIMARY KEY (user_id, property, merge_behaviour), 1038 CONSTRAINT icinga_user_states_set_user 1039 FOREIGN KEY icinga_user (user_id) 1040 REFERENCES icinga_user (id) 1041 ON DELETE CASCADE 1042 ON UPDATE CASCADE 1043) ENGINE=InnoDB; 1044 1045CREATE TABLE icinga_user_types_set ( 1046 user_id INT(10) UNSIGNED NOT NULL, 1047 property ENUM( 1048 'DowntimeStart', 1049 'DowntimeEnd', 1050 'DowntimeRemoved', 1051 'Custom', 1052 'Acknowledgement', 1053 'Problem', 1054 'Recovery', 1055 'FlappingStart', 1056 'FlappingEnd' 1057 ) NOT NULL, 1058 merge_behaviour ENUM('override', 'extend', 'blacklist') NOT NULL DEFAULT 'override' 1059 COMMENT 'override: = [], extend: += [], blacklist: -= []', 1060 PRIMARY KEY (user_id, property, merge_behaviour), 1061 CONSTRAINT icinga_user_types_set_user 1062 FOREIGN KEY icinga_user (user_id) 1063 REFERENCES icinga_user (id) 1064 ON DELETE CASCADE 1065 ON UPDATE CASCADE 1066) ENGINE=InnoDB; 1067 1068CREATE TABLE icinga_user_var ( 1069 user_id INT(10) UNSIGNED NOT NULL, 1070 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1071 varvalue TEXT DEFAULT NULL, 1072 format ENUM('string', 'json', 'expression') NOT NULL DEFAULT 'string', 1073 checksum VARBINARY(20) DEFAULT NULL, 1074 PRIMARY KEY (user_id, varname), 1075 INDEX search_idx (varname), 1076 INDEX checksum (checksum), 1077 CONSTRAINT icinga_user_var_user 1078 FOREIGN KEY icinga_user (user_id) 1079 REFERENCES icinga_user (id) 1080 ON DELETE CASCADE 1081 ON UPDATE CASCADE 1082) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1083 1084CREATE TABLE icinga_user_field ( 1085 user_id INT(10) UNSIGNED NOT NULL COMMENT 'Makes only sense for templates', 1086 datafield_id INT(10) UNSIGNED NOT NULL, 1087 is_required ENUM('y', 'n') NOT NULL, 1088 var_filter TEXT DEFAULT NULL, 1089 PRIMARY KEY (user_id, datafield_id), 1090 CONSTRAINT icinga_user_field_user 1091 FOREIGN KEY user(user_id) 1092 REFERENCES icinga_user (id) 1093 ON DELETE CASCADE 1094 ON UPDATE CASCADE, 1095 CONSTRAINT icinga_user_field_datafield 1096 FOREIGN KEY datafield(datafield_id) 1097 REFERENCES director_datafield (id) 1098 ON DELETE CASCADE 1099 ON UPDATE CASCADE 1100) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1101 1102CREATE TABLE icinga_usergroup ( 1103 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1104 object_name VARCHAR(255) NOT NULL, 1105 object_type ENUM('object', 'template') NOT NULL, 1106 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 1107 display_name VARCHAR(255) DEFAULT NULL, 1108 zone_id INT(10) UNSIGNED DEFAULT NULL, 1109 PRIMARY KEY (id), 1110 UNIQUE INDEX object_name (object_name), 1111 KEY search_idx (display_name), 1112 CONSTRAINT icinga_usergroup_zone 1113 FOREIGN KEY zone (zone_id) 1114 REFERENCES icinga_zone (id) 1115 ON DELETE RESTRICT 1116 ON UPDATE CASCADE 1117) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1118 1119CREATE TABLE icinga_usergroup_inheritance ( 1120 usergroup_id INT(10) UNSIGNED NOT NULL, 1121 parent_usergroup_id INT(10) UNSIGNED NOT NULL, 1122 weight MEDIUMINT UNSIGNED DEFAULT NULL, 1123 PRIMARY KEY (usergroup_id, parent_usergroup_id), 1124 UNIQUE KEY unique_order (usergroup_id, weight), 1125 CONSTRAINT icinga_usergroup_inheritance_usergroup 1126 FOREIGN KEY usergroup (usergroup_id) 1127 REFERENCES icinga_usergroup (id) 1128 ON DELETE CASCADE 1129 ON UPDATE CASCADE, 1130 CONSTRAINT icinga_usergroup_inheritance_parent_usergroup 1131 FOREIGN KEY usergroup (parent_usergroup_id) 1132 REFERENCES icinga_usergroup (id) 1133 ON DELETE RESTRICT 1134 ON UPDATE CASCADE 1135) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1136 1137CREATE TABLE icinga_usergroup_user ( 1138 usergroup_id INT(10) UNSIGNED NOT NULL, 1139 user_id INT(10) UNSIGNED NOT NULL, 1140 PRIMARY KEY (usergroup_id, user_id), 1141 CONSTRAINT icinga_usergroup_user_user 1142 FOREIGN KEY icinga_user (user_id) 1143 REFERENCES icinga_user (id) 1144 ON DELETE CASCADE 1145 ON UPDATE CASCADE, 1146 CONSTRAINT icinga_usergroup_user_usergroup 1147 FOREIGN KEY usergroup (usergroup_id) 1148 REFERENCES icinga_usergroup (id) 1149 ON DELETE CASCADE 1150 ON UPDATE CASCADE 1151) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1152 1153CREATE TABLE icinga_usergroup_parent ( 1154 usergroup_id INT(10) UNSIGNED NOT NULL, 1155 parent_usergroup_id INT(10) UNSIGNED NOT NULL, 1156 PRIMARY KEY (usergroup_id, parent_usergroup_id), 1157 CONSTRAINT icinga_usergroup_parent_usergroup 1158 FOREIGN KEY usergroup (usergroup_id) 1159 REFERENCES icinga_usergroup (id) 1160 ON DELETE CASCADE 1161 ON UPDATE CASCADE, 1162 CONSTRAINT icinga_usergroup_parent_parent 1163 FOREIGN KEY parent (parent_usergroup_id) 1164 REFERENCES icinga_usergroup (id) 1165 ON DELETE RESTRICT 1166 ON UPDATE CASCADE 1167) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1168 1169CREATE TABLE icinga_notification ( 1170 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1171 object_name VARCHAR(255) DEFAULT NULL, 1172 object_type ENUM('object', 'template', 'apply') NOT NULL, 1173 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 1174 apply_to ENUM('host', 'service') DEFAULT NULL, 1175 host_id INT(10) UNSIGNED DEFAULT NULL, 1176 service_id INT(10) UNSIGNED DEFAULT NULL, 1177 times_begin INT(10) UNSIGNED DEFAULT NULL, 1178 times_end INT(10) UNSIGNED DEFAULT NULL, 1179 notification_interval INT(10) UNSIGNED DEFAULT NULL, 1180 command_id INT(10) UNSIGNED DEFAULT NULL, 1181 period_id INT(10) UNSIGNED DEFAULT NULL, 1182 zone_id INT(10) UNSIGNED DEFAULT NULL, 1183 assign_filter TEXT DEFAULT NULL, 1184 PRIMARY KEY (id), 1185 CONSTRAINT icinga_notification_host 1186 FOREIGN KEY host (host_id) 1187 REFERENCES icinga_host (id) 1188 ON DELETE CASCADE 1189 ON UPDATE CASCADE, 1190 CONSTRAINT icinga_notification_service 1191 FOREIGN KEY service (service_id) 1192 REFERENCES icinga_service (id) 1193 ON DELETE CASCADE 1194 ON UPDATE CASCADE, 1195 CONSTRAINT icinga_notification_command 1196 FOREIGN KEY command (command_id) 1197 REFERENCES icinga_command (id) 1198 ON DELETE RESTRICT 1199 ON UPDATE CASCADE, 1200 CONSTRAINT icinga_notification_period 1201 FOREIGN KEY period (period_id) 1202 REFERENCES icinga_timeperiod (id) 1203 ON DELETE RESTRICT 1204 ON UPDATE CASCADE, 1205 CONSTRAINT icinga_notification_zone 1206 FOREIGN KEY zone (zone_id) 1207 REFERENCES icinga_zone (id) 1208 ON DELETE RESTRICT 1209 ON UPDATE CASCADE 1210) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1211 1212CREATE TABLE icinga_notification_var ( 1213 notification_id INT(10) UNSIGNED NOT NULL, 1214 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1215 varvalue TEXT DEFAULT NULL, 1216 format enum ('string', 'json', 'expression'), 1217 checksum VARBINARY(20) DEFAULT NULL, 1218 PRIMARY KEY (notification_id, varname), 1219 INDEX search_idx (varname), 1220 INDEX checksum (checksum), 1221 CONSTRAINT icinga_notification_var_notification 1222 FOREIGN KEY notification (notification_id) 1223 REFERENCES icinga_notification (id) 1224 ON DELETE CASCADE 1225 ON UPDATE CASCADE 1226) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1227 1228CREATE TABLE icinga_notification_field ( 1229 notification_id INT(10) UNSIGNED NOT NULL COMMENT 'Makes only sense for templates', 1230 datafield_id INT(10) UNSIGNED NOT NULL, 1231 is_required ENUM('y', 'n') NOT NULL, 1232 var_filter TEXT DEFAULT NULL, 1233 PRIMARY KEY (notification_id, datafield_id), 1234 CONSTRAINT icinga_notification_field_notification 1235 FOREIGN KEY notification (notification_id) 1236 REFERENCES icinga_notification (id) 1237 ON DELETE CASCADE 1238 ON UPDATE CASCADE, 1239 CONSTRAINT icinga_notification_field_datafield 1240 FOREIGN KEY datafield(datafield_id) 1241 REFERENCES director_datafield (id) 1242 ON DELETE CASCADE 1243 ON UPDATE CASCADE 1244) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1245 1246CREATE TABLE icinga_notification_inheritance ( 1247 notification_id INT(10) UNSIGNED NOT NULL, 1248 parent_notification_id INT(10) UNSIGNED NOT NULL, 1249 weight MEDIUMINT UNSIGNED DEFAULT NULL, 1250 PRIMARY KEY (notification_id, parent_notification_id), 1251 UNIQUE KEY unique_order (notification_id, weight), 1252 CONSTRAINT icinga_notification_inheritance_notification 1253 FOREIGN KEY host (notification_id) 1254 REFERENCES icinga_notification (id) 1255 ON DELETE CASCADE 1256 ON UPDATE CASCADE, 1257 CONSTRAINT icinga_notification_inheritance_parent_notification 1258 FOREIGN KEY host (parent_notification_id) 1259 REFERENCES icinga_notification (id) 1260 ON DELETE RESTRICT 1261 ON UPDATE CASCADE 1262) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1263 1264CREATE TABLE icinga_notification_states_set ( 1265 notification_id INT(10) UNSIGNED NOT NULL, 1266 property ENUM( 1267 'OK', 1268 'Warning', 1269 'Critical', 1270 'Unknown', 1271 'Up', 1272 'Down' 1273 ) NOT NULL, 1274 merge_behaviour ENUM('override', 'extend', 'blacklist') NOT NULL DEFAULT 'override' 1275 COMMENT 'override: = [], extend: += [], blacklist: -= []', 1276 PRIMARY KEY (notification_id, property, merge_behaviour), 1277 CONSTRAINT icinga_notification_states_set_notification 1278 FOREIGN KEY icinga_notification (notification_id) 1279 REFERENCES icinga_notification (id) 1280 ON DELETE CASCADE 1281 ON UPDATE CASCADE 1282) ENGINE=InnoDB; 1283 1284CREATE TABLE icinga_notification_types_set ( 1285 notification_id INT(10) UNSIGNED NOT NULL, 1286 property ENUM( 1287 'DowntimeStart', 1288 'DowntimeEnd', 1289 'DowntimeRemoved', 1290 'Custom', 1291 'Acknowledgement', 1292 'Problem', 1293 'Recovery', 1294 'FlappingStart', 1295 'FlappingEnd' 1296 ) NOT NULL, 1297 merge_behaviour ENUM('override', 'extend', 'blacklist') NOT NULL DEFAULT 'override' 1298 COMMENT 'override: = [], extend: += [], blacklist: -= []', 1299 PRIMARY KEY (notification_id, property, merge_behaviour), 1300 CONSTRAINT icinga_notification_types_set_notification 1301 FOREIGN KEY icinga_notification (notification_id) 1302 REFERENCES icinga_notification (id) 1303 ON DELETE CASCADE 1304 ON UPDATE CASCADE 1305) ENGINE=InnoDB; 1306 1307CREATE TABLE icinga_notification_user ( 1308 notification_id INT(10) UNSIGNED NOT NULL, 1309 user_id INT(10) UNSIGNED NOT NULL, 1310 PRIMARY KEY (notification_id, user_id), 1311 CONSTRAINT icinga_notification_user_user 1312 FOREIGN KEY user (user_id) 1313 REFERENCES icinga_user (id) 1314 ON DELETE CASCADE 1315 ON UPDATE CASCADE, 1316 CONSTRAINT icinga_notification_user_notification 1317 FOREIGN KEY notification (notification_id) 1318 REFERENCES icinga_notification (id) 1319 ON DELETE CASCADE 1320 ON UPDATE CASCADE 1321) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1322 1323CREATE TABLE icinga_notification_usergroup ( 1324 notification_id INT(10) UNSIGNED NOT NULL, 1325 usergroup_id INT(10) UNSIGNED NOT NULL, 1326 PRIMARY KEY (notification_id, usergroup_id), 1327 CONSTRAINT icinga_notification_usergroup_usergroup 1328 FOREIGN KEY usergroup (usergroup_id) 1329 REFERENCES icinga_usergroup (id) 1330 ON DELETE CASCADE 1331 ON UPDATE CASCADE, 1332 CONSTRAINT icinga_notification_usergroup_notification 1333 FOREIGN KEY notification (notification_id) 1334 REFERENCES icinga_notification (id) 1335 ON DELETE CASCADE 1336 ON UPDATE CASCADE 1337) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1338 1339CREATE TABLE import_source ( 1340 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1341 source_name VARCHAR(64) NOT NULL, 1342 key_column VARCHAR(64) NOT NULL, 1343 provider_class VARCHAR(128) NOT NULL, 1344 import_state ENUM( 1345 'unknown', 1346 'in-sync', 1347 'pending-changes', 1348 'failing' 1349 ) NOT NULL DEFAULT 'unknown', 1350 last_error_message TEXT DEFAULT NULL, 1351 last_attempt DATETIME DEFAULT NULL, 1352 description TEXT DEFAULT NULL, 1353 PRIMARY KEY (id), 1354 UNIQUE INDEX source_name (source_name), 1355 INDEX search_idx (key_column) 1356) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1357 1358CREATE TABLE import_source_setting ( 1359 source_id INT(10) UNSIGNED NOT NULL, 1360 setting_name VARCHAR(64) NOT NULL, 1361 setting_value TEXT NOT NULL, 1362 PRIMARY KEY (source_id, setting_name), 1363 CONSTRAINT import_source_settings_source 1364 FOREIGN KEY source (source_id) 1365 REFERENCES import_source (id) 1366 ON DELETE CASCADE 1367 ON UPDATE CASCADE 1368) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1369 1370CREATE TABLE import_row_modifier ( 1371 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1372 source_id INT(10) UNSIGNED NOT NULL, 1373 property_name VARCHAR(255) NOT NULL, 1374 target_property VARCHAR(255) DEFAULT NULL, 1375 provider_class VARCHAR(128) NOT NULL, 1376 priority SMALLINT UNSIGNED NOT NULL, 1377 description TEXT DEFAULT NULL, 1378 PRIMARY KEY (id), 1379 KEY search_idx (property_name), 1380 CONSTRAINT row_modifier_import_source 1381 FOREIGN KEY source (source_id) 1382 REFERENCES import_source (id) 1383 ON DELETE CASCADE 1384 ON UPDATE CASCADE 1385) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1386 1387CREATE TABLE import_row_modifier_setting ( 1388 row_modifier_id INT UNSIGNED NOT NULL, 1389 setting_name VARCHAR(64) NOT NULL, 1390 setting_value TEXT DEFAULT NULL, 1391 PRIMARY KEY (row_modifier_id, setting_name), 1392 CONSTRAINT row_modifier_settings 1393 FOREIGN KEY row_modifier (row_modifier_id) 1394 REFERENCES import_row_modifier (id) 1395 ON DELETE CASCADE 1396 ON UPDATE CASCADE 1397) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1398 1399CREATE TABLE imported_rowset ( 1400 checksum VARBINARY(20) NOT NULL, 1401 PRIMARY KEY (checksum) 1402) ENGINE=InnoDB; 1403 1404CREATE TABLE import_run ( 1405 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1406 source_id INT(10) UNSIGNED NOT NULL, 1407 rowset_checksum VARBINARY(20) DEFAULT NULL, 1408 start_time DATETIME NOT NULL, 1409 end_time DATETIME DEFAULT NULL, 1410 succeeded ENUM('y', 'n') DEFAULT NULL, 1411 PRIMARY KEY (id), 1412 CONSTRAINT import_run_source 1413 FOREIGN KEY import_source (source_id) 1414 REFERENCES import_source (id) 1415 ON DELETE CASCADE 1416 ON UPDATE RESTRICT, 1417 CONSTRAINT import_run_rowset 1418 FOREIGN KEY rowset (rowset_checksum) 1419 REFERENCES imported_rowset (checksum) 1420 ON DELETE RESTRICT 1421 ON UPDATE CASCADE 1422) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1423 1424CREATE TABLE imported_row ( 1425 checksum VARBINARY(20) NOT NULL COMMENT 'sha1(object_name;property_checksum;...)', 1426 object_name VARCHAR(255) NOT NULL, 1427 PRIMARY KEY (checksum) 1428) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1429 1430CREATE TABLE imported_rowset_row ( 1431 rowset_checksum VARBINARY(20) NOT NULL, 1432 row_checksum VARBINARY(20) NOT NULL, 1433 PRIMARY KEY (rowset_checksum, row_checksum), 1434 CONSTRAINT imported_rowset_row_rowset 1435 FOREIGN KEY rowset_row_rowset (rowset_checksum) 1436 REFERENCES imported_rowset (checksum) 1437 ON DELETE CASCADE 1438 ON UPDATE CASCADE, 1439 CONSTRAINT imported_rowset_row_row 1440 FOREIGN KEY rowset_row_rowset (row_checksum) 1441 REFERENCES imported_row (checksum) 1442 ON DELETE RESTRICT 1443 ON UPDATE CASCADE 1444) ENGINE=InnoDB; 1445 1446CREATE TABLE imported_property ( 1447 checksum VARBINARY(20) NOT NULL, 1448 property_name VARCHAR(64) NOT NULL, 1449 property_value MEDIUMTEXT NOT NULL, 1450 format enum ('string', 'expression', 'json'), 1451 PRIMARY KEY (checksum), 1452 KEY search_idx (property_name) 1453) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1454 1455CREATE TABLE imported_row_property ( 1456 row_checksum VARBINARY(20) NOT NULL, 1457 property_checksum VARBINARY(20) NOT NULL, 1458 PRIMARY KEY (row_checksum, property_checksum), 1459 CONSTRAINT imported_row_property_row 1460 FOREIGN KEY row_checksum (row_checksum) 1461 REFERENCES imported_row (checksum) 1462 ON DELETE CASCADE 1463 ON UPDATE CASCADE, 1464 CONSTRAINT imported_row_property_property 1465 FOREIGN KEY property_checksum (property_checksum) 1466 REFERENCES imported_property (checksum) 1467 ON DELETE RESTRICT 1468 ON UPDATE CASCADE 1469) ENGINE=InnoDB; 1470 1471CREATE TABLE sync_rule ( 1472 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1473 rule_name VARCHAR(255) NOT NULL, 1474 object_type enum( 1475 'host', 1476 'service', 1477 'command', 1478 'user', 1479 'hostgroup', 1480 'servicegroup', 1481 'usergroup', 1482 'datalistEntry', 1483 'endpoint', 1484 'zone', 1485 'timePeriod', 1486 'serviceSet' 1487 ) NOT NULL, 1488 update_policy ENUM('merge', 'override', 'ignore') NOT NULL, 1489 purge_existing ENUM('y', 'n') NOT NULL DEFAULT 'n', 1490 filter_expression TEXT DEFAULT NULL, 1491 sync_state ENUM( 1492 'unknown', 1493 'in-sync', 1494 'pending-changes', 1495 'failing' 1496 ) NOT NULL DEFAULT 'unknown', 1497 last_error_message TEXT DEFAULT NULL, 1498 last_attempt DATETIME DEFAULT NULL, 1499 description TEXT DEFAULT NULL, 1500 PRIMARY KEY (id), 1501 UNIQUE INDEX rule_name (rule_name) 1502) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1503 1504CREATE TABLE sync_property ( 1505 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1506 rule_id INT(10) UNSIGNED NOT NULL, 1507 source_id INT(10) UNSIGNED NOT NULL, 1508 source_expression VARCHAR(255) NOT NULL, 1509 destination_field VARCHAR(64), 1510 priority SMALLINT UNSIGNED NOT NULL, 1511 filter_expression TEXT DEFAULT NULL, 1512 merge_policy ENUM('override', 'merge') NOT NULL, 1513 PRIMARY KEY (id), 1514 CONSTRAINT sync_property_rule 1515 FOREIGN KEY sync_rule (rule_id) 1516 REFERENCES sync_rule (id) 1517 ON DELETE CASCADE 1518 ON UPDATE CASCADE, 1519 CONSTRAINT sync_property_source 1520 FOREIGN KEY import_source (source_id) 1521 REFERENCES import_source (id) 1522 ON DELETE RESTRICT 1523 ON UPDATE CASCADE 1524) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1525 1526CREATE TABLE sync_run ( 1527 id BIGINT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1528 rule_id INT(10) UNSIGNED DEFAULT NULL, 1529 rule_name VARCHAR(255) NOT NULL, 1530 start_time DATETIME NOT NULL, 1531 duration_ms INT(10) UNSIGNED DEFAULT NULL, 1532 objects_deleted INT(10) UNSIGNED DEFAULT 0, 1533 objects_created INT(10) UNSIGNED DEFAULT 0, 1534 objects_modified INT(10) UNSIGNED DEFAULT 0, 1535 last_former_activity VARBINARY(20) DEFAULT NULL, 1536 last_related_activity VARBINARY(20) DEFAULT NULL, 1537 PRIMARY KEY (id), 1538 CONSTRAINT sync_run_rule 1539 FOREIGN KEY sync_rule (rule_id) 1540 REFERENCES sync_rule (id) 1541 ON DELETE SET NULL 1542 ON UPDATE CASCADE 1543) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1544 1545CREATE TABLE icinga_var ( 1546 checksum VARBINARY(20) NOT NULL, 1547 rendered_checksum VARBINARY(20) NOT NULL, 1548 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1549 varvalue TEXT NOT NULL, 1550 rendered TEXT NOT NULL, 1551 PRIMARY KEY (checksum), 1552 INDEX search_idx (varname) 1553) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1554 1555CREATE TABLE icinga_flat_var ( 1556 var_checksum VARBINARY(20) NOT NULL, 1557 flatname_checksum VARBINARY(20) NOT NULL, 1558 flatname VARCHAR(512) NOT NULL COLLATE utf8_bin, 1559 flatvalue TEXT NOT NULL, 1560 PRIMARY KEY (var_checksum, flatname_checksum), 1561 INDEX search_varname (flatname (191)), 1562 INDEX search_varvalue (flatvalue (128)), 1563 CONSTRAINT flat_var_var 1564 FOREIGN KEY checksum (var_checksum) 1565 REFERENCES icinga_var (checksum) 1566 ON DELETE CASCADE 1567 ON UPDATE CASCADE 1568) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1569 1570CREATE TABLE icinga_command_resolved_var ( 1571 command_id INT(10) UNSIGNED NOT NULL, 1572 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1573 checksum VARBINARY(20) NOT NULL, 1574 PRIMARY KEY (command_id, checksum), 1575 INDEX search_varname (varname), 1576 CONSTRAINT command_resolved_var_command 1577 FOREIGN KEY command (command_id) 1578 REFERENCES icinga_command (id) 1579 ON DELETE CASCADE 1580 ON UPDATE CASCADE, 1581 CONSTRAINT command_resolved_var_checksum 1582 FOREIGN KEY checksum (checksum) 1583 REFERENCES icinga_var (checksum) 1584 ON DELETE RESTRICT 1585 ON UPDATE RESTRICT 1586) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1587 1588CREATE TABLE icinga_host_resolved_var ( 1589 host_id INT(10) UNSIGNED NOT NULL, 1590 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1591 checksum VARBINARY(20) NOT NULL, 1592 PRIMARY KEY (host_id, checksum), 1593 INDEX search_varname (varname), 1594 FOREIGN KEY host_resolved_var_host (host_id) 1595 REFERENCES icinga_host (id) 1596 ON DELETE CASCADE 1597 ON UPDATE CASCADE, 1598 FOREIGN KEY host_resolved_var_checksum (checksum) 1599 REFERENCES icinga_var (checksum) 1600 ON DELETE RESTRICT 1601 ON UPDATE RESTRICT 1602) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1603 1604CREATE TABLE icinga_notification_resolved_var ( 1605 notification_id INT(10) UNSIGNED NOT NULL, 1606 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1607 checksum VARBINARY(20) NOT NULL, 1608 PRIMARY KEY (notification_id, checksum), 1609 INDEX search_varname (varname), 1610 FOREIGN KEY notification_resolved_var_notification (notification_id) 1611 REFERENCES icinga_notification (id) 1612 ON DELETE CASCADE 1613 ON UPDATE CASCADE, 1614 FOREIGN KEY notification_resolved_var_checksum (checksum) 1615 REFERENCES icinga_var (checksum) 1616 ON DELETE RESTRICT 1617 ON UPDATE RESTRICT 1618) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1619 1620CREATE TABLE icinga_service_set_resolved_var ( 1621 service_set_id INT(10) UNSIGNED NOT NULL, 1622 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1623 checksum VARBINARY(20) NOT NULL, 1624 PRIMARY KEY (service_set_id, checksum), 1625 INDEX search_varname (varname), 1626 FOREIGN KEY service_set_resolved_var_service_set (service_set_id) 1627 REFERENCES icinga_service_set (id) 1628 ON DELETE CASCADE 1629 ON UPDATE CASCADE, 1630 FOREIGN KEY service_set_resolved_var_checksum(checksum) 1631 REFERENCES icinga_var (checksum) 1632 ON DELETE RESTRICT 1633 ON UPDATE RESTRICT 1634) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1635 1636CREATE TABLE icinga_service_resolved_var ( 1637 service_id INT(10) UNSIGNED NOT NULL, 1638 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1639 checksum VARBINARY(20) NOT NULL, 1640 PRIMARY KEY (service_id, checksum), 1641 INDEX search_varname (varname), 1642 FOREIGN KEY service_resolve_var_service (service_id) 1643 REFERENCES icinga_service (id) 1644 ON DELETE CASCADE 1645 ON UPDATE CASCADE, 1646 FOREIGN KEY service_resolve_var_checksum(checksum) 1647 REFERENCES icinga_var (checksum) 1648 ON DELETE RESTRICT 1649 ON UPDATE RESTRICT 1650) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1651 1652CREATE TABLE icinga_user_resolved_var ( 1653 user_id INT(10) UNSIGNED NOT NULL, 1654 varname VARCHAR(255) NOT NULL COLLATE utf8_bin, 1655 checksum VARBINARY(20) NOT NULL, 1656 PRIMARY KEY (user_id, checksum), 1657 INDEX search_varname (varname), 1658 FOREIGN KEY user_resolve_var_user (user_id) 1659 REFERENCES icinga_user (id) 1660 ON DELETE CASCADE 1661 ON UPDATE CASCADE, 1662 FOREIGN KEY user_resolve_var_checksum(checksum) 1663 REFERENCES icinga_var (checksum) 1664 ON DELETE RESTRICT 1665 ON UPDATE RESTRICT 1666) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1667 1668CREATE TABLE icinga_dependency ( 1669 id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, 1670 object_name VARCHAR(255) DEFAULT NULL, 1671 object_type ENUM('object', 'template', 'apply') NOT NULL, 1672 disabled ENUM('y', 'n') NOT NULL DEFAULT 'n', 1673 apply_to ENUM('host', 'service') DEFAULT NULL, 1674 parent_host_id INT(10) UNSIGNED DEFAULT NULL, 1675 parent_service_id INT(10) UNSIGNED DEFAULT NULL, 1676 child_host_id INT(10) UNSIGNED DEFAULT NULL, 1677 child_service_id INT(10) UNSIGNED DEFAULT NULL, 1678 disable_checks ENUM('y', 'n') DEFAULT NULL, 1679 disable_notifications ENUM('y', 'n') DEFAULT NULL, 1680 ignore_soft_states ENUM('y', 'n') DEFAULT NULL, 1681 period_id INT(10) UNSIGNED DEFAULT NULL, 1682 zone_id INT(10) UNSIGNED DEFAULT NULL, 1683 assign_filter TEXT DEFAULT NULL, 1684 parent_service_by_name VARCHAR(255) DEFAULT NULL, 1685 PRIMARY KEY (id), 1686 CONSTRAINT icinga_dependency_parent_host 1687 FOREIGN KEY parent_host (parent_host_id) 1688 REFERENCES icinga_host (id) 1689 ON DELETE RESTRICT 1690 ON UPDATE CASCADE, 1691 CONSTRAINT icinga_dependency_parent_service 1692 FOREIGN KEY parent_service (parent_service_id) 1693 REFERENCES icinga_service (id) 1694 ON DELETE RESTRICT 1695 ON UPDATE CASCADE, 1696 CONSTRAINT icinga_dependency_child_host 1697 FOREIGN KEY child_host (child_host_id) 1698 REFERENCES icinga_host (id) 1699 ON DELETE CASCADE 1700 ON UPDATE CASCADE, 1701 CONSTRAINT icinga_dependency_child_service 1702 FOREIGN KEY child_service (child_service_id) 1703 REFERENCES icinga_service (id) 1704 ON DELETE CASCADE 1705 ON UPDATE CASCADE, 1706 CONSTRAINT icinga_dependency_period 1707 FOREIGN KEY period (period_id) 1708 REFERENCES icinga_timeperiod (id) 1709 ON DELETE RESTRICT 1710 ON UPDATE CASCADE, 1711 CONSTRAINT icinga_dependency_zone 1712 FOREIGN KEY zone (zone_id) 1713 REFERENCES icinga_zone (id) 1714 ON DELETE RESTRICT 1715 ON UPDATE CASCADE 1716) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1717 1718CREATE TABLE icinga_dependency_inheritance ( 1719 dependency_id INT(10) UNSIGNED NOT NULL, 1720 parent_dependency_id INT(10) UNSIGNED NOT NULL, 1721 weight MEDIUMINT UNSIGNED DEFAULT NULL, 1722 PRIMARY KEY (dependency_id, parent_dependency_id), 1723 UNIQUE KEY unique_order (dependency_id, weight), 1724 CONSTRAINT icinga_dependency_inheritance_dependency 1725 FOREIGN KEY dependency (dependency_id) 1726 REFERENCES icinga_dependency (id) 1727 ON DELETE CASCADE 1728 ON UPDATE CASCADE, 1729 CONSTRAINT icinga_dependency_inheritance_parent_dependency 1730 FOREIGN KEY parent_dependency (parent_dependency_id) 1731 REFERENCES icinga_dependency (id) 1732 ON DELETE RESTRICT 1733 ON UPDATE CASCADE 1734) ENGINE=InnoDB DEFAULT CHARSET=utf8; 1735 1736CREATE TABLE icinga_dependency_states_set ( 1737 dependency_id INT(10) UNSIGNED NOT NULL, 1738 property ENUM( 1739 'OK', 1740 'Warning', 1741 'Critical', 1742 'Unknown', 1743 'Up', 1744 'Down' 1745 ) NOT NULL, 1746 merge_behaviour ENUM('override', 'extend', 'blacklist') NOT NULL DEFAULT 'override' 1747 COMMENT 'override: = [], extend: += [], blacklist: -= []', 1748 PRIMARY KEY (dependency_id, property, merge_behaviour), 1749 CONSTRAINT icinga_dependency_states_set_dependency 1750 FOREIGN KEY icinga_dependency (dependency_id) 1751 REFERENCES icinga_dependency (id) 1752 ON DELETE CASCADE 1753 ON UPDATE CASCADE 1754) ENGINE=InnoDB; 1755 1756CREATE TABLE icinga_timeperiod_include ( 1757 timeperiod_id INT(10) UNSIGNED NOT NULL, 1758 include_id INT(10) UNSIGNED NOT NULL, 1759 PRIMARY KEY (timeperiod_id, include_id), 1760 CONSTRAINT icinga_timeperiod_include 1761 FOREIGN KEY timeperiod (include_id) 1762 REFERENCES icinga_timeperiod (id) 1763 ON DELETE RESTRICT 1764 ON UPDATE RESTRICT, 1765 CONSTRAINT icinga_timeperiod_include_timeperiod 1766 FOREIGN KEY include (timeperiod_id) 1767 REFERENCES icinga_timeperiod (id) 1768 ON DELETE CASCADE 1769 ON UPDATE CASCADE 1770); 1771 1772CREATE TABLE icinga_timeperiod_exclude ( 1773 timeperiod_id INT(10) UNSIGNED NOT NULL, 1774 exclude_id INT(10) UNSIGNED NOT NULL, 1775 PRIMARY KEY (timeperiod_id, exclude_id), 1776 CONSTRAINT icinga_timeperiod_exclude 1777 FOREIGN KEY timeperiod (exclude_id) 1778 REFERENCES icinga_timeperiod (id) 1779 ON DELETE RESTRICT 1780 ON UPDATE RESTRICT, 1781 CONSTRAINT icinga_timeperiod_exclude_timeperiod 1782 FOREIGN KEY exclude (timeperiod_id) 1783 REFERENCES icinga_timeperiod (id) 1784 ON DELETE CASCADE 1785 ON UPDATE CASCADE 1786); 1787 1788INSERT INTO director_schema_migration 1789 (schema_version, migration_time) 1790 VALUES (157, NOW()); 1791