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