Debian Codegraph @

mysql-test/suite/funcs_1/storedproc/storedproc_08.inc

raw
1 #### suite/funcs_1/storedproc/storedproc_08.inc
2 #
3 --source include/default_charset.inc
4 --source suite/funcs_1/storedproc/load_sp_tb.inc
5
6 # ==============================================================================
7 # (numbering from requirement document TP v1.0, Last updated: 25 Jan 2005 01:00)
8 #
9 # 3.1.8 SHOW statement checks:
10 #
11 #- 1. Ensure that the definition of a procedure is properly recorded and displayed when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement is executed.
12 #- 2. Ensure that the definition of a procedure is not displayed when a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement is executed.
13 #- 3. Ensure that an attempt to execute SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS, with the name of a non-existent procedure, fails with an appropriate error message.
14 #- 4. Ensure that an attempt to execute SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS, with the name of a function, fails with an appropriate error message.
15 #- 5. Ensure that the definition of a function is properly recorded and displayed when a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement is executed.
16 #- 6. Ensure that the definition of a function is not displayed when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement is executed.
17 #- 7. Ensure that an attempt to execute SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS, with the name of a non-existent function, fails with an appropriate error message.
18 #- 8. Ensure that an attempt to execute SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS, with the name of a procedure, fails with an appropriate error message.
19 ## 9. Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement (respectively) is executed.
20 #- 10. Ensure that, when a stored procedure is dropped, its definition no longer appears when a SHOW CREATE PROCEDURE or SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE FUNCTION STATUS statement (respectively) is executed.
21 #
22 # ==============================================================================
23 let $message= Section 3.1.8 - SHOW statement checks:;
24 --source include/show_msg80.inc
25
26
27 # ------------------------------------------------------------------------------
28 let $message= Testcase 3.1.8.9:;
29 --source include/show_msg.inc
30 let $message=
31 Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER
32 FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or
33 SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE
34 FUNCTION STATUS statement (respectively) is executed.;
35 --source include/show_msg80.inc
36
37 --disable_warnings
38 DROP FUNCTION IF EXISTS fn_1;
39 DROP FUNCTION IF EXISTS fn_2;
40 DROP PROCEDURE IF EXISTS sp_1;
41 DROP PROCEDURE IF EXISTS sp_2;
42 --enable_warnings
43
44 delimiter //;
45 CREATE PROCEDURE sp_1 (i1 int)
46 BEGIN
47 set @x=i1;
48 END//
49
50 CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER'
51 BEGIN
52 set @x=i1;
53 END//
54
55 CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year
56 BEGIN
57 set @x=i1;
58 set @y=@x;
59 return i4;
60 END//
61
62 CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real)
63 RETURNS YEAR
64 SQL SECURITY INVOKER
65 COMMENT 'created with INVOKER'
66 BEGIN
67 set @x=i1;
68 set @y=@x;
69 return i4;
70 END//
71 delimiter ;//
72
73 --source suite/funcs_1/storedproc/storedproc_08_show.inc
74
75 let $message= ... now change some stuff:;
76 --source include/show_msg.inc
77 # some of the changes might be 'strange' here, but they are used here ONLY to
78 # check that the changes are recorded / showd correct
79 ALTER PROCEDURE sp_1 SQL SECURITY INVOKER;
80 ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER';
81
82 ALTER PROCEDURE sp_2 SQL SECURITY DEFINER;
83 ALTER PROCEDURE sp_2 DROP COMMENT;
84 ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER';
85 ALTER PROCEDURE sp_2 READS SQL DATA;
86
87 ALTER FUNCTION fn_1 SQL SECURITY INVOKER;
88 ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER';
89 ALTER FUNCTION fn_1 NO SQL;
90
91 ALTER FUNCTION fn_2 SQL SECURITY DEFINER;
92 ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER';
93 ALTER FUNCTION fn_2 MODIFIES SQL DATA;
94
95 --source suite/funcs_1/storedproc/storedproc_08_show.inc
96
97
98 let $message= ... change back to default and check result:;
99 --source include/show_msg.inc
100 ALTER FUNCTION fn_2 CONTAINS SQL;
101 --source suite/funcs_1/storedproc/storedproc_08_show.inc
102
103
104 # cleanup 3.1.8.9
105 let $message= ... cleanup;
106 --source include/show_msg.inc
107 DROP FUNCTION fn_1;
108 DROP FUNCTION fn_2;
109 DROP PROCEDURE sp_1;
110
111
112 # ==============================================================================
113 # USE the same .inc to cleanup before and after the test
114 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
115
116 # ==============================================================================
117 let $message= . +++ END OF SCRIPT +++;
118 --source include/show_msg80.inc
119 # ==============================================================================