-- InterMapper Database -- Base Schema -- -- The database schema for storing information exported from one or more -- InterMapper Servers. -- -- Copyright (c) 2007-2008, Dartware, LLC. All rights reserved. -- Schema Version (1) -- Generated for POSTGRES on 2008-03-14 by sqlalchemy 0.4.3 -- Reply-To: Bill Fisher -- -- (Please note that the initial release of the InterMapper Database will -- only support the embedded PostgreSQL 8.3 DBMS, irregardless of what this -- schema file may say or hint. Do not become overly excited about the -- prospect of support for MySQL or Oracle; it will not happen in the first -- release. We promise.) -- -- The schema is divided into four sections. The Reference section contains -- the definitions of the lookup tables used. The Element section specifies -- the base tables for the different elements of the InterMapper system. -- The Temporal section contains tables that store time-based historical -- data. Finally, a Process Control section is reserved for making changes -- to the InterMapper system via special process control tables. -- -- Tables defined by this schema: -- -- [Reference] -- SchemaVersion -- DeviceKind -- RetentionPolicy -- IANAEnterprise -- IANAIfType -- [Element] -- Server -- Notifier -- Map -- Device -- Interface -- Address -- NotifierRule -- DataSet -- [Temporal] -- ServerPoll -- Event -- Acknowledgment -- Notification -- DataPoint -- DataSample -- DataSampleHourly -- DataSampleDaily -- [Process Control] -- (None) -- -- -- Soft-Delete Strategy -- -- Since the InterMapper Database contains historical information, clients -- may want to retrieve historical data related to elements that no longer -- exist. To facilitate this retrieval, all deleted elements have a -- non-NULL 'delete_time' attribute. A deleted element describes its state -- at the moment of deletion. -- -- - A "live" device or element will have a delete_time of 'infinity'. A -- deleted element will have a finite delete_time. -- -- - When a container's delete_time attribute is set, the delete_time -- attribute of all its sub-elements will be automatically set also. -- -- - Because Collection and Subdevice relationships are intimately tied to -- Devices, we will not retain deleted Collection or Subdevice information. -- These elements intentionally have no 'delete_time' flag. The same is true -- of NotifierRule information. -- -- Reconciliation Strategy -- -- The Device and Interface tables include a "customer_name_reference" -- column for use in reconciling the InterMapper database with other -- databases used by a customer. -- -- Case-Sensitivity of Hexadecimal Values -- -- Where values are entered in hexadecimal, e.g. UUID's, MAC addresses and -- IPv6 addresses, the convention is to use *lower case* for characters A-F. -- -- Document History: -- -- - 16 August 2007 - Made available for preliminary external review. -wwf -- - 21 August 2007 - Added a UNIQUE constraint to RetentionPolicy.name. -wwf -- - 23 August 2007 - Added notify_kind column to Notification table. -wwf -- - 23 August 2007 - Removed "unknown" from Event.status enumeration and added comment -- describing how unknown events are left as gaps in the time -- sequence of events. -wwf -- - 24 August 2007 - Removed DevicePerformance and InterfacePerformance tables from version -- 1.0 schema, due to implementation time-constraints. (These tables may -- re-appear in a future version of the schema.) -wwf -- - 24 August 2007 - Added comment to header describing the "soft-delete" strategy -- for elements. -wwf -- - 28 August 2007 - Changed deleted boolean to a nullable delete_time attribute. -wwf -- - 28 August 2007 - Simplified the rules in the RetentionPolicy table. -- Added a retentionpolicy_id attribute to DataSet table. -wwf -- - 28 August 2007 - Renamed columns in the Acknowledgment and Notification tables. -- Remove message attribute from Notification table and added a -- notify_attempt attribute instead. -wwf -- - 30 August 2007 - Added DataSample, DataSampleHourly and DataSampleDaily tables. -wwf -- - 31 August 2007 - Removed Role and RoleMember to a separate "auth" schema. Replaced -- role_id attribute in Server table with "authsecret". -- - 4 September 2007 - Element delete_time attribute is now non-nullable. Added probe -- attribute to Event table and renamed Event.message to Event.reason. -- Removed 'memo' ack_type. ack_source is no longer part of primary -- key. Added sample_count attributes to the DataSample* tables. -wwf -- - 4 September 2007 - Added LifeTimeConstraint and EnumConstraint. -wwf -- - 4 September 2007 - Added ServerPoll table, and removed lastpoll_time attribute from -- Server table. -- - 4 September 2007 - Renamed 'notify_kind' to 'notify_method'. Added comment on use -- of 'notify_attempt' field. -wwf -- - 4 September 2007 - Use MACADDR type for Mac Address on Postgres. Double's are now -- FLOAT(53) to force Postgres to use DOUBLE PRECISION. -wwf -- - 6 September 2007 - Added reference_date attribute to SchemaVersion table to -- help manage updates to the reference tables. -wwf -- - 9 September 2007 - Renamed DeviceKind.kind to DeviceKind.name. -- Renamed IANAIfType.iftype to IANAIfType.iftype_id. -- Renamed the enum constant 'ackloc' to 'acklocal'. -- Added NOT NULL constraint to DataPoint.data_value. -- Renamed DataSampleDaily.sample_date to sample_time. -wwf -- - 19 September 2007 - RetentionPolicy.retentionpolicy_id is auto-incrementing. -ds -- - 21 September 2007 - Removed Server.clientid attribute. -ds -- - 28 September 2007 - Added alarmpt attribute to Interface table. -wwf -- - 2 October 2007 - Added path attribute to Map table. -wwf -- - 3 October 2007 - Removed the Escalation table and fixed up NotifierRule table so -- it associates Notifier and Device tables directly. Removed -- escalation_id attribute from Device table. -wwf -- - 4 October 2007 - Added check constraint to Server.uuid and added a note about the -- case-sensitive convention for this field. -- Added Interface.name attribute, and removed Interface.vlanencapsulation -- attribute. -wwf -- - 10 October 2007 - Added ServerPoll.category attribute. -ds -- - 7 January 2008 - Added server.old_lastdate and server.old_profile. -ds -- - 17 January 2008 - Removed subdevice and collection tables. -ds -- - 20 February 2008 - Cleaned up comments & noted use of PG 8.3 rather than 8.2. -ds -- - 20 February 2008 - Renamed server.old_lastdate to oldest_time. -ds -- - 20 February 2008 - Renamed server.old_profile to import_profile. -ds -- - 20 February 2008 - Changed type of notifier.notifier_xml to XML. -ds -- - 20 February 2008 - Renamed device.comment to notes. -ds -- - 20 February 2008 - Changed type of device.probe_xml to XML. -ds -- - 20 February 2008 - Added a column 'tags' to dataset table. -ds -- - 20 February 2008 - Renamed serverpoll.begin_time_imserver to next_utc. -ds -- - 20 February 2008 - Changed type of renamed next_utc to INTEGER. -ds -- - 20 February 2008 - Renamed serverpoll.category to report. -ds -- - 20 February 2008 - Added ON DELETE CASCADE to datapoint & datasample tables. -ds -- - 26 February 2008 - Added recv_rows column to serverpoll table. -ds -- - 26 February 2008 - Changed server.oldest_time to oldest_utc, an integer. -ds -- -- -- SCHEMA VERSION 1 (2008-02-20) -- -- The SchemaVersion table contains meta-data about the database schema in -- use. In particular, this table contains the schema version, a date -- indicating the version of data in the reference tables, and the date -- when the present schema version was installed. -- -- Schema version numbers start at 1 and increment by 1 with each new -- version. Schema versions < 0 are reserved; they are not upgradable. -- Schema version 0 is invalid. -- -- A schema version of -9999 forces the database to drop all data and re-install -- the schema from scratch. This is primarily for testing and debugging. -- -- There should always be exactly one row in this table. To fetch the -- current schema version, you can use this SELECT command: -- -- SELECT version FROM SchemaVersion; CREATE TABLE schemaversion ( version INTEGER NOT NULL, -- the current schema version reference_date DATE NOT NULL, -- the date when the reference tables last changed create_time TIMESTAMP WITH TIME ZONE NOT NULL, -- when this schema was installed PRIMARY KEY (version) ) -- The DeviceKind table defines the available device types. Customers can -- define their own device types with devicekind_id's > 0. The -- devicekind_id 0 will represent 'Unspecified'. Devicekind_id's less than -- or equal to zero are reserved for Global Device Types. -- -- Global Device Types: -- Unspecified -- Unidentified -- Unauthorized -- Layer-3 Switch -- Router -- Switch -- Repeater -- Firewall -- Desktop PC -- Laptop PC -- Tablet PC -- Handheld PC -- Workstation -- Supercomputer -- Mainframe Computer -- Rackmount Server -- Blade Server -- IPMI BMC -- RAID -- NAS -- Access Point -- CPE -- Bridge -- Printer -- UPS -- Phone -- Environmental Monitor -- Video Camera -- Game Console -- Virtual Machine -- Appliance CREATE TABLE devicekind ( devicekind_id SERIAL NOT NULL, name VARCHAR(80) NOT NULL, PRIMARY KEY (devicekind_id) ) -- The RetentionPolicy table stores the available retention policies that -- maps, devices, interfaces can use for their historical data. Each -- retention policy describes how long to keep data and includes a schedule -- for optionally reducing the frequency of historical readings as the data -- ages. A retention policy may also specify that no data is to be retained. -- -- There is one pre-defined retention policy, "None". -- -- - "None" does not retain any data. This special retention policy -- indicates to the InterMapper Server that no data is to be recorded -- or stored for later. (retentionpolicy_id = 0) -- -- Retentionpolicy_id's less than or equal to zero are reserved. Customers -- may define their own retentionpolicy_id's > 0. -- -- Expiration attributes are represented in days. Zero means don't -- retain. An expiration of 2^15-1 means never expire. CREATE TABLE retentionpolicy ( retentionpolicy_id SERIAL NOT NULL, name VARCHAR(255) NOT NULL, raw_expiration SMALLINT NOT NULL, -- how long original data points are retained (days) hourly_expiration SMALLINT NOT NULL, -- if non-zero, how long hourly averages are retained (days) daily_expiration SMALLINT NOT NULL, -- if non-zero, how long daily averages are retained (days) custom_minutes SMALLINT NOT NULL, -- if non-zero data points are reduced to this frequency (minutes) custom_expiration SMALLINT NOT NULL, -- if non-zero, how long custom reduced data points are retained (days) PRIMARY KEY (retentionpolicy_id), UNIQUE (name) ) -- The IANAEnterprise table stores the organization enterprise numbers -- assigned by the Internet Assigned Numbers Authority (IANA). The SMI -- Network Management Private Enterprise Codes are defined in -- "http://www.iana.org/assignments/enterprise-numbers". -- -- The enterprise_id is typically the seventh subid of sysObjectID when it -- begins with "1.3.6.1.4.1". For example, a sysObjectID of -- "1.3.6.1.4.1.9.1.2.3" will have the enterprise_id of 9, which is Cisco. -- -- Enterprise_id's are assigned positive integers. Enterprise_id 0 is -- "Reserved" by the IANA. Enterprise_id -1 is reserved in this schema for -- "Unspecified". CREATE TABLE ianaenterprise ( enterprise_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, PRIMARY KEY (enterprise_id) ) -- The IANAIfType table stores the interface types assigned by the Internet -- Assigned Numbers Authority (IANA). The interface types (ifTypes) are -- defined in the IANAifType-MIB located at -- "http://www.iana.org/assignments/ianaiftype-mib". -- -- ifType 0 is not assigned by the IANA. ifType -1 is reserved in this -- schema for "Unspecified". CREATE TABLE ianaiftype ( iftype_id INTEGER NOT NULL, name VARCHAR(40) NOT NULL, PRIMARY KEY (iftype_id) ) -- The Server table stores information about each InterMapper Server. The -- information in this table will be used to identify data belonging to -- each server, to contact the server through the network, to locate the -- server machine physically, and to discern between servers on different -- RFC 1918 networks. -- -- The primary key for the Server table is [server_id]. A server_id is an -- integer in the range [1,4095] (12 bits). It is assigned by IMDC software. CREATE TABLE server ( server_id SMALLINT NOT NULL, name VARCHAR(255) NOT NULL, uuid VARCHAR(36) NOT NULL CHECK (uuid ~ '[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}'), -- uniquely identifies each server. Format "%08x-%04x-%04x-%04x-%012x" url VARCHAR(1000) NOT NULL, -- how to access server (intermapper or http url) authsecret VARCHAR(1000) NOT NULL, -- private, encrypted login name and password host_location VARCHAR(1000) NOT NULL, -- location of server's host computer host_contact VARCHAR(1000) NOT NULL, -- contact for server's host computer network_name VARCHAR(255) NOT NULL, -- name of network where server is connected poll_type VARCHAR(4) NOT NULL CHECK (poll_type IN ('pull','push')), -- how imdc accesses the server device_interval INTEGER NOT NULL, -- how often to poll device and interface performance history event_interval INTEGER NOT NULL, -- how often to poll events, acknowledgments, notifications data_interval INTEGER NOT NULL, -- how often to poll dataset values oldest_utc BIGINT NOT NULL, import_profile VARCHAR(7) NOT NULL CHECK (import_profile IN ('now','weekend','nightly','hourly','never')), create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id), UNIQUE (uuid), CHECK (create_time <= delete_time) ) -- The Notifier table stores information about the Notifiers on all -- InterMapper Servers. -- -- The primary key for the Notifier table is [server_id, notifier_id]. The -- notifier_id is an integer in the range [1,262143] (18 bits). -- notifier_id is an externally defined key. CREATE TABLE notifier ( server_id SMALLINT NOT NULL, notifier_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, enabled BOOLEAN NOT NULL, notifier_xml VARCHAR(4096) NOT NULL, create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, notifier_id), FOREIGN KEY(server_id) REFERENCES server (server_id) ON DELETE CASCADE, CHECK (create_time <= delete_time) ) -- The Map table stores information about each map. The information in this -- table will be used to identify data belonging to each map. -- -- The primary key for the Map table is [server_id, map_id]. The map_id is -- an integer in the range [1,16777215] (24 bits). map_id is an externally -- defined key. CREATE TABLE map ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, path VARCHAR(255) NOT NULL, enabled BOOLEAN NOT NULL, graphid VARCHAR(9) NOT NULL, retentionpolicy_id INTEGER NOT NULL, create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, map_id), FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT, FOREIGN KEY(server_id) REFERENCES server (server_id) ON DELETE CASCADE, CHECK (create_time <= delete_time) ) -- The Device table stores information about each device. -- -- The primary key for the Device table is [server_id, map_id, device_id]. -- The device_id is an integer in the range [1,16777215] (24 bits). -- Device_id is an externally defined key. CREATE TABLE device ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, ip INET NOT NULL, ipresolve VARCHAR(4) NOT NULL CHECK (ipresolve IN ('name','addr','none')), -- name-to-address, address-to-name, or no resolving port INTEGER NOT NULL, mac MACADDR, notes VARCHAR(4096) NOT NULL, dnsname VARCHAR(255) NOT NULL, netbios VARCHAR(255) NOT NULL, probe VARCHAR(255) NOT NULL, probekind VARCHAR(6) NOT NULL CHECK (probekind IN ('none','other','snmp','tcp','udp','icmp','cmd','bigbro','ntsvcs')), probe_xml VARCHAR(4096) NOT NULL, devicekind_id INTEGER NOT NULL, enterprise_id INTEGER NOT NULL, retentionpolicy_id INTEGER NOT NULL, latitude FLOAT(53), longitude FLOAT(53), snmpversion VARCHAR(4) NOT NULL CHECK (snmpversion IN ('none','v1','v2c','v3')), sysobjectid VARCHAR(255) NOT NULL, sysdescr VARCHAR(4096) NOT NULL, sysname VARCHAR(255) NOT NULL, syslocation VARCHAR(255) NOT NULL, syscontact VARCHAR(255) NOT NULL, sysuptime TIMESTAMP WITH TIME ZONE, mfgname VARCHAR(255) NOT NULL, modelname VARCHAR(255) NOT NULL, serialnum VARCHAR(255) NOT NULL, customer_name_reference VARCHAR(255), create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, map_id, device_id), FOREIGN KEY(devicekind_id) REFERENCES devicekind (devicekind_id) ON DELETE RESTRICT, FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT, FOREIGN KEY(enterprise_id) REFERENCES ianaenterprise (enterprise_id) ON DELETE RESTRICT, CHECK (create_time <= delete_time), FOREIGN KEY(server_id, map_id) REFERENCES map (server_id, map_id) ON DELETE CASCADE ) -- The Interface table stores information about each interface. -- -- The primary key for the Interface table is [server_id, map_id, -- device_id, interface_id]. The interface_id is an integer in the range -- [1,16777215] (24 bits). Interface_id is an externally defined key. CREATE TABLE interface ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, interface_id INTEGER NOT NULL, name VARCHAR(255) NOT NULL, ifindex BIGINT NOT NULL, ifdescr VARCHAR(255) NOT NULL, ifname VARCHAR(255) NOT NULL, ifalias VARCHAR(255) NOT NULL, iftype_id INTEGER NOT NULL, ifmtu INTEGER NOT NULL, iflastchange TIMESTAMP WITH TIME ZONE NOT NULL, dot3duplex VARCHAR(7) NOT NULL CHECK (dot3duplex IN ('none','unknown','half','full')), retentionpolicy_id INTEGER NOT NULL, txspeed BIGINT NOT NULL, rxspeed BIGINT NOT NULL, mac MACADDR, status VARCHAR(8) NOT NULL CHECK (status IN ('up','down','ack','acklocal')), enabled BOOLEAN NOT NULL, pvid INTEGER NOT NULL, vlans VARCHAR(1000) NOT NULL, alarmpt VARCHAR(30) NOT NULL CHECK (alarmpt = ('@' || interface_id)), customer_name_reference VARCHAR(255), create_time TIMESTAMP WITH TIME ZONE NOT NULL, delete_time TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY (server_id, map_id, device_id, interface_id), FOREIGN KEY(iftype_id) REFERENCES ianaiftype (iftype_id), CHECK (create_time <= delete_time), FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT, FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE ) -- The Address table stores information about the IPv4 and IPv6 addresses -- associated with each Interface. An Interface that is numbered will have -- one or more addresses. So called "unnumbered" interfaces do not have any -- associated addresses in this table. -- -- The primary key of the Address table is [server_id, map_id, device_id, -- interface_id, ip]. The IPv4 address is stored as a dotted-decimal. The -- IPv6 address is stored in "canonical" colon-hexadecimal format. -- -- IPv6 Note: The "canonical" string format for IPv6 addresses is the -- lower-case, fully-expanded colon-hex format. (No ::). CREATE TABLE address ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, interface_id INTEGER NOT NULL, ip INET NOT NULL, prefix SMALLINT NOT NULL, PRIMARY KEY (server_id, map_id, device_id, interface_id, ip), FOREIGN KEY(server_id, map_id, device_id, interface_id) REFERENCES interface (server_id, map_id, device_id, interface_id) ON DELETE CASCADE ) -- The NotifierRule table stores information about the notifier triggers -- contained within an Escalation. Each row in the NotifierRule table -- corresponds to a row in a Device's Notifier window (with the six -- checkboxes). -- -- The primary key for the NotifierRule table is [server_id, map_id, -- device_id, notifier_id]. CREATE TABLE notifierrule ( server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, notifier_id INTEGER NOT NULL, down BOOLEAN NOT NULL, up BOOLEAN NOT NULL, critical BOOLEAN NOT NULL, alarm BOOLEAN NOT NULL, warning BOOLEAN NOT NULL, okay BOOLEAN NOT NULL, trap BOOLEAN NOT NULL, delay_interval INTEGER NOT NULL, repeat_interval INTEGER NOT NULL, repeat_count INTEGER NOT NULL, PRIMARY KEY (server_id, map_id, device_id, notifier_id), FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE, FOREIGN KEY(server_id, notifier_id) REFERENCES notifier (server_id, notifier_id) ON DELETE CASCADE ) -- The DataSet table stores each general-purpose data set. Each dataset -- contains a series of data points gathered over time. -- -- A DataSet inherits the retentionpolicy_id of its owning Device or -- Interface. -- -- The primary key of the DataSet table is [dataset_id]. The alternate key -- is [server_id, map_id, device_id, alarmpt, name]. CREATE TABLE dataset ( dataset_id BIGSERIAL NOT NULL, server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, alarmpt VARCHAR(255) NOT NULL, name VARCHAR(30) NOT NULL, label VARCHAR(255) NOT NULL, tags VARCHAR(1024) NOT NULL, retentionpolicy_id INTEGER NOT NULL, PRIMARY KEY (dataset_id), FOREIGN KEY(retentionpolicy_id) REFERENCES retentionpolicy (retentionpolicy_id) ON DELETE RESTRICT, FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE, UNIQUE (server_id, map_id, device_id, alarmpt, name) ) -- The ServerPoll table stores the recent IMDC polling history for all the -- servers. Only successful polls are recorded in this table. This table -- allows us to track the last time a server was polled. CREATE TABLE serverpoll ( server_id SMALLINT NOT NULL, next_utc BIGSERIAL NOT NULL, begin_time TIMESTAMP WITH TIME ZONE NOT NULL, end_time TIMESTAMP WITH TIME ZONE NOT NULL, report VARCHAR(32) NOT NULL, recv_bytes INTEGER NOT NULL, recv_rows INTEGER NOT NULL, PRIMARY KEY (server_id, next_utc, begin_time, report), FOREIGN KEY(server_id) REFERENCES server (server_id) ON DELETE CASCADE, CHECK (begin_time <= end_time) ) -- The Event table stores event history for Devices and their alarm points. -- Interface events are stored using special alarm point names. -- -- Each event has a begin_time and end_time. Current events will be modeled -- as half-open events; they will have an end_time of 'infinity'. -- -- 'memo', 'trap' and 'syslog' describe instantaneous events. As such, -- their end_time value always equals their begin_time. -- -- The 'unknown' status is not included. Unknown events do not need to be -- logged; their presence can be deduced from gaps in the time sequence. CREATE TABLE event ( event_id BIGSERIAL NOT NULL, server_id SMALLINT NOT NULL, map_id INTEGER NOT NULL, device_id INTEGER NOT NULL, alarmpt VARCHAR(255) NOT NULL, begin_time TIMESTAMP WITH TIME ZONE NOT NULL, end_time TIMESTAMP WITH TIME ZONE NOT NULL, status VARCHAR(8) NOT NULL CHECK (status IN ('down','critical','alarm','warning','okay','unknown','memo','trap','syslog')), probe VARCHAR(255) NOT NULL, reason VARCHAR(4096) NOT NULL, PRIMARY KEY (event_id), FOREIGN KEY(server_id, map_id, device_id) REFERENCES device (server_id, map_id, device_id) ON DELETE CASCADE, CHECK (begin_time <= end_time) ) -- The Acknowledgment table stores acknowlegment history for each Event. -- -- The 'current' ack state of an event is the ack_kind of the most recent -- Acknowledgement (or unack if there is none). CREATE TABLE acknowledgment ( event_id BIGINT NOT NULL, ack_time TIMESTAMP WITH TIME ZONE NOT NULL, ack_kind VARCHAR(5) NOT NULL CHECK (ack_kind IN ('ack','unack')), ack_source VARCHAR(255) NOT NULL, ack_message VARCHAR(4096) NOT NULL, PRIMARY KEY (event_id, ack_time), FOREIGN KEY(event_id) REFERENCES event (event_id) ON DELETE CASCADE ) -- The Notification table stores notification history for each Event. -- -- The 'notify_attempt' indicates the particular notification within the -- event. For status change events like "down", the first notification is -- notify_attempt 1, the next is 2, and so on. The instant notification -- sent when a device comes back "up" is attempt 0. -- -- For instantaneous events like "trap", which don't support delayed or -- repeated notifications, the notify_attempt is 0. CREATE TABLE notification ( event_id BIGINT NOT NULL, notifier_id INTEGER NOT NULL, notify_time TIMESTAMP WITH TIME ZONE NOT NULL, notify_method VARCHAR(10) NOT NULL CHECK (notify_method IN ('smtpmail','audible','snmptrap','snpppager','modempager','winpopup','cmdline','syslog','group')), notify_attempt SMALLINT NOT NULL, -- number of notification attempt; 1 for first, 2 for second, etc. notify_result VARCHAR(4) NOT NULL CHECK (notify_result IN ('okay','fail')), PRIMARY KEY (event_id, notifier_id, notify_time), FOREIGN KEY(event_id) REFERENCES event (event_id) ON DELETE CASCADE ) -- The DataPoint table stores the data readings (referred to as Points) for -- each DataSet. CREATE TABLE datapoint ( dataset_id BIGINT NOT NULL, data_time TIMESTAMP WITH TIME ZONE NOT NULL, data_value FLOAT(53) NOT NULL, PRIMARY KEY (dataset_id, data_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE ) -- The DataSample table stores samples of data readings consolidated at a -- pre-defined number of minutes within each hour. Each sample stores the -- arithmetic mean (average), standard deviation, minimum reading and -- maximum reading over the time interval. CREATE TABLE datasample ( dataset_id BIGINT NOT NULL, sample_time TIMESTAMP WITH TIME ZONE NOT NULL, sample_mean FLOAT(53) NOT NULL, sample_stdv FLOAT(53) NOT NULL, sample_min FLOAT(53) NOT NULL, sample_max FLOAT(53) NOT NULL, sample_size SMALLINT NOT NULL, PRIMARY KEY (dataset_id, sample_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE ) -- The DataSampleHourly table stores samples of data readings consolidated -- every hour. Each sample stores the arithmetic mean (average), standard -- deviation, minimum reading and maximum reading for the entire hour. CREATE TABLE datasamplehourly ( dataset_id BIGINT NOT NULL, sample_time TIMESTAMP WITH TIME ZONE NOT NULL, sample_mean FLOAT(53) NOT NULL, sample_stdv FLOAT(53) NOT NULL, sample_min FLOAT(53) NOT NULL, sample_max FLOAT(53) NOT NULL, sample_size SMALLINT NOT NULL, PRIMARY KEY (dataset_id, sample_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE ) -- The DataSampleDaily table stores samples of data readings consolidated -- every day. Each sample stores the arithmetic mean (average), standard -- deviation, minimum reading and maximum reading for the entire day. -- -- A day is defined in the UTC; it starts at 00:00:00 UTC and ends at -- 23:59:59 UTC. CREATE TABLE datasampledaily ( dataset_id BIGINT NOT NULL, sample_time DATE NOT NULL, sample_mean FLOAT(53) NOT NULL, sample_stdv FLOAT(53) NOT NULL, sample_min FLOAT(53) NOT NULL, sample_max FLOAT(53) NOT NULL, sample_size INTEGER NOT NULL, PRIMARY KEY (dataset_id, sample_time), FOREIGN KEY(dataset_id) REFERENCES dataset (dataset_id) ON DELETE CASCADE )