- All component locks moved to memory
[public/netxms.git] / sql / schema.in
CommitLineData
6c9e7d36
VK
1/*
2** System configuration table
b51c8c91 3** ex: syntax=sql
6c9e7d36 4*/
cbcaf8c8 5
feea53fd 6CREATE TABLE config
cbcaf8c8 7(
31115ff9
VK
8 var_name varchar(63) not null,
9 var_value varchar(255),
333ece94
VK
10 is_visible integer,
11 need_server_restart integer,
31115ff9 12 PRIMARY KEY(var_name)
cbcaf8c8
VK
13);
14
15
6c9e7d36
VK
16/*
17** Users
18*/
9b057805 19
feea53fd 20CREATE TABLE users
9b057805
VK
21(
22 id integer not null,
a97797f8 23 name varchar(63) not null,
4c57296d 24 password varchar(48),
f806ed94 25 access integer,
057f9dfb 26 flags integer,
00c79c7b
VK
27 full_name varchar(127),
28 description varchar(255),
9b057805
VK
29 PRIMARY KEY(id)
30);
31
32
6c9e7d36
VK
33/*
34** User groups
35*/
f806ed94 36
feea53fd 37CREATE TABLE user_groups
f806ed94
VK
38(
39 id integer not null,
a97797f8 40 name varchar(63) not null,
f806ed94 41 access integer,
057f9dfb 42 flags integer,
00c79c7b 43 description varchar(255),
f806ed94
VK
44 PRIMARY KEY(id)
45);
46
47
6c9e7d36
VK
48/*
49** Users to groups mapping
50*/
f806ed94 51
feea53fd 52CREATE TABLE user_group_members
f806ed94
VK
53(
54 group_id integer not null,
55 user_id integer not null,
057f9dfb 56 PRIMARY KEY(group_id,user_id)
f806ed94
VK
57);
58
59
6c9e7d36
VK
60/*
61** Nodes to be added
62*/
49826a80 63
feea53fd 64CREATE TABLE new_nodes
49826a80
VK
65(
66 id integer not null,
205acaf4
VK
67 ip_addr varchar(15) not null,
68 ip_netmask varchar(15) not null,
49826a80
VK
69 discovery_flags integer not null
70);
71
72
6c9e7d36
VK
73/*
74** Nodes information
75*/
cbcaf8c8 76
feea53fd 77CREATE TABLE nodes
cbcaf8c8 78(
9e6c6d05 79 id integer not null,
a97797f8 80 name varchar(63),
cbcaf8c8 81 status integer,
49826a80 82 is_deleted integer not null,
205acaf4 83 primary_ip varchar(15),
49826a80
VK
84 is_snmp integer,
85 is_agent integer,
86 is_bridge integer,
87 is_router integer,
83f01fdf 88 is_local_mgmt integer,
49826a80 89 snmp_version integer,
a713e82e 90 community varchar(32),
e5c63a9b 91 snmp_oid varchar(255),
49826a80 92 discovery_flags integer,
a713e82e
VK
93 auth_method integer,
94 secret varchar(64),
95 agent_port integer,
96 status_poll_type integer,
f806ed94 97 inherit_access_rights integer,
eb817cf8 98 image_id integer,
70573ffe 99 description TEXT,
5e918a37 100 node_type integer,
9e6c6d05
VK
101 PRIMARY KEY(id)
102);
103
104
6c9e7d36
VK
105/*
106** Subnets
107*/
cbcaf8c8 108
feea53fd 109CREATE TABLE subnets
cbcaf8c8 110(
9e6c6d05 111 id integer not null,
a97797f8 112 name varchar(63),
cbcaf8c8 113 status integer,
49826a80 114 is_deleted integer not null,
205acaf4
VK
115 ip_addr varchar(15),
116 ip_netmask varchar(15),
eb817cf8 117 image_id integer,
9e6c6d05 118 PRIMARY KEY(id)
cbcaf8c8
VK
119);
120
121
6c9e7d36
VK
122/*
123** Nodes' interfaces
124*/
cbcaf8c8 125
feea53fd 126CREATE TABLE interfaces
cbcaf8c8 127(
9e6c6d05 128 id integer not null,
a97797f8 129 name varchar(63),
49826a80
VK
130 status integer,
131 is_deleted integer,
132 node_id integer not null,
205acaf4
VK
133 ip_addr varchar(15),
134 ip_netmask varchar(15),
49826a80
VK
135 if_type integer,
136 if_index integer,
eb817cf8 137 image_id integer,
b50f1100 138 mac_addr varchar(15),
b51c8c91 139 PRIMARY KEY(id)
cbcaf8c8
VK
140);
141
142
ef44d5ea
VK
143/*
144** Container objects
145*/
146
147CREATE TABLE containers
148(
149 id integer not null,
a97797f8 150 name varchar(63),
ef44d5ea
VK
151 status integer,
152 is_deleted integer not null,
153 category integer,
154 description TEXT,
eb817cf8 155 image_id integer,
62d11997 156 object_class integer not null,
ef44d5ea
VK
157 PRIMARY KEY(id)
158);
159
160
62d11997
VK
161/*
162** Data collection templates
163*/
164
165CREATE TABLE templates
166(
167 id integer not null,
168 name varchar(63),
169 is_deleted integer not null,
170 image_id integer,
70573ffe
VK
171 version integer,
172 description TEXT,
62d11997
VK
173 PRIMARY KEY(id)
174);
175
176
177/*
178** Mapping hosts to templates
179*/
180
181CREATE 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
6c9e7d36
VK
189/*
190** Nodes to subnets mapping
191*/
cbcaf8c8 192
a713e82e 193CREATE TABLE nsmap
cbcaf8c8 194(
49826a80
VK
195 subnet_id integer not null,
196 node_id integer not null,
c98d2ec7 197 PRIMARY KEY(subnet_id,node_id)
cbcaf8c8
VK
198);
199
200
ef44d5ea
VK
201/*
202** Container members
203*/
204
205CREATE 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
217CREATE TABLE container_categories
218(
219 category integer not null,
a97797f8 220 name varchar(63),
ef44d5ea
VK
221 image_id integer not null,
222 description TEXT,
223 PRIMARY KEY(category)
224);
225
226
6c9e7d36
VK
227/*
228** Objects' ACLs
229*/
f806ed94 230
feea53fd 231CREATE TABLE acl
f806ed94
VK
232(
233 object_id integer not null,
234 user_id integer not null,
235 access_rights integer not null,
c98d2ec7 236 PRIMARY KEY(object_id,user_id)
f806ed94
VK
237);
238
239
ef44d5ea
VK
240/*
241** Objects' access options
242*/
243
244CREATE TABLE access_options
245(
246 object_id integer not null,
247 inherit_rights integer not null,
248 PRIMARY KEY(object_id)
249);
250
251
6c9e7d36
VK
252/*
253** Data collection items
333ece94
VK
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.
6c9e7d36 261*/
cbcaf8c8 262
feea53fd 263CREATE TABLE items
cbcaf8c8 264(
2fa71464 265 item_id integer not null,
333ece94
VK
266 node_id integer not null,
267 template_id integer not null,
2fa71464 268 name varchar(255) not null,
cbcaf8c8 269 description varchar(255),
6c9e7d36 270 source integer, // 0 for internal or 1 for native agent or 2 for SNMP
cbcaf8c8
VK
271 datatype integer,
272 polling_interval integer,
b2581e52 273 retention_time integer,
2fa71464 274 status integer, // ACTIVE, DISABLED or NOT_SUPPORTED
333ece94
VK
275 delta_calculation integer,
276 transformation TEXT, // Transformation formula
6ad5eb6d 277 instance varchar(255), // Free form text which can be used in events
c98d2ec7 278 PRIMARY KEY(item_id)
cbcaf8c8
VK
279);
280
281
6c9e7d36
VK
282/*
283** Events configuration
284*/
cbcaf8c8 285
feea53fd 286CREATE TABLE events
cbcaf8c8 287(
59f88625 288 event_id integer not null,
a97797f8 289 name varchar(63) not null, // Short event name
cbcaf8c8 290 severity integer,
c1142e46 291 flags integer,
6c9e7d36
VK
292 message varchar(255), // Message template
293 description DT_TEXT,
c98d2ec7 294 PRIMARY KEY(event_id)
cbcaf8c8
VK
295);
296
297
6c9e7d36
VK
298/*
299** Event log
300*/
cbcaf8c8 301
feea53fd 302CREATE TABLE event_log
cbcaf8c8
VK
303(
304 event_id integer,
31115ff9
VK
305 event_timestamp integer,
306 event_source integer, // Source object ID
307 event_severity integer,
308 event_message varchar(255)
cbcaf8c8
VK
309);
310
311
6c9e7d36
VK
312/*
313** Actions on events
314*/
cbcaf8c8 315
feea53fd 316CREATE TABLE actions
cbcaf8c8 317(
b03374f5 318 action_id integer not null,
a97797f8
VK
319 action_name varchar(63) not null,
320 action_type integer not null,
c9363772 321 is_disabled integer,
4ae6b70e
VK
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),
b6a77d6d 326 email_subject varchar(255),
4ae6b70e
VK
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
a97797f8 330 action_data TEXT not null,
b03374f5 331 PRIMARY KEY(action_id)
cbcaf8c8 332);
69cc295f
VK
333
334
6c9e7d36
VK
335/*
336** Event groups
337*/
69cc295f 338
feea53fd 339CREATE TABLE event_groups
69cc295f
VK
340(
341 id integer not null,
a97797f8 342 name varchar(63),
3c468b80 343 description varchar(255),
69cc295f
VK
344 PRIMARY KEY(id)
345);
346
347
6c9e7d36
VK
348/*
349** Event group members
350*/
69cc295f 351
feea53fd 352CREATE TABLE event_group_members
69cc295f
VK
353(
354 group_id integer not null,
355 event_id integer not null,
3c468b80 356 PRIMARY KEY(group_id,event_id)
69cc295f
VK
357);
358
359
6c9e7d36
VK
360/*
361** Event processing policy
362*/
69cc295f 363
feea53fd 364CREATE TABLE event_policy
69cc295f 365(
59a21a78 366 rule_id integer not null, // Rule number
b6a77d6d 367 flags integer not null,
a97797f8 368 comments TEXT,
59a21a78
VK
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
59a21a78 373 PRIMARY KEY(rule_id)
69cc295f
VK
374);
375
feea53fd 376CREATE TABLE policy_source_list
69cc295f
VK
377(
378 rule_id integer not null,
69cc295f 379 object_id integer not null,
3c468b80 380 PRIMARY KEY(rule_id,object_id)
69cc295f
VK
381);
382
feea53fd 383CREATE TABLE policy_event_list
69cc295f
VK
384(
385 rule_id integer not null,
69cc295f 386 event_id integer not null,
b6a77d6d
VK
387 PRIMARY KEY(rule_id,event_id)
388);
389
feea53fd 390CREATE TABLE policy_action_list
b6a77d6d
VK
391(
392 rule_id integer not null,
393 action_id integer not null,
394 PRIMARY KEY(rule_id,action_id)
69cc295f 395);
54f0de5b
VK
396
397
398/*
399** Deleted objects
400*/
401
feea53fd 402CREATE TABLE deleted_objects
54f0de5b
VK
403(
404 object_id integer not null,
405 object_class integer,
a97797f8 406 name varchar(63),
205acaf4
VK
407 ip_addr varchar(15),
408 ip_netmask varchar(15),
54f0de5b
VK
409 PRIMARY KEY(object_id)
410);
59f88625
VK
411
412
413/*
414** Threshold checking rules
415*/
416
feea53fd 417CREATE TABLE thresholds
59f88625
VK
418(
419 threshold_id integer not null,
420 item_id integer not null,
59a21a78 421 sequence_number integer not null,
59f88625
VK
422 fire_value varchar(255),
423 rearm_value varchar(255),
2fa71464
VK
424 check_function integer,
425 check_operation integer,
426 parameter_1 integer,
427 parameter_2 integer,
59f88625
VK
428 event_code integer,
429 PRIMARY KEY(threshold_id)
430);
2fa71464
VK
431
432
59a21a78
VK
433/*
434** Alarms
435*/
436
437CREATE TABLE alarms
438(
439 alarm_id integer not null, // Unique alarm identifier
31115ff9 440 alarm_timestamp integer,
4de204a3
VK
441 source_object_id integer,
442 source_event_id integer,
59a21a78
VK
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
456CREATE TABLE alarm_notes
457(
458 note_id integer not null,
459 alarm_id integer not null,
31115ff9 460 alarm_note_timestamp integer,
59a21a78
VK
461 note_text DT_TEXT,
462 PRIMARY KEY(note_id)
463);
917aa2e6
VK
464
465
466/*
467** Image catalog
468*/
469
470CREATE TABLE images
471(
472 image_id integer not null,
473 name varchar(64),
ddf0e5b9
VK
474 file_name_png varchar(255),
475 file_hash_png varchar(32),
476 file_name_ico varchar(255),
477 file_hash_ico varchar(32),
917aa2e6
VK
478 PRIMARY KEY(image_id)
479);
eb817cf8
VK
480
481
482/*
483** Default images for various object classes
484*/
485
486CREATE TABLE default_images
487(
488 object_class integer not null,
489 image_id integer not null,
490 PRIMARY KEY(object_class)
491);
e01f7113
VK
492
493
494/*
495** OID to node type translation
496*/
497
498CREATE TABLE oid_to_type
499(
500 pair_id integer not null,
501 snmp_oid varchar(255) not null,
502 node_type integer not null,
5e918a37 503 node_flags integer not null,
e01f7113
VK
504 PRIMARY KEY(pair_id)
505);
3aeed82c
VK
506
507
508/*
509** SNMP trap configuration
510*/
511
512CREATE 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
526CREATE 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);