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