removed unused database tables (issue #1186)
[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, '')
117COMMENT_COLUMN(users.ldap_dn, '')
9b057805 118
6c9e7d36
VK
119/*
120** User groups
121*/
f806ed94 122
feea53fd 123CREATE TABLE user_groups
f806ed94 124(
51ff26ea
AK
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,
bad2c02e 132 ldap_unique_id varchar(63) null,
51ff26ea 133 PRIMARY KEY(id)
cb7ec554 134) TABLE_TYPE;
f806ed94 135
938a6733 136COMMENT_TABLE(user_groups, 'System user groups')
085ed918
AK
137COMMENT_COLUMN(user_groups.id, '')
138COMMENT_COLUMN(user_groups.guid, '')
139COMMENT_COLUMN(user_groups.name, '')
140COMMENT_COLUMN(user_groups.system_access, '')
141COMMENT_COLUMN(user_groups.flags, '')
142COMMENT_COLUMN(user_groups.description, '')
143COMMENT_COLUMN(user_groups.ldap_dn, '')
f806ed94 144
6c9e7d36
VK
145/*
146** Users to groups mapping
147*/
f806ed94 148
feea53fd 149CREATE TABLE user_group_members
f806ed94 150(
51ff26ea
AK
151 group_id integer not null,
152 user_id integer not null,
153 PRIMARY KEY(group_id,user_id)
cb7ec554 154) TABLE_TYPE;
f806ed94 155
938a6733 156COMMENT_TABLE(user_group_members, 'Users and group relationships')
085ed918
AK
157COMMENT_COLUMN(user_group_members.group_id, '')
158COMMENT_COLUMN(user_group_members.user_id, '')
f806ed94 159
b4fbaa08
VK
160/*
161** User profiles
162*/
163
164CREATE TABLE user_profiles
165(
51ff26ea
AK
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)
b4fbaa08
VK
170) TABLE_TYPE;
171
938a6733 172COMMENT_TABLE(user_profiles, 'User profiles')
085ed918
AK
173COMMENT_COLUMN(user_profiles.user_id, '')
174COMMENT_COLUMN(user_profiles.var_name, '')
175COMMENT_COLUMN(user_profiles.var_value, '')
b4fbaa08 176
c45e0213
VK
177/*
178** Custom attributes for user database objects
179*/
180
181CREATE TABLE userdb_custom_attributes
182(
51ff26ea
AK
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)
c45e0213
VK
187) TABLE_TYPE;
188
085ed918
AK
189COMMENT_TABLE(userdb_custom_attributes, '')
190COMMENT_COLUMN(userdb_custom_attributes.object_id, '')
191COMMENT_COLUMN(userdb_custom_attributes.attr_name, '')
192COMMENT_COLUMN(userdb_custom_attributes.attr_value, '')
c45e0213 193
b4fbaa08
VK
194/*
195** Common object properties
196*/
197
198CREATE TABLE object_properties
199(
51ff26ea
AK
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)
b4fbaa08
VK
230) TABLE_TYPE;
231
085ed918
AK
232COMMENT_TABLE(object_properties, '')
233COMMENT_COLUMN(object_properties.object_id, '')
234COMMENT_COLUMN(object_properties.guid, '')
235COMMENT_COLUMN(object_properties.name, '')
236COMMENT_COLUMN(object_properties.status, '')
237COMMENT_COLUMN(object_properties.is_deleted, '')
238COMMENT_COLUMN(object_properties.is_system, '')
239COMMENT_COLUMN(object_properties.last_modified, '')
240COMMENT_COLUMN(object_properties.inherit_access_rights, '')
241COMMENT_COLUMN(object_properties.status_calc_alg, '')
242COMMENT_COLUMN(object_properties.status_prop_alg, '')
243COMMENT_COLUMN(object_properties.status_fixed_val, '')
244COMMENT_COLUMN(object_properties.status_shift, '')
245COMMENT_COLUMN(object_properties.status_translation, '')
246COMMENT_COLUMN(object_properties.status_single_threshold, '')
247COMMENT_COLUMN(object_properties.status_thresholds, '')
248COMMENT_COLUMN(object_properties.comments, '')
249COMMENT_COLUMN(object_properties.location_type, '')
250COMMENT_COLUMN(object_properties.latitude, '')
251COMMENT_COLUMN(object_properties.longitude, '')
252COMMENT_COLUMN(object_properties.location_accuracy, '')
253COMMENT_COLUMN(object_properties.location_timestamp, '')
254COMMENT_COLUMN(object_properties.image, '')
255COMMENT_COLUMN(object_properties.submap_id, '')
256COMMENT_COLUMN(object_properties.country, '')
257COMMENT_COLUMN(object_properties.city, '')
258COMMENT_COLUMN(object_properties.street_address, '')
259COMMENT_COLUMN(object_properties.postcode, '')
260COMMENT_COLUMN(object_properties.maint_mode, '')
261COMMENT_COLUMN(object_properties.maint_event_id, '')
b4fbaa08 262
a1236e96
VK
263/*
264** Object custom attributes
265*/
a1236e96
VK
266CREATE TABLE object_custom_attributes
267(
51ff26ea
AK
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)
a1236e96
VK
272) TABLE_TYPE;
273
ff392ecf
VK
274CREATE INDEX idx_ocattr_oid ON object_custom_attributes(object_id);
275
085ed918
AK
276COMMENT_TABLE(object_custom_attributes, '')
277COMMENT_COLUMN(object_custom_attributes.object_id, '')
278COMMENT_COLUMN(object_custom_attributes.attr_name, '')
279COMMENT_COLUMN(object_custom_attributes.attr_value, '')
280
9587eba3 281/*
9aa67910 282** Zone objects
9587eba3 283*/
9587eba3
VK
284CREATE TABLE zones
285(
51ff26ea
AK
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)
9587eba3
VK
292) TABLE_TYPE;
293
085ed918
AK
294COMMENT_TABLE(zones, '')
295COMMENT_COLUMN(zones.id, '')
296COMMENT_COLUMN(zones.zone_guid, '')
297COMMENT_COLUMN(zones.agent_proxy, '')
298COMMENT_COLUMN(zones.snmp_proxy, '')
299COMMENT_COLUMN(zones.icmp_proxy, '')
300
9aa67910
VK
301/*
302** Mobile device objects
303*/
9aa67910
VK
304CREATE TABLE mobile_devices
305(
51ff26ea
AK
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)
9aa67910
VK
316) TABLE_TYPE;
317
085ed918
AK
318COMMENT_TABLE(mobile_devices, '')
319COMMENT_COLUMN(mobile_devices.id, '')
320COMMENT_COLUMN(mobile_devices.device_id, '')
321COMMENT_COLUMN(mobile_devices.vendor, '')
322COMMENT_COLUMN(mobile_devices.model, '')
323COMMENT_COLUMN(mobile_devices.serial_number, '')
324COMMENT_COLUMN(mobile_devices.os_name, '')
325COMMENT_COLUMN(mobile_devices.os_version, '')
326COMMENT_COLUMN(mobile_devices.user_id, '')
327COMMENT_COLUMN(mobile_devices.battery_level, '')
328
8715a84c
VK
329/*
330** Access point objects
331*/
8715a84c
VK
332CREATE TABLE access_points
333(
51ff26ea
AK
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)
8715a84c
VK
343) TABLE_TYPE;
344
085ed918
AK
345COMMENT_TABLE(access_points, '')
346COMMENT_COLUMN(access_points.id, '')
347COMMENT_COLUMN(access_points.node_id, '')
348COMMENT_COLUMN(access_points.mac_address, '')
349COMMENT_COLUMN(access_points.vendor, '')
350COMMENT_COLUMN(access_points.model, '')
351COMMENT_COLUMN(access_points.serial_number, '')
352COMMENT_COLUMN(access_points.ap_state, '')
353COMMENT_COLUMN(access_points.ap_index, '')
354
8715a84c
VK
355/*
356** Rack objects
357*/
8715a84c
VK
358CREATE TABLE racks
359(
51ff26ea
AK
360 id integer not null,
361 height integer not null,
362 top_bottom_num char(1) not null,
363 PRIMARY KEY(id)
8715a84c
VK
364) TABLE_TYPE;
365
085ed918
AK
366COMMENT_TABLE(racks, '')
367COMMENT_COLUMN(racks.id, '')
368COMMENT_COLUMN(racks.height, '')
369COMMENT_COLUMN(racks.top_bottom_num, '')
370
6c9e7d36
VK
371/*
372** Nodes information
373*/
feea53fd 374CREATE TABLE nodes
cbcaf8c8 375(
51ff26ea
AK
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)
cb7ec554 416) TABLE_TYPE;
9e6c6d05 417
085ed918
AK
418COMMENT_TABLE(nodes, '')
419COMMENT_COLUMN(nodes.id, '')
420COMMENT_COLUMN(nodes.primary_name, '')
421COMMENT_COLUMN(nodes.primary_ip, '')
422COMMENT_COLUMN(nodes.node_flags, '')
423COMMENT_COLUMN(nodes.runtime_flags, '')
424COMMENT_COLUMN(nodes.snmp_version, '')
425COMMENT_COLUMN(nodes.snmp_port, '')
426COMMENT_COLUMN(nodes.community, '')
427COMMENT_COLUMN(nodes.usm_auth_password, '')
428COMMENT_COLUMN(nodes.usm_priv_password, '')
429COMMENT_COLUMN(nodes.usm_methods, '')
430COMMENT_COLUMN(nodes.snmp_oid, '')
431COMMENT_COLUMN(nodes.auth_method, '')
432COMMENT_COLUMN(nodes.secret, '')
433COMMENT_COLUMN(nodes.agent_port, '')
434COMMENT_COLUMN(nodes.status_poll_type, '')
435COMMENT_COLUMN(nodes.agent_version, '')
436COMMENT_COLUMN(nodes.platform_name, '')
437COMMENT_COLUMN(nodes.poller_node_id, '')
438COMMENT_COLUMN(nodes.zone_guid, '')
439COMMENT_COLUMN(nodes.proxy_node, '')
440COMMENT_COLUMN(nodes.snmp_proxy, '')
441COMMENT_COLUMN(nodes.icmp_proxy, '')
442COMMENT_COLUMN(nodes.required_polls, '')
443COMMENT_COLUMN(nodes.uname, '')
444COMMENT_COLUMN(nodes.use_ifxtable, '')
445COMMENT_COLUMN(nodes.snmp_sys_name, '')
446COMMENT_COLUMN(nodes.snmp_sys_contact, '')
447COMMENT_COLUMN(nodes.snmp_sys_location, '')
448COMMENT_COLUMN(nodes.bridge_base_addr, '')
449COMMENT_COLUMN(nodes.down_since, '')
450COMMENT_COLUMN(nodes.boot_time, '')
451COMMENT_COLUMN(nodes.driver_name, '')
452COMMENT_COLUMN(nodes.rack_image, '')
453COMMENT_COLUMN(nodes.rack_position, '')
454COMMENT_COLUMN(nodes.rack_height, '')
455COMMENT_COLUMN(nodes.rack_id, '')
456COMMENT_COLUMN(nodes.agent_cache_mode, '')
457COMMENT_COLUMN(nodes.last_agent_comm_time, '')
9e6c6d05 458
97e09882
VK
459/*
460** Clusters information
461*/
462
375e0736 463CREATE TABLE clusters
97e09882 464(
51ff26ea
AK
465 id integer not null,
466 cluster_type integer not null,
467 zone_guid integer not null,
468 PRIMARY KEY(id)
375e0736 469) TABLE_TYPE;
97e09882 470
085ed918
AK
471COMMENT_TABLE(clusters, '')
472COMMENT_COLUMN(clusters.id, '')
473COMMENT_COLUMN(clusters.cluster_type, '')
474COMMENT_COLUMN(clusters.zone_guid, '')
97e09882
VK
475
476/*
477** Cluster members
478*/
479
375e0736 480CREATE TABLE cluster_members
97e09882 481(
51ff26ea
AK
482 cluster_id integer not null,
483 node_id integer not null,
484 PRIMARY KEY(cluster_id,node_id)
375e0736 485) TABLE_TYPE;
97e09882 486
085ed918
AK
487COMMENT_TABLE(cluster_members, '')
488COMMENT_COLUMN(cluster_members.cluster_id, '')
489COMMENT_COLUMN(cluster_members.node_id, '')
97e09882 490
a14a4916
VK
491/*
492** Cluster interconnect subnets
493*/
494
375e0736 495CREATE TABLE cluster_sync_subnets
a14a4916 496(
51ff26ea
AK
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)
375e0736 501) TABLE_TYPE;
a14a4916 502
085ed918
AK
503COMMENT_TABLE(cluster_sync_subnets, '')
504COMMENT_COLUMN(cluster_sync_subnets.cluster_id, '')
505COMMENT_COLUMN(cluster_sync_subnets.subnet_addr, '')
506COMMENT_COLUMN(cluster_sync_subnets.subnet_mask, '')
a14a4916 507
6f512367
VK
508/*
509** Cluster resources
510*/
511
512CREATE TABLE cluster_resources
513(
51ff26ea
AK
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)
6f512367
VK
520) TABLE_TYPE;
521
085ed918
AK
522COMMENT_TABLE(cluster_resources, '')
523COMMENT_COLUMN(cluster_resources.cluster_id, '')
524COMMENT_COLUMN(cluster_resources.resource_id, '')
525COMMENT_COLUMN(cluster_resources.resource_name, '')
526COMMENT_COLUMN(cluster_resources.ip_addr, '')
527COMMENT_COLUMN(cluster_resources.current_owner, '')
528
6c9e7d36
VK
529/*
530** Subnets
531*/
feea53fd 532CREATE TABLE subnets
cbcaf8c8 533(
51ff26ea
AK
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)
cb7ec554 540) TABLE_TYPE;
cbcaf8c8 541
085ed918
AK
542COMMENT_TABLE(subnets, '')
543
6c9e7d36
VK
544/*
545** Nodes' interfaces
546*/
feea53fd 547CREATE TABLE interfaces
cbcaf8c8 548(
51ff26ea
AK
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)
cb7ec554 572) TABLE_TYPE;
cbcaf8c8 573
085ed918
AK
574COMMENT_TABLE(interfaces, '')
575COMMENT_COLUMN(interfaces.id, '')
576COMMENT_COLUMN(interfaces.node_id, '')
577COMMENT_COLUMN(interfaces.flags, '')
578COMMENT_COLUMN(interfaces.if_type, '')
579COMMENT_COLUMN(interfaces.if_index, '')
580COMMENT_COLUMN(interfaces.mtu, '')
581COMMENT_COLUMN(interfaces.speed, '')
582COMMENT_COLUMN(interfaces.bridge_port, '')
583COMMENT_COLUMN(interfaces.phy_slot, '')
584COMMENT_COLUMN(interfaces.phy_port, '')
585COMMENT_COLUMN(interfaces.peer_node_id, '')
586COMMENT_COLUMN(interfaces.peer_if_id, '')
587COMMENT_COLUMN(interfaces.peer_proto, '')
588COMMENT_COLUMN(interfaces.mac_addr, '')
589COMMENT_COLUMN(interfaces.required_polls, '')
590COMMENT_COLUMN(interfaces.admin_state, '')
591COMMENT_COLUMN(interfaces.oper_state, '')
592COMMENT_COLUMN(interfaces.dot1x_pae_state, '')
593COMMENT_COLUMN(interfaces.dot1x_backend_state, '')
594COMMENT_COLUMN(interfaces.description, '')
595COMMENT_COLUMN(interfaces.alias, '')
596COMMENT_COLUMN(interfaces.iftable_suffix, '')
597
c30c0c0f
VK
598/*
599** Interface IP addresses
600*/
601CREATE TABLE interface_address_list
602(
51ff26ea
AK
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)
c30c0c0f 607) TABLE_TYPE;
cbcaf8c8 608
085ed918
AK
609COMMENT_TABLE(interface_address_list, '')
610COMMENT_COLUMN(interface_address_list.iface_id, '')
611COMMENT_COLUMN(interface_address_list.ip_addr, '')
612COMMENT_COLUMN(interface_address_list.ip_netmask, '')
613
bebf4833
VK
614/*
615** Network services
616*/
617
618CREATE TABLE network_services
619(
51ff26ea
AK
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)
bebf4833
VK
631) TABLE_TYPE;
632
085ed918
AK
633COMMENT_TABLE(network_services, '')
634COMMENT_COLUMN(network_services.id, '')
635COMMENT_COLUMN(network_services.node_id, '')
636COMMENT_COLUMN(network_services.service_type, '')
637COMMENT_COLUMN(network_services.ip_bind_addr, '')
638COMMENT_COLUMN(network_services.ip_proto, '')
639COMMENT_COLUMN(network_services.ip_port, '')
640COMMENT_COLUMN(network_services.check_request, '')
641COMMENT_COLUMN(network_services.check_responce, '')
642COMMENT_COLUMN(network_services.poller_node_id, '')
643COMMENT_COLUMN(network_services.required_polls, '')
644
a11d8dab
VK
645/*
646** VPN connectors
647*/
a11d8dab
VK
648CREATE TABLE vpn_connectors
649(
51ff26ea
AK
650 id integer not null,
651 node_id integer not null,
652 peer_gateway integer not null,
653 PRIMARY KEY(id)
a11d8dab
VK
654) TABLE_TYPE;
655
085ed918
AK
656COMMENT_TABLE(vpn_connectors, '')
657COMMENT_COLUMN(vpn_connectors.id, '')
658COMMENT_COLUMN(vpn_connectors.node_id, '')
659COMMENT_COLUMN(vpn_connectors.peer_gateway, '')
660
a11d8dab
VK
661/*
662** VPN connector networks
663*/
a11d8dab
VK
664CREATE TABLE vpn_connector_networks
665(
51ff26ea
AK
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)
a11d8dab
VK
671) TABLE_TYPE;
672
085ed918
AK
673COMMENT_TABLE(vpn_connector_networks, '')
674COMMENT_COLUMN(vpn_connector_networks.vpn_id, '')
675COMMENT_COLUMN(vpn_connector_networks.network_type, '')
676COMMENT_COLUMN(vpn_connector_networks.ip_addr, '')
677COMMENT_COLUMN(vpn_connector_networks.ip_netmask, '')
678
ef44d5ea
VK
679/*
680** Container objects
681*/
23464115 682CREATE TABLE object_containers
ef44d5ea 683(
51ff26ea
AK
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)
cb7ec554 689) TABLE_TYPE;
ef44d5ea 690
085ed918
AK
691COMMENT_TABLE(object_containers, '')
692COMMENT_COLUMN(object_containers.id, '')
693COMMENT_COLUMN(object_containers.object_class, '')
694COMMENT_COLUMN(object_containers.flags, '')
695COMMENT_COLUMN(object_containers.auto_bind_filter, '')
696
383b42a1
VK
697/*
698** Condition objects
699*/
383b42a1
VK
700CREATE TABLE conditions
701(
51ff26ea
AK
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)
383b42a1
VK
710) TABLE_TYPE;
711
085ed918
AK
712COMMENT_TABLE(conditions, '')
713COMMENT_COLUMN(conditions.id, '')
714COMMENT_COLUMN(conditions.activation_event, '')
715COMMENT_COLUMN(conditions.deactivation_event, '')
716COMMENT_COLUMN(conditions.source_object, '')
717COMMENT_COLUMN(conditions.active_status, '')
718COMMENT_COLUMN(conditions.inactive_status, '')
719COMMENT_COLUMN(conditions.script, '')
720
383b42a1
VK
721/*
722** DCI to condition mapping
723*/
383b42a1
VK
724CREATE TABLE cond_dci_map
725(
51ff26ea
AK
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)
383b42a1
VK
733) TABLE_TYPE;
734
085ed918
AK
735COMMENT_TABLE(cond_dci_map, '')
736COMMENT_COLUMN(cond_dci_map.condition_id, '')
737COMMENT_COLUMN(cond_dci_map.sequence_number, '')
738COMMENT_COLUMN(cond_dci_map.dci_id, '')
739COMMENT_COLUMN(cond_dci_map.node_id, '')
740COMMENT_COLUMN(cond_dci_map.dci_func, '')
741COMMENT_COLUMN(cond_dci_map.num_polls, '')
742
23464115
VK
743/**
744 * Data collection templates
745 */
62d11997
VK
746CREATE TABLE templates
747(
51ff26ea
AK
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)
cb7ec554 753) TABLE_TYPE;
62d11997 754
085ed918
AK
755COMMENT_TABLE(templates, '')
756COMMENT_COLUMN(templates.id, '')
757COMMENT_COLUMN(templates.version, '')
758COMMENT_COLUMN(templates.flags, '')
759COMMENT_COLUMN(templates.apply_filter, '')
760
23464115
VK
761/**
762 * Mapping hosts to templates
763 */
62d11997
VK
764CREATE TABLE dct_node_map
765(
51ff26ea
AK
766 template_id integer not null,
767 node_id integer not null,
768 PRIMARY KEY(template_id,node_id)
cb7ec554 769) TABLE_TYPE;
62d11997 770
085ed918
AK
771COMMENT_TABLE(dct_node_map, '')
772COMMENT_COLUMN(dct_node_map.template_id, '')
773COMMENT_COLUMN(dct_node_map.node_id, '')
774
23464115 775/**
3df8bccd 776 * Nodes to subnets mapping
23464115 777 */
a713e82e 778CREATE TABLE nsmap
cbcaf8c8 779(
51ff26ea
AK
780 subnet_id integer not null,
781 node_id integer not null,
782 PRIMARY KEY(subnet_id,node_id)
cb7ec554 783) TABLE_TYPE;
cbcaf8c8 784
085ed918
AK
785COMMENT_TABLE(nsmap, '')
786COMMENT_COLUMN(nsmap.subnet_id, '')
787COMMENT_COLUMN(nsmap.node_id, '')
788
23464115 789/**
3df8bccd 790 * Container members
23464115 791 */
ef44d5ea
VK
792CREATE TABLE container_members
793(
51ff26ea
AK
794 container_id integer not null,
795 object_id integer not null,
796 PRIMARY KEY(container_id,object_id)
cb7ec554 797) TABLE_TYPE;
ef44d5ea 798
085ed918
AK
799COMMENT_TABLE(container_members, '')
800COMMENT_COLUMN(container_members.container_id, '')
801COMMENT_COLUMN(container_members.object_id, '')
802
23464115
VK
803/**
804 * Objects' ACLs
805 */
feea53fd 806CREATE TABLE acl
f806ed94 807(
51ff26ea
AK
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)
cb7ec554 812) TABLE_TYPE;
f806ed94 813
085ed918
AK
814COMMENT_TABLE(acl, '')
815COMMENT_COLUMN(acl.object_id, '')
816COMMENT_COLUMN(acl.user_id, '')
817COMMENT_COLUMN(acl.access_rights, '')
818
23464115
VK
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 */
d51ccc0d
VK
824CREATE TABLE trusted_nodes
825(
51ff26ea
AK
826 source_object_id integer not null,
827 target_node_id integer not null,
828 PRIMARY KEY(source_object_id,target_node_id)
d51ccc0d
VK
829) TABLE_TYPE;
830
085ed918
AK
831COMMENT_TABLE(trusted_nodes, '')
832COMMENT_COLUMN(trusted_nodes.source_object_id, '')
833COMMENT_COLUMN(trusted_nodes.target_node_id, '')
834
23464115
VK
835/**
836 * Data collection items
837 *
51ff26ea 838 * If node_id != 0, it's an item bound to node, and template_id points to
23464115
VK
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 */
feea53fd 845CREATE TABLE items
cbcaf8c8 846(
51ff26ea
AK
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)
cb7ec554 878) TABLE_TYPE;
cbcaf8c8 879
b06436f4
VK
880CREATE INDEX idx_items_node_id ON items(node_id);
881
085ed918
AK
882COMMENT_TABLE(items, '')
883COMMENT_COLUMN(items.item_id, '')
884COMMENT_COLUMN(items.node_id, '')
885COMMENT_COLUMN(items.template_id, '')
886COMMENT_COLUMN(items.template_item_id, '')
887COMMENT_COLUMN(items.guid, '')
888COMMENT_COLUMN(items.name, '')
889COMMENT_COLUMN(items.description, '')
890COMMENT_COLUMN(items.flags, '')
891COMMENT_COLUMN(items.source, '')
892COMMENT_COLUMN(items.snmp_port, '')
893COMMENT_COLUMN(items.datatype, '')
894COMMENT_COLUMN(items.polling_interval, '')
895COMMENT_COLUMN(items.retention_time, '')
896COMMENT_COLUMN(items.status, '')
897COMMENT_COLUMN(items.snmp_raw_value_type, '')
898COMMENT_COLUMN(items.delta_calculation, '')
899COMMENT_COLUMN(items.transformation, '')
900COMMENT_COLUMN(items.instance, '')
901COMMENT_COLUMN(items.system_tag, '')
902COMMENT_COLUMN(items.resource_id, '')
903COMMENT_COLUMN(items.proxy_node, '')
904COMMENT_COLUMN(items.base_units, '')
905COMMENT_COLUMN(items.unit_multiplier, '')
906COMMENT_COLUMN(items.custom_units_name, '')
907COMMENT_COLUMN(items.perftab_settings, '')
908COMMENT_COLUMN(items.instd_method, '')
909COMMENT_COLUMN(items.instd_data, '')
910COMMENT_COLUMN(items.instd_filter, '')
911COMMENT_COLUMN(items.samples, '')
912COMMENT_COLUMN(items.comments, '')
913
cc8ce218
VK
914/*
915** Data collection tables
916*/
cc8ce218
VK
917CREATE TABLE dc_tables
918(
51ff26ea
AK
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)
cc8ce218
VK
939) TABLE_TYPE;
940
b06436f4
VK
941CREATE INDEX idx_dc_tables_node_id ON dc_tables(node_id);
942
085ed918
AK
943COMMENT_TABLE(dc_tables, '')
944COMMENT_COLUMN(dc_tables.item_id, '')
945COMMENT_COLUMN(dc_tables.node_id, '')
946COMMENT_COLUMN(dc_tables.template_id, '')
947COMMENT_COLUMN(dc_tables.template_item_id, '')
948COMMENT_COLUMN(dc_tables.guid, '')
949COMMENT_COLUMN(dc_tables.name, '')
950COMMENT_COLUMN(dc_tables.description, '')
951COMMENT_COLUMN(dc_tables.flags, '')
952COMMENT_COLUMN(dc_tables.source, '')
953COMMENT_COLUMN(dc_tables.snmp_port, '')
954COMMENT_COLUMN(dc_tables.polling_interval, '')
955COMMENT_COLUMN(dc_tables.retention_time, '')
956COMMENT_COLUMN(dc_tables.status, '')
957COMMENT_COLUMN(dc_tables.system_tag, '')
958COMMENT_COLUMN(dc_tables.resource_id, '')
959COMMENT_COLUMN(dc_tables.proxy_node, '')
960COMMENT_COLUMN(dc_tables.perftab_settings, '')
961COMMENT_COLUMN(dc_tables.transformation_script, '')
962COMMENT_COLUMN(dc_tables.comments, '')
963
cc8ce218
VK
964/*
965** Columns for data collection tables
966*/
cc8ce218
VK
967CREATE TABLE dc_table_columns
968(
51ff26ea
AK
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)
cc8ce218
VK
976) TABLE_TYPE;
977
085ed918
AK
978COMMENT_TABLE(dc_table_columns, '')
979COMMENT_COLUMN(dc_table_columns.table_id, '')
980COMMENT_COLUMN(dc_table_columns.sequence_number, '')
981COMMENT_COLUMN(dc_table_columns.column_name, '')
982COMMENT_COLUMN(dc_table_columns.snmp_oid, '')
983COMMENT_COLUMN(dc_table_columns.flags, '')
984COMMENT_COLUMN(dc_table_columns.display_name, '')
985
d6124fa0
VK
986/*
987** Column name dictionary
988*/
d6124fa0
VK
989CREATE TABLE dct_column_names
990(
51ff26ea
AK
991 column_id integer not null,
992 column_name varchar(63) not null,
993 PRIMARY KEY(column_id)
d6124fa0
VK
994) TABLE_TYPE;
995
085ed918
AK
996COMMENT_TABLE(dct_column_names, '')
997COMMENT_COLUMN(dct_column_names.column_id, '')
998COMMENT_COLUMN(dct_column_names.column_name, '')
999
9098ad59
VK
1000/*
1001** Table thresholds
1002*/
1003CREATE TABLE dct_thresholds
1004(
51ff26ea
AK
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)
9098ad59
VK
1011) TABLE_TYPE;
1012
085ed918
AK
1013COMMENT_TABLE(dct_thresholds, '')
1014COMMENT_COLUMN(dct_thresholds.id, '')
1015COMMENT_COLUMN(dct_thresholds.table_id, '')
1016COMMENT_COLUMN(dct_thresholds.sequence_number, '')
1017COMMENT_COLUMN(dct_thresholds.activation_event, '')
1018COMMENT_COLUMN(dct_thresholds.deactivation_event, '')
1019
9098ad59
VK
1020/*
1021** Table threshold conditions
1022*/
1023CREATE TABLE dct_threshold_conditions
1024(
51ff26ea
AK
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)
9098ad59
VK
1032) TABLE_TYPE;
1033
085ed918
AK
1034COMMENT_TABLE(dct_threshold_conditions, '')
1035COMMENT_COLUMN(dct_threshold_conditions.threshold_id, '')
1036COMMENT_COLUMN(dct_threshold_conditions.group_id, '')
1037COMMENT_COLUMN(dct_threshold_conditions.sequence_number, '')
1038COMMENT_COLUMN(dct_threshold_conditions.column_name, '')
1039COMMENT_COLUMN(dct_threshold_conditions.check_operation, '')
1040COMMENT_COLUMN(dct_threshold_conditions.check_value, '')
1041
b9ce1c9d
VK
1042/*
1043** Schedules for DCIs
1044*/
b9ce1c9d
VK
1045CREATE TABLE dci_schedules
1046(
51ff26ea
AK
1047 schedule_id integer not null,
1048 item_id integer not null,
1049 schedule varchar(255) null,
1050 PRIMARY KEY(item_id,schedule_id)
b9ce1c9d
VK
1051) TABLE_TYPE;
1052
085ed918
AK
1053COMMENT_TABLE(dci_schedules, '')
1054COMMENT_COLUMN(dci_schedules.schedule_id, '')
1055COMMENT_COLUMN(dci_schedules.item_id, '')
1056COMMENT_COLUMN(dci_schedules.schedule, '')
1057
57a5e132
VK
1058/*
1059** Latest raw values for all data collection items
1060*/
57a5e132
VK
1061CREATE TABLE raw_dci_values
1062(
51ff26ea
AK
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)
57a5e132
VK
1068) TABLE_TYPE;
1069
7b61f11f 1070#if !defined(DB_ORACLE) && !defined(DB_DB2) && !defined(DB_POSTGRESQL)
c13cb4a5 1071CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id);
fa3f5211 1072#endif
57a5e132 1073
085ed918
AK
1074COMMENT_TABLE(raw_dci_values, '')
1075COMMENT_COLUMN(raw_dci_values.item_id, '')
1076COMMENT_COLUMN(raw_dci_values.raw_value, '')
1077COMMENT_COLUMN(raw_dci_values.transformed_value, '')
1078COMMENT_COLUMN(raw_dci_values.last_poll_time, '')
1079
6c9e7d36
VK
1080/*
1081** Events configuration
1082*/
0c6014e4 1083CREATE TABLE event_cfg
cbcaf8c8 1084(
51ff26ea
AK
1085 event_code integer not null,
1086 event_name varchar(63) not null, // Short event name
50963ced 1087 guid varchar(36) not null,
51ff26ea
AK
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)
cb7ec554 1093) TABLE_TYPE;
cbcaf8c8 1094
50963ced
VK
1095COMMENT_TABLE(event_cfg, 'Event templates')
1096COMMENT_COLUMN(event_cfg.event_code, 'Event code (unique within system)')
1097COMMENT_COLUMN(event_cfg.event_name, 'Event name')
1098COMMENT_COLUMN(event_cfg.guid, 'Event template GUID')
1099COMMENT_COLUMN(event_cfg.severity, 'Severity')
1100COMMENT_COLUMN(event_cfg.flags, 'Flags')
1101COMMENT_COLUMN(event_cfg.message, 'Message template')
1102COMMENT_COLUMN(event_cfg.description, 'Event description')
085ed918 1103
6c9e7d36
VK
1104/*
1105** Event log
1106*/
feea53fd 1107CREATE TABLE event_log
cbcaf8c8 1108(
51ff26ea
AK
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)
cb7ec554 1119) TABLE_TYPE;
cbcaf8c8 1120
30639d32
VK
1121CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp);
1122
ed0a9e41
VK
1123CREATE INDEX idx_event_log_source ON event_log(event_source);
1124
40094b47 1125#if defined(DB_POSTGRESQL)
69bb7f47
VK
1126CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0;
1127#elif defined(DB_ORACLE)
1128CREATE OR REPLACE FUNCTION zero_to_null(id NUMBER)
1129RETURN NUMBER
1130DETERMINISTIC
1131AS BEGIN
1132 IF id > 0 THEN
1133 RETURN id;
1134 ELSE
1135 RETURN NULL;
1136 END IF;
1137END;
1138/
1139CREATE INDEX idx_event_log_root_id ON event_log(zero_to_null(root_event_id));
1140#else
1141CREATE INDEX idx_event_log_root_id ON event_log(root_event_id);
1142#endif
cbcaf8c8 1143
085ed918
AK
1144COMMENT_TABLE(event_log, '')
1145COMMENT_COLUMN(event_log.event_id, '')
1146COMMENT_COLUMN(event_log.event_code, '')
1147COMMENT_COLUMN(event_log.event_timestamp, '')
1148COMMENT_COLUMN(event_log.event_source, '')
1149COMMENT_COLUMN(event_log.dci_id, '')
1150COMMENT_COLUMN(event_log.event_severity, '')
1151COMMENT_COLUMN(event_log.event_message, '')
1152COMMENT_COLUMN(event_log.root_event_id, '')
1153COMMENT_COLUMN(event_log.user_tag, '')
1154
6c9e7d36
VK
1155/*
1156** Actions on events
1157*/
feea53fd 1158CREATE TABLE actions
cbcaf8c8 1159(
51ff26ea
AK
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)
cb7ec554 1174) TABLE_TYPE;
69cc295f 1175
085ed918
AK
1176COMMENT_TABLE(actions, '')
1177COMMENT_COLUMN(actions.action_id, '')
1178COMMENT_COLUMN(actions.action_name, '')
1179COMMENT_COLUMN(actions.action_type, '')
1180COMMENT_COLUMN(actions.is_disabled, '')
1181COMMENT_COLUMN(actions.rcpt_addr, '')
1182COMMENT_COLUMN(actions.email_subject, '')
1183COMMENT_COLUMN(actions.action_data, '')
1184
6c9e7d36
VK
1185/*
1186** Event groups
1187*/
feea53fd 1188CREATE TABLE event_groups
69cc295f 1189(
51ff26ea
AK
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)
cb7ec554 1196) TABLE_TYPE;
69cc295f 1197
085ed918
AK
1198COMMENT_TABLE(event_groups, '')
1199COMMENT_COLUMN(event_groups.id, '')
1200COMMENT_COLUMN(event_groups.name, '')
1201COMMENT_COLUMN(event_groups.description, '')
1202COMMENT_COLUMN(event_groups.range_start, '')
1203COMMENT_COLUMN(event_groups.range_end, '')
1204
6c9e7d36
VK
1205/*
1206** Event group members
1207*/
feea53fd 1208CREATE TABLE event_group_members
69cc295f 1209(
51ff26ea
AK
1210 group_id integer not null,
1211 event_code integer not null,
1212 PRIMARY KEY(group_id,event_code)
cb7ec554 1213) TABLE_TYPE;
69cc295f 1214
085ed918
AK
1215COMMENT_TABLE(event_group_members, '')
1216COMMENT_COLUMN(event_group_members.group_id, '')
1217COMMENT_COLUMN(event_group_members.event_code, '')
1218
6c9e7d36
VK
1219/*
1220** Event processing policy
1221*/
feea53fd 1222CREATE TABLE event_policy
69cc295f 1223(
51ff26ea
AK
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)
cb7ec554 1237) TABLE_TYPE;
69cc295f 1238
085ed918
AK
1239COMMENT_TABLE(event_policy, '')
1240COMMENT_COLUMN(event_policy.rule_id, '')
1241COMMENT_COLUMN(event_policy.rule_guid, '')
1242COMMENT_COLUMN(event_policy.flags, '')
1243COMMENT_COLUMN(event_policy.comments, '')
1244COMMENT_COLUMN(event_policy.script, '')
1245COMMENT_COLUMN(event_policy.alarm_message, '')
1246COMMENT_COLUMN(event_policy.alarm_severity, '')
1247COMMENT_COLUMN(event_policy.alarm_key, '')
1248COMMENT_COLUMN(event_policy.alarm_timeout, '')
1249COMMENT_COLUMN(event_policy.alarm_timeout_event, '')
1250COMMENT_COLUMN(event_policy.situation_id, '')
1251COMMENT_COLUMN(event_policy.situation_instance, '')
1252
1253/**
1254 *
1255 */
feea53fd 1256CREATE TABLE policy_source_list
69cc295f 1257(
51ff26ea
AK
1258 rule_id integer not null,
1259 object_id integer not null,
1260 PRIMARY KEY(rule_id,object_id)
cb7ec554 1261) TABLE_TYPE;
69cc295f 1262
085ed918
AK
1263COMMENT_TABLE(policy_source_list, '')
1264COMMENT_COLUMN(policy_source_list.rule_id, '')
1265COMMENT_COLUMN(policy_source_list.object_id, '')
1266
1267/**
1268 *
1269 */
feea53fd 1270CREATE TABLE policy_event_list
69cc295f 1271(
51ff26ea
AK
1272 rule_id integer not null,
1273 event_code integer not null,
1274 PRIMARY KEY(rule_id,event_code)
cb7ec554 1275) TABLE_TYPE;
b6a77d6d 1276
085ed918
AK
1277COMMENT_TABLE(policy_event_list, '')
1278COMMENT_COLUMN(policy_event_list.rule_id, '')
1279COMMENT_COLUMN(policy_event_list.event_code, '')
1280
1281/**
1282 *
1283 */
feea53fd 1284CREATE TABLE policy_action_list
b6a77d6d 1285(
51ff26ea
AK
1286 rule_id integer not null,
1287 action_id integer not null,
1288 PRIMARY KEY(rule_id,action_id)
cb7ec554 1289) TABLE_TYPE;
54f0de5b 1290
085ed918
AK
1291COMMENT_TABLE(policy_action_list, '')
1292COMMENT_COLUMN(policy_action_list.rule_id, '')
1293COMMENT_COLUMN(policy_action_list.action_id, '')
1294
085ed918
AK
1295/**
1296 *
1297 */
b13fd0b7
VK
1298CREATE TABLE policy_situation_attr_list
1299(
51ff26ea
AK
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)
b13fd0b7
VK
1305) TABLE_TYPE;
1306
085ed918
AK
1307COMMENT_TABLE(policy_situation_attr_list, '')
1308COMMENT_COLUMN(policy_situation_attr_list.rule_id, '')
1309COMMENT_COLUMN(policy_situation_attr_list.situation_id, '')
1310COMMENT_COLUMN(policy_situation_attr_list.attr_name, '')
1311COMMENT_COLUMN(policy_situation_attr_list.attr_value, '')
54f0de5b 1312
59f88625
VK
1313/*
1314** Threshold checking rules
1315*/
feea53fd 1316CREATE TABLE thresholds
59f88625 1317(
51ff26ea
AK
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)
cb7ec554 1335) TABLE_TYPE;
2fa71464 1336
c13cb4a5
AK
1337CREATE INDEX idx_thresholds_item_id ON thresholds(item_id);
1338CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number);
505ca1ae 1339
085ed918
AK
1340COMMENT_TABLE(thresholds, '')
1341COMMENT_COLUMN(thresholds.threshold_id, '')
1342COMMENT_COLUMN(thresholds.item_id, '')
1343COMMENT_COLUMN(thresholds.sequence_number, '')
1344COMMENT_COLUMN(thresholds.fire_value, '')
1345COMMENT_COLUMN(thresholds.rearm_value, '')
1346COMMENT_COLUMN(thresholds.check_function, '')
1347COMMENT_COLUMN(thresholds.check_operation, '')
1348COMMENT_COLUMN(thresholds.sample_count, '')
1349COMMENT_COLUMN(thresholds.script, '')
1350COMMENT_COLUMN(thresholds.event_code, '')
1351COMMENT_COLUMN(thresholds.rearm_event_code, '')
1352COMMENT_COLUMN(thresholds.repeat_interval, '')
1353COMMENT_COLUMN(thresholds.current_state, '')
1354COMMENT_COLUMN(thresholds.current_severity, '')
1355COMMENT_COLUMN(thresholds.match_count, '')
1356COMMENT_COLUMN(thresholds.last_event_timestamp, '')
1357
59a21a78
VK
1358/*
1359** Alarms
1360*/
59a21a78
VK
1361CREATE TABLE alarms
1362(
51ff26ea
AK
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)
cb7ec554 1385) TABLE_TYPE;
59a21a78 1386
085ed918
AK
1387COMMENT_TABLE(alarms, '')
1388COMMENT_COLUMN(alarms.alarm_id, '')
1389COMMENT_COLUMN(alarms.alarm_state, '')
1390COMMENT_COLUMN(alarms.hd_state, '')
1391COMMENT_COLUMN(alarms.hd_ref, '')
1392COMMENT_COLUMN(alarms.creation_time, '')
1393COMMENT_COLUMN(alarms.last_change_time, '')
1394COMMENT_COLUMN(alarms.source_object_id, '')
1395COMMENT_COLUMN(alarms.source_event_code, '')
1396COMMENT_COLUMN(alarms.source_event_id, '')
1397COMMENT_COLUMN(alarms.dci_id, '')
1398COMMENT_COLUMN(alarms.message, '')
1399COMMENT_COLUMN(alarms.original_severity, '')
1400COMMENT_COLUMN(alarms.current_severity, '')
1401COMMENT_COLUMN(alarms.repeat_count, '')
1402COMMENT_COLUMN(alarms.alarm_key, '')
1403COMMENT_COLUMN(alarms.ack_by, '')
1404COMMENT_COLUMN(alarms.resolved_by, '')
1405COMMENT_COLUMN(alarms.term_by, '')
1406COMMENT_COLUMN(alarms.timeout, '')
1407COMMENT_COLUMN(alarms.timeout_event, '')
1408COMMENT_COLUMN(alarms.ack_timeout, '')
1409
59a21a78
VK
1410/*
1411** Alarm notes
1412*/
59a21a78
VK
1413CREATE TABLE alarm_notes
1414(
51ff26ea
AK
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)
cb7ec554 1421) TABLE_TYPE;
917aa2e6 1422
c5131765
VK
1423CREATE INDEX idx_alarm_notes_alarm_id ON alarm_notes(alarm_id);
1424
085ed918
AK
1425COMMENT_TABLE(alarm_notes, '')
1426COMMENT_COLUMN(alarm_notes.note_id, '')
1427COMMENT_COLUMN(alarm_notes.alarm_id, '')
1428COMMENT_COLUMN(alarm_notes.change_time, '')
1429COMMENT_COLUMN(alarm_notes.user_id, '')
1430COMMENT_COLUMN(alarm_notes.note_text, '')
1431
b1e9b6b3
VK
1432/*
1433** Source events for alarms
1434*/
b1e9b6b3
VK
1435CREATE TABLE alarm_events
1436(
51ff26ea
AK
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)
b1e9b6b3
VK
1446) TABLE_TYPE;
1447
1448CREATE INDEX idx_alarm_events_alarm_id ON alarm_events(alarm_id);
1449
085ed918
AK
1450COMMENT_TABLE(alarm_events, '')
1451COMMENT_COLUMN(alarm_events.alarm_id, '')
1452COMMENT_COLUMN(alarm_events.event_id, '')
1453COMMENT_COLUMN(alarm_events.event_code, '')
1454COMMENT_COLUMN(alarm_events.event_name, '')
1455COMMENT_COLUMN(alarm_events.severity, '')
1456COMMENT_COLUMN(alarm_events.source_object_id, '')
1457COMMENT_COLUMN(alarm_events.event_timestamp, '')
1458COMMENT_COLUMN(alarm_events.message, '')
b1e9b6b3 1459
3aeed82c
VK
1460/*
1461** SNMP trap configuration
1462*/
1463
1464CREATE TABLE snmp_trap_cfg
1465(
51ff26ea
AK
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)
cb7ec554 1472) TABLE_TYPE;
3aeed82c 1473
085ed918
AK
1474COMMENT_TABLE(snmp_trap_cfg, '')
1475COMMENT_COLUMN(snmp_trap_cfg.trap_id, '')
1476COMMENT_COLUMN(snmp_trap_cfg.snmp_oid, '')
1477COMMENT_COLUMN(snmp_trap_cfg.event_code, '')
1478COMMENT_COLUMN(snmp_trap_cfg.user_tag, '')
1479COMMENT_COLUMN(snmp_trap_cfg.description, '')
3aeed82c
VK
1480
1481/*
1482** SNMP trap parameters mapping
1483*/
3aeed82c
VK
1484CREATE TABLE snmp_trap_pmap
1485(
51ff26ea
AK
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)
cb7ec554 1492) TABLE_TYPE;
6726bfdc 1493
085ed918
AK
1494COMMENT_TABLE(snmp_trap_pmap, '')
1495COMMENT_COLUMN(snmp_trap_pmap.trap_id, '')
1496COMMENT_COLUMN(snmp_trap_pmap.parameter, '')
1497COMMENT_COLUMN(snmp_trap_pmap.flags, '')
1498COMMENT_COLUMN(snmp_trap_pmap.snmp_oid, '')
1499COMMENT_COLUMN(snmp_trap_pmap.description, '')
1500
6726bfdc
VK
1501/*
1502** Agent packages
1503*/
6726bfdc
VK
1504CREATE TABLE agent_pkg
1505(
51ff26ea
AK
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)
cb7ec554 1513) TABLE_TYPE;
b7a391d7 1514
085ed918
AK
1515COMMENT_TABLE(agent_pkg, '')
1516COMMENT_COLUMN(agent_pkg.pkg_id, '')
1517COMMENT_COLUMN(agent_pkg.pkg_name, '')
1518COMMENT_COLUMN(agent_pkg.version, '')
1519COMMENT_COLUMN(agent_pkg.platform, '')
1520COMMENT_COLUMN(agent_pkg.pkg_file, '')
1521COMMENT_COLUMN(agent_pkg.description, '')
1522
b7a391d7
VK
1523/*
1524** Object tools
1525*/
b7a391d7
VK
1526CREATE TABLE object_tools
1527(
51ff26ea
AK
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)
b7a391d7
VK
1541) TABLE_TYPE;
1542
085ed918
AK
1543COMMENT_TABLE(object_tools, '')
1544COMMENT_COLUMN(object_tools.tool_id, '')
1545COMMENT_COLUMN(object_tools.guid, '')
1546COMMENT_COLUMN(object_tools.tool_name, '')
1547COMMENT_COLUMN(object_tools.tool_type, '')
1548COMMENT_COLUMN(object_tools.tool_data, '')
1549COMMENT_COLUMN(object_tools.description, '')
1550COMMENT_COLUMN(object_tools.flags, '')
1551COMMENT_COLUMN(object_tools.tool_filter, '')
1552COMMENT_COLUMN(object_tools.confirmation_text, '')
1553COMMENT_COLUMN(object_tools.command_name, '')
1554COMMENT_COLUMN(object_tools.command_short_name, '')
1555COMMENT_COLUMN(object_tools.icon, '')
1556
b7a391d7
VK
1557/*
1558** Access list for object tools
1559*/
b7a391d7
VK
1560CREATE TABLE object_tools_acl
1561(
51ff26ea
AK
1562 tool_id integer not null,
1563 user_id integer not null,
1564 PRIMARY KEY(tool_id,user_id)
b7a391d7 1565) TABLE_TYPE;
02108d74 1566
085ed918
AK
1567COMMENT_TABLE(object_tools_acl, '')
1568COMMENT_COLUMN(object_tools_acl.tool_id, '')
1569COMMENT_COLUMN(object_tools_acl.user_id, '')
1570
02108d74 1571/*
461b4074 1572** Configuration of columns for SNMP_TABLE and AGENT_TABLE type of object tools
02108d74 1573*/
461b4074 1574CREATE TABLE object_tools_table_columns
02108d74 1575(
51ff26ea
AK
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)
02108d74 1583) TABLE_TYPE;
46ef501a 1584
085ed918
AK
1585COMMENT_TABLE(object_tools_table_columns, '')
1586COMMENT_COLUMN(object_tools_table_columns.tool_id, '')
1587COMMENT_COLUMN(object_tools_table_columns.col_number, '')
1588COMMENT_COLUMN(object_tools_table_columns.col_name, '')
1589COMMENT_COLUMN(object_tools_table_columns.col_oid, '')
1590COMMENT_COLUMN(object_tools_table_columns.col_format, '')
1591COMMENT_COLUMN(object_tools_table_columns.col_substr, '')
1592
b576249a
VK
1593/**
1594 * Input fields for object tools
1595 */
1596CREATE TABLE object_tools_input_fields
1597(
51ff26ea
AK
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)
b576249a
VK
1605) TABLE_TYPE;
1606
085ed918
AK
1607COMMENT_TABLE(object_tools_input_fields, '')
1608COMMENT_COLUMN(object_tools_input_fields.tool_id, '')
1609COMMENT_COLUMN(object_tools_input_fields.name, '')
1610COMMENT_COLUMN(object_tools_input_fields.input_type, '')
1611COMMENT_COLUMN(object_tools_input_fields.display_name, '')
1612COMMENT_COLUMN(object_tools_input_fields.sequence_num, '')
1613COMMENT_COLUMN(object_tools_input_fields.config, '')
1614
c4096dd8
VK
1615/*
1616** Stored syslog messages
1617*/
c4096dd8
VK
1618CREATE TABLE syslog
1619(
51ff26ea
AK
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)
c4096dd8
VK
1629) TABLE_TYPE;
1630
30639d32
VK
1631CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp);
1632
085ed918
AK
1633COMMENT_TABLE(syslog, '')
1634COMMENT_COLUMN(syslog.msg_id, '')
1635COMMENT_COLUMN(syslog.msg_timestamp, '')
1636COMMENT_COLUMN(syslog.facility, '')
1637COMMENT_COLUMN(syslog.severity, '')
1638COMMENT_COLUMN(syslog.source_object_id, '')
1639COMMENT_COLUMN(syslog.hostname, '')
1640COMMENT_COLUMN(syslog.msg_tag, '')
1641COMMENT_COLUMN(syslog.msg_text, '')
c4096dd8 1642
5c6b881b
VK
1643/*
1644** Script library
1645*/
5c6b881b
VK
1646CREATE TABLE script_library
1647(
51ff26ea
AK
1648 script_id integer not null,
1649 script_name varchar(255) not null,
1650 script_code SQL_TEXT null,
1651 PRIMARY KEY(script_id)
5c6b881b
VK
1652) TABLE_TYPE;
1653
085ed918
AK
1654COMMENT_TABLE(script_library, '')
1655COMMENT_COLUMN(script_library.script_id, '')
1656COMMENT_COLUMN(script_library.script_name, '')
1657COMMENT_COLUMN(script_library.script_code, '')
5c6b881b 1658
56b32598
VK
1659/*
1660** Extended SNMP trap log
1661*/
1662
1663CREATE TABLE snmp_trap_log
1664(
51ff26ea
AK
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)
56b32598
VK
1672) TABLE_TYPE;
1673
bf4a58d4 1674CREATE INDEX idx_snmp_trap_log_tt ON snmp_trap_log(trap_timestamp);
30639d32 1675
085ed918
AK
1676COMMENT_TABLE(snmp_trap_log, '')
1677COMMENT_COLUMN(snmp_trap_log.trap_id, '')
1678COMMENT_COLUMN(snmp_trap_log.trap_timestamp, '')
1679COMMENT_COLUMN(snmp_trap_log.ip_addr, '')
1680COMMENT_COLUMN(snmp_trap_log.object_id, '')
1681COMMENT_COLUMN(snmp_trap_log.trap_oid, '')
1682COMMENT_COLUMN(snmp_trap_log.trap_varlist, '')
1683
d2d7327c
VK
1684/*
1685** Agent configurations
1686*/
d2d7327c
VK
1687CREATE TABLE agent_configs
1688(
51ff26ea
AK
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)
d2d7327c 1695) TABLE_TYPE;
e2ce7f94 1696
085ed918
AK
1697COMMENT_TABLE(agent_configs, '')
1698COMMENT_COLUMN(agent_configs.config_id, '')
1699COMMENT_COLUMN(agent_configs.config_name, '')
1700COMMENT_COLUMN(agent_configs.config_file, '')
1701COMMENT_COLUMN(agent_configs.config_filter, '')
1702COMMENT_COLUMN(agent_configs.sequence_number, '')
1703
e2ce7f94
VK
1704/*
1705** Address lists
1706*/
e2ce7f94
VK
1707CREATE TABLE address_lists
1708(
51ff26ea
AK
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)
e2ce7f94
VK
1715) TABLE_TYPE;
1716
1717CREATE INDEX idx_address_lists_list_type ON address_lists(list_type);
381ed729 1718
085ed918
AK
1719COMMENT_TABLE(address_lists, '')
1720COMMENT_COLUMN(address_lists.list_type, '')
1721COMMENT_COLUMN(address_lists.community_id, '')
1722COMMENT_COLUMN(address_lists.addr_type, '')
1723COMMENT_COLUMN(address_lists.addr1, '')
1724COMMENT_COLUMN(address_lists.addr2, '')
1725
381ed729
VK
1726/*
1727** Graphs
1728*/
381ed729
VK
1729CREATE TABLE graphs
1730(
51ff26ea
AK
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)
381ed729
VK
1738) TABLE_TYPE;
1739
085ed918
AK
1740COMMENT_TABLE(graphs, '')
1741COMMENT_COLUMN(graphs.graph_id, '')
1742COMMENT_COLUMN(graphs.owner_id, '')
1743COMMENT_COLUMN(graphs.flags, '')
1744COMMENT_COLUMN(graphs.name, '')
1745COMMENT_COLUMN(graphs.config, '')
1746COMMENT_COLUMN(graphs.filters, '')
1747
381ed729
VK
1748/*
1749** Graph access lists
1750*/
381ed729
VK
1751CREATE TABLE graph_acl
1752(
51ff26ea
AK
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)
381ed729 1757) TABLE_TYPE;
3d1058ed 1758
085ed918
AK
1759COMMENT_TABLE(graph_acl, '')
1760COMMENT_COLUMN(graph_acl.graph_id, '')
1761COMMENT_COLUMN(graph_acl.user_id, '')
1762COMMENT_COLUMN(graph_acl.user_rights, '')
3d1058ed
VK
1763
1764/*
1765** Certificates
1766*/
1767
1768CREATE TABLE certificates
1769(
51ff26ea
AK
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)
3d1058ed 1776) TABLE_TYPE;
fa585702 1777
085ed918
AK
1778COMMENT_TABLE(certificates, '')
1779COMMENT_COLUMN(certificates.cert_id, '')
1780COMMENT_COLUMN(certificates.cert_type, '')
1781COMMENT_COLUMN(certificates.cert_data, '')
1782COMMENT_COLUMN(certificates.subject, '')
1783COMMENT_COLUMN(certificates.comments, '')
1784
889841e1
VK
1785/*
1786** Audit log
1787*/
889841e1
VK
1788CREATE TABLE audit_log
1789(
51ff26ea
AK
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)
889841e1 1800) TABLE_TYPE;
4ee86f55 1801
085ed918
AK
1802COMMENT_TABLE(audit_log, '')
1803COMMENT_COLUMN(audit_log.record_id, '')
1804COMMENT_COLUMN(audit_log.timestamp, '')
1805COMMENT_COLUMN(audit_log.subsystem, '')
1806COMMENT_COLUMN(audit_log.success, '')
1807COMMENT_COLUMN(audit_log.user_id, '')
1808COMMENT_COLUMN(audit_log.workstation, '')
1809COMMENT_COLUMN(audit_log.session_id, '')
1810COMMENT_COLUMN(audit_log.object_id, '')
1811COMMENT_COLUMN(audit_log.message, '')
1812
4ee86f55
VK
1813/*
1814** Situations
1815*/
4ee86f55
VK
1816CREATE TABLE situations
1817(
51ff26ea
AK
1818 id integer not null,
1819 name varchar(127) not null,
1820 comments SQL_TEXT not null,
1821 PRIMARY KEY(id)
4ee86f55
VK
1822) TABLE_TYPE;
1823
085ed918
AK
1824COMMENT_TABLE(situations, '')
1825COMMENT_COLUMN(situations.id, '')
1826COMMENT_COLUMN(situations.name, '')
1827COMMENT_COLUMN(situations.comments, '')
4ee86f55 1828
023779b8
VK
1829/*
1830** List of possible community strings
1831*/
023779b8
VK
1832CREATE TABLE snmp_communities
1833(
51ff26ea
AK
1834 id integer not null,
1835 community varchar(255) null,
1836 PRIMARY KEY(id)
023779b8 1837) TABLE_TYPE;
a1236e96 1838
085ed918
AK
1839COMMENT_TABLE(snmp_communities, '')
1840COMMENT_COLUMN(snmp_communities.id, '')
1841COMMENT_COLUMN(snmp_communities.community, '')
a1236e96 1842
f2bb4aa1
VK
1843/*
1844** Agent policies - common attributes
1845*/
f2bb4aa1
VK
1846CREATE TABLE ap_common
1847(
51ff26ea
AK
1848 id integer not null,
1849 policy_type integer not null,
1850 version integer not null,
1851 PRIMARY KEY(id)
f2bb4aa1
VK
1852) TABLE_TYPE;
1853
3df8bccd
Z
1854COMMENT_TABLE(ap_common, 'Agent policies common attributes')
1855COMMENT_COLUMN(ap_common.id, 'Unique policy identifier')
1856COMMENT_COLUMN(ap_common.policy_type, 'Policy type')
1857COMMENT_COLUMN(ap_common.version, 'Policy version')
085ed918 1858
f2bb4aa1
VK
1859/*
1860** Agent policies - node binding
1861*/
f2bb4aa1
VK
1862CREATE TABLE ap_bindings
1863(
51ff26ea
AK
1864 policy_id integer not null,
1865 node_id integer not null,
1866 PRIMARY KEY(policy_id,node_id)
f2bb4aa1
VK
1867) TABLE_TYPE;
1868
3df8bccd
Z
1869COMMENT_TABLE(ap_bindings, 'Agent policies to node binding')
1870COMMENT_COLUMN(ap_bindings.policy_id, 'Unique policy identifier')
1871COMMENT_COLUMN(ap_bindings.node_id, 'Unique node identifier')
085ed918 1872
f2bb4aa1
VK
1873/*
1874** Agent policies - configuration files
1875*/
f2bb4aa1
VK
1876CREATE TABLE ap_config_files
1877(
51ff26ea
AK
1878 policy_id integer not null,
1879 file_content SQL_TEXT null,
1880 PRIMARY KEY(policy_id)
f2bb4aa1 1881) TABLE_TYPE;
df8a4ca2 1882
3df8bccd
Z
1883COMMENT_TABLE(ap_config_files, 'Agent policies configuration files')
1884COMMENT_COLUMN(ap_config_files.policy_id, 'Unique policy identifier')
1885COMMENT_COLUMN(ap_config_files.file_content, 'Policy file content')
1886
1887/**
1888** Agent policies - log parser files
1889*/
1890CREATE 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
1897COMMENT_TABLE(ap_log_parser, 'Agent policies log parser files')
1898COMMENT_COLUMN(ap_log_parser.policy_id, 'Unique policy identifier')
1899COMMENT_COLUMN(ap_log_parser.file_content, 'Log parser policy file content')
085ed918 1900
df8a4ca2
VK
1901/*
1902** Default SNMP v3 USM credentials
1903*/
df8a4ca2
VK
1904CREATE TABLE usm_credentials
1905(
51ff26ea
AK
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)
df8a4ca2 1913) TABLE_TYPE;
021dcda7 1914
3df8bccd
Z
1915COMMENT_TABLE(usm_credentials, 'Default SNMP v3 USM credentials')
1916COMMENT_COLUMN(usm_credentials.id, 'SNMP v3 USM credential unique identifier')
1917COMMENT_COLUMN(usm_credentials.user_name, 'SNMP v3 USM username')
1918COMMENT_COLUMN(usm_credentials.auth_method, 'SNMP v3 USM authentication method')
1919COMMENT_COLUMN(usm_credentials.priv_method, 'SNMP v3 USM encryption method')
1920COMMENT_COLUMN(usm_credentials.auth_password, 'SNMP v3 USM authentication password')
1921COMMENT_COLUMN(usm_credentials.priv_password, 'SNMP v3 USM encryption password')
085ed918 1922
021dcda7
VK
1923/*
1924** Network maps
1925*/
1926CREATE TABLE network_maps
1927(
51ff26ea
AK
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)
021dcda7
VK
1944) TABLE_TYPE;
1945
085ed918
AK
1946COMMENT_TABLE(network_maps, '')
1947COMMENT_COLUMN(network_maps.id, '')
1948COMMENT_COLUMN(network_maps.map_type, '')
1949COMMENT_COLUMN(network_maps.layout, '')
1950COMMENT_COLUMN(network_maps.seed, '')
1951COMMENT_COLUMN(network_maps.radius, '')
1952COMMENT_COLUMN(network_maps.background, '')
1953COMMENT_COLUMN(network_maps.bg_latitude, '')
1954COMMENT_COLUMN(network_maps.bg_longitude, '')
1955COMMENT_COLUMN(network_maps.bg_zoom, '')
1956COMMENT_COLUMN(network_maps.flags, '')
1957COMMENT_COLUMN(network_maps.bg_color, '')
1958COMMENT_COLUMN(network_maps.link_color, '')
1959COMMENT_COLUMN(network_maps.link_routing, '')
1960COMMENT_COLUMN(network_maps.object_display_mode, '')
1961COMMENT_COLUMN(network_maps.filter, '')
1962
8bdd26dc
VK
1963/**
1964 * Elements of network maps
1965 */
021dcda7
VK
1966CREATE TABLE network_map_elements
1967(
51ff26ea
AK
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)
021dcda7 1974) TABLE_TYPE;
cce8f214 1975
085ed918
AK
1976COMMENT_TABLE(network_map_elements, '')
1977COMMENT_COLUMN(network_map_elements.map_id, '')
1978COMMENT_COLUMN(network_map_elements.element_id, '')
1979COMMENT_COLUMN(network_map_elements.element_type, '')
1980COMMENT_COLUMN(network_map_elements.element_data, '')
1981COMMENT_COLUMN(network_map_elements.flags, '')
1982
8bdd26dc
VK
1983/**
1984 * Links on network maps
1985 */
cce8f214
VK
1986CREATE TABLE network_map_links
1987(
51ff26ea
AK
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
cce8f214 1997) TABLE_TYPE;
e6b9439a 1998
173c189d
VK
1999CREATE INDEX idx_network_map_links_map_id ON network_map_links(map_id);
2000
085ed918
AK
2001COMMENT_TABLE(network_map_links, '')
2002COMMENT_COLUMN(network_map_links.map_id, '')
2003COMMENT_COLUMN(network_map_links.element1, '')
2004COMMENT_COLUMN(network_map_links.element2, '')
2005COMMENT_COLUMN(network_map_links.link_type, '')
2006COMMENT_COLUMN(network_map_links.link_name, '')
2007COMMENT_COLUMN(network_map_links.connector_name1, '')
2008COMMENT_COLUMN(network_map_links.connector_name2, '')
2009COMMENT_COLUMN(network_map_links.element_data, '')
2010COMMENT_COLUMN(network_map_links.flags, '')
2011
8bdd26dc
VK
2012/**
2013 * Image Library
2014 */
e6b9439a
AK
2015CREATE TABLE images
2016(
51ff26ea
AK
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)
e6b9439a 2024) TABLE_TYPE;
926e8ce7 2025
085ed918
AK
2026COMMENT_TABLE(images, '')
2027COMMENT_COLUMN(images.guid, '')
2028COMMENT_COLUMN(images.name, '')
2029COMMENT_COLUMN(images.category, '')
2030COMMENT_COLUMN(images.mimetype, '')
2031COMMENT_COLUMN(images.protected, '')
2032
8bdd26dc
VK
2033/**
2034 * Dashboards
2035 */
926e8ce7
VK
2036CREATE TABLE dashboards
2037(
51ff26ea
AK
2038 id integer not null,
2039 num_columns integer not null,
2040 options integer not null,
2041 PRIMARY KEY(id)
926e8ce7
VK
2042) TABLE_TYPE;
2043
085ed918
AK
2044COMMENT_TABLE(dashboards, '')
2045COMMENT_COLUMN(dashboards.id, '')
2046COMMENT_COLUMN(dashboards.num_columns, '')
2047COMMENT_COLUMN(dashboards.options, '')
2048
8bdd26dc
VK
2049/**
2050 * Dashboard elements
2051 */
926e8ce7
VK
2052CREATE TABLE dashboard_elements
2053(
51ff26ea
AK
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)
926e8ce7 2060) TABLE_TYPE;
abf24277 2061
085ed918
AK
2062COMMENT_TABLE(dashboard_elements, '')
2063COMMENT_COLUMN(dashboard_elements.dashboard_id, '')
2064COMMENT_COLUMN(dashboard_elements.element_id, '')
2065COMMENT_COLUMN(dashboard_elements.element_type, '')
2066COMMENT_COLUMN(dashboard_elements.element_data, '')
2067COMMENT_COLUMN(dashboard_elements.layout_data, '')
2068
8bdd26dc
VK
2069/**
2070 * Associations between dashboards and other objects
2071 */
2072CREATE TABLE dashboard_associations
2073(
51ff26ea
AK
2074 object_id integer not null,
2075 dashboard_id integer not null,
2076 PRIMARY KEY(object_id,dashboard_id)
8bdd26dc
VK
2077) TABLE_TYPE;
2078
085ed918
AK
2079COMMENT_TABLE(dashboard_associations, '')
2080COMMENT_COLUMN(dashboard_associations.object_id, '')
2081COMMENT_COLUMN(dashboard_associations.dashboard_id, '')
2082
8bdd26dc
VK
2083/**
2084 * SLA business services
2085 */
abf24277
VK
2086CREATE TABLE business_services
2087(
51ff26ea
AK
2088 service_id integer not null,
2089 PRIMARY KEY(service_id)
abf24277
VK
2090) TABLE_TYPE;
2091
085ed918
AK
2092COMMENT_TABLE(business_services, '')
2093COMMENT_COLUMN(business_services.service_id, '')
2094
8bdd26dc
VK
2095/**
2096 * SLA service checks
2097 */
abf24277
VK
2098CREATE TABLE slm_checks
2099(
51ff26ea
AK
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)
abf24277
VK
2109) TABLE_TYPE;
2110
085ed918
AK
2111COMMENT_TABLE(slm_checks, '')
2112COMMENT_COLUMN(slm_checks.id, '')
2113COMMENT_COLUMN(slm_checks.type, '')
2114COMMENT_COLUMN(slm_checks.content, '')
2115COMMENT_COLUMN(slm_checks.threshold_id, '')
2116COMMENT_COLUMN(slm_checks.reason, '')
2117COMMENT_COLUMN(slm_checks.is_template, '')
2118COMMENT_COLUMN(slm_checks.template_id, '')
2119COMMENT_COLUMN(slm_checks.current_ticket, '')
2120
abf24277
VK
2121/*
2122** SLA node links
2123*/
abf24277
VK
2124CREATE TABLE node_links
2125(
51ff26ea
AK
2126 nodelink_id integer not null,
2127 node_id integer not null, // refers to node.id
2128 PRIMARY KEY(nodelink_id)
abf24277
VK
2129) TABLE_TYPE;
2130
085ed918
AK
2131COMMENT_TABLE(node_links, '')
2132COMMENT_COLUMN(node_links.nodelink_id, '')
2133COMMENT_COLUMN(node_links.node_id, '')
2134
abf24277
VK
2135/*
2136** SLA - agreements
2137*/
abf24277
VK
2138CREATE TABLE slm_agreements
2139(
51ff26ea
AK
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)
abf24277
VK
2148) TABLE_TYPE;
2149
085ed918
AK
2150COMMENT_TABLE(slm_agreements, '')
2151COMMENT_COLUMN(slm_agreements.agreement_id, '')
2152COMMENT_COLUMN(slm_agreements.service_id, '')
2153COMMENT_COLUMN(slm_agreements.org_id, '')
2154COMMENT_COLUMN(slm_agreements.uptime, '')
2155COMMENT_COLUMN(slm_agreements.period, '')
2156COMMENT_COLUMN(slm_agreements.start_date, '')
2157COMMENT_COLUMN(slm_agreements.notes, '')
abf24277
VK
2158
2159/*
2160** SLA tickets
2161*/
2162
2163CREATE TABLE slm_tickets
2164(
51ff26ea
AK
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)
abf24277
VK
2172) TABLE_TYPE;
2173
085ed918
AK
2174COMMENT_TABLE(slm_tickets, '')
2175COMMENT_COLUMN(slm_tickets.ticket_id, '')
2176COMMENT_COLUMN(slm_tickets.service_id, '')
2177COMMENT_COLUMN(slm_tickets.check_id, '')
2178COMMENT_COLUMN(slm_tickets.create_timestamp, '')
2179COMMENT_COLUMN(slm_tickets.close_timestamp, '')
2180COMMENT_COLUMN(slm_tickets.reason, '')
abf24277 2181
dfe3b3e7
AK
2182/*
2183** SLA service change log
2184*/
2185
2186CREATE TABLE slm_service_history
2187(
51ff26ea
AK
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)
dfe3b3e7
AK
2193) TABLE_TYPE;
2194
085ed918
AK
2195COMMENT_TABLE(slm_service_history, '')
2196COMMENT_COLUMN(slm_service_history.record_id, '')
2197COMMENT_COLUMN(slm_service_history.service_id, '')
2198COMMENT_COLUMN(slm_service_history.change_timestamp, '')
2199COMMENT_COLUMN(slm_service_history.new_status, '')
2200
dfe3b3e7 2201
abf24277
VK
2202/*
2203** Organizations
2204*/
2205
2206CREATE TABLE organizations
2207(
51ff26ea
AK
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)
abf24277
VK
2215) TABLE_TYPE;
2216
085ed918
AK
2217COMMENT_TABLE(organizations, '')
2218COMMENT_COLUMN(organizations.id, '')
2219COMMENT_COLUMN(organizations.parent_id, '')
2220COMMENT_COLUMN(organizations.org_type, '')
2221COMMENT_COLUMN(organizations.name, '')
2222COMMENT_COLUMN(organizations.description, '')
2223COMMENT_COLUMN(organizations.manager, '')
abf24277
VK
2224
2225/*
2226** Persons
2227*/
2228
2229CREATE TABLE persons
2230(
51ff26ea
AK
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)
abf24277 2238) TABLE_TYPE;
878b4261 2239
085ed918
AK
2240COMMENT_TABLE(persons, '')
2241COMMENT_COLUMN(persons.id, '')
2242COMMENT_COLUMN(persons.org_id, '')
2243COMMENT_COLUMN(persons.first_name, '')
2244COMMENT_COLUMN(persons.last_name, '')
2245COMMENT_COLUMN(persons.title, '')
2246COMMENT_COLUMN(persons.status, '')
2247
878b4261
VK
2248/*
2249** Job history
2250*/
878b4261
VK
2251CREATE TABLE job_history
2252(
51ff26ea
AK
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)
878b4261 2265) TABLE_TYPE;
845b8121 2266
085ed918
AK
2267COMMENT_TABLE(job_history, '')
2268COMMENT_COLUMN(job_history.id, '')
2269COMMENT_COLUMN(job_history.time_created, '')
2270COMMENT_COLUMN(job_history.time_started, '')
2271COMMENT_COLUMN(job_history.time_finished, '')
2272COMMENT_COLUMN(job_history.job_type, '')
2273COMMENT_COLUMN(job_history.description, '')
2274COMMENT_COLUMN(job_history.additional_info, '')
2275COMMENT_COLUMN(job_history.node_id, '')
2276COMMENT_COLUMN(job_history.user_id, '')
2277COMMENT_COLUMN(job_history.status, '')
2278COMMENT_COLUMN(job_history.failure_message, '')
2279
ae2a3458
VK
2280/*
2281** License repository
2282*/
ae2a3458
VK
2283CREATE TABLE licenses
2284(
51ff26ea
AK
2285 id integer not null,
2286 content SQL_TEXT null,
2287 PRIMARY KEY(id)
ae2a3458 2288) TABLE_TYPE;
69bb7f47 2289
085ed918
AK
2290COMMENT_TABLE(licenses, '')
2291COMMENT_COLUMN(licenses.id, '')
2292COMMENT_COLUMN(licenses.content, '')
2293
69bb7f47
VK
2294/*
2295** Mapping tables
2296*/
69bb7f47
VK
2297CREATE TABLE mapping_tables
2298(
51ff26ea
AK
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)
69bb7f47
VK
2304) TABLE_TYPE;
2305
085ed918
AK
2306COMMENT_TABLE(mapping_tables, '')
2307COMMENT_COLUMN(mapping_tables.id, '')
2308COMMENT_COLUMN(mapping_tables.name, '')
2309COMMENT_COLUMN(mapping_tables.flags, '')
2310COMMENT_COLUMN(mapping_tables.description, '')
2311
69bb7f47
VK
2312/*
2313** Mapping tables content
2314*/
69bb7f47
VK
2315CREATE TABLE mapping_data
2316(
51ff26ea
AK
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)
69bb7f47 2322) TABLE_TYPE;
b4c2a628 2323
085ed918
AK
2324COMMENT_TABLE(mapping_data, '')
2325COMMENT_COLUMN(mapping_data.table_id, '')
2326COMMENT_COLUMN(mapping_data.md_key, '')
2327COMMENT_COLUMN(mapping_data.md_value, '')
2328COMMENT_COLUMN(mapping_data.description, '')
2329
b4c2a628
VK
2330/*
2331** DCI summary tables
2332*/
b4c2a628
VK
2333CREATE TABLE dci_summary_tables
2334(
51ff26ea
AK
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)
b4c2a628 2343) TABLE_TYPE;
0a145c10 2344
085ed918
AK
2345COMMENT_TABLE(dci_summary_tables, '')
2346COMMENT_COLUMN(dci_summary_tables.id, '')
2347COMMENT_COLUMN(dci_summary_tables.guid, '')
2348COMMENT_COLUMN(dci_summary_tables.menu_path, '')
2349COMMENT_COLUMN(dci_summary_tables.title, '')
2350COMMENT_COLUMN(dci_summary_tables.node_filter, '')
2351COMMENT_COLUMN(dci_summary_tables.flags, '')
2352COMMENT_COLUMN(dci_summary_tables.columns, '')
2353
0a145c10 2354/*
c6e191d2 2355** Scheduled tasks
0a145c10 2356*/
c6e191d2 2357CREATE TABLE scheduled_tasks
0a145c10 2358(
51ff26ea
AK
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)
0a145c10 2369) TABLE_TYPE;
f61f151c 2370
085ed918
AK
2371COMMENT_TABLE(scheduled_tasks, '')
2372COMMENT_COLUMN(scheduled_tasks.id, '')
2373COMMENT_COLUMN(scheduled_tasks.taskId, '')
2374COMMENT_COLUMN(scheduled_tasks.schedule, '')
2375COMMENT_COLUMN(scheduled_tasks.params, '')
2376COMMENT_COLUMN(scheduled_tasks.execution_time, '')
2377COMMENT_COLUMN(scheduled_tasks.last_execution_time, '')
2378COMMENT_COLUMN(scheduled_tasks.flags, '')
2379COMMENT_COLUMN(scheduled_tasks.owner, '')
2380COMMENT_COLUMN(scheduled_tasks.object_id, '')
2381
f61f151c
AK
2382/*
2383** ZMQ Subscriptions
2384*/
2385CREATE TABLE zmq_subscription
2386(
51ff26ea
AK
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)
f61f151c 2392) TABLE_TYPE;
085ed918
AK
2393
2394COMMENT_TABLE(zmq_subscription, '')
2395COMMENT_COLUMN(zmq_subscription.object_id, '')
2396COMMENT_COLUMN(zmq_subscription.subscription_type, '')
2397COMMENT_COLUMN(zmq_subscription.ignore_items, '')
2398COMMENT_COLUMN(zmq_subscription.items, '')
0de31ec3
VK
2399
2400/*
2401** Currency codes
2402*/
2403CREATE TABLE currency_codes
2404(
2405 numeric_code char(3) not null,
2406 alpha_code char(3) not null,
a27a303e 2407 description varchar(127) not null,
0de31ec3
VK
2408 exponent integer not null,
2409 PRIMARY KEY(numeric_code)
2410) TABLE_TYPE;
2411
2412COMMENT_TABLE(currency_codes, 'List of currency codes')
2413COMMENT_COLUMN(currency_codes.numeric_code, 'Numeric ISO code')
2414COMMENT_COLUMN(currency_codes.alpha_code, 'Alphabetical ISO code')
2415COMMENT_COLUMN(currency_codes.description, 'Description')
2416COMMENT_COLUMN(currency_codes.exponent, 'Exponent for minor units')
2417
2418/*
2419** Country codes
2420*/
2421CREATE 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,
a27a303e 2426 name varchar(127) not null,
0de31ec3
VK
2427 PRIMARY KEY(numeric_code)
2428) TABLE_TYPE;
2429
2430COMMENT_TABLE(country_codes, 'List of country codes')
2431COMMENT_COLUMN(country_codes.numeric_code, 'Numeric ISO code')
2432COMMENT_COLUMN(country_codes.alpha_code, 'Alphabetical 2 character ISO code')
2433COMMENT_COLUMN(country_codes.alpha3_code, 'Alphabetical 3 character ISO code')
2434COMMENT_COLUMN(country_codes.name, 'Country name')
8e6e8ef1
VK
2435
2436/*
2437** Config repositories
2438*/
2439CREATE 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;