viewvc.cgi/sql/mysql2pgsql

#!/usr/bin/perl -w

# MySQL to PostgreSQL dump file converter
#
# For usage: mysqldump --help
#
# Convert mysqldump file (from MySQL) to something readable by psql !
#
# homepage: http://www.rot13.org/~dpavlin/projects.html

# 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
# 1999-12-26 DbP don't make serial from auto_increment, create all manually
#                (to set start value right)
# 2000-01-11 DbP now creates sequences with correct value
# 2000-04-25 DbP import into CVS (at cvs.linux.hr)
# 2001-01-29 tpo -- Tomas Pospisek <tpo@sourcepole.ch>:
#                1) make script comply to usage:
#                2) make script output to STDOUT instead of STERR
#                3) change verbosity behaveour
#                4) add debug option

# see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql

use DBI;

$DEBUG   =0;
$VERBOSE =0;

sub usage {
        print "Usage: $0 [-v|--h] mysql_dump_file [pg_database_name]\n\n";
        print "\t* mysql_dump_file is the filename of the mysql dump.\n";
        print "\t* pg_database_name is the name of the postgres database.\n";
        print "\t  When ommitted standard output is used.\n";
        print "\t  Combined with -v, it will tell you what SQL\n";
        print "\t  commands are being executed\n";
}

# if mysql_dump_file is not suplied or --h is supplied then we dump usage info
if (! defined($ARGV[0]) || $ARGV[0] eq "--h") {
        usage();
        exit 1;
}

if ($ARGV[0] eq "-v") {
        $VERBOSE = 1;
        # if verbose is set then user has to supply both arguments
        if(! defined($ARGV[1]) || ! defined($ARGV[2])) {
                usage();
                exit 1;
        } else {
                $dump="$ARGV[1]";
                $database="$ARGV[1]";
        }
} else {
        $dump="$ARGV[0]";

        # if database name is supplied then use it
        if (defined($ARGV[1])) {
                $database="$ARGV[1]";
        # else be verbose
        } else {
                $database="";
                $VERBOSE = 1;
        }
}

if ($database) {
        my $dbh = DBI->connect("DBI:Pg:dbname=template1","","") || die $DBI::errstr;
        $dbh->do("create database $database") || die $dbh->errstr();
        $dbh->disconnect;

        $dbh = DBI->connect("DBI:Pg:dbname=$database","","") || die $DBI::errstr;
}

$create=0;      # inside create table?
$table="";

open(DUMP,"$dump") || die "can't open dump file $dump";

