SQLite Forum

can we use a for/while loop inside sqlite triggers ?
Login

can we use a for/while loop inside sqlite triggers ?

(1.1) By lokakit (loki1993) on 2021-06-15 06:21:11 edited from 1.0 [link] [source]

can we use a for/while loop inside sqlite triggers without sqlite trigger recursion ?

i was able to do looping via "PRAGMA recursive-triggers = true" but its less efficient than simple for loop in cpp code, so is there any for/while loop in sqlite trigger

CREATE TRIGGER less_den_ten_mb_after_insert AFTER INSERT ON mqtt6 when (SELECT sum(size_t)>10240000 from mqtt6) BEGIN DELETE FROM mqtt6 WHERE timestamp=(select min(timestamp) from mqtt6); end;

CREATE TRIGGER less_den_ten_mb_delete after DELETE on mqtt6 when (SELECT sum(size_t)>10240000 from mqtt6) BEGIN DELETE from mqtt6 WHERE timestamp=(SELECT min(timestamp) from mqtt6); END;

PRAGMA recursive_triggers = 1;

(2) By anonymous on 2021-06-15 10:48:13 in reply to 1.1 [link] [source]

And this is a fine example of the kind of Heath Robinson contraption that has to be used because SQLite does not support stored procedures.

Anyone still wanting to stick to "nobody needs stored procedures"?

I do need them and looking at this others do to.

(3) By anonymous on 2021-06-16 06:27:16 in reply to 2 [link] [source]

I agree that while loops would be good to have, since using recursion instead would be a bad idea.

Another thing is some way to produce results out of triggers on views. The existing design doesn't really support this, but perhaps an idea can be: Add a CALL statement which has a similar syntax to INSERT INTO (only replacing the words INSERT INTO with CALL, and disallowing a RETURNING clause), which is only allowed on views, not on real tables. However, different statements in a trigger might have different numbers of result columns, and there may need a way to specify the column names of the result.

Note that for some purposes involving needing results, an extension can be used (mentioned recently on SQLite forum). However, that has an optimization barrier, and cannot be used when you want the other features of triggers (including the ability to insert multiple rows at once and have the triggers fire for each one, executing multiple statements including writes). However, it is good for table-valued functions; you can use recursive CTEs to make many kinds of computations and this will work.

I think these are really two different things, anyways; there is no need to combine them (and cannot meaningfully be combined, unless they later allow RETURNING clauses where a SELECT statement is expected; in that case it would make sense to allow CALL too).

(4) By anonymous on 2021-06-16 08:34:42 in reply to 1.1 [link] [source]

Let's do something naughty and not supported (but it does do the job!)
Here is what I do in a trigger to parse a string before I check the IP-address:

    INSERT INTO _INET_
        WITH RECURSIVE SPLIT(IP_ADDRESS, LAST_OCTET, REMAINDER) AS (
            VALUES ('', '', NEW.DCS_URI)
            UNION ALL
            SELECT CASE
                WHEN LAST_OCTET == '.'
                THEN
                    SUBSTR(REMAINDER, 1, 1)
                ELSE
                    IP_ADDRESS || SUBSTR(REMAINDER, 1, 1)
                END,
                SUBSTR(REMAINDER, 1, 1),
                SUBSTR(REMAINDER, 2)
      FROM SPLIT
      WHERE REMAINDER <> ''
    )
    SELECT
           REPLACE(IP_ADDRESS, '.','') AS 'OCTET'
    FROM
           SPLIT
    WHERE
           LAST_OCTET == '.' OR REMAINDER =='' ;

(5) By Simon Slavin (slavin) on 2021-06-16 12:07:30 in reply to 1.1 [link] [source]

I don't understand why you need trigger recursion for this. It is possible to delete all the rows you want to delete in one command. There's no need to do it one by one.

(6) By anonymous on 2021-06-16 12:28:37 in reply to 5 [link] [source]

Hmm. Here is the complete code

CREATE TRIGGER IF NOT EXISTS DCS_UPDATE_URI BEFORE UPDATE OF DCS_URI ON DEVICE_COM_SETTINGS WHEN FCS(23) --WHEN ((SELECT F_FLAG FROM FLAGS WHERE F_NAME == 'SIGNAL_FLAG_03') == 2) BEGIN

