|
 |
 |
|
|
|
||
|
|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
FileMaker Field Type |
PostgreSQL Column Type |
| Text | VARCHAR(254) |
| Text | TEXT (if > 4096 bytes) |
| Number | DECIMAL |
| Date/Time | DATE |
|
Date/Time - Time Data |
TIME |
| Container | lo |
[Table 1 - FileMaker and PostgreSQL Data Types]
FileMaker Date/Time fields can store Date or Time information and FileMaker 7 even includes a new Timestamp field which can be used for storing more precise Time data. However FileMaker does not provide any external way to query the database to determine which type of data is actually stored within the field. Therefore FmPro Migrator attempts to determine the type of stored data within the field based upon the name of the field. If a Date/Time field includes the text "time" within its name, it is migrated to a PostgreSQL Time column. This behavior can be overridden by simply changing the field name within FileMaker so that FmPro Migrator doesn't see the text "time" within the name of the field.
FileMaker Parameters
[Figure 2 - FileMaker Parameters]
The first step in the migration process is to launch FileMaker, open
the Example.fp5 database file and add a few records of sample data to
the database. Make sure that the Local and Remote Data Access Companion
plug ins are enabled and that file sharing is enabled for the Example.fp5
FileMaker database file. The FileMaker database filename will become
the PostgreSQL database table name.
Launch FmPro Migrator for Windows, click on the FileMaker
folder tab then select FileMaker 6 as the source database and PostgreSQL
as the destination database. The ODBC DSN for the source database will
be used by FmPro Migrator to obtain metadata from the FileMaker database.
Either keep the default "example_fmp_dsn" ODBC DSN
name listed in FmPro Migrator or change it to match an existing FileMaker
ODBC DSN on your computer.
Since I frequently perform migration projects for customers, I change the contents of the Customer field to reflect their name. The name within this field is used to create the Copyright information within each generated script, thus keeping my customer's legal department happy. Click the browse button to select an output directory, since there will be over a dozen files created for the migration process.
Create the FileMaker ODBC DSN
Open the Windows Data Sources (ODBC) Control Panel to
create a new FileMaker 6 System DSN to match the ODBC DSN name entered
into FmPro Migrator for the source database. The Data Sources (ODBC)
control panel is located within the Administrative Tools folder of the
Windows Control Panel.
Note: With Windows XP, it may be necessary to select the Classic View
for the Control Panel in order to see the Administrative Tools folder.
 |
 |
[Figure 3- Data Sources (ODBC) Control Panel]
This ODBC DSN should be created as a System DSN, so click
on the System DSN tab, then click the Add button.
Select the FileMaker Pro driver, then click the Finish button.
Note: If FileMaker 7 is being used as the source database then
the DataDirect 32-BIT SequeLink 5.4 driver would need to be selected.
 |
 |
[Figure 4 - Create New FileMaker Datasource]
Enter the name of the ODBC DSN in the first field. This
name should exactly match the name entered into the Source Database
ODBC DSN field within FmPro Migrator. It is not necessary to enter
the Remote Connection information unless the actual FileMaker database
file is being accessed from another computer. Click the Advanced tab
of the FileMakerPro ODBC Driver Setup panel.
[Figure 5 - Enter FileMaker ODBC DSN Name]
On the Advanced tab, change the Max Text Length from 255 to 65000. FileMaker 6 databases can store up to 64K of text within a text field, so this change in the ODBC Driver Setup panel insures that data is not truncated as it is read from the FileMaker database.
The Fetch Chunk Size determines how many records will
be retrieved from the FileMaker database at a time. When reading records
from the FileMaker database you will generally notice that 100 records
are retrieved, then there will be a pause while the next 100 records
are read from the database. This process will continue until all of
the records have been read. The default Fetch Chunk Size is 100, and
this value should generally not be increased. Increasing the ODBC Fetch
Chunk Size can cause FileMaker to crash while serving database files.
In fact this parameter may need to be reduced to as few as 10 records
if there are more than 500 fields within the FileMaker database file.
Click the Ok button.
[Figure 6 - FileMaker ODBC DSN Advanced Parameters]
Now that the FileMaker ODBC DSN has been created, FmPro
Migrator can query the FileMaker database and obtain the metadata required
to create migration scripts to PostgreSQL. Click the Refresh button
in FmPro Migrator. The example.fp5 database files opened in FileMaker
6 should show up in the Open Databases field.
[Figure 7 - FileMaker Open Databases]
PostgreSQL Database Parameters
PostgreSQL database parameters are entered on the FmPro Migrator "Other" tab. These parameters include database name, username, password and TCP/IP port number information which will be used within the generated migration scripts.
[Figure 8- PostgreSQL Database Parameters]
Generating Migration Scripts
Press the Migrate button in FmPro Migrator to generate
migration scripts and database documentation files. Scripts which start
with the name of the original FileMaker database file(s) will then be
created within the output directory as shown below.
[Figure 9 - Generated PostgreSQL Migration Files]
If these scripts are not created, select the Status Window menu item from the FmPro Migrator File menu. The Status Window will display info about any problems which occurred during the script generation process.
[Figure 10 - FmPro Migrator Status Window]
There are 8 files created for the migration process, but we will only need to use the 5 files which are described in Table 2.
| Filename | Description |
| example_instructions1.txt | This file contains detailed instructions concerning the migration process and is customized for the database which is currently being migrated. |
| example_report_postgresql1.txt | This report file shows the structure of the FileMaker database, including the names of fields, and field types. This file also shows how the FileMaker fields will be renamed when creating the new PostgreSQL table. |
| example_create_table1.sql | The SQL file which creates the PostgreSQL database table. |
| example_fmpro_max_fieldsize1.pl | The Perl script which reads each of the FileMaker records, then creates a report showing the maximum number of characters within each field. |
| example_fmpro_to_postgresql_xfer_odbc1.pl | This Perl script reads the data from the FileMaker database and then writes the data into the PostgreSQL database via an ODBC connection. |
[Table 2 - Migration File Descriptions]
Checking Field Sizes
Open the Windows Command Prompt by selecting Start > Programs >
Accessories > Command Prompt.
Use the cd command to navigate into the folder of generated scripts.
 |
 |
