4
# My2Pg: MySQL to PostgreSQL dump conversion utility
6
# (c) 2000,2001 Maxim Rudensky <fonin@ziet.zhitomir.ua>
7
# (c) 2000 Valentine Danilchuk <valdan@ziet.zhitomir.ua>
10
# Redistribution and use in source and binary forms, with or without
11
# modification, are permitted provided that the following conditions
13
# 1. Redistributions of source code must retain the above copyright
14
# notice, this list of conditions and the following disclaimer.
15
# 2. Redistributions in binary form must reproduce the above copyright
16
# notice, this list of conditions and the following disclaimer in the
17
# documentation and/or other materials provided with the distribution.
18
# 3. All advertising materials mentioning features or use of this software
19
# must display the following acknowledgement:
20
# This product includes software developed by the Max Rudensky
21
# and its contributors.
22
# 4. Neither the name of the author nor the names of its contributors
23
# may be used to endorse or promote products derived from this software
24
# without specific prior written permission.
26
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
27
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
28
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
29
# ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
30
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
31
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
32
# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
33
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
34
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
35
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
38
# $My2pg: my2pg.pl,v 1.28 2001/12/06 19:32:20 fonin Exp $
39
# $Id: my2pg.pl,v 1.13 2004-04-19 23:18:12 momjian Exp $
42
# Revision 1.9 2002/08/22 00:01:39 tgl
43
# Add a bunch of pseudo-types to replace the behavior formerly associated
44
# with OPAQUE, as per recent pghackers discussion. I still want to do some
45
# more work on the 'cstring' pseudo-type, but I'm going to commit the bulk
46
# of the changes now before the tree starts shifting under me ...
50
# Revision 1.13 2004-04-19 23:18:12 momjian
51
# Update to my2pg version 1.28, add docs, update URL for newest version.
53
# Create diff of custom changes Tom made to the utility for CREATE
56
# This will make moving this utility out of CVS easier.
58
# Revision 1.12 2004/04/19 23:11:49 momjian
59
# Update to my2pg 1.28, from:
61
# http://www.omnistarinc.com/~fonin/downloads.php#my2pg
63
# Revision 1.28 2002/11/30 12:03:48 fonin
64
# PostgreSQL does not support indexes on the partial length of column,
66
# CREATE INDEX i_index ON table (column(16));
67
# will not work. Fixed.
69
# Added command-line option -s that prevents my2pg from attempting convert
70
# the data (currently only timestamps).
72
# Better timestamps conversion.
74
# Revision 1.27 2002/07/16 14:54:07 fonin
75
# Bugfix - didn't quote the fields inside PRIMARY KEY with -d option.
76
# Fix by Milan P. Stanic <mps@rns-nis.co.yu>.
78
# Revision 1.26 2002/07/14 10:30:27 fonin
79
# Bugfix - MySQL keywords inside data (INSERT INTO sentence) were replaced
80
# with Postgres keywords and therefore messed up the data.
82
# Revision 1.25 2002/07/05 09:20:25 fonin
83
# - fixed data that contains two consecutive timestamps - thanks to
84
# Ben Darnell <bdarnell@google.com>
85
# - word 'default' was converted to upper case inside the data - fixed.
86
# Thanks to Madsen Wikholm <madsen@iki.fi>
88
# Revision 1.24 2002/04/20 14:15:43 fonin
89
# Patch by Felipe Nievinski <fnievinski@terra.com.br>.
90
# A table I was re-creating had a composite primary key, and I was using
91
# the -d switch to maintain the table and column names
92
# adding double quotes around them.
94
# The SQL code generated was something like this:
96
# CREATE TABLE "rinav" (
97
# "UnidadeAtendimento" INT8 DEFAULT '0' NOT NULL,
98
# "NumeroRinav" INT8 DEFAULT '0' NOT NULL,
100
# PRIMARY KEY ("UnidadeAtendimento"," NumeroRinav")
103
# Please note the space inside the second column name string in the PK
104
# definition. Because of this PostgreSQL was not able to create the table.
108
# Revision 1.23 2002/02/07 22:13:52 fonin
109
# Bugfix by Hans-Juergen Schoenig <hs@cybertec.at>: additional space after
110
# FLOAT8 is required.
112
# Revision 1.22 2001/12/06 19:32:20 fonin
113
# Patch: On line 594 where you check for UNIQUE, I believe the regex should try
114
# and match 'UNIQUE KEY'. Otherwise it outputs no unique indexes for the
116
# Thanks to Brad Hilton <bhilton@vpop.net>
118
# Revision 1.21 2001/08/25 18:55:28 fonin
119
# Incorporated changes from Yunliang Yu <yu@math.duke.edu>:
120
# - By default table & column names are not quoted; use the new
121
# "-d" option if you want to,
122
# - Use conditional substitutions to speed up and preserve
123
# the data integrity.
125
# - timestamps conversion fix. Shouldn't break now matching binary data and
128
# Revision 1.21 2001/07/23 03:04:39 yu
129
# Updates & fixes by Yunliang Yu <yu@math.duke.edu>
130
# . By default table & column names are not quoted; use the new
131
# "-d" option if you want to,
132
# . Use conditional substitutions to speed up and preserve
133
# the data integrity.
135
# Revision 1.20 2001/07/05 12:45:05 fonin
136
# Timestamp conversion enhancement from Joakim Lemstr�m <jocke@bytewize.com>
138
# Revision 1.19 2001/05/07 19:36:38 fonin
139
# Fixed a bug in quoting PRIMARY KEYs, KEYs and UNIQUE indexes with more than 2 columns. Thanks to Jeff Waugh <jaw@ic.net>.
141
# Revision 1.18 2001/03/06 22:25:40 fonin
142
# Documentation up2dating.
144
# Revision 1.17 2001/03/04 13:01:50 fonin
145
# Fixes to make work it right with MySQL 3.23 dumps. Tested on mysqldump 8.11.
146
# Also, AUTO_INCREMENT->SERIAL fields no more have DEFAULT and NOT NULL
149
# Revision 1.16 2001/02/02 08:15:34 fonin
150
# Sequences should be created BEFORE creating any objects \nthat depends on it.
152
# Revision 1.15 2001/01/30 10:13:36 fonin
153
# Re-released under BSD-like license.
155
# Revision 1.14 2000/12/18 20:55:13 fonin
156
# Better -n implementation.
158
# Revision 1.13 2000/12/18 15:26:33 fonin
159
# Added command-line options. -n forces *CHAR DEFAULT '' NOT NULL to be
160
# converted to *CHAR NULL.
161
# AUTO_INCREMENT fields converted not in SERIAL but in
162
# INT* NOT NULL DEFAULT nextval('seqname').
163
# Documentation refreshed.
164
# Dump enclosed in single transaction from now.
166
# Revision 1.12 2000/12/14 20:57:15 fonin
167
# Doublequotation bug fixed (in CREATE INDEX ON TABLE (field1,field2))
169
# Revision 1.10 2000/11/27 14:18:22 fonin
170
# Fixed bug - occasionaly was broken CREATE SEQUENCE generation
172
# Revision 1.8 2000/11/24 15:24:16 fonin
173
# TIMESTAMP fix: MySQL output YYYYMMDDmmhhss to YYYYMMDD mmhhss
175
# Revision 1.7 2000/11/22 23:04:41 fonin
176
# TIMESTAMP field fix. Better doublequoting. Splitting output dump
177
# into 2 transactions - create/load/indexing first, sequence setvals then.
178
# Added POD documentation.
184
my %opts; # command line options
185
my $chareg=''; # CHAR conversion regexps
186
my $dq=''; # double quote
189
getopts('nhds',\%opts);
197
# convert CHAR types from NOT NULL DEFAULT '' to NULL
199
$chareg='\s*?(default\s*?\'\')*?\s*?not\s*?null';
215
print("------------------------------------------------------------------");
216
print("\n-- My2Pg 1.28 translated dump");
218
print("\n------------------------------------------------------------------");
220
print("\n\nBEGIN;\n\n\n");
222
my %index; # contains array of CREATE INDEX for each table
223
my %seq; # contains CREATE SEQUENCE for each table
224
my %primary; # contains primary (eg SERIAL) fields for each table
225
my %identifier; # contains generated by this program identifiers
226
my $j=-1; # current position in $index{table}
227
my @check; # CHECK constraint for current
229
# generating full path to libtypes.c
230
my $libtypesource='libtypes.c';
231
my $libtypename=`pwd`;
233
$libtypename.='/libtypes.so';
235
# push header to libtypes.c
236
open(LIBTYPES,">$libtypesource");
237
print LIBTYPES "/******************************************************";
238
print LIBTYPES "\n * My2Pg 1.27 \translated dump";
239
print LIBTYPES "\n * User types definitions";
240
print LIBTYPES "\n ******************************************************/";
241
print LIBTYPES "\n\n#include <postgres.h>\n";
242
print LIBTYPES "\n#define ADD_COMMA if(strcmp(result,\"\")!=0) strcat(result,\",\")\n";
245
my $tabledef=0; # we are outside a table definition
247
if(!$tabledef && /^CREATE TABLE \S+/i){
249
} elsif($tabledef && /^\) type=\w*;/i){ # /^\w/i
253
# Comments start with -- in SQL
254
if(/^#/) {# !/insert into.*\(.*#.*\)/i, in mysqldump output
259
# Convert numeric types
260
s/tinyint\(\d+\)/INT2/i;
261
s/smallint\(\d+\)/INT2/i;
262
s/mediumint\(\d+\)/INT4/i;
263
s/bigint\(\d+\)/INT8/i;
265
s/float(\(\d+,\d*\))/DECIMAL$1/i;
266
s/double precision/FLOAT8 /i;
267
s/([\W])double(\(\d+,\d*\))/$1DECIMAL$2/i;
268
s/([\W])double[\W]/$1FLOAT8 /i;
269
s/([\W])real[\W]/$1FLOAT8 /i;
270
s/([\W])real(\(\d+,\d*\))/$1DECIMAL$2/i;
272
# Convert string types
273
s/\w*blob$chareg/text/i;
274
s/mediumtext$chareg/text/i;
275
s/tinytext$chareg/text/i;
276
s/\stext\s+not\s+null/ TEXT DEFAULT '' NOT NULL/i;
277
s/(.*?char\(.*?\))$chareg/$1/i;
279
# Old and New are reserved words in Postgres
280
s/^(\s+)Old /${1}MyOld /;
281
s/^(\s+)New /${1}MyNew /;
284
s/datetime/TIMESTAMP/;
285
s/timestamp\(\d+\)/TIMESTAMP/i;
287
if((/date/ig || /time/ig) && /[,(]\d{4}(\d{2})(\d{2})[,)]/ &&
288
$1>=0 && $1<=12 && $2>=0 && $2<=31) {
289
s/,(\d{4})(\d{2})(\d{2}),/,'$1-$2-$3 00:00:00',/g;
292
# small hack - convert "default" to uppercase, because below we
293
# enclose all lowercase words in double quotes
298
# Change all AUTO_INCREMENT fields to SERIAL ones with a pre-defined sequence
299
if(/([\w\d]+)\sint.*auto_increment/i) {
300
$tmpseq=new_name("$table_name"."_"."$+"."_SEQ",28);
301
$seq{$table_name}=$tmpseq;
302
$primary{$table_name}=$+;
303
s/(int.*?) .*AUTO_INCREMENT/$1 DEFAULT nextval\('$tmpseq'\)/i;
306
# convert UNSIGNED to CHECK constraints
307
if(/^\s+?([\w\d_]+).*?unsigned/i) {
308
$check.=",\n CHECK ($dq$1$dq>=0)";
312
# Limited ENUM support - little heuristic
313
s/enum\('N','Y'\)/BOOL/i;
314
s/enum\('Y','N'\)/BOOL/i;
316
if(/^\s+?([\w\d_]+).*?enum\((.*?)\)/i) {
320
while($enumlist=~s/'([\d\w_]+)'//i) {
323
# forming identifier name
324
$typename=new_name('enum_'.$table_name.'_'.$item[1],28);
325
# creating input type function
327
int2* $typename"."_in (char *str) {
333
result=(int2*)palloc(sizeof(int2));
335
for(my $i=0;$i<=$#item;$i++) {
337
if(strcmp(str,\"$item[$i]\")==0) {
343
elog(ERROR,\"$typename"."_in: incorrect input value\");
349
$types.="\n--- Types for table ".uc($table_name);
351
print LIBTYPES "\n/*";
352
print LIBTYPES "\n * Types for table ".uc($table_name);
353
print LIBTYPES "\n */\n";
355
$types.="\nCREATE FUNCTION $typename"."_in (cstring)
359
WITH (ISSTRICT, ISCACHABLE);\n";
361
# creating output function
363
char* $typename"."_out (int2 *outvalue) {
369
result=(char*)palloc(10);
370
switch (*outvalue) {";
371
for(my $i=0;$i<=$#item;$i++) {
374
strcpy(result,\"$item[$i]\");
379
elog(ERROR,\"$typename"."_out: incorrect stored value\");
385
$func_out.="\nbool $typename"."_eq(int2* a, int2* b) {
389
bool $typename"."_ne(int2* a, int2* b) {
393
bool $typename"."_lt(int2* a, int2* b) {
397
bool $typename"."_le(int2* a, int2* b) {
401
bool $typename"."_gt(int2* a, int2* b) {
405
bool $typename"."_ge(int2* a, int2* b) {
409
$types.="\nCREATE FUNCTION $typename"."_out ($typename)
413
WITH (ISSTRICT, ISCACHABLE);\n";
415
$types.="\nCREATE TYPE $typename (
417
input = $typename\_in,
418
output = $typename\_out
421
$types.="\nCREATE FUNCTION $typename"."_eq ($typename,$typename)
426
CREATE FUNCTION $typename"."_lt ($typename,$typename)
431
CREATE FUNCTION $typename"."_le ($typename,$typename)
436
CREATE FUNCTION $typename"."_gt ($typename,$typename)
441
CREATE FUNCTION $typename"."_ge ($typename,$typename)
446
CREATE FUNCTION $typename"."_ne ($typename,$typename)
453
rightarg = $typename,
455
procedure = $typename"."_lt
460
rightarg = $typename,
462
procedure = $typename"."_le
467
rightarg = $typename,
470
procedure = $typename"."_eq
475
rightarg = $typename,
477
procedure = $typename"."_ge
482
rightarg = $typename,
484
procedure = $typename"."_gt
489
rightarg = $typename,
491
procedure = $typename"."_ne
494
print LIBTYPES $func_in;
495
print LIBTYPES $func_out;
496
s/enum\(.*?\)/$typename/i;
500
if(/^\s+?([\w\d_]+).*?set\((.*?)\)/i) {
504
my $maxlen=0; # maximal string length
505
while($setlist=~s/'([\d\w_]+)'//i) {
507
$maxlen+=length($item[$#item])+1;
510
my $typesize=int($#item/8);
514
$internalsize=$typesize;
515
$typesize='int'.$typesize;
516
$typename=new_name('set_'.$table_name.'_'.$item[1],28);
517
# creating input type function
519
$typesize* $typename"."_in (char *str) {
526
result=($typesize*)palloc(sizeof($typesize));
528
if(strcmp(str,\"\")==0)
530
for(token=strtok(str,\",\");token!=NULL;token=strtok(NULL,\",\")) {";
531
for(my $i=0,my $j=1;$i<=$#item;$i++,$j*=2) {
533
if(strcmp(token,\"$item[$i]\")==0) {
542
elog(ERROR,\"$typename"."_in: incorrect input value\");
549
$types.="\n--- Types for table ".uc($table_name);
551
print LIBTYPES "\n/*";
552
print LIBTYPES "\n * Types for table ".uc($table_name);
553
print LIBTYPES "\n */\n";
555
$types.="\nCREATE FUNCTION $typename"."_in (cstring)
560
# creating output function
562
char* $typename"."_out ($typesize *outvalue) {
569
result=(char*)palloc($maxlen);
571
for(i=1;i<=2 << (sizeof(int2)*8);i*=2) {
572
switch (*outvalue & i) {";
573
for(my $i=0,$j=1;$i<=$#item;$i++,$j*=2) {
576
if($item[$i] ne '') {
577
$func_out.="ADD_COMMA;";
579
$func_out.="strcat(result,\"$item[$i]\");
590
$func_out.="\nbool $typename"."_eq($typesize* a, $typesize* b) {
594
$typesize find_in_set($typesize *a, $typesize *b) {
597
for(i=1;i<=sizeof($typesize)*8;i*=2) {
607
$types.="\nCREATE FUNCTION $typename"."_out ($typename)
612
$types.="\nCREATE TYPE $typename (
613
internallength = $internalsize,
614
input = $typename\_in,
615
output = $typename\_out
618
$types.="\nCREATE FUNCTION $typename"."_eq ($typename,$typename)
623
CREATE FUNCTION find_in_set ($typename,$typename)
630
rightarg = $typename,
632
procedure = $typename"."_eq
637
rightarg = $typename,
640
procedure = $typename"."_eq
645
print LIBTYPES $func_in;
646
print LIBTYPES $func_out;
647
s/set\(.*?\)/$typename/i;
650
# Change multy-field keys to multi-field indices
651
# MySQL Dump usually ends the CREATE TABLE statement like this:
652
# CREATE TABLE bids (
654
# PRIMARY KEY (bids_id),
655
# KEY offer_id (offer_id,user_id,the_time),
656
# KEY bid_value (bid_value)
658
# We want to replace this with smth like
659
# CREATE TABLE bids (
661
# PRIMARY KEY (bids_id),
663
# CREATE INDEX offer_id ON bids (offer_id,user_id,the_time);
664
# CREATE INDEX bid_value ON bids (bid_value);
665
if (s/CREATE TABLE (.*) /CREATE TABLE $dq$1$dq /i) {
666
if($oldtable ne $table_name) {
667
$oldtable=$table_name;
671
if($seq{$table_name} ne '') {
673
print "\n-- Sequences for table ".uc($table_name);
675
print "\nCREATE SEQUENCE ".$seq{$table_name}.";\n\n";
680
$dump=~s/,\n\).*;/\n\);/gmi;
681
# removing table options after closing bracket:
682
# ) TYPE=ISAM PACK_KEYS=1;
683
$dump=~s/\n\).*/\n\);/gmi;
690
# output CHECK constraints instead UNSIGNED modifiers
691
if(/PRIMARY KEY\s+\((.*)\)/i) {
693
$tmpfld=~s/,/","/g if $dq;
695
s/PRIMARY KEY\s+(\(.*\))/PRIMARY KEY \($dq$tmpfld$dq\)/i;
696
s/(PRIMARY KEY \(.*\)).*/$1$check\n/i;
699
if(/^\s*KEY ([\w\d_]+)\s*\((.*)\).*/i) {
700
my $tmpfld=$2; my $ky=$1;
701
$tmpfld=~s/\s*,\s*/","/g if $dq;
702
$tmpfld=~s/(\(\d+\))//g;
703
$index{$table_name}[++$j]="CREATE INDEX ${ky}_$table_name\_index ON $dq$table_name$dq ($dq$tmpfld$dq);";
705
if(/^\s*UNIQUE.*?([\w\d_]+)\s*\((.*)\).*/i) {
706
my $tmpfld=$2; my $ky=$1;
707
$tmpfld=~s/,/","/g if $dq;
708
$tmpfld=~s/(\(\d+\))//g;
709
$index{$table_name}[++$j]="CREATE UNIQUE INDEX ${ky}_$table_name\_index ON $dq$table_name$dq ($dq$tmpfld$dq);";
711
s/^\s*UNIQUE (.+).*(\(.*\)).*\n//i;
712
s/^\s*KEY (.+).*(\(.*\)).*\n//i;
714
if($dq && !/^\s*(PRIMARY KEY|UNIQUE |KEY |CREATE TABLE|INSERT INTO|\);)/i) {
715
s/\s([A-Za-z_\d]+)\s/ $dq$+$dq /;
717
} # end of if($tabledef)
719
s/INSERT INTO\s+?(.*?)\s+?/INSERT INTO $dq$1$dq /i;
721
# if not defined -s command-line option (safe data conversion),
722
# attempting to convert timestamp data
723
if(!$safe_data_conv) {
725
s/'0000-00-00/'0001-01-01/g;
726
# may corrupt data !!!
727
s/([,(])00000000000000(?=[,)])/$1'00010101 000000'/g;
728
if(/[,(]\d{4}(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})[,)]/ &&
729
$1>=0 && $1<=12 && $2>=0 && $2<=31 && $3>=0 && $3<=23 &&
730
$4>=0 && $4<=59 && $5>=0 && $5<=59) {
731
s/([,(])(\d{8})(\d{6})(?=[,)])/$1'$2 $3'/g;
733
if(/[,(]\d{4}(\d{2})(\d{2})[,)]/ &&
734
$2>=0 && $2<=12 && $3>=0 && $3<=31) {
735
s/([,(])(\d{4})(\d{2})(\d{2})(?=[,)])/$1'$2-$3-$4 00:00:00'/g;
742
if($seq{$table_name} ne '') {
744
print "\n-- Sequences for table ".uc($table_name);
746
print "\nCREATE SEQUENCE ".$seq{$table_name}.";\n\n";
749
$dump=~s/,\n\).*;/\n\);/gmi;
750
$dump=~s/\n\).*/\n\);/gmi;
753
# Output indices for tables
754
while(my($table,$ind)=each(%index)) {
756
print "\n-- Indexes for table ".uc($table);
758
for(my $i=0;$i<=$#{$ind};$i++) {
759
print "\n$ind->[$i]";
764
while(my($table,$s)=each(%seq)) {
766
print "\n-- Sequences for table ".uc($table);
769
# setting SERIAL sequence values right
770
if($primary{$table} ne '') {
771
print "\nSELECT SETVAL('".$seq{$table}."',(select case when max($dq".$primary{$table}."$dq)>0 then max($dq".$primary{$table}."$dq)+1 else 1 end from $dq$table$dq));";
775
print("\n\nCOMMIT;\n");
778
open(MAKE,">Makefile");
780
# My2Pg \$Revision: 1.13 $ \translated dump
786
libtypes.o: libtypes.c
787
gcc -c -fPIC -g -O libtypes.c
788
libtypes.so: libtypes.o
789
ld -Bshareable -o libtypes.so libtypes.o";
793
# Function generates unique identifier
794
# Args : template name, max length
795
# Globals: %identifier
798
my $name=lc(shift @_);
801
# truncate long names
802
if(length($name)>$len) {
803
$name=~s/(.{$len}).*/$1/i;
806
# find reserved identifiers
807
if($identifier{$name}!=1) {
808
$identifier{$name}=1;
812
for(my $i=1,my $tmpname=$name.$i;$identifier{$tmpname}!=1;) {
815
$identifier{$tmpname}=1;
819
die "Error during unique identifier generation :-(";
824
my2pg - MySQL to PostgreSQL database dump converter
826
Copyright (c) 2000-2002 Max Rudensky <fonin\@ziet.zhitomir.ua>
827
Copyright (c) 2000 Valentine Danilchuk <valdan\@ziet.zhitomir.ua>
829
This program is distributed in the hope that it will be useful,
830
but WITHOUT ANY WARRANTY; without even the implied warranty of
831
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
832
code source for license details.
839
n - convert *CHAR NOT NULL DEFAULT '' types to *CHAR NULL
840
d - double quotes around table and column names
841
s - do not attempt to convert data (timestamps at the moment)
849
my2pg - MySQL -> PostgreSQL dump conversion utility.
853
mysqldump db | ./my2pg.pl [-nds] > pgsqldump.sql
856
psql database < pgsqldump.txt
861
=item F<pgsqldump.sql>
863
- file suitable for loading into PostgreSQL.
867
- C source for emulated MySQL types (ENUM, SET) generated by B<my2pg>
873
B<my2pg> utility attempts to convert MySQL database dump to Postgres's one.
874
B<my2pg> performs such conversions:
878
=item * Type conversion.
880
It tries to find proper Postgres
881
type for each column.
882
Unknown types are silently pushing to output dump;
883
ENUM and SET types implemented via user types
884
(C source for such types can be found in
887
=item * Encloses identifiers into double quotes.
890
names should be enclosed to double-quotes to prevent
891
conflict with reserved SQL keywords;
895
AUTO_INCREMENT fields to SERIAL. Actually, creating the sequence and
896
setting default value to nextval('seq'), well, you know :)
900
KEY(field) to CREATE INDEX i_field on table (field);
908
are creating AFTER rows insertion (to speed up the load);
910
=item * Translates '#'
912
MySQL comments to ANSI SQL '--'
916
It encloses dump in transaction block to prevent single errors
919
=head1 COMMAND-LINE OPTIONS
921
My2pg takes the following command-line options:
927
Convert *CHAR DEFAULT '' NOT NULL types to *CHAR NULL.
928
Postgres can't load empty '' strings in NOT NULL fields.
932
Add double quotes around table and column names
940
Do not attempt to convert data. Currently my2pg only tries to convert
951
file B<libtypes.c> in current directory
952
overwriting existed file without any checks;
966
=item * Possible problems with the timestamp data.
968
PostgreSQL does not accept incorrect date/time values like B<2002-00-15>,
969
while MySQL does not care about that. Currently my2pg cannot handle this
970
issue. You should care yourself to convert such a data.
972
=item * Use -s option if your numeric data are broken during conversion.
974
My2pg attempts to convert MySQL timestamps of the form B<yyyymmdd> to
975
B<yyyy-mm-dd> and B<yyyymmddhhmmss> to B<yyyy-mm-dd hh:mm:ss>. It performs
976
some heuristic checks to ensure that the month,day,hour,minutes and seconds have
977
values from the correct range (0..12, 0..31, 0..23, 0..59, 0..59 respectively).
978
It is still possible that your numeric values that satisfy these conditions
981
=item * Possible problems with enclosing identifiers in double quotes.
983
All identifiers such as table and column names should be enclosed in double
984
quotes. Program can't handle upper-case identifiers,
985
like DBA. Lower-case identifiers are OK.
987
=item * SET type emulation is not full. LIKE operation on
989
SETs, raw integer input values should be implemented
993
generated during output is
994
platform-dependent and surely works only on
995
Linux/gcc (FreeBSD/gcc probably works as well - not tested)
997
=item * Generated B<libtypes.c> contain line
999
#include <postgres.h>
1001
This file may be located not in standard compiler
1002
include path, you need to check it before compiling.
1008
B<(c) 2000-2002 Maxim V. Rudensky (fonin@ziet.zhitomir.ua)> (developer, maintainer)
1010
B<(c) 2000 Valentine V. Danilchuk (valdan@ziet.zhitomir.ua)> (original script)
1014
Great thanks to all those people who provided feedback and make development
1015
of this tool easier.
1017
Jeff Waugh <jaw@ic.net>
1019
Joakim Lemstr�m <jocke@bytewize.com> || <buddyh19@hotmail.com>
1021
Yunliang Yu <yu@math.duke.edu>
1023
Brad Hilton <bhilton@vpop.net>
1025
If you are not listed here please write to me.
added
removed
contrib/mysql/my2pg.pl
