/[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.2 by dpavlin, Tue Apr 25 13:48:56 2000 UTC revision 1.8 by dpavlin, Thu Feb 1 15:15:48 2001 UTC
# Line 1  Line 1 
1  #!/usr/local/bin/perl  #!/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    #
9    # homepage: http://www.rot13.org/~dpavlin/projects.html
10    
11  # 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@linux.hr>  # 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
12  # 1999-12-26 DbP don't make serial from auto_increment, create all manually  # 1999-12-26 DbP don't make serial from auto_increment, create all manually
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
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  my $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 37  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 62  while(<DUMP>) { Line 105  while(<DUMP>) {
105                          if (/^\s*(\w+)\s+/) {                          if (/^\s*(\w+)\s+/) {
106                                  $seq="${table}_${1}_seq";                                  $seq="${table}_${1}_seq";
107                                  push @sequences,"$table.$1";                                  push @sequences,"$table.$1";
108                                  s/\w*int.*auto_increment/int4 default nextval('$seq') not null/ig;                                  s/(\w+) \w*int.*auto_increment/$1 int4 default nextval('$seq') not null/ig;
109                          } else {                          } else {
110                                  die "can't get name of field!";                                  die "can't get name of field!";
111                          }                          }
# Line 86  while(<DUMP>) { Line 129  while(<DUMP>) {
129    
130                  # blob -> text                  # blob -> text
131                  s/\w*blob/text/gi;                  s/\w*blob/text/gi;
132                    # tinytext/mediumtext -> text
133                    s/tinytext/text/gi;
134                    s/mediumtext/text/gi;
135    
136                  # char -> varchar                  # char -> varchar
137                  # PostgreSQL would otherwise pad with spaces as opposed                  # PostgreSQL would otherwise pad with spaces as opposed
# Line 97  while(<DUMP>) { Line 143  while(<DUMP>) {
143                  s/date default '[^']+'/datetime/i;                  s/date default '[^']+'/datetime/i;
144                  s/time default '[^']+'/datetime/i;                  s/time default '[^']+'/datetime/i;
145    
146                  # change not null datetime filend to null valid ones                  # change not null datetime field to null valid ones
147                  # (to support remapping of "zaro time" to null                  # (to support remapping of "zero time" to null
148                  s/datetime not null/datetime/i;                  s/datetime not null/datetime/i;
149    
150                    # nuke size of timestamp
151                    s/timestamp\([^)]*\)/timestamp/i;
152    
153                    # double -> float8
154                    s/double\([^)]*\)/float8/i;
155    
156                  # add unique to definition of type (MySQL separates this)                  # add unique to definition of type (MySQL separates this)
157                  if (/unique \w+ \((\w+)\)/i) {                  if (/unique \w+ \((\w+)\)/i) {
158                          $sql=~s/($1)([^,]+)/$1$2 unique/gi;                          $sql=~s/($1)([^,]+)/$1$2 unique/i;
159                          next;                          next;
160                  }                  }
161                    # FIX: unique for multipe columns (col1,col2) are unsupported!
162                    next if (/unique/i);
163    
164                    # quote column names
165                    s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);
166    
167                    # remap colums with names of existing system attribute
168                    if (/"oid"/i) {
169                            s/"oid"/"_oid"/g;
170                            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>;
172                    }
173                    s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
174    
175          } else {        # not inside create table          } else {        # not inside create table
176    
# Line 123  while(<DUMP>) { Line 188  while(<DUMP>) {
188                  s/'0000-00-00 00:00:00'/null/gi;                  s/'0000-00-00 00:00:00'/null/gi;
189                  s/'0000-00-00'/null/gi;                  s/'0000-00-00'/null/gi;
190                  s/'00:00:00'/null/gi;                  s/'00:00:00'/null/gi;
191                    s/([12]\d\d\d)([01]\d)([0-3]\d)([0-2]\d)([0-6]\d)([0-6]\d)/'$1-$2-$3 $4:$5:$6'/;
192    
193                    # protect ; in inserts
194                    while (/('[^']*);([^']*)'/) {
195                            s/('[^']*);([^']*')/$1 _dotcol_ $2/g;
196                    }
197          }          }
198    
199          $sql.="$_";          $sql.="$_";
# Line 139  while(<DUMP>) { Line 210  while(<DUMP>) {
210                  ($dosql,$sql)=split(/\);/,$sql);                  ($dosql,$sql)=split(/\);/,$sql);
211                  $dosql.=");";   # nuked by split, put it back!                  $dosql.=");";   # nuked by split, put it back!
212                  if ("$dosql" ne "") {                  if ("$dosql" ne "") {
213                          print STDERR "$dosql\n";                          $dosql=~s/ _dotcol_ /;/g;
214                          $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();                          print "$dosql\n" if $VERBOSE;
215                            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 154  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();                  $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.2  
changed lines
  Added in v.1.8
  ViewVC Help
Powered by ViewVC 1.1.26