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