# -*- encoding: utf-8 -*-
require 'spec_helper'
describe Upsert do
  describe 'hstore on pg' do
    require 'pg_hstore'
    Pet.connection.execute 'CREATE EXTENSION HSTORE'
    Pet.connection.execute "ALTER TABLE pets ADD COLUMN crazy HSTORE"
    Pet.connection.execute "ALTER TABLE pets ADD COLUMN cool HSTORE"

    before do
      Pet.delete_all
    end

    it "works for ugly text" do
      upsert = Upsert.new $conn, :pets
      uggy = <<-EOS
{"results":[{"locations":[],"providedLocation":{"location":"3001 STRATTON WAY, MADISON, WI 53719 UNITED STATES"}}],"options":{"ignoreLatLngInput":true,"maxResults":1,"thumbMaps":false},"info":{"copyright":{"text":"© 2012 MapQuest, Inc.","imageUrl":"http://api.mqcdn.com/res/mqlogo.gif","imageAltText":"© 2012 MapQuest, Inc."},"statuscode":0,"messages":[]}}
EOS
      upsert.row({:name => 'Uggy'}, :crazy => {:uggy => uggy})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Uggy'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'uggy' => uggy }
    end

    it "just works" do
      upsert = Upsert.new $conn, :pets

      upsert.row({:name => 'Bill'}, :crazy => nil)
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      row['crazy'].should == nil

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => nil)
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      row['crazy'].should == nil

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => {:whatdat => 'whodat'})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'whatdat' => 'whodat' }

      upsert.row({:name => 'Bill'}, :crazy => {:whatdat => "D'ONOFRIO"})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'whatdat' => "D'ONOFRIO" }

      upsert.row({:name => 'Bill'}, :crazy => {:a => 2})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '2', 'whatdat' => "D'ONOFRIO" }
    end

    it "can nullify entire hstore" do
      upsert = Upsert.new $conn, :pets

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => nil)
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      row['crazy'].should == nil
    end

    it "deletes keys that are nil" do
      upsert = Upsert.new $conn, :pets

      upsert.row({:name => 'Bill'}, :crazy => nil)
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      row['crazy'].should == nil

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => {})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => {:a => nil})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == {}

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1, :b => 5})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {:a => nil})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1, :b => 5})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {:a => nil, :b => nil, :c => 12})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'c' => '12' }
    end

    it "takes dangerous keys" do
      upsert = Upsert.new $conn, :pets

      upsert.row({:name => 'Bill'}, :crazy => nil)
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      row['crazy'].should == nil

      upsert.row({:name => 'Bill'}, :crazy => {:'foo"bar' => 1})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'foo"bar' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => {})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'foo"bar' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => {:'foo"bar' => nil})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == {}

      upsert.row({:name => 'Bill'}, :crazy => {:'foo"bar' => 1, :b => 5})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'foo"bar' => '1', 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'foo"bar' => '1', 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {:'foo"bar' => nil})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {:'foo"bar' => 1, :b => 5})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'foo"bar' => '1', 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {:'foo"bar' => nil, :b => nil, :c => 12})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'c' => '12' }
    end

    it "handles multiple hstores" do
      upsert = Upsert.new $conn, :pets
      upsert.row({:name => 'Bill'}, :crazy => {:a => 1, :b => 9}, :cool => {:c => 12, :d => 19})
      row = Pet.connection.select_one(%{SELECT crazy, cool FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'b' => '9' }
      cool = PgHstore.parse row['cool']
      cool.should == { 'c' => '12', 'd' => '19' }
    end

    it "can deletes keys from multiple hstores at once" do
      upsert = Upsert.new $conn, :pets

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1}, :cool => {5 => 9})
      row = Pet.connection.select_one(%{SELECT crazy, cool FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1' }
      cool = PgHstore.parse row['cool']
      cool.should == { '5' => '9' }

      # NOOP
      upsert.row({:name => 'Bill'}, :crazy => {}, :cool => {})
      row = Pet.connection.select_one(%{SELECT crazy, cool FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1' }
      cool = PgHstore.parse row['cool']
      cool.should == { '5' => '9' }

      upsert.row({:name => 'Bill'}, :crazy => {:a => nil}, :cool => {13 => 17})
      row = Pet.connection.select_one(%{SELECT crazy, cool FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == {}
      cool = PgHstore.parse row['cool']
      cool.should == { '5' => '9', '13' => '17' }

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1, :b => 5})
      row = Pet.connection.select_one(%{SELECT crazy, cool FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'b' => '5' }

      upsert.row({:name => 'Bill'}, :crazy => {:b => nil}, :cool => {5 => nil})
      row = Pet.connection.select_one(%{SELECT crazy, cool FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == {'a' => '1'}
      cool = PgHstore.parse row['cool']
      cool.should == {'13' => '17' }
    end

    it "deletes keys whether new or existing record" do
      upsert = Upsert.new $conn, :pets

      upsert.row({:name => 'Bill'}, :crazy => {:z => 1, :x => nil})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'z' => '1' }

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'z' => '1' }
    end

    it "can turn off eager nullify" do
      upsert = Upsert.new $conn, :pets

      upsert.row({:name => 'Bill'}, {:crazy => {:z => 1, :x => nil}}, :eager_nullify => false)
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'z' => '1', 'x' => nil }

      upsert.row({:name => 'Bill'}, :crazy => {:a => 1})
      row = Pet.connection.select_one(%{SELECT crazy FROM pets WHERE name = 'Bill'})
      crazy = PgHstore.parse row['crazy']
      crazy.should == { 'a' => '1', 'z' => '1', 'x' => nil}
    end

  end
end if ENV['DB'] == 'postgresql'