mysql to pgsql

Re: mysql to pgsql

am 28.08.2003 12:02:25 von Gerd Terlutter

This is a multi-part message in MIME format.
--------------080103040605000706080602
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

K. Suresh wrote:
> Hello,
>
> I have a database application with Java and PHP clients. I used mysql
> with 64 tables. Now, we have decided to use pgsql. Please suggest the
> migration procedure.
>
> Thanks.
>
> Suresh
>

Hi all,
for a few weeks i had the same problem. I found the attached script, doc
is online :
http://ns2.ziet.zhitomir.ua/~fonin/projects/my2pg/my2pg_man. html

shortcut :
mysqldump -u username --password=mypasswd databasetodump [tables]
|(pipe) my2pg.pl>dumpfile
dumpfile the script that you can use to create tables in an already
existing PG-dbase. If you need functions in PG (e.g. you need enum in
mysql) you have to read the file as PG-superuser and not as owner of the
db. The my2pg.pl script creates Makefile and three libtypes.*. You need
a specific pg-libary on your system, but i've forgotten the name. If you
convert as superuser, don't forget to give the privs to the user (grant
all on 'tablename' to 'user). do the same for *_seq-tables.
hope it helps
Gerd
--
--------------------------------------------------------
# Gerd Terlutter | Mueller+Blanck Software GmbH #
# gerd@MplusB.de | Gutenbergring 38 #
# gerd.terlutter@web.de | D-22848 Noderstedt #
# tel:0171/6992579 | tel:+49 40 500 171-1 #
# Buero:040/500171-17 | fax:+49 40 500 171-71 #
--------------------------------------------------------

--------------080103040605000706080602
Content-Type: application/x-perl;
name="my2pg.pl"
Content-Disposition: inline;
filename="my2pg.pl"
Content-Transfer-Encoding: quoted-printable
X-MIME-Autoconverted: from 8bit to quoted-printable by compass.MplusB.de id MAA20980

#!/usr/bin/perl

#
# My2Pg: MySQL to PostgreSQL dump conversion utility
#
# (c) 2000,2001 Maxim Rudensky
# (c) 2000 Valentine Danilchuk
# All right reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
# notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
# notice, this list of conditions and the following disclaimer in the
# documentation and/or other materials provided with the distribution.
# 3. All advertising materials mentioning features or use of this software
# must display the following acknowledgement:
# This product includes software developed by the Max Rudensky
# and its contributors.
# 4. Neither the name of the author nor the names of its contributors
# may be used to endorse or promote products derived from this software
# without specific prior written permission.
#=20
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
# ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
# ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
# FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
# DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
# OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
# HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
# LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
# OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
# SUCH DAMAGE.
#
# $My2pg: my2pg.pl,v 1.28 2001/12/06 19:32:20 fonin Exp $
# $Id: my2pg.pl,v 1.1 2003/07/09 18:03:33 gerd Exp $

