wQiYc0B4

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema dbggdevice
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema dbggdevice
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `dbggdevice` DEFAULT CHARACTER SET utf8 ;
15USE `dbggdevice` ;
16
17-- -----------------------------------------------------
18-- Table `dbggdevice`.`tbcontrollermodbustcp`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `dbggdevice`.`tbcontrollermodbustcp` (
21  `modbusTcpID` INT(11) NOT NULL,
22  `ip` VARCHAR(20) NOT NULL DEFAULT '127.0.0.1',
23  `portDevice` INT(11) NOT NULL DEFAULT '502',
24  `timeout` INT(11) NOT NULL DEFAULT 60000,
25  `slaveID` INT(11) NOT NULL DEFAULT 1,
26  `createdAt` TIMESTAMP NOT NULL,
27  `updateAt` TIMESTAMP NULL,
28  UNIQUE INDEX `sqlite_autoindex_tbControllerModbusTCP_1` (`modbusTcpID` ASC))
29ENGINE = InnoDB
30DEFAULT CHARACTER SET = utf8;
31
32
33-- -----------------------------------------------------
34-- Table `dbggdevice`.`tblistcontrollers`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `dbggdevice`.`tblistcontrollers` (
37  `controllerListID` INT(11) NOT NULL,
38  `controllerName` VARCHAR(30) NOT NULL,
39  `protokol` VARCHAR(30) NOT NULL DEFAULT 'MODBUS_TCP',
40  PRIMARY KEY (`controllerListID`),
41  UNIQUE INDEX `protokol_UNIQUE` (`protokol` ASC),
42  UNIQUE INDEX `controllerName_UNIQUE` (`controllerName` ASC))
43ENGINE = InnoDB
44DEFAULT CHARACTER SET = utf8;
45
46
47-- -----------------------------------------------------
48-- Table `dbggdevice`.`tbcontrollersiemenstcp`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `dbggdevice`.`tbcontrollersiemenstcp` (
51  `siemensTcpID` INT(11) NOT NULL,
52  `ip` VARCHAR(20) NOT NULL DEFAULT '127.0.0.1',
53  `portDevice` INT(11) NOT NULL DEFAULT '102',
54  `rack` INT(11) NOT NULL DEFAULT '0',
55  `slot` INT(11) NOT NULL DEFAULT '1',
56  `timeout` INT(11) NOT NULL DEFAULT '60000',
57  `createdAt` TIMESTAMP NULL,
58  `updateAt` TIMESTAMP NULL,
59  PRIMARY KEY (`siemensTcpID`))
60ENGINE = InnoDB
61DEFAULT CHARACTER SET = utf8;
62
63
64-- -----------------------------------------------------
65-- Table `dbggdevice`.`pivotcontroller`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `dbggdevice`.`pivotcontroller` (
68  `controllerListID` INT(11) NOT NULL,
69  `modbusTcpID` INT(11) NULL DEFAULT NULL,
70  `siemensTcpID` INT(11) NULL DEFAULT NULL,
71  INDEX `Idx_pivotController_modbusTcpID` (`modbusTcpID` ASC),
72  INDEX `Idx_pivotController_controllerListID` (`controllerListID` ASC),
73  CONSTRAINT `pivotcontroller_ibfk_1`
74    FOREIGN KEY (`modbusTcpID`)
75    REFERENCES `dbggdevice`.`tbcontrollermodbustcp` (`modbusTcpID`),
76  CONSTRAINT `pivotcontroller_ibfk_2`
77    FOREIGN KEY (`controllerListID`)
78    REFERENCES `dbggdevice`.`tblistcontrollers` (`controllerListID`),
79  CONSTRAINT `pivotcontroller_ibfk_3`
80    FOREIGN KEY (`siemensTcpID`)
81    REFERENCES `dbggdevice`.`tbcontrollersiemenstcp` (`siemensTcpID`))
82ENGINE = InnoDB
83DEFAULT CHARACTER SET = utf8;
84
85
86-- -----------------------------------------------------
87-- Table `dbggdevice`.`tbtagsmodbus`
88-- -----------------------------------------------------
89CREATE TABLE IF NOT EXISTS `dbggdevice`.`tbtagsmodbus` (
90  `tagModbusID` INT(11) NOT NULL,
91  `address` INT NOT NULL,
92  `typeMemory` VARCHAR(10) NOT NULL DEFAULT 'HOLDING',
93  `access` VARCHAR(10) NOT NULL DEFAULT 'READ',
94  `value` FLOAT NULL DEFAULT NULL,
95  `typeData` VARCHAR(10) NOT NULL DEFAULT 'INT',
96  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
97  `updateAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
98  `endian` VARCHAR(2) NOT NULL DEFAULT 'LE',
99  `modbusTcpID` INT(11) NOT NULL,
100  PRIMARY KEY (`tagModbusID`),
101  INDEX `Idx_tbTagsModbus_modbusTcpID` (`modbusTcpID` ASC),
102  CONSTRAINT `tbtagsmodbus_ibfk_1`
103    FOREIGN KEY (`modbusTcpID`)
104    REFERENCES `dbggdevice`.`tbcontrollermodbustcp` (`modbusTcpID`))
105ENGINE = InnoDB
106DEFAULT CHARACTER SET = utf8;
107
108
109-- -----------------------------------------------------
110-- Table `dbggdevice`.`tbtagssiemens`
111-- -----------------------------------------------------
112CREATE TABLE IF NOT EXISTS `dbggdevice`.`tbtagssiemens` (
113  `tagSiemensID` INT(11) NOT NULL,
114  `address` INT NOT NULL,
115  `typeMemory` VARCHAR(10) NOT NULL DEFAULT 'HOLDING',
116  `access` VARCHAR(10) NOT NULL DEFAULT 'READ',
117  `value` FLOAT NULL DEFAULT NULL,
118  `typeData` VARCHAR(10) NOT NULL DEFAULT 'INT',
119  `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
120  `updateAt` TIMESTAMP NULL,
121  `endian` VARCHAR(2) NOT NULL DEFAULT 'LE',
122  `siemensTcpID` INT(11) NOT NULL,
123  PRIMARY KEY (`tagSiemensID`),
124  INDEX `FR_siemensTcpID_to_siemensTcpID_idx` (`siemensTcpID` ASC),
125  CONSTRAINT `FR_siemensTcpID_to_siemensTcpID`
126    FOREIGN KEY (`siemensTcpID`)
127    REFERENCES `dbggdevice`.`tbcontrollersiemenstcp` (`siemensTcpID`)
128    ON DELETE NO ACTION
129    ON UPDATE NO ACTION)
130ENGINE = InnoDB
131DEFAULT CHARACTER SET = utf8;
132
133
134SET SQL_MODE=@OLD_SQL_MODE;
135SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
136SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;