2324.2.1
by patrick crews
Initial work for sql-bench mode. Added sql-bench to the tree. Test script for running entire suite added |
1 |
#!/usr/bin/perl
|
2 |
# Copyright (C) 2000, 2003 MySQL AB
|
|
3 |
#
|
|
4 |
# This library is free software; you can redistribute it and/or
|
|
5 |
# modify it under the terms of the GNU Library General Public
|
|
6 |
# License as published by the Free Software Foundation; version 2
|
|
7 |
# of the License.
|
|
8 |
#
|
|
9 |
# This library is distributed in the hope that it will be useful,
|
|
10 |
# but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
11 |
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
|
|
12 |
# Library General Public License for more details.
|
|
13 |
#
|
|
14 |
# You should have received a copy of the GNU Library General Public
|
|
15 |
# License along with this library; if not, write to the Free
|
|
16 |
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
|
|
17 |
# MA 02111-1307, USA
|
|
18 |
#
|
|
19 |
# start initialition
|
|
20 |
#
|
|
21 |
||
22 |
$VER = "1.0"; |
|
23 |
||
24 |
use Getopt::Long; |
|
25 |
use Cwd; |
|
26 |
use DBI; |
|
27 |
||
28 |
$max_row_length=500000; # Don't create bigger SQL rows that this |
|
29 |
$opt_lock=1; # lock tables |
|
30 |
||
31 |
$pwd = cwd(); $pwd = "." if ($pwd eq ''); |
|
32 |
||
33 |
require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n"; |
|
34 |
||
35 |
$|=1; |
|
36 |
||
37 |
$opt_from_server= $opt_to_server= "mysql"; |
|
38 |
$opt_from_host= $opt_to_host= "localhost"; |
|
39 |
$opt_from_db= $opt_to_db= "test"; |
|
40 |
$opt_from_user=$opt_from_password=$opt_to_user=$opt_to_password=""; |
|
41 |
$opt_help=$opt_verbose=$opt_debug=0; |
|
42 |
||
43 |
||
44 |
GetOptions("from-server=s","to-server=s","from-host=s","to-host=s","from-db=s", |
|
45 |
"to-db=s", "help", "verbose","debug") || usage(); |
|
46 |
||
47 |
usage() if ($opt_help || |
|
48 |
($opt_from_server eq $opt_to_server && |
|
49 |
$opt_from_db eq $opt_to_db && |
|
50 |
$opt_from_host eq $opt_to_host)); |
|
51 |
||
52 |
####
|
|
53 |
#### Usage
|
|
54 |
####
|
|
55 |
||
56 |
||
57 |
sub usage |
|
58 |
{
|
|
59 |
print <<EOF; |
|
60 |
||
61 |
$0 version $VER by Monty
|
|
62 |
||
63 |
Copies tables between two database servers. If the destination table doesn\'t
|
|
64 |
exist it\'s autoamticly created. If the destination table exists, it
|
|
65 |
should be compatible with the source table.
|
|
66 |
||
67 |
Because DBI doesn\'t provide full information about the columns in a table,
|
|
68 |
some columns may not have optimal types in a create tables. Any created
|
|
69 |
tables will also not have any keys!
|
|
70 |
||
71 |
Usage: $0 [options] tables...
|
|
72 |
||
73 |
Options:
|
|
74 |
--help Show this help and exit
|
|
75 |
--from-server Source server (Default: $opt_from_server)
|
|
76 |
--from-host Source hostname (Default: $opt_from_host)
|
|
77 |
--from-db Source database name (Default: $opt_from_db)
|
|
78 |
--from-user Source user (Default: $opt_from_password)
|
|
79 |
--from-password Source password (Default: $opt_from_password)
|
|
80 |
--to-server Destination server (Default: $opt_to_server)
|
|
81 |
--to-host Destination hostname (Default: $opt_to_host)
|
|
82 |
--to-db Destination database name (Default: $opt_to_db)
|
|
83 |
--to-user Destination user (Default: $opt_to_user)
|
|
84 |
--to-password Destination password (Default: $opt_to_password)
|
|
85 |
--verbose Be more verbose
|
|
86 |
||
87 |
If you the server names ends with _ODBC, then this program will connect
|
|
88 |
through ODBC instead of using a native driver.
|
|
89 |
EOF
|
|
90 |
exit(0); |
|
91 |
}
|
|
92 |
||
93 |
####
|
|
94 |
#### Connect
|
|
95 |
####
|
|
96 |
||
97 |
$from_server=get_server($opt_from_server,$opt_from_host,$opt_from_db); |
|
98 |
$to_server=get_server($opt_to_server,$opt_to_host,$opt_to_db); |
|
99 |
||
100 |
$opt_user=$opt_from_user; $opt_password=$opt_from_password; |
|
101 |
print "- connecting to SQL servers\n" if ($opt_verbose); |
|
102 |
$from_dbh=$from_server->connect() || die "Can't connect to source server $opt_from_server on host $opt_from_host using db $opt_from_db"; |
|
103 |
$opt_user=$opt_to_user; $opt_password=$opt_to_password; |
|
104 |
$to_dbh=$to_server->connect() || die "Can't connect to source server $opt_to_server on host $opt_to_host using db $opt_to_db"; |
|
105 |
||
106 |
####
|
|
107 |
#### Copy data
|
|
108 |
####
|
|
109 |
||
110 |
foreach $table (@ARGV) |
|
111 |
{
|
|
112 |
||
113 |
print "- querying $table\n" if ($opt_verbose); |
|
114 |
$sth=$from_dbh->prepare("select * from $table") || die "Can't prepare query to get $table; $DBI::errstr"; |
|
115 |
$sth->execute || die "Can't execute query to get data from $table; $DBI::errstr"; |
|
116 |
||
117 |
if (!table_exists($to_server,$to_dbh,$table)) |
|
118 |
{
|
|
119 |
print "- creating $table\n" if ($opt_verbose); |
|
120 |
$table_def=get_table_definition($from_server,$from_dbh,$sth); |
|
121 |
do_many($to_dbh,$to_server->create($table,$table_def,[])); |
|
122 |
}
|
|
123 |
if ($opt_lock && $to_server->{'lock_tables'}) |
|
124 |
{
|
|
125 |
print "- locking $table\n" if ($opt_verbose); |
|
126 |
$to_dbh->do("lock tables $table WRITE"); |
|
127 |
}
|
|
128 |
||
129 |
$columns=$sth->{NUM_OF_FIELDS}; |
|
130 |
$columns_to_quote=get_columns_to_quote($sth); |
|
131 |
$insert_multi_value=$sth->{'insert_multi_value'}; |
|
132 |
$query="insert into $table values"; $result=""; |
|
133 |
||
134 |
print "- copying $table\n" if ($opt_verbose); |
|
135 |
while (($row = $sth->fetchrow_arrayref)) |
|
136 |
{
|
|
137 |
$tmp="("; |
|
138 |
for ($i=0 ; $i < $columns ; $i++) |
|
139 |
{
|
|
140 |
if ($columns_to_quote->[$i]) |
|
141 |
{
|
|
142 |
$tmp.= $to_dbh->quote($row->[$i]) . ","; |
|
143 |
}
|
|
144 |
else
|
|
145 |
{
|
|
146 |
$tmp.= $row->[$i] . ","; |
|
147 |
}
|
|
148 |
}
|
|
149 |
substr($tmp,-1)=")"; # Remove last ',' |
|
150 |
if ($insert_multi_value) |
|
151 |
{
|
|
152 |
$to_dbh->do($query . $tmp) || die "Can't insert row: $DBI::errstr"; |
|
153 |
}
|
|
154 |
elsif (length($result)+length($tmp) >= $max_row_length && $result) |
|
155 |
{
|
|
156 |
$to_dbh->do($query . $result) || die "Can't insert row: $DBI::errstr"; |
|
157 |
$result=""; |
|
158 |
}
|
|
159 |
elsif (length($result)) |
|
160 |
{
|
|
161 |
$result.= ",$tmp"; |
|
162 |
}
|
|
163 |
else
|
|
164 |
{
|
|
165 |
$result=$tmp; |
|
166 |
}
|
|
167 |
}
|
|
168 |
if (length($result)) |
|
169 |
{
|
|
170 |
$to_dbh->do($query . $result) || die "Can't insert row: $DBI::errstr"; |
|
171 |
}
|
|
172 |
if ($opt_lock && $to_server->{'lock_tables'}) |
|
173 |
{
|
|
174 |
$to_dbh->do("unlock tables"); |
|
175 |
}
|
|
176 |
}
|
|
177 |
||
178 |
||
179 |
sub get_table_definition |
|
180 |
{
|
|
181 |
my ($server,$dbh,$sth)=@_; |
|
182 |
my ($i,$names,$types,$scale,$precision,$nullable,@res); |
|
183 |
||
184 |
$names=$sth->{NAME}; |
|
185 |
$types=$sth->{TYPE}; |
|
186 |
$nullable=$sth->{NULLABLE}; |
|
187 |
if (0) |
|
188 |
{
|
|
189 |
# The following doesn't yet work
|
|
190 |
$scale=$sth->{SCALE}; |
|
191 |
$precision=$sth->{PRECISION}; |
|
192 |
}
|
|
193 |
else
|
|
194 |
{
|
|
195 |
my (@tmp); |
|
196 |
@tmp= (undef()) x $sth->{NUM_OF_FIELDS}; |
|
197 |
$precision= $scale= \@tmp; |
|
198 |
}
|
|
199 |
for ($i = 0; $i < $sth->{NUM_OF_FIELDS} ; $i++) |
|
200 |
{
|
|
201 |
push(@res,$names->[$i] . " " . |
|
202 |
odbc_to_sql($server,$types->[$i],$precision->[$i],$scale->[$i]) . |
|
203 |
($nullable->[$i] ? "" : " NOT NULL")); |
|
204 |
}
|
|
205 |
return \@res; |
|
206 |
}
|
|
207 |
||
208 |
||
209 |
sub odbc_to_sql |
|
210 |
{
|
|
211 |
my ($server,$type,$precision,$scale)=@_; |
|
212 |
||
213 |
if ($type == DBI::SQL_CHAR()) |
|
214 |
{
|
|
215 |
return defined($precision) ? "char($precision)" : "varchar(255)"; |
|
216 |
}
|
|
217 |
||
218 |
if ($type == DBI::SQL_NUMERIC()) |
|
219 |
{
|
|
220 |
$precision=15 if (!defined($precision)); |
|
221 |
$scale=6 if (!defined($scale)); |
|
222 |
return "numeric($precision,$scale)"; |
|
223 |
}
|
|
224 |
if ($type == DBI::SQL_DECIMAL()) |
|
225 |
{
|
|
226 |
$precision=15 if (!defined($precision)); |
|
227 |
$scale=6 if (!defined($scale)); |
|
228 |
return "decimal($precision,$scale)"; |
|
229 |
}
|
|
230 |
if ($type == DBI::SQL_INTEGER()) |
|
231 |
{
|
|
232 |
return "integer" if (!defined($precision)); |
|
233 |
return "integer($precision)"; |
|
234 |
}
|
|
235 |
if ($type == DBI::SQL_SMALLINT()) |
|
236 |
{
|
|
237 |
return "smallint" if (!defined($precision)); |
|
238 |
return "smallint($precision)"; |
|
239 |
}
|
|
240 |
if ($type == DBI::SQL_FLOAT()) |
|
241 |
{
|
|
242 |
$precision=12 if (!defined($precision)); |
|
243 |
$scale=2 if (!defined($scale)); |
|
244 |
return "float($precision,$scale)"; |
|
245 |
}
|
|
246 |
if ($type == DBI::SQL_REAL()) |
|
247 |
{
|
|
248 |
$precision=12 if (!defined($precision)); |
|
249 |
$scale=2 if (!defined($scale)); |
|
250 |
return "float($precision,$scale)"; |
|
251 |
}
|
|
252 |
if ($type == DBI::SQL_DOUBLE()) |
|
253 |
{
|
|
254 |
$precision=22 if (!defined($precision)); |
|
255 |
$scale=2 if (!defined($scale)); |
|
256 |
return "double($precision,$scale)"; |
|
257 |
}
|
|
258 |
if ($type == DBI::SQL_VARCHAR()) |
|
259 |
{
|
|
260 |
$precision=255 if (!defined($precision)); |
|
261 |
return "varchar($precision)"; |
|
262 |
}
|
|
263 |
return "date" if ($type == DBI::SQL_DATE()); |
|
264 |
return "time" if ($type == DBI::SQL_TIME()); |
|
265 |
return "timestamp" if ($type == DBI::SQL_TIMESTAMP()); |
|
266 |
return $server->{'text'} if ($type == DBI::SQL_LONGVARCHAR()); |
|
267 |
return $server->{'blob'} if ($type == DBI::SQL_LONGVARBINARY()); |
|
268 |
if ($type == DBI::SQL_BIGINT()) |
|
269 |
{
|
|
270 |
return "bigint" if (!defined($precision)); |
|
271 |
return "bigint($precision)"; |
|
272 |
}
|
|
273 |
if ($type == DBI::SQL_TINYINT()) |
|
274 |
{
|
|
275 |
return "tinyint" if (!defined($precision)); |
|
276 |
return "tinyint($precision)"; |
|
277 |
}
|
|
278 |
die "Can't covert type '$type' to a ODBC type\n"; |
|
279 |
}
|
|
280 |
||
281 |
#
|
|
282 |
# return an array with 1 for all coumns that we have to quote
|
|
283 |
#
|
|
284 |
||
285 |
sub get_columns_to_quote($sth) |
|
286 |
{
|
|
287 |
my ($sth)=@_; |
|
288 |
my ($i,@res,$type,$tmp); |
|
289 |
||
290 |
@res=(); |
|
291 |
for ($i = 0; $i < $sth->{NUM_OF_FIELDS} ; $i++) |
|
292 |
{
|
|
293 |
$type=$sth->{TYPE}->[$i]; |
|
294 |
$tmp=1; # String by default |
|
295 |
if ($type == DBI::SQL_NUMERIC() || $type == DBI::SQL_DECIMAL() || |
|
296 |
$type == DBI::SQL_INTEGER() || $type == DBI::SQL_SMALLINT() || |
|
297 |
$type == DBI::SQL_SMALLINT() || $type == DBI::SQL_FLOAT() || |
|
298 |
$type == DBI::SQL_REAL() || $type == DBI::SQL_DOUBLE() || |
|
299 |
$type == DBI::SQL_BIGINT() || $type == DBI::SQL_TINYINT()) |
|
300 |
{
|
|
301 |
$tmp=0; |
|
302 |
}
|
|
303 |
push (@res,$tmp); |
|
304 |
}
|
|
305 |
return \@res; |
|
306 |
}
|
|
307 |
||
308 |
#
|
|
309 |
# Check if table exists; Return 1 if table exists
|
|
310 |
#
|
|
311 |
||
312 |
sub table_exists |
|
313 |
{
|
|
314 |
my ($server,$dbh,$table)=@_; |
|
315 |
if ($server->{'limits'}->{'group_functions'}) |
|
316 |
{
|
|
317 |
return !safe_query($dbh,"select count(*) from $table"); |
|
318 |
}
|
|
319 |
if ($server->{'limits'}->{'limit'}) |
|
320 |
{
|
|
321 |
return !safe_query($dbh,"select * from $table limit 1"); |
|
322 |
}
|
|
323 |
die "Don't know how to check if table '$table' exists in destination server\n"; |
|
324 |
}
|
|
325 |
||
326 |
||
327 |
#
|
|
328 |
# execute query; return 0 if query is ok
|
|
329 |
#
|
|
330 |
||
331 |
sub safe_query |
|
332 |
{
|
|
333 |
my ($dbh,$query)=@_; |
|
334 |
my ($sth); |
|
335 |
||
336 |
print "query: $query\n" if ($opt_debug); |
|
337 |
if (!($sth= $dbh->prepare($query))) |
|
338 |
{
|
|
339 |
print "error: $DBI::errstr\n" if ($opt_debug); |
|
340 |
return 1; |
|
341 |
}
|
|
342 |
if (!$sth->execute) |
|
343 |
{
|
|
344 |
print "error: $DBI::errstr\n" if ($opt_debug); |
|
345 |
return 1 |
|
346 |
}
|
|
347 |
while ($sth->fetchrow_arrayref) |
|
348 |
{
|
|
349 |
}
|
|
350 |
$sth->finish; |
|
351 |
undef($sth); |
|
352 |
return 0; |
|
353 |
}
|
|
354 |
||
355 |
#
|
|
356 |
# execute an array of queries
|
|
357 |
#
|
|
358 |
||
359 |
sub do_many |
|
360 |
{
|
|
361 |
my ($dbh,@statements)=@_; |
|
362 |
my ($statement,$sth); |
|
363 |
||
364 |
foreach $statement (@statements) |
|
365 |
{
|
|
366 |
print "query: $statement\n" if ($opt_debug); |
|
367 |
if (!($sth=$dbh->do($statement))) |
|
368 |
{
|
|
369 |
die "Can't execute command '$statement'\nError: $DBI::errstr\n"; |
|
370 |
}
|
|
371 |
}
|
|
372 |
}
|