/[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.7 by dpavlin, Tue Oct 24 13:55:04 2000 UTC
# Line 1  Line 1 
1  #!/usr/local/bin/perl  #!/usr/local/bin/perl -w
2    
3  # MySQL to PostgreSQL dump file converter  # MySQL to PostgreSQL dump file converter
4  #  #
# Line 6  Line 6 
6  # mysqldump my_db_name | ./mysql2pgsql | psql pg_db_name  # mysqldump my_db_name | ./mysql2pgsql | psql pg_db_name
7  #  #
8  # Convert mysqldump file (from MySQL) to something readable by psql !  # Convert mysqldump file (from MySQL) to something readable by psql !
9    #
10    # homepage: http://www.rot13.org/~dpavlin/projects.html
11    
12  # 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@linux.hr>  # 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
13  # 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
14  #                (to set start value right)  #                (to set start value right)
15  # 2000-01-11 DbP now creates sequences with correct value  # 2000-01-11 DbP now creates sequences with correct value
16    # 2000-04-25 DbP import into CVS (at cvs.linux.hr)
17    
18    # see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql
19    
20  use DBI;  use DBI;
21    
# Line 26  my $dbh = DBI->connect("DBI:Pg:dbname=te Line 31  my $dbh = DBI->connect("DBI:Pg:dbname=te
31  $dbh->do("create database $database") || die $dbh->errstr();  $dbh->do("create database $database") || die $dbh->errstr();
32  $dbh->disconnect;  $dbh->disconnect;
33    
34  my $dbh = DBI->connect("DBI:Pg:dbname=$database","","") || die $DBI::errstr;  $dbh = DBI->connect("DBI:Pg:dbname=$database","","") || die $DBI::errstr;
35    
36    
37  $create=0;      # inside create table?  $create=0;      # inside create table?
# Line 61  while(<DUMP>) { Line 66  while(<DUMP>) {
66                          if (/^\s*(\w+)\s+/) {                          if (/^\s*(\w+)\s+/) {
67                                  $seq="${table}_${1}_seq";                                  $seq="${table}_${1}_seq";
68                                  push @sequences,"$table.$1";                                  push @sequences,"$table.$1";
69                                  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;
70                          } else {                          } else {
71                                  die "can't get name of field!";                                  die "can't get name of field!";
72                          }                          }
# Line 85  while(<DUMP>) { Line 90  while(<DUMP>) {
90    
91                  # blob -> text                  # blob -> text
92                  s/\w*blob/text/gi;                  s/\w*blob/text/gi;
93                    # tinytext/mediumtext -> text
94                    s/tinytext/text/gi;
95                    s/mediumtext/text/gi;
96    
97                  # char -> varchar                  # char -> varchar
98                  # PostgreSQL would otherwise pad with spaces as opposed                  # PostgreSQL would otherwise pad with spaces as opposed
# Line 93  while(<DUMP>) { Line 101  while(<DUMP>) {
101    
102                  # nuke date representation (not supported in PostgreSQL)                  # nuke date representation (not supported in PostgreSQL)
103                  s/datetime default '[^']+'/datetime/i;                  s/datetime default '[^']+'/datetime/i;
104                    s/date default '[^']+'/datetime/i;
105                    s/time default '[^']+'/datetime/i;
106    
107                  # change not null datetime filend to null valid ones                  # change not null datetime field to null valid ones
108                  # (to support remapping of "zaro time" to null                  # (to support remapping of "zero time" to null
109                  s/datetime not null/datetime/i;                  s/datetime not null/datetime/i;
110    
111                    # nuke size of timestamp
112                    s/timestamp\([^)]*\)/timestamp/i;
113    
114                    # double -> float8
115                    s/double\([^)]*\)/float8/i;
116    
117                    # add unique to definition of type (MySQL separates this)
118                    if (/unique \w+ \((\w+)\)/i) {
119                            $sql=~s/($1)([^,]+)/$1$2 unique/i;
120                            next;
121                    }
122                    # FIX: unique for multipe columns (col1,col2) are unsupported!
123                    next if (/unique/i);
124    
125                    # quote column names
126                    s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);
127    
128                    # remap colums with names of existing system attribute
129                    if (/"oid"/i) {
130                            s/"oid"/"_oid"/g;
131                            print "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
132                            my $wait=<STDIN>;
133                    }
134                    s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
135    
136            } else {        # not inside create table
137    
138                  #---- fix data in inserted data: (from MS world)                  #---- fix data in inserted data: (from MS world)
139                  # FIX: disabled for now                  # FIX: disabled for now
140                  if (00 && /insert into/i) {                  if (00 && /insert into/i) {
# Line 108  while(<DUMP>) { Line 145  while(<DUMP>) {
145                          s!\x92!`!g;                          s!\x92!`!g;
146                  }                  }
147    
         } else {        # not inside create table  
   
148                  # fix dates '0000-00-00 00:00:00' (should be null)                  # fix dates '0000-00-00 00:00:00' (should be null)
149                  s/'0000-00-00 00:00:00'/null/gi;                  s/'0000-00-00 00:00:00'/null/gi;
150                    s/'0000-00-00'/null/gi;
151                    s/'00:00:00'/null/gi;
152                    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'/;
153    
154                    # protect ; in inserts
155                    while (/('[^']*);([^']*)'/) {
156                            s/('[^']*);([^']*')/$1 _dotcol_ $2/g;
157                    }
158          }          }
159    
160          $sql.="$_";          $sql.="$_";
# Line 128  while(<DUMP>) { Line 171  while(<DUMP>) {
171                  ($dosql,$sql)=split(/\);/,$sql);                  ($dosql,$sql)=split(/\);/,$sql);
172                  $dosql.=");";   # nuked by split, put it back!                  $dosql.=");";   # nuked by split, put it back!
173                  if ("$dosql" ne "") {                  if ("$dosql" ne "") {
174                            $dosql=~s/ _dotcol_ /;/g;
175                          print STDERR "$dosql\n";                          print STDERR "$dosql\n";
176                          $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();                          $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();
177                  } else {                  } else {
# Line 146  foreach $seq (@sequences) { Line 190  foreach $seq (@sequences) {
190          print STDERR "$sql\n";          print STDERR "$sql\n";
191          $sth = $dbh->prepare($sql) || die $dbh->errstr();          $sth = $dbh->prepare($sql) || die $dbh->errstr();
192          $sth->execute() || die $sth->errstr();          $sth->execute() || die $sth->errstr();
193          ($start) = $sth->fetchrow_array();          ($start) = $sth->fetchrow_array() || 1;
194    
195          $seq="${table}_${field}_seq";          $seq="${table}_${field}_seq";
196    
Legend:
Removed from v.1.1.1.1  
changed lines
  Added in v.1.7
  ViewVC Help
Powered by ViewVC 1.1.26