/******************************************************************************/
/***          Generated by IBExpert 2022.3.4.1 21.03.2025 17:38:08          ***/
/******************************************************************************/
 
SET SQL DIALECT 3;
 
SET NAMES WIN1251;
 
CONNECT 'myhost:motohr' USER 'SYSDBA' PASSWORD 'masterkey';
 
 
 
/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/
 
CREATE DOMAIN JROBO_MOTOHR AS
INTEGER;
 
 
 
/******************************************************************************/
/***                            Stored functions                            ***/
/******************************************************************************/
 
 
 
SET TERM ^ ;
 
CREATE FUNCTION MH_CNT_SEC (
    SEC INTEGER,
    DT TIMESTAMP,
    RUNNING SMALLINT,
    TIMER SMALLINT)
RETURNS INTEGER
AS
BEGIN
  RETURN NULL;
END^
 
 
 
 
 
 
SET TERM ; ^
 
 
 
/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/
 
 
 
CREATE TABLE MH_COUNTER (
    ID               INTEGER GENERATED BY DEFAULT AS IDENTITY,
    MACH_ID          INTEGER,
    COUNTER_TYPE_ID  INTEGER,
    SEC              INTEGER DEFAULT 2147483647,
    DELETED          SMALLINT DEFAULT 0
);
 
CREATE TABLE MH_COUNTER_TYPE (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    DESCR    VARCHAR(128),
    TIMER    SMALLINT DEFAULT 0,
    DELETED  SMALLINT DEFAULT 0
);
 
CREATE TABLE MH_INTERVAL (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    MACH_ID  INTEGER,
    DTBEG    TIMESTAMP,
    DTEND    TIMESTAMP,
    SEC      INTEGER
);
 
CREATE TABLE MH_LOGBOOK (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    MACH_ID  INTEGER,
    DT       TIMESTAMP,
    TEXT     VARCHAR(512),
    FIO      VARCHAR(64)
);
 
CREATE TABLE MH_MACH (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    NAME     VARCHAR(128) NOT NULL,
    TAGNAME  VARCHAR(128),
    DESCR    VARCHAR(128)
);
 
CREATE TABLE MH_MACH_STATE (
    MACH_ID  INTEGER,
    DT       TIMESTAMP,
    RUNNING  SMALLINT,
    STATCNT  INTEGER,
    STATSEC  INTEGER
);
 
CREATE TABLE MH_REPO (
    NAME  VARCHAR(128) NOT NULL,
    VAL   VARCHAR(128) NOT NULL
);
 
CREATE TABLE MH_STAT (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    MACH_ID  INTEGER,
    PERIOD   INTEGER,
    CNT      INTEGER,
    SEC      INTEGER
);
 
 
 
/******************************************************************************/
/***                                 Views                                  ***/
/******************************************************************************/
 
 
/* View: MH_COUNTER_CALC */
CREATE VIEW MH_COUNTER_CALC(
    MACH_ID,
    COUNTER_ID,
    COUNTER_TYPE_ID,
    MACH_NAME,
    MACH_DESCR,
    RUNNING,
    COUNTER_DESCR,
    TIMER,
    SEC)
AS
SELECT
  M.ID MACH_ID,
  C.ID COUNTER_ID,
  CT.ID COUNTER_TYPE_ID,
  M.NAME MACH_NAME,
  M.DESCR MACH_DESCR,
  MS.RUNNING RUNNING,
  CT.DESCR COUNTER_DESCR,
  CT.TIMER TIMER,
  MH_CNT_SEC(C.SEC, MS.DT, MS.RUNNING, CT.TIMER) SEC
FROM MH_COUNTER C
JOIN MH_COUNTER_TYPE CT ON CT.ID = C.COUNTER_TYPE_ID
JOIN MH_MACH M ON M.ID = C.MACH_ID
JOIN MH_MACH_STATE MS ON MS.MACH_ID = M.ID
WHERE
  C.DELETED=0
--
-- Usage example (clause PLAN is optional and used for better perfomance):
--
-- select * from mh_pivot p
-- where counter_name = 'total' and mach_name = 'M1001'
-- plan join (
--   p m index (mh_uq_mach_name),
--   p ct index(mh_uq_countertype_name),
--   p c index(mh_ix_counter_machtype),
--   p ms index (mh_fk_machstate_mach))
;
 
 
 
 
/******************************************************************************/
/***                        Autoincrement generators                        ***/
/******************************************************************************/
 
