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