/[sql]/mysql2pgsql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Diff of /mysql2pgsql

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1.7 by dpavlin, Tue Oct 24 13:55:04 2000 UTC revision 1.8 by dpavlin, Thu Feb 1 15:15:48 2001 UTC
# Line 1  Line 1 
1  #!/usr/local/bin/perl -w  #!/usr/bin/perl -w
2    
3  # MySQL to PostgreSQL dump file converter  # MySQL to PostgreSQL dump file converter
4  #  #
5  # usage:  # For usage: mysqldump --help
 # mysqldump my_db_name | ./mysql2pgsql | psql pg_db_name  
6  #  #
7  # Convert mysqldump file (from MySQL) to something readable by psql !  # Convert mysqldump file (from MySQL) to something readable by psql !
8  #  #
# Line 14  Line 13 
13  #                (to set start value right)  #                (to set start value right)
14  # 2000-01-11 DbP now creates sequences with correct value  # 2000-01-11 DbP now creates sequences with correct value
15  # 2000-04-25 DbP import into CVS (at cvs.linux.hr)  # 2000-04-25 DbP import into CVS (at cvs.linux.hr)
16    # 2001-01-29 tpo -- Tomas Pospisek <tpo@sourcepole.ch>:
17    #                1) make script comply to usage:
18    #                2) make script output to STDOUT instead of STERR
19    #                3) change verbosity behaveour
20    #                4) add debug option
21    
22  # see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql  # see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql
23    
24  use DBI;  use DBI;
25    
26  if (!defined($ARGV[0]) || !defined($ARGV[1])) {  $DEBUG   =0;
27          print "Usage: $0 name_of_dump pg_database_name\n";  $VERBOSE =0;
28    
29    sub usage {
30            print "Usage: $0 [-v|--h] mysql_dump_file [pg_database_name]\n\n";
31            print "\t* mysql_dump_file is the filename of the mysql dump.\n";
32            print "\t* pg_database_name is the name of the postgres database.\n";
33            print "\t  When ommitted standard output is used.\n";
34            print "\t  Combined with -v, it will tell you what SQL\n";
35            print "\t  commands are being executed\n";
36    }
37    
38    # if mysql_dump_file is not suplied or --h is supplied then we dump usage info
39    if (! defined($ARGV[0]) || $ARGV[0] eq "--h") {
40            usage();
41          exit 1;          exit 1;
42  }  }
43    
44  $dump="$ARGV[0]";  if ($ARGV[0] eq "-v") {
45  $database="$ARGV[1]";          $VERBOSE = 1;
46            # if verbose is set then user has to supply both arguments
47            if(! defined($ARGV[1]) || ! defined($ARGV[2])) {
48                    usage();
49                    exit 1;
50            } else {
51                    $dump="$ARGV[1]";
52                    $database="$ARGV[1]";
53            }
54    } else {
55            $dump="$ARGV[0]";
56    
57  my $dbh = DBI->connect("DBI:Pg:dbname=template1","","") || die $DBI::errstr;          # if database name is supplied then use it
58  $dbh->do("create database $database") || die $dbh->errstr();          if (defined($ARGV[1])) {
59  $dbh->disconnect;                  $database="$ARGV[1]";
60            # else be verbose
61            } else {
62                    $database="";
63                    $VERBOSE = 1;
64            }
65    }
66    
67  $dbh = DBI->connect("DBI:Pg:dbname=$database","","") || die $DBI::errstr;  if ($database) {
68            my $dbh = DBI->connect("DBI:Pg:dbname=template1","","") || die $DBI::errstr;
69            $dbh->do("create database $database") || die $dbh->errstr();
70            $dbh->disconnect;
71    
72            $dbh = DBI->connect("DBI:Pg:dbname=$database","","") || die $DBI::errstr;
73    }
74    
75  $create=0;      # inside create table?  $create=0;      # inside create table?
76  $table="";  $table="";
# Line 41  open(DUMP,"$dump") || die "can't open du Line 79  open(DUMP,"$dump") || die "can't open du
79    
80  while(<DUMP>) {  while(<DUMP>) {
81          chomp;          chomp;
82            print "Processing line: $_\n" if $DEBUG;
83    
84  # nuke comments or empty lines  # nuke comments or empty lines
85          next if (/^#/ || /^$/);          next if (/^#/ || /^$/);
# Line 128  while(<DUMP>) { Line 167  while(<DUMP>) {
167                  # remap colums with names of existing system attribute                  # remap colums with names of existing system attribute
168                  if (/"oid"/i) {                  if (/"oid"/i) {
169                          s/"oid"/"_oid"/g;                          s/"oid"/"_oid"/g;
170                          print "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";                          print STDERR "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
171                          my $wait=<STDIN>;                          my $wait=<STDIN>;
172                  }                  }
173                  s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key                  s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
# Line 172  while(<DUMP>) { Line 211  while(<DUMP>) {
211                  $dosql.=");";   # nuked by split, put it back!                  $dosql.=");";   # nuked by split, put it back!
212                  if ("$dosql" ne "") {                  if ("$dosql" ne "") {
213                          $dosql=~s/ _dotcol_ /;/g;                          $dosql=~s/ _dotcol_ /;/g;
214                          print STDERR "$dosql\n";                          print "$dosql\n" if $VERBOSE;
215                          $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();                          if($database) {
216                                    $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();
217                            }
218                  } else {                  } else {
219                          print STDERR "empty sql!\n";                          print STDERR "empty sql!\n";
220                  }                  }
# Line 187  foreach $seq (@sequences) { Line 228  foreach $seq (@sequences) {
228          ($table,$field) = split(/\./,$seq);          ($table,$field) = split(/\./,$seq);
229    
230          $sql="select max($field)+1 from $table";          $sql="select max($field)+1 from $table";
231          print STDERR "$sql\n";          print "$sql\n" if $VERBOSE;
232          $sth = $dbh->prepare($sql) || die $dbh->errstr();          if($database){
233          $sth->execute() || die $sth->errstr();                  $sth = $dbh->prepare($sql) || die $dbh->errstr();
234          ($start) = $sth->fetchrow_array() || 1;                  $sth->execute() || die $sth->errstr();
235                    ($start) = $sth->fetchrow_array() || 1;
236            } else {
237                    print STDERR<<EOT
238    WARNING: Couldn't find the sequence start for the field $field
239             in table $table since you didn't give me an accessible DB.
240             Please verify the validity of the SQL command before inserting
241             into your DB.
242    EOT
243    ;
244                    $start = 1;
245            }
246          $seq="${table}_${field}_seq";          $seq="${table}_${field}_seq";
247    
248          $sql="create sequence $seq start $start increment 1";          $sql="create sequence $seq start $start increment 1";
249          print STDERR "$sql\n";          print "$sql\n" if $VERBOSE;
250          $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();          if($database){
251                    $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();
252            }
253  }  }
254    
255  print "\n";      print "\n";    
Legend:
Removed from v.1.7  
changed lines
  Added in v.1.8
  ViewVC Help
Powered by ViewVC 1.1.26