views_master.inc

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

# VIEW <viewname> after AS SELECT
--error 1064
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table VIEW v1 WITH CASCADED CHECK OPTION;
# VIEW <viewname> after CHECK OPTION
--error 1064
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table WITH CASCADED CHECK OPTION VIEW v1;

# Variants with LOCAL CHECK OPTION
--error 1064
REPLACE OR CREATE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table WITH LOCAL CHECK OPTION;
--error 1064
CREATE OR REPLACE VIEW v1 SELECT AS F59, F60
FROM test.tb2 my_table WITH LOCAL CHECK OPTION;
--error 1064
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table LOCAL WITH CHECK OPTION;
--error 1064
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table WITH LOCAL OPTION CHECK;
--error 1064
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table CHECK OPTION WITH LOCAL;
--error 1064
CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION
AS SELECT F59, F60 FROM test.tb2 my_table;
--error 1064
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table VIEW v1 WITH LOCAL CHECK OPTION;
--error 1064
CREATE OR REPLACE AS SELECT F59, F60
FROM test.tb2 my_table WITH LOCAL CHECK OPTION VIEW v1;

--disable_warnings
Drop table if exists t1 ;
--enable_warnings
CREATE table t1 (f1 int ,f2 int)  ;
INSERT INTO t1 values (235, 22);
INSERT INTO t1 values (554, 11);
# SELECTs of UNION in braces
--error 1064
CREATE or REPLACE view v1 as (Select from f59 tb2)
Union ALL (Select from f1 t1);
# by before order
--error 1064
CREATE or REPLACE view v1 as Select f59, f60
from tb2 by order f59 limit 100 ;
# by before group
--error 1064
CREATE or REPLACE view v1 as Select f59, f60
from tb2 by group f59  ;


let $message= Testcase 3.3.1.5 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.5:  Ensure that all clauses that are defined to be mandatory
#                    are indeed required to be mandatory by the MySQL server
#                    and tools.
###############################################################################
--disable_warnings
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
--error 1064
CREATE VIEW v1 SELECT * FROM tb2 limit 100 ;
--error 1064
CREATE v1 AS SELECT * FROM tb2 limit 100 ;
--error 1064
VIEW v1 AS SELECT * FROM tb2 limit 100 ;
# positive case
CREATE VIEW v1 AS SELECT 1;
DROP VIEW v1;
--error 1064
       VIEW v1 AS SELECT 1;
--error 1064
CREATE      v1 AS SELECT 1;
--error 1064
CREATE VIEW    AS SELECT 1;
--error 1064
CREATE VIEW v1    SELECT 1;
--error 1064
CREATE VIEW v1 AS         ;


let $message= Testcase 3.3.1.6 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.6: Ensure that any clauses that are defined to be optional
#                   are indeed treated as optional by the MySQL server
#                   and tools.
###############################################################################
# Note: The positive test in 3.3.1.5 shows, that ALGORITHM ..., CHECK OPTION
#       and any column_list after the VIEW name are optional.
#       Therefore check here:
#       - ALGORITHM = <all possible algorithms>
#       - all possible CHECK OPTIONs
#       - some incomplete or wrong stuff
--disable_warnings
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
CREATE or REPLACE                       VIEW v1
as SELECT * from tb2 limit 100 ;
CREATE or REPLACE ALGORITHM = UNDEFINED VIEW v1
as SELECT * from tb2 limit 100 ;
CREATE or REPLACE ALGORITHM = MERGE     VIEW v1
as SELECT * from tb2 limit 100 ;
CREATE or REPLACE ALGORITHM = TEMPTABLE VIEW v1
as SELECT * from tb2 limit 100 ;
CREATE or REPLACE ALGORITHM = TEMPTABLE VIEW v1
as SELECT * from tb2 limit 100 ;
# negative test cases
--error 1064
CREATE or REPLACE           = TEMPTABLE VIEW v1
as SELECT * from tb2 limit 100 ;
--error 1064
CREATE or REPLACE ALGORITHM   TEMPTABLE VIEW v1
as SELECT * from tb2 limit 100 ;
--error 1064
CREATE or REPLACE ALGORITHM =           VIEW v1
as SELECT * from tb2 limit 100 ;
--error 1064
CREATE or REPLACE TEMPTABLE = ALGORITHM VIEW v1
as SELECT * from tb2 limit 100 ;
--error 1064
CREATE or REPLACE TEMPTABLE - ALGORITHM VIEW v1
as SELECT * from tb2 limit 100 ;
--error 1064
CREATE or REPLACE GARBAGE   = TEMPTABLE VIEW v1
as SELECT * from tb2 limit 100 ;
--error 1064
CREATE or REPLACE ALGORITHM = GARBAGE VIEW v1
as SELECT * from tb2 limit 100 ;
Drop view if exists v1 ;

CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1;
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH          CHECK OPTION;
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH CASCADED CHECK OPTION;
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH LOCAL    CHECK OPTION;
# negative test cases
--error 1064
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH NO       CHECK OPTION;
--error 1064
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1      CASCADED CHECK OPTION;
--error 1064
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH CASCADED       OPTION;
--error 1064
CREATE or REPLACE VIEW v1
AS SELECT * from tb2 where f59 < 1 WITH CASCADED CHECK       ;


let $message= Testcase 3.3.1.7 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.7: Ensure that all valid, fully-qualified, and non-qualified,
#                   view names are accepted, at creation time, alteration time,
#                   and drop time.
###############################################################################
# Note(ML): non-qualified view name means a view name without preceeding
#           database name
--disable_warnings
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
Create view test.v1 AS Select * from test.tb2 limit 100 ;
Alter view test.v1 AS Select F59 from test. tb2 limit 100 ;
Drop view test.v1 ;
Create view v1 AS Select * from test.tb2 limit 100 ;
Alter view v1 AS Select F59 from test.tb2 limit 100 ;
Drop view v1 ;


let $message= Testcase 3.3.1.A0 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.A0: Ensure that view names are treated case sensitive.
###############################################################################
# Note(ML): Maybe this test produces portability problems on Windows.
#           FIXME There should be a test outside this one checking the
#                 creation of objects with cases sensitive names.
#                 If we have this test the following sub testcase should
#                 be deleted.
--disable_warnings
DROP TABLE IF EXISTS t1 ;
DROP VIEW  IF EXISTS v1 ;
DROP VIEW  IF EXISTS V1 ;
--enable_warnings
eval CREATE TABLE t1 (f1 NUMERIC(4)) ENGINE = $engine_type;
INSERT INTO t1 VALUES(1111), (2222);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE f1 = 1111;
# We get here the sql code
# - 0    on OS with    cases sensitive view names (Example: UNIX)
# - 1050 on OS without cases sensitive view names (Example: WINDOWS)
--error 0,1050
CREATE VIEW V1 AS SELECT * FROM t1 WHERE f1 = 2222;
SELECT * FROM v1;
# SELECT * FROM V1;
--disable_warnings
DROP TABLE IF EXISTS t1 ;
DROP VIEW  IF EXISTS v1 ;
DROP VIEW  IF EXISTS V1 ;
--enable_warnings


