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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(5)
--!./tcltestrunner.lua
-- 2005 August 18
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
-- May you do good and not evil.
-- May you find forgiveness for yourself and forgive others.
-- May you share freely, never taking more than you give.
--
-------------------------------------------------------------------------
-- This file implements regression tests for sql library.
--
-- This file implements tests to increase coverage of trigger.c.
--
-- $Id: trigger7.test,v 1.3 2008/08/11 18:44:58 drh Exp $
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]
-- Error messages resulting from qualified trigger names.
--
local function arr_match(array, str)
for _, v in ipairs(array) do
if type(v) == 'string' and string.find(v, str) then
return 1
end
end
return 0
end
-- When the UPDATE OF syntax is used, no code is generated for triggers
-- that do not match the update columns.
--
test:do_test(
"trigger7-2.1",
function()
test:execsql [[
CREATE TABLE t1(x INT UNIQUE, y INT, z INT PRIMARY KEY AUTOINCREMENT);
CREATE TRIGGER r1 AFTER UPDATE OF x ON t1 FOR EACH ROW BEGIN
SELECT '___update_t1.x___';
END;
CREATE TRIGGER r2 AFTER UPDATE OF y ON t1 FOR EACH ROW BEGIN
SELECT '___update_t1.y___';
END;
]]
txt = test:execsql "EXPLAIN UPDATE t1 SET x=5"
return arr_match(txt, "___update_t1.x___")
-- return X(63, "X!cmd", [=[["string","match","*___update_t1.x___*",["txt"]]]=])
end,
-- <trigger7-2.1>
1
-- </trigger7-2.1>
)
test:do_test(
"trigger7-2.2",
function()
txt = test:execsql "EXPLAIN UPDATE t1 SET x=5"
return arr_match(txt, "___update_t1.y___")
-- return X(67, "X!cmd", [=[["string","match","*___update_t1.y___*",["txt"]]]=])
end,
-- <trigger7-2.2>
0
-- </trigger7-2.2>
)
test:do_test(
"trigger7-2.3",
function()
txt = test:execsql "EXPLAIN UPDATE t1 SET y=5"
return arr_match(txt, "___update_t1.x___")
-- return X(71, "X!cmd", [=[["string","match","*___update_t1.x___*",["txt"]]]=])
end,
-- <trigger7-2.3>
0
-- </trigger7-2.3>
)
test:do_test(
"trigger7-2.4",
function()
txt = test:execsql "EXPLAIN UPDATE t1 SET y=5"
return arr_match(txt, "___update_t1.y___")
-- return X(75, "X!cmd", [=[["string","match","*___update_t1.y___*",["txt"]]]=])
end,
-- <trigger7-2.4>
1
-- </trigger7-2.4>
)
-- do_test trigger7-2.5 {
-- set txt [db eval {EXPLAIN UPDATE t1 SET =10}]
-- string match *___update_t1.x___* $txt
-- } 0
-- do_test trigger7-2.6 {
-- set txt [db eval {EXPLAIN UPDATE t1 SET x=5}]
-- string match *___update_t1.x___* $txt
-- } 0
-- Test the ability to create many triggers on the same table, then
-- selectively drop those triggers.
--
test:do_execsql_test(
"trigger7-3.1",
[[
CREATE TABLE t2(x INT PRIMARY KEY,y INT,z INT);
CREATE TRIGGER t2r1 AFTER INSERT ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r2 BEFORE INSERT ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r3 AFTER UPDATE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r4 BEFORE UPDATE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r5 AFTER DELETE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r6 BEFORE DELETE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r7 AFTER INSERT ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r8 BEFORE INSERT ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r9 AFTER UPDATE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r10 BEFORE UPDATE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r11 AFTER DELETE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
CREATE TRIGGER t2r12 BEFORE DELETE ON t2 FOR EACH ROW BEGIN SELECT 1; END;
DROP TRIGGER t2r6;
]], {
-- <trigger7-3.1>
-- </trigger7-3.1>
})
-- # This test corrupts the database file so it must be the last test
-- # in the series.
-- #
-- do_test trigger7-99.1 {
-- execsql {
-- UPDATE sql_master SET sql='nonsense';
-- }
-- db close
-- catch { sql db test.db }
-- catchsql { DROP TRIGGER t2r5 }
-- } {1 {malformed database schema (t2r12)}}
test:finish_test()
|
