views_master.inc

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

#### suite/funcs_1/views/views_master.test

let $message= ! Attention: The file with the expected results is not
              |            thoroughly checked.
              !            The server return codes are correct, but
              |            most result sets where the table tb2 is
              !            involved are not checked.;
--source include/show_msg80.inc

# As long as
# Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill
# is not fixed, we must switch the ps-protocol for some statements off.
# If this bug is fixed, please
# 1. set the following variable to 0
# 2. check, if the test passes
# 3. remove the workarounds 
let $have_bug_11589= 1;
if ($have_bug_11589)
{
   let $message= There are some statements where the ps-protocol is switched off.
                 Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill;
   --source include/show_msg80.inc
}

let $message= ! Attention: The file with the expected results suffers from
Bug#10713: mysqldump includes database in create view and referenced tables;
--source include/show_msg80.inc


# The sub testcases are nearly independend. That is the reason why
# we do not want to abort after the first error.
--disable_abort_on_error


# 3.3 Views
#     MySQL views are based on a subset of the view requirements described in
#     the following standard SQL document:
#
#     * ISO/IEC 9075-2:2003 Information technology -- Database languages --
#       SQL -- Part 2: Foundation (SQL/Foundation)
#
#     MySQL has also added some vendor-specific enhancements to the standard
#     SQL requirements.

# FIXME (ML)
#   - Alter all object names so that they follow the v/t/..<number> scheme or
#     apply another method which prevents that customer data might be
#     accidently modified
#   - Remove any reference to the preloaded tables tb1 - tb4, if they could
#     be replaced without loss of value.
#     Example: failing CREATE VIEW statements
#     The goal is to split this script into two, where the first one does
#     not need the possibly huge tables.

# Load records needed within the testcases.
#     We load them here and not within the testcases itself, because the
#     removal of any unneeded testcase during bug analysis should not alter
#     result sets.
#     Testcase 3.3.1.1
insert into test.tb2 (f59,f60) values (76710,226546);
insert into test.tb2 (f59,f60) values(2760,985654);
insert into test.tb2 (f59,f60) values(569300,9114376);
insert into test.tb2 (f59,f60) values(660,876546);
insert into test.tb2 (f59,f60) values(250,87895654);
insert into test.tb2 (f59,f60) values(340,9984376);
insert into test.tb2 (f59,f60) values(3410,996546);
insert into test.tb2 (f59,f60) values(2550,775654);
insert into test.tb2 (f59,f60) values(3330,764376);
insert into test.tb2 (f59,f60) values(441,16546);
insert into test.tb2 (f59,f60) values(24,51654);
insert into test.tb2 (f59,f60) values(323,14376);
#     Testcase 3.3.1.45
insert into test.tb2 (f59,f60) values(34,41);
insert into test.tb2 (f59,f60) values(04,74);
insert into test.tb2 (f59,f60) values(15,87);
insert into test.tb2 (f59,f60) values(22,93);
#     Testcase 3.3.1.46
insert into test.tb2 (f59,f60) values(394,41);
insert into test.tb2 (f59,f60) values(094,74);
insert into test.tb2 (f59,f60) values(195,87);
insert into test.tb2 (f59,f60) values(292,93);
#     Testcase 3.3.1.47
insert into test.tb2 (f59,f60) values(0987,41) ;
insert into test.tb2 (f59,f60) values(7876,74) ;
#     Testcase 3.3.1.52
INSERT INTO tb2 (f59,f61) VALUES(321,765 );
INSERT INTO tb2 (f59,f61) VALUES(9112,8771);
#     Testcase 3.3.1.53
INSERT INTO tb2 (f59,f61) VALUES (500,900 ) ;
INSERT INTO tb2 (f59,f61) VALUES (500,900 ) ;
INSERT INTO tb2 (f59,f61) VALUES (500,900 ) ;
#     Testcase 3.3.1.A1
Insert into tb2 (f59,f60,f61) values (107,105,106) ;
Insert into tb2 (f59,f60,f61) values (109,108,104) ;
#     Testcase 3.3.1.A2
Insert into tb2 (f59,f60,f61) values (207,205,206) ;
Insert into tb2 (f59,f60,f61) values (209,208,204) ;
#     Testcase 3.3.1.A3
Insert into tb2 (f59,f60,f61) values (27,25,26) ;
Insert into tb2 (f59,f60,f61) values (29,28,24) ;
#     Testcase 3.3.1.63
Insert into tb2 (f59,f60,f61) values (17,15,16) ;
Insert into tb2 (f59,f60,f61) values (19,18,14) ;
insert into tb2 (f59,f60,f61) values (107,105,106);
insert into tb2 (f59,f60,f61) values (109,108,104);
#     Testcase 3.3.1.64
INSERT INTO tb2 (f59,f60) VALUES( 299,899 );
INSERT INTO tb2 (f59,f60) VALUES( 242,79  );
INSERT INTO tb2 (f59,f60) VALUES( 424,89  );
#
#
Use test;
#
# End of basic preparations.
#
##############################################################################



