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