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	#!/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;
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	# 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	s/(\w+) \wint.auto_increment/$1 int4 default nextval('$seq') not null/ig;
109	} 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	# tinytext/mediumtext -> text
133	s/tinytext/text/gi;
134	s/mediumtext/text/gi;
135
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	s/date default '[^']+'/datetime/i;
144	s/time default '[^']+'/datetime/i;
145
146	# change not null datetime field to null valid ones
147	# (to support remapping of "zero time" to null
148	s/datetime not null/datetime/i;
149
150	# nuke size of timestamp
151	s/timestamp\([^)]*\)/timestamp/i;
152
153	# double -> float8
154	s/double\([^)]*\)/float8/i;
155
156	# add unique to definition of type (MySQL separates this)
157	if (/unique \w+ \((\w+)\)/i) {
158	$sql=~s/($1)([^,]+)/$1$2 unique/i;
159	next;
160	}
161	# FIX: unique for multipe columns (col1,col2) are unsupported!
162	next if (/unique/i);
163
164	# quote column names
165	s/(^\s)(\S+)(\s)/$1"$2"$3/gi if (!/key/i);
166
167	# remap colums with names of existing system attribute
168	if (/"oid"/i) {
169	s/"oid"/"_oid"/g;
170	print STDERR "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
171	my $wait=<STDIN>;
172	}
173	s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
174
175	} else { # not inside create table
176
177	#---- 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	s/'0000-00-00'/null/gi;
190	s/'00:00:00'/null/gi;
191	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
193	# protect ; in inserts
194	while (/('[^']);([^'])'/) {
195	s/('[^']);([^']')/$1 _dotcol_ $2/g;
196	}
197	}
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	$dosql=~s/ _dotcol_ /;/g;
214	print "$dosql\n" if $VERBOSE;
215	if($database) {
216	$dbh->do("$dosql") \|\| die "do: '$dosql' ",$dbh->errstr();
217	}
218	} 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	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	$seq="${table}_${field}_seq";
247
248	$sql="create sequence $seq start $start increment 1";
249	print "$sql\n" if $VERBOSE;
250	if($database){
251	$dbh->do("create sequence $seq start $start increment 1") \|\| die $dbh->errstr();
252	}
253	}
254
255	print "\n";