-- SELECT RAISE (ABORT, 'Table DEVICE_COM_SETTINGS ((INSERT MASTER / STATND-ALONE): ERROR-96: The URI is malformed') -- WHERE (NEW.DDS_URI REGEXP '^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}');

DELETE FROM _INET_;
INSERT INTO _INET_
    WITH RECURSIVE SPLIT(IP_ADDRESS, LAST_OCTET, REMAINDER) AS (
        VALUES ('', '', NEW.DCS_URI)
        UNION ALL
        SELECT CASE
            WHEN LAST_OCTET == '.'
            THEN
                SUBSTR(REMAINDER, 1, 1)
            ELSE
                IP_ADDRESS || SUBSTR(REMAINDER, 1, 1)
            END,
            SUBSTR(REMAINDER, 1, 1),
            SUBSTR(REMAINDER, 2)
  FROM SPLIT
  WHERE REMAINDER <> ''
)
SELECT
       REPLACE(IP_ADDRESS, '.','') AS 'OCTET'
FROM
       SPLIT
WHERE
       LAST_OCTET == '.' OR REMAINDER =='' ;

-- Debug

-- INSERT INTO Variables (NAME, VALUE)
-- VALUES -- ('OCTET_1', (SELECT OCTET FROM _INET
WHERE ROWID == 1)), -- ('OCTET_2', (SELECT OCTET FROM INET WHERE ROWID == 2)), -- ('OCTET_3', (SELECT OCTET FROM INET WHERE ROWID == 3)), -- ('OCTET_4', (SELECT OCTET FROM INET WHERE ROWID == 4));

SELECT CASE
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 1) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 1) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-07: The first octet of the URI is invalid')
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 2) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 2) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-08: The second octet of the URI is invalid')
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 3) < 0) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 3) > 255)) THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-09: The third octet of the URI is invalid')
    WHEN (((SELECT OCTET FROM _INET_ WHERE ROWID == 4) < 1) OR ((SELECT OCTET FROM _INET_ WHERE ROWID == 4) > 253)) AND ((SELECT SUM(OCTET) FROM _INET_) NOT IN (0, 1020))
        THEN  RAISE (FAIL, 'Table DIR_SETTINGS (UPDATE URI): ERROR-80-10: The URI is invalid')
END;

-- Prevent and endless recursive loop

-- UPDATE FLAGS SET -- F_FLAG = 1 -- WHERE F_NAME == 'SIGNAL_FLAG_03';

-- Update the URI
UPDATE DEVICE_COM_SETTINGS SET
    DCS_URI = (SELECT OCTET FROM _INET_ WHERE ROWID == 1)
    WHERE DCS_PK_SERIAL_NUMBER == NEW.DCS_PK_SERIAL_NUMBER;

-- CLEAR the flag   

-- UPDATE FLAGS SET -- F_FLAG = 2 -- WHERE F_NAME == 'SIGNAL_FLAG_03'; SELECT CF(23);

END;

(7.1) By Bernardo Ramos (kroggen) on 2021-06-17 21:17:15 edited from 7.0 in reply to 5 [link] [source]

Hi Simon,

I was thinking that this could be implemented, but I don't know how.

I imagine creating a dynamic column containing the sum of the "size_t" starting from the older row (reverse order)

But how to create this dynamic column that aggregates data from other rows? Using a virtual table?

I am really curious about this

-- EDIT --

OK, it appears that using window functions (SUM OVER) could help, as described here:

https://stackoverflow.com/a/58339386/4626775

(8.1) By Keith Medcalf (kmedcalf) on 2021-06-18 02:28:05 edited from 8.0 in reply to 1.1 [source]

How about something like:

create trigger less_den_ten_mb_delete after insert on mqtt6 when (select sum(size_t) from mqtt6) > 1024000
begin
  delete from mqtt6
        where ts <= (
                       select ts 
                         from (
                               select ts, 
                                      sum(size_t) over (order by ts desc) as size
                                 from mqtt6
                              ) 
                        where size >= 1024000 
                        limit 1
                    );
end;

and get rid of the delete trigger. You can probably do away with the when clause since if size >= 1024000 is never met then the delete will do nothing and assuming that you have an covering index on (ts, size_t) it will be very fast.

(9) By Keith Medcalf (kmedcalf) on 2021-06-18 02:58:43 in reply to 8.1 [link] [source]

