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 |
# |
# |
86 |
|
|
87 |
# blob -> text |
# blob -> text |
88 |
s/\w*blob/text/gi; |
s/\w*blob/text/gi; |
89 |
|
# tinytext -> text |
90 |
|
s/tinytext/text/gi; |
91 |
|
|
92 |
# char -> varchar |
# char -> varchar |
93 |
# PostgreSQL would otherwise pad with spaces as opposed |
# PostgreSQL would otherwise pad with spaces as opposed |
99 |
s/date default '[^']+'/datetime/i; |
s/date default '[^']+'/datetime/i; |
100 |
s/time default '[^']+'/datetime/i; |
s/time default '[^']+'/datetime/i; |
101 |
|
|
102 |
# change not null datetime filend to null valid ones |
# change not null datetime field to null valid ones |
103 |
# (to support remapping of "zaro time" to null |
# (to support remapping of "zero time" to null |
104 |
s/datetime not null/datetime/i; |
s/datetime not null/datetime/i; |
105 |
|
|
106 |
|
# nuke size of timestamp |
107 |
|
s/timestamp\([^)]*\)/timestamp/i; |
108 |
|
|
109 |
|
# double -> float8 |
110 |
|
s/double\([^)]*\)/float8/i; |
111 |
|
|
112 |
# add unique to definition of type (MySQL separates this) |
# add unique to definition of type (MySQL separates this) |
113 |
if (/unique \w+ \((\w+)\)/i) { |
if (/unique \w+ \((\w+)\)/i) { |
114 |
$sql=~s/($1)([^,]+)/$1$2 unique/gi; |
$sql=~s/($1)([^,]+)/$1$2 unique/i; |
115 |
next; |
next; |
116 |
} |
} |
117 |
|
|
118 |
|
# quote column names |
119 |
|
s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i); |
120 |
|
|
121 |
} else { # not inside create table |
} else { # not inside create table |
122 |
|
|
123 |
#---- fix data in inserted data: (from MS world) |
#---- fix data in inserted data: (from MS world) |
134 |
s/'0000-00-00 00:00:00'/null/gi; |
s/'0000-00-00 00:00:00'/null/gi; |
135 |
s/'0000-00-00'/null/gi; |
s/'0000-00-00'/null/gi; |
136 |
s/'00:00:00'/null/gi; |
s/'00:00:00'/null/gi; |
137 |
|
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'/; |
138 |
} |
} |
139 |
|
|
140 |
$sql.="$_"; |
$sql.="$_"; |
169 |
print STDERR "$sql\n"; |
print STDERR "$sql\n"; |
170 |
$sth = $dbh->prepare($sql) || die $dbh->errstr(); |
$sth = $dbh->prepare($sql) || die $dbh->errstr(); |
171 |
$sth->execute() || die $sth->errstr(); |
$sth->execute() || die $sth->errstr(); |
172 |
($start) = $sth->fetchrow_array(); |
($start) = $sth->fetchrow_array() || 1; |
173 |
|
|
174 |
$seq="${table}_${field}_seq"; |
$seq="${table}_${field}_seq"; |
175 |
|
|