Debian Codegraph @

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

raw
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 # ==============================================================================