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