/[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.1.1.1 by dpavlin, Mon Mar 6 07:13:23 2000 UTC revision 1.9 by dpavlin, Thu Apr 12 12:41:12 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)
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 35  $table=""; Line 78  $table="";
78  open(DUMP,"$dump") || die "can't open dump file $dump";  open(DUMP,"$dump") || die "can't open dump file $dump";
79    
80  while(<DUMP>) {  while(<DUMP>) {
81          chomp;          chomp; s/\r//g;
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 47  while(<DUMP>) { Line 91  while(<DUMP>) {
91    
92          if ($create) {                  # are we inside create table?          if ($create) {                  # are we inside create table?
93    
                 # FIX: nuke keys  
                 next if (/^\s+key/i && !/^\s+primary key/i);  
   
94                  # int,auto_increment -> serial                  # int,auto_increment -> serial
95                  if (/int.*auto_increment/i) {                  if (/int.*auto_increment/i) {
96    
97    
98                          # this was simple solution, but squence isn't                          # this was simple solution, but squence isn't
99                          # initialized correctly so I have to do a work-around                          # initialized correctly so I have to do a work-around
100                          #                          #
# Line 61  while(<DUMP>) { Line 103  while(<DUMP>) {
103                          if (/^\s*(\w+)\s+/) {                          if (/^\s*(\w+)\s+/) {
104                                  $seq="${table}_${1}_seq";                                  $seq="${table}_${1}_seq";
105                                  push @sequences,"$table.$1";                                  push @sequences,"$table.$1";
106                                  s/\w*int.*auto_increment/int4 default nextval('$seq') not null/ig;                                  s/(\s+)\w*int.*auto_increment[^,]*/$1int4 default nextval('$seq') not null/ig;
107                          } else {                          } else {
108                                  die "can't get name of field!";                                  die "can't get name of field!";
109                          }                          }
# Line 85  while(<DUMP>) { Line 127  while(<DUMP>) {
127    
128                  # blob -> text                  # blob -> text
129                  s/\w*blob/text/gi;                  s/\w*blob/text/gi;
130                    # tinytext/mediumtext -> text
131                    s/tinytext/text/gi;
132                    s/mediumtext/text/gi;
133    
134                  # char -> varchar                  # char -> varchar
135                  # PostgreSQL would otherwise pad with spaces as opposed                  # PostgreSQL would otherwise pad with spaces as opposed
# Line 93  while(<DUMP>) { Line 138  while(<DUMP>) {
138    
139                  # nuke date representation (not supported in PostgreSQL)                  # nuke date representation (not supported in PostgreSQL)
140                  s/datetime default '[^']+'/datetime/i;                  s/datetime default '[^']+'/datetime/i;
141                    s/date default '[^']+'/datetime/i;
142                    s/time default '[^']+'/datetime/i;
143    
144                  # change not null datetime filend to null valid ones                  # change not null datetime field to null valid ones
145                  # (to support remapping of "zaro time" to null                  # (to support remapping of "zero time" to null
146                  s/datetime not null/datetime/i;                  s/datetime not null/datetime/i;
147    
148                    # nuke size of timestamp
149                    s/timestamp\([^)]*\)/timestamp/i;
150    
151                    # double -> float8
152                    s/double\([^)]*\)/float8/i;
153    
154                    # add unique to definition of type (MySQL separates this)
155                    if (/unique \w+ \((\w+)\)/i) {
156                            $sql=~s/($1)([^,]+)/$1$2 unique/i;
157                            next;
158                    }
159                    # FIX: unique for multipe columns (col1,col2) are unsupported!
160                    next if (/unique/i);
161    
162                    # FIX: nuke keys
163                    next if (/^\s+key/i && !/^\s+primary key/i);
164    
165                    # quote column names
166                    s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);
167    
168                    # remap colums with names of existing system attribute
169                    if (/"oid"/i) {
170                            s/"oid"/"_oid"/g;
171                            print STDERR "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
172                            my $wait=<STDIN>;
173                    }
174                    s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
175    
176            } else {        # not inside create table
177    
178                  #---- fix data in inserted data: (from MS world)                  #---- fix data in inserted data: (from MS world)
179                  # FIX: disabled for now                  # FIX: disabled for now
180                  if (00 && /insert into/i) {                  if (00 && /insert into/i) {
# Line 108  while(<DUMP>) { Line 185  while(<DUMP>) {
185                          s!\x92!`!g;                          s!\x92!`!g;
186                  }                  }
187    
         } else {        # not inside create table  
   
188                  # fix dates '0000-00-00 00:00:00' (should be null)                  # fix dates '0000-00-00 00:00:00' (should be null)
189                  s/'0000-00-00 00:00:00'/null/gi;                  s/'0000-00-00 00:00:00'/null/gi;
190                    s/'0000-00-00'/null/gi;
191                    s/'00:00:00'/null/gi;
192                    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'/;
193    
194                    # protect ; in inserts
195                    while (/('[^']*);([^']*)'/) {
196                            s/('[^']*);([^']*')/$1 _dotcol_ $2/g;
197                    }
198          }          }
199    
200          $sql.="$_";          $sql.="$_";
# Line 128  while(<DUMP>) { Line 211  while(<DUMP>) {
211                  ($dosql,$sql)=split(/\);/,$sql);                  ($dosql,$sql)=split(/\);/,$sql);
212                  $dosql.=");";   # nuked by split, put it back!                  $dosql.=");";   # nuked by split, put it back!
213                  if ("$dosql" ne "") {                  if ("$dosql" ne "") {
214                          print STDERR "$dosql\n";                          $dosql=~s/ _dotcol_ /;/g;
215                          $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();                          print "$dosql\n" if $VERBOSE;
216                            if($database) {
217                                    $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();
218                            }
219                  } else {                  } else {
220                          print STDERR "empty sql!\n";                          print STDERR "empty sql!\n";
221                  }                  }
# Line 143  foreach $seq (@sequences) { Line 229  foreach $seq (@sequences) {
229          ($table,$field) = split(/\./,$seq);          ($table,$field) = split(/\./,$seq);
230    
231          $sql="select max($field)+1 from $table";          $sql="select max($field)+1 from $table";
232          print STDERR "$sql\n";          print "$sql\n" if $VERBOSE;
233          $sth = $dbh->prepare($sql) || die $dbh->errstr();          if($database){
234          $sth->execute() || die $sth->errstr();                  $sth = $dbh->prepare($sql) || die $dbh->errstr();
235          ($start) = $sth->fetchrow_array();                  $sth->execute() || die $sth->errstr();
236                    ($start) = $sth->fetchrow_array() || 1;
237            } else {
238                    print STDERR<<EOT
239    WARNING: Couldn't find the sequence start for the field $field
240             in table $table since you didn't give me an accessible DB.
241             Please verify the validity of the SQL command before inserting
242             into your DB.
243    EOT
244    ;
245                    $start = 1;
246            }
247          $seq="${table}_${field}_seq";          $seq="${table}_${field}_seq";
248    
249          $sql="create sequence $seq start $start increment 1";          $sql="create sequence $seq start $start increment 1";
250          print STDERR "$sql\n";          print "$sql\n" if $VERBOSE;
251          $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();          if($database){
252                    $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();
253            }
254  }  }
255    
256  print "\n";      print "\n";    
Legend:
Removed from v.1.1.1.1  
changed lines
  Added in v.1.9
  ViewVC Help
Powered by ViewVC 1.1.26