ALTER TABLE MH_COUNTER ALTER ID RESTART WITH 577;
ALTER TABLE MH_COUNTER_TYPE ALTER ID RESTART WITH 4;
ALTER TABLE MH_INTERVAL ALTER ID RESTART WITH 0;
ALTER TABLE MH_LOGBOOK ALTER ID RESTART WITH 8;
ALTER TABLE MH_MACH ALTER ID RESTART WITH 555;
ALTER TABLE MH_STAT ALTER ID RESTART WITH 266400;
 
 
 
 
/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/
 
ALTER TABLE MH_MACH ADD CONSTRAINT MH_UQ_MACH_NAME UNIQUE (NAME);
 
 
/******************************************************************************/
/***                              Primary keys                              ***/
/******************************************************************************/
 
ALTER TABLE MH_COUNTER ADD CONSTRAINT MH_PK_COUNTER PRIMARY KEY (ID);
ALTER TABLE MH_COUNTER_TYPE ADD CONSTRAINT MH_PK_COUNTERTYPE PRIMARY KEY (ID);
ALTER TABLE MH_INTERVAL ADD CONSTRAINT MH_PK_INVERVAL PRIMARY KEY (ID);
ALTER TABLE MH_LOGBOOK ADD CONSTRAINT MH_PK_LOGBOOK PRIMARY KEY (ID);
ALTER TABLE MH_MACH ADD CONSTRAINT MH_PK_MACH PRIMARY KEY (ID);
ALTER TABLE MH_REPO ADD PRIMARY KEY (NAME);
ALTER TABLE MH_STAT ADD CONSTRAINT MH_PK_STAT PRIMARY KEY (ID);
 
 
/******************************************************************************/
/***                              Foreign keys                              ***/
/******************************************************************************/
 
ALTER TABLE MH_COUNTER ADD CONSTRAINT MH_FK_COUNTER_MACH FOREIGN KEY (MACH_ID) REFERENCES MH_MACH (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE MH_COUNTER ADD CONSTRAINT MH_FK_COUNTER_TYPE FOREIGN KEY (COUNTER_TYPE_ID) REFERENCES MH_COUNTER_TYPE (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE MH_INTERVAL ADD CONSTRAINT MH_FK_INTERVAL_MACH FOREIGN KEY (MACH_ID) REFERENCES MH_MACH (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE MH_LOGBOOK ADD CONSTRAINT MH_FK_LOGBOOK_MACH FOREIGN KEY (MACH_ID) REFERENCES MH_MACH (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE MH_MACH_STATE ADD CONSTRAINT MH_FK_MACHSTATE_MACH FOREIGN KEY (MACH_ID) REFERENCES MH_MACH (ID) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE MH_STAT ADD CONSTRAINT MH_FK_STAT_MACH FOREIGN KEY (MACH_ID) REFERENCES MH_MACH (ID) ON DELETE CASCADE ON UPDATE CASCADE;
 
 
/******************************************************************************/
/***                                Indices                                 ***/
/******************************************************************************/
 
CREATE INDEX MH_IX_COUNTER_MACHTYPE ON MH_COUNTER (MACH_ID, COUNTER_TYPE_ID);
CREATE INDEX MH_IX_STAT_PERIOD ON MH_STAT (PERIOD);
 
 
/******************************************************************************/
/***                            Stored functions                            ***/
/******************************************************************************/
 
 
 
SET TERM ^ ;
 
ALTER FUNCTION MH_CNT_SEC (
    SEC INTEGER,
    DT TIMESTAMP,
    RUNNING SMALLINT,
    TIMER SMALLINT)
RETURNS INTEGER
AS
BEGIN
  IF (SEC = 2147483647) THEN
    RETURN SEC;
  RETURN  CAST(SEC + IIF(TIMER = 0, 1, -1) * IIF(RUNNING = 0, 0, (CURRENT_TIMESTAMP - DT) * 86400) AS INTEGER);
END^
 
 
 
SET TERM ; ^