Commit | Line | Data |
---|---|---|
6c9e7d36 | 1 | /* |
50cbbfec VK |
2 | ** NetXMS Database Schema |
3 | ** | |
51ff26ea | 4 | ** ex: syntax=sql ts=2 sw=2 expandtab |
6c9e7d36 | 5 | */ |
cbcaf8c8 | 6 | |
28f5b9a4 VK |
7 | /* |
8 | ** Metadata | |
9 | */ | |
28f5b9a4 VK |
10 | CREATE TABLE metadata |
11 | ( | |
51ff26ea AK |
12 | var_name varchar(63) not null, |
13 | var_value varchar(255) not null, | |
14 | PRIMARY KEY(var_name) | |
28f5b9a4 VK |
15 | ) TABLE_TYPE; |
16 | ||
c85c8ef2 | 17 | COMMENT_TABLE(metadata, 'System meta data (DBMS type, schema version, etc.)') |
938a6733 AK |
18 | COMMENT_COLUMN(metadata.var_name, 'Variable name') |
19 | COMMENT_COLUMN(metadata.var_value, 'Variable value') | |
20 | ||
50cbbfec VK |
21 | /* |
22 | ** System configuration table | |
23 | */ | |
feea53fd | 24 | CREATE TABLE config |
cbcaf8c8 | 25 | ( |
51ff26ea AK |
26 | var_name varchar(63) not null, |
27 | var_value varchar(2000) null, | |
963c030d | 28 | #ifdef DB_ORACLE |
51ff26ea AK |
29 | is_visible integer default 1 not null, |
30 | need_server_restart integer default 0 not null, | |
31 | data_type char(1) default 'S' not null, | |
32 | is_public char(1) default 'N' not null, | |
963c030d | 33 | #else |
51ff26ea AK |
34 | is_visible integer not null default 1, |
35 | need_server_restart integer not null default 0, | |
36 | data_type char(1) not null default 'S', | |
37 | is_public char(1) not null default 'N', | |
963c030d | 38 | #endif |
51ff26ea AK |
39 | description varchar(255) null, |
40 | possible_values SQL_TEXT null, | |
41 | PRIMARY KEY(var_name) | |
cb7ec554 | 42 | ) TABLE_TYPE; |
cbcaf8c8 | 43 | |
51ff26ea | 44 | COMMENT_TABLE(config, 'Server configuration') |
085ed918 AK |
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') | |
938a6733 | 53 | |
07f58d3c VK |
54 | /* |
55 | ** System configuration data (for large data) | |
56 | */ | |
07f58d3c VK |
57 | CREATE TABLE config_clob |
58 | ( | |
51ff26ea AK |
59 | var_name varchar(63) not null, |
60 | var_value SQL_TEXT null, | |
61 | PRIMARY KEY(var_name) | |
07f58d3c VK |
62 | ) TABLE_TYPE; |
63 | ||
085ed918 AK |
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') | |
938a6733 | 67 | |
6c9e7d36 VK |
68 | /* |
69 | ** Users | |
70 | */ | |
feea53fd | 71 | CREATE TABLE users |
9b057805 | 72 | ( |
51ff26ea AK |
73 | id integer not null, |
74 | guid varchar(36) not null, | |
75 | name varchar(63) not null, | |
76 | password varchar(127) not null, | |
77 | system_access SQL_INT64 not null, | |
78 | flags integer not null, | |
79 | full_name varchar(127) null, | |
80 | description varchar(255) null, | |
81 | grace_logins integer not null, | |
82 | auth_method integer not null, | |
83 | cert_mapping_method integer not null, | |
84 | cert_mapping_data SQL_TEXT null, | |
85 | auth_failures integer not null, | |
86 | last_passwd_change integer not null, | |
87 | min_passwd_length integer not null, | |
88 | disabled_until integer not null, | |
89 | last_login integer not null, | |
90 | password_history SQL_TEXT null, | |
91 | xmpp_id varchar(127) null, | |
92 | ldap_dn SQL_TEXT null, | |
bad2c02e | 93 | ldap_unique_id varchar(64) null, |
51ff26ea | 94 | PRIMARY KEY(id) |
cb7ec554 | 95 | ) TABLE_TYPE; |
9b057805 | 96 | |
938a6733 | 97 | COMMENT_TABLE(users, 'System users') |
085ed918 AK |
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, '') | |
1ea2769b | 117 | COMMENT_COLUMN(users.ldap_dn, 'LDAP dn') |
118 | COMMENT_COLUMN(users.ldap_unique_id, 'LDAP unique id if field set') | |
9b057805 | 119 | |
6c9e7d36 VK |
120 | /* |
121 | ** User groups | |
122 | */ | |
f806ed94 | 123 | |
feea53fd | 124 | CREATE TABLE user_groups |
f806ed94 | 125 | ( |
51ff26ea AK |
126 | id integer not null, |
127 | guid varchar(36) not null, | |
128 | name varchar(63) not null, | |
129 | system_access SQL_INT64 not null, | |
130 | flags integer not null, | |
131 | description varchar(255), | |
132 | ldap_dn SQL_TEXT null, | |
80b13e3c | 133 | ldap_unique_id varchar(64) null, |
51ff26ea | 134 | PRIMARY KEY(id) |
cb7ec554 | 135 | ) TABLE_TYPE; |
f806ed94 | 136 | |
938a6733 | 137 | COMMENT_TABLE(user_groups, 'System user groups') |
1ea2769b | 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') | |
085ed918 | 142 | COMMENT_COLUMN(user_groups.flags, '') |
1ea2769b | 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') | |
f806ed94 | 146 | |
6c9e7d36 VK |
147 | /* |
148 | ** Users to groups mapping | |
149 | */ | |
f806ed94 | 150 | |
feea53fd | 151 | CREATE TABLE user_group_members |
f806ed94 | 152 | ( |
51ff26ea AK |
153 | group_id integer not null, |
154 | user_id integer not null, | |
155 | PRIMARY KEY(group_id,user_id) | |
cb7ec554 | 156 | ) TABLE_TYPE; |
f806ed94 | 157 | |
938a6733 | 158 | COMMENT_TABLE(user_group_members, 'Users and group relationships') |
1ea2769b | 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') | |
f806ed94 | 161 | |
b4fbaa08 VK |
162 | /* |
163 | ** User profiles | |
164 | */ | |
165 | ||
166 | CREATE TABLE user_profiles | |
167 | ( | |
51ff26ea AK |
168 | user_id integer not null, |
169 | var_name varchar(255) not null, | |
170 | var_value SQL_TEXT not null, | |
171 | PRIMARY KEY(user_id,var_name) | |
b4fbaa08 VK |
172 | ) TABLE_TYPE; |
173 | ||
938a6733 | 174 | COMMENT_TABLE(user_profiles, 'User profiles') |
085ed918 AK |
175 | COMMENT_COLUMN(user_profiles.user_id, '') |
176 | COMMENT_COLUMN(user_profiles.var_name, '') | |
177 | COMMENT_COLUMN(user_profiles.var_value, '') | |
b4fbaa08 | 178 | |
c45e0213 VK |
179 | /* |
180 | ** Custom attributes for user database objects | |
181 | */ | |
182 | ||
183 | CREATE TABLE userdb_custom_attributes | |
184 | ( | |
51ff26ea AK |
185 | object_id integer not null, |
186 | attr_name varchar(255) not null, | |
187 | attr_value SQL_TEXT not null, | |
188 | PRIMARY KEY(object_id,attr_name) | |
c45e0213 VK |
189 | ) TABLE_TYPE; |
190 | ||
085ed918 AK |
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, '') | |
c45e0213 | 195 | |
b4fbaa08 VK |
196 | /* |
197 | ** Common object properties | |
198 | */ | |
199 | ||
200 | CREATE TABLE object_properties | |
201 | ( | |
51ff26ea AK |
202 | object_id integer not null, |
203 | guid varchar(36) not null, | |
204 | name varchar(63) not null, | |
205 | status integer not null, | |
206 | is_deleted integer not null, | |
207 | is_system integer not null, | |
208 | last_modified integer not null, | |
209 | inherit_access_rights integer not null, | |
210 | status_calc_alg integer not null, | |
211 | status_prop_alg integer not null, | |
212 | status_fixed_val integer not null, | |
213 | status_shift integer not null, | |
214 | status_translation varchar(8) not null, | |
215 | status_single_threshold integer not null, | |
216 | status_thresholds varchar(8) not null, | |
217 | comments SQL_TEXT null, | |
218 | location_type integer not null, | |
219 | latitude varchar(20), | |
220 | longitude varchar(20), | |
221 | location_accuracy integer not null, | |
222 | location_timestamp integer not null, | |
223 | image varchar(36) not null, | |
224 | submap_id integer not null, | |
225 | country varchar(63) null, | |
226 | city varchar(63) null, | |
227 | street_address varchar(255) null, | |
228 | postcode varchar(31) null, | |
229 | maint_mode char(1) not null, | |
230 | maint_event_id SQL_INT64 not null, | |
231 | PRIMARY KEY(object_id) | |
b4fbaa08 VK |
232 | ) TABLE_TYPE; |
233 | ||
1ea2769b | 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') | |
9decb6b9 | 238 | COMMENT_COLUMN(object_properties.status, 'Object status(Unknown, Normal, Warning, Minor ...)') |
1ea2769b | 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...)') | |
7bbd8cf1 | 241 | COMMENT_COLUMN(object_properties.last_modified, 'Timestamp when this object was last modified') |
085ed918 AK |
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, '') | |
7bbd8cf1 | 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') | |
085ed918 AK |
256 | COMMENT_COLUMN(object_properties.image, '') |
257 | COMMENT_COLUMN(object_properties.submap_id, '') | |
7bbd8cf1 | 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') | |
085ed918 AK |
262 | COMMENT_COLUMN(object_properties.maint_mode, '') |
263 | COMMENT_COLUMN(object_properties.maint_event_id, '') | |
b4fbaa08 | 264 | |
a1236e96 VK |
265 | /* |
266 | ** Object custom attributes | |
267 | */ | |
a1236e96 VK |
268 | CREATE TABLE object_custom_attributes |
269 | ( | |
51ff26ea AK |
270 | object_id integer not null, |
271 | attr_name varchar(127) not null, | |
272 | attr_value SQL_TEXT null, | |
273 | PRIMARY KEY(object_id,attr_name) | |
a1236e96 VK |
274 | ) TABLE_TYPE; |
275 | ||
ff392ecf VK |
276 | CREATE INDEX idx_ocattr_oid ON object_custom_attributes(object_id); |
277 | ||
7bbd8cf1 | 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') | |
085ed918 | 282 | |
9587eba3 | 283 | /* |
9aa67910 | 284 | ** Zone objects |
9587eba3 | 285 | */ |
9587eba3 VK |
286 | CREATE TABLE zones |
287 | ( | |
51ff26ea AK |
288 | id integer not null, // Zone object ID |
289 | zone_guid integer not null, // Globally unique ID for zone | |
43b62436 | 290 | proxy_node integer not null, |
51ff26ea | 291 | PRIMARY KEY(id) |
9587eba3 VK |
292 | ) TABLE_TYPE; |
293 | ||
7bbd8cf1 | 294 | COMMENT_TABLE(zones, 'Zones') |
295 | COMMENT_COLUMN(zones.id, 'Zone id from object_properties') | |
296 | COMMENT_COLUMN(zones.zone_guid, 'Zone GUID') | |
43b62436 | 297 | COMMENT_COLUMN(zones.proxy_node, 'ID of proxy node for this zone') |
085ed918 | 298 | |
9aa67910 VK |
299 | /* |
300 | ** Mobile device objects | |
301 | */ | |
9aa67910 VK |
302 | CREATE TABLE mobile_devices |
303 | ( | |
51ff26ea AK |
304 | id integer not null, |
305 | device_id varchar(64) not null, | |
306 | vendor varchar(64) null, | |
307 | model varchar(128) null, | |
308 | serial_number varchar(64) null, | |
309 | os_name varchar(32) null, | |
310 | os_version varchar(64) null, | |
311 | user_id varchar(64) null, | |
312 | battery_level integer not null, | |
313 | PRIMARY KEY(id) | |
9aa67910 VK |
314 | ) TABLE_TYPE; |
315 | ||
7bbd8cf1 | 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') | |
085ed918 | 324 | COMMENT_COLUMN(mobile_devices.user_id, '') |
7bbd8cf1 | 325 | COMMENT_COLUMN(mobile_devices.battery_level, 'Battery charge level') |
085ed918 | 326 | |
8715a84c VK |
327 | /* |
328 | ** Access point objects | |
329 | */ | |
8715a84c VK |
330 | CREATE TABLE access_points |
331 | ( | |
51ff26ea AK |
332 | id integer not null, |
333 | node_id integer not null, | |
334 | mac_address varchar(12) null, | |
335 | vendor varchar(64) null, | |
336 | model varchar(128) null, | |
337 | serial_number varchar(64) null, | |
338 | ap_state integer not null, | |
339 | ap_index integer not null, | |
340 | PRIMARY KEY(id) | |
8715a84c VK |
341 | ) TABLE_TYPE; |
342 | ||
085ed918 AK |
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 | ||
8715a84c VK |
353 | /* |
354 | ** Rack objects | |
355 | */ | |
8715a84c VK |
356 | CREATE TABLE racks |
357 | ( | |
51ff26ea AK |
358 | id integer not null, |
359 | height integer not null, | |
360 | top_bottom_num char(1) not null, | |
361 | PRIMARY KEY(id) | |
8715a84c VK |
362 | ) TABLE_TYPE; |
363 | ||
7bbd8cf1 | 364 | COMMENT_TABLE(racks, 'Racks') |
365 | COMMENT_COLUMN(racks.id, 'Rack id form object_properties') | |
085ed918 AK |
366 | COMMENT_COLUMN(racks.height, '') |
367 | COMMENT_COLUMN(racks.top_bottom_num, '') | |
368 | ||
e4926628 VK |
369 | /* |
370 | ** Chassis objects | |
371 | */ | |
372 | CREATE TABLE chassis | |
373 | ( | |
374 | id integer not null, | |
375 | controller_id integer not null, | |
32a400d9 | 376 | flags integer not null, |
e4926628 VK |
377 | rack_id integer not null, |
378 | rack_image varchar(36) null, | |
379 | rack_position integer not null, | |
380 | rack_height integer not null, | |
381 | PRIMARY KEY(id) | |
382 | ) TABLE_TYPE; | |
383 | ||
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') | |
32a400d9 | 387 | COMMENT_COLUMN(chassis.flags, 'Chassis options as bit flags') |
e4926628 VK |
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 | ||
6c9e7d36 VK |
393 | /* |
394 | ** Nodes information | |
395 | */ | |
feea53fd | 396 | CREATE TABLE nodes |
cbcaf8c8 | 397 | ( |
51ff26ea AK |
398 | id integer not null, |
399 | primary_name varchar(255) null, | |
400 | primary_ip varchar(48) not null, | |
401 | node_flags integer not null, | |
402 | runtime_flags integer not null, | |
403 | snmp_version integer not null, | |
404 | snmp_port integer not null, | |
405 | community varchar(127) null, | |
406 | usm_auth_password varchar(127) null, | |
407 | usm_priv_password varchar(127) null, | |
408 | usm_methods integer not null, | |
409 | snmp_oid varchar(255) null, | |
410 | auth_method integer not null, | |
411 | secret varchar(64) null, | |
412 | agent_port integer not null, | |
413 | status_poll_type integer not null, | |
414 | agent_version varchar(63) null, | |
415 | platform_name varchar(63) null, | |
416 | poller_node_id integer not null, | |
417 | zone_guid integer not null, | |
418 | proxy_node integer not null, | |
419 | snmp_proxy integer not null, | |
420 | icmp_proxy integer not null, | |
421 | required_polls integer not null, | |
422 | uname varchar(255) null, | |
423 | use_ifxtable integer not null, | |
424 | snmp_sys_name varchar(127) null, | |
425 | snmp_sys_contact varchar(127) null, | |
426 | snmp_sys_location varchar(127) null, | |
427 | bridge_base_addr varchar(15) null, | |
428 | down_since integer not null, | |
429 | boot_time integer not null, | |
430 | driver_name varchar(32) null, | |
431 | rack_image varchar(36) null, | |
432 | rack_position integer not null, | |
433 | rack_height integer not null, | |
434 | rack_id integer not null, | |
e4926628 | 435 | chassis_id integer not null, |
51ff26ea AK |
436 | agent_cache_mode char(1) not null, |
437 | last_agent_comm_time integer not null, | |
ba756b1a VK |
438 | syslog_msg_count SQL_INT64 not null, |
439 | snmp_trap_count SQL_INT64 not null, | |
e980db40 VK |
440 | node_type integer not null, |
441 | node_subtype varchar(127) null, | |
241541f4 VK |
442 | ssh_login varchar(63) null, |
443 | ssh_password varchar(63) null, | |
444 | ssh_proxy integer not null, | |
51ff26ea | 445 | PRIMARY KEY(id) |
cb7ec554 | 446 | ) TABLE_TYPE; |
9e6c6d05 | 447 | |
7bbd8cf1 | 448 | COMMENT_TABLE(nodes, 'Nodes') |
449 | COMMENT_COLUMN(nodes.id, 'Node id from object_properties') | |
085ed918 AK |
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, '') | |
e4926628 | 486 | COMMENT_COLUMN(nodes.chassis_id, 'ID of chassis object this node belongs to') |
085ed918 AK |
487 | COMMENT_COLUMN(nodes.agent_cache_mode, '') |
488 | COMMENT_COLUMN(nodes.last_agent_comm_time, '') | |
ba756b1a VK |
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') | |
9e6c6d05 | 491 | |
97e09882 VK |
492 | /* |
493 | ** Clusters information | |
494 | */ | |
495 | ||
375e0736 | 496 | CREATE TABLE clusters |
97e09882 | 497 | ( |
51ff26ea AK |
498 | id integer not null, |
499 | cluster_type integer not null, | |
500 | zone_guid integer not null, | |
501 | PRIMARY KEY(id) | |
375e0736 | 502 | ) TABLE_TYPE; |
97e09882 | 503 | |
7bbd8cf1 | 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') | |
97e09882 VK |
508 | |
509 | /* | |
510 | ** Cluster members | |
511 | */ | |
512 | ||
375e0736 | 513 | CREATE TABLE cluster_members |
97e09882 | 514 | ( |
51ff26ea AK |
515 | cluster_id integer not null, |
516 | node_id integer not null, | |
517 | PRIMARY KEY(cluster_id,node_id) | |
375e0736 | 518 | ) TABLE_TYPE; |
97e09882 | 519 | |
7bbd8cf1 | 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') | |
97e09882 | 523 | |
a14a4916 VK |
524 | /* |
525 | ** Cluster interconnect subnets | |
526 | */ | |
527 | ||
375e0736 | 528 | CREATE TABLE cluster_sync_subnets |
a14a4916 | 529 | ( |
51ff26ea AK |
530 | cluster_id integer not null, |
531 | subnet_addr varchar(48) not null, | |
532 | subnet_mask integer not null, | |
533 | PRIMARY KEY(cluster_id,subnet_addr) | |
375e0736 | 534 | ) TABLE_TYPE; |
a14a4916 | 535 | |
085ed918 AK |
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, '') | |
a14a4916 | 540 | |
6f512367 VK |
541 | /* |
542 | ** Cluster resources | |
543 | */ | |
544 | ||
545 | CREATE TABLE cluster_resources | |
546 | ( | |
51ff26ea AK |
547 | cluster_id integer not null, |
548 | resource_id integer not null, | |
549 | resource_name varchar(255), | |
550 | ip_addr varchar(48) not null, | |
551 | current_owner integer not null, | |
552 | PRIMARY KEY(cluster_id,resource_id) | |
6f512367 VK |
553 | ) TABLE_TYPE; |
554 | ||
085ed918 AK |
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 | ||
6c9e7d36 VK |
562 | /* |
563 | ** Subnets | |
564 | */ | |
feea53fd | 565 | CREATE TABLE subnets |
cbcaf8c8 | 566 | ( |
51ff26ea AK |
567 | id integer not null, |
568 | ip_addr varchar(48) not null, | |
569 | ip_netmask integer not null, | |
570 | zone_guid integer not null, | |
571 | synthetic_mask integer not null, | |
572 | PRIMARY KEY(id) | |
cb7ec554 | 573 | ) TABLE_TYPE; |
cbcaf8c8 | 574 | |
085ed918 AK |
575 | COMMENT_TABLE(subnets, '') |
576 | ||
6c9e7d36 VK |
577 | /* |
578 | ** Nodes' interfaces | |
579 | */ | |
feea53fd | 580 | CREATE TABLE interfaces |
cbcaf8c8 | 581 | ( |
51ff26ea AK |
582 | id integer not null, |
583 | node_id integer not null, | |
584 | flags integer not null, | |
585 | if_type integer not null, | |
586 | if_index integer not null, | |
587 | mtu integer not null, | |
588 | speed SQL_INT64 not null, | |
589 | bridge_port integer not null, | |
590 | phy_slot integer not null, | |
591 | phy_port integer not null, | |
592 | peer_node_id integer not null, | |
593 | peer_if_id integer not null, | |
594 | peer_proto integer not null, | |
595 | mac_addr varchar(12) not null, | |
596 | required_polls integer not null, | |
597 | admin_state integer not null, | |
598 | oper_state integer not null, | |
599 | dot1x_pae_state integer not null, | |
600 | dot1x_backend_state integer not null, | |
601 | description varchar(255) null, | |
602 | alias varchar(255) null, | |
603 | iftable_suffix varchar(127) null, | |
604 | PRIMARY KEY(id) | |
cb7ec554 | 605 | ) TABLE_TYPE; |
cbcaf8c8 | 606 | |
085ed918 AK |
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 | ||
c30c0c0f VK |
631 | /* |
632 | ** Interface IP addresses | |
633 | */ | |
634 | CREATE TABLE interface_address_list | |
635 | ( | |
51ff26ea AK |
636 | iface_id integer not null, |
637 | ip_addr varchar(48) not null, | |
638 | ip_netmask integer not null, | |
639 | PRIMARY KEY(iface_id,ip_addr) | |
c30c0c0f | 640 | ) TABLE_TYPE; |
cbcaf8c8 | 641 | |
085ed918 AK |
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 | ||
bebf4833 VK |
647 | /* |
648 | ** Network services | |
649 | */ | |
650 | ||
651 | CREATE TABLE network_services | |
652 | ( | |
51ff26ea AK |
653 | id integer not null, |
654 | node_id integer not null, | |
655 | service_type integer not null, | |
656 | ip_bind_addr varchar(48) not null, | |
657 | ip_proto integer not null, | |
658 | ip_port integer not null, | |
659 | check_request SQL_TEXT null, | |
660 | check_responce SQL_TEXT null, | |
661 | poller_node_id integer not null, | |
662 | required_polls integer not null, | |
663 | PRIMARY KEY(id) | |
bebf4833 VK |
664 | ) TABLE_TYPE; |
665 | ||
085ed918 AK |
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 | ||
a11d8dab VK |
678 | /* |
679 | ** VPN connectors | |
680 | */ | |
a11d8dab VK |
681 | CREATE TABLE vpn_connectors |
682 | ( | |
51ff26ea AK |
683 | id integer not null, |
684 | node_id integer not null, | |
685 | peer_gateway integer not null, | |
686 | PRIMARY KEY(id) | |
a11d8dab VK |
687 | ) TABLE_TYPE; |
688 | ||
085ed918 AK |
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 | ||
a11d8dab VK |
694 | /* |
695 | ** VPN connector networks | |
696 | */ | |
a11d8dab VK |
697 | CREATE TABLE vpn_connector_networks |
698 | ( | |
51ff26ea AK |
699 | vpn_id integer not null, |
700 | network_type integer not null, // 0 == local, 1 == remote | |
701 | ip_addr varchar(48) not null, | |
702 | ip_netmask integer not null, | |
703 | PRIMARY KEY(vpn_id,ip_addr) | |
a11d8dab VK |
704 | ) TABLE_TYPE; |
705 | ||
085ed918 AK |
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 | ||
ef44d5ea VK |
712 | /* |
713 | ** Container objects | |
714 | */ | |
23464115 | 715 | CREATE TABLE object_containers |
ef44d5ea | 716 | ( |
51ff26ea AK |
717 | id integer not null, |
718 | object_class integer not null, | |
719 | flags integer not null, | |
720 | auto_bind_filter SQL_TEXT null, | |
721 | PRIMARY KEY(id) | |
cb7ec554 | 722 | ) TABLE_TYPE; |
ef44d5ea | 723 | |
085ed918 AK |
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 | ||
383b42a1 VK |
730 | /* |
731 | ** Condition objects | |
732 | */ | |
383b42a1 VK |
733 | CREATE TABLE conditions |
734 | ( | |
51ff26ea AK |
735 | id integer not null, |
736 | activation_event integer not null, | |
737 | deactivation_event integer not null, | |
738 | source_object integer not null, | |
739 | active_status integer not null, | |
740 | inactive_status integer not null, | |
741 | script SQL_TEXT not null, | |
742 | PRIMARY KEY(id) | |
383b42a1 VK |
743 | ) TABLE_TYPE; |
744 | ||
085ed918 AK |
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 | ||
383b42a1 VK |
754 | /* |
755 | ** DCI to condition mapping | |
756 | */ | |
383b42a1 VK |
757 | CREATE TABLE cond_dci_map |
758 | ( | |
51ff26ea AK |
759 | condition_id integer not null, |
760 | sequence_number integer not null, | |
761 | dci_id integer not null, | |
762 | node_id integer not null, | |
763 | dci_func integer not null, | |
764 | num_polls integer not null, | |
765 | PRIMARY KEY(condition_id,sequence_number) | |
383b42a1 VK |
766 | ) TABLE_TYPE; |
767 | ||
085ed918 AK |
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 | ||
23464115 VK |
776 | /** |
777 | * Data collection templates | |
778 | */ | |
62d11997 VK |
779 | CREATE TABLE templates |
780 | ( | |
51ff26ea AK |
781 | id integer not null, |
782 | version integer not null, | |
783 | flags integer not null, | |
784 | apply_filter SQL_TEXT null, | |
785 | PRIMARY KEY(id) | |
cb7ec554 | 786 | ) TABLE_TYPE; |
62d11997 | 787 | |
085ed918 AK |
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 | ||
23464115 VK |
794 | /** |
795 | * Mapping hosts to templates | |
796 | */ | |
62d11997 VK |
797 | CREATE TABLE dct_node_map |
798 | ( | |
51ff26ea AK |
799 | template_id integer not null, |
800 | node_id integer not null, | |
801 | PRIMARY KEY(template_id,node_id) | |
cb7ec554 | 802 | ) TABLE_TYPE; |
62d11997 | 803 | |
085ed918 AK |
804 | COMMENT_TABLE(dct_node_map, '') |
805 | COMMENT_COLUMN(dct_node_map.template_id, '') | |
806 | COMMENT_COLUMN(dct_node_map.node_id, '') | |
807 | ||
23464115 | 808 | /** |
3df8bccd | 809 | * Nodes to subnets mapping |
23464115 | 810 | */ |
a713e82e | 811 | CREATE TABLE nsmap |
cbcaf8c8 | 812 | ( |
51ff26ea AK |
813 | subnet_id integer not null, |
814 | node_id integer not null, | |
815 | PRIMARY KEY(subnet_id,node_id) | |
cb7ec554 | 816 | ) TABLE_TYPE; |
cbcaf8c8 | 817 | |
085ed918 AK |
818 | COMMENT_TABLE(nsmap, '') |
819 | COMMENT_COLUMN(nsmap.subnet_id, '') | |
820 | COMMENT_COLUMN(nsmap.node_id, '') | |
821 | ||
23464115 | 822 | /** |
3df8bccd | 823 | * Container members |
23464115 | 824 | */ |
ef44d5ea VK |
825 | CREATE TABLE container_members |
826 | ( | |
51ff26ea AK |
827 | container_id integer not null, |
828 | object_id integer not null, | |
829 | PRIMARY KEY(container_id,object_id) | |
cb7ec554 | 830 | ) TABLE_TYPE; |
ef44d5ea | 831 | |
085ed918 AK |
832 | COMMENT_TABLE(container_members, '') |
833 | COMMENT_COLUMN(container_members.container_id, '') | |
834 | COMMENT_COLUMN(container_members.object_id, '') | |
835 | ||
23464115 VK |
836 | /** |
837 | * Objects' ACLs | |
838 | */ | |
feea53fd | 839 | CREATE TABLE acl |
f806ed94 | 840 | ( |
51ff26ea AK |
841 | object_id integer not null, |
842 | user_id integer not null, | |
843 | access_rights integer not null, | |
844 | PRIMARY KEY(object_id,user_id) | |
cb7ec554 | 845 | ) TABLE_TYPE; |
f806ed94 | 846 | |
085ed918 AK |
847 | COMMENT_TABLE(acl, '') |
848 | COMMENT_COLUMN(acl.object_id, '') | |
849 | COMMENT_COLUMN(acl.user_id, '') | |
850 | COMMENT_COLUMN(acl.access_rights, '') | |
851 | ||
23464115 VK |
852 | /** |
853 | * Trusted nodes - used for cross-node data collection | |
854 | * Source object is an object providing data (it can be node or condition), | |
855 | * and target node is a node owning DCI | |
856 | */ | |
d51ccc0d VK |
857 | CREATE TABLE trusted_nodes |
858 | ( | |
51ff26ea AK |
859 | source_object_id integer not null, |
860 | target_node_id integer not null, | |
861 | PRIMARY KEY(source_object_id,target_node_id) | |
d51ccc0d VK |
862 | ) TABLE_TYPE; |
863 | ||
085ed918 AK |
864 | COMMENT_TABLE(trusted_nodes, '') |
865 | COMMENT_COLUMN(trusted_nodes.source_object_id, '') | |
866 | COMMENT_COLUMN(trusted_nodes.target_node_id, '') | |
867 | ||
23464115 VK |
868 | /** |
869 | * Data collection items | |
870 | * | |
51ff26ea | 871 | * If node_id != 0, it's an item bound to node, and template_id points to |
23464115 VK |
872 | * the template used for creating this item. In this case, template_id = 0 |
873 | * means that item was created manually. | |
874 | * If node_id = 0, it's a template item, and template_id points to a template | |
875 | * this item belongs to. | |
876 | * If both node_id and template_id is 0, it's an error. | |
877 | */ | |
feea53fd | 878 | CREATE TABLE items |
cbcaf8c8 | 879 | ( |
51ff26ea AK |
880 | item_id integer not null, |
881 | node_id integer not null, | |
882 | template_id integer not null, | |
883 | template_item_id integer not null, | |
884 | guid varchar(36) not null, | |
885 | name varchar(1023) null, | |
886 | description varchar(255) null, | |
887 | flags integer not null, | |
888 | source integer not null, // 0 for internal or 1 for native agent or 2 for SNMP | |
889 | snmp_port integer not null, | |
890 | datatype integer not null, | |
891 | polling_interval integer not null, | |
892 | retention_time integer not null, | |
893 | status integer not null, // ACTIVE, DISABLED or NOT_SUPPORTED | |
894 | snmp_raw_value_type integer not null, | |
895 | delta_calculation integer not null, | |
896 | transformation SQL_TEXT, // Transformation script | |
897 | instance varchar(255) null, // Free form text which can be used in events | |
898 | system_tag varchar(255) null, // System tag | |
899 | resource_id integer not null, // associated cluster resource ID | |
900 | proxy_node integer not null, // ID of proxy node (for SNMP and agent items) | |
901 | base_units integer not null, // bytes, seconds, etc. | |
902 | unit_multiplier integer not null, // kilo, mega, milli, etc. | |
903 | custom_units_name varchar(63) null, // units name of base_units = CUSTOM | |
904 | perftab_settings SQL_TEXT null, // Settings for displaying graph on performance tab | |
905 | instd_method integer not null, | |
906 | instd_data varchar(255) null, | |
907 | instd_filter SQL_TEXT null, | |
908 | samples integer not null, | |
909 | comments SQL_TEXT null, | |
910 | PRIMARY KEY(item_id) | |
cb7ec554 | 911 | ) TABLE_TYPE; |
cbcaf8c8 | 912 | |
b06436f4 VK |
913 | CREATE INDEX idx_items_node_id ON items(node_id); |
914 | ||
085ed918 AK |
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 | ||
cc8ce218 VK |
947 | /* |
948 | ** Data collection tables | |
949 | */ | |
cc8ce218 VK |
950 | CREATE TABLE dc_tables |
951 | ( | |
51ff26ea AK |
952 | item_id integer not null, |
953 | node_id integer not null, | |
954 | template_id integer not null, | |
955 | template_item_id integer not null, | |
956 | guid varchar(36) not null, | |
957 | name varchar(1023) null, | |
958 | description varchar(255) null, | |
959 | flags integer not null, | |
960 | source integer not null, // 0 for internal or 1 for native agent or 2 for SNMP | |
961 | snmp_port integer not null, | |
962 | polling_interval integer not null, | |
963 | retention_time integer not null, | |
964 | status integer not null, // ACTIVE, DISABLED or NOT_SUPPORTED | |
965 | system_tag varchar(255) null, // System tag | |
966 | resource_id integer not null, // associated cluster resource ID | |
967 | proxy_node integer not null, // ID of proxy node (for SNMP and agent items) | |
968 | perftab_settings SQL_TEXT null, // Settings for displaying graph on performance tab | |
969 | transformation_script SQL_TEXT null, | |
970 | comments SQL_TEXT null, | |
971 | PRIMARY KEY(item_id) | |
cc8ce218 VK |
972 | ) TABLE_TYPE; |
973 | ||
b06436f4 VK |
974 | CREATE INDEX idx_dc_tables_node_id ON dc_tables(node_id); |
975 | ||
085ed918 AK |
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 | ||
cc8ce218 VK |
997 | /* |
998 | ** Columns for data collection tables | |
999 | */ | |
cc8ce218 VK |
1000 | CREATE TABLE dc_table_columns |
1001 | ( | |
51ff26ea AK |
1002 | table_id integer not null, |
1003 | sequence_number integer not null, | |
1004 | column_name varchar(63) not null, | |
1005 | snmp_oid varchar(1023) null, // SNMP OID for this column, valid only for SNMP tables | |
1006 | flags integer not null, | |
1007 | display_name varchar(255) null, | |
1008 | PRIMARY KEY(table_id,column_name) | |
cc8ce218 VK |
1009 | ) TABLE_TYPE; |
1010 | ||
085ed918 AK |
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 | ||
d6124fa0 VK |
1019 | /* |
1020 | ** Column name dictionary | |
1021 | */ | |
d6124fa0 VK |
1022 | CREATE TABLE dct_column_names |
1023 | ( | |
51ff26ea AK |
1024 | column_id integer not null, |
1025 | column_name varchar(63) not null, | |
1026 | PRIMARY KEY(column_id) | |
d6124fa0 VK |
1027 | ) TABLE_TYPE; |
1028 | ||
085ed918 AK |
1029 | COMMENT_TABLE(dct_column_names, '') |
1030 | COMMENT_COLUMN(dct_column_names.column_id, '') | |
1031 | COMMENT_COLUMN(dct_column_names.column_name, '') | |
1032 | ||
9098ad59 VK |
1033 | /* |
1034 | ** Table thresholds | |
1035 | */ | |
1036 | CREATE TABLE dct_thresholds | |
1037 | ( | |
51ff26ea AK |
1038 | id integer not null, |
1039 | table_id integer not null, | |
1040 | sequence_number integer not null, | |
1041 | activation_event integer not null, | |
1042 | deactivation_event integer not null, | |
1043 | PRIMARY KEY(id) | |
9098ad59 VK |
1044 | ) TABLE_TYPE; |
1045 | ||
085ed918 AK |
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 | ||
9098ad59 VK |
1053 | /* |
1054 | ** Table threshold conditions | |
1055 | */ | |
1056 | CREATE TABLE dct_threshold_conditions | |
1057 | ( | |
51ff26ea AK |
1058 | threshold_id integer not null, |
1059 | group_id integer not null, | |
1060 | sequence_number integer not null, | |
1061 | column_name varchar(63) null, | |
1062 | check_operation integer not null, | |
1063 | check_value varchar(255) null, | |
1064 | PRIMARY KEY(threshold_id,group_id,sequence_number) | |
9098ad59 VK |
1065 | ) TABLE_TYPE; |
1066 | ||
085ed918 AK |
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 | ||
b9ce1c9d VK |
1075 | /* |
1076 | ** Schedules for DCIs | |
1077 | */ | |
b9ce1c9d VK |
1078 | CREATE TABLE dci_schedules |
1079 | ( | |
51ff26ea AK |
1080 | schedule_id integer not null, |
1081 | item_id integer not null, | |
1082 | schedule varchar(255) null, | |
1083 | PRIMARY KEY(item_id,schedule_id) | |
b9ce1c9d VK |
1084 | ) TABLE_TYPE; |
1085 | ||
085ed918 AK |
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 | ||
57a5e132 VK |
1091 | /* |
1092 | ** Latest raw values for all data collection items | |
1093 | */ | |
57a5e132 VK |
1094 | CREATE TABLE raw_dci_values |
1095 | ( | |
51ff26ea AK |
1096 | item_id integer not null, |
1097 | raw_value varchar(255) null, | |
1098 | transformed_value varchar(255) null, | |
1099 | last_poll_time integer not null, | |
1100 | PRIMARY KEY(item_id) | |
57a5e132 VK |
1101 | ) TABLE_TYPE; |
1102 | ||
7b61f11f | 1103 | #if !defined(DB_ORACLE) && !defined(DB_DB2) && !defined(DB_POSTGRESQL) |
c13cb4a5 | 1104 | CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id); |
fa3f5211 | 1105 | #endif |
57a5e132 | 1106 | |
085ed918 AK |
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 | ||
6c9e7d36 VK |
1113 | /* |
1114 | ** Events configuration | |
1115 | */ | |
0c6014e4 | 1116 | CREATE TABLE event_cfg |
cbcaf8c8 | 1117 | ( |
51ff26ea AK |
1118 | event_code integer not null, |
1119 | event_name varchar(63) not null, // Short event name | |
50963ced | 1120 | guid varchar(36) not null, |
51ff26ea AK |
1121 | severity integer not null, |
1122 | flags integer not null, | |
1123 | message varchar(2000) null, /* Message template */ | |
1124 | description SQL_TEXT null, | |
1125 | PRIMARY KEY(event_code) | |
cb7ec554 | 1126 | ) TABLE_TYPE; |
cbcaf8c8 | 1127 | |
50963ced VK |
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') | |
085ed918 | 1136 | |
6c9e7d36 VK |
1137 | /* |
1138 | ** Event log | |
1139 | */ | |
feea53fd | 1140 | CREATE TABLE event_log |
cbcaf8c8 | 1141 | ( |
51ff26ea AK |
1142 | event_id SQL_INT64 not null, |
1143 | event_code integer not null, | |
1144 | event_timestamp integer not null, | |
1145 | event_source integer not null, /* Source object ID */ | |
1146 | dci_id integer not null, /* ID of related DCI or 0 */ | |
1147 | event_severity integer not null, | |
1148 | event_message varchar(2000) null, | |
1149 | root_event_id SQL_INT64 not null, /* Non-zero if current event correlates to some other event */ | |
1150 | user_tag varchar(63) null, | |
1151 | PRIMARY KEY(event_id) | |
cb7ec554 | 1152 | ) TABLE_TYPE; |
cbcaf8c8 | 1153 | |
30639d32 VK |
1154 | CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp); |
1155 | ||
ed0a9e41 VK |
1156 | CREATE INDEX idx_event_log_source ON event_log(event_source); |
1157 | ||
40094b47 | 1158 | #if defined(DB_POSTGRESQL) |
69bb7f47 VK |
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 | |
cbcaf8c8 | 1176 | |
085ed918 AK |
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 | ||
6c9e7d36 VK |
1188 | /* |
1189 | ** Actions on events | |
1190 | */ | |
feea53fd | 1191 | CREATE TABLE actions |
cbcaf8c8 | 1192 | ( |
51ff26ea AK |
1193 | action_id integer not null, |
1194 | action_name varchar(63) not null, | |
1195 | action_type integer not null, | |
1196 | is_disabled integer not null, | |
1197 | // Field "rcpt_addr" holds e-mail address for e-mail actions, | |
1198 | // phone number for sms actions, and remote host address for | |
1199 | // remote execution actions | |
1200 | rcpt_addr varchar(255) null, | |
1201 | email_subject varchar(255) null, | |
1202 | // Field "action_data" holds message text for e-mail and sms actions, | |
1203 | // command line for external command execution actions, or | |
1204 | // action name with optional arguments for remote execution actions | |
1205 | action_data SQL_TEXT null, | |
1206 | PRIMARY KEY(action_id) | |
cb7ec554 | 1207 | ) TABLE_TYPE; |
69cc295f | 1208 | |
085ed918 AK |
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 | ||
6c9e7d36 VK |
1218 | /* |
1219 | ** Event groups | |
1220 | */ | |
feea53fd | 1221 | CREATE TABLE event_groups |
69cc295f | 1222 | ( |
51ff26ea AK |
1223 | id integer not null, |
1224 | name varchar(63) not null, | |
1225 | description varchar(255) not null, | |
1226 | range_start integer not null, | |
1227 | range_end integer not null, | |
1228 | PRIMARY KEY(id) | |
cb7ec554 | 1229 | ) TABLE_TYPE; |
69cc295f | 1230 | |
085ed918 AK |
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 | ||
6c9e7d36 VK |
1238 | /* |
1239 | ** Event group members | |
1240 | */ | |
feea53fd | 1241 | CREATE TABLE event_group_members |
69cc295f | 1242 | ( |
51ff26ea AK |
1243 | group_id integer not null, |
1244 | event_code integer not null, | |
1245 | PRIMARY KEY(group_id,event_code) | |
cb7ec554 | 1246 | ) TABLE_TYPE; |
69cc295f | 1247 | |
085ed918 AK |
1248 | COMMENT_TABLE(event_group_members, '') |
1249 | COMMENT_COLUMN(event_group_members.group_id, '') | |
1250 | COMMENT_COLUMN(event_group_members.event_code, '') | |
1251 | ||
6c9e7d36 VK |
1252 | /* |
1253 | ** Event processing policy | |
1254 | */ | |
feea53fd | 1255 | CREATE TABLE event_policy |
69cc295f | 1256 | ( |
51ff26ea AK |
1257 | rule_id integer not null, // Rule number |
1258 | rule_guid varchar(36) not null, | |
1259 | flags integer not null, | |
1260 | comments SQL_TEXT null, | |
1261 | script SQL_TEXT null, | |
1262 | alarm_message varchar(2000) null, | |
1263 | alarm_severity integer not null, | |
1264 | alarm_key varchar(255) null, // Alarm key (used for auto termination) | |
1265 | alarm_timeout integer not null, // Timeout before sending event | |
1266 | alarm_timeout_event integer not null, // Event to be sent on timeout | |
1267 | situation_id integer not null, // Situation to update | |
1268 | situation_instance varchar(255) null, | |
1269 | PRIMARY KEY(rule_id) | |
cb7ec554 | 1270 | ) TABLE_TYPE; |
69cc295f | 1271 | |
085ed918 AK |
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 | */ | |
feea53fd | 1289 | CREATE TABLE policy_source_list |
69cc295f | 1290 | ( |
51ff26ea AK |
1291 | rule_id integer not null, |
1292 | object_id integer not null, | |
1293 | PRIMARY KEY(rule_id,object_id) | |
cb7ec554 | 1294 | ) TABLE_TYPE; |
69cc295f | 1295 | |
085ed918 AK |
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 | */ | |
feea53fd | 1303 | CREATE TABLE policy_event_list |
69cc295f | 1304 | ( |
51ff26ea AK |
1305 | rule_id integer not null, |
1306 | event_code integer not null, | |
1307 | PRIMARY KEY(rule_id,event_code) | |
cb7ec554 | 1308 | ) TABLE_TYPE; |
b6a77d6d | 1309 | |
085ed918 AK |
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 | */ | |
feea53fd | 1317 | CREATE TABLE policy_action_list |
b6a77d6d | 1318 | ( |
51ff26ea AK |
1319 | rule_id integer not null, |
1320 | action_id integer not null, | |
1321 | PRIMARY KEY(rule_id,action_id) | |
cb7ec554 | 1322 | ) TABLE_TYPE; |
54f0de5b | 1323 | |
085ed918 AK |
1324 | COMMENT_TABLE(policy_action_list, '') |
1325 | COMMENT_COLUMN(policy_action_list.rule_id, '') | |
1326 | COMMENT_COLUMN(policy_action_list.action_id, '') | |
1327 | ||
085ed918 AK |
1328 | /** |
1329 | * | |
1330 | */ | |
b13fd0b7 VK |
1331 | CREATE TABLE policy_situation_attr_list |
1332 | ( | |
51ff26ea AK |
1333 | rule_id integer not null, |
1334 | situation_id integer not null, | |
1335 | attr_name varchar(255) not null, | |
1336 | attr_value varchar(255) null, | |
1337 | PRIMARY KEY(rule_id,situation_id,attr_name) | |
b13fd0b7 VK |
1338 | ) TABLE_TYPE; |
1339 | ||
085ed918 AK |
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, '') | |
54f0de5b | 1345 | |
59f88625 VK |
1346 | /* |
1347 | ** Threshold checking rules | |
1348 | */ | |
feea53fd | 1349 | CREATE TABLE thresholds |
59f88625 | 1350 | ( |
51ff26ea AK |
1351 | threshold_id integer not null, |
1352 | item_id integer not null, | |
1353 | sequence_number integer not null, | |
1354 | fire_value varchar(255) null, | |
1355 | rearm_value varchar(255) null, | |
1356 | check_function integer not null, | |
1357 | check_operation integer not null, | |
1358 | sample_count integer not null, | |
1359 | script SQL_TEXT null, | |
1360 | event_code integer not null, | |
1361 | rearm_event_code integer not null, | |
1362 | repeat_interval integer not null, | |
1363 | current_state integer not null, | |
1364 | current_severity integer not null, | |
1365 | match_count integer not null, | |
1366 | last_event_timestamp integer not null, | |
1367 | PRIMARY KEY(threshold_id) | |
cb7ec554 | 1368 | ) TABLE_TYPE; |
2fa71464 | 1369 | |
c13cb4a5 AK |
1370 | CREATE INDEX idx_thresholds_item_id ON thresholds(item_id); |
1371 | CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number); | |
505ca1ae | 1372 | |
085ed918 AK |
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 | ||
59a21a78 VK |
1391 | /* |
1392 | ** Alarms | |
1393 | */ | |
59a21a78 VK |
1394 | CREATE TABLE alarms |
1395 | ( | |
51ff26ea AK |
1396 | alarm_id integer not null, // Unique alarm identifier |
1397 | alarm_state integer not null, | |
1398 | hd_state integer not null, // Help desk system state | |
1399 | hd_ref varchar(63) null, // Help desk reference | |
1400 | creation_time integer not null, | |
1401 | last_change_time integer not null, | |
1402 | source_object_id integer not null, | |
1403 | source_event_code integer not null, | |
1404 | source_event_id SQL_INT64 not null, | |
1405 | dci_id integer not null, | |
1406 | message varchar(2000) null, | |
1407 | original_severity integer not null, | |
1408 | current_severity integer not null, | |
1409 | repeat_count integer not null, | |
1410 | alarm_key varchar(255) null, // Alarm key (used for auto acknowlegment) | |
1411 | ack_by integer not null, // ID of user who was acknowleged alarm | |
1412 | resolved_by integer not null, // ID of user who was resolved alarm | |
1413 | term_by integer not null, // ID of user who was terminated alarm | |
1414 | timeout integer not null, | |
1415 | timeout_event integer not null, | |
1416 | ack_timeout integer not null, | |
1417 | PRIMARY KEY(alarm_id) | |
cb7ec554 | 1418 | ) TABLE_TYPE; |
59a21a78 | 1419 | |
085ed918 AK |
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 | ||
59a21a78 VK |
1443 | /* |
1444 | ** Alarm notes | |
1445 | */ | |
59a21a78 VK |
1446 | CREATE TABLE alarm_notes |
1447 | ( | |
51ff26ea AK |
1448 | note_id integer not null, |
1449 | alarm_id integer not null, | |
1450 | change_time integer not null, | |
1451 | user_id integer not null, // Last edited by | |
1452 | note_text SQL_TEXT null, | |
1453 | PRIMARY KEY(note_id) | |
cb7ec554 | 1454 | ) TABLE_TYPE; |
917aa2e6 | 1455 | |
c5131765 VK |
1456 | CREATE INDEX idx_alarm_notes_alarm_id ON alarm_notes(alarm_id); |
1457 | ||
085ed918 AK |
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 | ||
b1e9b6b3 VK |
1465 | /* |
1466 | ** Source events for alarms | |
1467 | */ | |
b1e9b6b3 VK |
1468 | CREATE TABLE alarm_events |
1469 | ( | |
51ff26ea AK |
1470 | alarm_id integer not null, |
1471 | event_id SQL_INT64 not null, | |
1472 | event_code integer not null, | |
1473 | event_name varchar(63) null, | |
1474 | severity integer not null, | |
1475 | source_object_id integer not null, | |
1476 | event_timestamp integer not null, | |
1477 | message varchar(2000) null, | |
1478 | PRIMARY KEY(alarm_id,event_id) | |
b1e9b6b3 VK |
1479 | ) TABLE_TYPE; |
1480 | ||
1481 | CREATE INDEX idx_alarm_events_alarm_id ON alarm_events(alarm_id); | |
1482 | ||
085ed918 AK |
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, '') | |
b1e9b6b3 | 1492 | |
3aeed82c VK |
1493 | /* |
1494 | ** SNMP trap configuration | |
1495 | */ | |
1496 | ||
1497 | CREATE TABLE snmp_trap_cfg | |
1498 | ( | |
51ff26ea AK |
1499 | trap_id integer not null, |
1500 | snmp_oid varchar(255), | |
1501 | event_code integer not null, | |
1502 | user_tag varchar(63), | |
1503 | description varchar(255), | |
1504 | PRIMARY KEY(trap_id) | |
cb7ec554 | 1505 | ) TABLE_TYPE; |
3aeed82c | 1506 | |
085ed918 AK |
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, '') | |
3aeed82c VK |
1513 | |
1514 | /* | |
1515 | ** SNMP trap parameters mapping | |
1516 | */ | |
3aeed82c VK |
1517 | CREATE TABLE snmp_trap_pmap |
1518 | ( | |
51ff26ea AK |
1519 | trap_id integer not null, |
1520 | parameter integer not null, | |
1521 | flags integer not null, | |
1522 | snmp_oid varchar(255) null, | |
1523 | description varchar(255) null, | |
1524 | PRIMARY KEY(trap_id,parameter) | |
cb7ec554 | 1525 | ) TABLE_TYPE; |
6726bfdc | 1526 | |
085ed918 AK |
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 | ||
6726bfdc VK |
1534 | /* |
1535 | ** Agent packages | |
1536 | */ | |
6726bfdc VK |
1537 | CREATE TABLE agent_pkg |
1538 | ( | |
51ff26ea AK |
1539 | pkg_id integer not null, |
1540 | pkg_name varchar(63), | |
1541 | version varchar(31), | |
1542 | platform varchar(63), | |
1543 | pkg_file varchar(255), | |
1544 | description varchar(255), | |
1545 | PRIMARY KEY(pkg_id) | |
cb7ec554 | 1546 | ) TABLE_TYPE; |
b7a391d7 | 1547 | |
085ed918 AK |
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 | ||
b7a391d7 VK |
1556 | /* |
1557 | ** Object tools | |
1558 | */ | |
b7a391d7 VK |
1559 | CREATE TABLE object_tools |
1560 | ( | |
51ff26ea AK |
1561 | tool_id integer not null, |
1562 | guid varchar(36) not null, | |
1563 | tool_name varchar(255) null, | |
1564 | tool_type integer not null, | |
1565 | tool_data SQL_TEXT null, | |
1566 | description varchar(255) null, | |
1567 | flags integer not null, | |
1568 | tool_filter SQL_TEXT null, | |
1569 | confirmation_text varchar(255) null, | |
1570 | command_name varchar(255) null, | |
1571 | command_short_name varchar(31) null, | |
1572 | icon SQL_TEXT null, | |
1573 | PRIMARY KEY(tool_id) | |
b7a391d7 VK |
1574 | ) TABLE_TYPE; |
1575 | ||
085ed918 AK |
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 | ||
b7a391d7 VK |
1590 | /* |
1591 | ** Access list for object tools | |
1592 | */ | |
b7a391d7 VK |
1593 | CREATE TABLE object_tools_acl |
1594 | ( | |
51ff26ea AK |
1595 | tool_id integer not null, |
1596 | user_id integer not null, | |
1597 | PRIMARY KEY(tool_id,user_id) | |
b7a391d7 | 1598 | ) TABLE_TYPE; |
02108d74 | 1599 | |
085ed918 AK |
1600 | COMMENT_TABLE(object_tools_acl, '') |
1601 | COMMENT_COLUMN(object_tools_acl.tool_id, '') | |
1602 | COMMENT_COLUMN(object_tools_acl.user_id, '') | |
1603 | ||
02108d74 | 1604 | /* |
461b4074 | 1605 | ** Configuration of columns for SNMP_TABLE and AGENT_TABLE type of object tools |
02108d74 | 1606 | */ |
461b4074 | 1607 | CREATE TABLE object_tools_table_columns |
02108d74 | 1608 | ( |
51ff26ea AK |
1609 | tool_id integer not null, |
1610 | col_number integer not null, | |
1611 | col_name varchar(255) null, | |
1612 | col_oid varchar(255) null, | |
1613 | col_format integer, // Column format (integer, string, etc.) | |
1614 | col_substr integer, // Number of matching substring in regexp | |
1615 | PRIMARY KEY(tool_id,col_number) | |
02108d74 | 1616 | ) TABLE_TYPE; |
46ef501a | 1617 | |
085ed918 AK |
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 | ||
b576249a VK |
1626 | /** |
1627 | * Input fields for object tools | |
1628 | */ | |
1629 | CREATE TABLE object_tools_input_fields | |
1630 | ( | |
51ff26ea AK |
1631 | tool_id integer not null, |
1632 | name varchar(31) not null, | |
1633 | input_type char(1) not null, | |
1634 | display_name varchar(127) null, | |
1635 | sequence_num integer not null, | |
1636 | config SQL_TEXT null, | |
1637 | PRIMARY KEY(tool_id,name) | |
b576249a VK |
1638 | ) TABLE_TYPE; |
1639 | ||
085ed918 AK |
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 | ||
c4096dd8 VK |
1648 | /* |
1649 | ** Stored syslog messages | |
1650 | */ | |
c4096dd8 VK |
1651 | CREATE TABLE syslog |
1652 | ( | |
51ff26ea AK |
1653 | msg_id SQL_INT64 not null, |
1654 | msg_timestamp integer not null, | |
1655 | facility integer not null, | |
1656 | severity integer not null, | |
1657 | source_object_id integer not null, | |
1658 | hostname varchar(127) null, | |
1659 | msg_tag varchar(32) null, | |
1660 | msg_text SQL_TEXT null, | |
1661 | PRIMARY KEY(msg_id) | |
c4096dd8 VK |
1662 | ) TABLE_TYPE; |
1663 | ||
30639d32 VK |
1664 | CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp); |
1665 | ||
085ed918 AK |
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, '') | |
c4096dd8 | 1675 | |
5c6b881b VK |
1676 | /* |
1677 | ** Script library | |
1678 | */ | |
5c6b881b VK |
1679 | CREATE TABLE script_library |
1680 | ( | |
51ff26ea AK |
1681 | script_id integer not null, |
1682 | script_name varchar(255) not null, | |
1683 | script_code SQL_TEXT null, | |
1684 | PRIMARY KEY(script_id) | |
5c6b881b VK |
1685 | ) TABLE_TYPE; |
1686 | ||
085ed918 AK |
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, '') | |
5c6b881b | 1691 | |
56b32598 VK |
1692 | /* |
1693 | ** Extended SNMP trap log | |
1694 | */ | |
1695 | ||
1696 | CREATE TABLE snmp_trap_log | |
1697 | ( | |
51ff26ea AK |
1698 | trap_id SQL_INT64 not null, |
1699 | trap_timestamp integer not null, | |
1700 | ip_addr varchar(48) not null, | |
1701 | object_id integer not null, | |
1702 | trap_oid varchar(255) not null, | |
1703 | trap_varlist SQL_TEXT null, | |
1704 | PRIMARY KEY(trap_id) | |
56b32598 VK |
1705 | ) TABLE_TYPE; |
1706 | ||
bf4a58d4 | 1707 | CREATE INDEX idx_snmp_trap_log_tt ON snmp_trap_log(trap_timestamp); |
30639d32 | 1708 | |
085ed918 AK |
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 | ||
d2d7327c VK |
1717 | /* |
1718 | ** Agent configurations | |
1719 | */ | |
d2d7327c VK |
1720 | CREATE TABLE agent_configs |
1721 | ( | |
51ff26ea AK |
1722 | config_id integer not null, |
1723 | config_name varchar(255) not null, | |
1724 | config_file SQL_TEXT not null, | |
1725 | config_filter SQL_TEXT not null, | |
1726 | sequence_number integer not null, | |
1727 | PRIMARY KEY(config_id) | |
d2d7327c | 1728 | ) TABLE_TYPE; |
e2ce7f94 | 1729 | |
085ed918 AK |
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 | ||
e2ce7f94 VK |
1737 | /* |
1738 | ** Address lists | |
1739 | */ | |
e2ce7f94 VK |
1740 | CREATE TABLE address_lists |
1741 | ( | |
51ff26ea AK |
1742 | list_type integer not null, // discovery filter, etc. |
1743 | community_id integer not null, // community id for snmp community addr list, otherwise 0 | |
1744 | addr_type integer not null, // 0 - addr/mask, 1 - address range | |
1745 | addr1 varchar(48) not null, | |
1746 | addr2 varchar(48) not null, | |
1747 | PRIMARY KEY(list_type,community_id,addr_type,addr1,addr2) | |
e2ce7f94 VK |
1748 | ) TABLE_TYPE; |
1749 | ||
1750 | CREATE INDEX idx_address_lists_list_type ON address_lists(list_type); | |
381ed729 | 1751 | |
085ed918 AK |
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 | ||
381ed729 VK |
1759 | /* |
1760 | ** Graphs | |
1761 | */ | |
381ed729 VK |
1762 | CREATE TABLE graphs |
1763 | ( | |
51ff26ea AK |
1764 | graph_id integer not null, |
1765 | owner_id integer not null, | |
1766 | flags integer not null, | |
1767 | name varchar(255) not null, | |
17b6b6a4 VK |
1768 | config SQL_TEXT null, |
1769 | filters SQL_TEXT null, | |
51ff26ea | 1770 | PRIMARY KEY(graph_id) |
381ed729 VK |
1771 | ) TABLE_TYPE; |
1772 | ||
085ed918 AK |
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 | ||
381ed729 VK |
1781 | /* |
1782 | ** Graph access lists | |
1783 | */ | |
381ed729 VK |
1784 | CREATE TABLE graph_acl |
1785 | ( | |
51ff26ea AK |
1786 | graph_id integer not null, |
1787 | user_id integer not null, | |
1788 | user_rights integer not null, | |
1789 | PRIMARY KEY(graph_id,user_id) | |
381ed729 | 1790 | ) TABLE_TYPE; |
3d1058ed | 1791 | |
085ed918 AK |
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, '') | |
3d1058ed VK |
1796 | |
1797 | /* | |
1798 | ** Certificates | |
1799 | */ | |
1800 | ||
1801 | CREATE TABLE certificates | |
1802 | ( | |
51ff26ea AK |
1803 | cert_id integer not null, |
1804 | cert_type integer not null, | |
1805 | cert_data SQL_TEXT not null, // Certificate in PEM format | |
1806 | subject SQL_TEXT not null, | |
1807 | comments SQL_TEXT not null, | |
1808 | PRIMARY KEY(cert_id) | |
3d1058ed | 1809 | ) TABLE_TYPE; |
fa585702 | 1810 | |
085ed918 AK |
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 | ||
889841e1 VK |
1818 | /* |
1819 | ** Audit log | |
1820 | */ | |
889841e1 VK |
1821 | CREATE TABLE audit_log |
1822 | ( | |
51ff26ea AK |
1823 | record_id integer not null, |
1824 | timestamp integer not null, | |
1825 | subsystem varchar(32) not null, | |
1826 | success integer not null, | |
1827 | user_id integer not null, // 0x7FFFFFFF for system | |
1828 | workstation varchar(63) not null, | |
1829 | session_id integer not null, | |
1830 | object_id integer not null, | |
1831 | message SQL_TEXT null, | |
1832 | PRIMARY KEY(record_id) | |
889841e1 | 1833 | ) TABLE_TYPE; |
4ee86f55 | 1834 | |
085ed918 AK |
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 | ||
4ee86f55 VK |
1846 | /* |
1847 | ** Situations | |
1848 | */ | |
4ee86f55 VK |
1849 | CREATE TABLE situations |
1850 | ( | |
51ff26ea AK |
1851 | id integer not null, |
1852 | name varchar(127) not null, | |
1853 | comments SQL_TEXT not null, | |
1854 | PRIMARY KEY(id) | |
4ee86f55 VK |
1855 | ) TABLE_TYPE; |
1856 | ||
085ed918 AK |
1857 | COMMENT_TABLE(situations, '') |
1858 | COMMENT_COLUMN(situations.id, '') | |
1859 | COMMENT_COLUMN(situations.name, '') | |
1860 | COMMENT_COLUMN(situations.comments, '') | |
4ee86f55 | 1861 | |
023779b8 VK |
1862 | /* |
1863 | ** List of possible community strings | |
1864 | */ | |
023779b8 VK |
1865 | CREATE TABLE snmp_communities |
1866 | ( | |
51ff26ea AK |
1867 | id integer not null, |
1868 | community varchar(255) null, | |
1869 | PRIMARY KEY(id) | |
023779b8 | 1870 | ) TABLE_TYPE; |
a1236e96 | 1871 | |
085ed918 AK |
1872 | COMMENT_TABLE(snmp_communities, '') |
1873 | COMMENT_COLUMN(snmp_communities.id, '') | |
1874 | COMMENT_COLUMN(snmp_communities.community, '') | |
a1236e96 | 1875 | |
f2bb4aa1 VK |
1876 | /* |
1877 | ** Agent policies - common attributes | |
1878 | */ | |
f2bb4aa1 VK |
1879 | CREATE TABLE ap_common |
1880 | ( | |
51ff26ea AK |
1881 | id integer not null, |
1882 | policy_type integer not null, | |
1883 | version integer not null, | |
1884 | PRIMARY KEY(id) | |
f2bb4aa1 VK |
1885 | ) TABLE_TYPE; |
1886 | ||
3df8bccd Z |
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') | |
085ed918 | 1891 | |
f2bb4aa1 VK |
1892 | /* |
1893 | ** Agent policies - node binding | |
1894 | */ | |
f2bb4aa1 VK |
1895 | CREATE TABLE ap_bindings |
1896 | ( | |
51ff26ea AK |
1897 | policy_id integer not null, |
1898 | node_id integer not null, | |
1899 | PRIMARY KEY(policy_id,node_id) | |
f2bb4aa1 VK |
1900 | ) TABLE_TYPE; |
1901 | ||
3df8bccd Z |
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') | |
085ed918 | 1905 | |
f2bb4aa1 VK |
1906 | /* |
1907 | ** Agent policies - configuration files | |
1908 | */ | |
f2bb4aa1 VK |
1909 | CREATE TABLE ap_config_files |
1910 | ( | |
51ff26ea AK |
1911 | policy_id integer not null, |
1912 | file_content SQL_TEXT null, | |
1913 | PRIMARY KEY(policy_id) | |
f2bb4aa1 | 1914 | ) TABLE_TYPE; |
df8a4ca2 | 1915 | |
3df8bccd Z |
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') | |
085ed918 | 1933 | |
df8a4ca2 VK |
1934 | /* |
1935 | ** Default SNMP v3 USM credentials | |
1936 | */ | |
df8a4ca2 VK |
1937 | CREATE TABLE usm_credentials |
1938 | ( | |
51ff26ea AK |
1939 | id integer not null, |
1940 | user_name varchar(255) not null, | |
1941 | auth_method integer not null, | |
1942 | priv_method integer not null, | |
1943 | auth_password varchar(255), | |
1944 | priv_password varchar(255), | |
1945 | PRIMARY KEY(id) | |
df8a4ca2 | 1946 | ) TABLE_TYPE; |
021dcda7 | 1947 | |
3df8bccd Z |
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') | |
085ed918 | 1955 | |
021dcda7 VK |
1956 | /* |
1957 | ** Network maps | |
1958 | */ | |
1959 | CREATE TABLE network_maps | |
1960 | ( | |
51ff26ea AK |
1961 | id integer not null, |
1962 | map_type integer not null, | |
1963 | layout integer not null, | |
1964 | seed integer not null, | |
1965 | radius integer not null, | |
1966 | background varchar(36) null, | |
1967 | bg_latitude varchar(20) null, | |
1968 | bg_longitude varchar(20) null, | |
1969 | bg_zoom integer null, | |
1970 | flags integer not null, | |
1971 | bg_color integer not null, | |
1972 | link_color integer not null, | |
1973 | link_routing integer not null, | |
1974 | object_display_mode integer not null, | |
1975 | filter SQL_TEXT null, | |
1976 | PRIMARY KEY(id) | |
021dcda7 VK |
1977 | ) TABLE_TYPE; |
1978 | ||
085ed918 AK |
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 | ||
8bdd26dc VK |
1996 | /** |
1997 | * Elements of network maps | |
1998 | */ | |
021dcda7 VK |
1999 | CREATE TABLE network_map_elements |
2000 | ( | |
51ff26ea AK |
2001 | map_id integer not null, |
2002 | element_id integer not null, | |
2003 | element_type integer not null, | |
2004 | element_data SQL_TEXT not null, | |
2005 | flags integer not null, | |
2006 | PRIMARY KEY(map_id,element_id) | |
021dcda7 | 2007 | ) TABLE_TYPE; |
cce8f214 | 2008 | |
085ed918 AK |
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 | ||
8bdd26dc VK |
2016 | /** |
2017 | * Links on network maps | |
2018 | */ | |
cce8f214 VK |
2019 | CREATE TABLE network_map_links |
2020 | ( | |
51ff26ea AK |
2021 | map_id integer not null, |
2022 | element1 integer not null, | |
2023 | element2 integer not null, | |
2024 | link_type integer not null, | |
2025 | link_name varchar(255) null, | |
2026 | connector_name1 varchar(255) null, | |
2027 | connector_name2 varchar(255) null, | |
2028 | element_data SQL_TEXT null, | |
2029 | flags integer not null | |
cce8f214 | 2030 | ) TABLE_TYPE; |
e6b9439a | 2031 | |
173c189d VK |
2032 | CREATE INDEX idx_network_map_links_map_id ON network_map_links(map_id); |
2033 | ||
085ed918 AK |
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 | ||
8bdd26dc VK |
2045 | /** |
2046 | * Image Library | |
2047 | */ | |
e6b9439a AK |
2048 | CREATE TABLE images |
2049 | ( | |
51ff26ea AK |
2050 | guid varchar(36) not null, |
2051 | name varchar(63) not null, | |
2052 | category varchar(63) not null, | |
2053 | mimetype varchar(32) not null, | |
2054 | protected integer default 0, | |
2055 | PRIMARY KEY(guid), | |
2056 | UNIQUE(name, category) | |
e6b9439a | 2057 | ) TABLE_TYPE; |
926e8ce7 | 2058 | |
085ed918 AK |
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 | ||
8bdd26dc VK |
2066 | /** |
2067 | * Dashboards | |
2068 | */ | |
926e8ce7 VK |
2069 | CREATE TABLE dashboards |
2070 | ( | |
51ff26ea AK |
2071 | id integer not null, |
2072 | num_columns integer not null, | |
2073 | options integer not null, | |
2074 | PRIMARY KEY(id) | |
926e8ce7 VK |
2075 | ) TABLE_TYPE; |
2076 | ||
085ed918 AK |
2077 | COMMENT_TABLE(dashboards, '') |
2078 | COMMENT_COLUMN(dashboards.id, '') | |
2079 | COMMENT_COLUMN(dashboards.num_columns, '') | |
2080 | COMMENT_COLUMN(dashboards.options, '') | |
2081 | ||
8bdd26dc VK |
2082 | /** |
2083 | * Dashboard elements | |
2084 | */ | |
926e8ce7 VK |
2085 | CREATE TABLE dashboard_elements |
2086 | ( | |
51ff26ea AK |
2087 | dashboard_id integer not null, |
2088 | element_id integer not null, | |
2089 | element_type integer not null, | |
2090 | element_data SQL_TEXT null, | |
2091 | layout_data SQL_TEXT null, | |
2092 | PRIMARY KEY(dashboard_id,element_id) | |
926e8ce7 | 2093 | ) TABLE_TYPE; |
abf24277 | 2094 | |
085ed918 AK |
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 | ||
8bdd26dc VK |
2102 | /** |
2103 | * Associations between dashboards and other objects | |
2104 | */ | |
2105 | CREATE TABLE dashboard_associations | |
2106 | ( | |
51ff26ea AK |
2107 | object_id integer not null, |
2108 | dashboard_id integer not null, | |
2109 | PRIMARY KEY(object_id,dashboard_id) | |
8bdd26dc VK |
2110 | ) TABLE_TYPE; |
2111 | ||
085ed918 AK |
2112 | COMMENT_TABLE(dashboard_associations, '') |
2113 | COMMENT_COLUMN(dashboard_associations.object_id, '') | |
2114 | COMMENT_COLUMN(dashboard_associations.dashboard_id, '') | |
2115 | ||
8bdd26dc VK |
2116 | /** |
2117 | * SLA business services | |
2118 | */ | |
abf24277 VK |
2119 | CREATE TABLE business_services |
2120 | ( | |
51ff26ea AK |
2121 | service_id integer not null, |
2122 | PRIMARY KEY(service_id) | |
abf24277 VK |
2123 | ) TABLE_TYPE; |
2124 | ||
085ed918 AK |
2125 | COMMENT_TABLE(business_services, '') |
2126 | COMMENT_COLUMN(business_services.service_id, '') | |
2127 | ||
8bdd26dc VK |
2128 | /** |
2129 | * SLA service checks | |
2130 | */ | |
abf24277 VK |
2131 | CREATE TABLE slm_checks |
2132 | ( | |
51ff26ea AK |
2133 | id integer not null, |
2134 | type integer not null, // 1 - threshold, 2 - script | |
2135 | content SQL_TEXT null, // if type == 2 | |
2136 | threshold_id integer not null, // if type == 1; refers to thresholds.id | |
2137 | reason varchar(255) null, | |
2138 | is_template integer not null, | |
2139 | template_id integer not null, // template check ID | |
2140 | current_ticket integer not null, | |
2141 | PRIMARY KEY(id) | |
abf24277 VK |
2142 | ) TABLE_TYPE; |
2143 | ||
085ed918 AK |
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 | ||
abf24277 VK |
2154 | /* |
2155 | ** SLA node links | |
2156 | */ | |
abf24277 VK |
2157 | CREATE TABLE node_links |
2158 | ( | |
51ff26ea AK |
2159 | nodelink_id integer not null, |
2160 | node_id integer not null, // refers to node.id | |
2161 | PRIMARY KEY(nodelink_id) | |
abf24277 VK |
2162 | ) TABLE_TYPE; |
2163 | ||
085ed918 AK |
2164 | COMMENT_TABLE(node_links, '') |
2165 | COMMENT_COLUMN(node_links.nodelink_id, '') | |
2166 | COMMENT_COLUMN(node_links.node_id, '') | |
2167 | ||
abf24277 VK |
2168 | /* |
2169 | ** SLA - agreements | |
2170 | */ | |
abf24277 VK |
2171 | CREATE TABLE slm_agreements |
2172 | ( | |
51ff26ea AK |
2173 | agreement_id integer not null, |
2174 | service_id integer not null, | |
2175 | org_id integer not null, | |
2176 | uptime varchar(63) not null, // required uptime, numeric(10,2) ? | |
2177 | period integer not null, // 1 - day, 2 - month | |
2178 | start_date integer not null, // date in YYYYMMDD format | |
2179 | notes varchar(255), | |
2180 | PRIMARY KEY(agreement_id) | |
abf24277 VK |
2181 | ) TABLE_TYPE; |
2182 | ||
085ed918 AK |
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, '') | |
abf24277 VK |
2191 | |
2192 | /* | |
2193 | ** SLA tickets | |
2194 | */ | |
2195 | ||
2196 | CREATE TABLE slm_tickets | |
2197 | ( | |
51ff26ea AK |
2198 | ticket_id integer not null, |
2199 | service_id integer not null, | |
2200 | check_id integer not null, | |
2201 | create_timestamp integer not null, | |
2202 | close_timestamp integer not null, | |
2203 | reason varchar(255) null, | |
2204 | PRIMARY KEY(ticket_id) | |
abf24277 VK |
2205 | ) TABLE_TYPE; |
2206 | ||
085ed918 AK |
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, '') | |
abf24277 | 2214 | |
dfe3b3e7 AK |
2215 | /* |
2216 | ** SLA service change log | |
2217 | */ | |
2218 | ||
2219 | CREATE TABLE slm_service_history | |
2220 | ( | |
51ff26ea AK |
2221 | record_id integer not null, |
2222 | service_id integer not null, | |
2223 | change_timestamp integer not null, | |
2224 | new_status integer not null, | |
2225 | PRIMARY KEY(record_id) | |
dfe3b3e7 AK |
2226 | ) TABLE_TYPE; |
2227 | ||
085ed918 AK |
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 | ||
dfe3b3e7 | 2234 | |
abf24277 VK |
2235 | /* |
2236 | ** Organizations | |
2237 | */ | |
2238 | ||
2239 | CREATE TABLE organizations | |
2240 | ( | |
51ff26ea AK |
2241 | id integer not null, |
2242 | parent_id integer not null, | |
2243 | org_type integer not null, | |
2244 | name varchar(63) not null, | |
2245 | description varchar(255), | |
2246 | manager integer not null, // manager - id from "persons" table | |
2247 | PRIMARY KEY(id) | |
abf24277 VK |
2248 | ) TABLE_TYPE; |
2249 | ||
085ed918 AK |
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, '') | |
abf24277 VK |
2257 | |
2258 | /* | |
2259 | ** Persons | |
2260 | */ | |
2261 | ||
2262 | CREATE TABLE persons | |
2263 | ( | |
51ff26ea AK |
2264 | id integer not null, |
2265 | org_id integer not null, | |
2266 | first_name varchar(63), | |
2267 | last_name varchar(63), | |
2268 | title varchar(255), | |
2269 | status integer not null, | |
2270 | PRIMARY KEY(id) | |
abf24277 | 2271 | ) TABLE_TYPE; |
878b4261 | 2272 | |
085ed918 AK |
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 | ||
878b4261 VK |
2281 | /* |
2282 | ** Job history | |
2283 | */ | |
878b4261 VK |
2284 | CREATE TABLE job_history |
2285 | ( | |
51ff26ea AK |
2286 | id integer not null, |
2287 | time_created integer not null, | |
2288 | time_started integer not null, | |
2289 | time_finished integer not null, | |
2290 | job_type varchar(127) null, | |
2291 | description varchar(255) null, | |
2292 | additional_info varchar(255) null, | |
2293 | node_id integer not null, | |
2294 | user_id integer not null, | |
2295 | status integer not null, | |
2296 | failure_message varchar(255) null, | |
2297 | PRIMARY KEY(id) | |
878b4261 | 2298 | ) TABLE_TYPE; |
845b8121 | 2299 | |
085ed918 AK |
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 | ||
ae2a3458 VK |
2313 | /* |
2314 | ** License repository | |
2315 | */ | |
ae2a3458 VK |
2316 | CREATE TABLE licenses |
2317 | ( | |
51ff26ea AK |
2318 | id integer not null, |
2319 | content SQL_TEXT null, | |
2320 | PRIMARY KEY(id) | |
ae2a3458 | 2321 | ) TABLE_TYPE; |
69bb7f47 | 2322 | |
085ed918 AK |
2323 | COMMENT_TABLE(licenses, '') |
2324 | COMMENT_COLUMN(licenses.id, '') | |
2325 | COMMENT_COLUMN(licenses.content, '') | |
2326 | ||
69bb7f47 VK |
2327 | /* |
2328 | ** Mapping tables | |
2329 | */ | |
69bb7f47 VK |
2330 | CREATE TABLE mapping_tables |
2331 | ( | |
51ff26ea AK |
2332 | id integer not null, |
2333 | name varchar(63) not null, | |
2334 | flags integer not null, | |
2335 | description SQL_TEXT4K null, | |
2336 | PRIMARY KEY(id) | |
69bb7f47 VK |
2337 | ) TABLE_TYPE; |
2338 | ||
085ed918 AK |
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 | ||
69bb7f47 VK |
2345 | /* |
2346 | ** Mapping tables content | |
2347 | */ | |
69bb7f47 VK |
2348 | CREATE TABLE mapping_data |
2349 | ( | |
51ff26ea AK |
2350 | table_id integer not null, |
2351 | md_key varchar(63) not null, | |
2352 | md_value varchar(255) null, | |
2353 | description SQL_TEXT4K null, | |
2354 | PRIMARY KEY(table_id,md_key) | |
69bb7f47 | 2355 | ) TABLE_TYPE; |
b4c2a628 | 2356 | |
085ed918 AK |
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 | ||
b4c2a628 VK |
2363 | /* |
2364 | ** DCI summary tables | |
2365 | */ | |
b4c2a628 VK |
2366 | CREATE TABLE dci_summary_tables |
2367 | ( | |
51ff26ea AK |
2368 | id integer not null, |
2369 | guid varchar(36) not null, | |
73bbcd77 | 2370 | menu_path varchar(255) null, |
51ff26ea AK |
2371 | title varchar(127) null, |
2372 | node_filter SQL_TEXT null, | |
2373 | flags integer not null, | |
2374 | columns SQL_TEXT null, | |
2375 | PRIMARY KEY(id) | |
b4c2a628 | 2376 | ) TABLE_TYPE; |
0a145c10 | 2377 | |
085ed918 AK |
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 | ||
0a145c10 | 2387 | /* |
c6e191d2 | 2388 | ** Scheduled tasks |
0a145c10 | 2389 | */ |
c6e191d2 | 2390 | CREATE TABLE scheduled_tasks |
0a145c10 | 2391 | ( |
51ff26ea AK |
2392 | id integer not null, |
2393 | taskId varchar(255) null, | |
2394 | schedule varchar(127) null, | |
2395 | params varchar(1023) null, | |
2396 | execution_time integer not null, | |
2397 | last_execution_time integer not null, | |
2398 | flags integer not null, | |
2399 | owner integer not null, | |
2400 | object_id integer not null, | |
2401 | PRIMARY KEY(id) | |
0a145c10 | 2402 | ) TABLE_TYPE; |
f61f151c | 2403 | |
085ed918 AK |
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 | ||
f61f151c AK |
2415 | /* |
2416 | ** ZMQ Subscriptions | |
2417 | */ | |
2418 | CREATE TABLE zmq_subscription | |
2419 | ( | |
51ff26ea AK |
2420 | object_id integer not null, |
2421 | subscription_type char(1) not null, | |
2422 | ignore_items integer not null, | |
2423 | items SQL_TEXT, | |
2424 | PRIMARY KEY(object_id, subscription_type) | |
f61f151c | 2425 | ) TABLE_TYPE; |
085ed918 AK |
2426 | |
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, '') | |
0de31ec3 VK |
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, | |
a27a303e | 2440 | description varchar(127) not null, |
0de31ec3 VK |
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, | |
a27a303e | 2459 | name varchar(127) not null, |
0de31ec3 VK |
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') | |
8e6e8ef1 VK |
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; |