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; s/\r//g;
        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?

                # 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/(\s+)\w*int.*auto_increment[^,]*/$1int4 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);

                # FIX: nuke keys
                next if (/^\s+key/i && !/^\s+primary key/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	dpavlin	1.9	chomp; s/\r//g;
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			# int,auto_increment -> serial
95			if (/int.*auto_increment/i) {
96
97	dpavlin	1.9
98	dpavlin	1.1	# this was simple solution, but squence isn't
99			# initialized correctly so I have to do a work-around
100			#
101			# s/\wint.auto_increment/serial/ig;
102
103			if (/^\s*(\w+)\s+/) {
104			$seq="${table}_${1}_seq";
105			push @sequences,"$table.$1";
106	dpavlin	1.9	s/(\s+)\wint.auto_increment[^,]*/$1int4 default nextval('$seq') not null/ig;
107	dpavlin	1.1	} 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	dpavlin	1.7	# tinytext/mediumtext -> text
131	dpavlin	1.5	s/tinytext/text/gi;
132	dpavlin	1.7	s/mediumtext/text/gi;
133	dpavlin	1.1
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	dpavlin	1.2	s/date default '[^']+'/datetime/i;
142			s/time default '[^']+'/datetime/i;
143	dpavlin	1.1
144	dpavlin	1.5	# change not null datetime field to null valid ones
145			# (to support remapping of "zero time" to null
146	dpavlin	1.1	s/datetime not null/datetime/i;
147
148	dpavlin	1.5	# nuke size of timestamp
149			s/timestamp\([^)]*\)/timestamp/i;
150
151			# double -> float8
152			s/double\([^)]*\)/float8/i;
153
154	dpavlin	1.2	# add unique to definition of type (MySQL separates this)
155			if (/unique \w+ \((\w+)\)/i) {
156	dpavlin	1.5	$sql=~s/($1)([^,]+)/$1$2 unique/i;
157	dpavlin	1.2	next;
158			}
159	dpavlin	1.7	# FIX: unique for multipe columns (col1,col2) are unsupported!
160			next if (/unique/i);
161	dpavlin	1.9
162			# FIX: nuke keys
163			next if (/^\s+key/i && !/^\s+primary key/i);
164	dpavlin	1.2
165	dpavlin	1.5	# quote column names
166			s/(^\s)(\S+)(\s)/$1"$2"$3/gi if (!/key/i);
167
168	dpavlin	1.7	# remap colums with names of existing system attribute
169			if (/"oid"/i) {
170			s/"oid"/"_oid"/g;
171	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.";
172	dpavlin	1.7	my $wait=<STDIN>;
173			}
174			s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
175
176	dpavlin	1.2	} else { # not inside create table
177
178	dpavlin	1.1	#---- 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	dpavlin	1.2	s/'0000-00-00'/null/gi;
191			s/'00:00:00'/null/gi;
192	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'/;
193	dpavlin	1.7
194			# protect ; in inserts
195			while (/('[^']);([^'])'/) {
196			s/('[^']);([^']')/$1 _dotcol_ $2/g;
197			}
198	dpavlin	1.1	}
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	dpavlin	1.7	$dosql=~s/ _dotcol_ /;/g;
215	dpavlin	1.8	print "$dosql\n" if $VERBOSE;
216			if($database) {
217			$dbh->do("$dosql") \|\| die "do: '$dosql' ",$dbh->errstr();
218			}
219	dpavlin	1.1	} 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	dpavlin	1.8	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	dpavlin	1.1	$seq="${table}_${field}_seq";
248
249			$sql="create sequence $seq start $start increment 1";
250	dpavlin	1.8	print "$sql\n" if $VERBOSE;
251			if($database){
252			$dbh->do("create sequence $seq start $start increment 1") \|\| die $dbh->errstr();
253			}
254	dpavlin	1.1	}
255
256			print "\n";