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

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Скрипт базы данных модуля kkmansvr ======

<file sql 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 ; ^

</file>