sql/schema.in

   1 /*
   2 ** NetXMS Database Schema
   3 **
   4 ** ex: syntax=sql ts=2 sw=2 expandtab
   5 */
   6
   7 /*
   8 ** Metadata
   9 */
  10 CREATE TABLE metadata
  11 (
  12   var_name varchar(63) not null,
  13   var_value varchar(255) not null,
  14   PRIMARY KEY(var_name)
  15 ) TABLE_TYPE;
  16
  17 COMMENT_TABLE(metadata, 'System meta data (DBMS type, schema version, etc.)')
  18 COMMENT_COLUMN(metadata.var_name, 'Variable name')
  19 COMMENT_COLUMN(metadata.var_value, 'Variable value')
  20
  21 /*
  22 ** System configuration table
  23 */
  24 CREATE TABLE config
  25 (
  26   var_name varchar(63) not null,
  27   var_value varchar(2000) null,
  28 #ifdef DB_ORACLE
  29   is_visible integer default 1 not null,
  30   need_server_restart integer default 0 not null,
  31   data_type char(1) default 'S' not null,
  32   is_public char(1) default 'N' not null,
  33 #else
  34   is_visible integer not null default 1,
  35   need_server_restart integer not null default 0,
  36   data_type char(1) not null default 'S',
  37   is_public char(1) not null default 'N',
  38 #endif
  39   description varchar(255) null,
  40   possible_values SQL_TEXT null,
  41   PRIMARY KEY(var_name)
  42 ) TABLE_TYPE;
  43
  44 COMMENT_TABLE(config, 'Server configuration')
  45 COMMENT_COLUMN(config.var_name, 'Parameter name')
  46 COMMENT_COLUMN(config.var_value, 'Value')
  47 COMMENT_COLUMN(config.is_visible, 'GUI visibility indicator')
  48 COMMENT_COLUMN(config.need_server_restart, 'Change take effect after server restart')
  49 COMMENT_COLUMN(config.data_type, 'Not used in current verion')
  50 COMMENT_COLUMN(config.is_public, 'No used in current version')
  51 COMMENT_COLUMN(config.description, 'Not used in current version')
  52 COMMENT_COLUMN(config.possible_values, 'Not used in current version')
  53
  54 /*
  55 ** System configuration data (for large data)
  56 */
  57 CREATE TABLE config_clob
  58 (
  59   var_name varchar(63) not null,
  60   var_value SQL_TEXT null,
  61   PRIMARY KEY(var_name)
  62 ) TABLE_TYPE;
  63
  64 COMMENT_TABLE(config_clob, 'System configuration table for large data elements')
  65 COMMENT_COLUMN(config_clob.var_name, 'Parameter name')
  66 COMMENT_COLUMN(config_clob.var_value, 'Value')
  67
  68 /*
  69 ** Users
  70 */
  71 CREATE TABLE users
  72 (
  73   id integer not null,
  74   guid varchar(36) not null,
  75   name varchar(63) not null,
  76   password varchar(127) not null,
  77   system_access SQL_INT64 not null,
  78   flags integer not null,
  79   full_name varchar(127) null,
  80   description varchar(255) null,
  81   grace_logins integer not null,
  82   auth_method integer not null,
  83   cert_mapping_method integer not null,
  84   cert_mapping_data SQL_TEXT null,
  85   auth_failures integer not null,
  86   last_passwd_change integer not null,
  87   min_passwd_length integer not null,
  88   disabled_until integer not null,
  89   last_login integer not null,
  90   password_history SQL_TEXT null,
  91   xmpp_id varchar(127) null,
  92   ldap_dn SQL_TEXT null,
  93   ldap_unique_id varchar(64) null,
  94   PRIMARY KEY(id)
  95 ) TABLE_TYPE;
  96
  97 COMMENT_TABLE(users, 'System users')
  98 COMMENT_COLUMN(users.id, 'User id')
  99 COMMENT_COLUMN(users.guid, 'User guid')
 100 COMMENT_COLUMN(users.name, 'Login name')
 101 COMMENT_COLUMN(users.password, 'Hashed password')
 102 COMMENT_COLUMN(users.system_access, 'Bit-mask of system access rights')
 103 COMMENT_COLUMN(users.flags, 'Account flags (locked, password never expire etc.)')
 104 COMMENT_COLUMN(users.full_name, 'Full name')
 105 COMMENT_COLUMN(users.description, 'Description')
 106 COMMENT_COLUMN(users.grace_logins, '')
 107 COMMENT_COLUMN(users.auth_method, '')
 108 COMMENT_COLUMN(users.cert_mapping_method, '')
 109 COMMENT_COLUMN(users.cert_mapping_data, '')
 110 COMMENT_COLUMN(users.auth_failures, '')
 111 COMMENT_COLUMN(users.last_passwd_change, '')
 112 COMMENT_COLUMN(users.min_passwd_length, '')
 113 COMMENT_COLUMN(users.disabled_until, '')
 114 COMMENT_COLUMN(users.last_login, '')
 115 COMMENT_COLUMN(users.password_history, '')
 116 COMMENT_COLUMN(users.xmpp_id, '')
 117 COMMENT_COLUMN(users.ldap_dn, 'LDAP dn')
 118 COMMENT_COLUMN(users.ldap_unique_id, 'LDAP unique id if field set')
 119
 120 /*
 121 ** User groups
 122 */
 123
 124 CREATE TABLE user_groups
 125 (
 126   id integer not null,
 127   guid varchar(36) not null,
 128   name varchar(63) not null,
 129   system_access SQL_INT64 not null,
 130   flags integer not null,
 131   description varchar(255),
 132   ldap_dn SQL_TEXT null,
 133   ldap_unique_id varchar(64) null,
 134   PRIMARY KEY(id)
 135 ) TABLE_TYPE;
 136
 137 COMMENT_TABLE(user_groups, 'System user groups')
 138 COMMENT_COLUMN(user_groups.id, 'Group id')
 139 COMMENT_COLUMN(user_groups.guid, 'Group GUID')
 140 COMMENT_COLUMN(user_groups.name, 'Group name')
 141 COMMENT_COLUMN(user_groups.system_access, 'Bit-mask of system access rights')
 142 COMMENT_COLUMN(user_groups.flags, '')
 143 COMMENT_COLUMN(user_groups.description, 'Description')
 144 COMMENT_COLUMN(user_groups.ldap_dn, 'LDAP dn')
 145 COMMENT_COLUMN(users.ldap_unique_id, 'LDAP unique id if field set')
 146
 147 /*
 148 ** Users to groups mapping
 149 */
 150
 151 CREATE TABLE user_group_members
 152 (
 153   group_id integer not null,
 154   user_id integer not null,
 155   PRIMARY KEY(group_id,user_id)
 156 ) TABLE_TYPE;
 157
 158 COMMENT_TABLE(user_group_members, 'Users and group relationships')
 159 COMMENT_COLUMN(user_group_members.group_id, 'Group id from user_groups table')
 160 COMMENT_COLUMN(user_group_members.user_id, 'User id from users table')
 161
 162 /*
 163 ** User profiles
 164 */
 165
 166 CREATE TABLE user_profiles
 167 (
 168   user_id integer not null,
 169   var_name varchar(255) not null,
 170   var_value SQL_TEXT not null,
 171   PRIMARY KEY(user_id,var_name)
 172 ) TABLE_TYPE;
 173
 174 COMMENT_TABLE(user_profiles, 'User profiles')
 175 COMMENT_COLUMN(user_profiles.user_id, '')
 176 COMMENT_COLUMN(user_profiles.var_name, '')
 177 COMMENT_COLUMN(user_profiles.var_value, '')
 178
 179 /*
 180 ** Custom attributes for user database objects
 181 */
 182
 183 CREATE TABLE userdb_custom_attributes
 184 (
 185   object_id integer not null,
 186   attr_name varchar(255) not null,
 187   attr_value SQL_TEXT not null,
 188   PRIMARY KEY(object_id,attr_name)
 189 ) TABLE_TYPE;
 190
 191 COMMENT_TABLE(userdb_custom_attributes, '')
 192 COMMENT_COLUMN(userdb_custom_attributes.object_id, '')
 193 COMMENT_COLUMN(userdb_custom_attributes.attr_name, '')
 194 COMMENT_COLUMN(userdb_custom_attributes.attr_value, '')
 195
 196 /*
 197 ** Common object properties
 198 */
 199
 200 CREATE TABLE object_properties
 201 (
 202   object_id integer not null,
 203   guid varchar(36) not null,
 204   name varchar(63) not null,
 205   status integer not null,
 206   is_deleted integer not null,
 207   is_system integer not null,
 208   last_modified integer not null,
 209   inherit_access_rights integer not null,
 210   status_calc_alg integer not null,
 211   status_prop_alg integer not null,
 212   status_fixed_val integer not null,
 213   status_shift integer not null,
 214   status_translation varchar(8) not null,
 215   status_single_threshold integer not null,
 216   status_thresholds varchar(8) not null,
 217   comments SQL_TEXT null,
 218   location_type integer not null,
 219   latitude varchar(20),
 220   longitude varchar(20),
 221   location_accuracy integer not null,
 222   location_timestamp integer not null,
 223   image varchar(36) not null,
 224   submap_id integer not null,
 225   country varchar(63) null,
 226   city varchar(63) null,
 227   street_address varchar(255) null,
 228   postcode varchar(31) null,
 229   maint_mode char(1) not null,
 230   maint_event_id SQL_INT64 not null,
 231   PRIMARY KEY(object_id)
 232 ) TABLE_TYPE;
 233
 234 COMMENT_TABLE(object_properties, 'Object properties of NetObj and everything that is inherited from it(Dashboard, Interface, Node...)')
 235 COMMENT_COLUMN(object_properties.object_id, 'Object id')
 236 COMMENT_COLUMN(object_properties.guid, 'Object GUID')
 237 COMMENT_COLUMN(object_properties.name, 'Object name')
 238 COMMENT_COLUMN(object_properties.status, 'Object status(Unknown, Normal, Warning, Minor ...)')
 239 COMMENT_COLUMN(object_properties.is_deleted, 'Flag that defines if this object is scheduled for deletion')
 240 COMMENT_COLUMN(object_properties.is_system, 'Flag that defines if this object is the system object(DashboardRoot, ServiceRoot...)')
 241 COMMENT_COLUMN(object_properties.last_modified, 'Timestamp when this object was last modified')
 242 COMMENT_COLUMN(object_properties.inherit_access_rights, '')
 243 COMMENT_COLUMN(object_properties.status_calc_alg, '')
 244 COMMENT_COLUMN(object_properties.status_prop_alg, '')
 245 COMMENT_COLUMN(object_properties.status_fixed_val, '')
 246 COMMENT_COLUMN(object_properties.status_shift, '')
 247 COMMENT_COLUMN(object_properties.status_translation, '')
 248 COMMENT_COLUMN(object_properties.status_single_threshold, '')
 249 COMMENT_COLUMN(object_properties.status_thresholds, '')
 250 COMMENT_COLUMN(object_properties.comments, 'Comments')
 251 COMMENT_COLUMN(object_properties.location_type, 'Lacation type(Undefined, Manual, Automatic)')
 252 COMMENT_COLUMN(object_properties.latitude, 'Latitude value')
 253 COMMENT_COLUMN(object_properties.longitude, 'Longitude value')
 254 COMMENT_COLUMN(object_properties.location_accuracy, 'Received location accuracy')
 255 COMMENT_COLUMN(object_properties.location_timestamp, 'Last received location time')
 256 COMMENT_COLUMN(object_properties.image, '')
 257 COMMENT_COLUMN(object_properties.submap_id, '')
 258 COMMENT_COLUMN(object_properties.country, 'Object location country')
 259 COMMENT_COLUMN(object_properties.city, 'Object location city')
 260 COMMENT_COLUMN(object_properties.street_address, 'Object location address')
 261 COMMENT_COLUMN(object_properties.postcode, 'Object location postcode')
 262 COMMENT_COLUMN(object_properties.maint_mode, '')
 263 COMMENT_COLUMN(object_properties.maint_event_id, '')
 264
 265 /*
 266 ** Object custom attributes
 267 */
 268 CREATE TABLE object_custom_attributes
 269 (
 270   object_id integer not null,
 271   attr_name varchar(127) not null,
 272   attr_value SQL_TEXT null,
 273   PRIMARY KEY(object_id,attr_name)
 274 ) TABLE_TYPE;
 275
 276 CREATE INDEX idx_ocattr_oid ON object_custom_attributes(object_id);
 277
 278 COMMENT_TABLE(object_custom_attributes, 'Object custom attributes')
 279 COMMENT_COLUMN(object_custom_attributes.object_id, 'Object id from object_properties')
 280 COMMENT_COLUMN(object_custom_attributes.attr_name, 'Object attribute name')
 281 COMMENT_COLUMN(object_custom_attributes.attr_value, 'Object attribute value')
 282
 283 /*
 284 ** Zone objects
 285 */
 286 CREATE TABLE zones
 287 (
 288   id integer not null,    // Zone object ID
 289   zone_guid integer not null, // Globally unique ID for zone
 290   proxy_node integer not null,
 291   PRIMARY KEY(id)
 292 ) TABLE_TYPE;
 293
 294 COMMENT_TABLE(zones, 'Zones')
 295 COMMENT_COLUMN(zones.id, 'Zone id from object_properties')
 296 COMMENT_COLUMN(zones.zone_guid, 'Zone GUID')
 297 COMMENT_COLUMN(zones.proxy_node, 'ID of proxy node for this zone')
 298
 299 /*
 300 ** Mobile device objects
 301 */
 302 CREATE TABLE mobile_devices
 303 (
 304   id integer not null,
 305   device_id varchar(64) not null,
 306   vendor varchar(64) null,
 307   model varchar(128) null,
 308   serial_number varchar(64) null,
 309   os_name varchar(32) null,
 310   os_version varchar(64) null,
 311   user_id varchar(64) null,
 312   battery_level integer not null,
 313   PRIMARY KEY(id)
 314 ) TABLE_TYPE;
 315
 316 COMMENT_TABLE(mobile_devices, 'Mobile devices from object_properties')
 317 COMMENT_COLUMN(mobile_devices.id, 'Mobile device id')
 318 COMMENT_COLUMN(mobile_devices.device_id, 'Device ID(IMEI or ESN)')
 319 COMMENT_COLUMN(mobile_devices.vendor, 'Device vendor')
 320 COMMENT_COLUMN(mobile_devices.model, 'Device model')
 321 COMMENT_COLUMN(mobile_devices.serial_number, 'Serial number')
 322 COMMENT_COLUMN(mobile_devices.os_name, 'Operating system name')
 323 COMMENT_COLUMN(mobile_devices.os_version, 'Operation system version')
 324 COMMENT_COLUMN(mobile_devices.user_id, '')
 325 COMMENT_COLUMN(mobile_devices.battery_level, 'Battery charge level')
 326
 327 /*
 328 ** Access point objects
 329 */
 330 CREATE TABLE access_points
 331 (
 332   id integer not null,
 333   node_id integer not null,
 334   mac_address varchar(12) null,
 335   vendor varchar(64) null,
 336   model varchar(128) null,
 337   serial_number varchar(64) null,
 338   ap_state integer not null,
 339   ap_index integer not null,
 340   PRIMARY KEY(id)
 341 ) TABLE_TYPE;
 342
 343 COMMENT_TABLE(access_points, '')
 344 COMMENT_COLUMN(access_points.id, '')
 345 COMMENT_COLUMN(access_points.node_id, '')
 346 COMMENT_COLUMN(access_points.mac_address, '')
 347 COMMENT_COLUMN(access_points.vendor, '')
 348 COMMENT_COLUMN(access_points.model, '')
 349 COMMENT_COLUMN(access_points.serial_number, '')
 350 COMMENT_COLUMN(access_points.ap_state, '')
 351 COMMENT_COLUMN(access_points.ap_index, '')
 352
 353 /*
 354 ** Rack objects
 355 */
 356 CREATE TABLE racks
 357 (
 358   id integer not null,
 359   height integer not null,
 360   top_bottom_num char(1) not null,
 361   PRIMARY KEY(id)
 362 ) TABLE_TYPE;
 363
 364 COMMENT_TABLE(racks, 'Racks')
 365 COMMENT_COLUMN(racks.id, 'Rack id form object_properties')
 366 COMMENT_COLUMN(racks.height, '')
 367 COMMENT_COLUMN(racks.top_bottom_num, '')
 368
 369 /*
 370 ** Chassis objects
 371 */
 372 CREATE TABLE chassis
 373 (
 374   id integer not null,
 375   controller_id integer not null,
 376   flags integer not null,
 377   rack_id integer not null,
 378   rack_image varchar(36) null,
 379   rack_position integer not null,
 380   rack_height integer not null,
 381   PRIMARY KEY(id)
 382 ) TABLE_TYPE;
 383
 384 COMMENT_TABLE(chassis, 'Chassis')
 385 COMMENT_COLUMN(chassis.id, 'Chassis id form object_properties')
 386 COMMENT_COLUMN(chassis.controller_id, 'Id of node object providing management capabilities for this chassis')
 387 COMMENT_COLUMN(chassis.flags, 'Chassis options as bit flags')
 388 COMMENT_COLUMN(chassis.rack_id, 'Related rack object ID')
 389 COMMENT_COLUMN(chassis.rack_image, 'Image to be used in rack view')
 390 COMMENT_COLUMN(chassis.rack_position, 'Position in rack (in rack units)')
 391 COMMENT_COLUMN(chassis.rack_height, 'Height in rack (in rack units)')
 392
 393 /*
 394 ** Nodes information
 395 */
 396 CREATE TABLE nodes
 397 (
 398   id integer not null,
 399   primary_name varchar(255) null,
 400   primary_ip varchar(48) not null,
 401   node_flags integer not null,
 402   runtime_flags integer not null,
 403   snmp_version integer not null,
 404   snmp_port integer not null,
 405   community varchar(127) null,
 406   usm_auth_password varchar(127) null,
 407   usm_priv_password varchar(127) null,
 408   usm_methods integer not null,
 409   snmp_oid varchar(255) null,
 410   auth_method integer not null,
 411   secret varchar(64) null,
 412   agent_port integer not null,
 413   status_poll_type integer not null,
 414   agent_version varchar(63) null,
 415   platform_name varchar(63) null,
 416   poller_node_id integer not null,
 417   zone_guid integer not null,
 418   proxy_node integer not null,
 419   snmp_proxy integer not null,
 420   icmp_proxy integer not null,
 421   required_polls integer not null,
 422   uname varchar(255) null,
 423   use_ifxtable integer not null,
 424   snmp_sys_name varchar(127) null,
 425   snmp_sys_contact varchar(127) null,
 426   snmp_sys_location varchar(127) null,
 427   bridge_base_addr varchar(15) null,
 428   down_since integer not null,
 429   boot_time integer not null,
 430   driver_name varchar(32) null,
 431   rack_image varchar(36) null,
 432   rack_position integer not null,
 433   rack_height integer not null,
 434   rack_id integer not null,
 435   chassis_id integer not null,
 436   agent_cache_mode char(1) not null,
 437   last_agent_comm_time integer not null,
 438   syslog_msg_count SQL_INT64 not null,
 439   snmp_trap_count SQL_INT64 not null,
 440   node_type integer not null,
 441   node_subtype varchar(127) null,
 442   ssh_login varchar(63) null,
 443   ssh_password varchar(63) null,
 444   ssh_proxy integer not null,
 445   PRIMARY KEY(id)
 446 ) TABLE_TYPE;
 447
 448 COMMENT_TABLE(nodes, 'Nodes')
 449 COMMENT_COLUMN(nodes.id, 'Node id from object_properties')
 450 COMMENT_COLUMN(nodes.primary_name, '')
 451 COMMENT_COLUMN(nodes.primary_ip, '')
 452 COMMENT_COLUMN(nodes.node_flags, '')
 453 COMMENT_COLUMN(nodes.runtime_flags, '')
 454 COMMENT_COLUMN(nodes.snmp_version, '')
 455 COMMENT_COLUMN(nodes.snmp_port, '')
 456 COMMENT_COLUMN(nodes.community, '')
 457 COMMENT_COLUMN(nodes.usm_auth_password, '')
 458 COMMENT_COLUMN(nodes.usm_priv_password, '')
 459 COMMENT_COLUMN(nodes.usm_methods, '')
 460 COMMENT_COLUMN(nodes.snmp_oid, '')
 461 COMMENT_COLUMN(nodes.auth_method, '')
 462 COMMENT_COLUMN(nodes.secret, '')
 463 COMMENT_COLUMN(nodes.agent_port, '')
 464 COMMENT_COLUMN(nodes.status_poll_type, '')
 465 COMMENT_COLUMN(nodes.agent_version, '')
 466 COMMENT_COLUMN(nodes.platform_name, '')
 467 COMMENT_COLUMN(nodes.poller_node_id, '')
 468 COMMENT_COLUMN(nodes.zone_guid, '')
 469 COMMENT_COLUMN(nodes.proxy_node, '')
 470 COMMENT_COLUMN(nodes.snmp_proxy, '')
 471 COMMENT_COLUMN(nodes.icmp_proxy, '')
 472 COMMENT_COLUMN(nodes.required_polls, '')
 473 COMMENT_COLUMN(nodes.uname, '')
 474 COMMENT_COLUMN(nodes.use_ifxtable, '')
 475 COMMENT_COLUMN(nodes.snmp_sys_name, '')
 476 COMMENT_COLUMN(nodes.snmp_sys_contact, '')
 477 COMMENT_COLUMN(nodes.snmp_sys_location, '')
 478 COMMENT_COLUMN(nodes.bridge_base_addr, '')
 479 COMMENT_COLUMN(nodes.down_since, '')
 480 COMMENT_COLUMN(nodes.boot_time, '')
 481 COMMENT_COLUMN(nodes.driver_name, '')
 482 COMMENT_COLUMN(nodes.rack_image, '')
 483 COMMENT_COLUMN(nodes.rack_position, '')
 484 COMMENT_COLUMN(nodes.rack_height, '')
 485 COMMENT_COLUMN(nodes.rack_id, '')
 486 COMMENT_COLUMN(nodes.chassis_id, 'ID of chassis object this node belongs to')
 487 COMMENT_COLUMN(nodes.agent_cache_mode, '')
 488 COMMENT_COLUMN(nodes.last_agent_comm_time, '')
 489 COMMENT_COLUMN(nodes.syslog_msg_count, 'Total number of received syslog messages')
 490 COMMENT_COLUMN(nodes.snmp_trap_count, 'Total number of received SNMP traps')
 491
 492 /*
 493 ** Clusters information
 494 */
 495
 496 CREATE TABLE clusters
 497 (
 498   id integer not null,
 499   cluster_type integer not null,
 500   zone_guid integer not null,
 501   PRIMARY KEY(id)
 502 ) TABLE_TYPE;
 503
 504 COMMENT_TABLE(clusters, 'Clusters')
 505 COMMENT_COLUMN(clusters.id, 'Cluster id form object_properties')
 506 COMMENT_COLUMN(clusters.cluster_type, 'Cluster type')
 507 COMMENT_COLUMN(clusters.zone_guid, 'Zone GUID form zones table')
 508
 509 /*
 510 ** Cluster members
 511 */
 512
 513 CREATE TABLE cluster_members
 514 (
 515   cluster_id integer not null,
 516   node_id integer not null,
 517   PRIMARY KEY(cluster_id,node_id)
 518 ) TABLE_TYPE;
 519
 520 COMMENT_TABLE(cluster_members, 'Cluster members')
 521 COMMENT_COLUMN(cluster_members.cluster_id, 'Cluster id from clusters table')
 522 COMMENT_COLUMN(cluster_members.node_id, 'Node id form nodes table')
 523
 524 /*
 525 ** Cluster interconnect subnets
 526 */
 527
 528 CREATE TABLE cluster_sync_subnets
 529 (
 530   cluster_id integer not null,
 531   subnet_addr varchar(48) not null,
 532   subnet_mask integer not null,
 533   PRIMARY KEY(cluster_id,subnet_addr)
 534 ) TABLE_TYPE;
 535
 536 COMMENT_TABLE(cluster_sync_subnets, '')
 537 COMMENT_COLUMN(cluster_sync_subnets.cluster_id, '')
 538 COMMENT_COLUMN(cluster_sync_subnets.subnet_addr, '')
 539 COMMENT_COLUMN(cluster_sync_subnets.subnet_mask, '')
 540
 541 /*
 542 ** Cluster resources
 543 */
 544
 545 CREATE TABLE cluster_resources
 546 (
 547   cluster_id integer not null,
 548   resource_id integer not null,
 549   resource_name varchar(255),
 550   ip_addr varchar(48) not null,
 551   current_owner integer not null,
 552   PRIMARY KEY(cluster_id,resource_id)
 553 ) TABLE_TYPE;
 554
 555 COMMENT_TABLE(cluster_resources, '')
 556 COMMENT_COLUMN(cluster_resources.cluster_id, '')
 557 COMMENT_COLUMN(cluster_resources.resource_id, '')
 558 COMMENT_COLUMN(cluster_resources.resource_name, '')
 559 COMMENT_COLUMN(cluster_resources.ip_addr, '')
 560 COMMENT_COLUMN(cluster_resources.current_owner, '')
 561
 562 /*
 563 ** Subnets
 564 */
 565 CREATE TABLE subnets
 566 (
 567   id integer not null,
 568   ip_addr varchar(48) not null,
 569   ip_netmask integer not null,
 570   zone_guid integer not null,
 571   synthetic_mask integer not null,
 572   PRIMARY KEY(id)
 573 ) TABLE_TYPE;
 574
 575 COMMENT_TABLE(subnets, '')
 576
 577 /*
 578 ** Nodes' interfaces
 579 */
 580 CREATE TABLE interfaces
 581 (
 582   id integer not null,
 583   node_id integer not null,
 584   flags integer not null,
 585   if_type integer not null,
 586   if_index integer not null,
 587   mtu integer not null,
 588   speed SQL_INT64 not null,
 589   bridge_port integer not null,
 590   phy_slot integer not null,
 591   phy_port integer not null,
 592   peer_node_id integer not null,
 593   peer_if_id integer not null,
 594   peer_proto integer not null,
 595   mac_addr varchar(12) not null,
 596   required_polls integer not null,
 597   admin_state integer not null,
 598   oper_state integer not null,
 599   dot1x_pae_state integer not null,
 600   dot1x_backend_state integer not null,
 601   description varchar(255) null,
 602   alias varchar(255) null,
 603   iftable_suffix varchar(127) null,
 604   PRIMARY KEY(id)
 605 ) TABLE_TYPE;
 606
 607 COMMENT_TABLE(interfaces, '')
 608 COMMENT_COLUMN(interfaces.id, '')
 609 COMMENT_COLUMN(interfaces.node_id, '')
 610 COMMENT_COLUMN(interfaces.flags, '')
 611 COMMENT_COLUMN(interfaces.if_type, '')
 612 COMMENT_COLUMN(interfaces.if_index, '')
 613 COMMENT_COLUMN(interfaces.mtu, '')
 614 COMMENT_COLUMN(interfaces.speed, '')
 615 COMMENT_COLUMN(interfaces.bridge_port, '')
 616 COMMENT_COLUMN(interfaces.phy_slot, '')
 617 COMMENT_COLUMN(interfaces.phy_port, '')
 618 COMMENT_COLUMN(interfaces.peer_node_id, '')
 619 COMMENT_COLUMN(interfaces.peer_if_id, '')
 620 COMMENT_COLUMN(interfaces.peer_proto, '')
 621 COMMENT_COLUMN(interfaces.mac_addr, '')
 622 COMMENT_COLUMN(interfaces.required_polls, '')
 623 COMMENT_COLUMN(interfaces.admin_state, '')
 624 COMMENT_COLUMN(interfaces.oper_state, '')
 625 COMMENT_COLUMN(interfaces.dot1x_pae_state, '')
 626 COMMENT_COLUMN(interfaces.dot1x_backend_state, '')
 627 COMMENT_COLUMN(interfaces.description, '')
 628 COMMENT_COLUMN(interfaces.alias, '')
 629 COMMENT_COLUMN(interfaces.iftable_suffix, '')
 630
 631 /*
 632 ** Interface IP addresses
 633 */
 634 CREATE TABLE interface_address_list
 635 (
 636   iface_id integer not null,
 637   ip_addr varchar(48) not null,
 638   ip_netmask integer not null,
 639   PRIMARY KEY(iface_id,ip_addr)
 640 ) TABLE_TYPE;
 641
 642 COMMENT_TABLE(interface_address_list, '')
 643 COMMENT_COLUMN(interface_address_list.iface_id, '')
 644 COMMENT_COLUMN(interface_address_list.ip_addr, '')
 645 COMMENT_COLUMN(interface_address_list.ip_netmask, '')
 646
 647 /*
 648 ** Network services
 649 */
 650
 651 CREATE TABLE network_services
 652 (
 653   id integer not null,
 654   node_id integer not null,
 655   service_type integer not null,
 656   ip_bind_addr varchar(48) not null,
 657   ip_proto integer not null,
 658   ip_port integer not null,
 659   check_request SQL_TEXT null,
 660   check_responce SQL_TEXT null,
 661   poller_node_id integer not null,
 662   required_polls integer not null,
 663   PRIMARY KEY(id)
 664 ) TABLE_TYPE;
 665
 666 COMMENT_TABLE(network_services, '')
 667 COMMENT_COLUMN(network_services.id, '')
 668 COMMENT_COLUMN(network_services.node_id, '')
 669 COMMENT_COLUMN(network_services.service_type, '')
 670 COMMENT_COLUMN(network_services.ip_bind_addr, '')
 671 COMMENT_COLUMN(network_services.ip_proto, '')
 672 COMMENT_COLUMN(network_services.ip_port, '')
 673 COMMENT_COLUMN(network_services.check_request, '')
 674 COMMENT_COLUMN(network_services.check_responce, '')
 675 COMMENT_COLUMN(network_services.poller_node_id, '')
 676 COMMENT_COLUMN(network_services.required_polls, '')
 677
 678 /*
 679 ** VPN connectors
 680 */
 681 CREATE TABLE vpn_connectors
 682 (
 683   id integer not null,
 684   node_id integer not null,
 685   peer_gateway integer not null,
 686   PRIMARY KEY(id)
 687 ) TABLE_TYPE;
 688
 689 COMMENT_TABLE(vpn_connectors, '')
 690 COMMENT_COLUMN(vpn_connectors.id, '')
 691 COMMENT_COLUMN(vpn_connectors.node_id, '')
 692 COMMENT_COLUMN(vpn_connectors.peer_gateway, '')
 693
 694 /*
 695 ** VPN connector networks
 696 */
 697 CREATE TABLE vpn_connector_networks
 698 (
 699   vpn_id integer not null,
 700   network_type integer not null,  // 0 == local, 1 == remote
 701   ip_addr varchar(48) not null,
 702   ip_netmask integer not null,
 703   PRIMARY KEY(vpn_id,ip_addr)
 704 ) TABLE_TYPE;
 705
 706 COMMENT_TABLE(vpn_connector_networks, '')
 707 COMMENT_COLUMN(vpn_connector_networks.vpn_id, '')
 708 COMMENT_COLUMN(vpn_connector_networks.network_type, '')
 709 COMMENT_COLUMN(vpn_connector_networks.ip_addr, '')
 710 COMMENT_COLUMN(vpn_connector_networks.ip_netmask, '')
 711
 712 /*
 713 ** Container objects
 714 */
 715 CREATE TABLE object_containers
 716 (
 717   id integer not null,
 718   object_class integer not null,
 719   flags integer not null,
 720   auto_bind_filter SQL_TEXT null,
 721   PRIMARY KEY(id)
 722 ) TABLE_TYPE;
 723
 724 COMMENT_TABLE(object_containers, '')
 725 COMMENT_COLUMN(object_containers.id, '')
 726 COMMENT_COLUMN(object_containers.object_class, '')
 727 COMMENT_COLUMN(object_containers.flags, '')
 728 COMMENT_COLUMN(object_containers.auto_bind_filter, '')
 729
 730 /*
 731 ** Condition objects
 732 */
 733 CREATE TABLE conditions
 734 (
 735   id integer not null,
 736   activation_event integer not null,
 737   deactivation_event integer not null,
 738   source_object integer not null,
 739   active_status integer not null,
 740   inactive_status integer not null,
 741   script SQL_TEXT not null,
 742   PRIMARY KEY(id)
 743 ) TABLE_TYPE;
 744
 745 COMMENT_TABLE(conditions, '')
 746 COMMENT_COLUMN(conditions.id, '')
 747 COMMENT_COLUMN(conditions.activation_event, '')
 748 COMMENT_COLUMN(conditions.deactivation_event, '')
 749 COMMENT_COLUMN(conditions.source_object, '')
 750 COMMENT_COLUMN(conditions.active_status, '')
 751 COMMENT_COLUMN(conditions.inactive_status, '')
 752 COMMENT_COLUMN(conditions.script, '')
 753
 754 /*
 755 ** DCI to condition mapping
 756 */
 757 CREATE TABLE cond_dci_map
 758 (
 759   condition_id integer not null,
 760   sequence_number integer not null,
 761   dci_id integer not null,
 762   node_id integer not null,
 763   dci_func integer not null,
 764   num_polls integer not null,
 765   PRIMARY KEY(condition_id,sequence_number)
 766 ) TABLE_TYPE;
 767
 768 COMMENT_TABLE(cond_dci_map, '')
 769 COMMENT_COLUMN(cond_dci_map.condition_id, '')
 770 COMMENT_COLUMN(cond_dci_map.sequence_number, '')
 771 COMMENT_COLUMN(cond_dci_map.dci_id, '')
 772 COMMENT_COLUMN(cond_dci_map.node_id, '')
 773 COMMENT_COLUMN(cond_dci_map.dci_func, '')
 774 COMMENT_COLUMN(cond_dci_map.num_polls, '')
 775
 776 /**
 777  * Data collection templates
 778  */
 779 CREATE TABLE templates
 780 (
 781   id integer not null,
 782   version integer not null,
 783   flags integer not null,
 784   apply_filter SQL_TEXT null,
 785   PRIMARY KEY(id)
 786 ) TABLE_TYPE;
 787
 788 COMMENT_TABLE(templates, '')
 789 COMMENT_COLUMN(templates.id, '')
 790 COMMENT_COLUMN(templates.version, '')
 791 COMMENT_COLUMN(templates.flags, '')
 792 COMMENT_COLUMN(templates.apply_filter, '')
 793
 794 /**
 795  * Mapping hosts to templates
 796  */
 797 CREATE TABLE dct_node_map
 798 (
 799   template_id integer not null,
 800   node_id integer not null,
 801   PRIMARY KEY(template_id,node_id)
 802 ) TABLE_TYPE;
 803
 804 COMMENT_TABLE(dct_node_map, '')
 805 COMMENT_COLUMN(dct_node_map.template_id, '')
 806 COMMENT_COLUMN(dct_node_map.node_id, '')
 807
 808 /**
 809  * Nodes to subnets mapping
 810  */
 811 CREATE TABLE nsmap
 812 (
 813   subnet_id integer not null,
 814   node_id integer not null,
 815   PRIMARY KEY(subnet_id,node_id)
 816 ) TABLE_TYPE;
 817
 818 COMMENT_TABLE(nsmap, '')
 819 COMMENT_COLUMN(nsmap.subnet_id, '')
 820 COMMENT_COLUMN(nsmap.node_id, '')
 821
 822 /**
 823  * Container members
 824  */
 825 CREATE TABLE container_members
 826 (
 827   container_id integer not null,
 828   object_id integer not null,
 829   PRIMARY KEY(container_id,object_id)
 830 ) TABLE_TYPE;
 831
 832 COMMENT_TABLE(container_members, '')
 833 COMMENT_COLUMN(container_members.container_id, '')
 834 COMMENT_COLUMN(container_members.object_id, '')
 835
 836 /**
 837  * Objects' ACLs
 838  */
 839 CREATE TABLE acl
 840 (
 841   object_id integer not null,
 842   user_id integer not null,
 843   access_rights integer not null,
 844   PRIMARY KEY(object_id,user_id)
 845 ) TABLE_TYPE;
 846
 847 COMMENT_TABLE(acl, '')
 848 COMMENT_COLUMN(acl.object_id, '')
 849 COMMENT_COLUMN(acl.user_id, '')
 850 COMMENT_COLUMN(acl.access_rights, '')
 851
 852 /**
 853  * Trusted nodes - used for cross-node data collection
 854  * Source object is an object providing data (it can be node or condition),
 855  * and target node is a node owning DCI
 856  */
 857 CREATE TABLE trusted_nodes
 858 (
 859   source_object_id integer not null,
 860   target_node_id integer not null,
 861   PRIMARY KEY(source_object_id,target_node_id)
 862 ) TABLE_TYPE;
 863
 864 COMMENT_TABLE(trusted_nodes, '')
 865 COMMENT_COLUMN(trusted_nodes.source_object_id, '')
 866 COMMENT_COLUMN(trusted_nodes.target_node_id, '')
 867
 868 /**
 869  * Data collection items
 870  *
 871  * If node_id != 0, it's an item bound to node, and template_id points to
 872  * the template used for creating this item. In this case, template_id = 0
 873  * means that item was created manually.
 874  * If node_id = 0, it's a template item, and template_id points to a template
 875  * this item belongs to.
 876  * If both node_id and template_id is 0, it's an error.
 877  */
 878 CREATE TABLE items
 879 (
 880   item_id integer not null,
 881   node_id integer not null,
 882   template_id integer not null,
 883   template_item_id integer not null,
 884   guid varchar(36) not null,
 885   name varchar(1023) null,
 886   description varchar(255) null,
 887   flags integer not null,
 888   source integer not null,  // 0 for internal or 1 for native agent or 2 for SNMP
 889   snmp_port integer not null,
 890   datatype integer not null,
 891   polling_interval integer not null,
 892   retention_time integer not null,
 893   status integer not null,      // ACTIVE, DISABLED or NOT_SUPPORTED
 894   snmp_raw_value_type integer not null,
 895   delta_calculation integer not null,
 896   transformation SQL_TEXT,         // Transformation script
 897   instance varchar(255) null,   // Free form text which can be used in events
 898   system_tag varchar(255) null,         // System tag
 899   resource_id integer not null,   // associated cluster resource ID
 900   proxy_node integer not null,    // ID of proxy node (for SNMP and agent items)
 901   base_units integer not null,    // bytes, seconds, etc.
 902   unit_multiplier integer not null, // kilo, mega, milli, etc.
 903   custom_units_name varchar(63) null, // units name of base_units = CUSTOM
 904   perftab_settings SQL_TEXT null,     // Settings for displaying graph on performance tab
 905   instd_method integer not null,
 906   instd_data varchar(255) null,
 907   instd_filter SQL_TEXT null,
 908   samples integer not null,
 909   comments SQL_TEXT null,
 910   PRIMARY KEY(item_id)
 911 ) TABLE_TYPE;
 912
 913 CREATE INDEX idx_items_node_id ON items(node_id);
 914
 915 COMMENT_TABLE(items, '')
 916 COMMENT_COLUMN(items.item_id, '')
 917 COMMENT_COLUMN(items.node_id, '')
 918 COMMENT_COLUMN(items.template_id, '')
 919 COMMENT_COLUMN(items.template_item_id, '')
 920 COMMENT_COLUMN(items.guid, '')
 921 COMMENT_COLUMN(items.name, '')
 922 COMMENT_COLUMN(items.description, '')
 923 COMMENT_COLUMN(items.flags, '')
 924 COMMENT_COLUMN(items.source, '')
 925 COMMENT_COLUMN(items.snmp_port, '')
 926 COMMENT_COLUMN(items.datatype, '')
 927 COMMENT_COLUMN(items.polling_interval, '')
 928 COMMENT_COLUMN(items.retention_time, '')
 929 COMMENT_COLUMN(items.status, '')
 930 COMMENT_COLUMN(items.snmp_raw_value_type, '')
 931 COMMENT_COLUMN(items.delta_calculation, '')
 932 COMMENT_COLUMN(items.transformation, '')
 933 COMMENT_COLUMN(items.instance, '')
 934 COMMENT_COLUMN(items.system_tag, '')
 935 COMMENT_COLUMN(items.resource_id, '')
 936 COMMENT_COLUMN(items.proxy_node, '')
 937 COMMENT_COLUMN(items.base_units, '')
 938 COMMENT_COLUMN(items.unit_multiplier, '')
 939 COMMENT_COLUMN(items.custom_units_name, '')
 940 COMMENT_COLUMN(items.perftab_settings, '')
 941 COMMENT_COLUMN(items.instd_method, '')
 942 COMMENT_COLUMN(items.instd_data, '')
 943 COMMENT_COLUMN(items.instd_filter, '')
 944 COMMENT_COLUMN(items.samples, '')
 945 COMMENT_COLUMN(items.comments, '')
 946
 947 /*
 948 ** Data collection tables
 949 */
 950 CREATE TABLE dc_tables
 951 (
 952   item_id integer not null,
 953   node_id integer not null,
 954   template_id integer not null,
 955   template_item_id integer not null,
 956   guid varchar(36) not null,
 957   name varchar(1023) null,
 958   description varchar(255) null,
 959   flags integer not null,
 960   source integer not null,  // 0 for internal or 1 for native agent or 2 for SNMP
 961   snmp_port integer not null,
 962   polling_interval integer not null,
 963   retention_time integer not null,
 964   status integer not null,      // ACTIVE, DISABLED or NOT_SUPPORTED
 965   system_tag varchar(255) null,         // System tag
 966   resource_id integer not null,   // associated cluster resource ID
 967   proxy_node integer not null,    // ID of proxy node (for SNMP and agent items)
 968   perftab_settings SQL_TEXT null,     // Settings for displaying graph on performance tab
 969   transformation_script SQL_TEXT null,
 970   comments SQL_TEXT null,
 971   PRIMARY KEY(item_id)
 972 ) TABLE_TYPE;
 973
 974 CREATE INDEX idx_dc_tables_node_id ON dc_tables(node_id);
 975
 976 COMMENT_TABLE(dc_tables, '')
 977 COMMENT_COLUMN(dc_tables.item_id, '')
 978 COMMENT_COLUMN(dc_tables.node_id, '')
 979 COMMENT_COLUMN(dc_tables.template_id, '')
 980 COMMENT_COLUMN(dc_tables.template_item_id, '')
 981 COMMENT_COLUMN(dc_tables.guid, '')
 982 COMMENT_COLUMN(dc_tables.name, '')
 983 COMMENT_COLUMN(dc_tables.description, '')
 984 COMMENT_COLUMN(dc_tables.flags, '')
 985 COMMENT_COLUMN(dc_tables.source, '')
 986 COMMENT_COLUMN(dc_tables.snmp_port, '')
 987 COMMENT_COLUMN(dc_tables.polling_interval, '')
 988 COMMENT_COLUMN(dc_tables.retention_time, '')
 989 COMMENT_COLUMN(dc_tables.status, '')
 990 COMMENT_COLUMN(dc_tables.system_tag, '')
 991 COMMENT_COLUMN(dc_tables.resource_id, '')
 992 COMMENT_COLUMN(dc_tables.proxy_node, '')
 993 COMMENT_COLUMN(dc_tables.perftab_settings, '')
 994 COMMENT_COLUMN(dc_tables.transformation_script, '')
 995 COMMENT_COLUMN(dc_tables.comments, '')
 996
 997 /*
 998 ** Columns for data collection tables
 999 */
