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()
|
