/[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.4 - (hide annotations)
Tue Apr 25 14:02:47 2000 UTC (23 years, 11 months ago) by dpavlin
Branch: MAIN
Changes since 1.3: +1 -1 lines
if no element init squence to 1

1 dpavlin 1.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 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/\w*int.*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+) \w*int.*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    
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 dpavlin 1.2 s/date default '[^']+'/datetime/i;
98     s/time default '[^']+'/datetime/i;
99 dpavlin 1.1
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 dpavlin 1.2 # 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 dpavlin 1.1 #---- 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 dpavlin 1.2 s/'0000-00-00'/null/gi;
125     s/'00:00:00'/null/gi;
126 dpavlin 1.1 }
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 dpavlin 1.4 ($start) = $sth->fetchrow_array() || 1;
161 dpavlin 1.1
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