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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(201)
--!./tcltestrunner.lua
-- 2008 April 15
--
-- 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. Specifically,
-- it tests updating tables with constraints within a trigger. Ticket #3055.
--
-- ["set","testdir",[["file","dirname",["argv0"]]]]
-- ["source",[["testdir"],"\/tester.tcl"]]
-- Create test tables with constraints.
--
test:do_execsql_test(
"triggerB-1.1",
[[
CREATE TABLE x(id INTEGER PRIMARY KEY, x INTEGER UNIQUE, y INT NOT NULL);
INSERT INTO x VALUES(1, 1, 1);
INSERT INTO x VALUES(2, 2, 1);
CREATE VIEW vx AS SELECT x, y, 0 AS yy FROM x;
CREATE TRIGGER tx INSTEAD OF UPDATE OF y ON vx
FOR EACH ROW
BEGIN
UPDATE x SET y = new.y WHERE x = new.x;
END;
SELECT * FROM vx;
]], {
-- <triggerB-1.1>
1, 1, 0, 2, 1, 0
-- </triggerB-1.1>
})
-- MUST_WORK_TEST
-- do_test triggerB-1.2 {
-- execsql {
-- UPDATE vx SET y = yy;
-- SELECT * FROM vx;
-- }
-- } {1 0 0 2 0 0}
-- Added 2008-08-22:
--
-- Name resolution within triggers.
--
test:do_catchsql_test(
"triggerB-2.1",
[[
CREATE TRIGGER ty AFTER INSERT ON x FOR EACH ROW
BEGIN
SELECT wen.x; -- Unrecognized name
END;
INSERT INTO x VALUES(3,1,2);
]], {
-- <triggerB-2.1>
1, "Field 'X' was not found in space 'WEN' format"
-- </triggerB-2.1>
})
test:do_catchsql_test(
"triggerB-2.2",
[[
CREATE TRIGGER tz AFTER UPDATE ON x FOR EACH ROW
BEGIN
SELECT dlo.x; -- Unrecognized name
END;
UPDATE x SET y=y+1;
]], {
-- <triggerB-2.2>
1, "Field 'X' was not found in space 'DLO' format"
-- </triggerB-2.2>
})
test:do_test(
"triggerB-2.3",
function()
test:execsql [[
CREATE TABLE t2(id INTEGER PRIMARY KEY, a INTEGER UNIQUE, b INT );
INSERT INTO t2 VALUES(1, 1,2);
CREATE TABLE changes(x INT PRIMARY KEY,y INT );
CREATE TRIGGER r1t2 AFTER UPDATE ON t2 FOR EACH ROW
BEGIN
INSERT INTO changes VALUES(new.a, new.b);
END;
]]
return test:execsql [[
UPDATE t2 SET a=a+10;
SELECT * FROM changes;
]]
end, {
-- <triggerB-2.3>
11, 2
-- </triggerB-2.3>
})
test:do_test(
"triggerB-2.4",
function()
test:execsql [[
CREATE TRIGGER r2t2 AFTER DELETE ON t2 FOR EACH ROW
BEGIN
INSERT INTO changes VALUES(old.a, old.c);
END;
]]
return test:catchsql [[
DELETE FROM t2;
]]
end, {
-- <triggerB-2.4>
1, "Field 'C' was not found in space 'OLD' format"
-- </triggerB-2.4>
})
-- Triggers maintain a mask of columns from the invoking table that are
-- used in the trigger body as NEW.column or OLD.column. That mask is then
-- used to reduce the amount of information that needs to be loaded into
-- the NEW and OLD pseudo-tables at run-time.
--
-- These tests cases check the logic for when there are many columns - more
-- than will fit in a bitmask.
--
test:do_test(
"triggerB-3.1",
function()
test:execsql [[
CREATE TABLE t3(
id INT PRIMARY KEY, c0 TEXT UNIQUE, c1 TEXT , c2 TEXT , c3 TEXT , c4 TEXT , c5 TEXT , c6 TEXT , c7 TEXT ,
c8 TEXT , c9 TEXT , c10 TEXT , c11 TEXT , c12 TEXT , c13 TEXT , c14 TEXT , c15 TEXT , c16 TEXT , c17 TEXT ,
c18 TEXT , c19 TEXT , c20 TEXT , c21 TEXT , c22 TEXT , c23 TEXT , c24 TEXT , c25 TEXT , c26 TEXT , c27 TEXT ,
c28 TEXT , c29 TEXT , c30 TEXT , c31 TEXT , c32 TEXT , c33 TEXT , c34 TEXT , c35 TEXT , c36 TEXT , c37 TEXT ,
c38 TEXT , c39 TEXT , c40 TEXT , c41 TEXT , c42 TEXT , c43 TEXT , c44 TEXT , c45 TEXT , c46 TEXT , c47 TEXT ,
c48 TEXT , c49 TEXT , c50 TEXT , c51 TEXT , c52 TEXT , c53 TEXT , c54 TEXT , c55 TEXT , c56 TEXT , c57 TEXT ,
c58 TEXT , c59 TEXT , c60 TEXT , c61 TEXT , c62 TEXT , c63 TEXT , c64 TEXT , c65 TEXT
);
CREATE TABLE t3_changes(colnum INT PRIMARY KEY, oldval TEXT , newval TEXT );
INSERT INTO t3 VALUES(
0, 'a0', 'a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9',
'a10','a11','a12','a13','a14','a15','a16','a17','a18','a19',
'a20','a21','a22','a23','a24','a25','a26','a27','a28','a29',
'a30','a31','a32','a33','a34','a35','a36','a37','a38','a39',
'a40','a41','a42','a43','a44','a45','a46','a47','a48','a49',
'a50','a51','a52','a53','a54','a55','a56','a57','a58','a59',
'a60','a61','a62','a63','a64','a65'
);
]]
-- for _ in X(0, "X!for", [=[["set i 0","$i<=65","incr i"]]=]) do
for i=0,65 do
sql = string.format([[
CREATE TRIGGER t3c%s AFTER UPDATE ON t3
FOR EACH ROW
WHEN old.c%s!=new.c%s
BEGIN
INSERT INTO t3_changes VALUES(%s, old.c%s, new.c%s);
END
]], i, i, i, i, i, i)
test:execsql(sql)
end
return test:execsql [[
SELECT * FROM t3_changes
]]
end, {
-- <triggerB-3.1>
-- </triggerB-3.1>
})
-- for _ in X(0, "X!for", [=[["set i 0","$i<=64","incr i"]]=]) do
for i=0,64 do
-- X(139, "X!cmd", [=[["do_test",["triggerB-3.2.",["i"],".1"],["\n execsql {\n UPDATE t3 SET c",["i"],"='b",["i"],"';\n SELECT * FROM t3_changes ORDER BY colnum DESC LIMIT 1;\n }\n "],[["i"]," a",["i"]," b",["i"]]]]=])
test:do_execsql_test("triggerB-3.2."..i..".1",
string.format([[UPDATE t3 SET c%d='b%d';
SELECT * FROM t3_changes ORDER BY colnum DESC LIMIT 1; ]],
i, i),
{i, string.format("a%d", i), string.format("b%d", i)})
test:do_execsql_test(
"triggerB-3.2."..i..".2",
[[
SELECT count(*) FROM t3_changes
]], {
(i + 1)
})
-- X(150, "X!cmd", [=[["do_test",["triggerB-3.2.",["i"],".2"],["\n execsql {\n SELECT * FROM t3_changes WHERE colnum=",["i"],"\n }\n "],[["i"]," a",["i"]," b",["i"]]]]=])
test:do_execsql_test("triggerB-3.2."..i..".2",
string.format([[SELECT * FROM t3_changes WHERE colnum=%d]], i),
{i, string.format("a%d", i), string.format("b%d", i)})
end
test:finish_test()
|
