I am working on a project where I have to take an existing Access Database and migrate it to a mysql database.
A little background…
The access database has been around for about ten years or so, who ever designed it did their best I’m sure but the structure wasn’t very well thought out. Also it is quite possible that the requirements have changed over the years. Anyway this legacy design and data comes to me as is.
There are some related tables and some many-many tables and moving forward we want to keep those relationships. For this task I chose my old friend PERL.
I should also mention that not only do I carry forward all of the old data from the access db but I also have to merge that with an existing mysql database that currently powers my clients website, I designed that database and the website is currently using CodeIgniter for data access etc. The admin panel of the original database used a crud tool called CodeExtinguisher, this tool saved me a lot of time but there were a few conventions that had to be honoured.
One of these conventions was that the primary key was always named ‘id’. I have generally followed this convention on many projects and never had a problem. Until now.
Using the PERL module XML::Simple I was able to dump data from the access table and read it in and do what ever I wanted with it. Very slick actually!
The problem came about when I wanted do the same with my mysql database by dumping the data into xml files from phpmyadmin.
XML::Simple was unable to deal with a field named ‘id’, after some hair pulling I changed the ‘id’ to ‘fid’ or whatever. Voila! everything worked again!
Has anyone had other problems with fields named ‘id’ it also seems to bail if there is a field named ‘name’.
I suspect that other html tags would also cause problems like
‘alt’ ‘title’ ‘class’ ‘style’ etc. all perfectly reasonable choices for field names.
Anyone else encountered anything like this?