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 `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
CREATE SCHEMA IF NOT EXISTS `tester` DEFAULT CHARACTER SET latin1 ;
USE `tester`;

-- -----------------------------------------------------
-- Table `tester`.`city`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`city` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`country`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`country` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`timezone`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`timezone` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) NOT NULL ,
  `offset` TINYINT(4) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`zipcode`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`zipcode` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `code` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`address`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`address` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `street` VARCHAR(255) NOT NULL ,
  `number` INT(11) NOT NULL ,
  `cityId` BIGINT(20) NOT NULL ,
  `zipcodeId` BIGINT(20) NOT NULL ,
  `countryId` BIGINT(20) NOT NULL ,
  `timezoneId` BIGINT(20) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_Address_City` (`cityId` ASC) ,
  INDEX `fk_Address_Country` (`countryId` ASC) ,
  INDEX `fk_Address_TimeZone` (`timezoneId` ASC) ,
  INDEX `fk_Address_ZipCode` (`zipcodeId` ASC) ,
  CONSTRAINT `fk_Address_City`
    FOREIGN KEY (`cityId` )
    REFERENCES `tester`.`city` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Address_Country`
    FOREIGN KEY (`countryId` )
    REFERENCES `tester`.`country` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Address_TimeZone`
    FOREIGN KEY (`timezoneId` )
    REFERENCES `tester`.`timezone` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Address_ZipCode`
    FOREIGN KEY (`zipcodeId` )
    REFERENCES `tester`.`zipcode` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`application`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`application` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`applicationcall`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`applicationcall` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `value` BIGINT(20) NOT NULL ,
  `data` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`applicationcalllist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`applicationcalllist` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `applicationId` BIGINT(20) NOT NULL ,
  `applicationCallId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_ApplicationSettingList_Application` (`applicationId` ASC) ,
  INDEX `fk_ApplicationSettingList_ApplicationSetting` (`applicationCallId` ASC) ,
  CONSTRAINT `fk_ApplicationSettingList_Application`
    FOREIGN KEY (`applicationId` )
    REFERENCES `tester`.`application` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ApplicationSettingList_ApplicationSetting`
    FOREIGN KEY (`applicationCallId` )
    REFERENCES `tester`.`applicationcall` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodefirmwaretype`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodefirmwaretype` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `operatingSystem` TINYINT(4) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodefirmware`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodefirmware` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `firmwareType` BIGINT(20) NOT NULL ,
  `version` FLOAT NOT NULL ,
  `flashSize` INT(11) NOT NULL ,
  `ramSize` INT(11) NOT NULL ,
  `eepromSize` INT(11) NOT NULL ,
  `data` BLOB NOT NULL ,
  `checksum` VARCHAR(255) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_NodeFirmware_NodeFirmwareType` (`firmwareType` ASC) ,
  CONSTRAINT `fk_NodeFirmware_NodeFirmwareType`
    FOREIGN KEY (`firmwareType` )
    REFERENCES `tester`.`nodefirmwaretype` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`cartesianpoint`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`cartesianpoint` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `x` FLOAT NOT NULL ,
  `y` FLOAT NOT NULL ,
  `zLevel` INT(11) NOT NULL ,
  `certainty` TINYINT(4) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`gpspoint`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`gpspoint` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `longitude` FLOAT NOT NULL ,
  `latitude` FLOAT NOT NULL ,
  `certainty` TINYINT(4) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`location`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`location` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `gpsPointId` BIGINT(20) NOT NULL ,
  `cartesianPointId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_Location_CartesianPoint` (`cartesianPointId` ASC) ,
  INDEX `fk_Location_GpsPosition` (`gpsPointId` ASC) ,
  CONSTRAINT `fk_Location_CartesianPoint`
    FOREIGN KEY (`cartesianPointId` )
    REFERENCES `tester`.`cartesianpoint` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Location_GpsPosition`
    FOREIGN KEY (`gpsPointId` )
    REFERENCES `tester`.`gpspoint` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`areatype`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`areatype` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`areaplan`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`areaplan` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `image` BLOB NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`area`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`area` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  `number` BIGINT(20) NOT NULL ,
  `centerGpsPointId` BIGINT(20) NOT NULL ,
  `centerCartesianPointId` BIGINT(20) NOT NULL ,
  `addressId` BIGINT(20) NOT NULL ,
  `areaTypeId` BIGINT(20) NOT NULL ,
  `areaPlanId` BIGINT(20) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_AreaPosition_AreaType` (`areaTypeId` ASC) ,
  INDEX `fk_AreaPosition_GpsPosition` (`centerGpsPointId` ASC) ,
  INDEX `fk_Area_Address` (`addressId` ASC) ,
  INDEX `fk_Area_CartesianPoint` (`centerCartesianPointId` ASC) ,
  INDEX `fk_Area_GroundPlan` (`areaPlanId` ASC) ,
  CONSTRAINT `fk_AreaPosition_AreaType`
    FOREIGN KEY (`areaTypeId` )
    REFERENCES `tester`.`areatype` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_AreaPosition_GpsPosition`
    FOREIGN KEY (`centerGpsPointId` )
    REFERENCES `tester`.`gpspoint` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Area_Address`
    FOREIGN KEY (`addressId` )
    REFERENCES `tester`.`address` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Area_CartesianPoint`
    FOREIGN KEY (`centerCartesianPointId` )
    REFERENCES `tester`.`cartesianpoint` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Area_GroundPlan`
    FOREIGN KEY (`areaPlanId` )
    REFERENCES `tester`.`areaplan` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`frequencyband`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`frequencyband` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `value` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`networkprotocol`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`networkprotocol` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`network`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`network` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `areaId` BIGINT(20) NOT NULL ,
  `protocolId` SMALLINT(6) NOT NULL ,
  `panId` VARCHAR(255) NOT NULL ,
  `extPanId` VARCHAR(16) NOT NULL ,
  `channelMask` TINYINT(4) NOT NULL ,
  `frequencyBandId` SMALLINT(6) NOT NULL ,
  `active` TINYINT(4) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  `name` VARCHAR(255) NOT NULL ,
  `xmppIPaddress` VARCHAR(16) NOT NULL ,
  `xmppDomainName` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  UNIQUE INDEX `protocolId` (`protocolId` ASC) ,
  INDEX `fk_Network_Area` (`areaId` ASC) ,
  INDEX `fk_Network_FrequencyBand` (`frequencyBandId` ASC) ,
  CONSTRAINT `fk_Network_Area`
    FOREIGN KEY (`areaId` )
    REFERENCES `tester`.`area` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Network_FrequencyBand`
    FOREIGN KEY (`frequencyBandId` )
    REFERENCES `tester`.`frequencyband` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Network_Protocols`
    FOREIGN KEY (`protocolId` )
    REFERENCES `tester`.`networkprotocol` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`manufacturer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`manufacturer` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`hardwaremcu`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`hardwaremcu` (
  `id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT ,
  `manufacturerId` SMALLINT(6) NOT NULL ,
  `chipName` VARCHAR(20) NOT NULL ,
  `flashSize` INT(11) NOT NULL ,
  `ramSize` INT(11) NOT NULL ,
  `eepromSize` INT(11) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_HardwareMcu_Manufacturer` (`manufacturerId` ASC) ,
  CONSTRAINT `fk_HardwareMcu_Manufacturer`
    FOREIGN KEY (`manufacturerId` )
    REFERENCES `tester`.`manufacturer` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`hardwarerftransceiver`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`hardwarerftransceiver` (
  `id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT ,
  `manufacturer` SMALLINT(6) NOT NULL ,
  `chipName` VARCHAR(20) NOT NULL ,
  `rxSensitivity` SMALLINT(6) NOT NULL ,
  `minTxPower` SMALLINT(6) NOT NULL ,
  `maxTxPower` SMALLINT(6) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_HardwareRfTransceiver_Manufacturer` (`manufacturer` ASC) ,
  CONSTRAINT `fk_HardwareRfTransceiver_Manufacturer`
    FOREIGN KEY (`manufacturer` )
    REFERENCES `tester`.`manufacturer` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`sensorboard`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`sensorboard` (
  `id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT ,
  `manufacturerId` SMALLINT(6) NOT NULL ,
  `name` VARCHAR(20) NOT NULL ,
  `sensorList` MEDIUMINT(9) NOT NULL ,
  `version` FLOAT NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_SensorBoard_Manufacturer` (`manufacturerId` ASC) ,
  CONSTRAINT `fk_SensorBoard_Manufacturer`
    FOREIGN KEY (`manufacturerId` )
    REFERENCES `tester`.`manufacturer` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodetype`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodetype` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `hardwareRfTransceiverId` MEDIUMINT(9) NOT NULL ,
  `hardwareMcuId` MEDIUMINT(9) NOT NULL ,
  `sensorBoardId` MEDIUMINT(9) NOT NULL ,
  `manufacturerId` SMALLINT(6) NOT NULL ,
  `fullFuncionality` TINYINT(4) NOT NULL ,
  `name` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_NodeType_HardwareMcu` (`hardwareMcuId` ASC) ,
  INDEX `fk_NodeType_HardwareRfTransceiver` (`hardwareRfTransceiverId` ASC) ,
  INDEX `fk_NodeType_Manufacturer` (`manufacturerId` ASC) ,
  INDEX `fk_NodeType_SensorBoard` (`sensorBoardId` ASC) ,
  CONSTRAINT `fk_NodeType_HardwareMcu`
    FOREIGN KEY (`hardwareMcuId` )
    REFERENCES `tester`.`hardwaremcu` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NodeType_HardwareRfTransceiver`
    FOREIGN KEY (`hardwareRfTransceiverId` )
    REFERENCES `tester`.`hardwarerftransceiver` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NodeType_Manufacturer`
    FOREIGN KEY (`manufacturerId` )
    REFERENCES `tester`.`manufacturer` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NodeType_SensorBoard`
    FOREIGN KEY (`sensorBoardId` )
    REFERENCES `tester`.`sensorboard` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`noderole`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`noderole` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `networkProtocolId` INT(11) NOT NULL ,
  `name` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodesecurity`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodesecurity` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `protocolId` SMALLINT(6) NOT NULL ,
  `active` TINYINT(4) NOT NULL ,
  `level` INT(11) NOT NULL ,
  `sharedKey` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_NodeSecurity_NetworkProtocol` (`protocolId` ASC) ,
  CONSTRAINT `fk_NodeSecurity_NetworkProtocol`
    FOREIGN KEY (`protocolId` )
    REFERENCES `tester`.`networkprotocol` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodestate`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodestate` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`energysource`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`energysource` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  `voltageLevelMax` TINYINT(4) NOT NULL ,
  `voltageLevelMin` TINYINT(4) NOT NULL ,
  `capacity` INT(11) NOT NULL ,
  `cycles` INT(11) NOT NULL ,
  `maxOuputPower` INT(11) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`node`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`node` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `networkId` BIGINT(20) NOT NULL ,
  `nodeStateId` SMALLINT(6) NOT NULL ,
  `locationId` BIGINT(20) NOT NULL ,
  `nodeTypeId` BIGINT(20) NOT NULL ,
  `energySourceId` SMALLINT(6) NULL DEFAULT NULL ,
  `nodeRoleId` BIGINT(20) NOT NULL ,
  `currentFirmwareId` BIGINT(20) NOT NULL ,
  `nodeSecurityId` BIGINT(20) NOT NULL ,
  `macAddress` VARCHAR(255) NOT NULL ,
  `logicalAddress` VARCHAR(255) NOT NULL ,
  `groupId` BIGINT(20) NOT NULL ,
  `txPower` SMALLINT(6) NOT NULL ,
  `mobile` TINYINT(4) NOT NULL ,
  `remotelyUpgradable` TINYINT(4) NOT NULL ,
  `active` TINYINT(4) NOT NULL ,
  `canSleep` TINYINT(4) NOT NULL ,
  `sleepPeriod` INT(11) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_Nodes_Firmware` (`currentFirmwareId` ASC) ,
  INDEX `fk_Nodes_Location` (`locationId` ASC) ,
  INDEX `fk_Nodes_Network` (`networkId` ASC) ,
  INDEX `fk_Nodes_NodeHardware` (`nodeTypeId` ASC) ,
  INDEX `fk_Nodes_NodeRole` (`nodeRoleId` ASC) ,
  INDEX `fk_Nodes_NodeSecurity` (`nodeSecurityId` ASC) ,
  INDEX `fk_Nodes_NodeState` (`nodeStateId` ASC) ,
  INDEX `fk_Node_EnergySource` (`energySourceId` ASC) ,
  CONSTRAINT `fk_Nodes_Firmware`
    FOREIGN KEY (`currentFirmwareId` )
    REFERENCES `tester`.`nodefirmware` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nodes_Location`
    FOREIGN KEY (`locationId` )
    REFERENCES `tester`.`location` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nodes_Network`
    FOREIGN KEY (`networkId` )
    REFERENCES `tester`.`network` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nodes_NodeHardware`
    FOREIGN KEY (`nodeTypeId` )
    REFERENCES `tester`.`nodetype` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nodes_NodeRole`
    FOREIGN KEY (`nodeRoleId` )
    REFERENCES `tester`.`noderole` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nodes_NodeSecurity`
    FOREIGN KEY (`nodeSecurityId` )
    REFERENCES `tester`.`nodesecurity` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Nodes_NodeState`
    FOREIGN KEY (`nodeStateId` )
    REFERENCES `tester`.`nodestate` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Node_EnergySource`
    FOREIGN KEY (`energySourceId` )
    REFERENCES `tester`.`energysource` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`applicationlist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`applicationlist` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `applicationId` BIGINT(20) NOT NULL ,
  `active` TINYINT(4) NOT NULL ,
  `restAvailable` TINYINT(4) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_ApplicationList_Application` (`applicationId` ASC) ,
  INDEX `fk_ApplicationList_Nodes` (`nodeId` ASC) ,
  CONSTRAINT `fk_ApplicationList_Application`
    FOREIGN KEY (`applicationId` )
    REFERENCES `tester`.`application` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ApplicationList_Nodes`
    FOREIGN KEY (`nodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`areaplanlayer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`areaplanlayer` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `areaPlanId` BIGINT(20) NOT NULL ,
  `name` VARCHAR(255) NOT NULL ,
  `zLevel` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_GroundPlanLayer_GroundPlan` (`areaPlanId` ASC) ,
  CONSTRAINT `fk_GroundPlanLayer_GroundPlan`
    FOREIGN KEY (`areaPlanId` )
    REFERENCES `tester`.`areaplan` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`areaplanlayerobject`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`areaplanlayerobject` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `areaPlanLayerId` BIGINT(20) NOT NULL ,
  `name` VARCHAR(255) NOT NULL ,
  `number` INT(11) NOT NULL ,
  `areaTypeId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_GroundPlanObject_AreaType` (`areaTypeId` ASC) ,
  INDEX `fk_GroundPlanObject_GroundPlanLayer` (`areaPlanLayerId` ASC) ,
  CONSTRAINT `fk_GroundPlanObject_AreaType`
    FOREIGN KEY (`areaTypeId` )
    REFERENCES `tester`.`areatype` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_GroundPlanObject_GroundPlanLayer`
    FOREIGN KEY (`areaPlanLayerId` )
    REFERENCES `tester`.`areaplanlayer` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`areashape`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`areashape` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`areashapecoordinates`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`areashapecoordinates` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `areaShapeId` BIGINT(20) NOT NULL ,
  `cartesianPointId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_AreaShapeCoordinates_AreaShape` (`areaShapeId` ASC) ,
  INDEX `fk_AreaShapeCoordinates_CartesianPoint` (`cartesianPointId` ASC) ,
  CONSTRAINT `fk_AreaShapeCoordinates_AreaShape`
    FOREIGN KEY (`areaShapeId` )
    REFERENCES `tester`.`areashape` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_AreaShapeCoordinates_CartesianPoint`
    FOREIGN KEY (`cartesianPointId` )
    REFERENCES `tester`.`cartesianpoint` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`areasize`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`areasize` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `sizeX` INT(11) NOT NULL ,
  `sizeY` INT(11) NOT NULL ,
  `sizeZ` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`building`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`building` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`cartesianposition`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`cartesianposition` (
  `id` BIGINT(20) NOT NULL ,
  `x` INT(11) NOT NULL ,
  `y` INT(11) NOT NULL ,
  `z` INT(11) NOT NULL ,
  `certainty` TINYINT(4) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`contoh`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`contoh` (
  `nama` VARCHAR(30) NOT NULL ,
  `alamat` VARCHAR(100) NULL DEFAULT NULL ,
  PRIMARY KEY (`nama`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`units`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`units` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  `mark` VARCHAR(5) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`phenomenon`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`phenomenon` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `unitId` SMALLINT(6) NOT NULL ,
  `name` VARCHAR(50) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_Phenomena_Units` (`unitId` ASC) ,
  CONSTRAINT `fk_Phenomena_Units`
    FOREIGN KEY (`unitId` )
    REFERENCES `tester`.`units` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`datahistory`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`datahistory` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `phenomenonId` SMALLINT(6) NOT NULL ,
  `value1` FLOAT NOT NULL ,
  `value2` FLOAT NOT NULL ,
  `value3` FLOAT NOT NULL ,
  `data` VARCHAR(255) NOT NULL ,
  `timestampValue` DATETIME NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_DataHistory_Nodes` (`nodeId` ASC) ,
  INDEX `fk_DataHistory_Phenomena` (`phenomenonId` ASC) ,
  CONSTRAINT `fk_DataHistory_Nodes`
    FOREIGN KEY (`nodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_DataHistory_Phenomena`
    FOREIGN KEY (`phenomenonId` )
    REFERENCES `tester`.`phenomenon` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 205
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`energyhistory`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`energyhistory` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `timestampValue` DATETIME NOT NULL ,
  `value` SMALLINT(6) NULL DEFAULT NULL ,
  `voltageLevel` SMALLINT(6) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_EnergyHistory_Nodes` (`nodeId` ASC) ,
  CONSTRAINT `fk_EnergyHistory_Nodes`
    FOREIGN KEY (`nodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 10
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`networkhistorytype`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`networkhistorytype` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`networkhistory`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`networkhistory` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `networkId` BIGINT(20) NOT NULL ,
  `networkHistoryTypeId` BIGINT(20) NOT NULL ,
  `timestampValue` DATETIME NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_NetworkHistory_Network` (`networkId` ASC) ,
  INDEX `fk_NetworkHistory_NetworkHistoryType` (`networkHistoryTypeId` ASC) ,
  INDEX `fk_NetworkHistory_Nodes` (`nodeId` ASC) ,
  CONSTRAINT `fk_NetworkHistory_Network`
    FOREIGN KEY (`networkId` )
    REFERENCES `tester`.`network` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NetworkHistory_NetworkHistoryType`
    FOREIGN KEY (`networkHistoryTypeId` )
    REFERENCES `tester`.`networkhistorytype` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NetworkHistory_Nodes`
    FOREIGN KEY (`nodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`locationhistory`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`locationhistory` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `locationId` BIGINT(20) NOT NULL ,
  `timestampValue` DATETIME NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_PositionHistory_Location` (`locationId` ASC) ,
  INDEX `fk_PositionHistory_Nodes` (`nodeId` ASC) ,
  CONSTRAINT `fk_PositionHistory_Location`
    FOREIGN KEY (`locationId` )
    REFERENCES `tester`.`location` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PositionHistory_Nodes`
    FOREIGN KEY (`nodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`currentnetworkstate`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`currentnetworkstate` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `positionHistoryId` BIGINT(20) NOT NULL ,
  `dataHistoryId` BIGINT(20) NOT NULL ,
  `energyHistoryId` BIGINT(20) NOT NULL ,
  `networkHistoryId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_CurrentNetworkState_DataHistory` (`dataHistoryId` ASC) ,
  INDEX `fk_CurrentNetworkState_EnergyHistory` (`energyHistoryId` ASC) ,
  INDEX `fk_CurrentNetworkState_NetworkHistory` (`networkHistoryId` ASC) ,
  INDEX `fk_CurrentNetworkState_PositionHistory` (`positionHistoryId` ASC) ,
  CONSTRAINT `fk_CurrentNetworkState_DataHistory`
    FOREIGN KEY (`dataHistoryId` )
    REFERENCES `tester`.`datahistory` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CurrentNetworkState_EnergyHistory`
    FOREIGN KEY (`energyHistoryId` )
    REFERENCES `tester`.`energyhistory` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CurrentNetworkState_NetworkHistory`
    FOREIGN KEY (`networkHistoryId` )
    REFERENCES `tester`.`networkhistory` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_CurrentNetworkState_PositionHistory`
    FOREIGN KEY (`positionHistoryId` )
    REFERENCES `tester`.`locationhistory` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`gateway`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`gateway` (
  `id` BIGINT(20) NOT NULL ,
  `ipaddress` VARCHAR(16) NOT NULL ,
  `hostname` VARCHAR(255) NOT NULL ,
  `name` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`gatewaynetwork`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`gatewaynetwork` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `networkId` BIGINT(20) NOT NULL ,
  `gatewayId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_GatewayNetwork_Gateway` (`gatewayId` ASC) ,
  INDEX `fk_GatewayNetwork_Network` (`networkId` ASC) ,
  CONSTRAINT `fk_GatewayNetwork_Gateway`
    FOREIGN KEY (`gatewayId` )
    REFERENCES `tester`.`gateway` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_GatewayNetwork_Network`
    FOREIGN KEY (`networkId` )
    REFERENCES `tester`.`network` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`linkdirection`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`linkdirection` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodefirmwarechunk`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodefirmwarechunk` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `number` BIGINT(20) NOT NULL ,
  `data` BLOB NOT NULL ,
  `checksum` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodefirmwarechunklist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodefirmwarechunklist` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeFirmwareId` BIGINT(20) NOT NULL ,
  `nodeFirmwareChunkId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_NodeFirmwareChunkList_NodeFirmware` (`nodeFirmwareId` ASC) ,
  INDEX `fk_NodeFirmwareChunkList_NodeFirmwareChunk` (`nodeFirmwareChunkId` ASC) ,
  CONSTRAINT `fk_NodeFirmwareChunkList_NodeFirmware`
    FOREIGN KEY (`nodeFirmwareId` )
    REFERENCES `tester`.`nodefirmware` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NodeFirmwareChunkList_NodeFirmwareChunk`
    FOREIGN KEY (`nodeFirmwareChunkId` )
    REFERENCES `tester`.`nodefirmwarechunk` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodelink`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodelink` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `remoteNodeId` BIGINT(20) NOT NULL ,
  `forwardDirection` TINYINT(4) NOT NULL ,
  `txPower` SMALLINT(6) NOT NULL ,
  `RSSI` SMALLINT(6) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_NodeLink_Nodes` (`remoteNodeId` ASC) ,
  CONSTRAINT `fk_NodeLink_Nodes`
    FOREIGN KEY (`remoteNodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`nodelinklist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`nodelinklist` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `nodeLinkId` BIGINT(20) NOT NULL ,
  `active` TINYINT(4) NOT NULL ,
  `timestampValue` DATETIME NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_NodeLinksList_NodeLink` (`nodeLinkId` ASC) ,
  INDEX `fk_NodeLinksList_Nodes` (`nodeId` ASC) ,
  CONSTRAINT `fk_NodeLinksList_NodeLink`
    FOREIGN KEY (`nodeLinkId` )
    REFERENCES `tester`.`nodelink` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_NodeLinksList_Nodes`
    FOREIGN KEY (`nodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`protocolzigbee`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`protocolzigbee` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `deviceType` SMALLINT(6) NOT NULL ,
  `panId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`protocolzigbeedevicetypes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`protocolzigbeedevicetypes` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(10) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`protocolzigbeehierarchy`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`protocolzigbeehierarchy` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `parentNodeId` BIGINT(20) NOT NULL ,
  `childNodeId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`sensor`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`sensor` (
  `id` SMALLINT(6) NOT NULL AUTO_INCREMENT ,
  `manufacturerId` SMALLINT(6) NOT NULL ,
  `chipName` VARCHAR(50) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_Sensors_Manufacturer` (`manufacturerId` ASC) ,
  CONSTRAINT `fk_Sensors_Manufacturer`
    FOREIGN KEY (`manufacturerId` )
    REFERENCES `tester`.`manufacturer` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`sensorlist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`sensorlist` (
  `id` MEDIUMINT(9) NOT NULL AUTO_INCREMENT ,
  `sensorBoardId` MEDIUMINT(9) NOT NULL ,
  `sensorId` MEDIUMINT(9) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_SensorList_SensorBoard` (`sensorBoardId` ASC) ,
  CONSTRAINT `fk_SensorList_SensorBoard`
    FOREIGN KEY (`sensorBoardId` )
    REFERENCES `tester`.`sensorboard` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`sensorphenomenonlist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`sensorphenomenonlist` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `phenomenonId` SMALLINT(6) NOT NULL ,
  `sensorId` SMALLINT(6) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_SensorPhenomenaList_Phenomena` (`phenomenonId` ASC) ,
  INDEX `fk_SensorPhenomenaList_Sensors` (`sensorId` ASC) ,
  CONSTRAINT `fk_SensorPhenomenaList_Phenomena`
    FOREIGN KEY (`phenomenonId` )
    REFERENCES `tester`.`phenomenon` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_SensorPhenomenaList_Sensors`
    FOREIGN KEY (`sensorId` )
    REFERENCES `tester`.`sensor` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`sensorphenomenontolerance`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`sensorphenomenontolerance` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `sensorPhenomenonListId` BIGINT(20) NOT NULL ,
  `dependentPhenomenonId` SMALLINT(6) NOT NULL ,
  `rangeFrom` FLOAT NOT NULL ,
  `rangeTo` FLOAT NOT NULL ,
  `error` FLOAT NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_SensorPhenomenaTolerance_Phenomena` (`dependentPhenomenonId` ASC) ,
  INDEX `fk_SensorPhenomenaTolerance_SensorPhenomenaList` (`sensorPhenomenonListId` ASC) ,
  CONSTRAINT `fk_SensorPhenomenaTolerance_Phenomena`
    FOREIGN KEY (`dependentPhenomenonId` )
    REFERENCES `tester`.`phenomenon` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_SensorPhenomenaTolerance_SensorPhenomenaList`
    FOREIGN KEY (`sensorPhenomenonListId` )
    REFERENCES `tester`.`sensorphenomenonlist` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`subarea`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`subarea` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `gpsPosition` BIGINT(20) NOT NULL ,
  `cartesianPositionId` BIGINT(20) NOT NULL ,
  `name` VARCHAR(50) NOT NULL ,
  `number` SMALLINT(6) NOT NULL ,
  `areaTypeId` BIGINT(20) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  `sizeX` INT(11) NOT NULL ,
  `sizeY` INT(11) NOT NULL ,
  `sizeZ` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_Room_GpsPosition` (`gpsPosition` ASC) ,
  INDEX `fk_SubArea_AreaType` (`areaTypeId` ASC) ,
  INDEX `fk_SubArea_CartesianPosition` (`cartesianPositionId` ASC) ,
  CONSTRAINT `fk_Room_GpsPosition`
    FOREIGN KEY (`gpsPosition` )
    REFERENCES `tester`.`gpspoint` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_SubArea_AreaType`
    FOREIGN KEY (`areaTypeId` )
    REFERENCES `tester`.`areatype` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_SubArea_CartesianPosition`
    FOREIGN KEY (`cartesianPositionId` )
    REFERENCES `tester`.`cartesianposition` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`subarealist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`subarealist` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `areaId` BIGINT(20) NOT NULL ,
  `subAreaId` BIGINT(20) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`trustednodelist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`trustednodelist` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `nodeId` BIGINT(20) NOT NULL ,
  `networkId` BIGINT(20) NOT NULL ,
  `level` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_TrustedNodesList_Network` (`networkId` ASC) ,
  INDEX `fk_TrustedNodesList_Nodes` (`nodeId` ASC) ,
  CONSTRAINT `fk_TrustedNodesList_Network`
    FOREIGN KEY (`networkId` )
    REFERENCES `tester`.`network` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_TrustedNodesList_Nodes`
    FOREIGN KEY (`nodeId` )
    REFERENCES `tester`.`node` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`useraction`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`useraction` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`userauthority`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`userauthority` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `authority` VARCHAR(50) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`userdata`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`userdata` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `authorityId` BIGINT(20) NOT NULL ,
  `username` VARCHAR(255) NOT NULL ,
  `password` VARCHAR(255) NOT NULL ,
  `name` VARCHAR(255) NOT NULL ,
  `surname` VARCHAR(255) NOT NULL ,
  `email` VARCHAR(255) NOT NULL ,
  `phone` VARCHAR(255) NOT NULL ,
  `addressId` BIGINT(20) NOT NULL ,
  `enabled` TINYINT(4) NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_Users_Address` (`addressId` ASC) ,
  INDEX `fk_Users_UserAuthorities` (`authorityId` ASC) ,
  CONSTRAINT `fk_Users_Address`
    FOREIGN KEY (`addressId` )
    REFERENCES `tester`.`address` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Users_UserAuthorities`
    FOREIGN KEY (`authorityId` )
    REFERENCES `tester`.`userauthority` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `tester`.`useraccesslog`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `tester`.`useraccesslog` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `usernameId` BIGINT(20) NOT NULL ,
  `userActionId` BIGINT(20) NOT NULL ,
  `ipAddress` VARCHAR(255) NOT NULL ,
  `timestampValue` DATETIME NOT NULL ,
  `note` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_UserAccessLog_UserAction` (`userActionId` ASC) ,
  INDEX `fk_UserLog_Users` (`usernameId` ASC) ,
  CONSTRAINT `fk_UserAccessLog_UserAction`
    FOREIGN KEY (`userActionId` )
    REFERENCES `tester`.`useraction` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_UserLog_Users`
    FOREIGN KEY (`usernameId` )
    REFERENCES `tester`.`userdata` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;