Skip to content

Commit 3345da5

Browse files
bmomjianbmomjian
committed
Update my2pg.pl for release.
1 parent 83d4a1c commit 3345da5

File tree

1 file changed

+91
-33
lines changed

1 file changed

+91
-33
lines changed

‎contrib/mysql/my2pg.pl

Lines changed: 91 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -35,7 +35,8 @@
3535
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
3636
# SUCH DAMAGE.
3737
#
38-
# $Id: my2pg.pl,v 1.4 2001/03/06 22:46:50 momjian Exp $
38+
# $My2pg: my2pg.pl,v 1.21 2001/08/25 18:55:28 fonin Exp $
39+
# $Id: my2pg.pl,v 1.5 2001/11/21 02:43:30 momjian Exp $
3940

4041
# TODO:
4142
# + Handle SETs
@@ -46,8 +47,31 @@
4647

4748
#
4849
# $Log: my2pg.pl,v $
49-
# Revision 1.4 2001/03/06 22:46:50 momjian
50-
# Update my2pg, new version.
50+
# Revision 1.5 2001/11/21 02:43:30 momjian
51+
# Update my2pg.pl for release.
52+
#
53+
# Revision 1.21 2001/08/25 18:55:28 fonin
54+
# Incorporated changes from Yunliang Yu <yu@math.duke.edu>:
55+
# - By default table & column names are not quoted; use the new
56+
# "-d" option if you want to,
57+
# - Use conditional substitutions to speed up and preserve
58+
# the data integrity.
59+
# Fixes by Max:
60+
# - timestamps conversion fix. Shouldn't break now matching binary data and
61+
# strings.
62+
#
63+
# Revision 1.21 2001/07/23 03:04:39 yu
64+
# Updates & fixes by Yunliang Yu <yu@math.duke.edu>
65+
# . By default table & column names are not quoted; use the new
66+
# "-d" option if you want to,
67+
# . Use conditional substitutions to speed up and preserve
68+
# the data integrity.
69+
#
70+
# Revision 1.20 2001/07/05 12:45:05 fonin
71+
# Timestamp conversion enhancement from Joakim Lemström <jocke@bytewize.com>
72+
#
73+
# Revision 1.19 2001/05/07 19:36:38 fonin
74+
# Fixed a bug in quoting PRIMARY KEYs, KEYs and UNIQUE indexes with more than 2 columns. Thanks to Jeff Waugh <jaw@ic.net>.
5175
#
5276
# Revision 1.18 2001/03/06 22:25:40 fonin
5377
# Documentation up2dating.
@@ -94,9 +118,10 @@
94118

95119
my %opts; # command line options
96120
my $chareg=''; # CHAR conversion regexps
121+
my $dq=''; # double quote
97122

98123
# parse command line
99-
getopts('nh',\%opts);
124+
getopts('nhd',\%opts);
100125

