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

Annotation of /mysql2pgsql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.7 - (hide annotations)
Tue Oct 24 13:55:04 2000 UTC (23 years, 5 months ago) by dpavlin
Branch: MAIN
Changes since 1.6: +19 -2 lines
rename oid column to _oid, protect ; in insert

1 dpavlin 1.5 #!/usr/local/bin/perl -w
2 dpavlin 1.1
3     # MySQL to PostgreSQL dump file converter
4     #
5     # usage:
6     # mysqldump my_db_name | ./mysql2pgsql | psql pg_db_name
7     #
8     # Convert mysqldump file (from MySQL) to something readable by psql !
9 dpavlin 1.6 #
10     # homepage: http://www.rot13.org/~dpavlin/projects.html
11 dpavlin 1.1
12 dpavlin 1.6 # 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
13 dpavlin 1.1 # 1999-12-26 DbP don't make serial from auto_increment, create all manually
14     # (to set start value right)
15     # 2000-01-11 DbP now creates sequences with correct value
16 dpavlin 1.2 # 2000-04-25 DbP import into CVS (at cvs.linux.hr)
17 dpavlin 1.6
18     # see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql
19 dpavlin 1.1
20     use DBI;
21    
22     if (!defined($ARGV[0]) || !defined($ARGV[1])) {
23     print "Usage: $0 name_of_dump pg_database_name\n";
24     exit 1;
25     }
26    
27     $dump="$ARGV[0]";
28     $database="$ARGV[1]";
29    
30     my $dbh = DBI->connect("DBI:Pg:dbname=template1","","") || die $DBI::errstr;
31     $dbh->do("create database $database") || die $dbh->errstr();
32     $dbh->disconnect;
33    
34 dpavlin 1.7 $dbh = DBI->connect("DBI:Pg:dbname=$database","","") || die $DBI::errstr;
35 dpavlin 1.1
36    
37     $create=0; # inside create table?
38     $table="";
39    
40     open(DUMP,"$dump") || die "can't open dump file $dump";
41    
42     while(<DUMP>) {
43     chomp;
44    
45     # nuke comments or empty lines
46     next if (/^#/ || /^$/);
47    
48     if ($create && /^\);/i) { # end of create table squence
49     $create=0;
50     $sql =~ s/,$//g; # strip last , inside create table
51     }
52    
53     if ($create) { # are we inside create table?
54    
55     # FIX: nuke keys
56     next if (/^\s+key/i && !/^\s+primary key/i);
57    
58     # int,auto_increment -> serial
59     if (/int.*auto_increment/i) {
60    
61     # this was simple solution, but squence isn't
62     # initialized correctly so I have to do a work-around
63     #
64     # s/\w*int.*auto_increment/serial/ig;
65    
66     if (/^\s*(\w+)\s+/) {
67     $seq="${table}_${1}_seq";
68     push @sequences,"$table.$1";
69 dpavlin 1.3 s/(\w+) \w*int.*auto_increment/$1 int4 default nextval('$seq') not null/ig;
70 dpavlin 1.1 } else {
71     die "can't get name of field!";
72     }
73    
74     # int type conversion
75     } elsif (/(\w*)int\(\d+\)/i) {
76     $size=$1;
77     $size =~ tr [A-Z] [a-z];
78     if ($size eq "tiny" || $size eq "small") {
79     $out = "int2";
80     } elsif ($size eq "big") {
81     $out = "int8";
82     } else {
83     $out = "int4";
84     }
85     s/\w*int\(\d+\)/$out/gc;
86     }
87    
88     # nuke int unsigned
89     s/(int\w+)\s+unsigned/$1/gi;
90    
91     # blob -> text
92     s/\w*blob/text/gi;
93 dpavlin 1.7 # tinytext/mediumtext -> text
94 dpavlin 1.5 s/tinytext/text/gi;
95 dpavlin 1.7 s/mediumtext/text/gi;
96 dpavlin 1.1
97     # char -> varchar
98     # PostgreSQL would otherwise pad with spaces as opposed
99     # to MySQL! Your user interface may depend on this!
100     s/\s+char/ varchar/gi;
101    
102     # nuke date representation (not supported in PostgreSQL)
103     s/datetime default '[^']+'/datetime/i;
104 dpavlin 1.2 s/date default '[^']+'/datetime/i;
105     s/time default '[^']+'/datetime/i;
106 dpavlin 1.1
107 dpavlin 1.5 # change not null datetime field to null valid ones
108     # (to support remapping of "zero time" to null
109 dpavlin 1.1 s/datetime not null/datetime/i;
110    
111 dpavlin 1.5 # nuke size of timestamp
112     s/timestamp\([^)]*\)/timestamp/i;
113    
114     # double -> float8
115     s/double\([^)]*\)/float8/i;
116    
117 dpavlin 1.2 # add unique to definition of type (MySQL separates this)
118     if (/unique \w+ \((\w+)\)/i) {
119 dpavlin 1.5 $sql=~s/($1)([^,]+)/$1$2 unique/i;
120 dpavlin 1.2 next;
121     }
122 dpavlin 1.7 # FIX: unique for multipe columns (col1,col2) are unsupported!
123     next if (/unique/i);
124 dpavlin 1.2
125 dpavlin 1.5 # quote column names
126     s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);
127    
128 dpavlin 1.7 # 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 dpavlin 1.2 } else { # not inside create table
137    
138 dpavlin 1.1 #---- fix data in inserted data: (from MS world)
139     # FIX: disabled for now
140     if (00 && /insert into/i) {
141     s!\x96!-!g; # --
142     s!\x93!"!g; # ``
143     s!\x94!"!g; # ''
144     s!\x85!... !g; # \ldots
145     s!\x92!`!g;
146     }
147    
148     # fix dates '0000-00-00 00:00:00' (should be null)
149     s/'0000-00-00 00:00:00'/null/gi;
150 dpavlin 1.2 s/'0000-00-00'/null/gi;
151     s/'00:00:00'/null/gi;
152 dpavlin 1.5 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 dpavlin 1.7
154     # protect ; in inserts
155     while (/('[^']*);([^']*)'/) {
156     s/('[^']*);([^']*')/$1 _dotcol_ $2/g;
157     }
158 dpavlin 1.1 }
159    
160     $sql.="$_";
161    
162     if (/create table/i) {
163     $create++;
164     /create table (\w+)/i;
165     $table=$1 if (defined($1));
166     }
167    
168    
169    
170     if ($sql=~/\);/) {
171     ($dosql,$sql)=split(/\);/,$sql);
172     $dosql.=");"; # nuked by split, put it back!
173     if ("$dosql" ne "") {
174 dpavlin 1.7 $dosql=~s/ _dotcol_ /;/g;
175 dpavlin 1.1 print STDERR "$dosql\n";
176     $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();
177     } else {
178     print STDERR "empty sql!\n";
179     }
180     }
181    
182     }
183    
184     #print "creating sequences: @sequences\n";
185    
186     foreach $seq (@sequences) {
187     ($table,$field) = split(/\./,$seq);
188    
189     $sql="select max($field)+1 from $table";
190     print STDERR "$sql\n";
191     $sth = $dbh->prepare($sql) || die $dbh->errstr();
192     $sth->execute() || die $sth->errstr();
193 dpavlin 1.4 ($start) = $sth->fetchrow_array() || 1;
194 dpavlin 1.1
195     $seq="${table}_${field}_seq";
196    
197     $sql="create sequence $seq start $start increment 1";
198     print STDERR "$sql\n";
199     $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();
200     }
201    
202     print "\n";

  ViewVC Help
Powered by ViewVC 1.1.26