ps_1general.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

###################### ps_general.test #######################
#                                                            #
#   basic and miscellaneous tests for prepared statements    #
#                                                            #
##############################################################

#    
# NOTE: PLEASE SEE THE DETAILED DESCRIPTION AT THE BOTTOM OF THIS FILE
#       BEFORE ADDING NEW TEST CASES HERE !!!

--disable_warnings
drop table if exists t5, t6, t7, t8;
drop database if exists mysqltest ;

# Cleanup from other tests
drop database if exists client_test_db;
drop database if exists testtets;
drop table if exists t1Aa,t2Aa,v1Aa,v2Aa;
drop view if exists t1Aa,t2Aa,v1Aa,v2Aa;
--enable_warnings

--disable_query_log
select '------ basic tests ------' as test_sequence ;
--enable_query_log

let $type= 'MYISAM' ;
# create the tables (t1 and t9) used in many tests
--source include/ps_create.inc
# insert data into these tables
--source include/ps_renew.inc


################ The basic functions ################

# 1. PREPARE stmt_name FROM <preparable statement>;
#    <preparable statement> ::=
#     'literal_stmt' |
#     @variable_ref_stmt.
#    The statement may contain question marks as placeholders for parameters.
#
#    Bind a statement name to a string containing a SQL statement and
#    send it to the server. The server will parse the statement and
#    reply with "Query Ok" or an error message.
#
PREPARE stmt FROM ' select * from t1 where a = ? ' ;

# 2. EXECUTE stmt_name [USING @var [, @var ]];
#    Current values of supplied variables are used as parameters.
#
#    Send the server the order to execute the statement and supply values
#    for the input parameters needed.
#    If no error occurs the server reply will be identical to the reply for
#    the query used in PREPARE with question marks replaced with values of
#    the input variables.
#
SET @var= 2 ;
EXECUTE stmt USING @var ;
#    The non prepared statement with the same server reply would be:
select * from t1 where a = @var ;

# 3. DEALLOCATE PREPARE stmt_name;
#
#    Send the server the order to drop the parse informations.
#    The server will reply with "Query Ok" or an error message.
DEALLOCATE PREPARE stmt ;

################ PREPARE ################
# prepare without parameter
prepare stmt1 from ' select 1 as my_col ' ;
# prepare with parameter
prepare stmt1 from ' select ? as my_col ' ;
# prepare must fail (incomplete statements/wrong syntax)
--error 1064
prepare ;
--error 1064
prepare stmt1 ;
--error 1064
prepare stmt1 from ;
--error 1064
prepare_garbage stmt1 from ' select 1 ' ;
--error 1064
prepare stmt1 from_garbage ' select 1 ' ;
--error 1064
prepare stmt1 from ' select_garbage 1 ' ;
--error 1064
prepare from ' select 1 ' ;
--error 1064
prepare stmt1 ' select 1 ' ;
--error 1064
prepare ? from ' select ? as my_col ' ;
# statement in variable
set @arg00='select 1 as my_col';
prepare stmt1 from @arg00;
# prepare must fail (query variable is empty)
set @arg00='';
--error 1065
prepare stmt1 from @arg00;
set @arg00=NULL;
# prepare must fail (query variable is NULL)
--error 1064
prepare stmt1 from @arg01;

prepare stmt1 from ' select * from t1 where a <= 2 ' ;
# prepare must fail (column x does not exist)
--error 1054
prepare stmt1 from ' select * from t1 where x <= 2 ' ;
# cases derived from client_test.c: test_null()
# prepare must fail (column x does not exist)
--error 1054
prepare stmt1 from ' insert into t1(a,x) values(?,?) ' ;
--error 1054
prepare stmt1 from ' insert into t1(x,a) values(?,?) ' ;
--disable_warnings
drop table if exists not_exist ;
--enable_warnings
# prepare must fail (table does not exist)
--error 1146
prepare stmt1 from ' select * from not_exist where a <= 2 ' ;

# case derived from client_test.c: test_prepare_syntax()
# prepare must fail (incomplete statement)
--error 1064
prepare stmt1 from ' insert into t1 values(? ' ;
--error 1064
prepare stmt1 from ' select a, b from t1
                     where a=? and where ' ;

################ EXECUTE ################
# execute must fail (statement never_prepared never prepared)
--error 1243
execute never_prepared ;
# execute must fail (prepare stmt1 just failed,
#         but there was a successful prepare of stmt1 before)
prepare stmt1 from ' select * from t1 where a <= 2 ' ;
--error 1146
prepare stmt1 from ' select * from not_exist where a <= 2 ' ;
--error 1243
execute stmt1 ;

