Save Comments Inside Photos

On 7 minutes, 56 seconds read By Edward

Save Comments Inside Photos Using Exiftool

It is possible to save comments inside photos without affecting the originals. One scenario is displaying photos with the descriptions from the comments. This process also converts mysql captions into photo comments which was requested from http://www.ojambo.com/request-for-photo-gallery-software. Instead of altering the EXIF data, captions will be appended into the comment section using exiftool.

    Tools Required:

  • Command-line
  • AWK
  • BASH
  • CAT
  • ECHO
  • EXIFTOOL
  • GREP
  • SQL database
  • Knowledge of the files to be served.

Create database and database user

#!/bin/bash
# Create database photouserdb and user photouser #
echo "CREATE DATABASE IF NOT EXISTS photouserdb; GRANT ALL ON photouserdb.* TO \'photouser\'@\'localhost\'  identified by \'somepassword\';" | mysql -h localhost

Echo is used to send commands for creating a database and user called photouserdb and photouser respectively into mysql.

Create images folder and copy images

# Create image folders
mkdir Design Design/Logos Design/Buttons

# Copy Test Files
cp /media/Photos/Design/Buttons/Ojambo_button_001.png Design/Buttons/ojambo_button.png
cp /media/Photos/Design/Buttons/Ojamboshop_button_001.png Design/Buttons/ojamboshop_button.png
cp /media/Photos/Design/Logos/OJAMBODOTcom.png Design/Logos/ojambo_logo.png
cp /media/Photos/Design/Logos/OJAMBOSHOP_002.png Design/Logos/ojamboshop_logo.png

Mkdir is used to create image folders Design, Design/Logos and Design/Buttons. Cp is used to copy original photos to the created folders.

Create Database tables including data for photos and albums

cat >> table_photos.sql <<EOF
# Table structure for table 'photos' and 'albums'
#
# Copyright 2010 edward <http://ojambo.com>
#
CREATE TABLE IF NOT EXISTS photos (
	pid int(3) NOT NULL AUTO_INCREMENT,
	aid int(3) NOT NULL,
	filepath varchar(80) NOT NULL DEFAULT '',
	filename varchar(80) NOT NULL DEFAULT '',
	caption varchar(80) NOT NULL DEFAULT '',
	PRIMARY KEY (pid)
) ENGINE=INNODB AUTO_INCREMENT = 6;

CREATE TABLE IF NOT EXISTS albums (
	aid int(3) NOT NULL AUTO_INCREMENT,
	album varchar(80) NOT NULL DEFAULT '',
	description varchar(80) NOT NULL DEFAULT '',
	PRIMARY KEY (aid)
) ENGINE=INNODB AUTO_INCREMENT = 6;
EOF

cat >> data_photos.sql <<EOF
#
# Dumping data for table 'photos' and albums
#
# Copyright 2010 edward <http://ojambo.com>
#
INSERT INTO photos SET pid = '1', aid = '1', filepath='Design/Logos/', filename='ojambo_logo.png', caption = 'ojambo.com logo v1';
INSERT INTO photos SET pid = '2', aid = '1', filepath='Design/Logos/', filename='ojamboshop_logo.png', caption = 'ojamboshop.com logo v1';
INSERT INTO photos SET pid = '3', aid = '2', filepath='Design/Buttons/', filename='ojambo_button.png', caption = 'ojambo.com button v1';
INSERT INTO photos SET pid = '4', aid = '2', filepath='Design/Buttons/', filename='ojamboshop_button.png', caption = 'ojamboshop.com button v1';
# --------------------------------------------------------
#
INSERT INTO albums SET aid = '1', album = 'Logos', description = 'ojambo.com logo album';
INSERT INTO albums SET aid = '2', album = 'Buttons', description = 'ojamboshop.com logo album';
# --------------------------------------------------------
EOF

The table schema is based on Coppermine, where pid and aid are the photo id and album id respectively. Filepath is based on our created folders, and the filename is the name of the photo. Caption will be inserted into photos as a comment. Description will be appended to a simple text file.

Insert Database table sample data

# Create table for users and insert sample data
mysql -u photouser -psomepassword -h localhost photouserdb < table_photos.sql
mysql -u photouser -psomepassword -h localhost photouserdb < data_photos.sql
# Show table fields
echo "SHOW FIELDS FROM photouserdb.photos;" | mysql -u photouser -psomepassword -h localhost
echo "SHOW FIELDS FROM photouserdb.albums;" | mysql -u photouser -psomepassword -h localhost

The two sql files are directly piped into the mysql database. Echo is used to send commands for creating a database and user called photouserdb and photouser respectively into mysql.

Create Album Descriptions

