require 'rubygems'
require 'json'

require 'couchdb'

employees = CouchDB.new('http://localhost:8888/employees')

# initial load of the database
data = {'departments' => {}, 'jobs' => {}, 'employees' => {}}
NULL = nil
open('Employee.sql').
  read().
  gsub(/[\s+]/, ' ').
  scan(/INSERT INTO (\w+) \((.*?)\) VALUES \((.*?)\);/m).
  each do |table, columns, values|
    values.gsub! /TO_Date\( ('.*?'),.*?\)/, '\1'
    columns = columns.strip.sub(/\w+_ID/, '_ID').downcase
    record = Hash[*columns.split(/,\s*/).zip(eval("[#{values}]")).flatten]
    if table == 'EMPLOYEES'
      # denormalize a bit :-)
      record['department'] = data['departments'][record['department_id']]
      record['job'] = data['jobs'][record['job_id']]
      employees.put(record['_id'], record)
    else
      data[table.downcase][record["_id"]] = record
    end
  end

# define a map/reduce job/view
employees.post('job_titles_by_location/', <<EOF);
  # with couchdb, this would actually be JavaScript...

  def map(id, employee)
    { employee['department']['location'] => employee['job']['job_title'] }
  end

  def reduce(dept, employee_titles)
    employee_titles.uniq.collect {|title|
      [employee_titles.select {|t| t==title}.length, title]
    }
  end
EOF

# fetch one of the view outputs
puts "\njob_titles_by_location/Seattle:"
puts employees.get('job_titles_by_location/Seattle').to_json

# update a record
puts "\nKaren gets a promotion from PU_CLERK to PU_MAN"
Karen = employees.get('119')
Karen['job_id'] = 'PU_MAN'
Karen['job'] = data['jobs']['PU_MAN']
employees.put(Karen['_id'], Karen)

# refetch the view output
puts "\njob_titles_by_location/Seattle:"
puts employees.get('job_titles_by_location/Seattle').to_json