1let $MYSQLD_DATADIR= `select @@datadir`; 2 3--copy_file $MTR_SUITE_DIR/std_data/people.csv $MYSQLD_DATADIR/test/people.csv 4 5SET NAMES utf8; 6 7--echo # 8--echo # Testing errors 9--echo # 10CREATE TABLE t1 11( 12 ID INT NOT NULL 13) Engine=CONNECT TABLE_TYPE=CSV FILE_NAME='nonexistent.txt'; 14--replace_regex /on .*test.nonexistent.txt/on DATADIR\/test\/nonexistent.txt/ 15# TODO: check why this is needed for Windows 16--replace_result Open(rt) Open(rb) 17SELECT * FROM t1; 18DROP TABLE t1; 19 20--echo # 21--echo # Testing examples from the manual 22--echo # 23CREATE TABLE t1 24( 25 name CHAR(12) NOT NULL, 26 birth DATE NOT NULL DATE_FORMAT='DD/MM/YY', 27 children SMALLINT(2) NOT NULL 28) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='people.csv' 29 HEADER=1 SEP_CHAR=';' QUOTED=1; 30SELECT * FROM t1; 31INSERT INTO t1 VALUES ('RONALD','1980-02-26',4); 32SELECT * FROM t1; 33DROP TABLE t1; 34--chmod 0777 $MYSQLD_DATADIR/test/people.csv 35--replace_result $MYSQLD_DATADIR DATADIR 36--eval SELECT REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/people.csv'),'\r\n','\n'); 37 38--echo # 39--echo # Testing READONLY tables 40--echo # 41CREATE TABLE t1 42( 43 name CHAR(12) NOT NULL, 44 birth DATE NOT NULL DATE_FORMAT='DD/MM/YY', 45 children SMALLINT(2) NOT NULL 46) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='people.csv' 47 HEADER=1 SEP_CHAR=';' QUOTED=1 READONLY=yes; 48--error ER_OPEN_AS_READONLY 49INSERT INTO t1 VALUES ('BILL','1973-06-30',5); 50--error ER_GET_ERRMSG 51UPDATE t1 SET children=6 WHERE name='BILL'; 52--error ER_GET_ERRMSG 53DELETE FROM t1 WHERE name='BILL'; 54--error ER_OPEN_AS_READONLY 55TRUNCATE TABLE t1; 56SELECT * FROM t1; 57ALTER TABLE t1 READONLY=no; 58SHOW CREATE TABLE t1; 59INSERT INTO t1 VALUES ('BILL','1973-06-30',5); 60SELECT * FROM t1; 61ALTER TABLE t1 READONLY=1; 62SHOW CREATE TABLE t1; 63--error ER_OPEN_AS_READONLY 64INSERT INTO t1 VALUES ('BILL','1973-06-30',5); 65SELECT * FROM t1; 66DROP TABLE t1; 67 68 69--echo # 70--echo # Testing that the underlying file is created 71--echo # 72CREATE TABLE t1 73( 74 c1 CHAR(12) NOT NULL, 75 c2 CHAR(12) NOT NULL 76) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='tmp.csv' 77 HEADER=1 SEP_CHAR=',' QUOTED=1; 78INSERT INTO t1 VALUES (10,10),(20,20),(300,300),(4000,4000), ('a b','c d'); 79SELECT * FROM t1; 80DROP TABLE t1; 81--chmod 0777 $MYSQLD_DATADIR/test/tmp.csv 82--replace_result $MYSQLD_DATADIR DATADIR 83--eval SELECT REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/tmp.csv'),'\r\n','\n'); 84 85--echo # 86--echo # Creating a CSV table from a MyISAM table 87--echo # 88CREATE TABLE t1 (a VARCHAR(10) NOT NULL, b INT NOT NULL) ENGINE=MyISAM; 89INSERT INTO t1 VALUES ('test1',1), ('test2',2); 90CREATE TABLE t2 ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t2.csv' 91 AS SELECT * FROM t1; 92SELECT * FROM t2; 93DROP TABLE t2; 94DROP TABLE t1; 95--chmod 0777 $MYSQLD_DATADIR/test/t2.csv 96--replace_result $MYSQLD_DATADIR DATADIR 97--eval SELECT REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/t2.csv'),'\r\n','\n'); 98--remove_file $MYSQLD_DATADIR/test/t2.csv 99 100--echo # 101--echo # Testing international data 102--echo # 103CREATE TABLE t1 104( 105 c1 CHAR(12) NOT NULL 106) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv' 107 CHARSET=utf8; 108INSERT INTO t1 VALUES ('á'); 109SELECT * FROM t1; 110DROP TABLE t1; 111--chmod 0777 $MYSQLD_DATADIR/test/t1.csv 112--replace_result $MYSQLD_DATADIR DATADIR 113--eval SELECT HEX(REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/t1.csv'),'\r\n','\n')); 114--remove_file $MYSQLD_DATADIR/test/t1.csv 115 116CREATE TABLE t1 117( 118 c1 CHAR(12) NOT NULL 119) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv' 120 CHARSET=utf8 DATA_CHARSET=latin1; 121INSERT INTO t1 VALUES ('á'); 122SELECT * FROM t1; 123DROP TABLE t1; 124--chmod 0777 $MYSQLD_DATADIR/test/t1.csv 125--replace_result $MYSQLD_DATADIR DATADIR 126--eval SELECT HEX(REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/t1.csv'),'\r\n','\n')); 127--remove_file $MYSQLD_DATADIR/test/t1.csv 128 129CREATE TABLE t1 130( 131 c1 CHAR(12) NOT NULL 132) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv'; 133INSERT INTO t1 VALUES ('á'); 134SELECT * FROM t1; 135DROP TABLE t1; 136--chmod 0777 $MYSQLD_DATADIR/test/t1.csv 137--replace_result $MYSQLD_DATADIR DATADIR 138--eval SELECT HEX(REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/t1.csv'),'\r\n','\n')); 139--remove_file $MYSQLD_DATADIR/test/t1.csv 140 141CREATE TABLE t1 142( 143 c1 CHAR(12) NOT NULL 144) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv' 145 CHARSET=latin1; 146INSERT INTO t1 VALUES ('á'); 147SELECT * FROM t1; 148DROP TABLE t1; 149--chmod 0777 $MYSQLD_DATADIR/test/t1.csv 150--replace_result $MYSQLD_DATADIR DATADIR 151--eval SELECT HEX(REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/t1.csv'),'\r\n','\n')); 152--remove_file $MYSQLD_DATADIR/test/t1.csv 153 154CREATE TABLE t1 155( 156 c1 CHAR(12) NOT NULL 157) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv' 158 CHARSET=latin1 DATA_CHARSET=utf8; 159INSERT INTO t1 VALUES ('á'); 160SELECT * FROM t1; 161DROP TABLE t1; 162--chmod 0777 $MYSQLD_DATADIR/test/t1.csv 163--replace_result $MYSQLD_DATADIR DATADIR 164--eval SELECT HEX(REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/t1.csv'),'\r\n','\n')); 165--remove_file $MYSQLD_DATADIR/test/t1.csv 166 167CREATE TABLE t1 168( 169 c1 CHAR(12) CHARACTER SET latin1 NOT NULL, 170 c2 CHAR(12) CHARACTER SET utf8 NOT NULL 171) ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='t1.csv'; 172INSERT INTO t1 VALUES ('á','á'); 173SELECT * FROM t1; 174DROP TABLE t1; 175--chmod 0777 $MYSQLD_DATADIR/test/t1.csv 176--replace_result $MYSQLD_DATADIR DATADIR 177--eval SELECT HEX(REPLACE(LOAD_FILE('$MYSQLD_DATADIR/test/t1.csv'),'\r\n','\n')); 178--remove_file $MYSQLD_DATADIR/test/t1.csv 179 180 181# 182# Clean up 183# 184--remove_file $MYSQLD_DATADIR/test/people.csv 185--remove_file $MYSQLD_DATADIR/test/tmp.csv 186