views_master.inc

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

Create database test3 ;

Create table test3.t1(f59 int,f60 int) ;
Insert into test3.t1 values (1023,7670) ;
Create view test3.v1_2 as Select f59,f60 from test3.t1 ;
Select * from test3.v1_2 ;
use test ;

# ML: FIXME The SELECT should deliver at least one row.
Create view v1_main
as SELECT test.tb2.f59 as f1, test1.v1_1.f59 as f2,
          test3.v1_2.f59 as f3
FROM (test.tb2,test1.v1_1,test.v1) JOIN test3.v1_2
ON (test.v1.f59 = test1.v1_1.f59) ;
Select * from v1_main ;

DROP VIEW test.v1 ;
DROP VIEW test1.v1_1 ;
DROP VIEW test.v1_main ;
DROP DATABASE test3;


let $message= Testcase 3.3.1.39 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.39: Ensure that a view definition that includes a subquery in
#                    a FROM clause is rejected with an appropriate error
#                    message at create time.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
--error 1349
CREATE VIEW test.v1
AS Select f59 from (Select * FROM tb2 limit 20) tx ;
--error 1146
SELECT * FROM test.v1      ;
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings


let $message= Testcase 3.3.1.40 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.40: Ensure that a view definition that includes references to
#                    one or more user variables is rejected with an appropriate
#                    error message at create time.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
Set @var1 = 'ABC' ;
Set @var2 = 'XYZ' ;
--error 1351
CREATE VIEW test.v1 AS SELECT @var1, @var2 ;
# System variables (name starts with '@@') are also not allowed
--error 1351
CREATE VIEW test.v1 AS SELECT @@global.sort_buffer_size;
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings


let $message= Testcase 3.3.1.41 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.41: Ensure that a view definition within a stored procedure
#                    definition cannot include references to any of the stored
#                    procedure’s parameters.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
Drop procedure if exists sp1 ;
--enable_warnings

delimiter //;
Create procedure sp1() DETERMINISTIC
      Begin
              DECLARE x char;
              Set x = 200 ;
              Create view test.v1  as SELECT * FROM tb2 WHERE f59 = x ;
      End   //
delimiter ;//
--error ER_SP_DOES_NOT_EXIST
Call sp1() ;
Drop view if exists test.v1 ;
Drop procedure sp1 ;


let $message= Testcase 3.3.1.42 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.42: Ensure that a view definition that attempts to create a
#                    temporary view (e.g. CREATE TEMPORARY VIEW or CREATE OR
#                    REPLACE TEMPORARY VIEW) fails, with an appropriate
#                    error message.
###############################################################################
#(01)
--disable_warnings
Drop VIEW if exists test.v1 ;
--enable_warnings
--error 1064
CREATE TEMPORARY VIEW test.v1 AS
SELECT * FROM test.tb2 limit 2 ;
#(02)
--error 1064
CREATE OR REPLACE TEMPORARY VIEW test.v1 AS
SELECT * FROM test.tb2 limit 2 ;

--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings

Use test;


let $message= Testcase 3.3.1.43 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.43: Ensure that all valid changes (i.e. INSERT, UPDATE, DELETE
#                    statements) to a view are shown in the view and are
#                    accepted as changes by the underlying table(s).
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings

CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2;

INSERT INTO test.v1 values(122,432);

if ($have_bug_11589)
{
--disable_ps_protocol
}
SELECT * FROM test.tb2 where f59 = 122 and f60 = 432 limit 0,20;
--enable_ps_protocol

UPDATE test.v1 SET f59 = 3000 WHERE test.v1.f59 = 122 ;

if ($have_bug_11589)
{
--disable_ps_protocol
}
SELECT * FROM test.tb2  where f59 = 3000 limit 0,20;
--enable_ps_protocol

DELETE FROM test.v1
where test.v1.f59 = 3000 and test.v1.f60 = 432;

SELECT * FROM test.tb2 where f59 = 3000 and f60 = 432;

drop view test.v1 ;


let $message= Testcase 3.3.1.44 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.44: Ensure that all invalid changes to a view are rejected
#                    with an appropriate error message and do not affect the
#                    data in the underlying tables(s).
###############################################################################
# ML: Maybe we need some more tests here.
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings

# Note(ML): The modification will fail, because the VIEW contains 'limit'
CREATE VIEW test.v1 AS SELECT f59,f60 FROM test.tb2 limit 100;

--error ER_NON_INSERTABLE_TABLE
INSERT INTO test.v1 values(31, 32, 33) ;

Drop view test.v1 ;


let $message= Testcase 3.3.1.45 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.45: Ensure that, for a view with a definition that does not
#                    include WITH CHECK OPTION, all changes to the view which
#                    violate the view definition do not show in the view but
#                    are accepted as changes by the underlying table(s) unless
#                    a constraint on an underlying table also makes the change
#                    invalid.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
CREATE VIEW test.v1 AS SELECT * FROM test.tb2 where f59 = 04;

--enable_info
UPDATE test.v1 SET f59 = 30 where F59 = 04 ;
--disable_info
SELECT * FROM test.v1   where f59 = 30 ;
if ($have_bug_11589)
{
--disable_ps_protocol
}
SELECT * FROM test.tb2        where f59 = 30 ;
--enable_ps_protocol

--enable_info
UPDATE tb2 SET f59 = 100 where f59 = 30 ;
--disable_info
if ($have_bug_11589)
{
--disable_ps_protocol
}
SELECT * FROM tb2 where f59 = 100 ;
--enable_ps_protocol
SELECT * FROM test.v1 ;

drop view if exists test.v1 ;

--disable_warnings
Drop TABLE IF EXISTS test.t1 ;
Drop VIEW  IF EXISTS test.v1 ;
--enable_warnings
eval CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), PRIMARY KEY(f1))
     ENGINE = $engine_type;