#
# $Log: my2pg.pl,v $
# Revision 1.1 2003/07/09 18:03:33 gerd
# basic converter files for converting a mysql DB to postgresql. after usag=
e set the grant perms into pgsql.
#
# Revision 1.28 2002/11/30 12:03:48 fonin
# PostgreSQL does not support indexes on the partial length of column,
# e.g.
# CREATE INDEX i_index ON table (column(16));
# will not work. Fixed.
#
# Added command-line option -s that prevents my2pg from attempting convert
# the data (currently only timestamps).
#
# Better timestamps conversion.
#
# Revision 1.27 2002/07/16 14:54:07 fonin
# Bugfix - didn't quote the fields inside PRIMARY KEY with -d option.
# Fix by Milan P. Stanic .
#
# Revision 1.26 2002/07/14 10:30:27 fonin
# Bugfix - MySQL keywords inside data (INSERT INTO sentence) were replaced
# with Postgres keywords and therefore messed up the data.
#
# Revision 1.25 2002/07/05 09:20:25 fonin
# - fixed data that contains two consecutive timestamps - thanks to
# Ben Darnell
# - word 'default' was converted to upper case inside the data - fixed.
# Thanks to Madsen Wikholm
#
# Revision 1.24 2002/04/20 14:15:43 fonin
# Patch by Felipe Nievinski .
# A table I was re-creating had a composite primary key, and I was using
# the -d switch to maintain the table and column names
# adding double quotes around them.
#
# The SQL code generated was something like this:
#
# CREATE TABLE "rinav" (
# "UnidadeAtendimento" INT8 DEFAULT '0' NOT NULL,
# "NumeroRinav" INT8 DEFAULT '0' NOT NULL,
# -- ...
# PRIMARY KEY ("UnidadeAtendimento"," NumeroRinav")
# );
#
# Please note the space inside the second column name string in the PK
# definition. Because of this PostgreSQL was not able to create the table.
#
# FIXED.
#
# Revision 1.23 2002/02/07 22:13:52 fonin
# Bugfix by Hans-Juergen Schoenig : additional space after
# FLOAT8 is required.
#
# Revision 1.22 2001/12/06 19:32:20 fonin
# Patch: On line 594 where you check for UNIQUE, I believe the regex should=
try
# and match 'UNIQUE KEY'. Otherwise it outputs no unique indexes for the
# postgres dump.
# Thanks to Brad Hilton
#
# Revision 1.21 2001/08/25 18:55:28 fonin
# Incorporated changes from Yunliang Yu :
# - By default table & column names are not quoted; use the new
# "-d" option if you want to,
# - Use conditional substitutions to speed up and preserve
# the data integrity.
# Fixes by Max:
# - timestamps conversion fix. Shouldn't break now matching binary data and
# strings.
#
# Revision 1.21 2001/07/23 03:04:39 yu
# Updates & fixes by Yunliang Yu
# . By default table & column names are not quoted; use the new
# "-d" option if you want to,
# . Use conditional substitutions to speed up and preserve
# the data integrity.
#
# Revision 1.20 2001/07/05 12:45:05 fonin
# Timestamp conversion enhancement from Joakim Lemström om>
#
# Revision 1.19 2001/05/07 19:36:38 fonin
# Fixed a bug in quoting PRIMARY KEYs, KEYs and UNIQUE indexes with more t=
han 2 columns. Thanks to Jeff Waugh .
#
# Revision 1.18 2001/03/06 22:25:40 fonin
# Documentation up2dating.
#
# Revision 1.17 2001/03/04 13:01:50 fonin
# Fixes to make work it right with MySQL 3.23 dumps. Tested on mysqldump 8.=
11.
# Also, AUTO_INCREMENT->SERIAL fields no more have DEFAULT and NOT NULL=20
# definitions.
#
# Revision 1.16 2001/02/02 08:15:34 fonin
# Sequences should be created BEFORE creating any objects \nthat depends on=
it.
#
# Revision 1.15 2001/01/30 10:13:36 fonin
# Re-released under BSD-like license.
#
# Revision 1.14 2000/12/18 20:55:13 fonin
# Better -n implementation.
#
# Revision 1.13 2000/12/18 15:26:33 fonin
# Added command-line options. -n forces *CHAR DEFAULT '' NOT NULL to be=20
# converted to *CHAR NULL.
# AUTO_INCREMENT fields converted not in SERIAL but in=20
# INT* NOT NULL DEFAULT nextval('seqname').
# Documentation refreshed.
# Dump enclosed in single transaction from now.
#
# Revision 1.12 2000/12/14 20:57:15 fonin
# Doublequotation bug fixed (in CREATE INDEX ON TABLE (field1,field2))
#
# Revision 1.10 2000/11/27 14:18:22 fonin
# Fixed bug - occasionaly was broken CREATE SEQUENCE generation
#
# Revision 1.8 2000/11/24 15:24:16 fonin
# TIMESTAMP fix: MySQL output YYYYMMDDmmhhss to YYYYMMDD mmhhss
#
# Revision 1.7 2000/11/22 23:04:41 fonin
# TIMESTAMP field fix. Better doublequoting. Splitting output dump
# into 2 transactions - create/load/indexing first, sequence setvals then.=
=20
# Added POD documentation.
#
#

