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