ce53cbe8c0d177f4972e2b8486ccd8c2ecd8745b
[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 status integer not null,
155 is_deleted integer not null,
156 is_system integer not null,
157 last_modified integer not null,
158 inherit_access_rights integer not null,
159 status_calc_alg integer not null,
160 status_prop_alg integer not null,
161 status_fixed_val integer not null,
162 status_shift integer not null,
163 status_translation varchar(8) not null,
164 status_single_threshold integer not null,
165 status_thresholds varchar(8) not null,
166 comments SQL_TEXT null,
167 location_type integer not null,
168 latitude varchar(20),
169 longitude varchar(20),
170 location_accuracy integer not null,
171 location_timestamp integer not null,
172 image varchar(36) not null,
173 submap_id integer not null,
174 country varchar(63) null,
175 city varchar(63) null,
176 street_address varchar(255) null,
177 postcode varchar(31) null,
178 maint_mode char(1) not null,
179 maint_event_id SQL_INT64 not null,
180 PRIMARY KEY(object_id)
181 ) TABLE_TYPE;
182
183 /**
184 * Object URLs
185 */
186 CREATE TABLE object_urls
187 (
188 object_id integer not null,
189 url_id integer not null,
190 url varchar(2000) null,
191 description varchar(2000) null,
192 PRIMARY KEY(object_id,url_id)
193 ) TABLE_TYPE;
194
195 /*
196 ** Object custom attributes
197 */
198 CREATE TABLE object_custom_attributes
199 (
200 object_id integer not null,
201 attr_name varchar(127) not null,
202 attr_value SQL_TEXT null,
203 PRIMARY KEY(object_id,attr_name)
204 ) TABLE_TYPE;
205
206 CREATE INDEX idx_ocattr_oid ON object_custom_attributes(object_id);
207
208 /*
209 ** Zone objects
210 */
211 CREATE TABLE zones
212 (
213 id integer not null, // Zone object ID
214 zone_guid integer not null, // Globally unique ID for zone
215 proxy_node integer not null,
216 PRIMARY KEY(id)
217 ) TABLE_TYPE;
218
219 /*
220 ** Mobile device objects
221 */
222 CREATE TABLE mobile_devices
223 (
224 id integer not null,
225 device_id varchar(64) not null,
226 vendor varchar(64) null,
227 model varchar(128) null,
228 serial_number varchar(64) null,
229 os_name varchar(32) null,
230 os_version varchar(64) null,
231 user_id varchar(64) null,
232 battery_level integer not null,
233 PRIMARY KEY(id)
234 ) TABLE_TYPE;
235
236 /*
237 ** Access point objects
238 */
239 CREATE TABLE access_points
240 (
241 id integer not null,
242 node_id integer not null,
243 mac_address varchar(12) null,
244 vendor varchar(64) null,
245 model varchar(128) null,
246 serial_number varchar(64) null,
247 ap_state integer not null,
248 ap_index integer not null,
249 PRIMARY KEY(id)
250 ) TABLE_TYPE;
251
252 /*
253 ** Rack objects
254 */
255 CREATE TABLE racks
256 (
257 id integer not null,
258 height integer not null,
259 top_bottom_num char(1) not null,
260 PRIMARY KEY(id)
261 ) TABLE_TYPE;
262
263 /*
264 ** Chassis objects
265 */
266 CREATE TABLE chassis
267 (
268 id integer not null,
269 controller_id integer not null,
270 flags integer not null,
271 rack_id integer not null,
272 rack_image varchar(36) null,
273 rack_position integer not null,
274 rack_height integer not null,
275 PRIMARY KEY(id)
276 ) TABLE_TYPE;
277
278 /*
279 ** Nodes information
280 */
281 CREATE TABLE nodes
282 (
283 id integer not null,
284 primary_name varchar(255) null,
285 primary_ip varchar(48) not null,
286 tunnel_id varchar(36) null,
287 node_flags integer not null,
288 runtime_flags integer not 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 PRIMARY KEY(id)
336 ) TABLE_TYPE;
337
338 /*
339 ** Clusters information
340 */
341
342 CREATE TABLE clusters
343 (
344 id integer not null,
345 cluster_type integer not null,
346 zone_guid integer not null,
347 PRIMARY KEY(id)
348 ) TABLE_TYPE;
349
350 /*
351 ** Cluster members
352 */
353
354 CREATE TABLE cluster_members
355 (
356 cluster_id integer not null,
357 node_id integer not null,
358 PRIMARY KEY(cluster_id,node_id)
359 ) TABLE_TYPE;
360
361 /*
362 ** Cluster interconnect subnets
363 */
364
365 CREATE TABLE cluster_sync_subnets
366 (
367 cluster_id integer not null,
368 subnet_addr varchar(48) not null,
369 subnet_mask integer not null,
370 PRIMARY KEY(cluster_id,subnet_addr)
371 ) TABLE_TYPE;
372
373 /*
374 ** Cluster resources
375 */
376
377 CREATE TABLE cluster_resources
378 (
379 cluster_id integer not null,
380 resource_id integer not null,
381 resource_name varchar(255),
382 ip_addr varchar(48) not null,
383 current_owner integer not null,
384 PRIMARY KEY(cluster_id,resource_id)
385 ) TABLE_TYPE;
386
387 /*
388 ** Subnets
389 */
390 CREATE TABLE subnets
391 (
392 id integer not null,
393 ip_addr varchar(48) not null,
394 ip_netmask integer not null,
395 zone_guid integer not null,
396 synthetic_mask integer not null,
397 PRIMARY KEY(id)
398 ) TABLE_TYPE;
399
400 /*
401 ** Nodes' interfaces
402 */
403 CREATE TABLE interfaces
404 (
405 id integer not null,
406 node_id integer not null,
407 parent_iface integer not null,
408 flags integer not null,
409 if_type integer not null,
410 if_index integer not null,
411 mtu integer not null,
412 speed SQL_INT64 not null,
413 bridge_port integer not null,
414 phy_slot integer not null,
415 phy_port integer not null,
416 peer_node_id integer not null,
417 peer_if_id integer not null,
418 peer_proto integer not null,
419 mac_addr varchar(12) not null,
420 required_polls integer not null,
421 admin_state integer not null,
422 oper_state integer not null,
423 dot1x_pae_state integer not null,
424 dot1x_backend_state integer not null,
425 description varchar(255) null,
426 alias varchar(255) null,
427 iftable_suffix varchar(127) null,
428 PRIMARY KEY(id)
429 ) TABLE_TYPE;
430
431 /*
432 ** Interface IP addresses
433 */
434 CREATE TABLE interface_address_list
435 (
436 iface_id integer not null,
437 ip_addr varchar(48) not null,
438 ip_netmask integer not null,
439 PRIMARY KEY(iface_id,ip_addr)
440 ) TABLE_TYPE;
441
442 /*
443 ** Network services
444 */
445
446 CREATE TABLE network_services
447 (
448 id integer not null,
449 node_id integer not null,
450 service_type integer not null,
451 ip_bind_addr varchar(48) not null,
452 ip_proto integer not null,
453 ip_port integer not null,
454 check_request SQL_TEXT null,
455 check_responce SQL_TEXT null,
456 poller_node_id integer not null,
457 required_polls integer not null,
458 PRIMARY KEY(id)
459 ) TABLE_TYPE;
460
461 /*
462 ** VPN connectors
463 */
464 CREATE TABLE vpn_connectors
465 (
466 id integer not null,
467 node_id integer not null,
468 peer_gateway integer not null,
469 PRIMARY KEY(id)
470 ) TABLE_TYPE;
471
472 /*
473 ** VPN connector networks
474 */
475 CREATE TABLE vpn_connector_networks
476 (
477 vpn_id integer not null,
478 network_type integer not null, // 0 == local, 1 == remote
479 ip_addr varchar(48) not null,
480 ip_netmask integer not null,
481 PRIMARY KEY(vpn_id,ip_addr)
482 ) TABLE_TYPE;
483
484 /*
485 ** Container objects
486 */
487 CREATE TABLE object_containers
488 (
489 id integer not null,
490 object_class integer not null,
491 flags integer not null,
492 auto_bind_filter SQL_TEXT null,
493 PRIMARY KEY(id)
494 ) TABLE_TYPE;
495
496 /*
497 ** Condition objects
498 */
499 CREATE TABLE conditions
500 (
501 id integer not null,
502 activation_event integer not null,
503 deactivation_event integer not null,
504 source_object integer not null,
505 active_status integer not null,
506 inactive_status integer not null,
507 script SQL_TEXT not null,
508 PRIMARY KEY(id)
509 ) TABLE_TYPE;
510
511 /*
512 ** DCI to condition mapping
513 */
514 CREATE TABLE cond_dci_map
515 (
516 condition_id integer not null,
517 sequence_number integer not null,
518 dci_id integer not null,
519 node_id integer not null,
520 dci_func integer not null,
521 num_polls integer not null,
522 PRIMARY KEY(condition_id,sequence_number)
523 ) TABLE_TYPE;
524
525 /**
526 * Data collection templates
527 */
528 CREATE TABLE templates
529 (
530 id integer not null,
531 version integer not null,
532 flags 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 id integer not null,
844 name varchar(63) not null,
845 description varchar(255) not null,
846 range_start integer not null,
847 range_end integer not null,
848 PRIMARY KEY(id)
849 ) TABLE_TYPE;
850
851 /*
852 ** Event group members
853 */
854 CREATE TABLE event_group_members
855 (
856 group_id integer not null,
857 event_code integer not null,
858 PRIMARY KEY(group_id,event_code)
859 ) TABLE_TYPE;
860
861 /*
862 ** Event processing policy
863 */
864 CREATE TABLE event_policy
865 (
866 rule_id integer not null, // Rule number
867 rule_guid varchar(36) not null,
868 flags integer not null,
869 comments SQL_TEXT null,
870 script SQL_TEXT null,
871 alarm_message varchar(2000) null,
872 alarm_severity integer not null,
873 alarm_key varchar(255) null, // Alarm key (used for auto termination)
874 alarm_timeout integer not null, // Timeout before sending event
875 alarm_timeout_event integer not null, // Event to be sent on timeout
876 PRIMARY KEY(rule_id)
877 ) TABLE_TYPE;
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 PRIMARY KEY(id)
1296 ) TABLE_TYPE;
1297
1298 /*
1299 ** Agent policies - common attributes
1300 */
1301 CREATE TABLE ap_common
1302 (
1303 id integer not null,
1304 policy_type integer not null,
1305 version integer not null,
1306 PRIMARY KEY(id)
1307 ) TABLE_TYPE;
1308
1309 /*
1310 ** Agent policies - node binding
1311 */
1312 CREATE TABLE ap_bindings
1313 (
1314 policy_id integer not null,
1315 node_id integer not null,
1316 PRIMARY KEY(policy_id,node_id)
1317 ) TABLE_TYPE;
1318
1319 /*
1320 ** Agent policies - configuration files
1321 */
1322 CREATE TABLE ap_config_files
1323 (
1324 policy_id integer not null,
1325 file_content SQL_TEXT null,
1326 PRIMARY KEY(policy_id)
1327 ) TABLE_TYPE;
1328
1329 /**
1330 ** Agent policies - log parser files
1331 */
1332 CREATE TABLE ap_log_parser
1333 (
1334 policy_id integer not null,
1335 file_content SQL_TEXT null,
1336 PRIMARY KEY(policy_id)
1337 ) TABLE_TYPE;
1338
1339 /*
1340 ** Default SNMP v3 USM credentials
1341 */
1342 CREATE TABLE usm_credentials
1343 (
1344 id integer not null,
1345 user_name varchar(255) not null,
1346 auth_method integer not null,
1347 priv_method integer not null,
1348 auth_password varchar(255),
1349 priv_password varchar(255),
1350 PRIMARY KEY(id)
1351 ) TABLE_TYPE;
1352
1353 /*
1354 ** Network maps
1355 */
1356 CREATE TABLE network_maps
1357 (
1358 id integer not null,
1359 map_type integer not null,
1360 layout integer not null,
1361 radius integer not null,
1362 background varchar(36) null,
1363 bg_latitude varchar(20) null,
1364 bg_longitude varchar(20) null,
1365 bg_zoom integer null,
1366 flags integer not null,
1367 bg_color integer not null,
1368 link_color integer not null,
1369 link_routing integer not null,
1370 object_display_mode integer not null,
1371 filter SQL_TEXT null,
1372 PRIMARY KEY(id)
1373 ) TABLE_TYPE;
1374
1375 /**
1376 * Elements of network maps
1377 */
1378 CREATE TABLE network_map_elements
1379 (
1380 map_id integer not null,
1381 element_id integer not null,
1382 element_type integer not null,
1383 element_data SQL_TEXT not null,
1384 flags integer not null,
1385 PRIMARY KEY(map_id,element_id)
1386 ) TABLE_TYPE;
1387
1388 /**
1389 * Links on network maps
1390 */
1391 CREATE TABLE network_map_links
1392 (
1393 map_id integer not null,
1394 element1 integer not null,
1395 element2 integer not null,
1396 link_type integer not null,
1397 link_name varchar(255) null,
1398 connector_name1 varchar(255) null,
1399 connector_name2 varchar(255) null,
1400 element_data SQL_TEXT null,
1401 flags integer not null
1402 ) TABLE_TYPE;
1403
1404 CREATE INDEX idx_network_map_links_map_id ON network_map_links(map_id);
1405
1406 /**
1407 * Seed nodes of network maps
1408 */
1409 CREATE TABLE network_map_seed_nodes
1410 (
1411 map_id integer not null,
1412 seed_node_id integer not null,
1413 PRIMARY KEY(map_id,seed_node_id)
1414 ) TABLE_TYPE;
1415
1416 /**
1417 * Image Library
1418 */
1419 CREATE TABLE images
1420 (
1421 guid varchar(36) not null,
1422 name varchar(63) not null,
1423 category varchar(63) not null,
1424 mimetype varchar(32) not null,
1425 protected integer default 0,
1426 PRIMARY KEY(guid),
1427 UNIQUE(name, category)
1428 ) TABLE_TYPE;
1429
1430 /**
1431 * Dashboards
1432 */
1433 CREATE TABLE dashboards
1434 (
1435 id integer not null,
1436 num_columns integer not null,
1437 options integer not null,
1438 PRIMARY KEY(id)
1439 ) TABLE_TYPE;
1440
1441 /**
1442 * Dashboard elements
1443 */
1444 CREATE TABLE dashboard_elements
1445 (
1446 dashboard_id integer not null,
1447 element_id integer not null,
1448 element_type integer not null,
1449 element_data SQL_TEXT null,
1450 layout_data SQL_TEXT null,
1451 PRIMARY KEY(dashboard_id,element_id)
1452 ) TABLE_TYPE;
1453
1454 /**
1455 * Associations between dashboards and other objects
1456 */
1457 CREATE TABLE dashboard_associations
1458 (
1459 object_id integer not null,
1460 dashboard_id integer not null,
1461 PRIMARY KEY(object_id,dashboard_id)
1462 ) TABLE_TYPE;
1463
1464 /**
1465 * SLA business services
1466 */
1467 CREATE TABLE business_services
1468 (
1469 service_id integer not null,
1470 PRIMARY KEY(service_id)
1471 ) TABLE_TYPE;
1472
1473 /**
1474 * SLA service checks
1475 */
1476 CREATE TABLE slm_checks
1477 (
1478 id integer not null,
1479 type integer not null, // 1 - threshold, 2 - script
1480 content SQL_TEXT null, // if type == 2
1481 threshold_id integer not null, // if type == 1; refers to thresholds.id
1482 reason varchar(255) null,
1483 is_template integer not null,
1484 template_id integer not null, // template check ID
1485 current_ticket integer not null,
1486 PRIMARY KEY(id)
1487 ) TABLE_TYPE;
1488
1489 /*
1490 ** SLA node links
1491 */
1492 CREATE TABLE node_links
1493 (
1494 nodelink_id integer not null,
1495 node_id integer not null, // refers to node.id
1496 PRIMARY KEY(nodelink_id)
1497 ) TABLE_TYPE;
1498
1499 /*
1500 ** SLA - agreements
1501 */
1502 CREATE TABLE slm_agreements
1503 (
1504 agreement_id integer not null,
1505 service_id integer not null,
1506 org_id integer not null,
1507 uptime varchar(63) not null, // required uptime, numeric(10,2) ?
1508 period integer not null, // 1 - day, 2 - month
1509 start_date integer not null, // date in YYYYMMDD format
1510 notes varchar(255),
1511 PRIMARY KEY(agreement_id)
1512 ) TABLE_TYPE;
1513
1514 /*
1515 ** SLA tickets
1516 */
1517
1518 CREATE TABLE slm_tickets
1519 (
1520 ticket_id integer not null,
1521 service_id integer not null,
1522 check_id integer not null,
1523 create_timestamp integer not null,
1524 close_timestamp integer not null,
1525 reason varchar(255) null,
1526 PRIMARY KEY(ticket_id)
1527 ) TABLE_TYPE;
1528
1529 /*
1530 ** SLA service change log
1531 */
1532
1533 CREATE TABLE slm_service_history
1534 (
1535 record_id integer not null,
1536 service_id integer not null,
1537 change_timestamp integer not null,
1538 new_status integer not null,
1539 PRIMARY KEY(record_id)
1540 ) TABLE_TYPE;
1541
1542 /*
1543 ** Organizations
1544 */
1545
1546 CREATE TABLE organizations
1547 (
1548 id integer not null,
1549 parent_id integer not null,
1550 org_type integer not null,
1551 name varchar(63) not null,
1552 description varchar(255),
1553 manager integer not null, // manager - id from "persons" table
1554 PRIMARY KEY(id)
1555 ) TABLE_TYPE;
1556
1557 /*
1558 ** Persons
1559 */
1560
1561 CREATE TABLE persons
1562 (
1563 id integer not null,
1564 org_id integer not null,
1565 first_name varchar(63),
1566 last_name varchar(63),
1567 title varchar(255),
1568 status integer not null,
1569 PRIMARY KEY(id)
1570 ) TABLE_TYPE;
1571
1572 /*
1573 ** Job history
1574 */
1575 CREATE TABLE job_history
1576 (
1577 id integer not null,
1578 time_created integer not null,
1579 time_started integer not null,
1580 time_finished integer not null,
1581 job_type varchar(127) null,
1582 description varchar(255) null,
1583 additional_info varchar(255) null,
1584 node_id integer not null,
1585 user_id integer not null,
1586 status integer not null,
1587 failure_message varchar(255) null,
1588 PRIMARY KEY(id)
1589 ) TABLE_TYPE;
1590
1591 /*
1592 ** License repository
1593 */
1594 CREATE TABLE licenses
1595 (
1596 id integer not null,
1597 content SQL_TEXT null,
1598 PRIMARY KEY(id)
1599 ) TABLE_TYPE;
1600
1601 /*
1602 ** Mapping tables
1603 */
1604 CREATE TABLE mapping_tables
1605 (
1606 id integer not null,
1607 name varchar(63) not null,
1608 flags integer not null,
1609 description SQL_TEXT4K null,
1610 PRIMARY KEY(id)
1611 ) TABLE_TYPE;
1612
1613 /*
1614 ** Mapping tables content
1615 */
1616 CREATE TABLE mapping_data
1617 (
1618 table_id integer not null,
1619 md_key varchar(63) not null,
1620 md_value varchar(255) null,
1621 description SQL_TEXT4K null,
1622 PRIMARY KEY(table_id,md_key)
1623 ) TABLE_TYPE;
1624
1625 /*
1626 ** DCI summary tables
1627 */
1628 CREATE TABLE dci_summary_tables
1629 (
1630 id integer not null,
1631 guid varchar(36) not null,
1632 menu_path varchar(255) null,
1633 title varchar(127) null,
1634 node_filter SQL_TEXT null,
1635 flags integer not null,
1636 columns SQL_TEXT null,
1637 table_dci_name varchar(256) null,
1638 PRIMARY KEY(id)
1639 ) TABLE_TYPE;
1640
1641 /*
1642 ** Scheduled tasks
1643 */
1644 CREATE TABLE scheduled_tasks
1645 (
1646 id integer not null,
1647 taskId varchar(255) null,
1648 schedule varchar(127) null,
1649 params varchar(1023) null,
1650 execution_time integer not null,
1651 last_execution_time integer not null,
1652 flags integer not null,
1653 owner integer not null,
1654 object_id integer not null,
1655 comments varchar(255) null,
1656 PRIMARY KEY(id)
1657 ) TABLE_TYPE;
1658
1659 /*
1660 ** ZMQ Subscriptions
1661 */
1662 CREATE TABLE zmq_subscription
1663 (
1664 object_id integer not null,
1665 subscription_type char(1) not null,
1666 ignore_items integer not null,
1667 items SQL_TEXT,
1668 PRIMARY KEY(object_id, subscription_type)
1669 ) TABLE_TYPE;
1670
1671 /**
1672 * Currency codes
1673 */
1674 CREATE TABLE currency_codes
1675 (
1676 numeric_code char(3) not null,
1677 alpha_code char(3) not null,
1678 description varchar(127) not null,
1679 exponent integer not null,
1680 PRIMARY KEY(numeric_code)
1681 ) TABLE_TYPE;
1682
1683 /**
1684 * Country codes
1685 */
1686 CREATE TABLE country_codes
1687 (
1688 numeric_code char(3) not null,
1689 alpha_code char(2) not null,
1690 alpha3_code char(3) not null,
1691 name varchar(127) not null,
1692 PRIMARY KEY(numeric_code)
1693 ) TABLE_TYPE;
1694
1695 /**
1696 * Config repositories
1697 */
1698 CREATE TABLE config_repositories
1699 (
1700 id integer not null,
1701 url varchar(1023) not null,
1702 auth_token varchar(63) null,
1703 description varchar(1023) null,
1704 PRIMARY KEY(id)
1705 ) TABLE_TYPE;
1706
1707 /**
1708 * Network equipment port layouts
1709 */
1710 CREATE TABLE port_layouts
1711 (
1712 device_oid varchar(255) not null,
1713 numbering_scheme char(1) not null,
1714 row_count char(1) not null,
1715 layout_data varchar(4000) null,
1716 PRIMARY KEY(device_oid)
1717 ) TABLE_TYPE;
1718
1719 /**
1720 * Snapshot for object access rights
1721 */
1722 CREATE TABLE object_access_snapshot
1723 (
1724 user_id integer not null,
1725 object_id integer not null,
1726 access_rights integer not null,
1727 PRIMARY KEY(user_id,object_id)
1728 ) TABLE_TYPE;
1729
1730 /**
1731 * Sensor specific information table
1732 */
1733 CREATE TABLE sensors
1734 (
1735 id integer not null,
1736 proxy_node integer not null,
1737 flags 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 runtime_flags integer null,
1754 PRIMARY KEY(id)
1755 ) TABLE_TYPE;