1#!/usr/bin/env perl 2 3# Copyright (C) 2000, 2001 MySQL AB 4# Use is subject to license terms 5# 6# This program is free software; you can redistribute it and/or modify 7# it under the terms of the GNU General Public License as published by 8# the Free Software Foundation; version 2 of the License. 9# 10# This program is distributed in the hope that it will be useful, 11# but WITHOUT ANY WARRANTY; without even the implied warranty of 12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13# GNU General Public License for more details. 14# 15# You should have received a copy of the GNU General Public License 16# along with this program; if not, write to the Free Software 17# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA 18 19# This is a test with uses two processes to a database. 20# The other inserts records in two tables, the other does a lot of joins 21# on these. 22# 23# Warning, the output from this test will differ in 'found' from time to time, 24# but there should never be any errors 25# 26 27$host= shift || ""; 28$test_db="test"; 29 30use Mysql; 31$|= 1; # Autoflush 32 33$org_file="/tmp/export-org.$$"; 34$tmp_file="/tmp/export-old.$$"; 35$tmp_file2="/tmp/export-new.$$"; 36 37print "Connection to database $test_db\n"; 38 39$dbh = Mysql->Connect($host) || die "Can't connect: $Mysql::db_errstr\n"; 40$dbh->SelectDB($test_db) || die "Can't use database $test_db: $Mysql::db_errstr\n"; 41 42$dbh->Query("drop table if exists export"); # Ignore this error 43 44print "Creating table\n"; 45 46($dbh->Query("\ 47CREATE TABLE export ( 48 auto int(5) unsigned NOT NULL DEFAULT '0' auto_increment, 49 string char(11) NOT NULL, 50 tiny tinyint(4) NOT NULL DEFAULT '0', 51 short smallint(6) NOT NULL DEFAULT '0', 52 medium mediumint(8) NOT NULL DEFAULT '0', 53 longint int(11) NOT NULL DEFAULT '0', 54 longlong bigint(20) NOT NULL DEFAULT '0', 55 real_float float(13,1) NOT NULL DEFAULT '0.0', 56 real_double double(13,1) NOT NULL, 57 utiny tinyint(3) unsigned NOT NULL DEFAULT '0', 58 ushort smallint(5) unsigned zerofill NOT NULL DEFAULT '00000', 59 umedium mediumint(8) unsigned NOT NULL DEFAULT '0', 60 ulong int(11) unsigned NOT NULL DEFAULT '0', 61 ulonglong bigint(20) unsigned NOT NULL DEFAULT '0', 62 time_stamp timestamp, 63 blob_col blob, 64 tinyblob_col tinyblob, 65 mediumblob_col tinyblob not null, 66 longblob_col longblob not null, 67 PRIMARY KEY (auto), 68 KEY (string(5)), 69 KEY unsigned_tinykey (utiny), 70 KEY (tiny), 71 KEY (short), 72 FOREIGN KEY (medium) references export, 73 KEY (longlong), 74 KEY (real_float), 75 KEY (real_double), 76 KEY (ushort), 77 KEY (umedium), 78 KEY (ulong), 79 KEY (ulonglong), 80 KEY (ulonglong,ulong))")) or die $Mysql::db_errstr; 81 82print "Inserting data\n"; 83 84@A=("insert into export values (10, 1,1,1,1,1,1,1,1,1,1,1,1,1,0,1,1,1,1)", 85 "insert into export values (NULL,2,2,2,2,2,2,2,2,2,2,2,2,2,NULL,NULL,NULL,2,2)", 86 "insert into export values (0,1/3,3,3,3,3,3,3,3,3,3,3,3,3,3,'','','','3')", 87 "insert into export values (0,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,'-1')", 88 "insert into export values (0,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,'-4294967295')", 89 "insert into export values (0,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,'4294967295')", 90 "insert into export (string,tinyblob_col) values ('special','''\\0\\t\t\n''')", 91 "insert into export (string) values (',,!!\\\\##')", 92 "insert into export (tinyblob_col) values (',,!!!\\\\\\##')" 93 ); 94 95foreach $A (@A) 96{ 97 $dbh->Query($A) or die "query: $A returned: " . $Mysql::db_errstr; 98} 99 100 101print "Doing dump, load, check on different formats\n"; 102 103@A=(# Ordinary format 104 "", 105 # Field terminated by something 106 "fields optionally enclosed by '+' escaped by '' terminated by ',,,' lines terminated by ',,,,'", 107 "fields enclosed by '' terminated by ',' lines terminated by ''", 108 "fields enclosed by '' terminated by ',' lines terminated by '!!'", 109 #Fields enclosed by 110 #"fields enclosed by '+' terminated by ''", 111 #"fields enclosed by '+' terminated by '' lines terminated by ''", 112 "fields enclosed by '+' terminated by ',,' lines terminated by '!!!'", 113 "fields enclosed by '+' terminated by ',,' lines terminated by '##'", 114 "fields enclosed by '+' escaped by '' terminated by ',,' lines terminated by '###'", 115 "fields enclosed by '+' escaped by '' terminated by '!' lines terminated by ''", 116 "fields enclosed by '+' terminated by ',' lines terminated by ''", 117 #Fields optionally enclosed by 118 "fields optionally enclosed by '+' terminated by ','", 119 "fields optionally enclosed by '+' terminated by ',' lines terminated by ''", 120 "fields optionally enclosed by '''' terminated by ',' lines starting by 'INSERT INTO a VALUES(' terminated by ');\n'", 121 ); 122 123$dbh->Query("select * into outfile '$org_file' from export") or die $Mysql::db_errstr; 124 125 126foreach $A (@A) 127{ 128 unlink($tmp_file); 129 unlink($tmp_file2); 130 $dbh->Query("select * into outfile '$tmp_file' $A from export") or die $Mysql::db_errstr; 131 $dbh->Query("delete from export") or die $Mysql::db_errstr; 132 $dbh->Query("load data infile '$tmp_file' into table export $A") or die $Mysql::db_errstr . " with format: $A\n"; 133 $dbh->Query("select * into outfile '$tmp_file2' from export") or die $Mysql::db_errstr; 134 if (`cmp $tmp_file2 $org_file`) 135 { 136 print "Using format $A\n"; 137 print "$tmp_file2 and $org_file differ. Plese check files\n"; 138 exit 1; 139 } 140} 141 142 143@A=(#Fixed size fields 144 "fields enclosed by '' escaped by '' terminated by ''", 145 "fields enclosed by '' escaped by '' terminated by '' lines terminated by '\\r\\n'", 146 "fields enclosed by '' terminated by '' lines terminated by ''" 147 ); 148 149unlink($org_file); 150 151$field_list="auto,ifnull(string,''),tiny,short,medium,longint,longlong,real_float,ifnull(real_double,''),utiny,ushort,umedium,ulong,ulonglong,time_stamp"; 152 153$dbh->Query("select $field_list into outfile '$org_file' from export") or die $Mysql::db_errstr; 154 155$field_list="auto,string,tiny,short,medium,longint,longlong,real_float,real_double,utiny,ushort,umedium,ulong,ulonglong,time_stamp"; 156 157foreach $A (@A) 158{ 159 unlink($tmp_file); 160 unlink($tmp_file2); 161 $dbh->Query("select $field_list into outfile '$tmp_file' $A from export") or die $Mysql::db_errstr; 162 $dbh->Query("delete from export") or die $Mysql::db_errstr; 163 $dbh->Query("load data infile '$tmp_file' into table export $A ($field_list)") or die $Mysql::db_errstr; 164 $dbh->Query("select $field_list into outfile '$tmp_file2' from export") or die $Mysql::db_errstr; 165 if (`cmp $tmp_file2 $org_file`) 166 { 167 print "Using format $A\n"; 168 print "$tmp_file2 and $org_file differ. Plese check files\n"; 169 exit 1; 170 } 171} 172 173unlink($tmp_file); 174unlink($tmp_file2); 175unlink($org_file); 176 177$dbh->Query("drop table export") or die $Mysql::db_errstr; 178 179print "Test ok\n"; 180exit 0; 181