File: gh2250-trigger-chain-limit.test.lua

package info (click to toggle)
tarantool 2.6.0-1.4
  • links: PTS, VCS
  • area: main
  • in suites: sid, trixie
  • size: 85,412 kB
  • sloc: ansic: 513,775; cpp: 69,493; sh: 25,650; python: 19,190; perl: 14,973; makefile: 4,178; yacc: 1,329; sql: 1,074; pascal: 620; ruby: 190; awk: 18; lisp: 7
file content (100 lines) | stat: -rwxr-xr-x 3,664 bytes parent folder | download | duplicates (3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(8)

for _, table_count in ipairs({30, 31}) do
    -- Clean up, create tables, add entries
    for i = 1,table_count do
        -- First table for uniform triggers check
        drop_string = 'DROP TABLE IF EXISTS t' .. i .. ';'
        box.execute(drop_string)

        create_string = 'CREATE TABLE t' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
        box.execute(create_string)

        insert_string = 'INSERT INTO t' .. i .. ' VALUES (0,' .. i .. ', 0);'
        box.execute(insert_string)

        -- Second table for triggers mixture check
        drop_string = 'DROP TABLE IF EXISTS tt' .. i .. ';'
        box.execute(drop_string)

        create_string = 'CREATE TABLE tt' .. i .. ' (s1 INT UNIQUE, s2 INT, s3 INT PRIMARY KEY);'
        box.execute(create_string)

        insert_string = 'INSERT INTO tt' .. i .. ' VALUES (0,' .. i .. ', 0);'
        box.execute(insert_string)
    end

    -- And ON DELETE|UPDATE|INSERT triggers
    for i = 1,table_count-1 do
        create_string = 'CREATE TRIGGER td' .. i
        create_string = create_string .. ' BEFORE DELETE ON t' .. i
            .. ' FOR EACH ROW '
        create_string = create_string .. ' BEGIN DELETE FROM t' .. i+1
            .. '; END'
        box.execute(create_string)

        create_string = 'CREATE TRIGGER tu' .. i
        create_string = create_string .. ' BEFORE UPDATE ON t' .. i
            .. ' FOR EACH ROW '
        create_string = create_string .. ' BEGIN UPDATE t' .. i+1 ..
            ' SET s1=s1+1; END'
        box.execute(create_string)

        create_string = 'CREATE TRIGGER ti' .. i
        create_string = create_string .. ' BEFORE INSERT ON t' .. i
            .. ' FOR EACH ROW '
        create_string = create_string .. ' BEGIN INSERT INTO t' .. i+1
            .. ' (s1) SELECT max(s1)+1 FROM t' .. i+1 .. '; END'
        box.execute(create_string)

        -- Try triggers mixture: DELETE triggers UPDATE, which triggers
        -- INSERT, which triggers DELETE etc.
        create_string = 'CREATE TRIGGER tt' .. i
        if i % 3 == 0 then
            create_string = create_string .. ' BEFORE INSERT ON tt' .. i
                .. ' FOR EACH ROW'
            create_string = create_string .. ' BEGIN DELETE FROM tt' .. i+1
                .. '; END'
        else
            if (i - math.floor(i / 3)) % 2 == 0 then
                create_string = create_string .. ' BEFORE UPDATE ON tt' .. i
                    .. ' FOR EACH ROW'
                create_string = create_string .. ' BEGIN INSERT INTO tt' .. i+1
                    .. ' (s1) SELECT max(s1)+1 FROM tt' .. i+1 .. '; END'
            else
                create_string = create_string .. ' BEFORE DELETE ON tt' .. i
                    .. ' FOR EACH ROW'
                create_string = create_string .. ' BEGIN UPDATE tt' .. i+1 ..
                    ' SET s1=s1+1; END'
            end
        end
        box.execute(create_string)
    end

    function check(sql)
        msg = ''
        local _, msg = pcall(function () test:execsql(sql) end)
        msg = tostring(msg)
        test:do_test(sql,
                     function()
                         return true
                     end,
                     table_count <= 30 or msg == 'Maximum number of chained trigger activations exceeded.')
    end

    -- Exceed check for UPDATE
    check('UPDATE t1 SET s1=2')

    -- Exceed check for INSERT
    check('INSERT INTO t1 (s1) VALUES (3)')

    -- Exceed check for DELETE
    check('DELETE FROM t1')

    -- Exceed check for triggers mixture
    check('DELETE FROM tt1')
end

test:finish_test()