is_basics_mixed.test

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

# suite/funcs_1/t/is_basics_mixed.test
#
# Checks of some basic properties of the INFORMATION_SCHEMA which are not
# related to a certain INFORMATION_SCHEMA table.
#
# This test should not check properties related to storage engines.
#
# Author:
# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
#                           testsuite funcs_1
#                   Create this script based on older scripts and new code.
#

# This test is strict adjusted to the behaviour of the non embedded server.
# Example of common differences between both servers:
#    USE information_schema; CREATE VIEW tables AS SELECT 'garbage';
#    non embedded server:
#        - errname ER_DBACCESS_DENIED_ERROR
#        - ERROR 42000: Access denied for user 'root'@'localhost' to
#          database 'information_schema'
#    embedded server:
#        - errno 1 
#        - Can't create/write to file
#          '.../var/master-data/information_schema/tables.frm~
--source include/not_embedded.inc

--source suite/funcs_1/datadict/datadict.pre

# $engine_type must point to storage engine which is all time available.
# The fastest engine should be preferred.
let $engine_type = MEMORY;


# The INFORMATION_SCHEMA database must exist.
SHOW DATABASES LIKE 'information_schema';


--echo #######################################################################
--echo # Testcase 3.2.1.20: USE INFORMATION_SCHEMA is supported
--echo #######################################################################
# Ensure that USE INFORMATION_SCHEMA allows the user to switch to the
# INFORMATION_SCHEMA database, for query purposes only.
#
# Note: The "for query purposes only" is checked in other tests.
# High privileged user (root)
--echo # Switch to connection default
connection default;
USE test;
SELECT DATABASE();
USE information_schema;
SELECT DATABASE();
#
--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
# Low privileged user
--echo # Establish connection testuser1 (user=testuser1)
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser1, localhost, testuser1, , test);
SELECT DATABASE();
USE information_schema;
SELECT DATABASE();
#
--echo # Switch to connection default and close connection testuser1
connection default;
disconnect testuser1;
DROP   USER 'testuser1'@'localhost';


--echo #######################################################################
--echo # Testcase TBD1: The INFORMATION_SCHEMA cannot be dropped.
--echo #######################################################################
--error ER_DBACCESS_DENIED_ERROR
DROP DATABASE information_schema;


--echo #######################################################################
--echo # Testcase TBD2: There cannot be a second database INFORMATION_SCHEMA.
--echo #######################################################################
--error ER_DBACCESS_DENIED_ERROR
CREATE DATABASE information_schema;


--echo ##################################################################################
--echo # Testcase 3.2.1.6+3.2.1.7: No user may create an INFORMATION_SCHEMA table or view
--echo ##################################################################################
# 3.2.1.6 Ensure that no user may create an INFORMATION_SCHEMA base table.
# 3.2.1.7 Ensure that no user may create an INFORMATION_SCHEMA view
#

# 1. High privileged user (root)
--echo # Switch to connection default (user=root)
connection default;
--source suite/funcs_1/datadict/basics_mixed1.inc

# 2. High privileged user (testuser1)
--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT ALL ON *.* TO testuser1@localhost;
SHOW GRANTS FOR testuser1@localhost;
--echo # Establish connection testuser1 (user=testuser1)
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser1, localhost, testuser1, , test);
--source suite/funcs_1/datadict/basics_mixed1.inc

--echo # Switch to connection default (user=root) and close connection testuser1
connection default;
disconnect testuser1;
DROP   USER 'testuser1'@'localhost';

--echo ###############################################################################
--echo # Testcase 3.2.1.1+3.2.1.2: INFORMATION_SCHEMA tables can be queried via SELECT
--echo ###############################################################################
# 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.
# 3.2.1.2 Ensure that queries on an INFORMATION_SCHEMA table can accept all
#         SELECT statement options and are always correctly evaluated.
#
# Some notes(mleich):
# - Currently here only a subset of select statement options is checked, it's
#   still not possible to check here all possible options
# - The content of many INFORMATION_SCHEMA tables is checked in other tests.
# - We work here only with a subset of the columns of information_schema.tables
#   because we want have a stable base (all time existing table, stable layout).
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <some_engine>
eval
CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT)
ENGINE = $engine_type;
--replace_result $engine_type <some_engine>
eval
CREATE TABLE db_datadict.t1_second (f1 BIGINT UNIQUE, f2 BIGINT)
ENGINE = $engine_type;