use Getopt::Std;

my %opts; # command line options
my $chareg=3D''; # CHAR conversion regexps
my $dq=3D''; # double quote

# parse command line
getopts('nhds',\%opts);

# output syntax
if($opts{h} ne '') {
usage();
exit;
}

# convert CHAR types from NOT NULL DEFAULT '' to NULL
if($opts{n} ne '') {
$chareg=3D'\s*?(default\s*?\'\')*?\s*?not\s*?null';
}
# want double quotes
if($opts{d} ne '') {
$dq=3D'"';
}

if($opts{s} ne '') {
$safe_data_conv=3D1;
}
else {
$safe_data_conv=3D0;
}

$|=3D1;

print("----------------------------------------------------- -------------");
print("\n-- My2Pg 1.28 translated dump");
print("\n--");
print("\n--------------------------------------------------- ---------------=
");

print("\n\nBEGIN;\n\n\n");

my %index; # contains array of CREATE INDEX for each table
my %seq; # contains CREATE SEQUENCE for each table
my %primary; # contains primary (eg SERIAL) fields for each table
my %identifier; # contains generated by this program identifiers
my $j=3D-1; # current position in $index{table}
my @check; # CHECK constraint for current

# generating full path to libtypes.c
my $libtypesource=3D'libtypes.c';
my $libtypename=3D`pwd`;
chomp($libtypename);
$libtypename.=3D'/libtypes.so';

# push header to libtypes.c
open(LIBTYPES,">$libtypesource");
print LIBTYPES "/******************************************************";
print LIBTYPES "\n * My2Pg 1.27 \translated dump";
print LIBTYPES "\n * User types definitions";
print LIBTYPES "\n ******************************************************/";
print LIBTYPES "\n\n#include \n";
print LIBTYPES "\n#define ADD_COMMA if(strcmp(result,\"\")!=3D0) strcat(res=
ult,\",\")\n";

# reading STDIN...
my $tabledef=3D0; # we are outside a table definition
while (<>) {
if(!$tabledef && /^CREATE TABLE \S+/i){
$tabledef=3D1;
} elsif($tabledef && /^\) type=3D\w*;/i){ # /^\w/i
$tabledef=3D0;
}
=09
# Comments start with -- in SQL
if(/^#/) {# !/insert into.*$.*#.*$/i, in mysqldump output
s/#/--/;
}

if($tabledef) {
# Convert numeric types
s/tinyint$\d+$/INT2/i;
s/smallint$\d+$/INT2/i;
s/mediumint$\d+$/INT4/i;
s/bigint$\d+$/INT8/i;
s/int$\d+$/INT4/i;
s/float($\d+,\d*$)/DECIMAL$1/i;
s/double precision/FLOAT8 /i;
s/([\W])double($\d+,\d*$)/$1DECIMAL$2/i;
s/([\W])double[\W]/$1FLOAT8 /i;
s/([\W])real[\W]/$1FLOAT8 /i;
s/([\W])real($\d+,\d*$)/$1DECIMAL$2/i;

# Convert string types
s/\w*blob$chareg/text/i;
s/mediumtext$chareg/text/i;
s/tinytext$chareg/text/i;
s/\stext\s+not\s+null/ TEXT DEFAULT '' NOT NULL/i;
s/(.*?char$.*?$)$chareg/$1/i;

# Old and New are reserved words in Postgres
s/^(\s+)Old /${1}MyOld /;
s/^(\s+)New /${1}MyNew /;

# Convert DATE types
s/datetime/TIMESTAMP/;
s/timestamp$\d+$/TIMESTAMP/i;
s/ date / DATE /i;
if((/date/ig || /time/ig) && /[,(]\d{4}(\d{2})(\d{2})[,)]/ &&=20
$1>=3D0 && $1<=3D12 && $2>=3D0 && $2<=3D31) {
s/,(\d{4})(\d{2})(\d{2}),/,'$1-$2-$3 00:00:00',/g;
}

# small hack - convert "default" to uppercase, because below we=20
# enclose all lowercase words in double quotes
if(!/^INSERT/) {
s/default/DEFAULT/;
}

# Change all AUTO_INCREMENT fields to SERIAL ones with a pre-defined sequen=
ce
if(/([\w\d]+)\sint.*auto_increment/i) {
$tmpseq=3Dnew_name("$table_name"."_"."$+"."_SEQ",28);
$seq{$table_name}=3D$tmpseq;
$primary{$table_name}=3D$+;
s/(int.*?) .*AUTO_INCREMENT/$1 DEFAULT nextval$'$tmpseq'$/i;
}

# convert UNSIGNED to CHECK constraints
if(/^\s+?([\w\d_]+).*?unsigned/i) {
$check.=3D",\n CHECK ($dq$1$dq>=3D0)";
}
s/unsigned//i;

# Limited ENUM support - little heuristic
s/enum$'N','Y'$/BOOL/i;
s/enum$'Y','N'$/BOOL/i;
# ENUM support
if(/^\s+?([\w\d_]+).*?enum$(.*?)$/i) {
my $enumlist=3D$2;
my @item;
$item[0]=3D'';
while($enumlist=3D~s/'([\d\w_]+)'//i) {
$item[++$#item]=3D$1;
}
# forming identifier name
$typename=3Dnew_name('enum_'.$table_name.'_'.$item[1],28);
# creating input type function
my $func_in=3D"
int2* $typename"."_in (char *str) {
int2* result;

if(str==NULL)
return NULL;

result=3D(int2*)palloc(sizeof(int2));
*result=3D-1;";
for(my $i=3D0;$i<=3D$#item;$i++) {
$func_in.=3D"
if(strcmp(str,\"$item[$i]\")==0) {
*result=3D$i;
}";
}
$func_in.=3D"
if(*result == -1) {
elog(ERROR,\"$typename"."_in: incorrect input value\");
return NULL;
}
return (result);
}\n";
$types.=3D"\n---";
$types.=3D"\n--- Types for table ".uc($table_name);
$types.=3D"\n---\n";
print LIBTYPES "\n/*";
print LIBTYPES "\n * Types for table ".uc($table_name);
print LIBTYPES "\n */\n";

$types.=3D"\nCREATE FUNCTION $typename"."_in (opaque)
RETURNS $typename
AS '$libtypename'
LANGUAGE 'c'
WITH (ISCACHABLE);\n";

# creating output function
my $func_out=3D"
char* $typename"."_out (int2 *outvalue) {
char* result;

if(outvalue==NULL)
return NULL;

result=3D(char*)palloc(10);
switch (*outvalue) {";
for(my $i=3D0;$i<=3D$#item;$i++) {
$func_out.=3D"
case $i:
strcpy(result,\"$item[$i]\");
break;";
}
$func_out.=3D"
default :
elog(ERROR,\"$typename"."_out: incorrect stored value\");
return NULL;
break;
}
return result;
}\n";
$func_out.=3D"\nbool $typename"."_eq(int2* a, int2* b) {
return (*a==*b);
}

bool $typename"."_ne(int2* a, int2* b) {
return (*a!=3D*b);
}

bool $typename"."_lt(int2* a, int2* b) {
return (*a<*b);
}

bool $typename"."_le(int2* a, int2* b) {
return (*a<=3D*b);
}

bool $typename"."_gt(int2* a, int2* b) {
return (*a>*b);
}

bool $typename"."_ge(int2* a, int2* b) {
return (*a>=3D*b);
}\n";

$types.=3D"\nCREATE FUNCTION $typename"."_out (opaque)
RETURNS opaque
AS '$libtypename'
LANGUAGE 'c'
WITH (ISCACHABLE);\n";

$types.=3D"\nCREATE TYPE $typename (
internallength =3D 2,
input =3D $typename\_in,
output =3D $typename\_out
);\n";

$types.=3D"\nCREATE FUNCTION $typename"."_eq ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE FUNCTION $typename"."_lt ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE FUNCTION $typename"."_le ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE FUNCTION $typename"."_gt ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE FUNCTION $typename"."_ge ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE FUNCTION $typename"."_ne ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE OPERATOR < (
leftarg =3D $typename,
rightarg =3D $typename,
-- negator =3D >=3D,
procedure =3D $typename"."_lt
);

CREATE OPERATOR <=3D (
leftarg =3D $typename,
rightarg =3D $typename,
-- negator =3D >,
procedure =3D $typename"."_le
);

CREATE OPERATOR =3D (
leftarg =3D $typename,
rightarg =3D $typename,
commutator =3D =3D,
-- negator =3D <>,
procedure =3D $typename"."_eq
);

CREATE OPERATOR >=3D (
leftarg =3D $typename,
rightarg =3D $typename,
negator =3D <,
procedure =3D $typename"."_ge
);

CREATE OPERATOR > (
leftarg =3D $typename,
rightarg =3D $typename,
negator =3D <=3D,
procedure =3D $typename"."_gt
);

CREATE OPERATOR <> (
leftarg =3D $typename,
rightarg =3D $typename,
negator =3D =3D,
procedure =3D $typename"."_ne
);\n";

print LIBTYPES $func_in;
print LIBTYPES $func_out;
s/enum$.*?$/$typename/i;
}

# SET support
if(/^\s+?([\w\d_]+).*?set$(.*?)$/i) {
my $setlist=3D$2;
my @item;
$item[0]=3D'';
my $maxlen=3D0; # maximal string length
while($setlist=3D~s/'([\d\w_]+)'//i) {
$item[++$#item]=3D$1;
$maxlen+=3Dlength($item[$#item])+1;
}
$maxlen+=3D1;
my $typesize=3Dint($#item/8);
if($typesize<2) {
$typesize=3D2;
}
$internalsize=3D$typesize;
$typesize=3D'int'.$typesize;
$typename=3Dnew_name('set_'.$table_name.'_'.$item[1],28);
# creating input type function
my $func_in=3D"
$typesize* $typename"."_in (char *str) {
$typesize* result;
char* token;

if(str==NULL)
return NULL;

result=3D($typesize*)palloc(sizeof($typesize));
*result=3D0;
if(strcmp(str,\"\")==0)
return result;
for(token=3Dstrtok(str,\",\");token!=3DNULL;token=3Dstrtok(N ULL,\",\"))=
{";
for(my $i=3D0,my $j=3D1;$i<=3D$#item;$i++,$j*=3D2) {
$func_in.=3D"
if(strcmp(token,\"$item[$i]\")==0) {
*result|=3D$j;
continue;
}";
}
$func_in.=3D"
}

if(*result == 0) {
elog(ERROR,\"$typename"."_in: incorrect input value\");
return NULL;
}
return (result);

}\n";
$types.=3D"\n---";
$types.=3D"\n--- Types for table ".uc($table_name);
$types.=3D"\n---\n";
print LIBTYPES "\n/*";
print LIBTYPES "\n * Types for table ".uc($table_name);
print LIBTYPES "\n */\n";

$types.=3D"\nCREATE FUNCTION $typename"."_in (opaque)
RETURNS $typename
AS '$libtypename'
LANGUAGE 'c';\n";

# creating output function
my $func_out=3D"
char* $typename"."_out ($typesize *outvalue) {
char* result;
int i;

if(outvalue==NULL)
return NULL;

result=3D(char*)palloc($maxlen);
strcpy(result,\"\");
for(i=3D1;i<=3D2 << (sizeof(int2)*8);i*=3D2) {
switch (*outvalue & i) {";
for(my $i=3D0,$j=3D1;$i<=3D$#item;$i++,$j*=3D2) {
$func_out.=3D"
case $j:";
if($item[$i] ne '') {
$func_out.=3D"ADD_COMMA;";
}
$func_out.=3D"strcat(result,\"$item[$i]\");
break;";
}
$func_out.=3D"
default :
break;
}
}

return result;
}\n";
$func_out.=3D"\nbool $typename"."_eq($typesize* a, $typesize* b) {
return (*a==*b);
}

$typesize find_in_set($typesize *a, $typesize *b) {
int i;

for(i=3D1;i<=3Dsizeof($typesize)*8;i*=3D2) {
if(*a & *b) {
return 1;
}
}
return 0;
}

\n";

$types.=3D"\nCREATE FUNCTION $typename"."_out (opaque)
RETURNS opaque
AS '$libtypename'
LANGUAGE 'c';\n";

$types.=3D"\nCREATE TYPE $typename (
internallength =3D $internalsize,
input =3D $typename\_in,
output =3D $typename\_out
);\n";

$types.=3D"\nCREATE FUNCTION $typename"."_eq ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE FUNCTION find_in_set ($typename,$typename)
RETURNS bool
AS '$libtypename'
LANGUAGE 'c';

CREATE OPERATOR =3D (
leftarg =3D $typename,
rightarg =3D $typename,
commutator =3D =3D,
procedure =3D $typename"."_eq
);

CREATE OPERATOR <> (
leftarg =3D $typename,
rightarg =3D $typename,
commutator =3D <>,
negator =3D =3D,
procedure =3D $typename"."_eq
);

\n";

print LIBTYPES $func_in;
print LIBTYPES $func_out;
s/set$.*?$/$typename/i;
}

# Change multy-field keys to multi-field indices
# MySQL Dump usually ends the CREATE TABLE statement like this:
# CREATE TABLE bids (
# ...
# PRIMARY KEY (bids_id),
# KEY offer_id (offer_id,user_id,the_time),
# KEY bid_value (bid_value)
# );
# We want to replace this with smth like
# CREATE TABLE bids (
# ...
# PRIMARY KEY (bids_id),
# );
# CREATE INDEX offer_id ON bids (offer_id,user_id,the_time);
# CREATE INDEX bid_value ON bids (bid_value);
if (s/CREATE TABLE (.*) /CREATE TABLE $dq$1$dq /i) {
if($oldtable ne $table_name) {
$oldtable=3D$table_name;
$j=3D-1;
$check=3D'';

if($seq{$table_name} ne '') {
print "\n\n--";
print "\n-- Sequences for table ".uc($table_name);
print "\n--\n";
print "\nCREATE SEQUENCE ".$seq{$table_name}.";\n\n";
}

print $types;
$types=3D'';
$dump=3D~s/,\n\).*;/\n\);/gmi;
# removing table options after closing bracket:
# ) TYPE=3DISAM PACK_KEYS=3D1;
$dump=3D~s/\n\).*/\n\);/gmi;
print $dump;
$dump=3D'';
}
$table_name=3D$1;
}

# output CHECK constraints instead UNSIGNED modifiers
if(/PRIMARY KEY\s+$(.*)$/i) {
my $tmpfld=3D$1;
$tmpfld=3D~s/,/","/g if $dq;
$tmpfld=3D~s/ //g;
s/PRIMARY KEY\s+($.*$)/PRIMARY KEY $$dq$tmpfld$dq$/i;
s/(PRIMARY KEY $.*$).*/$1$check\n/i;
}

if(/^\s*KEY ([\w\d_]+)\s*$(.*)$.*/i) {
my $tmpfld=3D$2; my $ky=3D$1;
$tmpfld=3D~s/\s*,\s*/","/g if $dq;
$tmpfld=3D~s/($\d+$)//g;
$index{$table_name}[++$j]=3D"CREATE INDEX ${ky}_$table_name\_index ON =
$dq$table_name$dq ($dq$tmpfld$dq);";
}
if(/^\s*UNIQUE.*?([\w\d_]+)\s*$(.*)$.*/i) {
my $tmpfld=3D$2; my $ky=3D$1;
$tmpfld=3D~s/,/","/g if $dq;
$tmpfld=3D~s/($\d+$)//g;
$index{$table_name}[++$j]=3D"CREATE UNIQUE INDEX ${ky}_$table_name\_in=
dex ON $dq$table_name$dq ($dq$tmpfld$dq);";
}
s/^\s*UNIQUE (.+).*($.*$).*\n//i;
s/^\s*KEY (.+).*($.*$).*\n//i;

if($dq && !/^\s*(PRIMARY KEY|UNIQUE |KEY |CREATE TABLE|INSERT INTO|=
\);)/i) {
s/\s([A-Za-z_\d]+)\s/ $dq$+$dq /;
}
} # end of if($tabledef)

s/INSERT INTO\s+?(.*?)\s+?/INSERT INTO $dq$1$dq /i;

# if not defined -s command-line option (safe data conversion),
# attempting to convert timestamp data
if(!$safe_data_conv) {
# Fix timestamps
s/'0000-00-00/'0001-01-01/g;
# may corrupt data !!!
s/([,(])00000000000000(?=3D[,)])/$1'00010101 000000'/g;
if(/[,(]\d{4}(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})[,)]/ &&=20
$1>=3D0 && $1<=3D12 && $2>=3D0 && $2<=3D31 && $3>=3D0 && $3<=3D23 &&
$4>=3D0 && $4<=3D59 && $5>=3D0 && $5<=3D59) {
s/([,(])(\d{8})(\d{6})(?=3D[,)])/$1'$2 $3'/g;
}
if(/[,(]\d{4}(\d{2})(\d{2})[,)]/ &&=20
$2>=3D0 && $2<=3D12 && $3>=3D0 && $3<=3D31) {
s/([,(])(\d{4})(\d{2})(\d{2})(?=3D[,)])/$1'$2-$3-$4 00:00:00'/g;
}
}

$dump.=3D$_;
}

if($seq{$table_name} ne '') {
print "\n\n--";
print "\n-- Sequences for table ".uc($table_name);
print "\n--\n";
print "\nCREATE SEQUENCE ".$seq{$table_name}.";\n\n";
}
print $types;
$dump=3D~s/,\n\).*;/\n\);/gmi;
$dump=3D~s/\n\).*/\n\);/gmi;
print $dump;

# Output indices for tables
while(my($table,$ind)=3Deach(%index)) {
print "\n\n--";
print "\n-- Indexes for table ".uc($table);
print "\n--\n";
for(my $i=3D0;$i<=3D$#{$ind};$i++) {
print "\n$ind->[$i]";
}

}

while(my($table,$s)=3Deach(%seq)) {
print "\n\n--";
print "\n-- Sequences for table ".uc($table);
print "\n--\n";

# setting SERIAL sequence values right
if($primary{$table} ne '') {
print "\nSELECT SETVAL('".$seq{$table}."',(select case when max($dq".$prim=
ary{$table}."$dq)>0 then max($dq".$primary{$table}."$dq)+1 else 1 end from =
$dq$table$dq));";
}
}

