Class Sequel::SQL::VirtualRow
In: lib/sequel/sql.rb
Parent: BasicObject

The purpose of the VirtualRow class is to allow the easy creation of SQL identifiers and functions without relying on methods defined on Symbol. This is useful if another library defines the methods defined by Sequel, if you are running on ruby 1.9, or if you are not using the core extensions.

An instance of this class is yielded to the block supplied to Dataset#filter, Dataset#order, and Dataset#select (and the other methods that accept a block and pass it to one of those methods). If the block doesn‘t take an argument, the block is instance_execed in the context of an instance of this class.

VirtualRow uses method_missing to return either an Identifier, QualifiedIdentifier, or Function depending on how it is called.

If a block is not given, creates one of the following objects:

Function :Returned if any arguments are supplied, using the method name as the function name, and the arguments as the function arguments.
QualifiedIdentifier :Returned if the method name contains __, with the table being the part before __, and the column being the part after.
Identifier :Returned otherwise, using the method name.

If a block is given, it returns a Function. Note that the block is currently not called by the code, though this may change in a future version. If the first argument is:

no arguments given :creates a Function with no arguments.
:* :creates a Function with a literal wildcard argument (*), mostly useful for COUNT.
:distinct :creates a Function that prepends DISTINCT to the rest of the arguments, mostly useful for aggregate functions.
:over :creates a Function with a window. If a second argument is provided, it should be a hash of options which are used to create the Window (with possible keys :window, :partition, :order, and :frame). The arguments to the function itself should be specified as :*=>true for a wildcard, or via the :args option.

Examples:

  ds = DB[:t]

  # Argument yielded to block
  ds.filter{|r| r.name < 2} # SELECT * FROM t WHERE (name < 2)

  # Block without argument (instance_eval)
  ds.filter{name < 2} # SELECT * FROM t WHERE (name < 2)

  # Qualified identifiers
  ds.filter{table__column + 1 < 2} # SELECT * FROM t WHERE ((table.column + 1) < 2)

  # Functions
  ds.filter{is_active(1, 'arg2')} # SELECT * FROM t WHERE is_active(1, 'arg2')
  ds.select{version{}} # SELECT version() FROM t
  ds.select{count(:*){}} # SELECT count(*) FROM t
  ds.select{count(:distinct, col1){}} # SELECT count(DISTINCT col1) FROM t

  # Window Functions
  ds.select{rank(:over){}} # SELECT rank() OVER () FROM t
  ds.select{count(:over, :*=>true){}} # SELECT count(*) OVER () FROM t
  ds.select{sum(:over, :args=>col1, :partition=>col2, :order=>col3){}} # SELECT sum(col1) OVER (PARTITION BY col2 ORDER BY col3) FROM t

  # Math Operators
  ds.select{|o| o.+(1, :a).as(:b)} # SELECT (1 + a) AS b FROM t
  ds.select{|o| o.-(2, :a).as(:b)} # SELECT (2 - a) AS b FROM t
  ds.select{|o| o.*(3, :a).as(:b)} # SELECT (3 * a) AS b FROM t
  ds.select{|o| o./(4, :a).as(:b)} # SELECT (4 / a) AS b FROM t

  # Boolean Operators
  ds.filter{|o| o.&({:a=>1}, :b)}    # SELECT * FROM t WHERE ((a = 1) AND b)
  ds.filter{|o| o.|({:a=>1}, :b)}    # SELECT * FROM t WHERE ((a = 1) OR b)
  ds.filter{|o| o.~({:a=>1})}        # SELECT * FROM t WHERE (a != 1)
  ds.filter{|o| o.~({:a=>1, :b=>2})} # SELECT * FROM t WHERE ((a != 1) OR (b != 2))

  # Inequality Operators
  ds.filter{|o| o.>(1, :a)}  # SELECT * FROM t WHERE (1 > a)
  ds.filter{|o| o.<(2, :a)}  # SELECT * FROM t WHERE (2 < a)
  ds.filter{|o| o.>=(3, :a)} # SELECT * FROM t WHERE (3 >= a)
  ds.filter{|o| o.<=(4, :a)} # SELECT * FROM t WHERE (4 <= a)

  # Literal Strings
  ds.filter{{a=>`some SQL`}} # SELECT * FROM t WHERE (a = some SQL)

For a more detailed explanation, see the Virtual Rows guide.

Methods

`   method_missing  

Included Modules

OperatorBuilders

Classes and Modules

Class Sequel::SQL::VirtualRow::Window
Class Sequel::SQL::VirtualRow::Wrapper

Constants

QUESTION_MARK = LiteralString.new('?').freeze
DOUBLE_UNDERSCORE = '__'.freeze
VIRTUAL_ROW = new

Public Instance methods

Return a literal string created with the given string.

[Source]

      # File lib/sequel/sql.rb, line 1849
1849:       def `(s)
1850:         Sequel::LiteralString.new(s)
1851:       end

Return an Identifier, QualifiedIdentifier, or Function, depending on arguments and whether a block is provided. Does not currently call the block. See the class level documentation.

[Source]

      # File lib/sequel/sql.rb, line 1856
1856:       def method_missing(m, *args, &block)
1857:         if block
1858:           if args.empty?
1859:             Function.new(m)
1860:           else
1861:             case args.shift
1862:             when :*
1863:               Function.new(m, *args).*
1864:             when :distinct
1865:               Function.new(m, *args).distinct
1866:             when :over
1867:               opts = args.shift || OPTS
1868:               f = Function.new(m, *::Kernel.Array(opts[:args]))
1869:               f = f.* if opts[:*]
1870:               f.over(opts)
1871:             else
1872:               Kernel.raise(Error, 'unsupported VirtualRow method argument used with block')
1873:             end
1874:           end
1875:         elsif args.empty?
1876:           table, column = m.to_s.split(DOUBLE_UNDERSCORE, 2)
1877:           column ? QualifiedIdentifier.new(table, column) : Identifier.new(m)
1878:         else
1879:           Function.new(m, *args)
1880:         end
1881:       end
1882: 
1883:       Sequel::VIRTUAL_ROW = new
1884:     end

[Validate]