-- I want to create a fancy AppleScript that will: -- - create an insert statement for the picture data to be loaded -- into my PICTURES database -- - resize the photos to a maximum width/height of 800 -- - create thumbnail images that are maximum width/height of 150 -- - automatically FTP the images and thumbnails to my website -- - automatically run the insert statement against the database -- The last two may not be able to be done, but we'll see. -- -- -- here is the table structure (* ID int(11) NOT NULL auto_increment, filepath varchar(100) NOT NULL default '', background varchar(100) default NULL, imagepath varchar(100) NOT NULL default '', width smallint(6) NOT NULL default '0', height smallint(6) NOT NULL default '0', thumbpath varchar(100) default NULL, thumb_width smallint(6) default NULL, thumb_height smallint(6) default NULL, picture_date datetime default NULL, filesize int(11) default NULL, title varchar(100) NOT NULL default '', keywords varchar(100) default NULL, comments text, collection varchar(40) default NULL, collectionID int(11) default NULL, location varchar(50) default 'Houston Texas', shutterspeed float default NULL, fstop float default NULL, zoom float default NULL, flash smallint(6) default NULL, created datetime NOT NULL default '0000-00-00 00:00:00' *) -- -- Here is a sample INSERT statement. (* INSERT INTO pictures VALUES (552, 'http://www.robreid.com/pictures/twins_200311/twins_200311-Pages/Image20.html', 'http://www.robreid.com/pictures/twins_200311/twins_200311-Images/99999.jpg', 'http://www.robreid.com/pictures/twins_200311/twins_200311-Images/20.jpg', 600, 800, 'http://www.robreid.com/pictures/twins_200311/twins_200311-Thumbnails/20.jpg', 112, 150, '2003-12-24 23:25:58', 109940, 'Christmas tree with presents', 43,NULL, NULL, 'The Reid Twins: November- December 2003', 'Houston Texas', '4', '2.8', '1', 1, '2004-01-02 15:00:19'); *) -- property insert_start : "INSERT INTO pictures VALUES (" property insert_end : ");" property html_prefix : "http://www.robreid.com/pictures/" property big_image_size : 800 property thumb_image_size : 150 property export_location : "~/Pictures/for robreid.com/to upload/warehouse/" property thumb_location : "~/Pictures/for robreid.com/to upload/thumbhouse/" property export_location2 : "~/Pictures/for\\ robreid.com/to\\ upload/warehouse/" property thumb_location2 : "~/Pictures/for\\ robreid.com/to\\ upload/thumbhouse/" try -- dates must be of the format "yyyy-mm-dd hh:mm:ss" set current_date to current date set monthList to {January, February, March, April, May, June, July, August, September, October, November, December} repeat with i from 1 to 12 if (current_date's month) = (item i of monthList) then set current_month to text -2 thru -1 of ("0" & i) exit repeat -- no point continuing once we have what we want end if end repeat -- get the day number with leading zero set current_day to text -2 thru -1 of ("0" & current_date's day) -- set the time fields set current_time to time of current_date set current_seconds to current_time mod 60 set current_seconds to text -2 thru -1 of ("0" & current_seconds) set current_time to current_time div 60 set current_minutes to current_time mod 60 set current_minutes to text -2 thru -1 of ("0" & current_minutes) set current_hours to current_time div 60 set current_minutes to text -2 thru -1 of ("0" & current_minutes) set current_date to (year of current_date & "-" & current_month & "-" & current_day &  " " & current_hours & ":" & current_minutes & ":" & current_seconds) -- display dialog image_date & ", " & current_date tell application "iPhoto" activate copy (my selected_images()) to these_images if these_images is false then  error "Please select the images to prepare for robreid.com." set the image_count to the count of these_images if (image_count > 1) then display dialog "Enter the collection name for these " & (the image_count as string) & " photos if they are part of a collection." default answer "" set image_collection to the text returned of result else set image_collection to "" set insert_collection to "" -- display dialog "Processing 1 photo." & return & return & "This will take about 30 seconds..." buttons {"¥¥¥"} default button 1 giving up after 2 end if -- add the SQL to update the PICTURE_COLLECTIONS table -- INSERT INTO picture_collections VALUES -- (NULL, 'The Reid Twins: November- December 2003', 'http://www.robreid.com/pictures/thumbnails/twins18.jpg', '2004-01-02 15:00:19', 'twins'); if (image_collection ­ "") then set insert_collection to "insert into picture_collections values " &  "(NULL,'" & image_collection & "',NULL,'" & current_date & "'," if (image_collection contains "twins") then set insert_collection to insert_collection & "'twins')" else set insert_collection to insert_collection & "NULL)" end if set the clipboard to insert_collection end if end tell if (image_collection ­ "") then tell application "Safari" activate make new document at end of documents set URL of document 1 to "http://www.robreid.com:2082/3rdparty/phpMyAdmin/index.php" end tell set dialog_text to "Go to the Public database and paste the code into the SQL window." set dialog_text to dialog_text & " Run the code and then tell me what the ID number " &  "is for the \"" & image_collection & "\" collection." display dialog dialog_text default answer "" set image_collection_id to the text returned of result set dialog_text to dialog_text & " Don't forget to to create an 80x60 thumbnail image for the \"" &  image_collection & "\", upload it, and update the record in PICTURE_COLLECTIONS to match this." display dialog dialog_text buttons "OK" display dialog "Processing " & (the image_count as string) & " photos." & return & return & "This will take about " &  ((image_count * 25 / 60) as string) & " minutes..." buttons {"¥¥¥"} default button 1 giving up after 2 else set image_collection_id to "" end if tell application "iPhoto" set the insert_statement to "" repeat with i from 1 to the image_count set this_photo to item i of these_images tell this_photo set the image_file to the image path set the image_title to the title set the image_filename to the image filename set the image_thumbname to the thumbnail filename set the image_date to the date set the image_comment to the comment set the image_width to the width as string set the image_height to the height as string set safe_filename to my replaceChar(image_filename, " ", "_") -- display dialog safe_filename -- resize the image and store it in the designated folder -- tell application "Finder" -- duplicate image_file as alias to (export_location & image_filename) as alias -- end tell -- handle spaces in the file name do shell script "cp \"" & image_file & "\" " & export_location2 & safe_filename do shell script "cp \"" & image_file & "\" " & thumb_location2 & safe_filename tell application "Image Events" launch set the target_size to 800 set macImageFile to (export_location & safe_filename) as string set this_image to open macImageFile scale this_image to size target_size save this_image close this_image saving yes end tell tell application "Image Events" launch set the target_thumb_size to 150 set macThumbFile to (thumb_location & safe_filename) as string set this_thumb to open macThumbFile scale this_thumb to size target_thumb_size -- save this_thumb close this_thumb saving yes end tell -- do shell script "/usr/local/bin/mogrify -resize 800x800 -quality 80 " & export_location2 & safe_filename -- do shell script "/usr/local/bin/mogrify -resize 150x150 -quality 70 " & thumb_location2 & safe_filename -- get the appropriate EXIF data from the pictures do shell script "jhead " & export_location2 & safe_filename set jhead_big to the result do shell script "jhead " & thumb_location2 & safe_filename set jhead_thumb to the result -- parse the jhead data to get the image dimensions, filesize, zoom, shutterspeed, and fstop set image_dimensions to my get_jhead_field(jhead_big, "Resolution") if (image_dimensions = "unknown") then set image_height to 0 set image_width to 0 else set image_height to word 3 of image_dimensions set image_width to word 1 of image_dimensions end if set image_size to my get_jhead_field(jhead_big, "File size") if (image_size = "unknown") then set image_size to 0 else set image_size to word 1 of image_size end if set image_shutter to my get_jhead_field(jhead_big, "Exposure time") if (image_shutter = "unknown") then set image_shutter to 0 else if (number of words of image_shutter < 3) then set image_shutter to (1 / (word 1 of image_shutter)) else if word 3 of image_shutter = "1" then set image_shutter to word 4 of image_shutter else set image_shutter to ((word 4 of image_shutter) / (word 3 of image_shutter)) end if end if set image_flash to my get_jhead_field(jhead_big, "Flash used") if (image_flash = "unknown") then set image_flash to "NULL" else set image_flash to word 1 of image_flash if (image_flash = "Yes") then set image_flash to "1" else set image_flash to "NULL" end if end if set image_zoom to my get_jhead_field(jhead_big, "Focal length") if (image_zoom = "unknown") then set image_zoom to 0 else set image_zoom to (((characters 1 through -3 of image_zoom) as string) / 8) end if set image_fstop to my get_jhead_field(jhead_big, "Aperture") if (image_fstop = "unknown") then set image_fstop to 0 else set image_fstop to ((characters 4 through end of image_fstop) as string) end if set thumb_dimensions to my get_jhead_field(jhead_thumb, "Resolution") if (thumb_dimensions = "unknown") then set thumb_height to 0 set thumb_width to 0 else set thumb_height to word 3 of thumb_dimensions set thumb_width to word 1 of thumb_dimensions end if -- dates must be of the format "yyyy-mm-dd hh:mm:ss" -- display dialog ("\"" & image_date & "\" \"" & word 1 of image_date & "\" \"" & word 2 of image_date & "\"") set image_date to (characters 1 through 19 of image_date) as string -- set the insert_body to my generate_insert(image_title, safe_filename, image_date, image_width, image_height,  image_comment, image_collection, image_collection_id,  image_size, image_shutter, image_flash, image_zoom, image_fstop, thumb_width, thumb_height, current_date) set the insert_statement to the insert_statement & insert_start & insert_body & insert_end & return end tell -- upload this photo -- the .netrc file needs to contain the following: -- machine robreid.com login xxx password xxx -- macdef upload_pic -- lcd "~/Pictures/for robreid.com/to upload/warehouse" -- cd public_html/pictures/warehouse -- binary -- put $1 -- lcd ../thumbhouse -- cd ../thumbhouse -- put $1 -- quit -- also, the .netrc file had to be set to "chmod 600" or it will not work do shell script "cd; echo '$upload_pic " & safe_filename & "' | ftp robreid.com " -- display dialog "Image file is at " & image_file buttons "OK" end repeat end tell set the clipboard to insert_statement (* set the target_file to ((path to temporary items folder as string) & "insert_statement.txt") as string my write_to_file(insert_statement, target_file, false) if the result is false then error "There was a problem writing the data to file." else tell application "TextEdit" activate open (target_file as alias) -- set the bounds of window 1 to {0, 22, 600, 600} end tell end if *) tell application "Safari" activate make new document at end of documents set URL of document 1 to "http://www.robreid.com:2082/3rdparty/phpMyAdmin/index.php" set dialog_text to "Go to the Public database and paste the code into the SQL window." display dialog dialog_text buttons "OK" end tell on error error_message number error_number tell application "iPhoto" activate if the error_number is not -128 then display dialog error_message buttons {"OK"} default button 1 end if end tell end try on replaceChar(inputString, fromString, toString) set numChars to the number of characters of inputString set fromLength to the number of characters of fromString set toLength to the number of characters of toString set currentCharNum to 1 set returnString to "" repeat while currentCharNum ² (number of characters of inputString) set testFrom to (characters currentCharNum thru (currentCharNum + fromLength - 1) of inputString) as string set testFrom to (testFrom as string) if (testFrom as string) = fromString then set returnString to returnString & toString set currentCharNum to currentCharNum + fromLength else set returnString to (returnString & (character currentCharNum of inputString)) as string set currentCharNum to currentCharNum + 1 end if -- display dialog (currentCharNum as string) & (testFrom as string) & (returnString as string) end repeat return returnString end replaceChar on generate_insert(image_title, image_filename, image_date, image_width, image_height, image_comment,  image_collection, image_collection_id,  image_size, image_shutter, image_flash, image_zoom, image_fstop, thumb_width, thumb_height, current_date) set the insert_body to "" set the insert_body to the insert_body & "NULL," -- ID set the insert_body to the insert_body & "NULL," -- filepath set the insert_body to the insert_body & "NULL," -- background set the insert_body to the insert_body & "'" & html_prefix & "warehouse/" & image_filename & "'," -- imagepath set the insert_body to the insert_body & image_width & "," -- width set the insert_body to the insert_body & image_height & "," -- height set the insert_body to the insert_body & "'" & html_prefix & "thumbhouse/" & image_filename & "'," -- thumbpath set the insert_body to the insert_body & thumb_width & "," -- thumb_width set the insert_body to the insert_body & thumb_height & "," -- thumb_height set the insert_body to the insert_body & "'" & image_date & "'," -- picture_date set the insert_body to the insert_body & image_size & "," -- filesize set the insert_body to the insert_body & "'" & my replaceChar(image_title, "'", "\\'") & "'," -- title set the insert_body to the insert_body & "NULL," -- keywords set the insert_body to the insert_body & "'" & my replaceChar(image_comment, "'", "\\'") & "'," -- comments set the insert_body to the insert_body & "'" & image_collection & "'," -- collection set the insert_body to the insert_body & image_collection_id & "," -- collectionID set the insert_body to the insert_body & "'Houston Texas'," -- location set the insert_body to the insert_body & image_shutter & "," -- shutterspeed set the insert_body to the insert_body & image_fstop & "," -- fstop set the insert_body to the insert_body & image_zoom & "," -- zoom set the insert_body to the insert_body & image_flash & "," -- flash set the insert_body to the insert_body & "'" & current_date & "'" -- created return the insert_body end generate_insert on get_jhead_field(jhead_text, jhead_label) -- Here is sample output from the jhead command (* File name : /Users/robreid/Pictures/for robreid.com/Archive/20040125152202.jpg File size : 1952909 bytes File date : 2004:02:01 09:42:29 Camera make : NIKON Camera model : E885 Date/Time : 2004:01:25 15:22:02 Resolution : 1536 x 2048 Flash used : No Focal length : 8.0mm Exposure time: 0.0074 s (1/135) Aperture : f/7.6 ISO equiv. : 100 Metering Mode: matrix Exposure : program (auto) Jpeg process : Baseline Comment : AppleMark *) set theBigList to {} set oldDelim to AppleScript's text item delimiters set text item delimiters to ":" set returnValue to "" repeat with i from 1 to number of paragraphs of jhead_text set theLine to paragraph i of jhead_text if (text item 1 of theLine) starts with jhead_label then set returnValue to text item 2 of theLine end if end repeat set text item delimiters to oldDelim if returnValue = "" then -- display dialog "Unable to lookup " & jhead_label set returnValue to "unknown" end if return returnValue end get_jhead_field on selected_images() tell application "iPhoto" try -- get selection set these_items to the selection -- check for single album selected if the class of item 1 of these_items is album then error -- return the list of selected photos return these_items on error return false end try end tell end selected_images on write_to_file(this_data, target_file, append_data) try set the target_file to the target_file as text set the open_target_file to  open for access file target_file with write permission if append_data is false then  set eof of the open_target_file to 0 write this_data to the open_target_file starting at eof close access the open_target_file return true on error try close access file target_file end try return false end try end write_to_file