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

Contents of /mysql2pgsql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.2 - (show annotations)
Tue Apr 25 13:48:56 2000 UTC (23 years, 11 months ago) by dpavlin
Branch: MAIN
Changes since 1.1: +13 -2 lines
better support for "null" time, support for unique

1 #!/usr/local/bin/perl
2
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 # 2000-04-25 DbP import into CVS (at cvs.linux.hr)
15
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/\w*int.*auto_increment/serial/ig;
61
62 if (/^\s*(\w+)\s+/) {
63 $seq="${table}_${1}_seq";
64 push @sequences,"$table.$1";
65 s/\w*int.*auto_increment/int4 default nextval('$seq') not null/ig;
66 } 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
90 # char -> varchar
91 # PostgreSQL would otherwise pad with spaces as opposed
92 # to MySQL! Your user interface may depend on this!
93 s/\s+char/ varchar/gi;
94
95 # nuke date representation (not supported in PostgreSQL)
96 s/datetime default '[^']+'/datetime/i;
97 s/date default '[^']+'/datetime/i;
98 s/time default '[^']+'/datetime/i;
99
100 # change not null datetime filend to null valid ones
101 # (to support remapping of "zaro time" to null
102 s/datetime not null/datetime/i;
103
104 # add unique to definition of type (MySQL separates this)
105 if (/unique \w+ \((\w+)\)/i) {
106 $sql=~s/($1)([^,]+)/$1$2 unique/gi;
107 next;
108 }
109
110 } else { # not inside create table
111
112 #---- fix data in inserted data: (from MS world)
113 # FIX: disabled for now
114 if (00 && /insert into/i) {
115 s!\x96!-!g; # --
116 s!\x93!"!g; # ``
117 s!\x94!"!g; # ''
118 s!\x85!... !g; # \ldots
119 s!\x92!`!g;
120 }
121
122 # fix dates '0000-00-00 00:00:00' (should be null)
123 s/'0000-00-00 00:00:00'/null/gi;
124 s/'0000-00-00'/null/gi;
125 s/'00:00:00'/null/gi;
126 }
127
128 $sql.="$_";
129
130 if (/create table/i) {
131 $create++;
132 /create table (\w+)/i;
133 $table=$1 if (defined($1));
134 }
135
136
137
138 if ($sql=~/\);/) {
139 ($dosql,$sql)=split(/\);/,$sql);
140 $dosql.=");"; # nuked by split, put it back!
141 if ("$dosql" ne "") {
142 print STDERR "$dosql\n";
143 $dbh->do("$dosql") || die "do: '$dosql' ",$dbh->errstr();
144 } else {
145 print STDERR "empty sql!\n";
146 }
147 }
148
149 }
150
151 #print "creating sequences: @sequences\n";
152
153 foreach $seq (@sequences) {
154 ($table,$field) = split(/\./,$seq);
155
156 $sql="select max($field)+1 from $table";
157 print STDERR "$sql\n";
158 $sth = $dbh->prepare($sql) || die $dbh->errstr();
159 $sth->execute() || die $sth->errstr();
160 ($start) = $sth->fetchrow_array();
161
162 $seq="${table}_${field}_seq";
163
164 $sql="create sequence $seq start $start increment 1";
165 print STDERR "$sql\n";
166 $dbh->do("create sequence $seq start $start increment 1") || die $dbh->errstr();
167 }
168
169 print "\n";

  ViewVC Help
Powered by ViewVC 1.1.26