Debian Codegraph @

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

raw
1 #### suite/funcs_1/storedproc/storedproc_10.inc
2
3
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.10 CALL checks:
10 #
11 ## 1. Ensure that a properly defined procedure can always be called, assuming
12 # the appropriate privileges exist.
13 #- 2. Ensure that a procedure cannot be called if the appropriate privileges
14 # do not exist.
15 ## 3. Ensure that a function can never be called.
16 ## 4. Ensure that a properly defined function can always be executed, assuming
17 # the appropriate privileges exist.
18 #- 5. Ensure that a function cannot be executed if the appropriate privileges
19 # do not exist.
20 ## 6. Ensure that a procedure can never be executed.
21 ## 7. Ensure that the ROW_COUNT() SQL function always returns the correct
22 # number of rows affected by the execution of a stored procedure.
23 ## 8. Ensure that the mysql_affected_rows() C API function always returns
24 # the correct number of rows affected by the execution of a
25 # stored procedure.
26 #
27 # ==============================================================================
28 let $message= Section 3.1.10 - CALL checks:;
29 --source include/show_msg80.inc
30
31
32 USE db_storedproc;
33
34 # ------------------------------------------------------------------------------
35 let $message= Testcase 3.1.10.2 + 3.1.10.5:;
36 --source include/show_msg.inc
37 let $message=
38 2. Ensure that a procedure cannot be called if the appropriate privileges do not
39 exist.
40 5. Ensure that a function cannot be executed if the appropriate privileges do
41 not exist.;
42 --source include/show_msg80.inc
43
44 --disable_warnings
45 DROP PROCEDURE IF EXISTS sp31102;
46 DROP FUNCTION IF EXISTS fn31105;
47 --enable_warnings
48
49 # DEFINER
50 create user 'user_1'@'localhost';
51 # INVOKER
52 create user 'user_2'@'localhost';
53
54 GRANT CREATE ROUTINE ON db_storedproc.* TO 'user_1'@'localhost';
55 GRANT SELECT ON db_storedproc.* TO 'user_2'@'localhost';
56 FLUSH PRIVILEGES;
57
58 connect (user2_1, localhost, user_1, , db_storedproc);
59 --source suite/funcs_1/include/show_connection.inc
60
61 delimiter //;
62 CREATE PROCEDURE sp31102 () SQL SECURITY INVOKER
63 BEGIN
64 SELECT * FROM db_storedproc.t1 WHERE f4=-5000 LIMIT 1;
65 END//
66 delimiter ;//
67
68 delimiter //;
69 CREATE FUNCTION fn31105(n INT) RETURNS INT
70 BEGIN
71 DECLARE res INT;
72 SET res = n * n;
73 RETURN res;
74 END//
75 delimiter ;//
76
77 disconnect user2_1;
78
79 connect (user2_2, localhost, user_2, , db_storedproc);
80 --source suite/funcs_1/include/show_connection.inc
81
82 # no privileges exist
83 --error ER_PROCACCESS_DENIED_ERROR
84 CALL sp31102();
85 SELECT fn31105( 9 );
86
87 # now 'add' EXECUTE to INVOKER
88 --echo connection default;
89 connection default;
90 USE db_storedproc;
91 --source suite/funcs_1/include/show_connection.inc
92 # root can execute ...
93 CALL sp31102();
94 SELECT fn31105( 9 );
95 GRANT EXECUTE ON db_storedproc.* TO 'user_2'@'localhost';
96 FLUSH PRIVILEGES;
97 disconnect user2_2;
98
99 # new connection
100 connect (user2_3, localhost, user_2, , db_storedproc);
101 --source suite/funcs_1/include/show_connection.inc
102 CALL sp31102();
103 SELECT fn31105( 9 );
104 disconnect user2_3;
105
106 # now 'remove' SELECT from INVOKER
107 --echo connection default;
108 connection default;
109 USE db_storedproc;
110 --source suite/funcs_1/include/show_connection.inc
111 REVOKE EXECUTE ON db_storedproc.* FROM 'user_2'@'localhost';
112 FLUSH PRIVILEGES;
113
114 # root can still execute
115 CALL sp31102();
116 SELECT fn31105( 9 );
117
118 connect (user2_4, localhost, user_2, , db_storedproc);
119 --source suite/funcs_1/include/show_connection.inc
120 CALL sp31102();
121 SELECT fn31105( 9 );
122 disconnect user2_4;
123
124 # cleanup
125 connection default;
126 USE db_storedproc;
127
128 --source suite/funcs_1/include/show_connection.inc
129 DROP PROCEDURE sp31102;
130 DROP FUNCTION fn31105;
131 DROP USER 'user_1'@'localhost';
132 DROP USER 'user_2'@'localhost';
133
134
135 # ------------------------------------------------------------------------------
136 let $message= Testcase 3.1.10.3:;
137 --source include/show_msg.inc
138 let $message=
139 Ensure that a function can never be called.;
140 --source include/show_msg80.inc
141
142 --disable_warnings
143 DROP FUNCTION IF EXISTS fn1;
144 --enable_warnings
145
146 delimiter //;
147 CREATE FUNCTION fn1(a int) returns int
148 BEGIN
149 set @b = 0.9 * a;
150 return @b;
151 END//
152 delimiter ;//
153
154 --error ER_SP_DOES_NOT_EXIST
155 CALL fn1();
156
157 # cleanup
158 DROP FUNCTION fn1;
159
160
161 # ------------------------------------------------------------------------------
162 let $message= Testcase 3.1.10.6:;
163 --source include/show_msg.inc
164 let $message=
165 Ensure that a procedure can never be executed.;
166 --source include/show_msg80.inc
167
168 --disable_warnings
169 DROP PROCEDURE IF EXISTS sp1;
170 DROP FUNCTION IF EXISTS sp1;
171 --enable_warnings
172
173 delimiter //;
174 CREATE PROCEDURE sp1()
175 BEGIN
176 SELECT * from t10;
177 END//
178 delimiter ;//
179
180 --error ER_SP_DOES_NOT_EXIST
181 SELECT sp1();
182
183 # cleanup
184 DROP PROCEDURE sp1;
185
186
187 # ------------------------------------------------------------------------------
188 let $message= Testcase 3.1.10.7:;
189 --source include/show_msg.inc
190 let $message=
191 Ensure that the ROW_COUNT() SQL function always returns the correct number of
192 rows affected by the execution of a stored procedure.;
193 --source include/show_msg80.inc
194 # Note(mleich): Information taken from a comments in
195 # Bug#21818 Return value of ROW_COUNT() is incorrect for
196 # ALTER TABLE, LOAD DATA
197 # ROW_COUNT() is -1 following any statement which is not DELETE, INSERT
198 # or UPDATE.
199 # Also, after a CALL statement, ROW_COUNT() will return the value of the
200 # last statement in the stored procedure.
201
202 --disable_warnings
203 DROP PROCEDURE IF EXISTS sp_ins_1;
204 DROP PROCEDURE IF EXISTS sp_ins_3;
205 DROP PROCEDURE IF EXISTS sp_upd;
206 DROP PROCEDURE IF EXISTS sp_ins_upd;
207 DROP PROCEDURE IF EXISTS sp_del;
208 DROP PROCEDURE IF EXISTS sp_with_rowcount;
209 --enable_warnings
210
211 CREATE TABLE temp(f1 CHAR(20),f2 CHAR(25),f3 DATE,f4 INT,f5 CHAR(25),f6 INT);
212 INSERT INTO temp SELECT * FROM t10;
213
214 delimiter //;
215 CREATE PROCEDURE sp_ins_1()
216 BEGIN
217 INSERT INTO temp VALUES ('abc', 'abc', '20051003', 100, 'uvw', 1000);
218 END//
219
220 CREATE PROCEDURE sp_ins_3()
221 BEGIN
222 INSERT INTO temp VALUES ('abc', 'xyz', '19490523', 100, 'uvw', 1000);
223 INSERT INTO temp VALUES ('abc', 'xyz', '1989-11-09', 100, 'uvw', 1000);
224 INSERT INTO temp VALUES ('abc', 'xyz', '2005-10-24', 100, 'uvw', 1000);
225 END//
226
227 CREATE PROCEDURE sp_upd()
228 BEGIN
229 UPDATE temp SET temp.f1 = 'updated' WHERE temp.f1 ='abc';
230 END//
231
232 CREATE PROCEDURE sp_ins_upd()
233 BEGIN
234 BEGIN
235 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000);
236 INSERT INTO temp VALUES ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000);
237 INSERT INTO temp VALUES ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000);
238 INSERT INTO temp VALUES ('qwe', 'abc', '2005-11-07', 100, 'uvw', 1000);
239 END;
240 SELECT COUNT( f1 ), f1 FROM temp GROUP BY f1;
241 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f1 ='qwe' AND temp.f2 = 'abc';
242 END//
243
244 CREATE PROCEDURE sp_del()
245 BEGIN
246 DELETE FROM temp WHERE temp.f1 ='qwe' OR temp.f1 = 'updated_2';
247 END//
248
249 CREATE PROCEDURE sp_with_rowcount()
250 BEGIN
251 BEGIN
252 INSERT INTO temp VALUES ('qwe', 'abc', '1989-11-09', 100, 'uvw', 1000),
253 ('qwe', 'xyz', '1998-03-26', 100, 'uvw', 1000),
254 ('qwe', 'abc', '2000-11-09', 100, 'uvw', 1000),
255 ('qwe', 'xyz', '2005-11-07', 100, 'uvw', 1000);
256 END;
257 SELECT row_count() AS 'row_count() after insert';
258 SELECT row_count() AS 'row_count() after select row_count()';
259 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
260 UPDATE temp SET temp.f1 = 'updated_2' WHERE temp.f2 = 'abc';
261 SELECT row_count() AS 'row_count() after update';
262 SELECT f1,f2,f3 FROM temp ORDER BY f1,f2,f3;
263 DELETE FROM temp WHERE temp.f1 = 'updated_2';
264 SELECT row_count() AS 'row_count() after delete';
265 END//
266 delimiter ;//
267
268 CALL sp_ins_1();
269 SELECT row_count();
270 --sorted_result
271 SELECT * FROM temp;
272
273 CALL sp_ins_3();
274 SELECT row_count();
275 --sorted_result
276 SELECT * FROM temp;
277
278 CALL sp_upd();
279 SELECT row_count();
280 --sorted_result
281 SELECT * FROM temp;
282
283 CALL sp_ins_upd();
284 SELECT row_count();
285 --sorted_result
286 SELECT * FROM temp;
287
288 CALL sp_del();
289 SELECT row_count();
290 --sorted_result
291 SELECT * FROM temp;
292
293 DELETE FROM temp;
294 CALL sp_with_rowcount();
295 SELECT row_count();
296 --sorted_result
297 SELECT * FROM temp;
298
299
300 # cleanup
301 DROP PROCEDURE sp_ins_1;
302 DROP PROCEDURE sp_ins_3;
303 DROP PROCEDURE sp_upd;
304 DROP PROCEDURE sp_ins_upd;
305 DROP PROCEDURE sp_del;
306 DROP PROCEDURE sp_with_rowcount;
307 DROP TABLE temp;
308
309
310 # ------------------------------------------------------------------------------
311 let $message= Testcase 3.1.10.8:;
312 --source include/show_msg.inc
313 let $message=
314 Ensure that the mysql_affected_rows() C API function always returns the correct
315 number of rows affected by the execution of a stored procedure.;
316 --source include/show_msg80.inc
317
318 #FIXME: 3.1.10.8: to be added later.
319
320 # ==============================================================================
321 # USE the same .inc to cleanup before and after the test
322 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
323
324 # ==============================================================================
325 --echo
326 --echo . +++ END OF SCRIPT +++
327 --echo --------------------------------------------------------------------------------
328 # ==============================================================================