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