view.test

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

prepare stmt1 from "update v1,t1 set v1.s1=? where t1.s1=v1.s1";
set @arg='d';
execute stmt1 using @arg;
select * from v1;
set @arg='e';
execute stmt1 using @arg;
select * from v1;
deallocate prepare stmt1;
drop view v1;
drop table t1;

#
# test view with LOCK TABLES (work around)
#
create table t1 (a int);
create table t2 (a int);
create view v1 as select * from t1;
lock tables t1 read, v1 read;
select * from v1;
-- error 1100
select * from t2;
drop view v1;
drop table t1, t2;

#
# WITH CHECK OPTION insert/update test
#
create table t1 (a int);
create view v1 as select * from t1 where a < 2 with check option;
# simple insert
insert into v1 values(1);
-- error 1369
insert into v1 values(3);
# simple insert with ignore
insert ignore into v1 values (2),(3),(0);
select * from t1;
# prepare data for next check
delete from t1;
# INSERT SELECT test
insert into v1 SELECT 1;
-- error 1369
insert into v1 SELECT 3;
# prepare data for next check
create table t2 (a int);
insert into t2 values (2),(3),(0);
# INSERT SELECT with ignore test
insert ignore into v1 SELECT a from t2;
select * from t1;
#simple UPDATE test
update v1 set a=-1 where a=0;
-- error 1369
update v1 set a=2 where a=1;
select * from t1;
# prepare data for next check
update v1 set a=0 where a=0;
insert into t2 values (1);
# multiupdate test
update v1,t2 set v1.a=v1.a-1 where v1.a=t2.a;
select * from t1;
# prepare data for next check
update v1 set a=a+1;
# multiupdate with ignore test
update ignore v1,t2 set v1.a=v1.a+1 where v1.a=t2.a;
select * from t1;

drop view v1;
drop table t1, t2;

#
# CASCADED/LOCAL CHECK OPTION test
#
create table t1 (a int);
create view v1 as select * from t1 where a < 2 with check option;
create view v2 as select * from v1 where a > 0 with local check option;
create view v3 as select * from v1 where a > 0 with cascaded check option;
insert into v2 values (1);
insert into v3 values (1);
-- error 1369
insert into v2 values (0);
-- error 1369
insert into v3 values (0);
insert into v2 values (2);
-- error 1369
insert into v3 values (2);
select * from t1;
drop view v3,v2,v1;
drop table t1;

#
# CHECK OPTION with INSERT ... ON DUPLICATE KEY UPDATE
#
create table t1 (a int, primary key (a));
create view v1 as select * from t1 where a < 2 with check option;
insert into v1 values (1) on duplicate key update a=2;
-- error 1369
insert into v1 values (1) on duplicate key update a=2;
insert ignore into v1 values (1) on duplicate key update a=2;
select * from t1;
drop view v1;
drop table t1;

#
# check cyclic referencing protection on altering view
#
create table t1 (s1 int);
create view v1 as select * from t1;
create view v2 as select * from v1;
-- error 1146
alter view v1 as select * from v2;
-- error 1146
alter view v1 as select * from v1;
-- error 1146
create or replace view v1 as select * from v2;
-- error 1146
create or replace view v1 as select * from v1;
drop view v2,v1;
drop table t1;

#
# check altering differ options
#
create table t1 (a int);
create view v1 as select * from t1;
show create view v1;
alter algorithm=undefined view v1 as select * from t1 with check option;
show create view v1;
alter algorithm=merge view v1 as select * from t1 with cascaded check option;
show create view v1;
alter algorithm=temptable view v1 as select * from t1;
show create view v1;
drop view v1;
drop table t1;

#
# updating view with subquery in the WHERE clause
#
create table t1 (s1 int);
create table t2 (s1 int);
create view v2 as select * from t2 where s1 in (select s1 from t1);
insert into v2 values (5);
insert into t1 values (5);
select * from v2;
update v2 set s1 = 0;
select * from v2;
select * from t2;
# check it with check option
alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option;
insert into v2 values (5);
-- error 1369
update v2 set s1 = 1;
insert into t1 values (1);
update v2 set s1 = 1;
select * from v2;
select * from t2;
# scheck how VIEWs with subqueries work with prepared statements
prepare stmt1 from "select * from v2;";
execute stmt1;
insert into t1 values (0);
execute stmt1;
deallocate prepare stmt1;
drop view v2;
drop table t1, t2;

#
# test of substring_index with view
#
create table t1 (t time);
create view v1 as select substring_index(t,':',2) as t from t1;
insert into t1 (t) values ('12:24:10');
select substring_index(t,':',2) from t1;
select substring_index(t,':',2) from v1;
drop view v1;
drop table t1;

