release/src/router/mysql/mysql-test/suite/funcs_1/t/is_coll_char_set_appl.test

   1 # suite/funcs_1/t/is_coll_char_set_appl.test
   2 #
   3 # Check the layout of information_schema.collation_character_set_applicability
   4 # and some functionality related tests.
   5 #
   6 # Author:
   7 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
   8 #                           testsuite funcs_1
   9 #                   Create this script based on older scripts and new code.
  10 #
  11
  12 if (`SELECT VERSION() LIKE '%embedded%'`)
  13 {
  14    --skip Bug#37456 funcs_1: Several tests crash when used with embedded server
  15 }
  16
  17 let $is_table = COLLATION_CHARACTER_SET_APPLICABILITY;
  18
  19 # The table INFORMATION_SCHEMA.CHARACTER_SET_APPLICABILITY must exist
  20 eval SHOW TABLES FROM information_schema LIKE '$is_table';
  21
  22 --echo #######################################################################
  23 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
  24 --echo #######################################################################
  25 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
  26 # statement, just as if it were an ordinary user-defined table.
  27 #
  28 --source suite/funcs_1/datadict/is_table_query.inc
  29
  30
  31 --echo #########################################################################
  32 --echo # Testcase 3.2.4.1: INFORMATION_SCHEMA.CHARACTER_SET_APPLICABILITY layout
  33 --echo #########################################################################
  34 # Ensure that the INFORMATION_SCHEMA.CHARACTER_SET_APPLICABILITY table has the
  35 # following columns, in the following order:
  36 #
  37 # COLLATION_NAME (shows the name of a collation),
  38 # CHARACTER_SET_NAME (shows the name of a character set to which that
  39 #           collation applies).
  40 #
  41 eval DESCRIBE          information_schema.$is_table;
  42 eval SHOW CREATE TABLE information_schema.$is_table;
  43 eval SHOW COLUMNS FROM information_schema.$is_table;
  44
  45 # Note: Retrieval of information within information_schema.columns about
  46 #       information_schema.collation_character_set_applicability is in
  47 #       is_columns_is.test.
  48 #       Retrieval of information_schema.collation_character_set_applicability
  49 #       content is in charset_collation.inc (sourced by charset_collation_*.test).
  50
  51 echo # Testcases 3.2.4.2 and 3.2.4.3 are checked in suite/funcs_1/t/charset_collation*.test;
  52
  53 --echo ########################################################################
  54 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
  55 --echo #           DDL on INFORMATION_SCHEMA tables are not supported
  56 --echo ########################################################################
  57 # 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
  58 #           INFORMATION_SCHEMA table.
  59 # 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
  60 #           INFORMATION_SCHEMA table.
  61 # 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
  62 #           INFORMATION_SCHEMA table.
  63 # 3.2.1.8:  Ensure that no user may create an index on an
  64 #           INFORMATION_SCHEMA table.
  65 # 3.2.1.9:  Ensure that no user may alter the definition of an
  66 #           INFORMATION_SCHEMA table.
  67 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
  68 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
  69 #           other database.
  70 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
  71 #           in an INFORMATION_SCHEMA table.
  72 #
  73 --disable_warnings
  74 DROP DATABASE IF EXISTS db_datadict;
  75 --enable_warnings
  76 CREATE DATABASE db_datadict;
  77
  78 --error ER_DBACCESS_DENIED_ERROR
  79 INSERT INTO information_schema.collation_character_set_applicability
  80 SELECT * FROM information_schema.collation_character_set_applicability;
  81
  82 --error ER_DBACCESS_DENIED_ERROR
  83 UPDATE information_schema.collation_character_set_applicability
  84 SET collation_name = 'big6_chinese_ci' WHERE character_set_name = 'big6';
  85 --error ER_DBACCESS_DENIED_ERROR
  86 UPDATE information_schema.collation_character_set_applicability
  87 SET character_set_name = 't_4711';
  88
  89 --error ER_DBACCESS_DENIED_ERROR
  90 DELETE FROM information_schema.collation_character_set_applicability;
  91 --error ER_DBACCESS_DENIED_ERROR
  92 TRUNCATE information_schema.collation_character_set_applicability;
  93
  94 --error ER_DBACCESS_DENIED_ERROR
  95 CREATE INDEX my_idx
  96 ON information_schema.collation_character_set_applicability(collation_name);
  97
  98 --error ER_DBACCESS_DENIED_ERROR
  99 ALTER TABLE information_schema.collation_character_set_applicability ADD f1 INT;
 100
 101 --error ER_DBACCESS_DENIED_ERROR
 102 DROP TABLE information_schema.collation_character_set_applicability;
 103
 104 --error ER_DBACCESS_DENIED_ERROR
 105 ALTER TABLE information_schema.collation_character_set_applicability
 106 RENAME db_datadict.collation_character_set_applicability;
 107 --error ER_DBACCESS_DENIED_ERROR
 108 ALTER TABLE information_schema.collation_character_set_applicability
 109 RENAME information_schema.xcollation_character_set_applicability;
 110
 111 # Cleanup
 112 DROP DATABASE db_datadict;
 113