Joe’s Amazing Technicolor Weblog

or, Why To Use Dedicated Layouts When Connecting To FileMaker Via PHP

I’d read that it’s a good practice to always use a dedicated layout for any PHP scripts you have that are talking to a FileMaker database. While I’d seen reasons of efficiency and reliability, today I learned another reason that’s true: it can eliminate otherwise hard-to-debug problems.

At first when working on my current FileMaker <-> PHP project, I was attempting to reuse an existing layout that had all the info I needed. While my permissions seemed to be fine for the data file and layout I was attempting to access, actually running the script kept resulting in “Error 100: File is missing” coming back at me as soon as I added any criteria to my search. FileMaker doesn’t bother putting anything useful in its server logs, either, so it wouldn’t have been much fun picking through the layout & figuring what linkage(s) were to blame.

However, by simply creating a dedicated layout, everything started working as planned. A practice I’ll be following in the future.

I’ve had a number of experiences setting up rich text editors online over the last few years, usually in the context of a Drupal site, and it’s always been a pain.

My most recent experience has mostly been the same, except it ends a little better than usual:

1. Start with TinyMCE, which has more-or-less worked in the past. This time around with Drupal 6, unfortunately, my experience was mostly less: it just wouldn’t respond to the customization settings I was setting in the Drupal backend.
2. Do some more research, find excitement about the YUI editor. It seemed promising, but ultimately failed to allow image uploads despite hours of fiddling.
3. Turn to the other oft-mentioned option, FCKeditor. After a few minutes of fiddling supported by the included readme.txt, it’s actually working and uploading images easily. Amazing!

Not everything in FCK is customizable through the web, but that’s fine. It’s probably easier to comment out a line in fckeditor.config.js, anyhow. I started with the DrupalFull toolbar & took off a few things we don’t want.

Since versions are so often key with these things, I’m using Drupal 6.6, fckeditor-6.x-1.3-rc3 (the drupal module), FCKeditor_2.6.3 (the javascript bit).

Including a reasonably debugged WYSIWYG in the Acquia distribution would get me to take it for a test drive next time around, because this cycle is such an amazing waste of time whenever I go through it.

We last looked at this a few months ago, but have been revisiting it to come up with something a little more robust. Notes:

• there’s a good overview of using PHP with FM at the sixfriedrice blog.
• the API for FileMaker’s PHP interface is available at http://YOURSERVER.URL:16000/docs/PHP%20API%20Documentation/index.html
• I’d missed this last time around, but accounts & permissions are a little funky. fmphp needs to be added to the Extended Privileges of the database you’re trying to get to, and must have the same privilege set as the account you’re connecting as.
• The solution we settled on is a CLI PHP script running hourly, checking for mail to send. Launchd would be the logical way to do the scheduling, but always drives me nuts. Fortunately the server in question has cron set up (so much simpler!)

The code we’re more or less using:

#!/usr/bin/php
<?php
set_include_path(get_include_path() . PATH_SEPARATOR .
  '/Library/FileMaker Server/Web Publishing/publishing-engine/php/lib/php/');
require_once('FileMaker.php');

echo "PHP email-sending-script, running at " .
  date('m/d/Y H:i') . "\n";
$layout = 'Outgoing_Email';
$fm = new FileMaker('Layout Name');
$fm->setProperty('username', 'your filemaker username');
$fm->setProperty('password', 'your filemaker password');

$findCmd =& $fm->newFindCommand($layout);
$findCmd->addFindCriterion('Sent_Flag', '< 1');
$result = $findCmd->execute();
if (FileMaker::isError($result)) {
  if ($result->code == 401) {
    exit("No emails to send.\n");
  } else {
    exit("trouble: " . $result->message . "(" . $result->code . ")");
  }
}