#
# test of cascaded check option for whiew without WHERE clause
#
create table t1 (s1 tinyint);
create view v1 as select * from t1 where s1 <> 0 with local check option;
create view v2 as select * from v1 with cascaded check option;
-- error 1369
insert into v2 values (0);
drop view v2, v1;
drop table t1;

#
# inserting single value with check option failed always get error
#
create table t1 (s1 int);
create view v1 as select * from t1 where s1 < 5 with check option;
#single value
-- error 1369
insert ignore into v1 values (6);
#several values
insert ignore into v1 values (6),(3);
select * from t1;
drop view v1;
drop table t1;

#
# changing value by trigger and CHECK OPTION
#
create table t1 (s1 tinyint);
create trigger t1_bi before insert on t1 for each row set new.s1 = 500;
create view v1 as select * from t1 where s1 <> 127 with check option;
-- error 1369
insert into v1 values (0);
select * from v1;
select * from t1;
drop trigger t1_bi;
drop view v1;
drop table t1;

#
# CASCADED should be used for all underlaying VIEWs
#
create table t1 (s1 tinyint);
create view v1 as select * from t1 where s1 <> 0;
create view v2 as select * from v1 where s1 <> 1 with cascaded check option;
-- error 1369
insert into v2 values (0);
select * from v2;
select * from t1;
drop view v2, v1;
drop table t1;

#
# LOAD DATA with view and CHECK OPTION
#
# fixed length fields
create table t1 (a int, b char(10));
create view v1 as select * from t1 where a != 0 with check option;
-- error 1369
load data infile '../std_data_ln/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines;
select * from t1;
select * from v1;
delete from t1;
load data infile '../std_data_ln/loaddata3.dat' ignore into table v1 fields terminated by '' enclosed by '' ignore 1 lines;
select * from t1;
select * from v1;
drop view v1;
drop table t1;
# variable length fields
create table t1 (a text, b text);
create view v1 as select * from t1 where a <> 'Field A' with check option;
-- error 1369
load data infile '../std_data_ln/loaddata2.dat' into table v1 fields terminated by ',' enclosed by '''';
select concat('|',a,'|'), concat('|',b,'|') from t1;
select concat('|',a,'|'), concat('|',b,'|') from v1;
delete from t1;
load data infile '../std_data_ln/loaddata2.dat' ignore into table v1 fields terminated by ',' enclosed by '''';
select concat('|',a,'|'), concat('|',b,'|') from t1;
select concat('|',a,'|'), concat('|',b,'|') from v1;
drop view v1;
drop table t1;

#
# Trys update table from which we select using views and subqueries
#
create table t1 (s1 smallint);
create view v1 as select * from t1 where 20 < (select (s1) from t1);
-- error 1471
insert into v1 values (30);
create view v2 as select * from t1;
create view v3 as select * from t1 where 20 < (select (s1) from v2);
-- error 1471
insert into v3 values (30);
create view v4 as select * from v2 where 20 < (select (s1) from t1);
-- error 1471
insert into v4 values (30);
drop view v4, v3, v2, v1;
drop table t1;

#
# CHECK TABLE with VIEW
#
create table t1 (a int);
create view v1 as select * from t1;
check table t1,v1;
check table v1,t1;
drop table t1;
check table v1;
drop view v1;

#
# merge of VIEW with several tables
#
create table t1 (a int);
create table t2 (a int);
create table t3 (a int);
insert into t1 values (1), (2), (3);
insert into t2 values (1), (3);
insert into t3 values (1), (2), (4);
# view over tables
create view v3 (a,b) as select t1.a as a, t2.a as b from t1 left join t2 on (t1.a=t2.a);
select * from t3 left join v3 on (t3.a = v3.a);
explain extended select * from t3 left join v3 on (t3.a = v3.a);
# view over views
create view v1 (a) as select a from t1;
create view v2 (a) as select a from t2;
create view v4 (a,b) as select v1.a as a, v2.a as b from v1 left join v2 on (v1.a=v2.a);
select * from t3 left join v4 on (t3.a = v4.a);
explain extended select * from t3 left join v4 on (t3.a = v4.a);
# PS with view over views
prepare stmt1 from "select * from t3 left join v4 on (t3.a = v4.a);";
execute stmt1;
execute stmt1;
deallocate prepare stmt1;
drop view v4,v3,v2,v1;
drop tables t1,t2,t3;

#
# updating of join view
#
create table t1 (a int, primary key (a), b int);
create table t2 (a int, primary key (a));
insert into t1 values (1,100), (2,200);
insert into t2 values (1), (3);
# legal view for update
create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
update v3 set a= 10 where a=1;
select * from t1;
select * from t2;
# view without primary key
create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2;
set updatable_views_with_limit=NO;
-- error 1288
update v2 set a= 10 where a=200 limit 1;
set updatable_views_with_limit=DEFAULT;
# just view selects
select * from v3;
select * from v2;
# prepare statement with updating join view
set @a= 10;
set @b= 100;
prepare stmt1 from "update v3 set a= ? where a=?";
execute stmt1 using @a,@b;
select * from v3;
set @a= 300;
set @b= 10;
execute stmt1 using @a,@b;
select * from v3;
deallocate prepare stmt1;
drop view v3,v2;
drop tables t1,t2;

