1-- -------------------------------------------------------- 2-- pgsql.sql 3-- DB definition for IDO Postgresql 4-- 5-- Icinga 2 | (c) 2012 Icinga GmbH | GPLv2+ 6-- 7-- -------------------------------------------------------- 8 9-- 10-- Functions 11-- 12 13DROP FUNCTION IF EXISTS from_unixtime(bigint); 14CREATE FUNCTION from_unixtime(bigint) RETURNS timestamp AS $$ 15 SELECT to_timestamp($1) AT TIME ZONE 'UTC' AS result 16$$ LANGUAGE sql; 17 18DROP FUNCTION IF EXISTS unix_timestamp(timestamp WITH TIME ZONE); 19CREATE OR REPLACE FUNCTION unix_timestamp(timestamp) RETURNS bigint AS ' 20 SELECT CAST(EXTRACT(EPOCH FROM $1) AS bigint) AS result; 21' LANGUAGE sql; 22 23 24-- ----------------------------------------- 25-- set dbversion 26-- ----------------------------------------- 27 28CREATE OR REPLACE FUNCTION updatedbversion(version_i TEXT) RETURNS void AS $$ 29BEGIN 30 IF EXISTS( SELECT * FROM icinga_dbversion WHERE name='idoutils') 31 THEN 32 UPDATE icinga_dbversion 33 SET version=version_i, modify_time=NOW() 34 WHERE name='idoutils'; 35 ELSE 36 INSERT INTO icinga_dbversion (dbversion_id, name, version, create_time, modify_time) VALUES ('1', 'idoutils', version_i, NOW(), NOW()); 37 END IF; 38 39 RETURN; 40END; 41$$ LANGUAGE plpgsql; 42-- HINT: su - postgres; createlang plpgsql icinga; 43 44 45 46-- 47-- Database: icinga 48-- 49 50-- -------------------------------------------------------- 51 52-- 53-- Table structure for table icinga_acknowledgements 54-- 55 56CREATE TABLE icinga_acknowledgements ( 57 acknowledgement_id bigserial, 58 instance_id bigint default 0, 59 entry_time timestamp, 60 entry_time_usec INTEGER default 0, 61 acknowledgement_type INTEGER default 0, 62 object_id bigint default 0, 63 state INTEGER default 0, 64 author_name TEXT default '', 65 comment_data TEXT default '', 66 is_sticky INTEGER default 0, 67 persistent_comment INTEGER default 0, 68 notify_contacts INTEGER default 0, 69 end_time timestamp, 70 CONSTRAINT PK_acknowledgement_id PRIMARY KEY (acknowledgement_id) 71) ; 72 73-- -------------------------------------------------------- 74 75-- 76-- Table structure for table icinga_commands 77-- 78 79CREATE TABLE icinga_commands ( 80 command_id bigserial, 81 instance_id bigint default 0, 82 config_type INTEGER default 0, 83 object_id bigint default 0, 84 command_line TEXT default '', 85 config_hash varchar(64) DEFAULT NULL, 86 CONSTRAINT PK_command_id PRIMARY KEY (command_id) , 87 CONSTRAINT UQ_commands UNIQUE (instance_id,object_id,config_type) 88) ; 89 90-- -------------------------------------------------------- 91 92-- 93-- Table structure for table icinga_commenthistory 94-- 95 96CREATE TABLE icinga_commenthistory ( 97 commenthistory_id bigserial, 98 instance_id bigint default 0, 99 entry_time timestamp, 100 entry_time_usec INTEGER default 0, 101 comment_type INTEGER default 0, 102 entry_type INTEGER default 0, 103 object_id bigint default 0, 104 comment_time timestamp, 105 internal_comment_id bigint default 0, 106 author_name TEXT default '', 107 comment_data TEXT default '', 108 is_persistent INTEGER default 0, 109 comment_source INTEGER default 0, 110 expires INTEGER default 0, 111 expiration_time timestamp, 112 deletion_time timestamp, 113 deletion_time_usec INTEGER default 0, 114 name TEXT default NULL, 115 CONSTRAINT PK_commenthistory_id PRIMARY KEY (commenthistory_id) 116); 117 118-- -------------------------------------------------------- 119 120-- 121-- Table structure for table icinga_comments 122-- 123 124CREATE TABLE icinga_comments ( 125 comment_id bigserial, 126 instance_id bigint default 0, 127 entry_time timestamp, 128 entry_time_usec INTEGER default 0, 129 comment_type INTEGER default 0, 130 entry_type INTEGER default 0, 131 object_id bigint default 0, 132 comment_time timestamp, 133 internal_comment_id bigint default 0, 134 author_name TEXT default '', 135 comment_data TEXT default '', 136 is_persistent INTEGER default 0, 137 comment_source INTEGER default 0, 138 expires INTEGER default 0, 139 expiration_time timestamp, 140 name TEXT default NULL, 141 session_token INTEGER default NULL, 142 CONSTRAINT PK_comment_id PRIMARY KEY (comment_id) 143) ; 144 145-- -------------------------------------------------------- 146 147-- 148-- Table structure for table icinga_configfiles 149-- 150 151CREATE TABLE icinga_configfiles ( 152 configfile_id bigserial, 153 instance_id bigint default 0, 154 configfile_type INTEGER default 0, 155 configfile_path TEXT default '', 156 CONSTRAINT PK_configfile_id PRIMARY KEY (configfile_id) , 157 CONSTRAINT UQ_configfiles UNIQUE (instance_id,configfile_type,configfile_path) 158); 159 160-- -------------------------------------------------------- 161 162-- 163-- Table structure for table icinga_configfilevariables 164-- 165 166CREATE TABLE icinga_configfilevariables ( 167 configfilevariable_id bigserial, 168 instance_id bigint default 0, 169 configfile_id bigint default 0, 170 varname TEXT default '', 171 varvalue TEXT default '', 172 CONSTRAINT PK_configfilevariable_id PRIMARY KEY (configfilevariable_id) 173) ; 174 175-- -------------------------------------------------------- 176 177-- 178-- Table structure for table icinga_conninfo 179-- 180 181CREATE TABLE icinga_conninfo ( 182 conninfo_id bigserial, 183 instance_id bigint default 0, 184 agent_name TEXT default '', 185 agent_version TEXT default '', 186 disposition TEXT default '', 187 connect_source TEXT default '', 188 connect_type TEXT default '', 189 connect_time timestamp, 190 disconnect_time timestamp, 191 last_checkin_time timestamp, 192 data_start_time timestamp, 193 data_end_time timestamp, 194 bytes_processed bigint default 0, 195 lines_processed bigint default 0, 196 entries_processed bigint default 0, 197 CONSTRAINT PK_conninfo_id PRIMARY KEY (conninfo_id) 198) ; 199 200-- -------------------------------------------------------- 201 202-- 203-- Table structure for table icinga_contactgroups 204-- 205 206CREATE TABLE icinga_contactgroups ( 207 contactgroup_id bigserial, 208 instance_id bigint default 0, 209 config_type INTEGER default 0, 210 contactgroup_object_id bigint default 0, 211 alias TEXT default '', 212 config_hash varchar(64) DEFAULT NULL, 213 CONSTRAINT PK_contactgroup_id PRIMARY KEY (contactgroup_id) , 214 CONSTRAINT UQ_contactgroups UNIQUE (instance_id,config_type,contactgroup_object_id) 215); 216 217-- -------------------------------------------------------- 218 219-- 220-- Table structure for table icinga_contactgroup_members 221-- 222 223CREATE TABLE icinga_contactgroup_members ( 224 contactgroup_member_id bigserial, 225 instance_id bigint default 0, 226 contactgroup_id bigint default 0, 227 contact_object_id bigint default 0, 228 session_token INTEGER default NULL, 229 CONSTRAINT PK_contactgroup_member_id PRIMARY KEY (contactgroup_member_id) 230); 231 232-- -------------------------------------------------------- 233 234-- 235-- Table structure for table icinga_contactnotificationmethods 236-- 237 238CREATE TABLE icinga_contactnotificationmethods ( 239 contactnotificationmethod_id bigserial, 240 instance_id bigint default 0, 241 contactnotification_id bigint default 0, 242 start_time timestamp, 243 start_time_usec INTEGER default 0, 244 end_time timestamp, 245 end_time_usec INTEGER default 0, 246 command_object_id bigint default 0, 247 command_args TEXT default '', 248 CONSTRAINT PK_contactnotificationmethod_id PRIMARY KEY (contactnotificationmethod_id) , 249 CONSTRAINT UQ_contactnotificationmethods UNIQUE (instance_id,contactnotification_id,start_time,start_time_usec) 250) ; 251 252-- -------------------------------------------------------- 253 254-- 255-- Table structure for table icinga_contactnotifications 256-- 257 258CREATE TABLE icinga_contactnotifications ( 259 contactnotification_id bigserial, 260 instance_id bigint default 0, 261 notification_id bigint default 0, 262 contact_object_id bigint default 0, 263 start_time timestamp, 264 start_time_usec INTEGER default 0, 265 end_time timestamp, 266 end_time_usec INTEGER default 0, 267 CONSTRAINT PK_contactnotification_id PRIMARY KEY (contactnotification_id) , 268 CONSTRAINT UQ_contactnotifications UNIQUE (instance_id,contact_object_id,start_time,start_time_usec) 269) ; 270 271-- -------------------------------------------------------- 272 273-- 274-- Table structure for table icinga_contacts 275-- 276 277CREATE TABLE icinga_contacts ( 278 contact_id bigserial, 279 instance_id bigint default 0, 280 config_type INTEGER default 0, 281 contact_object_id bigint default 0, 282 alias TEXT default '', 283 email_address TEXT default '', 284 pager_address TEXT default '', 285 host_timeperiod_object_id bigint default 0, 286 service_timeperiod_object_id bigint default 0, 287 host_notifications_enabled INTEGER default 0, 288 service_notifications_enabled INTEGER default 0, 289 can_submit_commands INTEGER default 0, 290 notify_service_recovery INTEGER default 0, 291 notify_service_warning INTEGER default 0, 292 notify_service_unknown INTEGER default 0, 293 notify_service_critical INTEGER default 0, 294 notify_service_flapping INTEGER default 0, 295 notify_service_downtime INTEGER default 0, 296 notify_host_recovery INTEGER default 0, 297 notify_host_down INTEGER default 0, 298 notify_host_unreachable INTEGER default 0, 299 notify_host_flapping INTEGER default 0, 300 notify_host_downtime INTEGER default 0, 301 config_hash varchar(64) DEFAULT NULL, 302 CONSTRAINT PK_contact_id PRIMARY KEY (contact_id) , 303 CONSTRAINT UQ_contacts UNIQUE (instance_id,config_type,contact_object_id) 304) ; 305 306-- -------------------------------------------------------- 307 308-- 309-- Table structure for table icinga_contactstatus 310-- 311 312CREATE TABLE icinga_contactstatus ( 313 contactstatus_id bigserial, 314 instance_id bigint default 0, 315 contact_object_id bigint default 0, 316 status_update_time timestamp, 317 host_notifications_enabled INTEGER default 0, 318 service_notifications_enabled INTEGER default 0, 319 last_host_notification timestamp, 320 last_service_notification timestamp, 321 modified_attributes INTEGER default 0, 322 modified_host_attributes INTEGER default 0, 323 modified_service_attributes INTEGER default 0, 324 CONSTRAINT PK_contactstatus_id PRIMARY KEY (contactstatus_id) , 325 CONSTRAINT UQ_contactstatus UNIQUE (contact_object_id) 326) ; 327 328-- -------------------------------------------------------- 329 330-- 331-- Table structure for table icinga_contact_addresses 332-- 333 334CREATE TABLE icinga_contact_addresses ( 335 contact_address_id bigserial, 336 instance_id bigint default 0, 337 contact_id bigint default 0, 338 address_number INTEGER default 0, 339 address TEXT default '', 340 CONSTRAINT PK_contact_address_id PRIMARY KEY (contact_address_id) , 341 CONSTRAINT UQ_contact_addresses UNIQUE (contact_id,address_number) 342) ; 343 344-- -------------------------------------------------------- 345 346-- 347-- Table structure for table icinga_contact_notificationcommands 348-- 349 350CREATE TABLE icinga_contact_notificationcommands ( 351 contact_notificationcommand_id bigserial, 352 instance_id bigint default 0, 353 contact_id bigint default 0, 354 notification_type INTEGER default 0, 355 command_object_id bigint default 0, 356 command_args TEXT default '', 357 CONSTRAINT PK_contact_notificationcommand_id PRIMARY KEY (contact_notificationcommand_id) , 358 CONSTRAINT UQ_contact_notificationcommands UNIQUE (contact_id,notification_type,command_object_id,command_args) 359) ; 360 361-- -------------------------------------------------------- 362 363-- 364-- Table structure for table icinga_customvariables 365-- 366 367CREATE TABLE icinga_customvariables ( 368 customvariable_id bigserial, 369 instance_id bigint default 0, 370 object_id bigint default 0, 371 config_type INTEGER default 0, 372 has_been_modified INTEGER default 0, 373 varname TEXT default '', 374 varvalue TEXT default '', 375 is_json INTEGER default 0, 376 session_token INTEGER default NULL, 377 CONSTRAINT PK_customvariable_id PRIMARY KEY (customvariable_id) , 378 CONSTRAINT UQ_customvariables UNIQUE (object_id,config_type,varname) 379) ; 380CREATE INDEX icinga_customvariables_i ON icinga_customvariables(varname); 381 382-- -------------------------------------------------------- 383 384-- 385-- Table structure for table icinga_customvariablestatus 386-- 387 388CREATE TABLE icinga_customvariablestatus ( 389 customvariablestatus_id bigserial, 390 instance_id bigint default 0, 391 object_id bigint default 0, 392 status_update_time timestamp, 393 has_been_modified INTEGER default 0, 394 varname TEXT default '', 395 varvalue TEXT default '', 396 is_json INTEGER default 0, 397 session_token INTEGER default NULL, 398 CONSTRAINT PK_customvariablestatus_id PRIMARY KEY (customvariablestatus_id) , 399 CONSTRAINT UQ_customvariablestatus UNIQUE (object_id,varname) 400) ; 401CREATE INDEX icinga_customvariablestatus_i ON icinga_customvariablestatus(varname); 402 403 404-- -------------------------------------------------------- 405 406-- 407-- Table structure for table icinga_dbversion 408-- 409 410CREATE TABLE icinga_dbversion ( 411 dbversion_id bigserial, 412 name TEXT default '', 413 version TEXT default '', 414 create_time timestamp, 415 modify_time timestamp, 416 CONSTRAINT PK_dbversion_id PRIMARY KEY (dbversion_id) , 417 CONSTRAINT UQ_dbversion UNIQUE (name) 418) ; 419 420-- -------------------------------------------------------- 421 422-- 423-- Table structure for table icinga_downtimehistory 424-- 425 426CREATE TABLE icinga_downtimehistory ( 427 downtimehistory_id bigserial, 428 instance_id bigint default 0, 429 downtime_type INTEGER default 0, 430 object_id bigint default 0, 431 entry_time timestamp, 432 author_name TEXT default '', 433 comment_data TEXT default '', 434 internal_downtime_id bigint default 0, 435 triggered_by_id bigint default 0, 436 is_fixed INTEGER default 0, 437 duration BIGINT default 0, 438 scheduled_start_time timestamp, 439 scheduled_end_time timestamp, 440 was_started INTEGER default 0, 441 actual_start_time timestamp, 442 actual_start_time_usec INTEGER default 0, 443 actual_end_time timestamp, 444 actual_end_time_usec INTEGER default 0, 445 was_cancelled INTEGER default 0, 446 is_in_effect INTEGER default 0, 447 trigger_time timestamp, 448 name TEXT default NULL, 449 CONSTRAINT PK_downtimehistory_id PRIMARY KEY (downtimehistory_id) 450) ; 451 452-- -------------------------------------------------------- 453 454-- 455-- Table structure for table icinga_eventhandlers 456-- 457 458CREATE TABLE icinga_eventhandlers ( 459 eventhandler_id bigserial, 460 instance_id bigint default 0, 461 eventhandler_type INTEGER default 0, 462 object_id bigint default 0, 463 state INTEGER default 0, 464 state_type INTEGER default 0, 465 start_time timestamp, 466 start_time_usec INTEGER default 0, 467 end_time timestamp, 468 end_time_usec INTEGER default 0, 469 command_object_id bigint default 0, 470 command_args TEXT default '', 471 command_line TEXT default '', 472 timeout INTEGER default 0, 473 early_timeout INTEGER default 0, 474 execution_time double precision default 0, 475 return_code INTEGER default 0, 476 output TEXT default '', 477 long_output TEXT default '', 478 CONSTRAINT PK_eventhandler_id PRIMARY KEY (eventhandler_id) , 479 CONSTRAINT UQ_eventhandlers UNIQUE (instance_id,object_id,start_time,start_time_usec) 480) ; 481 482-- -------------------------------------------------------- 483 484-- 485-- Table structure for table icinga_externalcommands 486-- 487 488CREATE TABLE icinga_externalcommands ( 489 externalcommand_id bigserial, 490 instance_id bigint default 0, 491 entry_time timestamp, 492 command_type INTEGER default 0, 493 command_name TEXT default '', 494 command_args TEXT default '', 495 CONSTRAINT PK_externalcommand_id PRIMARY KEY (externalcommand_id) 496) ; 497 498-- -------------------------------------------------------- 499 500-- 501-- Table structure for table icinga_flappinghistory 502-- 503 504CREATE TABLE icinga_flappinghistory ( 505 flappinghistory_id bigserial, 506 instance_id bigint default 0, 507 event_time timestamp, 508 event_time_usec INTEGER default 0, 509 event_type INTEGER default 0, 510 reason_type INTEGER default 0, 511 flapping_type INTEGER default 0, 512 object_id bigint default 0, 513 percent_state_change double precision default 0, 514 low_threshold double precision default 0, 515 high_threshold double precision default 0, 516 comment_time timestamp, 517 internal_comment_id bigint default 0, 518 CONSTRAINT PK_flappinghistory_id PRIMARY KEY (flappinghistory_id) 519) ; 520 521-- -------------------------------------------------------- 522 523-- 524-- Table structure for table icinga_hostchecks 525-- 526 527CREATE TABLE icinga_hostchecks ( 528 hostcheck_id bigserial, 529 instance_id bigint default 0, 530 host_object_id bigint default 0, 531 check_type INTEGER default 0, 532 is_raw_check INTEGER default 0, 533 current_check_attempt INTEGER default 0, 534 max_check_attempts INTEGER default 0, 535 state INTEGER default 0, 536 state_type INTEGER default 0, 537 start_time timestamp, 538 start_time_usec INTEGER default 0, 539 end_time timestamp, 540 end_time_usec INTEGER default 0, 541 command_object_id bigint default 0, 542 command_args TEXT default '', 543 command_line TEXT default '', 544 timeout INTEGER default 0, 545 early_timeout INTEGER default 0, 546 execution_time double precision default 0, 547 latency double precision default 0, 548 return_code INTEGER default 0, 549 output TEXT default '', 550 long_output TEXT default '', 551 perfdata TEXT default '', 552 CONSTRAINT PK_hostcheck_id PRIMARY KEY (hostcheck_id) 553) ; 554 555-- -------------------------------------------------------- 556 557-- 558-- Table structure for table icinga_hostdependencies 559-- 560 561CREATE TABLE icinga_hostdependencies ( 562 hostdependency_id bigserial, 563 instance_id bigint default 0, 564 config_type INTEGER default 0, 565 host_object_id bigint default 0, 566 dependent_host_object_id bigint default 0, 567 dependency_type INTEGER default 0, 568 inherits_parent INTEGER default 0, 569 timeperiod_object_id bigint default 0, 570 fail_on_up INTEGER default 0, 571 fail_on_down INTEGER default 0, 572 fail_on_unreachable INTEGER default 0, 573 CONSTRAINT PK_hostdependency_id PRIMARY KEY (hostdependency_id) 574) ; 575CREATE INDEX idx_hostdependencies ON icinga_hostdependencies(instance_id,config_type,host_object_id,dependent_host_object_id,dependency_type,inherits_parent,fail_on_up,fail_on_down,fail_on_unreachable); 576 577-- -------------------------------------------------------- 578 579-- 580-- Table structure for table icinga_hostescalations 581-- 582 583CREATE TABLE icinga_hostescalations ( 584 hostescalation_id bigserial, 585 instance_id bigint default 0, 586 config_type INTEGER default 0, 587 host_object_id bigint default 0, 588 timeperiod_object_id bigint default 0, 589 first_notification INTEGER default 0, 590 last_notification INTEGER default 0, 591 notification_interval double precision default 0, 592 escalate_on_recovery INTEGER default 0, 593 escalate_on_down INTEGER default 0, 594 escalate_on_unreachable INTEGER default 0, 595 CONSTRAINT PK_hostescalation_id PRIMARY KEY (hostescalation_id) , 596 CONSTRAINT UQ_hostescalations UNIQUE (instance_id,config_type,host_object_id,timeperiod_object_id,first_notification,last_notification) 597) ; 598 599-- -------------------------------------------------------- 600 601-- 602-- Table structure for table icinga_hostescalation_contactgroups 603-- 604 605CREATE TABLE icinga_hostescalation_contactgroups ( 606 hostescalation_contactgroup_id bigserial, 607 instance_id bigint default 0, 608 hostescalation_id bigint default 0, 609 contactgroup_object_id bigint default 0, 610 CONSTRAINT PK_hostescalation_contactgroup_id PRIMARY KEY (hostescalation_contactgroup_id) , 611 CONSTRAINT UQ_hostescalation_contactgroups UNIQUE (hostescalation_id,contactgroup_object_id) 612) ; 613 614-- -------------------------------------------------------- 615 616-- 617-- Table structure for table icinga_hostescalation_contacts 618-- 619 620CREATE TABLE icinga_hostescalation_contacts ( 621 hostescalation_contact_id bigserial, 622 instance_id bigint default 0, 623 hostescalation_id bigint default 0, 624 contact_object_id bigint default 0, 625 CONSTRAINT PK_hostescalation_contact_id PRIMARY KEY (hostescalation_contact_id) , 626 CONSTRAINT UQ_hostescalation_contacts UNIQUE (instance_id,hostescalation_id,contact_object_id) 627) ; 628 629-- -------------------------------------------------------- 630 631-- 632-- Table structure for table icinga_hostgroups 633-- 634 635CREATE TABLE icinga_hostgroups ( 636 hostgroup_id bigserial, 637 instance_id bigint default 0, 638 config_type INTEGER default 0, 639 hostgroup_object_id bigint default 0, 640 alias TEXT default '', 641 notes TEXT default NULL, 642 notes_url TEXT default NULL, 643 action_url TEXT default NULL, 644 config_hash varchar(64) DEFAULT NULL, 645 CONSTRAINT PK_hostgroup_id PRIMARY KEY (hostgroup_id) , 646 CONSTRAINT UQ_hostgroups UNIQUE (instance_id,hostgroup_object_id) 647) ; 648 649-- -------------------------------------------------------- 650 651-- 652-- Table structure for table icinga_hostgroup_members 653-- 654 655CREATE TABLE icinga_hostgroup_members ( 656 hostgroup_member_id bigserial, 657 instance_id bigint default 0, 658 hostgroup_id bigint default 0, 659 host_object_id bigint default 0, 660 session_token INTEGER default NULL, 661 CONSTRAINT PK_hostgroup_member_id PRIMARY KEY (hostgroup_member_id) 662) ; 663 664-- -------------------------------------------------------- 665 666-- 667-- Table structure for table icinga_hosts 668-- 669 670CREATE TABLE icinga_hosts ( 671 host_id bigserial, 672 instance_id bigint default 0, 673 config_type INTEGER default 0, 674 host_object_id bigint default 0, 675 alias TEXT default '', 676 display_name TEXT default '', 677 address TEXT default '', 678 address6 TEXT default '', 679 check_command_object_id bigint default 0, 680 check_command_args TEXT default '', 681 eventhandler_command_object_id bigint default 0, 682 eventhandler_command_args TEXT default '', 683 notification_timeperiod_object_id bigint default 0, 684 check_timeperiod_object_id bigint default 0, 685 failure_prediction_options TEXT default '', 686 check_interval double precision default 0, 687 retry_interval double precision default 0, 688 max_check_attempts INTEGER default 0, 689 first_notification_delay double precision default 0, 690 notification_interval double precision default 0, 691 notify_on_down INTEGER default 0, 692 notify_on_unreachable INTEGER default 0, 693 notify_on_recovery INTEGER default 0, 694 notify_on_flapping INTEGER default 0, 695 notify_on_downtime INTEGER default 0, 696 stalk_on_up INTEGER default 0, 697 stalk_on_down INTEGER default 0, 698 stalk_on_unreachable INTEGER default 0, 699 flap_detection_enabled INTEGER default 0, 700 flap_detection_on_up INTEGER default 0, 701 flap_detection_on_down INTEGER default 0, 702 flap_detection_on_unreachable INTEGER default 0, 703 low_flap_threshold double precision default 0, 704 high_flap_threshold double precision default 0, 705 process_performance_data INTEGER default 0, 706 freshness_checks_enabled INTEGER default 0, 707 freshness_threshold INTEGER default 0, 708 passive_checks_enabled INTEGER default 0, 709 event_handler_enabled INTEGER default 0, 710 active_checks_enabled INTEGER default 0, 711 retain_status_information INTEGER default 0, 712 retain_nonstatus_information INTEGER default 0, 713 notifications_enabled INTEGER default 0, 714 obsess_over_host INTEGER default 0, 715 failure_prediction_enabled INTEGER default 0, 716 notes TEXT default '', 717 notes_url TEXT default '', 718 action_url TEXT default '', 719 icon_image TEXT default '', 720 icon_image_alt TEXT default '', 721 vrml_image TEXT default '', 722 statusmap_image TEXT default '', 723 have_2d_coords INTEGER default 0, 724 x_2d INTEGER default 0, 725 y_2d INTEGER default 0, 726 have_3d_coords INTEGER default 0, 727 x_3d double precision default 0, 728 y_3d double precision default 0, 729 z_3d double precision default 0, 730 config_hash varchar(64) DEFAULT NULL, 731 CONSTRAINT PK_host_id PRIMARY KEY (host_id) , 732 CONSTRAINT UQ_hosts UNIQUE (instance_id,config_type,host_object_id) 733) ; 734 735-- -------------------------------------------------------- 736 737-- 738-- Table structure for table icinga_hoststatus 739-- 740 741CREATE TABLE icinga_hoststatus ( 742 hoststatus_id bigserial, 743 instance_id bigint default 0, 744 host_object_id bigint default 0, 745 status_update_time timestamp, 746 output TEXT default '', 747 long_output TEXT default '', 748 perfdata TEXT default '', 749 check_source varchar(255) default '', 750 current_state INTEGER default 0, 751 has_been_checked INTEGER default 0, 752 should_be_scheduled INTEGER default 0, 753 current_check_attempt INTEGER default 0, 754 max_check_attempts INTEGER default 0, 755 last_check timestamp, 756 next_check timestamp, 757 check_type INTEGER default 0, 758 last_state_change timestamp, 759 last_hard_state_change timestamp, 760 last_hard_state INTEGER default 0, 761 last_time_up timestamp, 762 last_time_down timestamp, 763 last_time_unreachable timestamp, 764 state_type INTEGER default 0, 765 last_notification timestamp, 766 next_notification timestamp, 767 no_more_notifications INTEGER default 0, 768 notifications_enabled INTEGER default 0, 769 problem_has_been_acknowledged INTEGER default 0, 770 acknowledgement_type INTEGER default 0, 771 current_notification_number INTEGER default 0, 772 passive_checks_enabled INTEGER default 0, 773 active_checks_enabled INTEGER default 0, 774 event_handler_enabled INTEGER default 0, 775 flap_detection_enabled INTEGER default 0, 776 is_flapping INTEGER default 0, 777 percent_state_change double precision default 0, 778 latency double precision default 0, 779 execution_time double precision default 0, 780 scheduled_downtime_depth INTEGER default 0, 781 failure_prediction_enabled INTEGER default 0, 782 process_performance_data INTEGER default 0, 783 obsess_over_host INTEGER default 0, 784 modified_host_attributes INTEGER default 0, 785 original_attributes TEXT default NULL, 786 event_handler TEXT default '', 787 check_command TEXT default '', 788 normal_check_interval double precision default 0, 789 retry_check_interval double precision default 0, 790 check_timeperiod_object_id bigint default 0, 791 is_reachable INTEGER default 0, 792 CONSTRAINT PK_hoststatus_id PRIMARY KEY (hoststatus_id) , 793 CONSTRAINT UQ_hoststatus UNIQUE (host_object_id) 794) ; 795 796-- -------------------------------------------------------- 797 798-- 799-- Table structure for table icinga_host_contactgroups 800-- 801 802CREATE TABLE icinga_host_contactgroups ( 803 host_contactgroup_id bigserial, 804 instance_id bigint default 0, 805 host_id bigint default 0, 806 contactgroup_object_id bigint default 0, 807 CONSTRAINT PK_host_contactgroup_id PRIMARY KEY (host_contactgroup_id) 808) ; 809 810-- -------------------------------------------------------- 811 812-- 813-- Table structure for table icinga_host_contacts 814-- 815 816CREATE TABLE icinga_host_contacts ( 817 host_contact_id bigserial, 818 instance_id bigint default 0, 819 host_id bigint default 0, 820 contact_object_id bigint default 0, 821 CONSTRAINT PK_host_contact_id PRIMARY KEY (host_contact_id) 822) ; 823 824-- -------------------------------------------------------- 825 826-- 827-- Table structure for table icinga_host_parenthosts 828-- 829 830CREATE TABLE icinga_host_parenthosts ( 831 host_parenthost_id bigserial, 832 instance_id bigint default 0, 833 host_id bigint default 0, 834 parent_host_object_id bigint default 0, 835 CONSTRAINT PK_host_parenthost_id PRIMARY KEY (host_parenthost_id) 836) ; 837 838-- -------------------------------------------------------- 839 840-- 841-- Table structure for table icinga_instances 842-- 843 844CREATE TABLE icinga_instances ( 845 instance_id bigserial, 846 instance_name TEXT default '', 847 instance_description TEXT default '', 848 CONSTRAINT PK_instance_id PRIMARY KEY (instance_id) 849) ; 850 851-- -------------------------------------------------------- 852 853-- 854-- Table structure for table icinga_logentries 855-- 856 857CREATE TABLE icinga_logentries ( 858 logentry_id bigserial, 859 instance_id bigint default 0, 860 logentry_time timestamp, 861 entry_time timestamp, 862 entry_time_usec INTEGER default 0, 863 logentry_type INTEGER default 0, 864 logentry_data TEXT default '', 865 realtime_data INTEGER default 0, 866 inferred_data_extracted INTEGER default 0, 867 object_id bigint default NULL, 868 CONSTRAINT PK_logentry_id PRIMARY KEY (logentry_id) 869) ; 870 871-- -------------------------------------------------------- 872 873-- 874-- Table structure for table icinga_notifications 875-- 876 877CREATE TABLE icinga_notifications ( 878 notification_id bigserial, 879 instance_id bigint default 0, 880 notification_type INTEGER default 0, 881 notification_reason INTEGER default 0, 882 object_id bigint default 0, 883 start_time timestamp, 884 start_time_usec INTEGER default 0, 885 end_time timestamp, 886 end_time_usec INTEGER default 0, 887 state INTEGER default 0, 888 output TEXT default '', 889 long_output TEXT default '', 890 escalated INTEGER default 0, 891 contacts_notified INTEGER default 0, 892 CONSTRAINT PK_notification_id PRIMARY KEY (notification_id) , 893 CONSTRAINT UQ_notifications UNIQUE (instance_id,object_id,start_time,start_time_usec) 894) ; 895 896-- -------------------------------------------------------- 897 898-- 899-- Table structure for table icinga_objects 900-- 901 902CREATE TABLE icinga_objects ( 903 object_id bigserial, 904 instance_id bigint default 0, 905 objecttype_id bigint default 0, 906 name1 TEXT, 907 name2 TEXT, 908 is_active INTEGER default 0, 909 CONSTRAINT PK_object_id PRIMARY KEY (object_id) 910-- UNIQUE (objecttype_id,name1,name2) 911) ; 912CREATE INDEX icinga_objects_i ON icinga_objects(objecttype_id,name1,name2); 913 914-- -------------------------------------------------------- 915 916-- 917-- Table structure for table icinga_processevents 918-- 919 920CREATE TABLE icinga_processevents ( 921 processevent_id bigserial, 922 instance_id bigint default 0, 923 event_type INTEGER default 0, 924 event_time timestamp, 925 event_time_usec INTEGER default 0, 926 process_id bigint default 0, 927 program_name TEXT default '', 928 program_version TEXT default '', 929 program_date TEXT default '', 930 CONSTRAINT PK_processevent_id PRIMARY KEY (processevent_id) 931) ; 932 933-- -------------------------------------------------------- 934 935-- 936-- Table structure for table icinga_programstatus 937-- 938 939CREATE TABLE icinga_programstatus ( 940 programstatus_id bigserial, 941 instance_id bigint default 0, 942 program_version TEXT default NULL, 943 status_update_time timestamp, 944 program_start_time timestamp, 945 program_end_time timestamp, 946 is_currently_running INTEGER default 0, 947 endpoint_name TEXT default '', 948 process_id bigint default 0, 949 daemon_mode INTEGER default 0, 950 last_command_check timestamp, 951 last_log_rotation timestamp, 952 notifications_enabled INTEGER default 0, 953 disable_notif_expire_time timestamp, 954 active_service_checks_enabled INTEGER default 0, 955 passive_service_checks_enabled INTEGER default 0, 956 active_host_checks_enabled INTEGER default 0, 957 passive_host_checks_enabled INTEGER default 0, 958 event_handlers_enabled INTEGER default 0, 959 flap_detection_enabled INTEGER default 0, 960 failure_prediction_enabled INTEGER default 0, 961 process_performance_data INTEGER default 0, 962 obsess_over_hosts INTEGER default 0, 963 obsess_over_services INTEGER default 0, 964 modified_host_attributes INTEGER default 0, 965 modified_service_attributes INTEGER default 0, 966 global_host_event_handler TEXT default '', 967 global_service_event_handler TEXT default '', 968 config_dump_in_progress INTEGER default 0, 969 CONSTRAINT PK_programstatus_id PRIMARY KEY (programstatus_id) , 970 CONSTRAINT UQ_programstatus UNIQUE (instance_id) 971) ; 972 973-- -------------------------------------------------------- 974 975-- 976-- Table structure for table icinga_runtimevariables 977-- 978 979CREATE TABLE icinga_runtimevariables ( 980 runtimevariable_id bigserial, 981 instance_id bigint default 0, 982 varname TEXT default '', 983 varvalue TEXT default '', 984 CONSTRAINT PK_runtimevariable_id PRIMARY KEY (runtimevariable_id) 985) ; 986 987-- -------------------------------------------------------- 988 989-- 990-- Table structure for table icinga_scheduleddowntime 991-- 992 993CREATE TABLE icinga_scheduleddowntime ( 994 scheduleddowntime_id bigserial, 995 instance_id bigint default 0, 996 downtime_type INTEGER default 0, 997 object_id bigint default 0, 998 entry_time timestamp, 999 author_name TEXT default '', 1000 comment_data TEXT default '', 1001 internal_downtime_id bigint default 0, 1002 triggered_by_id bigint default 0, 1003 is_fixed INTEGER default 0, 1004 duration BIGINT default 0, 1005 scheduled_start_time timestamp, 1006 scheduled_end_time timestamp, 1007 was_started INTEGER default 0, 1008 actual_start_time timestamp, 1009 actual_start_time_usec INTEGER default 0, 1010 is_in_effect INTEGER default 0, 1011 trigger_time timestamp, 1012 name TEXT default NULL, 1013 session_token INTEGER default NULL, 1014 CONSTRAINT PK_scheduleddowntime_id PRIMARY KEY (scheduleddowntime_id) 1015) ; 1016 1017-- -------------------------------------------------------- 1018 1019-- 1020-- Table structure for table icinga_servicechecks 1021-- 1022 1023CREATE TABLE icinga_servicechecks ( 1024 servicecheck_id bigserial, 1025 instance_id bigint default 0, 1026 service_object_id bigint default 0, 1027 check_type INTEGER default 0, 1028 current_check_attempt INTEGER default 0, 1029 max_check_attempts INTEGER default 0, 1030 state INTEGER default 0, 1031 state_type INTEGER default 0, 1032 start_time timestamp, 1033 start_time_usec INTEGER default 0, 1034 end_time timestamp, 1035 end_time_usec INTEGER default 0, 1036 command_object_id bigint default 0, 1037 command_args TEXT default '', 1038 command_line TEXT default '', 1039 timeout INTEGER default 0, 1040 early_timeout INTEGER default 0, 1041 execution_time double precision default 0, 1042 latency double precision default 0, 1043 return_code INTEGER default 0, 1044 output TEXT default '', 1045 long_output TEXT default '', 1046 perfdata TEXT default '', 1047 CONSTRAINT PK_servicecheck_id PRIMARY KEY (servicecheck_id) 1048) ; 1049 1050-- -------------------------------------------------------- 1051 1052-- 1053-- Table structure for table icinga_servicedependencies 1054-- 1055 1056CREATE TABLE icinga_servicedependencies ( 1057 servicedependency_id bigserial, 1058 instance_id bigint default 0, 1059 config_type INTEGER default 0, 1060 service_object_id bigint default 0, 1061 dependent_service_object_id bigint default 0, 1062 dependency_type INTEGER default 0, 1063 inherits_parent INTEGER default 0, 1064 timeperiod_object_id bigint default 0, 1065 fail_on_ok INTEGER default 0, 1066 fail_on_warning INTEGER default 0, 1067 fail_on_unknown INTEGER default 0, 1068 fail_on_critical INTEGER default 0, 1069 CONSTRAINT PK_servicedependency_id PRIMARY KEY (servicedependency_id) 1070) ; 1071CREATE INDEX idx_servicedependencies ON icinga_servicedependencies(instance_id,config_type,service_object_id,dependent_service_object_id,dependency_type,inherits_parent,fail_on_ok,fail_on_warning,fail_on_unknown,fail_on_critical); 1072 1073-- -------------------------------------------------------- 1074 1075-- 1076-- Table structure for table icinga_serviceescalations 1077-- 1078 1079CREATE TABLE icinga_serviceescalations ( 1080 serviceescalation_id bigserial, 1081 instance_id bigint default 0, 1082 config_type INTEGER default 0, 1083 service_object_id bigint default 0, 1084 timeperiod_object_id bigint default 0, 1085 first_notification INTEGER default 0, 1086 last_notification INTEGER default 0, 1087 notification_interval double precision default 0, 1088 escalate_on_recovery INTEGER default 0, 1089 escalate_on_warning INTEGER default 0, 1090 escalate_on_unknown INTEGER default 0, 1091 escalate_on_critical INTEGER default 0, 1092 CONSTRAINT PK_serviceescalation_id PRIMARY KEY (serviceescalation_id) , 1093 CONSTRAINT UQ_serviceescalations UNIQUE (instance_id,config_type,service_object_id,timeperiod_object_id,first_notification,last_notification) 1094) ; 1095 1096-- -------------------------------------------------------- 1097 1098-- 1099-- Table structure for table icinga_serviceescalation_contactgroups 1100-- 1101 1102CREATE TABLE icinga_serviceescalation_contactgroups ( 1103 serviceescalation_contactgroup_id bigserial, 1104 instance_id bigint default 0, 1105 serviceescalation_id bigint default 0, 1106 contactgroup_object_id bigint default 0, 1107 CONSTRAINT PK_serviceescalation_contactgroup_id PRIMARY KEY (serviceescalation_contactgroup_id) , 1108 CONSTRAINT UQ_serviceescalation_contactgro UNIQUE (serviceescalation_id,contactgroup_object_id) 1109) ; 1110 1111-- -------------------------------------------------------- 1112 1113-- 1114-- Table structure for table icinga_serviceescalation_contacts 1115-- 1116 1117CREATE TABLE icinga_serviceescalation_contacts ( 1118 serviceescalation_contact_id bigserial, 1119 instance_id bigint default 0, 1120 serviceescalation_id bigint default 0, 1121 contact_object_id bigint default 0, 1122 CONSTRAINT PK_serviceescalation_contact_id PRIMARY KEY (serviceescalation_contact_id) , 1123 CONSTRAINT UQ_serviceescalation_contacts UNIQUE (instance_id,serviceescalation_id,contact_object_id) 1124) ; 1125 1126-- -------------------------------------------------------- 1127 1128-- 1129-- Table structure for table icinga_servicegroups 1130-- 1131 1132CREATE TABLE icinga_servicegroups ( 1133 servicegroup_id bigserial, 1134 instance_id bigint default 0, 1135 config_type INTEGER default 0, 1136 servicegroup_object_id bigint default 0, 1137 alias TEXT default '', 1138 notes TEXT default NULL, 1139 notes_url TEXT default NULL, 1140 action_url TEXT default NULL, 1141 config_hash varchar(64) DEFAULT NULL, 1142 CONSTRAINT PK_servicegroup_id PRIMARY KEY (servicegroup_id) , 1143 CONSTRAINT UQ_servicegroups UNIQUE (instance_id,config_type,servicegroup_object_id) 1144) ; 1145 1146-- -------------------------------------------------------- 1147 1148-- 1149-- Table structure for table icinga_servicegroup_members 1150-- 1151 1152CREATE TABLE icinga_servicegroup_members ( 1153 servicegroup_member_id bigserial, 1154 instance_id bigint default 0, 1155 servicegroup_id bigint default 0, 1156 service_object_id bigint default 0, 1157 session_token INTEGER default NULL, 1158 CONSTRAINT PK_servicegroup_member_id PRIMARY KEY (servicegroup_member_id) 1159) ; 1160 1161-- -------------------------------------------------------- 1162 1163-- 1164-- Table structure for table icinga_services 1165-- 1166 1167CREATE TABLE icinga_services ( 1168 service_id bigserial, 1169 instance_id bigint default 0, 1170 config_type INTEGER default 0, 1171 host_object_id bigint default 0, 1172 service_object_id bigint default 0, 1173 display_name TEXT default '', 1174 check_command_object_id bigint default 0, 1175 check_command_args TEXT default '', 1176 eventhandler_command_object_id bigint default 0, 1177 eventhandler_command_args TEXT default '', 1178 notification_timeperiod_object_id bigint default 0, 1179 check_timeperiod_object_id bigint default 0, 1180 failure_prediction_options TEXT default '', 1181 check_interval double precision default 0, 1182 retry_interval double precision default 0, 1183 max_check_attempts INTEGER default 0, 1184 first_notification_delay double precision default 0, 1185 notification_interval double precision default 0, 1186 notify_on_warning INTEGER default 0, 1187 notify_on_unknown INTEGER default 0, 1188 notify_on_critical INTEGER default 0, 1189 notify_on_recovery INTEGER default 0, 1190 notify_on_flapping INTEGER default 0, 1191 notify_on_downtime INTEGER default 0, 1192 stalk_on_ok INTEGER default 0, 1193 stalk_on_warning INTEGER default 0, 1194 stalk_on_unknown INTEGER default 0, 1195 stalk_on_critical INTEGER default 0, 1196 is_volatile INTEGER default 0, 1197 flap_detection_enabled INTEGER default 0, 1198 flap_detection_on_ok INTEGER default 0, 1199 flap_detection_on_warning INTEGER default 0, 1200 flap_detection_on_unknown INTEGER default 0, 1201 flap_detection_on_critical INTEGER default 0, 1202 low_flap_threshold double precision default 0, 1203 high_flap_threshold double precision default 0, 1204 process_performance_data INTEGER default 0, 1205 freshness_checks_enabled INTEGER default 0, 1206 freshness_threshold INTEGER default 0, 1207 passive_checks_enabled INTEGER default 0, 1208 event_handler_enabled INTEGER default 0, 1209 active_checks_enabled INTEGER default 0, 1210 retain_status_information INTEGER default 0, 1211 retain_nonstatus_information INTEGER default 0, 1212 notifications_enabled INTEGER default 0, 1213 obsess_over_service INTEGER default 0, 1214 failure_prediction_enabled INTEGER default 0, 1215 notes TEXT default '', 1216 notes_url TEXT default '', 1217 action_url TEXT default '', 1218 icon_image TEXT default '', 1219 icon_image_alt TEXT default '', 1220 config_hash varchar(64) DEFAULT NULL, 1221 CONSTRAINT PK_service_id PRIMARY KEY (service_id) , 1222 CONSTRAINT UQ_services UNIQUE (instance_id,config_type,service_object_id) 1223) ; 1224 1225-- -------------------------------------------------------- 1226 1227-- 1228-- Table structure for table icinga_servicestatus 1229-- 1230 1231CREATE TABLE icinga_servicestatus ( 1232 servicestatus_id bigserial, 1233 instance_id bigint default 0, 1234 service_object_id bigint default 0, 1235 status_update_time timestamp, 1236 output TEXT default '', 1237 long_output TEXT default '', 1238 perfdata TEXT default '', 1239 check_source varchar(255) default '', 1240 current_state INTEGER default 0, 1241 has_been_checked INTEGER default 0, 1242 should_be_scheduled INTEGER default 0, 1243 current_check_attempt INTEGER default 0, 1244 max_check_attempts INTEGER default 0, 1245 last_check timestamp, 1246 next_check timestamp, 1247 check_type INTEGER default 0, 1248 last_state_change timestamp, 1249 last_hard_state_change timestamp, 1250 last_hard_state INTEGER default 0, 1251 last_time_ok timestamp, 1252 last_time_warning timestamp, 1253 last_time_unknown timestamp, 1254 last_time_critical timestamp, 1255 state_type INTEGER default 0, 1256 last_notification timestamp, 1257 next_notification timestamp, 1258 no_more_notifications INTEGER default 0, 1259 notifications_enabled INTEGER default 0, 1260 problem_has_been_acknowledged INTEGER default 0, 1261 acknowledgement_type INTEGER default 0, 1262 current_notification_number INTEGER default 0, 1263 passive_checks_enabled INTEGER default 0, 1264 active_checks_enabled INTEGER default 0, 1265 event_handler_enabled INTEGER default 0, 1266 flap_detection_enabled INTEGER default 0, 1267 is_flapping INTEGER default 0, 1268 percent_state_change double precision default 0, 1269 latency double precision default 0, 1270 execution_time double precision default 0, 1271 scheduled_downtime_depth INTEGER default 0, 1272 failure_prediction_enabled INTEGER default 0, 1273 process_performance_data INTEGER default 0, 1274 obsess_over_service INTEGER default 0, 1275 modified_service_attributes INTEGER default 0, 1276 original_attributes TEXT default NULL, 1277 event_handler TEXT default '', 1278 check_command TEXT default '', 1279 normal_check_interval double precision default 0, 1280 retry_check_interval double precision default 0, 1281 check_timeperiod_object_id bigint default 0, 1282 is_reachable INTEGER default 0, 1283 CONSTRAINT PK_servicestatus_id PRIMARY KEY (servicestatus_id) , 1284 CONSTRAINT UQ_servicestatus UNIQUE (service_object_id) 1285) ; 1286 1287-- -------------------------------------------------------- 1288 1289-- 1290-- Table structure for table icinga_service_contactgroups 1291-- 1292 1293CREATE TABLE icinga_service_contactgroups ( 1294 service_contactgroup_id bigserial, 1295 instance_id bigint default 0, 1296 service_id bigint default 0, 1297 contactgroup_object_id bigint default 0, 1298 CONSTRAINT PK_service_contactgroup_id PRIMARY KEY (service_contactgroup_id) 1299) ; 1300 1301-- -------------------------------------------------------- 1302 1303-- 1304-- Table structure for table icinga_service_contacts 1305-- 1306 1307CREATE TABLE icinga_service_contacts ( 1308 service_contact_id bigserial, 1309 instance_id bigint default 0, 1310 service_id bigint default 0, 1311 contact_object_id bigint default 0, 1312 CONSTRAINT PK_service_contact_id PRIMARY KEY (service_contact_id) 1313) ; 1314 1315-- -------------------------------------------------------- 1316 1317-- 1318-- Table structure for table icinga_statehistory 1319-- 1320 1321CREATE TABLE icinga_statehistory ( 1322 statehistory_id bigserial, 1323 instance_id bigint default 0, 1324 state_time timestamp, 1325 state_time_usec INTEGER default 0, 1326 object_id bigint default 0, 1327 state_change INTEGER default 0, 1328 state INTEGER default 0, 1329 state_type INTEGER default 0, 1330 current_check_attempt INTEGER default 0, 1331 max_check_attempts INTEGER default 0, 1332 last_state INTEGER default '-1', 1333 last_hard_state INTEGER default '-1', 1334 output TEXT default '', 1335 long_output TEXT default '', 1336 check_source varchar(255) default '', 1337 CONSTRAINT PK_statehistory_id PRIMARY KEY (statehistory_id) 1338) ; 1339 1340-- -------------------------------------------------------- 1341 1342-- 1343-- Table structure for table icinga_systemcommands 1344-- 1345 1346CREATE TABLE icinga_systemcommands ( 1347 systemcommand_id bigserial, 1348 instance_id bigint default 0, 1349 start_time timestamp, 1350 start_time_usec INTEGER default 0, 1351 end_time timestamp, 1352 end_time_usec INTEGER default 0, 1353 command_line TEXT default '', 1354 timeout INTEGER default 0, 1355 early_timeout INTEGER default 0, 1356 execution_time double precision default 0, 1357 return_code INTEGER default 0, 1358 output TEXT default '', 1359 long_output TEXT default '', 1360 CONSTRAINT PK_systemcommand_id PRIMARY KEY (systemcommand_id) , 1361 CONSTRAINT UQ_systemcommands UNIQUE (instance_id,start_time,start_time_usec) 1362) ; 1363 1364-- -------------------------------------------------------- 1365 1366-- 1367-- Table structure for table icinga_timeperiods 1368-- 1369 1370CREATE TABLE icinga_timeperiods ( 1371 timeperiod_id bigserial, 1372 instance_id bigint default 0, 1373 config_type INTEGER default 0, 1374 timeperiod_object_id bigint default 0, 1375 alias TEXT default '', 1376 config_hash varchar(64) DEFAULT NULL, 1377 CONSTRAINT PK_timeperiod_id PRIMARY KEY (timeperiod_id) , 1378 CONSTRAINT UQ_timeperiods UNIQUE (instance_id,config_type,timeperiod_object_id) 1379) ; 1380 1381-- -------------------------------------------------------- 1382 1383-- 1384-- Table structure for table icinga_timeperiod_timeranges 1385-- 1386 1387CREATE TABLE icinga_timeperiod_timeranges ( 1388 timeperiod_timerange_id bigserial, 1389 instance_id bigint default 0, 1390 timeperiod_id bigint default 0, 1391 day INTEGER default 0, 1392 start_sec INTEGER default 0, 1393 end_sec INTEGER default 0, 1394 CONSTRAINT PK_timeperiod_timerange_id PRIMARY KEY (timeperiod_timerange_id) 1395) ; 1396 1397 1398-- -------------------------------------------------------- 1399-- Icinga 2 specific schema extensions 1400-- -------------------------------------------------------- 1401 1402-- 1403-- Table structure for table icinga_endpoints 1404-- 1405 1406CREATE TABLE icinga_endpoints ( 1407 endpoint_id bigserial, 1408 instance_id bigint default 0, 1409 endpoint_object_id bigint default 0, 1410 zone_object_id bigint default 0, 1411 config_type integer default 0, 1412 identity text DEFAULT NULL, 1413 node text DEFAULT NULL, 1414 config_hash varchar(64) DEFAULT NULL, 1415 CONSTRAINT PK_endpoint_id PRIMARY KEY (endpoint_id) , 1416 CONSTRAINT UQ_endpoints UNIQUE (instance_id,config_type,endpoint_object_id) 1417) ; 1418 1419-- -------------------------------------------------------- 1420 1421-- 1422-- Table structure for table icinga_endpointstatus 1423-- 1424 1425CREATE TABLE icinga_endpointstatus ( 1426 endpointstatus_id bigserial, 1427 instance_id bigint default 0, 1428 endpoint_object_id bigint default 0, 1429 zone_object_id bigint default 0, 1430 status_update_time timestamp, 1431 identity text DEFAULT NULL, 1432 node text DEFAULT NULL, 1433 is_connected integer default 0, 1434 CONSTRAINT PK_endpointstatus_id PRIMARY KEY (endpointstatus_id) , 1435 CONSTRAINT UQ_endpointstatus UNIQUE (endpoint_object_id) 1436) ; 1437 1438-- 1439-- Table structure for table icinga_zones 1440-- 1441 1442CREATE TABLE icinga_zones ( 1443 zone_id bigserial, 1444 instance_id bigint default 0, 1445 zone_object_id bigint default 0, 1446 parent_zone_object_id bigint default 0, 1447 config_type integer default 0, 1448 is_global integer default 0, 1449 config_hash varchar(64) DEFAULT NULL, 1450 CONSTRAINT PK_zone_id PRIMARY KEY (zone_id) , 1451 CONSTRAINT UQ_zones UNIQUE (instance_id,config_type,zone_object_id) 1452) ; 1453 1454-- -------------------------------------------------------- 1455 1456-- 1457-- Table structure for table icinga_zonestatus 1458-- 1459 1460CREATE TABLE icinga_zonestatus ( 1461 zonestatus_id bigserial, 1462 instance_id bigint default 0, 1463 zone_object_id bigint default 0, 1464 parent_zone_object_id bigint default 0, 1465 status_update_time timestamp, 1466 CONSTRAINT PK_zonestatus_id PRIMARY KEY (zonestatus_id) , 1467 CONSTRAINT UQ_zonestatus UNIQUE (zone_object_id) 1468) ; 1469 1470 1471ALTER TABLE icinga_servicestatus ADD COLUMN endpoint_object_id bigint default NULL; 1472ALTER TABLE icinga_hoststatus ADD COLUMN endpoint_object_id bigint default NULL; 1473ALTER TABLE icinga_contactstatus ADD COLUMN endpoint_object_id bigint default NULL; 1474ALTER TABLE icinga_programstatus ADD COLUMN endpoint_object_id bigint default NULL; 1475ALTER TABLE icinga_comments ADD COLUMN endpoint_object_id bigint default NULL; 1476ALTER TABLE icinga_scheduleddowntime ADD COLUMN endpoint_object_id bigint default NULL; 1477ALTER TABLE icinga_runtimevariables ADD COLUMN endpoint_object_id bigint default NULL; 1478ALTER TABLE icinga_customvariablestatus ADD COLUMN endpoint_object_id bigint default NULL; 1479 1480ALTER TABLE icinga_acknowledgements ADD COLUMN endpoint_object_id bigint default NULL; 1481ALTER TABLE icinga_commenthistory ADD COLUMN endpoint_object_id bigint default NULL; 1482ALTER TABLE icinga_contactnotifications ADD COLUMN endpoint_object_id bigint default NULL; 1483ALTER TABLE icinga_downtimehistory ADD COLUMN endpoint_object_id bigint default NULL; 1484ALTER TABLE icinga_eventhandlers ADD COLUMN endpoint_object_id bigint default NULL; 1485ALTER TABLE icinga_externalcommands ADD COLUMN endpoint_object_id bigint default NULL; 1486ALTER TABLE icinga_flappinghistory ADD COLUMN endpoint_object_id bigint default NULL; 1487ALTER TABLE icinga_hostchecks ADD COLUMN endpoint_object_id bigint default NULL; 1488ALTER TABLE icinga_logentries ADD COLUMN endpoint_object_id bigint default NULL; 1489ALTER TABLE icinga_notifications ADD COLUMN endpoint_object_id bigint default NULL; 1490ALTER TABLE icinga_processevents ADD COLUMN endpoint_object_id bigint default NULL; 1491ALTER TABLE icinga_servicechecks ADD COLUMN endpoint_object_id bigint default NULL; 1492ALTER TABLE icinga_statehistory ADD COLUMN endpoint_object_id bigint default NULL; 1493ALTER TABLE icinga_systemcommands ADD COLUMN endpoint_object_id bigint default NULL; 1494 1495 1496-- ----------------------------------------- 1497-- add index (delete) 1498-- ----------------------------------------- 1499 1500-- for periodic delete 1501-- instance_id and 1502-- TIMEDEVENTS => scheduled_time 1503-- SYSTEMCOMMANDS, SERVICECHECKS, HOSTCHECKS, EVENTHANDLERS => start_time 1504-- EXTERNALCOMMANDS => entry_time 1505 1506-- instance_id 1507CREATE INDEX systemcommands_i_id_idx on icinga_systemcommands(instance_id); 1508CREATE INDEX servicechecks_i_id_idx on icinga_servicechecks(instance_id); 1509CREATE INDEX hostchecks_i_id_idx on icinga_hostchecks(instance_id); 1510CREATE INDEX eventhandlers_i_id_idx on icinga_eventhandlers(instance_id); 1511CREATE INDEX externalcommands_i_id_idx on icinga_externalcommands(instance_id); 1512 1513-- time 1514CREATE INDEX systemcommands_time_id_idx on icinga_systemcommands(start_time); 1515CREATE INDEX servicechecks_time_id_idx on icinga_servicechecks(start_time); 1516CREATE INDEX hostchecks_time_id_idx on icinga_hostchecks(start_time); 1517CREATE INDEX eventhandlers_time_id_idx on icinga_eventhandlers(start_time); 1518CREATE INDEX externalcommands_time_id_idx on icinga_externalcommands(entry_time); 1519 1520 1521-- for starting cleanup - referenced in dbhandler.c:882 1522-- instance_id only 1523 1524-- realtime data 1525CREATE INDEX programstatus_i_id_idx on icinga_programstatus(instance_id); 1526CREATE INDEX hoststatus_i_id_idx on icinga_hoststatus(instance_id); 1527CREATE INDEX servicestatus_i_id_idx on icinga_servicestatus(instance_id); 1528CREATE INDEX contactstatus_i_id_idx on icinga_contactstatus(instance_id); 1529CREATE INDEX comments_i_id_idx on icinga_comments(instance_id); 1530CREATE INDEX scheduleddowntime_i_id_idx on icinga_scheduleddowntime(instance_id); 1531CREATE INDEX runtimevariables_i_id_idx on icinga_runtimevariables(instance_id); 1532CREATE INDEX customvariablestatus_i_id_idx on icinga_customvariablestatus(instance_id); 1533 1534-- config data 1535CREATE INDEX configfiles_i_id_idx on icinga_configfiles(instance_id); 1536CREATE INDEX configfilevariables_i_id_idx on icinga_configfilevariables(instance_id); 1537CREATE INDEX customvariables_i_id_idx on icinga_customvariables(instance_id); 1538CREATE INDEX commands_i_id_idx on icinga_commands(instance_id); 1539CREATE INDEX timeperiods_i_id_idx on icinga_timeperiods(instance_id); 1540CREATE INDEX timeperiod_timeranges_i_id_idx on icinga_timeperiod_timeranges(instance_id); 1541CREATE INDEX contactgroups_i_id_idx on icinga_contactgroups(instance_id); 1542CREATE INDEX contactgroup_members_i_id_idx on icinga_contactgroup_members(instance_id); 1543CREATE INDEX hostgroups_i_id_idx on icinga_hostgroups(instance_id); 1544CREATE INDEX hostgroup_members_i_id_idx on icinga_hostgroup_members(instance_id); 1545CREATE INDEX servicegroups_i_id_idx on icinga_servicegroups(instance_id); 1546CREATE INDEX servicegroup_members_i_id_idx on icinga_servicegroup_members(instance_id); 1547CREATE INDEX hostesc_i_id_idx on icinga_hostescalations(instance_id); 1548CREATE INDEX hostesc_contacts_i_id_idx on icinga_hostescalation_contacts(instance_id); 1549CREATE INDEX serviceesc_i_id_idx on icinga_serviceescalations(instance_id); 1550CREATE INDEX serviceesc_contacts_i_id_idx on icinga_serviceescalation_contacts(instance_id); 1551CREATE INDEX hostdependencies_i_id_idx on icinga_hostdependencies(instance_id); 1552CREATE INDEX contacts_i_id_idx on icinga_contacts(instance_id); 1553CREATE INDEX contact_addresses_i_id_idx on icinga_contact_addresses(instance_id); 1554CREATE INDEX contact_notifcommands_i_id_idx on icinga_contact_notificationcommands(instance_id); 1555CREATE INDEX hosts_i_id_idx on icinga_hosts(instance_id); 1556CREATE INDEX host_parenthosts_i_id_idx on icinga_host_parenthosts(instance_id); 1557CREATE INDEX host_contacts_i_id_idx on icinga_host_contacts(instance_id); 1558CREATE INDEX services_i_id_idx on icinga_services(instance_id); 1559CREATE INDEX service_contacts_i_id_idx on icinga_service_contacts(instance_id); 1560CREATE INDEX service_contactgroups_i_id_idx on icinga_service_contactgroups(instance_id); 1561CREATE INDEX host_contactgroups_i_id_idx on icinga_host_contactgroups(instance_id); 1562CREATE INDEX hostesc_cgroups_i_id_idx on icinga_hostescalation_contactgroups(instance_id); 1563CREATE INDEX serviceesc_cgroups_i_id_idx on icinga_serviceescalation_contactgroups(instance_id); 1564 1565-- ----------------------------------------- 1566-- more index stuff (WHERE clauses) 1567-- ----------------------------------------- 1568 1569-- hosts 1570CREATE INDEX hosts_host_object_id_idx on icinga_hosts(host_object_id); 1571 1572-- hoststatus 1573CREATE INDEX hoststatus_stat_upd_time_idx on icinga_hoststatus(status_update_time); 1574CREATE INDEX hoststatus_current_state_idx on icinga_hoststatus(current_state); 1575CREATE INDEX hoststatus_check_type_idx on icinga_hoststatus(check_type); 1576CREATE INDEX hoststatus_state_type_idx on icinga_hoststatus(state_type); 1577CREATE INDEX hoststatus_last_state_chg_idx on icinga_hoststatus(last_state_change); 1578CREATE INDEX hoststatus_notif_enabled_idx on icinga_hoststatus(notifications_enabled); 1579CREATE INDEX hoststatus_problem_ack_idx on icinga_hoststatus(problem_has_been_acknowledged); 1580CREATE INDEX hoststatus_act_chks_en_idx on icinga_hoststatus(active_checks_enabled); 1581CREATE INDEX hoststatus_pas_chks_en_idx on icinga_hoststatus(passive_checks_enabled); 1582CREATE INDEX hoststatus_event_hdl_en_idx on icinga_hoststatus(event_handler_enabled); 1583CREATE INDEX hoststatus_flap_det_en_idx on icinga_hoststatus(flap_detection_enabled); 1584CREATE INDEX hoststatus_is_flapping_idx on icinga_hoststatus(is_flapping); 1585CREATE INDEX hoststatus_p_state_chg_idx on icinga_hoststatus(percent_state_change); 1586CREATE INDEX hoststatus_latency_idx on icinga_hoststatus(latency); 1587CREATE INDEX hoststatus_ex_time_idx on icinga_hoststatus(execution_time); 1588CREATE INDEX hoststatus_sch_downt_d_idx on icinga_hoststatus(scheduled_downtime_depth); 1589 1590-- services 1591CREATE INDEX services_host_object_id_idx on icinga_services(host_object_id); 1592 1593--servicestatus 1594CREATE INDEX srvcstatus_stat_upd_time_idx on icinga_servicestatus(status_update_time); 1595CREATE INDEX srvcstatus_current_state_idx on icinga_servicestatus(current_state); 1596CREATE INDEX srvcstatus_check_type_idx on icinga_servicestatus(check_type); 1597CREATE INDEX srvcstatus_state_type_idx on icinga_servicestatus(state_type); 1598CREATE INDEX srvcstatus_last_state_chg_idx on icinga_servicestatus(last_state_change); 1599CREATE INDEX srvcstatus_notif_enabled_idx on icinga_servicestatus(notifications_enabled); 1600CREATE INDEX srvcstatus_problem_ack_idx on icinga_servicestatus(problem_has_been_acknowledged); 1601CREATE INDEX srvcstatus_act_chks_en_idx on icinga_servicestatus(active_checks_enabled); 1602CREATE INDEX srvcstatus_pas_chks_en_idx on icinga_servicestatus(passive_checks_enabled); 1603CREATE INDEX srvcstatus_event_hdl_en_idx on icinga_servicestatus(event_handler_enabled); 1604CREATE INDEX srvcstatus_flap_det_en_idx on icinga_servicestatus(flap_detection_enabled); 1605CREATE INDEX srvcstatus_is_flapping_idx on icinga_servicestatus(is_flapping); 1606CREATE INDEX srvcstatus_p_state_chg_idx on icinga_servicestatus(percent_state_change); 1607CREATE INDEX srvcstatus_latency_idx on icinga_servicestatus(latency); 1608CREATE INDEX srvcstatus_ex_time_idx on icinga_servicestatus(execution_time); 1609CREATE INDEX srvcstatus_sch_downt_d_idx on icinga_servicestatus(scheduled_downtime_depth); 1610 1611-- hostchecks 1612CREATE INDEX hostchks_h_obj_id_idx on icinga_hostchecks(host_object_id); 1613 1614-- servicechecks 1615CREATE INDEX servicechks_s_obj_id_idx on icinga_servicechecks(service_object_id); 1616 1617-- objects 1618CREATE INDEX objects_objtype_id_idx ON icinga_objects(objecttype_id); 1619CREATE INDEX objects_name1_idx ON icinga_objects(name1); 1620CREATE INDEX objects_name2_idx ON icinga_objects(name2); 1621CREATE INDEX objects_inst_id_idx ON icinga_objects(instance_id); 1622 1623-- instances 1624-- CREATE INDEX instances_name_idx on icinga_instances(instance_name); 1625 1626-- logentries 1627-- CREATE INDEX loge_instance_id_idx on icinga_logentries(instance_id); 1628-- #236 1629CREATE INDEX loge_time_idx on icinga_logentries(logentry_time); 1630-- CREATE INDEX loge_data_idx on icinga_logentries(logentry_data); 1631CREATE INDEX loge_inst_id_time_idx on icinga_logentries (instance_id, logentry_time); 1632 1633 1634-- commenthistory 1635-- CREATE INDEX c_hist_instance_id_idx on icinga_logentries(instance_id); 1636-- CREATE INDEX c_hist_c_time_idx on icinga_logentries(comment_time); 1637-- CREATE INDEX c_hist_i_c_id_idx on icinga_logentries(internal_comment_id); 1638 1639-- downtimehistory 1640-- CREATE INDEX d_t_hist_nstance_id_idx on icinga_downtimehistory(instance_id); 1641-- CREATE INDEX d_t_hist_type_idx on icinga_downtimehistory(downtime_type); 1642-- CREATE INDEX d_t_hist_object_id_idx on icinga_downtimehistory(object_id); 1643-- CREATE INDEX d_t_hist_entry_time_idx on icinga_downtimehistory(entry_time); 1644-- CREATE INDEX d_t_hist_sched_start_idx on icinga_downtimehistory(scheduled_start_time); 1645-- CREATE INDEX d_t_hist_sched_end_idx on icinga_downtimehistory(scheduled_end_time); 1646 1647-- scheduleddowntime 1648-- CREATE INDEX sched_d_t_downtime_type_idx on icinga_scheduleddowntime(downtime_type); 1649-- CREATE INDEX sched_d_t_object_id_idx on icinga_scheduleddowntime(object_id); 1650-- CREATE INDEX sched_d_t_entry_time_idx on icinga_scheduleddowntime(entry_time); 1651-- CREATE INDEX sched_d_t_start_time_idx on icinga_scheduleddowntime(scheduled_start_time); 1652-- CREATE INDEX sched_d_t_end_time_idx on icinga_scheduleddowntime(scheduled_end_time); 1653 1654-- Icinga Web Notifications 1655CREATE INDEX notification_idx ON icinga_notifications(notification_type, object_id, start_time); 1656CREATE INDEX notification_object_id_idx ON icinga_notifications(object_id); 1657CREATE INDEX contact_notification_idx ON icinga_contactnotifications(notification_id, contact_object_id); 1658CREATE INDEX contacts_object_id_idx ON icinga_contacts(contact_object_id); 1659CREATE INDEX contact_notif_meth_notif_idx ON icinga_contactnotificationmethods(contactnotification_id, command_object_id); 1660CREATE INDEX command_object_idx ON icinga_commands(object_id); 1661CREATE INDEX services_combined_object_idx ON icinga_services(service_object_id, host_object_id); 1662 1663-- statehistory 1664CREATE INDEX statehist_i_id_o_id_s_ty_s_ti on icinga_statehistory(instance_id, object_id, state_type, state_time); 1665--#2274 1666create index statehist_state_idx on icinga_statehistory(object_id,state); 1667 1668-- #2618 1669CREATE INDEX cntgrpmbrs_cgid_coid ON icinga_contactgroup_members (contactgroup_id,contact_object_id); 1670CREATE INDEX hstgrpmbrs_hgid_hoid ON icinga_hostgroup_members (hostgroup_id,host_object_id); 1671CREATE INDEX hstcntgrps_hid_cgoid ON icinga_host_contactgroups (host_id,contactgroup_object_id); 1672CREATE INDEX hstprnthsts_hid_phoid ON icinga_host_parenthosts (host_id,parent_host_object_id); 1673CREATE INDEX runtimevars_iid_varn ON icinga_runtimevariables (instance_id,varname); 1674CREATE INDEX sgmbrs_sgid_soid ON icinga_servicegroup_members (servicegroup_id,service_object_id); 1675CREATE INDEX scgrps_sid_cgoid ON icinga_service_contactgroups (service_id,contactgroup_object_id); 1676CREATE INDEX tperiod_tid_d_ss_es ON icinga_timeperiod_timeranges (timeperiod_id,day,start_sec,end_sec); 1677 1678-- #3649 1679CREATE INDEX sla_idx_sthist ON icinga_statehistory (object_id, state_time DESC); 1680CREATE INDEX sla_idx_dohist ON icinga_downtimehistory (object_id, actual_start_time, actual_end_time); 1681CREATE INDEX sla_idx_obj ON icinga_objects (objecttype_id, is_active, name1); 1682 1683-- #4985 1684CREATE INDEX commenthistory_delete_idx ON icinga_commenthistory (instance_id, comment_time, internal_comment_id); 1685 1686-- #10070 1687CREATE INDEX idx_comments_object_id on icinga_comments(object_id); 1688CREATE INDEX idx_scheduleddowntime_object_id on icinga_scheduleddowntime(object_id); 1689 1690-- #10066 1691CREATE INDEX idx_endpoints_object_id on icinga_endpoints(endpoint_object_id); 1692CREATE INDEX idx_endpointstatus_object_id on icinga_endpointstatus(endpoint_object_id); 1693 1694CREATE INDEX idx_endpoints_zone_object_id on icinga_endpoints(zone_object_id); 1695CREATE INDEX idx_endpointstatus_zone_object_id on icinga_endpointstatus(zone_object_id); 1696 1697CREATE INDEX idx_zones_object_id on icinga_zones(zone_object_id); 1698CREATE INDEX idx_zonestatus_object_id on icinga_zonestatus(zone_object_id); 1699 1700CREATE INDEX idx_zones_parent_object_id on icinga_zones(parent_zone_object_id); 1701CREATE INDEX idx_zonestatus_parent_object_id on icinga_zonestatus(parent_zone_object_id); 1702 1703-- #12210 1704CREATE INDEX idx_comments_session_del ON icinga_comments (instance_id, session_token); 1705CREATE INDEX idx_downtimes_session_del ON icinga_scheduleddowntime (instance_id, session_token); 1706 1707-- #12107 1708CREATE INDEX idx_statehistory_cleanup on icinga_statehistory(instance_id, state_time); 1709 1710-- #12435 1711CREATE INDEX idx_customvariables_object_id on icinga_customvariables(object_id); 1712CREATE INDEX idx_contactgroup_members_object_id on icinga_contactgroup_members(contact_object_id); 1713CREATE INDEX idx_hostgroup_members_object_id on icinga_hostgroup_members(host_object_id); 1714CREATE INDEX idx_servicegroup_members_object_id on icinga_servicegroup_members(service_object_id); 1715CREATE INDEX idx_servicedependencies_dependent_service_object_id on icinga_servicedependencies(dependent_service_object_id); 1716CREATE INDEX idx_hostdependencies_dependent_host_object_id on icinga_hostdependencies(dependent_host_object_id); 1717CREATE INDEX idx_service_contacts_service_id on icinga_service_contacts(service_id); 1718CREATE INDEX idx_host_contacts_host_id on icinga_host_contacts(host_id); 1719 1720-- #5458 1721CREATE INDEX idx_downtimehistory_remove ON icinga_downtimehistory (object_id, entry_time, scheduled_start_time, scheduled_end_time); 1722CREATE INDEX idx_scheduleddowntime_remove ON icinga_scheduleddowntime (object_id, entry_time, scheduled_start_time, scheduled_end_time); 1723 1724-- #5492 1725CREATE INDEX idx_commenthistory_remove ON icinga_commenthistory (object_id, entry_time); 1726CREATE INDEX idx_comments_remove ON icinga_comments (object_id, entry_time); 1727 1728-- ----------------------------------------- 1729-- set dbversion 1730-- ----------------------------------------- 1731 1732SELECT updatedbversion('1.14.3'); 1733 1734