Это старая версия документа.

<code 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 ; ^

</file>