#! /usr/bin/env ruby

## Aperture Duplicate Finder GUI Edition ##
## Does *not* require SQLite3 Vr. > 3.4  ##
## Writes into ZRESERVED column in ZRKVERSION ##

require 'digest'
require 'open3'
require 'pp'

$TERMINAL_MODE = true
$DEBUG = false
$REINDEXALL = true
$STACK = true
$UNSTACK = true

class DuplicateFinder

def initialize
  @aperture = Aperture.new
  @run_ok = @aperture.album_zpk != 0 ? true : false
end

def start
  main() if @run_ok
end

def main

  clean_slate() if $REINDEXALL

  pics_to_index = @aperture.get_unindexed_pics()

  generate_hashes(pics_to_index) unless pics_to_index.empty?

  dupes, dupe_groups = @aperture.get_duplicates()

  pp dupes if $DEBUG
  pp dupe_groups if $DEBUG

  find_duplicates()

end

def generate_hashes(pics_to_index)

  sha1_update_all = MultilineQuery.new

  begin

  pics_to_index.each_with_index do |row,i|

        path = (@aperture.libpath + row.last).gsub(/\\/,'')
        open(path, "r") { |f| sha1_update_all.newline("update ZRKVERSION set ZRESERVED = '#{Digest::SHA1.hexdigest(f.read)}' where Z_PK = #{row.first}") }

        @aperture.send(sha1_update_all.commit) if (i%300).zero?
        sha1_update_all = MultilineQuery.new if (i%300).zero?

        if $TERMINAL_MODE
          puts `clear`
          puts "#{pics_to_index.size} will be indexed."
          puts "Indexed #{i+1} | #{File.basename(path)}"
        end

  end

  @aperture.send(sha1_update_all.commit)

  rescue => e
    @aperture.send(sha1_update_all.commit)
  end

end

def find_duplicates

  dupes, dupe_groups = @aperture.get_duplicates()

  if $TERMINAL_MODE
    puts `clear`
    puts "Found #{dupes.size} duplicates in #{dupe_groups.size} groups."
  end

  albumstack_update, count = MultilineQuery.new, 0

  dupe_groups.each_with_index do |checksum,i|

      x = dupes.find_all { |k,v| v == checksum }.map! { |k| k.first.to_i }.sort!

      pp x if $DEBUG

      stackid = Digest::MD5.hexdigest(Time.now.to_f.to_s)

      if $STACK
        albumstack_update.newline("update ZRKVERSION set ZSTACKID = '#{stackid}' where ZRESERVED = '#{checksum}'")
      elsif $UNSTACK
        albumstack_update.newline("update ZRKVERSION set ZSTACKID = null where ZRESERVED = '#{checksum}'")
      end

      x.each_with_index do |id,i|
          albumstack_update.newline("update ZRKVERSION set ZSTACKINDEX = #{i} where Z_PK = #{id}") if $STACK
          albumstack_update.newline("insert into Z_11VERSIONS values (#{@aperture.album_zpk},#{id})")
          albumstack_update.newline("update ZRKVERSION set ZMAINRATING = -1 where Z_PK = #{id}") if i != 0
          count += 1
      end

  end

  albumstack_update.newline("update ZRKPERSISTENTALBUM set ZVERSIONCOUNT = #{count} where Z_PK = #{@aperture.album_zpk}")

  pp albumstack_update.commit if $DEBUG

  @aperture.send_by_file(albumstack_update.commit)

end

def clean_slate
  @aperture.send("update ZRKVERSION set ZRESERVED = null where Z_PK in (select Z_PK from ZRKVERSION)")
end

end