#
# inserting/deleting join view
#
create table t1 (a int, primary key (a), b int);
create table t2 (a int, primary key (a), b int);
insert into t2 values (1000, 2000);
create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
# inserting into join view without field list
-- error 1394
insert into v3 values (1,2);
-- error 1394
insert into v3 select * from t2;
# inserting in several tables of join view
-- error 1393
insert into v3(a,b) values (1,2);
-- error 1393
insert into v3(a,b) select * from t2;
# correct inserts into join view
insert into v3(a) values (1);
insert into v3(b) values (10);
insert into v3(a) select a from t2;
insert into v3(b) select b from t2;
insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a);
select * from t1;
select * from t2;
# try delete from join view
-- error 1395
delete from v3;
-- error 1395
delete v3,t1 from v3,t1;
-- error 1395
delete t1,v3 from t1,v3;
# delete from t1 just to reduce result set size
delete from t1;
# prepare statement with insert join view
prepare stmt1 from "insert into v3(a) values (?);";
set @a= 100;
execute stmt1 using @a;
set @a= 300;
execute stmt1 using @a;
deallocate prepare stmt1;
prepare stmt1 from "insert into v3(a) select ?;";
set @a= 101;
execute stmt1 using @a;
set @a= 301;
execute stmt1 using @a;
deallocate prepare stmt1;
select * from v3;

drop view v3;
drop tables t1,t2;

#
# View field names should be case insensitive 
#
create table t1(f1 int);
create view v1 as select f1 from t1;
select * from v1 where F1 = 1;
drop view v1;
drop table t1;

#
# Resolving view fields in subqueries in VIEW (Bug #6394)
#
create table t1(c1 int);
create table t2(c2 int);
insert into t1 values (1),(2),(3);
insert into t2 values (1);
SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
create view v1 as SELECT c1 FROM t1 WHERE c1 IN (SELECT c2 FROM t2);
create view v2 as SELECT c1 FROM t1 WHERE EXISTS (SELECT c2 FROM t2 WHERE c2 = c1);
select * from v1;
select * from v2;
select * from (select c1 from v2) X;
drop view v2, v1;
drop table t1, t2;

#
# view over other view setup (BUG#7433)
#
CREATE TABLE t1 (C1 INT, C2 INT);
CREATE TABLE t2 (C2 INT);
CREATE VIEW v1 AS SELECT C2 FROM t2;
CREATE VIEW v2 AS SELECT C1 FROM t1 LEFT OUTER JOIN v1 USING (C2);
SELECT * FROM v2;
drop view v2, v1;
drop table t1, t2;

#
# view and group_concat() (BUG#7116)
#
create table t1 (col1 char(5),col2 int,col3 int); 
insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25); 
create view v1 as select * from t1;
select col1,group_concat(col2,col3) from t1 group by col1;
select col1,group_concat(col2,col3) from v1 group by col1;
drop view v1;
drop table t1;

#
# Item_ref resolved as view field (BUG#6894)
#
create table t1 (s1 int, s2 char);
create view v1 as select s1, s2 from t1;
-- error 1054
select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2);
select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa);
drop view v1;
drop table t1;

#
# Test case for bug #9398 CREATE TABLE with SELECT from a multi-table view
#
CREATE TABLE t1 (a1 int);
CREATE TABLE t2 (a2 int);
INSERT INTO t1 VALUES (1), (2), (3), (4);
INSERT INTO t2 VALUES (1), (2), (3);
CREATE VIEW v1(a,b) AS SELECT a1,a2 FROM t1 JOIN t2 ON a1=a2 WHERE a1>1;

SELECT * FROM v1;
CREATE TABLE t3 SELECT * FROM v1;
SELECT * FROM t3;

DROP VIEW v1;
DROP TABLE t1,t2,t3;

#
# Test for BUG#8703 "insert into table select from view crashes"
#
create table t1 (a int);
create table t2 like t1;
create table t3 like t1;
create view v1 as select t1.a x, t2.a y from t1 join t2 where t1.a=t2.a;
insert into t3 select x from v1;
insert into t2 select x from v1;
drop view v1;
drop table t1,t2,t3;

#
# Test for BUG #6106: query over a view using subquery for the underlying table
#

CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); 
INSERT INTO t1 VALUES(1,'trudy'); 
INSERT INTO t1 VALUES(2,'peter'); 
INSERT INTO t1 VALUES(3,'sanja'); 
INSERT INTO t1 VALUES(4,'monty');