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