code refactoring; preparation for zoning support
[public/netxms.git] / sql / schema.in
1 /*
2 ** NetXMS Database Schema
3 **
4 ** ex: syntax=sql
5 */
6
7
8 /*
9 ** Metadata
10 */
11
12 CREATE TABLE metadata
13 (
14 var_name varchar(63) not null,
15 var_value varchar(255) not null,
16 PRIMARY KEY(var_name)
17 ) TABLE_TYPE;
18
19
20 /*
21 ** System configuration table
22 */
23
24 CREATE TABLE config
25 (
26 var_name varchar(63) not null,
27 var_value varchar(255) not null,
28 #ifdef DB_ORACLE
29 is_visible integer default 1 not null,
30 need_server_restart integer default 0 not null,
31 #else
32 is_visible integer not null default 1,
33 need_server_restart integer not null default 0,
34 #endif
35 PRIMARY KEY(var_name)
36 ) TABLE_TYPE;
37
38
39 /*
40 ** System configuration data (for large data)
41 */
42
43 CREATE TABLE config_clob
44 (
45 var_name varchar(63) not null,
46 var_value SQL_TEXT not null,
47 PRIMARY KEY(var_name)
48 ) TABLE_TYPE;
49
50
51 /*
52 ** Users
53 */
54
55 CREATE TABLE users
56 (
57 id integer not null,
58 guid varchar(36) not null,
59 name varchar(63) not null,
60 password varchar(48) not null,
61 system_access integer not null,
62 flags integer not null,
63 full_name varchar(127) not null,
64 description varchar(255) not null,
65 grace_logins integer not null,
66 auth_method integer not null,
67 cert_mapping_method integer not null,
68 cert_mapping_data SQL_TEXT not null,
69 auth_failures integer not null,
70 last_passwd_change integer not null,
71 min_passwd_length integer not null,
72 disabled_until integer not null,
73 last_login integer not null,
74 password_history SQL_TEXT null,
75 PRIMARY KEY(id)
76 ) TABLE_TYPE;
77
78
79 /*
80 ** User groups
81 */
82
83 CREATE TABLE user_groups
84 (
85 id integer not null,
86 guid varchar(36) not null,
87 name varchar(63) not null,
88 system_access integer not null,
89 flags integer not null,
90 description varchar(255) not null,
91 PRIMARY KEY(id)
92 ) TABLE_TYPE;
93
94
95 /*
96 ** Users to groups mapping
97 */
98
99 CREATE TABLE user_group_members
100 (
101 group_id integer not null,
102 user_id integer not null,
103 PRIMARY KEY(group_id,user_id)
104 ) TABLE_TYPE;
105
106
107 /*
108 ** User profiles
109 */
110
111 CREATE TABLE user_profiles
112 (
113 user_id integer not null,
114 var_name varchar(255) not null,
115 var_value SQL_TEXT not null,
116 PRIMARY KEY(user_id,var_name)
117 ) TABLE_TYPE;
118
119
120 /*
121 ** Custom attributes for user database objects
122 */
123
124 CREATE TABLE userdb_custom_attributes
125 (
126 object_id integer not null,
127 attr_name varchar(255) not null,
128 attr_value SQL_TEXT not null,
129 PRIMARY KEY(object_id,attr_name)
130 ) TABLE_TYPE;
131
132
133 /*
134 ** Common object properties
135 */
136
137 CREATE TABLE object_properties
138 (
139 object_id integer not null,
140 guid varchar(36) not null,
141 name varchar(63) not null,
142 status integer not null,
143 is_deleted integer not null,
144 is_system integer not null,
145 last_modified integer not null,
146 inherit_access_rights integer not null,
147 status_calc_alg integer not null,
148 status_prop_alg integer not null,
149 status_fixed_val integer not null,
150 status_shift integer not null,
151 status_translation varchar(8) not null,
152 status_single_threshold integer not null,
153 status_thresholds varchar(8) not null,
154 comments SQL_TEXT null,
155 location_type integer not null, // 0 = unset, 1 = manual, 2 = GPS
156 latitude varchar(20),
157 longitude varchar(20),
158 image varchar(36) not null,
159 PRIMARY KEY(object_id)
160 ) TABLE_TYPE;
161
162
163 /*
164 ** Object custom attributes
165 */
166
167 CREATE TABLE object_custom_attributes
168 (
169 object_id integer not null,
170 attr_name varchar(127) not null,
171 attr_value SQL_TEXT not null,
172 PRIMARY KEY(object_id,attr_name)
173 ) TABLE_TYPE;
174
175
176 /*
177 ** Zone object
178 */
179
180 CREATE TABLE zones
181 (
182 id integer not null, // Zone object's ID
183 zone_guid integer not null, // Globally unique ID for zone
184 agent_proxy integer not null,
185 snmp_proxy integer not null,
186 icmp_proxy integer not null,
187 PRIMARY KEY(id)
188 ) TABLE_TYPE;
189
190
191 /*
192 ** Nodes information
193 */
194
195 CREATE TABLE nodes
196 (
197 id integer not null,
198 primary_name varchar(255) null,
199 primary_ip varchar(15) not null,
200 node_flags integer not null,
201 snmp_version integer not null,
202 snmp_port integer not null,
203 community varchar(127) null,
204 usm_auth_password varchar(127) null,
205 usm_priv_password varchar(127) null,
206 usm_methods integer not null,
207 snmp_oid varchar(255) null,
208 auth_method integer not null,
209 secret varchar(64) null,
210 agent_port integer not null,
211 status_poll_type integer not null,
212 agent_version varchar(63) null,
213 platform_name varchar(63) null,
214 poller_node_id integer not null,
215 zone_guid integer not null,
216 proxy_node integer not null,
217 snmp_proxy integer not null,
218 required_polls integer not null,
219 uname varchar(255) null,
220 use_ifxtable integer not null,
221 snmp_sys_name varchar(127) null,
222 PRIMARY KEY(id)
223 ) TABLE_TYPE;
224
225
226 /*
227 ** Clusters information
228 */
229
230 CREATE TABLE clusters
231 (
232 id integer not null,
233 cluster_type integer not null,
234 PRIMARY KEY(id)
235 ) TABLE_TYPE;
236
237
238 /*
239 ** Cluster members
240 */
241
242 CREATE TABLE cluster_members
243 (
244 cluster_id integer not null,
245 node_id integer not null,
246 PRIMARY KEY(cluster_id,node_id)
247 ) TABLE_TYPE;
248
249
250 /*
251 ** Cluster interconnect subnets
252 */
253
254 CREATE TABLE cluster_sync_subnets
255 (
256 cluster_id integer not null,
257 subnet_addr varchar(15) not null,
258 subnet_mask varchar(15) not null,
259 PRIMARY KEY(cluster_id,subnet_addr)
260 ) TABLE_TYPE;
261
262
263 /*
264 ** Cluster resources
265 */
266
267 CREATE TABLE cluster_resources
268 (
269 cluster_id integer not null,
270 resource_id integer not null,
271 resource_name varchar(255) not null,
272 ip_addr varchar(15) not null,
273 current_owner integer not null,
274 PRIMARY KEY(cluster_id,resource_id)
275 ) TABLE_TYPE;
276
277
278 /*
279 ** Subnets
280 */
281
282 CREATE TABLE subnets
283 (
284 id integer not null,
285 ip_addr varchar(15) not null,
286 ip_netmask varchar(15) not null,
287 zone_guid integer not null,
288 synthetic_mask integer not null,
289 PRIMARY KEY(id)
290 ) TABLE_TYPE;
291
292
293 /*
294 ** Nodes' interfaces
295 */
296
297 CREATE TABLE interfaces
298 (
299 id integer not null,
300 node_id integer not null,
301 ip_addr varchar(15) not null,
302 ip_netmask varchar(15) not null,
303 if_type integer not null,
304 if_index integer not null,
305 bridge_port integer not null,
306 phy_slot integer not null,
307 phy_port integer not null,
308 peer_node_id integer not null,
309 peer_if_id integer not null,
310 mac_addr varchar(15) not null,
311 synthetic_mask integer not null,
312 required_polls integer not null,
313 PRIMARY KEY(id)
314 ) TABLE_TYPE;
315
316
317 /*
318 ** Network services
319 */
320
321 CREATE TABLE network_services
322 (
323 id integer not null,
324 node_id integer not null,
325 service_type integer not null,
326 ip_bind_addr varchar(15) not null,
327 ip_proto integer not null,
328 ip_port integer not null,
329 check_request SQL_TEXT not null,
330 check_responce SQL_TEXT not null,
331 poller_node_id integer not null,
332 required_polls integer not null,
333 PRIMARY KEY(id)
334 ) TABLE_TYPE;
335
336
337 /*
338 ** VPN connectors
339 */
340
341 CREATE TABLE vpn_connectors
342 (
343 id integer not null,
344 node_id integer not null,
345 peer_gateway integer not null,
346 PRIMARY KEY(id)
347 ) TABLE_TYPE;
348
349
350 /*
351 ** VPN connector networks
352 */
353
354 CREATE TABLE vpn_connector_networks
355 (
356 vpn_id integer not null,
357 network_type integer not null, // 0 == local, 1 == remote
358 ip_addr varchar(15) not null,
359 ip_netmask varchar(15) not null,
360 PRIMARY KEY(vpn_id,ip_addr)
361 ) TABLE_TYPE;
362
363
364 /*
365 ** Container objects
366 */
367
368 CREATE TABLE containers
369 (
370 id integer not null,
371 category integer not null,
372 object_class integer not null,
373 enable_auto_bind integer not null,
374 auto_bind_filter SQL_TEXT not null,
375 PRIMARY KEY(id)
376 ) TABLE_TYPE;
377
378
379 /*
380 ** Condition objects
381 */
382
383 CREATE TABLE conditions
384 (
385 id integer not null,
386 activation_event integer not null,
387 deactivation_event integer not null,
388 source_object integer not null,
389 active_status integer not null,
390 inactive_status integer not null,
391 script SQL_TEXT not null,
392 PRIMARY KEY(id)
393 ) TABLE_TYPE;
394
395
396 /*
397 ** DCI to condition mapping
398 */
399
400 CREATE TABLE cond_dci_map
401 (
402 condition_id integer not null,
403 sequence_number integer not null,
404 dci_id integer not null,
405 node_id integer not null,
406 dci_func integer not null,
407 num_polls integer not null,
408 PRIMARY KEY(condition_id,sequence_number)
409 ) TABLE_TYPE;
410
411
412 /*
413 ** Data collection templates
414 */
415
416 CREATE TABLE templates
417 (
418 id integer not null,
419 version integer not null,
420 enable_auto_apply integer not null,
421 apply_filter SQL_TEXT not null,
422 PRIMARY KEY(id)
423 ) TABLE_TYPE;
424
425
426 /*
427 ** Mapping hosts to templates
428 */
429
430 CREATE TABLE dct_node_map
431 (
432 template_id integer not null,
433 node_id integer not null,
434 PRIMARY KEY(template_id,node_id)
435 ) TABLE_TYPE;
436
437
438 /*
439 ** Nodes to subnets mapping
440 */
441
442 CREATE TABLE nsmap
443 (
444 subnet_id integer not null,
445 node_id integer not null,
446 PRIMARY KEY(subnet_id,node_id)
447 ) TABLE_TYPE;
448
449
450 /*
451 ** Container members
452 */
453
454 CREATE TABLE container_members
455 (
456 container_id integer not null,
457 object_id integer not null,
458 PRIMARY KEY(container_id,object_id)
459 ) TABLE_TYPE;
460
461
462 /*
463 ** Container categories
464 */
465
466 CREATE TABLE container_categories
467 (
468 category integer not null,
469 name varchar(63),
470 image_id integer not null,
471 description SQL_TEXT,
472 PRIMARY KEY(category)
473 ) TABLE_TYPE;
474
475
476 /*
477 ** Objects' ACLs
478 */
479
480 CREATE TABLE acl
481 (
482 object_id integer not null,
483 user_id integer not null,
484 access_rights integer not null,
485 PRIMARY KEY(object_id,user_id)
486 ) TABLE_TYPE;
487
488
489 /*
490 ** Trusted nodes - used for cross-node data collection
491 ** Source object is an object providing data (it can be node or condition),
492 ** and target node is a node owning DCI
493 */
494
495 CREATE TABLE trusted_nodes
496 (
497 source_object_id integer not null,
498 target_node_id integer not null,
499 PRIMARY KEY(source_object_id,target_node_id)
500 ) TABLE_TYPE;
501
502
503 /*
504 ** Data collection items
505 **
506 ** If node_id != 0, it's an item bound to node, and template_id points to
507 ** the template used for creating this item. In this case, template_id = 0
508 ** means that item was created manually.
509 ** If node_id = 0, it's a template item, and template_id points to a template
510 ** this item belongs to.
511 ** If both node_id and template_id is 0, it's an error.
512 */
513
514 CREATE TABLE items
515 (
516 item_id integer not null,
517 node_id integer not null,
518 template_id integer not null,
519 template_item_id integer not null,
520 name varchar(255) null,
521 description varchar(255) null,
522 source integer not null, // 0 for internal or 1 for native agent or 2 for SNMP
523 snmp_port integer not null,
524 datatype integer not null,
525 polling_interval integer not null,
526 retention_time integer not null,
527 adv_schedule integer not null, // 1 if item has advanced schedule
528 status integer not null, // ACTIVE, DISABLED or NOT_SUPPORTED
529 delta_calculation integer not null,
530 transformation SQL_TEXT, // Transformation script
531 instance varchar(255) null, // Free form text which can be used in events
532 system_tag varchar(255) null, // System tag
533 all_thresholds integer not null, // 1 if all thresholds should be processed at every check
534 resource_id integer not null, // associated cluster resource ID
535 proxy_node integer not null, // ID of proxy node (for SNMP and agent items)
536 base_units integer not null, // bytes, seconds, etc.
537 unit_multiplier integer not null, // kilo, mega, milli, etc.
538 custom_units_name varchar(63), // units name of base_units = CUSTOM
539 perftab_settings SQL_TEXT, // Settings for dispklaying graph on performance tab
540 PRIMARY KEY(item_id)
541 ) TABLE_TYPE;
542
543
544 /*
545 ** Schedules for DCIs
546 */
547
548 CREATE TABLE dci_schedules
549 (
550 schedule_id integer not null,
551 item_id integer not null,
552 schedule varchar(255) not null,
553 PRIMARY KEY(item_id,schedule_id)
554 ) TABLE_TYPE;
555
556
557 /*
558 ** Latest raw values for all data collection items
559 */
560
561 CREATE TABLE raw_dci_values
562 (
563 item_id integer not null,
564 raw_value varchar(255) null,
565 last_poll_time integer not null,
566 PRIMARY KEY(item_id)
567 ) TABLE_TYPE;
568
569 #if !defined(DB_ORACLE) && !defined(DB_DB2) && !defined(DB_POSTGRESQL)
570 CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id);
571 #endif
572
573
574 /*
575 ** Events configuration
576 */
577
578 CREATE TABLE event_cfg
579 (
580 event_code integer not null,
581 event_name varchar(63) not null, // Short event name
582 severity integer not null,
583 flags integer not null,
584 message varchar(255) not null, // Message template
585 description SQL_TEXT not null,
586 PRIMARY KEY(event_code)
587 ) TABLE_TYPE;
588
589
590 /*
591 ** Event log
592 */
593
594 CREATE TABLE event_log
595 (
596 event_id SQL_INT64 not null,
597 event_code integer not null,
598 event_timestamp integer not null,
599 event_source integer not null, // Source object ID
600 event_severity integer not null,
601 event_message varchar(255) null,
602 root_event_id SQL_INT64 not null,
603 /* Non-zero if current event correlates to some other event */
604 user_tag varchar(63) null,
605 PRIMARY KEY(event_id)
606 ) TABLE_TYPE;
607
608 CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp);
609
610
611 /*
612 ** Actions on events
613 */
614
615 CREATE TABLE actions
616 (
617 action_id integer not null,
618 action_name varchar(63) not null,
619 action_type integer not null,
620 is_disabled integer not null,
621 // Field "rcpt_addr" holds e-mail address for e-mail actions,
622 // phone number for sms actions, and remote host address for
623 // remote execution actions
624 rcpt_addr varchar(255) not null,
625 email_subject varchar(255) not null,
626 // Field "action_data" holds message text for e-mail and sms actions,
627 // command line for external command execution actions, or
628 // action name with optional arguments for remote execution actions
629 action_data SQL_TEXT not null,
630 PRIMARY KEY(action_id)
631 ) TABLE_TYPE;
632
633
634 /*
635 ** Event groups
636 */
637
638 CREATE TABLE event_groups
639 (
640 id integer not null,
641 name varchar(63) not null,
642 description varchar(255) not null,
643 range_start integer not null,
644 range_end integer not null,
645 PRIMARY KEY(id)
646 ) TABLE_TYPE;
647
648
649 /*
650 ** Event group members
651 */
652
653 CREATE TABLE event_group_members
654 (
655 group_id integer not null,
656 event_code integer not null,
657 PRIMARY KEY(group_id,event_code)
658 ) TABLE_TYPE;
659
660
661 /*
662 ** Event processing policy
663 */
664
665 CREATE TABLE event_policy
666 (
667 rule_id integer not null, // Rule number
668 flags integer not null,
669 comments SQL_TEXT not null,
670 script SQL_TEXT not null,
671 alarm_message varchar(255) not null,
672 alarm_severity integer not null,
673 alarm_key varchar(255) not null, // Alarm key (used for auto termination)
674 alarm_timeout integer not null, // Timeout before sending event
675 alarm_timeout_event integer not null, // Event to be sent on timeout
676 situation_id integer not null, // Situation to update
677 situation_instance varchar(255) not null,
678 PRIMARY KEY(rule_id)
679 ) TABLE_TYPE;
680
681 CREATE TABLE policy_source_list
682 (
683 rule_id integer not null,
684 object_id integer not null,
685 PRIMARY KEY(rule_id,object_id)
686 ) TABLE_TYPE;
687
688 CREATE TABLE policy_event_list
689 (
690 rule_id integer not null,
691 event_code integer not null,
692 PRIMARY KEY(rule_id,event_code)
693 ) TABLE_TYPE;
694
695 CREATE TABLE policy_action_list
696 (
697 rule_id integer not null,
698 action_id integer not null,
699 PRIMARY KEY(rule_id,action_id)
700 ) TABLE_TYPE;
701
702 CREATE TABLE policy_time_range_list
703 (
704 rule_id integer not null,
705 time_range_id integer not null,
706 PRIMARY KEY(rule_id,time_range_id)
707 ) TABLE_TYPE;
708
709 CREATE TABLE policy_situation_attr_list
710 (
711 rule_id integer not null,
712 situation_id integer not null,
713 attr_name varchar(255) not null,
714 attr_value varchar(255) not null,
715 PRIMARY KEY(rule_id,situation_id,attr_name)
716 ) TABLE_TYPE;
717
718
719 /*
720 ** Time range objects
721 */
722
723 CREATE TABLE time_ranges
724 (
725 time_range_id integer not null,
726 wday_mask integer not null, // Bits 0 .. 6 for each day of week
727 mday_mask integer not null, // Bits 0 .. 30 for each day of month
728 month_mask integer not null, // Bits 0 .. 11 for each month
729 time_range varchar(255) not null, // In format HHMMHHMM[,HHMMHHMM[,...]]
730 PRIMARY KEY(time_range_id)
731 ) TABLE_TYPE;
732
733
734 /*
735 ** Deleted objects
736 */
737
738 CREATE TABLE deleted_objects
739 (
740 object_id integer not null,
741 object_class integer not null,
742 name varchar(63) not null,
743 ip_addr varchar(15) not null,
744 ip_netmask varchar(15) not null,
745 PRIMARY KEY(object_id)
746 ) TABLE_TYPE;
747
748
749 /*
750 ** Threshold checking rules
751 */
752
753 CREATE TABLE thresholds
754 (
755 threshold_id integer not null,
756 item_id integer not null,
757 sequence_number integer not null,
758 fire_value varchar(255) not null,
759 rearm_value varchar(255) not null,
760 check_function integer not null,
761 check_operation integer not null,
762 parameter_1 integer not null,
763 parameter_2 integer not null,
764 event_code integer not null,
765 rearm_event_code integer not null,
766 current_state integer not null,
767 repeat_interval integer not null,
768 PRIMARY KEY(threshold_id)
769 ) TABLE_TYPE;
770
771 CREATE INDEX idx_thresholds_item_id ON thresholds(item_id);
772 CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number);
773
774
775 /*
776 ** Alarms
777 */
778
779 CREATE TABLE alarms
780 (
781 alarm_id integer not null, // Unique alarm identifier
782 alarm_state integer not null,
783 hd_state integer not null, // Help desk system state
784 hd_ref varchar(63) null, // Help desk reference
785 creation_time integer not null,
786 last_change_time integer not null,
787 source_object_id integer not null,
788 source_event_code integer not null,
789 source_event_id SQL_INT64 not null,
790 message varchar(255) null,
791 original_severity integer not null,
792 current_severity integer not null,
793 repeat_count integer not null,
794 alarm_key varchar(255) null, // Alarm key (used for auto acknowlegment)
795 ack_by integer not null, // ID of user who was acknowleged alarm
796 term_by integer not null, // ID of user who was terminated alarm
797 timeout integer not null,
798 timeout_event integer not null,
799 PRIMARY KEY(alarm_id)
800 ) TABLE_TYPE;
801
802
803 /*
804 ** Alarm notes
805 */
806
807 CREATE TABLE alarm_notes
808 (
809 note_id integer not null,
810 alarm_id integer not null,
811 change_time integer not null,
812 user_id integer not null, // Last edited by
813 note_text SQL_TEXT not null,
814 PRIMARY KEY(note_id)
815 ) TABLE_TYPE;
816
817 CREATE INDEX idx_alarm_notes_alarm_id ON alarm_notes(alarm_id);
818
819
820 /*
821 ** SNMP trap configuration
822 */
823
824 CREATE TABLE snmp_trap_cfg
825 (
826 trap_id integer not null,
827 snmp_oid varchar(255),
828 event_code integer not null,
829 user_tag varchar(63),
830 description varchar(255),
831 PRIMARY KEY(trap_id)
832 ) TABLE_TYPE;
833
834
835 /*
836 ** SNMP trap parameters mapping
837 */
838
839 CREATE TABLE snmp_trap_pmap
840 (
841 trap_id integer not null,
842 parameter integer not null,
843 snmp_oid varchar(255),
844 description varchar(255),
845 PRIMARY KEY(trap_id,parameter)
846 ) TABLE_TYPE;
847
848
849 /*
850 ** Agent packages
851 */
852
853 CREATE TABLE agent_pkg
854 (
855 pkg_id integer not null,
856 pkg_name varchar(63),
857 version varchar(31),
858 platform varchar(63),
859 pkg_file varchar(255),
860 description varchar(255),
861 PRIMARY KEY(pkg_id)
862 ) TABLE_TYPE;
863
864
865 /*
866 ** Object tools
867 */
868
869 CREATE TABLE object_tools
870 (
871 tool_id integer not null,
872 tool_name varchar(255) not null,
873 tool_type integer not null,
874 tool_data SQL_TEXT not null,
875 description varchar(255) not null,
876 flags integer not null,
877 matching_oid varchar(255) not null,
878 confirmation_text varchar(255) not null,
879 PRIMARY KEY(tool_id)
880 ) TABLE_TYPE;
881
882
883 /*
884 ** Access list for object tools
885 */
886
887 CREATE TABLE object_tools_acl
888 (
889 tool_id integer not null,
890 user_id integer not null,
891 PRIMARY KEY(tool_id,user_id)
892 ) TABLE_TYPE;
893
894
895 /*
896 ** Configuration of columns for SNMP_TABLE and AGENT_TABLE type of object tools
897 */
898
899 CREATE TABLE object_tools_table_columns
900 (
901 tool_id integer not null,
902 col_number integer not null,
903 col_name varchar(255),
904 col_oid varchar(255),
905 col_format integer, // Column format (integer, string, etc.)
906 col_substr integer, // Number of matching substring in regexp
907 PRIMARY KEY(tool_id,col_number)
908 ) TABLE_TYPE;
909
910
911 /*
912 ** Stored syslog messages
913 */
914
915 CREATE TABLE syslog
916 (
917 msg_id SQL_INT64 not null,
918 msg_timestamp integer not null,
919 facility integer not null,
920 severity integer not null,
921 source_object_id integer not null,
922 hostname varchar(127) null,
923 msg_tag varchar(32) null,
924 msg_text SQL_TEXT null,
925 PRIMARY KEY(msg_id)
926 ) TABLE_TYPE;
927
928 CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp);
929
930
931 /*
932 ** Script library
933 */
934
935 CREATE TABLE script_library
936 (
937 script_id integer not null,
938 script_name varchar(255) not null,
939 script_code SQL_TEXT null,
940 PRIMARY KEY(script_id)
941 ) TABLE_TYPE;
942
943
944 /*
945 ** Extended SNMP trap log
946 */
947
948 CREATE TABLE snmp_trap_log
949 (
950 trap_id SQL_INT64 not null,
951 trap_timestamp integer not null,
952 ip_addr varchar(15) not null,
953 object_id integer not null,
954 trap_oid varchar(255) not null,
955 trap_varlist SQL_TEXT null,
956 PRIMARY KEY(trap_id)
957 ) TABLE_TYPE;
958
959 CREATE INDEX idx_snmp_trap_log_tt ON snmp_trap_log(trap_timestamp);
960
961
962 /*
963 ** Network maps
964 */
965
966 CREATE TABLE maps
967 (
968 map_id integer not null,
969 map_name varchar(255) not null,
970 description SQL_TEXT not null,
971 root_object_id integer not null,
972 PRIMARY KEY(map_id)
973 ) TABLE_TYPE;
974
975
976 /*
977 ** Map ACLs
978 */
979
980 CREATE TABLE map_access_lists
981 (
982 map_id integer not null,
983 user_id integer not null,
984 access_rights integer not null,
985 PRIMARY KEY(map_id,user_id)
986 ) TABLE_TYPE;
987
988
989 /*
990 ** Submaps of network maps
991 */
992
993 CREATE TABLE submaps
994 (
995 map_id integer not null,
996 submap_id integer not null, // Equal to object id
997 attributes integer not null,
998 PRIMARY KEY(map_id,submap_id)
999 ) TABLE_TYPE;
1000
1001
1002 /*
1003 ** Object positioning on submap
1004 */
1005
1006 CREATE TABLE submap_object_positions
1007 (
1008 map_id integer not null,
1009 submap_id integer not null,
1010 object_id integer not null,
1011 x integer not null,
1012 y integer not null,
1013 PRIMARY KEY(map_id,submap_id,object_id)
1014 ) TABLE_TYPE;
1015
1016
1017 /*
1018 ** Object links on submap
1019 */
1020
1021 CREATE TABLE submap_links
1022 (
1023 map_id integer not null,
1024 submap_id integer not null,
1025 object_id1 integer not null,
1026 object_id2 integer not null,
1027 link_type integer not null,
1028 port1 varchar(255) not null,
1029 port2 varchar(255) not null,
1030 PRIMARY KEY(map_id,submap_id,object_id1,object_id2)
1031 ) TABLE_TYPE;
1032
1033
1034 /*
1035 ** Agent configurations
1036 */
1037
1038 CREATE TABLE agent_configs
1039 (
1040 config_id integer not null,
1041 config_name varchar(255) not null,
1042 config_file SQL_TEXT not null,
1043 config_filter SQL_TEXT not null,
1044 sequence_number integer not null,
1045 PRIMARY KEY(config_id)
1046 ) TABLE_TYPE;
1047
1048
1049 /*
1050 ** Address lists
1051 */
1052
1053 CREATE TABLE address_lists
1054 (
1055 list_type integer not null, // discovery filter, etc.
1056 community_id integer not null, // community id for snmp community addr list, otherwise 0
1057 addr_type integer not null, // 0 - addr/mask, 1 - address range
1058 addr1 varchar(15) not null,
1059 addr2 varchar(15) not null,
1060 PRIMARY KEY(list_type,community_id,addr_type,addr1,addr2)
1061 ) TABLE_TYPE;
1062
1063 CREATE INDEX idx_address_lists_list_type ON address_lists(list_type);
1064
1065
1066 /*
1067 ** Graphs
1068 */
1069
1070 CREATE TABLE graphs
1071 (
1072 graph_id integer not null,
1073 owner_id integer not null,
1074 name varchar(255) not null,
1075 config SQL_TEXT not null,
1076 PRIMARY KEY(graph_id)
1077 ) TABLE_TYPE;
1078
1079
1080 /*
1081 ** Graph access lists
1082 */
1083
1084 CREATE TABLE graph_acl
1085 (
1086 graph_id integer not null,
1087 user_id integer not null,
1088 user_rights integer not null,
1089 PRIMARY KEY(graph_id,user_id)
1090 ) TABLE_TYPE;
1091
1092
1093 /*
1094 ** Certificates
1095 */
1096
1097 CREATE TABLE certificates
1098 (
1099 cert_id integer not null,
1100 cert_type integer not null,
1101 cert_data SQL_TEXT not null, // Certificate in PEM format
1102 subject SQL_TEXT not null,
1103 comments SQL_TEXT not null,
1104 PRIMARY KEY(cert_id)
1105 ) TABLE_TYPE;
1106
1107
1108
1109 /*
1110 ** Audit log
1111 */
1112
1113 CREATE TABLE audit_log
1114 (
1115 record_id integer not null,
1116 timestamp integer not null,
1117 subsystem varchar(32) not null,
1118 success integer not null,
1119 user_id integer not null, // 0x7FFFFFFF for system
1120 workstation varchar(63) not null,
1121 object_id integer not null,
1122 message SQL_TEXT null,
1123 PRIMARY KEY(record_id)
1124 ) TABLE_TYPE;
1125
1126
1127 /*
1128 ** Situations
1129 */
1130
1131 CREATE TABLE situations
1132 (
1133 id integer not null,
1134 name varchar(127) not null,
1135 comments SQL_TEXT not null,
1136 PRIMARY KEY(id)
1137 ) TABLE_TYPE;
1138
1139
1140 /*
1141 ** List of possible community strings
1142 */
1143
1144 CREATE TABLE snmp_communities
1145 (
1146 id integer not null,
1147 community varchar(255) not null,
1148 PRIMARY KEY(id)
1149 ) TABLE_TYPE;
1150
1151
1152 /*
1153 ** Maps for XULRunner client and web clients
1154 */
1155
1156 CREATE TABLE web_maps
1157 (
1158 id integer not null,
1159 title varchar(63) not null,
1160 properties SQL_TEXT not null,
1161 data SQL_TEXT not null,
1162 PRIMARY KEY(id)
1163 ) TABLE_TYPE;
1164
1165
1166
1167 /*
1168 ** Agent policies - common attributes
1169 */
1170
1171 CREATE TABLE ap_common
1172 (
1173 id integer not null,
1174 policy_type integer not null,
1175 version integer not null,
1176 description SQL_TEXT null,
1177 PRIMARY KEY(id)
1178 ) TABLE_TYPE;
1179
1180
1181 /*
1182 ** Agent policies - node binding
1183 */
1184
1185 CREATE TABLE ap_bindings
1186 (
1187 policy_id integer not null,
1188 node_id integer not null,
1189 PRIMARY KEY(policy_id,node_id)
1190 ) TABLE_TYPE;
1191
1192
1193 /*
1194 ** Agent policies - configuration files
1195 */
1196
1197 CREATE TABLE ap_config_files
1198 (
1199 policy_id integer not null,
1200 file_content SQL_TEXT null,
1201 PRIMARY KEY(policy_id)
1202 ) TABLE_TYPE;
1203
1204 /*
1205 ** Default SNMP v3 USM credentials
1206 */
1207 CREATE TABLE usm_credentials
1208 (
1209 id integer not null,
1210 user_name varchar(255) not null,
1211 auth_method integer not null,
1212 priv_method integer not null,
1213 auth_password varchar(255),
1214 priv_password varchar(255),
1215 PRIMARY KEY(id)
1216 ) TABLE_TYPE;
1217
1218 /*
1219 ** Network maps
1220 */
1221 CREATE TABLE network_maps
1222 (
1223 id integer not null,
1224 map_type integer not null,
1225 layout integer not null,
1226 seed integer not null,
1227 background varchar(36) null,
1228 PRIMARY KEY(id)
1229 ) TABLE_TYPE;
1230
1231 /*
1232 ** Elements of network maps
1233 */
1234 CREATE TABLE network_map_elements
1235 (
1236 map_id integer not null,
1237 element_id integer not null,
1238 element_type integer not null,
1239 element_data SQL_TEXT not null,
1240 PRIMARY KEY(map_id,element_id)
1241 ) TABLE_TYPE;
1242
1243 /*
1244 ** Links on network maps
1245 */
1246
1247 CREATE TABLE network_map_links
1248 (
1249 map_id integer not null,
1250 element1 integer not null,
1251 element2 integer not null,
1252 link_type integer not null,
1253 link_name varchar(255) null,
1254 connector_name1 varchar(63) null,
1255 connector_name2 varchar(63) null,
1256 PRIMARY KEY(map_id,element1,element2)
1257 ) TABLE_TYPE;
1258
1259
1260 /*
1261 ** Image Library
1262 */
1263
1264 CREATE TABLE images
1265 (
1266 guid varchar(36) not null,
1267 name varchar(255) not null,
1268 category varchar(255) not null,
1269 mimetype varchar(32) not null,
1270 protected integer default 0,
1271 PRIMARY KEY(guid),
1272 UNIQUE(name, category)
1273 ) TABLE_TYPE;