Automated Web Photo Galleries with iPhoto and Perlby Mike Schienle
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.
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.
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.
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 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:
Be sure to include the quotes around the command and the pipe before the command name.
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.
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) );
Pages: 1, 2