Скрипт создания БД.sql

/******************************************************************************/
/***         Generated by IBExpert 2017.8.22.1 08.11.2017 16:36:26          ***/
/******************************************************************************/



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

CREATE GENERATOR GEN_PEOPLE_ID;
SET GENERATOR GEN_PEOPLE_ID TO 230;



/******************************************************************************/
/***                               Exceptions                               ***/
/******************************************************************************/

CREATE EXCEPTION EXIST_PEOPLE 'Òàêîé ÷åëîâåê óæå ñóùåñòâóåò';



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



CREATE TABLE CHAT_INFO (
    ID         BIGINT NOT NULL,
    FULL_NAME  VARCHAR(50),
    NICKNAME   VARCHAR(50)
);

CREATE TABLE CHATS (
    ID                 BIGINT NOT NULL,
    FULL_NAME          VARCHAR(50) NOT NULL,
    NIKNAME            VARCHAR(50),
    TIME_LAST_SENDING  TIMESTAMP,
    TIME_NEXT_SENDING  TIMESTAMP,
    TIME_SENDING       TIME DEFAULT '8:00:00',
    NEED_IN_SENDING    COMPUTED BY ((IIF((CURRENT_TIMESTAMP>TIME_NEXT_SENDING),1,0)))
);

CREATE TABLE DIALOGS (
    CHAT    BIGINT NOT NULL,
    STATUS  INTEGER DEFAULT 0 NOT NULL
);

CREATE TABLE DIALOGS_DATA (
    CHAT         BIGINT NOT NULL,
    FAMILIYA     VARCHAR(50),
    IMYA         VARCHAR(50),
    OTCHESTVO    VARCHAR(50),
    TELEFON      VARCHAR(15),
    BIRTHDAY     DATE,
    DESCRIPTION  VARCHAR(100)
);

CREATE TABLE PEOPLE (
    ID           INTEGER NOT NULL,
    FAMILIYA     VARCHAR(20),
    IMYA         VARCHAR(20) CHARACTER SET WIN1251,
    OTCHESTVO    VARCHAR(20),
    TELEFON      VARCHAR(20),
    BIRTHDAY     DATE,
    DESCRIPTION  VARCHAR(100),
    VOZRAST      COMPUTED BY (( EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM BIRTHDAY)
 - IIF((EXTRACT(MONTH FROM CURRENT_DATE) < EXTRACT(MONTH FROM BIRTHDAY)),
   1, IIF((EXTRACT(MONTH FROM CURRENT_DATE) = EXTRACT(MONTH FROM BIRTHDAY))
  AND ((EXTRACT(DAY FROM CURRENT_DATE) < EXTRACT(DAY FROM BIRTHDAY))),1,0))))
);



/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/

ALTER TABLE CHATS ADD CONSTRAINT PK_CHATS PRIMARY KEY (ID);
ALTER TABLE CHAT_INFO ADD CONSTRAINT PK_CHAT_INFO PRIMARY KEY (ID);
ALTER TABLE DIALOGS ADD CONSTRAINT PK_DIALOGS PRIMARY KEY (CHAT);
ALTER TABLE DIALOGS_DATA ADD CONSTRAINT PK_DIALOGS_DATA PRIMARY KEY (CHAT);
ALTER TABLE PEOPLE ADD CONSTRAINT PK_PEOPLE PRIMARY KEY (ID);


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



SET TERM ^ ;



/******************************************************************************/
/***                          Triggers for tables                           ***/
/******************************************************************************/



/* Trigger: CHATS_BI0 */
CREATE TRIGGER CHATS_BI0 FOR CHATS
ACTIVE BEFORE INSERT POSITION 0
AS
begin
if(new.time_sending>current_time)
then new.time_next_sending=current_date+new.time_sending;
else new.time_next_sending=(current_date+1)+new.time_sending;
end
^

/* Trigger: CHATS_BU0 */
CREATE TRIGGER CHATS_BU0 FOR CHATS
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
  if(new.time_last_sending<>old.time_last_sending)
  then new.time_next_sending=(current_date+1)+new.time_sending;
  else if(new.time_sending<>old.time_sending)
  then if(new.time_sending>current_time)
then new.time_next_sending=current_date+new.time_sending;
else new.time_next_sending=(current_date+1)+new.time_sending;
end
^

/* Trigger: PEOPLE_BI */
CREATE TRIGGER PEOPLE_BI FOR PEOPLE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_people_id,1);
end
^

/* Trigger: PEOPLE_BI0 */
CREATE TRIGGER PEOPLE_BI0 FOR PEOPLE
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE X BIGINT;
begin
  select COUNT (*) FROM people as P
  where P.FAMILIYA = NEW.familiya AND
  P.IMYA =  NEW.imya and P.OTCHESTVO = NEW.otchestvo AND P.BIRTHDAY = NEW.birthday
  INTO X;
  if (X>0) then exception exist_people;
end
^
SET TERM ; ^