datadict_master.inc

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· INC 代码 · 共 1,847 行 · 第 1/5 页

#### suite/funcs_1/datadict/datadict_master.inc
#
# Checks of INFORMATION_SCHEMA table properties and content.
# (mostly only the features introduced with MySQL 5.1)
#
# Please set the variable $OTHER_ENGINE_TYPE before sourcing this script.
# $OTHER_ENGINE_TYPE must be
# - <> $engine_type
# - all time available like MyISAM or MEMORY
#
# Last change:
# 2007-08-24 mleich Fixes for the bugs
#                   #30438 "{memory,myisam,ndb}__datadict" tests fail:
#                          Use "InnoDB" without checking
#                   #30418 "datadict" tests (all engines) fail:
#                          Dependency on the host name for ordering
#                   #30420 "datadict" tests (all engines) fail:
#                          Release build has help tables loaded
#

--disable_abort_on_error
let $message=
.
. It is intended that the 3 <engine>__datadict.test files are named this way to be
. sure they are - in a *full run* of the suite - the first tests done for each
. storage engine. Using two _ and the order of processing in mysql-test-run.pl
. ensures this in an easy way.
.
. If needed a restart could be implemented later between the storage engines if
. values changes in the result depending from the position where the
. *__datadict.test are started. This can be a result of showing e.g. maximum
. values of the number of rows of tables.
.
. This .result file has been checked OK with Linux 5.0.48,
. build tree ChangeSet@1.2477.6.3, 2007-07-30
. except that the not fixed Bug#30020 causes a difference.
.;
--source include/show_msg80.inc

################################################################################
#
   let $message= FIXME: There are subtests that are switched off due to known bugs:;
   --source include/show_msg.inc
   #set variable(s) here to be able to switch crashing sub tests with ONE change HERE.
   #change the variable(s) to enable / disable the crashing parts.

   # different 'logics' are used because sometimes codelines needs to be switched off
   # and otherwise some extra statements needs to be executed as long as the bug is not
   # fixed:
   let $have_bug_11589= 1;

   #seems not to work: --vertical_results
   eval SELECT $have_bug_11589 AS "have_bug_11589";
   #seems not to work: --horizontal_results

   # As long as
   # Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill
   # is not fixed, we must switch the ps-protocol for some statements off.
   # If this bug is fixed, please
   # 1. set the following variable to 0
   # 2. check, if the test passes
   # 3. remove the workarounds
   if ($have_bug_11589)
   {
      let $message= There are some statements where the ps-protocol is switched off.
                    This may come from the bug listed below, ir from other problems.
                    Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill;
      --source include/show_msg80.inc
   }
#
################################################################################


# loading the tables (data is not really needed in this test) is separated to
# make it easier in this file to show the message above.
--source suite/funcs_1/datadict/datadict_load.inc

#FIXME:  -  check for remaining change of object names to standards: db_, tb_, v_, u_, ...
#FIXME:  -  check warnings when data is loaded (Data truncated for column ...)
#FIXME:  -  change connect() to use less users / connections
#
#FIXME:  -  check for additional 'FIXME' here in the script

use information_schema;
--source suite/funcs_1/include/show_connection.inc


################################################################################
#
#  Data Dictionary
#
################################################################################

let $message= Testcase 3.2.1.1:;
--source include/show_msg80.inc

################################################################################
# Testcase 3.2.1.1: Ensure that every INFORMATION_SCHEMA table can be queried
#                   with a SELECT statement, just as if it were an ordinary
#                   user-defined table.
################################################################################

# create at least one object for all 'tables' to be checked
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
USE db_datadict;

CREATE VIEW v1 AS SELECT * FROM information_schema.tables;

# try to get the server's name to be able to clean-up the result from machine
#  specific stuff.
CREATE OR REPLACE VIEW db_datadict.vu1 as
SELECT grantee AS u
  FROM information_schema.user_privileges;
CREATE OR REPLACE VIEW db_datadict.vu as
SELECT DISTINCT u,
  SUBSTRING( u, LENGTH(SUBSTRING_INDEX(u,_utf8'@',1))+3 )
    AS server,
  SUBSTRING( u, LENGTH(SUBSTRING_INDEX(u,_utf8'@',1))+3,
    LENGTH( SUBSTRING( u,
    LENGTH( SUBSTRING_INDEX(u, _utf8'@',1)) +3 )) - 1 )
    AS Server_Clean