# SELECT *
--echo # Attention: The protocolling of the next result set is disabled.
--disable_result_log
SELECT * FROM information_schema.tables;
--enable_result_log
#
# SELECT <some columns> + WHERE
--sorted_result
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'db_datadict';
#
# SELECT string_function(<some column>) + ORDER BY
SELECT LENGTH(table_name) FROM information_schema.tables
WHERE table_schema = 'db_datadict' ORDER BY table_name;
#
# SELECT aggregate_function(<some column>) + WHERE with LIKE
SELECT count(table_name) FROM information_schema.tables
WHERE table_schema LIKE 'db_datadic%';
#
# SELECT with addition in column list
--sorted_result
SELECT CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))
FROM information_schema.tables
WHERE table_schema = 'db_datadict';
#
# WHERE with IN + LIMIT
SELECT table_name FROM information_schema.tables
WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1;
SELECT table_name FROM information_schema.tables
WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1,1;
#
# WHERE with AND
SELECT table_name,table_schema AS my_col FROM information_schema.tables
WHERE table_name = 't1_first' AND table_schema = 'db_datadict';
#
# SELECT HIGH_PRIORITY + WHERE with OR
--sorted_result
SELECT HIGH_PRIORITY table_name AS my_col FROM information_schema.tables
WHERE table_name = 't1_first' OR table_name = 't1_second';
#
# Empty result set
SELECT 1 AS my_col FROM information_schema.tables
WHERE table_name = 't1_third';
#
# SELECT INTO USER VARIABLE
SELECT table_name,table_schema INTO @table_name,@table_schema
FROM information_schema.tables
WHERE table_schema = 'db_datadict' ORDER BY table_name LIMIT 1;
SELECT @table_name,@table_schema;
#
# SELECT INTO OUTFILE
let $OUTFILE = $MYSQLTEST_VARDIR/tmp/datadict.out;
--error 0,1
remove_file $OUTFILE;
--replace_result $OUTFILE <OUTFILE>
eval SELECT table_name,table_schema
INTO OUTFILE '$OUTFILE'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM information_schema.tables
WHERE table_schema = 'db_datadict' ORDER BY table_name;
cat_file $OUTFILE;
remove_file $OUTFILE;
#
# UNION
--sorted_result
SELECT table_name FROM information_schema.tables
WHERE table_name = 't1_first'
UNION ALL
SELECT table_name FROM information_schema.tables
WHERE table_name = 't1_second';
#
# DISTINCT + SUBQUERY
SELECT DISTINCT table_schema FROM information_schema.tables
WHERE table_name IN (SELECT table_name FROM information_schema.tables
                     WHERE table_schema = 'db_datadict')
ORDER BY table_name;
#
# JOIN
SELECT table_name FROM information_schema.tables t1
LEFT JOIN information_schema.tables t2 USING(table_name,table_schema)
WHERE t2.table_schema = 'db_datadict'
ORDER BY table_name;
#
# No schema assigned in SELECT + we are in SCHEMA test
#    --> The table tables does not exist
USE test;
--error ER_NO_SUCH_TABLE
SELECT * FROM tables;


--echo #########################################################################
--echo # Testcase 3.2.1.17+3.2.1.18
--echo #########################################################################
# 3.2.1.17: Ensure that the SELECT privilege is granted TO PUBLIC WITH GRANT
#           OPTION on every INFORMATION_SCHEMA table.
#
# 3.2.1.18: Ensure that the CREATE VIEW privilege on an INFORMATION_SCHEMA table
#           may be granted to any user.
#
# Note (mleich): The requirements are to some extend outdated.
#                Every user is allowed to SELECT on the INFORMATION_SCHEMA.
#                But the result sets depend on the privileges of the user.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <some_engine>
eval
CREATE TABLE db_datadict.t1 (f1 BIGINT UNIQUE, f2 BIGINT)
ENGINE = $engine_type;
SELECT * FROM db_datadict.t1;

--error 0,ER_CANNOT_USER
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
--error 0,ER_CANNOT_USER
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
GRANT CREATE VIEW,SELECT ON db_datadict.* TO testuser1@localhost