Skip to content

Commit 13b63d3

Browse files
bmomjianbmomjian
committed
Update to my2pg version 1.28, add docs, update URL for newest version.
Create diff of custom changes Tom made to the utility for CREATE FUNCTION. This will make moving this utility out of CVS easier.
1 parent 085fa8a commit 13b63d3

File tree

4 files changed

+331
-11
lines changed

4 files changed

+331
-11
lines changed

‎contrib/mysql/README

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,7 @@ Here are two conversion utilities for MySQL dumps. Use the one you prefer.
22

33
The most recent version of my2pg.pl can be obtained from:
44

5-
http://ziet.zhitomir.ua/~fonin/code/
5+
http://www.omnistarinc.com/~fonin/downloads.php#my2pg
66

77
Another tool, mysql2pgsql, can be found at:
88

‎contrib/mysql/my2pg.diff

Lines changed: 112 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,112 @@
1+
*** /laptop/my2pg.pl Mon Apr 19 18:51:44 2004
2+
--- my2pg.pl Mon Apr 19 18:59:09 2004
3+
***************
4+
*** 38,43 ****
5+
--- 38,50 ----
6+
# $My2pg: my2pg.pl,v 1.28 2001/12/06 19:32:20 fonin Exp $
7+
# $Id: my2pg.diff,v 1.1 2004/04/19 23:18:12 momjian Exp $
8+
9+
+ # Custom patch
10+
+ # Revision 1.9 2002/08/22 00:01:39 tgl
11+
+ # Add a bunch of pseudo-types to replace the behavior formerly associated
12+
+ # with OPAQUE, as per recent pghackers discussion. I still want to do some
13+
+ # more work on the 'cstring' pseudo-type, but I'm going to commit the bulk
14+
+ # of the changes now before the tree starts shifting under me ...
15+
+
16+
#
17+
# $Log: my2pg.diff,v $
18+
# Revision 1.1 2004/04/19 23:18:12 momjian
19+
# Update to my2pg version 1.28, add docs, update URL for newest version.
20+
#
21+
# Create diff of custom changes Tom made to the utility for CREATE
22+
# FUNCTION.
23+
#
24+
# This will make moving this utility out of CVS easier.
25+
#
26+
# Revision 1.28 2002/11/30 12:03:48 fonin
27+
***************
28+
*** 332,342 ****
29+
print LIBTYPES "\n * Types for table ".uc($table_name);
30+
print LIBTYPES "\n */\n";
31+
32+
! $types.="\nCREATE FUNCTION $typename"."_in (opaque)
33+
RETURNS $typename
34+
AS '$libtypename'
35+
LANGUAGE 'c'
36+
! WITH (ISCACHABLE);\n";
37+
38+
# creating output function
39+
my $func_out="
40+
--- 339,349 ----
41+
print LIBTYPES "\n * Types for table ".uc($table_name);
42+
print LIBTYPES "\n */\n";
43+
44+
! $types.="\nCREATE FUNCTION $typename"."_in (cstring)
45+
RETURNS $typename
46+
AS '$libtypename'
47+
LANGUAGE 'c'
48+
! WITH (ISSTRICT, ISCACHABLE);\n";
49+
50+
# creating output function
51+
my $func_out="
52+
***************
53+
*** 386,396 ****
54+
return (*a>=*b);
55+
}\n";
56+
57+
! $types.="\nCREATE FUNCTION $typename"."_out (opaque)
58+
! RETURNS opaque
59+
AS '$libtypename'
60+
LANGUAGE 'c'
61+
! WITH (ISCACHABLE);\n";
62+
63+
$types.="\nCREATE TYPE $typename (
64+
internallength = 2,
65+
--- 393,403 ----
66+
return (*a>=*b);
67+
}\n";
68+
69+
! $types.="\nCREATE FUNCTION $typename"."_out ($typename)
70+
! RETURNS cstring
71+
AS '$libtypename'
72+
LANGUAGE 'c'
73+
! WITH (ISSTRICT, ISCACHABLE);\n";
74+
75+
$types.="\nCREATE TYPE $typename (
76+
internallength = 2,
77+
***************
78+
*** 532,538 ****
79+
print LIBTYPES "\n * Types for table ".uc($table_name);
80+
print LIBTYPES "\n */\n";
81+
82+
! $types.="\nCREATE FUNCTION $typename"."_in (opaque)
83+
RETURNS $typename
84+
AS '$libtypename'
85+
LANGUAGE 'c';\n";
86+
--- 539,545 ----
87+
print LIBTYPES "\n * Types for table ".uc($table_name);
88+
print LIBTYPES "\n */\n";
89+
90+
! $types.="\nCREATE FUNCTION $typename"."_in (cstring)
91+
RETURNS $typename
92+
AS '$libtypename'
93+
LANGUAGE 'c';\n";
94+
***************
95+
*** 584,591 ****
96+
97+
\n";
98+
99+
! $types.="\nCREATE FUNCTION $typename"."_out (opaque)
100+
! RETURNS opaque
101+
AS '$libtypename'
102+
LANGUAGE 'c';\n";
103+
104+
--- 591,598 ----
105+
106+
\n";
107+
108+
! $types.="\nCREATE FUNCTION $typename"."_out ($typename)
109+
! RETURNS cstring
110+
AS '$libtypename'
111+
LANGUAGE 'c';\n";
112+

