/[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.6 by dpavlin, Tue Oct 24 11:51:00 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)  # 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 62  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 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
94                    s/tinytext/text/gi;
95    
96                  # char -> varchar                  # char -> varchar
97                  # PostgreSQL would otherwise pad with spaces as opposed                  # PostgreSQL would otherwise pad with spaces as opposed
# Line 97  while(<DUMP>) { Line 103  while(<DUMP>) {
103                  s/date default '[^']+'/datetime/i;                  s/date default '[^']+'/datetime/i;
104                  s/time default '[^']+'/datetime/i;                  s/time default '[^']+'/datetime/i;
105    
106                  # change not null datetime filend to null valid ones                  # change not null datetime field to null valid ones
107                  # (to support remapping of "zaro time" to null                  # (to support remapping of "zero time" to null
108                  s/datetime not null/datetime/i;                  s/datetime not null/datetime/i;
109    
110                    # nuke size of timestamp
111                    s/timestamp\([^)]*\)/timestamp/i;
112    
113                    # double -> float8
114                    s/double\([^)]*\)/float8/i;
115    
116                  # add unique to definition of type (MySQL separates this)                  # add unique to definition of type (MySQL separates this)
117                  if (/unique \w+ \((\w+)\)/i) {                  if (/unique \w+ \((\w+)\)/i) {
118                          $sql=~s/($1)([^,]+)/$1$2 unique/gi;                          $sql=~s/($1)([^,]+)/$1$2 unique/i;
119                          next;                          next;
120                  }                  }
121    
122                    # quote column names
123                    s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);
124    
125          } else {        # not inside create table          } else {        # not inside create table
126    
127                  #---- fix data in inserted data: (from MS world)                  #---- fix data in inserted data: (from MS world)
# Line 123  while(<DUMP>) { Line 138  while(<DUMP>) {
138                  s/'0000-00-00 00:00:00'/null/gi;                  s/'0000-00-00 00:00:00'/null/gi;
139                  s/'0000-00-00'/null/gi;                  s/'0000-00-00'/null/gi;
140                  s/'00:00:00'/null/gi;                  s/'00:00:00'/null/gi;
141                    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'/;
142          }          }
143    
144          $sql.="$_";          $sql.="$_";
# Line 157  foreach $seq (@sequences) { Line 173  foreach $seq (@sequences) {
173          print STDERR "$sql\n";          print STDERR "$sql\n";
174          $sth = $dbh->prepare($sql) || die $dbh->errstr();          $sth = $dbh->prepare($sql) || die $dbh->errstr();
175          $sth->execute() || die $sth->errstr();          $sth->execute() || die $sth->errstr();
176          ($start) = $sth->fetchrow_array();          ($start) = $sth->fetchrow_array() || 1;
177    
178          $seq="${table}_${field}_seq";          $seq="${table}_${field}_seq";
179    
Legend:
Removed from v.1.2  
changed lines
  Added in v.1.6
  ViewVC Help
Powered by ViewVC 1.1.26