How to structure the string to create a trigger?

[georgevbsantiago]

Hello, I have a "Silly" question, but I'm stuck on it. I can't create a trigger in SQLite. I believe there is a problem when writing the string. I can create the trigger when using DB Browser, but I can't in Visual Code with C++ code.

In DB Browser successfully:

CREATE TRIGGER IF NOT EXISTS keep_5_rows 
        AFTER INSERT 
        ON tab_datalogger
        WHEN (SELECT COUNT(*) FROM tab_datalogger) > 5
    BEGIN 
        DELETE FROM tab_datalogger 
        WHERE ts = (SELECT ts FROM tab_datalogger ORDER BY ts, ts LIMIT 1);
    END;

In VS without success:

rc = db_exec(db, "CREATE TRIGGER IF NOT EXISTS keep_5_rows AFTER INSERT ON tab_datalogger WHEN (SELECT COUNT(*) FROM tab_datalogger) > 5 BEGIN DELETE FROM tab_datalogger WHERE ts = (SELECT ts FROM tab_datalogger ORDER BY ts, ts LIMIT 1); END;");

Serial Monitor:

17:29:49.481 > CREATE TRIGGER IF NOT EXISTS keep_5_rows AFTER INSERT ON tab_datalogger WHEN (SELECT COUNT(*) FROM tab_datalogger) > 5 BEGIN DELETE FROM tab_datalogger WHERE ts = (SELECT ts FROM tab_datalogger ORDER BY ts, ts LIMIT 1); END;
17:29:49.503 > SQL error: parser stack overflow

Any help would be greatly appreciated as I've been stuck on this for a few hours.

[georgevbsantiago]

Apparently, it was resolved by changing YYSTACKDEPTHin config_ext.h, from 20 to 30, as suggested in the post below. Many people are encountering the error:"SQL error: parser stack overflow" and apparently the solution is to change the value of YYSTACKDEPTHin config_ext.h.

@siara-cc Why was the value 20 assigned?

I got the answer to this issue:

[siara-cc]

@georgevbsantiago Thank you for posting this. According to SQLite documentation, 20 is supposed to be sufficient:

YYSTACKDEPTH=

This macro sets the maximum depth of the LALR(1) stack used by the SQL parser within SQLite. The default value is 100. A typical application will use less than about 20 levels of the stack. Developers whose applications contain SQL statements that need more than 100 LALR(1) stack entries should seriously consider refactoring their SQL as it is likely to be well beyond the ability of any human to comprehend.

Since ESP32 has very little RAM, I set it to the minimum value. Good that increasing it works for you, but I am not sure of implications for people who don't need it.

Although not documented, it appears that by setting it to 0 it is dynamically allocated, which might be a good idea. So I am not acting on this until the time it can be tested.