Debian Codegraph @

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

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