print("\n\nCOMMIT;\n");
close(LIBTYPES);

open(MAKE,">Makefile");
print MAKE "#
# My2Pg \$Revision: 1.1 $ \translated dump
# Makefile
#

all: libtypes.so

libtypes.o: libtypes.c
gcc -c -fPIC -g -I/usr/include/postgresql/server -O libtypes.c
libtypes.so: libtypes.o
ld -Bshareable -o libtypes.so libtypes.o";
close(MAKE);

#
# Function generates unique identifier
# Args : template name, max length
# Globals: %identifier
#
sub new_name() {
my $name=3Dlc(shift @_);
my $len=3Dshift @_;

# truncate long names
if(length($name)>$len) {
$name=3D~s/(.{$len}).*/$1/i;
}

# find reserved identifiers
if($identifier{$name}!=3D1) {
$identifier{$name}=3D1;
return $name;
}
else {
for(my $i=3D1,my $tmpname=3D$name.$i;$identifier{$tmpname}!=3D1;) {
$tmpname=3D$name.$i
}
$identifier{$tmpname}=3D1;
return $tmpname;
}

die "Error during unique identifier generation :-(";
}

sub usage() {
print < my2pg - MySQL to PostgreSQL database dump converter

Copyright (c) 2000-2002 Max Rudensky
Copyright (c) 2000 Valentine Danilchuk

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
code source for license details.

SYNTAX:
my2pg [-hnds]

OPTIONS:
h - this help
n - convert *CHAR NOT NULL DEFAULT '' types to *CHAR NULL
d - double quotes around table and column names
s - do not attempt to convert data (timestamps at the moment)
EOF
;
}

