Hello All,

Just looking for reccommendations on data
conversions....techniques, tools, experiences.

I have a MySQL DB that was poorly designed and
needs to be revamped. It is essential to change the
schema/design, but the information is valuable and I
need to do a conversion.

Any thoughts? Normally, I'd write a Perl Script to read
in a flat file produced from MySQL, but the file would
be too big and drain RAM. Any other ideas? Should
the script just read then write one record at a time?

Thanks in Advance.

Marty

"I need to do a conversion."

To what? If the data is too big to write into flat files, it seems too large to resurrect.

I assume you have explored mysqldump?

http://www.ems-itech.com/mysqlutils#myexport

http://www.icewalkers.com/download/EMS-MySQL-Export/1725/dls/

Well, like I said, I inherited a legacy DB. The design
does not allow a number of features my client
needs......besides being a flat out mess.

No flat files. All MySQL....I know the dump utility. I'm
thinking that since the DB is all text that I would
dump out all the tables into an XML file. Then have a
Perl Script read the file, apply rules and update a new
DB schema. the challenge is that a new database may
incorporate 6 or 7 tables while the old one was 2
tables......with no relationships.

Should I be worrying about Perl having enough
memory to read the entire XML file? Or, should I
break it into peices? any reccommendations?

Regards,
Marty

Perl can probably handle itself. The script length itself is a determining factor of performance, but this will take a while regardless.

I do not know the size of your main memory or database, so you have to make the call. Generally, breaking things into pieces is a good idea, though sometimes a large inconvenience. Since this whole project sounds like one big incovenience, I would say go ahead.

Perhaps you might be enlightened by a reading of "Data Mungling with Perl". It is all about converting data with Perl. XML is not covered in incredible depth, though.

http://www.amazon.com/exec/obidos/tg/detail/-/1930110006/ref%3Dpd%5Fsl%5Faw%5Falx-jeb-6-1%5Fbook%5F396565%5F1/104-1386894-4389563

You keep talking about reading the entire file into memory. Is there a reason to do that???

Normally you read one read, process it and write it. If you have only two tables in the current database, it would seem likely that the most you'd need to do is read one record from each table and then write the appropriate data to the the 6 or 7 new tables.

In 20 years of programming, I don't remember ever needing to read an entire dataset into memory at the same time.

Thank you for the comments. I'm realizing now that the XML dump file won't be that big so memory is not a concern in this case. I'll let you all know how it goes...