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