# Copyright (c) 2000-2002 Carnegie Mellon University. All rights reserved. # Redistribution and use in source and binary forms, with or without # modification, are permitted provided that the following conditions are met: # # 1. Redistributions of source code must retain the above copyright notice, # this list of conditions and the following disclaimer. # # 2. Redistributions in binary form must reproduce the above copyright notice, # this list of conditions and the following disclaimer in the # documentation and/or other materials provided with the distribution. # # 3. The name "Carnegie Mellon University" must not be used to endorse or # promote products derived from this software without prior written # permission. For permission or any legal details, please contact: # Office of Technology Transfer # Carnegie Mellon University # 5000 Forbes Avenue # Pittsburgh, PA 15213-3890 # (412) 268-4387, fax: (412) 268-7395 # tech-transfer@andrew.cmu.edu # # 4. Redistributions of any form whatsoever must retain the following # acknowledgment: "This product includes software developed by Computing # Services at Carnegie Mellon University (http://www.cmu.edu/computing/)." # # CARNEGIE MELLON UNIVERSITY DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS # SOFTWARE, INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, # IN NO EVENT SHALL CARNEGIE MELLON UNIVERSITY BE LIABLE FOR ANY SPECIAL, # INDIRECT OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM # LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE # OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR # PERFORMANCE OF THIS SOFTWARE. DROP DATABASE IF EXISTS netsage; CREATE DATABASE netsage; CONNECT netsage; CREATE TABLE views ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, external CHAR(255), PRIMARY KEY index_id (id) ) TYPE = InnoDB; CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(64) NOT NULL, default_view INT UNSIGNED, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_name (name), INDEX index_default_view (default_view), FOREIGN KEY (default_view) REFERENCES views(id) ON DELETE SET NULL ) TYPE = InnoDB; CREATE TABLE usergroups ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, default_view INT UNSIGNED, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_name (name) ) TYPE = InnoDB; CREATE TABLE usergroup_memberships ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), usergroup INT UNSIGNED NOT NULL, user INT UNSIGNED NOT NULL, PRIMARY KEY index_id (id), UNIQUE KEY index_memberships (usergroup, user), INDEX index_user (user, usergroup), FOREIGN KEY (usergroup) REFERENCES usergroups(id) ON DELETE CASCADE, FOREIGN KEY (user) REFERENCES users(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE service_class ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_name (name) ) TYPE = InnoDB; CREATE TABLE service_responsibility ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_name (name) ) TYPE = InnoDB; CREATE TABLE hostgroups ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, description CHAR(255) NOT NULL, service_class INT UNSIGNED NOT NULL, service_responsibility INT UNSIGNED NOT NULL, external CHAR(255), PRIMARY KEY index_id (id), INDEX index_class (service_class), INDEX index_responsibility (service_responsibility), UNIQUE KEY index_name (name), FOREIGN KEY (service_class) REFERENCES service_class(id), FOREIGN KEY (service_responsibility) REFERENCES service_responsibility(id) ) TYPE = InnoDB; CREATE TABLE hosts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), host_name CHAR(255) NOT NULL, ip_address INT UNSIGNED NOT NULL, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_host (host_name), UNIQUE KEY index_ip (ip_address) ) TYPE = InnoDB; CREATE TABLE hostgroup_memberships ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), hostgroup INT UNSIGNED NOT NULL, host INT UNSIGNED NOT NULL, PRIMARY KEY index_id (id), UNIQUE KEY index_memberships (hostgroup, host), INDEX index_host (host, hostgroup), FOREIGN KEY (hostgroup) REFERENCES hostgroups(id) ON DELETE CASCADE, FOREIGN KEY (host) REFERENCES hosts(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE permissions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), user INT UNSIGNED, level INT UNSIGNED NOT NULL, usergroup INT UNSIGNED, hostgroup INT UNSIGNED, PRIMARY KEY index_id (id), INDEX index_perms (user, level, hostgroup, id), INDEX index_perms2 (usergroup, level, hostgroup, id), INDEX index_group (hostgroup), FOREIGN KEY (user) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (usergroup) REFERENCES usergroups(id) ON DELETE CASCADE, FOREIGN KEY (hostgroup) REFERENCES hostgroups(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE view_memberships ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), view INT UNSIGNED NOT NULL, hostgroup INT UNSIGNED, subview INT UNSIGNED, PRIMARY KEY index_id (id), INDEX index_memberships (view, hostgroup, subview), INDEX index_group (hostgroup), INDEX index_subview (subview), FOREIGN KEY (view) REFERENCES views(id) ON DELETE CASCADE, FOREIGN KEY (hostgroup) REFERENCES hostgroups(id) ON DELETE CASCADE, FOREIGN KEY (subview) REFERENCES views(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE service_types ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_name (name) ) TYPE = InnoDB; CREATE TABLE services ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), hostgroup INT UNSIGNED NOT NULL, service_type INT UNSIGNED NOT NULL, alert_dependency_tree INT UNSIGNED, monitor_dependency_tree INT UNSIGNED, host_dependency_tree INT UNSIGNED, class INT UNSIGNED, responsibility INT UNSIGNED, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_groupsvc (hostgroup, service_type), INDEX index_svc (service_type), INDEX index_adep (alert_dependency_tree), INDEX index_mdep (monitor_dependency_tree), INDEX index_hdep (host_dependency_tree), INDEX index_class (class), INDEX index_responsibility (responsibility), FOREIGN KEY (hostgroup) REFERENCES hostgroups(id) ON DELETE CASCADE, FOREIGN KEY (class) REFERENCES service_class(id), FOREIGN KEY (responsibility) REFERENCES service_responsibility(id) ON DELETE CASCADE, FOREIGN KEY (service_type) REFERENCES service_types(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE alert_period ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255), parent_table ENUM ('services', 'service_types'), service_type INT UNSIGNED, service INT UNSIGNED, external CHAR(255), PRIMARY KEY index_id (id), INDEX index_service (service, parent_table), INDEX index_servicetype (service_type, parent_table), FOREIGN KEY (service) REFERENCES services(id) ON DELETE CASCADE, FOREIGN KEY (service_type) REFERENCES service_types(id) ON DELETE CASCADE, UNIQUE index_svc (name, service), UNIQUE index_svctype (name, service_type) ) TYPE = InnoDB; CREATE TABLE subperiods ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), alert_period INT UNSIGNED, weekday_start INT, # Sunday = 1, Saturday = 7 weekday_end INT, time_start TIME, time_end TIME, month_start INT, # January = 1, December = 12 month_end INT, monthday_start INT, monthday_end INT, monthweek_start INT, monthweek_end INT, external CHAR(255), PRIMARY KEY index_id (id), INDEX index_alert_period (alert_period), FOREIGN KEY (alert_period) REFERENCES alert_period(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE server_types ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), software CHAR(255) NOT NULL, software_version CHAR(64) NOT NULL, description CHAR(255), external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_software (software, software_version) ) TYPE = InnoDB; CREATE TABLE server_files ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), server_type INT UNSIGNED NOT NULL, file CHAR(255) NOT NULL, PRIMARY KEY index_id (id), INDEX index_type (server_type), FOREIGN KEY (server_type) REFERENCES server_types(id) ON DELETE CASCADE, UNIQUE index_file (file, server_type) ) TYPE = InnoDB; CREATE TABLE servers ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, host INT UNSIGNED, server_type INT UNSIGNED NOT NULL, description CHAR(255), external CHAR(255), PRIMARY KEY index_id (id), INDEX index_host (host), INDEX index_type (server_type), FOREIGN KEY (host) REFERENCES hosts(id) ON DELETE SET NULL, FOREIGN KEY (server_type) REFERENCES server_types(id) ON DELETE CASCADE, UNIQUE index_name (name) ) TYPE = InnoDB; CREATE TABLE actions ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_name (name) ) TYPE = InnoDB; CREATE TABLE attribute_types ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), name CHAR(255) NOT NULL, parent_table SET ('services','service_types','alert_period'), value_has_string BOOL, value_string_format CHAR(255), value_has_number BOOL, value_has_range BOOL, value_has_time BOOL, value_has_boolean BOOL, value_has_host BOOL, value_has_usergroup BOOL, value_has_period BOOL, value_has_action BOOL, external CHAR(255), PRIMARY KEY index_id (id), UNIQUE KEY index_name (name) ) TYPE = InnoDB; CREATE TABLE action_attribute_types ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), action INT UNSIGNED, attribute_type INT UNSIGNED, PRIMARY KEY index_id (id), UNIQUE KEY index_action (action,attribute_type), INDEX index_attribute_type (attribute_type), FOREIGN KEY (action) REFERENCES actions(id) ON DELETE CASCADE, FOREIGN KEY (attribute_type) REFERENCES attribute_types(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE dependency_tree ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), oper ENUM ('AND', 'OR', 'NOT', 'NONE'), left_type ENUM ('TREE', 'LEAF'), left_dep_link INT UNSIGNED, left_service_link INT UNSIGNED, right_type ENUM ('TREE', 'LEAF', 'NONE'), right_dep_link INT UNSIGNED, right_service_link INT UNSIGNED, parent_service INT UNSIGNED NOT NULL, # Just for purposes of cascade delete external CHAR(255), PRIMARY KEY index_id (id), INDEX index_left_dep_link (left_dep_link), FOREIGN KEY (left_dep_link) REFERENCES dependency_tree(id) ON DELETE SET NULL, INDEX index_left_service_link (left_service_link), FOREIGN KEY (left_service_link) REFERENCES services(id) ON DELETE SET NULL, INDEX index_right_dep_link (right_dep_link), FOREIGN KEY (right_dep_link) REFERENCES dependency_tree(id) ON DELETE SET NULL, INDEX index_right_service_link (right_service_link), FOREIGN KEY (right_service_link) REFERENCES services(id) ON DELETE SET NULL, INDEX index_parent_service (parent_service), FOREIGN KEY (parent_service) REFERENCES services(id) ON DELETE CASCADE ) TYPE = InnoDB; CREATE TABLE attributes ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, version TIMESTAMP(14), attribute_type INT UNSIGNED NOT NULL, string CHAR(255), number INT, range_start INT, range_end INT, time INT UNSIGNED, boolean BOOL, server INT UNSIGNED NOT NULL, server_file INT UNSIGNED NOT NULL, service_class INT UNSIGNED, service_responsibility INT UNSIGNED, service INT UNSIGNED, alert_period INT UNSIGNED, service_type INT UNSIGNED, period INT UNSIGNED, action INT UNSIGNED, host INT UNSIGNED, usergroup INT UNSIGNED, PRIMARY KEY index_id (id), INDEX index_attribute_type (attribute_type), INDEX index_server (server), INDEX index_server_file (server_file), INDEX index_service (service), INDEX index_service_class (service_class), INDEX index_service_responsibility (service_responsibility), INDEX index_alert_period (alert_period), INDEX index_service_type (service_type), INDEX index_period (period), INDEX index_action (action), INDEX index_host (host), INDEX index_usergroup (usergroup), FOREIGN KEY (attribute_type) REFERENCES attribute_types(id) ON DELETE CASCADE, FOREIGN KEY (server) REFERENCES servers(id) ON DELETE CASCADE, FOREIGN KEY (server_file) REFERENCES server_files(id) ON DELETE CASCADE, FOREIGN KEY (service) REFERENCES services(id) ON DELETE CASCADE, FOREIGN KEY (service_class) REFERENCES service_class(id) ON DELETE CASCADE, FOREIGN KEY (service_responsibility) REFERENCES service_responsibility(id) ON DELETE CASCADE, FOREIGN KEY (alert_period) REFERENCES alert_period(id) ON DELETE CASCADE, FOREIGN KEY (service_type) REFERENCES service_types(id) ON DELETE CASCADE, FOREIGN KEY (period) REFERENCES subperiods(id) ON DELETE CASCADE, FOREIGN KEY (action) REFERENCES actions(id) ON DELETE CASCADE, FOREIGN KEY (host) REFERENCES hosts(id) ON DELETE CASCADE, FOREIGN KEY (usergroup) REFERENCES usergroups(id) ON DELETE CASCADE ) TYPE = InnoDB; ALTER TABLE services ADD FOREIGN KEY (alert_dependency_tree) REFERENCES dependency_tree(id) ON DELETE SET NULL; ALTER TABLE services ADD FOREIGN KEY (monitor_dependency_tree) REFERENCES dependency_tree(id) ON DELETE SET NULL; ALTER TABLE services ADD FOREIGN KEY (host_dependency_tree) REFERENCES dependency_tree(id) ON DELETE SET NULL;