|
 |
 |
|
|
|
||
|
|
|
|
||||||||||||||||||||||||
 |
 |
 |
Summary
This article covers the procedures involved with migrating a FileMaker
Pro database to a MySQL database running on any platform using the FmPro
Migrator utility on MacOS X.
Note: The info in this article is for an older version of FmPro Migrator. The newest illustrated PDF manuals are located on the support web page.
Also, this article doesn't cover the process of migrating or converting layouts into functional PHP web applications, which is covered by a new web page and PDF manual.
Why Use FileMaker Pro?
FileMaker Pro offers unrivaled ease of use for the implementation of workgroup-sized relational databases. Even an inexperienced user can create their first database within a few minutes. The FileMaker Pro user interface is very intuitive, but still offers in-depth features such as scripting and complex field calculations for more experienced users. In a FileMaker Pro database, the display of information can be customized by the creation of multiple layouts. In other databases like Oracle or SQL Server this functionality is implemented by creating a view within the database.
Why Migrate to MySQL?
MySQL is the most popular open-source database available today with an estimated user base exceeding 4 million. 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. MySQL also offers better scalability 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.
Overview of Migration Tasks
For the purposes of this article, we will assume that the MySQL database software has been installed on your platform of choice and that the default MySQL database root account password has been changed. There are many helpful articles and instructions readily available concerning the installation of MySQL on a variety of platforms.
The migration tasks required for a FileMaker to MySQL
migration include:
• creating a list of fields and field types available within the
FileMaker Pro database
• creating a MySQL database which will contain new database tables
(unless a database has already been created). Note: Do not use
the database created as part of the MySQL installation process, which
is named “mysql”. You should create a new database to store
your new database tables.
• creating a new MySQL database user account which will be used
to log in and use the new table.
• creating the new MySQL database table to match the structure
of the FileMaker database.
• transferring the data from the FileMaker Pro database into MySQL.
How FmPro Migrator Works
FmPro Migrator uses Apple Events via AppleScript to query the FileMaker Pro application for information about each of the database files which are currently open. The information gathered from each database is summarized within a report file which can be used by the developer. This information is also used to generate Perl and SQL scripts used for creating database tables and moving the data from FileMaker Pro to the destination database. (See Figure 1)
Figure 1 - Using Apple Events to Obtain FileMaker Pro Database Structure
After creating the MySQL database user account and MySQL database table, the FmPro Migrator generated Perl program is used to transfer text and image data from FileMaker Pro to the new MySQL database table. A Perl DBD::ODBC connection is made to the FileMaker Pro database running on MacOS X, MacOS 9 or Windows. The destination MySQL database may be located locally on any OS platform or at a remote location such as an ISP. (See Figure 2)
Figure 2 - Using a Perl Program to Transfer Data to MySQL
Though there is flexibility concerning the servers which may be used to host the FileMaker Pro and MySQL databases, the Perl program must be run on a Windows server in order to utilize FileMaker Pro ODBC drivers. At the present time there is no FileMaker ODBC driver available for MacOS X, and the FileMaker Pro ODBC driver used on the MacOS 9 architecture cannot be used with the Perl DBD::ODBC module.
Preparation Steps
The first preparation step for the migration process is to rename the FileMaker Pro database to insure that there are no spaces or special characters in the name of the database file. This step is required in order to insure that the FileMaker Pro database name will be usable within a SQL query passed thru the ODBC connection to the FileMaker Pro database. Therefore a database named “Contact Management.fp5” would be renamed as “Contact_Management.fp5”. FmPro Migrator will then construct a database table name based upon this filename which will result in the name “contact_management”.
The second preparation step is to insure that the Local and Remote data access companion plug-ins are enabled within FileMaker Pro. These plug-ins are enabled via the Application Preferences dialog (within the FileMaker Pro/Preferences/Application... menu in MacOS X). (See Figure 3)
Figure 3 - Enabling the FileMaker Pro Local and Remote Data Access Companions
Once these plug-ins have been enabled for the application, the use of these plug-ins needs to be enabled for each individual database file which will be migrated. Enabling this functionality configures FileMaker Pro to utilize its internal built-in ODBC driver in order to accept and process SQL commands via an ODBC connection. (See Figure 4)
Figure 4 - Enabling the FileMaker Pro Database Sharing with the Data Access Companions
The FileMaker Pro built-in ODBC driver can only be utilized
by the FileMaker Pro application to process ODBC connections. This ODBC
driver cannot be utilized by Perl DBD::ODBC software (or any other software)
running on the same computer for making connections to the database.
This is why it is currently necessary to utilize a Windows-based computer
(or Virtual PC running on MacOS X) to run the Perl DBD::ODBC/mysql program
for data transfer between databases. The third preparation step is to
install Activestate Perl, the Perl DBI, DBD::ODBC and DBD::mysql modules
on the Windows server along with the FileMaker ODBC Driver. (The DBI/DBD::ODBC
modules can be downloaded from www.cpan.org) or installed with Activestate
PPM from a Windows command prompt window. Use the following install
commands:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> install DBD-mysql
PPM> quit
The fourth preparation step is to create the MySQL database (unless one already exists for your use) and create the MySQL user account which will access the new database table which will be created to hold the FileMaker Pro data. This user account should have the proper privileges to insert and delete records and access the database table thru the network. This will generally involve specifying the IP address or range of hostnames which have permission to access the database. Creating the /etc/my.cnf, ~username/my.cnf or c:\my.ini file will enable logging into the MySQL database locally without having to specify a password on the command line. This feature prevents the password from being seen in the process list by other users.
The fifth preparation step is to create the FileMaker Pro ODBC DSN entry in the Windows ODBC Data Sources control panel. Create the contact_management_fmpro_odbc_dsn entry in the ODBC Control Panel. Change the configuration of the Max Text length parameter from 255 to 65000 in the Advanced tab of the FileMaker DSN.
Step By Step Migration Instructions
Step 1) Launch FileMaker Pro and open one or more FileMaker Pro databases on MacOS X.
Step 2) Launch FmPro Migrator on the same server where FileMaker Pro is running.
Step 3) Fill in the fields on the FileMaker tab of FmPro Migrator. Use the Browse button to select a destination directory for the generated migration files and select “MySQL” as the destination database. The maximum length of data supported in MySQL VARCHAR columns is 255 characters, however FileMaker Pro text fields may contain as many as 64000 characters. To prevent problems with FileMaker Pro data exceeding the MySQL column size, FileMaker Pro fields which contain more than 255 characters should be listed in the Large Text Fields field. Any fields listed in the Large Text Fields field will be created as MySQL Text columns in the destination database. Note: The text field names listed in this field should represent the name which will be used by FmPro Migrator for the MySQL column. Special characters and spaces are removed from FileMaker Pro field names in order to create the MySQL column names. For ease of data entry you may want to press the Migrate button to generate all of the files first. Then look at the MySQL version of the report file (named contact_management_report_mysql1.txt for this example) in order to copy and paste the MySQL field names.
The Path to Perl field will not generally need to be changed because the default value is already set for MacOS X. (See Figure 5)
Figure 5 - FileMaker tab of FmPro Migrator
Step 4) Click on the MySQL folder tab and enter the MySQL-specific
information for the destination database. The Hostname field may contain
either a hostname resolvable by a DNS server or the IP address of the
MySQL database server. The default MySQL TCP/IP port number is 3306
and this will generally not need to be changed unless your DBA, system
administrator or ISP tells you to use a different port number. The Database
Name should represent the name an existing MySQL database which must
already exist within the database. Note: Do not use the database
named “mysql” because it is reserved for use by the MySQL
database server. The Username and Password fields should represent an
existing MySQL database user account.
(See Figure 6)
Figure 6 - MySQL tab of FmPro Migrator
Step 5) Press the Migrate button to generate the migration scripts and database documentation files.
Step 6) Examine the contact_management_create_table1.sql file to determine if any changes need to be made. The MySQL table columns created as VARCHAR(255) should be modified as needed prior to creating the table. These columns should only be created as large as needed because the default index width is 500 bytes for non-FULLTEXT search indexes. If the column widths are too large, then it may not be possible to create a multi-column index containing the columns you require at some future date. Transfer the contact_management_create_table1.sql file to the MySQL server and execute this code from within the mysql client application to create the table which will contain the FileMaker data. Using the following command:
mysql mysql1 -u user1 < contact_management_create_table1.sql
If you have not specified a default password via the my.cnf or my.ini file, then the contents of the file can be executed as follows:
mysql mysql1 -u user1
Enter password: *************
mysql> source contact_management_create_table1.sql
Step 7) Transfer the contact_management_fmpro_to_mysql_xfer_odbc1.pl program to the Windows server and execute the code as shown below. The contact_management_fmpro_to_mysql_xfer_odbc1.pl program will transfer text, numeric, date, time and JPEG image information from FileMaker Pro to the new MySQL table.
perl contact_management_fmpro_to_mysql_xfer_odbc1.pl
If Perl was associated with the .pl file extension during the installation of Activestate Perl, then you can simply type:
contact_management_fmpro_to_mysql_xfer_odbc1.pl
This program transfers all records from the FileMaker Pro database into the new MySQL table within the MySQL database. The program can be customized if necessary in order to transfer a subset of records by changing the SQL query. Instead of using:
my $fmpro_sth = $fmpro_dbh->prepare("select
* from $filemaker_database_name");
this code could be modified as follows:
my $fmpro_sth = $fmpro_dbh->prepare("select
* from $filemaker_database_name where identification_number > 1000
");
The contact_management_fmpro_to_mysql_xfer_odbc1.pl
Perl program can also be modified to support JPEG images within a container
fields larger than 100,000 bytes. This is the default setting for the
$long_readlength variable within the program.
Once the data has been transferred to the MySQL table, it can easily be viewed or modified with the MySQL Control Center utility (also available for download from www.mysql.com). The MySQL Control Center utility can be used to administer the MySQL database as well as view images stored within LongBLOB columns. This utility is currently available for Windows and Linux with a future revision planned for MacOS X.
Summary
FileMaker Pro is a versatile easy to use workgroup database application. But if you need functionality not included in FileMaker Pro such as better scalability, replication, or inexpensive hosting at an existing ISP, MySQL is a good alternative to consider. By simplifying the migration process from FileMaker Pro to MySQL, FmPro Migrator enables database developers to utilize the best database for their needs. Databases may easily be prototyped and tested with FileMaker Pro and then migrated to MySQL if the need arises.
Resources/Links
FmPro
Migrator Example Files
FmPro Migrator Info
www.mysql.com
ActiveState Perl
|
||
|
|
|
|
||
