require File.dirname(__FILE__) + '/../spec_helper'
require 'rgviz'

include Rgviz

describe Executor do
  before :each do
    [Person, City, Country].each &:delete_all
  end

  def parse(string)
    Parser.new(string).parse
  end
  
  def exec(query)
    exec = Executor.new Person, (parse query)
    exec.execute
  end
  
  def format_datetime(date)
    date.strftime "%Y-%m-%d %H:%M:%S"
  end
  
  def format_date(date)
    date.strftime "%Y-%m-%d"
  end
  
  def self.it_processes_single_select_column(query, id, type, value)
    it "processes select #{query}" do
      if block_given?
        yield
      else
        Person.make
      end
    
      table = exec "select #{query}"
      table.cols.length.should == 1
      
      table.cols[0].id.should == id
      table.cols[0].type.should == type
      
      table.rows.length.should == 1
      table.rows[0].c.length.should == 1
      
      table.rows[0].c[0].v.should == value
    end
  end

  it "processes select *" do
    p = Person.make
    
    table = exec 'select *'
    table.cols.length.should == 7
    
    i = 0
    [['id', :number], ['name', :string], ['age', :number], ['birthday', :date],
      ['created_at', :datetime], ['updated_at', :datetime],
      ['city_id', :number]].each do |id, type|
      table.cols[i].id.should == id
      table.cols[i].type.should == type
      i += 1
    end
    
    table.rows.length.should == 1
    table.rows[0].c.length.should == 7
    
    i = 0
    [p.id, p.name, p.age, format_date(p.birthday), 
      format_datetime(p.created_at), format_datetime(p.updated_at), p.city.id].each do |val|
      table.rows[0].c[i].v.should == val
      i += 1
    end
  end

  it_processes_single_select_column 'name', 'name', :string, 'foo' do
    Person.make :name => 'foo'
  end
  
  it_processes_single_select_column '1', 'c0', :number, 1
  it_processes_single_select_column '1.2', 'c0', :number, 1.2
  it_processes_single_select_column '"hello"', 'c0', :string, 'hello'
  it_processes_single_select_column 'false', 'c0', :boolean, false
  it_processes_single_select_column 'true', 'c0', :boolean, true
  it_processes_single_select_column 'date "2010-01-02"', 'c0', :date, '2010-01-02'
  it_processes_single_select_column 'datetime "2010-01-02 10:11:12"', 'c0', :datetime, '2010-01-02 10:11:12'
  it_processes_single_select_column 'timeofday "10:11:12"', 'c0', :timeofday, '10:11:12'
  
  it_processes_single_select_column '1 + 2', 'c0', :number, 3
  it_processes_single_select_column '3 - 2', 'c0', :number, 1
  it_processes_single_select_column '2 * 3', 'c0', :number, 6
  it_processes_single_select_column '6 / 3', 'c0', :number, 2
  it_processes_single_select_column '3 * age', 'c0', :number, 60 do
    Person.make :age => 20
  end
  
  it_processes_single_select_column 'sum(age)', 'c0', :number, 6 do
    [1, 2, 3].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'avg(age)', 'c0', :number, 30 do
    [10, 20, 60].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'count(age)', 'c0', :number, 3 do
    3.times{|i| Person.make}
  end
  
  it_processes_single_select_column 'max(age)', 'c0', :number, 3 do
    [1, 2, 3].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'min(age)', 'c0', :number, 1 do
    [1, 2, 3].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'age where age > 2', 'age', :number, 3 do
    [1, 2, 3].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'age where age > 2 and age <= 3', 'age', :number, 3 do
    [1, 2, 3, 4, 5].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'name where age is null', 'name', :string, 'b' do
    Person.make :age => 1, :name => 'a'
    Person.make :age => nil, :name => 'b'
  end
  
  it_processes_single_select_column 'name where age is not null', 'name', :string, 'a' do
    Person.make :age => 1, :name => 'a'
    Person.make :age => nil, :name => 'b'
  end
  
  it "processes group by" do
    Person.make :name => 'one', :age => 1
    Person.make :name => 'one', :age => 2
    Person.make :name => 'two', :age => 3
    Person.make :name => 'two', :age => 4
  
    table = exec 'select max(age) group by name'
    
    table.rows.length.should == 2
    table.rows[0].c.length.should == 1
    table.rows[0].c[0].v.should == 2
    table.rows[1].c.length.should == 1
    table.rows[1].c[0].v.should == 4
  end
  
  it "processes order by" do
    Person.make :age => 1
    Person.make :age => 3
    Person.make :age => 2
  
    table = exec 'select age order by age desc'
    
    table.rows.length.should == 3
    table.rows[0].c.length.should == 1
    table.rows[0].c[0].v.should == 3
    table.rows[1].c.length.should == 1
    table.rows[1].c[0].v.should == 2
    table.rows[2].c.length.should == 1
    table.rows[2].c[0].v.should == 1
  end
  
  it_processes_single_select_column 'age where age > 3 order by age limit 1', 'age', :number, 4 do
    [1, 2, 3, 4, 5].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'age where age > 3 order by age limit 1 offset 1', 'age', :number, 5 do
    [1, 2, 3, 4, 5].each{|i| Person.make :age => i}
  end
  
  it_processes_single_select_column 'city_name', 'city_name', :string, 'Buenos Aires' do
    Person.make :city => City.make(:name => 'Buenos Aires') 
  end
  
  it_processes_single_select_column 'city_country_name', 'city_country_name', :string, 'Argentina' do
    Person.make :city => City.make(:country => Country.make(:name => 'Argentina')) 
  end
  
  it_processes_single_select_column 'city_country_name group by city_country_name', 'city_country_name', :string, 'Argentina' do
    Person.make :city => City.make(:country => Country.make(:name => 'Argentina')) 
  end
  
end