1000 CREATE TABLE dc_table_columns
1001 (
1002   table_id integer not null,
1003   sequence_number integer not null,
1004   column_name varchar(63) not null,
1005   snmp_oid varchar(1023) null,    // SNMP OID for this column, valid only for SNMP tables
1006   flags integer not null,
1007   display_name varchar(255) null,
1008   PRIMARY KEY(table_id,column_name)
1009 ) TABLE_TYPE;
1010
1011 COMMENT_TABLE(dc_table_columns, '')
1012 COMMENT_COLUMN(dc_table_columns.table_id, '')
1013 COMMENT_COLUMN(dc_table_columns.sequence_number, '')
1014 COMMENT_COLUMN(dc_table_columns.column_name, '')
1015 COMMENT_COLUMN(dc_table_columns.snmp_oid, '')
1016 COMMENT_COLUMN(dc_table_columns.flags, '')
1017 COMMENT_COLUMN(dc_table_columns.display_name, '')
1018
1019 /*
1020 ** Column name dictionary
1021 */
1022 CREATE TABLE dct_column_names
1023 (
1024   column_id integer not null,
1025   column_name varchar(63) not null,
1026   PRIMARY KEY(column_id)
1027 ) TABLE_TYPE;
1028
1029 COMMENT_TABLE(dct_column_names, '')
1030 COMMENT_COLUMN(dct_column_names.column_id, '')
1031 COMMENT_COLUMN(dct_column_names.column_name, '')
1032
1033 /*
1034 ** Table thresholds
1035 */
1036 CREATE TABLE dct_thresholds
1037 (
1038   id integer not null,
1039   table_id integer not null,
1040   sequence_number integer not null,
1041   activation_event integer not null,
1042   deactivation_event integer not null,
1043   PRIMARY KEY(id)
1044 ) TABLE_TYPE;
1045
1046 COMMENT_TABLE(dct_thresholds, '')
1047 COMMENT_COLUMN(dct_thresholds.id, '')
1048 COMMENT_COLUMN(dct_thresholds.table_id, '')
1049 COMMENT_COLUMN(dct_thresholds.sequence_number, '')
1050 COMMENT_COLUMN(dct_thresholds.activation_event, '')
1051 COMMENT_COLUMN(dct_thresholds.deactivation_event, '')
1052
1053 /*
1054 ** Table threshold conditions
1055 */
1056 CREATE TABLE dct_threshold_conditions
1057 (
1058   threshold_id integer not null,
1059   group_id integer not null,
1060   sequence_number integer not null,
1061   column_name varchar(63) null,
1062   check_operation integer not null,
1063   check_value varchar(255) null,
1064   PRIMARY KEY(threshold_id,group_id,sequence_number)
1065 ) TABLE_TYPE;
1066
1067 COMMENT_TABLE(dct_threshold_conditions, '')
1068 COMMENT_COLUMN(dct_threshold_conditions.threshold_id, '')
1069 COMMENT_COLUMN(dct_threshold_conditions.group_id, '')
1070 COMMENT_COLUMN(dct_threshold_conditions.sequence_number, '')
1071 COMMENT_COLUMN(dct_threshold_conditions.column_name, '')
1072 COMMENT_COLUMN(dct_threshold_conditions.check_operation, '')
1073 COMMENT_COLUMN(dct_threshold_conditions.check_value, '')
1074
1075 /*
1076 ** Schedules for DCIs
1077 */
1078 CREATE TABLE dci_schedules
1079 (
1080   schedule_id integer not null,
1081   item_id integer not null,
1082   schedule varchar(255) null,
1083   PRIMARY KEY(item_id,schedule_id)
1084 ) TABLE_TYPE;
1085
1086 COMMENT_TABLE(dci_schedules, '')
1087 COMMENT_COLUMN(dci_schedules.schedule_id, '')
1088 COMMENT_COLUMN(dci_schedules.item_id, '')
1089 COMMENT_COLUMN(dci_schedules.schedule, '')
1090
1091 /*
1092 ** Latest raw values for all data collection items
1093 */
1094 CREATE TABLE raw_dci_values
1095 (
1096   item_id integer not null,
1097   raw_value varchar(255) null,
1098   transformed_value varchar(255) null,
1099   last_poll_time integer not null,
1100   PRIMARY KEY(item_id)
1101 ) TABLE_TYPE;
1102
1103 #if !defined(DB_ORACLE) && !defined(DB_DB2) && !defined(DB_POSTGRESQL)
1104 CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id);
1105 #endif
1106
1107 COMMENT_TABLE(raw_dci_values, '')
1108 COMMENT_COLUMN(raw_dci_values.item_id, '')
1109 COMMENT_COLUMN(raw_dci_values.raw_value, '')
1110 COMMENT_COLUMN(raw_dci_values.transformed_value, '')
1111 COMMENT_COLUMN(raw_dci_values.last_poll_time, '')
1112
1113 /*
1114 ** Events configuration
1115 */
1116 CREATE TABLE event_cfg
1117 (
1118   event_code integer not null,
1119   event_name varchar(63) not null,  // Short event name
1120   guid varchar(36) not null,
1121   severity integer not null,
1122   flags integer not null,
1123   message varchar(2000) null,   /* Message template */
1124   description SQL_TEXT null,
1125   PRIMARY KEY(event_code)
1126 ) TABLE_TYPE;
1127
1128 COMMENT_TABLE(event_cfg, 'Event templates')
1129 COMMENT_COLUMN(event_cfg.event_code, 'Event code (unique within system)')
1130 COMMENT_COLUMN(event_cfg.event_name, 'Event name')
1131 COMMENT_COLUMN(event_cfg.guid, 'Event template GUID')
1132 COMMENT_COLUMN(event_cfg.severity, 'Severity')
1133 COMMENT_COLUMN(event_cfg.flags, 'Flags')
1134 COMMENT_COLUMN(event_cfg.message, 'Message template')
1135 COMMENT_COLUMN(event_cfg.description, 'Event description')
1136
1137 /*
1138 ** Event log
1139 */
1140 CREATE TABLE event_log
1141 (
1142   event_id SQL_INT64 not null,
1143   event_code integer not null,
1144   event_timestamp integer not null,
1145   event_source integer not null,    /* Source object ID */
1146   dci_id integer not null,                /* ID of related DCI or 0 */
1147   event_severity integer not null,
1148   event_message varchar(2000) null,
1149   root_event_id SQL_INT64 not null, /* Non-zero if current event correlates to some other event */
1150   user_tag varchar(63) null,
1151   PRIMARY KEY(event_id)
1152 ) TABLE_TYPE;
1153
1154 CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp);
1155
1156 CREATE INDEX idx_event_log_source ON event_log(event_source);
1157
1158 #if defined(DB_POSTGRESQL)
1159 CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0;
1160 #elif defined(DB_ORACLE)
1161 CREATE OR REPLACE FUNCTION zero_to_null(id NUMBER)
1162 RETURN NUMBER
1163 DETERMINISTIC
1164 AS BEGIN
1165    IF id > 0 THEN
1166       RETURN id;
1167    ELSE
1168       RETURN NULL;
1169    END IF;
1170 END;
1171 /
1172 CREATE INDEX idx_event_log_root_id ON event_log(zero_to_null(root_event_id));
1173 #else
1174 CREATE INDEX idx_event_log_root_id ON event_log(root_event_id);
1175 #endif
1176
1177 COMMENT_TABLE(event_log, '')
1178 COMMENT_COLUMN(event_log.event_id, '')
1179 COMMENT_COLUMN(event_log.event_code, '')
1180 COMMENT_COLUMN(event_log.event_timestamp, '')
1181 COMMENT_COLUMN(event_log.event_source, '')
1182 COMMENT_COLUMN(event_log.dci_id, '')
1183 COMMENT_COLUMN(event_log.event_severity, '')
1184 COMMENT_COLUMN(event_log.event_message, '')
1185 COMMENT_COLUMN(event_log.root_event_id, '')
1186 COMMENT_COLUMN(event_log.user_tag, '')
1187
1188 /*
1189 ** Actions on events
1190 */
1191 CREATE TABLE actions
1192 (
1193   action_id integer not null,
1194   action_name varchar(63) not null,
1195   action_type integer not null,
1196   is_disabled integer not null,
1197   // Field "rcpt_addr" holds e-mail address for e-mail actions,
1198   // phone number for sms actions, and remote host address for
1199   // remote execution actions
1200   rcpt_addr varchar(255) null,
1201   email_subject varchar(255) null,
1202   // Field "action_data" holds message text for e-mail and sms actions,
1203   // command line for external command execution actions, or
1204   // action name with optional arguments for remote execution actions
1205   action_data SQL_TEXT null,
1206   PRIMARY KEY(action_id)
1207 ) TABLE_TYPE;
1208
1209 COMMENT_TABLE(actions, '')
1210 COMMENT_COLUMN(actions.action_id, '')
1211 COMMENT_COLUMN(actions.action_name, '')
1212 COMMENT_COLUMN(actions.action_type, '')
1213 COMMENT_COLUMN(actions.is_disabled, '')
1214 COMMENT_COLUMN(actions.rcpt_addr, '')
1215 COMMENT_COLUMN(actions.email_subject, '')
1216 COMMENT_COLUMN(actions.action_data, '')
1217
1218 /*
1219 ** Event groups
1220 */
1221 CREATE TABLE event_groups
1222 (
1223   id integer not null,
1224   name varchar(63) not null,
1225   description varchar(255) not null,
1226   range_start integer not null,
1227   range_end integer not null,
1228   PRIMARY KEY(id)
1229 ) TABLE_TYPE;
1230
1231 COMMENT_TABLE(event_groups, '')
1232 COMMENT_COLUMN(event_groups.id, '')
1233 COMMENT_COLUMN(event_groups.name, '')
1234 COMMENT_COLUMN(event_groups.description, '')
1235 COMMENT_COLUMN(event_groups.range_start, '')
1236 COMMENT_COLUMN(event_groups.range_end, '')
1237
1238 /*
1239 ** Event group members
1240 */
1241 CREATE TABLE event_group_members
1242 (
1243   group_id integer not null,
1244   event_code integer not null,
1245   PRIMARY KEY(group_id,event_code)
1246 ) TABLE_TYPE;
1247
1248 COMMENT_TABLE(event_group_members, '')
1249 COMMENT_COLUMN(event_group_members.group_id, '')
1250 COMMENT_COLUMN(event_group_members.event_code, '')
1251
1252 /*
1253 ** Event processing policy
1254 */
1255 CREATE TABLE event_policy
1256 (
1257   rule_id integer not null,   // Rule number
1258   rule_guid varchar(36) not null,
1259   flags integer not null,
1260   comments SQL_TEXT null,
1261   script SQL_TEXT null,
1262   alarm_message varchar(2000) null,
1263   alarm_severity integer not null,
1264   alarm_key varchar(255) null,    // Alarm key (used for auto termination)
1265   alarm_timeout integer not null, // Timeout before sending event
1266   alarm_timeout_event integer not null,  // Event to be sent on timeout
1267   situation_id integer not null,  // Situation to update
1268   situation_instance varchar(255) null,
1269   PRIMARY KEY(rule_id)
1270 ) TABLE_TYPE;
1271
1272 COMMENT_TABLE(event_policy, '')
1273 COMMENT_COLUMN(event_policy.rule_id, '')
1274 COMMENT_COLUMN(event_policy.rule_guid, '')
1275 COMMENT_COLUMN(event_policy.flags, '')
1276 COMMENT_COLUMN(event_policy.comments, '')
1277 COMMENT_COLUMN(event_policy.script, '')
1278 COMMENT_COLUMN(event_policy.alarm_message, '')
1279 COMMENT_COLUMN(event_policy.alarm_severity, '')
1280 COMMENT_COLUMN(event_policy.alarm_key, '')
1281 COMMENT_COLUMN(event_policy.alarm_timeout, '')
1282 COMMENT_COLUMN(event_policy.alarm_timeout_event, '')
1283 COMMENT_COLUMN(event_policy.situation_id, '')
1284 COMMENT_COLUMN(event_policy.situation_instance, '')
1285
1286 /**
1287  *
1288  */
1289 CREATE TABLE policy_source_list
1290 (
1291   rule_id integer not null,
1292   object_id integer not null,
1293   PRIMARY KEY(rule_id,object_id)
1294 ) TABLE_TYPE;
1295
1296 COMMENT_TABLE(policy_source_list, '')
1297 COMMENT_COLUMN(policy_source_list.rule_id, '')
1298 COMMENT_COLUMN(policy_source_list.object_id, '')
1299
1300 /**
1301  *
1302  */
1303 CREATE TABLE policy_event_list
1304 (
1305   rule_id integer not null,
1306   event_code integer not null,
1307   PRIMARY KEY(rule_id,event_code)
1308 ) TABLE_TYPE;
1309
1310 COMMENT_TABLE(policy_event_list, '')
1311 COMMENT_COLUMN(policy_event_list.rule_id, '')
1312 COMMENT_COLUMN(policy_event_list.event_code, '')
1313
1314 /**
1315  *
1316  */
1317 CREATE TABLE policy_action_list
1318 (
1319   rule_id integer not null,
1320   action_id integer not null,
1321   PRIMARY KEY(rule_id,action_id)
1322 ) TABLE_TYPE;
1323
1324 COMMENT_TABLE(policy_action_list, '')
1325 COMMENT_COLUMN(policy_action_list.rule_id, '')
1326 COMMENT_COLUMN(policy_action_list.action_id, '')
1327
1328 /**
1329  *
1330  */
1331 CREATE TABLE policy_situation_attr_list
1332 (
1333   rule_id integer not null,
1334   situation_id integer not null,
1335   attr_name varchar(255) not null,
1336   attr_value varchar(255) null,
1337   PRIMARY KEY(rule_id,situation_id,attr_name)
1338 ) TABLE_TYPE;
1339
1340 COMMENT_TABLE(policy_situation_attr_list, '')
1341 COMMENT_COLUMN(policy_situation_attr_list.rule_id, '')
1342 COMMENT_COLUMN(policy_situation_attr_list.situation_id, '')
1343 COMMENT_COLUMN(policy_situation_attr_list.attr_name, '')
1344 COMMENT_COLUMN(policy_situation_attr_list.attr_value, '')
1345
1346 /*
1347 ** Threshold checking rules
1348 */
1349 CREATE TABLE thresholds
1350 (
1351   threshold_id integer not null,
1352   item_id integer not null,
1353   sequence_number integer not null,
1354   fire_value varchar(255) null,
1355   rearm_value varchar(255) null,
1356   check_function integer not null,
1357   check_operation integer not null,
1358   sample_count integer not null,
1359   script SQL_TEXT null,
1360   event_code integer not null,
1361   rearm_event_code integer not null,
1362   repeat_interval integer not null,
1363   current_state integer not null,
1364   current_severity integer not null,
1365   match_count integer not null,
1366   last_event_timestamp integer not null,
1367   PRIMARY KEY(threshold_id)
1368 ) TABLE_TYPE;
1369
1370 CREATE INDEX idx_thresholds_item_id ON thresholds(item_id);
1371 CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number);
1372
1373 COMMENT_TABLE(thresholds, '')
1374 COMMENT_COLUMN(thresholds.threshold_id, '')
1375 COMMENT_COLUMN(thresholds.item_id, '')
1376 COMMENT_COLUMN(thresholds.sequence_number, '')
1377 COMMENT_COLUMN(thresholds.fire_value, '')
1378 COMMENT_COLUMN(thresholds.rearm_value, '')
1379 COMMENT_COLUMN(thresholds.check_function, '')
1380 COMMENT_COLUMN(thresholds.check_operation, '')
1381 COMMENT_COLUMN(thresholds.sample_count, '')
1382 COMMENT_COLUMN(thresholds.script, '')
1383 COMMENT_COLUMN(thresholds.event_code, '')
1384 COMMENT_COLUMN(thresholds.rearm_event_code, '')
1385 COMMENT_COLUMN(thresholds.repeat_interval, '')
1386 COMMENT_COLUMN(thresholds.current_state, '')
1387 COMMENT_COLUMN(thresholds.current_severity, '')
1388 COMMENT_COLUMN(thresholds.match_count, '')
1389 COMMENT_COLUMN(thresholds.last_event_timestamp, '')
1390
1391 /*
1392 ** Alarms
1393 */
1394 CREATE TABLE alarms
1395 (
1396   alarm_id integer not null,  // Unique alarm identifier
1397   alarm_state integer not null,
1398   hd_state integer not null,  // Help desk system state
1399   hd_ref varchar(63) null,  // Help desk reference
1400   creation_time integer not null,
1401   last_change_time integer not null,
1402   source_object_id integer not null,
1403   source_event_code integer not null,
1404   source_event_id SQL_INT64 not null,
1405   dci_id integer not null,
1406   message varchar(2000) null,
1407   original_severity integer not null,
1408   current_severity integer not null,
1409   repeat_count integer not null,
1410   alarm_key varchar(255) null,     // Alarm key (used for auto acknowlegment)
1411   ack_by integer not null,         // ID of user who was acknowleged alarm
1412   resolved_by integer not null,    // ID of user who was resolved alarm
1413   term_by integer not null,        // ID of user who was terminated alarm
1414   timeout integer not null,
1415   timeout_event integer not null,
1416   ack_timeout integer not null,
1417   PRIMARY KEY(alarm_id)
1418 ) TABLE_TYPE;
1419
1420 COMMENT_TABLE(alarms, '')
1421 COMMENT_COLUMN(alarms.alarm_id, '')
1422 COMMENT_COLUMN(alarms.alarm_state, '')
1423 COMMENT_COLUMN(alarms.hd_state, '')
1424 COMMENT_COLUMN(alarms.hd_ref, '')
1425 COMMENT_COLUMN(alarms.creation_time, '')
1426 COMMENT_COLUMN(alarms.last_change_time, '')
1427 COMMENT_COLUMN(alarms.source_object_id, '')
1428 COMMENT_COLUMN(alarms.source_event_code, '')
1429 COMMENT_COLUMN(alarms.source_event_id, '')
1430 COMMENT_COLUMN(alarms.dci_id, '')
1431 COMMENT_COLUMN(alarms.message, '')
1432 COMMENT_COLUMN(alarms.original_severity, '')
1433 COMMENT_COLUMN(alarms.current_severity, '')
1434 COMMENT_COLUMN(alarms.repeat_count, '')
1435 COMMENT_COLUMN(alarms.alarm_key, '')
1436 COMMENT_COLUMN(alarms.ack_by, '')
1437 COMMENT_COLUMN(alarms.resolved_by, '')
1438 COMMENT_COLUMN(alarms.term_by, '')
1439 COMMENT_COLUMN(alarms.timeout, '')
1440 COMMENT_COLUMN(alarms.timeout_event, '')
1441 COMMENT_COLUMN(alarms.ack_timeout, '')
1442
1443 /*
1444 ** Alarm notes
1445 */
1446 CREATE TABLE alarm_notes
1447 (
1448   note_id integer not null,
1449   alarm_id integer not null,
1450   change_time integer not null,
1451   user_id integer not null, // Last edited by
1452   note_text SQL_TEXT null,
1453   PRIMARY KEY(note_id)
1454 ) TABLE_TYPE;
1455
1456 CREATE INDEX idx_alarm_notes_alarm_id ON alarm_notes(alarm_id);
1457
1458 COMMENT_TABLE(alarm_notes, '')
1459 COMMENT_COLUMN(alarm_notes.note_id, '')
1460 COMMENT_COLUMN(alarm_notes.alarm_id, '')
1461 COMMENT_COLUMN(alarm_notes.change_time, '')
1462 COMMENT_COLUMN(alarm_notes.user_id, '')
1463 COMMENT_COLUMN(alarm_notes.note_text, '')
1464
1465 /*
1466 ** Source events for alarms
1467 */
1468 CREATE TABLE alarm_events
1469 (
1470   alarm_id integer not null,
1471   event_id SQL_INT64 not null,
1472   event_code integer not null,
1473   event_name varchar(63) null,
1474   severity integer not null,
1475   source_object_id integer not null,
1476   event_timestamp integer not null,
1477   message varchar(2000) null,
1478   PRIMARY KEY(alarm_id,event_id)
1479 ) TABLE_TYPE;
1480
1481 CREATE INDEX idx_alarm_events_alarm_id ON alarm_events(alarm_id);
1482
1483 COMMENT_TABLE(alarm_events, '')
1484 COMMENT_COLUMN(alarm_events.alarm_id, '')
1485 COMMENT_COLUMN(alarm_events.event_id, '')
1486 COMMENT_COLUMN(alarm_events.event_code, '')
1487 COMMENT_COLUMN(alarm_events.event_name, '')
1488 COMMENT_COLUMN(alarm_events.severity, '')
1489 COMMENT_COLUMN(alarm_events.source_object_id, '')
1490 COMMENT_COLUMN(alarm_events.event_timestamp, '')
1491 COMMENT_COLUMN(alarm_events.message, '')
1492
1493 /*
1494 ** SNMP trap configuration
1495 */
1496
1497 CREATE TABLE snmp_trap_cfg
1498 (
1499   trap_id integer not null,
1500   snmp_oid varchar(255),
1501   event_code integer not null,
1502   user_tag varchar(63),
1503   description varchar(255),
1504   PRIMARY KEY(trap_id)
1505 ) TABLE_TYPE;
1506
1507 COMMENT_TABLE(snmp_trap_cfg, '')
1508 COMMENT_COLUMN(snmp_trap_cfg.trap_id, '')
1509 COMMENT_COLUMN(snmp_trap_cfg.snmp_oid, '')
1510 COMMENT_COLUMN(snmp_trap_cfg.event_code, '')
1511 COMMENT_COLUMN(snmp_trap_cfg.user_tag, '')
1512 COMMENT_COLUMN(snmp_trap_cfg.description, '')
1513
1514 /*
1515 ** SNMP trap parameters mapping
1516 */
1517 CREATE TABLE snmp_trap_pmap
1518 (
1519   trap_id integer not null,
1520   parameter integer not null,
1521   flags integer not null,
1522   snmp_oid varchar(255) null,
1523   description varchar(255) null,
1524   PRIMARY KEY(trap_id,parameter)
1525 ) TABLE_TYPE;
1526
1527 COMMENT_TABLE(snmp_trap_pmap, '')
1528 COMMENT_COLUMN(snmp_trap_pmap.trap_id, '')
1529 COMMENT_COLUMN(snmp_trap_pmap.parameter, '')
1530 COMMENT_COLUMN(snmp_trap_pmap.flags, '')
1531 COMMENT_COLUMN(snmp_trap_pmap.snmp_oid, '')
1532 COMMENT_COLUMN(snmp_trap_pmap.description, '')
1533
1534 /*
1535 ** Agent packages
1536 */
1537 CREATE TABLE agent_pkg
1538 (
1539   pkg_id integer not null,
1540   pkg_name varchar(63),
1541   version varchar(31),
1542   platform varchar(63),
1543   pkg_file varchar(255),
1544   description varchar(255),
1545   PRIMARY KEY(pkg_id)
1546 ) TABLE_TYPE;
1547
1548 COMMENT_TABLE(agent_pkg, '')
1549 COMMENT_COLUMN(agent_pkg.pkg_id, '')
1550 COMMENT_COLUMN(agent_pkg.pkg_name, '')
1551 COMMENT_COLUMN(agent_pkg.version, '')
1552 COMMENT_COLUMN(agent_pkg.platform, '')
1553 COMMENT_COLUMN(agent_pkg.pkg_file, '')
1554 COMMENT_COLUMN(agent_pkg.description, '')
1555
1556 /*
1557 ** Object tools
1558 */
1559 CREATE TABLE object_tools
1560 (
1561   tool_id integer not null,
1562   guid varchar(36) not null,
1563   tool_name varchar(255) null,
1564   tool_type integer not null,
1565   tool_data SQL_TEXT null,
1566   description varchar(255) null,
1567   flags integer not null,
1568   tool_filter SQL_TEXT null,
1569   confirmation_text varchar(255) null,
1570   command_name varchar(255) null,
1571   command_short_name varchar(31) null,
1572   icon SQL_TEXT null,
1573   PRIMARY KEY(tool_id)
1574 ) TABLE_TYPE;
1575
1576 COMMENT_TABLE(object_tools, '')
1577 COMMENT_COLUMN(object_tools.tool_id, '')
1578 COMMENT_COLUMN(object_tools.guid, '')
1579 COMMENT_COLUMN(object_tools.tool_name, '')
1580 COMMENT_COLUMN(object_tools.tool_type, '')
1581 COMMENT_COLUMN(object_tools.tool_data, '')
1582 COMMENT_COLUMN(object_tools.description, '')
1583 COMMENT_COLUMN(object_tools.flags, '')
1584 COMMENT_COLUMN(object_tools.tool_filter, '')
1585 COMMENT_COLUMN(object_tools.confirmation_text, '')
1586 COMMENT_COLUMN(object_tools.command_name, '')
1587 COMMENT_COLUMN(object_tools.command_short_name, '')
1588 COMMENT_COLUMN(object_tools.icon, '')
1589
1590 /*
1591 ** Access list for object tools
1592 */
1593 CREATE TABLE object_tools_acl
1594 (
1595   tool_id integer not null,
1596   user_id integer not null,
1597   PRIMARY KEY(tool_id,user_id)
1598 ) TABLE_TYPE;
1599
1600 COMMENT_TABLE(object_tools_acl, '')
1601 COMMENT_COLUMN(object_tools_acl.tool_id, '')
1602 COMMENT_COLUMN(object_tools_acl.user_id, '')
1603
1604 /*
1605 ** Configuration of columns for SNMP_TABLE and AGENT_TABLE type of object tools
1606 */
1607 CREATE TABLE object_tools_table_columns
1608 (
1609   tool_id integer not null,
1610   col_number integer not null,
1611   col_name varchar(255) null,
1612   col_oid varchar(255) null,
1613   col_format integer, // Column format (integer, string, etc.)
1614   col_substr integer, // Number of matching substring in regexp
1615   PRIMARY KEY(tool_id,col_number)
1616 ) TABLE_TYPE;
1617
1618 COMMENT_TABLE(object_tools_table_columns, '')
1619 COMMENT_COLUMN(object_tools_table_columns.tool_id, '')
1620 COMMENT_COLUMN(object_tools_table_columns.col_number, '')
1621 COMMENT_COLUMN(object_tools_table_columns.col_name, '')
1622 COMMENT_COLUMN(object_tools_table_columns.col_oid, '')
1623 COMMENT_COLUMN(object_tools_table_columns.col_format, '')
1624 COMMENT_COLUMN(object_tools_table_columns.col_substr, '')
1625
1626 /**
1627  * Input fields for object tools
1628  */
1629 CREATE TABLE object_tools_input_fields
1630 (
1631   tool_id integer not null,
1632   name varchar(31) not null,
1633   input_type char(1) not null,
1634   display_name varchar(127) null,
1635   sequence_num integer not null,
1636   config SQL_TEXT null,
1637   PRIMARY KEY(tool_id,name)
1638 ) TABLE_TYPE;
1639
1640 COMMENT_TABLE(object_tools_input_fields, '')
1641 COMMENT_COLUMN(object_tools_input_fields.tool_id, '')
1642 COMMENT_COLUMN(object_tools_input_fields.name, '')
1643 COMMENT_COLUMN(object_tools_input_fields.input_type, '')
1644 COMMENT_COLUMN(object_tools_input_fields.display_name, '')
1645 COMMENT_COLUMN(object_tools_input_fields.sequence_num, '')
1646 COMMENT_COLUMN(object_tools_input_fields.config, '')
1647
1648 /*
1649 ** Stored syslog messages
1650 */
1651 CREATE TABLE syslog
1652 (
1653   msg_id SQL_INT64 not null,
1654   msg_timestamp integer not null,
1655   facility integer not null,
1656   severity integer not null,
1657   source_object_id integer not null,
1658   hostname varchar(127) null,
1659   msg_tag varchar(32) null,
1660   msg_text SQL_TEXT null,
1661   PRIMARY KEY(msg_id)
1662 ) TABLE_TYPE;
1663
1664 CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp);
1665
1666 COMMENT_TABLE(syslog, '')
1667 COMMENT_COLUMN(syslog.msg_id, '')
1668 COMMENT_COLUMN(syslog.msg_timestamp, '')
1669 COMMENT_COLUMN(syslog.facility, '')
1670 COMMENT_COLUMN(syslog.severity, '')
1671 COMMENT_COLUMN(syslog.source_object_id, '')
1672 COMMENT_COLUMN(syslog.hostname, '')
1673 COMMENT_COLUMN(syslog.msg_tag, '')
1674 COMMENT_COLUMN(syslog.msg_text, '')
1675
1676 /*
1677 ** Script library
1678 */
1679 CREATE TABLE script_library
1680 (
1681   script_id integer not null,
1682   script_name varchar(255) not null,
1683   script_code SQL_TEXT null,
1684   PRIMARY KEY(script_id)
1685 ) TABLE_TYPE;
1686
1687 COMMENT_TABLE(script_library, '')
1688 COMMENT_COLUMN(script_library.script_id, '')
1689 COMMENT_COLUMN(script_library.script_name, '')
1690 COMMENT_COLUMN(script_library.script_code, '')
1691
1692 /*
1693 ** Extended SNMP trap log
1694 */
1695
1696 CREATE TABLE snmp_trap_log
1697 (
1698   trap_id SQL_INT64 not null,
1699   trap_timestamp integer not null,
1700   ip_addr varchar(48) not null,
1701   object_id integer not null,
1702   trap_oid varchar(255) not null,
1703   trap_varlist SQL_TEXT null,
1704   PRIMARY KEY(trap_id)
1705 ) TABLE_TYPE;
1706
1707 CREATE INDEX idx_snmp_trap_log_tt ON snmp_trap_log(trap_timestamp);
1708
1709 COMMENT_TABLE(snmp_trap_log, '')
1710 COMMENT_COLUMN(snmp_trap_log.trap_id, '')
1711 COMMENT_COLUMN(snmp_trap_log.trap_timestamp, '')
1712 COMMENT_COLUMN(snmp_trap_log.ip_addr, '')
1713 COMMENT_COLUMN(snmp_trap_log.object_id, '')
1714 COMMENT_COLUMN(snmp_trap_log.trap_oid, '')
1715 COMMENT_COLUMN(snmp_trap_log.trap_varlist, '')
1716
1717 /*
1718 ** Agent configurations
1719 */
1720 CREATE TABLE agent_configs
1721 (
1722   config_id integer not null,
1723   config_name varchar(255) not null,
1724   config_file SQL_TEXT not null,
1725   config_filter SQL_TEXT not null,
1726   sequence_number integer not null,
1727   PRIMARY KEY(config_id)
1728 ) TABLE_TYPE;
1729
1730 COMMENT_TABLE(agent_configs, '')
1731 COMMENT_COLUMN(agent_configs.config_id, '')
1732 COMMENT_COLUMN(agent_configs.config_name, '')
1733 COMMENT_COLUMN(agent_configs.config_file, '')
1734 COMMENT_COLUMN(agent_configs.config_filter, '')
1735 COMMENT_COLUMN(agent_configs.sequence_number, '')
1736
1737 /*
1738 ** Address lists
1739 */
1740 CREATE TABLE address_lists
1741 (
1742   list_type integer not null, // discovery filter, etc.
1743   community_id integer not null, // community id for snmp community addr list, otherwise 0
1744   addr_type integer not null, // 0 - addr/mask, 1 - address range
1745   addr1 varchar(48) not null,
1746   addr2 varchar(48) not null,
1747   PRIMARY KEY(list_type,community_id,addr_type,addr1,addr2)
1748 ) TABLE_TYPE;
1749
1750 CREATE INDEX idx_address_lists_list_type ON address_lists(list_type);
1751
1752 COMMENT_TABLE(address_lists, '')
1753 COMMENT_COLUMN(address_lists.list_type, '')
1754 COMMENT_COLUMN(address_lists.community_id, '')
1755 COMMENT_COLUMN(address_lists.addr_type, '')
1756 COMMENT_COLUMN(address_lists.addr1, '')
1757 COMMENT_COLUMN(address_lists.addr2, '')
1758
1759 /*
1760 ** Graphs
1761 */
1762 CREATE TABLE graphs
1763 (
1764   graph_id integer not null,
1765   owner_id integer not null,
1766   flags integer not null,
1767   name varchar(255) not null,
1768   config SQL_TEXT not null,
1769   filters SQL_TEXT not null,
1770   PRIMARY KEY(graph_id)
1771 ) TABLE_TYPE;
1772
1773 COMMENT_TABLE(graphs, '')
1774 COMMENT_COLUMN(graphs.graph_id, '')
1775 COMMENT_COLUMN(graphs.owner_id, '')
1776 COMMENT_COLUMN(graphs.flags, '')
1777 COMMENT_COLUMN(graphs.name, '')
1778 COMMENT_COLUMN(graphs.config, '')
1779 COMMENT_COLUMN(graphs.filters, '')
1780
1781 /*
1782 ** Graph access lists
1783 */
1784 CREATE TABLE graph_acl
1785 (
1786   graph_id integer not null,
1787   user_id integer not null,
1788   user_rights integer not null,
1789   PRIMARY KEY(graph_id,user_id)
1790 ) TABLE_TYPE;
1791
1792 COMMENT_TABLE(graph_acl, '')
1793 COMMENT_COLUMN(graph_acl.graph_id, '')
1794 COMMENT_COLUMN(graph_acl.user_id, '')
1795 COMMENT_COLUMN(graph_acl.user_rights, '')
1796
1797 /*
1798 ** Certificates
1799 */
1800
1801 CREATE TABLE certificates
1802 (
1803   cert_id integer not null,
1804   cert_type integer not null,
1805   cert_data SQL_TEXT not null,  // Certificate in PEM format
1806   subject SQL_TEXT not null,
1807   comments SQL_TEXT not null,
1808   PRIMARY KEY(cert_id)
1809 ) TABLE_TYPE;
1810
1811 COMMENT_TABLE(certificates, '')
1812 COMMENT_COLUMN(certificates.cert_id, '')
1813 COMMENT_COLUMN(certificates.cert_type, '')
1814 COMMENT_COLUMN(certificates.cert_data, '')
1815 COMMENT_COLUMN(certificates.subject, '')
1816 COMMENT_COLUMN(certificates.comments, '')
1817
1818 /*
1819 ** Audit log
1820 */
1821 CREATE TABLE audit_log
1822 (
1823   record_id integer not null,
1824   timestamp integer not null,
1825   subsystem varchar(32) not null,
1826   success integer not null,
1827   user_id integer not null, // 0x7FFFFFFF for system
1828   workstation varchar(63) not null,
1829   session_id integer not null,
1830   object_id integer not null,
1831   message SQL_TEXT null,
1832   PRIMARY KEY(record_id)
1833 ) TABLE_TYPE;
1834
1835 COMMENT_TABLE(audit_log, '')
1836 COMMENT_COLUMN(audit_log.record_id, '')
1837 COMMENT_COLUMN(audit_log.timestamp, '')
1838 COMMENT_COLUMN(audit_log.subsystem, '')
1839 COMMENT_COLUMN(audit_log.success, '')
1840 COMMENT_COLUMN(audit_log.user_id, '')
1841 COMMENT_COLUMN(audit_log.workstation, '')
1842 COMMENT_COLUMN(audit_log.session_id, '')
1843 COMMENT_COLUMN(audit_log.object_id, '')
1844 COMMENT_COLUMN(audit_log.message, '')
1845
1846 /*
1847 ** Situations
1848 */
1849 CREATE TABLE situations
1850 (
1851   id integer not null,
1852   name varchar(127) not null,
1853   comments SQL_TEXT not null,
1854   PRIMARY KEY(id)
1855 ) TABLE_TYPE;
1856
1857 COMMENT_TABLE(situations, '')
1858 COMMENT_COLUMN(situations.id, '')
1859 COMMENT_COLUMN(situations.name, '')
1860 COMMENT_COLUMN(situations.comments, '')
1861
1862 /*
1863 ** List of possible community strings
1864 */
1865 CREATE TABLE snmp_communities
1866 (
1867   id integer not null,
1868   community varchar(255) null,
1869   PRIMARY KEY(id)
1870 ) TABLE_TYPE;
1871
1872 COMMENT_TABLE(snmp_communities, '')
1873 COMMENT_COLUMN(snmp_communities.id, '')
1874 COMMENT_COLUMN(snmp_communities.community, '')
1875
1876 /*
1877 ** Agent policies - common attributes
1878 */
1879 CREATE TABLE ap_common
1880 (
1881   id integer not null,
1882   policy_type integer not null,
1883   version integer not null,
1884   PRIMARY KEY(id)
1885 ) TABLE_TYPE;
1886
1887 COMMENT_TABLE(ap_common, 'Agent policies common attributes')
1888 COMMENT_COLUMN(ap_common.id, 'Unique policy identifier')
1889 COMMENT_COLUMN(ap_common.policy_type, 'Policy type')
1890 COMMENT_COLUMN(ap_common.version, 'Policy version')
1891
1892 /*
1893 ** Agent policies - node binding
1894 */
1895 CREATE TABLE ap_bindings
1896 (
1897   policy_id integer not null,
1898   node_id integer not null,
1899   PRIMARY KEY(policy_id,node_id)
1900 ) TABLE_TYPE;
1901
1902 COMMENT_TABLE(ap_bindings, 'Agent policies to node binding')
1903 COMMENT_COLUMN(ap_bindings.policy_id, 'Unique policy identifier')
1904 COMMENT_COLUMN(ap_bindings.node_id, 'Unique node identifier')
1905
1906 /*
1907 ** Agent policies - configuration files
1908 */
1909 CREATE TABLE ap_config_files
1910 (
1911   policy_id integer not null,
1912   file_content SQL_TEXT null,
1913   PRIMARY KEY(policy_id)
1914 ) TABLE_TYPE;
1915
1916 COMMENT_TABLE(ap_config_files, 'Agent policies configuration files')
1917 COMMENT_COLUMN(ap_config_files.policy_id, 'Unique policy identifier')
1918 COMMENT_COLUMN(ap_config_files.file_content, 'Policy file content')
1919
1920 /**
1921 ** Agent policies - log parser files
1922 */
1923 CREATE TABLE ap_log_parser
1924 (
1925   policy_id integer not null,
1926   file_content SQL_TEXT null,
1927   PRIMARY KEY(policy_id)
1928 ) TABLE_TYPE;
1929
1930 COMMENT_TABLE(ap_log_parser, 'Agent policies log parser files')
1931 COMMENT_COLUMN(ap_log_parser.policy_id, 'Unique policy identifier')
1932 COMMENT_COLUMN(ap_log_parser.file_content, 'Log parser policy file content')
1933
1934 /*
1935 ** Default SNMP v3 USM credentials
1936 */
1937 CREATE TABLE usm_credentials
1938 (
1939   id integer not null,
1940   user_name varchar(255) not null,
1941   auth_method integer not null,
1942   priv_method integer not null,
1943   auth_password varchar(255),
1944   priv_password varchar(255),
1945   PRIMARY KEY(id)
1946 ) TABLE_TYPE;
1947
1948 COMMENT_TABLE(usm_credentials, 'Default SNMP v3 USM credentials')
1949 COMMENT_COLUMN(usm_credentials.id, 'SNMP v3 USM credential unique identifier')
1950 COMMENT_COLUMN(usm_credentials.user_name, 'SNMP v3 USM username')
1951 COMMENT_COLUMN(usm_credentials.auth_method, 'SNMP v3 USM authentication method')
1952 COMMENT_COLUMN(usm_credentials.priv_method, 'SNMP v3 USM encryption method')
1953 COMMENT_COLUMN(usm_credentials.auth_password, 'SNMP v3 USM authentication password')
1954 COMMENT_COLUMN(usm_credentials.priv_password, 'SNMP v3 USM encryption password')
1955
1956 /*
1957 ** Network maps
1958 */
1959 CREATE TABLE network_maps
1960 (
1961   id integer not null,
1962   map_type integer not null,
1963   layout integer not null,
1964   seed integer not null,
1965   radius integer not null,
1966   background varchar(36) null,
1967   bg_latitude varchar(20) null,
1968   bg_longitude varchar(20) null,
1969   bg_zoom integer null,
1970   flags integer not null,
1971   bg_color integer not null,
1972   link_color integer not null,
1973   link_routing integer not null,
1974   object_display_mode integer not null,
1975   filter SQL_TEXT null,
1976   PRIMARY KEY(id)
1977 ) TABLE_TYPE;
1978
1979 COMMENT_TABLE(network_maps, '')
1980 COMMENT_COLUMN(network_maps.id, '')
1981 COMMENT_COLUMN(network_maps.map_type, '')
1982 COMMENT_COLUMN(network_maps.layout, '')
1983 COMMENT_COLUMN(network_maps.seed, '')
1984 COMMENT_COLUMN(network_maps.radius, '')
1985 COMMENT_COLUMN(network_maps.background, '')
1986 COMMENT_COLUMN(network_maps.bg_latitude, '')
1987 COMMENT_COLUMN(network_maps.bg_longitude, '')
1988 COMMENT_COLUMN(network_maps.bg_zoom, '')
1989 COMMENT_COLUMN(network_maps.flags, '')
1990 COMMENT_COLUMN(network_maps.bg_color, '')
1991 COMMENT_COLUMN(network_maps.link_color, '')
1992 COMMENT_COLUMN(network_maps.link_routing, '')
1993 COMMENT_COLUMN(network_maps.object_display_mode, '')
1994 COMMENT_COLUMN(network_maps.filter, '')
1995
1996 /**
1997  * Elements of network maps
1998  */
1999 CREATE TABLE network_map_elements
2000 (
2001   map_id integer not null,
2002   element_id integer not null,
2003   element_type integer not null,
2004   element_data SQL_TEXT not null,
2005   flags integer not null,
2006   PRIMARY KEY(map_id,element_id)
2007 ) TABLE_TYPE;
2008
2009 COMMENT_TABLE(network_map_elements, '')
2010 COMMENT_COLUMN(network_map_elements.map_id, '')
2011 COMMENT_COLUMN(network_map_elements.element_id, '')
2012 COMMENT_COLUMN(network_map_elements.element_type, '')
2013 COMMENT_COLUMN(network_map_elements.element_data, '')
2014 COMMENT_COLUMN(network_map_elements.flags, '')
2015
2016 /**
2017  * Links on network maps
2018  */
2019 CREATE TABLE network_map_links
2020 (
2021   map_id integer not null,
2022   element1 integer not null,
2023   element2 integer not null,
2024   link_type integer not null,
2025   link_name varchar(255) null,
2026   connector_name1 varchar(255) null,
2027   connector_name2 varchar(255) null,
2028   element_data SQL_TEXT null,
2029   flags integer not null
2030 ) TABLE_TYPE;
2031
2032 CREATE INDEX idx_network_map_links_map_id ON network_map_links(map_id);
2033
2034 COMMENT_TABLE(network_map_links, '')
2035 COMMENT_COLUMN(network_map_links.map_id, '')
2036 COMMENT_COLUMN(network_map_links.element1, '')
2037 COMMENT_COLUMN(network_map_links.element2, '')
2038 COMMENT_COLUMN(network_map_links.link_type, '')
2039 COMMENT_COLUMN(network_map_links.link_name, '')
2040 COMMENT_COLUMN(network_map_links.connector_name1, '')
2041 COMMENT_COLUMN(network_map_links.connector_name2, '')
2042 COMMENT_COLUMN(network_map_links.element_data, '')
2043 COMMENT_COLUMN(network_map_links.flags, '')
2044
2045 /**
2046  * Image Library
2047  */
2048 CREATE TABLE images
2049 (
2050   guid varchar(36) not null,
2051   name varchar(63) not null,
2052   category varchar(63) not null,
2053   mimetype varchar(32) not null,
2054   protected integer default 0,
2055   PRIMARY KEY(guid),
2056   UNIQUE(name, category)
2057 ) TABLE_TYPE;
2058
2059 COMMENT_TABLE(images, '')
2060 COMMENT_COLUMN(images.guid, '')
2061 COMMENT_COLUMN(images.name, '')
2062 COMMENT_COLUMN(images.category, '')
2063 COMMENT_COLUMN(images.mimetype, '')
2064 COMMENT_COLUMN(images.protected, '')
2065
2066 /**
2067  * Dashboards
2068  */
2069 CREATE TABLE dashboards
2070 (
2071   id integer not null,
2072   num_columns integer not null,
2073   options integer not null,
2074   PRIMARY KEY(id)
2075 ) TABLE_TYPE;
2076
2077 COMMENT_TABLE(dashboards, '')
2078 COMMENT_COLUMN(dashboards.id, '')
2079 COMMENT_COLUMN(dashboards.num_columns, '')
2080 COMMENT_COLUMN(dashboards.options, '')
2081
2082 /**
2083  * Dashboard elements
2084  */
2085 CREATE TABLE dashboard_elements
2086 (
2087   dashboard_id integer not null,
2088   element_id integer not null,
2089   element_type integer not null,
2090   element_data SQL_TEXT null,
2091   layout_data SQL_TEXT null,
2092   PRIMARY KEY(dashboard_id,element_id)
2093 ) TABLE_TYPE;
2094
2095 COMMENT_TABLE(dashboard_elements, '')
2096 COMMENT_COLUMN(dashboard_elements.dashboard_id, '')
2097 COMMENT_COLUMN(dashboard_elements.element_id, '')
2098 COMMENT_COLUMN(dashboard_elements.element_type, '')
2099 COMMENT_COLUMN(dashboard_elements.element_data, '')
2100 COMMENT_COLUMN(dashboard_elements.layout_data, '')
2101
2102 /**
2103  * Associations between dashboards and other objects
2104  */
2105 CREATE TABLE dashboard_associations
2106 (
2107   object_id integer not null,
2108   dashboard_id integer not null,
2109   PRIMARY KEY(object_id,dashboard_id)
2110 ) TABLE_TYPE;
2111
2112 COMMENT_TABLE(dashboard_associations, '')
2113 COMMENT_COLUMN(dashboard_associations.object_id, '')
2114 COMMENT_COLUMN(dashboard_associations.dashboard_id, '')
2115
2116 /**
2117  * SLA business services
2118  */
2119 CREATE TABLE business_services
2120 (
2121   service_id integer not null,
2122   PRIMARY KEY(service_id)
2123 ) TABLE_TYPE;
2124
2125 COMMENT_TABLE(business_services, '')
2126 COMMENT_COLUMN(business_services.service_id, '')
2127
2128 /**
2129  * SLA service checks
2130  */
2131 CREATE TABLE slm_checks
2132 (
2133   id integer not null,
2134   type integer not null, // 1 - threshold, 2 - script
2135   content SQL_TEXT null, // if type == 2
2136   threshold_id integer not null, // if type == 1; refers to thresholds.id
2137   reason varchar(255) null,
2138   is_template integer not null,
2139   template_id integer not null,  // template check ID
2140   current_ticket integer not null,
2141   PRIMARY KEY(id)
2142 ) TABLE_TYPE;
2143
2144 COMMENT_TABLE(slm_checks, '')
2145 COMMENT_COLUMN(slm_checks.id, '')
2146 COMMENT_COLUMN(slm_checks.type, '')
2147 COMMENT_COLUMN(slm_checks.content, '')
2148 COMMENT_COLUMN(slm_checks.threshold_id, '')
2149 COMMENT_COLUMN(slm_checks.reason, '')
2150 COMMENT_COLUMN(slm_checks.is_template, '')
2151 COMMENT_COLUMN(slm_checks.template_id, '')
2152 COMMENT_COLUMN(slm_checks.current_ticket, '')
2153
2154 /*
2155 ** SLA node links
2156 */
2157 CREATE TABLE node_links
2158 (
2159   nodelink_id integer not null,
2160   node_id integer not null, // refers to node.id
2161   PRIMARY KEY(nodelink_id)
2162 ) TABLE_TYPE;
2163
2164 COMMENT_TABLE(node_links, '')
2165 COMMENT_COLUMN(node_links.nodelink_id, '')
2166 COMMENT_COLUMN(node_links.node_id, '')
2167
2168 /*
2169 ** SLA - agreements
2170 */
2171 CREATE TABLE slm_agreements
2172 (
2173   agreement_id integer not null,
2174   service_id integer not null,
2175   org_id integer not null,
2176   uptime varchar(63) not null, // required uptime, numeric(10,2) ?
2177   period integer not null, // 1 - day, 2 - month
2178   start_date integer not null, // date in YYYYMMDD format
2179   notes varchar(255),
2180   PRIMARY KEY(agreement_id)
2181 ) TABLE_TYPE;
2182
2183 COMMENT_TABLE(slm_agreements, '')
2184 COMMENT_COLUMN(slm_agreements.agreement_id, '')
2185 COMMENT_COLUMN(slm_agreements.service_id, '')
2186 COMMENT_COLUMN(slm_agreements.org_id, '')
2187 COMMENT_COLUMN(slm_agreements.uptime, '')
2188 COMMENT_COLUMN(slm_agreements.period, '')
2189 COMMENT_COLUMN(slm_agreements.start_date, '')
2190 COMMENT_COLUMN(slm_agreements.notes, '')
2191
2192 /*
2193 ** SLA tickets
2194 */
2195
2196 CREATE TABLE slm_tickets
2197 (
2198   ticket_id integer not null,
2199   service_id integer not null,
2200   check_id integer not null,
2201   create_timestamp integer not null,
2202   close_timestamp integer not null,
2203   reason varchar(255) null,
2204   PRIMARY KEY(ticket_id)
2205 ) TABLE_TYPE;
2206
2207 COMMENT_TABLE(slm_tickets, '')
2208 COMMENT_COLUMN(slm_tickets.ticket_id, '')
2209 COMMENT_COLUMN(slm_tickets.service_id, '')
2210 COMMENT_COLUMN(slm_tickets.check_id, '')
2211 COMMENT_COLUMN(slm_tickets.create_timestamp, '')
2212 COMMENT_COLUMN(slm_tickets.close_timestamp, '')
2213 COMMENT_COLUMN(slm_tickets.reason, '')
2214
2215 /*
2216 ** SLA service change log
2217 */
2218
2219 CREATE TABLE slm_service_history
2220 (
2221   record_id integer not null,
2222   service_id integer not null,
2223   change_timestamp integer not null,
2224   new_status integer not null,
2225   PRIMARY KEY(record_id)
2226 ) TABLE_TYPE;
2227
2228 COMMENT_TABLE(slm_service_history, '')
2229 COMMENT_COLUMN(slm_service_history.record_id, '')
2230 COMMENT_COLUMN(slm_service_history.service_id, '')
2231 COMMENT_COLUMN(slm_service_history.change_timestamp, '')
2232 COMMENT_COLUMN(slm_service_history.new_status, '')
2233
2234
2235 /*
2236 ** Organizations
2237 */
2238
2239 CREATE TABLE organizations
2240 (
2241   id integer not null,
2242   parent_id integer not null,
2243   org_type integer not null,
2244   name varchar(63) not null,
2245   description varchar(255),
2246   manager integer not null, // manager - id from "persons" table
2247   PRIMARY KEY(id)
2248 ) TABLE_TYPE;
2249
2250 COMMENT_TABLE(organizations, '')
2251 COMMENT_COLUMN(organizations.id, '')
2252 COMMENT_COLUMN(organizations.parent_id, '')
2253 COMMENT_COLUMN(organizations.org_type, '')
2254 COMMENT_COLUMN(organizations.name, '')
2255 COMMENT_COLUMN(organizations.description, '')
2256 COMMENT_COLUMN(organizations.manager, '')
2257
2258 /*
2259 ** Persons
2260 */
2261
2262 CREATE TABLE persons
2263 (
2264   id integer not null,
2265   org_id integer not null,
2266   first_name varchar(63),
2267   last_name varchar(63),
2268   title varchar(255),
2269   status integer not null,
2270   PRIMARY KEY(id)
2271 ) TABLE_TYPE;
2272
2273 COMMENT_TABLE(persons, '')
2274 COMMENT_COLUMN(persons.id, '')
2275 COMMENT_COLUMN(persons.org_id, '')
2276 COMMENT_COLUMN(persons.first_name, '')
2277 COMMENT_COLUMN(persons.last_name, '')
2278 COMMENT_COLUMN(persons.title, '')
2279 COMMENT_COLUMN(persons.status, '')
2280
2281 /*
2282 ** Job history
2283 */
2284 CREATE TABLE job_history
2285 (
2286   id integer not null,
2287   time_created integer not null,
2288   time_started integer not null,
2289   time_finished integer not null,
2290   job_type varchar(127) null,
2291   description varchar(255) null,
2292   additional_info varchar(255) null,
2293   node_id integer not null,
2294   user_id integer not null,
2295   status integer not null,
2296   failure_message varchar(255) null,
2297   PRIMARY KEY(id)
2298 ) TABLE_TYPE;
2299
2300 COMMENT_TABLE(job_history, '')
2301 COMMENT_COLUMN(job_history.id, '')
2302 COMMENT_COLUMN(job_history.time_created, '')
2303 COMMENT_COLUMN(job_history.time_started, '')
2304 COMMENT_COLUMN(job_history.time_finished, '')
2305 COMMENT_COLUMN(job_history.job_type, '')
2306 COMMENT_COLUMN(job_history.description, '')
2307 COMMENT_COLUMN(job_history.additional_info, '')
2308 COMMENT_COLUMN(job_history.node_id, '')
2309 COMMENT_COLUMN(job_history.user_id, '')
2310 COMMENT_COLUMN(job_history.status, '')
2311 COMMENT_COLUMN(job_history.failure_message, '')
2312
2313 /*
2314 ** License repository
2315 */
2316 CREATE TABLE licenses
2317 (
2318   id integer not null,
2319   content SQL_TEXT null,
2320   PRIMARY KEY(id)
2321 ) TABLE_TYPE;
2322
2323 COMMENT_TABLE(licenses, '')
2324 COMMENT_COLUMN(licenses.id, '')
2325 COMMENT_COLUMN(licenses.content, '')
2326
2327 /*
2328 ** Mapping tables
2329 */
2330 CREATE TABLE mapping_tables
2331 (
2332   id integer not null,
2333   name varchar(63) not null,
2334   flags integer not null,
2335   description SQL_TEXT4K null,
2336   PRIMARY KEY(id)
2337 ) TABLE_TYPE;
2338
2339 COMMENT_TABLE(mapping_tables, '')
2340 COMMENT_COLUMN(mapping_tables.id, '')
2341 COMMENT_COLUMN(mapping_tables.name, '')
2342 COMMENT_COLUMN(mapping_tables.flags, '')
2343 COMMENT_COLUMN(mapping_tables.description, '')
2344
2345 /*
2346 ** Mapping tables content
2347 */
2348 CREATE TABLE mapping_data
2349 (
2350   table_id integer not null,
2351   md_key varchar(63) not null,
2352   md_value varchar(255) null,
2353   description SQL_TEXT4K null,
2354   PRIMARY KEY(table_id,md_key)
2355 ) TABLE_TYPE;
2356
2357 COMMENT_TABLE(mapping_data, '')
2358 COMMENT_COLUMN(mapping_data.table_id, '')
2359 COMMENT_COLUMN(mapping_data.md_key, '')
2360 COMMENT_COLUMN(mapping_data.md_value, '')
2361 COMMENT_COLUMN(mapping_data.description, '')
2362
2363 /*
2364 ** DCI summary tables
2365 */
2366 CREATE TABLE dci_summary_tables
2367 (
2368   id integer not null,
2369   guid varchar(36) not null,
2370   menu_path varchar(255) null,
2371   title varchar(127) null,
2372   node_filter SQL_TEXT null,
2373   flags integer not null,
2374   columns SQL_TEXT null,
2375   PRIMARY KEY(id)
2376 ) TABLE_TYPE;
2377
2378 COMMENT_TABLE(dci_summary_tables, '')
2379 COMMENT_COLUMN(dci_summary_tables.id, '')
2380 COMMENT_COLUMN(dci_summary_tables.guid, '')
2381 COMMENT_COLUMN(dci_summary_tables.menu_path, '')
2382 COMMENT_COLUMN(dci_summary_tables.title, '')
2383 COMMENT_COLUMN(dci_summary_tables.node_filter, '')
2384 COMMENT_COLUMN(dci_summary_tables.flags, '')
2385 COMMENT_COLUMN(dci_summary_tables.columns, '')
2386
2387 /*
2388 ** Scheduled tasks
2389 */
2390 CREATE TABLE scheduled_tasks
2391 (
2392   id integer not null,
2393   taskId varchar(255) null,
2394   schedule varchar(127) null,
2395   params varchar(1023) null,
2396   execution_time integer not null,
2397   last_execution_time integer not null,
2398   flags integer not null,
2399   owner integer not null,
2400   object_id integer not null,
2401   PRIMARY KEY(id)
2402 ) TABLE_TYPE;
2403
2404 COMMENT_TABLE(scheduled_tasks, '')
2405 COMMENT_COLUMN(scheduled_tasks.id, '')
2406 COMMENT_COLUMN(scheduled_tasks.taskId, '')
2407 COMMENT_COLUMN(scheduled_tasks.schedule, '')
2408 COMMENT_COLUMN(scheduled_tasks.params, '')
2409 COMMENT_COLUMN(scheduled_tasks.execution_time, '')
2410 COMMENT_COLUMN(scheduled_tasks.last_execution_time, '')
2411 COMMENT_COLUMN(scheduled_tasks.flags, '')
2412 COMMENT_COLUMN(scheduled_tasks.owner, '')
2413 COMMENT_COLUMN(scheduled_tasks.object_id, '')
2414
2415 /*
2416 ** ZMQ Subscriptions
2417 */
2418 CREATE TABLE zmq_subscription
2419 (
2420   object_id integer not null,
2421   subscription_type char(1) not null,
2422   ignore_items integer not null,
2423   items SQL_TEXT,
2424   PRIMARY KEY(object_id, subscription_type)
2425 ) TABLE_TYPE;
2426
2427 COMMENT_TABLE(zmq_subscription, '')
2428 COMMENT_COLUMN(zmq_subscription.object_id, '')
2429 COMMENT_COLUMN(zmq_subscription.subscription_type, '')
2430 COMMENT_COLUMN(zmq_subscription.ignore_items, '')
2431 COMMENT_COLUMN(zmq_subscription.items, '')
2432
2433 /*
2434 ** Currency codes
2435 */
2436 CREATE TABLE currency_codes
2437 (
2438         numeric_code char(3) not null,
2439         alpha_code char(3) not null,
2440         description varchar(127) not null,
2441         exponent integer not null,
2442         PRIMARY KEY(numeric_code)
2443 ) TABLE_TYPE;
2444
2445 COMMENT_TABLE(currency_codes, 'List of currency codes')
2446 COMMENT_COLUMN(currency_codes.numeric_code, 'Numeric ISO code')
2447 COMMENT_COLUMN(currency_codes.alpha_code, 'Alphabetical ISO code')
2448 COMMENT_COLUMN(currency_codes.description, 'Description')
2449 COMMENT_COLUMN(currency_codes.exponent, 'Exponent for minor units')
2450
2451 /*
2452 ** Country codes
2453 */
2454 CREATE TABLE country_codes
2455 (
2456         numeric_code char(3) not null,
2457         alpha_code char(2) not null,
2458         alpha3_code char(3) not null,
2459         name varchar(127) not null,
2460         PRIMARY KEY(numeric_code)
2461 ) TABLE_TYPE;
2462
2463 COMMENT_TABLE(country_codes, 'List of country codes')
2464 COMMENT_COLUMN(country_codes.numeric_code, 'Numeric ISO code')
2465 COMMENT_COLUMN(country_codes.alpha_code, 'Alphabetical 2 character ISO code')
2466 COMMENT_COLUMN(country_codes.alpha3_code, 'Alphabetical 3 character ISO code')
2467 COMMENT_COLUMN(country_codes.name, 'Country name')
2468
2469 /*
2470 ** Config repositories
2471 */
2472 CREATE TABLE config_repositories
2473 (
2474         id integer not null,
2475         url varchar(1023) not null,
2476         auth_token varchar(63) null,
2477         description varchar(1023) null,
2478         PRIMARY KEY(id)
2479 ) TABLE_TYPE;