<commit_before>module Sequel
  class Dataset
    # ---------------------
    # :section: 1 - Methods that return modified datasets
    # These methods all return modified copies of the receiver.
    # ---------------------

    # The dataset options that require the removal of cached columns
    # if changed.
    COLUMN_CHANGE_OPTS = [:select, :sql, :from, :join].freeze

    # Which options don't affect the SQL generation.  Used by simple_select_all?
    # to determine if this is a simple SELECT * FROM table.
    NON_SQL_OPTIONS = [:server, :defaults, :overrides, :graph, :eager_graph, :graph_aliases]
    
    # These symbols have _join methods created (e.g. inner_join) that
    # call join_table with the symbol, passing along the arguments and
    # block from the method call.
    CONDITIONED_JOIN_TYPES = [:inner, :full_outer, :right_outer, :left_outer, :full, :right, :left]

    # These symbols have _join methods created (e.g. natural_join) that
    # call join_table with the symbol.  They only accept a single table
    # argument which is passed to join_table, and they raise an error
    # if called with a block.
    UNCONDITIONED_JOIN_TYPES = [:natural, :natural_left, :natural_right, :natural_full, :cross]
    
    # All methods that return modified datasets with a joined table added.
    JOIN_METHODS = (CONDITIONED_JOIN_TYPES + UNCONDITIONED_JOIN_TYPES).map{|x| "#{x}_join".to_sym} + [:join, :join_table]
    
    # Methods that return modified datasets
    QUERY_METHODS = (<<-METHS).split.map{|x| x.to_sym} + JOIN_METHODS
      add_graph_aliases and distinct except exclude exclude_having exclude_where
      filter for_update from from_self graph grep group group_and_count group_by having intersect invert
      limit lock_style naked or order order_append order_by order_more order_prepend paginate qualify query
      reverse reverse_order select select_all select_append select_group select_more server
      set_defaults set_graph_aliases set_overrides unfiltered ungraphed ungrouped union
      unlimited unordered where with with_recursive with_sql
    METHS

    # Adds an further filter to an existing filter using AND. If no filter 
    # exists an error is raised. This method is identical to #filter except
    # it expects an existing filter.
    #
    #   DB[:table].filter(:a).and(:b) # SELECT * FROM table WHERE a AND b
    def and(*cond, &block)
      raise(InvalidOperation, "No existing filter found.") unless @opts[:having] || @opts[:where]
      filter(*cond, &block)
    end
    
    # Returns a new clone of the dataset with with the given options merged.
    # If the options changed include options in COLUMN_CHANGE_OPTS, the cached
    # columns are deleted.  This method should generally not be called
    # directly by user code.
    def clone(opts = {})
      c = super()
      c.opts = @opts.merge(opts)
      c.instance_variable_set(:@columns, nil) if opts.keys.any?{|o| COLUMN_CHANGE_OPTS.include?(o)}
      c
    end

    # Returns a copy of the dataset with the SQL DISTINCT clause.
    # The DISTINCT clause is used to remove duplicate rows from the
    # output.  If arguments are provided, uses a DISTINCT ON clause,
    # in which case it will only be distinct on those columns, instead
    # of all returned columns.  Raises an error if arguments
    # are given and DISTINCT ON is not supported.
    #
    #  DB[:items].distinct # SQL: SELECT DISTINCT * FROM items
    #  DB[:items].order(:id).distinct(:id) # SQL: SELECT DISTINCT ON (id) * FROM items ORDER BY id
    def distinct(*args)
      raise(InvalidOperation, "DISTINCT ON not supported") if !args.empty? && !supports_distinct_on?
      clone(:distinct => args)
    end

    # Adds an EXCEPT clause using a second dataset object.
    # An EXCEPT compound dataset returns all rows in the current dataset
    # that are not in the given dataset.
    # Raises an +InvalidOperation+ if the operation is not supported.
    # Options:
    # :alias :: Use the given value as the from_self alias
    # :all :: Set to true to use EXCEPT ALL instead of EXCEPT, so duplicate rows can occur
    # :from_self :: Set to false to not wrap the returned dataset in a from_self, use with care.
    #
    #   DB[:items].except(DB[:other_items])
    #   # SELECT * FROM (SELECT * FROM items EXCEPT SELECT * FROM other_items) AS t1
    #
    #   DB[:items].except(DB[:other_items], :all=>true, :from_self=>false)
    #   # SELECT * FROM items EXCEPT ALL SELECT * FROM other_items
    #
    #   DB[:items].except(DB[:other_items], :alias=>:i)
    #   # SELECT * FROM (SELECT * FROM items EXCEPT SELECT * FROM other_items) AS i
    def except(dataset, opts={})
      opts = {:all=>opts} unless opts.is_a?(Hash)
      raise(InvalidOperation, "EXCEPT not supported") unless supports_intersect_except?
      raise(InvalidOperation, "EXCEPT ALL not supported") if opts[:all] && !supports_intersect_except_all?
      compound_clone(:except, dataset, opts)
    end

    # Performs the inverse of Dataset#filter.  Note that if you have multiple filter
    # conditions, this is not the same as a negation of all conditions.
    #
    #   DB[:items].exclude(:category => 'software')
    #   # SELECT * FROM items WHERE (category != 'software')
    #   
    #   DB[:items].exclude(:category => 'software', :id=>3)
    #   # SELECT * FROM items WHERE ((category != 'software') OR (id != 3))
    def exclude(*cond, &block)
      _filter_or_exclude(true, @opts[:having] ? :having : :where, *cond, &block)
    end

    # Inverts the given conditions and adds them to the HAVING clause.
    #
    #   DB[:items].select_group(:name).exclude_having{count(name) < 2}
    #   # SELECT name FROM items GROUP BY name HAVING (count(name) >= 2)
    def exclude_having(*cond, &block)
      _filter_or_exclude(true, :having, *cond, &block)
    end

    # Inverts the given conditions and adds them to the WHERE clause.
    #
    #   DB[:items].select_group(:name).exclude_where(:category => 'software')
    #   # SELECT * FROM items WHERE (category != 'software')
    #
    #   DB[:items].select_group(:name).
    #     exclude_having{count(name) < 2}.
    #     exclude_where(:category => 'software')
    #   # SELECT name FROM items WHERE (category != 'software')
    #   # GROUP BY name HAVING (count(name) >= 2)
    def exclude_where(*cond, &block)
      _filter_or_exclude(true, :where, *cond, &block)
    end

    # Returns a copy of the dataset with the given conditions imposed upon it.  
    # If the query already has a HAVING clause, then the conditions are imposed in the 
    # HAVING clause. If not, then they are imposed in the WHERE clause.
    # 
    # filter accepts the following argument types:
    #
    # * Hash - list of equality/inclusion expressions
    # * Array - depends:
    #   * If first member is a string, assumes the rest of the arguments
    #     are parameters and interpolates them into the string.
    #   * If all members are arrays of length two, treats the same way
    #     as a hash, except it allows for duplicate keys to be
    #     specified.
    #   * Otherwise, treats each argument as a separate condition.
    # * String - taken literally
    # * Symbol - taken as a boolean column argument (e.g. WHERE active)
    # * Sequel::SQL::BooleanExpression - an existing condition expression,
    #   probably created using the Sequel expression filter DSL.
    #
    # filter also takes a block, which should return one of the above argument
    # types, and is treated the same way.  This block yields a virtual row object,
    # which is easy to use to create identifiers and functions.  For more details
    # on the virtual row support, see the {"Virtual Rows" guide}[link:files/doc/virtual_rows_rdoc.html]
    #
    # If both a block and regular argument are provided, they get ANDed together.
    #
    # Examples:
    #
    #   DB[:items].filter(:id => 3)
    #   # SELECT * FROM items WHERE (id = 3)
    #
    #   DB[:items].filter('price < ?', 100)
    #   # SELECT * FROM items WHERE price < 100
    #
    #   DB[:items].filter([[:id, (1,2,3)], [:id, 0..10]])
    #   # SELECT * FROM items WHERE ((id IN (1, 2, 3)) AND ((id >= 0) AND (id <= 10)))
    #
    #   DB[:items].filter('price < 100')
    #   # SELECT * FROM items WHERE price < 100
    #
    #   DB[:items].filter(:active)
    #   # SELECT * FROM items WHERE :active
    #
    #   DB[:items].filter{price < 100}
    #   # SELECT * FROM items WHERE (price < 100)
    # 
    # Multiple filter calls can be chained for scoping:
    #
    #   software = dataset.filter(:category => 'software').filter{price < 100}
    #   # SELECT * FROM items WHERE ((category = 'software') AND (price < 100))
    #
    # See the the {"Dataset Filtering" guide}[link:files/doc/dataset_filtering_rdoc.html] for more examples and details.
    def filter(*cond, &block)
      _filter(@opts[:having] ? :having : :where, *cond, &block)
    end
    
    # Returns a cloned dataset with a :update lock style.
    #
    #   DB[:table].for_update # SELECT * FROM table FOR UPDATE
    def for_update
      lock_style(:update)<commit_msg>dataset/query.rb : Fix an error in filter documentation<commit_after>module Sequel
  class Dataset
    # ---------------------
    # :section: 1 - Methods that return modified datasets
    # These methods all return modified copies of the receiver.
    # ---------------------

    # The dataset options that require the removal of cached columns
    # if changed.
    COLUMN_CHANGE_OPTS = [:select, :sql, :from, :join].freeze

    # Which options don't affect the SQL generation.  Used by simple_select_all?
    # to determine if this is a simple SELECT * FROM table.
    NON_SQL_OPTIONS = [:server, :defaults, :overrides, :graph, :eager_graph, :graph_aliases]
    
    # These symbols have _join methods created (e.g. inner_join) that
    # call join_table with the symbol, passing along the arguments and
    # block from the method call.
    CONDITIONED_JOIN_TYPES = [:inner, :full_outer, :right_outer, :left_outer, :full, :right, :left]

    # These symbols have _join methods created (e.g. natural_join) that
    # call join_table with the symbol.  They only accept a single table
    # argument which is passed to join_table, and they raise an error
    # if called with a block.
    UNCONDITIONED_JOIN_TYPES = [:natural, :natural_left, :natural_right, :natural_full, :cross]
    
    # All methods that return modified datasets with a joined table added.
    JOIN_METHODS = (CONDITIONED_JOIN_TYPES + UNCONDITIONED_JOIN_TYPES).map{|x| "#{x}_join".to_sym} + [:join, :join_table]
    
    # Methods that return modified datasets
    QUERY_METHODS = (<<-METHS).split.map{|x| x.to_sym} + JOIN_METHODS
      add_graph_aliases and distinct except exclude exclude_having exclude_where
      filter for_update from from_self graph grep group group_and_count group_by having intersect invert
      limit lock_style naked or order order_append order_by order_more order_prepend paginate qualify query
      reverse reverse_order select select_all select_append select_group select_more server
      set_defaults set_graph_aliases set_overrides unfiltered ungraphed ungrouped union
      unlimited unordered where with with_recursive with_sql
    METHS

    # Adds an further filter to an existing filter using AND. If no filter 
    # exists an error is raised. This method is identical to #filter except
    # it expects an existing filter.
    #
    #   DB[:table].filter(:a).and(:b) # SELECT * FROM table WHERE a AND b
    def and(*cond, &block)
      raise(InvalidOperation, "No existing filter found.") unless @opts[:having] || @opts[:where]
      filter(*cond, &block)
    end
    
    # Returns a new clone of the dataset with with the given options merged.
    # If the options changed include options in COLUMN_CHANGE_OPTS, the cached
    # columns are deleted.  This method should generally not be called
    # directly by user code.
    def clone(opts = {})
      c = super()
      c.opts = @opts.merge(opts)
      c.instance_variable_set(:@columns, nil) if opts.keys.any?{|o| COLUMN_CHANGE_OPTS.include?(o)}
      c
    end

    # Returns a copy of the dataset with the SQL DISTINCT clause.
    # The DISTINCT clause is used to remove duplicate rows from the
    # output.  If arguments are provided, uses a DISTINCT ON clause,
    # in which case it will only be distinct on those columns, instead
    # of all returned columns.  Raises an error if arguments
    # are given and DISTINCT ON is not supported.
    #
    #  DB[:items].distinct # SQL: SELECT DISTINCT * FROM items
    #  DB[:items].order(:id).distinct(:id) # SQL: SELECT DISTINCT ON (id) * FROM items ORDER BY id
    def distinct(*args)
      raise(InvalidOperation, "DISTINCT ON not supported") if !args.empty? && !supports_distinct_on?
      clone(:distinct => args)
    end

    # Adds an EXCEPT clause using a second dataset object.
    # An EXCEPT compound dataset returns all rows in the current dataset
    # that are not in the given dataset.
    # Raises an +InvalidOperation+ if the operation is not supported.
    # Options:
    # :alias :: Use the given value as the from_self alias
    # :all :: Set to true to use EXCEPT ALL instead of EXCEPT, so duplicate rows can occur
    # :from_self :: Set to false to not wrap the returned dataset in a from_self, use with care.
    #
    #   DB[:items].except(DB[:other_items])
    #   # SELECT * FROM (SELECT * FROM items EXCEPT SELECT * FROM other_items) AS t1
    #
    #   DB[:items].except(DB[:other_items], :all=>true, :from_self=>false)
    #   # SELECT * FROM items EXCEPT ALL SELECT * FROM other_items
    #
    #   DB[:items].except(DB[:other_items], :alias=>:i)
    #   # SELECT * FROM (SELECT * FROM items EXCEPT SELECT * FROM other_items) AS i
    def except(dataset, opts={})
      opts = {:all=>opts} unless opts.is_a?(Hash)
      raise(InvalidOperation, "EXCEPT not supported") unless supports_intersect_except?
      raise(InvalidOperation, "EXCEPT ALL not supported") if opts[:all] && !supports_intersect_except_all?
      compound_clone(:except, dataset, opts)
    end

    # Performs the inverse of Dataset#filter.  Note that if you have multiple filter
    # conditions, this is not the same as a negation of all conditions.
    #
    #   DB[:items].exclude(:category => 'software')
    #   # SELECT * FROM items WHERE (category != 'software')
    #   
    #   DB[:items].exclude(:category => 'software', :id=>3)
    #   # SELECT * FROM items WHERE ((category != 'software') OR (id != 3))
    def exclude(*cond, &block)
      _filter_or_exclude(true, @opts[:having] ? :having : :where, *cond, &block)
    end

    # Inverts the given conditions and adds them to the HAVING clause.
    #
    #   DB[:items].select_group(:name).exclude_having{count(name) < 2}
    #   # SELECT name FROM items GROUP BY name HAVING (count(name) >= 2)
    def exclude_having(*cond, &block)
      _filter_or_exclude(true, :having, *cond, &block)
    end

    # Inverts the given conditions and adds them to the WHERE clause.
    #
    #   DB[:items].select_group(:name).exclude_where(:category => 'software')
    #   # SELECT * FROM items WHERE (category != 'software')
    #
    #   DB[:items].select_group(:name).
    #     exclude_having{count(name) < 2}.
    #     exclude_where(:category => 'software')
    #   # SELECT name FROM items WHERE (category != 'software')
    #   # GROUP BY name HAVING (count(name) >= 2)
    def exclude_where(*cond, &block)
      _filter_or_exclude(true, :where, *cond, &block)
    end

    # Returns a copy of the dataset with the given conditions imposed upon it.  
    # If the query already has a HAVING clause, then the conditions are imposed in the 
    # HAVING clause. If not, then they are imposed in the WHERE clause.
    # 
    # filter accepts the following argument types:
    #
    # * Hash - list of equality/inclusion expressions
    # * Array - depends:
    #   * If first member is a string, assumes the rest of the arguments
    #     are parameters and interpolates them into the string.
    #   * If all members are arrays of length two, treats the same way
    #     as a hash, except it allows for duplicate keys to be
    #     specified.
    #   * Otherwise, treats each argument as a separate condition.
    # * String - taken literally
    # * Symbol - taken as a boolean column argument (e.g. WHERE active)
    # * Sequel::SQL::BooleanExpression - an existing condition expression,
    #   probably created using the Sequel expression filter DSL.
    #
    # filter also takes a block, which should return one of the above argument
    # types, and is treated the same way.  This block yields a virtual row object,
    # which is easy to use to create identifiers and functions.  For more details
    # on the virtual row support, see the {"Virtual Rows" guide}[link:files/doc/virtual_rows_rdoc.html]
    #
    # If both a block and regular argument are provided, they get ANDed together.
    #
    # Examples:
    #
    #   DB[:items].filter(:id => 3)
    #   # SELECT * FROM items WHERE (id = 3)
    #
    #   DB[:items].filter('price < ?', 100)
    #   # SELECT * FROM items WHERE price < 100
    #
    #   DB[:items].filter([[:id, [1,2,3]], [:id, 0..10]])
    #   # SELECT * FROM items WHERE ((id IN (1, 2, 3)) AND ((id >= 0) AND (id <= 10)))
    #
    #   DB[:items].filter('price < 100')
    #   # SELECT * FROM items WHERE price < 100
    #
    #   DB[:items].filter(:active)
    #   # SELECT * FROM items WHERE :active
    #
    #   DB[:items].filter{price < 100}
    #   # SELECT * FROM items WHERE (price < 100)
    # 
    # Multiple filter calls can be chained for scoping:
    #
    #   software = dataset.filter(:category => 'software').filter{price < 100}
    #   # SELECT * FROM items WHERE ((category = 'software') AND (price < 100))
    #
    # See the the {"Dataset Filtering" guide}[link:files/doc/dataset_filtering_rdoc.html] for more examples and details.
    def filter(*cond, &block)
      _filter(@opts[:having] ? :having : :where, *cond, &block)
    end
    
    # Returns a cloned dataset with a :update lock style.
    #
    #   DB[:table].for_update # SELECT * FROM table FOR UPDATE
    def for_update
      lock_style(:update)
{'fraction_non_alphanumeric': 0.10241095890410959, 'fraction_numerical': 0.005041095890410959, 'mean_word_length': 3.004168494953927, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 33, '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': '6930738', 'n_tokens_mistral': 5533, 'n_tokens_neox': 5286, 'n_words': 1985}