# drop the table between prepare and execute
create table t5
(
  a int primary key,
  b char(30),
  c int
);
insert into t5( a, b, c) values( 1, 'original table', 1);
prepare stmt2 from ' select * from t5 ' ;
execute stmt2 ;
drop table t5 ;
# execute must fail (table was dropped after prepare)
--error 1146
execute stmt2 ;
# cases derived from client_test.c: test_select_prepare()
# 1. drop + create table (same column names/types/order) 
# between prepare and execute
create table t5
(
  a int primary key,
  b char(30),
  c int
);
insert into t5( a, b, c) values( 9, 'recreated table', 9);
execute stmt2 ;
drop table t5 ;
# 2. drop + create table (same column names/types but different order)
# between prepare and execute
create table t5
(
  a int primary key,
  c int,
  b char(30)
);
insert into t5( a, b, c) values( 9, 'recreated table', 9);
execute stmt2 ;
drop table t5 ;
# 3. drop + create table (same column names/types/order+extra column) 
# between prepare and execute
create table t5
(
  a int primary key,
  b char(30),
  c int,
  d timestamp default current_timestamp
);
insert into t5( a, b, c) values( 9, 'recreated table', 9);
execute stmt2 ;
drop table t5 ;
# 4. drop + create table (same column names/types, different order +
# additional column) between prepare and execute
create table t5
(
  a int primary key,
  d timestamp default current_timestamp,
  b char(30),
  c int
);
insert into t5( a, b, c) values( 9, 'recreated table', 9);
execute stmt2 ;
drop table t5 ;
# 5. drop + create table (same column names/order, different types)
# between prepare and execute
create table t5
(
  a timestamp default '2004-02-29 18:01:59',
  b char(30),
  c int
);
insert into t5( b, c) values( 'recreated table', 9);
execute stmt2 ;
drop table t5 ;
# 6. drop + create table (same column types/order, different names) 
# between prepare and execute
create table t5
(
  f1 int primary key,
  f2 char(30),
  f3 int
);
insert into t5( f1, f2, f3) values( 9, 'recreated table', 9);
--error 1054
execute stmt2 ;
drop table t5 ;

# execute without parameter
prepare stmt1 from ' select * from t1 where a <= 2 ' ;
execute stmt1 ;
# execute with parameter
set @arg00=1 ;
set @arg01='two' ;
prepare stmt1 from ' select * from t1 where a <= ? ' ;
execute stmt1 using @arg00;
# execute must fail (too small number of parameters)
--error 1210
execute stmt1 ;
# execute must fail (too big number of parameters)
--error 1210
execute stmt1 using @arg00, @arg01;
# execute must fail (parameter is not set)
execute stmt1 using @not_set;

################ DEALLOCATE ################
# deallocate must fail (the statement 'never_prepared' was never prepared)
--error 1243
deallocate prepare never_prepared ;
# deallocate must fail (prepare stmt1 just failed,
#         but there was a successful prepare before)
prepare stmt1 from ' select * from t1 where a <= 2 ' ;
--error 1146
prepare stmt1 from ' select * from not_exist where a <= 2 ' ;
--error 1243
deallocate prepare stmt1;
create table t5
(
  a int primary key,
  b char(10)
);
prepare stmt2 from ' select a,b from t5 where a <= 2 ' ;
drop table t5 ;
# deallocate prepared statement where the table was dropped after prepare
deallocate prepare stmt2;

## parallel use of more than one prepared statement handlers
# switch between different queries
prepare stmt1 from ' select a from t1 where a <= 2 ' ;
prepare stmt2 from ' select b from t1 where a <= 2 ' ;
execute stmt2 ;
execute stmt1 ;
# switch between statement handlers of the same query
prepare stmt1 from ' select a from t1 where a <= 2 ' ;
prepare stmt2 from ' select a from t1 where a <= 2 ' ;
execute stmt2 ;
execute stmt1 ;
deallocate prepare stmt1 ;
# Will the deallocate of stmt1 with the same query affect stmt2 ?
execute stmt2 ;

--disable_query_log
select '------ show and misc tests ------' as test_sequence ;
--enable_query_log

--disable_warnings
drop table if exists t2;
--enable_warnings
create table t2 
(
  a int primary key, b char(10)
);

################ SHOW COMMANDS ################
prepare stmt4 from ' show databases ';
execute stmt4;
prepare stmt4 from ' show tables from test like ''t2%'' ';
execute stmt4;
prepare stmt4 from ' show columns from t2 where field in (select ?) ';
SET @arg00="a";
execute stmt4 using @arg00;
SET @arg00="b";
execute stmt4 using @arg00;
SET @arg00=1;
execute stmt4 using @arg00;

