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_evaled in the context of a new instance of this class.

`VirtualRow` uses `method_missing` to return either an `Identifier`, `QualifiedIdentifier`, `Function`, or `WindowFunction`, 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 either a `Function` or `WindowFunction`, depending on the first argument to the method. 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 `WindowFunction`. If a second argument is provided, it should be a hash of options which are passed to 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

### Constants

WILDCARD | = | LiteralString.new('*').freeze
QUESTION_MARK | = | LiteralString.new('?').freeze
COMMA_SEPARATOR | = | LiteralString.new(', ').freeze
DOUBLE_UNDERSCORE | = | '__'.freeze
DISTINCT | = | ["DISTINCT ".freeze].freeze
COMMA_ARRAY | = | [COMMA_SEPARATOR].freeze

### Public Instance methods

`(s)

Return a literal string created with the given string.

[Source]

          # File lib/sequel/sql.rb, line 1503
    1503:       def `(s)
    1504:         Sequel::LiteralString.new(s)
    1505:       end

method_missing(m, *args, &block)

Return an `Identifier`, `QualifiedIdentifier`, `Function`, or `WindowFunction`, 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 1510
    1510:       def method_missing(m, *args, &block)
    1511:         if block
    1512:           if args.empty?
    1513:             Function.new(m)
    1514:           else
    1515:             case arg = args.shift
    1516:             when :*
    1517:               Function.new(m, WILDCARD)
    1518:             when :distinct
    1519:               Function.new(m, PlaceholderLiteralString.new(DISTINCT + COMMA_ARRAY * (args.length-1), args))
    1520:             when :over
    1521:               opts = args.shift || {}
    1522:               fun_args = ::Kernel.Array(opts[:*] ? WILDCARD : opts[:args])
    1523:               WindowFunction.new(Function.new(m, *fun_args), Window.new(opts))
    1524:             else
    1525:               raise Error, 'unsupported VirtualRow method argument used with block'
    1526:             end
    1527:           end
    1528:         elsif args.empty?
    1529:           table, column = m.to_s.split(DOUBLE_UNDERSCORE, 2)
    1530:           column ? QualifiedIdentifier.new(table, column) : Identifier.new(m)
    1531:         else
    1532:           Function.new(m, *args)
    1533:         end
    1534:       end

[Validate]