/******************************************************************************/ /*** Generated by IBExpert 2017.6.19.1 21.02.2022 10:16:18 ***/ /******************************************************************************/ SET SQL DIALECT 3; SET NAMES WIN1251; CREATE DATABASE 'myhost:kkman' USER 'SYSDBA' PAGE_SIZE 8192 DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251; /******************************************************************************/ /*** Generators ***/ /******************************************************************************/ CREATE SEQUENCE GEN_KM_CYCLE_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_CYCLE_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_CYCLE_PRODUCT_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_CYCLE_PRODUCT_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_DOSER_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_DOSER_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_PRODUCT_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_PRODUCT_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_RECIPE_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_RECIPE_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_RECIPE_PRODUCT_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_RECIPE_PRODUCT_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_SHIFT_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_SHIFT_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_SHIFT_PRODUCT_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_SHIFT_PRODUCT_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_SHIFT_RECIPE_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_SHIFT_RECIPE_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_TASK_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_TASK_ID RESTART WITH 0; CREATE SEQUENCE GEN_KM_TASK_PRODUCT_ID START WITH 0 INCREMENT BY 1; ALTER SEQUENCE GEN_KM_TASK_PRODUCT_ID RESTART WITH 0; /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE TABLE KM_CYCLE ( ID INTEGER NOT NULL, TASK_ID INTEGER NOT NULL, DT TIMESTAMP, CYCLE_CNT INTEGER, WEIGHT_REQ INTEGER, WEIGHT_FIN INTEGER, WEIGHT_CTL INTEGER ); CREATE TABLE KM_CYCLE_PRODUCT ( ID INTEGER NOT NULL, CYCLE_ID INTEGER NOT NULL, PRODUCT_ID INTEGER NOT NULL, WEIGHT_REQ INTEGER, WEIGHT_FIN INTEGER ); CREATE TABLE KM_DOSER ( ID INTEGER NOT NULL, LINE_ID INTEGER NOT NULL, NAME VARCHAR(32) NOT NULL, DESCR VARCHAR(128), CAN_CONTROL SMALLINT, DELETED SMALLINT DEFAULT 0 ); CREATE TABLE KM_LINE ( ID INTEGER NOT NULL, NAME VARCHAR(128) NOT NULL, DELETED SMALLINT DEFAULT 0 ); CREATE TABLE KM_PRODUCT ( ID INTEGER NOT NULL, NAME VARCHAR(128) NOT NULL, DELETED SMALLINT DEFAULT 0 ); CREATE TABLE KM_PRODUCT_DOSER ( PRODUCT_ID INTEGER NOT NULL, DOSER_ID INTEGER NOT NULL ); CREATE TABLE KM_RECIPE ( ID INTEGER NOT NULL, NAME VARCHAR(128) NOT NULL, WEIGHT_MIN INTEGER, WEIGHT_MAX INTEGER, DELETED SMALLINT DEFAULT 0 ); CREATE TABLE KM_RECIPE_PRODUCT ( ID INTEGER NOT NULL, RECIPE_ID INTEGER NOT NULL, PRODUCT_ID INTEGER NOT NULL, WEIGHT INTEGER, ORD SMALLINT ); CREATE TABLE KM_REPO ( NAME VARCHAR(128) NOT NULL, VAL VARCHAR(128) NOT NULL ); CREATE TABLE KM_SHIFT ( ID INTEGER NOT NULL, USER_ID INTEGER NOT NULL, DT_OPEN TIMESTAMP, DT_CLOSE TIMESTAMP, DELETED SMALLINT DEFAULT 0 ); CREATE TABLE KM_SHIFT_PRODUCT ( ID INTEGER NOT NULL, SHIFT_ID INTEGER NOT NULL, PRODUCT_ID INTEGER NOT NULL, WEIGHT_FIN BIGINT ); CREATE TABLE KM_SHIFT_RECIPE ( ID INTEGER NOT NULL, SHIFT_ID INTEGER NOT NULL, RECIPE_ID INTEGER NOT NULL, WEIGHT_FIN BIGINT, WEIGHT_CTL BIGINT ); CREATE TABLE KM_TASK ( ID INTEGER NOT NULL, RECIPE_ID INTEGER NOT NULL, LINE_ID INTEGER NOT NULL, USER_ID_CREATE INTEGER NOT NULL, DT_CREATE TIMESTAMP, CYCLE_REQ INTEGER, CYCLE_CNT INTEGER, WEIGHT_REQ INTEGER, CONTROL_DOSER_ID INTEGER, DT_EXECUTE TIMESTAMP, SHIFT_ID INTEGER, STATUS SMALLINT, DELETED SMALLINT DEFAULT 0 ); CREATE TABLE KM_TASK_PRODUCT ( ID INTEGER NOT NULL, TASK_ID INTEGER NOT NULL, PRODUCT_ID INTEGER NOT NULL, DOSER_ID INTEGER, WEIGHT_REQ INTEGER, ORD SMALLINT ); CREATE TABLE KM_USER ( ID INTEGER NOT NULL, NAME VARCHAR(128) NOT NULL, PSW VARCHAR(64), DELETED SMALLINT DEFAULT 0 ); /******************************************************************************/ /*** Primary keys ***/ /******************************************************************************/ ALTER TABLE KM_CYCLE ADD CONSTRAINT PK_KM_CYCLE PRIMARY KEY (ID); ALTER TABLE KM_CYCLE_PRODUCT ADD CONSTRAINT PK_KM_CYCLE_PRODUCT PRIMARY KEY (ID); ALTER TABLE KM_DOSER ADD CONSTRAINT PK_KM_DOSER PRIMARY KEY (ID); ALTER TABLE KM_LINE ADD CONSTRAINT PK_KM_LINE PRIMARY KEY (ID); ALTER TABLE KM_PRODUCT ADD CONSTRAINT PK_KM_PRODUCT PRIMARY KEY (ID); ALTER TABLE KM_RECIPE ADD CONSTRAINT PK_KM_RECIPE PRIMARY KEY (ID); ALTER TABLE KM_RECIPE_PRODUCT ADD CONSTRAINT PK_KM_RECIPE_PRODUCT PRIMARY KEY (ID); ALTER TABLE KM_REPO ADD PRIMARY KEY (NAME); ALTER TABLE KM_SHIFT ADD CONSTRAINT PK_KM_SHIFT PRIMARY KEY (ID); ALTER TABLE KM_SHIFT_PRODUCT ADD CONSTRAINT PK_KM_SHIFT_PRODUCT PRIMARY KEY (ID); ALTER TABLE KM_SHIFT_RECIPE ADD CONSTRAINT PK_KM_SHIFT_RECIPE PRIMARY KEY (ID); ALTER TABLE KM_TASK ADD CONSTRAINT PK_KM_TASK PRIMARY KEY (ID); ALTER TABLE KM_TASK_PRODUCT ADD CONSTRAINT PK_KM_TASK_PRODUCT PRIMARY KEY (ID); ALTER TABLE KM_USER ADD CONSTRAINT PK_KM_USER PRIMARY KEY (ID); /******************************************************************************/ /*** Foreign keys ***/ /******************************************************************************/ ALTER TABLE KM_CYCLE ADD CONSTRAINT FK_KM_CYCLE_TASK FOREIGN KEY (TASK_ID) REFERENCES KM_TASK (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_CYCLE_PRODUCT ADD CONSTRAINT FK_KM_CYCLE_PRODUCT_1 FOREIGN KEY (CYCLE_ID) REFERENCES KM_CYCLE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_CYCLE_PRODUCT ADD CONSTRAINT FK_KM_CYCLE_PRODUCT_2 FOREIGN KEY (PRODUCT_ID) REFERENCES KM_PRODUCT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_DOSER ADD CONSTRAINT FK_KM_DOSER_1 FOREIGN KEY (LINE_ID) REFERENCES KM_LINE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_PRODUCT_DOSER ADD CONSTRAINT FK_KM_PRODUCT_DOSER_1 FOREIGN KEY (PRODUCT_ID) REFERENCES KM_PRODUCT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_PRODUCT_DOSER ADD CONSTRAINT FK_KM_PRODUCT_DOSER_2 FOREIGN KEY (DOSER_ID) REFERENCES KM_DOSER (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_RECIPE_PRODUCT ADD CONSTRAINT FK_KM_RECIPE_PRODUCT_1 FOREIGN KEY (RECIPE_ID) REFERENCES KM_RECIPE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_RECIPE_PRODUCT ADD CONSTRAINT FK_KM_RECIPE_PRODUCT_2 FOREIGN KEY (PRODUCT_ID) REFERENCES KM_PRODUCT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_SHIFT ADD CONSTRAINT FK_KM_SHIFT_1 FOREIGN KEY (USER_ID) REFERENCES KM_USER (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_SHIFT_PRODUCT ADD CONSTRAINT FK_KM_SHIFT_PRODUCT_1 FOREIGN KEY (PRODUCT_ID) REFERENCES KM_PRODUCT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_SHIFT_PRODUCT ADD CONSTRAINT FK_KM_SHIFT_PRODUCT_2 FOREIGN KEY (SHIFT_ID) REFERENCES KM_SHIFT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_SHIFT_RECIPE ADD CONSTRAINT FK_KM_SHIFT_RECIPE_1 FOREIGN KEY (SHIFT_ID) REFERENCES KM_SHIFT (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_SHIFT_RECIPE ADD CONSTRAINT FK_KM_SHIFT_RECIPE_2 FOREIGN KEY (RECIPE_ID) REFERENCES KM_RECIPE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_TASK ADD CONSTRAINT FK_KM_TASK_1 FOREIGN KEY (RECIPE_ID) REFERENCES KM_RECIPE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_TASK ADD CONSTRAINT FK_KM_TASK_2 FOREIGN KEY (LINE_ID) REFERENCES KM_LINE (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_TASK_PRODUCT ADD CONSTRAINT FK_KM_TASK_PRODUCT_1 FOREIGN KEY (TASK_ID) REFERENCES KM_TASK (ID) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE KM_TASK_PRODUCT ADD CONSTRAINT FK_KM_TASK_PRODUCT_2 FOREIGN KEY (PRODUCT_ID) REFERENCES KM_PRODUCT (ID) ON DELETE CASCADE ON UPDATE CASCADE; /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: KM_CYCLE_BI */ CREATE TRIGGER KM_CYCLE_BI FOR KM_CYCLE ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_CYCLE_ID,1); END ^ /* Trigger: KM_CYCLE_BIUD0 */ CREATE TRIGGER KM_CYCLE_BIUD0 FOR KM_CYCLE ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0 AS BEGIN POST_EVENT 'KM_CYCLE'; END ^ /* Trigger: KM_CYCLE_PRODUCT_BI */ CREATE TRIGGER KM_CYCLE_PRODUCT_BI FOR KM_CYCLE_PRODUCT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_CYCLE_PRODUCT_ID,1); END ^ /* Trigger: KM_CYCLE_PRODUCT_BIUD0 */ CREATE TRIGGER KM_CYCLE_PRODUCT_BIUD0 FOR KM_CYCLE_PRODUCT ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0 AS BEGIN POST_EVENT 'KM_CYCLE'; END ^ /* Trigger: KM_DOSER_BI */ CREATE TRIGGER KM_DOSER_BI FOR KM_DOSER ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_DOSER_ID,1); END ^ /* Trigger: KM_PRODUCT_BI */ CREATE TRIGGER KM_PRODUCT_BI FOR KM_PRODUCT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_PRODUCT_ID,1); END ^ /* Trigger: KM_RECIPE_BI */ CREATE TRIGGER KM_RECIPE_BI FOR KM_RECIPE ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_RECIPE_ID,1); END ^ /* Trigger: KM_RECIPE_PRODUCT_BI */ CREATE TRIGGER KM_RECIPE_PRODUCT_BI FOR KM_RECIPE_PRODUCT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_RECIPE_PRODUCT_ID,1); END ^ /* Trigger: KM_SHIFT_BI */ CREATE TRIGGER KM_SHIFT_BI FOR KM_SHIFT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_SHIFT_ID,1); END ^ /* Trigger: KM_SHIFT_PRODUCT_BI */ CREATE TRIGGER KM_SHIFT_PRODUCT_BI FOR KM_SHIFT_PRODUCT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_SHIFT_PRODUCT_ID,1); END ^ /* Trigger: KM_SHIFT_RECIPE_BI */ CREATE TRIGGER KM_SHIFT_RECIPE_BI FOR KM_SHIFT_RECIPE ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_SHIFT_RECIPE_ID,1); END ^ /* Trigger: KM_TASK_BI */ CREATE TRIGGER KM_TASK_BI FOR KM_TASK ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_TASK_ID,1); END ^ /* Trigger: KM_TASK_BIUD0 */ CREATE TRIGGER KM_TASK_BIUD0 FOR KM_TASK ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0 AS BEGIN POST_EVENT 'KM_TASK'; END ^ /* Trigger: KM_TASK_PRODUCT_BI */ CREATE TRIGGER KM_TASK_PRODUCT_BI FOR KM_TASK_PRODUCT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ID IS NULL) THEN NEW.ID = GEN_ID(GEN_KM_TASK_PRODUCT_ID,1); END ^ SET TERM ; ^