$records = $result->getRecords();
foreach($records as $record) {
  echo "To: " . $record->getField('Recipient') . "\n";
  echo "Subject: " . $record->getField('Subject') . "\n";
  $headers = array(
    "From: filemaker@example.com",
    "MIME-Version: 1.0",
    "Content-type: text/html"
    );

  /*
   FM helpfully encodes < and >...
   */
  $body = preg_replace('/&lt;/', '< ', $record->getField('Body'));
  $body = preg_replace('/&gt;/', '>', $body);
  $rc = mail($record->getField('Recipient'),
       $record->getField('Subject'),
       $body,
       implode("\r\n", $headers)
      );
  if ($rc) {
    $update = $fm->newEditCommand($layout, $record->getRecordId());
    $update->setField('Sent_Date', date('m/d/Y'));
    $update->setField('Sent_Time', date('H:i'));
    $update->setField('Sent_Flag', '1');
    $result = $update->execute();
    if (FileMaker::isError($result)) {
      exit("trouble updating the database after sending email: " .
        $result->message . "(" . $result->code . ")");
    }
    echo "Mailed!\n";
  } else {
    exit("Mail didn't work.\n");
  }
}

Thanks to Hacker News for bringing this common problem with Rails apps to my attention. Nobody seems to have taken advantage of it on my app, but still, it’s a drag having insecure applications, and a little disappointing that there aren’t more heads-up about this, or a more secure default as Merb apparently has.

Some context — my new gig features a big ‘ol FileMaker installation, which has a number of automated maintenance routines. Some of those routines send emails, through a convoluted process involving FileMaker calling a GUI MUA (e.g. Mail.app). There are a number of practical problems with this, such as the requirement of another computer running, always logged into an account capable of sending the emails.

We’d done a bit of research about strictly server-side alternatives, and found surprisingly little. The best resource was Graham Sprague’s page about sending emails via FileMaker’s XSLT Web Publishing tool. We gave that approach a try, but didn’t get any results, or anything useful from FileMaker’s logs to explain why things weren’t working. I’m not sure what FileMaker version Graham’s example was written for, perhaps something’s changed with version 9?

Rather than dive into FileMaker’s proprietary XSLT system to debug things, it occurred to me that this might be a job for FileMaker’s PHP API. Sure enough, after about 15 minutes of consulting the API Doc, we were sending emails based on the contents of a FileMaker record.

We’re still working on ironing out the details, but here’s the rough proof of concept PHP file. It works with the example email database from Graham’s XSLT sample, with the php permission added to the database. Plenty of missing features such as cc & bcc fields, actually checking for the ‘send’ flag, checking for errors, any kind of authorization or authentication, etc. In other words, you probably don’t want to be running this on a publicly accessible webserver, but at least it presents the basic idea in a simple form.

<?php
require_once('FileMaker.php');
$fm = new FileMaker();
$fm->setProperty('username', 'send_email');
$fm->setProperty('password', 'whatever_the_password_is');
$fm->setProperty('database', 'Email');

$findCmd =& $fm->newFindAllCommand('Utility_Email');
$result = $findCmd->execute();
$records = $result->getRecords();
foreach($records as $record) {
  $headers = array("From: " . $record->getField('from'));

  mail($record->getField('to'),
       $record->getField('subject'),
       $record->getField('message'),
       implode("\r\n", $headers)
      );
}
echo "Emails sent";

As regular readers saw earlier, I’ve been casting about trying to find an open source backup solution that handles OS X metadata reliably. Having been disappointed by rdiff-backup, I’ve turned by eyes to the similar rsnapshot project, which uses the venerable rsync.

Since macports includes the latest version of rsync, 3.0.2, I gave it a try with the familiar rsync -avz /from /to syntax, but it performed disappointingly on n8’s handy Backup Bouncer test suite. Thanks to Mike Bombich, I learned about some extra flags to add (though my copy of rsync doesn’t seem to know about the -N or — fileflags he has):

$ sudo rsync -aHAXx  /Volumes/Src/ /Volumes/rsynctest/
$ ./bbouncer verify -d /Volumes/Src/ /Volumes/rsynctest/
Verifying:    basic-permissions ... ok
Verifying:           timestamps ...
   Sub-test:    modification time ... ok
ok
Verifying:             symlinks ... ok
Verifying:    symlink-ownership ... ok
Verifying:            hardlinks ... ok
Verifying:       resource-forks ... ok
Verifying:         finder-flags ... ok
Verifying:         finder-locks ... FAIL
Verifying:        creation-date ... FAIL
Verifying:            bsd-flags ... FAIL
Verifying:       extended-attrs ...
   Sub-test:             on files ... ok
   Sub-test:       on directories ... ok
   Sub-test:          on symlinks ... ok
