~drizzle-trunk/drizzle/development

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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
#======================================================================
#
# Trigger Tests 
# (test case numbering refer to requirement document TP v1.1) 
#======================================================================
# WL#4084: enable disabled parts, 2007-11-15 hhunger

# General setup for Trigger tests
let $message= Testcase: 3.5:;
--source include/show_msg.inc

--disable_abort_on_error

	create User test_general@localhost;
	set password for test_general@localhost = password('PWD');
	revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;

	create User test_super@localhost;
	set password for test_super@localhost = password('PWD');
	grant ALL on *.* to test_super@localhost with grant OPTION;
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (con2_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
	connect (con2_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
	connection default;

#################################
####### Section 3.5.8 ###########
# Checks on Triggered Actions   #
#################################

#Section 3.5.8.1
# Testcase: Ensure that the triggered action of every trigger always executes 
#           correctly and the results in all expected changes made to the database
let $message= Testcase 3.5.8.1: (implied in previous tests);
--source include/show_msg.inc

#Section 3.5.8.2
# Testcase: Ensure that the triggered actions of every trigger never results 
#           in an unexpected change made to the database.
let $message= Testcase 3.5.8.2: (implied in previous tests);
--source include/show_msg.inc


#Section 3.5.8.3 / 3.5.8.4
#Test case: Ensure that the triggered action can any valid SQL statement / set 
#           of valid SQL statements, provided the statements are written within
#           a BEGIN/END compound statement construct
# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements
#       as there are the most likely to be used in triggers
let $message= Testcase 3.5.8.3/4:;
--source include/show_msg.inc

# creating test tables to perform the trigger SQL on
	connection con2_super;
	create database db_test;
	grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
	grant LOCK TABLES on db_test.* to test_general;
	Use db_test;
	eval create table t1_i ( 
		i120 char ascii not null DEFAULT b'101',
		i136 smallint zerofill not null DEFAULT 999,
		i144 int zerofill not null DEFAULT 99999,
		i163 decimal (63,30)) engine=$engine_type;
	eval create table t1_u ( 
		u120 char ascii not null DEFAULT b'101',
		u136 smallint zerofill not null DEFAULT 999,
		u144 int zerofill not null DEFAULT 99999,
		u163 decimal (63,30)) engine=$engine_type;
	eval create table t1_d ( 
		d120 char ascii not null DEFAULT b'101',
		d136 smallint zerofill not null DEFAULT 999,
		d144 int zerofill not null DEFAULT 99999,
		d163 decimal (63,30)) engine=$engine_type;
	Insert into t1_u values ('a',111,99999,999.99);
	Insert into t1_u values ('b',222,99999,999.99);
	Insert into t1_u values ('c',333,99999,999.99);
	Insert into t1_u values ('d',222,99999,999.99);
	Insert into t1_u values ('e',222,99999,999.99);
	Insert into t1_u values ('f',333,99999,999.99);
	Insert into t1_d values ('a',111,99999,999.99);
	Insert into t1_d values ('b',222,99999,999.99);
	Insert into t1_d values ('c',333,99999,999.99);
	Insert into t1_d values ('d',444,99999,999.99);
	Insert into t1_d values ('e',222,99999,999.99);
	Insert into t1_d values ('f',222,99999,999.99);

let $message= 3.5.8.4 - multiple SQL;
--source include/show_msg.inc
# Trigger definition - multiple SQL
	use test;
	delimiter //;
	Create trigger trg1 AFTER INSERT on tb3 for each row
	BEGIN
		insert into db_test.t1_i 
			values (new.f120, new.f136, new.f144, new.f163);
		update db_test.t1_u 
			set u144=new.f144, u163=new.f163
			where u136=new.f136; 
		delete from db_test.t1_d where d136= new.f136;
		select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u 
			where u136= new.f136; 
	END// 
	delimiter ;//

# Test trigger execution - multiple SQL
	connection con2_general;
	Use test;
	set @test_var=0;
	Insert into tb3 (f120, f122, f136, f144, f163) 
		values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
	Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
	--sorted_result
	select * from db_test.t1_i;
	--sorted_result
	select * from db_test.t1_u;
	--sorted_result
	select * from db_test.t1_d;
	select @test_var;


let $message= 3.5.8.4 - single SQL - insert;
--source include/show_msg.inc
# Trigger definition - single SQL Insert
	connection con2_super;
        delimiter //;
	Create trigger trg2 BEFORE UPDATE on tb3 for each row
        BEGIN
		insert into db_test.t1_i 
			values (new.f120, new.f136, new.f144, new.f163);
        END//
        delimiter ;//

# Trigger exeution - single SQL Insert
	connection con2_general;
        Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
        select * from db_test.t1_i order by i120;
	update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
		 where f122='Test 3.5.8.4';
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select * from db_test.t1_i order by i120;


let $message= 3.5.8.4 - single SQL - update;
--source include/show_msg.inc
# Trigger definition - single SQL update
	connection con2_super;
	drop trigger trg2;
	Create trigger trg3 BEFORE UPDATE on tb3 for each row
		update db_test.t1_u 
			set u120=new.f120
			where u136=new.f136; 

# Trigger exeution - single SQL  - update;
	connection con2_general;
	update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
		 where f122='Test 3.5.8.4-Single Insert';
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select * from db_test.t1_u order by u120;


let $message= 3.5.8.3/4 - single SQL - delete;
--source include/show_msg.inc
# Trigger definition - single SQL delete
	connection con2_super;
	drop trigger trg3;
	Create trigger trg4 AFTER UPDATE on tb3 for each row
		delete from db_test.t1_d where d136= new.f136;

# Trigger exeution - single SQL delete
	connection con2_general;
#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write;
	update tb3 set f120='D', f136=444, 
		f122='Test 3.5.8.4-Single Delete'
		 where f122='Test 3.5.8.4-Single Update';
#unlock tables;
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select * from db_test.t1_d order by d120;


let $message= 3.5.8.3/4 - single SQL - select;
--source include/show_msg.inc
# Trigger definition - single SQL select
	connection con2_super;
	drop trigger trg4;
	Create trigger trg5 AFTER UPDATE on tb3 for each row
		select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u 
			where u136= new.f136; 

# Trigger exeution - single SQL select
	connection con2_general;
	set @test_var=0;
	update tb3 set f120='S', f136=111, 
		f122='Test 3.5.8.4-Single Select'
		 where f122='Test 3.5.8.4-Single Delete';
	Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
	select @test_var;

#Cleanup
	connection default;
	--disable_warnings 
	drop trigger trg1;
	drop trigger trg5;
	drop database if exists db_test;
	delete from tb3 where f122 like 'Test 3.5.8.4%';
	revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
	--enable_warnings


#Section 3.5.8.5 (IF)
# Test case: Ensure that the stored procedure-specific flow control statement like IF 
#            works correctly when it is a part of the triggered action portion of a 
#            trigger definition.
let $message= Testcase 3.5.8.5 (IF):;
--source include/show_msg.inc

	delimiter //;
	create trigger trg2 before insert on tb3 for each row
	BEGIN
		IF new.f120='1' then
			set @test_var='one', new.f120='2';
		ELSEIF new.f120='2' then
			set @test_var='two', new.f120='3';
		ELSEIF new.f120='3' then
			set @test_var='three', new.f120='4';
		END IF;

		IF (new.f120='4') and (new.f136=10) then
			set @test_var2='2nd if', new.f120='d';
		ELSE 
			set @test_var2='2nd else', new.f120='D';
		END IF;
	END//
	delimiter ;//

	set @test_var='Empty', @test_var2=0;
	Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
	Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
	Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;
	Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
	select f120, f122, f136, @test_var, @test_var2 
		from tb3 where f122 = 'Test 3.5.8.5-if' order by f136;

	delimiter //;
	--error 1064
	create trigger trg3 before update on tb3 for each row
	BEGIN
		ELSEIF new.f120='2' then
		END IF;
	END//
	--error 0, 1360
	drop trigger trg3//

	--error 1064
	create trigger trg4 before update on tb3 for each row
	BEGIN
		IF (new.f120='4') and (new.f136=10) then
			set @test_var2='2nd if', new.f120='d';
		ELSE 
			set @test_var2='2nd else', new.f120='D';
	END//
	delimiter ;//
	--error 0, 1360
	drop trigger trg4;

#Cleanup
	--disable_warnings 
	drop trigger trg2;
	delete from tb3 where f121='Test 3.5.8.5-if';	
	--enable_warnings


#Section 3.5.8.5 (CASE)
# Test case: Ensure that the stored procedure-specific flow control statement 
#            like CASE works correctly when it is a part of the triggered action 
#            portion of a trigger definition.
let $message= Testcase 3.5.8.5-case:;
--source include/show_msg.inc
	
	delimiter //;
	create trigger trg3 before insert on tb3 for each row
	BEGIN
		SET new.f120=char(ascii(new.f120)-32);
		CASE
			when new.f136<100 then set new.f136=new.f136+120;
			when new.f136<10 then set new.f144=777;
			when new.f136>100 then set new.f120=new.f136-1;
		END case;
		CASE
			when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
			ELSE set @test_var=concat(new.f120, '*');
		END case;
		CASE new.f144
			when 1 then set @test_var=concat(@test_var, 'one');
			when 2 then set @test_var=concat(@test_var, 'two');
			when 3 then set @test_var=concat(@test_var, 'three');
			when 4 then set @test_var=concat(@test_var, 'four');
			when 5 then set @test_var=concat(@test_var, 'five');
			when 6 then set @test_var=concat(@test_var, 'six');
			when 7 then set @test_var=concat(@test_var, 'seven');
			when 8 then set @test_var=concat(@test_var, 'eight');
			when 9 then set @test_var=concat(@test_var, 'nine');
			when 10 then set @test_var=concat(@test_var, 'ten');
			when 11 then set @test_var=concat(@test_var, 'eleven');
			when 12 then set @test_var=concat(@test_var, 'twelve');
			when 13 then set @test_var=concat(@test_var, 'thirteen');
			when 14 then set @test_var=concat(@test_var, 'fourteen');
			when 15 then set @test_var=concat(@test_var, 'fifteen');
			ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
		END case;
	END//
	delimiter ;//

	set @test_var='Empty';
	Insert into tb3 (f120, f122, f136, f144) 
		values ('a', 'Test 3.5.8.5-case', 5, 7);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
	Insert into tb3 (f120, f122, f136, f144) 
		values ('b', 'Test 3.5.8.5-case', 71,16);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
	Insert into tb3 (f120, f122, f136, f144) 
		values ('c', 'Test 3.5.8.5-case', 80,1);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
	Insert into tb3 (f120, f122, f136) 
		values ('d', 'Test 3.5.8.5-case', 152);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
	Insert into tb3 (f120, f122, f136, f144) 
		values ('e', 'Test 3.5.8.5-case', 200, 8);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;
	--error 0, 1339
	Insert into tb3 (f120, f122, f136, f144) 
		values ('f', 'Test 3.5.8.5-case', 100, 8);
	select f120, f122, f136, f144, @test_var 
		from tb3 where f122 = 'Test 3.5.8.5-case' order by f120,f136;

	delimiter //;
	--error 1064
	create trigger trg3a before update on tb3 for each row
	BEGIN
		CASE
			when new.f136<100 then set new.f120='p';
	END//
	delimiter ;//

	--error 0, 1360
	drop trigger trg3a;

#Cleanup
	--disable_warnings 
	drop trigger trg3;
	delete from tb3 where f121='Test 3.5.8.5-case';	
	--enable_warnings

#Section 3.5.8.5 (LOOP)
# Test case: Ensure that the stored procedure-specific flow control
#            statement like LOOP / LEAVE work correctly when they are
#            part of the triggered action portion of a trigger definition.
let $message= Testcase 3.5.8.5-loop/leave:;
--source include/show_msg.inc
		
	delimiter //;
	Create trigger trg4 after insert on tb3 for each row
	BEGIN 
		set @counter=0, @flag='Initial';
		Label1: loop 
			if new.f136<new.f144 then
				set @counter='Nothing to loop';
				leave Label1; 
			else
				set @counter=@counter+1;
				if new.f136=new.f144+@counter then
					set @counter=concat(@counter, ' loops');
					leave Label1;
				end if; 
			end if; 
			iterate label1; 
			set @flag='Final';
		END loop Label1; 
	END//
	delimiter ;//
	Insert into tb3 (f122, f136, f144) 
		values ('Test 3.5.8.5-loop', 2, 8);
		select @counter, @flag;
	Insert into tb3 (f122, f136, f144) 
		values ('Test 3.5.8.5-loop', 11, 8);
		select @counter, @flag;


	delimiter //;

	--error 1064
	Create trigger trg4_2 after update on tb3 for each row
	BEGIN 
		Label1: loop 
			set @counter=@counter+1;
		END;  
	END//
	delimiter ;//
	--error 0, 1360
	drop trigger trg4_2;

#Cleanup
	--disable_warnings 
	drop trigger trg4;
	delete from tb3 where f122='Test 3.5.8.5-loop';
	--enable_warnings

#Section 3.5.8.5 (REPEAT ITERATE)
#Testcase: Ensure that the stored procedure-specific flow control statements 
#          like REPEAT work correctly when they are part of the triggered action
#          portion of a trigger definition.
let $message= Testcase 3.5.8.5-repeat:;
--source include/show_msg.inc
			
	delimiter //;
	Create trigger trg6 after insert on tb3 for each row
	BEGIN
		rp_label: REPEAT 
			SET @counter1 = @counter1 + 1; 
			IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label; 	
			END IF;
			SET @counter2 = @counter2 + 1; 
		UNTIL @counter1> new.f136 END REPEAT rp_label;
	END//
	delimiter ;//

	set @counter1= 0, @counter2= 0;
	Insert into tb3 (f122, f136) 
		values ('Test 3.5.8.5-repeat', 13);
	select @counter1, @counter2;

			
	delimiter //;
	--error 1064 
	Create trigger trg6_2 after update on tb3 for each row
	BEGIN
		REPEAT 
			SET @counter2 = @counter2 + 1; 
	END//
	delimiter ;//

#Cleanup			
	--disable_warnings 
	drop trigger trg6;
	delete from tb3 where f122='Test 3.5.8.5-repeat';
	--enable_warnings


#Section 3.5.8.5 (WHILE)
# Test case: Ensure that the stored procedure-specific flow control 
#            statements WHILE, work correctly when they are part of 
#            the triggered action portion of a trigger definition.
let $message= Testcase 3.5.8.5-while:;
--source include/show_msg.inc

	delimiter //;
	Create trigger trg7 after insert on tb3 for each row
		wl_label: WHILE @counter1 < new.f136 DO 
			SET @counter1 = @counter1 + 1; 
			IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label; 	
			END IF;
			SET @counter2 = @counter2 + 1; 
		END WHILE wl_label//
	delimiter ;//

	set @counter1= 0, @counter2= 0;
	Insert into tb3 (f122, f136) 
		values ('Test 3.5.8.5-while', 7);
	select @counter1, @counter2;
	delimiter //;
	--error 1064 
	Create trigger trg7_2 after update on tb3 for each row
	BEGIN
		WHILE @counter1 < new.f136 
			SET @counter1 = @counter1 + 1; 
	END//
	delimiter ;//

#Cleanup
	--disable_warnings 
	delete from tb3 where f122='Test 3.5.8.5-while';
	drop trigger trg7;
	--enable_warnings

#Section 3.5.8.6
# Test case: Ensure that a trigger definition that includes a CALL to a stored 
#            procedure fails, at CREATE TRIGGER time, with an appropriate error 
#            message. Not more valid requirement.
let $message= Testcase 3.5.8.6: (requirement void);
--source include/show_msg.inc
	delimiter //;
	CREATE PROCEDURE sp_01 () BEGIN set @v1=1; END//

	CREATE TRIGGER trg8_1 BEFORE UPDATE ON tb3 FOR EACH ROW
	BEGIN
		CALL sp_01 ();
	END//
	delimiter ;//
	Insert into tb3 (f120, f122, f136) values ('6', 'Test 3.5.8.6-insert', 101);
	update tb3 set f120='S', f136=111,
	       f122='Test 3.5.8.6-tr8_1'
	       where f122='Test 3.5.8.6-insert';
	select f120, f122
	       from tb3 where f122  like 'Test 3.5.8.6%' order by f120;
	DROP TRIGGER trg8_1;
	DROP PROCEDURE sp_01;


#Section 3.5.8.7
# Test case: Ensure that a trigger definition that includes a 
#            transaction-delimiting statement (e.g. COMMIT, 
#            ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER 
#            time, with an appropriate error message.
let $message= Testcase 3.5.8.7;
--source include/show_msg.inc
	
	delimiter //;
	--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG 
	Create trigger trg9_1 before update on tb3 for each row
	BEGIN
		Start transaction;
			Set new.f120='U';
		Commit;
	END//

	--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG 
	Create trigger trg9_2 before delete on tb3 for each row
	BEGIN
		Start transaction;
			Set @var2=old.f120;
		Rollback;
	END//
	delimiter ;//


# Cleanup section 3.5
	connection default;
	drop user test_general@localhost;
	drop user test_general;
	drop user test_super@localhost;