Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3345da5

Browse files
committed
Update my2pg.pl for release.
1 parent83d4a1c commit3345da5

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($oldtablene$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/,/","/gif$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*/","/gif$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/,/","/gif$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 Licenseformore details.
749+
code sourceforlicense 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+
=head1CREDITS
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
=head1LICENSE
855913
856914
B<BSD>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp