viewvc.cgi/sql/mysql2pgsql

#!/usr/local/bin/perl -w

# MySQL to PostgreSQL dump file converter
#
# usage:
# mysqldump my_db_name | ./mysql2pgsql | psql pg_db_name
#
# Convert mysqldump file (from MySQL) to something readable by psql !

# 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@linux.hr>
# 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)

use DBI;

if (!defined($ARGV[0]) || !defined($ARGV[1])) {
        print "Usage: $0 name_of_dump pg_database_name\n";
        exit 1;
}

$dump="$ARGV[0]";
$database="$ARGV[1]";

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

my $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;

# 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 -> text
                s/tinytext/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;
                }

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

        } 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'/;
        }

        $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 "") {
                        print STDERR "$dosql\n";
                        $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 STDERR "$sql\n";
        $sth = $dbh->prepare($sql) || die $dbh->errstr();
        $sth->execute() || die $sth->errstr();
        ($start) = $sth->fetchrow_array() || 1;

        $seq="${table}_${field}_seq";

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

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