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.

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");
  }
}

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";

On advice from Nate, I’m taking Symfony for a spin (using the stable version 1, not the under-development 1.1), reimplementing the aforementioned contact management application. Here’s what I’m noticing:

• Somewhat like Rails, there’s DB-independent schema definition! Big win here. Among other things, this lets you develop in SQLite and deploy in MySQL, which is a nice pattern. Rails’ awesome migrations aren’t here, so it’s not as useful and flexible a system, but it’s better than nothing for getting up & running.
• One big difference from Rails that’s clear right away: rather than building code dynamically based on the DB schema, thousands of lines of getters / setters / etc. are generated by the symfony command line tool. Makes sense for PHP, but it’s a clunkier development experience.
• There’s testing built in! It’s a bit of a PITA to do things like create a test database, and all the pieces for testing your models aren’t there right away, but still better than CakePHP. The aforementioned missing piece is available as a plugin. There doesn’t seem to be a straightforward way to get the propel-insert-sql task to run on alternate environments, so one must create the db on their own. I’m using SQLite, which is great, but my local environment somehow ended up with PHP having SQLite 2.8 while my command line is version 3 (macports at fault? what a step backwards from Ubuntu…). One port install sqlite2 later, and you can do sqlite data/test.db < data/sql/lib.model.schema.sql
• No console, which makes the tests even more essential, so it does seem to be worth the pain of getting them working.

Side note — already, on the basis of the previous post, I’m seeing a surprising amount of traffic from searches on CakePHP, Symfony, and REST. Clearly I’m not the only one looking for this kind of thing.

I have a new project coming up that seems like a great fit for Ruby on Rails, particularly the RESTful interfaces that have gone in over the last year. However, the typical questions about using Rails apply here: I’m not sure how well the project’s hosting environment will support Rails, and collaborators aren’t as familiar with it. Thus, it seemed like it might be worth another look at options on the PHP side of things. I keep hearing that CakePHP is Rails-inspired and has many of the same advantages, so today I’ve taken the stable version (1.1.8.5850) for a spin, letting the Cake manual’s blog tutorial get me started. Here’s what I’ve noticed about CakePHP development as practiced in the tutorial — maybe there’s better ways to do things & the tutorial just isn’t mentioning them?

• No migrations; you have to generate the DDL on your own, both initially and for any subsequent modifications. Also, no model or controller generators. It’s definitely nicer to just do script/generate scaffold post title:string body:text; rake db:migrate and be off to the races — from Rails 1.2 on, those races include the whole REST business. To be fair, there is a one off reference to ‘bake’ scripts in the CakePHP manual, but no pointers on what those are, or where they live, and the website isn’t much clearer.
• No TDD. Woah. That’s, like, half the advantage of Rails — automated testing is right there in your face, and is firmly entrenched in the community.
• PHP code is not quite as elegant as Ruby. For example, generating a link to delete a post looks like this: $html->link('Delete', "/posts/delete/{$post['Post'][id']}", null, 'Are you sure?' ). Can you spot the syntax error? Probably you can, but it took me a good few minutes. The Rails equivalent would be
  link_to "Delete", { :action => "delete", :id => @post.id }, :confirm => "Are you sure?", :method => :delete
• A lack of the experiences baked into Rails. For example, after the Google Web Accelerator fiasco, it became common practice to make all links to destructive actions happen via POST, which can be done via link_to’s :method parameter. Compare to the GET-powered delete link above, straight from the Cake tutorial.
• I miss my vim & rails integration, which makes creating and navigating the various files a breeze. (Glad I went to the trouble of making that a link, as it got me re-reading the plugin’s about page, and I saw the parts about partial extraction and migration inversion, neither of which I’d noticed before)

On the upside, using CakePHP sure beats writing a CRUD-heavy PHP application from scratch. One of my current projects, a completely custom contact management application, would be in much better shape if it were using CakePHP instead of the project-specific ORM I cobbled together — I’m going to have to give conversion of that project to CakePHP some serious thought.

But is CakePHP a DSL for the web in the sense that Rails is? Not yet.