FROM db_datadict.vu1;
--replace_result $SERVER_NAME <SERVER_NAME>
--sorted_result
SELECT * FROM db_datadict.vu order by u;

delimiter //;
CREATE PROCEDURE db_datadict.sp_1()
  BEGIN
    SELECT * FROM db_datadict.v1;
  END//
delimiter ;//

#FIXME 3.2.1.1: add missing objects of each type to have something to select
#FIXME 3.2.1.1: - FUNCTION
#FIXME 3.2.1.1: - TRIGGER

USE information_schema;
SHOW tables;

select * from schemata ORDER BY 2 DESC, 1 ASC;

if ($have_bug_11589)
{
--disable_ps_protocol
}
--vertical_results
#SELECT * FROM tables;
#
#FIXME 3.2.1.1: we split the "SELECT * FROM tables" in two parts until
#FIXME 3.2.1.1: Bug #12397: wrong values shown in column CREATE_OPTIONS of
#FIXME 3.2.1.1: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
#FIXME 3.2.1.1: with 'less' replace
#  9 AVG_ROW_LENGTH
# 10 DATA_LENGTH
# 11 MAX_DATA_LENGTH
# 12 INDEX_LENGTH
# 13 DATA_FREE
# 15 CREATE_TIME
# 16 UPDATE_TIME
# 17 CHECK_TIME
# 20 CREATE_OPTIONS
--replace_column  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "YYYY-MM-DD hh:mm:ss"  16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"  20 "#CO#"
SELECT * FROM tables
 WHERE table_schema = 'information_schema';
#  9 AVG_ROW_LENGTH
# 10 DATA_LENGTH
# 11 MAX_DATA_LENGTH
# 12 INDEX_LENGTH
# 13 DATA_FREE
# 15 CREATE_TIME
# 16 UPDATE_TIME
# 17 CHECK_TIME
--replace_column  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "YYYY-MM-DD hh:mm:ss"  16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
SELECT * FROM tables
WHERE NOT( table_schema = 'information_schema')
  AND NOT (table_schema = 'mysql' AND table_name LIKE 'help_%');
--horizontal_results
--enable_ps_protocol

select s.catalog_name, s.schema_name, s.default_character_set_name,
       t.table_type, t.engine
  from schemata s inner join tables t
 ORDER BY s.schema_name, s.default_character_set_name, table_type, engine;
--source suite/funcs_1/datadict/datadict_bug_12777.inc
select * from columns;
select * from character_sets;
select sum(id) from collations;
select collation_name, character_set_name into @x,@y
  from collation_character_set_applicability limit 1;
  select @x, @y;

--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
select * from routines;

select count(*) from routines;
select * from statistics
where not (table_schema = 'mysql' and table_name like 'help_%');
select * from views;
--replace_result $SERVER_NAME <SERVER_NAME>
--sorted_result
select * from user_privileges order by grantee, privilege_type;
select * from schema_privileges;
select * from table_privileges;
select * from column_privileges;
select * from table_constraints;
select * from key_column_usage;
select count(*) as max_recs from key_column_usage;

select max(cardinality) from statistics
where not (table_schema = 'mysql' and table_name like 'help_%');

select concat("View '",
       table_name, "' is associated with the database '", table_schema, "'.")
       AS "Who is Who for the Views"
  from views;

select concat("Table or view '", table_name,
              "' is associated with the database '", table_schema, "'.") as "Who is Who"
  from tables;

--replace_result $SERVER_NAME <SERVER_NAME>
--sorted_result
select grantee as "user's having select privilege",
       substring( grantee, length(SUBSTRING_INDEX(grantee,_utf8'@',1))+2 )
  from user_privileges where privilege_type = 'select'
  order by grantee;

select all table_schema from schema_privileges limit 0,5;

select distinct(privilege_type) from table_privileges;

select * from column_privileges
 group by table_schema having table_schema = 'db_datadict';

select * from table_constraints limit 0,5;
select count(*) as max_recs from key_column_usage limit 0,5;

select information_schema.tables.table_name as "table name",
       count(distinct(column_name)) as "no of columns in the table"
  from information_schema.tables left outer join information_schema.columns on
    information_schema.tables.table_name = information_schema.columns.table_name
 group by information_schema.tables.table_name;

# Reference Manual 22.1.16 - we will add more ...:
# select * from parameters;
# select * from referential_constraints;
# select * from triggers;