#==============================================================================
# 3.3.1 Syntax checks for CREATE VIEW, CREATE OR REPLACE VIEW, ALTER VIEW,
#       and DROP VIEW:
#==============================================================================

let $message= Testcase 3.3.1.1 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.1:  Ensure that all clauses that should be supported
#                    are supported.
###############################################################################
--disable_warnings
Drop table if exists t1;
--enable_warnings
Create table t1 (f59 INT, f60 INT) ;
Insert into t1  values (100,4234);
Insert into t1  values (990,6624);
Insert into t1  values (710,765);
Insert into t1  values (300,433334);
Insert into t1  values (800,9788);
Insert into t1  values (500,9866);

#(01)
    --disable_warnings
    Drop view if exists v1 ;
    --enable_warnings
    CREATE VIEW v1 AS select f59,f60,f61
    FROM test.tb2 where f59=250;
    select * FROM v1 limit 0,10;

#(02)
    Drop view if exists v1 ;
    CREATE VIEW v1 AS select f59,f60,f61
    FROM test.tb2 limit 100;
    select * FROM v1 limit 0,10;

#(03)
    CREATE or REPLACE VIEW v1 AS select f59,f60,f61
    FROM test.tb2 limit 4,3;
    select * FROM v1 limit 0,10;

#(04)
    CREATE or REPLACE VIEW v1 AS select distinct f59
    FROM test.tb2 limit 4,3;
    select * FROM v1 limit 0,10;

#(05)
    ALTER VIEW v1 AS select f59
    FROM test.tb2 limit 6,2;
    select * FROM v1 limit 0,10;

#(06)
    CREATE or REPLACE VIEW v1 AS select f59
    from tb2 order by f59 limit 100;
    select * FROM v1 limit 0,10;

#(07)
    CREATE or REPLACE VIEW v1 AS select f59
    from tb2 order by f59 asc limit 100;
    select * FROM v1 limit 0,10;

#(08)
    CREATE or REPLACE VIEW v1 AS select f59
    from tb2 order by f59 desc limit 100;
    select * FROM v1 limit 0,10;

#(09)
    CREATE or REPLACE VIEW v1 AS select f59
    from tb2 group by f59 limit 100;
    select * FROM v1 limit 0,10;

#(10)
    CREATE or REPLACE VIEW v1 AS select f59
    from tb2 group by f59 asc limit 100;
    select * FROM v1 limit 0,10;

#(11)
    CREATE or REPLACE VIEW v1 AS select f59
    from tb2 group by f59 desc limit 100;
    select * FROM v1 limit 0,10;

#(12)
    CREATE or REPLACE VIEW v1 AS (select f59 from tb2)
    union (select f59 from t1) limit 100;
    select * FROM v1 limit 0,10;

#(13)
    CREATE or REPLACE VIEW v1 AS (select f59 FROM tb2)
    UNION DISTINCT(select f59 FROM t1) ;
    select * FROM v1 limit 0,10;

#(14)
    CREATE or REPLACE VIEW v1 AS (select f59 FROM tb2)
    UNION ALL(select f59 FROM t1) ;
    select * FROM v1 limit 0,10;

#(15)
if ($have_bug_11589)
{
--disable_ps_protocol
}
    CREATE or REPLACE VIEW v1 AS select *
    FROM test.tb2 WITH LOCAL CHECK OPTION ;
    select * FROM v1 limit 0,50;

    #(16)
CREATE or REPLACE VIEW v1 AS select *
    FROM test.tb2 WITH CASCADED CHECK OPTION ;
    select * FROM v1 limit 0,10;
--enable_ps_protocol

#(17)
    CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
    FROM test.tb2 WITH CASCADED CHECK OPTION;
    SELECT * FROM v1 limit 0,10;


#(18)
    CREATE or REPLACE VIEW v1 AS select f59, f60
    from test.tb2 where f59=3330 ;
    select * FROM v1 limit 0,10;

    DROP VIEW  v1 ;
    DROP TABLE t1 ;


let $message= Testcase 3.3.1.2 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.3.1.2:  Ensure that all clauses that should not be supported are
#                    disallowed with an appropriate error message.
###############################################################################
--disable_warnings
DROP TABLE IF EXISTS t1 ;
DROP VIEW  IF EXISTS v1 ;
DROP VIEW  IF EXISTS v2 ;
--enable_warnings
CREATE TABLE t1 (f1 BIGINT) ;

# User variables and parameters are not supported in VIEWs -> 3.3.1.40

