|
| 1 | +# -*- perl -*- |
| 2 | +# mysql2pgsql |
| 3 | +# Take a MySQL schema dump and turn it into SQL92 and PostgreSQL form. |
| 4 | +# Thomas Lockhart, (c) 2000, PostgreSQL Inc. |
| 5 | +# Thanks to Tim Perdue at SourceForge.Net for testing and feedback. |
| 6 | +# |
| 7 | + |
| 8 | +eval'(exit $?0)' &&eval'exec perl -S $0 ${1+"$@"}' |
| 9 | + &eval'exec perl -S $0 $argv:q' |
| 10 | +if 0; |
| 11 | + |
| 12 | +use IO::File; |
| 13 | +use Getopt::Long; |
| 14 | + |
| 15 | +my$progname ="mysql2pgsql"; |
| 16 | +my$version ="0.3"; |
| 17 | + |
| 18 | +GetOptions("debug!","verbose!","version","path=s","help"); |
| 19 | + |
| 20 | +my$debug =$opt_debug || 0; |
| 21 | +my$verbose =$opt_verbose || 0; |
| 22 | +my$pathfrom =$opt_path ||""; |
| 23 | + |
| 24 | +$pathfrom ="$pathfrom/"if ($pathfrom =~/.*[^\/]$/); |
| 25 | + |
| 26 | +print"$0:$progname version$version\n" |
| 27 | +if ($opt_version ||$opt_help); |
| 28 | +print"\t(c) 2000 Thomas Lockhart PostgreSQL Inc.\n" |
| 29 | +if ($opt_version &&$opt_verbose ||$opt_help); |
| 30 | + |
| 31 | +if ($opt_help) { |
| 32 | +print"$0 --verbose --version --help --path=dir infile ...\n"; |
| 33 | +exit; |
| 34 | +} |
| 35 | + |
| 36 | +while (@ARGV) { |
| 37 | +my$ostem; |
| 38 | + |
| 39 | +$iname =shift@ARGV; |
| 40 | +$ostem =$iname; |
| 41 | +$ostem =$1if ($ostem =~/.+\/([^\/]+)$/); |
| 42 | +$ostem =$1if ($ostem =~/(.+)[.][^.]*$/); |
| 43 | + |
| 44 | +$oname ="$ostem.sql92"; |
| 45 | +$pname ="$ostem.init"; |
| 46 | + |
| 47 | +print"$iname$oname$pname\n"if ($debug); |
| 48 | + |
| 49 | + TransformDumpFile($iname,$oname,$pname); |
| 50 | +} |
| 51 | + |
| 52 | +exit; |
| 53 | + |
| 54 | +subTransformDumpFile { |
| 55 | +local ($iname,$oname,$pname) =@_; |
| 56 | +local@dlines; |
| 57 | +local@slines; |
| 58 | +local@plines; |
| 59 | +local@tables;# list of tables created |
| 60 | +local%pkeys; |
| 61 | +local%pseqs; |
| 62 | +local%sequences; |
| 63 | + |
| 64 | +open(IN,"<$iname") ||die"Unable to open file$iname"; |
| 65 | + |
| 66 | +while (<IN>) { |
| 67 | +chomp; |
| 68 | +push@dlines,$_; |
| 69 | + } |
| 70 | + |
| 71 | +print"Calling CreateSchema with$#dlines lines\n"if ($debug); |
| 72 | +@slines = CreateSchema(@dlines); |
| 73 | + |
| 74 | +open(OUT,">$oname") ||die"Unable to open output file$oname"; |
| 75 | + |
| 76 | +foreach (@slines) { |
| 77 | +print">$_"if ($debug); |
| 78 | +print OUT"$_"; |
| 79 | + } |
| 80 | +close(OUT); |
| 81 | + |
| 82 | +returnif (!defined($pname)); |
| 83 | + |
| 84 | +@plines = PopulateSchema(@tables); |
| 85 | + |
| 86 | +open(OUT,">$pname") ||die"Unable to open output file$pname"; |
| 87 | + |
| 88 | +foreach (@plines) { |
| 89 | +print">$_"if ($debug); |
| 90 | +print OUT"$_"; |
| 91 | + } |
| 92 | +close(OUT); |
| 93 | +} |
| 94 | + |
| 95 | +subPopulateSchema { |
| 96 | +local@tables =@_; |
| 97 | +local@out; |
| 98 | +local$pkey; |
| 99 | +local$pseq; |
| 100 | + |
| 101 | +foreach (@tables) { |
| 102 | +$table =$_; |
| 103 | +$tpath ="$pathfrom$table"; |
| 104 | + |
| 105 | +print"Table is$table\n"if ($debug); |
| 106 | +push@out,"\n"; |
| 107 | +push@out,"copy$table from '$tpath.txt';\n"; |
| 108 | +if (defined($pkeys{$table})) { |
| 109 | +foreach ($pkeys{$table}) { |
| 110 | +$pkey =$_; |
| 111 | +$pseq =$pseqs{$table}; |
| 112 | + |
| 113 | +print"Key for$table is$pkey on$pseq\n"if ($debug); |
| 114 | + |
| 115 | +#//push @out, "\$value = select max($pkey) from $table;\n"; |
| 116 | +push@out,"select setval ('$pseq', (select max($pkey) from$table));\n"; |
| 117 | +} |
| 118 | +} |
| 119 | + } |
| 120 | + |
| 121 | +return@out; |
| 122 | +} |
| 123 | + |
| 124 | +subCreateSchema { |
| 125 | +local@lines =@_; |
| 126 | +local@out; |
| 127 | + |
| 128 | +# undef $last; |
| 129 | +local%knames; |
| 130 | + |
| 131 | +push@out,"--\n"; |
| 132 | +push@out,"-- Generated from mysql2pgsql\n"; |
| 133 | +push@out,"-- (c) 2000, Thomas Lockhart, PostgreSQL Inc.\n"; |
| 134 | +push@out,"--\n"; |
| 135 | +push@out,"\n"; |
| 136 | + |
| 137 | +while (@lines) { |
| 138 | +$_ =shift@lines; |
| 139 | +print"<$_\n"if ($debug); |
| 140 | +# Replace hash comments with SQL9x standard syntax |
| 141 | +$_ ="--$1"if (/^[\#](.*)/); |
| 142 | + |
| 143 | +# Found a CREATE TABLE statement? |
| 144 | +if (/(create\s+table)\s+(\w+)\s+([(])\s*$/i) { |
| 145 | +$table =$2; |
| 146 | +$table ="\"$1\""if ($table =~/^(user)$/); |
| 147 | +push@tables,$table; |
| 148 | +push@tabledef,"create table$table ("; |
| 149 | +#push @out, "$_\n"; |
| 150 | + |
| 151 | +while (@lines) { |
| 152 | +$_ =shift@lines; |
| 153 | +print"<$_\n"if ($debug); |
| 154 | + |
| 155 | +# Replace int(11) with SQL9x standard syntax |
| 156 | +while (/int\(\d*\)/gi) { |
| 157 | +$_ ="$`integer$'"; |
| 158 | +} |
| 159 | + |
| 160 | +# Replace float(10,2) with SQL9x standard syntax |
| 161 | +while (/(float)\((\d+),\s*(\d+)\)/gi) { |
| 162 | +$_ ="$`$1($2)$'"; |
| 163 | +} |
| 164 | + |
| 165 | +# Replace smallinteger with SQL9x syntax |
| 166 | +while (/smallinteger/gi) { |
| 167 | +$_ ="$`integer$'"; |
| 168 | +} |
| 169 | + |
| 170 | +# Replace mediumtext with PostgreSQL syntax |
| 171 | +while (/(longtext|mediumtext|blob|largeblob)/gi) { |
| 172 | +$_ ="$`text$'"; |
| 173 | +} |
| 174 | + |
| 175 | +# Replace integer ... auto_increment with PostgreSQL syntax |
| 176 | +while (/(\s*)(\w+)\s+integer\s+(.*)\s+auto_increment/gi) { |
| 177 | +$serid =$table ."_pk_seq"; |
| 178 | +push@out,"-- serial identifier$serid will likely be truncated\n" |
| 179 | +if (length($serid) >= 32); |
| 180 | + |
| 181 | +if (length($serid) >= 32) { |
| 182 | +$excess=(length($serid)-31); |
| 183 | +$serid =substr($table,0,-($excess)) ."_pk_seq"; |
| 184 | +push@out,"-- serial identifier$serid was truncated\n"; |
| 185 | +} |
| 186 | +push@out,"CREATE SEQUENCE$serid;\n\n"; |
| 187 | +$pkeys{$table} =$2; |
| 188 | +$pseqs{$table} =$serid; |
| 189 | +push@out,"-- key is$pkeys{$table}, sequence is$pseqs{$table}\n"if ($debug); |
| 190 | +$_ ="$`$1$2 integer default nextval('$serid')$3$'"; |
| 191 | +} |
| 192 | + |
| 193 | +# Replace date with double-quoted name |
| 194 | +# while (/^(\s*)(date|time)(\s+)/gi) { |
| 195 | +#$_ = "$1\"$2\"$3$'"; |
| 196 | +# } |
| 197 | + |
| 198 | +# Found "KEY"? Then remove it from the CREATE TABLE statement |
| 199 | +# and instead write a CREATE INDEX statement. |
| 200 | +if (/^\s*key\s+(\w+)\s*[(](\w[()\w\d,\s]*)[)][,]?/i) { |
| 201 | +$iname =$1; |
| 202 | +$column =$2; |
| 203 | +$iname =$1if ($iname =~/^idx_(\w[\_\w\d]+)/); |
| 204 | +# Sheesh, there can be upper bounds on index string sizes? |
| 205 | +# Get rid of the length specifier (e.g. filename(45) -> filename) |
| 206 | +while ($column =~/(\w[\w\d])[(]\d+[)]/g) { |
| 207 | +$column ="$`$1$'"; |
| 208 | +} |
| 209 | +#$column = $1 if ($column =~ /(\w+)[(]\d+[)]/); |
| 210 | +#push @out, "Index on $table($column) is $iname\n"; |
| 211 | +if (defined($knames{$iname})) { |
| 212 | +push@out,"--$iname already exists"; |
| 213 | +# sprintf($iname, "idx_%_%s", $table, $iname); |
| 214 | +# $iname = "idx_" . $table . "_" . $column; |
| 215 | +# Do not bother with more to the name; it will be too big anyway |
| 216 | +$iname =$table ."_" .$column; |
| 217 | +push@out,"; use$iname instead\n"; |
| 218 | +} |
| 219 | +$knames{$iname} =$iname; |
| 220 | +$keydef{$column} =$iname; |
| 221 | +#push @out, "! $_\n"; |
| 222 | +#$last = $tabledef[$#tabledef]; |
| 223 | +#push @out, "? $#tabledef $last\n"; |
| 224 | +#push @out, "match $1\n" if ($last =~ /(.*),\s*$/); |
| 225 | +# Remove the trailing comma from the previous line, if necessary |
| 226 | +$tabledef[$#tabledef] =$1 |
| 227 | +if (($#tabledef > 0) && ($tabledef[$#tabledef] =~/(.*),\s*$/)); |
| 228 | +#push @out, "? $tabledef[$#tabledef]\n"; |
| 229 | + |
| 230 | +# If this is the end of the statement, save it and exit loop |
| 231 | +}elsif (/^\s*[)]\;/) { |
| 232 | +push@tabledef,$_; |
| 233 | +#push @out, "< $_\n"; |
| 234 | +last; |
| 235 | + |
| 236 | +# Otherwise, just save the line |
| 237 | +}else { |
| 238 | +#push @out, "$last\n" if (defined($last)); |
| 239 | +#$last = $_; |
| 240 | +push@tabledef,$_; |
| 241 | +#push @out, "$_\n"; |
| 242 | +} |
| 243 | +} |
| 244 | + |
| 245 | +foreach$t (@tabledef) { |
| 246 | +push@out,"$t\n"; |
| 247 | +} |
| 248 | +undef@tabledef; |
| 249 | + |
| 250 | +foreach$k (keys%keydef) { |
| 251 | +push@out,"create index$keydef{$k} on$table ($k);\n"; |
| 252 | +} |
| 253 | +undef%keydef; |
| 254 | + |
| 255 | +}else { |
| 256 | +push@out,"$_\n"; |
| 257 | +} |
| 258 | + } |
| 259 | + |
| 260 | +# push @out, "$last\n" if (defined($last)); |
| 261 | + |
| 262 | +foreach (keys%pkeys) { |
| 263 | +my$val =$pkeys{$_}; |
| 264 | +print"key is$val\n"if ($debug); |
| 265 | + } |
| 266 | + |
| 267 | +return@out; |
| 268 | +} |
| 269 | + |
| 270 | +subStripComma { |
| 271 | +local$line =shift@_; |
| 272 | + |
| 273 | +$line ="$1"if ($line =~/(.*)[,]\s*$/); |
| 274 | + |
| 275 | +return$line; |
| 276 | +} |