let $message= root: simple select to check all - and never forget some - tables;
let $dd_part1= SELECT * FROM;
let $dd_part2= LIMIT 1;
--source suite/funcs_1/datadict/datadict_tables.inc

# check again, but from different database (will fail due to missing database name)
use db_datadict;

--error ER_NO_SUCH_TABLE
select * from schemata;
--error ER_NO_SUCH_TABLE
select * from tables;
--error ER_NO_SUCH_TABLE
select s.catalog_name, s.schema_name, s.default_character_set_name,
       t.table_type, t.engine
  from schemata s inner join tables t
 ORDER BY s.catalog_name, s.schema_name, s.default_character_set_name;
--error ER_NO_SUCH_TABLE
select * from columns limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from character_sets limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from collations limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from collation_character_set_applicability limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from routines limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from statistics limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from views limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from user_privileges limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from schema_privileges limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from table_privileges limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from column_privileges limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from table_constraints limit 0, 5;
--error ER_NO_SUCH_TABLE
select * from key_column_usage limit 0, 5;
# Reference Manual 22.1.16 - we will add more ...:
# --error ER_NO_SUCH_TABLE
# select * from parameters;
# --error ER_NO_SUCH_TABLE
# select * from referential_constraints;
# --error ER_NO_SUCH_TABLE
# select * from triggers;
let $message= will fail due to missing database name;
let $dd_part1= SELECT * FROM;
let $dd_part2=;
--source suite/funcs_1/datadict/datadict_tables_error_1146.inc

# now check from "other" database, but with database name
select * from information_schema.schemata ORDER BY 2 DESC;

#SELECT * FROM information_schema.tables;
#
#FIXME 3.2.1.1: we split the "SELECT * FROM tables" in two parts until
#FIXME 3.2.1.1: Bug #12397: wrong values shown in column CREATE_OPTIONS of
#FIXME 3.2.1.1: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
#FIXME 3.2.1.1: with 'less' replace
#  9 AVG_ROW_LENGTH
# 10 DATA_LENGTH
# 11 MAX_DATA_LENGTH
# 12 INDEX_LENGTH
# 13 DATA_FREE
# 15 CREATE_TIME
# 16 UPDATE_TIME
# 17 CHRCK_TIME
# 20 CREATE_OPTIONS
if ($have_bug_11589)
{
--disable_ps_protocol
}
--vertical_results
--replace_column  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "YYYY-MM-DD hh:mm:ss"  16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss" 20 "#CO#"
SELECT * FROM information_schema.tables
 WHERE table_schema = 'information_schema';
#  9 AVG_ROW_LENGTH
# 10 DATA_LENGTH
# 11 MAX_DATA_LENGTH
# 12 INDEX_LENGTH
# 13 DATA_FREE
# 15 CREATE_TIME
# 16 UPDATE_TIME
# 17 CHRCK_TIME
--replace_column  9 "#ARL#"  10 "#DL#"  11 "#MDL#"  12 "#IL#"  13 "#DF#"  15 "YYYY-MM-DD hh:mm:ss"  16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
SELECT * FROM information_schema.tables
WHERE NOT( table_schema = 'information_schema')
  AND NOT (table_schema = 'mysql' AND table_name LIKE 'help_%');
--horizontal_results
--enable_ps_protocol

select s.catalog_name, s.schema_name, s.default_character_set_name,
       t.table_type, t.engine
  from information_schema.schemata s inner join information_schema.tables t
 ORDER BY s.schema_name, s.default_character_set_name, table_type, engine;

--source suite/funcs_1/datadict/datadict_bug_12777.inc
select * from information_schema.columns limit 0, 5;
select * from information_schema.character_sets limit 0, 5;
select * from information_schema.collations limit 0, 5;
select * from information_schema.collation_character_set_applicability limit 0, 5;
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
select * from information_schema.routines limit 0, 5;
select * from information_schema.statistics limit 0, 5;
select * from information_schema.views limit 0, 5;
--replace_result $SERVER_NAME <SERVER_NAME>
select * from information_schema.user_privileges limit 0, 5;
select * from information_schema.schema_privileges limit 0, 5;
select * from information_schema.table_privileges limit 0, 5;
select * from information_schema.column_privileges limit 0, 5;
select * from information_schema.table_constraints limit 0, 5;
select * from information_schema.key_column_usage limit 0, 5;
select count(*) as max_recs from information_schema.key_column_usage limit 0, 5;