101126
# output syntax
102127
if($opts{h} ne '') {
@@ -108,12 +133,15 @@
108133
if($opts{n} ne '') {
109134
$chareg='\s*?(default\s*?\'\')*?\s*?not\s*?null';
110135
}
111-
136+
# want double quotes
137+
if($opts{d} ne '') {
138+
$dq='"';
139+
}
112140

113141
$|=1;
114142

115143
print("------------------------------------------------------------------");
116-
print("\n-- My2Pg \$Revision: 1.4 $ \translated dump");
144+
print("\n-- My2Pg \$Revision: 1.5 $ \translated dump");
117145
print("\n--");
118146
print("\n------------------------------------------------------------------");
119147

@@ -135,19 +163,28 @@
135163
# push header to libtypes.c
136164
open(LIBTYPES,">$libtypesource");
137165
print LIBTYPES "/******************************************************";
138-
print LIBTYPES "\n * My2Pg \$Revision: 1.4 $ \translated dump";
166+
print LIBTYPES "\n * My2Pg \$Revision: 1.5 $ \translated dump";
139167
print LIBTYPES "\n * User types definitions";
140168
print LIBTYPES "\n ******************************************************/";
141169
print LIBTYPES "\n\n#include <postgres.h>\n";
142170
print LIBTYPES "\n#define ADD_COMMA if(strcmp(result,\"\")!=0) strcat(result,\",\")\n";
143171

144172
# reading STDIN...
173+
my $tabledef=0; # we are outside a table definition
145174
while (<>) {
146175

176+
if(!$tabledef && /^CREATE TABLE \S+/i){
177+
$tabledef=1;
178+
}elsif($tabledef && /^\);/i){ # /^\w/i
179+
$tabledef=0;
180+
}
181+
147182
# Comments start with -- in SQL
148-
if(!/insert into.*\(.*#.*\)/i) {
183+
if(/^#/) {# !/insert into.*\(.*#.*\)/i, in mysqldump output
149184
s/#/--/;
150185
}
186+
187+
if($tabledef){##################################
151188
# Convert numeric types
152189
s/tinyint\(\d+\)/INT2/i;
153190
s/smallint\(\d+\)/INT2/i;
@@ -165,11 +202,18 @@
165202
s/\w*blob$chareg/text/i;
166203
s/mediumtext$chareg/text/i;
167204
s/tinytext$chareg/text/i;
205+
s/\stext\s+not\s+null/ TEXT DEFAULT '' NOT NULL/i;
168206
s/(.*?char\(.*?\))$chareg/$1/i;
169207

208+
# Old and New are reserved words in Postgres
209+
s/^(\s+)Old /${1}MyOld /;
210+
s/^(\s+)New /${1}MyNew /;
211+
170212
# Convert DATE types
171213
s/datetime/TIMESTAMP/;
172214
s/timestamp\(\d+\)/TIMESTAMP/i;
215+
s/ date / DATE /i;
216+
s/,(\d{4})(\d{2})(\d{2}),/,'$1-$2-$3 00:00:00',/g;
173217

174218
# small hack - convert "default" to uppercase, because below we
175219
# enclose all lowercase words in double quotes
@@ -184,17 +228,9 @@
184228
#s/(int.*?)DEFAULT\s*?'.*?'(.*?)AUTO_INCREMENT/$1$2DEFAULT nextval\('$tmpseq'\)/i;
185229
}
186230

187-
# Fix timestamps
188-
s/0000-00-00/0001-01-01/g;
189-
# may work wrong !!!
190-
s/00000000000000/00010101000000/g;
191-
s/(\d{8})(\d{6})/'$1 $2'/g;
192-
193-
#<Hackzone> ---------------------------------------------------
194-
195231
# convert UNSIGNED to CHECK constraints
196232
if(/^\s+?([\w\d_]+).*?unsigned/i) {
197-
$check.=",\n CHECK (\"$1\">=0)";
233+
$check.=",\n CHECK ($dq$1$dq>=0)";
198234
}
199235
s/unsigned//i;
200236

@@ -553,7 +589,7 @@
553589
# );
554590
# CREATE INDEX offer_id ON bids (offer_id,user_id,the_time);
555591
# CREATE INDEX bid_value ON bids (bid_value);
556-
if (s/CREATE TABLE (.*) /CREATE TABLE "$1" /i) {
592+
if (s/CREATE TABLE (.*) /CREATE TABLE $dq$1$dq /i) {
557593
if($oldtable ne $table_name) {
558594
$oldtable=$table_name;
559595
$j=-1;
@@ -581,30 +617,41 @@
581617
# output CHECK constraints instead UNSIGNED modifiers
582618
if(/PRIMARY KEY \((.*)\)/i) {
583619
my $tmpfld=$1;
584-
$tmpfld=~s/,/","/;
585-
s/PRIMARY KEY (\(.*\))/PRIMARY KEY \("$tmpfld"\)/i;
620+
$tmpfld=~s/,/","/g if $dq;
621+
s/PRIMARY KEY (\(.*\))/PRIMARY KEY \($dq$tmpfld$dq\)/i;
586622
s/(PRIMARY KEY \(.*\)).*/$1$check\n/i;
587623
}
588624

589625
if(/^\s*KEY ([\w\d_]+)\s*\((.*)\).*/i) {
590-
my $tmpfld=$2;
591-
$tmpfld=~s/\s*,\s*/","/;
592-
$index{$table_name}[++$j]="CREATE INDEX $1_$table_name\_index ON \"$table_name\" (\"$tmpfld\");";
626+
my $tmpfld=$2; my $ky=$1;
627+
$tmpfld=~s/\s*,\s*/","/g if $dq;
628+
$index{$table_name}[++$j]="CREATE INDEX ${ky}_$table_name\_index ON $dq$table_name$dq ($dq$tmpfld$dq);";
593629
}
594630
if(/^\s*UNIQUE ([\w\d_]+)\s*\((.*)\).*/i) {
595-
my $tmpfld=$2;
596-
$tmpfld=~s/,/","/;
597-
$index{$table_name}[++$j]="CREATE UNIQUE INDEX $1_$table_name\_index ON \"$table_name\" (\"$tmpfld\");";
631+
my $tmpfld=$2; my $ky=$1;
632+
$tmpfld=~s/,/","/g if $dq;
633+
$index{$table_name}[++$j]="CREATE UNIQUE INDEX ${ky}_$table_name\_index ON $dq$table_name$dq ($dq$tmpfld$dq);";
598634
}
599635
s/^\s*UNIQUE (.+).*(\(.*\)).*\n//i;
600636
s/^\s*KEY (.+).*(\(.*\)).*\n//i;
637+
638+
if($dq && !/^\s*(PRIMARY KEY|UNIQUE |KEY |CREATE TABLE |\);)/i){
639+
s/\s([A-Za-z_\d]+)\s/ $dq$+$dq /;
640+
}
641+
} ####if($tabledef)###############################
601642

602-
if(!s/INSERT INTO\s+?(.*?)\s+?/INSERT INTO "$1" /i) {
643+
if($dq && !s/INSERT INTO\s+?(.*?)\s+?/INSERT INTO $dq$1$dq /i) {
603644
# Quote lowercase identifiers in double quotes
604-
while(!/^--/ && s/\s([A-Za-z_\d]+[a-z][A-Za-z_\d]*)\s/ "$+" /) {;}
645+
#while(!/^--/ && s/\s([A-Za-z_\d]+[a-z][A-Za-z_\d]*)\s/ $dq$+$dq /) {;}
605646
}
606647

607-
648+
# Fix timestamps
649+
s/'0000-00-00/'0001-01-01/g;
650+
# may work wrong !!!
651+
s/([,(])00000000000000([,)])/$1'00010101 000000'$2/g;
652+
s/([,(])(\d{8})(\d{6})([,)])/$1'$2 $3'$4/g;
653+
s/([,(])(\d{4})(\d{2})(\d{2})([,)])/$1'$2-$3-$4 00:00:00'$5/g;
654+
#<Hackzone> ---------------------------------------------------
608655
#</Hackzone> --------------------------------------------------
609656
$dump.=$_;
610657
}
@@ -638,7 +685,7 @@
638685

639686
# setting SERIAL sequence values right
640687
if($primary{$table} ne '') {
641-
print "\nSELECT SETVAL('".$seq{$table}."',(select case when max(\"".$primary{$table}."\")>0 then max(\"".$primary{$table}."\")+1 else 1 end from \"$table\"));";
688+
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));";
642689
}
643690
}
644691

@@ -647,7 +694,7 @@
647694

648695
open(MAKE,">Makefile");
649696
print MAKE "#
650-
# My2Pg \$Revision: 1.4 $ \translated dump
697+
# My2Pg \$Revision: 1.5 $ \translated dump
651698
# Makefile
652699
#
653700
@@ -699,14 +746,15 @@ ()
699746
This program is distributed in the hope that it will be useful,
700747
but WITHOUT ANY WARRANTY; without even the implied warranty of
701748
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
702-
GNU General Public License for more details.
749+
code source for license details.
703750
704751
SYNTAX:
705-
my2pg [-hn]
752+
my2pg [-hnd]
706753
707754
OPTIONS:
708755
h - this help
709756
n - convert *CHAR NOT NULL DEFAULT '' types to *CHAR NULL
757+
d - double quotes around table and column names
710758
EOF
711759
;
712760
}
@@ -795,6 +843,10 @@ =head1 COMMAND-LINE OPTIONS
795843
Convert *CHAR DEFAULT '' NOT NULL types to *CHAR NULL.
796844
Postgres can't load empty '' strings in NOT NULL fields.
797845
846+
=item -d
847+
848+
Add double quotes around table and column names
849+
798850
=item -h
799851
800852
Show usage banner.
@@ -851,6 +903,12 @@ =head1 AUTHORS
851903
B<(c) 2000 Maxim V. Rudensky (fonin@ziet.zhitomir.ua)>
852904
B<(c) 2000 Valentine V. Danilchuk (valdan@ziet.zhitomir.ua)>
853905
906+
=head1 CREDITS
907+
908+
Jeff Waugh <jaw@ic.net>
909+
Joakim Lemström <jocke@bytewize.com> || <buddyh19@hotmail.com>
910+
Yunliang Yu <yu@math.duke.edu>
911+
854912
=head1 LICENSE
855913
856914
B<BSD>

0 commit comments

Comments
 (0)