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