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