rLGN0g7D

1
2CREATE TABLE IF NOT EXISTS tbListControllers ( 
3	controllerListID     integer NOT NULL  ,
4	controllerName       varchar(30) NOT NULL  UNIQUE,
5	protokol             varchar(30)  DEFAULT 'MODBUS_TCP' ,
6	CONSTRAINT sqlite_autoindex_tbListControllers_1 UNIQUE ( controllerListID ) 
7 );
8 CREATE TABLE IF NOT EXISTS tbControllerModbusTCP ( 
9	modbusTcpID          integer NOT NULL  ,
10	ip                   varchar(20) NOT NULL DEFAULT '127.0.0.1' ,
11	portDevice                 integer NOT NULL DEFAULT 502 ,
12	timeout              integer NOT NULL  ,
13	slaveID              integer NOT NULL  ,
14	CONSTRAINT sqlite_autoindex_tbControllerModbusTCP_1 UNIQUE ( modbusTcpID ) 
15 );
16
17CREATE TABLE IF NOT EXISTS  tbControllerSiemensTCP ( 
18	siemensTcpID         integer NOT NULL  ,
19	ip                   varchar(20) NOT NULL DEFAULT '127.0.0.1' ,
20	portDevice                 integer NOT NULL DEFAULT 102 ,
21	rack                 integer NOT NULL DEFAULT 0 ,
22	slot                 integer NOT NULL DEFAULT 1 ,
23	timeout              integer NOT NULL DEFAULT 60000 ,
24	CONSTRAINT Pk_tbControllerSiemensTCP_siemensTcpID PRIMARY KEY ( siemensTcpID )
25 );
26
27CREATE TABLE IF NOT EXISTS pivotController ( 
28	controllerListID     integer NOT NULL  ,
29	modbusTcpID          integer   ,
30	siemensTcpID         integer   ,
31	CONSTRAINT sqlite_autoindex_pivotController_1 UNIQUE ( siemensTcpID ) ,
32	FOREIGN KEY ( modbusTcpID ) REFERENCES tbControllerModbusTCP( modbusTcpID )  ,
33	FOREIGN KEY ( controllerListID ) REFERENCES tbListControllers( controllerListID )  ,
34	FOREIGN KEY ( siemensTcpID ) REFERENCES tbControllerSiemensTCP( siemensTcpID )  
35 );
36
37
38
39
40CREATE TABLE IF NOT EXISTS tbTagsModbus ( 
41	tagModbusID          integer NOT NULL  ,
42	address              varchar(100)   ,
43	typeMemory           varchar(10) NOT NULL DEFAULT 'HOLDING' ,
44	access               varchar(10) NOT NULL DEFAULT 'READ' ,
45	value                float(6)   ,
46	typeData             varchar(10) NOT NULL DEFAULT 'INT' ,
47	createdAt            timestamp  DEFAULT CURRENT_TIMESTAMP ,
48	updateAt             timestamp   ,
49	endian               varchar(2) NOT NULL DEFAULT 'LE' ,
50	modbusTcpID          integer   ,
51	CONSTRAINT Pk_tbTagsModbus_tagModbusID PRIMARY KEY ( tagModbusID ),
52	FOREIGN KEY ( modbusTcpID ) REFERENCES tbControllerModbusTCP( modbusTcpID )  
53 );
54
55CREATE INDEX Idx_tbTagsModbus_modbusTcpID ON tbTagsModbus ( modbusTcpID );
56
57
58CREATE INDEX Idx_pivotController_modbusTcpID ON pivotController ( modbusTcpID );
59
60CREATE INDEX Idx_pivotController_controllerListID ON pivotController ( controllerListID );