1# 2# Testing errors 3# 4CREATE TABLE t1 5( 6ID INT 7) Engine=CONNECT TABLE_TYPE=INI FILE_NAME='nonexistent.txt'; 8SELECT * FROM t1; 9ID 10DROP TABLE t1; 11# 12# Testing examples from the manual 13# 14CREATE TABLE t1 15( 16contact CHAR(16) flag=1, 17name CHAR(20), 18forename CHAR(32), 19hired date date_format='DD/MM/YYYY', 20address CHAR(64), 21city CHAR(20), 22zipcode CHAR(8), 23tel CHAR(16) 24) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='contact.ini'; 25SELECT contact, name, hired, city, tel FROM t1; 26contact name hired city tel 27BER Bertrand NULL Issy-les-Mlx 09.54.36.29.60 28WEL Schmitt 1985-02-19 Berlin 03.43.377.360 29UK1 Smith 2003-11-08 London NULL 30UPDATE t1 SET forename= 'Harry' where contact='UK1'; 31SELECT * FROM t1 WHERE contact='UK1'; 32contact name forename hired address city zipcode tel 33UK1 Smith Harry 2003-11-08 143 Blum Rd. London NW1 2BP NULL 34INSERT INTO t1 (contact,forename) VALUES ('UK1','Harrison'); 35SELECT * FROM t1 WHERE contact='UK1'; 36contact name forename hired address city zipcode tel 37UK1 Smith Harrison 2003-11-08 143 Blum Rd. London NW1 2BP NULL 38INSERT INTO t1 (contact,forename) VALUES ('UK2','John'); 39SELECT * FROM t1 WHERE contact='UK2'; 40contact name forename hired address city zipcode tel 41UK2 NULL John NULL NULL NULL NULL NULL 42DROP TABLE t1; 43SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n');; 44REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n') 45[BER] 46name=Bertrand 47forename=Olivier 48address=21 rue Ferdinand Buisson 49city=Issy-les-Mlx 50zipcode=92130 51tel=09.54.36.29.60 52cell=06.70.06.04.16 53[WEL] 54name=Schmitt 55forename=Bernard 56hired=19/02/1985 57address=64 tiergarten strasse 58city=Berlin 59zipcode=95013 60tel=03.43.377.360 61[UK1] 62name=Smith 63forename=Harrison 64hired=08/11/2003 65address=143 Blum Rd. 66city=London 67zipcode=NW1 2BP 68[UK2] 69forename=John 70 71CREATE TABLE t1 72( 73section CHAR(16) flag=1, 74keyname CHAR(16) flag=2, 75value CHAR(32) 76) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='contact.ini' 77 OPTION_LIST='Layout=Row'; 78UPDATE t1 SET value='Paul' WHERE section='UK2' AND keyname='forename'; 79SELECT * FROM t1; 80section keyname value 81BER name Bertrand 82BER forename Olivier 83BER address 21 rue Ferdinand Buisson 84BER city Issy-les-Mlx 85BER zipcode 92130 86BER tel 09.54.36.29.60 87BER cell 06.70.06.04.16 88WEL name Schmitt 89WEL forename Bernard 90WEL hired 19/02/1985 91WEL address 64 tiergarten strasse 92WEL city Berlin 93WEL zipcode 95013 94WEL tel 03.43.377.360 95UK1 name Smith 96UK1 forename Harrison 97UK1 hired 08/11/2003 98UK1 address 143 Blum Rd. 99UK1 city London 100UK1 zipcode NW1 2BP 101UK2 forename Paul 102DROP TABLE t1; 103SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n');; 104REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n') 105[BER] 106name=Bertrand 107forename=Olivier 108address=21 rue Ferdinand Buisson 109city=Issy-les-Mlx 110zipcode=92130 111tel=09.54.36.29.60 112cell=06.70.06.04.16 113[WEL] 114name=Schmitt 115forename=Bernard 116hired=19/02/1985 117address=64 tiergarten strasse 118city=Berlin 119zipcode=95013 120tel=03.43.377.360 121[UK1] 122name=Smith 123forename=Harrison 124hired=08/11/2003 125address=143 Blum Rd. 126city=London 127zipcode=NW1 2BP 128[UK2] 129forename=Paul 130 131# 132# Testing that the underlying file is created 133# 134CREATE TABLE t1 135( 136contact CHAR(12) NOT NULL flag=1, 137c2 CHAR(12) NOT NULL 138) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='tmp.ini'; 139INSERT INTO t1 VALUES (10,10),(20,20),(300,300),(4000,4000), ('a b','c d'); 140SELECT * FROM t1; 141contact c2 14210 10 14320 20 144300 300 1454000 4000 146a b c d 147DROP TABLE t1; 148SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/tmp.ini'),'\r\n','\n'),'\n\n','\n');; 149REPLACE(REPLACE(LOAD_FILE('DATADIR/test/tmp.ini'),'\r\n','\n'),'\n\n','\n') 150[10] 151c2=10 152[20] 153c2=20 154[300] 155c2=300 156[4000] 157c2=4000 158[a b] 159c2=c d 160 161# 162# Testing bad table 163# 164CREATE TABLE t1 165( 166id INT 167) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.ini'; 168INSERT INTO t1 VALUES (10); 169ERROR HY000: Got error 122 'Section name must come first on Insert' from CONNECT 170SELECT * FROM t1; 171id 172DROP TABLE t1; 173# 174# Testing READONLY tables 175# 176CREATE TABLE t1 177( 178contact CHAR(10) flag=1, 179c2 CHAR(60) 180) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.ini'; 181INSERT INTO t1 VALUES ('UK',10),('FR',20),('RU',30); 182SELECT * FROM t1; 183contact c2 184UK 10 185FR 20 186RU 30 187ALTER TABLE t1 READONLY=1; 188SHOW CREATE TABLE t1; 189Table Create Table 190t1 CREATE TABLE `t1` ( 191 `contact` char(10) DEFAULT NULL `flag`=1, 192 `c2` char(60) DEFAULT NULL 193) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=INI `FILE_NAME`='t1.ini' `READONLY`=1 194INSERT INTO t1 VALUES ('US',40); 195ERROR HY000: Table 't1' is read only 196UPDATE t1 SET c2=20 WHERE c2=10; 197ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT 198DELETE FROM t1 WHERE c2=10; 199ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT 200TRUNCATE TABLE t1; 201ERROR HY000: Table 't1' is read only 202ALTER TABLE t1 READONLY=0; 203SHOW CREATE TABLE t1; 204Table Create Table 205t1 CREATE TABLE `t1` ( 206 `contact` char(10) DEFAULT NULL `flag`=1, 207 `c2` char(60) DEFAULT NULL 208) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=INI `FILE_NAME`='t1.ini' `READONLY`=0 209INSERT INTO t1 VALUES ('US',40); 210SELECT * FROM t1; 211contact c2 212UK 10 213FR 20 214RU 30 215US 40 216DROP TABLE t1; 217# 218# Bug: TABLE_TYPE=ini does not clear memory between CREATE TABLEs 219# 220CREATE TABLE t1 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL) 221ENGINE=CONNECT TABLE_TYPE=INI; 222Warnings: 223Warning 1105 No file name. Table will use t1.ini 224INSERT INTO t1 VALUES ('sec1','val1'),('sec2','val2'); 225SELECT sec AS s, val AS v FROM t1; 226s v 227sec1 val1 228sec2 val2 229DROP TABLE t1; 230CREATE TABLE t1 (sec2 CHAR(10) NOT NULL FLAG=1, val2 CHAR(10) NOT NULL) 231ENGINE=CONNECT TABLE_TYPE=INI; 232Warnings: 233Warning 1105 No file name. Table will use t1.ini 234INSERT INTO t1 VALUES ('sec1','val11'),('sec2','val22'); 235SELECT sec2 AS s, val2 AS v FROM t1; 236s v 237sec1 val11 238sec2 val22 239SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n');; 240REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n') 241[sec1] 242val2=val11 243[sec2] 244val2=val22 245 246DROP TABLE t1; 247CREATE TABLE t1 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL) 248ENGINE=CONNECT TABLE_TYPE=INI; 249Warnings: 250Warning 1105 No file name. Table will use t1.ini 251CREATE TABLE t2 (sec CHAR(10) NOT NULL FLAG=1, val CHAR(10) NOT NULL) 252ENGINE=CONNECT TABLE_TYPE=INI; 253Warnings: 254Warning 1105 No file name. Table will use t2.ini 255INSERT INTO t1 VALUES('1sec1','1val1'),('1sec2','1val2'); 256INSERT INTO t2 VALUES('2sec1','2val1'),('2sec2','2val2'); 257SELECT sec AS s, val AS v FROM t1; 258s v 2591sec1 1val1 2601sec2 1val2 261SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n');; 262REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t1.ini'),'\r\n','\n'),'\n\n','\n') 263[1sec1] 264val=1val1 265[1sec2] 266val=1val2 267 268SELECT sec AS s, val AS v FROM t2; 269s v 2702sec1 2val1 2712sec2 2val2 272SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t2.ini'),'\r\n','\n'),'\n\n','\n');; 273REPLACE(REPLACE(LOAD_FILE('DATADIR/test/t2.ini'),'\r\n','\n'),'\n\n','\n') 274[2sec1] 275val=2val1 276[2sec2] 277val=2val2 278 279DROP TABLE t1, t2; 280