/[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.1 - (hide annotations)
Mon Mar 6 07:13:23 2000 UTC (24 years ago) by dpavlin
Branch: MAIN
Branch point for: DbP
Initial revision

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

  ViewVC Help
Powered by ViewVC 1.1.26