Class: Sequel::SQL::VirtualRow

Inherits:
BasicObject
Includes:
OperatorBuilders
Defined in:
lib/sequel/sql.rb

Overview

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#where, 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.where{|r| r.name < 2} # SELECT * FROM t WHERE (name < 2)

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

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

# Functions
ds.where{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.where{|o| o.&({:a=>1}, :b)}    # SELECT * FROM t WHERE ((a = 1) AND b)
ds.where{|o| o.|({:a=>1}, :b)}    # SELECT * FROM t WHERE ((a = 1) OR b)
ds.where{|o| o.~({:a=>1})}        # SELECT * FROM t WHERE (a != 1)
ds.where{|o| o.~({:a=>1, :b=>2})} # SELECT * FROM t WHERE ((a != 1) OR (b != 2))

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

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

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

Constant Summary collapse

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

Constants inherited from BasicObject

BasicObject::KEEP_METHODS

Instance Method Summary collapse

Methods included from OperatorBuilders

#**, #~

Methods inherited from BasicObject

const_missing, remove_methods!

Constructor Details

#initialize ⇒ VirtualRow

Returns a new instance of VirtualRow.



1899
1900
1901
 
# File 'lib/sequel/sql.rb', line 1899

def initialize
  freeze
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(m, *args, &block) ⇒ Object

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.



1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
 
# File 'lib/sequel/sql.rb', line 1911

def method_missing(m, *args, &block)
  if block
    if args.empty?
      Function.new(m)
    else
      case args.shift
      when :*
        Function.new(m, *args).*
      when :distinct
        Function.new(m, *args).distinct
      when :over
        opts = args.shift || OPTS
        f = Function.new(m, *::Kernel.Array(opts[:args]))
        f = f.* if opts[:*]
        f.over(opts)
      else
        Kernel.raise(Error, 'unsupported VirtualRow method argument used with block')
      end
    end
  elsif args.empty?
    if Sequel.split_symbols?
      table, column = m.to_s.split(DOUBLE_UNDERSCORE, 2)
      column ? QualifiedIdentifier.new(table, column) : Identifier.new(m)
    else
      Identifier.new(m)
    end
  else
    Function.new(m, *args)
  end
end

Instance Method Details

#`(s) ⇒ Object

Return a literal string created with the given string.



1904
1905
1906
 
# File 'lib/sequel/sql.rb', line 1904

def `(s)
  Sequel::LiteralString.new(s)
end