/******************************************************************************/
/***          Generated by IBExpert 2022.3.4.1 23.02.2025 20:26:11          ***/
/******************************************************************************/
 
SET SQL DIALECT 3;
 
SET NAMES WIN1251;
 
CONNECT 'myhost:motohr' USER 'SYSDBA' PASSWORD 'masterkey';
 
 
 
/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/
 
CREATE DOMAIN JROBO_MOTOHR AS
INTEGER;
 
 
 
/******************************************************************************/
/***                                 Tables                                 ***/
/******************************************************************************/
 
 
 
CREATE TABLE MH_COUNTER (
    ID               INTEGER GENERATED BY DEFAULT AS IDENTITY,
    MACH_ID          INTEGER,
    COUNTER_TYPE_ID  INTEGER,
    SEC              INTEGER,
    DELETED          SMALLINT DEFAULT 0
);
 
CREATE TABLE MH_COUNTER_TYPE (
    ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
    NAME     VARCHAR(64) NOT NULL,
    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_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_NAME,
    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.NAME COUNTER_NAME,
  CT.DESCR COUNTER_DESCR,
  CT.TIMER TIMER,
  CAST(C.SEC + IIF(CT.TIMER = 0, 1, -1) * IIF(MS.RUNNING = 0, 0, (CURRENT_TIMESTAMP - MS.DT) * 86400) AS INTEGER) 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))
;
 
 
/******************************************************************************/
/***                           Unique constraints                           ***/
/******************************************************************************/
 
ALTER TABLE MH_COUNTER_TYPE ADD CONSTRAINT MH_UQ_COUNTERTYPE_NAME UNIQUE (NAME);
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_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_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);