class Aperture

  attr_reader :libpath, :dbpath, :album_zpk

  def initialize
    @libpath, @dbpath = find_library()
    @album_zpk = create_new_album()
  end
  
  def get_unindexed_pics
    ids = parse_for_requery(send('select Z_PK from ZRKVERSION where ZRESERVED is null'))
    query = "select V.Z_PK,Path||'/'||ImportGroup||'/'||FolderName||'/'||FileName as FullPath from ZRKVERSION V join (select ZUUID, ZLIBRARYRELATIVEPATH as Path from ZRKFOLDER) b on (b.ZUUID = V.ZPROJECTUUID) join (select Z_PK, ZNAME as FolderName, ZIMPORTGROUP as ImportGroup from ZRKMASTER) c on (c.Z_PK = V.ZMASTER) join (select Z_PK,ZNAME as Filename, ZCHECKSUM as CheckSum from ZRKFILE) d on (d.Z_PK = V.ZFILE) where V.Z_PK in (#{ids})"
    parse_results(send(query))
  end

  def get_duplicates
    e = parse_results(send("begin; select Z_PK,ZRESERVED from ZRKVERSION where ZRESERVED in (select ZRESERVED from ZRKVERSION group by ZRESERVED having count(ZRESERVED) > 1); select ZRESERVED from ZRKVERSION group by ZRESERVED having count(ZRESERVED) > 1; commit;"))
    a,b = e.partition { |x| x.size == 2 }
    return a.inject({}) { |h,k| h.update k.first => k.last }, b.flatten
  end

  def send(msg)
    unless msg =~ /^\//
      %x|sqlite3 -list -separator ':::' #{@dbpath} "#{msg}"|
    else
      Open3.popen3("sqlite3 -init #{msg} #{@dbpath}")
    end
  end

  def send_by_file(msg)
    sqldump = ENV['HOME'] + "/Library/Caches/" + Digest::MD5.hexdigest(Time.now.to_f.to_s)
    File.open(sqldump,'w') { |f| f << msg }
    send(sqldump)
  end

  private

  def find_library
    aperture_plist = ENV['HOME'] + '/Library/Preferences/com.apple.Aperture.plist'
    plistdump = ENV['HOME'] + '/Library/Caches/' + 'plistdump.tmp'
    `plutil -convert xml1 -o #{plistdump} #{aperture_plist}`
    relative_lib_path = File.open(plistdump).grep(/aplibrary/).first.match(/>(.+)</).to_a[1]
    fullpath = relative_lib_path =~ /^~/ ? relative_lib_path.gsub(/^~/, ENV['HOME']) : relative_lib_path
    `rm #{plistdump}`
    puts "The current active library resides at " + fullpath if $TERMINAL_MODE
    return fullpath.gsub(/ /,'\ ') + "/", (fullpath + "/Aperture.aplib/Library.apdb").gsub(/ /,'\ ')
  end

  def create_new_album
    name = "Duplicates | " << Time.now.asctime << '.apalbum'
    zuuid = Digest::MD5.hexdigest(Time.now.to_f.to_s)
    create_album = "insert into ZRKPERSISTENTALBUM (Z_ENT,Z_OPT,ZDATELASTSAVEDINDATABASE,ZMETADATACHANGEDATE,ZCREATEDATE,ZNAME,ZUUID,ZVERSIONCOUNT,ZISMISSING,ZALBUMSUBCLASS,ZALBUMTYPE,ZFOLDER,Z5_FOLDER) values (11,2,209322883.588766,209322883.587464,209322875.604068,'#{name}','#{zuuid}',0,0,3,1,1,5)"
    send(create_album)
    return send("select Z_PK from ZRKPERSISTENTALBUM where ZNAME = '#{name}'")
  end

  def parse_for_requery(msg)
     msg.split("\n").map! { |e| "'" + e + "'" }.join(',')
  end

  def parse_results(query)
    query.split("\n").map! { |e| e.split(":::") }
  end

end

class MultilineQuery

  attr_reader :query

  def initialize
    @query = "begin; "
  end

  def newline(line)
    @query << " " << line << ";"
  end

  def commit
    newline("commit")
    return @query
  end

end

finder = DuplicateFinder.new

finder.start()

UPDATED WED Aug 15/07

PLEASE NOTE: requires the following modules "active_record", "rbosa" and "hpricot" and my own Imageshack API to work.

USAGE:

You can include/require this as a module in your own scripts, or as I do, use it directly from irb in the Terminal. Documentation is provided within the script itself, but essentially I use it to get these things done:

With Aperture launched, select the pictures of your choice (one or multiples makes no diff), then entering the following inside irb results in:

1. Aperture.reveal is essentially "Reveal in Finder"

2. Aperture.paths returns the Unix paths to the locations of the pics.

3. Aperture.open opens the pictures with an external default app (usually Preview).

4. If you provide the script with Photoshop's path, then:

Aperture.edit will open the original file directly in Photoshop without creating a separate version inside Aperture.

5. Aperture.ids will give you an array of the unique ID asssigned by Aperture to each pic within the ZRKVERSION table. What this is for is up to you to find out.

6. NEW: Imageshack.us upload/backup

Aperture.upload will upload the pic(s) to Imageshack.us and return the direct link/URL of the pic.

