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

kkman.sql

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