SQLite version 3.36.0 2021-06-15 11:18:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> create table mqtt6
   ...> (
   ...>     ts integer not null,
   ...>     size_t integer not null
   ...> );
sqlite> create index mqtt6_ts_size_t on mqtt6 (ts, size_t);
sqlite> insert into mqtt6 select value, RandomV(1024) + 1 from generate_series where start == 1 and stop == 1000000;
sqlite> create trigger less_den_ten_mb_delete after insert on mqtt6 when (select sum(size_t) from mqtt6) > 1024000
   ...> begin
   ...>   delete from mqtt6
   ...>         where ts <= (
   ...>                        select ts
   ...>                          from (
   ...>                                select ts,
   ...>                                       sum(size_t) over (order by ts desc) as size
   ...>                                  from mqtt6
   ...>                               )
   ...>                         where size >= 1024000
   ...>                         limit 1
   ...>                     );
   ...> end;
sqlite> .timer on
sqlite> .eqp on
sqlite>
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 1000000  │ 512366354   │ 1       │ 1000000 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.147 user 0.140625 sys 0.000000
sqlite> insert into mqtt6 values (1000001, RandomV(1024)+1);
Run Time: real 0.504 user 0.500000 sys 0.015625
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023824     │ 997986  │ 1000001 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.004 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000002, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023316     │ 997987  │ 1000002 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000003, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2015     │ 1023479     │ 997989  │ 1000003 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000004, RandomV(1024)+1);
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023841     │ 997989  │ 1000004 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000005, RandomV(1024)+1);
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023939     │ 997989  │ 1000005 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000006, RandomV(1024)+1);
Run Time: real 0.002 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023846     │ 997990  │ 1000006 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000007, RandomV(1024)+1);
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2017     │ 1023853     │ 997991  │ 1000007 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000008, RandomV(1024)+1);
Run Time: real 0.001 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023964     │ 997993  │ 1000008 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.006 user 0.000000 sys 0.000000
sqlite> insert into mqtt6 values (1000009, RandomV(1024)+1);
Run Time: real 0.002 user 0.015625 sys 0.000000
sqlite> select count(*), sum(size_t), min(ts), max(ts) from mqtt6;
QUERY PLAN
`--SCAN mqtt6 (~1048576 rows)
┌──────────┬─────────────┬─────────┬─────────┐
│ count(*) │ sum(size_t) │ min(ts) │ max(ts) │
├──────────┼─────────────┼─────────┼─────────┤
│ 2016     │ 1023726     │ 997994  │ 1000009 │
└──────────┴─────────────┴─────────┴─────────┘
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite>

(10) By Bernardo Ramos (kroggen) on 2021-06-18 03:47:16 in reply to 9 [link] [source]

Beautiful solution!

Maybe we can remove the WHEN clause from the trigger to make it do a single scan on the table instead of 2

I suppose that the size_t column is on the index to make the engine use only the index b-tree on the SUM OVER sub-query, not needing to read the table b-tree. Only the DELETE would touch the original table

(11) By Keith Medcalf (kmedcalf) on 2021-06-18 05:53:05 in reply to 10 [link] [source]

Yes, exactly.

Note that the select ts, sum(size_t) over (order by ts desc from mqtt6 will output the ts and sum so far obtained in descending order by ts (and ascending sum). This is a subquery with a limit 1 and a condition on the sum(size_t) so the summing will run until it hits the first row (going backwards in time) where the size_t sum exceeds 1024000 and will return the ts for that row so the delete can delete all the rows less or equal to this ts.

The index on (ts, size_t) serves two purposes. (1) it can be used by the sum ... over process so that only the index is scanned without need to access or process the underlying table. (2) to find all the rows to be deleted without scanning the underlying table (thus rows can be inserted out of order if one wants and it will make no difference to the running time).

You can add an order by to the sum ... over query for the greater certainty, but at the moment it is not required (I suppose this is an implementation detail), that is:

create trigger less_den_ten_mb_delete after insert on mqtt6
begin
  delete from mqtt6
        where ts <= (
                     select ts 
                       from (
                                select ts, 
                                       sum(size_t) over (order by ts desc) as size
                                  from mqtt6
                              order by ts desc
                             ) 
                      where size >= 1024000 
                      limit 1
                    );
end;