‎contrib/mysql/my2pg.html

Lines changed: 193 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,193 @@
1+
<HTML>
2+
<HEAD>
3+
<TITLE>my2pg - MySQL -> PostgreSQL dump conversion utility.</TITLE>
4+
<LINK REV="made" HREF="mailto:bhcompile@daffy.perf.redhat.com">
5+
</HEAD>
6+
7+
<BODY>
8+
9+
<A NAME="__index__"></A>
10+
<!-- INDEX BEGIN -->
11+
12+
<UL>
13+
14+
<LI><A HREF="#name">NAME</A></LI>
15+
<LI><A HREF="#syntax">SYNTAX</A></LI>
16+
<LI><A HREF="#overview">OVERVIEW</A></LI>
17+
<LI><A HREF="#commandline options">COMMAND-LINE OPTIONS</A></LI>
18+
<LI><A HREF="#side effects">SIDE EFFECTS</A></LI>
19+
<LI><A HREF="#bugs">BUGS</A></LI>
20+
<LI><A HREF="#authors">AUTHORS</A></LI>
21+
<LI><A HREF="#credits">CREDITS</A></LI>
22+
<LI><A HREF="#license">LICENSE</A></LI>
23+
</UL>
24+
<!-- INDEX END -->
25+
26+
<HR>
27+
<P>
28+
<H1><A NAME="name">NAME</A></H1>
29+
<P>my2pg - MySQL -&gt; PostgreSQL dump conversion utility.</P>
30+
<P>
31+
<HR>
32+
<H1><A NAME="syntax">SYNTAX</A></H1>
33+
<PRE>
34+
mysqldump db | ./my2pg.pl [-nds] &gt; pgsqldump.sql
35+
vi libtypes.c
36+
make
37+
psql database &lt; pgsqldump.txt
38+
where</PRE>
39+
<DL>
40+
<DT><STRONG><A NAME="item_pgsqldump%2Esql"><EM>pgsqldump.sql</EM></A></STRONG><BR>
41+
<DD>
42+
- file suitable for loading into PostgreSQL.
43+
<P></P>
44+
<DT><STRONG><A NAME="item_libtypes%2Ec"><EM>libtypes.c</EM></A></STRONG><BR>
45+
<DD>
46+
- C source for emulated MySQL types (ENUM, SET) generated by <STRONG>my2pg</STRONG>
47+
<P></P></DL>
48+
<P>
49+
<HR>
50+
<H1><A NAME="overview">OVERVIEW</A></H1>
51+
<P><STRONG>my2pg</STRONG> utility attempts to convert MySQL database dump to Postgres's one.
52+
<STRONG>my2pg</STRONG> performs such conversions:</P>
53+
<UL>
54+
<LI><STRONG><A NAME="item_Type_conversion%2E">Type conversion.</A></STRONG><BR>
55+
56+
It tries to find proper Postgres
57+
type for each column.
58+
Unknown types are silently pushing to output dump;
59+
ENUM and SET types implemented via user types
60+
(C source for such types can be found in
61+
<STRONG>libtypes.c</STRONG> file);
62+
<P></P>
63+
<LI><STRONG><A NAME="item_Encloses_identifiers_into_double_quotes%2E">Encloses identifiers into double quotes.</A></STRONG><BR>
64+
65+
All column and table
66+
names should be enclosed to double-quotes to prevent
67+
conflict with reserved SQL keywords;
68+
<P></P>
69+
<LI><STRONG><A NAME="item_Converting">Converting</A></STRONG><BR>
70+
71+
AUTO_INCREMENT fields to SERIAL. Actually, creating the sequence and
72+
setting default value to nextval('seq'), well, you know :)
73+
<P></P>
74+
<LI><STRONG>Converting</STRONG><BR>
75+
76+
<CODE>KEY(field)</CODE> to CREATE INDEX i_field on table (field);
77+
<P></P>
78+
<LI><STRONG><A NAME="item_The_same">The same</A></STRONG><BR>
79+
80+
for UNIQUE keys;
81+
<P></P>
82+
<LI><STRONG><A NAME="item_Indices">Indices</A></STRONG><BR>
83+
84+
are creating AFTER rows insertion (to speed up the load);
85+
<P></P>
86+
<LI><STRONG><A NAME="item_Translates_%27%23%27">Translates '#'</A></STRONG><BR>
87+
88+
MySQL comments to ANSI SQL '--'
89+
<P></P></UL>
90+
<P>It encloses dump in transaction block to prevent single errors
91+
during data load.</P>
92+
<P>
93+
<HR>
94+
<H1><A NAME="commandline options">COMMAND-LINE OPTIONS</A></H1>
95+
<P>My2pg takes the following command-line options:</P>
96+
<DL>
97+
<DT><STRONG><A NAME="item_%2Dn">-n</A></STRONG><BR>
98+
<DD>
99+
Convert *CHAR DEFAULT '' NOT NULL types to *CHAR NULL.
100+
Postgres can't load empty '' strings in NOT NULL fields.
101+
<P></P>
102+
<DT><STRONG><A NAME="item_%2Dd">-d</A></STRONG><BR>
103+
<DD>
104+
Add double quotes around table and column names
105+
<P></P>
106+
<DT><STRONG><A NAME="item_%2Dh">-h</A></STRONG><BR>
107+
<DD>
108+
Show usage banner.
109+
<P></P>
110+
<DT><STRONG><A NAME="item_%2Ds">-s</A></STRONG><BR>
111+
<DD>
112+
Do not attempt to convert data. Currently my2pg only tries to convert
113+
date and time data.
114+
<P></P></DL>
115+
<P>
116+
<HR>
117+
<H1><A NAME="side effects">SIDE EFFECTS</A></H1>
118+
<UL>
119+
<LI><STRONG><A NAME="item_creates">creates</A></STRONG><BR>
120+
121+
file <STRONG>libtypes.c</STRONG> in current directory
122+
overwriting existed file without any checks;
123+
<P></P>
124+
<LI><STRONG><A NAME="item_the_same">the same</A></STRONG><BR>
125+
126+
for Makefile.
127+
<P></P></UL>
128+
<P>
129+
<HR>
130+
<H1><A NAME="bugs">BUGS</A></H1>
131+
<P>Known bugs are:</P>
132+
<UL>
133+
<LI><STRONG><A NAME="item_Possible_problems_with_the_timestamp_data%2E">Possible problems with the timestamp data.</A></STRONG><BR>
134+
135+
PostgreSQL does not accept incorrect date/time values like <STRONG>2002-00-15</STRONG>,
136+
while MySQL does not care about that. Currently my2pg cannot handle this
137+
issue. You should care yourself to convert such a data.
138+
<P></P>
139+
<LI><STRONG><A NAME="item_Use_%2Ds_option_if_your_numeric_data_are_broken_du">Use -s option if your numeric data are broken during conversion.</A></STRONG><BR>
140+
141+
My2pg attempts to convert MySQL timestamps of the form <STRONG>yyyymmdd</STRONG> to
142+
<STRONG>yyyy-mm-dd</STRONG> and <STRONG>yyyymmddhhmmss</STRONG> to <STRONG>yyyy-mm-dd hh:mm:ss</STRONG>. It performs
143+
some heuristic checks to ensure that the month,day,hour,minutes and seconds have
144+
values from the correct range (0..12, 0..31, 0..23, 0..59, 0..59 respectively).
145+
It is still possible that your numeric values that satisfy these conditions
146+
will get broken.
147+
<P></P>
148+
<LI><STRONG><A NAME="item_Possible_problems_with_enclosing_identifiers_in_do">Possible problems with enclosing identifiers in double quotes.</A></STRONG><BR>
149+
150+
All identifiers such as table and column names should be enclosed in double
151+
quotes. Program can't handle upper-case identifiers,
152+
like DBA. Lower-case identifiers are OK.
153+
<P></P>
154+
<LI><STRONG><A NAME="item_SET_type_emulation_is_not_full%2E_LIKE_operation_o">SET type emulation is not full. LIKE operation on</A></STRONG><BR>
155+
156+
SETs, raw integer input values should be implemented
157+
<P></P>
158+
<LI><STRONG><A NAME="item_Makefile"><STRONG>Makefile</STRONG></A></STRONG><BR>
159+
160+
generated during output is
161+
platform-dependent and surely works only on
162+
Linux/gcc (FreeBSD/gcc probably works as well - not tested)
163+
<P></P>
164+
<LI><STRONG><A NAME="item_Generated_libtypes%2Ec_contain_line">Generated <STRONG>libtypes.c</STRONG> contain line</A></STRONG><BR>
165+
166+
<PRE>
167+
#include &lt;postgres.h&gt;</PRE>
168+
<P>This file may be located not in standard compiler
169+
include path, you need to check it before compiling.</P>
170+
</UL>
171+
<P>
172+
<HR>
173+
<H1><A NAME="authors">AUTHORS</A></H1>
174+
<P><STRONG>(c) 2000-2002 Maxim V. Rudensky (<A HREF="mailto:fonin@omnistaronline.com">fonin@ziet.zhitomir.ua</A>)</STRONG> (developer, maintainer)</P>
175+
<P><STRONG>(c) 2000 Valentine V. Danilchuk (<A HREF="mailto:valdan@ziet.zhitomir.ua">valdan@ziet.zhitomir.ua</A>)</STRONG> (original script)</P>
176+
<P>
177+
<HR>
178+
<H1><A NAME="credits">CREDITS</A></H1>
179+
<P>Great thanks to all those people who provided feedback and make development
180+
of this tool easier.</P>
181+
<P>Jeff Waugh &lt;<A HREF="mailto:jaw@ic.net">jaw@ic.net</A>&gt;</P>
182+
<P>Joakim Lemström &lt;<A HREF="mailto:jocke@bytewize.com">jocke@bytewize.com</A>&gt; || &lt;<A HREF="mailto:buddyh19@hotmail.com">buddyh19@hotmail.com</A>&gt;</P>
183+
<P>Yunliang Yu &lt;<A HREF="mailto:yu@math.duke.edu">yu@math.duke.edu</A>&gt;</P>
184+
<P>Brad Hilton &lt;<A HREF="mailto:bhilton@vpop.net">bhilton@vpop.net</A>&gt;</P>
185+
<P>If you are not listed here please write to me.</P>
186+
<P>
187+
<HR>
188+
<H1><A NAME="license">LICENSE</A></H1>
189+
<P><STRONG>BSD</STRONG></P>
190+
191+
</BODY>
192+
193+
</HTML>

0 commit comments

Comments
 (0)