# Output album description after deleting first line(field name) to text file
echo "SELECT DISTINCT albums.aid FROM photouserdb.albums LEFT JOIN photouserdb.photos ON albums.aid = photos.aid" | mysql -u photouser -psomepassword -h localhost | awk 'FNR>1'  > albums.txt

# Album Descriptions
# Loop through the text file to do something
# AWK Remove first line "FNR" and output everything else
# CAT Escape quotes by dumping into a temporary file
# BASH Append to variable from another variable
# ECHO Append new line to Comment
while read LINE;  do 
	unset copyright albumid
	copyright="Copyright (c) Edward Ojambo http://ojambo.com 2002 - 2010"
	albumid=$(echo $LINE | awk '{print $1}')
	echo "SELECT albums.album FROM photouserdb.albums WHERE albums.aid='"$albumid"'" | mysql -u photouser -psomepassword -h localhost | awk 'FNR>1'  > albumdescription.txt
	echo $LINE | awk '{print $1}' >> albumdescription.txt
	echo "SELECT albums.description FROM photouserdb.albums WHERE albums.aid='"$albumid"'" | mysql -u photouser -psomepassword -h localhost | awk 'FNR>1'  >>  albumdescription.txt
	echo >> albumdescription.txt
	cat albumdescription.txt >>album_descriptions.txt
done < albums.txt

# Add copyright notice to album descriptions
echo $copyright >>album_descriptions.txt

Mysql Distinct function is called to make sure the album id matches the album id in the photos table once. The resultant text file is looped through to create the album descriptions text file. Unset clears the variables. Awk is used to remove the first line. Copyright and albumid are variables. Since Album descriptions may have spaces, the database is queried for the full album description.

Create Batch Comments

# Output filepath after deleting first line(field name) to text file
echo "SELECT photos.aid, photos.filepath, photos.filename FROM photouserdb.photos" | mysql -u photouser -psomepassword -h localhost | awk 'FNR>1'  > photos.txt

# Create Batch Comments
# Loop through the text file to do something
# AWK Remove Everything before first delimiter ": " and first line space
# CAT Escape quotes by dumping into a temporary file
# BASH Append to variable from another variable
# ECHO Append new line to Comment
# ExifTool Comment from variable
while read LINE;  do 
	unset copyright albumid filepath filename album albumdescription comment_append comment_append2
	copyright="Copyright (c) Edward Ojambo http://ojambo.com 2002 - 2010"
	albumid=$(echo $LINE | awk '{print $1}')
	filepath=$(echo $LINE | awk '{print $2}')
	filename=$(echo $LINE |awk '{print $3}')
	echo "SELECT photos.caption FROM photouserdb.photos WHERE photos.filename='"$filename"'" | mysql -u photouser -psomepassword -h localhost | awk 'FNR>1' > caption.txt
		echo $copyright >>caption.txt	
	album=$(echo "SELECT albums.album FROM photouserdb.albums WHERE albums.aid='"$albumid"'" | mysql -u photouser -psomepassword -h localhost | awk 'FNR>1')
	echo $album >>$filepath$album.txt
	echo $filepath >>$filepath$album.txt
	echo $filename >>$filepath$album.txt
	comment_append=$(exiftool -Comment $filepath$filename | awk ' BEGIN { FS=": " } {print $2 }') 
	comment_append2=${comment_append}$(echo ""|cat - caption.txt >caption2.txt)$(cat caption2.txt)
	echo -e $comment_append$(cat caption.txt)"\n" >>$filepath$album.txt
	exiftool -Comment="$comment_append2" $filepath$filename
done < photos.txt

Mysql is called to obtain the unique photo id of every photo and appended into photos.txt. The resultant text file is looped through to save captions in photos as comments. Unset clears the variables. Awk is used to remove everything before the first delimiter. Copyright and albumid, filepath and filename are variables. Since Photo captions may have spaces, the database is queried for the full photo caption. Exiftool is used to append the caption from the database to any previous comment on the photo.

Test Comments

# Test Comments
# GREP Regular expression
identify -verbose Design/Buttons/ojamboshop_button.png |grep comment:

Identify is used to output the photo information. However grep is utilized as a regular expression “comment:” to only output everything in the comment section.

Cleanup

# Remove temporary files
rm table_photos.sql data_photos.sql photos.txt caption.txt caption2.txt albums.txt albumdescription.txt

# Remove Copied Originals
find ./ -name '*_original' | xargs rm -rf

Rm is used to remove temporary files such as the sql and caption files. Find is used to remove the original files that exiftool renamed by affixing “_original”. Xargs is used to pipe the results of find into the remove tool. The rm options of “-rf” allow deletion of folders.

    Recommendations:

  1. Remove the database user after adding the comments.
  2. Make a copy of the database and save it with the album descriptions.