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