let $message= Testcase 3.3.1.8 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.8: Ensure that any invalid view name is never accepted, and
#                   that an appropriate error message is returned when the name
#                   is rejected.
###############################################################################
# Note(ML): There could be more negative tests here, but I assume that the
#           server routines checking if a table or view name is acceptable are
#           heavily tested in tests checking the creation of tables.
--error 1064
Create view select AS Select * from test.tb2 limit 100;
--error 1064
Create view as AS Select * from test.tb2 limit 100;
--error 1064
Create view where AS Select * from test.tb2 limit 100;
--error 1064
Create view from AS Select * from test.tb2 limit 100;
--error 1064
Create view while AS Select * from test.tb2 limit 100;
--error 1064
Create view asdkj*(&*&&^ as Select * from test.tb2 limit 100 ;
--disable_warnings
Drop view if exists test.procedure ;
--enable_warnings
Create view test.procedure as Select * from test.tb2 limit 100 ;
Drop view if exists test.procedure ;


let $message= Testcase 3.3.1.9 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.9: Ensure that a reference to a non-existent view is rejected
#                   with an appropriate error message
###############################################################################
# Note(ML): The SELECT statement syntax does not contain any functionality to
#           claim, that the object after FROM must be a VIEW. SHOW's will be
#           checked in 3.3.11 Checks on SHOW, EXPLAIN, and DESCRIBE statements.
#           Let's check here a view based on a dropped view or table.
--disable_warnings
Drop TABLE IF EXISTS t1 ;
Drop VIEW  IF EXISTS v1;
Drop VIEW  IF EXISTS v2;
Drop VIEW  IF EXISTS v3;
--enable_warnings
CREATE TABLE t1 ( f1 char(5));
INSERT INTO t1 SET f1 = 'abcde';
CREATE VIEW v1 AS SELECT f1 FROM t1;
CREATE VIEW v2 AS SELECT *  FROM v1;

# Only negative cases, positive cases will be checked later:
DROP TABLE t1;
--error 1356
SELECT * FROM v1;
--error 1356
DELETE FROM v1;
--error 1356
UPDATE v1 SET f1 = 'aaaaa';
--error 1356
INSERT INTO v1 SET f1 = "fffff";
# v2 is based on v1, which is now invalid
--error 1356
SELECT * FROM v2;
--error 1356
DELETE FROM v2;
--error 1356
UPDATE v2 SET f1 = 'aaaaa';
--error 1356
INSERT INTO v2 SET f1 = "fffff";
DROP VIEW v1;
# v2 is based on v1, which is now dropped
--error 1356
SELECT * FROM v2;
--error 1356
DELETE FROM v2;
--error 1356
UPDATE v2 SET f1 = 'aaaaa';
--error 1356
INSERT INTO v2 SET f1 = "fffff";

DROP VIEW v2;

# A VIEW based on itself is non sense.
--disable_warnings
DROP TABLE IF EXISTS t1 ;
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
CREATE TABLE t1 (f1 FLOAT);
# Create a new VIEW based on itself
--error 1146
CREATE VIEW v1 AS SELECT * FROM v1;
# Replace a valid VIEW with one new based on itself
CREATE VIEW  v1 AS SELECT * FROM t1;
--error 1146
CREATE or REPLACE VIEW  v1 AS SELECT * FROM v1;

DROP VIEW  v1;
DROP TABLE t1;

let $message= Testcase 3.3.1.10 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.10: Ensure that it is not possible to create two views with
#                    the same name in the same database.
###############################################################################
--disable_warnings
Drop view if exists test.v1 ;
--enable_warnings
Create view test.v1 AS Select * from test.tb2 ;
--error 1050
Create view test.v1 AS Select F59 from test.tb2 ;
--error 1050
Create view      v1 AS Select F59 from test.tb2 ;


let $message= Testcase 3.3.1.11 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.11: Ensure that it is not possible to create a view and a base
#                    table with the same name in the same database.
###############################################################################
# The VIEW should get the same name like an already existing TABLE.
--error 1050
Create view test.tb2 AS Select f59,f60 from test.tb2 limit 100 ;
--error 1050
Create view      tb2 AS Select f59,f60 from test.tb2 limit 100 ;
# The TABLE should get the same name like an already existing VIEW.
--disable_warnings
Drop view if exists test.v111 ;
--enable_warnings
Create view test.v111 as select * from tb2 limit 50;
--error 1050
Create table test.v111(f1 int );
--error 1050
Create table      v111(f1 int );
DROP VIEW test.v111;


let $message= Testcase 3.3.1.12 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.12: Ensure that it is possible to create two or more views and
#                    base tables with the same name, providing each resides in
#                    a different database.
###############################################################################
USE test;
--disable_warnings
Drop database if exists test2 ;
--enable_warnings
Create database test2 ;
# Plan of sub tests
# Object name  object type in  object type in
#              database test   database test2
# t1           TABLE           TABLE
# t2           TABLE           VIEW
# v1           VIEW            TABLE
# v2           VIEW            VIEW
--disable_warnings
DROP TABLE IF EXISTS test.t0, test.t1, test.t2;
DROP VIEW  IF EXISTS test.v1;
DROP VIEW  IF EXISTS test.v2;
--enable_warnings
CREATE TABLE  test.t1 ( f1 VARCHAR(20));
CREATE TABLE test2.t1 ( f1 VARCHAR(20));
CREATE TABLE  test.t2 ( f1 VARCHAR(20));
CREATE TABLE test2.v1 ( f1 VARCHAR(20));
# t0 is an auxiliary table needed for the VIEWs
CREATE TABLE  test.t0 ( f1 VARCHAR(20));