[Figure 11 - Opening Windows Command Prompt]
Prior to creating the PostgreSQL database table, it is necessary to determine the maximum amount of data stored within each field of the FileMaker database. Run the example_fmpro_max_fieldsize1.pl program from the Windows command prompt to create the fieldsize report file.
perl example_fmpro_max_fieldsize1.pl
After this program finishes, it will produce the example_fmpro_max_fieldsize_report.txt
file. Examine the contents of the example_fmpro_max_fieldsize_report.txt
file to determine whether there are more than 254 characters of information
stored within any of the text fields of the FileMaker database file.
For this article, the FileMaker field named “location” is
used as an example of a text field which contains more than 254 characters.
Examining the example_fmpro_max_fieldsize_report.txt file shows
that FmPro Migrator will rename this FileMaker field as “location_”
when creating the PostgreSQL database table. To designate this field
as a Large Text field, enter “location_” within the FmPro
Migrator Large Text Fields field.
[Figure 12 - Defining Large Text Fields]
|
A Note About Reserved Words FmPro Migrator contains a list of SQL Key Words derived from the PostgreSQL documentation. In order to be conservative about the use of reserved words for column names, FmPro Migrator treats each entry in the list of SQL Key Words as a reserved word for column names. This conservative approach toward column names is intended to prevent problems with column names with previous, current and future PostgreSQL database versions. FileMaker field names which appear on the list of SQL Key Words are renamed by appending the "_" character to the end of the column name. |
Creating the PostgreSQL Table
Open the example_create_table1.sql file in a text editor. Using the fieldsize report you can determine the optimum size to use for each column of the new PostgreSQL table. It is generally a good idea to reduce text column widths from the default value of VARCHAR(254) to be closer to the actual amount of data contained within the FileMaker database.
This particular FileMaker example database does not contain a primary key. This is OK if all we want to do is perform a quick migration to get the data into PostgreSQL and then copy the data to another table later. But if we want the migration process to produce a table which is immediately ready for production use, we should add a primary key field to the table. FmPro Migrator automatically determines that you have a primary key within the FileMaker database by looking for the first numeric field which has the attributes of "Not Empty" and "Unique". Within the table creation SQL file, a primary key column will be created as BIGSERIAL NOT NULL, in order to provide an incrementing sequence upon record insertion. The table creation SQL code may be manually modified as needed prior to creating the PostgreSQL database table. The best strategy to follow is to make any column name changes within the original FileMaker database, then regenerate the scripts with FmPro Migrator. In most cases there is no reason to try to manually track the dependencies between the generated output files, so just let FmPro Migrator recreate these files for you.
The example_create_table1.sql file makes use of a user defined type called “lo” for storing FileMaker container field data as large binary objects within the PostgreSQL database table. The SQL statements used to create this user defined type (or domain in PostgreSQL 7.3+) is commented out by default. Un-comment the appropriate code for your particular version of the PostgreSQL database.
Transfer the example_create_table1.sql file to
the PostgreSQL database server.
For this example, the PostgreSQL database is running on a Linux server.
The file has been transferred to the PostgreSQL server via an FTP connection
and an SSH session has been opened to the Linux server.
Execute the command listed at the top of this file to create the PostgreSQL
database table.
psql -d test -U user1 -p 5432 -f example_create_table1.sql
[Figure 13 - Create the PostgreSQL Table]
The "CREATE TABLE" result from psql shows that the table was successfully created. You can further verify the table creation process by issuing a describe command for the new database table.
[Figure 14 - Verifying PostgreSQL Table Creation]
Migrating the Data
Now that the PostgreSQL table has been created, the Perl
DBI program which transfers the data from FileMaker to PostgreSQL can
be run. This part of the migration process requires the use of the psqlodbc
driver in order to write data into the new PostgreSQL database table.
Download and install the psqlodbc driver from the gborg
website.
The psqlodbc ODBC driver is used to transfer data from FileMaker to
PostgreSQL because it provides native support for the user defined “lo”
column type used for transferring FileMaker container field data to
PostgreSQL large object columns.
Create a new ODBC System DSN to match the Destination
Database ODBC DSN name defined by FmPro Migrator within the example_fmpro_to_postgresql_xfer_odbc1.pl
file. For this example database file, the ODBC DSN name is example_pg_dsn.
Click the System DSN tab, then click the Add button.
Select the PostgreSQL driver, then click the Finish button.
|
 |