ok
Verifying: access-control-lists ...
   Sub-test:             on files ... ok
   Sub-test:              on dirs ... ok
ok
Verifying:                 fifo ... FAIL
Verifying:              devices ... FAIL
Verifying:          combo-tests ...
   Sub-test:  xattrs + rsrc forks ... ok
   Sub-test:     lots of metadata ... ok
ok

Sure, there are a few FAILS in there, but they’re not important:

$ ./bbouncer verify -T important -d /Volumes/Src/ /Volumes/rsynctest/
Verifying:    basic-permissions ... ok
Verifying:           timestamps ...
   Sub-test:    modification time ... ok
ok
Verifying:             symlinks ... ok
Verifying:            hardlinks ... ok
Verifying:       resource-forks ... ok
Verifying:         finder-flags ... ok
Verifying:       extended-attrs ...
   Sub-test:             on files ... ok
   Sub-test:       on directories ... ok
   Sub-test:          on symlinks ... ok
ok
Verifying: access-control-lists ...
   Sub-test:             on files ... ok
   Sub-test:              on dirs ... ok
ok

(note the -T important flag telling Backup Bouncer to remove the extra-finicky tests). Good enough! On to get familiar with rsnapshot.

I’ve finally had the chance to repeat my test of the rdiff-backup-devel package from MacPorts, using the same steps as I used for the stable package. The results are better but not fantastic:

$ ./bbouncer  verify -d /Volumes/Src/ /Volumes/rdifftest
Verifying:    basic-permissions ... ok
Verifying:           timestamps ...
   Sub-test:    modification time ... ok
ok
Verifying:             symlinks ... ok
Verifying:    symlink-ownership ... ok
Verifying:            hardlinks ... ok
Verifying:       resource-forks ... ok
Verifying:         finder-flags ... FAIL
Verifying:         finder-locks ... FAIL
Verifying:        creation-date ... ok
Verifying:            bsd-flags ... FAIL
Verifying:       extended-attrs ...
   Sub-test:             on files ... ok
   Sub-test:       on directories ... ok
   Sub-test:          on symlinks ... FAIL
FAIL
Verifying: access-control-lists ...
   Sub-test:             on files ... FAIL
   Sub-test:              on dirs ... FAIL
FAIL
Verifying:                 fifo ... ok
Verifying:              devices ... ok
Verifying:          combo-tests ...
   Sub-test:  xattrs + rsrc forks ... ok
   Sub-test:     lots of metadata ... FAIL
FAIL

With promising reports out on rsync 3, looks like it’s time to take another look at rsnapshot…

Preamble: after a bunch of research, rdiff-backup looked like a good open source solution for OS X backup. And it’s in macports!

Wrinkle: after getting further into my current backup project, I’ve become more aware of the difficulty of getting all of the various forms of OS X metadata backed up. Some research revealed the tool Backup Bouncer, which provides a way to run some tricky tests on any OS X backup system. Here I’m using the latest bbouncer (0.1.2), on a 10.4.11 system all patched up through security update 2008-03.

The stable version of rdiff-backup in macports is 1.0.5, which is what’s being used for the following tests. I’ve poked at both this and the rdiff-backup-devel version, which is 1.1.15, but it doesn’t automatically pull in the xattr module from macports. I’ll retest with that version later. Also note that the bbouncer results don’t suggest that xattr is doing 1.0.5 much good.

$ ./bbouncer create-vol rdifftest
$ sudo rdiff-backup /Volumes/Src /tmp/bb
$ sudo rdiff-backup --force -r 1D /tmp/bb /Volumes/rdifftest/
$ ./bbouncer verify -d /Volumes/Src/ /Volumes/rdifftest/
Verifying:    basic-permissions ... ok
Verifying:           timestamps ...
   Sub-test:    modification time ... ok