while(<DUMP>) {
        chomp;
        print "Processing line: $_\n" if $DEBUG;

# nuke comments or empty lines
        next if (/^#/ || /^$/);

        if ($create && /^\);/i) {       # end of create table squence
                $create=0;
                $sql =~ s/,$//g;        # strip last , inside create table
        }

        if ($create) {                  # are we inside create table?

                # FIX: nuke keys
                next if (/^\s+key/i && !/^\s+primary key/i);

                # int,auto_increment -> serial
                if (/int.*auto_increment/i) {

                        # this was simple solution, but squence isn't
                        # initialized correctly so I have to do a work-around
                        #
                        # s/\w*int.*auto_increment/serial/ig;

                        if (/^\s*(\w+)\s+/) {
                                $seq="${table}_${1}_seq";
                                push @sequences,"$table.$1";
                                s/(\w+) \w*int.*auto_increment/$1 int4 default nextval('$seq') not null/ig;
                        } else {
                                die "can't get name of field!";
                        }

                # int type conversion
                } elsif (/(\w*)int\(\d+\)/i) {
                        $size=$1;
                        $size =~ tr [A-Z] [a-z];
                        if ($size eq "tiny" || $size eq "small") {
                                $out = "int2";
                        } elsif ($size eq "big") {
                                $out = "int8";
                        } else {
                                $out = "int4";
                        }
                        s/\w*int\(\d+\)/$out/gc;
                }

                # nuke int unsigned
                s/(int\w+)\s+unsigned/$1/gi;

                # blob -> text
                s/\w*blob/text/gi;
                # tinytext/mediumtext -> text
                s/tinytext/text/gi;
                s/mediumtext/text/gi;

                # char -> varchar
                # PostgreSQL would otherwise pad with spaces as opposed
                # to MySQL! Your user interface may depend on this!
                s/\s+char/ varchar/gi;

                # nuke date representation (not supported in PostgreSQL)
                s/datetime default '[^']+'/datetime/i;
                s/date default '[^']+'/datetime/i;
                s/time default '[^']+'/datetime/i;

                # change not null datetime field to null valid ones
                # (to support remapping of "zero time" to null
                s/datetime not null/datetime/i;

                # nuke size of timestamp
                s/timestamp\([^)]*\)/timestamp/i;

                # double -> float8
                s/double\([^)]*\)/float8/i;

                # add unique to definition of type (MySQL separates this)
                if (/unique \w+ \((\w+)\)/i) {
                        $sql=~s/($1)([^,]+)/$1$2 unique/i;
                        next;
                }
                # FIX: unique for multipe columns (col1,col2) are unsupported!
                next if (/unique/i);

                # quote column names
                s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/key/i);

                # remap colums with names of existing system attribute 
                if (/"oid"/i) {
                        s/"oid"/"_oid"/g;
                        print STDERR "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
                        my $wait=<STDIN>;
                }
                s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key

        } else {        # not inside create table

                #---- fix data in inserted data: (from MS world)
                # FIX: disabled for now
                if (00 && /insert into/i) {
                        s!\x96!-!g;     # --
                        s!\x93!"!g;     # ``
                        s!\x94!"!g;     # ''
                        s!\x85!... !g;  # \ldots
                        s!\x92!`!g;
                }

                # fix dates '0000-00-00 00:00:00' (should be null)
                s/'0000-00-00 00:00:00'/null/gi;
                s/'0000-00-00'/null/gi;
                s/'00:00:00'/null/gi;
                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'/;

                # protect ; in inserts
                while (/('[^']*);([^']*)'/) {
                        s/('[^']*);([^']*')/$1 _dotcol_ $2/g;
                }
        }

        $sql.="$_";

        if (/create table/i) {
                $create++;
                /create table (\w+)/i;
                $table=$1 if (defined($1));
        }


        if ($sql=~/\);/) {
                ($dosql,$sql)=split(/\);/,$sql);
                $dosql.=");";   # nuked by split, put it back!
                if ("$dosql" ne "") {
                        $dosql=~s/ _dotcol_ /;/g;
                        print "$dosql\n" if $VERBOSE;
                        if($database) {
                                $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();
                        }
                } else {
                        print STDERR "empty sql!\n";
                }
        }

}

#print "creating sequences: @sequences\n";

foreach $seq (@sequences) {
        ($table,$field) = split(/\./,$seq);

        $sql="select max($field)+1 from $table";
        print "$sql\n" if $VERBOSE;
        if($database){
                $sth = $dbh->prepare($sql) || die $dbh->errstr();
                $sth->execute() || die $sth->errstr();
                ($start) = $sth->fetchrow_array() || 1;
        } else {
                print STDERR<<EOT
WARNING: Couldn't find the sequence start for the field $field
         in table $table since you didn't give me an accessible DB.
         Please verify the validity of the SQL command before inserting
         into your DB.
EOT
;
                $start = 1;
        }
        $seq="${table}_${field}_seq";

        $sql="create sequence $seq start $start increment 1";
        print "$sql\n" if $VERBOSE;
        if($database){
                $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();
        }
}

print "\n";     
1	dpavlin	1.8	#!/usr/bin/perl -w
2	dpavlin	1.1
3			# MySQL to PostgreSQL dump file converter
4			#
5	dpavlin	1.8	# For usage: mysqldump --help
6	dpavlin	1.1	#
7			# Convert mysqldump file (from MySQL) to something readable by psql !
8	dpavlin	1.6	#
9			# homepage: http://www.rot13.org/~dpavlin/projects.html
10	dpavlin	1.1
11	dpavlin	1.6	# 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
12	dpavlin	1.1	# 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	dpavlin	1.2	# 2000-04-25 DbP import into CVS (at cvs.linux.hr)
16	dpavlin	1.8	# 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	dpavlin	1.6
22			# see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql
23	dpavlin	1.1
24			use DBI;
25
26	dpavlin	1.8	$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	dpavlin	1.1	exit 1;
42			}
43
44	dpavlin	1.8	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	dpavlin	1.1
57	dpavlin	1.8	# 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	dpavlin	1.1
67	dpavlin	1.8	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	dpavlin	1.1
72	dpavlin	1.8	$dbh = DBI->connect("DBI:Pg:dbname=$database","","") \|\| die $DBI::errstr;
73			}
74	dpavlin	1.1
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;
82	dpavlin	1.8	print "Processing line: $_\n" if $DEBUG;
83	dpavlin	1.1
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			# FIX: nuke keys
95			next if (/^\s+key/i && !/^\s+primary key/i);
96
97			# int,auto_increment -> serial
98			if (/int.*auto_increment/i) {
99
100			# this was simple solution, but squence isn't
101			# initialized correctly so I have to do a work-around
102			#
103			# s/\wint.auto_increment/serial/ig;
104
105			if (/^\s*(\w+)\s+/) {
106			$seq="${table}_${1}_seq";
107			push @sequences,"$table.$1";
108	dpavlin	1.3	s/(\w+) \wint.auto_increment/$1 int4 default nextval('$seq') not null/ig;
109	dpavlin	1.1	} else {
110			die "can't get name of field!";
111			}
112
113			# int type conversion
114			} elsif (/(\w*)int\(\d+\)/i) {
115			$size=$1;
116			$size =~ tr [A-Z] [a-z];
117			if ($size eq "tiny" \|\| $size eq "small") {
118			$out = "int2";
119			} elsif ($size eq "big") {
120			$out = "int8";
121			} else {
122			$out = "int4";
123			}
124			s/\w*int\(\d+\)/$out/gc;
125			}
126
127			# nuke int unsigned
128			s/(int\w+)\s+unsigned/$1/gi;
129
130			# blob -> text
131			s/\w*blob/text/gi;
132	dpavlin	1.7	# tinytext/mediumtext -> text
133	dpavlin	1.5	s/tinytext/text/gi;
134	dpavlin	1.7	s/mediumtext/text/gi;
135	dpavlin	1.1
136			# char -> varchar
137			# PostgreSQL would otherwise pad with spaces as opposed
138			# to MySQL! Your user interface may depend on this!
139			s/\s+char/ varchar/gi;
140
141			# nuke date representation (not supported in PostgreSQL)
142			s/datetime default '[^']+'/datetime/i;
143	dpavlin	1.2	s/date default '[^']+'/datetime/i;
144			s/time default '[^']+'/datetime/i;
145	dpavlin	1.1
146	dpavlin	1.5	# change not null datetime field to null valid ones
147			# (to support remapping of "zero time" to null
148	dpavlin	1.1	s/datetime not null/datetime/i;
149
150	dpavlin	1.5	# nuke size of timestamp
151			s/timestamp\([^)]*\)/timestamp/i;
152
153			# double -> float8
154			s/double\([^)]*\)/float8/i;
155
156	dpavlin	1.2	# add unique to definition of type (MySQL separates this)
157			if (/unique \w+ \((\w+)\)/i) {
158	dpavlin	1.5	$sql=~s/($1)([^,]+)/$1$2 unique/i;
159	dpavlin	1.2	next;
160			}
161	dpavlin	1.7	# FIX: unique for multipe columns (col1,col2) are unsupported!
162			next if (/unique/i);
163	dpavlin	1.2
164	dpavlin	1.5	# quote column names
165			s/(^\s)(\S+)(\s)/$1"$2"$3/gi if (!/key/i);
166
167	dpavlin	1.7	# remap colums with names of existing system attribute
168			if (/"oid"/i) {
169			s/"oid"/"_oid"/g;
170	dpavlin	1.8	print STDERR "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
171	dpavlin	1.7	my $wait=<STDIN>;
172			}
173			s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
174
175	dpavlin	1.2	} else { # not inside create table
176
177	dpavlin	1.1	#---- fix data in inserted data: (from MS world)
178			# FIX: disabled for now
179			if (00 && /insert into/i) {
180			s!\x96!-!g; # --
181			s!\x93!"!g; # ``
182			s!\x94!"!g; # ''
183			s!\x85!... !g; # \ldots
184			s!\x92!`!g;
185			}
186
187			# fix dates '0000-00-00 00:00:00' (should be null)
188			s/'0000-00-00 00:00:00'/null/gi;
189	dpavlin	1.2	s/'0000-00-00'/null/gi;
190			s/'00:00:00'/null/gi;
191	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'/;
192	dpavlin	1.7
193			# protect ; in inserts
194			while (/('[^']);([^'])'/) {
195			s/('[^']);([^']')/$1 _dotcol_ $2/g;
196			}
197	dpavlin	1.1	}
198
199			$sql.="$_";
200
201			if (/create table/i) {
202			$create++;
203			/create table (\w+)/i;
204			$table=$1 if (defined($1));
205			}
206
207
208
209			if ($sql=~/\);/) {
210			($dosql,$sql)=split(/\);/,$sql);
211			$dosql.=");"; # nuked by split, put it back!
212			if ("$dosql" ne "") {
213	dpavlin	1.7	$dosql=~s/ _dotcol_ /;/g;
214	dpavlin	1.8	print "$dosql\n" if $VERBOSE;
215			if($database) {
216			$dbh->do("$dosql") \|\| die "do: '$dosql' ",$dbh->errstr();
217			}
218	dpavlin	1.1	} else {
219			print STDERR "empty sql!\n";
220			}
221			}
222
223			}
224
225			#print "creating sequences: @sequences\n";
226
227			foreach $seq (@sequences) {
228			($table,$field) = split(/\./,$seq);
229
230			$sql="select max($field)+1 from $table";
231	dpavlin	1.8	print "$sql\n" if $VERBOSE;
232			if($database){
233			$sth = $dbh->prepare($sql) \|\| die $dbh->errstr();
234			$sth->execute() \|\| die $sth->errstr();
235			($start) = $sth->fetchrow_array() \|\| 1;
236			} else {
237			print STDERR<<EOT
238			WARNING: Couldn't find the sequence start for the field $field
239			in table $table since you didn't give me an accessible DB.
240			Please verify the validity of the SQL command before inserting
241			into your DB.
242			EOT
243			;
244			$start = 1;
245			}
246	dpavlin	1.1	$seq="${table}_${field}_seq";
247
248			$sql="create sequence $seq start $start increment 1";
249	dpavlin	1.8	print "$sql\n" if $VERBOSE;
250			if($database){
251			$dbh->do("create sequence $seq start $start increment 1") \|\| die $dbh->errstr();
252			}
253	dpavlin	1.1	}
254
255			print "\n";