class Sequel::SQL::VirtualRow
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 theWindow
(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.
Constants
- DOUBLE_UNDERSCORE
- QUESTION_MARK
Public Instance Methods
Return a literal string created with the given string.
# File lib/sequel/sql.rb, line 1766 def `(s) Sequel::LiteralString.new(s) 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.
# File lib/sequel/sql.rb, line 1773 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? table, column = m.to_s.split(DOUBLE_UNDERSCORE, 2) column ? QualifiedIdentifier.new(table, column) : Identifier.new(m) else Function.new(m, *args) end end