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

describe "Simple Dataset operations" do
  before do
    @db = INTEGRATION_DB
    @db.create_table!(:items) do
      primary_key :id
      Integer :number
    end
    @ds = @db[:items]
    @ds.insert(:number=>10)
  end
  after do
    @db.drop_table?(:items)
  end

  specify "should support sequential primary keys" do
    @ds << {:number=>20}
    @ds << {:number=>30}
    @ds.order(:number).all.should == [
      {:id => 1, :number=>10},
      {:id => 2, :number=>20},
      {:id => 3, :number=>30} ]   
  end 

  cspecify "should insert with a primary key specified", :db2, :mssql do
    @ds.insert(:id=>100, :number=>20)
    @ds.count.should == 2
    @ds.order(:id).all.should == [{:id=>1, :number=>10}, {:id=>100, :number=>20}]
  end

  specify "should have insert return primary key value" do
    @ds.insert(:number=>20).should == 2
    @ds.filter(:id=>2).first[:number].should == 20
  end

  specify "should have insert work correctly with static SQL" do
    @db["INSERT INTO #{@ds.literal(:items)} (#{@ds.literal(:number)}) VALUES (20)"].insert
    @ds.filter(:id=>2).first[:number].should == 20
  end

  specify "should have insert_multiple return primary key values" do
    @ds.insert_multiple([{:number=>20}, {:number=>30}]).should == [2, 3]
    @ds.filter(:id=>2).get(:number).should == 20
    @ds.filter(:id=>3).get(:number).should == 30
  end

  specify "should join correctly" do
    @ds.join(:items___b, :id=>:id).select_all(:items).all.should == [{:id=>1, :number=>10}]
  end

  specify "should correctly deal with qualified columns and subselects" do
    @ds.from_self(:alias=>:a).select(:a__id, :number.qualify(:a)).all.should == [{:id=>1, :number=>10}]
    @ds.join(@ds.as(:a), :id=>:id).select(:a__id, :number.qualify(:a)).all.should == [{:id=>1, :number=>10}]
  end

  specify "should graph correctly" do
    @ds.graph(:items, {:id=>:id}, :table_alias=>:b).all.should == [{:items=>{:id=>1, :number=>10}, :b=>{:id=>1, :number=>10}}]
  end

  specify "should graph correctly with a subselect" do
    @ds.from_self(:alias=>:items).graph(@ds.from_self, {:id=>:id}, :table_alias=>:b).all.should == [{:items=>{:id=>1, :number=>10}, :b=>{:id=>1, :number=>10}}]
  end

  cspecify "should have insert work correctly when inserting a row with all NULL values", :hsqldb do
    @db.create_table!(:items) do
      String :name
      Integer :number
    end
    proc{@ds.insert}.should_not raise_error
    @ds.all.should == [{:name=>nil, :number=>nil}]
  end

  specify "should delete correctly" do
    @ds.filter(1=>1).delete.should == 1
    @ds.count.should == 0
  end
  
  specify "should update correctly" do
    @ds.update(:number=>:number+1).should == 1
    @ds.all.should == [{:id=>1, :number=>11}]
  end
  
  cspecify "should have update return the number of matched rows", [:do, :mysql], [:ado] do
    @ds.update(:number=>:number).should == 1
    @ds.filter(:id=>1).update(:number=>:number).should == 1
    @ds.filter(:id=>2).update(:number=>:number).should == 0
    @ds.all.should == [{:id=>1, :number=>10}]
  end

  specify "should fetch all results correctly" do
    @ds.all.should == [{:id=>1, :number=>10}]
  end

  specify "should fetch a single row correctly" do
    @ds.first.should == {:id=>1, :number=>10}
  end
  
  specify "should have distinct work with limit" do
    @ds.limit(1).distinct.all.should == [{:id=>1, :number=>10}]
  end
  
  specify "should fetch correctly with a limit" do
    @ds.order(:id).limit(2).all.should == [{:id=>1, :number=>10}]
    @ds.insert(:number=>20)
    @ds.order(:id).limit(1).all.should == [{:id=>1, :number=>10}]
    @ds.order(:id).limit(2).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
  end
  
  specify "should fetch correctly with a limit and offset" do
    @ds.order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10}]
    @ds.order(:id).limit(2, 1).all.should == []
    @ds.insert(:number=>20)
    @ds.order(:id).limit(1, 1).all.should == [{:id=>2, :number=>20}]
    @ds.order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
    @ds.order(:id).limit(2, 1).all.should == [{:id=>2, :number=>20}]
  end
  
  specify "should provide correct columns when using a limit and offset" do
    ds = @ds.order(:id).limit(1, 1)
    ds.all
    ds.columns.should == [:id, :number]
    @ds.order(:id).limit(1, 1).columns.should == [:id, :number]
  end

  specify "should fetch correctly with a limit and offset for different combinations of from and join tables" do
    @db.create_table!(:items2){primary_key :id2; Integer :number2}
    @db[:items2].insert(:number2=>10)
    @ds.from(:items, :items2).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.from(:items___i, :items2___i2).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.cross_join(:items2).order(:id).limit(2, 0).all.should ==[{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.from(:items___i).cross_join(:items2___i2).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.cross_join(:items2___i).cross_join(@db[:items2].select(:id2___id3, :number2___number3)).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10, :id3=>1, :number3=>10}]

    @ds.from(:items, :items2).order(:id).limit(2, 1).all.should == []
    @ds.from(:items___i, :items2___i2).order(:id).limit(2, 1).all.should == []
    @ds.cross_join(:items2).order(:id).limit(2, 1).all.should == []
    @ds.from(:items___i).cross_join(:items2___i2).order(:id).limit(2, 1).all.should == []
    @ds.cross_join(:items2___i).cross_join(@db[:items2].select(:id2___id3, :number2___number3)).order(:id).limit(2, 1).all.should == []
    @db.drop_table(:items2)
  end
  
  cspecify "should fetch correctly with a limit and offset without an order", :db2, :mssql, :oracle do
    @ds.limit(2, 1).all.should == []
  end

  specify "should fetch correctly with a limit in an IN subselect" do
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2)).all.should == [{:id=>1, :number=>10}]
    @ds.insert(:number=>20)
    @ds.where(:id=>@ds.select(:id).order(:id).limit(1)).all.should == [{:id=>1, :number=>10}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2)).order(:id).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
  end
  
  specify "should fetch correctly with a limit and offset in an IN subselect" do
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 0)).all.should == [{:id=>1, :number=>10}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 1)).all.should == []
    @ds.insert(:number=>20)
    @ds.where(:id=>@ds.select(:id).order(:id).limit(1, 1)).all.should == [{:id=>2, :number=>20}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 0)).order(:id).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 1)).all.should == [{:id=>2, :number=>20}]
  end
  
  specify "should alias columns correctly" do
    @ds.select(:id___x, :number___n).first.should == {:x=>1, :n=>10}
  end

  specify "should handle true/false properly" do
    @ds.filter(Sequel::TRUE).select_map(:number).should == [10]
    @ds.filter(Sequel::FALSE).select_map(:number).should == []
    @ds.filter(true).select_map(:number).should == [10]
    @ds.filter(false).select_map(:number).should == []
  end
end

describe "Simple dataset operations with nasty table names" do
  before do
    @db = INTEGRATION_DB
    @table = :"i`t' [e]\"m\\s" 
  end

  cspecify "should work correctly", :mssql, :oracle do
    @db.create_table!(@table) do
      primary_key :id
      Integer :number
    end
    @ds = @db[@table]
    @ds.insert(:number=>10).should == 1
    @ds.all.should == [{:id=>1, :number=>10}]
    @ds.update(:number=>20).should == 1 
    @ds.all.should == [{:id=>1, :number=>20}]
    @ds.delete.should == 1<commit_msg>Enable quote identifiers for the nasty table spec<commit_after>require File.join(File.dirname(File.expand_path(__FILE__)), 'spec_helper.rb')

describe "Simple Dataset operations" do
  before do
    @db = INTEGRATION_DB
    @db.create_table!(:items) do
      primary_key :id
      Integer :number
    end
    @ds = @db[:items]
    @ds.insert(:number=>10)
  end
  after do
    @db.drop_table?(:items)
  end

  specify "should support sequential primary keys" do
    @ds << {:number=>20}
    @ds << {:number=>30}
    @ds.order(:number).all.should == [
      {:id => 1, :number=>10},
      {:id => 2, :number=>20},
      {:id => 3, :number=>30} ]   
  end 

  cspecify "should insert with a primary key specified", :db2, :mssql do
    @ds.insert(:id=>100, :number=>20)
    @ds.count.should == 2
    @ds.order(:id).all.should == [{:id=>1, :number=>10}, {:id=>100, :number=>20}]
  end

  specify "should have insert return primary key value" do
    @ds.insert(:number=>20).should == 2
    @ds.filter(:id=>2).first[:number].should == 20
  end

  specify "should have insert work correctly with static SQL" do
    @db["INSERT INTO #{@ds.literal(:items)} (#{@ds.literal(:number)}) VALUES (20)"].insert
    @ds.filter(:id=>2).first[:number].should == 20
  end

  specify "should have insert_multiple return primary key values" do
    @ds.insert_multiple([{:number=>20}, {:number=>30}]).should == [2, 3]
    @ds.filter(:id=>2).get(:number).should == 20
    @ds.filter(:id=>3).get(:number).should == 30
  end

  specify "should join correctly" do
    @ds.join(:items___b, :id=>:id).select_all(:items).all.should == [{:id=>1, :number=>10}]
  end

  specify "should correctly deal with qualified columns and subselects" do
    @ds.from_self(:alias=>:a).select(:a__id, :number.qualify(:a)).all.should == [{:id=>1, :number=>10}]
    @ds.join(@ds.as(:a), :id=>:id).select(:a__id, :number.qualify(:a)).all.should == [{:id=>1, :number=>10}]
  end

  specify "should graph correctly" do
    @ds.graph(:items, {:id=>:id}, :table_alias=>:b).all.should == [{:items=>{:id=>1, :number=>10}, :b=>{:id=>1, :number=>10}}]
  end

  specify "should graph correctly with a subselect" do
    @ds.from_self(:alias=>:items).graph(@ds.from_self, {:id=>:id}, :table_alias=>:b).all.should == [{:items=>{:id=>1, :number=>10}, :b=>{:id=>1, :number=>10}}]
  end

  cspecify "should have insert work correctly when inserting a row with all NULL values", :hsqldb do
    @db.create_table!(:items) do
      String :name
      Integer :number
    end
    proc{@ds.insert}.should_not raise_error
    @ds.all.should == [{:name=>nil, :number=>nil}]
  end

  specify "should delete correctly" do
    @ds.filter(1=>1).delete.should == 1
    @ds.count.should == 0
  end
  
  specify "should update correctly" do
    @ds.update(:number=>:number+1).should == 1
    @ds.all.should == [{:id=>1, :number=>11}]
  end
  
  cspecify "should have update return the number of matched rows", [:do, :mysql], [:ado] do
    @ds.update(:number=>:number).should == 1
    @ds.filter(:id=>1).update(:number=>:number).should == 1
    @ds.filter(:id=>2).update(:number=>:number).should == 0
    @ds.all.should == [{:id=>1, :number=>10}]
  end

  specify "should fetch all results correctly" do
    @ds.all.should == [{:id=>1, :number=>10}]
  end

  specify "should fetch a single row correctly" do
    @ds.first.should == {:id=>1, :number=>10}
  end
  
  specify "should have distinct work with limit" do
    @ds.limit(1).distinct.all.should == [{:id=>1, :number=>10}]
  end
  
  specify "should fetch correctly with a limit" do
    @ds.order(:id).limit(2).all.should == [{:id=>1, :number=>10}]
    @ds.insert(:number=>20)
    @ds.order(:id).limit(1).all.should == [{:id=>1, :number=>10}]
    @ds.order(:id).limit(2).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
  end
  
  specify "should fetch correctly with a limit and offset" do
    @ds.order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10}]
    @ds.order(:id).limit(2, 1).all.should == []
    @ds.insert(:number=>20)
    @ds.order(:id).limit(1, 1).all.should == [{:id=>2, :number=>20}]
    @ds.order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
    @ds.order(:id).limit(2, 1).all.should == [{:id=>2, :number=>20}]
  end
  
  specify "should provide correct columns when using a limit and offset" do
    ds = @ds.order(:id).limit(1, 1)
    ds.all
    ds.columns.should == [:id, :number]
    @ds.order(:id).limit(1, 1).columns.should == [:id, :number]
  end

  specify "should fetch correctly with a limit and offset for different combinations of from and join tables" do
    @db.create_table!(:items2){primary_key :id2; Integer :number2}
    @db[:items2].insert(:number2=>10)
    @ds.from(:items, :items2).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.from(:items___i, :items2___i2).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.cross_join(:items2).order(:id).limit(2, 0).all.should ==[{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.from(:items___i).cross_join(:items2___i2).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10}]
    @ds.cross_join(:items2___i).cross_join(@db[:items2].select(:id2___id3, :number2___number3)).order(:id).limit(2, 0).all.should == [{:id=>1, :number=>10, :id2=>1, :number2=>10, :id3=>1, :number3=>10}]

    @ds.from(:items, :items2).order(:id).limit(2, 1).all.should == []
    @ds.from(:items___i, :items2___i2).order(:id).limit(2, 1).all.should == []
    @ds.cross_join(:items2).order(:id).limit(2, 1).all.should == []
    @ds.from(:items___i).cross_join(:items2___i2).order(:id).limit(2, 1).all.should == []
    @ds.cross_join(:items2___i).cross_join(@db[:items2].select(:id2___id3, :number2___number3)).order(:id).limit(2, 1).all.should == []
    @db.drop_table(:items2)
  end
  
  cspecify "should fetch correctly with a limit and offset without an order", :db2, :mssql, :oracle do
    @ds.limit(2, 1).all.should == []
  end

  specify "should fetch correctly with a limit in an IN subselect" do
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2)).all.should == [{:id=>1, :number=>10}]
    @ds.insert(:number=>20)
    @ds.where(:id=>@ds.select(:id).order(:id).limit(1)).all.should == [{:id=>1, :number=>10}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2)).order(:id).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
  end
  
  specify "should fetch correctly with a limit and offset in an IN subselect" do
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 0)).all.should == [{:id=>1, :number=>10}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 1)).all.should == []
    @ds.insert(:number=>20)
    @ds.where(:id=>@ds.select(:id).order(:id).limit(1, 1)).all.should == [{:id=>2, :number=>20}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 0)).order(:id).all.should == [{:id=>1, :number=>10}, {:id=>2, :number=>20}]
    @ds.where(:id=>@ds.select(:id).order(:id).limit(2, 1)).all.should == [{:id=>2, :number=>20}]
  end
  
  specify "should alias columns correctly" do
    @ds.select(:id___x, :number___n).first.should == {:x=>1, :n=>10}
  end

  specify "should handle true/false properly" do
    @ds.filter(Sequel::TRUE).select_map(:number).should == [10]
    @ds.filter(Sequel::FALSE).select_map(:number).should == []
    @ds.filter(true).select_map(:number).should == [10]
    @ds.filter(false).select_map(:number).should == []
  end
end

describe "Simple dataset operations with nasty table names" do
  before do
    @db = INTEGRATION_DB
    @table = :"i`t' [e]\"m\\s" 
    @qi = @db.quote_identifiers?
    @db.quote_identifiers = true
  end
  after do
    @db.quote_identifiers = @qi
  end

  cspecify "should work correctly", :mssql, :oracle do
    @db.create_table!(@table) do
      primary_key :id
      Integer :number
    end
    @ds = @db[@table]
    @ds.insert(:number=>10).should == 1
    @ds.all.should == [{:id=>1, :number=>10}]
    @ds.update(:number=>20).should == 1 
    @ds.all.should == [{:id=>1, :number=>20}]
    @ds.delete.should == 1
{'fraction_non_alphanumeric': 0.22789857329761384, 'fraction_numerical': 0.039000685315556664, 'mean_word_length': 4.143223325857097, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 295, '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': '950548', 'n_tokens_mistral': 6250, 'n_tokens_neox': 5970, 'n_words': 1193}