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

Annotation of /mysql2pgsql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.6 - (hide annotations)
Tue Oct 24 11:51:00 2000 UTC (23 years, 5 months ago) by dpavlin
Branch: MAIN
Changes since 1.5: +5 -1 lines
pointers to web resources in preparation of release

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

  ViewVC Help
Powered by ViewVC 1.1.26