Re: [GENERAL] MySQL to PostgreSQL, was ENUM type

On Wed, Jul 27, 2005 at 09:16:04PM -0700, Chris Travers wrote:
> Christopher Kings-Lynne wrote:
>
> >>So far, the checklist I can see includes:
> >>* Maintaining conversion scripts
> >
> >
> >What I think we need is a C program that dumps directly from MySQL
> >into PostgreSQL sql.
> >
> >ie. Take the mysqldump source code and just modify its output.
> >
> >Will inherit the MySQL license though :(
> >
>
> It then occurred to me that a better answer would be something like a
> DBI perl script that has modules for storing important queries for
> various SQL database servers, and outputting the format in PostgreSQL
> syntax. That way one can have a general conversion toolkit that can be
> easily expanded and could also be used in porting Oracle, MS SQL,
> SQL-Lite, and other databases to PostgreSQL. In this way, you could
> also avoid the MySQL license even by MySQL AB's weird interpretation of
> derivative works.
>
> This way one could also have fairly smart logic in the conversion as
> well. Gee, your max unsigned bigint is over our bigint limit, lets use
> numeric instead or hmm... you have an ENUM type here. Lets use a
> VARCHAR() with a constraint for now. This logic could be easily tweeked
> by a decent programmer. I think that such a program should be somewhat
> trivial to write for at least tables and data definitions. Triggers,
> functions, etc. would be more difficult. But I think it is reasonable
> to expect that functions might have to be manually ported in most
> circumstances.

The only downside I can think of to such an approach is that it will be
much slower than directly reading a dump file of some kind. But, on the
up-side, it's also possible that it could allow for minimal downtime
migraitons to take place in the future (of course adding that support
would take a lot of work, but theoretically it's possible).
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"