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