- All component locks moved to memory
[public/netxms.git] / sql / schema.in
1 /*
2 ** System configuration table
3 ** ex: syntax=sql
4 */
5
6 CREATE TABLE config
7 (
8 var_name varchar(63) not null,
9 var_value varchar(255),
10 is_visible integer,
11 need_server_restart integer,
12 PRIMARY KEY(var_name)
13 );
14
15
16 /*
17 ** Users
18 */
19
20 CREATE TABLE users
21 (
22 id integer not null,
23 name varchar(63) not null,
24 password varchar(48),
25 access integer,
26 flags integer,
27 full_name varchar(127),
28 description varchar(255),
29 PRIMARY KEY(id)
30 );
31
32
33 /*
34 ** User groups
35 */
36
37 CREATE TABLE user_groups
38 (
39 id integer not null,
40 name varchar(63) not null,
41 access integer,
42 flags integer,
43 description varchar(255),
44 PRIMARY KEY(id)
45 );
46
47
48 /*
49 ** Users to groups mapping
50 */
51
52 CREATE TABLE user_group_members
53 (
54 group_id integer not null,
55 user_id integer not null,
56 PRIMARY KEY(group_id,user_id)
57 );
58
59
60 /*
61 ** Nodes to be added
62 */
63
64 CREATE TABLE new_nodes
65 (
66 id integer not null,
67 ip_addr varchar(15) not null,
68 ip_netmask varchar(15) not null,
69 discovery_flags integer not null
70 );
71
72
73 /*
74 ** Nodes information
75 */
76
77 CREATE TABLE nodes
78 (
79 id integer not null,
80 name varchar(63),
81 status integer,
82 is_deleted integer not null,
83 primary_ip varchar(15),
84 is_snmp integer,
85 is_agent integer,
86 is_bridge integer,
87 is_router integer,
88 is_local_mgmt integer,
89 snmp_version integer,
90 community varchar(32),
91 snmp_oid varchar(255),
92 discovery_flags integer,
93 auth_method integer,
94 secret varchar(64),
95 agent_port integer,
96 status_poll_type integer,
97 inherit_access_rights integer,
98 image_id integer,
99 description TEXT,
100 node_type integer,
101 PRIMARY KEY(id)
102 );
103
104
105 /*
106 ** Subnets
107 */
108
109 CREATE TABLE subnets
110 (
111 id integer not null,
112 name varchar(63),
113 status integer,
114 is_deleted integer not null,
115 ip_addr varchar(15),
116 ip_netmask varchar(15),
117 image_id integer,
118 PRIMARY KEY(id)
119 );
120
121
122 /*
123 ** Nodes' interfaces
124 */
125
126 CREATE TABLE interfaces
127 (
128 id integer not null,
129 name varchar(63),
130 status integer,
131 is_deleted integer,
132 node_id integer not null,
133 ip_addr varchar(15),
134 ip_netmask varchar(15),
135 if_type integer,
136 if_index integer,
137 image_id integer,
138 mac_addr varchar(15),
139 PRIMARY KEY(id)
140 );
141
142
143 /*
144 ** Container objects
145 */
146
147 CREATE TABLE containers
148 (
149 id integer not null,
150 name varchar(63),
151 status integer,
152 is_deleted integer not null,
153 category integer,
154 description TEXT,
155 image_id integer,
156 object_class integer not null,
157 PRIMARY KEY(id)
158 );
159
160
161 /*
162 ** Data collection templates
163 */
164
165 CREATE TABLE templates
166 (
167 id integer not null,
168 name varchar(63),
169 is_deleted integer not null,
170 image_id integer,
171 version integer,
172 description TEXT,
173 PRIMARY KEY(id)
174 );
175
176
177 /*
178 ** Mapping hosts to templates
179 */
180
181 CREATE TABLE dct_node_map
182 (
183 template_id integer not null,
184 node_id integer not null,
185 PRIMARY KEY(template_id,node_id)
186 );
187
188
189 /*
190 ** Nodes to subnets mapping
191 */
192
193 CREATE TABLE nsmap
194 (
195 subnet_id integer not null,
196 node_id integer not null,
197 PRIMARY KEY(subnet_id,node_id)
198 );
199
200
201 /*
202 ** Container members
203 */
204
205 CREATE TABLE container_members
206 (
207 container_id integer not null,
208 object_id integer not null,
209 PRIMARY KEY(container_id,object_id)
210 );
211
212
213 /*
214 ** Container categories
215 */
216
217 CREATE TABLE container_categories
218 (
219 category integer not null,
220 name varchar(63),
221 image_id integer not null,
222 description TEXT,
223 PRIMARY KEY(category)
224 );
225
226
227 /*
228 ** Objects' ACLs
229 */
230
231 CREATE TABLE acl
232 (
233 object_id integer not null,
234 user_id integer not null,
235 access_rights integer not null,
236 PRIMARY KEY(object_id,user_id)
237 );
238
239
240 /*
241 ** Objects' access options
242 */
243
244 CREATE TABLE access_options
245 (
246 object_id integer not null,
247 inherit_rights integer not null,
248 PRIMARY KEY(object_id)
249 );
250
251
252 /*
253 ** Data collection items
254 **
255 ** If node_id != 0, it's an item bound to node, and template_id points to
256 ** the template used for creating this item. In this case, template_id = 0
257 ** means that item was created manually.
258 ** If node_id = 0, it's a template item, and template_id points to a template
259 ** this item belongs to.
260 ** If both node_id and template_id is 0, it's an error.
261 */
262
263 CREATE TABLE items
264 (
265 item_id integer not null,
266 node_id integer not null,
267 template_id integer not null,
268 name varchar(255) not null,
269 description varchar(255),
270 source integer, // 0 for internal or 1 for native agent or 2 for SNMP
271 datatype integer,
272 polling_interval integer,
273 retention_time integer,
274 status integer, // ACTIVE, DISABLED or NOT_SUPPORTED
275 delta_calculation integer,
276 transformation TEXT, // Transformation formula
277 instance varchar(255), // Free form text which can be used in events
278 PRIMARY KEY(item_id)
279 );
280
281
282 /*
283 ** Events configuration
284 */
285
286 CREATE TABLE events
287 (
288 event_id integer not null,
289 name varchar(63) not null, // Short event name
290 severity integer,
291 flags integer,
292 message varchar(255), // Message template
293 description DT_TEXT,
294 PRIMARY KEY(event_id)
295 );
296
297
298 /*
299 ** Event log
300 */
301
302 CREATE TABLE event_log
303 (
304 event_id integer,
305 event_timestamp integer,
306 event_source integer, // Source object ID
307 event_severity integer,
308 event_message varchar(255)
309 );
310
311
312 /*
313 ** Actions on events
314 */
315
316 CREATE TABLE actions
317 (
318 action_id integer not null,
319 action_name varchar(63) not null,
320 action_type integer not null,
321 is_disabled integer,
322 // Field "rcpt_addr" holds e-mail address for e-mail actions,
323 // phone number for sms actions, and remote host address for
324 // remote execution actions
325 rcpt_addr varchar(255),
326 email_subject varchar(255),
327 // Field "action_data" holds message text for e-mail and sms actions,
328 // command line for external command execution actions, or
329 // action name with optional arguments for remote execution actions
330 action_data TEXT not null,
331 PRIMARY KEY(action_id)
332 );
333
334
335 /*
336 ** Event groups
337 */
338
339 CREATE TABLE event_groups
340 (
341 id integer not null,
342 name varchar(63),
343 description varchar(255),
344 PRIMARY KEY(id)
345 );
346
347
348 /*
349 ** Event group members
350 */
351
352 CREATE TABLE event_group_members
353 (
354 group_id integer not null,
355 event_id integer not null,
356 PRIMARY KEY(group_id,event_id)
357 );
358
359
360 /*
361 ** Event processing policy
362 */
363
364 CREATE TABLE event_policy
365 (
366 rule_id integer not null, // Rule number
367 flags integer not null,
368 comments TEXT,
369 alarm_message varchar(255),
370 alarm_severity integer,
371 alarm_key varchar(255), // Alarm key (used for auto acknowlegment)
372 alarm_ack_key varchar(255), // Acknowlege all alarms with given key
373 PRIMARY KEY(rule_id)
374 );
375
376 CREATE TABLE policy_source_list
377 (
378 rule_id integer not null,
379 object_id integer not null,
380 PRIMARY KEY(rule_id,object_id)
381 );
382
383 CREATE TABLE policy_event_list
384 (
385 rule_id integer not null,
386 event_id integer not null,
387 PRIMARY KEY(rule_id,event_id)
388 );
389
390 CREATE TABLE policy_action_list
391 (
392 rule_id integer not null,
393 action_id integer not null,
394 PRIMARY KEY(rule_id,action_id)
395 );
396
397
398 /*
399 ** Deleted objects
400 */
401
402 CREATE TABLE deleted_objects
403 (
404 object_id integer not null,
405 object_class integer,
406 name varchar(63),
407 ip_addr varchar(15),
408 ip_netmask varchar(15),
409 PRIMARY KEY(object_id)
410 );
411
412
413 /*
414 ** Threshold checking rules
415 */
416
417 CREATE TABLE thresholds
418 (
419 threshold_id integer not null,
420 item_id integer not null,
421 sequence_number integer not null,
422 fire_value varchar(255),
423 rearm_value varchar(255),
424 check_function integer,
425 check_operation integer,
426 parameter_1 integer,
427 parameter_2 integer,
428 event_code integer,
429 PRIMARY KEY(threshold_id)
430 );
431
432
433 /*
434 ** Alarms
435 */
436
437 CREATE TABLE alarms
438 (
439 alarm_id integer not null, // Unique alarm identifier
440 alarm_timestamp integer,
441 source_object_id integer,
442 source_event_id integer,
443 message varchar(255),
444 severity integer,
445 alarm_key varchar(255), // Alarm key (used for auto acknowlegment)
446 is_ack integer, // TRUE if alarm is acknowleged
447 ack_by integer, // ID of user who acknowleges alarm
448 PRIMARY KEY(alarm_id)
449 );
450
451
452 /*
453 ** Alarm notes
454 */
455
456 CREATE TABLE alarm_notes
457 (
458 note_id integer not null,
459 alarm_id integer not null,
460 alarm_note_timestamp integer,
461 note_text DT_TEXT,
462 PRIMARY KEY(note_id)
463 );
464
465
466 /*
467 ** Image catalog
468 */
469
470 CREATE TABLE images
471 (
472 image_id integer not null,
473 name varchar(64),
474 file_name_png varchar(255),
475 file_hash_png varchar(32),
476 file_name_ico varchar(255),
477 file_hash_ico varchar(32),
478 PRIMARY KEY(image_id)
479 );
480
481
482 /*
483 ** Default images for various object classes
484 */
485
486 CREATE TABLE default_images
487 (
488 object_class integer not null,
489 image_id integer not null,
490 PRIMARY KEY(object_class)
491 );
492
493
494 /*
495 ** OID to node type translation
496 */
497
498 CREATE TABLE oid_to_type
499 (
500 pair_id integer not null,
501 snmp_oid varchar(255) not null,
502 node_type integer not null,
503 node_flags integer not null,
504 PRIMARY KEY(pair_id)
505 );
506
507
508 /*
509 ** SNMP trap configuration
510 */
511
512 CREATE TABLE snmp_trap_cfg
513 (
514 trap_id integer not null,
515 snmp_oid varchar(255) not null,
516 event_id integer not null,
517 sequence integer not null,
518 PRIMARY KEY(trap_id)
519 );
520
521
522 /*
523 ** SNMP trap parameters mapping
524 */
525
526 CREATE TABLE snmp_trap_pmap
527 (
528 trap_id integer not null,
529 parameter integer not null,
530 snmp_oid varchar(255),
531 PRIMARY KEY(trap_id,parameter)
532 );