INSERT INTO t1 VALUES(1,'one');
INSERT INTO t1 VALUES(2,'two');
INSERT INTO t1 VALUES(3,'three');
INSERT INTO t1 VALUES(5,'five');
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 BETWEEN 2 AND 4;

### SELECTs
# 1.   Searched record is within the scope of the view
# 1.1  + exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 2;
# 1.2  + does not exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 4;
# 2.   Searched record is outside of the scope of the view
# 2.1  + exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 5;
# 2.2  + does not exists within the base table
SELECT COUNT(*) FROM v1 WHERE f1 = 10;

INSERT INTO t1 VALUES(4,'four');

### DELETEs
--enable_info
# 1.   Searched record is within the scope of the view
#      + exists within the base table
DELETE FROM v1 WHERE f1 = 3;
# 2.   Searched record is outside of the scope of the view
#      + exists within the base table
DELETE FROM v1 WHERE f1 = 5;
--disable_info
SELECT * FROM t1 ORDER BY f1;
SELECT * FROM v1 ORDER BY f1;

### INSERTs
--enable_info
# 1.   The record to be inserted will be within the scope of the view.
#      But there is already a record with the PRIMARY KEY f1 = 2 .
--error 1062
INSERT INTO v1 VALUES(2,'two');
# 2.   The record to be inserted will be within the scope of the view.
#      There is no already existing record with the PRIMARY KEY f1 = 3 .
INSERT INTO v1 VALUES(3,'three');
# 3.   The record to be inserted will be outside of the scope of the view.
#      There is no already existing record with the PRIMARY KEY f1 = 6 .
INSERT INTO v1 VALUES(6,'six');
--disable_info
SELECT * FROM t1 ORDER BY f1;
SELECT * FROM v1 ORDER BY f1;

### UPDATEs
--enable_info
# 1.   The record to be updated is within the scope of the view
#      and will stay inside the scope.
#      But there is already a record with the PRIMARY KEY f1 = 2 .
--error 1062
UPDATE v1 SET f1 = 2 WHERE f1 = 3;
# 2.   The record to be updated is within the scope of the view
#      and will stay inside the scope.
UPDATE v1 SET f2 = 'number' WHERE f1 = 3;
# 3.   The record to be updated is within the scope of the view
#      and will leave the scope.
UPDATE v1 SET f1 = 10 WHERE f1 = 3;
# 4.   The record to be updated is outside of the scope of the view.
UPDATE v1 SET f2 = 'number' WHERE f1 = 1;
--disable_info


let $message= Testcase 3.3.1.46 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.46: Ensure that, for a view with a definition that does
#                    include WITH CHECK OPTION, all changes to the view which
#                    violate the view definition are rejected with an
#                    appropriate error message and are not accepted as changes
#                    by the underlying table(s).
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
CREATE VIEW test.v1 AS SELECT f59,f60
FROM test.tb2 where f59 = 195 WITH CHECK OPTION ;

--error 1369
UPDATE test.v1 SET f59 = 198 where f59=195 ;
SELECT * FROM test.v1 ;

drop view if exists test.v1 ;


let $message= Testcase 3.3.1.47 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.47: Ensure that, for a view with a definition that does
#                    include WITH LOCAL CHECK OPTION, all changes to the view
#                    which violate the view definition are rejected with an
#                    appropriate error message and are not accepted as changes
#                    by the underlying table(s).
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
Drop view if exists test.v2 ;
--enable_warnings
CREATE VIEW test.v1 AS SELECT f59,f60
FROM test.tb2 where F59 = 0987 WITH LOCAL CHECK OPTION ;
CREATE VIEW test.v2 as SELECT * FROM test.v1 ;

# This UPDATE violates the definition of VIEW test.v1.
--error 1369
UPDATE test.v1 SET F59 = 919 where f59 = 0987 ;
SELECT * FROM test.v1 ;

# ML: This UPDATE violates the definition of VIEW test.v1, but this
#     does not count, because the UPDATE runs on test.v2, which
#     is defined without any CHECK OPTION.
#     FIXME Does this testcase fit to 3.3.1.47 ?
UPDATE test.v2 SET F59 = 9879 where f59 = 919 ;
SELECT * FROM tb2 where f59 = 9879 ;

drop view if exists v1 ;
drop view if exists v2 ;


let $message= Testcase 3.3.1.48 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.48: Ensure that, for a view with a definition that does
#                    include WITH CASCADED CHECK OPTION, all changes to the
#                    view which violate the view definition are rejected with
#                    an appropriate error message and are not accepted as
#                    changes by the underlying table(s).
###############################################################################
--disable_warnings
DROP TABLE IF EXISTS test.t1;
DROP VIEW  IF EXISTS test.v1;
--enable_warnings
eval CREATE TABLE t1 (f1 ENUM('A', 'B', 'C') NOT NULL, f2 INTEGER)
     ENGINE = $engine_type;
INSERT INTO t1 VALUES ('A', 1);
SELECT * FROM t1;

CREATE VIEW  v1 AS SELECT * FROM t1 WHERE f2 BETWEEN 1 AND 2
WITH CASCADED CHECK OPTION ;
SELECT * FROM v1;
--enable_info
# positive cases
UPDATE v1 SET f2 = 2 WHERE f2 = 1;
INSERT INTO v1 VALUES('B',2);
--disable_info
# Bug#11771: View over InnoDB table, wrong result SELECT on VIEW,
#            field->query_id wrong
SELECT * FROM v1;
# negative cases
--enable_info
--error 1369
UPDATE v1 SET f2 = 4;
--error 1369
INSERT INTO v1 VALUES('B',3);