Rev 3 | Blame | Last modification | View Log | Download | RSS feed
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `camp2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `camp2` ;
-- -----------------------------------------------------
-- Table `camp2`.`_user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`_user` ;
CREATE TABLE IF NOT EXISTS `camp2`.`_user` (
`_user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NOT NULL COMMENT 'user login name' ,
`passwd` CHAR(32) NOT NULL COMMENT 'md5 hash of users password' ,
`email` VARCHAR(64) NULL COMMENT 'email address of user' ,
`added_date` DATETIME NULL COMMENT 'date user was added' ,
`removed_date` DATETIME NULL DEFAULT NULL COMMENT 'date user removed' ,
PRIMARY KEY (`_user_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `camp2`.`client`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`client` ;
CREATE TABLE IF NOT EXISTS `camp2`.`client` (
`client_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NULL DEFAULT NULL COMMENT 'display name of client' ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`client_id`) ,
INDEX `fk_client_1` (`_user_id` ASC) ,
CONSTRAINT `fk_client_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'stores client name. other client info stored in client_attri' /* comment truncated */;
-- -----------------------------------------------------
-- Table `camp2`.`location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`location` ;
CREATE TABLE IF NOT EXISTS `camp2`.`location` (
`location_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NULL DEFAULT NULL COMMENT 'display name of location' ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`location_id`) ,
INDEX `fk_location_1` (`_user_id` ASC) ,
CONSTRAINT `fk_location_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'stores location name. other location info stored in location' /* comment truncated */;
-- -----------------------------------------------------
-- Table `camp2`.`device_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`device_type` ;
CREATE TABLE IF NOT EXISTS `camp2`.`device_type` (
`device_type_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NULL DEFAULT NULL COMMENT 'display name of device type' ,
`show_as_system` TINYINT(1) NULL DEFAULT NULL COMMENT 'whether to display as a system or not' ,
PRIMARY KEY (`device_type_id`) )
ENGINE = InnoDB
COMMENT = 'types of devices (computer, printer ...)';
-- -----------------------------------------------------
-- Table `camp2`.`device`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`device` ;
CREATE TABLE IF NOT EXISTS `camp2`.`device` (
`device_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NULL DEFAULT NULL COMMENT 'display name of device' ,
`device_type_id` INT UNSIGNED NOT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`device_id`) ,
INDEX `fk_ecf202fe-d2bf-11e5-94fa-e0cb4ed780b9` (`device_type_id` ASC) ,
INDEX `fk_device_1` (`_user_id` ASC) ,
CONSTRAINT `fk_ecf202fe-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`device_type_id` )
REFERENCES `camp2`.`device_type` (`device_type_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_device_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'stores device name and type. other device info stored in dev' /* comment truncated */;
-- -----------------------------------------------------
-- Table `camp2`.`device_location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`device_location` ;
CREATE TABLE IF NOT EXISTS `camp2`.`device_location` (
`device_location_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NOT NULL ,
`location_id` INT UNSIGNED NOT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`device_location_id`) ,
INDEX (`device_id` ASC, `location_id` ASC, `added_date` ASC, `removed_date` ASC) ,
INDEX `fk_ecf2308a-d2bf-11e5-94fa-e0cb4ed780b9` (`location_id` ASC) ,
INDEX `fk_device_location_1` (`_user_id` ASC) ,
CONSTRAINT `fk_ecf22df6-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_ecf2308a-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`location_id` )
REFERENCES `camp2`.`location` (`location_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_device_location_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'links location and device tables';
-- -----------------------------------------------------
-- Table `camp2`.`device_client`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`device_client` ;
CREATE TABLE IF NOT EXISTS `camp2`.`device_client` (
`device_client_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NOT NULL ,
`client_id` INT UNSIGNED NOT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`device_client_id`) ,
INDEX `fk_device_client_device` (`device_id` ASC) ,
INDEX `fk_device_client_client` (`client_id` ASC) ,
INDEX `fk_device_client_user` (`_user_id` ASC) ,
CONSTRAINT `fk_device_client_device`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_device_client_client`
FOREIGN KEY (`client_id` )
REFERENCES `camp2`.`client` (`client_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_device_client_user`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'links device and client tables';
-- -----------------------------------------------------
-- Table `camp2`.`location_client`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`location_client` ;
CREATE TABLE IF NOT EXISTS `camp2`.`location_client` (
`location_client_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`location_id` INT UNSIGNED NOT NULL ,
`client_id` INT UNSIGNED NOT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`location_client_id`) ,
INDEX (`location_id` ASC, `client_id` ASC) ,
INDEX `fk_ecf26550-d2bf-11e5-94fa-e0cb4ed780b9` (`client_id` ASC) ,
INDEX `fk_location_client_1` (`_user_id` ASC) ,
CONSTRAINT `fk_ecf262d0-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`location_id` )
REFERENCES `camp2`.`location` (`location_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_ecf26550-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`client_id` )
REFERENCES `camp2`.`client` (`client_id` ),
CONSTRAINT `fk_location_client_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'links location and client tables';
-- -----------------------------------------------------
-- Table `camp2`.`category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`category` ;
CREATE TABLE IF NOT EXISTS `camp2`.`category` (
`category_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NULL DEFAULT NULL ,
PRIMARY KEY (`category_id`) )
ENGINE = InnoDB
COMMENT = 'categories for attribute table';
-- -----------------------------------------------------
-- Table `camp2`.`attribute`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`attribute` ;
CREATE TABLE IF NOT EXISTS `camp2`.`attribute` (
`attribute_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NOT NULL COMMENT 'name for display' ,
`report_name` VARCHAR(64) NULL DEFAULT NULL COMMENT 'name in sysinfo reports' ,
`category_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`attribute_id`) ,
INDEX `fk_ecf28e4a-d2bf-11e5-94fa-e0cb4ed780b9` (`category_id` ASC) ,
CONSTRAINT `fk_ecf28e4a-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`category_id` )
REFERENCES `camp2`.`category` (`category_id` ))
ENGINE = InnoDB
COMMENT = 'stores attribute names (not values) for other tables';
-- -----------------------------------------------------
-- Table `camp2`.`device_attribute`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`device_attribute` ;
CREATE TABLE IF NOT EXISTS `camp2`.`device_attribute` (
`device_attribute_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NOT NULL ,
`attribute_id` INT UNSIGNED NOT NULL ,
`report_value` TEXT NULL DEFAULT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`device_attribute_id`) ,
INDEX (`device_id` ASC, `attribute_id` ASC, `added_date` ASC, `removed_date` ASC) ,
INDEX `fk_ecf2a47a-d2bf-11e5-94fa-e0cb4ed780b9` (`attribute_id` ASC) ,
INDEX `fk_device_attribute_1` (`_user_id` ASC) ,
CONSTRAINT `fk_ecf2a178-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` ),
CONSTRAINT `fk_ecf2a47a-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`attribute_id` )
REFERENCES `camp2`.`attribute` (`attribute_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_device_attribute_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'stores various attribute values for the device table keeping' /* comment truncated */;
-- -----------------------------------------------------
-- Table `camp2`.`client_attribute`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`client_attribute` ;
CREATE TABLE IF NOT EXISTS `camp2`.`client_attribute` (
`client_attribute_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`client_id` INT UNSIGNED NOT NULL ,
`attribute_id` INT UNSIGNED NOT NULL ,
`report_value` TEXT NULL DEFAULT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`client_attribute_id`) ,
INDEX (`client_id` ASC, `attribute_id` ASC, `added_date` ASC, `removed_date` ASC) ,
INDEX `fk_ecf2c356-d2bf-11e5-94fa-e0cb4ed780b9` (`attribute_id` ASC) ,
INDEX `fk_client_attribute_1` (`_user_id` ASC) ,
CONSTRAINT `fk_ecf2c0b8-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`client_id` )
REFERENCES `camp2`.`client` (`client_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_ecf2c356-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`attribute_id` )
REFERENCES `camp2`.`attribute` (`attribute_id` ),
CONSTRAINT `fk_client_attribute_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'stores various attribute values for the client table keeping' /* comment truncated */;
-- -----------------------------------------------------
-- Table `camp2`.`location_attribute`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`location_attribute` ;
CREATE TABLE IF NOT EXISTS `camp2`.`location_attribute` (
`location_attribute_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`location_id` INT UNSIGNED NOT NULL ,
`attribute_id` INT UNSIGNED NOT NULL ,
`report_value` TEXT NULL DEFAULT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`location_attribute_id`) ,
INDEX (`location_id` ASC, `attribute_id` ASC, `added_date` ASC, `removed_date` ASC) ,
INDEX `fk_ecf2e2c8-d2bf-11e5-94fa-e0cb4ed780b9` (`attribute_id` ASC) ,
INDEX `fk_location_attribute_1` (`_user_id` ASC) ,
CONSTRAINT `fk_ecf2e03e-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`location_id` )
REFERENCES `camp2`.`location` (`location_id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_ecf2e2c8-d2bf-11e5-94fa-e0cb4ed780b9`
FOREIGN KEY (`attribute_id` )
REFERENCES `camp2`.`attribute` (`attribute_id` ),
CONSTRAINT `fk_location_attribute_1`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'stores various attribute values for the location table keepi' /* comment truncated */;
-- -----------------------------------------------------
-- Table `camp2`.`_system`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`_system` ;
CREATE TABLE IF NOT EXISTS `camp2`.`_system` (
`_system_id` INT NOT NULL AUTO_INCREMENT ,
`group_name` VARCHAR(45) NOT NULL ,
`key_name` VARCHAR(45) NOT NULL ,
`key_value` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`_system_id`) )
ENGINE = InnoDB
COMMENT = 'Stores internal system information like ini file';
-- -----------------------------------------------------
-- Table `camp2`.`_menu`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`_menu` ;
CREATE TABLE IF NOT EXISTS `camp2`.`_menu` (
`_menu_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`parent_id` INT UNSIGNED NULL DEFAULT NULL ,
`caption` VARCHAR(20) NOT NULL ,
`url` VARCHAR(64) NULL ,
PRIMARY KEY (`_menu_id`) )
ENGINE = InnoDB
COMMENT = 'Menus for the application';
-- -----------------------------------------------------
-- Table `camp2`.`software`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`software` ;
CREATE TABLE IF NOT EXISTS `camp2`.`software` (
`software_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NOT NULL COMMENT 'name of the software package' ,
`description` TEXT NULL DEFAULT NULL COMMENT 'optional multiline description of package' ,
`added_date` DATETIME NULL ,
PRIMARY KEY (`software_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `camp2`.`software_version`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`software_version` ;
CREATE TABLE IF NOT EXISTS `camp2`.`software_version` (
`software_version_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NOT NULL COMMENT 'full version identifier, possibly version and release or other data' ,
`added_date` DATETIME NOT NULL ,
PRIMARY KEY (`software_version_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `camp2`.`device_software`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`device_software` ;
CREATE TABLE IF NOT EXISTS `camp2`.`device_software` (
`device_software_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NOT NULL ,
`software_id` INT UNSIGNED NOT NULL ,
`software_version_id` INT UNSIGNED NOT NULL ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`device_software_id`) ,
INDEX `fk_device_software_1` (`device_id` ASC) ,
INDEX `fk_device_software_2` (`device_id` ASC) ,
INDEX `fk_device_software_3` (`software_version_id` ASC) ,
INDEX `fk_device_software_4` (`_user_id` ASC) ,
CONSTRAINT `fk_device_software_1`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_device_software_2`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`software` (`software_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_device_software_3`
FOREIGN KEY (`software_version_id` )
REFERENCES `camp2`.`software_version` (`software_version_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_device_software_4`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `camp2`.`device_network`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`device_network` ;
CREATE TABLE IF NOT EXISTS `camp2`.`device_network` (
`device_network_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NOT NULL ,
`interface` VARCHAR(32) NOT NULL COMMENT 'interface name' ,
`address` CHAR(15) NULL COMMENT 'IPv4 Address' ,
`netmask` CHAR(15) NULL COMMENT 'IPv4 netmask' ,
`ip6_address` VARCHAR(32) NULL COMMENT 'IPv6 Address' ,
`ip6_net` INT NULL COMMENT 'ipv6 netmask' ,
`mtu` INT NULL COMMENT 'MTU of connection' ,
`mac` VARCHAR(20) NULL COMMENT 'mac address of device' ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`device_network_id`) ,
INDEX `fk_device_network_1` (`device_id` ASC) ,
CONSTRAINT `fk_device_network_1`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `camp2`.`sysinfo_report`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`sysinfo_report` ;
CREATE TABLE IF NOT EXISTS `camp2`.`sysinfo_report` (
`sysinfo_report_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NULL ,
`client_version` VARCHAR(5) NULL COMMENT 'Version of the sysinfo client reporting this' ,
`report_date` DATETIME NOT NULL COMMENT 'Date on the report itself' ,
`processed_date` DATETIME NOT NULL COMMENT 'Date it was put into the system' ,
PRIMARY KEY (`sysinfo_report_id`) ,
INDEX `fk_sysinfo_report_1` (`device_id` ASC) ,
INDEX `device_date` (`device_id` ASC, `report_date` ASC) ,
CONSTRAINT `fk_sysinfo_report_1`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `camp2`.`maintenance_task`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`maintenance_task` ;
CREATE TABLE IF NOT EXISTS `camp2`.`maintenance_task` (
`maintenance_task_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(64) NOT NULL COMMENT 'Listing of different maintenance tasks to be performed' ,
`description` TEXT NULL ,
`default_period` INT NOT NULL DEFAULT 7 ,
PRIMARY KEY (`maintenance_task_id`) )
ENGINE = InnoDB
COMMENT = 'list of maintenance tasks which can be performed';
-- -----------------------------------------------------
-- Table `camp2`.`maintenance_schedule`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`maintenance_schedule` ;
CREATE TABLE IF NOT EXISTS `camp2`.`maintenance_schedule` (
`maintenance_schedule_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NOT NULL ,
`maintenance_task_id` INT UNSIGNED NOT NULL ,
`schedule` INT NOT NULL DEFAULT 7 ,
`_user_id` INT UNSIGNED NOT NULL ,
`added_date` DATETIME NOT NULL ,
`removed_date` DATETIME NULL DEFAULT NULL ,
PRIMARY KEY (`maintenance_schedule_id`) ,
INDEX `fk_maintenance_schedule_device` (`device_id` ASC) ,
INDEX `fk_maintenance_schedule_task` (`maintenance_task_id` ASC) ,
INDEX `fk_maintenance_schedule_user` (`_user_id` ASC) ,
CONSTRAINT `fk_maintenance_schedule_device`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_maintenance_schedule_task`
FOREIGN KEY (`maintenance_task_id` )
REFERENCES `camp2`.`maintenance_task` (`maintenance_task_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_maintenance_schedule_user`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'desired schedule of maintenance for machine';
-- -----------------------------------------------------
-- Table `camp2`.`maintenance_performed`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`maintenance_performed` ;
CREATE TABLE IF NOT EXISTS `camp2`.`maintenance_performed` (
`maintenance_performed_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`device_id` INT UNSIGNED NOT NULL ,
`maintenance_task_id` INT UNSIGNED NOT NULL ,
`date_performed` DATETIME NOT NULL COMMENT 'date technician performed the maintenanc' ,
`_user_id` INT UNSIGNED NOT NULL ,
`notes` TEXT NULL COMMENT 'any notes the technician wants to make' ,
PRIMARY KEY (`maintenance_performed_id`) ,
INDEX `fk_maintenance_performed_task` (`maintenance_task_id` ASC) ,
INDEX `fk_maintenance_performed_device` (`device_id` ASC) ,
INDEX `fk_maintenance_performed_user` (`_user_id` ASC) ,
CONSTRAINT `fk_maintenance_performed_task`
FOREIGN KEY (`maintenance_task_id` )
REFERENCES `camp2`.`maintenance_task` (`maintenance_task_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_maintenance_performed_device`
FOREIGN KEY (`device_id` )
REFERENCES `camp2`.`device` (`device_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_maintenance_performed_user`
FOREIGN KEY (`_user_id` )
REFERENCES `camp2`.`_user` (`_user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'maintenance actually performed on equipment';
-- -----------------------------------------------------
-- Table `camp2`.`maintenance_group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`maintenance_group` ;
CREATE TABLE IF NOT EXISTS `camp2`.`maintenance_group` (
`maintenance_group_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL COMMENT 'name of the task group' ,
`notes` TEXT NULL COMMENT 'any notes' ,
`last_modified` TIMESTAMP NULL ,
PRIMARY KEY (`maintenance_group_id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `camp2`.`maintenance_group_task`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `camp2`.`maintenance_group_task` ;
CREATE TABLE IF NOT EXISTS `camp2`.`maintenance_group_task` (
`maintenance_group_task_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`maintenance_task_id` INT UNSIGNED NOT NULL ,
`maintenance_group_id` INT UNSIGNED NOT NULL ,
`default_period` INT NULL DEFAULT NULL COMMENT 'ability to change the default period for a task' ,
PRIMARY KEY (`maintenance_group_task_id`) ,
INDEX `fk_maintenance_group_task_group` (`maintenance_group_id` ASC) ,
INDEX `fk_maintenance_group_task_task` (`maintenance_task_id` ASC) ,
CONSTRAINT `fk_maintenance_group_task_group`
FOREIGN KEY (`maintenance_group_id` )
REFERENCES `camp2`.`maintenance_group` (`maintenance_group_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_maintenance_group_task_task`
FOREIGN KEY (`maintenance_task_id` )
REFERENCES `camp2`.`maintenance_task` (`maintenance_task_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Placeholder table for view `camp2`.`v_client_attribute`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `camp2`.`v_client_attribute` (`attribute_id` INT, `name` INT, `report_name` INT, `category_id` INT);
-- -----------------------------------------------------
-- Placeholder table for view `camp2`.`v_current_device`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `camp2`.`v_current_device` (`device_id` INT, `name` INT, `device_type_id` INT, `_user_id` INT, `added_date` INT, `removed_date` INT);
-- -----------------------------------------------------
-- Placeholder table for view `camp2`.`v_current_client`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `camp2`.`v_current_client` (`client_id` INT, `name` INT, `_user_id` INT, `added_date` INT, `removed_date` INT);
-- -----------------------------------------------------
-- Placeholder table for view `camp2`.`v_current_location`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `camp2`.`v_current_location` (`location_id` INT, `name` INT, `_user_id` INT, `added_date` INT, `removed_date` INT);
-- -----------------------------------------------------
-- View `camp2`.`v_client_attribute`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `camp2`.`v_client_attribute` ;
DROP TABLE IF EXISTS `camp2`.`v_client_attribute`;
USE `camp2`;
CREATE OR REPLACE VIEW `camp2`.`v_client_attribute` AS
select
attribute.attribute_id,
attribute.name,
attribute.report_name,
attribute.category_id
from attribute join category using (category_id)
where category.name = 'client';
;
-- -----------------------------------------------------
-- View `camp2`.`v_current_device`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `camp2`.`v_current_device` ;
DROP TABLE IF EXISTS `camp2`.`v_current_device`;
USE `camp2`;
CREATE OR REPLACE VIEW `camp2`.`v_current_device` AS
select * from device where device.removed_date is null;
;
-- -----------------------------------------------------
-- View `camp2`.`v_current_client`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `camp2`.`v_current_client` ;
DROP TABLE IF EXISTS `camp2`.`v_current_client`;
USE `camp2`;
CREATE OR REPLACE VIEW `camp2`.`v_current_client` AS
select * from client where removed_date is null;
;
-- -----------------------------------------------------
-- View `camp2`.`v_current_location`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `camp2`.`v_current_location` ;
DROP TABLE IF EXISTS `camp2`.`v_current_location`;
USE `camp2`;
CREATE OR REPLACE VIEW `camp2`.`v_current_location` AS
select * from location where location.removed_date is null
;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;