=3Dhead1 NAME

my2pg - MySQL -> PostgreSQL dump conversion utility.

=3Dhead1 SYNTAX

mysqldump db | ./my2pg.pl [-nds] > pgsqldump.sql
vi libtypes.c
make
psql database < pgsqldump.txt
where

=3Dover 4

=3Ditem B

- file suitable for loading into PostgreSQL.

=3Ditem B

- C source for emulated MySQL types (ENUM, SET) generated by B

=3Dback

=3Dhead1 OVERVIEW

B utility attempts to convert MySQL database dump to Postgres's one.
B performs such conversions:

=3Dover 4

=3Ditem Type conversion.

It tries to find proper Postgres=20
type for each column.
Unknown types are silently pushing to output dump;
ENUM and SET types implemented via user types=20
(C source for such types can be found in=20
B file);

=3Ditem Identifiers double-quotation.

All column and table=20
names should be enclosed to double-quotes to prevent=20
interferension with reserved words;

=3Ditem Converting

AUTO_INCREMENT fields to SERIAL. Actually, creating the sequence and=20
setting default value to nextval('seq'), well, you know :)

=3Ditem Converting

KEY(field) to CREATE INDEX i_field on table (field);

=3Ditem The same

for UNIQUE keys;

=3Ditem Indices

