Различия
Здесь показаны различия между двумя версиями данной страницы.
| Предыдущая версия справа и слева Предыдущая версия Следующая версия | Предыдущая версия | ||
|
doc:jroboplc:modules:motohr_dbscr [2025/02/24 17:35] denis |
doc:jroboplc:modules:motohr_dbscr [2025/03/21 13:39] (текущий) denis |
||
|---|---|---|---|
| Строка 1: | Строка 1: | ||
| <code yaml> | <code yaml> | ||
| - | dbscr.motohr.init1: | ||
| - | |||
| dbscr.motohr.init1: | dbscr.motohr.init1: | ||
| Строка 12: | Строка 10: | ||
| do CREATE TABLE MH_COUNTER_TYPE ( | do CREATE TABLE MH_COUNTER_TYPE ( | ||
| ID INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_COUNTERTYPE PRIMARY KEY, | 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), | DESCR VARCHAR(128), | ||
| TIMER SMALLINT DEFAULT 0, | TIMER SMALLINT DEFAULT 0, | ||
| DELETED SMALLINT DEFAULT 0 | DELETED SMALLINT DEFAULT 0 | ||
| ) | ) | ||
| + | - | | ||
| + | do ALTER TABLE MH_COUNTER_TYPE ALTER ID RESTART WITH 1 | ||
| - | | - | | ||
| Строка 31: | Строка 30: | ||
| MACH_ID INTEGER CONSTRAINT MH_FK_COUNTER_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE, | 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, | COUNTER_TYPE_ID INTEGER CONSTRAINT MH_FK_COUNTER_TYPE REFERENCES MH_COUNTER_TYPE ON DELETE CASCADE ON UPDATE CASCADE, | ||
| - | SEC INTEGER, | + | SEC INTEGER DEFAULT 2147483647, |
| DELETED SMALLINT DEFAULT 0 | DELETED SMALLINT DEFAULT 0 | ||
| ) | ) | ||
| Строка 60: | Строка 59: | ||
| DTEND TIMESTAMP, | DTEND TIMESTAMP, | ||
| SEC INTEGER | SEC INTEGER | ||
| + | ) | ||
| + | |||
| + | - | | ||
| + | do CREATE TABLE MH_LOGBOOK ( | ||
| + | ID INTEGER GENERATED BY DEFAULT AS IDENTITY CONSTRAINT MH_PK_LOGBOOK PRIMARY KEY, | ||
| + | MACH_ID INTEGER CONSTRAINT MH_FK_LOGBOOK_MACH REFERENCES MH_MACH ON DELETE CASCADE ON UPDATE CASCADE, | ||
| + | DT TIMESTAMP, | ||
| + | TEXT VARCHAR(512), | ||
| + | FIO VARCHAR(64) | ||
| ) | ) | ||
| Строка 77: | Строка 85: | ||
| AS | AS | ||
| BEGIN | 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); | RETURN CAST(SEC + IIF(TIMER = 0, 1, -1) * IIF(RUNNING = 0, 0, (CURRENT_TIMESTAMP - DT) * 86400) AS INTEGER); | ||
| END | END | ||
| Строка 91: | Строка 101: | ||
| 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, | ||
| Строка 102: | Строка 111: | ||
| 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 the best 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)) |
| </code> | </code> | ||