prepare stmt4 from ' show columns from t2 from test like ''a%'' ';
execute stmt4;
create index t2_idx on t2(b);
prepare stmt4 from ' show index from t2 from test ';
execute stmt4;
prepare stmt4 from ' show table status from test like ''t2%'' ';
# egalize date and time values
--replace_column 8 # 12 # 13 # 14 #
# Bug#4288 : prepared statement 'show table status ..', wrong output on execute
execute stmt4;
# try the same with the big table
prepare stmt4 from ' show table status from test like ''t9%'' ';
# egalize date and time values
--replace_column 8 # 12 # 13 # 14 #
# Bug#4288
execute stmt4;
prepare stmt4 from ' show status like ''Threads_running'' ';
--replace_column 2 #
execute stmt4;
prepare stmt4 from ' show variables like ''sql_mode'' ';
execute stmt4;
prepare stmt4 from ' show engine bdb logs ';
# The output depends on the history (actions of the bdb engine).
# That is the reason why, we switch the output here off.
#   (The real output will be tested in ps_6bdb.test)
# --replace_result $MYSQL_TEST_DIR TEST_DIR
--disable_result_log
execute stmt4;
--enable_result_log
prepare stmt4 from ' show grants for user ';
--error 1295
prepare stmt4 from ' show create table t2 ';
--error 1295
prepare stmt4 from ' show master status ';
--error 1295
prepare stmt4 from ' show master logs ';
--error 1295
prepare stmt4 from ' show slave status ';
--error 1295
prepare stmt4 from ' show warnings limit 20 ';
--error 1295
prepare stmt4 from ' show errors limit 20 ';
prepare stmt4 from ' show storage engines ';
--replace_column 2 YES/NO
execute stmt4;

################ MISC STUFF ################
## get a warning and an error
# cases derived from client_test.c: test_warnings(), test_errors()
--disable_warnings
drop table if exists t5;
--enable_warnings
prepare stmt1 from ' drop table if exists t5 ' ;
execute stmt1 ;
prepare stmt1 from ' drop table t5 ' ;
--error 1051
execute stmt1 ;

## SELECT @@version
# cases derived from client_test.c: test_select_version()
#
# TODO: Metadata check is temporary disabled here, because metadata of 
# this statement also depends on @@version contents and you can't apply
# replace_column and replace_result to it. It will be enabled again when 
# support of replace_column and replace_result on metadata will be
# implemented.
#
#--enable_metadata
prepare stmt1 from ' SELECT @@version ' ;
# egalize the version
--replace_column 1 <version>
execute stmt1 ;
#--disable_metadata

## do @var:= and set @var=
# cases derived from client_test.c: test_do_set()
prepare stmt_do from ' do @var:=  (1 in (select a from t1)) ' ;
prepare stmt_set from ' set @var= (1 in (select a from t1)) ' ;
let $1= 3 ;
while ($1)
{
  execute stmt_do ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  execute stmt_set ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  dec $1 ;
}
# the same test with a table containing one column and 'select *'
--disable_warnings
drop table if exists t5 ;
--enable_warnings
create table t5 (a int) ;
prepare stmt_do from ' do @var:=  (1 in (select a from t5)) ' ;
prepare stmt_set from ' set @var= (1 in (select a from t5)) ' ;
let $1= 3 ;
while ($1)
{
  execute stmt_do ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  execute stmt_set ;
  --disable_query_log
  select @var as 'content of @var is:' ;
  --enable_query_log
  dec $1 ;
}
drop table t5 ;
deallocate prepare stmt_do ;
deallocate prepare stmt_set ;

## nonsense like prepare of prepare,execute or deallocate
--error ER_UNSUPPORTED_PS 
prepare stmt1 from ' prepare stmt2 from '' select 1 ''  ' ;
--error ER_UNSUPPORTED_PS 
prepare stmt1 from ' execute stmt2 ' ;
--error ER_UNSUPPORTED_PS 
prepare stmt1 from ' deallocate prepare never_prepared ' ;

## We don't support alter view as prepared statements
--error ER_UNSUPPORTED_PS
prepare stmt1 from 'alter view v1 as select 2';

## switch the database connection
--error 1295
prepare stmt4 from ' use test ' ;

## create/drop database
--error 1295
prepare stmt3 from ' create database mysqltest ';
create database mysqltest ;
--error 1295
prepare stmt3 from ' drop database mysqltest ';
drop database mysqltest ;

#### table related commands
## describe
prepare stmt3 from ' describe t2 ';
execute stmt3;
drop table t2 ;
--error 1146
execute stmt3;
## lock/unlock
--error 1295
prepare stmt3 from ' lock tables t1 read ' ;
--error 1295
prepare stmt3 from ' unlock tables ' ;
## Load/Unload table contents
--error 1295
prepare stmt1 from ' load data infile ''data.txt''
into table t1 fields terminated by ''\t'' ';
prepare stmt1 from ' select * into outfile ''data.txt'' from t1 ';
execute stmt1 ;
## 
prepare stmt1 from ' optimize table t1 ' ;
prepare stmt1 from ' analyze table t1 ' ;
--error 1295
prepare stmt1 from ' checksum table t1 ' ;
prepare stmt1 from ' repair table t1 ' ;
--error 1295
prepare stmt1 from ' restore table t1 from ''data.txt'' ' ;
## handler
--error 1295