summaryrefslogtreecommitdiffstats
path: root/sql-bench/copy-db.sh
diff options
context:
space:
mode:
Diffstat (limited to 'sql-bench/copy-db.sh')
-rw-r--r--sql-bench/copy-db.sh373
1 files changed, 373 insertions, 0 deletions
diff --git a/sql-bench/copy-db.sh b/sql-bench/copy-db.sh
new file mode 100644
index 00000000..d70472fe
--- /dev/null
+++ b/sql-bench/copy-db.sh
@@ -0,0 +1,373 @@
+#!/usr/bin/env perl
+# Copyright (c) 2000, 2003, 2006 MySQL AB, 2009 Sun Microsystems, Inc.
+# Use is subject to license terms.
+#
+# This library is free software; you can redistribute it and/or
+# modify it under the terms of the GNU Library General Public
+# License as published by the Free Software Foundation; version 2
+# of the License.
+#
+# This library is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+# Library General Public License for more details.
+#
+# You should have received a copy of the GNU Library General Public
+# License along with this library; if not, write to the Free
+# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
+# MA 02110-1335 USA
+#
+# start initialition
+#
+
+$VER = "1.0";
+
+use Getopt::Long;
+use Cwd;
+use DBI;
+
+$max_row_length=500000; # Don't create bigger SQL rows that this
+$opt_lock=1; # lock tables
+
+$pwd = cwd(); $pwd = "." if ($pwd eq '');
+
+require "$pwd/server-cfg" || die "Can't read Configuration file: $!\n";
+
+$|=1;
+
+$opt_from_server= $opt_to_server= "mysql";
+$opt_from_host= $opt_to_host= "localhost";
+$opt_from_db= $opt_to_db= "test";
+$opt_from_user=$opt_from_password=$opt_to_user=$opt_to_password="";
+$opt_help=$opt_verbose=$opt_debug=0;
+
+
+GetOptions("from-server=s","to-server=s","from-host=s","to-host=s","from-db=s",
+ "to-db=s", "help", "verbose","debug") || usage();
+
+usage() if ($opt_help ||
+ ($opt_from_server eq $opt_to_server &&
+ $opt_from_db eq $opt_to_db &&
+ $opt_from_host eq $opt_to_host));
+
+####
+#### Usage
+####
+
+
+sub usage
+{
+ print <<EOF;
+
+$0 version $VER by Monty
+
+ Copies tables between two database servers. If the destination table doesn\'t
+ exist it\'s autoamticly created. If the destination table exists, it
+ should be compatible with the source table.
+
+ Because DBI doesn\'t provide full information about the columns in a table,
+ some columns may not have optimal types in a create tables. Any created
+ tables will also not have any keys!
+
+ Usage: $0 [options] tables...
+
+ Options:
+ --help Show this help and exit
+ --from-server Source server (Default: $opt_from_server)
+ --from-host Source hostname (Default: $opt_from_host)
+ --from-db Source database name (Default: $opt_from_db)
+ --from-user Source user (Default: $opt_from_password)
+ --from-password Source password (Default: $opt_from_password)
+ --to-server Destination server (Default: $opt_to_server)
+ --to-host Destination hostname (Default: $opt_to_host)
+ --to-db Destination database name (Default: $opt_to_db)
+ --to-user Destination user (Default: $opt_to_user)
+ --to-password Destination password (Default: $opt_to_password)
+ --verbose Be more verbose
+
+ If you the server names ends with _ODBC, then this program will connect
+ through ODBC instead of using a native driver.
+EOF
+ exit(0);
+}
+
+####
+#### Connect
+####
+
+$from_server=get_server($opt_from_server,$opt_from_host,$opt_from_db);
+$to_server=get_server($opt_to_server,$opt_to_host,$opt_to_db);
+
+$opt_user=$opt_from_user; $opt_password=$opt_from_password;
+print "- connecting to SQL servers\n" if ($opt_verbose);
+$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";
+$opt_user=$opt_to_user; $opt_password=$opt_to_password;
+$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";
+
+####
+#### Copy data
+####
+
+foreach $table (@ARGV)
+{
+
+ print "- querying $table\n" if ($opt_verbose);
+ $sth=$from_dbh->prepare("select * from $table") || die "Can't prepare query to get $table; $DBI::errstr";
+ $sth->execute || die "Can't execute query to get data from $table; $DBI::errstr";
+
+ if (!table_exists($to_server,$to_dbh,$table))
+ {
+ print "- creating $table\n" if ($opt_verbose);
+ $table_def=get_table_definition($from_server,$from_dbh,$sth);
+ do_many($to_dbh,$to_server->create($table,$table_def,[]));
+ }
+ if ($opt_lock && $to_server->{'lock_tables'})
+ {
+ print "- locking $table\n" if ($opt_verbose);
+ $to_dbh->do("lock tables $table WRITE");
+ }
+
+ $columns=$sth->{NUM_OF_FIELDS};
+ $columns_to_quote=get_columns_to_quote($sth);
+ $insert_multi_value=$sth->{'insert_multi_value'};
+ $query="insert into $table values"; $result="";
+
+ print "- copying $table\n" if ($opt_verbose);
+ while (($row = $sth->fetchrow_arrayref))
+ {
+ $tmp="(";
+ for ($i=0 ; $i < $columns ; $i++)
+ {
+ if ($columns_to_quote->[$i])
+ {
+ $tmp.= $to_dbh->quote($row->[$i]) . ",";
+ }
+ else
+ {
+ $tmp.= $row->[$i] . ",";
+ }
+ }
+ substr($tmp,-1)=")"; # Remove last ','
+ if ($insert_multi_value)
+ {
+ $to_dbh->do($query . $tmp) || die "Can't insert row: $DBI::errstr";
+ }
+ elsif (length($result)+length($tmp) >= $max_row_length && $result)
+ {
+ $to_dbh->do($query . $result) || die "Can't insert row: $DBI::errstr";
+ $result="";
+ }
+ elsif (length($result))
+ {
+ $result.= ",$tmp";
+ }
+ else
+ {
+ $result=$tmp;
+ }
+ }
+ if (length($result))
+ {
+ $to_dbh->do($query . $result) || die "Can't insert row: $DBI::errstr";
+ }
+ if ($opt_lock && $to_server->{'lock_tables'})
+ {
+ $to_dbh->do("unlock tables");
+ }
+}
+
+
+sub get_table_definition
+{
+ my ($server,$dbh,$sth)=@_;
+ my ($i,$names,$types,$scale,$precision,$nullable,@res);
+
+ $names=$sth->{NAME};
+ $types=$sth->{TYPE};
+ $nullable=$sth->{NULLABLE};
+ if (0)
+ {
+ # The following doesn't yet work
+ $scale=$sth->{SCALE};
+ $precision=$sth->{PRECISION};
+ }
+ else
+ {
+ my (@tmp);
+ @tmp= (undef()) x $sth->{NUM_OF_FIELDS};
+ $precision= $scale= \@tmp;
+ }
+ for ($i = 0; $i < $sth->{NUM_OF_FIELDS} ; $i++)
+ {
+ push(@res,$names->[$i] . " " .
+ odbc_to_sql($server,$types->[$i],$precision->[$i],$scale->[$i]) .
+ ($nullable->[$i] ? "" : " NOT NULL"));
+ }
+ return \@res;
+}
+
+
+sub odbc_to_sql
+{
+ my ($server,$type,$precision,$scale)=@_;
+
+ if ($type == DBI::SQL_CHAR())
+ {
+ return defined($precision) ? "char($precision)" : "varchar(255)";
+ }
+
+ if ($type == DBI::SQL_NUMERIC())
+ {
+ $precision=15 if (!defined($precision));
+ $scale=6 if (!defined($scale));
+ return "numeric($precision,$scale)";
+ }
+ if ($type == DBI::SQL_DECIMAL())
+ {
+ $precision=15 if (!defined($precision));
+ $scale=6 if (!defined($scale));
+ return "decimal($precision,$scale)";
+ }
+ if ($type == DBI::SQL_INTEGER())
+ {
+ return "integer" if (!defined($precision));
+ return "integer($precision)";
+ }
+ if ($type == DBI::SQL_SMALLINT())
+ {
+ return "smallint" if (!defined($precision));
+ return "smallint($precision)";
+ }
+ if ($type == DBI::SQL_FLOAT())
+ {
+ $precision=12 if (!defined($precision));
+ $scale=2 if (!defined($scale));
+ return "float($precision,$scale)";
+ }
+ if ($type == DBI::SQL_REAL())
+ {
+ $precision=12 if (!defined($precision));
+ $scale=2 if (!defined($scale));
+ return "float($precision,$scale)";
+ }
+ if ($type == DBI::SQL_DOUBLE())
+ {
+ $precision=22 if (!defined($precision));
+ $scale=2 if (!defined($scale));
+ return "double($precision,$scale)";
+ }
+ if ($type == DBI::SQL_VARCHAR())
+ {
+ $precision=255 if (!defined($precision));
+ return "varchar($precision)";
+ }
+ return "date" if ($type == DBI::SQL_DATE());
+ return "time" if ($type == DBI::SQL_TIME());
+ return "timestamp" if ($type == DBI::SQL_TIMESTAMP());
+ return $server->{'text'} if ($type == DBI::SQL_LONGVARCHAR());
+ return $server->{'blob'} if ($type == DBI::SQL_LONGVARBINARY());
+ if ($type == DBI::SQL_BIGINT())
+ {
+ return "bigint" if (!defined($precision));
+ return "bigint($precision)";
+ }
+ if ($type == DBI::SQL_TINYINT())
+ {
+ return "tinyint" if (!defined($precision));
+ return "tinyint($precision)";
+ }
+ die "Can't covert type '$type' to a ODBC type\n";
+}
+
+#
+# return an array with 1 for all coumns that we have to quote
+#
+
+sub get_columns_to_quote($sth)
+{
+ my ($sth)=@_;
+ my ($i,@res,$type,$tmp);
+
+ @res=();
+ for ($i = 0; $i < $sth->{NUM_OF_FIELDS} ; $i++)
+ {
+ $type=$sth->{TYPE}->[$i];
+ $tmp=1; # String by default
+ if ($type == DBI::SQL_NUMERIC() || $type == DBI::SQL_DECIMAL() ||
+ $type == DBI::SQL_INTEGER() || $type == DBI::SQL_SMALLINT() ||
+ $type == DBI::SQL_SMALLINT() || $type == DBI::SQL_FLOAT() ||
+ $type == DBI::SQL_REAL() || $type == DBI::SQL_DOUBLE() ||
+ $type == DBI::SQL_BIGINT() || $type == DBI::SQL_TINYINT())
+ {
+ $tmp=0;
+ }
+ push (@res,$tmp);
+ }
+ return \@res;
+}
+
+#
+# Check if table exists; Return 1 if table exists
+#
+
+sub table_exists
+{
+ my ($server,$dbh,$table)=@_;
+ if ($server->{'limits'}->{'group_functions'})
+ {
+ return !safe_query($dbh,"select count(*) from $table");
+ }
+ if ($server->{'limits'}->{'limit'})
+ {
+ return !safe_query($dbh,"select * from $table limit 1");
+ }
+ die "Don't know how to check if table '$table' exists in destination server\n";
+}
+
+
+#
+# execute query; return 0 if query is ok
+#
+
+sub safe_query
+{
+ my ($dbh,$query)=@_;
+ my ($sth);
+
+ print "query: $query\n" if ($opt_debug);
+ if (!($sth= $dbh->prepare($query)))
+ {
+ print "error: $DBI::errstr\n" if ($opt_debug);
+ return 1;
+ }
+ if (!$sth->execute)
+ {
+ print "error: $DBI::errstr\n" if ($opt_debug);
+ return 1
+ }
+ while ($sth->fetchrow_arrayref)
+ {
+ }
+ $sth->finish;
+ undef($sth);
+ return 0;
+}
+
+#
+# execute an array of queries
+#
+
+sub do_many
+{
+ my ($dbh,@statements)=@_;
+ my ($statement,$sth);
+
+ foreach $statement (@statements)
+ {
+ print "query: $statement\n" if ($opt_debug);
+ if (!($sth=$dbh->do($statement)))
+ {
+ die "Can't execute command '$statement'\nError: $DBI::errstr\n";
+ }
+ }
+}
generated by cgit v1.2.3 (git 2.39.1) at 2024-06-19 17:51:46 +0000