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