505feab8978ee9b9749cb2c6c0adcc618d1a5c31
[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) not null,
10 #ifdef DB_ORACLE
11 is_visible integer default 1 not null,
12 need_server_restart integer default 0 not null,
13 #else
14 is_visible integer not null default 1,
15 need_server_restart integer not null default 0,
16 #endif
17 PRIMARY KEY(var_name)
18 ) TABLE_TYPE;
19
20
21 /*
22 ** Loadable modules
23 */
24
25 CREATE TABLE modules
26 (
27 module_id integer not null,
28 module_name varchar(63) not null,
29 exec_name varchar(255) not null,
30 #ifdef DB_ORACLE
31 module_flags integer default 0 not null,
32 #else
33 module_flags integer not null default 0,
34 #endif
35 description SQL_TEXT not null,
36 license_key varchar(255) not null,
37 PRIMARY KEY(module_id)
38 ) TABLE_TYPE;
39
40
41 /*
42 ** Users
43 */
44
45 CREATE TABLE users
46 (
47 id integer not null,
48 name varchar(63) not null,
49 password varchar(48) not null,
50 system_access integer not null,
51 flags integer not null,
52 full_name varchar(127) not null,
53 description varchar(255) not null,
54 grace_logins integer not null,
55 PRIMARY KEY(id)
56 ) TABLE_TYPE;
57
58
59 /*
60 ** User groups
61 */
62
63 CREATE TABLE user_groups
64 (
65 id integer not null,
66 name varchar(63) not null,
67 system_access integer,
68 flags integer,
69 description varchar(255),
70 PRIMARY KEY(id)
71 ) TABLE_TYPE;
72
73
74 /*
75 ** Users to groups mapping
76 */
77
78 CREATE TABLE user_group_members
79 (
80 group_id integer not null,
81 user_id integer not null,
82 PRIMARY KEY(group_id,user_id)
83 ) TABLE_TYPE;
84
85
86 /*
87 ** User profiles
88 */
89
90 CREATE TABLE user_profiles
91 (
92 user_id integer not null,
93 var_name varchar(255) not null,
94 var_value SQL_TEXT,
95 PRIMARY KEY(user_id,var_name)
96 ) TABLE_TYPE;
97
98
99 /*
100 ** Common object properties
101 */
102
103 CREATE TABLE object_properties
104 (
105 object_id integer not null,
106 name varchar(63) not null,
107 status integer not null,
108 is_deleted integer not null,
109 image_id integer,
110 last_modified integer not null,
111 inherit_access_rights integer not null,
112 status_calc_alg integer not null,
113 status_prop_alg integer not null,
114 status_fixed_val integer not null,
115 status_shift integer not null,
116 status_translation varchar(8) not null,
117 status_single_threshold integer not null,
118 status_thresholds varchar(8) not null,
119 PRIMARY KEY(object_id)
120 ) TABLE_TYPE;
121
122
123 /*
124 ** Zone object
125 */
126
127 CREATE TABLE zones
128 (
129 id integer not null, // Zone object's ID
130 zone_guid integer not null, // Globally unique ID for zone
131 zone_type integer not null, // Active or passive zone
132 controller_ip varchar(15) not null,
133 description SQL_TEXT,
134 PRIMARY KEY(id)
135 ) TABLE_TYPE;
136
137
138 /*
139 ** Zone IP address list
140 */
141
142 CREATE TABLE zone_ip_addr_list
143 (
144 zone_id integer not null, // Zone object's ID
145 ip_addr varchar(15) not null,
146 PRIMARY KEY(zone_id,ip_addr)
147 ) TABLE_TYPE;
148
149
150 /*
151 ** Nodes information
152 */
153
154 CREATE TABLE nodes
155 (
156 id integer not null,
157 primary_ip varchar(15),
158 node_flags integer,
159 snmp_version integer,
160 community varchar(32),
161 snmp_oid varchar(255),
162 discovery_flags integer,
163 auth_method integer,
164 secret varchar(64),
165 agent_port integer,
166 status_poll_type integer,
167 description SQL_TEXT,
168 node_type integer,
169 agent_version varchar(63),
170 platform_name varchar(63),
171 poller_node_id integer not null,
172 zone_guid integer not null,
173 proxy_node integer not null,
174 PRIMARY KEY(id)
175 ) TABLE_TYPE;
176
177
178 /*
179 ** Subnets
180 */
181
182 CREATE TABLE subnets
183 (
184 id integer not null,
185 ip_addr varchar(15),
186 ip_netmask varchar(15),
187 zone_guid integer not null,
188 PRIMARY KEY(id)
189 ) TABLE_TYPE;
190
191
192 /*
193 ** Nodes' interfaces
194 */
195
196 CREATE TABLE interfaces
197 (
198 id integer not null,
199 node_id integer not null,
200 ip_addr varchar(15),
201 ip_netmask varchar(15),
202 if_type integer,
203 if_index integer,
204 mac_addr varchar(15),
205 PRIMARY KEY(id)
206 ) TABLE_TYPE;
207
208
209 /*
210 ** Network services
211 */
212
213 CREATE TABLE network_services
214 (
215 id integer not null,
216 node_id integer not null,
217 service_type integer,
218 ip_bind_addr varchar(15),
219 ip_proto integer,
220 ip_port integer,
221 check_request SQL_TEXT,
222 check_responce SQL_TEXT,
223 poller_node_id integer not null,
224 PRIMARY KEY(id)
225 ) TABLE_TYPE;
226
227
228 /*
229 ** VPN connectors
230 */
231
232 CREATE TABLE vpn_connectors
233 (
234 id integer not null,
235 node_id integer not null,
236 peer_gateway integer not null,
237 PRIMARY KEY(id)
238 ) TABLE_TYPE;
239
240
241 /*
242 ** VPN connector networks
243 */
244
245 CREATE TABLE vpn_connector_networks
246 (
247 vpn_id integer not null,
248 network_type integer not null, // 0 == local, 1 == remote
249 ip_addr varchar(15) not null,
250 ip_netmask varchar(15) not null,
251 PRIMARY KEY(vpn_id,ip_addr)
252 ) TABLE_TYPE;
253
254
255 /*
256 ** Container objects
257 */
258
259 CREATE TABLE containers
260 (
261 id integer not null,
262 category integer,
263 description SQL_TEXT,
264 object_class integer not null,
265 PRIMARY KEY(id)
266 ) TABLE_TYPE;
267
268
269 /*
270 ** Data collection templates
271 */
272
273 CREATE TABLE templates
274 (
275 id integer not null,
276 version integer,
277 description SQL_TEXT,
278 PRIMARY KEY(id)
279 ) TABLE_TYPE;
280
281
282 /*
283 ** Mapping hosts to templates
284 */
285
286 CREATE TABLE dct_node_map
287 (
288 template_id integer not null,
289 node_id integer not null,
290 PRIMARY KEY(template_id,node_id)
291 ) TABLE_TYPE;
292
293
294 /*
295 ** Nodes to subnets mapping
296 */
297
298 CREATE TABLE nsmap
299 (
300 subnet_id integer not null,
301 node_id integer not null,
302 PRIMARY KEY(subnet_id,node_id)
303 ) TABLE_TYPE;
304
305
306 /*
307 ** Container members
308 */
309
310 CREATE TABLE container_members
311 (
312 container_id integer not null,
313 object_id integer not null,
314 PRIMARY KEY(container_id,object_id)
315 ) TABLE_TYPE;
316
317
318 /*
319 ** Container categories
320 */
321
322 CREATE TABLE container_categories
323 (
324 category integer not null,
325 name varchar(63),
326 image_id integer not null,
327 description SQL_TEXT,
328 PRIMARY KEY(category)
329 ) TABLE_TYPE;
330
331
332 /*
333 ** Objects' ACLs
334 */
335
336 CREATE TABLE acl
337 (
338 object_id integer not null,
339 user_id integer not null,
340 access_rights integer not null,
341 PRIMARY KEY(object_id,user_id)
342 ) TABLE_TYPE;
343
344
345 /*
346 ** Data collection items
347 **
348 ** If node_id != 0, it's an item bound to node, and template_id points to
349 ** the template used for creating this item. In this case, template_id = 0
350 ** means that item was created manually.
351 ** If node_id = 0, it's a template item, and template_id points to a template
352 ** this item belongs to.
353 ** If both node_id and template_id is 0, it's an error.
354 */
355
356 CREATE TABLE items
357 (
358 item_id integer not null,
359 node_id integer not null,
360 template_id integer not null,
361 template_item_id integer not null,
362 name varchar(255) not null,
363 description varchar(255),
364 source integer, // 0 for internal or 1 for native agent or 2 for SNMP
365 datatype integer,
366 polling_interval integer,
367 retention_time integer,
368 adv_schedule integer, // 1 if item has advanced schedule
369 status integer, // ACTIVE, DISABLED or NOT_SUPPORTED
370 delta_calculation integer,
371 transformation SQL_TEXT, // Transformation formula
372 instance varchar(255), // Free form text which can be used in events
373 PRIMARY KEY(item_id)
374 ) TABLE_TYPE;
375
376
377 /*
378 ** Schedules for DCIs
379 */
380
381 CREATE TABLE dci_schedules
382 (
383 item_id integer not null,
384 schedule varchar(255) not null
385 ) TABLE_TYPE;
386
387
388 /*
389 ** Latest raw values for all data collection items
390 */
391
392 CREATE TABLE raw_dci_values
393 (
394 item_id integer not null,
395 raw_value varchar(255),
396 last_poll_time integer,
397 PRIMARY KEY(item_id)
398 ) TABLE_TYPE;
399
400 #ifndef DB_ORACLE
401 CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id);
402 #endif
403
404
405 /*
406 ** Events configuration
407 */
408
409 CREATE TABLE event_cfg
410 (
411 event_code integer not null,
412 event_name varchar(63) not null, // Short event name
413 severity integer,
414 flags integer,
415 message varchar(255), // Message template
416 description SQL_TEXT,
417 PRIMARY KEY(event_code)
418 ) TABLE_TYPE;
419
420
421 /*
422 ** Event log
423 */
424
425 CREATE TABLE event_log
426 (
427 event_id SQL_INT64 not null,
428 event_code integer not null,
429 event_timestamp integer,
430 event_source integer, // Source object ID
431 event_severity integer,
432 event_message varchar(255),
433 root_event_id SQL_INT64 default 0,
434 /* Non-zero if current event correlates to some other event */
435 PRIMARY KEY(event_id)
436 ) TABLE_TYPE;
437
438 CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp);
439
440
441 /*
442 ** Actions on events
443 */
444
445 CREATE TABLE actions
446 (
447 action_id integer not null,
448 action_name varchar(63) not null,
449 action_type integer not null,
450 is_disabled integer,
451 // Field "rcpt_addr" holds e-mail address for e-mail actions,
452 // phone number for sms actions, and remote host address for
453 // remote execution actions
454 rcpt_addr varchar(255),
455 email_subject varchar(255),
456 // Field "action_data" holds message text for e-mail and sms actions,
457 // command line for external command execution actions, or
458 // action name with optional arguments for remote execution actions
459 action_data SQL_TEXT not null,
460 PRIMARY KEY(action_id)
461 ) TABLE_TYPE;
462
463
464 /*
465 ** Event groups
466 */
467
468 CREATE TABLE event_groups
469 (
470 id integer not null,
471 name varchar(63),
472 description varchar(255),
473 PRIMARY KEY(id)
474 ) TABLE_TYPE;
475
476
477 /*
478 ** Event group members
479 */
480
481 CREATE TABLE event_group_members
482 (
483 group_id integer not null,
484 event_code integer not null,
485 PRIMARY KEY(group_id,event_code)
486 ) TABLE_TYPE;
487
488
489 /*
490 ** Event processing policy
491 */
492
493 CREATE TABLE event_policy
494 (
495 rule_id integer not null, // Rule number
496 flags integer not null,
497 comments SQL_TEXT,
498 alarm_message varchar(255),
499 alarm_severity integer,
500 alarm_key varchar(255), // Alarm key (used for auto acknowlegment)
501 alarm_ack_key varchar(255), // Acknowlege all alarms with given key
502 PRIMARY KEY(rule_id)
503 ) TABLE_TYPE;
504
505 CREATE TABLE policy_source_list
506 (
507 rule_id integer not null,
508 object_id integer not null,
509 PRIMARY KEY(rule_id,object_id)
510 ) TABLE_TYPE;
511
512 CREATE TABLE policy_event_list
513 (
514 rule_id integer not null,
515 event_code integer not null,
516 PRIMARY KEY(rule_id,event_code)
517 ) TABLE_TYPE;
518
519 CREATE TABLE policy_action_list
520 (
521 rule_id integer not null,
522 action_id integer not null,
523 PRIMARY KEY(rule_id,action_id)
524 ) TABLE_TYPE;
525
526
527 /*
528 ** Deleted objects
529 */
530
531 CREATE TABLE deleted_objects
532 (
533 object_id integer not null,
534 object_class integer,
535 name varchar(63),
536 ip_addr varchar(15),
537 ip_netmask varchar(15),
538 PRIMARY KEY(object_id)
539 ) TABLE_TYPE;
540
541
542 /*
543 ** Threshold checking rules
544 */
545
546 CREATE TABLE thresholds
547 (
548 threshold_id integer not null,
549 item_id integer not null,
550 sequence_number integer not null,
551 fire_value varchar(255),
552 rearm_value varchar(255),
553 check_function integer,
554 check_operation integer,
555 parameter_1 integer,
556 parameter_2 integer,
557 event_code integer,
558 PRIMARY KEY(threshold_id)
559 ) TABLE_TYPE;
560
561 CREATE INDEX idx_thresholds_item_id ON thresholds(item_id);
562 CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number);
563
564
565 /*
566 ** Alarms
567 */
568
569 CREATE TABLE alarms
570 (
571 alarm_id integer not null, // Unique alarm identifier
572 alarm_timestamp integer,
573 source_object_id integer,
574 source_event_code integer,
575 source_event_id SQL_INT64,
576 message varchar(255),
577 severity integer,
578 alarm_key varchar(255), // Alarm key (used for auto acknowlegment)
579 is_ack integer, // TRUE if alarm is acknowleged
580 ack_by integer, // ID of user who acknowleges alarm
581 PRIMARY KEY(alarm_id)
582 ) TABLE_TYPE;
583
584
585 /*
586 ** Alarm notes
587 */
588
589 CREATE TABLE alarm_notes
590 (
591 note_id integer not null,
592 alarm_id integer not null,
593 alarm_note_timestamp integer,
594 note_text SQL_TEXT,
595 PRIMARY KEY(note_id)
596 ) TABLE_TYPE;
597
598
599 /*
600 ** Image catalog
601 */
602
603 CREATE TABLE images
604 (
605 image_id integer not null,
606 name varchar(64),
607 file_name_png varchar(255),
608 file_hash_png varchar(32),
609 file_name_ico varchar(255),
610 file_hash_ico varchar(32),
611 PRIMARY KEY(image_id)
612 ) TABLE_TYPE;
613
614
615 /*
616 ** Default images for various object classes
617 */
618
619 CREATE TABLE default_images
620 (
621 object_class integer not null,
622 image_id integer not null,
623 PRIMARY KEY(object_class)
624 ) TABLE_TYPE;
625
626
627 /*
628 ** OID to node type translation
629 */
630
631 CREATE TABLE oid_to_type
632 (
633 pair_id integer not null,
634 snmp_oid varchar(255) not null,
635 node_type integer not null,
636 node_flags integer not null,
637 PRIMARY KEY(pair_id)
638 ) TABLE_TYPE;
639
640
641 /*
642 ** SNMP trap configuration
643 */
644
645 CREATE TABLE snmp_trap_cfg
646 (
647 trap_id integer not null,
648 snmp_oid varchar(255) not null,
649 event_code integer not null,
650 description varchar(255),
651 PRIMARY KEY(trap_id)
652 ) TABLE_TYPE;
653
654
655 /*
656 ** SNMP trap parameters mapping
657 */
658
659 CREATE TABLE snmp_trap_pmap
660 (
661 trap_id integer not null,
662 parameter integer not null,
663 snmp_oid varchar(255),
664 description varchar(255),
665 PRIMARY KEY(trap_id,parameter)
666 ) TABLE_TYPE;
667
668
669 /*
670 ** Agent packages
671 */
672
673 CREATE TABLE agent_pkg
674 (
675 pkg_id integer not null,
676 pkg_name varchar(63),
677 version varchar(31),
678 platform varchar(63),
679 pkg_file varchar(255),
680 description varchar(255),
681 PRIMARY KEY(pkg_id)
682 ) TABLE_TYPE;
683
684
685 /*
686 ** Object tools
687 */
688
689 CREATE TABLE object_tools
690 (
691 tool_id integer not null,
692 tool_name varchar(255) not null,
693 tool_type integer not null,
694 tool_data SQL_TEXT,
695 description varchar(255),
696 flags integer not null,
697 matching_oid varchar(255),
698 PRIMARY KEY(tool_id)
699 ) TABLE_TYPE;
700
701
702 /*
703 ** Access list for object tools
704 */
705
706 CREATE TABLE object_tools_acl
707 (
708 tool_id integer not null,
709 user_id integer not null,
710 PRIMARY KEY(tool_id,user_id)
711 ) TABLE_TYPE;
712
713
714 /*
715 ** Configuration of columns for SNMP_TABLE and AGENT_TABLE type of object tools
716 */
717
718 CREATE TABLE object_tools_table_columns
719 (
720 tool_id integer not null,
721 col_number integer not null,
722 col_name varchar(255),
723 col_oid varchar(255),
724 col_format integer, // Column format (integer, string, etc.)
725 col_substr integer, // Number of matching substring in regexp
726 PRIMARY KEY(tool_id,col_number)
727 ) TABLE_TYPE;
728
729
730 /*
731 ** Stored syslog messages
732 */
733
734 CREATE TABLE syslog
735 (
736 msg_id SQL_INT64 not null,
737 msg_timestamp integer not null,
738 facility integer not null,
739 severity integer not null,
740 source_object_id integer not null,
741 hostname varchar(127) not null,
742 msg_tag varchar(32) not null,
743 msg_text SQL_TEXT not null,
744 PRIMARY KEY(msg_id)
745 ) TABLE_TYPE;
746
747 CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp);
748
749
750 /*
751 ** Script library
752 */
753
754 CREATE TABLE script_library
755 (
756 script_id integer not null,
757 script_name varchar(255) not null,
758 script_code SQL_TEXT not null,
759 PRIMARY KEY(script_id)
760 ) TABLE_TYPE;
761
762
763 /*
764 ** Extended SNMP trap log
765 */
766
767 CREATE TABLE snmp_trap_log
768 (
769 trap_id SQL_INT64 not null,
770 trap_timestamp integer not null,
771 ip_addr varchar(15) not null,
772 object_id integer not null,
773 trap_oid varchar(255) not null,
774 trap_varlist SQL_TEXT not null,
775 PRIMARY KEY(trap_id)
776 ) TABLE_TYPE;
777
778 CREATE INDEX idx_snmp_trap_log_trap_timestamp ON snmp_trap_log(trap_timestamp);
779
780
781 /*
782 ** Network maps
783 */
784
785 CREATE TABLE maps
786 (
787 map_id integer not null,
788 map_name varchar(255) not null,
789 description SQL_TEXT not null,
790 root_object_id integer not null,
791 PRIMARY KEY(map_id)
792 ) TABLE_TYPE;
793
794
795 /*
796 ** Map ACLs
797 */
798
799 CREATE TABLE map_access_lists
800 (
801 map_id integer not null,
802 user_id integer not null,
803 access_rights integer not null,
804 PRIMARY KEY(map_id,user_id)
805 ) TABLE_TYPE;
806
807
808 /*
809 ** Submaps of network maps
810 */
811
812 CREATE TABLE submaps
813 (
814 map_id integer not null,
815 submap_id integer not null, // Equal to object id
816 attributes integer not null,
817 PRIMARY KEY(map_id,submap_id)
818 ) TABLE_TYPE;
819
820
821 /*
822 ** Object positioning on submap
823 */
824
825 CREATE TABLE submap_object_positions
826 (
827 map_id integer not null,
828 submap_id integer not null,
829 object_id integer not null,
830 x integer not null,
831 y integer not null,
832 PRIMARY KEY(map_id,submap_id,object_id)
833 ) TABLE_TYPE;
834
835
836 /*
837 ** Object links on submap
838 */
839
840 CREATE TABLE submap_links
841 (
842 map_id integer not null,
843 submap_id integer not null,
844 object_id1 integer not null,
845 object_id2 integer not null,
846 link_type integer not null,
847 PRIMARY KEY(map_id,submap_id,object_id1,object_id2)
848 ) TABLE_TYPE;