/[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.5 by dpavlin, Tue Oct 24 11:40:45 2000 UTC revision 1.7 by dpavlin, Tue Oct 24 13:55:04 2000 UTC
# 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)  # 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    
22  if (!defined($ARGV[0]) || !defined($ARGV[1])) {  if (!defined($ARGV[0]) || !defined($ARGV[1])) {
# Line 27  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 86  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 -> text                  # tinytext/mediumtext -> text
94                  s/tinytext/text/gi;                  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 114  while(<DUMP>) { Line 119  while(<DUMP>) {
119                          $sql=~s/($1)([^,]+)/$1$2 unique/i;                          $sql=~s/($1)([^,]+)/$1$2 unique/i;
120                          next;                          next;
121                  }                  }
122                    # FIX: unique for multipe columns (col1,col2) are unsupported!
123                    next if (/unique/i);
124    
125                  # quote column names                  # quote column names
126                  s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);                  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          } else {        # not inside create table
137    
138                  #---- fix data in inserted data: (from MS world)                  #---- fix data in inserted data: (from MS world)
# Line 135  while(<DUMP>) { Line 150  while(<DUMP>) {
150                  s/'0000-00-00'/null/gi;                  s/'0000-00-00'/null/gi;
151                  s/'00:00:00'/null/gi;                  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'/;                  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 151  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 {
Legend:
Removed from v.1.5  
changed lines
  Added in v.1.7
  ViewVC Help
Powered by ViewVC 1.1.26