MacDevCenter    
 Published on MacDevCenter (http://www.macdevcenter.com/)
 See this if you're having trouble printing code examples


Automated Web Photo Galleries with iPhoto and Perl

by Mike Schienle
05/07/2004

My wife, Rhonda, recently started to need to send up to several thousand images from iPhoto directly to a MySQL database on a remote server, on an ongoing basis. She is an avid photographer and volunteers to take pictures for our city. One of her photos recently made the cover of the city's annual report, which got her more interested in putting up a web gallery, and perhaps even offering pictures for sale along the way.

She's been using iPhoto on her iMac G4/800 for a couple of years, to manage a few thousand photos using two different digital cameras. Since she has so many images, there was really no choice but to build a database to manage the pictures and use a web gallery to present them. What I wanted was for Rhonda to be able to work within iPhoto as much as possible and not have to worry about the actual publishing process to the web server. Since I travel a bit, it had to be a setup that didn't require me to mount disks, format photos, build SQL commands, or process email messages.

I've been running an iMac G3 as a co-located web server for small businesses for a couple years, which is perfectly adequate for her site needs. So we started building her site, knowing that I could take full advantage of Macs from both ends. What I needed was a way to get several thousand photos from iPhoto to a MySQL database on our web server in as few steps as possible. I specialize in task automation, making computers and networks do all the work and eliminating as much human error as possible, and it was nice to be able to apply some of my knowledge to our home life.

Related Reading

iPhoto 4: The Missing Manual
By David Pogue, Derrick Story

After looking at several different ways to set this up, I settled on a path that makes the most use of iPhoto, and includes Apple Mail, Sendmail on the server, MySQL, and plenty of Perl to hook it all together.

In iPhoto, photos are selected based on category, and formatted using iPhoto's email capabilities. iPhoto then uses Apple Mail to build an email message including the photos and emails it away to an address on the server. As the message arrives at the server, a Perl program intercepts it, picks out the various pieces and accompanying photos, applies some basic ImageMagick, then drops it all into a MySQL database on the server. When folks visit her site, based on what they select, a couple of different applications access the MySQL database to present the photos from our server.

iPhoto

There are no real procedures to working with iPhoto to make this setup work. iPhoto has enough bells and whistles and allows good management and basic editing of an ever-growing photo library. With a little discipline and some cooperation from your digital camera, iPhoto has everything you need to manage your collection.

If the camera you're using stores images as a unique sequence, that's perfect. Rhonda uses a Kodak DX6490 (four megapixels and 10X zoom are the main features), which stores photos as a sequence: 100_1219.JPG, 100_1220.JPG, 100_1221.JPG, etc. An older Sony Mavica that she used up until this year also stores files in a sequence: MVC-660X.JPG, MVC-661X.JPG, MVC-662X.JPG, etc. When the images are imported into iPhoto, these become unique titles for the images.

My wife has made a habit of entering comments for her pictures after they are imported. This was a great asset to her collection for a variety of reasons, particularly in this project. The iPhoto comments are used as captions when the photos are displayed in the web gallery. You can see an example in the image below.


Email button selected in iPhoto. Dialog box shows Size, along with Titles and Comments boxes selected.

When you select the Email button from within iPhoto, you can optionally select Titles and Comments to be added to the email message. At the same time, you can select the resolution of the images to be sent. We use 640 by 480 for the transfers, since that is easily displayed within a web page, and is not high enough resolution to be concerned when someone copies the image from the site. The original titles are maintained to allow easy reference to the full-size photos for ordering.

Mail

When iPhoto transfers the photos to Mail, it uses either the Title for the subject with one image, or something like "5 Great iPhotos" if you select five images. The subject is changed regardless, and is used as a category name. So if the photos are of some elk, the subject may be "Animals." Likewise, a series of pictures of a sunrise may have a subject such as "Scenery."

You can send as many photos as practical at one time; just make sure they all belong to a single category/subject. The comments can be changed within the email, though titles should be left unique as mentioned above. When iPhoto transfers the information to the email, you'll see something like this:


Once the image is transferred to Apple Mail, you'll see the image followed by the title and comments. The subject needs to be changed to reflect its category.

You can edit the comments, or insert them if they are not already provided. Once you've changed the subject and addressed the message, it's ready to be sent to the server.

Sendmail

Sendmail is the underlying program that processes email on MacOS X 10.2 (in 10.3, Postfix serves the same role). The server has a specific email account set aside to handle photos. By using the standard .forward file in that account's home directory, we can pass all incoming messages to a Perl program. That program processes the images, titles, and comments. There are several ways to prevent abuse on this account; the one I selected is to limit which email accounts from which it will process messages, from within the Perl code. In other words, it will toss any messages not coming from my wife's account, or a small list of other accounts.

The .forward file is simply:

"|/usr/adm/sm.bin/mailPhotoProcess.pl"

Be sure to include the quotes around the command and the pipe before the command name.

ImageMagick

ImageMagick is a robust collection of tools and libraries offered under a usage license to read, write, and manipulate images in many image formats. The Perl program processing the messages on the server sends the image portions of the messages to ImageMagick for some simple size reductions to build a thumbnail. ImageMagick will provide a variety of other tasks, such as framing, watermarking, etc. Since we keep the photos at a relatively small 640 by 480, there's no need for watermarking. However, if you want to add borders, drop shadows, or make any of a myriad of other changes to an image, ImageMagick is an excellent choice.

As part of the message processing, the Perl program also keeps track of the image type (i.e. JPG, GIF, etc.). The image type is stored in the database and used when we display the image on the web page.

MySQL

The MySQL database server is the world's most popular open source database, with more than five million active installations. Both the thumbnail and the original photo are stored in a MySQL database. The other fields that are added to the database table are a unique ID (generated by MySQL), image type (detected during the MIME parsing of the image), image title (extracted from the message body during MIME parsing), image comments (extracted from the message body during MIME parsing), and category (extracted from the message header during MIME parsing).

Here is the MySQL code to set up the database table:


CREATE DATABASE IF NOT EXISTS domain;
USE domain;

DROP TABLE IF EXISTS Gallery;

CREATE TABLE Gallery (
  ID        INT UNSIGNED AUTO_INCREMENT NOT NULL,
  Date      DATE NOT NULL,
  Image     LONGBLOB NOT NULL,
  Thumb     MEDIUMBLOB NOT NULL,
  Type      VARCHAR(20) NOT NULL,
  Title     VARCHAR(80) NOT NULL,
  Comments  TEXT,
  Category  VARCHAR(40) NOT NULL,
  PRIMARY KEY (ID),
  INDEX (Category)
);

Perl

Perl is often called "the duct tape of the Internet," and this application certainly fits that description. Now is the time to open an editor and create some Perl code. This code needs to go in a particular location to allow the server to process it correctly. My iMac server is currently running MacOS X 10.2.8, which uses Sendmail. Accordingly, the Perl program needs to be located in the /usr/adm/sm.bin directory. You'll need to create any directories along the way, such as adm and sm.bin.

The gist of the program is that we are going to receive an email message, which will have MIME attachments. Those attachments will have to be extracted from the message, processed, and stored in a database. Once we're done processing the email, we'll send back a message indicating how many images were processed and the URLs to those images.


#!/usr/bin/perl -w
# load in the modules
use MIME::Parser;
use MIME::Entity;
use DBI;
use Image::Magick;
use Mail::Mailer;
use strict;

# get DBI vars and related info
my %dbHash = (
  "dbType"  => 'mysql',
  "dbName"  => 'domain',
  "dbHost"  => 'localhost',
  "dbUser"  => 'myUser',
  "dbPass"  => 'myPass'
);

# DBI handles
my ($sth, $sql, $rv, $dbh, $dbData);

# set up DB connection
DBConnect(%dbHash)
  or DBError("Died in Connect");

# MIME parsing vars
my ($i, $parser, $entity, $head, $preamble, 
  $epilogue, $num_parts, $part, $content_type, 
  $body, $tmp);

# extract the pieces out of the email message
$parser   = MIME::Parser->new();
$parser->output_dir("data");
$entity   = $parser->parse(\*STDIN);
$head     = $entity->head;
$preamble = $entity->preamble;
$epilogue = $entity->epilogue;

# get the subject and use it as 
# the category for all the photos
my $category = $head->get('Subject');
my $mailTo   = $head->get('To');
my $mailFrom = $head->get('From');
chomp($category);
chomp($mailTo);
chomp($mailFrom);
# get the domain name from the To mailing address
my $domain = (split('@', $mailTo))[1];

# MIME vars
my ($bh, $filename, %file, @data, $title, 
  $comments, @url, $id);
# Image::Magick vars
my ($img, $imageData, $thumbData, $err);

# loop through the file attachments
$num_parts = $entity->parts;
for ($i = 1; $i < $num_parts; $i++) {
  $part         = $entity->parts($i);
  $content_type = $part->mime_type unless 
    ($part->mime_type =~ 'text');
  $body         = $part->as_string;
  $bh           = $part->bodyhandle;
  $filename     = $bh->path;

  if (($i % 2) == 1) {
    # handle the image file
    $file{'image'} = $filename;
  }
  else {
    # handle the data file and populate 
    # the database
    $file{'data'} = $filename;

    # init the data array
    @data = ();

    # open the data file and load into 
    # data array
    open(\*FILE, "< $file{'data'}")
      or die "Error opening $file{'data'}: $!";
    while (<FILE>) {
      chomp;
      # ignore empty lines
      push @data, $_ if length > 0;
    }
    close(FILE)
      or warn "Error closing $file{'data'}: $!";

    # extract title and comments from data array
    $title = $data[0];
    shift @data;
    $comments = '';
    $comments = join ' ', @data if @data;

    # convert image to thumbnail
    $img = new Image::Magick;
    $err = $img->Read($file{'image'});
    die "Can't read image file: $err\n" if $err;
    $imageData = $img->ImageToBlob();
    $err = $img->Scale(geometry=>"200x200");
    die "Can not scale image file: $err" if $err;
    $thumbData = $img->ImageToBlob();
    
    # build, prepare and execute SQL command
    $sql = "REPLACE INTO Gallery 
      (Date, Image, Thumb, Type, Title, 
      Comments, Category)
      VALUES (NOW(), ?, ?, ?, ?, ?, ?)";
    $sth = $dbh->prepare($sql)
      or DBError("Died in prepare");
    $sth->execute($imageData, $thumbData, 
      $content_type, $title, $comments, 
      $category)
      or DBError("Died in execute");

    # get the last inserted ID to build a URL
    $sql = "SELECT ID FROM Gallery 
      WHERE ID=LAST_INSERT_ID()";
    $sth = $dbh->prepare($sql)
      or DBError("Died in prepare");
    $sth->execute()
      or DBError("Died in execute");
    $dbData = $sth->fetchrow_hashref();
    $sth->finish();
    $id = $dbData->{'ID'};

    push @url, "http://www.$domain/cgi-bin/" .
      "gallery.pl?id=$id";
  }
}

# disconnect from the database
DBDisconnect();

# remove files from data directory
$entity->purge;

# return an email message to the sender
my $mailer = Mail::Mailer->new("sendmail");
# get the number of images sent
my $count = @url;
# build the message body
my $text = "$count new image";
$text .= "s" if ($count > 1);
# build the header
$mailer->open(
  {
    Subject => "$text added to $domain gallery",
    From    => '<no-reply@' . $domain . '>',
    To      => $mailFrom
  }
);

# print the message body
$text = "The following new image";
if ($count > 1) {
  $text .= "s were";
}
else {
  $text .= " was";
}
print $mailer "$text added:\n\n";
print $mailer "$_\n" for (@url);
print $mailer "\n";

# close the message
$mailer->close();

# DB connection subroutine
sub DBConnect {
  # convert the list to a hash
  my %dbHash = @_;
  # data source name
  my $dsn = "DBI:$dbHash{'dbType'}:" .
    "$dbHash{'dbName'}:$dbHash{'dbHost'}";

  # attributes
  my %attr = (
    PrintError => 0,
    RaiseError => 1
  );
  # connection command
  $dbh = DBI->connect($dsn, $dbHash{'dbUser'}, " .
    $dbHash{'dbPass'}, \%attr) or
    DBError("Cannot connect to $dsn");
}

# DB disconnect subroutine
sub DBDisconnect {
  $dbh->disconnect()
    or DBError("Cannot disconnect from DB");
}

# DB error subroutine
sub DBError {
  my $message = shift;
  # display a message
  warn "$message\nError $DBI::err " .
    "($DBI::errstr)\n";
}

Now we've accomplished getting the images into the database. That's all well and good, but the other half of the task is displaying them on a web site. Thankfully, we just got through the longer part. There are several gallery applications available that work with databases of images. This section handles extracting three thumbnail images to be used each day on a rotating basis. Each thumbnail is linked to its full-size image, which will be displayed in a new window when the thumbnail is selected. The HTML output is presented after the Perl code.


#!/usr/bin/perl -w
use CGI;
use DBI;
use HTML::Entities;
use strict;
$|++;

# local vars
my ($id, $comments, $file, $url);

# set file name
$file = '/Library/WebServer/WebSites/' .
  'www.domain.com/include/gallery.shtml';

# get a CGI object
my $q = new CGI;

# DBI handles
my ($sth, $sql, $rv, $dbh, $dbData);

# DB connection values
my %dbHash = (
  "dbType"  => 'mysql',
  "dbName"  => 'domain',
  "dbHost"  => 'localhost',
  "dbUser"  => 'myUser',
  "dbPass"  => 'myPass',
  "dbTable" => 'Gallery'
);

# connect to the database
DBConnect(%dbHash);

# build the sql command
$sql = "SELECT ID, Comments FROM 
  $dbHash{'dbTable'} ORDER BY RAND() 
  LIMIT 3";
# prepare and execute the statement
$sth = $dbh->prepare($sql)
  or DBError("Died in prepare");
$sth->execute()
  or DBError("Died in execute");

open(\*FILE, "> $file")
  or die "Unable to open $file: $!";

while ($dbData = $sth->fetchrow_hashref) {
  $id = $dbData->{'ID'};
  $comments = $dbData->{'Comments'};
  # set HTML nbsp if no comments
  $comments = ' ' unless 
    (length($comments) > 0);
  $url = "/cgi-bin/gallery.pl?id=$id";
  # send the results to the output file
  print FILE 
    $q->td(
      {
        -align=>'center'
      },
      $q->a(
        {
          -href=>$url,
          -target=>'gallery'
        },
        $q->img(
          {
            -src=>"/cgi-bin/gallery.pl?" .
              "id=$id;thumb=1",
            -border=>0,
            -alt=>$comments
          }
        )
      ),
      $q->br(),
      $comments
    ),
    "\n";
}
$sth->finish();

close(FILE)
  or warn "Unable to close $file: $!";

# DB connection subroutine
sub DBConnect {
  # convert the list to a hash
  my %dbHash = @_;
  # data source name
  my $dsn = "DBI:$dbHash{'dbType'}:" .
    "$dbHash{'dbName'}:$dbHash{'dbHost'}";

  # attributes
  my %attr = (
    PrintError => 0,
    RaiseError => 1
  );
  # connection command
  $dbh = DBI->connect($dsn, $dbHash{'dbUser'}, " .
    $dbHash{'dbPass'}, \%attr) or
    DBError("Cannot connect to $dsn");
}

# DB disconnect subroutine
sub DBDisconnect {
  $dbh->disconnect()
    or DBError("Cannot disconnect from DB");
}

# DB error subroutine
sub DBError {
  my $message = shift;
  # display a message
  warn "$message\nError $DBI::err " .
    "($DBI::errstr)\n";
}

Here is some sample output from the above code. The output has been prettied up, but renders the same in HTML.


<td align="center">
  <a target="gallery" 
  href="/cgi-bin/gallery.pl?id=28">
    <img alt="Georgetown Railroad" border="0"
    src="/cgi-bin/gallery.pl?id=28;thumb=1" />
  </a>
  <br />
  Georgetown Railroad
</td>

<td align="center">
  <a target="gallery" 
    href="/cgi-bin/gallery.pl?id=85">
    <img alt="Lakeside morning" border="0"
    src="/cgi-bin/gallery.pl?id=85;thumb=1" />
  </a>
  <br />
  Lakeside morning
</td>

<td align="center">
  <a target="gallery" 
    href="/cgi-bin/gallery.pl?id=95">
    <img alt="Elk in stream" border="0"
    src="/cgi-bin/gallery.pl?id=95;thumb=1" />
  </a>
  <br />
  Elk in stream
</td>

The actual output looks more like this:

Georgetown Railroad
Georgetown Railroad
Lakeside morning
Lakeside morning
Elk in stream
Elk in stream

Final Thoughts

While this application is still under development, the techniques are used to show a daily sample of pictures, eCards, and photos by category. There is also a database editor application that can be used to change images from one category to another, or to change comments for an image.

Mike Schienle has been using Macs since the day they took the Lisa computer out of his office in 1984. After 10 years of kicking shell scripts around, Perl became his language of choice, although he knows his way around several others. Mike runs Custom Visuals, LLC, a small company that dabbles in internet applications, website design/hosting and database integration.


Return to the Mac DevCenter

Copyright © 2009 O'Reilly Media, Inc.