/[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

Contents of /mysql2pgsql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.9 - (show annotations)
Thu Apr 12 12:41:12 2001 UTC (21 years, 7 months ago) by dpavlin
Branch: MAIN
CVS Tags: HEAD
Changes since 1.8: +6 -5 lines
fix

1 #!/usr/bin/perl -w
2
3 # MySQL to PostgreSQL dump file converter
4 #
5 # For usage: mysqldump --help
6 #
7 # Convert mysqldump file (from MySQL) to something readable by psql !
8 #
9 # homepage: http://www.rot13.org/~dpavlin/projects.html
10
11 # 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
12 # 1999-12-26 DbP don't make serial from auto_increment, create all manually
13 # (to set start value right)
14 # 2000-01-11 DbP now creates sequences with correct value
15 # 2000-04-25 DbP import into CVS (at cvs.linux.hr)
16 # 2001-01-29 tpo -- Tomas Pospisek <tpo@sourcepole.ch>:
17 # 1) make script comply to usage:
18 # 2) make script output to STDOUT instead of STERR
19 # 3) change verbosity behaveour
20 # 4) add debug option
21
22 # see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql
23
24 use DBI;
25
26 $DEBUG =0;
27 $VERBOSE =0;
28
29 sub usage {
30 print "Usage: $0 [-v|--h] mysql_dump_file [pg_database_name]\n\n";
31 print "\t* mysql_dump_file is the filename of the mysql dump.\n";
32 print "\t* pg_database_name is the name of the postgres database.\n";
33 print "\t When ommitted standard output is used.\n";
34 print "\t Combined with -v, it will tell you what SQL\n";
35 print "\t commands are being executed\n";
36 }
37
38 # if mysql_dump_file is not suplied or --h is supplied then we dump usage info
39 if (! defined($ARGV[0]) || $ARGV[0] eq "--h") {
40 usage();
41 exit 1;
42 }
43
44 if ($ARGV[0] eq "-v") {
45 $VERBOSE = 1;
46 # if verbose is set then user has to supply both arguments
47 if(! defined($ARGV[1]) || ! defined($ARGV[2])) {
48 usage();
49 exit 1;
50 } else {
51 $dump="$ARGV[1]";
52 $database="$ARGV[1]";
53 }
54 } else {
55 $dump="$ARGV[0]";
56
57 # if database name is supplied then use it
58 if (defined($ARGV[1])) {
59 $database="$ARGV[1]";
60 # else be verbose
61 } else {
62 $database="";
63 $VERBOSE = 1;
64 }
65 }
66
67 if ($database) {
68 my $dbh = DBI->connect("DBI:Pg:dbname=template1","","") || die $DBI::errstr;
69 $dbh->do("create database $database") || die $dbh->errstr();
70 $dbh->disconnect;
71
72 $dbh = DBI->connect("DBI:Pg:dbname=$database","","") || die $DBI::errstr;
73 }
74
75 $create=0; # inside create table?
76 $table="";
77
78 open(DUMP,"$dump") || die "can't open dump file $dump";
79
80 while(<DUMP>) {
81 chomp; s/\r//g;
82 print "Processing line: $_\n" if $DEBUG;
83
84 # nuke comments or empty lines
85 next if (/^#/ || /^$/);
86
87 if ($create && /^\);/i) { # end of create table squence
88 $create=0;
89 $sql =~ s/,$//g; # strip last , inside create table
90 }
91
92 if ($create) { # are we inside create table?
93
94 # int,auto_increment -> serial
95 if (/int.*auto_increment/i) {
96
97
98 # this was simple solution, but squence isn't
99 # initialized correctly so I have to do a work-around
100 #
101 # s/\w*int.*auto_increment/serial/ig;
102
103 if (/^\s*(\w+)\s+/) {
104 $seq="${table}_${1}_seq";
105 push @sequences,"$table.$1";
106 s/(\s+)\w*int.*auto_increment[^,]*/$1int4 default nextval('$seq') not null/ig;
107 } else {
108 die "can't get name of field!";
109 }
110
111 # int type conversion
112 } elsif (/(\w*)int\(\d+\)/i) {
113 $size=$1;
114 $size =~ tr [A-Z] [a-z];
115 if ($size eq "tiny" || $size eq "small") {
116 $out = "int2";
117 } elsif ($size eq "big") {
118 $out = "int8";
119 } else {
120 $out = "int4";
121 }
122 s/\w*int\(\d+\)/$out/gc;
123 }
124
125 # nuke int unsigned
126 s/(int\w+)\s+unsigned/$1/gi;
127
128 # blob -> text
129 s/\w*blob/text/gi;
130 # tinytext/mediumtext -> text
131 s/tinytext/text/gi;
132 s/mediumtext/text/gi;
133
134 # char -> varchar
135 # PostgreSQL would otherwise pad with spaces as opposed
136 # to MySQL! Your user interface may depend on this!
137 s/\s+char/ varchar/gi;
138
139 # nuke date representation (not supported in PostgreSQL)
140 s/datetime default '[^']+'/datetime/i;
141 s/date default '[^']+'/datetime/i;
142 s/time default '[^']+'/datetime/i;
143
144 # change not null datetime field to null valid ones
145 # (to support remapping of "zero time" to null
146 s/datetime not null/datetime/i;
147
148 # nuke size of timestamp
149 s/timestamp\([^)]*\)/timestamp/i;
150
151 # double -> float8
152 s/double\([^)]*\)/float8/i;
153
154 # add unique to definition of type (MySQL separates this)
155 if (/unique \w+ \((\w+)\)/i) {
156 $sql=~s/($1)([^,]+)/$1$2 unique/i;
157 next;
158 }
159 # FIX: unique for multipe columns (col1,col2) are unsupported!
160 next if (/unique/i);
161
162 # FIX: nuke keys
163 next if (/^\s+key/i && !/^\s+primary key/i);
164
165 # quote column names
166 s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);
167
168 # remap colums with names of existing system attribute
169 if (/"oid"/i) {
170 s/"oid"/"_oid"/g;
171 print STDERR "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
172 my $wait=<STDIN>;
173 }
174 s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
175
176 } else { # not inside create table
177
178 #---- fix data in inserted data: (from MS world)
179 # FIX: disabled for now
180 if (00 && /insert into/i) {
181 s!\x96!-!g; # --
182 s!\x93!"!g; # ``
183 s!\x94!"!g; # ''
184 s!\x85!... !g; # \ldots
185 s!\x92!`!g;
186 }
187
188 # fix dates '0000-00-00 00:00:00' (should be null)
189 s/'0000-00-00 00:00:00'/null/gi;
190 s/'0000-00-00'/null/gi;
191 s/'00:00:00'/null/gi;
192 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'/;
193
194 # protect ; in inserts
195 while (/('[^']*);([^']*)'/) {
196 s/('[^']*);([^']*')/$1 _dotcol_ $2/g;
197 }
198 }
199
200 $sql.="$_";
201
202 if (/create table/i) {
203 $create++;
204 /create table (\w+)/i;
205 $table=$1 if (defined($1));
206 }
207
208
209
210 if ($sql=~/\);/) {
211 ($dosql,$sql)=split(/\);/,$sql);
212 $dosql.=");"; # nuked by split, put it back!
213 if ("$dosql" ne "") {
214 $dosql=~s/ _dotcol_ /;/g;
215 print "$dosql\n" if $VERBOSE;
216 if($database) {
217 $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();
218 }
219 } else {
220 print STDERR "empty sql!\n";
221 }
222 }
223
224 }
225
226 #print "creating sequences: @sequences\n";
227
228 foreach $seq (@sequences) {
229 ($table,$field) = split(/\./,$seq);
230
231 $sql="select max($field)+1 from $table";
232 print "$sql\n" if $VERBOSE;
233 if($database){
234 $sth = $dbh->prepare($sql) || die $dbh->errstr();
235 $sth->execute() || die $sth->errstr();
236 ($start) = $sth->fetchrow_array() || 1;
237 } else {
238 print STDERR<<EOT
239 WARNING: Couldn't find the sequence start for the field $field
240 in table $table since you didn't give me an accessible DB.
241 Please verify the validity of the SQL command before inserting
242 into your DB.
243 EOT
244 ;
245 $start = 1;
246 }
247 $seq="${table}_${field}_seq";
248
249 $sql="create sequence $seq start $start increment 1";
250 print "$sql\n" if $VERBOSE;
251 if($database){
252 $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();
253 }
254 }
255
256 print "\n";

  ViewVC Help
Powered by ViewVC 1.1.26