FILEMAKER TO MYSQL STUFF

While I've been installing a dual boot operating system with Linux and without windows on one of our workstations I've been checking out a method for migrating metadata and data from FileMaker to MySQL. There usually is a much easier way to do this, and after finishing the documentation on this I found an opensource version on the net... Not Really, but you can have go if you don't believe me, go on, have a snoop.

There is no way there is a simple metamorphosing solution for an application you've developed in one that you want to migrate to the other. It all works differently. They have different bloodtypes FileMaker and MySQL, one wriggles and the other squirms.

I guess the following text is one way to migrate a FileMaker file/table to a MySQL database. Before I get into that a few observations on the differences between the two applications. I'm saying that because there are a lot of the calculation fields that you create in FileMaker to manipulate data which are permanently on tap, you develop them prior to formatting them for the output you want and then they stay as part of the database.

MySQL, on the other hand does a lot of it's data calculations outside the database, using queries to manipulate data. I guess the theory is that you don't store masses of fields which add to the time it takes to render data through recalculating it, either through augmenting the calculation itself or through sorting, searching and scripting. Both FileMaker and MySQL have their pros and cons, so I'm not going into a debate about which is best, leave that to the time-wasters (Who are they anyway?).

The things is that all the FileMaker databases we have have been developing have been developed on MS operating systems. One of the reasons for conversion is our migration to Linux - not supported by FileMaker of course. There are lots of other reasons for doing this as well - but that's another story.

First big mistake I made (which I would be very helpful for any pointers with) is that you don't seem to be able to copy MySQL databases from Windows to Linux - they end up being read-only on the Linux box. I've had a go at changing the file permissions after pasting databases to Linux via a samba share but have had no success. So if you're going to migrate between the operating systems, you don't want to develop your MySQL database in Windows, thinking that you'll be able to gaily frolic over with a pasting of it to Linux because you won't be able to. You'll be in schtuck if you do. Of course you can go vice-versa easey peasey - windows to Linux no probs with MySQL, just copypaste across no probs (Why? - I would be very helpful for any pointers with this one too).

So the only solution I've come up with is, after formatting data via calculations in the FileMaker database, export the data to a tab file in windows and rename it as a .SQL file adding and adding an insert statement to it. Then you can import to the Linux version of MySQL via PHPMySQLAdmin the after copypasting the SQL file to your MySQL system on Linux.

I spent a fair whack of time developing calculations for creation and modification dates and times in the FileMaker database so that if you want to you can copy them into another. They were a bit spaghetti-like to get to work and its a pain in the butt configuring a file with the calculations but you can just copypaste them across once they're developed.

I've had a look at using the ODBC and in the long run it would be pretty much the same. You still have to copy the database structure, You still have to create all the fields to the datatypes that MySQL has and will let you use, you still have to copy the identified fields that you can migrate across between the two applications and you still have to flood the MySQL database with data. Checkout this for an ODBC methodology, it looks pretty full-on.

Oh by the way, I'm using PHPMySQLAdmin through a Xampp setup, to avoid using the MySQL command line.

Migrating the Table Structure:
1) Assess what fields you can migrate and what you can't.
2) Open Define Fields and copy and paste the fields your going to migrate into a text file to create a schema for what you are going to create.
3) Open up PHPMySQLAdmin and create your table or you could just go for the create table statement - haven't tested that one out.
4) Insert some dummy data into the table using the PHPMySQLAdmin insert interface and navigate to the SQL page to copy and paste the INSERT statement header up to and including the word "Values" into a text file.
5) Bring this to the FileMaker database and keep it somewhere as you develop the calculation for exporting the data. You'll need to make sure that the fields are sequenced in the same order that is in the INSERT header.
5) Create a SQL development layout in the FMP file
6) Open up Define Fields once again and create the fields that are contained in this files Define Fields from the heading SQL line elements down. You'll have to open up and copy and paste the calculations in as well - of course. If you want a timestamped modification and creation date that is formatted for MySQL you will have to include those calculations as well

Migrating the Data:
1) Re-gig the calculation field SQL_line or SQL_line... To include the fields that you want to import remembering that the fields have to be in the sequential order that they are in in the INSERT Header
2) Export the SQL_line field to a tab separated text file and rename the extension .SQL.
3) Open the file up in a text editor and copy and paste your INSERT statement into the top of it. Don't forget to replace the last comma with a semicolon. Save your file again.
4) Truck on over to PHPMySQLAdmin and import the file. The means to do this is on the structure page of PHPMySQLAdmin. Hopefully your import is free of bugs. In my case this is most times very unlikely.

Over and out.