<commit_before>require File.join(File.dirname(File.expand_path(__FILE__)), 'spec_helper.rb')

describe "Class Table Inheritance Plugin" do
  before(:all) do
    @db = DB
    @db.instance_variable_set(:@schemas, {})
    @db.drop_table?(:staff, :executives, :managers, :employees)
    @db.create_table(:employees) do
      primary_key :id
      String :name
      String :kind
    end
    @db.create_table(:managers) do
      foreign_key :id, :employees, :primary_key=>true
      Integer :num_staff
    end
    @db.create_table(:executives) do
      foreign_key :id, :managers, :primary_key=>true
      Integer :num_managers
    end
    @db.create_table(:staff) do
      foreign_key :id, :employees, :primary_key=>true
      foreign_key :manager_id, :managers
    end
  end
  before do
    [:staff, :executives, :managers, :employees].each{|t| @db[t].delete}
    class ::Employee < Sequel::Model(@db)
      plugin :class_table_inheritance, :key=>:kind, :table_map=>{:Staff=>:staff}
    end 
    class ::Manager < Employee
      one_to_many :staff_members, :class=>:Staff
    end 
    class ::Executive < Manager
    end 
    class ::Staff < Employee
      many_to_one :manager
    end 
    
    @i1 =@db[:employees].insert(:name=>'E', :kind=>'Employee')
    @i2 = @db[:employees].insert(:name=>'S', :kind=>'Staff')
    @i3 = @db[:employees].insert(:name=>'M', :kind=>'Manager')
    @i4 = @db[:employees].insert(:name=>'Ex', :kind=>'Executive')
    @db[:managers].insert(:id=>@i3, :num_staff=>7)
    @db[:managers].insert(:id=>@i4, :num_staff=>5)
    @db[:executives].insert(:id=>@i4, :num_managers=>6)
    @db[:staff].insert(:id=>@i2, :manager_id=>@i4)
  end
  after do
    [:Executive, :Manager, :Staff, :Employee].each{|s| Object.send(:remove_const, s)}
  end
  after(:all) do
    @db.drop_table? :staff, :executives, :managers, :employees
  end

  specify "should return rows as subclass instances" do
    Employee.order(:id).all.should == [
      Employee.load(:id=>@i1, :name=>'E', :kind=>'Employee'),
      Staff.load(:id=>@i2, :name=>'S', :kind=>'Staff'),
      Manager.load(:id=>@i3, :name=>'M', :kind=>'Manager'),
      Executive.load(:id=>@i4, :name=>'Ex', :kind=>'Executive')
    ]
  end
  
  specify "should lazily load columns in subclass tables" do
    Employee[@i2][:manager_id].should == nil
    Employee[@i2].manager_id.should == @i4
    Employee[@i3][:num_staff].should == nil
    Employee[@i3].num_staff.should == 7
    Employee[@i4][:num_staff].should == nil
    Employee[@i4].num_staff.should == 5
    Employee[@i4][:num_managers].should == nil
    Employee[@i4].num_managers.should == 6
  end
  
  specify "should eagerly load columns in subclass tables when retrieving multiple objects" do
    a = Employee.order(:id).all
    a[1][:manager_id].should == nil
    a[1].manager_id.should == @i4
    a[2][:num_staff].should == nil
    a[2].num_staff.should == 7
    a[3][:num_staff].should == 5 # eagerly loaded by previous call
    a[3].num_staff.should == 5
    a[3][:num_managers].should == nil
    a[3].num_managers.should == 6
  end
  
  specify "should include schema for columns for tables for ancestor classes" do
    Employee.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :name]
    Staff.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :manager_id, :name]
    Manager.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :name, :num_staff]
    Executive.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :name, :num_managers, :num_staff]
  end
  
  specify "should include columns for tables for ancestor classes" do
    Employee.columns.should == [:id, :name, :kind]
    Staff.columns.should == [:id, :name, :kind, :manager_id]
    Manager.columns.should == [:id, :name, :kind, :num_staff]
    Executive.columns.should == [:id, :name, :kind, :num_staff, :num_managers]
  end
  
  specify "should delete rows from all tables" do
    e = Executive.first
    i = e.id
    e.staff_members_dataset.destroy
    e.destroy
    @db[:executives][:id=>i].should == nil
    @db[:managers][:id=>i].should == nil
    @db[:employees][:id=>i].should == nil
  end
  
  specify "should handle associations only defined in subclasses" do
    Employee.filter(:employees__id=>@i2).all.first.manager.id.should == @i4
  end

  specify "should insert rows into all tables" do
    e = Executive.create(:name=>'Ex2', :num_managers=>8, :num_staff=>9)
    i = e.id
    @db[:employees][:id=>i].should == {:id=>i, :name=>'Ex2', :kind=>'Executive'}
    @db[:managers][:id=>i].should == {:id=>i, :num_staff=>9}
    @db[:executives][:id=>i].should == {:id=>i, :num_managers=>8}
  end
  
  specify "should update rows in all tables" do
    Executive.first.update(:name=>'Ex2', :num_managers=>8, :num_staff=>9)
    @db[:employees][:id=>@i4].should == {:id=>@i4, :name=>'Ex2', :kind=>'Executive'}
    @db[:managers][:id=>@i4].should == {:id=>@i4, :num_staff=>9}
    @db[:executives][:id=>@i4].should == {:id=>@i4, :num_managers=>8}
  end
  
  specify "should handle many_to_one relationships" do
    m = Staff.first.manager
    m.should == Manager[@i4]
    m.should be_a_kind_of(Executive)
  end
  
  specify "should handle eagerly loading many_to_one relationships" do
    Staff.limit(1).eager(:manager).all.map{|x| x.manager}.should == [Manager[@i4]]
  end
  
  specify "should handle eagerly graphing many_to_one relationships" do
    ss = Staff.eager_graph(:manager).all
    ss.should == [Staff[@i2]]
    ss.map{|x| x.manager}.should == [Manager[@i4]]
  end
  
  specify "should handle one_to_many relationships" do
    Executive.first.staff_members.should == [Staff[@i2]]
  end
  
  specify "should handle eagerly loading one_to_many relationships" do
    Executive.limit(1).eager(:staff_members).first.staff_members.should == [Staff[@i2]]
  end
  
  specify "should handle eagerly graphing one_to_many relationships" do
    es = Executive.limit(1).eager_graph(:staff_members).all
    es.should == [Executive[@i4]]
    es.map{|x| x.staff_members}.should == [[Staff[@i2]]]
  end