ok
Verifying:             symlinks ... ok
Verifying:    symlink-ownership ... ok
Verifying:            hardlinks ... ok
Verifying:       resource-forks ... FAIL
Verifying:         finder-flags ... FAIL
Verifying:         finder-locks ... FAIL
Verifying:        creation-date ... FAIL
Verifying:            bsd-flags ... FAIL
Verifying:       extended-attrs ...
   Sub-test:             on files ... FAIL
   Sub-test:       on directories ... FAIL
   Sub-test:          on symlinks ... FAIL
FAIL
Verifying: access-control-lists ...
   Sub-test:             on files ... FAIL
   Sub-test:              on dirs ... FAIL
FAIL
Verifying:                 fifo ... FAIL
Verifying:              devices ... FAIL
Verifying:          combo-tests ...
   Sub-test:  xattrs + rsrc forks ... FAIL
   Sub-test:     lots of metadata ... FAIL
FAIL

That’s a whole lot of FAIL, especially compared to the built-in OS X rsync, which passes on resource forks and finder flags, extended attributes for files and directories, ACLs, and the last few tests.

Granted, there are a bunch of things being tested by bbouncer that may or may not be of interest to the average user. A recent post by the author points out that bbouncer has a -T flag for indicating which level of paranoia to run at. Rerunning the test for rdiff-backup 1.0.5 with only critical tests is somewhat instructive:

$ ./bbouncer verify -d -T critical /Volumes/Src/ /Volumes/rdifftest/
Verifying:    basic-permissions ... ok
Verifying:           timestamps ...
   Sub-test:    modification time ... ok
ok
Verifying:             symlinks ... ok
Verifying:       resource-forks ... FAIL
Verifying:         finder-flags ... FAIL

Compare to OS X’s rsync:

$ ./bbouncer verify -d -T critical /Volumes/Src/ /Volumes/Dst/10-rsync-apple/
Verifying:    basic-permissions ... ok
Verifying:           timestamps ...
   Sub-test:    modification time ... ok
ok
Verifying:             symlinks ... ok
Verifying:       resource-forks ... ok
Verifying:         finder-flags ... ok

Here’s my top 10 command line entries, according to history | awk '{a[$2]++} END{for(i in a){printf "%5d\t%s\n",a[i],i}}' | sort -rn | head

125 svn
58 ls
38 symfony
37 gvim
36 vim
36 cd
34 php
20 uptime
14 ssh
10 lighttpd

Idea & shell magic via Bill de Hóra. You can see that my recent work has been symfony-heavy (that’s what all the php is for, too).

I’ve been meaning to write something substantial about the projects I’ve been working on as a contractor, but it’s been long enough that just getting out a quick summary seems like it’s the best I’m likely to manage any time soon. Here’s some of what I’ve been up to for the last few years:

http://womenslunchplace.org/ — this just launched in November. It’s based on the Drupal open source CMS; my work included participation in the planning process, basic CMS configuration, building the theme based on static HTML & CSS templates from collaborators Jon Sachs and Andrew Rodgers, and writing some custom code to handle donations & related info.

http://rrankk.com — local startup. I did most of the data layer and vote processing work, while a couple other people did the design and interface. The voting stuff was particularly interesting, as it gave me a chance to learn more about the state of the art in voting theory, and to contribute some patches to Benjamin Mako Hill’s rubyvote library.

http://hear-db.org/ — this is a custom contact-management tool I developed with the BU School of Public Health and other local environmental health groups. Unfortunately, there isn’t much to look at without an account. I’m working on releasing the code behind this as an open source project, based on symfony.

http://skylineshuttle.com/ — a rare out-of-town project. My old friend Dave recently started this company, which runs shuttles from Duluth, MN to the Minneapolis airport, and points in-between. Dave is the sort of amazing guy who can teach himself how to build a scheduling and reservation-taking website while handling all the other aspects of launching a business. He’s also smart enough to bring in an old-timer like me to audit and modify his code for security, accessibility, and reliability before launching it publicly.

http://alexandreemmanuel.com/ — a portfolio site for local painter Alexandre Emmanuel, developed with my friend Cliff. This was an interesting challenge, because we needed to come up with a low-budget, bilingual site that could be cheaply hosted and easily added to.

http://jpgazette.com — another Drupal CMS site, which I worked on as part of the Alliance for Community Journalism. Beyond standard CMS configuration and theming, most of my work had to do with getting calendar events to show up similarly to their print edition.