diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:07:14 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:07:14 +0000 |
commit | a175314c3e5827eb193872241446f2f8f5c9d33c (patch) | |
tree | cd3d60ca99ae00829c52a6ca79150a5b6e62528b /sql-bench/copy-db.sh | |
parent | Initial commit. (diff) | |
download | mariadb-10.5-upstream/1%10.5.12.tar.xz mariadb-10.5-upstream/1%10.5.12.zip |
Adding upstream version 1:10.5.12.upstream/1%10.5.12upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'sql-bench/copy-db.sh')
-rw-r--r-- | sql-bench/copy-db.sh | 373 |
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"; + } + } +} |