2
# Copyright (C) 2000, 2003 MySQL AB
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
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.
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,
28
$max_row_length=500000; # Don't create bigger SQL rows that this
29
$opt_lock=1; # lock tables
31
$pwd = cwd(); $pwd = "." if ($pwd eq '');
33
require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
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;
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();
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));
61
$0 version $VER by Monty
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.
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!
71
Usage: $0 [options] tables...
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
87
If you the server names ends with _ODBC, then this program will connect
88
through ODBC instead of using a native driver.
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);
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";
110
foreach $table (@ARGV)
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";
117
if (!table_exists($to_server,$to_dbh,$table))
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,[]));
123
if ($opt_lock && $to_server->{'lock_tables'})
125
print "- locking $table\n" if ($opt_verbose);
126
$to_dbh->do("lock tables $table WRITE");
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="";
134
print "- copying $table\n" if ($opt_verbose);
135
while (($row = $sth->fetchrow_arrayref))
138
for ($i=0 ; $i < $columns ; $i++)
140
if ($columns_to_quote->[$i])
142
$tmp.= $to_dbh->quote($row->[$i]) . ",";
146
$tmp.= $row->[$i] . ",";
149
substr($tmp,-1)=")"; # Remove last ','
150
if ($insert_multi_value)
152
$to_dbh->do($query . $tmp) || die "Can't insert row: $DBI::errstr";
154
elsif (length($result)+length($tmp) >= $max_row_length && $result)
156
$to_dbh->do($query . $result) || die "Can't insert row: $DBI::errstr";
159
elsif (length($result))
170
$to_dbh->do($query . $result) || die "Can't insert row: $DBI::errstr";
172
if ($opt_lock && $to_server->{'lock_tables'})
174
$to_dbh->do("unlock tables");
179
sub get_table_definition
181
my ($server,$dbh,$sth)=@_;
182
my ($i,$names,$types,$scale,$precision,$nullable,@res);
186
$nullable=$sth->{NULLABLE};
189
# The following doesn't yet work
190
$scale=$sth->{SCALE};
191
$precision=$sth->{PRECISION};
196
@tmp= (undef()) x $sth->{NUM_OF_FIELDS};
197
$precision= $scale= \@tmp;
199
for ($i = 0; $i < $sth->{NUM_OF_FIELDS} ; $i++)
201
push(@res,$names->[$i] . " " .
202
odbc_to_sql($server,$types->[$i],$precision->[$i],$scale->[$i]) .
203
($nullable->[$i] ? "" : " NOT NULL"));
211
my ($server,$type,$precision,$scale)=@_;
213
if ($type == DBI::SQL_CHAR())
215
return defined($precision) ? "char($precision)" : "varchar(255)";
218
if ($type == DBI::SQL_NUMERIC())
220
$precision=15 if (!defined($precision));
221
$scale=6 if (!defined($scale));
222
return "numeric($precision,$scale)";
224
if ($type == DBI::SQL_DECIMAL())
226
$precision=15 if (!defined($precision));
227
$scale=6 if (!defined($scale));
228
return "decimal($precision,$scale)";
230
if ($type == DBI::SQL_INTEGER())
232
return "integer" if (!defined($precision));
233
return "integer($precision)";
235
if ($type == DBI::SQL_SMALLINT())
237
return "smallint" if (!defined($precision));
238
return "smallint($precision)";
240
if ($type == DBI::SQL_FLOAT())
242
$precision=12 if (!defined($precision));
243
$scale=2 if (!defined($scale));
244
return "float($precision,$scale)";
246
if ($type == DBI::SQL_REAL())
248
$precision=12 if (!defined($precision));
249
$scale=2 if (!defined($scale));
250
return "float($precision,$scale)";
252
if ($type == DBI::SQL_DOUBLE())
254
$precision=22 if (!defined($precision));
255
$scale=2 if (!defined($scale));
256
return "double($precision,$scale)";
258
if ($type == DBI::SQL_VARCHAR())
260
$precision=255 if (!defined($precision));
261
return "varchar($precision)";
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())
270
return "bigint" if (!defined($precision));
271
return "bigint($precision)";
273
if ($type == DBI::SQL_TINYINT())
275
return "tinyint" if (!defined($precision));
276
return "tinyint($precision)";
278
die "Can't covert type '$type' to a ODBC type\n";
282
# return an array with 1 for all coumns that we have to quote
285
sub get_columns_to_quote($sth)
288
my ($i,@res,$type,$tmp);
291
for ($i = 0; $i < $sth->{NUM_OF_FIELDS} ; $i++)
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())
309
# Check if table exists; Return 1 if table exists
314
my ($server,$dbh,$table)=@_;
315
if ($server->{'limits'}->{'group_functions'})
317
return !safe_query($dbh,"select count(*) from $table");
319
if ($server->{'limits'}->{'limit'})
321
return !safe_query($dbh,"select * from $table limit 1");
323
die "Don't know how to check if table '$table' exists in destination server\n";
328
# execute query; return 0 if query is ok
336
print "query: $query\n" if ($opt_debug);
337
if (!($sth= $dbh->prepare($query)))
339
print "error: $DBI::errstr\n" if ($opt_debug);
344
print "error: $DBI::errstr\n" if ($opt_debug);
347
while ($sth->fetchrow_arrayref)
356
# execute an array of queries
361
my ($dbh,@statements)=@_;
362
my ($statement,$sth);
364
foreach $statement (@statements)
366
print "query: $statement\n" if ($opt_debug);
367
if (!($sth=$dbh->do($statement)))
369
die "Can't execute command '$statement'\nError: $DBI::errstr\n";
added
removed
tests/sql-bench/copy-db
