/* Generated by IBExpert 23.10.03 16:01:44*/

SET SQL DIALECT 3;

SET NAMES WIN1251;

SET BLOBFILE 'D:\Dwork\Project\VisScada\Common\Product\sql\Create_prod_db.lob';

CREATE DATABASE 'D:\Dwork\Project\VisScada\Common\Product\sql\PROD.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1251;



/******************************************************************************/
/*                                 Generators                                 */
/******************************************************************************/

CREATE GENERATOR GEN_CULTURA_ID;
SET GENERATOR GEN_CULTURA_ID TO 10;

CREATE GENERATOR GEN_POSTAVSHIK_ID;
SET GENERATOR GEN_POSTAVSHIK_ID TO 4;

CREATE GENERATOR GEN_PRODUCT_ID;
SET GENERATOR GEN_PRODUCT_ID TO 14;



/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/

CREATE TABLE CULTSOVM (
    IDCULT1  INTEGER NOT NULL,
    IDCULT2  INTEGER NOT NULL,
    DWET     DECIMAL(12,2),
    DSOR     DECIMAL(12,2),
    DKLEY    DECIMAL(12,2)
);

CREATE TABLE CULTURA (
    IDCULT  INTEGER NOT NULL,
    NAME    VARCHAR(128)
);

CREATE TABLE POSTAVSHIK (
    IDPOSTAV  INTEGER NOT NULL,
    NAME      VARCHAR(128)
);

CREATE TABLE PRODUCT (
    IDPROD    INTEGER NOT NULL,
    IDCULT    INTEGER NOT NULL,
    IDPOSTAV  INTEGER NOT NULL,
    IDOBJ     VARCHAR(10),
    DTLOAD    TIMESTAMP,
    DTEMPTY   TIMESTAMP,
    ARH       CHAR(1),
    INFO      BLOB SUB_TYPE 1 SEGMENT SIZE 80
);





/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/

ALTER TABLE CULTURA ADD CONSTRAINT PK_CULTURA PRIMARY KEY (IDCULT);
ALTER TABLE POSTAVSHIK ADD CONSTRAINT PK_POSTAVSHIK PRIMARY KEY (IDPOSTAV);
ALTER TABLE PRODUCT ADD CONSTRAINT PK_PRODUCT PRIMARY KEY (IDPROD);


/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/

ALTER TABLE CULTSOVM ADD CONSTRAINT FK_CULT1SOVM FOREIGN KEY (IDCULT1) REFERENCES CULTURA (IDCULT) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE CULTSOVM ADD CONSTRAINT FK_CULT2SOVM FOREIGN KEY (IDCULT2) REFERENCES CULTURA (IDCULT) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PRODUCT ADD CONSTRAINT FK_PRODUCT_CULT FOREIGN KEY (IDCULT) REFERENCES CULTURA (IDCULT);
ALTER TABLE PRODUCT ADD CONSTRAINT FK_PRODUCT_POSTAV FOREIGN KEY (IDPOSTAV) REFERENCES POSTAVSHIK (IDPOSTAV);


/******************************************************************************/
/*                                  Indices                                   */
/******************************************************************************/

CREATE INDEX PRODUCT_IDX1 ON PRODUCT (ARH);
CREATE INDEX PRODUCT_IDX2 ON PRODUCT (IDOBJ);


/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/


SET TERM ^ ;




/* Trigger: CULTURA_BI */
CREATE TRIGGER CULTURA_BI FOR CULTURA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDCULT IS NULL) THEN
    NEW.IDCULT = GEN_ID(GEN_CULTURA_ID,1);
END
^

/* Trigger: POSTAVSHIK_BI */
CREATE TRIGGER POSTAVSHIK_BI FOR POSTAVSHIK
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDPOSTAV IS NULL) THEN
    NEW.IDPOSTAV = GEN_ID(GEN_POSTAVSHIK_ID,1);
END
^

/* Trigger: PRODUCT_BI */
CREATE TRIGGER PRODUCT_BI FOR PRODUCT
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDPROD IS NULL) THEN
    NEW.IDPROD = GEN_ID(GEN_PRODUCT_ID,1);
END
^

SET TERM ; ^
