/******************************************************************************/ /*** Generated by IBExpert 2022.3.4.1 27.09.2023 18:06:43 ***/ /******************************************************************************/ SET SQL DIALECT 3; SET NAMES WIN1251; CREATE DATABASE 'myhost:raduga' USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 8192 DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251; /******************************************************************************/ /*** Domains ***/ /******************************************************************************/ CREATE DOMAIN JROBO_RADUGA AS INTEGER; /******************************************************************************/ /*** Generators ***/ /******************************************************************************/ CREATE SEQUENCE SQ_RD_CYCLE_ID START WITH 0 INCREMENT BY 1; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE RD_BUNKER ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(32) NOT NULL, PRODUCT_ID INTEGER, WEIGHT BIGINT DEFAULT 0, DELETED BOOLEAN ); CREATE TABLE RD_DOSER ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(32) NOT NULL, DELETED BOOLEAN ); CREATE TABLE RD_FEEDER ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, DOSER_ID INTEGER, FEEDER_NUM SMALLINT, BUNKER_ID INTEGER, DELETED BOOLEAN ); CREATE TABLE RD_LINE ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(32) NOT NULL, DESCR VARCHAR(128), HIDDEN BOOLEAN, DELETED BOOLEAN ); CREATE TABLE RD_LINE_BUNKER ( LINE_ID INTEGER, BUNKER_ID INTEGER ); CREATE TABLE RD_LINE_DOSER ( LINE_ID INTEGER, DOSER_ID INTEGER ); CREATE TABLE RD_LINE_PARAM ( LINE_ID INTEGER, PARAM_ID INTEGER ); CREATE TABLE RD_PARAM ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(32) NOT NULL, DESCR VARCHAR(128), VALTYPE CHAR(6) NOT NULL, DELETED BOOLEAN ); CREATE TABLE RD_PERM ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(64) NOT NULL, DESCR VARCHAR(128), DELETED BOOLEAN ); CREATE TABLE RD_PLACE ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(128) NOT NULL, DELETED BOOLEAN ); CREATE TABLE RD_PRODUCT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(128) NOT NULL, DELETED BOOLEAN ); CREATE TABLE RD_RECIPE ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(128) NOT NULL, DESCR VARCHAR(2048), PRODUCT_ID INTEGER, DT TIMESTAMP, DELETED BOOLEAN ); CREATE TABLE RD_RECIPE_PARAM ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, RECIPE_ID INTEGER, PARAM_ID INTEGER, VAL VARCHAR(32) ); CREATE TABLE RD_RECIPE_PRODUCT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, RECIPE_ID INTEGER, PRODUCT_ID INTEGER, WEIGHT BIGINT ); CREATE TABLE RD_REPO ( NAME VARCHAR(128) NOT NULL, VAL VARCHAR(128) NOT NULL ); CREATE TABLE RD_ROLE ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(64) NOT NULL, DELETED BOOLEAN ); CREATE TABLE RD_ROLE_PERM ( ROLE_ID INTEGER, PERM_ID INTEGER ); CREATE TABLE RD_SHIFT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, USER_ID INTEGER, PLACE_ID INTEGER, DT_OPEN TIMESTAMP, DT_CLOSE TIMESTAMP ); CREATE TABLE RD_SHIFT_INPUT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, SHIFT_ID INTEGER, PRODUCT_ID INTEGER, BUNKER_ID INTEGER, DT TIMESTAMP, WEIGHT BIGINT ); CREATE TABLE RD_SHIFT_OUTPUT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, SHIFT_ID INTEGER, PRODUCT_ID INTEGER, BUNKER_ID INTEGER, DT TIMESTAMP, WEIGHT BIGINT ); CREATE TABLE RD_TASK ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, LINE_ID INTEGER, RECIPE_ID INTEGER, PRODUCT_ID INTEGER, BUNKER_ID INTEGER, USER_ID INTEGER, SHIFT_ID INTEGER, DT_CREATE TIMESTAMP, DT_EXEC TIMESTAMP, DT_FINISH TIMESTAMP, SET_WEIGHT_TOT BIGINT, STATUS INTEGER ); CREATE TABLE RD_TASK_COMMENT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, TASK_ID INTEGER, USER_ID INTEGER, TEXT VARCHAR(2048) ); CREATE TABLE RD_TASK_CTL ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, TASK_ID INTEGER, CYCLE_ID INTEGER, DT TIMESTAMP, WEIGHT BIGINT ); CREATE TABLE RD_TASK_EXEC ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, TASK_ID INTEGER, FEEDER_ID INTEGER, CYCLE_ID INTEGER, DT TIMESTAMP, WEIGHT BIGINT, SUM_WEIGHT_BEG BIGINT, SUM_WEIGHT_END BIGINT ); CREATE TABLE RD_TASK_PARAM ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, TASK_ID INTEGER, PARAM_ID INTEGER, VAL VARCHAR(32) ); CREATE TABLE RD_TASK_PRODUCT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, TASK_ID INTEGER, PRODUCT_ID INTEGER, BUNKER_ID INTEGER, WEIGHT BIGINT ); CREATE TABLE RD_USER ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY, NAME VARCHAR(64) NOT NULL, PSW VARCHAR(64), SUPER BOOLEAN, DELETED BOOLEAN ); CREATE TABLE RD_USER_ROLE ( USER_ID INTEGER, ROLE_ID INTEGER ); /******************************************************************************/ /*** Check constraints ***/ /******************************************************************************/ ALTER TABLE RD_PARAM ADD CONSTRAINT CH_RD_PARAM_VALTYPE CHECK (VALTYPE in ('BOOL','INT','LONG','DOUBLE','STRING')); /******************************************************************************/ /*** Unique constraints ***/ /******************************************************************************/ ALTER TABLE RD_BUNKER ADD CONSTRAINT UQ_RD_BUNKER_NAME UNIQUE (NAME); ALTER TABLE RD_DOSER ADD CONSTRAINT UQ_RD_DOSER_NAME UNIQUE (NAME); ALTER TABLE RD_LINE ADD CONSTRAINT UQ_RD_LINE_NAME UNIQUE (NAME); ALTER TABLE RD_PARAM ADD CONSTRAINT UQ_RD_PARAM_NAME UNIQUE (NAME); ALTER TABLE RD_PERM ADD CONSTRAINT UQ_RD_PERM_NAME UNIQUE (NAME); ALTER TABLE RD_PLACE ADD CONSTRAINT UQ_RD_PLACE_NAME UNIQUE (NAME); ALTER TABLE RD_PRODUCT ADD CONSTRAINT UQ_RD_PRODUCT_NAME UNIQUE (NAME); ALTER TABLE RD_RECIPE ADD CONSTRAINT UQ_RD_RECIPE_NAME UNIQUE (NAME); ALTER TABLE RD_ROLE ADD CONSTRAINT UQ_RD_ROLE_NAME UNIQUE (NAME); ALTER TABLE RD_USER ADD CONSTRAINT UQ_RD_USER_NAME UNIQUE (NAME); /******************************************************************************/ /*** Primary keys ***/ /******************************************************************************/ ALTER TABLE RD_BUNKER ADD CONSTRAINT PK_RD_BUNKER PRIMARY KEY (ID); ALTER TABLE RD_DOSER ADD CONSTRAINT PK_RD_DOSER PRIMARY KEY (ID); ALTER TABLE RD_FEEDER ADD CONSTRAINT PK_RD_FEEDER PRIMARY KEY (ID); ALTER TABLE RD_LINE ADD CONSTRAINT PK_RD_LINE PRIMARY KEY (ID); ALTER TABLE RD_PARAM ADD CONSTRAINT PK_RD_PARAM PRIMARY KEY (ID); ALTER TABLE RD_PERM ADD CONSTRAINT PK_RD_PERM PRIMARY KEY (ID); ALTER TABLE RD_PLACE ADD CONSTRAINT PK_RD_PLACE PRIMARY KEY (ID); ALTER TABLE RD_PRODUCT ADD CONSTRAINT PK_RD_PRODUCT PRIMARY KEY (ID); ALTER TABLE RD_RECIPE ADD CONSTRAINT PK_RD_RECIPE PRIMARY KEY (ID); ALTER TABLE RD_RECIPE_PARAM ADD CONSTRAINT PK_RD_RECIPE_PARAM PRIMARY KEY (ID); ALTER TABLE RD_RECIPE_PRODUCT ADD CONSTRAINT PK_RD_RECIPE_PRODUCT PRIMARY KEY (ID); ALTER TABLE RD_REPO ADD PRIMARY KEY (NAME); ALTER TABLE RD_ROLE ADD CONSTRAINT PK_RD_ROLE PRIMARY KEY (ID); ALTER TABLE RD_SHIFT ADD CONSTRAINT PK_RD_SHIFT PRIMARY KEY (ID); ALTER TABLE RD_SHIFT_INPUT ADD CONSTRAINT PK_RD_SHIFTINPUT PRIMARY KEY (ID); ALTER TABLE RD_SHIFT_OUTPUT ADD CONSTRAINT PK_RD_SHIFTOUTPUT PRIMARY KEY (ID); ALTER TABLE RD_TASK ADD CONSTRAINT PK_RD_TASK PRIMARY KEY (ID); ALTER TABLE RD_TASK_COMMENT ADD CONSTRAINT PK_RD_TASKCOMMENT PRIMARY KEY (ID); ALTER TABLE RD_TASK_CTL ADD CONSTRAINT PK_RD_TASKCTL PRIMARY KEY (ID); ALTER TABLE RD_TASK_EXEC ADD CONSTRAINT PK_RD_TASKEXEC PRIMARY KEY (ID); ALTER TABLE RD_TASK_PARAM ADD CONSTRAINT PK_RD_TASKPARAM PRIMARY KEY (ID); ALTER TABLE RD_TASK_PRODUCT ADD CONSTRAINT PK_RD_TASKPRODUCT PRIMARY KEY (ID); ALTER TABLE RD_USER ADD CONSTRAINT PK_RD_USER PRIMARY KEY (ID); /******************************************************************************/ /*** Foreign keys ***/ /******************************************************************************/ ALTER TABLE RD_BUNKER ADD CONSTRAINT FK_RD_BUNKER_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES RD_PRODUCT (ID) ON DELETE SET NULL ON UPDATE CASCADE; ALTER TABLE RD_FEEDER ADD CONSTRAINT FK_RD_DOSERBUNKER_BUNKER FOREIGN KEY (BUNKER_ID) REFERENCES RD_BUNKER (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_FEEDER ADD CONSTRAINT FK_RD_DOSERBUNKER_DOSER FOREIGN KEY (DOSER_ID) REFERENCES RD_DOSER (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_LINE_BUNKER ADD CONSTRAINT FK_RD_LINEBUNKER_BUNKER FOREIGN KEY (BUNKER_ID) REFERENCES RD_BUNKER (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_LINE_BUNKER ADD CONSTRAINT FK_RD_LINEBUNKER_LINE FOREIGN KEY (LINE_ID) REFERENCES RD_LINE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_LINE_DOSER ADD CONSTRAINT FK_RD_LINEDOSER_DOSER FOREIGN KEY (DOSER_ID) REFERENCES RD_DOSER (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_LINE_DOSER ADD CONSTRAINT FK_RD_LINEDOSER_LINE FOREIGN KEY (LINE_ID) REFERENCES RD_LINE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_LINE_PARAM ADD CONSTRAINT FK_RD_LINEPARAM_LINE FOREIGN KEY (LINE_ID) REFERENCES RD_LINE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_LINE_PARAM ADD CONSTRAINT FK_RD_LINEPARAM_PARAM FOREIGN KEY (PARAM_ID) REFERENCES RD_PARAM (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_RECIPE ADD CONSTRAINT FK_RD_RECIPE_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES RD_PRODUCT (ID); ALTER TABLE RD_RECIPE_PARAM ADD CONSTRAINT FK_RD_RECIPEPARAM_PARAM FOREIGN KEY (PARAM_ID) REFERENCES RD_PARAM (ID); ALTER TABLE RD_RECIPE_PARAM ADD CONSTRAINT FK_RD_RECIPEPARAM_RECIPE FOREIGN KEY (RECIPE_ID) REFERENCES RD_RECIPE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_RECIPE_PRODUCT ADD CONSTRAINT FK_RD_RECIPEPRODUCT_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES RD_PRODUCT (ID); ALTER TABLE RD_RECIPE_PRODUCT ADD CONSTRAINT FK_RD_RECIPEPRODUCT_RECIPE FOREIGN KEY (RECIPE_ID) REFERENCES RD_RECIPE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_ROLE_PERM ADD CONSTRAINT FK_RD_ROLEPERM_PERM FOREIGN KEY (PERM_ID) REFERENCES RD_PERM (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_ROLE_PERM ADD CONSTRAINT FK_RD_ROLEPERM_ROLE FOREIGN KEY (ROLE_ID) REFERENCES RD_USER (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_SHIFT ADD CONSTRAINT FK_RD_SHIFT_PLACE FOREIGN KEY (PLACE_ID) REFERENCES RD_PLACE (ID); ALTER TABLE RD_SHIFT ADD CONSTRAINT FK_RD_SHIFT_USER FOREIGN KEY (USER_ID) REFERENCES RD_USER (ID); ALTER TABLE RD_SHIFT_INPUT ADD CONSTRAINT FK_RD_SHIFTINPUT_BUNKER FOREIGN KEY (BUNKER_ID) REFERENCES RD_BUNKER (ID); ALTER TABLE RD_SHIFT_INPUT ADD CONSTRAINT FK_RD_SHIFTINPUT_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES RD_PRODUCT (ID); ALTER TABLE RD_SHIFT_INPUT ADD CONSTRAINT FK_RD_SHIFTINPUT_SHIFT FOREIGN KEY (SHIFT_ID) REFERENCES RD_SHIFT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_SHIFT_OUTPUT ADD CONSTRAINT FK_RD_SHIFTOUTPUT_BUNKER FOREIGN KEY (BUNKER_ID) REFERENCES RD_BUNKER (ID); ALTER TABLE RD_SHIFT_OUTPUT ADD CONSTRAINT FK_RD_SHIFTOUTPUT_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES RD_PRODUCT (ID); ALTER TABLE RD_SHIFT_OUTPUT ADD CONSTRAINT FK_RD_SHIFTOUTPUT_SHIFT FOREIGN KEY (SHIFT_ID) REFERENCES RD_SHIFT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_TASK ADD CONSTRAINT FK_RD_TASK_BUNKER FOREIGN KEY (BUNKER_ID) REFERENCES RD_BUNKER (ID); ALTER TABLE RD_TASK ADD CONSTRAINT FK_RD_TASK_LINE FOREIGN KEY (LINE_ID) REFERENCES RD_LINE (ID); ALTER TABLE RD_TASK ADD CONSTRAINT FK_RD_TASK_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES RD_PRODUCT (ID); ALTER TABLE RD_TASK ADD CONSTRAINT FK_RD_TASK_RECIPE FOREIGN KEY (RECIPE_ID) REFERENCES RD_RECIPE (ID); ALTER TABLE RD_TASK ADD CONSTRAINT FK_RD_TASK_SHIFT FOREIGN KEY (SHIFT_ID) REFERENCES RD_SHIFT (ID); ALTER TABLE RD_TASK ADD CONSTRAINT FK_RD_TASK_USER FOREIGN KEY (USER_ID) REFERENCES RD_USER (ID); ALTER TABLE RD_TASK_COMMENT ADD CONSTRAINT FK_RD_TASKCOMMENT_TASK FOREIGN KEY (TASK_ID) REFERENCES RD_TASK (ID); ALTER TABLE RD_TASK_COMMENT ADD CONSTRAINT FK_RD_TASKCOMMENT_USER FOREIGN KEY (USER_ID) REFERENCES RD_USER (ID); ALTER TABLE RD_TASK_CTL ADD CONSTRAINT FK_RD_TASKCTL_TASK FOREIGN KEY (TASK_ID) REFERENCES RD_TASK (ID); ALTER TABLE RD_TASK_EXEC ADD CONSTRAINT FK_RD_TASKEXEC_FEEDER FOREIGN KEY (FEEDER_ID) REFERENCES RD_FEEDER (ID); ALTER TABLE RD_TASK_EXEC ADD CONSTRAINT FK_RD_TASKEXEC_TASK FOREIGN KEY (TASK_ID) REFERENCES RD_TASK (ID); ALTER TABLE RD_TASK_PARAM ADD CONSTRAINT FK_RD_TASKPARAM_PRODUCT FOREIGN KEY (PARAM_ID) REFERENCES RD_PARAM (ID); ALTER TABLE RD_TASK_PARAM ADD CONSTRAINT FK_RD_TASKPARAM_TASK FOREIGN KEY (TASK_ID) REFERENCES RD_TASK (ID); ALTER TABLE RD_TASK_PRODUCT ADD CONSTRAINT FK_RD_TASKPRODUCT_BUNKER FOREIGN KEY (BUNKER_ID) REFERENCES RD_BUNKER (ID); ALTER TABLE RD_TASK_PRODUCT ADD CONSTRAINT FK_RD_TASKPRODUCT_PRODUCT FOREIGN KEY (PRODUCT_ID) REFERENCES RD_PRODUCT (ID); ALTER TABLE RD_TASK_PRODUCT ADD CONSTRAINT FK_RD_TASKPRODUCT_TASK FOREIGN KEY (TASK_ID) REFERENCES RD_TASK (ID); ALTER TABLE RD_USER_ROLE ADD CONSTRAINT FK_RD_USERROLE_ROLE FOREIGN KEY (ROLE_ID) REFERENCES RD_ROLE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE RD_USER_ROLE ADD CONSTRAINT FK_RD_USERROLE_USER FOREIGN KEY (USER_ID) REFERENCES RD_USER (ID) ON DELETE CASCADE ON UPDATE CASCADE; /******************************************************************************/ /*** Indices ***/ /******************************************************************************/ CREATE UNIQUE DESCENDING INDEX IX_RD_TASKEXEC_ID_DSC ON RD_TASK_EXEC (ID);