is_routines.inc

来自「这个文件是windows mysql源码」· INC 代码 · 共 473 行 · 第 1/2 页

ORDER BY routine_name;
--horizontal_results

ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
ALTER FUNCTION function_for_routines COMMENT 'updated comments';
--vertical_results
--replace_column 16 <created> 17 <modified>
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
--horizontal_results

DROP PROCEDURE sp_for_routines;
DROP FUNCTION function_for_routines;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';

CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
--vertical_results
--replace_column 16 <created> 17 <modified>
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
--horizontal_results
use test;
DROP DATABASE db_datadict;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';


--echo #########################################################################
--echo # 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
--echo #          ROUTINE_DEFINITION column
--echo #########################################################################
# Ensure that a stored procedure with a routine body that is too large to fit
# into the INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION column correctly shows
# as much of the information as is possible within the allotted size.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
USE db_datadict;
#
--replace_result $other_engine_type <other_engine_type>
eval
CREATE TABLE db_datadict.res_6_408004_1
       (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
ENGINE = $other_engine_type;
INSERT INTO db_datadict.res_6_408004_1
VALUES ('abc', 98765 , 99999999 , 98765, 10);
#
--replace_result $other_engine_type <other_engine_type>
eval
CREATE TABLE db_datadict.res_6_408004_2
       (f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
ENGINE = $other_engine_type;
INSERT INTO db_datadict.res_6_408004_2
VALUES ('abc', 98765 , 99999999 , 98765, 10);

--echo # Checking the max. possible length of (currently) 4 GByte is not
--echo # in this environment here.

delimiter //;
CREATE PROCEDURE sp_6_408004 ()
BEGIN
   DECLARE done INTEGER DEFAULt 0;
   DECLARE variable_number_1 LONGTEXT;
   DECLARE variable_number_2 MEDIUMINT;
   DECLARE variable_number_3 LONGBLOB;
   DECLARE variable_number_4 REAL;
   DECLARE variable_number_5 YEAR;
   DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
   DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
   DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
   DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
   DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
   BEGIN
      OPEN cursor_number_1;
      WHILE done <> 1 DO
         FETCH cursor_number_1
         INTO variable_number_1, variable_number_2, variable_number_3,
              variable_number_4, variable_number_5;
         IF done <> 0 THEN
            INSERT INTO res_6_408004_2
            VALUES (variable_number_1, variable_number_2, variable_number_3,
                    variable_number_4, variable_number_5);
         END IF;
      END WHILE;
      BEGIN
         BEGIN
            SET done = 0;
            OPEN cursor_number_2;
            WHILE done <> 1 DO
               FETCH cursor_number_2
               INTO variable_number_1, variable_number_2, variable_number_3,
                    variable_number_4, variable_number_5;
               IF done <> 0 THEN
                  INSERT INTO res_6_408004_2
                  VALUES(variable_number_1, variable_number_2, variable_number_3,
                         variable_number_4, variable_number_5);
               END IF;
            END WHILE;
         END;
         SET done = 0;
         OPEN cursor_number_3;
         WHILE done <> 1 DO
            FETCH cursor_number_3
            INTO variable_number_1, variable_number_2, variable_number_3,
                 variable_number_4, variable_number_5;
            IF done <> 0 THEN
               INSERT INTO res_6_408004_2
               VALUES(variable_number_1, variable_number_2, variable_number_3,
                      variable_number_4, variable_number_5);
            END IF;
         END WHILE;
      END;
   END;
   BEGIN
      SET done = 0;
      OPEN cursor_number_4;
      WHILE done <> 1 DO
         FETCH cursor_number_4
         INTO variable_number_1, variable_number_2, variable_number_3,
              variable_number_4, variable_number_5;
         IF done <> 0 THEN
            INSERT INTO res_6_408004_2
            VALUES (variable_number_1, variable_number_2, variable_number_3,
                    variable_number_4, variable_number_5);
         END IF;
      END WHILE;
   END;
   BEGIN
      SET @a='test row';
      SELECT @a;
      SELECT @a;
      SELECT @a;
   END;
   BEGIN
      SET done = 0;
      OPEN cursor_number_5;
      WHILE done <> 1 DO
         FETCH cursor_number_5
         INTO variable_number_1, variable_number_2, variable_number_3,
              variable_number_4, variable_number_5;
         IF done <> 0 THEN
            INSERT INTO res_6_408004_2
            VALUES (variable_number_1, variable_number_2, variable_number_3,
                    variable_number_4, variable_number_5);
         END IF;
      END WHILE;
   END;
   BEGIN
      SET @a='test row';
      SELECT @a;
      SELECT @a;
      SELECT @a;
   END;
END//
delimiter ;//

CALL db_datadict.sp_6_408004 ();
SELECT * FROM db_datadict.res_6_408004_2;

--vertical_results
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
SELECT *, LENGTH(routine_definition) FROM information_schema.routines
WHERE routine_schema = 'db_datadict';
--horizontal_results

# Cleanup
DROP DATABASE db_datadict;
# ----------------------------------------------------------------------------------------------


--echo ########################################################################
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
--echo #           DDL on INFORMATION_SCHEMA table are not supported
--echo ########################################################################
# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.8:  Ensure that no user may create an index on an INFORMATION_SCHEMA table.
# 3.2.1.9:  Ensure that no user may alter the definition of an
#           INFORMATION_SCHEMA table.
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
#           other database.
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
#           in an INFORMATION_SCHEMA table.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
USE db_datadict;
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
USE test;

--error ER_DBACCESS_DENIED_ERROR
INSERT INTO information_schema.routines (routine_name, routine_type )
VALUES ('p2', 'procedure');

--error ER_DBACCESS_DENIED_ERROR
UPDATE information_schema.routines SET routine_name = 'p2'
WHERE routine_body = 'sql';

--error ER_DBACCESS_DENIED_ERROR
DELETE FROM information_schema.routines ;
#
--error ER_DBACCESS_DENIED_ERROR
TRUNCATE information_schema.routines ;

--error ER_DBACCESS_DENIED_ERROR
CREATE INDEX i7 ON information_schema.routines (routine_name);

--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.routines  ADD f1 INT;
#
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.routines  DISCARD TABLESPACE;

--error ER_DBACCESS_DENIED_ERROR
DROP TABLE information_schema.routines ;

--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.routines RENAME db_datadict.routines;
#
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.routines RENAME information_schema.xroutines;

# Cleanup
DROP DATABASE db_datadict;