SQLite: Workaround for SQLite-TRIGGER with WITH-CodePudding

I'm working on a project to monitor downtimes of production lines with an embedded device. I want to automate acknowledging of these downtimes by generic rules the user can configure. I want to use a TRIGGER but get a syntax error near UPDATE even though the documentation says it should be fine to use the WITH statement.

CREATE TRIGGER autoAcknowledge
    AFTER UPDATE OF dtEnd ON ackGroups
    FOR EACH ROW
    WHEN old.dtEnd IS NULL AND new.dtEnd IS NOT NULL
    BEGIN
        WITH sub1(id, stationId, groupDur) AS (
            SELECT MIN(d.id), d.station,
                strftime('%s', ag.dtEnd) - strftime('%s', ag.dtStart) 
                FROM ackGroups AS ag
                LEFT JOIN downtimes AS d on d.acknowledge = ag.id
                WHERE ag.id = old.id
                GROUP BY ag.id ),
            sub2( originId, groupDur, reasonId, above, ruleDur) AS (
                SELECT sub1.stationId, sub1.groupDur, aar.reasonId, aar.above, aar.duration
                FROM sub1
                LEFT JOIN autoAckStations AS aas ON aas.stationId = sub1.stationId
                LEFT JOIN autoAckRules AS aar ON aas.autoAckRuleId = aar.id
                ORDER BY duration DESC )
        UPDATE ackGroups SET (reason, dtAck, origin)=(
            SELECT reasonId, datetime('now'), originId
            FROM sub2 as s
            WHERE ( s.ruleDur < s.groupDur AND above = 1 ) OR (s.ruleDur > s.groupDur AND above = 0)
            LIMIT 1
        )
        WHERE id = old.id;
    END

Background: First we have the downtimes table. Each production line consists of multiple parts called stations. Each station can start the line downtime and they can overlap with other stations downtimes.

CREATE TABLE "downtimes" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "station" integer NOT NULL, 
    "acknowledge" integer, 
    "dtStart" datetime NOT NULL, 
    "dtEnd" datetime, 
    "dtLastModified" datetime)

Overlaping downtimes are grouped to acknowledge groups using TRIGGER AFTER INSERT on downtimes to set acknowledge id right or create a new group.

CREATE TABLE "ackGroups" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "reason" integer, 
    "dtAck" datetime, 
    "dtStart" datetime NOT NULL, 
    "dtEnd" datetime, 
    "line" integer NOT NULL, 
    "origin" integer)

The autoAckRules table represents the configuration. The user decides whether the rule should apply to durations higher or lower a certain value and which rasonId should be used to acknowledge.

CREATE TABLE "autoAckRules" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
    "description" text NOT NULL, 
    "reasonId" integer NOT NULL, 
    "above" bool NOT NULL, 
    "duration" real NOT NULL)

The autoAckStations table is used to manage M:N relationship. Each rule allow multiple stations which started the ackGroup.

CREATE TABLE autoAckStations (
        autoAckRuleId INTEGER NOT NULL,
        stationId INTEGER NOT NULL,
        PRIMARY KEY ( autoAckRuleId, stationId )
    )

When the last downtime ends dtEnd of ackGroups is set to datetime('now') and the trigger is fired to check if there is a autoAckRule that fits.

If I substitute the sub selects with a SELECT .. FROM( SELECT .. FROM(SELECT .. FROM ))) cascade is there a nice way to avoid the need to write and evaluate it twice?

Or am I missing something stupid?

CodePudding user response：

Common table expression are not supported for statements inside of triggers. You need to convert CTE to sub-query such as

CREATE TRIGGER autoAcknowledge
AFTER UPDATE OF dtEnd ON ackGroups
FOR EACH ROW
WHEN old.dtEnd IS NULL AND new.dtEnd IS NOT NULL
BEGIN
    UPDATE ackGroups 
       SET (reason, dtAck, origin)= (
    SELECT reasonId, datetime('now'), originId
      FROM (SELECT sub1.stationId AS originId,
                   sub1.groupDur  AS groupDur, 
                   aar.reasonId   AS reasonId, 
                   aar.above      AS above, 
                   aar.duration   AS ruleDur
             FROM (SELECT MIN(d.id) AS id,
                         d.station AS stationId,
                         strftime('%s', ag.dtEnd) - strftime('%s', ag.dtStart) AS groupDur 
                    FROM ackGroups AS ag
                    LEFT 
                    JOIN downtimes AS d 
                      ON d.acknowledge = ag.id
                   WHERE ag.id = old.id
                   GROUP BY ag.id ) AS sub1
            LEFT 
            JOIN autoAckStations AS aas 
              ON aas.stationId = sub1.stationId
            LEFT 
            JOIN autoAckRules AS aar 
              ON aas.autoAckRuleId = aar.id
           ORDER BY duration DESC) as s
        WHERE ( s.ruleDur < s.groupDur AND above = 1 ) OR (s.ruleDur > s.groupDur AND above = 0)
        LIMIT 1
    );
END;