Это старая версия документа.

<code yaml> dbscr.motohr.init1: dbscr.motohr.init1: - if not has_domain(, JROBO_MOTOHR) - do CREATE DOMAIN JROBO_MOTOHR AS INTEGER # TABLES - | do CREATE TABLE MH_COUNTER_TYPE ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_COUNTERTYPE PRIMARY KEY, NAME VARCHAR(64) NOT NULL CONSTRAINT MH_UQ_COUNTERTYPE_NAME UNIQUE, DESCR VARCHAR(128), TIMER SMALLINT DEFAULT 0, DELETED SMALLINT DEFAULT 0 ) - | do CREATE TABLE MH_MACH ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_MACH PRIMARY KEY, NAME VARCHAR(128) NOT NULL CONSTRAINT MH_UQ_MACH_NAME UNIQUE, TAGNAME VARCHAR(128), DESCR VARCHAR(128) ) - | do CREATE TABLE MH_COUNTER ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_COUNTER PRIMARY KEY, MACH_ID INTEGER CONSTRAINT MH_FK_COUNTER_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE, COUNTER_TYPE_ID INTEGER CONSTRAINT MH_FK_COUNTER_TYPE REFERENCES MH_COUNTER_TYPE ON DELETE CASCADE ON UPDATE CASCADE, SEC INTEGER, DELETED SMALLINT DEFAULT 0 ) - | do CREATE TABLE MH_MACH_STATE ( MACH_ID INTEGER CONSTRAINT MH_FK_MACHSTATE_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE, DT TIMESTAMP, RUNNING SMALLINT, STATCNT INTEGER, STATSEC INTEGER ) - | do CREATE TABLE MH_STAT ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_STAT PRIMARY KEY, MACH_ID INTEGER CONSTRAINT MH_FK_STAT_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE, PERIOD INTEGER, CNT INTEGER, SEC INTEGER ) - | do CREATE TABLE MH_INTERVAL ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_INVERVAL PRIMARY KEY, MACH_ID INTEGER CONSTRAINT MH_FK_INTERVAL_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE, DTBEG TIMESTAMP, DTEND TIMESTAMP, SEC INTEGER ) # INDEXES - do CREATE INDEX MH_IX_STAT_PERIOD ON MH_STAT (PERIOD) - do CREATE INDEX MH_IX_COUNTER_MACHTYPE ON MH_COUNTER (MACH_ID, COUNTER_TYPE_ID) # FUNCTIONS - | do CREATE FUNCTION MH_CNT_SEC ( SEC INTEGER, DT TIMESTAMP, RUNNING SMALLINT, TIMER SMALLINT) RETURNS INTEGER AS BEGIN RETURN CAST(SEC + IIF(TIMER = 0, 1, -1) * IIF(RUNNING = 0, 0, (CURRENT_TIMESTAMP - DT) * 86400) AS INTEGER); END # VIEWS - | do CREATE VIEW MH_COUNTER_CALC 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, 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)) </code>