end

describe "Many Through Many Plugin" do
  before(:all) do
    @db = DB
    @db.instance_variable_set(:@schemas, {})
    @db.drop_table?(:albums_artists, :albums, :artists)
    @db.create_table(:albums) do
      primary_key :id
      String :name
    end
    @db.create_table(:artists) do
      primary_key :id
      String :name
    end
    @db.create_table(:albums_artists) do
      foreign_key :album_id, :albums
      foreign_key :artist_id, :artists
    end
  end
  before do
    [:albums_artists, :albums, :artists].each{|t| @db[t].delete}
    class ::Album < Sequel::Model(@db)
      many_to_many :artists
    end 
    class ::Artist < Sequel::Model(@db)
      plugin :many_through_many
    end 
    
    @artist1 = Artist.create(:name=>'1')
    @artist2 = Artist.create(:name=>'2')
    @artist3 = Artist.create(:name=>'3')
    @artist4 = Artist.create(:name=>'4')
    @album1 = Album.create(:name=>'A')
    @album1.add_artist(@artist1)
    @album1.add_artist(@artist2)
    @album2 = Album.create(:name=>'B')
    @album2.add_artist(@artist3)
    @album2.add_artist(@artist4)
    @album3 = Album.create(:name=>'C')
    @album3.add_artist(@artist2)
    @album3.add_artist(@artist3)
    @album4 = Album.create(:name=>'D')
    @album4.add_artist(@artist1)
    @album4.add_artist(@artist4)
  end
  after do
    [:Album, :Artist].each{|s| Object.send(:remove_const, s)}
  end
  after(:all) do
    @db.drop_table? :albums_artists, :albums, :artists
  end
  
  def self_join(c)
    c.join(Sequel.as(c.table_name, :b), Array(c.primary_key).zip(Array(c.primary_key))).select_all(c.table_name)
  end

  specify "should handle super simple case with 1 join table" do
    Artist.many_through_many :albums, [[:albums_artists, :artist_id, :album_id]]
    Artist[<EMAIL>1.id].albums.map{|x| x.name}.sort.should == %w'A D'
    Artist[<EMAIL>2.id].albums.map{|x| x.name}.sort.should == %w'A C'
    Artist[<EMAIL>3.id].albums.map{|x| x.name}.sort.should == %w'B C'
    Artist[@<EMAIL>4.id].albums.map{|x| x.name}.sort.should == %w'B D'
    
    Artist.plugin :prepared_statements_associations
    Artist[<EMAIL>].albums.map{|x| x.name}.sort.should == %w'A D'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'A C'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'B C'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'B D'

    Artist.filter(:id=>1).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A D'
    Artist.filter(:id=>2).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A C'
    Artist.filter(:id=>3).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B C'
    Artist.filter(:id=>4).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B D'
    
    Artist.filter(:artists__id=>1).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A D'
    Artist.filter(:artists__id=>2).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A C'
    Artist.filter(:artists__id=>3).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B C'
    Artist.filter(:artists__id=>4).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B D'

    Artist.filter(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2'
    Artist.filter(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'3 4'
    Artist.filter(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'2 3'
    Artist.filter(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'1 4'

    Artist.exclude(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'3 4'
    Artist.exclude(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2'
    Artist.exclude(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 4'
    Artist.exclude(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3'

    Artist.filter(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'1 3 4'

    Artist.exclude(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'2'

    Artist.filter(:albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.exclude(:albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'

    c = self_join(Artist)
    c.filter(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2'
    c.filter(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'3 4'
    c.filter(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'2 3'
    c.filter(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'1 4'

    c.exclude(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'3 4'
    c.exclude(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2'
    c.exclude(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 4'
    c.exclude(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3'

    c.filter(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'1 3 4'

    c.exclude(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'2'

    c.filter(:albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.exclude(:albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'
  end

  cspecify "should handle typical case with 3 join tables", :fdbsql do
    Artist.many_through_many :related_artists, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_artists, :album_id, :artist_id]], :class=>Artist, :distinct=>true
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 4'
    Artist[@<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 3'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'2 3 4'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 3 4'
    
    Artist.plugin :prepared_statements_associations
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 4'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 3'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'2 3 4'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 3 4'
    
    Artist.filter(:id=>@artist1.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 4'
    Artist.filter(:id=>@artist2.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 3'
    Artist.filter(:id=>@artist3.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'2 3 4'
    Artist.filter(:id=>@artist4.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 3 4'
    
    Artist.filter(:artists__id=>@artist1.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 4'
    Artist.filter(:artists__id=>@artist2.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 3'
    Artist.filter(:artists__id=>@artist3.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'2 3 4'
    Artist.filter(:artists__id=>@artist4.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 3 4'

    Artist.filter(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'1 2 4'
    Artist.filter(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'2 3 4'
    Artist.filter(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'1 3 4'

    Artist.exclude(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'3'
    Artist.exclude(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'1'
    Artist.exclude(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'2'

    Artist.filter(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    Artist.exclude(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w''

    Artist.filter(:related_artists=>Artist.filter(:id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'1 2 4'
    Artist.exclude(:related_artists=>Artist.filter(:id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'3'

    c = self_join(Artist)
    c.filter(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'1 2 4'
    c.filter(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'2 3 4'
    c.filter(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'1 3 4'

    c.exclude(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'3'
    c.exclude(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'1'
    c.exclude(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'2'

    c.filter(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    c.exclude(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w''

    c.filter(:related_artists=>c.filter(:artists__id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'1 2 4'
    c.exclude(:related_artists=>c.filter(:artists__id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'3'
  end

  cspecify "should handle extreme case with 5 join tables", :fdbsql do
    Artist.many_through_many :related_albums, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_artists, :album_id, :artist_id], [:artists, :id, :id], [:albums_artists, :artist_id, :album_id]], :class=>Album, :distinct=>true
    @db[:albums_artists].delete
    @album1.add_artist(@artist1)
    @album1.add_artist(@artist2)
    @album2.add_artist(@artist2)
    @album2.add_artist(@artist3)
    @album3.add_artist(@artist1)
    @album4.add_artist(@artist3)
    @album4.add_artist(@artist4)
    
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B C'
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B C D'
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B D'
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'B D'
    
    Artist.plugin :prepared_statements_associations
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B C'
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B C D'
    Artist[<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B D'
    Artist[<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'B D'
    
    Artist.filter(:id=>@artist1.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C'
    Artist.filter(:id=>@artist2.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C D'
    Artist.filter(:id=>@artist3.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B D'
    Artist.filter(:id=>@artist4.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'B D'
    
    Artist.filter(:artists__id=>@artist1.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C'
    Artist.filter(:artists__id=>@artist2.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C D'
    Artist.filter(:artists__id=>@artist3.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B D'
    Artist.filter(:artists__id=>@artist4.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'B D'

    Artist.filter(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    Artist.filter(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 2'
    Artist.filter(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3 4'

    Artist.exclude(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w''
    Artist.exclude(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'3 4'
    Artist.exclude(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'1'

    Artist.filter(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:related_albums=>[@album3, @album4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'

    Artist.exclude(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:related_albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w''

    Artist.filter(:related_albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.exclude(:related_albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'

    c = self_join(Artist)
    c.filter(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    c.filter(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 2'
    c.filter(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3 4'

    c.exclude(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w''
    c.exclude(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'3 4'
    c.exclude(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'1'

    c.filter(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:related_albums=>[@album3, @album4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'

    c.exclude(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:related_albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w''

    c.filter(:related_albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.exclude(:related_albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'
  end
end

describe "Lazy Attributes plugin" do 
  before(:all) do
    @db = DB
    @db.create_table!(:items) do
      primary_key :id
      String :name
      Integer :num
    end
    @db[:items].delete
    class ::Item < Sequel::Model(@db)
      plugin :lazy_attributes, :num
    end
    Item.create(:name=>'J', :num=>1)
  end
  after(:all) do
    @db.drop_table?(:items)
    Object.send(:remove_const, :Item)
  end
  
  specify "should not include lazy attribute columns by default" do
    Item.first.should == Item.load(:id=>1, :name=>'J')
  end
  
  specify "should load lazy attribute on access" do
    Item.first.num.should == 1
  end
  
  specify "should typecast lazy attribute in setter" do
    i = Item.new
    i.num = '1'
    i.num.should == 1
  end
  
  specify "should load lazy attribute for all items returned when accessing any item if using identity map " do
    Item.create(:name=>'K', :num=>2)
    a = Item.order(:name).all
    a.should == [Item.load(:id=>1, :name=>'J'), Item.load(:id=>2, :name=>'K')]
    a.map{|x| x[:num]}.should == [nil, nil]
    a.first.num.should == 1
    a.map{|x| x[:num]}.should == [1, 2]
    a.last.num.should == 2
  end
end

describe "Tactical Eager Loading Plugin" do
  before(:all) do
    @db = DB
    @db.instance_variable_set(:@schemas, {})
    @db.create_table!(:artists) do
      primary_key :id
      String :name
    end
    @db.create_table!(:albums) do
      primary_key :id
      String :name<commit_msg>Work around disconnect issue in specs when running fdbsql<commit_after>require File.join(File.dirname(File.expand_path(__FILE__)), 'spec_helper.rb')

describe "Class Table Inheritance Plugin" do
  before(:all) do
    @db = DB
    @db.instance_variable_set(:@schemas, {})
    @db.drop_table?(:staff, :executives, :managers, :employees)
    @db.create_table(:employees) do
      primary_key :id
      String :name
      String :kind
    end
    @db.create_table(:managers) do
      foreign_key :id, :employees, :primary_key=>true
      Integer :num_staff
    end
    @db.create_table(:executives) do
      foreign_key :id, :managers, :primary_key=>true
      Integer :num_managers
    end
    @db.create_table(:staff) do
      foreign_key :id, :employees, :primary_key=>true
      foreign_key :manager_id, :managers
    end
  end
  before do
    [:staff, :executives, :managers, :employees].each{|t| @db[t].delete}
    class ::Employee < Sequel::Model(@db)
      plugin :class_table_inheritance, :key=>:kind, :table_map=>{:Staff=>:staff}
    end 
    class ::Manager < Employee
      one_to_many :staff_members, :class=>:Staff
    end 
    class ::Executive < Manager
    end 
    class ::Staff < Employee
      many_to_one :manager
    end 
    
    @i1 =@db[:employees].insert(:name=>'E', :kind=>'Employee')
    @i2 = @db[:employees].insert(:name=>'S', :kind=>'Staff')
    @i3 = @db[:employees].insert(:name=>'M', :kind=>'Manager')
    @i4 = @db[:employees].insert(:name=>'Ex', :kind=>'Executive')
    @db[:managers].insert(:id=>@i3, :num_staff=>7)
    @db[:managers].insert(:id=>@i4, :num_staff=>5)
    @db[:executives].insert(:id=>@i4, :num_managers=>6)
    @db[:staff].insert(:id=>@i2, :manager_id=>@i4)
  end
  after do
    [:Executive, :Manager, :Staff, :Employee].each{|s| Object.send(:remove_const, s)}
  end
  after(:all) do
    @db.drop_table? :staff, :executives, :managers, :employees
  end

  specify "should return rows as subclass instances" do
    Employee.order(:id).all.should == [
      Employee.load(:id=>@i1, :name=>'E', :kind=>'Employee'),
      Staff.load(:id=>@i2, :name=>'S', :kind=>'Staff'),
      Manager.load(:id=>@i3, :name=>'M', :kind=>'Manager'),
      Executive.load(:id=>@i4, :name=>'Ex', :kind=>'Executive')
    ]
  end
  
  specify "should lazily load columns in subclass tables" do
    Employee[@i2][:manager_id].should == nil
    Employee[@i2].manager_id.should == @i4
    Employee[@i3][:num_staff].should == nil
    Employee[@i3].num_staff.should == 7
    Employee[@i4][:num_staff].should == nil
    Employee[@i4].num_staff.should == 5
    Employee[@i4][:num_managers].should == nil
    Employee[@i4].num_managers.should == 6
  end
  
  specify "should eagerly load columns in subclass tables when retrieving multiple objects" do
    a = Employee.order(:id).all
    a[1][:manager_id].should == nil
    a[1].manager_id.should == @i4
    a[2][:num_staff].should == nil
    a[2].num_staff.should == 7
    a[3][:num_staff].should == 5 # eagerly loaded by previous call
    a[3].num_staff.should == 5
    a[3][:num_managers].should == nil
    a[3].num_managers.should == 6
  end
  
  specify "should include schema for columns for tables for ancestor classes" do
    Employee.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :name]
    Staff.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :manager_id, :name]
    Manager.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :name, :num_staff]
    Executive.db_schema.keys.sort_by{|x| x.to_s}.should == [:id, :kind, :name, :num_managers, :num_staff]
  end
  
  specify "should include columns for tables for ancestor classes" do
    Employee.columns.should == [:id, :name, :kind]
    Staff.columns.should == [:id, :name, :kind, :manager_id]
    Manager.columns.should == [:id, :name, :kind, :num_staff]
    Executive.columns.should == [:id, :name, :kind, :num_staff, :num_managers]
  end
  
  specify "should delete rows from all tables" do
    e = Executive.first
    i = e.id
    e.staff_members_dataset.destroy
    e.destroy
    @db[:executives][:id=>i].should == nil
    @db[:managers][:id=>i].should == nil
    @db[:employees][:id=>i].should == nil
  end
  
  specify "should handle associations only defined in subclasses" do
    Employee.filter(:employees__id=>@i2).all.first.manager.id.should == @i4
  end

  specify "should insert rows into all tables" do
    e = Executive.create(:name=>'Ex2', :num_managers=>8, :num_staff=>9)
    i = e.id
    @db[:employees][:id=>i].should == {:id=>i, :name=>'Ex2', :kind=>'Executive'}
    @db[:managers][:id=>i].should == {:id=>i, :num_staff=>9}
    @db[:executives][:id=>i].should == {:id=>i, :num_managers=>8}
  end
  
  specify "should update rows in all tables" do
    Executive.first.update(:name=>'Ex2', :num_managers=>8, :num_staff=>9)
    @db[:employees][:id=>@i4].should == {:id=>@i4, :name=>'Ex2', :kind=>'Executive'}
    @db[:managers][:id=>@i4].should == {:id=>@i4, :num_staff=>9}
    @db[:executives][:id=>@i4].should == {:id=>@i4, :num_managers=>8}
  end
  
  specify "should handle many_to_one relationships" do
    m = Staff.first.manager
    m.should == Manager[@i4]
    m.should be_a_kind_of(Executive)
  end
  
  specify "should handle eagerly loading many_to_one relationships" do
    Staff.limit(1).eager(:manager).all.map{|x| x.manager}.should == [Manager[@i4]]
  end
  
  specify "should handle eagerly graphing many_to_one relationships" do
    ss = Staff.eager_graph(:manager).all
    ss.should == [Staff[@i2]]
    ss.map{|x| x.manager}.should == [Manager[@i4]]
  end
  
  specify "should handle one_to_many relationships" do
    Executive.first.staff_members.should == [Staff[@i2]]
  end
  
  specify "should handle eagerly loading one_to_many relationships" do
    Executive.limit(1).eager(:staff_members).first.staff_members.should == [Staff[@i2]]
  end
  
  specify "should handle eagerly graphing one_to_many relationships" do
    es = Executive.limit(1).eager_graph(:staff_members).all
    es.should == [Executive[@i4]]
    es.map{|x| x.staff_members}.should == [[Staff[@i2]]]
  end
end

describe "Many Through Many Plugin" do
  before(:all) do
    @db = DB
    @db.instance_variable_set(:@schemas, {})
    @db.drop_table?(:albums_artists, :albums, :artists)
    @db.create_table(:albums) do
      primary_key :id
      String :name
    end
    @db.create_table(:artists) do
      primary_key :id
      String :name
    end
    @db.create_table(:albums_artists) do
      foreign_key :album_id, :albums
      foreign_key :artist_id, :artists
    end
  end
  before do
    [:albums_artists, :albums, :artists].each{|t| @db[t].delete}
    class ::Album < Sequel::Model(@db)
      many_to_many :artists
    end 
    class ::Artist < Sequel::Model(@db)
      plugin :many_through_many
    end 
    
    @artist1 = Artist.create(:name=>'1')
    @artist2 = Artist.create(:name=>'2')
    @artist3 = Artist.create(:name=>'3')
    @artist4 = Artist.create(:name=>'4')
    @album1 = Album.create(:name=>'A')
    @album1.add_artist(@artist1)
    @album1.add_artist(@artist2)
    @album2 = Album.create(:name=>'B')
    @album2.add_artist(@artist3)
    @album2.add_artist(@artist4)
    @album3 = Album.create(:name=>'C')
    @album3.add_artist(@artist2)
    @album3.add_artist(@artist3)
    @album4 = Album.create(:name=>'D')
    @album4.add_artist(@artist1)
    @album4.add_artist(@artist4)
  end
  after do
    [:Album, :Artist].each{|s| Object.send(:remove_const, s)}
  end
  after(:all) do
    @db.drop_table? :albums_artists, :albums, :artists
  end
  
  def self_join(c)
    c.join(Sequel.as(c.table_name, :b), Array(c.primary_key).zip(Array(c.primary_key))).select_all(c.table_name)
  end

  specify "should handle super simple case with 1 join table" do
    Artist.many_through_many :albums, [[:albums_artists, :artist_id, :album_id]]
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'A D'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'A C'
    Artist[@<EMAIL>3.id].albums.map{|x| x.name}.sort.should == %w'B C'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'B D'
    
    Artist.plugin :prepared_statements_associations
    Artist[<EMAIL>].albums.map{|x| x.name}.sort.should == %w'A D'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'A C'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'B C'
    Artist[@<EMAIL>].albums.map{|x| x.name}.sort.should == %w'B D'

    Artist.filter(:id=>1).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A D'
    Artist.filter(:id=>2).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A C'
    Artist.filter(:id=>3).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B C'
    Artist.filter(:id=>4).eager(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B D'
    
    Artist.filter(:artists__id=>1).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A D'
    Artist.filter(:artists__id=>2).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'A C'
    Artist.filter(:artists__id=>3).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B C'
    Artist.filter(:artists__id=>4).eager_graph(:albums).all.map{|x| x.albums.map{|a| a.name}}.flatten.sort.should == %w'B D'

    Artist.filter(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2'
    Artist.filter(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'3 4'
    Artist.filter(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'2 3'
    Artist.filter(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'1 4'

    Artist.exclude(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'3 4'
    Artist.exclude(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2'
    Artist.exclude(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 4'
    Artist.exclude(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3'

    Artist.filter(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'1 3 4'

    Artist.exclude(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'2'

    Artist.filter(:albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.exclude(:albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'

    c = self_join(Artist)
    c.filter(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2'
    c.filter(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'3 4'
    c.filter(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'2 3'
    c.filter(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'1 4'

    c.exclude(:albums=>@album1).all.map{|a| a.name}.sort.should == %w'3 4'
    c.exclude(:albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2'
    c.exclude(:albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 4'
    c.exclude(:albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3'

    c.filter(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'1 3 4'

    c.exclude(:albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w'2'

    c.filter(:albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.exclude(:albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'
  end

  cspecify "should handle typical case with 3 join tables", :fdbsql do
    Artist.many_through_many :related_artists, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_artists, :album_id, :artist_id]], :class=>Artist, :distinct=>true
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 4'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 3'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'2 3 4'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 3 4'
    
    Artist.plugin :prepared_statements_associations
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 4'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 2 3'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'2 3 4'
    Artist[<EMAIL>].related_artists.map{|x| x.name}.sort.should == %w'1 3 4'
    
    Artist.filter(:id=>@artist1.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 4'
    Artist.filter(:id=>@artist2.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 3'
    Artist.filter(:id=><EMAIL>3.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'2 3 4'
    Artist.filter(:id=>@artist4.id).eager(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 3 4'
    
    Artist.filter(:artists__id=>@artist1.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 4'
    Artist.filter(:artists__id=>@artist2.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 2 3'
    Artist.filter(:artists__id=>@artist3.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'2 3 4'
    Artist.filter(:artists__id=>@artist4.id).eager_graph(:related_artists).all.map{|x| x.related_artists.map{|a| a.name}}.flatten.sort.should == %w'1 3 4'

    Artist.filter(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'1 2 4'
    Artist.filter(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'2 3 4'
    Artist.filter(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'1 3 4'

    Artist.exclude(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'3'
    Artist.exclude(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'1'
    Artist.exclude(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'2'

    Artist.filter(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    Artist.exclude(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w''

    Artist.filter(:related_artists=>Artist.filter(:id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'1 2 4'
    Artist.exclude(:related_artists=>Artist.filter(:id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'3'

    c = self_join(Artist)
    c.filter(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'1 2 4'
    c.filter(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'2 3 4'
    c.filter(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'1 3 4'

    c.exclude(:related_artists=>@artist1).all.map{|a| a.name}.sort.should == %w'3'
    c.exclude(:related_artists=>@artist2).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:related_artists=>@artist3).all.map{|a| a.name}.sort.should == %w'1'
    c.exclude(:related_artists=>@artist4).all.map{|a| a.name}.sort.should == %w'2'

    c.filter(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    c.exclude(:related_artists=>[@artist1, @artist4]).all.map{|a| a.name}.sort.should == %w''

    c.filter(:related_artists=>c.filter(:artists__id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'1 2 4'
    c.exclude(:related_artists=>c.filter(:artists__id=>@artist1.id)).all.map{|a| a.name}.sort.should == %w'3'
  end

  cspecify "should handle extreme case with 5 join tables", :fdbsql do
    Artist.many_through_many :related_albums, [[:albums_artists, :artist_id, :album_id], [:albums, :id, :id], [:albums_artists, :album_id, :artist_id], [:artists, :id, :id], [:albums_artists, :artist_id, :album_id]], :class=>Album, :distinct=>true
    @db[:albums_artists].delete
    @album1.add_artist(@artist1)
    @album1.add_artist(@artist2)
    @album2.add_artist(@artist2)
    @album2.add_artist(@artist3)
    @album3.add_artist(@artist1)
    @album4.add_artist(@artist3)
    @album4.add_artist(@artist4)
    
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B C'
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B C D'
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B D'
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'B D'
    
    Artist.plugin :prepared_statements_associations
    Artist[@<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B C'
    Artist[@<EMAIL>2.id].related_albums.map{|x| x.name}.sort.should == %w'A B C D'
    Artist[<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'A B D'
    Artist[<EMAIL>].related_albums.map{|x| x.name}.sort.should == %w'B D'
    
    Artist.filter(:id=>@artist1.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C'
    Artist.filter(:id=>@artist2.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C D'
    Artist.filter(:id=>@artist3.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B D'
    Artist.filter(:id=>@artist4.id).eager(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'B D'
    
    Artist.filter(:artists__id=>@artist1.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C'
    Artist.filter(:artists__id=>@artist2.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B C D'
    Artist.filter(:artists__id=>@artist3.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'A B D'
    Artist.filter(:artists__id=>@artist4.id).eager_graph(:related_albums).all.map{|x| x.related_albums.map{|a| a.name}}.flatten.sort.should == %w'B D'

    Artist.filter(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    Artist.filter(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 2'
    Artist.filter(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3 4'

    Artist.exclude(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w''
    Artist.exclude(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'3 4'
    Artist.exclude(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'1'

    Artist.filter(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.filter(:related_albums=>[@album3, @album4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'

    Artist.exclude(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    Artist.exclude(:related_albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w''

    Artist.filter(:related_albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    Artist.exclude(:related_albums=>Album.filter(:id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'

    c = self_join(Artist)
    c.filter(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w'1 2 3 4'
    c.filter(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'1 2'
    c.filter(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'2 3 4'

    c.exclude(:related_albums=>@album1).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:related_albums=>@album2).all.map{|a| a.name}.sort.should == %w''
    c.exclude(:related_albums=>@album3).all.map{|a| a.name}.sort.should == %w'3 4'
    c.exclude(:related_albums=>@album4).all.map{|a| a.name}.sort.should == %w'1'

    c.filter(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.filter(:related_albums=>[@album3, @album4]).all.map{|a| a.name}.sort.should == %w'1 2 3 4'

    c.exclude(:related_albums=>[@album1, @album3]).all.map{|a| a.name}.sort.should == %w'4'
    c.exclude(:related_albums=>[@album2, @album4]).all.map{|a| a.name}.sort.should == %w''

    c.filter(:related_albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'1 2 3'
    c.exclude(:related_albums=>self_join(Album).filter(:albums__id=>[@album1.id, @album3.id])).all.map{|a| a.name}.sort.should == %w'4'
  end
end

describe "Lazy Attributes plugin" do 
  before(:all) do
    @db = DB
    @db.create_table!(:items) do
      primary_key :id
      String :name
      Integer :num
    end
    @db[:items].delete
    class ::Item < Sequel::Model(@db)
      plugin :lazy_attributes, :num
    end
    Item.create(:name=>'J', :num=>1)
  end
  after(:all) do
    @db.drop_table?(:items)
    Object.send(:remove_const, :Item)
  end
  
  specify "should not include lazy attribute columns by default" do
    Item.first.should == Item.load(:id=>1, :name=>'J')
  end
  
  specify "should load lazy attribute on access" do
    Item.first.num.should == 1
  end
  
  specify "should typecast lazy attribute in setter" do
    i = Item.new
    i.num = '1'
    i.num.should == 1
  end
  
  specify "should load lazy attribute for all items returned when accessing any item if using identity map " do
    Item.create(:name=>'K', :num=>2)
    a = Item.order(:name).all
    a.should == [Item.load(:id=>1, :name=>'J'), Item.load(:id=>2, :name=>'K')]
    a.map{|x| x[:num]}.should == [nil, nil]
    a.first.num.should == 1
    a.map{|x| x[:num]}.should == [1, 2]
    a.last.num.should == 2
  end
end

describe "Tactical Eager Loading Plugin" do
  before(:all) do
    @db = DB
    @db.instance_variable_set(:@schemas, {})
    @db.drop_table?(:albums_artists) # work around fdbsql disconnect bug
    @db.create_table!(:artists) do
      primary_key :id
      String :name
    end
    @db.create_table!(:albums) do
      primary_key :id
      String :name
{'fraction_non_alphanumeric': 0.21142594175267898, 'fraction_numerical': 0.02068678368724388, 'mean_word_length': 4.236259323006311, 'pattern_counts': {'":': 0, '<': 66, '<?xml version=': 0, '>': 458, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3080324', 'n_tokens_mistral': 18901, 'n_tokens_neox': 18183, 'n_words': 2761}