release/src/router/mysql/sql-bench/copy-db.sh

   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
  25 use Getopt::Long;
  26 use Cwd;
  27 use 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
  34 require "$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
  45 GetOptions("from-server=s","to-server=s","from-host=s","to-host=s","from-db=s",
  46            "to-db=s", "help", "verbose","debug") || usage();
  47
  48 usage() 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
  58 sub 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.
  90 EOF
  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;
 102 print "- 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
 111 foreach $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
 180 sub 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
 210 sub 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
 286 sub 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
 313 sub 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
 332 sub 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
 360 sub 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 }