Различия

Здесь показаны различия между двумя версиями данной страницы.

--- doc:jroboplc:modules:motohr_db [2025/02/23 16:27]
denis
+++ doc:jroboplc:modules:motohr_db [2025/03/21 13:38] (текущий)
denis
@@ Строка 1: / Строка 1: @@
 <code sql>
 /******************************************************************************/
-/***          Generated by IBExpert 2022.3.4.1 23.02.2025 20:26:11          ***/
+/***          Generated by IBExpert 2022.3.4.1 21.03.2025 17:38:08          ***/
 /******************************************************************************/
@@ Строка 18: / Строка 18: @@
 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 ; ^
@@ Строка 31: / Строка 59: @@
     MACH_ID          INTEGER,
     COUNTER_TYPE_ID  INTEGER,
-    SEC              INTEGER,
+    SEC              INTEGER DEFAULT 2147483647,
     DELETED          SMALLINT DEFAULT 0
 );
@@ Строка 37: / Строка 65: @@
 CREATE TABLE MH_COUNTER_TYPE (
     ID       INTEGER GENERATED BY DEFAULT AS IDENTITY,
-    NAME     VARCHAR(64) NOT NULL,
     DESCR    VARCHAR(128),
     TIMER    SMALLINT DEFAULT 0,
@@ Строка 49: / Строка 76: @@
     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)
 );
@@ Строка 94: / Строка 129: @@
     MACH_DESCR,
     RUNNING,
-    COUNTER_NAME,
     COUNTER_DESCR,
     TIMER,
@@ Строка 106: / Строка 140: @@
   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
+  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
@@ Строка 117: / Строка 150: @@
   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
 -- 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;
@@ Строка 133: / Строка 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);
@@ Строка 144: / Строка 192: @@
 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);
@@ Строка 156: / Строка 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_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;
@@ Строка 166: / Строка 216: @@
 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 ; ^
 </code>