fix in DB upgrade procedure and schema init script
[public/netxms.git] / sql / schema.in
CommitLineData
6c9e7d36 1/*
50cbbfec
VK
2** NetXMS Database Schema
3**
51ff26ea 4** ex: syntax=sql ts=2 sw=2 expandtab
6c9e7d36 5*/
cbcaf8c8 6
28f5b9a4
VK
7/*
8** Metadata
9*/
28f5b9a4
VK
10CREATE TABLE metadata
11(
51ff26ea
AK
12 var_name varchar(63) not null,
13 var_value varchar(255) not null,
14 PRIMARY KEY(var_name)
28f5b9a4
VK
15) TABLE_TYPE;
16
c85c8ef2 17COMMENT_TABLE(metadata, 'System meta data (DBMS type, schema version, etc.)')
938a6733
AK
18COMMENT_COLUMN(metadata.var_name, 'Variable name')
19COMMENT_COLUMN(metadata.var_value, 'Variable value')
20
50cbbfec
VK
21/*
22** System configuration table
23*/
feea53fd 24CREATE TABLE config
cbcaf8c8 25(
51ff26ea
AK
26 var_name varchar(63) not null,
27 var_value varchar(2000) null,
963c030d 28#ifdef DB_ORACLE
51ff26ea
AK
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,
963c030d 33#else
51ff26ea
AK
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',
963c030d 38#endif
51ff26ea
AK
39 description varchar(255) null,
40 possible_values SQL_TEXT null,
41 PRIMARY KEY(var_name)
cb7ec554 42) TABLE_TYPE;
cbcaf8c8 43
51ff26ea 44COMMENT_TABLE(config, 'Server configuration')
085ed918
AK
45COMMENT_COLUMN(config.var_name, 'Parameter name')
46COMMENT_COLUMN(config.var_value, 'Value')
47COMMENT_COLUMN(config.is_visible, 'GUI visibility indicator')
48COMMENT_COLUMN(config.need_server_restart, 'Change take effect after server restart')
49COMMENT_COLUMN(config.data_type, 'Not used in current verion')
50COMMENT_COLUMN(config.is_public, 'No used in current version')
51COMMENT_COLUMN(config.description, 'Not used in current version')
52COMMENT_COLUMN(config.possible_values, 'Not used in current version')
938a6733 53
07f58d3c
VK
54/*
55** System configuration data (for large data)
56*/
07f58d3c
VK
57CREATE TABLE config_clob
58(
51ff26ea
AK
59 var_name varchar(63) not null,
60 var_value SQL_TEXT null,
61 PRIMARY KEY(var_name)
07f58d3c
VK
62) TABLE_TYPE;
63
085ed918
AK
64COMMENT_TABLE(config_clob, 'System configuration table for large data elements')
65COMMENT_COLUMN(config_clob.var_name, 'Parameter name')
66COMMENT_COLUMN(config_clob.var_value, 'Value')
938a6733 67
6c9e7d36
VK
68/*
69** Users
70*/
feea53fd 71CREATE TABLE users
9b057805 72(
51ff26ea
AK
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,
bad2c02e 93 ldap_unique_id varchar(64) null,
51ff26ea 94 PRIMARY KEY(id)
cb7ec554 95) TABLE_TYPE;
9b057805 96
938a6733 97COMMENT_TABLE(users, 'System users')
085ed918
AK
98COMMENT_COLUMN(users.id, 'User id')
99COMMENT_COLUMN(users.guid, 'User guid')
100COMMENT_COLUMN(users.name, 'Login name')
101COMMENT_COLUMN(users.password, 'Hashed password')
102COMMENT_COLUMN(users.system_access, 'Bit-mask of system access rights')
103COMMENT_COLUMN(users.flags, 'Account flags (locked, password never expire etc.)')
104COMMENT_COLUMN(users.full_name, 'Full name')
105COMMENT_COLUMN(users.description, 'Description')
106COMMENT_COLUMN(users.grace_logins, '')
107COMMENT_COLUMN(users.auth_method, '')
108COMMENT_COLUMN(users.cert_mapping_method, '')
109COMMENT_COLUMN(users.cert_mapping_data, '')
110COMMENT_COLUMN(users.auth_failures, '')
111COMMENT_COLUMN(users.last_passwd_change, '')
112COMMENT_COLUMN(users.min_passwd_length, '')
113COMMENT_COLUMN(users.disabled_until, '')
114COMMENT_COLUMN(users.last_login, '')
115COMMENT_COLUMN(users.password_history, '')
116COMMENT_COLUMN(users.xmpp_id, '')
1ea2769b 117COMMENT_COLUMN(users.ldap_dn, 'LDAP dn')
118COMMENT_COLUMN(users.ldap_unique_id, 'LDAP unique id if field set')
9b057805 119
6c9e7d36
VK
120/*
121** User groups
122*/
f806ed94 123
feea53fd 124CREATE TABLE user_groups
f806ed94 125(
51ff26ea
AK
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,
80b13e3c 133 ldap_unique_id varchar(64) null,
51ff26ea 134 PRIMARY KEY(id)
cb7ec554 135) TABLE_TYPE;
f806ed94 136
938a6733 137COMMENT_TABLE(user_groups, 'System user groups')
1ea2769b 138COMMENT_COLUMN(user_groups.id, 'Group id')
139COMMENT_COLUMN(user_groups.guid, 'Group GUID')
140COMMENT_COLUMN(user_groups.name, 'Group name')
141COMMENT_COLUMN(user_groups.system_access, 'Bit-mask of system access rights')
085ed918 142COMMENT_COLUMN(user_groups.flags, '')
1ea2769b 143COMMENT_COLUMN(user_groups.description, 'Description')
144COMMENT_COLUMN(user_groups.ldap_dn, 'LDAP dn')
145COMMENT_COLUMN(users.ldap_unique_id, 'LDAP unique id if field set')
f806ed94 146
6c9e7d36
VK
147/*
148** Users to groups mapping
149*/
f806ed94 150
feea53fd 151CREATE TABLE user_group_members
f806ed94 152(
51ff26ea
AK
153 group_id integer not null,
154 user_id integer not null,
155 PRIMARY KEY(group_id,user_id)
cb7ec554 156) TABLE_TYPE;
f806ed94 157
938a6733 158COMMENT_TABLE(user_group_members, 'Users and group relationships')
1ea2769b 159COMMENT_COLUMN(user_group_members.group_id, 'Group id from user_groups table')
160COMMENT_COLUMN(user_group_members.user_id, 'User id from users table')
f806ed94 161
b4fbaa08
VK
162/*
163** User profiles
164*/
165
166CREATE TABLE user_profiles
167(
51ff26ea
AK
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)
b4fbaa08
VK
172) TABLE_TYPE;
173
938a6733 174COMMENT_TABLE(user_profiles, 'User profiles')
085ed918
AK
175COMMENT_COLUMN(user_profiles.user_id, '')
176COMMENT_COLUMN(user_profiles.var_name, '')
177COMMENT_COLUMN(user_profiles.var_value, '')
b4fbaa08 178
c45e0213
VK
179/*
180** Custom attributes for user database objects
181*/
182
183CREATE TABLE userdb_custom_attributes
184(
51ff26ea
AK
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)
c45e0213
VK
189) TABLE_TYPE;
190
085ed918
AK
191COMMENT_TABLE(userdb_custom_attributes, '')
192COMMENT_COLUMN(userdb_custom_attributes.object_id, '')
193COMMENT_COLUMN(userdb_custom_attributes.attr_name, '')
194COMMENT_COLUMN(userdb_custom_attributes.attr_value, '')
c45e0213 195
b4fbaa08
VK
196/*
197** Common object properties
198*/
199
200CREATE TABLE object_properties
201(
51ff26ea
AK
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)
b4fbaa08
VK
232) TABLE_TYPE;
233
1ea2769b 234COMMENT_TABLE(object_properties, 'Object properties of NetObj and everything that is inherited from it(Dashboard, Interface, Node...)')
235COMMENT_COLUMN(object_properties.object_id, 'Object id')
236COMMENT_COLUMN(object_properties.guid, 'Object GUID')
237COMMENT_COLUMN(object_properties.name, 'Object name')
9decb6b9 238COMMENT_COLUMN(object_properties.status, 'Object status(Unknown, Normal, Warning, Minor ...)')
1ea2769b 239COMMENT_COLUMN(object_properties.is_deleted, 'Flag that defines if this object is scheduled for deletion')
240COMMENT_COLUMN(object_properties.is_system, 'Flag that defines if this object is the system object(DashboardRoot, ServiceRoot...)')
7bbd8cf1 241COMMENT_COLUMN(object_properties.last_modified, 'Timestamp when this object was last modified')
085ed918
AK
242COMMENT_COLUMN(object_properties.inherit_access_rights, '')
243COMMENT_COLUMN(object_properties.status_calc_alg, '')
244COMMENT_COLUMN(object_properties.status_prop_alg, '')
245COMMENT_COLUMN(object_properties.status_fixed_val, '')
246COMMENT_COLUMN(object_properties.status_shift, '')
247COMMENT_COLUMN(object_properties.status_translation, '')
248COMMENT_COLUMN(object_properties.status_single_threshold, '')
249COMMENT_COLUMN(object_properties.status_thresholds, '')
7bbd8cf1 250COMMENT_COLUMN(object_properties.comments, 'Comments')
251COMMENT_COLUMN(object_properties.location_type, 'Lacation type(Undefined, Manual, Automatic)')
252COMMENT_COLUMN(object_properties.latitude, 'Latitude value')
253COMMENT_COLUMN(object_properties.longitude, 'Longitude value')
254COMMENT_COLUMN(object_properties.location_accuracy, 'Received location accuracy')
255COMMENT_COLUMN(object_properties.location_timestamp, 'Last received location time')
085ed918
AK
256COMMENT_COLUMN(object_properties.image, '')
257COMMENT_COLUMN(object_properties.submap_id, '')
7bbd8cf1 258COMMENT_COLUMN(object_properties.country, 'Object location country')
259COMMENT_COLUMN(object_properties.city, 'Object location city')
260COMMENT_COLUMN(object_properties.street_address, 'Object location address')
261COMMENT_COLUMN(object_properties.postcode, 'Object location postcode')
085ed918
AK
262COMMENT_COLUMN(object_properties.maint_mode, '')
263COMMENT_COLUMN(object_properties.maint_event_id, '')
b4fbaa08 264
a1236e96
VK
265/*
266** Object custom attributes
267*/
a1236e96
VK
268CREATE TABLE object_custom_attributes
269(
51ff26ea
AK
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)
a1236e96
VK
274) TABLE_TYPE;
275
ff392ecf
VK
276CREATE INDEX idx_ocattr_oid ON object_custom_attributes(object_id);
277
7bbd8cf1 278COMMENT_TABLE(object_custom_attributes, 'Object custom attributes')
279COMMENT_COLUMN(object_custom_attributes.object_id, 'Object id from object_properties')
280COMMENT_COLUMN(object_custom_attributes.attr_name, 'Object attribute name')
281COMMENT_COLUMN(object_custom_attributes.attr_value, 'Object attribute value')
085ed918 282
9587eba3 283/*
9aa67910 284** Zone objects
9587eba3 285*/
9587eba3
VK
286CREATE TABLE zones
287(
51ff26ea
AK
288 id integer not null, // Zone object ID
289 zone_guid integer not null, // Globally unique ID for zone
43b62436 290 proxy_node integer not null,
51ff26ea 291 PRIMARY KEY(id)
9587eba3
VK
292) TABLE_TYPE;
293
7bbd8cf1 294COMMENT_TABLE(zones, 'Zones')
295COMMENT_COLUMN(zones.id, 'Zone id from object_properties')
296COMMENT_COLUMN(zones.zone_guid, 'Zone GUID')
43b62436 297COMMENT_COLUMN(zones.proxy_node, 'ID of proxy node for this zone')
085ed918 298
9aa67910
VK
299/*
300** Mobile device objects
301*/
9aa67910
VK
302CREATE TABLE mobile_devices
303(
51ff26ea
AK
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)
9aa67910
VK
314) TABLE_TYPE;
315
7bbd8cf1 316COMMENT_TABLE(mobile_devices, 'Mobile devices from object_properties')
317COMMENT_COLUMN(mobile_devices.id, 'Mobile device id')
318COMMENT_COLUMN(mobile_devices.device_id, 'Device ID(IMEI or ESN)')
319COMMENT_COLUMN(mobile_devices.vendor, 'Device vendor')
320COMMENT_COLUMN(mobile_devices.model, 'Device model')
321COMMENT_COLUMN(mobile_devices.serial_number, 'Serial number')
322COMMENT_COLUMN(mobile_devices.os_name, 'Operating system name')
323COMMENT_COLUMN(mobile_devices.os_version, 'Operation system version')
085ed918 324COMMENT_COLUMN(mobile_devices.user_id, '')
7bbd8cf1 325COMMENT_COLUMN(mobile_devices.battery_level, 'Battery charge level')
085ed918 326
8715a84c
VK
327/*
328** Access point objects
329*/
8715a84c
VK
330CREATE TABLE access_points
331(
51ff26ea
AK
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)
8715a84c
VK
341) TABLE_TYPE;
342
085ed918
AK
343COMMENT_TABLE(access_points, '')
344COMMENT_COLUMN(access_points.id, '')
345COMMENT_COLUMN(access_points.node_id, '')
346COMMENT_COLUMN(access_points.mac_address, '')
347COMMENT_COLUMN(access_points.vendor, '')
348COMMENT_COLUMN(access_points.model, '')
349COMMENT_COLUMN(access_points.serial_number, '')
350COMMENT_COLUMN(access_points.ap_state, '')
351COMMENT_COLUMN(access_points.ap_index, '')
352
8715a84c
VK
353/*
354** Rack objects
355*/
8715a84c
VK
356CREATE TABLE racks
357(
51ff26ea
AK
358 id integer not null,
359 height integer not null,
360 top_bottom_num char(1) not null,
361 PRIMARY KEY(id)
8715a84c
VK
362) TABLE_TYPE;
363
7bbd8cf1 364COMMENT_TABLE(racks, 'Racks')
365COMMENT_COLUMN(racks.id, 'Rack id form object_properties')
085ed918
AK
366COMMENT_COLUMN(racks.height, '')
367COMMENT_COLUMN(racks.top_bottom_num, '')
368
e4926628
VK
369/*
370** Chassis objects
371*/
372CREATE TABLE chassis
373(
374 id integer not null,
375 controller_id integer not null,
32a400d9 376 flags integer not null,
e4926628
VK
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
384COMMENT_TABLE(chassis, 'Chassis')
385COMMENT_COLUMN(chassis.id, 'Chassis id form object_properties')
386COMMENT_COLUMN(chassis.controller_id, 'Id of node object providing management capabilities for this chassis')
32a400d9 387COMMENT_COLUMN(chassis.flags, 'Chassis options as bit flags')
e4926628
VK
388COMMENT_COLUMN(chassis.rack_id, 'Related rack object ID')
389COMMENT_COLUMN(chassis.rack_image, 'Image to be used in rack view')
390COMMENT_COLUMN(chassis.rack_position, 'Position in rack (in rack units)')
391COMMENT_COLUMN(chassis.rack_height, 'Height in rack (in rack units)')
392
6c9e7d36
VK
393/*
394** Nodes information
395*/
feea53fd 396CREATE TABLE nodes
cbcaf8c8 397(
51ff26ea
AK
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,
e4926628 435 chassis_id integer not null,
51ff26ea
AK
436 agent_cache_mode char(1) not null,
437 last_agent_comm_time integer not null,
ba756b1a
VK
438 syslog_msg_count SQL_INT64 not null,
439 snmp_trap_count SQL_INT64 not null,
e980db40
VK
440 node_type integer not null,
441 node_subtype varchar(127) null,
241541f4
VK
442 ssh_login varchar(63) null,
443 ssh_password varchar(63) null,
444 ssh_proxy integer not null,
51ff26ea 445 PRIMARY KEY(id)
cb7ec554 446) TABLE_TYPE;
9e6c6d05 447
7bbd8cf1 448COMMENT_TABLE(nodes, 'Nodes')
449COMMENT_COLUMN(nodes.id, 'Node id from object_properties')
085ed918
AK
450COMMENT_COLUMN(nodes.primary_name, '')
451COMMENT_COLUMN(nodes.primary_ip, '')
452COMMENT_COLUMN(nodes.node_flags, '')
453COMMENT_COLUMN(nodes.runtime_flags, '')
454COMMENT_COLUMN(nodes.snmp_version, '')
455COMMENT_COLUMN(nodes.snmp_port, '')
456COMMENT_COLUMN(nodes.community, '')
457COMMENT_COLUMN(nodes.usm_auth_password, '')
458COMMENT_COLUMN(nodes.usm_priv_password, '')
459COMMENT_COLUMN(nodes.usm_methods, '')
460COMMENT_COLUMN(nodes.snmp_oid, '')
461COMMENT_COLUMN(nodes.auth_method, '')
462COMMENT_COLUMN(nodes.secret, '')
463COMMENT_COLUMN(nodes.agent_port, '')
464COMMENT_COLUMN(nodes.status_poll_type, '')
465COMMENT_COLUMN(nodes.agent_version, '')
466COMMENT_COLUMN(nodes.platform_name, '')
467COMMENT_COLUMN(nodes.poller_node_id, '')
468COMMENT_COLUMN(nodes.zone_guid, '')
469COMMENT_COLUMN(nodes.proxy_node, '')
470COMMENT_COLUMN(nodes.snmp_proxy, '')
471COMMENT_COLUMN(nodes.icmp_proxy, '')
472COMMENT_COLUMN(nodes.required_polls, '')
473COMMENT_COLUMN(nodes.uname, '')
474COMMENT_COLUMN(nodes.use_ifxtable, '')
475COMMENT_COLUMN(nodes.snmp_sys_name, '')
476COMMENT_COLUMN(nodes.snmp_sys_contact, '')
477COMMENT_COLUMN(nodes.snmp_sys_location, '')
478COMMENT_COLUMN(nodes.bridge_base_addr, '')
479COMMENT_COLUMN(nodes.down_since, '')
480COMMENT_COLUMN(nodes.boot_time, '')
481COMMENT_COLUMN(nodes.driver_name, '')
482COMMENT_COLUMN(nodes.rack_image, '')
483COMMENT_COLUMN(nodes.rack_position, '')
484COMMENT_COLUMN(nodes.rack_height, '')
485COMMENT_COLUMN(nodes.rack_id, '')
e4926628 486COMMENT_COLUMN(nodes.chassis_id, 'ID of chassis object this node belongs to')
085ed918
AK
487COMMENT_COLUMN(nodes.agent_cache_mode, '')
488COMMENT_COLUMN(nodes.last_agent_comm_time, '')
ba756b1a
VK
489COMMENT_COLUMN(nodes.syslog_msg_count, 'Total number of received syslog messages')
490COMMENT_COLUMN(nodes.snmp_trap_count, 'Total number of received SNMP traps')
9e6c6d05 491
97e09882
VK
492/*
493** Clusters information
494*/
495
375e0736 496CREATE TABLE clusters
97e09882 497(
51ff26ea
AK
498 id integer not null,
499 cluster_type integer not null,
500 zone_guid integer not null,
501 PRIMARY KEY(id)
375e0736 502) TABLE_TYPE;
97e09882 503
7bbd8cf1 504COMMENT_TABLE(clusters, 'Clusters')
505COMMENT_COLUMN(clusters.id, 'Cluster id form object_properties')
506COMMENT_COLUMN(clusters.cluster_type, 'Cluster type')
507COMMENT_COLUMN(clusters.zone_guid, 'Zone GUID form zones table')
97e09882
VK
508
509/*
510** Cluster members
511*/
512
375e0736 513CREATE TABLE cluster_members
97e09882 514(
51ff26ea
AK
515 cluster_id integer not null,
516 node_id integer not null,
517 PRIMARY KEY(cluster_id,node_id)
375e0736 518) TABLE_TYPE;
97e09882 519
7bbd8cf1 520COMMENT_TABLE(cluster_members, 'Cluster members')
521COMMENT_COLUMN(cluster_members.cluster_id, 'Cluster id from clusters table')
522COMMENT_COLUMN(cluster_members.node_id, 'Node id form nodes table')
97e09882 523
a14a4916
VK
524/*
525** Cluster interconnect subnets
526*/
527
375e0736 528CREATE TABLE cluster_sync_subnets
a14a4916 529(
51ff26ea
AK
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)
375e0736 534) TABLE_TYPE;
a14a4916 535
085ed918
AK
536COMMENT_TABLE(cluster_sync_subnets, '')
537COMMENT_COLUMN(cluster_sync_subnets.cluster_id, '')
538COMMENT_COLUMN(cluster_sync_subnets.subnet_addr, '')
539COMMENT_COLUMN(cluster_sync_subnets.subnet_mask, '')
a14a4916 540
6f512367
VK
541/*
542** Cluster resources
543*/
544
545CREATE TABLE cluster_resources
546(
51ff26ea
AK
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)
6f512367
VK
553) TABLE_TYPE;
554
085ed918
AK
555COMMENT_TABLE(cluster_resources, '')
556COMMENT_COLUMN(cluster_resources.cluster_id, '')
557COMMENT_COLUMN(cluster_resources.resource_id, '')
558COMMENT_COLUMN(cluster_resources.resource_name, '')
559COMMENT_COLUMN(cluster_resources.ip_addr, '')
560COMMENT_COLUMN(cluster_resources.current_owner, '')
561
6c9e7d36
VK
562/*
563** Subnets
564*/
feea53fd 565CREATE TABLE subnets
cbcaf8c8 566(
51ff26ea
AK
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)
cb7ec554 573) TABLE_TYPE;
cbcaf8c8 574
085ed918
AK
575COMMENT_TABLE(subnets, '')
576
6c9e7d36
VK
577/*
578** Nodes' interfaces
579*/
feea53fd 580CREATE TABLE interfaces
cbcaf8c8 581(
51ff26ea
AK
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)
cb7ec554 605) TABLE_TYPE;
cbcaf8c8 606
085ed918
AK
607COMMENT_TABLE(interfaces, '')
608COMMENT_COLUMN(interfaces.id, '')
609COMMENT_COLUMN(interfaces.node_id, '')
610COMMENT_COLUMN(interfaces.flags, '')
611COMMENT_COLUMN(interfaces.if_type, '')
612COMMENT_COLUMN(interfaces.if_index, '')
613COMMENT_COLUMN(interfaces.mtu, '')
614COMMENT_COLUMN(interfaces.speed, '')
615COMMENT_COLUMN(interfaces.bridge_port, '')
616COMMENT_COLUMN(interfaces.phy_slot, '')
617COMMENT_COLUMN(interfaces.phy_port, '')
618COMMENT_COLUMN(interfaces.peer_node_id, '')
619COMMENT_COLUMN(interfaces.peer_if_id, '')
620COMMENT_COLUMN(interfaces.peer_proto, '')
621COMMENT_COLUMN(interfaces.mac_addr, '')
622COMMENT_COLUMN(interfaces.required_polls, '')
623COMMENT_COLUMN(interfaces.admin_state, '')
624COMMENT_COLUMN(interfaces.oper_state, '')
625COMMENT_COLUMN(interfaces.dot1x_pae_state, '')
626COMMENT_COLUMN(interfaces.dot1x_backend_state, '')
627COMMENT_COLUMN(interfaces.description, '')
628COMMENT_COLUMN(interfaces.alias, '')
629COMMENT_COLUMN(interfaces.iftable_suffix, '')
630
c30c0c0f
VK
631/*
632** Interface IP addresses
633*/
634CREATE TABLE interface_address_list
635(
51ff26ea
AK
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)
c30c0c0f 640) TABLE_TYPE;
cbcaf8c8 641
085ed918
AK
642COMMENT_TABLE(interface_address_list, '')
643COMMENT_COLUMN(interface_address_list.iface_id, '')
644COMMENT_COLUMN(interface_address_list.ip_addr, '')
645COMMENT_COLUMN(interface_address_list.ip_netmask, '')
646
bebf4833
VK
647/*
648** Network services
649*/
650
651CREATE TABLE network_services
652(
51ff26ea
AK
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)
bebf4833
VK
664) TABLE_TYPE;
665
085ed918
AK
666COMMENT_TABLE(network_services, '')
667COMMENT_COLUMN(network_services.id, '')
668COMMENT_COLUMN(network_services.node_id, '')
669COMMENT_COLUMN(network_services.service_type, '')
670COMMENT_COLUMN(network_services.ip_bind_addr, '')
671COMMENT_COLUMN(network_services.ip_proto, '')
672COMMENT_COLUMN(network_services.ip_port, '')
673COMMENT_COLUMN(network_services.check_request, '')
674COMMENT_COLUMN(network_services.check_responce, '')
675COMMENT_COLUMN(network_services.poller_node_id, '')
676COMMENT_COLUMN(network_services.required_polls, '')
677
a11d8dab
VK
678/*
679** VPN connectors
680*/
a11d8dab
VK
681CREATE TABLE vpn_connectors
682(
51ff26ea
AK
683 id integer not null,
684 node_id integer not null,
685 peer_gateway integer not null,
686 PRIMARY KEY(id)
a11d8dab
VK
687) TABLE_TYPE;
688
085ed918
AK
689COMMENT_TABLE(vpn_connectors, '')
690COMMENT_COLUMN(vpn_connectors.id, '')
691COMMENT_COLUMN(vpn_connectors.node_id, '')
692COMMENT_COLUMN(vpn_connectors.peer_gateway, '')
693
a11d8dab
VK
694/*
695** VPN connector networks
696*/
a11d8dab
VK
697CREATE TABLE vpn_connector_networks
698(
51ff26ea
AK
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)
a11d8dab
VK
704) TABLE_TYPE;
705
085ed918
AK
706COMMENT_TABLE(vpn_connector_networks, '')
707COMMENT_COLUMN(vpn_connector_networks.vpn_id, '')
708COMMENT_COLUMN(vpn_connector_networks.network_type, '')
709COMMENT_COLUMN(vpn_connector_networks.ip_addr, '')
710COMMENT_COLUMN(vpn_connector_networks.ip_netmask, '')
711
ef44d5ea
VK
712/*
713** Container objects
714*/
23464115 715CREATE TABLE object_containers
ef44d5ea 716(
51ff26ea
AK
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)
cb7ec554 722) TABLE_TYPE;
ef44d5ea 723
085ed918
AK
724COMMENT_TABLE(object_containers, '')
725COMMENT_COLUMN(object_containers.id, '')
726COMMENT_COLUMN(object_containers.object_class, '')
727COMMENT_COLUMN(object_containers.flags, '')
728COMMENT_COLUMN(object_containers.auto_bind_filter, '')
729
383b42a1
VK
730/*
731** Condition objects
732*/
383b42a1
VK
733CREATE TABLE conditions
734(
51ff26ea
AK
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)
383b42a1
VK
743) TABLE_TYPE;
744
085ed918
AK
745COMMENT_TABLE(conditions, '')
746COMMENT_COLUMN(conditions.id, '')
747COMMENT_COLUMN(conditions.activation_event, '')
748COMMENT_COLUMN(conditions.deactivation_event, '')
749COMMENT_COLUMN(conditions.source_object, '')
750COMMENT_COLUMN(conditions.active_status, '')
751COMMENT_COLUMN(conditions.inactive_status, '')
752COMMENT_COLUMN(conditions.script, '')
753
383b42a1
VK
754/*
755** DCI to condition mapping
756*/
383b42a1
VK
757CREATE TABLE cond_dci_map
758(
51ff26ea
AK
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)
383b42a1
VK
766) TABLE_TYPE;
767
085ed918
AK
768COMMENT_TABLE(cond_dci_map, '')
769COMMENT_COLUMN(cond_dci_map.condition_id, '')
770COMMENT_COLUMN(cond_dci_map.sequence_number, '')
771COMMENT_COLUMN(cond_dci_map.dci_id, '')
772COMMENT_COLUMN(cond_dci_map.node_id, '')
773COMMENT_COLUMN(cond_dci_map.dci_func, '')
774COMMENT_COLUMN(cond_dci_map.num_polls, '')
775
23464115
VK
776/**
777 * Data collection templates
778 */
62d11997
VK
779CREATE TABLE templates
780(
51ff26ea
AK
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)
cb7ec554 786) TABLE_TYPE;
62d11997 787
085ed918
AK
788COMMENT_TABLE(templates, '')
789COMMENT_COLUMN(templates.id, '')
790COMMENT_COLUMN(templates.version, '')
791COMMENT_COLUMN(templates.flags, '')
792COMMENT_COLUMN(templates.apply_filter, '')
793
23464115
VK
794/**
795 * Mapping hosts to templates
796 */
62d11997
VK
797CREATE TABLE dct_node_map
798(
51ff26ea
AK
799 template_id integer not null,
800 node_id integer not null,
801 PRIMARY KEY(template_id,node_id)
cb7ec554 802) TABLE_TYPE;
62d11997 803
085ed918
AK
804COMMENT_TABLE(dct_node_map, '')
805COMMENT_COLUMN(dct_node_map.template_id, '')
806COMMENT_COLUMN(dct_node_map.node_id, '')
807
23464115 808/**
3df8bccd 809 * Nodes to subnets mapping
23464115 810 */
a713e82e 811CREATE TABLE nsmap
cbcaf8c8 812(
51ff26ea
AK
813 subnet_id integer not null,
814 node_id integer not null,
815 PRIMARY KEY(subnet_id,node_id)
cb7ec554 816) TABLE_TYPE;
cbcaf8c8 817
085ed918
AK
818COMMENT_TABLE(nsmap, '')
819COMMENT_COLUMN(nsmap.subnet_id, '')
820COMMENT_COLUMN(nsmap.node_id, '')
821
23464115 822/**
3df8bccd 823 * Container members
23464115 824 */
ef44d5ea
VK
825CREATE TABLE container_members
826(
51ff26ea
AK
827 container_id integer not null,
828 object_id integer not null,
829 PRIMARY KEY(container_id,object_id)
cb7ec554 830) TABLE_TYPE;
ef44d5ea 831
085ed918
AK
832COMMENT_TABLE(container_members, '')
833COMMENT_COLUMN(container_members.container_id, '')
834COMMENT_COLUMN(container_members.object_id, '')
835
23464115
VK
836/**
837 * Objects' ACLs
838 */
feea53fd 839CREATE TABLE acl
f806ed94 840(
51ff26ea
AK
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)
cb7ec554 845) TABLE_TYPE;
f806ed94 846
085ed918
AK
847COMMENT_TABLE(acl, '')
848COMMENT_COLUMN(acl.object_id, '')
849COMMENT_COLUMN(acl.user_id, '')
850COMMENT_COLUMN(acl.access_rights, '')
851
23464115
VK
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 */
d51ccc0d
VK
857CREATE TABLE trusted_nodes
858(
51ff26ea
AK
859 source_object_id integer not null,
860 target_node_id integer not null,
861 PRIMARY KEY(source_object_id,target_node_id)
d51ccc0d
VK
862) TABLE_TYPE;
863
085ed918
AK
864COMMENT_TABLE(trusted_nodes, '')
865COMMENT_COLUMN(trusted_nodes.source_object_id, '')
866COMMENT_COLUMN(trusted_nodes.target_node_id, '')
867
23464115
VK
868/**
869 * Data collection items
870 *
51ff26ea 871 * If node_id != 0, it's an item bound to node, and template_id points to
23464115
VK
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 */
feea53fd 878CREATE TABLE items
cbcaf8c8 879(
51ff26ea
AK
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)
cb7ec554 911) TABLE_TYPE;
cbcaf8c8 912
b06436f4
VK
913CREATE INDEX idx_items_node_id ON items(node_id);
914
085ed918
AK
915COMMENT_TABLE(items, '')
916COMMENT_COLUMN(items.item_id, '')
917COMMENT_COLUMN(items.node_id, '')
918COMMENT_COLUMN(items.template_id, '')
919COMMENT_COLUMN(items.template_item_id, '')
920COMMENT_COLUMN(items.guid, '')
921COMMENT_COLUMN(items.name, '')
922COMMENT_COLUMN(items.description, '')
923COMMENT_COLUMN(items.flags, '')
924COMMENT_COLUMN(items.source, '')
925COMMENT_COLUMN(items.snmp_port, '')
926COMMENT_COLUMN(items.datatype, '')
927COMMENT_COLUMN(items.polling_interval, '')
928COMMENT_COLUMN(items.retention_time, '')
929COMMENT_COLUMN(items.status, '')
930COMMENT_COLUMN(items.snmp_raw_value_type, '')
931COMMENT_COLUMN(items.delta_calculation, '')
932COMMENT_COLUMN(items.transformation, '')
933COMMENT_COLUMN(items.instance, '')
934COMMENT_COLUMN(items.system_tag, '')
935COMMENT_COLUMN(items.resource_id, '')
936COMMENT_COLUMN(items.proxy_node, '')
937COMMENT_COLUMN(items.base_units, '')
938COMMENT_COLUMN(items.unit_multiplier, '')
939COMMENT_COLUMN(items.custom_units_name, '')
940COMMENT_COLUMN(items.perftab_settings, '')
941COMMENT_COLUMN(items.instd_method, '')
942COMMENT_COLUMN(items.instd_data, '')
943COMMENT_COLUMN(items.instd_filter, '')
944COMMENT_COLUMN(items.samples, '')
945COMMENT_COLUMN(items.comments, '')
946
cc8ce218
VK
947/*
948** Data collection tables
949*/
cc8ce218
VK
950CREATE TABLE dc_tables
951(
51ff26ea
AK
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)
cc8ce218
VK
972) TABLE_TYPE;
973
b06436f4
VK
974CREATE INDEX idx_dc_tables_node_id ON dc_tables(node_id);
975
085ed918
AK
976COMMENT_TABLE(dc_tables, '')
977COMMENT_COLUMN(dc_tables.item_id, '')
978COMMENT_COLUMN(dc_tables.node_id, '')
979COMMENT_COLUMN(dc_tables.template_id, '')
980COMMENT_COLUMN(dc_tables.template_item_id, '')
981COMMENT_COLUMN(dc_tables.guid, '')
982COMMENT_COLUMN(dc_tables.name, '')
983COMMENT_COLUMN(dc_tables.description, '')
984COMMENT_COLUMN(dc_tables.flags, '')
985COMMENT_COLUMN(dc_tables.source, '')
986COMMENT_COLUMN(dc_tables.snmp_port, '')
987COMMENT_COLUMN(dc_tables.polling_interval, '')
988COMMENT_COLUMN(dc_tables.retention_time, '')
989COMMENT_COLUMN(dc_tables.status, '')
990COMMENT_COLUMN(dc_tables.system_tag, '')
991COMMENT_COLUMN(dc_tables.resource_id, '')
992COMMENT_COLUMN(dc_tables.proxy_node, '')
993COMMENT_COLUMN(dc_tables.perftab_settings, '')
994COMMENT_COLUMN(dc_tables.transformation_script, '')
995COMMENT_COLUMN(dc_tables.comments, '')
996
cc8ce218
VK
997/*
998** Columns for data collection tables
999*/
cc8ce218
VK
1000CREATE TABLE dc_table_columns
1001(
51ff26ea
AK
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)
cc8ce218
VK
1009) TABLE_TYPE;
1010
085ed918
AK
1011COMMENT_TABLE(dc_table_columns, '')
1012COMMENT_COLUMN(dc_table_columns.table_id, '')
1013COMMENT_COLUMN(dc_table_columns.sequence_number, '')
1014COMMENT_COLUMN(dc_table_columns.column_name, '')
1015COMMENT_COLUMN(dc_table_columns.snmp_oid, '')
1016COMMENT_COLUMN(dc_table_columns.flags, '')
1017COMMENT_COLUMN(dc_table_columns.display_name, '')
1018
d6124fa0
VK
1019/*
1020** Column name dictionary
1021*/
d6124fa0
VK
1022CREATE TABLE dct_column_names
1023(
51ff26ea
AK
1024 column_id integer not null,
1025 column_name varchar(63) not null,
1026 PRIMARY KEY(column_id)
d6124fa0
VK
1027) TABLE_TYPE;
1028
085ed918
AK
1029COMMENT_TABLE(dct_column_names, '')
1030COMMENT_COLUMN(dct_column_names.column_id, '')
1031COMMENT_COLUMN(dct_column_names.column_name, '')
1032
9098ad59
VK
1033/*
1034** Table thresholds
1035*/
1036CREATE TABLE dct_thresholds
1037(
51ff26ea
AK
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)
9098ad59
VK
1044) TABLE_TYPE;
1045
085ed918
AK
1046COMMENT_TABLE(dct_thresholds, '')
1047COMMENT_COLUMN(dct_thresholds.id, '')
1048COMMENT_COLUMN(dct_thresholds.table_id, '')
1049COMMENT_COLUMN(dct_thresholds.sequence_number, '')
1050COMMENT_COLUMN(dct_thresholds.activation_event, '')
1051COMMENT_COLUMN(dct_thresholds.deactivation_event, '')
1052
9098ad59
VK
1053/*
1054** Table threshold conditions
1055*/
1056CREATE TABLE dct_threshold_conditions
1057(
51ff26ea
AK
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)
9098ad59
VK
1065) TABLE_TYPE;
1066
085ed918
AK
1067COMMENT_TABLE(dct_threshold_conditions, '')
1068COMMENT_COLUMN(dct_threshold_conditions.threshold_id, '')
1069COMMENT_COLUMN(dct_threshold_conditions.group_id, '')
1070COMMENT_COLUMN(dct_threshold_conditions.sequence_number, '')
1071COMMENT_COLUMN(dct_threshold_conditions.column_name, '')
1072COMMENT_COLUMN(dct_threshold_conditions.check_operation, '')
1073COMMENT_COLUMN(dct_threshold_conditions.check_value, '')
1074
b9ce1c9d
VK
1075/*
1076** Schedules for DCIs
1077*/
b9ce1c9d
VK
1078CREATE TABLE dci_schedules
1079(
51ff26ea
AK
1080 schedule_id integer not null,
1081 item_id integer not null,
1082 schedule varchar(255) null,
1083 PRIMARY KEY(item_id,schedule_id)
b9ce1c9d
VK
1084) TABLE_TYPE;
1085
085ed918
AK
1086COMMENT_TABLE(dci_schedules, '')
1087COMMENT_COLUMN(dci_schedules.schedule_id, '')
1088COMMENT_COLUMN(dci_schedules.item_id, '')
1089COMMENT_COLUMN(dci_schedules.schedule, '')
1090
57a5e132
VK
1091/*
1092** Latest raw values for all data collection items
1093*/
57a5e132
VK
1094CREATE TABLE raw_dci_values
1095(
51ff26ea
AK
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)
57a5e132
VK
1101) TABLE_TYPE;
1102
7b61f11f 1103#if !defined(DB_ORACLE) && !defined(DB_DB2) && !defined(DB_POSTGRESQL)
c13cb4a5 1104CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id);
fa3f5211 1105#endif
57a5e132 1106
085ed918
AK
1107COMMENT_TABLE(raw_dci_values, '')
1108COMMENT_COLUMN(raw_dci_values.item_id, '')
1109COMMENT_COLUMN(raw_dci_values.raw_value, '')
1110COMMENT_COLUMN(raw_dci_values.transformed_value, '')
1111COMMENT_COLUMN(raw_dci_values.last_poll_time, '')
1112
6c9e7d36
VK
1113/*
1114** Events configuration
1115*/
0c6014e4 1116CREATE TABLE event_cfg
cbcaf8c8 1117(
51ff26ea
AK
1118 event_code integer not null,
1119 event_name varchar(63) not null, // Short event name
50963ced 1120 guid varchar(36) not null,
51ff26ea
AK
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)
cb7ec554 1126) TABLE_TYPE;
cbcaf8c8 1127
50963ced
VK
1128COMMENT_TABLE(event_cfg, 'Event templates')
1129COMMENT_COLUMN(event_cfg.event_code, 'Event code (unique within system)')
1130COMMENT_COLUMN(event_cfg.event_name, 'Event name')
1131COMMENT_COLUMN(event_cfg.guid, 'Event template GUID')
1132COMMENT_COLUMN(event_cfg.severity, 'Severity')
1133COMMENT_COLUMN(event_cfg.flags, 'Flags')
1134COMMENT_COLUMN(event_cfg.message, 'Message template')
1135COMMENT_COLUMN(event_cfg.description, 'Event description')
085ed918 1136
6c9e7d36
VK
1137/*
1138** Event log
1139*/
feea53fd 1140CREATE TABLE event_log
cbcaf8c8 1141(
51ff26ea
AK
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)
cb7ec554 1152) TABLE_TYPE;
cbcaf8c8 1153
30639d32
VK
1154CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp);
1155
ed0a9e41
VK
1156CREATE INDEX idx_event_log_source ON event_log(event_source);
1157
40094b47 1158#if defined(DB_POSTGRESQL)
69bb7f47
VK
1159CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0;
1160#elif defined(DB_ORACLE)
1161CREATE OR REPLACE FUNCTION zero_to_null(id NUMBER)
1162RETURN NUMBER
1163DETERMINISTIC
1164AS BEGIN
1165 IF id > 0 THEN
1166 RETURN id;
1167 ELSE
1168 RETURN NULL;
1169 END IF;
1170END;
1171/
1172CREATE INDEX idx_event_log_root_id ON event_log(zero_to_null(root_event_id));
1173#else
1174CREATE INDEX idx_event_log_root_id ON event_log(root_event_id);
1175#endif
cbcaf8c8 1176
085ed918
AK
1177COMMENT_TABLE(event_log, '')
1178COMMENT_COLUMN(event_log.event_id, '')
1179COMMENT_COLUMN(event_log.event_code, '')
1180COMMENT_COLUMN(event_log.event_timestamp, '')
1181COMMENT_COLUMN(event_log.event_source, '')
1182COMMENT_COLUMN(event_log.dci_id, '')
1183COMMENT_COLUMN(event_log.event_severity, '')
1184COMMENT_COLUMN(event_log.event_message, '')
1185COMMENT_COLUMN(event_log.root_event_id, '')
1186COMMENT_COLUMN(event_log.user_tag, '')
1187
6c9e7d36
VK
1188/*
1189** Actions on events
1190*/
feea53fd 1191CREATE TABLE actions
cbcaf8c8 1192(
51ff26ea
AK
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)
cb7ec554 1207) TABLE_TYPE;
69cc295f 1208
085ed918
AK
1209COMMENT_TABLE(actions, '')
1210COMMENT_COLUMN(actions.action_id, '')
1211COMMENT_COLUMN(actions.action_name, '')
1212COMMENT_COLUMN(actions.action_type, '')
1213COMMENT_COLUMN(actions.is_disabled, '')
1214COMMENT_COLUMN(actions.rcpt_addr, '')
1215COMMENT_COLUMN(actions.email_subject, '')
1216COMMENT_COLUMN(actions.action_data, '')
1217
6c9e7d36
VK
1218/*
1219** Event groups
1220*/
feea53fd 1221CREATE TABLE event_groups
69cc295f 1222(
51ff26ea
AK
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)
cb7ec554 1229) TABLE_TYPE;
69cc295f 1230
085ed918
AK
1231COMMENT_TABLE(event_groups, '')
1232COMMENT_COLUMN(event_groups.id, '')
1233COMMENT_COLUMN(event_groups.name, '')
1234COMMENT_COLUMN(event_groups.description, '')
1235COMMENT_COLUMN(event_groups.range_start, '')
1236COMMENT_COLUMN(event_groups.range_end, '')
1237
6c9e7d36
VK
1238/*
1239** Event group members
1240*/
feea53fd 1241CREATE TABLE event_group_members
69cc295f 1242(
51ff26ea
AK
1243 group_id integer not null,
1244 event_code integer not null,
1245 PRIMARY KEY(group_id,event_code)
cb7ec554 1246) TABLE_TYPE;
69cc295f 1247
085ed918
AK
1248COMMENT_TABLE(event_group_members, '')
1249COMMENT_COLUMN(event_group_members.group_id, '')
1250COMMENT_COLUMN(event_group_members.event_code, '')
1251
6c9e7d36
VK
1252/*
1253** Event processing policy
1254*/
feea53fd 1255CREATE TABLE event_policy
69cc295f 1256(
51ff26ea
AK
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)
cb7ec554 1270) TABLE_TYPE;
69cc295f 1271
085ed918
AK
1272COMMENT_TABLE(event_policy, '')
1273COMMENT_COLUMN(event_policy.rule_id, '')
1274COMMENT_COLUMN(event_policy.rule_guid, '')
1275COMMENT_COLUMN(event_policy.flags, '')
1276COMMENT_COLUMN(event_policy.comments, '')
1277COMMENT_COLUMN(event_policy.script, '')
1278COMMENT_COLUMN(event_policy.alarm_message, '')
1279COMMENT_COLUMN(event_policy.alarm_severity, '')
1280COMMENT_COLUMN(event_policy.alarm_key, '')
1281COMMENT_COLUMN(event_policy.alarm_timeout, '')
1282COMMENT_COLUMN(event_policy.alarm_timeout_event, '')
1283COMMENT_COLUMN(event_policy.situation_id, '')
1284COMMENT_COLUMN(event_policy.situation_instance, '')
1285
1286/**
1287 *
1288 */
feea53fd 1289CREATE TABLE policy_source_list
69cc295f 1290(
51ff26ea
AK
1291 rule_id integer not null,
1292 object_id integer not null,
1293 PRIMARY KEY(rule_id,object_id)
cb7ec554 1294) TABLE_TYPE;
69cc295f 1295
085ed918
AK
1296COMMENT_TABLE(policy_source_list, '')
1297COMMENT_COLUMN(policy_source_list.rule_id, '')
1298COMMENT_COLUMN(policy_source_list.object_id, '')
1299
1300/**
1301 *
1302 */
feea53fd 1303CREATE TABLE policy_event_list
69cc295f 1304(
51ff26ea
AK
1305 rule_id integer not null,
1306 event_code integer not null,
1307 PRIMARY KEY(rule_id,event_code)
cb7ec554 1308) TABLE_TYPE;
b6a77d6d 1309
085ed918
AK
1310COMMENT_TABLE(policy_event_list, '')
1311COMMENT_COLUMN(policy_event_list.rule_id, '')
1312COMMENT_COLUMN(policy_event_list.event_code, '')
1313
1314/**
1315 *
1316 */
feea53fd 1317CREATE TABLE policy_action_list
b6a77d6d 1318(
51ff26ea
AK
1319 rule_id integer not null,
1320 action_id integer not null,
1321 PRIMARY KEY(rule_id,action_id)
cb7ec554 1322) TABLE_TYPE;
54f0de5b 1323
085ed918
AK
1324COMMENT_TABLE(policy_action_list, '')
1325COMMENT_COLUMN(policy_action_list.rule_id, '')
1326COMMENT_COLUMN(policy_action_list.action_id, '')
1327
085ed918
AK
1328/**
1329 *
1330 */
b13fd0b7
VK
1331CREATE TABLE policy_situation_attr_list
1332(
51ff26ea
AK
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)
b13fd0b7
VK
1338) TABLE_TYPE;
1339
085ed918
AK
1340COMMENT_TABLE(policy_situation_attr_list, '')
1341COMMENT_COLUMN(policy_situation_attr_list.rule_id, '')
1342COMMENT_COLUMN(policy_situation_attr_list.situation_id, '')
1343COMMENT_COLUMN(policy_situation_attr_list.attr_name, '')
1344COMMENT_COLUMN(policy_situation_attr_list.attr_value, '')
54f0de5b 1345
59f88625
VK
1346/*
1347** Threshold checking rules
1348*/
feea53fd 1349CREATE TABLE thresholds
59f88625 1350(
51ff26ea
AK
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)
cb7ec554 1368) TABLE_TYPE;
2fa71464 1369
c13cb4a5
AK
1370CREATE INDEX idx_thresholds_item_id ON thresholds(item_id);
1371CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number);
505ca1ae 1372
085ed918
AK
1373COMMENT_TABLE(thresholds, '')
1374COMMENT_COLUMN(thresholds.threshold_id, '')
1375COMMENT_COLUMN(thresholds.item_id, '')
1376COMMENT_COLUMN(thresholds.sequence_number, '')
1377COMMENT_COLUMN(thresholds.fire_value, '')
1378COMMENT_COLUMN(thresholds.rearm_value, '')
1379COMMENT_COLUMN(thresholds.check_function, '')
1380COMMENT_COLUMN(thresholds.check_operation, '')
1381COMMENT_COLUMN(thresholds.sample_count, '')
1382COMMENT_COLUMN(thresholds.script, '')
1383COMMENT_COLUMN(thresholds.event_code, '')
1384COMMENT_COLUMN(thresholds.rearm_event_code, '')
1385COMMENT_COLUMN(thresholds.repeat_interval, '')
1386COMMENT_COLUMN(thresholds.current_state, '')
1387COMMENT_COLUMN(thresholds.current_severity, '')
1388COMMENT_COLUMN(thresholds.match_count, '')
1389COMMENT_COLUMN(thresholds.last_event_timestamp, '')
1390
59a21a78
VK
1391/*
1392** Alarms
1393*/
59a21a78
VK
1394CREATE TABLE alarms
1395(
51ff26ea
AK
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)
cb7ec554 1418) TABLE_TYPE;
59a21a78 1419
085ed918
AK
1420COMMENT_TABLE(alarms, '')
1421COMMENT_COLUMN(alarms.alarm_id, '')
1422COMMENT_COLUMN(alarms.alarm_state, '')
1423COMMENT_COLUMN(alarms.hd_state, '')
1424COMMENT_COLUMN(alarms.hd_ref, '')
1425COMMENT_COLUMN(alarms.creation_time, '')
1426COMMENT_COLUMN(alarms.last_change_time, '')
1427COMMENT_COLUMN(alarms.source_object_id, '')
1428COMMENT_COLUMN(alarms.source_event_code, '')
1429COMMENT_COLUMN(alarms.source_event_id, '')
1430COMMENT_COLUMN(alarms.dci_id, '')
1431COMMENT_COLUMN(alarms.message, '')
1432COMMENT_COLUMN(alarms.original_severity, '')
1433COMMENT_COLUMN(alarms.current_severity, '')
1434COMMENT_COLUMN(alarms.repeat_count, '')
1435COMMENT_COLUMN(alarms.alarm_key, '')
1436COMMENT_COLUMN(alarms.ack_by, '')
1437COMMENT_COLUMN(alarms.resolved_by, '')
1438COMMENT_COLUMN(alarms.term_by, '')
1439COMMENT_COLUMN(alarms.timeout, '')
1440COMMENT_COLUMN(alarms.timeout_event, '')
1441COMMENT_COLUMN(alarms.ack_timeout, '')
1442
59a21a78
VK
1443/*
1444** Alarm notes
1445*/
59a21a78
VK
1446CREATE TABLE alarm_notes
1447(
51ff26ea
AK
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)
cb7ec554 1454) TABLE_TYPE;
917aa2e6 1455
c5131765
VK
1456CREATE INDEX idx_alarm_notes_alarm_id ON alarm_notes(alarm_id);
1457
085ed918
AK
1458COMMENT_TABLE(alarm_notes, '')
1459COMMENT_COLUMN(alarm_notes.note_id, '')
1460COMMENT_COLUMN(alarm_notes.alarm_id, '')
1461COMMENT_COLUMN(alarm_notes.change_time, '')
1462COMMENT_COLUMN(alarm_notes.user_id, '')
1463COMMENT_COLUMN(alarm_notes.note_text, '')
1464
b1e9b6b3
VK
1465/*
1466** Source events for alarms
1467*/
b1e9b6b3
VK
1468CREATE TABLE alarm_events
1469(
51ff26ea
AK
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)
b1e9b6b3
VK
1479) TABLE_TYPE;
1480
1481CREATE INDEX idx_alarm_events_alarm_id ON alarm_events(alarm_id);
1482
085ed918
AK
1483COMMENT_TABLE(alarm_events, '')
1484COMMENT_COLUMN(alarm_events.alarm_id, '')
1485COMMENT_COLUMN(alarm_events.event_id, '')
1486COMMENT_COLUMN(alarm_events.event_code, '')
1487COMMENT_COLUMN(alarm_events.event_name, '')
1488COMMENT_COLUMN(alarm_events.severity, '')
1489COMMENT_COLUMN(alarm_events.source_object_id, '')
1490COMMENT_COLUMN(alarm_events.event_timestamp, '')
1491COMMENT_COLUMN(alarm_events.message, '')
b1e9b6b3 1492
3aeed82c
VK
1493/*
1494** SNMP trap configuration
1495*/
1496
1497CREATE TABLE snmp_trap_cfg
1498(
51ff26ea
AK
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)
cb7ec554 1505) TABLE_TYPE;
3aeed82c 1506
085ed918
AK
1507COMMENT_TABLE(snmp_trap_cfg, '')
1508COMMENT_COLUMN(snmp_trap_cfg.trap_id, '')
1509COMMENT_COLUMN(snmp_trap_cfg.snmp_oid, '')
1510COMMENT_COLUMN(snmp_trap_cfg.event_code, '')
1511COMMENT_COLUMN(snmp_trap_cfg.user_tag, '')
1512COMMENT_COLUMN(snmp_trap_cfg.description, '')
3aeed82c
VK
1513
1514/*
1515** SNMP trap parameters mapping
1516*/
3aeed82c
VK
1517CREATE TABLE snmp_trap_pmap
1518(
51ff26ea
AK
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)
cb7ec554 1525) TABLE_TYPE;
6726bfdc 1526
085ed918
AK
1527COMMENT_TABLE(snmp_trap_pmap, '')
1528COMMENT_COLUMN(snmp_trap_pmap.trap_id, '')
1529COMMENT_COLUMN(snmp_trap_pmap.parameter, '')
1530COMMENT_COLUMN(snmp_trap_pmap.flags, '')
1531COMMENT_COLUMN(snmp_trap_pmap.snmp_oid, '')
1532COMMENT_COLUMN(snmp_trap_pmap.description, '')
1533
6726bfdc
VK
1534/*
1535** Agent packages
1536*/
6726bfdc
VK
1537CREATE TABLE agent_pkg
1538(
51ff26ea
AK
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)
cb7ec554 1546) TABLE_TYPE;
b7a391d7 1547
085ed918
AK
1548COMMENT_TABLE(agent_pkg, '')
1549COMMENT_COLUMN(agent_pkg.pkg_id, '')
1550COMMENT_COLUMN(agent_pkg.pkg_name, '')
1551COMMENT_COLUMN(agent_pkg.version, '')
1552COMMENT_COLUMN(agent_pkg.platform, '')
1553COMMENT_COLUMN(agent_pkg.pkg_file, '')
1554COMMENT_COLUMN(agent_pkg.description, '')
1555
b7a391d7
VK
1556/*
1557** Object tools
1558*/
b7a391d7
VK
1559CREATE TABLE object_tools
1560(
51ff26ea
AK
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)
b7a391d7
VK
1574) TABLE_TYPE;
1575
085ed918
AK
1576COMMENT_TABLE(object_tools, '')
1577COMMENT_COLUMN(object_tools.tool_id, '')
1578COMMENT_COLUMN(object_tools.guid, '')
1579COMMENT_COLUMN(object_tools.tool_name, '')
1580COMMENT_COLUMN(object_tools.tool_type, '')
1581COMMENT_COLUMN(object_tools.tool_data, '')
1582COMMENT_COLUMN(object_tools.description, '')
1583COMMENT_COLUMN(object_tools.flags, '')
1584COMMENT_COLUMN(object_tools.tool_filter, '')
1585COMMENT_COLUMN(object_tools.confirmation_text, '')
1586COMMENT_COLUMN(object_tools.command_name, '')
1587COMMENT_COLUMN(object_tools.command_short_name, '')
1588COMMENT_COLUMN(object_tools.icon, '')
1589
b7a391d7
VK
1590/*
1591** Access list for object tools
1592*/
b7a391d7
VK
1593CREATE TABLE object_tools_acl
1594(
51ff26ea
AK
1595 tool_id integer not null,
1596 user_id integer not null,
1597 PRIMARY KEY(tool_id,user_id)
b7a391d7 1598) TABLE_TYPE;
02108d74 1599
085ed918
AK
1600COMMENT_TABLE(object_tools_acl, '')
1601COMMENT_COLUMN(object_tools_acl.tool_id, '')
1602COMMENT_COLUMN(object_tools_acl.user_id, '')
1603
02108d74 1604/*
461b4074 1605** Configuration of columns for SNMP_TABLE and AGENT_TABLE type of object tools
02108d74 1606*/
461b4074 1607CREATE TABLE object_tools_table_columns
02108d74 1608(
51ff26ea
AK
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)
02108d74 1616) TABLE_TYPE;
46ef501a 1617
085ed918
AK
1618COMMENT_TABLE(object_tools_table_columns, '')
1619COMMENT_COLUMN(object_tools_table_columns.tool_id, '')
1620COMMENT_COLUMN(object_tools_table_columns.col_number, '')
1621COMMENT_COLUMN(object_tools_table_columns.col_name, '')
1622COMMENT_COLUMN(object_tools_table_columns.col_oid, '')
1623COMMENT_COLUMN(object_tools_table_columns.col_format, '')
1624COMMENT_COLUMN(object_tools_table_columns.col_substr, '')
1625
b576249a
VK
1626/**
1627 * Input fields for object tools
1628 */
1629CREATE TABLE object_tools_input_fields
1630(
51ff26ea
AK
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)
b576249a
VK
1638) TABLE_TYPE;
1639
085ed918
AK
1640COMMENT_TABLE(object_tools_input_fields, '')
1641COMMENT_COLUMN(object_tools_input_fields.tool_id, '')
1642COMMENT_COLUMN(object_tools_input_fields.name, '')
1643COMMENT_COLUMN(object_tools_input_fields.input_type, '')
1644COMMENT_COLUMN(object_tools_input_fields.display_name, '')
1645COMMENT_COLUMN(object_tools_input_fields.sequence_num, '')
1646COMMENT_COLUMN(object_tools_input_fields.config, '')
1647
c4096dd8
VK
1648/*
1649** Stored syslog messages
1650*/
c4096dd8
VK
1651CREATE TABLE syslog
1652(
51ff26ea
AK
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)
c4096dd8
VK
1662) TABLE_TYPE;
1663
30639d32
VK
1664CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp);
1665
085ed918
AK
1666COMMENT_TABLE(syslog, '')
1667COMMENT_COLUMN(syslog.msg_id, '')
1668COMMENT_COLUMN(syslog.msg_timestamp, '')
1669COMMENT_COLUMN(syslog.facility, '')
1670COMMENT_COLUMN(syslog.severity, '')
1671COMMENT_COLUMN(syslog.source_object_id, '')
1672COMMENT_COLUMN(syslog.hostname, '')
1673COMMENT_COLUMN(syslog.msg_tag, '')
1674COMMENT_COLUMN(syslog.msg_text, '')
c4096dd8 1675
5c6b881b
VK
1676/*
1677** Script library
1678*/
5c6b881b
VK
1679CREATE TABLE script_library
1680(
51ff26ea
AK
1681 script_id integer not null,
1682 script_name varchar(255) not null,
1683 script_code SQL_TEXT null,
1684 PRIMARY KEY(script_id)
5c6b881b
VK
1685) TABLE_TYPE;
1686
085ed918
AK
1687COMMENT_TABLE(script_library, '')
1688COMMENT_COLUMN(script_library.script_id, '')
1689COMMENT_COLUMN(script_library.script_name, '')
1690COMMENT_COLUMN(script_library.script_code, '')
5c6b881b 1691
56b32598
VK
1692/*
1693** Extended SNMP trap log
1694*/
1695
1696CREATE TABLE snmp_trap_log
1697(
51ff26ea
AK
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)
56b32598
VK
1705) TABLE_TYPE;
1706
bf4a58d4 1707CREATE INDEX idx_snmp_trap_log_tt ON snmp_trap_log(trap_timestamp);
30639d32 1708
085ed918
AK
1709COMMENT_TABLE(snmp_trap_log, '')
1710COMMENT_COLUMN(snmp_trap_log.trap_id, '')
1711COMMENT_COLUMN(snmp_trap_log.trap_timestamp, '')
1712COMMENT_COLUMN(snmp_trap_log.ip_addr, '')
1713COMMENT_COLUMN(snmp_trap_log.object_id, '')
1714COMMENT_COLUMN(snmp_trap_log.trap_oid, '')
1715COMMENT_COLUMN(snmp_trap_log.trap_varlist, '')
1716
d2d7327c
VK
1717/*
1718** Agent configurations
1719*/
d2d7327c
VK
1720CREATE TABLE agent_configs
1721(
51ff26ea
AK
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)
d2d7327c 1728) TABLE_TYPE;
e2ce7f94 1729
085ed918
AK
1730COMMENT_TABLE(agent_configs, '')
1731COMMENT_COLUMN(agent_configs.config_id, '')
1732COMMENT_COLUMN(agent_configs.config_name, '')
1733COMMENT_COLUMN(agent_configs.config_file, '')
1734COMMENT_COLUMN(agent_configs.config_filter, '')
1735COMMENT_COLUMN(agent_configs.sequence_number, '')
1736
e2ce7f94
VK
1737/*
1738** Address lists
1739*/
e2ce7f94
VK
1740CREATE TABLE address_lists
1741(
51ff26ea
AK
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)
e2ce7f94
VK
1748) TABLE_TYPE;
1749
1750CREATE INDEX idx_address_lists_list_type ON address_lists(list_type);
381ed729 1751
085ed918
AK
1752COMMENT_TABLE(address_lists, '')
1753COMMENT_COLUMN(address_lists.list_type, '')
1754COMMENT_COLUMN(address_lists.community_id, '')
1755COMMENT_COLUMN(address_lists.addr_type, '')
1756COMMENT_COLUMN(address_lists.addr1, '')
1757COMMENT_COLUMN(address_lists.addr2, '')
1758
381ed729
VK
1759/*
1760** Graphs
1761*/
381ed729
VK
1762CREATE TABLE graphs
1763(
51ff26ea
AK
1764 graph_id integer not null,
1765 owner_id integer not null,
1766 flags integer not null,
1767 name varchar(255) not null,
17b6b6a4
VK
1768 config SQL_TEXT null,
1769 filters SQL_TEXT null,
51ff26ea 1770 PRIMARY KEY(graph_id)
381ed729
VK
1771) TABLE_TYPE;
1772
085ed918
AK
1773COMMENT_TABLE(graphs, '')
1774COMMENT_COLUMN(graphs.graph_id, '')
1775COMMENT_COLUMN(graphs.owner_id, '')
1776COMMENT_COLUMN(graphs.flags, '')
1777COMMENT_COLUMN(graphs.name, '')
1778COMMENT_COLUMN(graphs.config, '')
1779COMMENT_COLUMN(graphs.filters, '')
1780
381ed729
VK
1781/*
1782** Graph access lists
1783*/
381ed729
VK
1784CREATE TABLE graph_acl
1785(
51ff26ea
AK
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)
381ed729 1790) TABLE_TYPE;
3d1058ed 1791
085ed918
AK
1792COMMENT_TABLE(graph_acl, '')
1793COMMENT_COLUMN(graph_acl.graph_id, '')
1794COMMENT_COLUMN(graph_acl.user_id, '')
1795COMMENT_COLUMN(graph_acl.user_rights, '')
3d1058ed
VK
1796
1797/*
1798** Certificates
1799*/
1800
1801CREATE TABLE certificates
1802(
51ff26ea
AK
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)
3d1058ed 1809) TABLE_TYPE;
fa585702 1810
085ed918
AK
1811COMMENT_TABLE(certificates, '')
1812COMMENT_COLUMN(certificates.cert_id, '')
1813COMMENT_COLUMN(certificates.cert_type, '')
1814COMMENT_COLUMN(certificates.cert_data, '')
1815COMMENT_COLUMN(certificates.subject, '')
1816COMMENT_COLUMN(certificates.comments, '')
1817
889841e1
VK
1818/*
1819** Audit log
1820*/
889841e1
VK
1821CREATE TABLE audit_log
1822(
51ff26ea
AK
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)
889841e1 1833) TABLE_TYPE;
4ee86f55 1834
085ed918
AK
1835COMMENT_TABLE(audit_log, '')
1836COMMENT_COLUMN(audit_log.record_id, '')
1837COMMENT_COLUMN(audit_log.timestamp, '')
1838COMMENT_COLUMN(audit_log.subsystem, '')
1839COMMENT_COLUMN(audit_log.success, '')
1840COMMENT_COLUMN(audit_log.user_id, '')
1841COMMENT_COLUMN(audit_log.workstation, '')
1842COMMENT_COLUMN(audit_log.session_id, '')
1843COMMENT_COLUMN(audit_log.object_id, '')
1844COMMENT_COLUMN(audit_log.message, '')
1845
4ee86f55
VK
1846/*
1847** Situations
1848*/
4ee86f55
VK
1849CREATE TABLE situations
1850(
51ff26ea
AK
1851 id integer not null,
1852 name varchar(127) not null,
1853 comments SQL_TEXT not null,
1854 PRIMARY KEY(id)
4ee86f55
VK
1855) TABLE_TYPE;
1856
085ed918
AK
1857COMMENT_TABLE(situations, '')
1858COMMENT_COLUMN(situations.id, '')
1859COMMENT_COLUMN(situations.name, '')
1860COMMENT_COLUMN(situations.comments, '')
4ee86f55 1861
023779b8
VK
1862/*
1863** List of possible community strings
1864*/
023779b8
VK
1865CREATE TABLE snmp_communities
1866(
51ff26ea
AK
1867 id integer not null,
1868 community varchar(255) null,
1869 PRIMARY KEY(id)
023779b8 1870) TABLE_TYPE;
a1236e96 1871
085ed918
AK
1872COMMENT_TABLE(snmp_communities, '')
1873COMMENT_COLUMN(snmp_communities.id, '')
1874COMMENT_COLUMN(snmp_communities.community, '')
a1236e96 1875
f2bb4aa1
VK
1876/*
1877** Agent policies - common attributes
1878*/
f2bb4aa1
VK
1879CREATE TABLE ap_common
1880(
51ff26ea
AK
1881 id integer not null,
1882 policy_type integer not null,
1883 version integer not null,
1884 PRIMARY KEY(id)
f2bb4aa1
VK
1885) TABLE_TYPE;
1886
3df8bccd
Z
1887COMMENT_TABLE(ap_common, 'Agent policies common attributes')
1888COMMENT_COLUMN(ap_common.id, 'Unique policy identifier')
1889COMMENT_COLUMN(ap_common.policy_type, 'Policy type')
1890COMMENT_COLUMN(ap_common.version, 'Policy version')
085ed918 1891
f2bb4aa1
VK
1892/*
1893** Agent policies - node binding
1894*/
f2bb4aa1
VK
1895CREATE TABLE ap_bindings
1896(
51ff26ea
AK
1897 policy_id integer not null,
1898 node_id integer not null,
1899 PRIMARY KEY(policy_id,node_id)
f2bb4aa1
VK
1900) TABLE_TYPE;
1901
3df8bccd
Z
1902COMMENT_TABLE(ap_bindings, 'Agent policies to node binding')
1903COMMENT_COLUMN(ap_bindings.policy_id, 'Unique policy identifier')
1904COMMENT_COLUMN(ap_bindings.node_id, 'Unique node identifier')
085ed918 1905
f2bb4aa1
VK
1906/*
1907** Agent policies - configuration files
1908*/
f2bb4aa1
VK
1909CREATE TABLE ap_config_files
1910(
51ff26ea
AK
1911 policy_id integer not null,
1912 file_content SQL_TEXT null,
1913 PRIMARY KEY(policy_id)
f2bb4aa1 1914) TABLE_TYPE;
df8a4ca2 1915
3df8bccd
Z
1916COMMENT_TABLE(ap_config_files, 'Agent policies configuration files')
1917COMMENT_COLUMN(ap_config_files.policy_id, 'Unique policy identifier')
1918COMMENT_COLUMN(ap_config_files.file_content, 'Policy file content')
1919
1920/**
1921** Agent policies - log parser files
1922*/
1923CREATE 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
1930COMMENT_TABLE(ap_log_parser, 'Agent policies log parser files')
1931COMMENT_COLUMN(ap_log_parser.policy_id, 'Unique policy identifier')
1932COMMENT_COLUMN(ap_log_parser.file_content, 'Log parser policy file content')
085ed918 1933
df8a4ca2
VK
1934/*
1935** Default SNMP v3 USM credentials
1936*/
df8a4ca2
VK
1937CREATE TABLE usm_credentials
1938(
51ff26ea
AK
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)
df8a4ca2 1946) TABLE_TYPE;
021dcda7 1947
3df8bccd
Z
1948COMMENT_TABLE(usm_credentials, 'Default SNMP v3 USM credentials')
1949COMMENT_COLUMN(usm_credentials.id, 'SNMP v3 USM credential unique identifier')
1950COMMENT_COLUMN(usm_credentials.user_name, 'SNMP v3 USM username')
1951COMMENT_COLUMN(usm_credentials.auth_method, 'SNMP v3 USM authentication method')
1952COMMENT_COLUMN(usm_credentials.priv_method, 'SNMP v3 USM encryption method')
1953COMMENT_COLUMN(usm_credentials.auth_password, 'SNMP v3 USM authentication password')
1954COMMENT_COLUMN(usm_credentials.priv_password, 'SNMP v3 USM encryption password')
085ed918 1955
021dcda7
VK
1956/*
1957** Network maps
1958*/
1959CREATE TABLE network_maps
1960(
51ff26ea
AK
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)
021dcda7
VK
1977) TABLE_TYPE;
1978
085ed918
AK
1979COMMENT_TABLE(network_maps, '')
1980COMMENT_COLUMN(network_maps.id, '')
1981COMMENT_COLUMN(network_maps.map_type, '')
1982COMMENT_COLUMN(network_maps.layout, '')
1983COMMENT_COLUMN(network_maps.seed, '')
1984COMMENT_COLUMN(network_maps.radius, '')
1985COMMENT_COLUMN(network_maps.background, '')
1986COMMENT_COLUMN(network_maps.bg_latitude, '')
1987COMMENT_COLUMN(network_maps.bg_longitude, '')
1988COMMENT_COLUMN(network_maps.bg_zoom, '')
1989COMMENT_COLUMN(network_maps.flags, '')
1990COMMENT_COLUMN(network_maps.bg_color, '')
1991COMMENT_COLUMN(network_maps.link_color, '')
1992COMMENT_COLUMN(network_maps.link_routing, '')
1993COMMENT_COLUMN(network_maps.object_display_mode, '')
1994COMMENT_COLUMN(network_maps.filter, '')
1995
8bdd26dc
VK
1996/**
1997 * Elements of network maps
1998 */
021dcda7
VK
1999CREATE TABLE network_map_elements
2000(
51ff26ea
AK
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)
021dcda7 2007) TABLE_TYPE;
cce8f214 2008
085ed918
AK
2009COMMENT_TABLE(network_map_elements, '')
2010COMMENT_COLUMN(network_map_elements.map_id, '')
2011COMMENT_COLUMN(network_map_elements.element_id, '')
2012COMMENT_COLUMN(network_map_elements.element_type, '')
2013COMMENT_COLUMN(network_map_elements.element_data, '')
2014COMMENT_COLUMN(network_map_elements.flags, '')
2015
8bdd26dc
VK
2016/**
2017 * Links on network maps
2018 */
cce8f214
VK
2019CREATE TABLE network_map_links
2020(
51ff26ea
AK
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
cce8f214 2030) TABLE_TYPE;
e6b9439a 2031
173c189d
VK
2032CREATE INDEX idx_network_map_links_map_id ON network_map_links(map_id);
2033
085ed918
AK
2034COMMENT_TABLE(network_map_links, '')
2035COMMENT_COLUMN(network_map_links.map_id, '')
2036COMMENT_COLUMN(network_map_links.element1, '')
2037COMMENT_COLUMN(network_map_links.element2, '')
2038COMMENT_COLUMN(network_map_links.link_type, '')
2039COMMENT_COLUMN(network_map_links.link_name, '')
2040COMMENT_COLUMN(network_map_links.connector_name1, '')
2041COMMENT_COLUMN(network_map_links.connector_name2, '')
2042COMMENT_COLUMN(network_map_links.element_data, '')
2043COMMENT_COLUMN(network_map_links.flags, '')
2044
8bdd26dc
VK
2045/**
2046 * Image Library
2047 */
e6b9439a
AK
2048CREATE TABLE images
2049(
51ff26ea
AK
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)
e6b9439a 2057) TABLE_TYPE;
926e8ce7 2058
085ed918
AK
2059COMMENT_TABLE(images, '')
2060COMMENT_COLUMN(images.guid, '')
2061COMMENT_COLUMN(images.name, '')
2062COMMENT_COLUMN(images.category, '')
2063COMMENT_COLUMN(images.mimetype, '')
2064COMMENT_COLUMN(images.protected, '')
2065
8bdd26dc
VK
2066/**
2067 * Dashboards
2068 */
926e8ce7
VK
2069CREATE TABLE dashboards
2070(
51ff26ea
AK
2071 id integer not null,
2072 num_columns integer not null,
2073 options integer not null,
2074 PRIMARY KEY(id)
926e8ce7
VK
2075) TABLE_TYPE;
2076
085ed918
AK
2077COMMENT_TABLE(dashboards, '')
2078COMMENT_COLUMN(dashboards.id, '')
2079COMMENT_COLUMN(dashboards.num_columns, '')
2080COMMENT_COLUMN(dashboards.options, '')
2081
8bdd26dc
VK
2082/**
2083 * Dashboard elements
2084 */
926e8ce7
VK
2085CREATE TABLE dashboard_elements
2086(
51ff26ea
AK
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)
926e8ce7 2093) TABLE_TYPE;
abf24277 2094
085ed918
AK
2095COMMENT_TABLE(dashboard_elements, '')
2096COMMENT_COLUMN(dashboard_elements.dashboard_id, '')
2097COMMENT_COLUMN(dashboard_elements.element_id, '')
2098COMMENT_COLUMN(dashboard_elements.element_type, '')
2099COMMENT_COLUMN(dashboard_elements.element_data, '')
2100COMMENT_COLUMN(dashboard_elements.layout_data, '')
2101
8bdd26dc
VK
2102/**
2103 * Associations between dashboards and other objects
2104 */
2105CREATE TABLE dashboard_associations
2106(
51ff26ea
AK
2107 object_id integer not null,
2108 dashboard_id integer not null,
2109 PRIMARY KEY(object_id,dashboard_id)
8bdd26dc
VK
2110) TABLE_TYPE;
2111
085ed918
AK
2112COMMENT_TABLE(dashboard_associations, '')
2113COMMENT_COLUMN(dashboard_associations.object_id, '')
2114COMMENT_COLUMN(dashboard_associations.dashboard_id, '')
2115
8bdd26dc
VK
2116/**
2117 * SLA business services
2118 */
abf24277
VK
2119CREATE TABLE business_services
2120(
51ff26ea
AK
2121 service_id integer not null,
2122 PRIMARY KEY(service_id)
abf24277
VK
2123) TABLE_TYPE;
2124
085ed918
AK
2125COMMENT_TABLE(business_services, '')
2126COMMENT_COLUMN(business_services.service_id, '')
2127
8bdd26dc
VK
2128/**
2129 * SLA service checks
2130 */
abf24277
VK
2131CREATE TABLE slm_checks
2132(
51ff26ea
AK
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)
abf24277
VK
2142) TABLE_TYPE;
2143
085ed918
AK
2144COMMENT_TABLE(slm_checks, '')
2145COMMENT_COLUMN(slm_checks.id, '')
2146COMMENT_COLUMN(slm_checks.type, '')
2147COMMENT_COLUMN(slm_checks.content, '')
2148COMMENT_COLUMN(slm_checks.threshold_id, '')
2149COMMENT_COLUMN(slm_checks.reason, '')
2150COMMENT_COLUMN(slm_checks.is_template, '')
2151COMMENT_COLUMN(slm_checks.template_id, '')
2152COMMENT_COLUMN(slm_checks.current_ticket, '')
2153
abf24277
VK
2154/*
2155** SLA node links
2156*/
abf24277
VK
2157CREATE TABLE node_links
2158(
51ff26ea
AK
2159 nodelink_id integer not null,
2160 node_id integer not null, // refers to node.id
2161 PRIMARY KEY(nodelink_id)
abf24277
VK
2162) TABLE_TYPE;
2163
085ed918
AK
2164COMMENT_TABLE(node_links, '')
2165COMMENT_COLUMN(node_links.nodelink_id, '')
2166COMMENT_COLUMN(node_links.node_id, '')
2167
abf24277
VK
2168/*
2169** SLA - agreements
2170*/
abf24277
VK
2171CREATE TABLE slm_agreements
2172(
51ff26ea
AK
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)
abf24277
VK
2181) TABLE_TYPE;
2182
085ed918
AK
2183COMMENT_TABLE(slm_agreements, '')
2184COMMENT_COLUMN(slm_agreements.agreement_id, '')
2185COMMENT_COLUMN(slm_agreements.service_id, '')
2186COMMENT_COLUMN(slm_agreements.org_id, '')
2187COMMENT_COLUMN(slm_agreements.uptime, '')
2188COMMENT_COLUMN(slm_agreements.period, '')
2189COMMENT_COLUMN(slm_agreements.start_date, '')
2190COMMENT_COLUMN(slm_agreements.notes, '')
abf24277
VK
2191
2192/*
2193** SLA tickets
2194*/
2195
2196CREATE TABLE slm_tickets
2197(
51ff26ea
AK
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)
abf24277
VK
2205) TABLE_TYPE;
2206
085ed918
AK
2207COMMENT_TABLE(slm_tickets, '')
2208COMMENT_COLUMN(slm_tickets.ticket_id, '')
2209COMMENT_COLUMN(slm_tickets.service_id, '')
2210COMMENT_COLUMN(slm_tickets.check_id, '')
2211COMMENT_COLUMN(slm_tickets.create_timestamp, '')
2212COMMENT_COLUMN(slm_tickets.close_timestamp, '')
2213COMMENT_COLUMN(slm_tickets.reason, '')
abf24277 2214
dfe3b3e7
AK
2215/*
2216** SLA service change log
2217*/
2218
2219CREATE TABLE slm_service_history
2220(
51ff26ea
AK
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)
dfe3b3e7
AK
2226) TABLE_TYPE;
2227
085ed918
AK
2228COMMENT_TABLE(slm_service_history, '')
2229COMMENT_COLUMN(slm_service_history.record_id, '')
2230COMMENT_COLUMN(slm_service_history.service_id, '')
2231COMMENT_COLUMN(slm_service_history.change_timestamp, '')
2232COMMENT_COLUMN(slm_service_history.new_status, '')
2233
dfe3b3e7 2234
abf24277
VK
2235/*
2236** Organizations
2237*/
2238
2239CREATE TABLE organizations
2240(
51ff26ea
AK
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)
abf24277
VK
2248) TABLE_TYPE;
2249
085ed918
AK
2250COMMENT_TABLE(organizations, '')
2251COMMENT_COLUMN(organizations.id, '')
2252COMMENT_COLUMN(organizations.parent_id, '')
2253COMMENT_COLUMN(organizations.org_type, '')
2254COMMENT_COLUMN(organizations.name, '')
2255COMMENT_COLUMN(organizations.description, '')
2256COMMENT_COLUMN(organizations.manager, '')
abf24277
VK
2257
2258/*
2259** Persons
2260*/
2261
2262CREATE TABLE persons
2263(
51ff26ea
AK
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)
abf24277 2271) TABLE_TYPE;
878b4261 2272
085ed918
AK
2273COMMENT_TABLE(persons, '')
2274COMMENT_COLUMN(persons.id, '')
2275COMMENT_COLUMN(persons.org_id, '')
2276COMMENT_COLUMN(persons.first_name, '')
2277COMMENT_COLUMN(persons.last_name, '')
2278COMMENT_COLUMN(persons.title, '')
2279COMMENT_COLUMN(persons.status, '')
2280
878b4261
VK
2281/*
2282** Job history
2283*/
878b4261
VK
2284CREATE TABLE job_history
2285(
51ff26ea
AK
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)
878b4261 2298) TABLE_TYPE;
845b8121 2299
085ed918
AK
2300COMMENT_TABLE(job_history, '')
2301COMMENT_COLUMN(job_history.id, '')
2302COMMENT_COLUMN(job_history.time_created, '')
2303COMMENT_COLUMN(job_history.time_started, '')
2304COMMENT_COLUMN(job_history.time_finished, '')
2305COMMENT_COLUMN(job_history.job_type, '')
2306COMMENT_COLUMN(job_history.description, '')
2307COMMENT_COLUMN(job_history.additional_info, '')
2308COMMENT_COLUMN(job_history.node_id, '')
2309COMMENT_COLUMN(job_history.user_id, '')
2310COMMENT_COLUMN(job_history.status, '')
2311COMMENT_COLUMN(job_history.failure_message, '')
2312
ae2a3458
VK
2313/*
2314** License repository
2315*/
ae2a3458
VK
2316CREATE TABLE licenses
2317(
51ff26ea
AK
2318 id integer not null,
2319 content SQL_TEXT null,
2320 PRIMARY KEY(id)
ae2a3458 2321) TABLE_TYPE;
69bb7f47 2322
085ed918
AK
2323COMMENT_TABLE(licenses, '')
2324COMMENT_COLUMN(licenses.id, '')
2325COMMENT_COLUMN(licenses.content, '')
2326
69bb7f47
VK
2327/*
2328** Mapping tables
2329*/
69bb7f47
VK
2330CREATE TABLE mapping_tables
2331(
51ff26ea
AK
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)
69bb7f47
VK
2337) TABLE_TYPE;
2338
085ed918
AK
2339COMMENT_TABLE(mapping_tables, '')
2340COMMENT_COLUMN(mapping_tables.id, '')
2341COMMENT_COLUMN(mapping_tables.name, '')
2342COMMENT_COLUMN(mapping_tables.flags, '')
2343COMMENT_COLUMN(mapping_tables.description, '')
2344
69bb7f47
VK
2345/*
2346** Mapping tables content
2347*/
69bb7f47
VK
2348CREATE TABLE mapping_data
2349(
51ff26ea
AK
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)
69bb7f47 2355) TABLE_TYPE;
b4c2a628 2356
085ed918
AK
2357COMMENT_TABLE(mapping_data, '')
2358COMMENT_COLUMN(mapping_data.table_id, '')
2359COMMENT_COLUMN(mapping_data.md_key, '')
2360COMMENT_COLUMN(mapping_data.md_value, '')
2361COMMENT_COLUMN(mapping_data.description, '')
2362
b4c2a628
VK
2363/*
2364** DCI summary tables
2365*/
b4c2a628
VK
2366CREATE TABLE dci_summary_tables
2367(
51ff26ea
AK
2368 id integer not null,
2369 guid varchar(36) not null,
73bbcd77 2370 menu_path varchar(255) null,
51ff26ea
AK
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)
b4c2a628 2376) TABLE_TYPE;
0a145c10 2377
085ed918
AK
2378COMMENT_TABLE(dci_summary_tables, '')
2379COMMENT_COLUMN(dci_summary_tables.id, '')
2380COMMENT_COLUMN(dci_summary_tables.guid, '')
2381COMMENT_COLUMN(dci_summary_tables.menu_path, '')
2382COMMENT_COLUMN(dci_summary_tables.title, '')
2383COMMENT_COLUMN(dci_summary_tables.node_filter, '')
2384COMMENT_COLUMN(dci_summary_tables.flags, '')
2385COMMENT_COLUMN(dci_summary_tables.columns, '')
2386
0a145c10 2387/*
c6e191d2 2388** Scheduled tasks
0a145c10 2389*/
c6e191d2 2390CREATE TABLE scheduled_tasks
0a145c10 2391(
51ff26ea
AK
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)
0a145c10 2402) TABLE_TYPE;
f61f151c 2403
085ed918
AK
2404COMMENT_TABLE(scheduled_tasks, '')
2405COMMENT_COLUMN(scheduled_tasks.id, '')
2406COMMENT_COLUMN(scheduled_tasks.taskId, '')
2407COMMENT_COLUMN(scheduled_tasks.schedule, '')
2408COMMENT_COLUMN(scheduled_tasks.params, '')
2409COMMENT_COLUMN(scheduled_tasks.execution_time, '')
2410COMMENT_COLUMN(scheduled_tasks.last_execution_time, '')
2411COMMENT_COLUMN(scheduled_tasks.flags, '')
2412COMMENT_COLUMN(scheduled_tasks.owner, '')
2413COMMENT_COLUMN(scheduled_tasks.object_id, '')
2414
f61f151c
AK
2415/*
2416** ZMQ Subscriptions
2417*/
2418CREATE TABLE zmq_subscription
2419(
51ff26ea
AK
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)
f61f151c 2425) TABLE_TYPE;
085ed918
AK
2426
2427COMMENT_TABLE(zmq_subscription, '')
2428COMMENT_COLUMN(zmq_subscription.object_id, '')
2429COMMENT_COLUMN(zmq_subscription.subscription_type, '')
2430COMMENT_COLUMN(zmq_subscription.ignore_items, '')
2431COMMENT_COLUMN(zmq_subscription.items, '')
0de31ec3
VK
2432
2433/*
2434** Currency codes
2435*/
2436CREATE TABLE currency_codes
2437(
2438 numeric_code char(3) not null,
2439 alpha_code char(3) not null,
a27a303e 2440 description varchar(127) not null,
0de31ec3
VK
2441 exponent integer not null,
2442 PRIMARY KEY(numeric_code)
2443) TABLE_TYPE;
2444
2445COMMENT_TABLE(currency_codes, 'List of currency codes')
2446COMMENT_COLUMN(currency_codes.numeric_code, 'Numeric ISO code')
2447COMMENT_COLUMN(currency_codes.alpha_code, 'Alphabetical ISO code')
2448COMMENT_COLUMN(currency_codes.description, 'Description')
2449COMMENT_COLUMN(currency_codes.exponent, 'Exponent for minor units')
2450
2451/*
2452** Country codes
2453*/
2454CREATE 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,
a27a303e 2459 name varchar(127) not null,
0de31ec3
VK
2460 PRIMARY KEY(numeric_code)
2461) TABLE_TYPE;
2462
2463COMMENT_TABLE(country_codes, 'List of country codes')
2464COMMENT_COLUMN(country_codes.numeric_code, 'Numeric ISO code')
2465COMMENT_COLUMN(country_codes.alpha_code, 'Alphabetical 2 character ISO code')
2466COMMENT_COLUMN(country_codes.alpha3_code, 'Alphabetical 3 character ISO code')
2467COMMENT_COLUMN(country_codes.name, 'Country name')
8e6e8ef1
VK
2468
2469/*
2470** Config repositories
2471*/
2472CREATE 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;