diff options
| -rw-r--r-- | contrib/mysql/README | 2 | ||||
| -rw-r--r-- | contrib/mysql/my2pg.diff | 112 | ||||
| -rwxr-xr-x | contrib/mysql/my2pg.html | 193 | ||||
| -rwxr-xr-x | contrib/mysql/my2pg.pl | 35 |
4 files changed, 331 insertions, 11 deletions
diff --git a/contrib/mysql/README b/contrib/mysql/README index 5511df6c67..953e204d42 100644 --- a/contrib/mysql/README +++ b/contrib/mysql/README @@ -2,7 +2,7 @@ Here are two conversion utilities for MySQL dumps. Use the one you prefer. The most recent version of my2pg.pl can be obtained from: - http://ziet.zhitomir.ua/~fonin/code/ + http://www.omnistarinc.com/~fonin/downloads.php#my2pg Another tool, mysql2pgsql, can be found at: diff --git a/contrib/mysql/my2pg.diff b/contrib/mysql/my2pg.diff new file mode 100644 index 0000000000..7c870b3b27 --- /dev/null +++ b/contrib/mysql/my2pg.diff @@ -0,0 +1,112 @@ +*** /laptop/my2pg.pl Mon Apr 19 18:51:44 2004 +--- my2pg.pl Mon Apr 19 18:59:09 2004 +*************** +*** 38,43 **** +--- 38,50 ---- + # $My2pg: my2pg.pl,v 1.28 2001/12/06 19:32:20 fonin Exp $ + # $Id: my2pg.diff,v 1.1 2004/04/19 23:18:12 momjian Exp $ + ++ # Custom patch ++ # Revision 1.9 2002/08/22 00:01:39 tgl ++ # Add a bunch of pseudo-types to replace the behavior formerly associated ++ # with OPAQUE, as per recent pghackers discussion. I still want to do some ++ # more work on the 'cstring' pseudo-type, but I'm going to commit the bulk ++ # of the changes now before the tree starts shifting under me ... ++ + # + # $Log: my2pg.diff,v $ + # Revision 1.1 2004/04/19 23:18:12 momjian + # 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. + # + # Revision 1.28 2002/11/30 12:03:48 fonin +*************** +*** 332,342 **** + print LIBTYPES "\n * Types for table ".uc($table_name); + print LIBTYPES "\n */\n"; + +! $types.="\nCREATE FUNCTION $typename"."_in (opaque) + RETURNS $typename + AS '$libtypename' + LANGUAGE 'c' +! WITH (ISCACHABLE);\n"; + + # creating output function + my $func_out=" +--- 339,349 ---- + print LIBTYPES "\n * Types for table ".uc($table_name); + print LIBTYPES "\n */\n"; + +! $types.="\nCREATE FUNCTION $typename"."_in (cstring) + RETURNS $typename + AS '$libtypename' + LANGUAGE 'c' +! WITH (ISSTRICT, ISCACHABLE);\n"; + + # creating output function + my $func_out=" +*************** +*** 386,396 **** + return (*a>=*b); + }\n"; + +! $types.="\nCREATE FUNCTION $typename"."_out (opaque) +! RETURNS opaque + AS '$libtypename' + LANGUAGE 'c' +! WITH (ISCACHABLE);\n"; + + $types.="\nCREATE TYPE $typename ( + internallength = 2, +--- 393,403 ---- + return (*a>=*b); + }\n"; + +! $types.="\nCREATE FUNCTION $typename"."_out ($typename) +! RETURNS cstring + AS '$libtypename' + LANGUAGE 'c' +! WITH (ISSTRICT, ISCACHABLE);\n"; + + $types.="\nCREATE TYPE $typename ( + internallength = 2, +*************** +*** 532,538 **** + print LIBTYPES "\n * Types for table ".uc($table_name); + print LIBTYPES "\n */\n"; + +! $types.="\nCREATE FUNCTION $typename"."_in (opaque) + RETURNS $typename + AS '$libtypename' + LANGUAGE 'c';\n"; +--- 539,545 ---- + print LIBTYPES "\n * Types for table ".uc($table_name); + print LIBTYPES "\n */\n"; + +! $types.="\nCREATE FUNCTION $typename"."_in (cstring) + RETURNS $typename + AS '$libtypename' + LANGUAGE 'c';\n"; +*************** +*** 584,591 **** + + \n"; + +! $types.="\nCREATE FUNCTION $typename"."_out (opaque) +! RETURNS opaque + AS '$libtypename' + LANGUAGE 'c';\n"; + +--- 591,598 ---- + + \n"; + +! $types.="\nCREATE FUNCTION $typename"."_out ($typename) +! RETURNS cstring + AS '$libtypename' + LANGUAGE 'c';\n"; + diff --git a/contrib/mysql/my2pg.html b/contrib/mysql/my2pg.html new file mode 100755 index 0000000000..13704249aa --- /dev/null +++ b/contrib/mysql/my2pg.html @@ -0,0 +1,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> diff --git a/contrib/mysql/my2pg.pl b/contrib/mysql/my2pg.pl index b24e09d1dc..218a6a65ce 100755 --- a/contrib/mysql/my2pg.pl +++ b/contrib/mysql/my2pg.pl @@ -36,10 +36,25 @@ # SUCH DAMAGE. # # $My2pg: my2pg.pl,v 1.28 2001/12/06 19:32:20 fonin Exp $ -# $Id: my2pg.pl,v 1.12 2004/04/19 23:11:49 momjian Exp $ +# $Id: my2pg.pl,v 1.13 2004/04/19 23:18:12 momjian Exp $ + +# Custom patch +# Revision 1.9 2002/08/22 00:01:39 tgl +# Add a bunch of pseudo-types to replace the behavior formerly associated +# with OPAQUE, as per recent pghackers discussion. I still want to do some +# more work on the 'cstring' pseudo-type, but I'm going to commit the bulk +# of the changes now before the tree starts shifting under me ... # # $Log: my2pg.pl,v $ +# Revision 1.13 2004/04/19 23:18:12 momjian +# 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. +# # Revision 1.12 2004/04/19 23:11:49 momjian # Update to my2pg 1.28, from: # @@ -337,11 +352,11 @@ int2* $typename"."_in (char *str) { print LIBTYPES "\n * Types for table ".uc($table_name); print LIBTYPES "\n */\n"; - $types.="\nCREATE FUNCTION $typename"."_in (opaque) + $types.="\nCREATE FUNCTION $typename"."_in (cstring) RETURNS $typename AS '$libtypename' LANGUAGE 'c' - WITH (ISCACHABLE);\n"; + WITH (ISSTRICT, ISCACHABLE);\n"; # creating output function my $func_out=" @@ -391,11 +406,11 @@ bool $typename"."_ge(int2* a, int2* b) { return (*a>=*b); }\n"; - $types.="\nCREATE FUNCTION $typename"."_out (opaque) - RETURNS opaque + $types.="\nCREATE FUNCTION $typename"."_out ($typename) + RETURNS cstring AS '$libtypename' LANGUAGE 'c' - WITH (ISCACHABLE);\n"; + WITH (ISSTRICT, ISCACHABLE);\n"; $types.="\nCREATE TYPE $typename ( internallength = 2, @@ -537,7 +552,7 @@ $typesize* $typename"."_in (char *str) { print LIBTYPES "\n * Types for table ".uc($table_name); print LIBTYPES "\n */\n"; - $types.="\nCREATE FUNCTION $typename"."_in (opaque) + $types.="\nCREATE FUNCTION $typename"."_in (cstring) RETURNS $typename AS '$libtypename' LANGUAGE 'c';\n"; @@ -589,8 +604,8 @@ $typesize find_in_set($typesize *a, $typesize *b) { \n"; - $types.="\nCREATE FUNCTION $typename"."_out (opaque) - RETURNS opaque + $types.="\nCREATE FUNCTION $typename"."_out ($typename) + RETURNS cstring AS '$libtypename' LANGUAGE 'c';\n"; @@ -762,7 +777,7 @@ close(LIBTYPES); open(MAKE,">Makefile"); print MAKE "# -# My2Pg \$Revision: 1.12 $ \translated dump +# My2Pg \$Revision: 1.13 $ \translated dump # Makefile # |