Chris Browne wrote:
> scrappy@postgresql.org ("Marc G. Fournier") writes:
>> To give someone a running chance at migrating it to PostgreSQL, a
>> 'MySQL compatibility module' would allow them to just plug the
>> existing DB in, and then work at improving sections of the code over
>> time ...
>>
>> Hell, if done well, the module should be able to dump appropriately
>> 'clean' PgSQL schemas ... as in your example elow about the domains ...
>
> You can't have that because you essentially need to throw out four
> aspects of fairly vital "data validation" functionality:
>
> 1. Dates cease to be validatable.
>
> 2. NULL and 0 and '' are all roughly equivalent, even though they
> aren't.
>
> 3. Foreign key constraints have to be ignored.
>
> 4. You have to fabricate a locale offering a case-insensitive sort
> order.
>
> I suppose #4 isn't "vital data validation"...
>
> But after you "gut" the PostgreSQL-based system of those four aspects
> of data integrity, I'm not sure there's any remaining benefit to
> having PostgreSQL in play...
Assuming the objective with a transition would be to improve on things, an alternative
approach could be to offer a three step migration path:
1. A dump/restore utility that dumps a MySQL database and restores it into a PostgreSQL
database. This utility must have plugin capabilities where logic can be added that deals
with cases #1, #2, and #3 above. It might be as simple as just logging incorrect records to
a file and skip them. A pre-defined set of generic plugins could be supplied that did just
that. A user would have the chance to replace them with customized plugins to cover for
special cases in his own app. Perl or PHP would probably be good candidates for plugin language.
2. Provide an add-on to the PostgreSQL parser that would make it accept MySQL syntax. The
database would still run untainted underneath so from this point on, no more invalid dates
or foreign keys can be entered. Some other add-ons are needed as well to cater for some sane
but non-standard MySQL behavior that PostgreSQL is lacking.
3. A good user guide that helps the user to, over time, move away from the non standard
MySQL specific expressions. The objective being to at some point skip the MySQL syntax layer
altogether.
Regards,
Thomas Hallgren
(dead serious this time)