|
 |
 |
|
|
|
||
|
|
|
|
||||||||||||||||||||||||
 |
 |
 |
5/15/2004
by David Simpson
Summary:
FileMaker is unmatched in usability thus allowing even novices to quickly create useful database applications for themselves and other users. FileMaker solutions are often developed by individuals and departments who are working independently from a corporate IT department due to cost factors or reduced IT staffing. However as the number of users grows, it may be necessary to migrate a FileMaker database solution to MySQL. MySQL offers inexpensive hosting options and greater salability which is comparable to an Oracle database (as shown in the well known eWeek benchmarks).
This article covers the procedures involved with migrating a FileMaker 6 database to MySQL running on any platform using FmPro Migrator for Windows. Additional info is included regarding changes to this procedure which are required to convert FileMaker 7 to MySQL.
Why Migrate From FileMaker to MySQL?
MySQL is the most popular open-source database available today with an estimated user base exceeding 5 million installations. This popularity has led to wide availability of MySQL database servers within businesses and by ISPs. Most ISPs offer economical MySQL database hosting with their web hosting plans, thus making MySQL a popular choice for web developers. FileMaker database hosting is generally available at a premium price from a limited number of web hosting providers.
MySQL also offers better salability and features such as transactions and database replication. FileMaker Pro Unlimited or Server editions are generally limited to 250 (or fewer) simultaneous users, while MySQL can be expected to serve thousands of simultaneous users. In fact, a Ziff-Davis Media, Inc eWeek benchmark test has shown MySQL to have performance comparable to Oracle 9i.
High availability installations which include database replication and failover capability can be crucial for business critical database installations. FileMaker does not include any type of built-in replication functionality for high availability installations. MySQL does offer a built-in active/passive replication feature which is similar to an Oracle standby database configuration.
The FileMaker database has generally been known as a desktop oriented database with limited connectivity to the database from other applications. FileMaker ODBC connectivity is limited depending upon the platform upon which the FileMaker database is installed. With FileMaker Pro 5 - 6.0, a limited number of users were able to connect with the database through an ODBC/JDBC connection, as long as the connection was made from a Windows computer. FileMaker Pro 7 now limits database connectivity to the Windows host running the database, with no ODBC connectivity available from MacOS X clients. Full ODBC connectivity from Windows computers is reserved for the FileMaker 7 Advanced Server product. MySQL offers a variety of connectivity options for all platforms which can host a MySQL database. Applications can connect with a MySQL database through ODBC, Perl, PHP and Java. There are also no fixed limits to the number of hosts/users which can connect to the database.
Technical Challenges with Migration
Some of the technical challenges with manually migrating FileMaker databases include changing database and column names, dealing with large numbers of FileMaker fields, migrating text fields greater than 255 characters, and migrating images from FileMaker. The migration process implemented by FmPro Migrator involves the use of Perl programs to migrate the actual data via an ODBC connection between the FileMaker and MySQL databases.
• FileMaker Database Name Issues - A FileMaker database file name can contain characters (i.e. spaces and special characters) which can't be used as a MySQL table name. The first task is to manually change the database name to remove any special characters from the filename. FileMaker will give a warning message if related files can't be found, but this won't affect the migration process because the contents of each FileMaker file get transferred individually to the MySQL database.
• FileMaker Field Name Issues - FileMaker field names may also contain characters which would be illegal for an MySQL database column name. Fortunately FmPro Migrator handles this conversion process automatically when it generates migration scripts. But you do have to watch out for situations in which duplicate column names may be created. FmPro Migrator removes special characters from field names and changes spaces into underscore characters. MySQL column names also get truncated to the MySQL mandated limit of 64 characters. When migrating a FileMaker database containing hundreds or even thousands of fields, it is generally more efficient to create the first pass of migration scripts and then try to create a table with the resulting table creation code. If there are duplicate column names, the MySQL will provide an error message so that changes can be made within the original FileMaker database. The scripts can then be regenerated by FmPro Migrator to reflect these changes within FileMaker.
• Too Many FileMaker Fields - FileMaker versions prior to FileMaker 7 do not include a built-in mechanism to keep track of variables while running a script or calculation. Therefore many FileMaker databases contain dozens of global fields used to store this type of information. FileMaker summary and calculation fields are also handled differently within a database like MySQL. Calculation features during record insertion are usually handled via the software which is providing the interface to the database. Future versions of MySQL will offer stored procedures and triggers in order to provide this functionality. The problem with having so many fields is that you can exceed the maximum number of columns which can be created within the MySQL table (roughly 255 - 1500 columns depending upon column size). It is generally possible to remove all global fields, unstored calculation fields and summary fields from the FileMaker database prior to starting the migration process. Removing or at least disabling unstored FileMaker calculation fields and summary fields is also advisable in order to prevent FileMaker from dropping the ODBC connection while waiting for these calculations to be completed. FileMaker will usually issue a "fetch forward" error when this occurs.
• Migrating FileMaker Large Text Fields - FileMaker 6 can store as many as 64,000 characters within each text field. However the MySQL VARCHAR column only stores up to 255 characters. Fields containing more than 255 characters are considered to be Large Text fields as far as FmPro Migrator is concerned. Fields which are identified as Large Text fields are migrated by FmPro Migrator as MySQL TEXT columns which can store up to 64K of data per record. FileMaker 7 text fields can contain up to 4Gb of text instead of the previous 64K limit, so this column type is automatically changed from VARCHAR(255) to LONGTEXT if this much data needs to be migrated. The scripts generated by FmPro Migrator utilize a Perl DBI/DBD:ODBC connection between the FileMaker and MySQL databases in order to make it easier to migrate large amounts of text data.
• Migrating FileMaker Repeating Fields - There is no data type within a MySQL database which is exactly equivalent to the Repeating Fields feature within FileMaker. Repeating fields within FileMaker will only be completely transferred into MySQL if the fields are text fields. FileMaker numeric fields which contain repeating values should be converted to text fields within FileMaker in order to transfer each of the repeating values. Otherwise, MySQL will only recognize the first value of a numeric field. Each repeating field entry is separated by an ASCII (29) character which is used by FileMaker Pro to separate values within repeating fields. For full support of this functionality, redesign of the FileMaker Pro database structure should be considered. This type of feature should be implemented via separate rows of data within a MySQL database with the child records having a common foreign key value pointing back to the primary key of the parent record.
• Migrating FileMaker Container Fields - FileMaker Pro uses container fields for storing pictures, sound and QuickTime video. Data located within FileMaker Pro container fields is transferred into MySQL LONGBLOB type columns. There are some limitations which have been discovered while retrieving container field data from FileMaker Pro. I have only been able to determine the storage format for FileMaker JPEG images as the internal format is not well documented. Therefore only JPEG container field data is supported by the migration scripts. All container fields include the text "HTTP/1.0" prefixed to the data. This header information is removed from the data, with the remaining JPEG data being transferred into the MySQL database table. Other types of container field data are also prefixed with the "HTTP/1.0" file header information which is followed by a JPEG icon representing the type of data contained within the field. So if sound information is stored within a container field, you will end up getting a JPEG image of a sound icon copied into the MySQL database. FileMaker appears to be unique in handling information storage in this manner. All other databases I have worked with simply store the exact binary information which you entered into the field. This feature of FileMaker appears to be related to the tight integration of the user interface with the database engine technology. There appears to be a desire by the FileMaker developers to always show some type of image representing the type of info within the field, even if the type of data does not lend itself to a visual display.
• Migrating FileMaker Calculations - FileMaker field calculations and calculated summary values need to be re-engineered, usually as application specific code by the application which will be providing the graphical interface to the MySQL database. There are too many differences between how FileMaker handles these tasks to make it practical to automate the migration of calculation formulas. The Perl CGI scripts generated by FmPro Migrator can be generated to include default and calculated values in order to provide this functionality.
• Migrating FileMaker Scripts - The functionality incorporated within FileMaker scripts may be called by a remotely triggered script which could be scheduled via a CRON job on a UNIX/Linux/MacOS X server or a Scheduled Task on Windows.
How FmPro Migrator Works
The remainder of this article has been replaced with updated
info as a PDF file:
How
to migrate FileMaker Pro to MySQL (MacOS X, Windows) (6Mb pdf)
PHP Form Conversion with FmPro Migrator Platinum Edition
|
||
|
|
|
|
||
