Различия
Здесь показаны различия между двумя версиями данной страницы.
| Предыдущая версия справа и слева Предыдущая версия Следующая версия | Предыдущая версия | ||
|
doc:jroboplc:modules:motohr_db [2025/02/24 17:37] denis |
doc:jroboplc:modules:motohr_db [2025/03/21 13:38] (текущий) denis |
||
|---|---|---|---|
| Строка 1: | Строка 1: | ||
| <code sql> | <code sql> | ||
| /******************************************************************************/ | /******************************************************************************/ | ||
| - | /*** Generated by IBExpert 2022.3.4.1 24.02.2025 21:36:54 ***/ | + | /*** Generated by IBExpert 2022.3.4.1 21.03.2025 17:38:08 ***/ |
| /******************************************************************************/ | /******************************************************************************/ | ||
| Строка 29: | Строка 29: | ||
| SET TERM ^ ; | SET TERM ^ ; | ||
| - | CREATE FUNCTION MH_CNT_FUNC ( | + | CREATE FUNCTION MH_CNT_SEC ( |
| SEC INTEGER, | SEC INTEGER, | ||
| DT TIMESTAMP, | DT TIMESTAMP, | ||
| Строка 59: | Строка 59: | ||
| MACH_ID INTEGER, | MACH_ID INTEGER, | ||
| COUNTER_TYPE_ID INTEGER, | COUNTER_TYPE_ID INTEGER, | ||
| - | SEC INTEGER, | + | SEC INTEGER DEFAULT 2147483647, |
| DELETED SMALLINT DEFAULT 0 | DELETED SMALLINT DEFAULT 0 | ||
| ); | ); | ||
| Строка 65: | Строка 65: | ||
| CREATE TABLE MH_COUNTER_TYPE ( | CREATE TABLE MH_COUNTER_TYPE ( | ||
| ID INTEGER GENERATED BY DEFAULT AS IDENTITY, | ID INTEGER GENERATED BY DEFAULT AS IDENTITY, | ||
| - | NAME VARCHAR(64) NOT NULL, | ||
| DESCR VARCHAR(128), | DESCR VARCHAR(128), | ||
| TIMER SMALLINT DEFAULT 0, | TIMER SMALLINT DEFAULT 0, | ||
| Строка 77: | Строка 76: | ||
| DTEND TIMESTAMP, | DTEND TIMESTAMP, | ||
| SEC INTEGER | SEC INTEGER | ||
| + | ); | ||
| + | |||
| + | CREATE TABLE MH_LOGBOOK ( | ||
| + | ID INTEGER GENERATED BY DEFAULT AS IDENTITY, | ||
| + | MACH_ID INTEGER, | ||
| + | DT TIMESTAMP, | ||
| + | TEXT VARCHAR(512), | ||
| + | FIO VARCHAR(64) | ||
| ); | ); | ||
| Строка 122: | Строка 129: | ||
| MACH_DESCR, | MACH_DESCR, | ||
| RUNNING, | RUNNING, | ||
| - | COUNTER_NAME, | ||
| COUNTER_DESCR, | COUNTER_DESCR, | ||
| TIMER, | TIMER, | ||
| Строка 134: | Строка 140: | ||
| M.DESCR MACH_DESCR, | M.DESCR MACH_DESCR, | ||
| MS.RUNNING RUNNING, | MS.RUNNING RUNNING, | ||
| - | CT.NAME COUNTER_NAME, | ||
| CT.DESCR COUNTER_DESCR, | CT.DESCR COUNTER_DESCR, | ||
| CT.TIMER TIMER, | 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 | + | MH_CNT_SEC(C.SEC, MS.DT, MS.RUNNING, CT.TIMER) SEC |
| FROM MH_COUNTER C | FROM MH_COUNTER C | ||
| JOIN MH_COUNTER_TYPE CT ON CT.ID = C.COUNTER_TYPE_ID | JOIN MH_COUNTER_TYPE CT ON CT.ID = C.COUNTER_TYPE_ID | ||
| Строка 145: | Строка 150: | ||
| C.DELETED=0 | C.DELETED=0 | ||
| -- | -- | ||
| - | -- Usage example (clause plan is optional and used for better perfomance): | + | -- Usage example (clause PLAN is optional and used for better perfomance): |
| -- | -- | ||
| -- select * from mh_pivot p | -- select * from mh_pivot p | ||
| -- where counter_name = 'total' and mach_name = 'M1001' | -- where counter_name = 'total' and mach_name = 'M1001' | ||
| - | -- plan join ( | + | -- plan join ( |
| -- p m index (mh_uq_mach_name), | -- p m index (mh_uq_mach_name), | ||
| -- p ct index(mh_uq_countertype_name), | -- p ct index(mh_uq_countertype_name), | ||
| - | -- p c index(mh_ix_counter_machtype), | + | -- p c index(mh_ix_counter_machtype), |
| -- p ms index (mh_fk_machstate_mach)) | -- p ms index (mh_fk_machstate_mach)) | ||
| ; | ; | ||
| Строка 163: | Строка 168: | ||
| /******************************************************************************/ | /******************************************************************************/ | ||
| - | ALTER TABLE MH_COUNTER ALTER ID RESTART WITH 1699; | + | ALTER TABLE MH_COUNTER ALTER ID RESTART WITH 577; |
| ALTER TABLE MH_COUNTER_TYPE ALTER ID RESTART WITH 4; | ALTER TABLE MH_COUNTER_TYPE ALTER ID RESTART WITH 4; | ||
| - | ALTER TABLE MH_INTERVAL ALTER ID RESTART WITH 2; | + | ALTER TABLE MH_INTERVAL ALTER ID RESTART WITH 0; |
| - | ALTER TABLE MH_MACH ALTER ID RESTART WITH 566; | + | ALTER TABLE MH_LOGBOOK ALTER ID RESTART WITH 8; |
| - | ALTER TABLE MH_STAT ALTER ID RESTART WITH 128482; | + | ALTER TABLE MH_MACH ALTER ID RESTART WITH 555; |
| + | ALTER TABLE MH_STAT ALTER ID RESTART WITH 266400; | ||
| Строка 176: | Строка 182: | ||
| /******************************************************************************/ | /******************************************************************************/ | ||
| - | 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); | ALTER TABLE MH_MACH ADD CONSTRAINT MH_UQ_MACH_NAME UNIQUE (NAME); | ||
| Строка 187: | Строка 192: | ||
| ALTER TABLE MH_COUNTER_TYPE ADD CONSTRAINT MH_PK_COUNTERTYPE 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_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_MACH ADD CONSTRAINT MH_PK_MACH PRIMARY KEY (ID); | ||
| ALTER TABLE MH_REPO ADD PRIMARY KEY (NAME); | ALTER TABLE MH_REPO ADD PRIMARY KEY (NAME); | ||
| Строка 199: | Строка 205: | ||
| 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_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_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_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; | ALTER TABLE MH_STAT ADD CONSTRAINT MH_FK_STAT_MACH FOREIGN KEY (MACH_ID) REFERENCES MH_MACH (ID) ON DELETE CASCADE ON UPDATE CASCADE; | ||
| Строка 219: | Строка 226: | ||
| SET TERM ^ ; | SET TERM ^ ; | ||
| - | ALTER FUNCTION MH_CNT_FUNC ( | + | ALTER FUNCTION MH_CNT_SEC ( |
| SEC INTEGER, | SEC INTEGER, | ||
| DT TIMESTAMP, | DT TIMESTAMP, | ||
| Строка 226: | Строка 233: | ||
| RETURNS INTEGER | RETURNS INTEGER | ||
| AS | AS | ||
| - | begin | + | BEGIN |
| - | return cast(sec + iif(timer = 0, 1, -1) * iif(running = 0, 0, (current_timestamp - dt) * 86400) as integer); | + | IF (SEC = 2147483647) THEN |
| - | end^ | + | RETURN SEC; |
| + | RETURN CAST(SEC + IIF(TIMER = 0, 1, -1) * IIF(RUNNING = 0, 0, (CURRENT_TIMESTAMP - DT) * 86400) AS INTEGER); | ||
| + | END^ | ||
