Implemented functionality for zone specific SNMP credentials. Fixes #NX-1199
[public/netxms.git] / sql / schema.in
1 /*
2 ** NetXMS Database Schema
3 **
4 ** ex: syntax=sql ts=2 sw=2 expandtab
5 */
6
7 /*
8 ** Metadata
9 */
10 CREATE TABLE metadata
11 (
12 var_name varchar(63) not null,
13 var_value varchar(255) not null,
14 PRIMARY KEY(var_name)
15 ) TABLE_TYPE;
16
17 /*
18 ** System configuration table
19 */
20 CREATE TABLE config
21 (
22 var_name varchar(63) not null,
23 var_value varchar(2000) null,
24 default_value varchar(2000) null,
25 #ifdef DB_ORACLE
26 is_visible integer default 1 not null,
27 need_server_restart integer default 0 not null,
28 data_type char(1) default 'S' not null,
29 is_public char(1) default 'N' not null,
30 #else
31 is_visible integer not null default 1,
32 need_server_restart integer not null default 0,
33 data_type char(1) not null default 'S',
34 is_public char(1) not null default 'N',
35 #endif
36 description varchar(450) null,
37 possible_values SQL_TEXT null,
38 PRIMARY KEY(var_name)
39 ) TABLE_TYPE;
40
41 /*
42 ** System configuration data (for large data)
43 */
44 CREATE TABLE config_clob
45 (
46 var_name varchar(63) not null,
47 var_value SQL_TEXT null,
48 PRIMARY KEY(var_name)
49 ) TABLE_TYPE;
50
51 /*
52 ** System configuration possible values
53 */
54 CREATE TABLE config_values
55 (
56 var_name varchar(63) not null,
57 var_value varchar(15) not null,
58 var_description varchar(255) null,
59 PRIMARY KEY(var_name,var_value)
60 ) TABLE_TYPE;
61
62 /*
63 ** Users
64 */
65 CREATE TABLE users
66 (
67 id integer not null,
68 guid varchar(36) not null,
69 name varchar(63) not null,
70 password varchar(127) not null,
71 system_access SQL_INT64 not null,
72 flags integer not null,
73 full_name varchar(127) null,
74 description varchar(255) null,
75 grace_logins integer not null,
76 auth_method integer not null,
77 cert_mapping_method integer not null,
78 cert_mapping_data SQL_TEXT null,
79 auth_failures integer not null,
80 last_passwd_change integer not null,
81 min_passwd_length integer not null,
82 disabled_until integer not null,
83 last_login integer not null,
84 password_history SQL_TEXT null,
85 xmpp_id varchar(127) null,
86 ldap_dn SQL_TEXT null,
87 ldap_unique_id varchar(64) null,
88 created integer not null,
89 PRIMARY KEY(id)
90 ) TABLE_TYPE;
91
92 /*
93 ** User groups
94 */
95
96 CREATE TABLE user_groups
97 (
98 id integer not null,
99 guid varchar(36) not null,
100 name varchar(63) not null,
101 system_access SQL_INT64 not null,
102 flags integer not null,
103 description varchar(255),
104 ldap_dn SQL_TEXT null,
105 ldap_unique_id varchar(64) null,
106 created integer not null,
107 PRIMARY KEY(id)
108 ) TABLE_TYPE;
109
110 /*
111 ** Users to groups mapping
112 */
113
114 CREATE TABLE user_group_members
115 (
116 group_id integer not null,
117 user_id integer not null,
118 PRIMARY KEY(group_id,user_id)
119 ) TABLE_TYPE;
120
121 /*
122 ** User profiles
123 */
124
125 CREATE TABLE user_profiles
126 (
127 user_id integer not null,
128 var_name varchar(255) not null,
129 var_value SQL_TEXT not null,
130 PRIMARY KEY(user_id,var_name)
131 ) TABLE_TYPE;
132
133 /*
134 ** Custom attributes for user database objects
135 */
136
137 CREATE TABLE userdb_custom_attributes
138 (
139 object_id integer not null,
140 attr_name varchar(255) not null,
141 attr_value SQL_TEXT not null,
142 PRIMARY KEY(object_id,attr_name)
143 ) TABLE_TYPE;
144
145 /*
146 ** Common object properties
147 */
148
149 CREATE TABLE object_properties
150 (
151 object_id integer not null,
152 guid varchar(36) not null,
153 name varchar(63) not null,
154 flags integer not null,
155 state integer not null,
156 status integer not null,
157 is_deleted integer not null,
158 is_system integer not null,
159 last_modified integer not null,
160 inherit_access_rights integer not null,
161 status_calc_alg integer not null,
162 status_prop_alg integer not null,
163 status_fixed_val integer not null,
164 status_shift integer not null,
165 status_translation varchar(8) not null,
166 status_single_threshold integer not null,
167 status_thresholds varchar(8) not null,
168 comments SQL_TEXT null,
169 location_type integer not null,
170 latitude varchar(20),
171 longitude varchar(20),
172 location_accuracy integer not null,
173 location_timestamp integer not null,
174 image varchar(36) not null,
175 submap_id integer not null,
176 country varchar(63) null,
177 city varchar(63) null,
178 street_address varchar(255) null,
179 postcode varchar(31) null,
180 maint_mode char(1) not null,
181 maint_event_id SQL_INT64 not null,
182 PRIMARY KEY(object_id)
183 ) TABLE_TYPE;
184
185 /**
186 * Object URLs
187 */
188 CREATE TABLE object_urls
189 (
190 object_id integer not null,
191 url_id integer not null,
192 url varchar(2000) null,
193 description varchar(2000) null,
194 PRIMARY KEY(object_id,url_id)
195 ) TABLE_TYPE;
196
197 /*
198 ** Object custom attributes
199 */
200 CREATE TABLE object_custom_attributes
201 (
202 object_id integer not null,
203 attr_name varchar(127) not null,
204 attr_value SQL_TEXT null,
205 PRIMARY KEY(object_id,attr_name)
206 ) TABLE_TYPE;
207
208 CREATE INDEX idx_ocattr_oid ON object_custom_attributes(object_id);
209
210 /*
211 ** Zone objects
212 */
213 CREATE TABLE zones
214 (
215 id integer not null, // Zone object ID
216 zone_guid integer not null, // Globally unique ID for zone
217 proxy_node integer not null,
218 snmp_ports integer null,
219 PRIMARY KEY(id)
220 ) TABLE_TYPE;
221
222 /*
223 ** Mobile device objects
224 */
225 CREATE TABLE mobile_devices
226 (
227 id integer not null,
228 device_id varchar(64) not null,
229 vendor varchar(64) null,
230 model varchar(128) null,
231 serial_number varchar(64) null,
232 os_name varchar(32) null,
233 os_version varchar(64) null,
234 user_id varchar(64) null,
235 battery_level integer not null,
236 PRIMARY KEY(id)
237 ) TABLE_TYPE;
238
239 /*
240 ** Access point objects
241 */
242 CREATE TABLE access_points
243 (
244 id integer not null,
245 node_id integer not null,
246 mac_address varchar(12) null,
247 vendor varchar(64) null,
248 model varchar(128) null,
249 serial_number varchar(64) null,
250 ap_state integer not null,
251 ap_index integer not null,
252 PRIMARY KEY(id)
253 ) TABLE_TYPE;
254
255 /*
256 ** Rack objects
257 */
258 CREATE TABLE racks
259 (
260 id integer not null,
261 height integer not null,
262 top_bottom_num char(1) not null,
263 PRIMARY KEY(id)
264 ) TABLE_TYPE;
265
266 /*
267 ** Chassis objects
268 */
269 CREATE TABLE chassis
270 (
271 id integer not null,
272 controller_id integer not null,
273 rack_id integer not null,
274 rack_image varchar(36) null,
275 rack_position integer not null,
276 rack_height integer not null,
277 PRIMARY KEY(id)
278 ) TABLE_TYPE;
279
280 /*
281 ** Nodes information
282 */
283 CREATE TABLE nodes
284 (
285 id integer not null,
286 primary_name varchar(255) null,
287 primary_ip varchar(48) not null,
288 tunnel_id varchar(36) null,
289 snmp_version integer not null,
290 snmp_port integer not null,
291 community varchar(127) null,
292 usm_auth_password varchar(127) null,
293 usm_priv_password varchar(127) null,
294 usm_methods integer not null,
295 snmp_oid varchar(255) null,
296 auth_method integer not null,
297 secret varchar(64) null,
298 agent_port integer not null,
299 status_poll_type integer not null,
300 agent_version varchar(63) null,
301 platform_name varchar(63) null,
302 poller_node_id integer not null,
303 zone_guid integer not null,
304 proxy_node integer not null,
305 snmp_proxy integer not null,
306 icmp_proxy integer not null,
307 required_polls integer not null,
308 uname varchar(255) null,
309 use_ifxtable integer not null,
310 snmp_sys_name varchar(127) null,
311 snmp_sys_contact varchar(127) null,
312 snmp_sys_location varchar(127) null,
313 bridge_base_addr varchar(15) null,
314 lldp_id varchar(63) null,
315 down_since integer not null,
316 boot_time integer not null,
317 driver_name varchar(32) null,
318 rack_image varchar(36) null,
319 rack_position integer not null,
320 rack_height integer not null,
321 rack_id integer not null,
322 chassis_id integer not null,
323 agent_cache_mode char(1) not null,
324 last_agent_comm_time integer not null,
325 syslog_msg_count SQL_INT64 not null,
326 snmp_trap_count SQL_INT64 not null,
327 node_type integer not null,
328 node_subtype varchar(127) null,
329 ssh_login varchar(63) null,
330 ssh_password varchar(63) null,
331 ssh_proxy integer not null,
332 port_rows integer null,
333 port_numbering_scheme integer null,
334 agent_comp_mode char(1) not null, // compression mode for agent
335 capabilities integer null,
336 fail_time_snmp integer not null,
337 fail_time_agent integer not null,
338 PRIMARY KEY(id)
339 ) TABLE_TYPE;
340
341 /*
342 ** Clusters information
343 */
344
345 CREATE TABLE clusters
346 (
347 id integer not null,
348 cluster_type integer not null,
349 zone_guid integer not null,
350 PRIMARY KEY(id)
351 ) TABLE_TYPE;
352
353 /*
354 ** Cluster members
355 */
356
357 CREATE TABLE cluster_members
358 (
359 cluster_id integer not null,
360 node_id integer not null,
361 PRIMARY KEY(cluster_id,node_id)
362 ) TABLE_TYPE;
363
364 /*
365 ** Cluster interconnect subnets
366 */
367
368 CREATE TABLE cluster_sync_subnets
369 (
370 cluster_id integer not null,
371 subnet_addr varchar(48) not null,
372 subnet_mask integer not null,
373 PRIMARY KEY(cluster_id,subnet_addr)
374 ) TABLE_TYPE;
375
376 /*
377 ** Cluster resources
378 */
379
380 CREATE TABLE cluster_resources
381 (
382 cluster_id integer not null,
383 resource_id integer not null,
384 resource_name varchar(255),
385 ip_addr varchar(48) not null,
386 current_owner integer not null,
387 PRIMARY KEY(cluster_id,resource_id)
388 ) TABLE_TYPE;
389
390 /*
391 ** Subnets
392 */
393 CREATE TABLE subnets
394 (
395 id integer not null,
396 ip_addr varchar(48) not null,
397 ip_netmask integer not null,
398 zone_guid integer not null,
399 synthetic_mask integer not null,
400 PRIMARY KEY(id)
401 ) TABLE_TYPE;
402
403 /*
404 ** Nodes' interfaces
405 */
406 CREATE TABLE interfaces
407 (
408 id integer not null,
409 node_id integer not null,
410 parent_iface integer not null,
411 if_type integer not null,
412 if_index integer not null,
413 mtu integer not null,
414 speed SQL_INT64 not null,
415 bridge_port integer not null,
416 phy_slot integer not null,
417 phy_port integer not null,
418 peer_node_id integer not null,
419 peer_if_id integer not null,
420 peer_proto integer not null,
421 mac_addr varchar(12) not null,
422 required_polls integer not null,
423 admin_state integer not null,
424 oper_state integer not null,
425 dot1x_pae_state integer not null,
426 dot1x_backend_state integer not null,
427 description varchar(255) null,
428 alias varchar(255) null,
429 iftable_suffix varchar(127) null,
430 PRIMARY KEY(id)
431 ) TABLE_TYPE;
432
433 /*
434 ** Interface IP addresses
435 */
436 CREATE TABLE interface_address_list
437 (
438 iface_id integer not null,
439 ip_addr varchar(48) not null,
440 ip_netmask integer not null,
441 PRIMARY KEY(iface_id,ip_addr)
442 ) TABLE_TYPE;
443
444 /*
445 ** Network services
446 */
447
448 CREATE TABLE network_services
449 (
450 id integer not null,
451 node_id integer not null,
452 service_type integer not null,
453 ip_bind_addr varchar(48) not null,
454 ip_proto integer not null,
455 ip_port integer not null,
456 check_request SQL_TEXT null,
457 check_responce SQL_TEXT null,
458 poller_node_id integer not null,
459 required_polls integer not null,
460 PRIMARY KEY(id)
461 ) TABLE_TYPE;
462
463 /*
464 ** VPN connectors
465 */
466 CREATE TABLE vpn_connectors
467 (
468 id integer not null,
469 node_id integer not null,
470 peer_gateway integer not null,
471 PRIMARY KEY(id)
472 ) TABLE_TYPE;
473
474 /*
475 ** VPN connector networks
476 */
477 CREATE TABLE vpn_connector_networks
478 (
479 vpn_id integer not null,
480 network_type integer not null, // 0 == local, 1 == remote
481 ip_addr varchar(48) not null,
482 ip_netmask integer not null,
483 PRIMARY KEY(vpn_id,ip_addr)
484 ) TABLE_TYPE;
485
486 /*
487 ** Container objects
488 */
489 CREATE TABLE object_containers
490 (
491 id integer not null,
492 object_class integer not null,
493 auto_bind_filter SQL_TEXT null,
494 PRIMARY KEY(id)
495 ) TABLE_TYPE;
496
497 /*
498 ** Condition objects
499 */
500 CREATE TABLE conditions
501 (
502 id integer not null,
503 activation_event integer not null,
504 deactivation_event integer not null,
505 source_object integer not null,
506 active_status integer not null,
507 inactive_status integer not null,
508 script SQL_TEXT not null,
509 PRIMARY KEY(id)
510 ) TABLE_TYPE;
511
512 /*
513 ** DCI to condition mapping
514 */
515 CREATE TABLE cond_dci_map
516 (
517 condition_id integer not null,
518 sequence_number integer not null,
519 dci_id integer not null,
520 node_id integer not null,
521 dci_func integer not null,
522 num_polls integer not null,
523 PRIMARY KEY(condition_id,sequence_number)
524 ) TABLE_TYPE;
525
526 /**
527 * Data collection templates
528 */
529 CREATE TABLE templates
530 (
531 id integer not null,
532 version integer not null,
533 apply_filter SQL_TEXT null,
534 PRIMARY KEY(id)
535 ) TABLE_TYPE;
536
537 /**
538 * Mapping hosts to templates
539 */
540 CREATE TABLE dct_node_map
541 (
542 template_id integer not null,
543 node_id integer not null,
544 PRIMARY KEY(template_id,node_id)
545 ) TABLE_TYPE;
546
547 /**
548 * Nodes to subnets mapping
549 */
550 CREATE TABLE nsmap
551 (
552 subnet_id integer not null,
553 node_id integer not null,
554 PRIMARY KEY(subnet_id,node_id)
555 ) TABLE_TYPE;
556
557 /**
558 * Container members
559 */
560 CREATE TABLE container_members
561 (
562 container_id integer not null,
563 object_id integer not null,
564 PRIMARY KEY(container_id,object_id)
565 ) TABLE_TYPE;
566
567 /**
568 * Objects' ACLs
569 */
570 CREATE TABLE acl
571 (
572 object_id integer not null,
573 user_id integer not null,
574 access_rights integer not null,
575 PRIMARY KEY(object_id,user_id)
576 ) TABLE_TYPE;
577
578 /**
579 * Trusted nodes - used for cross-node data collection
580 * Source object is an object providing data (it can be node or condition),
581 * and target node is a node owning DCI
582 */
583 CREATE TABLE trusted_nodes
584 (
585 source_object_id integer not null,
586 target_node_id integer not null,
587 PRIMARY KEY(source_object_id,target_node_id)
588 ) TABLE_TYPE;
589
590 /**
591 * Data collection items
592 *
593 * If node_id != 0, it's an item bound to node, and template_id points to
594 * the template used for creating this item. In this case, template_id = 0
595 * means that item was created manually.
596 * If node_id = 0, it's a template item, and template_id points to a template
597 * this item belongs to.
598 * If both node_id and template_id is 0, it's an error.
599 */
600 CREATE TABLE items
601 (
602 item_id integer not null,
603 node_id integer not null,
604 template_id integer not null,
605 template_item_id integer not null,
606 guid varchar(36) not null,
607 name varchar(1023) null,
608 description varchar(255) null,
609 flags integer not null,
610 source integer not null, // 0 for internal or 1 for native agent or 2 for SNMP
611 snmp_port integer not null,
612 datatype integer not null,
613 polling_interval integer not null,
614 retention_time integer not null,
615 status integer not null, // ACTIVE, DISABLED or NOT_SUPPORTED
616 snmp_raw_value_type integer not null,
617 delta_calculation integer not null,
618 transformation SQL_TEXT, // Transformation script
619 instance varchar(255) null, // Free form text which can be used in events
620 system_tag varchar(255) null, // System tag
621 resource_id integer not null, // associated cluster resource ID
622 proxy_node integer not null, // ID of proxy node (for SNMP and agent items)
623 base_units integer not null, // bytes, seconds, etc.
624 unit_multiplier integer not null, // kilo, mega, milli, etc.
625 custom_units_name varchar(63) null, // units name of base_units = CUSTOM
626 perftab_settings SQL_TEXT null, // Settings for displaying graph on performance tab
627 instd_method integer not null,
628 instd_data varchar(255) null,
629 instd_filter SQL_TEXT null,
630 samples integer not null,
631 npe_name varchar(15) null,
632 comments SQL_TEXT null,
633 PRIMARY KEY(item_id)
634 ) TABLE_TYPE;
635
636 CREATE INDEX idx_items_node_id ON items(node_id);
637
638 /*
639 ** Data collection tables
640 */
641 CREATE TABLE dc_tables
642 (
643 item_id integer not null,
644 node_id integer not null,
645 template_id integer not null,
646 template_item_id integer not null,
647 guid varchar(36) not null,
648 name varchar(1023) null,
649 description varchar(255) null,
650 flags integer not null,
651 source integer not null, // 0 for internal or 1 for native agent or 2 for SNMP
652 snmp_port integer not null,
653 polling_interval integer not null,
654 retention_time integer not null,
655 status integer not null, // ACTIVE, DISABLED or NOT_SUPPORTED
656 system_tag varchar(255) null, // System tag
657 resource_id integer not null, // associated cluster resource ID
658 proxy_node integer not null, // ID of proxy node (for SNMP and agent items)
659 perftab_settings SQL_TEXT null, // Settings for displaying graph on performance tab
660 transformation_script SQL_TEXT null,
661 comments SQL_TEXT null,
662 instance varchar(255) null,
663 instd_method integer not null,
664 instd_data varchar(255) null,
665 instd_filter SQL_TEXT null,
666 PRIMARY KEY(item_id)
667 ) TABLE_TYPE;
668
669 CREATE INDEX idx_dc_tables_node_id ON dc_tables(node_id);
670
671 /*
672 ** Columns for data collection tables
673 */
674 CREATE TABLE dc_table_columns
675 (
676 table_id integer not null,
677 sequence_number integer not null,
678 column_name varchar(63) not null,
679 snmp_oid varchar(1023) null, // SNMP OID for this column, valid only for SNMP tables
680 flags integer not null,
681 display_name varchar(255) null,
682 PRIMARY KEY(table_id,column_name)
683 ) TABLE_TYPE;
684
685 /*
686 ** Column name dictionary
687 */
688 CREATE TABLE dct_column_names
689 (
690 column_id integer not null,
691 column_name varchar(63) not null,
692 PRIMARY KEY(column_id)
693 ) TABLE_TYPE;
694
695 /*
696 ** Table thresholds
697 */
698 CREATE TABLE dct_thresholds
699 (
700 id integer not null,
701 table_id integer not null,
702 sequence_number integer not null,
703 activation_event integer not null,
704 deactivation_event integer not null,
705 sample_count integer not null,
706 PRIMARY KEY(id)
707 ) TABLE_TYPE;
708
709 /*
710 ** Table threshold conditions
711 */
712 CREATE TABLE dct_threshold_conditions
713 (
714 threshold_id integer not null,
715 group_id integer not null,
716 sequence_number integer not null,
717 column_name varchar(63) null,
718 check_operation integer not null,
719 check_value varchar(255) null,
720 PRIMARY KEY(threshold_id,group_id,sequence_number)
721 ) TABLE_TYPE;
722
723 /*
724 ** Table threshold instances
725 */
726 CREATE TABLE dct_threshold_instances
727 (
728 threshold_id integer not null,
729 instance varchar(255) not null,
730 match_count integer not null,
731 is_active char(1) not null,
732 PRIMARY KEY(threshold_id,instance)
733 ) TABLE_TYPE;
734
735 /*
736 ** Schedules for DCIs
737 */
738 CREATE TABLE dci_schedules
739 (
740 schedule_id integer not null,
741 item_id integer not null,
742 schedule varchar(255) null,
743 PRIMARY KEY(item_id,schedule_id)
744 ) TABLE_TYPE;
745
746 /*
747 ** Latest raw values for all data collection items
748 */
749 CREATE TABLE raw_dci_values
750 (
751 item_id integer not null,
752 raw_value varchar(255) null,
753 transformed_value varchar(255) null,
754 last_poll_time integer not null,
755 PRIMARY KEY(item_id)
756 ) TABLE_TYPE;
757
758 #if !defined(DB_ORACLE) && !defined(DB_DB2) && !defined(DB_POSTGRESQL)
759 CREATE INDEX idx_raw_dci_values_item_id ON raw_dci_values(item_id);
760 #endif
761
762 /*
763 ** Events configuration
764 */
765 CREATE TABLE event_cfg
766 (
767 event_code integer not null,
768 event_name varchar(63) not null, // Short event name
769 guid varchar(36) not null,
770 severity integer not null,
771 flags integer not null,
772 message varchar(2000) null, /* Message template */
773 description SQL_TEXT null,
774 PRIMARY KEY(event_code)
775 ) TABLE_TYPE;
776
777 /*
778 ** Event log
779 */
780 CREATE TABLE event_log
781 (
782 event_id SQL_INT64 not null,
783 event_code integer not null,
784 event_timestamp integer not null,
785 event_source integer not null, /* Source object ID */
786 dci_id integer not null, /* ID of related DCI or 0 */
787 event_severity integer not null,
788 event_message varchar(2000) null,
789 root_event_id SQL_INT64 not null, /* Non-zero if current event correlates to some other event */
790 user_tag varchar(63) null,
791 PRIMARY KEY(event_id)
792 ) TABLE_TYPE;
793
794 CREATE INDEX idx_event_log_event_timestamp ON event_log(event_timestamp);
795
796 CREATE INDEX idx_event_log_source ON event_log(event_source);
797
798 #if defined(DB_POSTGRESQL)
799 CREATE INDEX idx_event_log_root_id ON event_log(root_event_id) WHERE root_event_id > 0;
800 #elif defined(DB_ORACLE)
801 CREATE OR REPLACE FUNCTION zero_to_null(id NUMBER)
802 RETURN NUMBER
803 DETERMINISTIC
804 AS BEGIN
805 IF id > 0 THEN
806 RETURN id;
807 ELSE
808 RETURN NULL;
809 END IF;
810 END;
811 /
812 CREATE INDEX idx_event_log_root_id ON event_log(zero_to_null(root_event_id));
813 #else
814 CREATE INDEX idx_event_log_root_id ON event_log(root_event_id);
815 #endif
816
817 /*
818 ** Actions on events
819 */
820 CREATE TABLE actions
821 (
822 action_id integer not null,
823 action_name varchar(63) not null,
824 action_type integer not null,
825 is_disabled integer not null,
826 // Field "rcpt_addr" holds e-mail address for e-mail actions,
827 // phone number for sms actions, and remote host address for
828 // remote execution actions
829 rcpt_addr varchar(255) null,
830 email_subject varchar(255) null,
831 // Field "action_data" holds message text for e-mail and sms actions,
832 // command line for external command execution actions, or
833 // action name with optional arguments for remote execution actions
834 action_data SQL_TEXT null,
835 PRIMARY KEY(action_id)
836 ) TABLE_TYPE;
837
838 /*
839 ** Event groups
840 */
841 CREATE TABLE event_groups
842 (
843 guid varchar(36) not null,
844 id integer not null,
845 name varchar(63) not null,
846 description varchar(255) not null,
847 PRIMARY KEY(id)
848 ) TABLE_TYPE;
849
850 /*
851 ** Event group members
852 */
853 CREATE TABLE event_group_members
854 (
855 group_id integer not null,
856 event_code integer not null,
857 PRIMARY KEY(group_id,event_code)
858 ) TABLE_TYPE;
859
860 /*
861 ** Event processing policy
862 */
863 CREATE TABLE event_policy
864 (
865 rule_id integer not null, // Rule number
866 rule_guid varchar(36) not null,
867 flags integer not null,
868 comments SQL_TEXT null,
869 script SQL_TEXT null,
870 alarm_message varchar(2000) null,
871 alarm_severity integer not null,
872 alarm_key varchar(255) null, // Alarm key (used for auto termination)
873 alarm_timeout integer not null, // Timeout before sending event
874 alarm_timeout_event integer not null, // Event to be sent on timeout
875 PRIMARY KEY(rule_id)
876 ) TABLE_TYPE;
877
878 /**
879 *
880 *
881 */
882 CREATE TABLE policy_source_list
883 (
884 rule_id integer not null,
885 object_id integer not null,
886 PRIMARY KEY(rule_id,object_id)
887 ) TABLE_TYPE;
888
889 /**
890 *
891 */
892 CREATE TABLE policy_event_list
893 (
894 rule_id integer not null,
895 event_code integer not null,
896 PRIMARY KEY(rule_id,event_code)
897 ) TABLE_TYPE;
898
899 /**
900 *
901 */
902 CREATE TABLE policy_action_list
903 (
904 rule_id integer not null,
905 action_id integer not null,
906 PRIMARY KEY(rule_id,action_id)
907 ) TABLE_TYPE;
908
909 /**
910 *
911 */
912 CREATE TABLE policy_pstorage_actions
913 (
914 rule_id integer not null,
915 ps_key varchar(255) not null,
916 value varchar(2000) null,
917 action integer not null,
918 PRIMARY KEY(rule_id,ps_key,action)
919 ) TABLE_TYPE;
920
921 /*
922 ** Threshold checking rules
923 */
924 CREATE TABLE thresholds
925 (
926 threshold_id integer not null,
927 item_id integer not null,
928 sequence_number integer not null,
929 fire_value varchar(255) null,
930 rearm_value varchar(255) null,
931 check_function integer not null,
932 check_operation integer not null,
933 sample_count integer not null,
934 script SQL_TEXT null,
935 event_code integer not null,
936 rearm_event_code integer not null,
937 repeat_interval integer not null,
938 current_state integer not null,
939 current_severity integer not null,
940 match_count integer not null,
941 last_event_timestamp integer not null,
942 PRIMARY KEY(threshold_id)
943 ) TABLE_TYPE;
944
945 CREATE INDEX idx_thresholds_item_id ON thresholds(item_id);
946 CREATE INDEX idx_thresholds_sequence ON thresholds(sequence_number);
947
948 /*
949 ** Alarms
950 */
951 CREATE TABLE alarms
952 (
953 alarm_id integer not null, // Unique alarm identifier
954 alarm_state integer not null,
955 hd_state integer not null, // Help desk system state
956 hd_ref varchar(63) null, // Help desk reference
957 creation_time integer not null,
958 last_change_time integer not null,
959 source_object_id integer not null,
960 source_event_code integer not null,
961 source_event_id SQL_INT64 not null,
962 dci_id integer not null,
963 message varchar(2000) null,
964 original_severity integer not null,
965 current_severity integer not null,
966 repeat_count integer not null,
967 alarm_key varchar(255) null, // Alarm key (used for auto acknowlegment)
968 ack_by integer not null, // ID of user who was acknowleged alarm
969 resolved_by integer not null, // ID of user who was resolved alarm
970 term_by integer not null, // ID of user who was terminated alarm
971 timeout integer not null,
972 timeout_event integer not null,
973 ack_timeout integer not null,
974 alarm_category_ids varchar(255) null,
975 PRIMARY KEY(alarm_id)
976 ) TABLE_TYPE;
977
978 /*
979 ** Alarm notes
980 */
981 CREATE TABLE alarm_notes
982 (
983 note_id integer not null,
984 alarm_id integer not null,
985 change_time integer not null,
986 user_id integer not null, // Last edited by
987 note_text SQL_TEXT null,
988 PRIMARY KEY(note_id)
989 ) TABLE_TYPE;
990
991 CREATE INDEX idx_alarm_notes_alarm_id ON alarm_notes(alarm_id);
992
993 /*
994 ** Source events for alarms
995 */
996 CREATE TABLE alarm_events
997 (
998 alarm_id integer not null,
999 event_id SQL_INT64 not null,
1000 event_code integer not null,
1001 event_name varchar(63) null,
1002 severity integer not null,
1003 source_object_id integer not null,
1004 event_timestamp integer not null,
1005 message varchar(2000) null,
1006 PRIMARY KEY(alarm_id,event_id)
1007 ) TABLE_TYPE;
1008
1009 CREATE INDEX idx_alarm_events_alarm_id ON alarm_events(alarm_id);
1010
1011 /*
1012 ** Alarm categories
1013 */
1014 CREATE TABLE alarm_categories
1015 (
1016 id integer not null,
1017 name varchar(63) null,
1018 descr varchar(255) null,
1019 PRIMARY KEY(id)
1020 ) TABLE_TYPE;
1021
1022 /*
1023 ** Alarm category access control list
1024 */
1025 CREATE TABLE alarm_category_acl
1026 (
1027 category_id integer not null,
1028 user_id integer not null,
1029 PRIMARY KEY(category_id,user_id)
1030 ) TABLE_TYPE;
1031
1032 /*
1033 ** Alarm-category map
1034 */
1035 CREATE TABLE alarm_category_map
1036 (
1037 alarm_id integer not null,
1038 category_id integer not null,
1039 PRIMARY KEY(alarm_id,category_id)
1040 ) TABLE_TYPE;
1041
1042 /*
1043 ** SNMP trap configuration
1044 */
1045
1046 CREATE TABLE snmp_trap_cfg
1047 (
1048 guid varchar(36) not null,
1049 trap_id integer not null,
1050 snmp_oid varchar(255),
1051 event_code integer not null,
1052 user_tag varchar(63),
1053 description varchar(255),
1054 PRIMARY KEY(trap_id)
1055 ) TABLE_TYPE;
1056
1057 /*
1058 ** SNMP trap parameters mapping
1059 */
1060 CREATE TABLE snmp_trap_pmap
1061 (
1062 trap_id integer not null,
1063 parameter integer not null,
1064 flags integer not null,
1065 snmp_oid varchar(255) null,
1066 description varchar(255) null,
1067 PRIMARY KEY(trap_id,parameter)
1068 ) TABLE_TYPE;
1069
1070 /*
1071 ** Agent packages
1072 */
1073 CREATE TABLE agent_pkg
1074 (
1075 pkg_id integer not null,
1076 pkg_name varchar(63),
1077 version varchar(31),
1078 platform varchar(63),
1079 pkg_file varchar(255),
1080 description varchar(255),
1081 PRIMARY KEY(pkg_id)
1082 ) TABLE_TYPE;
1083
1084 /*
1085 ** Object tools
1086 */
1087 CREATE TABLE object_tools
1088 (
1089 tool_id integer not null,
1090 guid varchar(36) not null,
1091 tool_name varchar(255) null,
1092 tool_type integer not null,
1093 tool_data SQL_TEXT null,
1094 description varchar(255) null,
1095 flags integer not null,
1096 tool_filter SQL_TEXT null,
1097 confirmation_text varchar(255) null,
1098 command_name varchar(255) null,
1099 command_short_name varchar(31) null,
1100 icon SQL_TEXT null,
1101 PRIMARY KEY(tool_id)
1102 ) TABLE_TYPE;
1103
1104 /*
1105 ** Access list for object tools
1106 */
1107 CREATE TABLE object_tools_acl
1108 (
1109 tool_id integer not null,
1110 user_id integer not null,
1111 PRIMARY KEY(tool_id,user_id)
1112 ) TABLE_TYPE;
1113
1114 /*
1115 ** Configuration of columns for SNMP_TABLE and AGENT_TABLE type of object tools
1116 */
1117 CREATE TABLE object_tools_table_columns
1118 (
1119 tool_id integer not null,
1120 col_number integer not null,
1121 col_name varchar(255) null,
1122 col_oid varchar(255) null,
1123 col_format integer, // Column format (integer, string, etc.)
1124 col_substr integer, // Number of matching substring in regexp
1125 PRIMARY KEY(tool_id,col_number)
1126 ) TABLE_TYPE;
1127
1128 /**
1129 * Input fields for object tools
1130 */
1131 CREATE TABLE object_tools_input_fields
1132 (
1133 tool_id integer not null,
1134 name varchar(31) not null,
1135 input_type char(1) not null,
1136 display_name varchar(127) null,
1137 sequence_num integer not null,
1138 config SQL_TEXT null,
1139 PRIMARY KEY(tool_id,name)
1140 ) TABLE_TYPE;
1141
1142 /*
1143 ** Stored syslog messages
1144 */
1145 CREATE TABLE syslog
1146 (
1147 msg_id SQL_INT64 not null,
1148 msg_timestamp integer not null,
1149 facility integer not null,
1150 severity integer not null,
1151 source_object_id integer not null,
1152 hostname varchar(127) null,
1153 msg_tag varchar(32) null,
1154 msg_text SQL_TEXT null,
1155 PRIMARY KEY(msg_id)
1156 ) TABLE_TYPE;
1157
1158 CREATE INDEX idx_syslog_msg_timestamp ON syslog(msg_timestamp);
1159 CREATE INDEX idx_syslog_source ON syslog(source_object_id);
1160
1161 /*
1162 ** Script library
1163 */
1164 CREATE TABLE script_library
1165 (
1166 guid varchar(36) not null,
1167 script_id integer not null,
1168 script_name varchar(255) not null,
1169 script_code SQL_TEXT null,
1170 PRIMARY KEY(script_id)
1171 ) TABLE_TYPE;
1172
1173 /*
1174 ** Extended SNMP trap log
1175 */
1176
1177 CREATE TABLE snmp_trap_log
1178 (
1179 trap_id SQL_INT64 not null,
1180 trap_timestamp integer not null,
1181 ip_addr varchar(48) not null,
1182 object_id integer not null,
1183 trap_oid varchar(255) not null,
1184 trap_varlist SQL_TEXT null,
1185 PRIMARY KEY(trap_id)
1186 ) TABLE_TYPE;
1187
1188 CREATE INDEX idx_snmp_trap_log_tt ON snmp_trap_log(trap_timestamp);
1189 CREATE INDEX idx_snmp_trap_log_oid ON snmp_trap_log(object_id);
1190
1191 /*
1192 ** Agent configurations
1193 */
1194 CREATE TABLE agent_configs
1195 (
1196 config_id integer not null,
1197 config_name varchar(255) not null,
1198 config_file SQL_TEXT not null,
1199 config_filter SQL_TEXT not null,
1200 sequence_number integer not null,
1201 PRIMARY KEY(config_id)
1202 ) TABLE_TYPE;
1203
1204 /*
1205 ** Address lists
1206 */
1207 CREATE TABLE address_lists
1208 (
1209 list_type integer not null, // discovery filter, etc.
1210 community_id integer not null, // community id for snmp community addr list, otherwise 0
1211 addr_type integer not null, // 0 - addr/mask, 1 - address range
1212 addr1 varchar(48) not null,
1213 addr2 varchar(48) not null,
1214 PRIMARY KEY(list_type,community_id,addr_type,addr1,addr2)
1215 ) TABLE_TYPE;
1216
1217 CREATE INDEX idx_address_lists_list_type ON address_lists(list_type);
1218
1219 /*
1220 ** Graphs
1221 */
1222 CREATE TABLE graphs
1223 (
1224 graph_id integer not null,
1225 owner_id integer not null,
1226 flags integer not null,
1227 name varchar(255) not null,
1228 config SQL_TEXT null,
1229 filters SQL_TEXT null,
1230 PRIMARY KEY(graph_id)
1231 ) TABLE_TYPE;
1232
1233 /*
1234 ** Graph access lists
1235 */
1236 CREATE TABLE graph_acl
1237 (
1238 graph_id integer not null,
1239 user_id integer not null,
1240 user_rights integer not null,
1241 PRIMARY KEY(graph_id,user_id)
1242 ) TABLE_TYPE;
1243
1244 /*
1245 ** Certificates
1246 */
1247
1248 CREATE TABLE certificates
1249 (
1250 cert_id integer not null,
1251 cert_type integer not null,
1252 cert_data SQL_TEXT not null, // Certificate in PEM format
1253 subject SQL_TEXT not null,
1254 comments SQL_TEXT not null,
1255 PRIMARY KEY(cert_id)
1256 ) TABLE_TYPE;
1257
1258 /*
1259 ** Audit log
1260 */
1261 CREATE TABLE audit_log
1262 (
1263 record_id integer not null,
1264 timestamp integer not null,
1265 subsystem varchar(32) not null,
1266 success integer not null,
1267 user_id integer not null, // 0x7FFFFFFF for system
1268 workstation varchar(63) not null,
1269 session_id integer not null,
1270 object_id integer not null,
1271 message SQL_TEXT null,
1272 old_value SQL_TEXT null,
1273 new_value SQL_TEXT null,
1274 value_diff SQL_TEXT null,
1275 PRIMARY KEY(record_id)
1276 ) TABLE_TYPE;
1277
1278 /*
1279 ** Persistent storage
1280 */
1281 CREATE TABLE persistent_storage
1282 (
1283 entry_key varchar(255) not null,
1284 value varchar(2000) null,
1285 PRIMARY KEY(entry_key)
1286 ) TABLE_TYPE;
1287
1288 /*
1289 ** List of possible community strings
1290 */
1291 CREATE TABLE snmp_communities
1292 (
1293 id integer not null,
1294 community varchar(255) null,
1295 zone integer not null,
1296 PRIMARY KEY(id)
1297 ) TABLE_TYPE;
1298
1299 /*
1300 ** Agent policies - common attributes
1301 */
1302 CREATE TABLE ap_common
1303 (
1304 id integer not null,
1305 policy_type integer not null,
1306 version integer not null,
1307 PRIMARY KEY(id)
1308 ) TABLE_TYPE;
1309
1310 /*
1311 ** Agent policies - node binding
1312 */
1313 CREATE TABLE ap_bindings
1314 (
1315 policy_id integer not null,
1316 node_id integer not null,
1317 PRIMARY KEY(policy_id,node_id)
1318 ) TABLE_TYPE;
1319
1320 /*
1321 ** Agent policies - configuration files
1322 */
1323 CREATE TABLE ap_config_files
1324 (
1325 policy_id integer not null,
1326 file_content SQL_TEXT null,
1327 PRIMARY KEY(policy_id)
1328 ) TABLE_TYPE;
1329
1330 /**
1331 ** Agent policies - log parser files
1332 */
1333 CREATE TABLE ap_log_parser
1334 (
1335 policy_id integer not null,
1336 file_content SQL_TEXT null,
1337 PRIMARY KEY(policy_id)
1338 ) TABLE_TYPE;
1339
1340 /*
1341 ** Default SNMP v3 USM credentials
1342 */
1343 CREATE TABLE usm_credentials
1344 (
1345 id integer not null,
1346 user_name varchar(255) not null,
1347 auth_method integer not null,
1348 priv_method integer not null,
1349 auth_password varchar(255),
1350 priv_password varchar(255),
1351 zone integer not null,
1352 PRIMARY KEY(id)
1353 ) TABLE_TYPE;
1354
1355 /*
1356 ** Network maps
1357 */
1358 CREATE TABLE network_maps
1359 (
1360 id integer not null,
1361 map_type integer not null,
1362 layout integer not null,
1363 radius integer not null,
1364 background varchar(36) null,
1365 bg_latitude varchar(20) null,
1366 bg_longitude varchar(20) null,
1367 bg_zoom integer null,
1368 bg_color integer not null,
1369 link_color integer not null,
1370 link_routing integer not null,
1371 object_display_mode integer not null,
1372 filter SQL_TEXT null,
1373 PRIMARY KEY(id)
1374 ) TABLE_TYPE;
1375
1376 /**
1377 * Elements of network maps
1378 */
1379 CREATE TABLE network_map_elements
1380 (
1381 map_id integer not null,
1382 element_id integer not null,
1383 element_type integer not null,
1384 element_data SQL_TEXT not null,
1385 flags integer not null,
1386 PRIMARY KEY(map_id,element_id)
1387 ) TABLE_TYPE;
1388
1389 /**
1390 * Links on network maps
1391 */
1392 CREATE TABLE network_map_links
1393 (
1394 map_id integer not null,
1395 element1 integer not null,
1396 element2 integer not null,
1397 link_type integer not null,
1398 link_name varchar(255) null,
1399 connector_name1 varchar(255) null,
1400 connector_name2 varchar(255) null,
1401 element_data SQL_TEXT null,
1402 flags integer not null
1403 ) TABLE_TYPE;
1404
1405 CREATE INDEX idx_network_map_links_map_id ON network_map_links(map_id);
1406
1407 /**
1408 * Seed nodes of network maps
1409 */
1410 CREATE TABLE network_map_seed_nodes
1411 (
1412 map_id integer not null,
1413 seed_node_id integer not null,
1414 PRIMARY KEY(map_id,seed_node_id)
1415 ) TABLE_TYPE;
1416
1417 /**
1418 * Image Library
1419 */
1420 CREATE TABLE images
1421 (
1422 guid varchar(36) not null,
1423 name varchar(63) not null,
1424 category varchar(63) not null,
1425 mimetype varchar(32) not null,
1426 protected integer default 0,
1427 PRIMARY KEY(guid),
1428 UNIQUE(name, category)
1429 ) TABLE_TYPE;
1430
1431 /**
1432 * Dashboards
1433 */
1434 CREATE TABLE dashboards
1435 (
1436 id integer not null,
1437 num_columns integer not null,
1438 options integer not null,
1439 PRIMARY KEY(id)
1440 ) TABLE_TYPE;
1441
1442 /**
1443 * Dashboard elements
1444 */
1445 CREATE TABLE dashboard_elements
1446 (
1447 dashboard_id integer not null,
1448 element_id integer not null,
1449 element_type integer not null,
1450 element_data SQL_TEXT null,
1451 layout_data SQL_TEXT null,
1452 PRIMARY KEY(dashboard_id,element_id)
1453 ) TABLE_TYPE;
1454
1455 /**
1456 * Associations between dashboards and other objects
1457 */
1458 CREATE TABLE dashboard_associations
1459 (
1460 object_id integer not null,
1461 dashboard_id integer not null,
1462 PRIMARY KEY(object_id,dashboard_id)
1463 ) TABLE_TYPE;
1464
1465 /**
1466 * SLA business services
1467 */
1468 CREATE TABLE business_services
1469 (
1470 service_id integer not null,
1471 PRIMARY KEY(service_id)
1472 ) TABLE_TYPE;
1473
1474 /**
1475 * SLA service checks
1476 */
1477 CREATE TABLE slm_checks
1478 (
1479 id integer not null,
1480 type integer not null, // 1 - threshold, 2 - script
1481 content SQL_TEXT null, // if type == 2
1482 threshold_id integer not null, // if type == 1; refers to thresholds.id
1483 reason varchar(255) null,
1484 is_template integer not null,
1485 template_id integer not null, // template check ID
1486 current_ticket integer not null,
1487 PRIMARY KEY(id)
1488 ) TABLE_TYPE;
1489
1490 /*
1491 ** SLA node links
1492 */
1493 CREATE TABLE node_links
1494 (
1495 nodelink_id integer not null,
1496 node_id integer not null, // refers to node.id
1497 PRIMARY KEY(nodelink_id)
1498 ) TABLE_TYPE;
1499
1500 /*
1501 ** SLA - agreements
1502 */
1503 CREATE TABLE slm_agreements
1504 (
1505 agreement_id integer not null,
1506 service_id integer not null,
1507 org_id integer not null,
1508 uptime varchar(63) not null, // required uptime, numeric(10,2) ?
1509 period integer not null, // 1 - day, 2 - month
1510 start_date integer not null, // date in YYYYMMDD format
1511 notes varchar(255),
1512 PRIMARY KEY(agreement_id)
1513 ) TABLE_TYPE;
1514
1515 /*
1516 ** SLA tickets
1517 */
1518
1519 CREATE TABLE slm_tickets
1520 (
1521 ticket_id integer not null,
1522 service_id integer not null,
1523 check_id integer not null,
1524 create_timestamp integer not null,
1525 close_timestamp integer not null,
1526 reason varchar(255) null,
1527 PRIMARY KEY(ticket_id)
1528 ) TABLE_TYPE;
1529
1530 /*
1531 ** SLA service change log
1532 */
1533
1534 CREATE TABLE slm_service_history
1535 (
1536 record_id integer not null,
1537 service_id integer not null,
1538 change_timestamp integer not null,
1539 new_status integer not null,
1540 PRIMARY KEY(record_id)
1541 ) TABLE_TYPE;
1542
1543 /*
1544 ** Organizations
1545 */
1546
1547 CREATE TABLE organizations
1548 (
1549 id integer not null,
1550 parent_id integer not null,
1551 org_type integer not null,
1552 name varchar(63) not null,
1553 description varchar(255),
1554 manager integer not null, // manager - id from "persons" table
1555 PRIMARY KEY(id)
1556 ) TABLE_TYPE;
1557
1558 /*
1559 ** Persons
1560 */
1561
1562 CREATE TABLE persons
1563 (
1564 id integer not null,
1565 org_id integer not null,
1566 first_name varchar(63),
1567 last_name varchar(63),
1568 title varchar(255),
1569 status integer not null,
1570 PRIMARY KEY(id)
1571 ) TABLE_TYPE;
1572
1573 /*
1574 ** Job history
1575 */
1576 CREATE TABLE job_history
1577 (
1578 id integer not null,
1579 time_created integer not null,
1580 time_started integer not null,
1581 time_finished integer not null,
1582 job_type varchar(127) null,
1583 description varchar(255) null,
1584 additional_info varchar(255) null,
1585 node_id integer not null,
1586 user_id integer not null,
1587 status integer not null,
1588 failure_message varchar(255) null,
1589 PRIMARY KEY(id)
1590 ) TABLE_TYPE;
1591
1592 /*
1593 ** License repository
1594 */
1595 CREATE TABLE licenses
1596 (
1597 id integer not null,
1598 content SQL_TEXT null,
1599 PRIMARY KEY(id)
1600 ) TABLE_TYPE;
1601
1602 /*
1603 ** Mapping tables
1604 */
1605 CREATE TABLE mapping_tables
1606 (
1607 id integer not null,
1608 name varchar(63) not null,
1609 flags integer not null,
1610 description SQL_TEXT4K null,
1611 PRIMARY KEY(id)
1612 ) TABLE_TYPE;
1613
1614 /*
1615 ** Mapping tables content
1616 */
1617 CREATE TABLE mapping_data
1618 (
1619 table_id integer not null,
1620 md_key varchar(63) not null,
1621 md_value varchar(255) null,
1622 description SQL_TEXT4K null,
1623 PRIMARY KEY(table_id,md_key)
1624 ) TABLE_TYPE;
1625
1626 /*
1627 ** DCI summary tables
1628 */
1629 CREATE TABLE dci_summary_tables
1630 (
1631 id integer not null,
1632 guid varchar(36) not null,
1633 menu_path varchar(255) null,
1634 title varchar(127) null,
1635 node_filter SQL_TEXT null,
1636 flags integer not null,
1637 columns SQL_TEXT null,
1638 table_dci_name varchar(255) null,
1639 PRIMARY KEY(id)
1640 ) TABLE_TYPE;
1641
1642 /*
1643 ** Scheduled tasks
1644 */
1645 CREATE TABLE scheduled_tasks
1646 (
1647 id integer not null,
1648 taskId varchar(255) null,
1649 schedule varchar(127) null,
1650 params varchar(1023) null,
1651 execution_time integer not null,
1652 last_execution_time integer not null,
1653 flags integer not null,
1654 owner integer not null,
1655 object_id integer not null,
1656 comments varchar(255) null,
1657 PRIMARY KEY(id)
1658 ) TABLE_TYPE;
1659
1660 /*
1661 ** ZMQ Subscriptions
1662 */
1663 CREATE TABLE zmq_subscription
1664 (
1665 object_id integer not null,
1666 subscription_type char(1) not null,
1667 ignore_items integer not null,
1668 items SQL_TEXT,
1669 PRIMARY KEY(object_id, subscription_type)
1670 ) TABLE_TYPE;
1671
1672 /**
1673 * Currency codes
1674 */
1675 CREATE TABLE currency_codes
1676 (
1677 numeric_code char(3) not null,
1678 alpha_code char(3) not null,
1679 description varchar(127) not null,
1680 exponent integer not null,
1681 PRIMARY KEY(numeric_code)
1682 ) TABLE_TYPE;
1683
1684 /**
1685 * Country codes
1686 */
1687 CREATE TABLE country_codes
1688 (
1689 numeric_code char(3) not null,
1690 alpha_code char(2) not null,
1691 alpha3_code char(3) not null,
1692 name varchar(127) not null,
1693 PRIMARY KEY(numeric_code)
1694 ) TABLE_TYPE;
1695
1696 /**
1697 * Config repositories
1698 */
1699 CREATE TABLE config_repositories
1700 (
1701 id integer not null,
1702 url varchar(1023) not null,
1703 auth_token varchar(63) null,
1704 description varchar(1023) null,
1705 PRIMARY KEY(id)
1706 ) TABLE_TYPE;
1707
1708 /**
1709 * Network equipment port layouts
1710 */
1711 CREATE TABLE port_layouts
1712 (
1713 device_oid varchar(255) not null,
1714 numbering_scheme char(1) not null,
1715 row_count char(1) not null,
1716 layout_data varchar(4000) null,
1717 PRIMARY KEY(device_oid)
1718 ) TABLE_TYPE;
1719
1720 /**
1721 * Snapshot for object access rights
1722 */
1723 CREATE TABLE object_access_snapshot
1724 (
1725 user_id integer not null,
1726 object_id integer not null,
1727 access_rights integer not null,
1728 PRIMARY KEY(user_id,object_id)
1729 ) TABLE_TYPE;
1730
1731 /**
1732 * Sensor specific information table
1733 */
1734 CREATE TABLE sensors
1735 (
1736 id integer not null,
1737 proxy_node integer not null,
1738 mac_address varchar(16) null,
1739 device_class integer not null,
1740 vendor varchar(128) null,
1741 communication_protocol integer not null,
1742 xml_reg_config varchar(4000) null,
1743 xml_config varchar(4000) null,
1744 serial_number varchar(256) null,
1745 device_address varchar(256) null,
1746 meta_type varchar(256) null,
1747 description varchar(512) null,
1748 last_connection_time integer not null,
1749 frame_count integer not null,
1750 signal_strenght integer not null,
1751 signal_noise integer not null,
1752 frequency integer not null,
1753 PRIMARY KEY(id)
1754 ) TABLE_TYPE;