are creating AFTER rows insertion (to speed up the load);

=3Ditem Translates '#'

MySQL comments to ANSI SQL '--'

=3Dback

It encloses dump in transaction block to prevent single errors=20
during data load.

=3Dhead1 COMMAND-LINE OPTIONS

My2pg takes the following command-line options:

=3Dover 2

=3Ditem -n

Convert *CHAR DEFAULT '' NOT NULL types to *CHAR NULL.
Postgres can't load empty '' strings in NOT NULL fields.

=3Ditem -d

Add double quotes around table and column names

=3Ditem -h

Show usage banner.

=3Ditem -s

Do not attempt to convert data. Currently my2pg only tries to convert
date and time data.

=3Dback

=3Dhead1 SIDE EFFECTS

=3Dover 4

=3Ditem creates

file B in current directory=20
overwriting existed file without any checks;

=3Ditem the same

for Makefile.

=3Dback

=3Dhead1 BUGS

Known bugs are:

=3Dover 4

=3Ditem Poor doublequotation.

All identifiers such as table and column names should be enclosed in double=
=20
quotes. Program can't handle upper-case identifiers,=20
like DBA. Lower-case identifiers are OK.

=3Ditem SET type emulation is not full. LIKE operation on=20

SETs, raw integer input values should be implemented

=3Ditem B generated during output is=20
platform-dependent and surely works only on=20
Linux/gcc (FreeBSD/gcc probably works as well - not tested)

=3Ditem Generated B contain line

#include

This file may be located not in standard compiler=20
include path, you need to check it before compiling.

=3Dback

=3Dhead1 AUTHORS

B<(c) 2000-2002 Maxim V. Rudensky (fonin@ziet.zhitomir.ua)> (maintainer)

B<(c) 2000 Valentine V. Danilchuk (valdan@ziet.zhitomir.ua)> (original scri=
pt)

=3Dhead1 CREDITS

Jeff Waugh
Joakim Lemström ||
Yunliang Yu
Brad Hilton

=3Dhead1 LICENSE

B

=3Dcut

--------------080103040605000706080602
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--------------080103040605000706080602--