ssearch.rb

#!/usr/bin/env ruby

require 'fileutils'
require 'optparse'
require 'sqlite3'

options = { }

OptionParser.new do |opts|
    opts.banner = "Sup search"

    opts.on("-f", "--fragment", "Search for string fragment") do |v|
        options[:fragment] = v
    end

    opts.on("-s", "--silent", "Hide messages") do |v|
        options[:silent] = v
    end
end.parse!

db_path = '/Users/John/Library/Application Support/Skype/john2496/main.db'
tmp_db_path = '/Users/John/Documents/scripts/main.db'
find_string = ARGV[0]
num_results_found = 0
user_hits = Hash.new(0)
user_aliases = { }

FileUtils.cp(db_path, tmp_db_path)

db = SQLite3::Database.open(tmp_db_path)
db.results_as_hash = true

#db.execute("SELECT * FROM main.sqlite_master WHERE type='table';") do |res|
    #print res
#end

db.execute("SELECT * FROM messages") do |row|
    message = row['body_xml'].nil? ? '' : String.new(row['body_xml'])

    result_found = false;

    if options[:fragment]
        result_found = message.downcase.include?(find_string.downcase)
    else
        result_found = message.downcase.split(' ').include?(find_string.downcase)
    end

    if result_found
        num_results_found += 1

        user_aliases[row['author']] = row['from_dispname']

        #user_hits[row['author']] ||= 0
        user_hits[row['author']] += 1

        if !options[:silent]
            date = Time.at(row['timestamp']).to_datetime.strftime("%Y-%m-%d %H:%m")
            print "#{date} #{row['author']}: #{message}\n\n"
        end
    end
end

user_hits.sort_by{|k, v| v}.reverse.each do |user, hits|
    display_name = user_aliases[user]
    print "#{display_name}: #{hits}\n"
end

print "\nResults found: #{num_results_found}\n"