# SELECT INTO is illegal
SET @x=0;
--error 1350
CREATE or REPLACE VIEW v1 AS Select 1 INTO @x;
Select @x;

# Subquery in the FROM clause is illegal
--error 1349
CREATE or REPLACE VIEW v1 AS Select 1
FROM (SELECT 1 FROM t1) my_table;

# Triggers cannot be associated with VIEWs
CREATE VIEW v1 AS SELECT f1 FROM t1;
# Show that 1. The trigger code basically works and the VIEW is updatable
#           2. The VIEW is updatable
#           3. Insert into view causes that the trigger is executed
CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1 ;
SET @a:=0 ;
SELECT @a ;
INSERT INTO v1 VALUES (1) ;
SELECT @a ;
SELECT * FROM t1;
DROP TRIGGER tr1 ;
SET @a:=0 ;
--error 1347
CREATE TRIGGER tr1 BEFORE INSERT ON v1 FOR EACH ROW SET @a:=1 ;

# RENAME VIEW is not available even when we try it via rename table.
# FIXME: Write a bug report for the ugly error message
#        1017: Can't find file: './test/v1.frm' (errno: 2),
#        because the much more beautiful
#        1347: 'test.v1' is not BASE TABLE
#        exists.
--replace_result '\\' '/'
# MLML --error 1017
RENAME TABLE v1 TO v2;
--error 1064
RENAME VIEW  v2 TO v1;
#--error 1347
ALTER TABLE v2 RENAME AS v1;
--error 1064
ALTER VIEW  v1 RENAME AS v2;

# VIEWs cannot contain a PRIMARY KEY or have an Index.
--disable_warnings
DROP TABLE IF EXISTS t1, t2 ;
DROP VIEW  IF EXISTS v1 ;
DROP VIEW  IF EXISTS v2 ;
--enable_warnings
CREATE TABLE t1 ( f1 DATE, f2 BLOB, f3 DOUBLE );
CREATE VIEW  v1 AS SELECT f1, f2, f3 FROM t1;
ALTER  TABLE t1 ADD PRIMARY KEY(f1);
--error 1347
ALTER  TABLE v1 ADD PRIMARY KEY(f1);
--error 1064
ALTER  VIEW  v1 ADD PRIMARY KEY(f1);
CREATE INDEX t1_idx ON t1(f3);
--error 1347
CREATE INDEX v1_idx ON v1(f3);
DROP TABLE t1;
DROP VIEW  v1;


let $message= Testcase 3.3.1.3 + 3.1.1.4 ;
--source include/show_msg80.inc
###############################################################################
# Testcase 3.1.1.3:  Ensure that all supported clauses are supported only in
#                    the correct order.
# Testcase 3.1.1.4:  Ensure that an appropriate error message is returned if
#                    a clause is out-of-order in an SQL statement.
###############################################################################
--disable_warnings
DROP VIEW  IF EXISTS v1 ;
--enable_warnings
# REPLACE after VIEW name
--error 1064
CREATE VIEW v1 or REPLACE AS Select * from tb2 my_table limit 50;
# CHECK OPTION before AS SELECT
--error 1064
CREATE VIEW v1 WITH CASCADED CHECK OPTION AS Select *
from tb2 my_table limit 50;
# CHECK OPTION before AS SELECT
--error 1064
CREATE VIEW v1 WITH LOCAL CHECK OPTION AS Select *
from tb2 my_table limit 50;
# CREATE after SELECT
--error 1064
SELECT * FROM tb2 my_table CREATE VIEW As v1 limit 100 ;
# AS forgotten
--error 1064
CREATE or REPLACE VIEW v1 Select f59, f60
from test.tb2 my_table where f59 = 250 ;
# positive case
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table WITH CASCADED CHECK OPTION;
DROP VIEW v1;
# REPLACE OR CREATE instead of CREATE OR REPLACE
--error 1064
REPLACE OR CREATE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table WITH CASCADED CHECK OPTION;
# AS after SELECT
--error 1064
CREATE OR REPLACE VIEW v1 SELECT AS F59, F60
FROM test.tb2 my_table WITH CASCADED CHECK OPTION;
--error 1064
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table CASCADED WITH CHECK OPTION;
# OPTION CHECK instead of CHECK OPTION
--error 1064
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table WITH CASCADED OPTION CHECK;
# CHECK OPTION before WITH
--error 1064
CREATE OR REPLACE VIEW v1 AS SELECT F59, F60
FROM test.tb2 my_table CHECK OPTION WITH CASCADED;
# CHECK OPTION before AS SELECT
--error 1064
CREATE OR REPLACE VIEW v1 WITH CASCADED CHECK OPTION
AS SELECT F59, F60 FROM test.tb2 my_table;