Upon first launch, a new metadata field is created inside Aperture's metadata panel called "Imageshack URL" (if you don't see it, inside Aperture, Ctrl-D --> Others). the uploaded pic's URL will be stored inside this field automatically.

The API directly interrogates Aperture's SQLite DB. Who knows, could be a nice place to start from to building other stuff.

require 'rubygems'
require 'active_record'
require 'rbosa'
require 'hpricot'
require 'open3'
require 'beckett/imageshack'

# --- GLOBALS --- #

HOME = ENV['HOME']
PATH_TO_PHOTOSHOP = "/Applications/Adobe\\ Photoshop\\ CS3/Adobe\\ Photoshop\\ CS3.app/Contents/MacOS/Adobe\\ Photoshop\\ CS3"

Aperture = OSA.app('Aperture')

# --- CONNECT TO CURRENT LIBRARY --- #

class LibraryConnect

  def initialize
    findLibrary
    connect unless $PATH_TO_DB.nil?
  end

  protected

  def findLibrary
  
  aperture_plist = HOME + '/Library/Preferences/com.apple.Aperture.plist'
  plistdump = HOME + '/Library/Caches/' + 'plistdump.tmp'
  plutil_error = Open3.popen3("plutil -convert xml1 -o #{plistdump} #{aperture_plist}") { |stdin,stdout,stderr| stderr.read }

  if plutil_error == ""
      doc = Hpricot(File.open(plistdump)) ; %x( rm #{plistdump} )
      relative_lib_path = (doc/:key).select { |key| key.inner_text == 'LibraryPath' }[0].next_sibling.inner_text
      $PATH_TO_LIB = relative_lib_path =~ /^~/ ? relative_lib_path.gsub(/^~/, HOME) : relative_lib_path
      $PATH_TO_DB = "#{$PATH_TO_LIB}/Aperture.aplib/Library.apdb"
      puts "Current library --> " + $PATH_TO_LIB
  else
      $PATH_TO_DB = nil
      puts "Cannot find library. Goodbye."
  end

  end

  def connect
    ActiveRecord::Base.establish_connection(
      :adapter => "sqlite3",
      :dbfile  => $PATH_TO_DB
    )
  end

end

# --- LIBRARY CLASSES-SQL TABLES MAPPINGS --- #

class Pictures < ActiveRecord::Base         # Contains the majority of non-metadata info on a picture
  set_table_name "ZRKVERSION"
  set_primary_key "ZUUID"
end

class Projects < ActiveRecord::Base         # Every pic only belongs to one project
  set_table_name "ZRKFOLDER"
  set_primary_key "ZUUID"
end

class Masters < ActiveRecord::Base          # Info on master images
  set_table_name "ZRKMASTER"
  set_primary_key "Z_PK"
end

class Files < ActiveRecord::Base            # Info on the actual image file 
  set_table_name "ZRKFILE"
  set_primary_key "Z_PK"
end

class Metadata < ActiveRecord::Base         # Metadata information tagged to every picture in library
  set_table_name "ZRKSEARCHABLEPROPERTY"
  set_primary_key "Z_PK"
end

class Albums < ActiveRecord::Base           # All albums within library
  set_table_name "ZRKPERSISTENTALBUM"
  set_primary_key "Z_PK"
end

class AlbumVersions < ActiveRecord::Base    # Foreign key that links a picture's Z_PK (from ZRKVERSION) to an
  set_table_name "Z_11VERSIONS"             # album's Z_PK in ZRKPERSISTENTALBUM
end

class Properties < ActiveRecord::Base       # Metadata categories
  set_table_name "ZRKPROPERTYIDENTIFIER"
  set_primary_key "Z_PK"
end

class Picture                               # Custom class that contains all relevant info on a picture

attr_reader :id, :project_id, :master_id, :file_id, :version_id, :width, :height, :filesize, :name, :moddate

def initialize(pic_id)

  # Query ZRKVERSION
  
  @id = pic_id
  pic = Pictures.find(pic_id)
  @project_id = pic.ZPROJECTUUID
  @master_id = pic.ZMASTER
  @file_id = pic.ZFILE
  @version_id = pic.Z_PK

  # Query ZRKFILE

  file = Files.find(@file_id)
  @width = pic.ZPIXELWIDTH
  @height = pic.ZPIXELHEIGHT
  @filesize = file.ZFILESIZE
  @name = file.ZNAME
  @moddate = realtime(file.ZFILEMODIFICATIONDATE_before_type_cast)

end

def filepath
    path = $PATH_TO_LIB + '/' + self.projectPath + '/' + self.importGroup + '/' + self.folderName + '/' + self.filename
end

def nixpath
    self.filepath.gsub(/ /,'\ ')
end

def open
  `open #{self.nixpath}`
end

def edit
  if PATH_TO_PHOTOSHOP != ""
    `open -a #{$PATH_TO_PHOTOSHOP} #{self.nixpath}`
  else
    `open #{self.nixpath}`
  end
end

def reveal
    puts nixpath
    `open #{File.dirname(self.nixpath)}`
end

def shack_uri
  pic_meta = Metadata.find( :first, :conditions => { :ZVERSION => @version_id, :ZPROPERTYIDENTIFIER => $SHACKURLPROP } )
  unless pic_meta == nil
    pic_meta.ZPROPERTYSPECIFICSTRING
  else
    return 'Does not exist.'
  end
end

def storeURI(url)
records = Metadata.find( :all, :conditions => { :ZVERSION => @version_id, :ZPROPERTYIDENTIFIER => $SHACKURLPROP } )
if  records.empty? == true
    new_uri = Metadata.create(
        :Z_ENT => 13,
        :Z_OPT => 1,
        :ZPROPERTYSPECIFICNUMBER =>  nil,
        :ZPROPERTYSPECIFICSTRING => url,
        :ZVALUETYPE => 3,
        :ZVERSION => @version_id,
        :ZPROPERTYIDENTIFIER => $SHACKURLPROP )
  new_uri.Z_PK
else
  records.each { |entry| Metadata.destroy(entry.Z_PK) }
  self.storeURI(url)
end

end

def realtime(sqtime)
  t = Time.at(sqtime.to_i + 978307200)      # => # Derives from: Time.utc(2001,"jan",1,0,0,0).to_i
  Time.utc(t.year,t.month,t.day,t.hour,t.min)
end

protected

def projectPath
    Projects.find(@project_id).ZLIBRARYRELATIVEPATH
end

def importGroup
   Masters.find(@master_id).ZIMPORTGROUP
end

def folderName
   Masters.find(@master_id).ZNAME
end

def filename
  Files.find(@file_id).ZNAME
end

end

class Album

def initialize(zpk)
  @album = Albums.find(zpk)
end

def add_count(n=0)
  @album.update_attributes(   :ZDATELASTSAVEDINDATABASE => (Time.now.to_f + 978307200.0),
                              :ZMETADATACHANGEDATE => (Time.now.to_f + 978307200.0),
                              :ZVERSIONCOUNT => @album.ZVERSIONCOUNT + n,
                              :ZCREATEDATE => @album.ZCREATEDATE_before_type_cast.to_f )
end

end

# --- INTERACTING WITH APERTURE FROM IRB --- #
# All operations are performed recursively on image(s) within the array returned by Aperture

class OSA::Aperture::Application

def new_prop(fieldName)

Properties.create(  :Z_ENT => 12,
                    :Z_OPT => 1,
                    :ZPROPERTYKEY => fieldName,
                    :ZPROPERTYTYPE => 7)
                    
return Properties.find_by_ZPROPERTYKEY(fieldName).Z_PK

end

def ids     # Returns the IDs used to query the ZRKVERSION table
  self.selection.collect { |pic| pic.id2 }
end

def paths   # Returns the filepaths of selected images
  self.ids.collect { |id| Picture.new(id).filepath }
end

def reveal  # 'Reveal in Finder' selected images
  self.ids.each { |id| Picture.new(id).reveal }
end

def open    # Opens selected images in Preview
  self.ids.each { |id| Picture.new(id).open }
end

def edit    # Opens the original file in Photoshop CS3 (if path provided or default)
  self.ids.each { |id| Picture.new(id).edit }
end

def upload(reveal=false)  # Uploads the image to Imageshack and returns the direct link to the pic, if called with true, then opens it in browser after upload

  self.ids.each do |id|
    pic = Picture.new(id)
    pic_mirror = ShackMirror.new(pic.filepath)
    pic.storeURI(pic_mirror.url)
    `open #{pic_mirror.url}` if reveal
    puts pic_mirror.url
  end

end

def print(name)
  self.ids.each { |id| puts Picture.new(id).send(name) }
end

end

# --- INITIALIZE --- #

LibraryConnect.new

$SHACKURLPROP = Properties.find_by_ZPROPERTYKEY("Imageshack URL") == nil ? Aperture.new_prop("Imageshack URL") : Properties.find_by_ZPROPERTYKEY("Imageshack URL").Z_PK