Front end to FMP

I need to build a Q&D front end for a FileMaker database. While the Server version of FileMaker ships with some PHP glue, I decided to go with a more familiar environment. Python! It's a nice language. I have some familiarity with it, and (most important of all) it works on the ISP's server.

I did a quick survey of some lightweight Python frameworks, and settled on TurboGears (with CherryPy) for the app itself. For the interface to FileMaker, I found PyFileMaker.

Ready to go! So, to begin with, I did a little spiking to kick the tires of PyFileMaker. Right out the gate, the results were disappointing. The code choked on my password! (Some regex seemed to believe that my password shouldn't contain special characters.) Not a problem: I created a special account for PyFileMaker (not a bad idea anyway for production) and got one teensy step further. I hit our FMP Server and asked for a list of databases. Kapow! Now PyFileMaker complained that one of the db names "contain unsupported characters." Pity it doesn't deign to tell me which one...

Well!

Foreseeing a life of misery and pain, I looked for a Ruby solution. A quick trip to the ISP... Whew! Ruby works there too. Anyway, it's high time I learned Ruby, so next stop, Rfm The visit to the six.fried.rice site was a breath of fresh air. I know from previous googling journeys that they seem to know what they're talking about. Best of all, they have a RoR sample app that talks to a FMP db, so I can start with that.

Into the world of Ruby!

Migrating from FileMaker to MySQL

FileMaker Pro (FMP) has the ability to access a real RDBMS via ODBC dynamically. An "external" table can be accessed in the same way as a native FMP table. FMP calculations, normally associated with a native FMP table, can be added to an external table as "supplementary fields".

So all this sounds terribly glorious. Surely one can migrate one's FMP db to MySQL (or other) so that the data is stored in an industrial-strength RDBMS. That, naturally, allows all kinds of other enterprise apps to hit the same data as the FMP. In fact, one can think of the FMP db as a front end to the enterprise db.

I know folks who have made this work. But migrating is another story. There is no programatic interface to the FMP schema - the definitions of the tables and "table occurrences" (really aliases that allow for the definition of associations that become joins at run time). And FMP's use internally of ids rather than names for fields, scripts, tables, etc – one of its key benefits that allows transparent renaming of objects – means that once a relationship is defined using one table, the only way to switch to another (external) table is to delete and recreate it.

That spells a ton of manual labor. I'll outsource it if I can find someone.

We'll still be left with all the presentation intelligence (FMP layouts) as well as the business logic (scripts, calculated fields, portals) firmly in the FMP world. As we build a parallel implementation that does not rely on FMP, we'll be rebuilding all that stuff. Only this time we'll do it in a transparent, modifiable, refactorable way, using code that can be edited using industry-standard tools like (ooh, gasp) a text editor.

So if you're thinking of building a real enterprise app in FMP, don't. It will bite you.

And to answer my own questions from a few months ago regarding FMP in an XP world: The more I bang my head against the closed, constrained FMP model, the more I realize that its only use is as a spiking tool.

PHP and FMP

Get FM_API_for_PHP_Standalone from within the FM server download. Use the trial if necessary.

Expand it and copy it into /usr/local/php/fmp. Make the parent directories as needed

Copy /etc/php.ini.default to/etc/php.ini

Edit php.ini. Uncomment and modify include_path = ".:/usr/local/php/includes"

I set up eclipse by copying the features and plugins after downloading from http://www.eclipse.org/pdt/downloads/ I couldn't get the update site to work (mysterious errors.) Finally, after messing with it for ages, I concluded that Eclipse PDT doesn't work with Ganymede. I now use PHPEclipse.

For debugging, we need xdebug. Oopsie. Must install pear to get pecl to get xdebug.

So,

curl http://pear.php.net/go-pear > go-pear.php
sudo php -q go-pear.php

Let the script update php.ini. And don't forget to update PATH like the script told you to.

Then it's

sudo pecl install xdebug

And do NOT do what it says (You should add "extension=xdebug.so" to php.ini")

Instead, add this:

[xdebug]
zend_extension=/usr/lib/php/extensions/no-debug-non-zts-20060613/xdebug.so

Then confirm that it's working by doing php -mxdebug will show up both under [PHP Modules] and [Zend Modules]

Get phpUnit installed. See Sebastian Bergmann's site: http://www.phpunit.de/manual/3.4/en/installation.html

sudo pear channel-discover pear.phpunit.de
sudo pear install phpunit/PHPUnit

Why FileMaker ultimately sucks

When you have a FileMaker "solution" in place, you have a large investment in UI design and usability. You might, depending on the quality of the original design, also have an investment in the database schema. Porting to another environment - say Java + Spring + Hibernate - is daunting, mostly because re-implementing the UI is such a vast undertaking.

As an XPer, I need to be able to test-drive development. The only reasonable way to do that is by using external AppleScript (or Python + appscript and maybe PyUnit too) tests. I don't know about you, but that sounds like far more trouble than it's worth. Just navigating the maze of what works as expected and what requires arcane tricks in the AppleScript world gives me a headache just thinking about it.

XPers also refactor. Not surprisingly, there are no tools at all for refactoring. Worse, the structure and syntax of FileMaker's scripting language actively discourages refactoring. For example, while it's possible to pass multiple arguments into a sub-script, they have to be packed on the caller side and unpacked by the callee - multiple lines of script. And having to drag commands into place rather than just type like a normal human...

Then there is basic source control. A FileMaker file consists of several elements, the most important being

• Tables and relationships
• Scripts
• Layouts

I need to be able to see what's changed from one version of my FMP file to another. Since all the above elements are stored in a single binary file with a proprietary format, it's impossible to do a diff. Sure, one could export a DDR XML file and diff that, but diffing XML?!

And there are indeed tools that purport to do this. Unfortunately, they are universally hard to use and slow, if you cam get them to work at all. (Of the 3 main contenders, I have only managed to get the demo version of one of them to work consistently. The others are so obsessed with license keys that they fail to work at all.)