[Figure 15 - Creating PostgreSQL ODBC DSN]
Enter the data source name and server info on the first pane of the PostgreSQL ODBC driver panel. Then click on the Datasource button.
[Figure 16 - PostgreSQL ODBC DSN Configuration - Pane 1]
Increase the size for the Max LongVarChar parameter from
8190 to 65000.
Click the OK button, then the Save button.
[Figure 17 - PostgreSQL ODBC DSN Configuration - Pane 2]
Run the example_fmpro_to_postgresql_xfer_odbc1.pl
script from the Windows command prompt window to copy the data from
the FileMaker database to the new PostgreSQL table.
As the script runs, each processed record number will be displayed on
the screen.
You can verify the copied data by using SQL commands in psql or by using
a graphical utility.
Tips for Migrating Very Large FileMaker Databases
When migrating very large FileMaker database files (over 500 Mb) there are a few modifications I make to the migration process in order to reduce the overall processing time. The typical scenario I have encountered is a situation in which the client wants to migrate the FileMaker data into a large corporate database server. The migration task is part of a larger re-engineering effort to properly normalize the FileMaker data and create a corporate-wide user interface for the data. Migration of the data represents only the first step in the process. The second part of the engineering effort involves using SQL commands to cleanse the data and copy the data to the new normalized database tables. This task can be performed more efficiently in two steps because the second step will involve working with data which will be entirely contained within the PostgreSQL database.
I apply the following steps in this type of situation:
• Avoid Making Two Passes Through the Data - This means that I
don't run the fieldsize report for very large database files. This also
means that I don't have the information required to properly size the
columns in the destination database.
• Create Each Column as Large as Possible - For each text column
of the destination table I use the largest possible text compatible
column size, which means using a TEXT data type in place of text, numeric
and date fields. FileMaker Container fields get transferred to PostgreSQL
"lo" object type columns so these columns don't require any
changes.
It is easy to use a text editor to manually change the table creation
SQL code by performing a global search and replace within the file.
This step resolves the problem of data potentially getting truncated
due to the PostgreSQL column size not being large enough. This also
means that data type errors within the FileMaker database will not prevent
the data from being written into the PostgreSQL database. You generally
don't want to be part of the way through migrating a 500 Mb file and
then encounter a data type mismatch error. It will be quicker to find
and fix these types of errors during a data cleansing operation at a
later point in time.
• Manually Change DBI data types - Once the new table has been
created, I then manually modify the Perl DBI transfer program to reflect
these changes. When dealing with large numbers of columns it is much
quicker to manually change the bind variable data types with a text
editor rather than manually enter each column name into FmPro Migrator
as a Large Text Field. The changes I make include:
1) Changing all SQL_VARCHAR bind variable data types to SQL_LONGVARCHAR
2) Changing all SQL_DATE bind variable data types to SQL_LONGVARCHAR
3) Changing all SQL_DOUBLE bind variable data types to SQL_LONGVARCHAR
The SQL_LONGVARBINARY columns remain unchanged because these columns
get transferred to "lo" object type columns - which store
up to 1 GB of data per record.
Tips for Migration FileMaker Stand Alone Files
The FileMaker Developer version of FileMaker enables developers to package one or more FileMaker database files as a stand alone package which does not require the FileMaker database software. The FileMaker Developer Tool creates a folder for the solution which contains the FileMaker runtime executable and each of the individual FileMaker database files. On Windows, each of the FileMaker database files will be given a developer assigned extension which by default consists of the characters USR. So FileMaker databases which were originally named filename.fp5 will now be named filename.usr.
To work with stand alone solutions files on Windows, just change the
filename extension from ".usr" to ".fp5" and use
the FileMaker application to open the file. On MacOS X, the database
filename extension will typically remain as ".fp5". All you
have to do is drag and drop the filename onto the running FileMaker
application icon in the dock. If you just double-click on the file instead
of opening it with the FileMaker application then the FileMaker runtime
application will launch the file - and this is not what you want to
have happen. You will want the database to be opened within FileMaker
so that you can enable the Local and Remote Data Access Companions and
make changes to the fields within the Define Fields dialog. Once you
have the database file open in FileMaker you can use FmPro Migrator
to generate migration scripts in the same manner as any other FileMaker
database file.
Conclusion
The automation which has resulted from the use of FmPro Migrator has made it possible for me and my customers take on projects which would have been too expensive or time consuming to be practical without an automated tool. FmPro Migrator has also made it possible for the FileMaker database to be used as a RAD (Rapid Application Development) tool for enterprise class databases like PostgreSQL.
|
||
|
|
|
|
||
