# # Testing errors # CREATE TABLE t1 ( ID INT ) Engine=CONNECT TABLE_TYPE=INI FILE_NAME='nonexistent.txt'; SELECT * FROM t1; ID DROP TABLE t1; # # Testing examples from the manual # CREATE TABLE t1 ( contact CHAR(16) flag=1, name CHAR(20), forename CHAR(32), hired date date_format='DD/MM/YYYY', address CHAR(64), city CHAR(20), zipcode CHAR(8), tel CHAR(16) ) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='contact.ini'; SELECT contact, name, hired, city, tel FROM t1; contact name hired city tel BER Bertrand NULL Issy-les-Mlx 09.54.36.29.60 WEL Schmitt 1985-02-19 Berlin 03.43.377.360 UK1 Smith 2003-11-08 London NULL UPDATE t1 SET forename= 'Harry' where contact='UK1'; SELECT * FROM t1 WHERE contact='UK1'; contact name forename hired address city zipcode tel UK1 Smith Harry 2003-11-08 143 Blum Rd. London NW1 2BP NULL INSERT INTO t1 (contact,forename) VALUES ('UK1','Harrison'); SELECT * FROM t1 WHERE contact='UK1'; contact name forename hired address city zipcode tel UK1 Smith Harrison 2003-11-08 143 Blum Rd. London NW1 2BP NULL INSERT INTO t1 (contact,forename) VALUES ('UK2','John'); SELECT * FROM t1 WHERE contact='UK2'; contact name forename hired address city zipcode tel UK2 NULL John NULL NULL NULL NULL NULL DROP TABLE t1; SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n');; REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n') [BER] name=Bertrand forename=Olivier address=21 rue Ferdinand Buisson city=Issy-les-Mlx zipcode=92130 tel=09.54.36.29.60 cell=06.70.06.04.16 [WEL] name=Schmitt forename=Bernard hired=19/02/1985 address=64 tiergarten strasse city=Berlin zipcode=95013 tel=03.43.377.360 [UK1] name=Smith forename=Harrison hired=08/11/2003 address=143 Blum Rd. city=London zipcode=NW1 2BP [UK2] forename=John CREATE TABLE t1 ( section CHAR(16) flag=1, keyname CHAR(16) flag=2, value CHAR(32) ) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='contact.ini' OPTION_LIST='Layout=Row'; UPDATE t1 SET value='Paul' WHERE section='UK2' AND keyname='forename'; SELECT * FROM t1; section keyname value BER name Bertrand BER forename Olivier BER address 21 rue Ferdinand Buisson BER city Issy-les-Mlx BER zipcode 92130 BER tel 09.54.36.29.60 BER cell 06.70.06.04.16 WEL name Schmitt WEL forename Bernard WEL hired 19/02/1985 WEL address 64 tiergarten strasse WEL city Berlin WEL zipcode 95013 WEL tel 03.43.377.360 UK1 name Smith UK1 forename Harrison UK1 hired 08/11/2003 UK1 address 143 Blum Rd. UK1 city London UK1 zipcode NW1 2BP UK2 forename Paul DROP TABLE t1; SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n');; REPLACE(REPLACE(LOAD_FILE('DATADIR/test/contact.ini'),'\r\n','\n'),'\n\n','\n') [BER] name=Bertrand forename=Olivier address=21 rue Ferdinand Buisson city=Issy-les-Mlx zipcode=92130 tel=09.54.36.29.60 cell=06.70.06.04.16 [WEL] name=Schmitt forename=Bernard hired=19/02/1985 address=64 tiergarten strasse city=Berlin zipcode=95013 tel=03.43.377.360 [UK1] name=Smith forename=Harrison hired=08/11/2003 address=143 Blum Rd. city=London zipcode=NW1 2BP [UK2] forename=Paul # # Testing that the underlying file is created # CREATE TABLE t1 ( contact CHAR(12) NOT NULL flag=1, c2 CHAR(12) NOT NULL ) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='tmp.ini'; INSERT INTO t1 VALUES (10,10),(20,20),(300,300),(4000,4000), ('a b','c d'); SELECT * FROM t1; contact c2 10 10 20 20 300 300 4000 4000 a b c d DROP TABLE t1; SELECT REPLACE(REPLACE(LOAD_FILE('DATADIR/test/tmp.ini'),'\r\n','\n'),'\n\n','\n');; REPLACE(REPLACE(LOAD_FILE('DATADIR/test/tmp.ini'),'\r\n','\n'),'\n\n','\n') [10] c2=10 [20] c2=20 [300] c2=300 [4000] c2=4000 [a b] c2=c d # # Testing bad table # CREATE TABLE t1 ( id INT ) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.ini'; INSERT INTO t1 VALUES (10); ERROR HY000: Got error 122 'Section name must come first on Insert' from CONNECT SELECT * FROM t1; id DROP TABLE t1; # # Testing READONLY tables # CREATE TABLE t1 ( contact CHAR(10) flag=1, c2 CHAR(60) ) ENGINE=CONNECT TABLE_TYPE=INI FILE_NAME='t1.ini'; INSERT INTO t1 VALUES ('UK',10),('FR',20),('RU',30); SELECT * FROM t1; contact c2 UK 10 FR 20 RU 30 ALTER TABLE t1 READONLY=1; Warnings: Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use on your own risk SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `contact` char(10) DEFAULT NULL `flag`=1, `c2` char(60) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=INI `FILE_NAME`='t1.ini' `READONLY`=1 INSERT INTO t1 VALUES ('US',40); ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT UPDATE t1 SET c2=20 WHERE c2=10; ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT DELETE FROM t1 WHERE c2=10; ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT TRUNCATE TABLE t1; ERROR HY000: Got error 174 'Cannot modify this read/only protected table' from CONNECT ALTER TABLE t1 READONLY=0; Warnings: Warning 1105 The current version of CONNECT did not check what you changed in ALTER. Use on your own risk SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `contact` char(10) DEFAULT NULL `flag`=1, `c2` char(60) DEFAULT NULL ) ENGINE=CONNECT DEFAULT CHARSET=latin1 `TABLE_TYPE`=INI `FILE_NAME`='t1.ini' `READONLY`=0 INSERT INTO t1 VALUES ('US',40); SELECT * FROM t1; contact c2 UK 10 FR 20 RU 30 US 40 DROP TABLE t1;