Скрипт базы данных модуля kkmansvr

/******************************************************************************/
/***          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);