One of the uses that I’ve found for AppleScript is to create automated processes to synchronize remote and local databases to keep websites up to date. I have a client who is a photographer. He prefers iView Media Pro to organize his photos locally but his website uses MySQL. Luckily, iView is very scriptable. To make updating his website practically automatic, I wrote an AppleScript that pulled the text data out of iView and triggered iView’s conversion process to create thumbnails and larger jpegs for the web. The URL Access Scripting addition that comes with the Mac operating system was used to upload the resulting images to the website via FTP. An url containing the corresponding text data targets a PHP document on the website that interacts with MySQL to log the graphics into the MySQL database that serves as the back end to the website.
First, we set up some variables for the local folderpaths and the ftp and http access.
property datafolder : ""
property imagefolder : ""
property thumbfolder : ""
set sortby to ""
set premysql to ""
if datafolder = "" then
set datafolder to choose folder with prompt "Please select your iView2MySQL folder:"
end if
set imageurl to "ftp://username:password@myurl.com/www/myfolder/image/"
set thumburl to "ftp://username:password@myurl.com/www/myfolder/preview/"
set editurl to "http:/username:password@myurl.com/otherfolder/edit.phpn="
set uploadfolder to datafolder & "logs:" as string
set imagefolder to datafolder & "images:" as string
set thumbfolder to datafolder & "thumbs:" as string
Next, I trigger handlers to make the thumbnails and jpegs for the set of records found in iView.
makegraphics("thumbnail", thumbfolder)
makegraphics("detail", imagefolder)
The makegraphics subroutine accepts one parameter that tells iView which stored settings to use to make the graphics and another parameter to indicate where to put the results:
on makegraphics(myoptions, thepath)
tell application "iView MediaPro"
convert images of catalog 1 with options myoptions saving in alias thepath
end tell
end makegraphics
Then, we count the records in iView:
tell application "iView MediaPro"
set mycount to count media item of catalog 1
end tell
Next, we get a list of every custom field in iView:
tell application "iView MediaPro"
set customfields to name of every custom field of media item 1 of catalog 1
end tell
The following code loops through the found set of records (repeat with x from 1 to mycount):
tell application "iView MediaPro"
set pictname to name of media item x of catalog 1
set thecaption to caption of annotations of media item x of catalog 1
set listkeys to keywords of annotations of media item x of catalog 1
try
set sortby to custom field "sort" of media item x of catalog 1 as string
end try
try
set premysql to custom field "MySQL" of media item x of catalog 1 as string
end try
end tell
The caption in iView will be a searchable description in the MySQL back end database. Keywords will also be searchable. Two optional custom fields will tell us the order in which the graphics should be sorted and the ID number of the existing MySQL record that corresponds to this iView record if applicable.
This text data will be sent to MySQL within a URL; therefore, all the data needs to be in string format, and certain characters are not allowed. The keywords in iView are returned as a list, so we loop through the list to make it a string:
repeat with eachkey in listkeys
if (length of keys) > 0 then
set keys to keys & "-"
end if
set keys to keys & eachkey
end repeat
Now we screen the caption, keywords, and graphic name for special characters that are not allowed in URLs.
set thecaption to screenchar(thecaption, {})
set keys to screenchar(keys, {"-"})
set thename to characters 1 through ((length of pictname) - 4) of pictname as string
set thename to screenchar(pictname, {".", "-"})
The screenchar handler accepts a string and a list of characters that should not be screened out. Rather than specifying every possible character that should be avoided in an URL, this code uses an ASCII range to determine what’s acceptable:
on screenchar(thecaption, exceptions)
set listcaption to every character of thecaption
set thecaption to ""
repeat with x in listcaption
if exceptions contains x then
set thecaption to thecaption & x
else
set asciinum to ASCII number x
if asciinum < 48 or asciinum > 122 then
set newx to "-ab" & asciinum & "-ae" as string
else
set newx to x
end if
set thecaption to thecaption & newx as string
end if
end repeat
end screenchar
Now we build the URL and send it through the URL Access Scripting addition. This scripting addition, unlike the usual scripting additions, is a faceless background application, so it is called as an application.
set upurl to editurl & thename & "&id=" & premysql & "&c=" & thecaption & "&k=" & keys & "&s=" & sortby as string
set logname to characters 1 through ((length of pictname) - 4) of pictname as string
set logpath to (uploadfolder & logname & ".txt" as string)
tell application "URL Access Scripting"
download upurl to file logpath replacing yes
end tell
The URL Access Scripting addition sends the data to a php document on the server that interacts with MySQL. The contents of the PHP document are beyond the scope of this article, but you could use any web server scripting language to create a document on the server end to respond to the data you are sending it. It just needs to be a document that can work with search arguments contained in an URL.
The URL Access Scripting addition downloads the results of its call to the specified download location. The results are the same as if you would’ve hit the URL with a web browser. I set up my PHP document to return the ID number from the newly created MySQL record so it can be stored with the record in the iView database and used to coordinate the corresponding graphics that I’m uploading. I know this number will be the first item in the file before the first space.
set fileid to open for access logpath
set MySQLid to read fileid before " "
close access fileid
I use the MySQL ID number to name the graphics, then I call handlers to upload the corresponding thumbnail and jpeg. I then try to store the ID in the corresponding iView record:
set MySQLname to MySQLid & ".jpg"
uploadgraphics(thumburl, thumbfolder, logname & ".jpg", MySQLname)
uploadgraphics(imageurl, imagefolder, logname & ".jpg", MySQLname)
tell application "iView MediaPro"
try
set custom field "MySQL" of media item x of catalog 1 to MySQLid
end try
end tell
My uploadgraphics routine uses the URL Access Scripting addition, but this time with the ftp path that I specified in my beginning variables.
on uploadgraphics(theurl, thepath, eachgraphic, newname)
set graphicpath to thepath & eachgraphic as string
set newpath to thepath & newname
tell application "Finder"
if file newpath exists then delete file newpath
set name of file graphicpath to newname
end tell
set graphicpath to thepath & newname
tell application "URL Access Scripting"
upload graphicpath to theurl replacing yes without binhexing
end tell
end uploadgraphics
That’s the end of my script. It’s a complex process that involves scripting iView, the URL Access Scripting addition, and the Finder to access the contents of the resulting file, not to mention the unspecified “black box” on the server that talks to MySQL and returns a result with a new MySQL record ID number. Hopefully, there are many bits of code contained above that you might find useful for various projects. I know many people will have questions about a system with so many aspects, but as I finish off this article, I am four days away from the due date for our first baby, so I ask that you take what you can from this article, experiment on your own, and seek other sources of information for answers to your questions.
[Questions are probably best directed to the MacScripter’s bbs while the author is on maternity leave. Editor]
Thanks, and happy scripting.