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