class Sequel::Dataset
A dataset represents an SQL
query. Datasets can be used to select, insert, update and delete records.
Query
results are always retrieved on demand, so a dataset can be kept around and reused indefinitely (datasets never cache results):
my_posts = DB[:posts].where(author: 'david') # no records are retrieved my_posts.all # records are retrieved my_posts.all # records are retrieved again
Datasets are frozen and use a functional style where modification methods return modified copies of the the dataset. This allows you to reuse datasets:
posts = DB[:posts] davids_posts = posts.where(author: 'david') old_posts = posts.where{stamp < Date.today - 7} davids_old_posts = davids_posts.where{stamp < Date.today - 7}
Datasets are Enumerable objects, so they can be manipulated using any of the Enumerable methods, such as map, inject, etc.
For more information, see the “Dataset Basics” guide.
Constants
- OPTS
- TRUE_FREEZE
Whether
Dataset#freeze
can actually freeze datasets. True only on ruby 2.4+, as it requires clone(freeze: false)
1 - Methods that return modified datasets
↑ topConstants
- COLUMN_CHANGE_OPTS
The dataset options that require the removal of cached columns if changed.
- CONDITIONED_JOIN_TYPES
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.- EMPTY_ARRAY
- EXTENSIONS
Hash
of extension name symbols to callable objects to load the extension into theDataset
object (usually by extending it with a module defined in the extension).- JOIN_METHODS
All methods that return modified datasets with a joined table added.
- NON_SQL_OPTIONS
Which options don't affect the
SQL
generation. Used by simple_select_all? to determine if this is a simple SELECT * FROM table.- QUERY_METHODS
Methods that return modified datasets
- SIMPLE_SELECT_ALL_ALLOWED_FROM
From types allowed to be considered a simple_select_all
- UNCONDITIONED_JOIN_TYPES
These symbols have _join methods created (e.g. natural_join). They accept a table argument and options hash which is passed to
join_table
, and they raise an error if called with a block.
Public Class Methods
Register an extension callback for Dataset
objects. ext should be the extension name symbol, and mod should either be a Module that the dataset is extended with, or a callable object called with the database object. If mod is not provided, a block can be provided and is treated as the mod object.
If mod is a module, this also registers a Database
extension that will extend all of the database's datasets.
# File lib/sequel/dataset/query.rb 55 def self.register_extension(ext, mod=nil, &block) 56 if mod 57 raise(Error, "cannot provide both mod and block to Dataset.register_extension") if block 58 if mod.is_a?(Module) 59 block = proc{|ds| ds.extend(mod)} 60 Sequel::Database.register_extension(ext){|db| db.extend_datasets(mod)} 61 else 62 block = mod 63 end 64 end 65 Sequel.synchronize{EXTENSIONS[ext] = block} 66 end
Public Instance Methods
Save original clone implementation, as some other methods need to call it internally.
Returns a new clone of the dataset 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.
# File lib/sequel/dataset/query.rb 84 def clone(opts = (return self; nil)) 85 c = super(:freeze=>false) 86 c.opts.merge!(opts) 87 unless opts.each_key{|o| break if COLUMN_CHANGE_OPTS.include?(o)} 88 c.clear_columns_cache 89 end 90 c.freeze 91 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. If a block is given, it is treated as a virtual row block, similar to where
. 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 DB[:items].order(:id).distinct{func(:id)} # SQL: SELECT DISTINCT ON (func(id)) * FROM items ORDER BY id
There is support for emualting the DISTINCT ON support in MySQL
, but it does not support the ORDER of the dataset, and also doesn't work in many cases if the ONLY_FULL_GROUP_BY sql_mode is used, which is the default on MySQL
5.7.5+.
# File lib/sequel/dataset/query.rb 121 def distinct(*args, &block) 122 virtual_row_columns(args, block) 123 if args.empty? 124 cached_dataset(:_distinct_ds){clone(:distinct => EMPTY_ARRAY)} 125 else 126 raise(InvalidOperation, "DISTINCT ON not supported") unless supports_distinct_on? 127 clone(:distinct => args.freeze) 128 end 129 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
# File lib/sequel/dataset/query.rb 148 def except(dataset, opts=OPTS) 149 raise(InvalidOperation, "EXCEPT not supported") unless supports_intersect_except? 150 raise(InvalidOperation, "EXCEPT ALL not supported") if opts[:all] && !supports_intersect_except_all? 151 compound_clone(:except, dataset, opts) 152 end
Performs the inverse of Dataset#where
. 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))
Also note that SQL
uses 3-valued boolean logic (true
, false
, NULL
), so the inverse of a true condition is a false condition, and will still not match rows that were NULL originally. If you take the earlier example:
DB[:items].exclude(category: 'software') # SELECT * FROM items WHERE (category != 'software')
Note that this does not match rows where category
is NULL
. This is because NULL
is an unknown value, and you do not know whether or not the NULL
category is software
. You can explicitly specify how to handle NULL
values if you want:
DB[:items].exclude(Sequel.~(category: nil) & {category: 'software'}) # SELECT * FROM items WHERE ((category IS NULL) OR (category != 'software'))
# File lib/sequel/dataset/query.rb 178 def exclude(*cond, &block) 179 add_filter(:where, cond, true, &block) 180 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)
See documentation for exclude for how inversion is handled in regards to SQL
3-valued boolean logic.
# File lib/sequel/dataset/query.rb 189 def exclude_having(*cond, &block) 190 add_filter(:having, cond, true, &block) 191 end
Return a clone of the dataset loaded with the given dataset extensions. If no related extension file exists or the extension does not have specific support for Dataset
objects, an Error
will be raised.
# File lib/sequel/dataset/query.rb 197 def extension(*a) 198 c = _clone(:freeze=>false) 199 c.send(:_extension!, a) 200 c.freeze 201 end
Alias for where.
# File lib/sequel/dataset/query.rb 213 def filter(*cond, &block) 214 where(*cond, &block) 215 end
Returns a cloned dataset with a :update lock style.
DB[:table].for_update # SELECT * FROM table FOR UPDATE
# File lib/sequel/dataset/query.rb 220 def for_update 221 cached_dataset(:_for_update_ds){lock_style(:update)} 222 end
Returns a copy of the dataset with the source changed. If no source is given, removes all tables. If multiple sources are given, it is the same as using a CROSS JOIN (cartesian product) between all tables. If a block is given, it is treated as a virtual row block, similar to where
.
DB[:items].from # SQL: SELECT * DB[:items].from(:blah) # SQL: SELECT * FROM blah DB[:items].from(:blah, :foo) # SQL: SELECT * FROM blah, foo DB[:items].from{fun(arg)} # SQL: SELECT * FROM fun(arg)
# File lib/sequel/dataset/query.rb 233 def from(*source, &block) 234 virtual_row_columns(source, block) 235 table_alias_num = 0 236 ctes = nil 237 source.map! do |s| 238 case s 239 when Dataset 240 if hoist_cte?(s) 241 ctes ||= [] 242 ctes += s.opts[:with] 243 s = s.clone(:with=>nil) 244 end 245 SQL::AliasedExpression.new(s, dataset_alias(table_alias_num+=1)) 246 when Symbol 247 sch, table, aliaz = split_symbol(s) 248 if aliaz 249 s = sch ? SQL::QualifiedIdentifier.new(sch, table) : SQL::Identifier.new(table) 250 SQL::AliasedExpression.new(s, aliaz.to_sym) 251 else 252 s 253 end 254 else 255 s 256 end 257 end 258 o = {:from=>source.empty? ? nil : source.freeze} 259 o[:with] = ((opts[:with] || EMPTY_ARRAY) + ctes).freeze if ctes 260 o[:num_dataset_sources] = table_alias_num if table_alias_num > 0 261 clone(o) 262 end
Returns a dataset selecting from the current dataset. Options:
- :alias
-
Controls the alias of the table
- :column_aliases
-
Also aliases columns, using derived column lists. Only used in conjunction with :alias.
ds = DB[:items].order(:name).select(:id, :name) # SELECT id,name FROM items ORDER BY name ds.from_self # SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS t1 ds.from_self(alias: :foo) # SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS foo ds.from_self(alias: :foo, column_aliases: [:c1, :c2]) # SELECT * FROM (SELECT id, name FROM items ORDER BY name) AS foo(c1, c2)
# File lib/sequel/dataset/query.rb 281 def from_self(opts=OPTS) 282 fs = {} 283 @opts.keys.each{|k| fs[k] = nil unless non_sql_option?(k)} 284 pr = proc do 285 c = clone(fs).from(opts[:alias] ? as(opts[:alias], opts[:column_aliases]) : self) 286 if cols = _columns 287 c.send(:columns=, cols) 288 end 289 c 290 end 291 292 cache ? cached_dataset(:_from_self_ds, &pr) : pr.call 293 end
Match any of the columns to any of the patterns. The terms can be strings (which use LIKE) or regular expressions if the database supports that. Note that the total number of pattern matches will be Array(columns).length * Array(terms).length, which could cause performance issues.
Options (all are boolean):
- :all_columns
-
All columns must be matched to any of the given patterns.
- :all_patterns
-
All patterns must match at least one of the columns.
- :case_insensitive
-
Use a case insensitive pattern match (the default is case sensitive if the database supports it).
If both :all_columns and :all_patterns are true, all columns must match all patterns.
Examples:
dataset.grep(:a, '%test%') # SELECT * FROM items WHERE (a LIKE '%test%' ESCAPE '\') dataset.grep([:a, :b], %w'%test% foo') # SELECT * FROM items WHERE ((a LIKE '%test%' ESCAPE '\') OR (a LIKE 'foo' ESCAPE '\') # OR (b LIKE '%test%' ESCAPE '\') OR (b LIKE 'foo' ESCAPE '\')) dataset.grep([:a, :b], %w'%foo% %bar%', all_patterns: true) # SELECT * FROM a WHERE (((a LIKE '%foo%' ESCAPE '\') OR (b LIKE '%foo%' ESCAPE '\')) # AND ((a LIKE '%bar%' ESCAPE '\') OR (b LIKE '%bar%' ESCAPE '\'))) dataset.grep([:a, :b], %w'%foo% %bar%', all_columns: true) # SELECT * FROM a WHERE (((a LIKE '%foo%' ESCAPE '\') OR (a LIKE '%bar%' ESCAPE '\')) # AND ((b LIKE '%foo%' ESCAPE '\') OR (b LIKE '%bar%' ESCAPE '\'))) dataset.grep([:a, :b], %w'%foo% %bar%', all_patterns: true, all_columns: true) # SELECT * FROM a WHERE ((a LIKE '%foo%' ESCAPE '\') AND (b LIKE '%foo%' ESCAPE '\') # AND (a LIKE '%bar%' ESCAPE '\') AND (b LIKE '%bar%' ESCAPE '\'))
# File lib/sequel/dataset/query.rb 330 def grep(columns, patterns, opts=OPTS) 331 if opts[:all_patterns] 332 conds = Array(patterns).map do |pat| 333 SQL::BooleanExpression.new(opts[:all_columns] ? :AND : :OR, *Array(columns).map{|c| SQL::StringExpression.like(c, pat, opts)}) 334 end 335 where(SQL::BooleanExpression.new(opts[:all_patterns] ? :AND : :OR, *conds)) 336 else 337 conds = Array(columns).map do |c| 338 SQL::BooleanExpression.new(:OR, *Array(patterns).map{|pat| SQL::StringExpression.like(c, pat, opts)}) 339 end 340 where(SQL::BooleanExpression.new(opts[:all_columns] ? :AND : :OR, *conds)) 341 end 342 end
Returns a copy of the dataset with the results grouped by the value of the given columns. If a block is given, it is treated as a virtual row block, similar to where
.
DB[:items].group(:id) # SELECT * FROM items GROUP BY id DB[:items].group(:id, :name) # SELECT * FROM items GROUP BY id, name DB[:items].group{[a, sum(b)]} # SELECT * FROM items GROUP BY a, sum(b)
# File lib/sequel/dataset/query.rb 351 def group(*columns, &block) 352 virtual_row_columns(columns, block) 353 clone(:group => (columns.compact.empty? ? nil : columns.freeze)) 354 end
Returns a dataset grouped by the given column with count by group. Column aliases may be supplied, and will be included in the select clause. If a block is given, it is treated as a virtual row block, similar to where
.
Examples:
DB[:items].group_and_count(:name).all # SELECT name, count(*) AS count FROM items GROUP BY name # => [{:name=>'a', :count=>1}, ...] DB[:items].group_and_count(:first_name, :last_name).all # SELECT first_name, last_name, count(*) AS count FROM items GROUP BY first_name, last_name # => [{:first_name=>'a', :last_name=>'b', :count=>1}, ...] DB[:items].group_and_count(Sequel[:first_name].as(:name)).all # SELECT first_name AS name, count(*) AS count FROM items GROUP BY first_name # => [{:name=>'a', :count=>1}, ...] DB[:items].group_and_count{substr(:first_name, 1, 1).as(:initial)}.all # SELECT substr(first_name, 1, 1) AS initial, count(*) AS count FROM items GROUP BY substr(first_name, 1, 1) # => [{:initial=>'a', :count=>1}, ...]
# File lib/sequel/dataset/query.rb 382 def group_and_count(*columns, &block) 383 select_group(*columns, &block).select_append(COUNT_OF_ALL_AS_COUNT) 384 end
Returns a copy of the dataset with the given columns added to the list of existing columns to group on. If no existing columns are present this method simply sets the columns as the initial ones to group on.
DB[:items].group_append(:b) # SELECT * FROM items GROUP BY b DB[:items].group(:a).group_append(:b) # SELECT * FROM items GROUP BY a, b
# File lib/sequel/dataset/query.rb 392 def group_append(*columns, &block) 393 columns = @opts[:group] + columns if @opts[:group] 394 group(*columns, &block) 395 end
Alias of group
# File lib/sequel/dataset/query.rb 357 def group_by(*columns, &block) 358 group(*columns, &block) 359 end
Adds the appropriate CUBE syntax to GROUP BY.
# File lib/sequel/dataset/query.rb 398 def group_cube 399 raise Error, "GROUP BY CUBE not supported on #{db.database_type}" unless supports_group_cube? 400 clone(:group_options=>:cube) 401 end
Adds the appropriate ROLLUP syntax to GROUP BY.
# File lib/sequel/dataset/query.rb 404 def group_rollup 405 raise Error, "GROUP BY ROLLUP not supported on #{db.database_type}" unless supports_group_rollup? 406 clone(:group_options=>:rollup) 407 end
Adds the appropriate GROUPING SETS syntax to GROUP BY.
# File lib/sequel/dataset/query.rb 410 def grouping_sets 411 raise Error, "GROUP BY GROUPING SETS not supported on #{db.database_type}" unless supports_grouping_sets? 412 clone(:group_options=>:"grouping sets") 413 end
Returns a copy of the dataset with the HAVING conditions changed. See where
for argument types.
DB[:items].group(:sum).having(sum: 10) # SELECT * FROM items GROUP BY sum HAVING (sum = 10)
# File lib/sequel/dataset/query.rb 419 def having(*cond, &block) 420 add_filter(:having, cond, &block) 421 end
Adds an INTERSECT clause using a second dataset object. An INTERSECT compound dataset returns all rows in both the current dataset and 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 INTERSECT ALL instead of INTERSECT, 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].intersect(DB[:other_items]) # SELECT * FROM (SELECT * FROM items INTERSECT SELECT * FROM other_items) AS t1 DB[:items].intersect(DB[:other_items], all: true, from_self: false) # SELECT * FROM items INTERSECT ALL SELECT * FROM other_items DB[:items].intersect(DB[:other_items], alias: :i) # SELECT * FROM (SELECT * FROM items INTERSECT SELECT * FROM other_items) AS i
# File lib/sequel/dataset/query.rb 440 def intersect(dataset, opts=OPTS) 441 raise(InvalidOperation, "INTERSECT not supported") unless supports_intersect_except? 442 raise(InvalidOperation, "INTERSECT ALL not supported") if opts[:all] && !supports_intersect_except_all? 443 compound_clone(:intersect, dataset, opts) 444 end
Inverts the current WHERE and HAVING clauses. If there is neither a WHERE or HAVING clause, adds a WHERE clause that is always false.
DB[:items].where(category: 'software').invert # SELECT * FROM items WHERE (category != 'software') DB[:items].where(category: 'software', id: 3).invert # SELECT * FROM items WHERE ((category != 'software') OR (id != 3))
See documentation for exclude for how inversion is handled in regards to SQL
3-valued boolean logic.
# File lib/sequel/dataset/query.rb 457 def invert 458 cached_dataset(:_invert_ds) do 459 having, where = @opts.values_at(:having, :where) 460 if having.nil? && where.nil? 461 where(false) 462 else 463 o = {} 464 o[:having] = SQL::BooleanExpression.invert(having) if having 465 o[:where] = SQL::BooleanExpression.invert(where) if where 466 clone(o) 467 end 468 end 469 end
Alias of inner_join
# File lib/sequel/dataset/query.rb 472 def join(*args, &block) 473 inner_join(*args, &block) 474 end
Returns a joined dataset. Not usually called directly, users should use the appropriate join method (e.g. join, left_join, natural_join, cross_join) which fills in the type
argument.
Takes the following arguments:
- type
-
The type of join to do (e.g. :inner)
- table
-
table to join into the current dataset. Generally one of the following types:
String
,Symbol
-
identifier used as table or view name
Dataset
-
a subselect is performed with an alias of tN for some value of N
SQL::Function
-
set returning function
SQL::AliasedExpression
-
already aliased expression. Uses given alias unless overridden by the :table_alias option.
- expr
-
conditions used when joining, depends on type:
Hash
,Array
of pairs-
Assumes key (1st arg) is column of joined table (unless already qualified), and value (2nd arg) is column of the last joined or primary table (or the :implicit_qualifier option). To specify multiple conditions on a single joined table column, you must use an array. Uses a JOIN with an ON clause.
Array
-
If all members of the array are symbols, considers them as columns and uses a JOIN with a USING clause. Most databases will remove duplicate columns from the result set if this is used.
- nil
-
If a block is not given, doesn't use ON or USING, so the JOIN should be a NATURAL or CROSS join. If a block is given, uses an ON clause based on the block, see below.
- otherwise
-
Treats the argument as a filter expression, so strings are considered literal, symbols specify boolean columns, and
Sequel
expressions can be used. Uses a JOIN with an ON clause.
- options
-
a hash of options, with the following keys supported:
- :table_alias
-
Override the table alias used when joining. In general you shouldn't use this option, you should provide the appropriate
SQL::AliasedExpression
as the table argument. - :implicit_qualifier
-
The name to use for qualifying implicit conditions. By default, the last joined or primary table is used.
- :reset_implicit_qualifier
-
Can set to false to ignore this join when future joins determine qualifier for implicit conditions.
- :qualify
-
Can be set to false to not do any implicit qualification. Can be set to :deep to use the
Qualifier
AST Transformer, which will attempt to qualify subexpressions of the expression tree. Can be set to :symbol to only qualify symbols. Defaults to the value of default_join_table_qualification.
- block
-
The block argument should only be given if a JOIN with an ON clause is used, in which case it yields the table alias/name for the table currently being joined, the table alias/name for the last joined (or first table), and an array of previous
SQL::JoinClause
. Unlikewhere
, this block is not treated as a virtual row block.
Examples:
DB[:a].join_table(:cross, :b) # SELECT * FROM a CROSS JOIN b DB[:a].join_table(:inner, DB[:b], c: d) # SELECT * FROM a INNER JOIN (SELECT * FROM b) AS t1 ON (t1.c = a.d) DB[:a].join_table(:left, Sequel[:b].as(:c), [:d]) # SELECT * FROM a LEFT JOIN b AS c USING (d) DB[:a].natural_join(:b).join_table(:inner, :c) do |ta, jta, js| (Sequel.qualify(ta, :d) > Sequel.qualify(jta, :e)) & {Sequel.qualify(ta, :f)=>DB.from(js.first.table).select(:g)} end # SELECT * FROM a NATURAL JOIN b INNER JOIN c # ON ((c.d > b.e) AND (c.f IN (SELECT g FROM b)))
# File lib/sequel/dataset/query.rb 535 def join_table(type, table, expr=nil, options=OPTS, &block) 536 if hoist_cte?(table) 537 s, ds = hoist_cte(table) 538 return s.join_table(type, ds, expr, options, &block) 539 end 540 541 using_join = expr.is_a?(Array) && !expr.empty? && expr.all?{|x| x.is_a?(Symbol)} 542 if using_join && !supports_join_using? 543 h = {} 544 expr.each{|e| h[e] = e} 545 return join_table(type, table, h, options) 546 end 547 548 table_alias = options[:table_alias] 549 550 if table.is_a?(SQL::AliasedExpression) 551 table_expr = if table_alias 552 SQL::AliasedExpression.new(table.expression, table_alias, table.columns) 553 else 554 table 555 end 556 table = table_expr.expression 557 table_name = table_alias = table_expr.alias 558 elsif table.is_a?(Dataset) 559 if table_alias.nil? 560 table_alias_num = (@opts[:num_dataset_sources] || 0) + 1 561 table_alias = dataset_alias(table_alias_num) 562 end 563 table_name = table_alias 564 table_expr = SQL::AliasedExpression.new(table, table_alias) 565 else 566 table, implicit_table_alias = split_alias(table) 567 table_alias ||= implicit_table_alias 568 table_name = table_alias || table 569 table_expr = table_alias ? SQL::AliasedExpression.new(table, table_alias) : table 570 end 571 572 join = if expr.nil? and !block 573 SQL::JoinClause.new(type, table_expr) 574 elsif using_join 575 raise(Sequel::Error, "can't use a block if providing an array of symbols as expr") if block 576 SQL::JoinUsingClause.new(expr, type, table_expr) 577 else 578 last_alias = options[:implicit_qualifier] || @opts[:last_joined_table] || first_source_alias 579 qualify_type = options[:qualify] 580 if Sequel.condition_specifier?(expr) 581 expr = expr.map do |k, v| 582 qualify_type = default_join_table_qualification if qualify_type.nil? 583 case qualify_type 584 when false 585 nil # Do no qualification 586 when :deep 587 k = Sequel::Qualifier.new(table_name).transform(k) 588 v = Sequel::Qualifier.new(last_alias).transform(v) 589 else 590 k = qualified_column_name(k, table_name) if k.is_a?(Symbol) 591 v = qualified_column_name(v, last_alias) if v.is_a?(Symbol) 592 end 593 [k,v] 594 end 595 expr = SQL::BooleanExpression.from_value_pairs(expr) 596 end 597 if block 598 expr2 = yield(table_name, last_alias, @opts[:join] || EMPTY_ARRAY) 599 expr = expr ? SQL::BooleanExpression.new(:AND, expr, expr2) : expr2 600 end 601 SQL::JoinOnClause.new(expr, type, table_expr) 602 end 603 604 opts = {:join => ((@opts[:join] || EMPTY_ARRAY) + [join]).freeze} 605 opts[:last_joined_table] = table_name unless options[:reset_implicit_qualifier] == false 606 opts[:num_dataset_sources] = table_alias_num if table_alias_num 607 clone(opts) 608 end
Marks this dataset as a lateral dataset. If used in another dataset's FROM or JOIN clauses, it will surround the subquery with LATERAL to enable it to deal with previous tables in the query:
DB.from(:a, DB[:b].where(Sequel[:a][:c]=>Sequel[:b][:d]).lateral) # SELECT * FROM a, LATERAL (SELECT * FROM b WHERE (a.c = b.d))
# File lib/sequel/dataset/query.rb 629 def lateral 630 cached_dataset(:_lateral_ds){clone(:lateral=>true)} 631 end
If given an integer, the dataset will contain only the first l results. If given a range, it will contain only those at offsets within that range. If a second argument is given, it is used as an offset. To use an offset without a limit, pass nil as the first argument.
DB[:items].limit(10) # SELECT * FROM items LIMIT 10 DB[:items].limit(10, 20) # SELECT * FROM items LIMIT 10 OFFSET 20 DB[:items].limit(10...20) # SELECT * FROM items LIMIT 10 OFFSET 10 DB[:items].limit(10..20) # SELECT * FROM items LIMIT 11 OFFSET 10 DB[:items].limit(nil, 20) # SELECT * FROM items OFFSET 20
# File lib/sequel/dataset/query.rb 643 def limit(l, o = (no_offset = true; nil)) 644 return from_self.limit(l, o) if @opts[:sql] 645 646 if l.is_a?(Range) 647 no_offset = false 648 o = l.first 649 l = l.last - l.first + (l.exclude_end? ? 0 : 1) 650 end 651 l = l.to_i if l.is_a?(String) && !l.is_a?(LiteralString) 652 if l.is_a?(Integer) 653 raise(Error, 'Limits must be greater than or equal to 1') unless l >= 1 654 end 655 656 ds = clone(:limit=>l) 657 ds = ds.offset(o) unless no_offset 658 ds 659 end
Returns a cloned dataset with the given lock style. If style is a string, it will be used directly. You should never pass a string to this method that is derived from user input, as that can lead to SQL
injection.
A symbol may be used for database independent locking behavior, but all supported symbols have separate methods (e.g. for_update
).
DB[:items].lock_style('FOR SHARE NOWAIT') # SELECT * FROM items FOR SHARE NOWAIT DB[:items].lock_style('FOR UPDATE OF table1 SKIP LOCKED') # SELECT * FROM items FOR UPDATE OF table1 SKIP LOCKED
# File lib/sequel/dataset/query.rb 673 def lock_style(style) 674 clone(:lock => style) 675 end
Returns a cloned dataset without a row_proc.
ds = DB[:items].with_row_proc(:invert.to_proc) ds.all # => [{2=>:id}] ds.naked.all # => [{:id=>2}]
# File lib/sequel/dataset/query.rb 682 def naked 683 cached_dataset(:_naked_ds){with_row_proc(nil)} 684 end
Returns a copy of the dataset that will raise a DatabaseLockTimeout instead of waiting for rows that are locked by another transaction
DB[:items].for_update.nowait # SELECT * FROM items FOR UPDATE NOWAIT
# File lib/sequel/dataset/query.rb 691 def nowait 692 cached_dataset(:_nowait_ds) do 693 raise(Error, 'This dataset does not support raises errors instead of waiting for locked rows') unless supports_nowait? 694 clone(:nowait=>true) 695 end 696 end
Returns a copy of the dataset with a specified order. Can be safely combined with limit. If you call limit with an offset, it will override override the offset if you've called offset first.
DB[:items].offset(10) # SELECT * FROM items OFFSET 10
# File lib/sequel/dataset/query.rb 703 def offset(o) 704 o = o.to_i if o.is_a?(String) && !o.is_a?(LiteralString) 705 if o.is_a?(Integer) 706 raise(Error, 'Offsets must be greater than or equal to 0') unless o >= 0 707 end 708 clone(:offset => o) 709 end
Adds an alternate filter to an existing WHERE clause using OR. If there is no WHERE clause, then the default is WHERE true, and OR would be redundant, so return the dataset in that case.
DB[:items].where(:a).or(:b) # SELECT * FROM items WHERE a OR b DB[:items].or(:b) # SELECT * FROM items
# File lib/sequel/dataset/query.rb 717 def or(*cond, &block) 718 if @opts[:where].nil? 719 self 720 else 721 add_filter(:where, cond, false, :OR, &block) 722 end 723 end
Returns a copy of the dataset with the order changed. If the dataset has an existing order, it is ignored and overwritten with this order. If a nil is given the returned dataset has no order. This can accept multiple arguments of varying kinds, such as SQL
functions. If a block is given, it is treated as a virtual row block, similar to where
.
DB[:items].order(:name) # SELECT * FROM items ORDER BY name DB[:items].order(:a, :b) # SELECT * FROM items ORDER BY a, b DB[:items].order(Sequel.lit('a + b')) # SELECT * FROM items ORDER BY a + b DB[:items].order(Sequel[:a] + :b) # SELECT * FROM items ORDER BY (a + b) DB[:items].order(Sequel.desc(:name)) # SELECT * FROM items ORDER BY name DESC DB[:items].order(Sequel.asc(:name, :nulls=>:last)) # SELECT * FROM items ORDER BY name ASC NULLS LAST DB[:items].order{sum(name).desc} # SELECT * FROM items ORDER BY sum(name) DESC DB[:items].order(nil) # SELECT * FROM items
# File lib/sequel/dataset/query.rb 739 def order(*columns, &block) 740 virtual_row_columns(columns, block) 741 clone(:order => (columns.compact.empty?) ? nil : columns.freeze) 742 end
Returns a copy of the dataset with the order columns added to the end of the existing order.
DB[:items].order(:a).order(:b) # SELECT * FROM items ORDER BY b DB[:items].order(:a).order_append(:b) # SELECT * FROM items ORDER BY a, b
# File lib/sequel/dataset/query.rb 749 def order_append(*columns, &block) 750 columns = @opts[:order] + columns if @opts[:order] 751 order(*columns, &block) 752 end
Alias of order
# File lib/sequel/dataset/query.rb 755 def order_by(*columns, &block) 756 order(*columns, &block) 757 end
Alias of order_append.
# File lib/sequel/dataset/query.rb 760 def order_more(*columns, &block) 761 order_append(*columns, &block) 762 end
Returns a copy of the dataset with the order columns added to the beginning of the existing order.
DB[:items].order(:a).order(:b) # SELECT * FROM items ORDER BY b DB[:items].order(:a).order_prepend(:b) # SELECT * FROM items ORDER BY b, a
# File lib/sequel/dataset/query.rb 769 def order_prepend(*columns, &block) 770 ds = order(*columns, &block) 771 @opts[:order] ? ds.order_append(*@opts[:order]) : ds 772 end
Qualify to the given table, or first source if no table is given.
DB[:items].where(id: 1).qualify # SELECT items.* FROM items WHERE (items.id = 1) DB[:items].where(id: 1).qualify(:i) # SELECT i.* FROM items WHERE (i.id = 1)
# File lib/sequel/dataset/query.rb 781 def qualify(table=(cache=true; first_source)) 782 o = @opts 783 return self if o[:sql] 784 785 pr = proc do 786 h = {} 787 (o.keys & QUALIFY_KEYS).each do |k| 788 h[k] = qualified_expression(o[k], table) 789 end 790 h[:select] = [SQL::ColumnAll.new(table)].freeze if !o[:select] || o[:select].empty? 791 clone(h) 792 end 793 794 cache ? cached_dataset(:_qualify_ds, &pr) : pr.call 795 end
Modify the RETURNING clause, only supported on a few databases. If returning is used, instead of insert returning the autogenerated primary key or update/delete returning the number of modified rows, results are returned using fetch_rows
.
DB[:items].returning # RETURNING * DB[:items].returning(nil) # RETURNING NULL DB[:items].returning(:id, :name) # RETURNING id, name DB[:items].returning.insert(:a=>1) do |hash| # hash for each row inserted, with values for all columns end DB[:items].returning.update(:a=>1) do |hash| # hash for each row updated, with values for all columns end DB[:items].returning.delete(:a=>1) do |hash| # hash for each row deleted, with values for all columns end
# File lib/sequel/dataset/query.rb 815 def returning(*values) 816 if values.empty? 817 cached_dataset(:_returning_ds) do 818 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 819 clone(:returning=>EMPTY_ARRAY) 820 end 821 else 822 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 823 clone(:returning=>values.freeze) 824 end 825 end
Returns a copy of the dataset with the order reversed. If no order is given, the existing order is inverted.
DB[:items].reverse(:id) # SELECT * FROM items ORDER BY id DESC DB[:items].reverse{foo(bar)} # SELECT * FROM items ORDER BY foo(bar) DESC DB[:items].order(:id).reverse # SELECT * FROM items ORDER BY id DESC DB[:items].order(:id).reverse(Sequel.desc(:name)) # SELECT * FROM items ORDER BY name ASC
# File lib/sequel/dataset/query.rb 834 def reverse(*order, &block) 835 if order.empty? && !block 836 cached_dataset(:_reverse_ds){order(*invert_order(@opts[:order]))} 837 else 838 virtual_row_columns(order, block) 839 order(*invert_order(order.empty? ? @opts[:order] : order.freeze)) 840 end 841 end
Alias of reverse
# File lib/sequel/dataset/query.rb 844 def reverse_order(*order, &block) 845 reverse(*order, &block) 846 end
Returns a copy of the dataset with the columns selected changed to the given columns. This also takes a virtual row block, similar to where
.
DB[:items].select(:a) # SELECT a FROM items DB[:items].select(:a, :b) # SELECT a, b FROM items DB[:items].select{[a, sum(b)]} # SELECT a, sum(b) FROM items
# File lib/sequel/dataset/query.rb 855 def select(*columns, &block) 856 virtual_row_columns(columns, block) 857 clone(:select => columns.freeze) 858 end
Returns a copy of the dataset selecting the wildcard if no arguments are given. If arguments are given, treat them as tables and select all columns (using the wildcard) from each table.
DB[:items].select(:a).select_all # SELECT * FROM items DB[:items].select_all(:items) # SELECT items.* FROM items DB[:items].select_all(:items, :foo) # SELECT items.*, foo.* FROM items
# File lib/sequel/dataset/query.rb 867 def select_all(*tables) 868 if tables.empty? 869 cached_dataset(:_select_all_ds){clone(:select => nil)} 870 else 871 select(*tables.map{|t| i, a = split_alias(t); a || i}.map!{|t| SQL::ColumnAll.new(t)}.freeze) 872 end 873 end
Returns a copy of the dataset with the given columns added to the existing selected columns. If no columns are currently selected, it will select the columns given in addition to *.
DB[:items].select(:a).select(:b) # SELECT b FROM items DB[:items].select(:a).select_append(:b) # SELECT a, b FROM items DB[:items].select_append(:b) # SELECT *, b FROM items
# File lib/sequel/dataset/query.rb 882 def select_append(*columns, &block) 883 cur_sel = @opts[:select] 884 if !cur_sel || cur_sel.empty? 885 unless supports_select_all_and_column? 886 return select_all(*(Array(@opts[:from]) + Array(@opts[:join]))).select_append(*columns, &block) 887 end 888 cur_sel = [WILDCARD] 889 end 890 select(*(cur_sel + columns), &block) 891 end
Set both the select and group clauses with the given columns
. Column aliases may be supplied, and will be included in the select clause. This also takes a virtual row block similar to where
.
DB[:items].select_group(:a, :b) # SELECT a, b FROM items GROUP BY a, b DB[:items].select_group(Sequel[:c].as(:a)){f(c2)} # SELECT c AS a, f(c2) FROM items GROUP BY c, f(c2)
# File lib/sequel/dataset/query.rb 902 def select_group(*columns, &block) 903 virtual_row_columns(columns, block) 904 select(*columns).group(*columns.map{|c| unaliased_identifier(c)}) 905 end
Alias for select_append.
# File lib/sequel/dataset/query.rb 908 def select_more(*columns, &block) 909 select_append(*columns, &block) 910 end
Set the server for this dataset to use. Used to pick a specific database shard to run a query against, or to override the default (where SELECT uses :read_only database and all other queries use the :default database). This method is always available but is only useful when database sharding is being used.
DB[:items].all # Uses the :read_only or :default server DB[:items].delete # Uses the :default server DB[:items].server(:blah).delete # Uses the :blah server
# File lib/sequel/dataset/query.rb 921 def server(servr) 922 clone(:server=>servr) 923 end
If the database uses sharding and the current dataset has not had a server set, return a cloned dataset that uses the given server. Otherwise, return the receiver directly instead of returning a clone.
# File lib/sequel/dataset/query.rb 928 def server?(server) 929 if db.sharded? && !opts[:server] 930 server(server) 931 else 932 self 933 end 934 end
Specify that the check for limits/offsets when updating/deleting be skipped for the dataset.
# File lib/sequel/dataset/query.rb 937 def skip_limit_check 938 cached_dataset(:_skip_limit_check_ds) do 939 clone(:skip_limit_check=>true) 940 end 941 end
Skip locked rows when returning results from this dataset.
# File lib/sequel/dataset/query.rb 944 def skip_locked 945 cached_dataset(:_skip_locked_ds) do 946 raise(Error, 'This dataset does not support skipping locked rows') unless supports_skip_locked? 947 clone(:skip_locked=>true) 948 end 949 end
Returns a copy of the dataset with no filters (HAVING or WHERE clause) applied.
DB[:items].group(:a).having(a: 1).where(:b).unfiltered # SELECT * FROM items GROUP BY a
# File lib/sequel/dataset/query.rb 955 def unfiltered 956 cached_dataset(:_unfiltered_ds){clone(:where => nil, :having => nil)} 957 end
Returns a copy of the dataset with no grouping (GROUP or HAVING clause) applied.
DB[:items].group(:a).having(a: 1).where(:b).ungrouped # SELECT * FROM items WHERE b
# File lib/sequel/dataset/query.rb 963 def ungrouped 964 cached_dataset(:_ungrouped_ds){clone(:group => nil, :having => nil)} 965 end
Adds a UNION clause using a second dataset object. A UNION compound dataset returns all rows in either the current dataset or the given dataset. Options:
- :alias
-
Use the given value as the
from_self
alias - :all
-
Set to true to use UNION ALL instead of UNION, 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].union(DB[:other_items]) # SELECT * FROM (SELECT * FROM items UNION SELECT * FROM other_items) AS t1 DB[:items].union(DB[:other_items], all: true, from_self: false) # SELECT * FROM items UNION ALL SELECT * FROM other_items DB[:items].union(DB[:other_items], alias: :i) # SELECT * FROM (SELECT * FROM items UNION SELECT * FROM other_items) AS i
# File lib/sequel/dataset/query.rb 983 def union(dataset, opts=OPTS) 984 compound_clone(:union, dataset, opts) 985 end
Returns a copy of the dataset with no limit or offset.
DB[:items].limit(10, 20).unlimited # SELECT * FROM items
# File lib/sequel/dataset/query.rb 990 def unlimited 991 cached_dataset(:_unlimited_ds){clone(:limit=>nil, :offset=>nil)} 992 end
Returns a copy of the dataset with no order.
DB[:items].order(:a).unordered # SELECT * FROM items
# File lib/sequel/dataset/query.rb 997 def unordered 998 cached_dataset(:_unordered_ds){clone(:order=>nil)} 999 end
Returns a copy of the dataset with the given WHERE conditions imposed upon it.
Accepts the following argument types:
Hash
,Array
of pairs-
list of equality/inclusion expressions
Symbol
-
taken as a boolean column argument (e.g. WHERE active)
Sequel::SQL::BooleanExpression
,Sequel::LiteralString
-
an existing condition expression, probably created using the
Sequel
expression filter DSL.
where also accepts 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
If both a block and regular argument are provided, they get ANDed together.
Examples:
DB[:items].where(id: 3) # SELECT * FROM items WHERE (id = 3) DB[:items].where(Sequel.lit('price < ?', 100)) # SELECT * FROM items WHERE price < 100 DB[:items].where([[:id, [1,2,3]], [:id, 0..10]]) # SELECT * FROM items WHERE ((id IN (1, 2, 3)) AND ((id >= 0) AND (id <= 10))) DB[:items].where(Sequel.lit('price < 100')) # SELECT * FROM items WHERE price < 100 DB[:items].where(:active) # SELECT * FROM items WHERE :active DB[:items].where{price < 100} # SELECT * FROM items WHERE (price < 100)
Multiple where calls can be chained for scoping:
software = dataset.where(category: 'software').where{price < 100} # SELECT * FROM items WHERE ((category = 'software') AND (price < 100))
See the “Dataset Filtering” guide for more examples and details.
# File lib/sequel/dataset/query.rb 1043 def where(*cond, &block) 1044 add_filter(:where, cond, &block) 1045 end
Add a common table expression (CTE) with the given name and a dataset that defines the CTE. A common table expression acts as an inline view for the query. Options:
- :args
-
Specify the arguments/columns for the CTE, should be an array of symbols.
- :recursive
-
Specify that this is a recursive CTE
DB[:items].with(:items, DB[:syx].where(Sequel[:name].like('A%'))) # WITH items AS (SELECT * FROM syx WHERE (name LIKE 'A%' ESCAPE '\')) SELECT * FROM items
# File lib/sequel/dataset/query.rb 1055 def with(name, dataset, opts=OPTS) 1056 raise(Error, 'This dataset does not support common table expressions') unless supports_cte? 1057 if hoist_cte?(dataset) 1058 s, ds = hoist_cte(dataset) 1059 s.with(name, ds, opts) 1060 else 1061 clone(:with=>((@opts[:with]||EMPTY_ARRAY) + [Hash[opts].merge!(:name=>name, :dataset=>dataset)]).freeze) 1062 end 1063 end
Return a clone of the dataset extended with the given modules. Note that like Object#extend, when multiple modules are provided as arguments the cloned dataset is extended with the modules in reverse order. If a block is provided, a DatasetModule
is created using the block and the clone is extended with that module after any modules given as arguments.
# File lib/sequel/dataset/query.rb 1100 def with_extend(*mods, &block) 1101 c = _clone(:freeze=>false) 1102 c.extend(*mods) unless mods.empty? 1103 c.extend(DatasetModule.new(&block)) if block 1104 c.freeze 1105 end
Add a recursive common table expression (CTE) with the given name, a dataset that defines the nonrecursive part of the CTE, and a dataset that defines the recursive part of the CTE. Options:
- :args
-
Specify the arguments/columns for the CTE, should be an array of symbols.
- :union_all
-
Set to false to use UNION instead of UNION ALL combining the nonrecursive and recursive parts.
DB[:t].with_recursive(:t, DB[:i1].select(:id, :parent_id).where(parent_id: nil), DB[:i1].join(:t, id: :parent_id).select(Sequel[:i1][:id], Sequel[:i1][:parent_id]), :args=>[:id, :parent_id]) # WITH RECURSIVE t(id, parent_id) AS ( # SELECT id, parent_id FROM i1 WHERE (parent_id IS NULL) # UNION ALL # SELECT i1.id, i1.parent_id FROM i1 INNER JOIN t ON (t.id = i1.parent_id) # ) SELECT * FROM t
# File lib/sequel/dataset/query.rb 1081 def with_recursive(name, nonrecursive, recursive, opts=OPTS) 1082 raise(Error, 'This datatset does not support common table expressions') unless supports_cte? 1083 if hoist_cte?(nonrecursive) 1084 s, ds = hoist_cte(nonrecursive) 1085 s.with_recursive(name, ds, recursive, opts) 1086 elsif hoist_cte?(recursive) 1087 s, ds = hoist_cte(recursive) 1088 s.with_recursive(name, nonrecursive, ds, opts) 1089 else 1090 clone(:with=>((@opts[:with]||EMPTY_ARRAY) + [Hash[opts].merge!(:recursive=>true, :name=>name, :dataset=>nonrecursive.union(recursive, {:all=>opts[:union_all] != false, :from_self=>false}))]).freeze) 1091 end 1092 end
Returns a cloned dataset with the given row_proc.
ds = DB[:items] ds.all # => [{:id=>2}] ds.with_row_proc(:invert.to_proc).all # => [{2=>:id}]
# File lib/sequel/dataset/query.rb 1122 def with_row_proc(callable) 1123 clone(:row_proc=>callable) 1124 end
Returns a copy of the dataset with the static SQL
used. This is useful if you want to keep the same row_proc/graph, but change the SQL
used to custom SQL
.
DB[:items].with_sql('SELECT * FROM foo') # SELECT * FROM foo
You can use placeholders in your SQL
and provide arguments for those placeholders:
DB[:items].with_sql('SELECT ? FROM foo', 1) # SELECT 1 FROM foo
You can also provide a method name and arguments to call to get the SQL:
DB[:items].with_sql(:insert_sql, :b=>1) # INSERT INTO items (b) VALUES (1)
Note that datasets that specify custom SQL
using this method will generally ignore future dataset methods that modify the SQL
used, as specifying custom SQL
overrides Sequel's SQL
generator. You should probably limit yourself to the following dataset methods when using this method, or use the implicit_subquery extension:
-
each
-
all
-
single_record
(if only one record could be returned) -
single_value
(if only one record could be returned, and a single column is selected) -
map
-
delete (if a DELETE statement)
-
update (if an UPDATE statement, with no arguments)
-
insert (if an INSERT statement, with no arguments)
-
truncate (if a TRUNCATE statement, with no arguments)
# File lib/sequel/dataset/query.rb 1156 def with_sql(sql, *args) 1157 if sql.is_a?(Symbol) 1158 sql = public_send(sql, *args) 1159 else 1160 sql = SQL::PlaceholderLiteralString.new(sql, args) unless args.empty? 1161 end 1162 clone(:sql=>sql) 1163 end
Protected Instance Methods
Add the dataset to the list of compounds
# File lib/sequel/dataset/query.rb 1168 def compound_clone(type, dataset, opts) 1169 if dataset.is_a?(Dataset) && dataset.opts[:with] && !supports_cte_in_compounds? 1170 s, ds = hoist_cte(dataset) 1171 return s.compound_clone(type, ds, opts) 1172 end 1173 ds = compound_from_self.clone(:compounds=>(Array(@opts[:compounds]).map(&:dup) + [[type, dataset.compound_from_self, opts[:all]].freeze]).freeze) 1174 opts[:from_self] == false ? ds : ds.from_self(opts) 1175 end
Return true if the dataset has a non-nil value for any key in opts.
# File lib/sequel/dataset/query.rb 1178 def options_overlap(opts) 1179 !(@opts.map{|k,v| k unless v.nil?}.compact & opts).empty? 1180 end
Whether this dataset is a simple select from an underlying table, such as:
SELECT * FROM table SELECT table.* FROM table
# File lib/sequel/dataset/query.rb 1189 def simple_select_all? 1190 return false unless (f = @opts[:from]) && f.length == 1 1191 o = @opts.reject{|k,v| v.nil? || non_sql_option?(k)} 1192 from = f.first 1193 from = from.expression if from.is_a?(SQL::AliasedExpression) 1194 1195 if SIMPLE_SELECT_ALL_ALLOWED_FROM.any?{|x| from.is_a?(x)} 1196 case o.length 1197 when 1 1198 true 1199 when 2 1200 (s = o[:select]) && s.length == 1 && s.first.is_a?(SQL::ColumnAll) 1201 else 1202 false 1203 end 1204 else 1205 false 1206 end 1207 end
Private Instance Methods
Load the extensions into the receiver, without checking if the receiver is frozen.
# File lib/sequel/dataset/query.rb 1212 def _extension!(exts) 1213 Sequel.extension(*exts) 1214 exts.each do |ext| 1215 if pr = Sequel.synchronize{EXTENSIONS[ext]} 1216 pr.call(self) 1217 else 1218 raise(Error, "Extension #{ext} does not have specific support handling individual datasets (try: Sequel.extension #{ext.inspect})") 1219 end 1220 end 1221 self 1222 end
# File lib/sequel/dataset/query.rb 1224 def add_filter(clause, cond, invert=false, combine=:AND, &block) 1225 if cond == EMPTY_ARRAY && !block 1226 raise Error, "must provide an argument to a filtering method if not passing a block" 1227 end 1228 1229 cond = cond.first if cond.size == 1 1230 1231 empty = cond == OPTS || cond == EMPTY_ARRAY 1232 1233 if empty && !block 1234 self 1235 else 1236 if cond == nil 1237 cond = Sequel::NULL 1238 end 1239 if empty && block 1240 cond = nil 1241 end 1242 1243 cond = filter_expr(cond, &block) 1244 cond = SQL::BooleanExpression.invert(cond) if invert 1245 cond = SQL::BooleanExpression.new(combine, @opts[clause], cond) if @opts[clause] 1246 1247 if cond.nil? 1248 cond = Sequel::NULL 1249 end 1250 1251 clone(clause => cond) 1252 end 1253 end
The default :qualify option to use for join tables if one is not specified.
# File lib/sequel/dataset/query.rb 1256 def default_join_table_qualification 1257 :symbol 1258 end
Return self if the dataset already has a server, or a cloned dataset with the default server otherwise.
# File lib/sequel/dataset/query.rb 1331 def default_server 1332 server?(:default) 1333 end
SQL
expression object based on the expr type. See where
.
# File lib/sequel/dataset/query.rb 1261 def filter_expr(expr = nil, &block) 1262 expr = nil if expr == EMPTY_ARRAY 1263 1264 if block 1265 cond = filter_expr(Sequel.virtual_row(&block)) 1266 cond = SQL::BooleanExpression.new(:AND, filter_expr(expr), cond) if expr 1267 return cond 1268 end 1269 1270 case expr 1271 when Hash 1272 SQL::BooleanExpression.from_value_pairs(expr) 1273 when Array 1274 if Sequel.condition_specifier?(expr) 1275 SQL::BooleanExpression.from_value_pairs(expr) 1276 else 1277 raise Error, "Invalid filter expression: #{expr.inspect}" 1278 end 1279 when LiteralString 1280 LiteralString.new("(#{expr})") 1281 when Numeric, SQL::NumericExpression, SQL::StringExpression, Proc, String 1282 raise Error, "Invalid filter expression: #{expr.inspect}" 1283 when TrueClass, FalseClass 1284 if supports_where_true? 1285 SQL::BooleanExpression.new(:NOOP, expr) 1286 elsif expr 1287 SQL::Constants::SQLTRUE 1288 else 1289 SQL::Constants::SQLFALSE 1290 end 1291 when PlaceholderLiteralizer::Argument 1292 expr.transform{|v| filter_expr(v)} 1293 when SQL::PlaceholderLiteralString 1294 expr.with_parens 1295 else 1296 expr 1297 end 1298 end
Return two datasets, the first a clone of the receiver with the WITH clause from the given dataset added to it, and the second a clone of the given dataset with the WITH clause removed.
# File lib/sequel/dataset/query.rb 1303 def hoist_cte(ds) 1304 [clone(:with => ((opts[:with] || EMPTY_ARRAY) + ds.opts[:with]).freeze), ds.clone(:with => nil)] 1305 end
Whether CTEs need to be hoisted from the given ds into the current ds.
# File lib/sequel/dataset/query.rb 1308 def hoist_cte?(ds) 1309 ds.is_a?(Dataset) && ds.opts[:with] && !supports_cte_in_subqueries? 1310 end
Inverts the given order by breaking it into a list of column references and inverting them.
DB[:items].invert_order([Sequel.desc(:id)]]) #=> [Sequel.asc(:id)] DB[:items].invert_order([:category, Sequel.desc(:price)]) #=> [Sequel.desc(:category), Sequel.asc(:price)]
# File lib/sequel/dataset/query.rb 1317 def invert_order(order) 1318 return unless order 1319 order.map do |f| 1320 case f 1321 when SQL::OrderedExpression 1322 f.invert 1323 else 1324 SQL::OrderedExpression.new(f) 1325 end 1326 end 1327 end
Whether the given option key does not affect the generated SQL
.
# File lib/sequel/dataset/query.rb 1336 def non_sql_option?(key) 1337 NON_SQL_OPTIONS.include?(key) 1338 end
Treat the block
as a virtual_row block if not nil
and add the resulting columns to the columns
array (modifies columns
).
# File lib/sequel/dataset/query.rb 1342 def virtual_row_columns(columns, block) 1343 if block 1344 v = Sequel.virtual_row(&block) 1345 if v.is_a?(Array) 1346 columns.concat(v) 1347 else 1348 columns << v 1349 end 1350 end 1351 end
2 - Methods that execute code on the database
↑ topConstants
- ACTION_METHODS
Action methods defined by
Sequel
that execute code on the database.- COLUMNS_CLONE_OPTIONS
The clone options to use when retriveing columns for a dataset.
- COUNT_SELECT
- EMPTY_SELECT
Public Instance Methods
Inserts the given argument into the database. Returns self so it can be used safely when chaining:
DB[:items] << {id: 0, name: 'Zero'} << DB[:old_items].select(:id, name)
# File lib/sequel/dataset/actions.rb 28 def <<(arg) 29 insert(arg) 30 self 31 end
Returns the first record matching the conditions. Examples:
DB[:table][id: 1] # SELECT * FROM table WHERE (id = 1) LIMIT 1 # => {:id=>1}
# File lib/sequel/dataset/actions.rb 37 def [](*conditions) 38 raise(Error, 'You cannot call Dataset#[] with an integer or with no arguments') if (conditions.length == 1 and conditions.first.is_a?(Integer)) or conditions.length == 0 39 first(*conditions) 40 end
Returns an array with all records in the dataset. If a block is given, the array is iterated over after all items have been loaded.
DB[:table].all # SELECT * FROM table # => [{:id=>1, ...}, {:id=>2, ...}, ...] # Iterate over all rows in the table DB[:table].all{|row| p row}
# File lib/sequel/dataset/actions.rb 50 def all(&block) 51 _all(block){|a| each{|r| a << r}} 52 end
Returns a hash with one column used as key and another used as value. If rows have duplicate values for the key column, the latter row(s) will overwrite the value of the previous row(s). If the value_column is not given or nil, uses the entire hash as the value.
DB[:table].as_hash(:id, :name) # SELECT * FROM table # {1=>'Jim', 2=>'Bob', ...} DB[:table].as_hash(:id) # SELECT * FROM table # {1=>{:id=>1, :name=>'Jim'}, 2=>{:id=>2, :name=>'Bob'}, ...}
You can also provide an array of column names for either the key_column, the value column, or both:
DB[:table].as_hash([:id, :foo], [:name, :bar]) # SELECT * FROM table # {[1, 3]=>['Jim', 'bo'], [2, 4]=>['Bob', 'be'], ...} DB[:table].as_hash([:id, :name]) # SELECT * FROM table # {[1, 'Jim']=>{:id=>1, :name=>'Jim'}, [2, 'Bob']=>{:id=>2, :name=>'Bob'}, ...}
Options:
- :all
-
Use all instead of each to retrieve the objects
- :hash
-
The object into which the values will be placed. If this is not given, an empty hash is used. This can be used to use a hash with a default value or default proc.
# File lib/sequel/dataset/actions.rb 764 def as_hash(key_column, value_column = nil, opts = OPTS) 765 h = opts[:hash] || {} 766 meth = opts[:all] ? :all : :each 767 if value_column 768 return naked.as_hash(key_column, value_column, opts) if row_proc 769 if value_column.is_a?(Array) 770 if key_column.is_a?(Array) 771 public_send(meth){|r| h[r.values_at(*key_column)] = r.values_at(*value_column)} 772 else 773 public_send(meth){|r| h[r[key_column]] = r.values_at(*value_column)} 774 end 775 else 776 if key_column.is_a?(Array) 777 public_send(meth){|r| h[r.values_at(*key_column)] = r[value_column]} 778 else 779 public_send(meth){|r| h[r[key_column]] = r[value_column]} 780 end 781 end 782 elsif key_column.is_a?(Array) 783 public_send(meth){|r| h[key_column.map{|k| r[k]}] = r} 784 else 785 public_send(meth){|r| h[r[key_column]] = r} 786 end 787 h 788 end
Returns the average value for the given column/expression. Uses a virtual row block if no argument is given.
DB[:table].avg(:number) # SELECT avg(number) FROM table LIMIT 1 # => 3 DB[:table].avg{function(column)} # SELECT avg(function(column)) FROM table LIMIT 1 # => 1
# File lib/sequel/dataset/actions.rb 61 def avg(arg=Sequel.virtual_row(&Proc.new)) 62 _aggregate(:avg, arg) 63 end
Returns the columns in the result set in order as an array of symbols. If the columns are currently cached, returns the cached value. Otherwise, a SELECT query is performed to retrieve a single row in order to get the columns.
If you are looking for all columns for a single table and maybe some information about each column (e.g. database type), see Database#schema
.
DB[:table].columns # => [:id, :name]
# File lib/sequel/dataset/actions.rb 74 def columns 75 _columns || columns! 76 end
Ignore any cached column information and perform a query to retrieve a row in order to get the columns.
DB[:table].columns! # => [:id, :name]
# File lib/sequel/dataset/actions.rb 83 def columns! 84 ds = clone(COLUMNS_CLONE_OPTIONS) 85 ds.each{break} 86 87 if cols = ds.cache[:_columns] 88 self.columns = cols 89 else 90 [] 91 end 92 end
Returns the number of records in the dataset. If an argument is provided, it is used as the argument to count. If a block is provided, it is treated as a virtual row, and the result is used as the argument to count.
DB[:table].count # SELECT count(*) AS count FROM table LIMIT 1 # => 3 DB[:table].count(:column) # SELECT count(column) AS count FROM table LIMIT 1 # => 2 DB[:table].count{foo(column)} # SELECT count(foo(column)) AS count FROM table LIMIT 1 # => 1
# File lib/sequel/dataset/actions.rb 107 def count(arg=(no_arg=true), &block) 108 if no_arg && !block 109 cached_dataset(:_count_ds) do 110 aggregate_dataset.select(COUNT_SELECT).single_value_ds 111 end.single_value!.to_i 112 else 113 if block 114 if no_arg 115 arg = Sequel.virtual_row(&block) 116 else 117 raise Error, 'cannot provide both argument and block to Dataset#count' 118 end 119 end 120 121 _aggregate(:count, arg) 122 end 123 end
Deletes the records in the dataset, returning the number of records deleted.
DB[:table].delete # DELETE * FROM table # => 3
# File lib/sequel/dataset/actions.rb 129 def delete(&block) 130 sql = delete_sql 131 if uses_returning?(:delete) 132 returning_fetch_rows(sql, &block) 133 else 134 execute_dui(sql) 135 end 136 end
Iterates over the records in the dataset as they are yielded from the database adapter, and returns self.
DB[:table].each{|row| p row} # SELECT * FROM table
Note that this method is not safe to use on many adapters if you are running additional queries inside the provided block. If you are running queries inside the block, you should use all
instead of each
for the outer queries, or use a separate thread or shard inside each
.
# File lib/sequel/dataset/actions.rb 147 def each 148 if rp = row_proc 149 fetch_rows(select_sql){|r| yield rp.call(r)} 150 else 151 fetch_rows(select_sql){|r| yield r} 152 end 153 self 154 end
Returns true if no records exist in the dataset, false otherwise
DB[:table].empty? # SELECT 1 AS one FROM table LIMIT 1 # => false
# File lib/sequel/dataset/actions.rb 162 def empty? 163 cached_dataset(:_empty_ds) do 164 single_value_ds.unordered.select(EMPTY_SELECT) 165 end.single_value!.nil? 166 end
Returns the first matching record if no arguments are given. If a integer argument is given, it is interpreted as a limit, and then returns all matching records up to that limit. If any other type of argument(s) is passed, it is treated as a filter and the first matching record is returned. If a block is given, it is used to filter the dataset before returning anything.
If there are no records in the dataset, returns nil (or an empty array if an integer argument is given).
Examples:
DB[:table].first # SELECT * FROM table LIMIT 1 # => {:id=>7} DB[:table].first(2) # SELECT * FROM table LIMIT 2 # => [{:id=>6}, {:id=>4}] DB[:table].first(id: 2) # SELECT * FROM table WHERE (id = 2) LIMIT 1 # => {:id=>2} DB[:table].first(Sequel.lit("id = 3")) # SELECT * FROM table WHERE (id = 3) LIMIT 1 # => {:id=>3} DB[:table].first(Sequel.lit("id = ?", 4)) # SELECT * FROM table WHERE (id = 4) LIMIT 1 # => {:id=>4} DB[:table].first{id > 2} # SELECT * FROM table WHERE (id > 2) LIMIT 1 # => {:id=>5} DB[:table].first(Sequel.lit("id > ?", 4)){id < 6} # SELECT * FROM table WHERE ((id > 4) AND (id < 6)) LIMIT 1 # => {:id=>5} DB[:table].first(2){id < 2} # SELECT * FROM table WHERE (id < 2) LIMIT 2 # => [{:id=>1}]
# File lib/sequel/dataset/actions.rb 203 def first(*args, &block) 204 case args.length 205 when 0 206 unless block 207 return single_record 208 end 209 when 1 210 arg = args[0] 211 if arg.is_a?(Integer) 212 res = if block 213 if loader = cached_placeholder_literalizer(:_first_integer_cond_loader) do |pl| 214 where(pl.arg).limit(pl.arg) 215 end 216 217 loader.all(filter_expr(&block), arg) 218 else 219 where(&block).limit(arg).all 220 end 221 else 222 if loader = cached_placeholder_literalizer(:_first_integer_loader) do |pl| 223 limit(pl.arg) 224 end 225 226 loader.all(arg) 227 else 228 limit(arg).all 229 end 230 end 231 232 return res 233 end 234 args = arg 235 end 236 237 if loader = cached_placeholder_literalizer(:_first_cond_loader) do |pl| 238 _single_record_ds.where(pl.arg) 239 end 240 241 loader.first(filter_expr(args, &block)) 242 else 243 _single_record_ds.where(args, &block).single_record! 244 end 245 end
Calls first. If first returns nil (signaling that no row matches), raise a Sequel::NoMatchingRow
exception.
# File lib/sequel/dataset/actions.rb 249 def first!(*args, &block) 250 first(*args, &block) || raise(Sequel::NoMatchingRow.new(self)) 251 end
Return the column value for the first matching record in the dataset. Raises an error if both an argument and block is given.
DB[:table].get(:id) # SELECT id FROM table LIMIT 1 # => 3 ds.get{sum(id)} # SELECT sum(id) AS v FROM table LIMIT 1 # => 6
You can pass an array of arguments to return multiple arguments, but you must make sure each element in the array has an alias that Sequel
can determine:
DB[:table].get([:id, :name]) # SELECT id, name FROM table LIMIT 1 # => [3, 'foo'] DB[:table].get{[sum(id).as(sum), name]} # SELECT sum(id) AS sum, name FROM table LIMIT 1 # => [6, 'foo']
# File lib/sequel/dataset/actions.rb 271 def get(column=(no_arg=true; nil), &block) 272 ds = naked 273 if block 274 raise(Error, 'Must call Dataset#get with an argument or a block, not both') unless no_arg 275 ds = ds.select(&block) 276 column = ds.opts[:select] 277 column = nil if column.is_a?(Array) && column.length < 2 278 else 279 case column 280 when Array 281 ds = ds.select(*column) 282 when LiteralString, Symbol, SQL::Identifier, SQL::QualifiedIdentifier, SQL::AliasedExpression 283 if loader = cached_placeholder_literalizer(:_get_loader) do |pl| 284 ds.single_value_ds.select(pl.arg) 285 end 286 287 return loader.get(column) 288 end 289 290 ds = ds.select(column) 291 else 292 if loader = cached_placeholder_literalizer(:_get_alias_loader) do |pl| 293 ds.single_value_ds.select(Sequel.as(pl.arg, :v)) 294 end 295 296 return loader.get(column) 297 end 298 299 ds = ds.select(Sequel.as(column, :v)) 300 end 301 end 302 303 if column.is_a?(Array) 304 if r = ds.single_record 305 r.values_at(*hash_key_symbols(column)) 306 end 307 else 308 ds.single_value 309 end 310 end
Inserts multiple records into the associated table. This method can be used to efficiently insert a large number of records into a table in a single query if the database supports it. Inserts are automatically wrapped in a transaction.
This method is called with a columns array and an array of value arrays:
DB[:table].import([:x, :y], [[1, 2], [3, 4]]) # INSERT INTO table (x, y) VALUES (1, 2) # INSERT INTO table (x, y) VALUES (3, 4)
This method also accepts a dataset instead of an array of value arrays:
DB[:table].import([:x, :y], DB[:table2].select(:a, :b)) # INSERT INTO table (x, y) SELECT a, b FROM table2
Options:
- :commit_every
-
Open a new transaction for every given number of records. For example, if you provide a value of 50, will commit after every 50 records.
- :return
-
When this is set to :primary_key, returns an array of autoincremented primary key values for the rows inserted.
- :server
-
Set the server/shard to use for the transaction and insert queries.
- :slice
-
Same as :commit_every, :commit_every takes precedence.
# File lib/sequel/dataset/actions.rb 337 def import(columns, values, opts=OPTS) 338 return @db.transaction{insert(columns, values)} if values.is_a?(Dataset) 339 340 return if values.empty? 341 raise(Error, 'Using Sequel::Dataset#import with an empty column array is not allowed') if columns.empty? 342 ds = opts[:server] ? server(opts[:server]) : self 343 344 if slice_size = opts.fetch(:commit_every, opts.fetch(:slice, default_import_slice)) 345 offset = 0 346 rows = [] 347 while offset < values.length 348 rows << ds._import(columns, values[offset, slice_size], opts) 349 offset += slice_size 350 end 351 rows.flatten 352 else 353 ds._import(columns, values, opts) 354 end 355 end
Inserts values into the associated table. The returned value is generally the value of the autoincremented primary key for the inserted row, assuming that the a single row is inserted and the table has an autoincrementing primary key.
insert
handles a number of different argument formats:
- no arguments or single empty hash
-
Uses
DEFAULT
VALUES - single hash
-
Most common format, treats keys as columns and values as values
- single array
-
Treats entries as values, with no columns
- two arrays
-
Treats first array as columns, second array as values
- single
Dataset
-
Treats as an insert based on a selection from the dataset given, with no columns
- array and dataset
-
Treats as an insert based on a selection from the dataset given, with the columns given by the array.
Examples:
DB[:items].insert # INSERT INTO items DEFAULT VALUES DB[:items].insert({}) # INSERT INTO items DEFAULT VALUES DB[:items].insert([1,2,3]) # INSERT INTO items VALUES (1, 2, 3) DB[:items].insert([:a, :b], [1,2]) # INSERT INTO items (a, b) VALUES (1, 2) DB[:items].insert(a: 1, b: 2) # INSERT INTO items (a, b) VALUES (1, 2) DB[:items].insert(DB[:old_items]) # INSERT INTO items SELECT * FROM old_items DB[:items].insert([:a, :b], DB[:old_items]) # INSERT INTO items (a, b) SELECT * FROM old_items
# File lib/sequel/dataset/actions.rb 393 def insert(*values, &block) 394 sql = insert_sql(*values) 395 if uses_returning?(:insert) 396 returning_fetch_rows(sql, &block) 397 else 398 execute_insert(sql) 399 end 400 end
Reverses the order and then runs first
with the given arguments and block. Note that this will not necessarily give you the last record in the dataset, unless you have an unambiguous order. If there is not currently an order for this dataset, raises an Error
.
DB[:table].order(:id).last # SELECT * FROM table ORDER BY id DESC LIMIT 1 # => {:id=>10} DB[:table].order(Sequel.desc(:id)).last(2) # SELECT * FROM table ORDER BY id ASC LIMIT 2 # => [{:id=>1}, {:id=>2}]
# File lib/sequel/dataset/actions.rb 412 def last(*args, &block) 413 raise(Error, 'No order specified') unless @opts[:order] 414 reverse.first(*args, &block) 415 end
Maps column values for each record in the dataset (if an argument is given) or performs the stock mapping functionality of Enumerable
otherwise. Raises an Error
if both an argument and block are given.
DB[:table].map(:id) # SELECT * FROM table # => [1, 2, 3, ...] DB[:table].map{|r| r[:id] * 2} # SELECT * FROM table # => [2, 4, 6, ...]
You can also provide an array of column names:
DB[:table].map([:id, :name]) # SELECT * FROM table # => [[1, 'A'], [2, 'B'], [3, 'C'], ...]
# File lib/sequel/dataset/actions.rb 431 def map(column=nil, &block) 432 if column 433 raise(Error, 'Must call Dataset#map with either an argument or a block, not both') if block 434 return naked.map(column) if row_proc 435 if column.is_a?(Array) 436 super(){|r| r.values_at(*column)} 437 else 438 super(){|r| r[column]} 439 end 440 else 441 super(&block) 442 end 443 end
Returns the maximum value for the given column/expression. Uses a virtual row block if no argument is given.
DB[:table].max(:id) # SELECT max(id) FROM table LIMIT 1 # => 10 DB[:table].max{function(column)} # SELECT max(function(column)) FROM table LIMIT 1 # => 7
# File lib/sequel/dataset/actions.rb 452 def max(arg=Sequel.virtual_row(&Proc.new)) 453 _aggregate(:max, arg) 454 end
Returns the minimum value for the given column/expression. Uses a virtual row block if no argument is given.
DB[:table].min(:id) # SELECT min(id) FROM table LIMIT 1 # => 1 DB[:table].min{function(column)} # SELECT min(function(column)) FROM table LIMIT 1 # => 0
# File lib/sequel/dataset/actions.rb 463 def min(arg=Sequel.virtual_row(&Proc.new)) 464 _aggregate(:min, arg) 465 end
This is a front end for import that allows you to submit an array of hashes instead of arrays of columns and values:
DB[:table].multi_insert([{x: 1}, {x: 2}]) # INSERT INTO table (x) VALUES (1) # INSERT INTO table (x) VALUES (2)
Be aware that all hashes should have the same keys if you use this calling method, otherwise some columns could be missed or set to null instead of to default values.
This respects the same options as import
.
# File lib/sequel/dataset/actions.rb 479 def multi_insert(hashes, opts=OPTS) 480 return if hashes.empty? 481 columns = hashes.first.keys 482 import(columns, hashes.map{|h| columns.map{|c| h[c]}}, opts) 483 end
Yields each row in the dataset, but interally uses multiple queries as needed to process the entire result set without keeping all rows in the dataset in memory, even if the underlying driver buffers all query results in memory.
Because this uses multiple queries internally, in order to remain consistent, it also uses a transaction internally. Additionally, to work correctly, the dataset must have unambiguous order. Using an ambiguous order can result in an infinite loop, as well as subtler bugs such as yielding duplicate rows or rows being skipped.
Sequel
checks that the datasets using this method have an order, but it cannot ensure that the order is unambiguous.
Note that this method is not safe to use on many adapters if you are running additional queries inside the provided block. If you are running queries inside the block, use a separate thread or shard inside paged_each
.
Options:
- :rows_per_fetch
-
The number of rows to fetch per query. Defaults to 1000.
- :strategy
-
The strategy to use for paging of results. By default this is :offset, for using an approach with a limit and offset for every page. This can be set to :filter, which uses a limit and a filter that excludes rows from previous pages. In order for this strategy to work, you must be selecting the columns you are ordering by, and none of the columns can contain NULLs. Note that some
Sequel
adapters have optimized implementations that will use cursors or streaming regardless of the :strategy option used. - :filter_values
-
If the strategy: :filter option is used, this option should be a proc that accepts the last retreived row for the previous page and an array of ORDER BY expressions, and returns an array of values relating to those expressions for the last retrieved row. You will need to use this option if your ORDER BY expressions are not simple columns, if they contain qualified identifiers that would be ambiguous unqualified, if they contain any identifiers that are aliased in SELECT, and potentially other cases.
Examples:
DB[:table].order(:id).paged_each{|row| } # SELECT * FROM table ORDER BY id LIMIT 1000 # SELECT * FROM table ORDER BY id LIMIT 1000 OFFSET 1000 # ... DB[:table].order(:id).paged_each(:rows_per_fetch=>100){|row| } # SELECT * FROM table ORDER BY id LIMIT 100 # SELECT * FROM table ORDER BY id LIMIT 100 OFFSET 100 # ... DB[:table].order(:id).paged_each(strategy: :filter){|row| } # SELECT * FROM table ORDER BY id LIMIT 1000 # SELECT * FROM table WHERE id > 1001 ORDER BY id LIMIT 1000 # ... DB[:table].order(:id).paged_each(strategy: :filter, filter_values: lambda{|row, exprs| [row[:id]]}){|row| } # SELECT * FROM table ORDER BY id LIMIT 1000 # SELECT * FROM table WHERE id > 1001 ORDER BY id LIMIT 1000 # ...
# File lib/sequel/dataset/actions.rb 540 def paged_each(opts=OPTS) 541 unless @opts[:order] 542 raise Sequel::Error, "Dataset#paged_each requires the dataset be ordered" 543 end 544 unless block_given? 545 return enum_for(:paged_each, opts) 546 end 547 548 total_limit = @opts[:limit] 549 offset = @opts[:offset] 550 if server = @opts[:server] 551 opts = Hash[opts] 552 opts[:server] = server 553 end 554 555 rows_per_fetch = opts[:rows_per_fetch] || 1000 556 strategy = if offset || total_limit 557 :offset 558 else 559 opts[:strategy] || :offset 560 end 561 562 db.transaction(opts) do 563 case strategy 564 when :filter 565 filter_values = opts[:filter_values] || proc{|row, exprs| exprs.map{|e| row[hash_key_symbol(e)]}} 566 base_ds = ds = limit(rows_per_fetch) 567 while ds 568 last_row = nil 569 ds.each do |row| 570 last_row = row 571 yield row 572 end 573 ds = (base_ds.where(ignore_values_preceding(last_row, &filter_values)) if last_row) 574 end 575 else 576 offset ||= 0 577 num_rows_yielded = rows_per_fetch 578 total_rows = 0 579 580 while num_rows_yielded == rows_per_fetch && (total_limit.nil? || total_rows < total_limit) 581 if total_limit && total_rows + rows_per_fetch > total_limit 582 rows_per_fetch = total_limit - total_rows 583 end 584 585 num_rows_yielded = 0 586 limit(rows_per_fetch, offset).each do |row| 587 num_rows_yielded += 1 588 total_rows += 1 if total_limit 589 yield row 590 end 591 592 offset += rows_per_fetch 593 end 594 end 595 end 596 597 self 598 end
Returns a hash with key_column values as keys and value_column values as values. Similar to as_hash
, but only selects the columns given. Like as_hash
, it accepts an optional :hash parameter, into which entries will be merged.
DB[:table].select_hash(:id, :name) # SELECT id, name FROM table # => {1=>'a', 2=>'b', ...}
You can also provide an array of column names for either the key_column, the value column, or both:
DB[:table].select_hash([:id, :foo], [:name, :bar]) # SELECT * FROM table # {[1, 3]=>['a', 'c'], [2, 4]=>['b', 'd'], ...}
When using this method, you must be sure that each expression has an alias that Sequel
can determine.
# File lib/sequel/dataset/actions.rb 616 def select_hash(key_column, value_column, opts = OPTS) 617 _select_hash(:as_hash, key_column, value_column, opts) 618 end
Returns a hash with key_column values as keys and an array of value_column values. Similar to to_hash_groups
, but only selects the columns given. Like to_hash_groups
, it accepts an optional :hash parameter, into which entries will be merged.
DB[:table].select_hash_groups(:name, :id) # SELECT id, name FROM table # => {'a'=>[1, 4, ...], 'b'=>[2, ...], ...}
You can also provide an array of column names for either the key_column, the value column, or both:
DB[:table].select_hash_groups([:first, :middle], [:last, :id]) # SELECT * FROM table # {['a', 'b']=>[['c', 1], ['d', 2], ...], ...}
When using this method, you must be sure that each expression has an alias that Sequel
can determine.
# File lib/sequel/dataset/actions.rb 635 def select_hash_groups(key_column, value_column, opts = OPTS) 636 _select_hash(:to_hash_groups, key_column, value_column, opts) 637 end
Selects the column given (either as an argument or as a block), and returns an array of all values of that column in the dataset. If you give a block argument that returns an array with multiple entries, the contents of the resulting array are undefined. Raises an Error
if called with both an argument and a block.
DB[:table].select_map(:id) # SELECT id FROM table # => [3, 5, 8, 1, ...] DB[:table].select_map{id * 2} # SELECT (id * 2) FROM table # => [6, 10, 16, 2, ...]
You can also provide an array of column names:
DB[:table].select_map([:id, :name]) # SELECT id, name FROM table # => [[1, 'A'], [2, 'B'], [3, 'C'], ...]
If you provide an array of expressions, you must be sure that each entry in the array has an alias that Sequel
can determine.
# File lib/sequel/dataset/actions.rb 658 def select_map(column=nil, &block) 659 _select_map(column, false, &block) 660 end
The same as select_map
, but in addition orders the array by the column.
DB[:table].select_order_map(:id) # SELECT id FROM table ORDER BY id # => [1, 2, 3, 4, ...] DB[:table].select_order_map{id * 2} # SELECT (id * 2) FROM table ORDER BY (id * 2) # => [2, 4, 6, 8, ...]
You can also provide an array of column names:
DB[:table].select_order_map([:id, :name]) # SELECT id, name FROM table ORDER BY id, name # => [[1, 'A'], [2, 'B'], [3, 'C'], ...]
If you provide an array of expressions, you must be sure that each entry in the array has an alias that Sequel
can determine.
# File lib/sequel/dataset/actions.rb 677 def select_order_map(column=nil, &block) 678 _select_map(column, true, &block) 679 end
Limits the dataset to one record, and returns the first record in the dataset, or nil if the dataset has no records. Users should probably use first
instead of this method. Example:
DB[:test].single_record # SELECT * FROM test LIMIT 1 # => {:column_name=>'value'}
# File lib/sequel/dataset/actions.rb 687 def single_record 688 _single_record_ds.single_record! 689 end
Returns the first record in dataset, without limiting the dataset. Returns nil if the dataset has no records. Users should probably use first
instead of this method. This should only be used if you know the dataset is already limited to a single record. This method may be desirable to use for performance reasons, as it does not clone the receiver. Example:
DB[:test].single_record! # SELECT * FROM test # => {:column_name=>'value'}
# File lib/sequel/dataset/actions.rb 699 def single_record! 700 with_sql_first(select_sql) 701 end
Returns the first value of the first record in the dataset. Returns nil if dataset is empty. Users should generally use get
instead of this method. Example:
DB[:test].single_value # SELECT * FROM test LIMIT 1 # => 'value'
# File lib/sequel/dataset/actions.rb 709 def single_value 710 single_value_ds.each do |r| 711 r.each{|_, v| return v} 712 end 713 nil 714 end
Returns the first value of the first record in the dataset, without limiting the dataset. Returns nil if the dataset is empty. Users should generally use get
instead of this method. Should not be used on graphed datasets or datasets that have row_procs that don't return hashes. This method may be desirable to use for performance reasons, as it does not clone the receiver.
DB[:test].single_value! # SELECT * FROM test # => 'value'
# File lib/sequel/dataset/actions.rb 724 def single_value! 725 with_sql_single_value(select_sql) 726 end
Returns the sum for the given column/expression. Uses a virtual row block if no column is given.
DB[:table].sum(:id) # SELECT sum(id) FROM table LIMIT 1 # => 55 DB[:table].sum{function(column)} # SELECT sum(function(column)) FROM table LIMIT 1 # => 10
# File lib/sequel/dataset/actions.rb 735 def sum(arg=Sequel.virtual_row(&Proc.new)) 736 _aggregate(:sum, arg) 737 end
Alias of as_hash
for backwards compatibility.
# File lib/sequel/dataset/actions.rb 791 def to_hash(*a) 792 as_hash(*a) 793 end
Returns a hash with one column used as key and the values being an array of column values. If the value_column is not given or nil, uses the entire hash as the value.
DB[:table].to_hash_groups(:name, :id) # SELECT * FROM table # {'Jim'=>[1, 4, 16, ...], 'Bob'=>[2], ...} DB[:table].to_hash_groups(:name) # SELECT * FROM table # {'Jim'=>[{:id=>1, :name=>'Jim'}, {:id=>4, :name=>'Jim'}, ...], 'Bob'=>[{:id=>2, :name=>'Bob'}], ...}
You can also provide an array of column names for either the key_column, the value column, or both:
DB[:table].to_hash_groups([:first, :middle], [:last, :id]) # SELECT * FROM table # {['Jim', 'Bob']=>[['Smith', 1], ['Jackson', 4], ...], ...} DB[:table].to_hash_groups([:first, :middle]) # SELECT * FROM table # {['Jim', 'Bob']=>[{:id=>1, :first=>'Jim', :middle=>'Bob', :last=>'Smith'}, ...], ...}
Options:
- :all
-
Use all instead of each to retrieve the objects
- :hash
-
The object into which the values will be placed. If this is not given, an empty hash is used. This can be used to use a hash with a default value or default proc.
# File lib/sequel/dataset/actions.rb 819 def to_hash_groups(key_column, value_column = nil, opts = OPTS) 820 h = opts[:hash] || {} 821 meth = opts[:all] ? :all : :each 822 if value_column 823 return naked.to_hash_groups(key_column, value_column, opts) if row_proc 824 if value_column.is_a?(Array) 825 if key_column.is_a?(Array) 826 public_send(meth){|r| (h[r.values_at(*key_column)] ||= []) << r.values_at(*value_column)} 827 else 828 public_send(meth){|r| (h[r[key_column]] ||= []) << r.values_at(*value_column)} 829 end 830 else 831 if key_column.is_a?(Array) 832 public_send(meth){|r| (h[r.values_at(*key_column)] ||= []) << r[value_column]} 833 else 834 public_send(meth){|r| (h[r[key_column]] ||= []) << r[value_column]} 835 end 836 end 837 elsif key_column.is_a?(Array) 838 public_send(meth){|r| (h[key_column.map{|k| r[k]}] ||= []) << r} 839 else 840 public_send(meth){|r| (h[r[key_column]] ||= []) << r} 841 end 842 h 843 end
Truncates the dataset. Returns nil.
DB[:table].truncate # TRUNCATE table # => nil
# File lib/sequel/dataset/actions.rb 849 def truncate 850 execute_ddl(truncate_sql) 851 end
Updates values for the dataset. The returned value is the number of rows updated. values
should be a hash where the keys are columns to set and values are the values to which to set the columns.
DB[:table].update(x: nil) # UPDATE table SET x = NULL # => 10 DB[:table].update(x: Sequel[:x]+1, y: 0) # UPDATE table SET x = (x + 1), y = 0 # => 10
# File lib/sequel/dataset/actions.rb 862 def update(values=OPTS, &block) 863 sql = update_sql(values) 864 if uses_returning?(:update) 865 returning_fetch_rows(sql, &block) 866 else 867 execute_dui(sql) 868 end 869 end
Return an array of all rows matching the given filter condition, also yielding each row to the given block. Basically the same as where(cond).all(&block), except it can be optimized to not create an intermediate dataset.
DB[:table].where_all(id: [1,2,3]) # SELECT * FROM table WHERE (id IN (1, 2, 3))
# File lib/sequel/dataset/actions.rb 877 def where_all(cond, &block) 878 if loader = _where_loader 879 loader.all(filter_expr(cond), &block) 880 else 881 where(cond).all(&block) 882 end 883 end
Iterate over all rows matching the given filter condition, yielding each row to the given block. Basically the same as where(cond).each(&block), except it can be optimized to not create an intermediate dataset.
DB[:table].where_each(id: [1,2,3]){|row| p row} # SELECT * FROM table WHERE (id IN (1, 2, 3))
# File lib/sequel/dataset/actions.rb 891 def where_each(cond, &block) 892 if loader = _where_loader 893 loader.each(filter_expr(cond), &block) 894 else 895 where(cond).each(&block) 896 end 897 end
Filter the datasets using the given filter condition, then return a single value. This assumes that the dataset has already been setup to limit the selection to a single column. Basically the same as where(cond).single_value, except it can be optimized to not create an intermediate dataset.
DB[:table].select(:name).where_single_value(id: 1) # SELECT name FROM table WHERE (id = 1) LIMIT 1
# File lib/sequel/dataset/actions.rb 906 def where_single_value(cond) 907 if loader = cached_placeholder_literalizer(:_where_single_value_loader) do |pl| 908 single_value_ds.where(pl.arg) 909 end 910 911 loader.get(filter_expr(cond)) 912 else 913 where(cond).single_value 914 end 915 end
Run the given SQL
and return an array of all rows. If a block is given, each row is yielded to the block after all rows are loaded. See with_sql_each.
# File lib/sequel/dataset/actions.rb 919 def with_sql_all(sql, &block) 920 _all(block){|a| with_sql_each(sql){|r| a << r}} 921 end
Execute the given SQL
and return the number of rows deleted. This exists solely as an optimization, replacing with_sql
(sql).delete. It's significantly faster as it does not require cloning the current dataset.
# File lib/sequel/dataset/actions.rb 926 def with_sql_delete(sql) 927 execute_dui(sql) 928 end
Run the given SQL
and yield each returned row to the block.
# File lib/sequel/dataset/actions.rb 932 def with_sql_each(sql) 933 if rp = row_proc 934 _with_sql_dataset.fetch_rows(sql){|r| yield rp.call(r)} 935 else 936 _with_sql_dataset.fetch_rows(sql){|r| yield r} 937 end 938 self 939 end
Run the given SQL
and return the first row, or nil if no rows were returned. See with_sql_each.
# File lib/sequel/dataset/actions.rb 943 def with_sql_first(sql) 944 with_sql_each(sql){|r| return r} 945 nil 946 end
Execute the given SQL
and (on most databases) return the primary key of the inserted row.
# File lib/sequel/dataset/actions.rb 959 def with_sql_insert(sql) 960 execute_insert(sql) 961 end
Run the given SQL
and return the first value in the first row, or nil if no rows were returned. For this to make sense, the SQL
given should select only a single value. See with_sql_each.
# File lib/sequel/dataset/actions.rb 951 def with_sql_single_value(sql) 952 if r = with_sql_first(sql) 953 r.each{|_, v| return v} 954 end 955 end
Protected Instance Methods
Internals of import
. If primary key values are requested, use separate insert commands for each row. Otherwise, call multi_insert_sql
and execute each statement it gives separately.
# File lib/sequel/dataset/actions.rb 968 def _import(columns, values, opts) 969 trans_opts = Hash[opts].merge!(:server=>@opts[:server]) 970 if opts[:return] == :primary_key 971 @db.transaction(trans_opts){values.map{|v| insert(columns, v)}} 972 else 973 stmts = multi_insert_sql(columns, values) 974 @db.transaction(trans_opts){stmts.each{|st| execute_dui(st)}} 975 end 976 end
Return an array of arrays of values given by the symbols in ret_cols.
# File lib/sequel/dataset/actions.rb 979 def _select_map_multiple(ret_cols) 980 map{|r| r.values_at(*ret_cols)} 981 end
Returns an array of the first value in each row.
# File lib/sequel/dataset/actions.rb 984 def _select_map_single 985 k = nil 986 map{|r| r[k||=r.keys.first]} 987 end
A dataset for returning single values from the current dataset.
# File lib/sequel/dataset/actions.rb 990 def single_value_ds 991 clone(:limit=>1).ungraphed.naked 992 end
Private Instance Methods
Cached placeholder literalizer for methods that return values using aggregate functions.
# File lib/sequel/dataset/actions.rb 1006 def _aggregate(function, arg) 1007 if loader = cached_placeholder_literalizer(:"_#{function}_loader") do |pl| 1008 aggregate_dataset.limit(1).select(SQL::Function.new(function, pl.arg).as(function)) 1009 end 1010 loader.get(arg) 1011 else 1012 aggregate_dataset.get(SQL::Function.new(function, arg).as(function)) 1013 end 1014 end
Internals of all and with_sql_all
# File lib/sequel/dataset/actions.rb 997 def _all(block) 998 a = [] 999 yield a 1000 post_load(a) 1001 a.each(&block) if block 1002 a 1003 end
Return a plain symbol given a potentially qualified or aliased symbol, specifying the symbol that is likely to be used as the hash key for the column when records are returned. Return nil if no hash key can be determined
# File lib/sequel/dataset/actions.rb 1105 def _hash_key_symbol(s, recursing=false) 1106 case s 1107 when Symbol 1108 _, c, a = split_symbol(s) 1109 (a || c).to_sym 1110 when SQL::Identifier, SQL::Wrapper 1111 _hash_key_symbol(s.value, true) 1112 when SQL::QualifiedIdentifier 1113 _hash_key_symbol(s.column, true) 1114 when SQL::AliasedExpression 1115 _hash_key_symbol(s.alias, true) 1116 when String 1117 s.to_sym if recursing 1118 end 1119 end
Internals of select_hash
and select_hash_groups
# File lib/sequel/dataset/actions.rb 1017 def _select_hash(meth, key_column, value_column, opts=OPTS) 1018 select(*(key_column.is_a?(Array) ? key_column : [key_column]) + (value_column.is_a?(Array) ? value_column : [value_column])). 1019 public_send(meth, hash_key_symbols(key_column), hash_key_symbols(value_column), opts) 1020 end
Internals of select_map
and select_order_map
# File lib/sequel/dataset/actions.rb 1023 def _select_map(column, order, &block) 1024 ds = ungraphed.naked 1025 columns = Array(column) 1026 virtual_row_columns(columns, block) 1027 select_cols = order ? columns.map{|c| c.is_a?(SQL::OrderedExpression) ? c.expression : c} : columns 1028 ds = ds.order(*columns.map{|c| unaliased_identifier(c)}) if order 1029 if column.is_a?(Array) || (columns.length > 1) 1030 ds.select(*select_cols)._select_map_multiple(hash_key_symbols(select_cols)) 1031 else 1032 ds.select(auto_alias_expression(select_cols.first))._select_map_single 1033 end 1034 end
A cached dataset for a single record for this dataset.
# File lib/sequel/dataset/actions.rb 1037 def _single_record_ds 1038 cached_dataset(:_single_record_ds){clone(:limit=>1)} 1039 end
Loader used for where_all
and where_each.
# File lib/sequel/dataset/actions.rb 1042 def _where_loader 1043 cached_placeholder_literalizer(:_where_loader) do |pl| 1044 where(pl.arg) 1045 end 1046 end
Cached dataset to use for with_sql_#{all,each,first,single_value}. This is used so that the columns returned by the given SQL
do not affect the receiver of the with_sql_* method.
# File lib/sequel/dataset/actions.rb 1223 def _with_sql_dataset 1224 if @opts[:_with_sql_ds] 1225 self 1226 else 1227 cached_dataset(:_with_sql_ds) do 1228 clone(:_with_sql_ds=>true) 1229 end 1230 end 1231 end
Automatically alias the given expression if it does not have an identifiable alias.
# File lib/sequel/dataset/actions.rb 1049 def auto_alias_expression(v) 1050 case v 1051 when LiteralString, Symbol, SQL::Identifier, SQL::QualifiedIdentifier, SQL::AliasedExpression 1052 v 1053 else 1054 SQL::AliasedExpression.new(v, :v) 1055 end 1056 end
The default number of rows that can be inserted in a single INSERT statement via import. The default is for no limit.
# File lib/sequel/dataset/actions.rb 1060 def default_import_slice 1061 nil 1062 end
Set the server to use to :default unless it is already set in the passed opts
# File lib/sequel/dataset/actions.rb 1065 def default_server_opts(opts) 1066 if @db.sharded? 1067 opts = Hash[opts] 1068 opts[:server] = @opts[:server] || :default 1069 end 1070 opts 1071 end
Execute the given select SQL
on the database using execute. Use the :read_only server unless a specific server is set.
# File lib/sequel/dataset/actions.rb 1075 def execute(sql, opts=OPTS, &block) 1076 db = @db 1077 if db.sharded? 1078 opts = Hash[opts] 1079 opts[:server] = @opts[:server] || (@opts[:lock] ? :default : :read_only) 1080 opts 1081 end 1082 db.execute(sql, opts, &block) 1083 end
Execute the given SQL
on the database using execute_ddl.
# File lib/sequel/dataset/actions.rb 1086 def execute_ddl(sql, opts=OPTS, &block) 1087 @db.execute_ddl(sql, default_server_opts(opts), &block) 1088 nil 1089 end
Execute the given SQL
on the database using execute_dui.
# File lib/sequel/dataset/actions.rb 1092 def execute_dui(sql, opts=OPTS, &block) 1093 @db.execute_dui(sql, default_server_opts(opts), &block) 1094 end
Execute the given SQL
on the database using execute_insert.
# File lib/sequel/dataset/actions.rb 1097 def execute_insert(sql, opts=OPTS, &block) 1098 @db.execute_insert(sql, default_server_opts(opts), &block) 1099 end
Return a plain symbol given a potentially qualified or aliased symbol, specifying the symbol that is likely to be used as the hash key for the column when records are returned. Raise Error
if the hash key symbol cannot be returned.
# File lib/sequel/dataset/actions.rb 1125 def hash_key_symbol(s) 1126 if v = _hash_key_symbol(s) 1127 v 1128 else 1129 raise(Error, "#{s.inspect} is not supported, should be a Symbol, SQL::Identifier, SQL::QualifiedIdentifier, or SQL::AliasedExpression") 1130 end 1131 end
If s is an array, return an array with the given hash key symbols. Otherwise, return a hash key symbol for the given expression If a hash key symbol cannot be determined, raise an error.
# File lib/sequel/dataset/actions.rb 1136 def hash_key_symbols(s) 1137 s.is_a?(Array) ? s.map{|c| hash_key_symbol(c)} : hash_key_symbol(s) 1138 end
Returns an expression that will ignore values preceding the given row, using the receiver's current order. This yields the row and the array of order expressions to the block, which should return an array of values to use.
# File lib/sequel/dataset/actions.rb 1143 def ignore_values_preceding(row) 1144 @opts[:order].map{|v| v.is_a?(SQL::OrderedExpression) ? v.expression : v} 1145 1146 order_exprs = @opts[:order].map do |v| 1147 if v.is_a?(SQL::OrderedExpression) 1148 descending = v.descending 1149 v = v.expression 1150 else 1151 descending = false 1152 end 1153 [v, descending] 1154 end 1155 1156 row_values = yield(row, order_exprs.map(&:first)) 1157 1158 last_expr = [] 1159 cond = order_exprs.zip(row_values).map do |(v, descending), value| 1160 expr = last_expr + [SQL::BooleanExpression.new(descending ? :< : :>, v, value)] 1161 last_expr += [SQL::BooleanExpression.new(:'=', v, value)] 1162 Sequel.&(*expr) 1163 end 1164 Sequel.|(*cond) 1165 end
Downcase identifiers by default when outputing them from the database.
# File lib/sequel/dataset/actions.rb 1168 def output_identifier(v) 1169 v = 'untitled' if v == '' 1170 v.to_s.downcase.to_sym 1171 end
This is run inside .all, after all of the records have been loaded via .each, but before any block passed to all is called. It is called with a single argument, an array of all returned records. Does nothing by default, added to make the model eager loading code simpler.
# File lib/sequel/dataset/actions.rb 1177 def post_load(all_records) 1178 end
Called by insert/update/delete when returning is used. Yields each row as a plain hash to the block if one is given, or returns an array of plain hashes for all rows if a block is not given
# File lib/sequel/dataset/actions.rb 1183 def returning_fetch_rows(sql, &block) 1184 if block 1185 default_server.fetch_rows(sql, &block) 1186 nil 1187 else 1188 rows = [] 1189 default_server.fetch_rows(sql){|r| rows << r} 1190 rows 1191 end 1192 end
Return the unaliased part of the identifier. Handles both implicit aliases in symbols, as well as SQL::AliasedExpression
objects. Other objects are returned as is.
# File lib/sequel/dataset/actions.rb 1197 def unaliased_identifier(c) 1198 case c 1199 when Symbol 1200 table, column, aliaz = split_symbol(c) 1201 if aliaz 1202 table ? SQL::QualifiedIdentifier.new(table, column) : Sequel.identifier(column) 1203 else 1204 c 1205 end 1206 when SQL::AliasedExpression 1207 c.expression 1208 when SQL::OrderedExpression 1209 case expr = c.expression 1210 when Symbol, SQL::AliasedExpression 1211 SQL::OrderedExpression.new(unaliased_identifier(expr), c.descending, :nulls=>c.nulls) 1212 else 1213 c 1214 end 1215 else 1216 c 1217 end 1218 end
3 - User Methods relating to SQL Creation
↑ topPublic Instance Methods
Returns an EXISTS
clause for the dataset as an SQL::PlaceholderLiteralString
.
DB.select(1).where(DB[:items].exists) # SELECT 1 WHERE (EXISTS (SELECT * FROM items))
# File lib/sequel/dataset/sql.rb 14 def exists 15 SQL::PlaceholderLiteralString.new(EXISTS, [self], true) 16 end
Returns an INSERT SQL
query string. See insert
.
DB[:items].insert_sql(a: 1) # => "INSERT INTO items (a) VALUES (1)"
# File lib/sequel/dataset/sql.rb 22 def insert_sql(*values) 23 return static_sql(@opts[:sql]) if @opts[:sql] 24 25 check_modification_allowed! 26 27 columns = [] 28 29 case values.size 30 when 0 31 return insert_sql(OPTS) 32 when 1 33 case vals = values[0] 34 when Hash 35 values = [] 36 vals.each do |k,v| 37 columns << k 38 values << v 39 end 40 when Dataset, Array, LiteralString 41 values = vals 42 end 43 when 2 44 if (v0 = values[0]).is_a?(Array) && ((v1 = values[1]).is_a?(Array) || v1.is_a?(Dataset) || v1.is_a?(LiteralString)) 45 columns, values = v0, v1 46 raise(Error, "Different number of values and columns given to insert_sql") if values.is_a?(Array) and columns.length != values.length 47 end 48 end 49 50 if values.is_a?(Array) && values.empty? && !insert_supports_empty_values? 51 columns, values = insert_empty_columns_values 52 elsif values.is_a?(Dataset) && hoist_cte?(values) && supports_cte?(:insert) 53 ds, values = hoist_cte(values) 54 return ds.clone(:columns=>columns, :values=>values).send(:_insert_sql) 55 end 56 clone(:columns=>columns, :values=>values).send(:_insert_sql) 57 end
Append a literal representation of a value to the given SQL
string.
If an unsupported object is given, an Error
is raised.
# File lib/sequel/dataset/sql.rb 62 def literal_append(sql, v) 63 case v 64 when Symbol 65 if skip_symbol_cache? 66 literal_symbol_append(sql, v) 67 else 68 unless l = db.literal_symbol(v) 69 l = String.new 70 literal_symbol_append(l, v) 71 db.literal_symbol_set(v, l) 72 end 73 sql << l 74 end 75 when String 76 case v 77 when LiteralString 78 sql << v 79 when SQL::Blob 80 literal_blob_append(sql, v) 81 else 82 literal_string_append(sql, v) 83 end 84 when Integer 85 sql << literal_integer(v) 86 when Hash 87 literal_hash_append(sql, v) 88 when SQL::Expression 89 literal_expression_append(sql, v) 90 when Float 91 sql << literal_float(v) 92 when BigDecimal 93 sql << literal_big_decimal(v) 94 when NilClass 95 sql << literal_nil 96 when TrueClass 97 sql << literal_true 98 when FalseClass 99 sql << literal_false 100 when Array 101 literal_array_append(sql, v) 102 when Time 103 v.is_a?(SQLTime) ? literal_sqltime_append(sql, v) : literal_time_append(sql, v) 104 when DateTime 105 literal_datetime_append(sql, v) 106 when Date 107 sql << literal_date(v) 108 when Dataset 109 literal_dataset_append(sql, v) 110 else 111 literal_other_append(sql, v) 112 end 113 end
Returns an array of insert statements for inserting multiple records. This method is used by multi_insert
to format insert statements and expects a keys array and and an array of value arrays.
# File lib/sequel/dataset/sql.rb 118 def multi_insert_sql(columns, values) 119 case multi_insert_sql_strategy 120 when :values 121 sql = LiteralString.new('VALUES ') 122 expression_list_append(sql, values.map{|r| Array(r)}) 123 [insert_sql(columns, sql)] 124 when :union 125 c = false 126 sql = LiteralString.new 127 u = ' UNION ALL SELECT ' 128 f = empty_from_sql 129 values.each do |v| 130 if c 131 sql << u 132 else 133 sql << 'SELECT ' 134 c = true 135 end 136 expression_list_append(sql, v) 137 sql << f if f 138 end 139 [insert_sql(columns, sql)] 140 else 141 values.map{|r| insert_sql(columns, r)} 142 end 143 end
Same as select_sql
, not aliased directly to make subclassing simpler.
# File lib/sequel/dataset/sql.rb 146 def sql 147 select_sql 148 end
Returns a TRUNCATE SQL
query string. See truncate
DB[:items].truncate_sql # => 'TRUNCATE items'
# File lib/sequel/dataset/sql.rb 153 def truncate_sql 154 if opts[:sql] 155 static_sql(opts[:sql]) 156 else 157 check_truncation_allowed! 158 check_not_limited!(:truncate) 159 raise(InvalidOperation, "Can't truncate filtered datasets") if opts[:where] || opts[:having] 160 t = String.new 161 source_list_append(t, opts[:from]) 162 _truncate_sql(t) 163 end 164 end
Formats an UPDATE statement using the given values. See update
.
DB[:items].update_sql(price: 100, category: 'software') # => "UPDATE items SET price = 100, category = 'software'
Raises an Error
if the dataset is grouped or includes more than one table.
# File lib/sequel/dataset/sql.rb 173 def update_sql(values = OPTS) 174 return static_sql(opts[:sql]) if opts[:sql] 175 check_modification_allowed! 176 check_not_limited!(:update) 177 178 case values 179 when LiteralString 180 # nothing 181 when String 182 raise Error, "plain string passed to Dataset#update is not supported, use Sequel.lit to use a literal string" 183 end 184 185 clone(:values=>values).send(:_update_sql) 186 end
4 - Methods that describe what the dataset supports
↑ topPublic Instance Methods
Whether this dataset will provide accurate number of rows matched for delete and update statements, true by default. Accurate in this case is the number of rows matched by the dataset's filter.
# File lib/sequel/dataset/features.rb 19 def provides_accurate_rows_matched? 20 true 21 end
Whether this dataset quotes identifiers.
# File lib/sequel/dataset/features.rb 12 def quote_identifiers? 13 @opts.fetch(:quote_identifiers, true) 14 end
Whether you must use a column alias list for recursive CTEs, false by default.
# File lib/sequel/dataset/features.rb 24 def recursive_cte_requires_column_aliases? 25 false 26 end
Whether type specifiers are required for prepared statement/bound variable argument placeholders (i.e. :bv__integer), false by default.
# File lib/sequel/dataset/features.rb 36 def requires_placeholder_type_specifiers? 37 false 38 end
Whether the dataset requires SQL
standard datetimes. False by default, as most allow strings with ISO 8601 format.
# File lib/sequel/dataset/features.rb 30 def requires_sql_standard_datetimes? 31 false 32 end
Whether the dataset supports common table expressions, false by default. If given, type
can be :select, :insert, :update, or :delete, in which case it determines whether WITH is supported for the respective statement type.
# File lib/sequel/dataset/features.rb 43 def supports_cte?(type=:select) 44 false 45 end
Whether the dataset supports common table expressions in subqueries, false by default. If false, applies the WITH clause to the main query, which can cause issues if multiple WITH clauses use the same name.
# File lib/sequel/dataset/features.rb 50 def supports_cte_in_subqueries? 51 false 52 end
Whether the database supports derived column lists (e.g. “table_expr AS table_alias(column_alias1, column_alias2, …)”), true by default.
# File lib/sequel/dataset/features.rb 57 def supports_derived_column_lists? 58 true 59 end
Whether the dataset supports or can emulate the DISTINCT ON clause, false by default.
# File lib/sequel/dataset/features.rb 62 def supports_distinct_on? 63 false 64 end
Whether the dataset supports CUBE with GROUP BY, false by default.
# File lib/sequel/dataset/features.rb 67 def supports_group_cube? 68 false 69 end
Whether the dataset supports ROLLUP with GROUP BY, false by default.
# File lib/sequel/dataset/features.rb 72 def supports_group_rollup? 73 false 74 end
Whether the dataset supports GROUPING SETS with GROUP BY, false by default.
# File lib/sequel/dataset/features.rb 77 def supports_grouping_sets? 78 false 79 end
Whether this dataset supports the insert_select
method for returning all columns values directly from an insert query, false by default.
# File lib/sequel/dataset/features.rb 83 def supports_insert_select? 84 supports_returning?(:insert) 85 end
Whether the dataset supports the INTERSECT and EXCEPT compound operations, true by default.
# File lib/sequel/dataset/features.rb 88 def supports_intersect_except? 89 true 90 end
Whether the dataset supports the INTERSECT ALL and EXCEPT ALL compound operations, true by default.
# File lib/sequel/dataset/features.rb 93 def supports_intersect_except_all? 94 true 95 end
Whether the dataset supports the IS TRUE syntax, true by default.
# File lib/sequel/dataset/features.rb 98 def supports_is_true? 99 true 100 end
Whether the dataset supports the JOIN table USING (column1, …) syntax, true by default. If false, support is emulated using JOIN table ON (table.column1 = other_table.column1).
# File lib/sequel/dataset/features.rb 104 def supports_join_using? 105 true 106 end
Whether the dataset supports LATERAL for subqueries in the FROM or JOIN clauses, false by default.
# File lib/sequel/dataset/features.rb 109 def supports_lateral_subqueries? 110 false 111 end
Whether modifying joined datasets is supported, false by default.
# File lib/sequel/dataset/features.rb 124 def supports_modifying_joins? 125 false 126 end
Whether the IN/NOT IN operators support multiple columns when an array of values is given, true by default.
# File lib/sequel/dataset/features.rb 130 def supports_multiple_column_in? 131 true 132 end
Whether the dataset supports skipping raising an error instead of waiting for locked rows when returning data, false by default.
# File lib/sequel/dataset/features.rb 119 def supports_nowait? 120 false 121 end
Whether the dataset supports or can fully emulate the DISTINCT ON clause, including respecting the ORDER BY clause, false by default.
# File lib/sequel/dataset/features.rb 141 def supports_ordered_distinct_on? 142 supports_distinct_on? 143 end
Whether the dataset supports pattern matching by regular expressions, false by default.
# File lib/sequel/dataset/features.rb 146 def supports_regexp? 147 false 148 end
Whether the dataset supports REPLACE syntax, false by default.
# File lib/sequel/dataset/features.rb 151 def supports_replace? 152 false 153 end
Whether the RETURNING clause is supported for the given type of query, false by default. type
can be :insert, :update, or :delete.
# File lib/sequel/dataset/features.rb 157 def supports_returning?(type) 158 false 159 end
Whether the database supports SELECT *, column FROM table
, true by default.
# File lib/sequel/dataset/features.rb 167 def supports_select_all_and_column? 168 true 169 end
Whether the dataset supports skipping locked rows when returning data, false by default.
# File lib/sequel/dataset/features.rb 162 def supports_skip_locked? 163 false 164 end
Whether the dataset supports timezones in literal timestamps, false by default.
# File lib/sequel/dataset/features.rb 172 def supports_timestamp_timezones? 173 false 174 end
Whether the dataset supports fractional seconds in literal timestamps, true by default.
# File lib/sequel/dataset/features.rb 177 def supports_timestamp_usecs? 178 true 179 end
Whether the dataset supports WHERE TRUE (or WHERE 1 for databases that that use 1 for true), true by default.
# File lib/sequel/dataset/features.rb 188 def supports_where_true? 189 true 190 end
Whether the dataset supports window functions, false by default.
# File lib/sequel/dataset/features.rb 182 def supports_window_functions? 183 false 184 end
Private Instance Methods
Whether insert(nil) or insert({}) must be emulated by using at least one value.
# File lib/sequel/dataset/features.rb 196 def insert_supports_empty_values? 197 true 198 end
Whether the dataset needs ESCAPE for LIKE for correct behavior.
# File lib/sequel/dataset/features.rb 201 def requires_like_escape? 202 true 203 end
Whether common table expressions are supported in UNION/INTERSECT/EXCEPT clauses.
# File lib/sequel/dataset/features.rb 206 def supports_cte_in_compounds? 207 supports_cte_in_subqueries? 208 end
Whether the database supports quoting function names.
# File lib/sequel/dataset/features.rb 211 def supports_quoted_function_names? 212 false 213 end
Whether the RETURNING clause is used for the given dataset. type
can be :insert, :update, or :delete.
# File lib/sequel/dataset/features.rb 217 def uses_returning?(type) 218 opts[:returning] && !@opts[:sql] && supports_returning?(type) 219 end
Whether the dataset uses WITH ROLLUP/CUBE instead of ROLLUP()/CUBE().
# File lib/sequel/dataset/features.rb 222 def uses_with_rollup? 223 false 224 end
6 - Miscellaneous methods
↑ topAttributes
The database related to this dataset. This is the Database
instance that will execute all of this dataset's queries.
The hash of options for this dataset, keys are symbols.
Public Class Methods
Constructs a new Dataset
instance with an associated database and options. Datasets are usually constructed by invoking the Database#[]
method:
DB[:posts]
Sequel::Dataset
is an abstract class that is not useful by itself. Each database adapter provides a subclass of Sequel::Dataset
, and has the Database#dataset
method return an instance of that subclass.
# File lib/sequel/dataset/misc.rb 25 def initialize(db) 26 @db = db 27 @opts = OPTS 28 @cache = {} 29 freeze 30 end
Public Instance Methods
Define a hash value such that datasets with the same class, DB, and opts will be considered equal.
# File lib/sequel/dataset/misc.rb 34 def ==(o) 35 o.is_a?(self.class) && db == o.db && opts == o.opts 36 end
An object representing the current date or time, should be an instance of Sequel.datetime_class
.
# File lib/sequel/dataset/misc.rb 40 def current_datetime 41 Sequel.datetime_class.now 42 end
Return self, as datasets are always frozen.
# File lib/sequel/dataset/misc.rb 50 def dup 51 self 52 end
Yield a dataset for each server in the connection pool that is tied to that server. Intended for use in sharded environments where all servers need to be modified with the same data:
DB[:configs].where(key: 'setting').each_server{|ds| ds.update(value: 'new_value')}
# File lib/sequel/dataset/misc.rb 59 def each_server 60 db.servers.each{|s| yield server(s)} 61 end
Alias for ==
# File lib/sequel/dataset/misc.rb 45 def eql?(o) 46 self == o 47 end
Returns the string with the LIKE metacharacters (% and _) escaped. Useful for when the LIKE term is a user-provided string where metacharacters should not be recognized. Example:
ds.escape_like("foo\\%_") # 'foo\\\%\_'
# File lib/sequel/dataset/misc.rb 68 def escape_like(string) 69 string.gsub(/[\\%_]/){|m| "\\#{m}"} 70 end
Alias of first_source_alias
# File lib/sequel/dataset/misc.rb 91 def first_source 92 first_source_alias 93 end
The first source (primary table) for this dataset. If the dataset doesn't have a table, raises an Error
. If the table is aliased, returns the aliased name.
DB[:table].first_source_alias # => :table DB[Sequel[:table].as(:t)].first_source_alias # => :t
# File lib/sequel/dataset/misc.rb 103 def first_source_alias 104 source = @opts[:from] 105 if source.nil? || source.empty? 106 raise Error, 'No source specified for query' 107 end 108 case s = source.first 109 when SQL::AliasedExpression 110 s.alias 111 when Symbol 112 _, _, aliaz = split_symbol(s) 113 aliaz ? aliaz.to_sym : s 114 else 115 s 116 end 117 end
The first source (primary table) for this dataset. If the dataset doesn't have a table, raises an error. If the table is aliased, returns the original table, not the alias
DB[:table].first_source_table # => :table DB[Sequel[:table].as(:t)].first_source_table # => :table
# File lib/sequel/dataset/misc.rb 128 def first_source_table 129 source = @opts[:from] 130 if source.nil? || source.empty? 131 raise Error, 'No source specified for query' 132 end 133 case s = source.first 134 when SQL::AliasedExpression 135 s.expression 136 when Symbol 137 sch, table, aliaz = split_symbol(s) 138 aliaz ? (sch ? SQL::QualifiedIdentifier.new(sch, table) : table.to_sym) : s 139 else 140 s 141 end 142 end
Freeze the opts when freezing the dataset.
# File lib/sequel/dataset/misc.rb 74 def freeze 75 @opts.freeze 76 super 77 end
Define a hash value such that datasets with the same class, DB, and opts, will have the same hash value.
# File lib/sequel/dataset/misc.rb 146 def hash 147 [self.class, db, opts].hash 148 end
Returns a string representation of the dataset including the class name and the corresponding SQL
select statement.
# File lib/sequel/dataset/misc.rb 152 def inspect 153 "#<#{visible_class_name}: #{sql.inspect}>" 154 end
Whether this dataset is a joined dataset (multiple FROM tables or any JOINs).
# File lib/sequel/dataset/misc.rb 157 def joined_dataset? 158 !!((opts[:from].is_a?(Array) && opts[:from].size > 1) || opts[:join]) 159 end
The alias to use for the row_number column, used when emulating OFFSET support and for eager limit strategies
# File lib/sequel/dataset/misc.rb 163 def row_number_column 164 :x_sequel_row_number_x 165 end
Splits a possible implicit alias in c
, handling both SQL::AliasedExpressions and Symbols. Returns an array of two elements, with the first being the main expression, and the second being the alias.
# File lib/sequel/dataset/misc.rb 176 def split_alias(c) 177 case c 178 when Symbol 179 c_table, column, aliaz = split_symbol(c) 180 [c_table ? SQL::QualifiedIdentifier.new(c_table, column.to_sym) : column.to_sym, aliaz] 181 when SQL::AliasedExpression 182 [c.expression, c.alias] 183 when SQL::JoinClause 184 [c.table, c.table_alias] 185 else 186 [c, nil] 187 end 188 end
This returns an SQL::Identifier
or SQL::AliasedExpression
containing an SQL
identifier that represents the unqualified column for the given value. The given value should be a Symbol
, SQL::Identifier
, SQL::QualifiedIdentifier
, or SQL::AliasedExpression
containing one of those. In other cases, this returns nil.
# File lib/sequel/dataset/misc.rb 195 def unqualified_column_for(v) 196 unless v.is_a?(String) 197 _unqualified_column_for(v) 198 end 199 end
Creates a unique table alias that hasn't already been used in the dataset. table_alias can be any type of object accepted by alias_symbol. The symbol returned will be the implicit alias in the argument, possibly appended with “_N” if the implicit alias has already been used, where N is an integer starting at 0 and increasing until an unused one is found.
You can provide a second addition array argument containing symbols that should not be considered valid table aliases. The current aliases for the FROM and JOIN tables are automatically included in this array.
DB[:table].unused_table_alias(:t) # => :t DB[:table].unused_table_alias(:table) # => :table_0 DB[:table, :table_0].unused_table_alias(:table) # => :table_1 DB[:table, :table_0].unused_table_alias(:table, [:table_1, :table_2]) # => :table_3
# File lib/sequel/dataset/misc.rb 223 def unused_table_alias(table_alias, used_aliases = []) 224 table_alias = alias_symbol(table_alias) 225 used_aliases += opts[:from].map{|t| alias_symbol(t)} if opts[:from] 226 used_aliases += opts[:join].map{|j| j.table_alias ? alias_alias_symbol(j.table_alias) : alias_symbol(j.table)} if opts[:join] 227 if used_aliases.include?(table_alias) 228 i = 0 229 while true 230 ta = :"#{table_alias}_#{i}" 231 return ta unless used_aliases.include?(ta) 232 i += 1 233 end 234 else 235 table_alias 236 end 237 end
Return a modified dataset with quote_identifiers set.
# File lib/sequel/dataset/misc.rb 240 def with_quote_identifiers(v) 241 clone(:quote_identifiers=>v, :skip_symbol_cache=>true) 242 end
Protected Instance Methods
The cached columns for the current dataset.
# File lib/sequel/dataset/misc.rb 271 def _columns 272 cache_get(:_columns) 273 end
Retreive a value from the dataset's cache in a thread safe manner.
# File lib/sequel/dataset/misc.rb 253 def cache_get(k) 254 Sequel.synchronize{@cache[k]} 255 end
Set a value in the dataset's cache in a thread safe manner.
# File lib/sequel/dataset/misc.rb 258 def cache_set(k, v) 259 Sequel.synchronize{@cache[k] = v} 260 end
Clear the columns hash for the current dataset. This is not a thread safe operation, so it should only be used if the dataset could not be used by another thread (such as one that was just created via clone).
# File lib/sequel/dataset/misc.rb 266 def clear_columns_cache 267 @cache.delete(:_columns) 268 end
Private Instance Methods
Internal recursive version of unqualified_column_for
, handling Strings inside of other objects.
# File lib/sequel/dataset/misc.rb 331 def _unqualified_column_for(v) 332 case v 333 when Symbol 334 _, c, a = Sequel.split_symbol(v) 335 c = Sequel.identifier(c) 336 a ? c.as(a) : c 337 when String 338 Sequel.identifier(v) 339 when SQL::Identifier 340 v 341 when SQL::QualifiedIdentifier 342 _unqualified_column_for(v.column) 343 when SQL::AliasedExpression 344 if expr = unqualified_column_for(v.expression) 345 SQL::AliasedExpression.new(expr, v.alias) 346 end 347 end 348 end
Check the cache for the given key, returning the value. Otherwise, yield to get the dataset and cache the dataset under the given key.
# File lib/sequel/dataset/misc.rb 279 def cached_dataset(key) 280 unless ds = cache_get(key) 281 ds = yield 282 cache_set(key, ds) 283 end 284 285 ds 286 end
Return a cached placeholder literalizer for the given key if there is one for this dataset. If there isn't one, increment the counter for the number of calls for the key, and if the counter is at least three, then create a placeholder literalizer by yielding to the block, and cache it.
# File lib/sequel/dataset/misc.rb 293 def cached_placeholder_literalizer(key) 294 if loader = cache_get(key) 295 return loader unless loader.is_a?(Integer) 296 loader += 1 297 298 if loader >= 3 299 loader = Sequel::Dataset::PlaceholderLiteralizer.loader(self){|pl, _| yield pl} 300 cache_set(key, loader) 301 else 302 cache_set(key, loader + 1) 303 loader = nil 304 end 305 elsif cache_sql? 306 cache_set(key, 1) 307 end 308 309 loader 310 end
Set the columns for the current dataset.
# File lib/sequel/dataset/misc.rb 313 def columns=(v) 314 cache_set(:_columns, v) 315 end
Set the db, opts, and cache for the copy of the dataset.
# File lib/sequel/dataset/misc.rb 318 def initialize_copy(c) 319 @db = c.db 320 @opts = Hash[c.opts] 321 if cols = c.cache_get(:_columns) 322 @cache = {:_columns=>cols} 323 else 324 @cache = {} 325 end 326 end
Return the class name for this dataset, but skip anonymous classes
# File lib/sequel/dataset/misc.rb 351 def visible_class_name 352 c = self.class 353 c = c.superclass while c.name.nil? || c.name == '' 354 c.name 355 end
9 - Internal Methods relating to SQL Creation
↑ topConstants
- BITWISE_METHOD_MAP
- COUNT_FROM_SELF_OPTS
- COUNT_OF_ALL_AS_COUNT
- DEFAULT
- EXISTS
- IS_LITERALS
- IS_OPERATORS
- LIKE_OPERATORS
- N_ARITY_OPERATORS
- QUALIFY_KEYS
- REGEXP_OPERATORS
- TWO_ARITY_OPERATORS
- WILDCARD
Public Class Methods
Given a type (e.g. select) and an array of clauses, return an array of methods to call to build the SQL
string.
# File lib/sequel/dataset/sql.rb 195 def self.clause_methods(type, clauses) 196 clauses.map{|clause| :"#{type}_#{clause}_sql"}.freeze 197 end
Define a dataset literalization method for the given type in the given module, using the given clauses.
Arguments:
- mod
-
Module in which to define method
- type
-
Type of
SQL
literalization method to create, either :select, :insert, :update, or :delete - clauses
-
array of clauses that make up the
SQL
query for the type. This can either be a single array of symbols/strings, or it can be an array of pairs, with the first element in each pair being an if/elsif/else code fragment, and the second element in each pair being an array of symbol/strings for the appropriate branch.
# File lib/sequel/dataset/sql.rb 209 def self.def_sql_method(mod, type, clauses) 210 priv = type == :update || type == :insert 211 cacheable = type == :select || type == :delete 212 213 lines = [] 214 lines << 'private' if priv 215 lines << "def #{'_' if priv}#{type}_sql" 216 lines << 'if sql = opts[:sql]; return static_sql(sql) end' unless priv 217 lines << "if sql = cache_get(:_#{type}_sql); return sql end" if cacheable 218 lines << 'check_modification_allowed!' << 'check_not_limited!(:delete)' if type == :delete 219 lines << 'sql = @opts[:append_sql] || sql_string_origin' 220 221 if clauses.all?{|c| c.is_a?(Array)} 222 clauses.each do |i, cs| 223 lines << i 224 lines.concat(clause_methods(type, cs).map{|x| "#{x}(sql)"}) 225 end 226 lines << 'end' 227 else 228 lines.concat(clause_methods(type, clauses).map{|x| "#{x}(sql)"}) 229 end 230 231 lines << "cache_set(:_#{type}_sql, sql) if cache_sql?" if cacheable 232 lines << 'sql' 233 lines << 'end' 234 235 mod.class_eval lines.join("\n"), __FILE__, __LINE__ 236 end
Public Instance Methods
Append literalization of aliased expression to SQL
string.
# File lib/sequel/dataset/sql.rb 270 def aliased_expression_sql_append(sql, ae) 271 literal_append(sql, ae.expression) 272 as_sql_append(sql, ae.alias, ae.columns) 273 end
Append literalization of array to SQL
string.
# File lib/sequel/dataset/sql.rb 276 def array_sql_append(sql, a) 277 if a.empty? 278 sql << '(NULL)' 279 else 280 sql << '(' 281 expression_list_append(sql, a) 282 sql << ')' 283 end 284 end
Append literalization of boolean constant to SQL
string.
# File lib/sequel/dataset/sql.rb 287 def boolean_constant_sql_append(sql, constant) 288 if (constant == true || constant == false) && !supports_where_true? 289 sql << (constant == true ? '(1 = 1)' : '(1 = 0)') 290 else 291 literal_append(sql, constant) 292 end 293 end
Append literalization of case expression to SQL
string.
# File lib/sequel/dataset/sql.rb 296 def case_expression_sql_append(sql, ce) 297 sql << '(CASE' 298 if ce.expression? 299 sql << ' ' 300 literal_append(sql, ce.expression) 301 end 302 w = " WHEN " 303 t = " THEN " 304 ce.conditions.each do |c,r| 305 sql << w 306 literal_append(sql, c) 307 sql << t 308 literal_append(sql, r) 309 end 310 sql << " ELSE " 311 literal_append(sql, ce.default) 312 sql << " END)" 313 end
Append literalization of cast expression to SQL
string.
# File lib/sequel/dataset/sql.rb 316 def cast_sql_append(sql, expr, type) 317 sql << 'CAST(' 318 literal_append(sql, expr) 319 sql << ' AS ' << db.cast_type_literal(type).to_s 320 sql << ')' 321 end
Append literalization of column all selection to SQL
string.
# File lib/sequel/dataset/sql.rb 324 def column_all_sql_append(sql, ca) 325 qualified_identifier_sql_append(sql, ca.table, WILDCARD) 326 end
Append literalization of complex expression to SQL
string.
# File lib/sequel/dataset/sql.rb 329 def complex_expression_sql_append(sql, op, args) 330 case op 331 when *IS_OPERATORS 332 r = args[1] 333 if r.nil? || supports_is_true? 334 raise(InvalidOperation, 'Invalid argument used for IS operator') unless val = IS_LITERALS[r] 335 sql << '(' 336 literal_append(sql, args[0]) 337 sql << ' ' << op.to_s << ' ' 338 sql << val << ')' 339 elsif op == :IS 340 complex_expression_sql_append(sql, :"=", args) 341 else 342 complex_expression_sql_append(sql, :OR, [SQL::BooleanExpression.new(:"!=", *args), SQL::BooleanExpression.new(:IS, args[0], nil)]) 343 end 344 when :IN, :"NOT IN" 345 cols = args[0] 346 vals = args[1] 347 col_array = true if cols.is_a?(Array) 348 if vals.is_a?(Array) 349 val_array = true 350 empty_val_array = vals == [] 351 end 352 if empty_val_array 353 literal_append(sql, empty_array_value(op, cols)) 354 elsif col_array 355 if !supports_multiple_column_in? 356 if val_array 357 expr = SQL::BooleanExpression.new(:OR, *vals.to_a.map{|vs| SQL::BooleanExpression.from_value_pairs(cols.to_a.zip(vs).map{|c, v| [c, v]})}) 358 literal_append(sql, op == :IN ? expr : ~expr) 359 else 360 old_vals = vals 361 vals = vals.naked if vals.is_a?(Sequel::Dataset) 362 vals = vals.to_a 363 val_cols = old_vals.columns 364 complex_expression_sql_append(sql, op, [cols, vals.map!{|x| x.values_at(*val_cols)}]) 365 end 366 else 367 # If the columns and values are both arrays, use array_sql instead of 368 # literal so that if values is an array of two element arrays, it 369 # will be treated as a value list instead of a condition specifier. 370 sql << '(' 371 literal_append(sql, cols) 372 sql << ' ' << op.to_s << ' ' 373 if val_array 374 array_sql_append(sql, vals) 375 else 376 literal_append(sql, vals) 377 end 378 sql << ')' 379 end 380 else 381 sql << '(' 382 literal_append(sql, cols) 383 sql << ' ' << op.to_s << ' ' 384 literal_append(sql, vals) 385 sql << ')' 386 end 387 when :LIKE, :'NOT LIKE' 388 sql << '(' 389 literal_append(sql, args[0]) 390 sql << ' ' << op.to_s << ' ' 391 literal_append(sql, args[1]) 392 if requires_like_escape? 393 sql << " ESCAPE " 394 literal_append(sql, "\\") 395 end 396 sql << ')' 397 when :ILIKE, :'NOT ILIKE' 398 complex_expression_sql_append(sql, (op == :ILIKE ? :LIKE : :"NOT LIKE"), args.map{|v| Sequel.function(:UPPER, v)}) 399 when :** 400 function_sql_append(sql, Sequel.function(:power, *args)) 401 when *TWO_ARITY_OPERATORS 402 if REGEXP_OPERATORS.include?(op) && !supports_regexp? 403 raise InvalidOperation, "Pattern matching via regular expressions is not supported on #{db.database_type}" 404 end 405 sql << '(' 406 literal_append(sql, args[0]) 407 sql << ' ' << op.to_s << ' ' 408 literal_append(sql, args[1]) 409 sql << ')' 410 when *N_ARITY_OPERATORS 411 sql << '(' 412 c = false 413 op_str = " #{op} " 414 args.each do |a| 415 sql << op_str if c 416 literal_append(sql, a) 417 c ||= true 418 end 419 sql << ')' 420 when :NOT 421 sql << 'NOT ' 422 literal_append(sql, args[0]) 423 when :NOOP 424 literal_append(sql, args[0]) 425 when :'B~' 426 sql << '~' 427 literal_append(sql, args[0]) 428 when :extract 429 sql << 'extract(' << args[0].to_s << ' FROM ' 430 literal_append(sql, args[1]) 431 sql << ')' 432 else 433 raise(InvalidOperation, "invalid operator #{op}") 434 end 435 end
Append literalization of constant to SQL
string.
# File lib/sequel/dataset/sql.rb 438 def constant_sql_append(sql, constant) 439 sql << constant.to_s 440 end
Append literalization of delayed evaluation to SQL
string, causing the delayed evaluation proc to be evaluated.
# File lib/sequel/dataset/sql.rb 444 def delayed_evaluation_sql_append(sql, delay) 445 # Delayed evaluations are used specifically so the SQL 446 # can differ in subsequent calls, so we definitely don't 447 # want to cache the sql in this case. 448 disable_sql_caching! 449 450 if recorder = @opts[:placeholder_literalizer] 451 recorder.use(sql, lambda{delay.call(self)}, nil) 452 else 453 literal_append(sql, delay.call(self)) 454 end 455 end
Append literalization of function call to SQL
string.
# File lib/sequel/dataset/sql.rb 458 def function_sql_append(sql, f) 459 name = f.name 460 opts = f.opts 461 462 if opts[:emulate] 463 if emulate_function?(name) 464 emulate_function_sql_append(sql, f) 465 return 466 end 467 468 name = native_function_name(name) 469 end 470 471 sql << 'LATERAL ' if opts[:lateral] 472 473 case name 474 when SQL::Identifier 475 if supports_quoted_function_names? && opts[:quoted] 476 literal_append(sql, name) 477 else 478 sql << name.value.to_s 479 end 480 when SQL::QualifiedIdentifier 481 if supports_quoted_function_names? && opts[:quoted] != false 482 literal_append(sql, name) 483 else 484 sql << split_qualifiers(name).join('.') 485 end 486 else 487 if supports_quoted_function_names? && opts[:quoted] 488 quote_identifier_append(sql, name) 489 else 490 sql << name.to_s 491 end 492 end 493 494 sql << '(' 495 if opts[:*] 496 sql << '*' 497 else 498 sql << "DISTINCT " if opts[:distinct] 499 expression_list_append(sql, f.args) 500 if order = opts[:order] 501 sql << " ORDER BY " 502 expression_list_append(sql, order) 503 end 504 end 505 sql << ')' 506 507 if group = opts[:within_group] 508 sql << " WITHIN GROUP (ORDER BY " 509 expression_list_append(sql, group) 510 sql << ')' 511 end 512 513 if filter = opts[:filter] 514 sql << " FILTER (WHERE " 515 literal_append(sql, filter_expr(filter, &opts[:filter_block])) 516 sql << ')' 517 end 518 519 if window = opts[:over] 520 sql << ' OVER ' 521 window_sql_append(sql, window.opts) 522 end 523 524 if opts[:with_ordinality] 525 sql << " WITH ORDINALITY" 526 end 527 end
Append literalization of JOIN clause without ON or USING to SQL
string.
# File lib/sequel/dataset/sql.rb 530 def join_clause_sql_append(sql, jc) 531 table = jc.table 532 table_alias = jc.table_alias 533 table_alias = nil if table == table_alias && !jc.column_aliases 534 sql << ' ' << join_type_sql(jc.join_type) << ' ' 535 identifier_append(sql, table) 536 as_sql_append(sql, table_alias, jc.column_aliases) if table_alias 537 end
Append literalization of JOIN ON clause to SQL
string.
# File lib/sequel/dataset/sql.rb 540 def join_on_clause_sql_append(sql, jc) 541 join_clause_sql_append(sql, jc) 542 sql << ' ON ' 543 literal_append(sql, filter_expr(jc.on)) 544 end
Append literalization of JOIN USING clause to SQL
string.
# File lib/sequel/dataset/sql.rb 547 def join_using_clause_sql_append(sql, jc) 548 join_clause_sql_append(sql, jc) 549 sql << ' USING (' 550 column_list_append(sql, jc.using) 551 sql << ')' 552 end
Append literalization of negative boolean constant to SQL
string.
# File lib/sequel/dataset/sql.rb 555 def negative_boolean_constant_sql_append(sql, constant) 556 sql << 'NOT ' 557 boolean_constant_sql_append(sql, constant) 558 end
Append literalization of ordered expression to SQL
string.
# File lib/sequel/dataset/sql.rb 561 def ordered_expression_sql_append(sql, oe) 562 literal_append(sql, oe.expression) 563 sql << (oe.descending ? ' DESC' : ' ASC') 564 case oe.nulls 565 when :first 566 sql << " NULLS FIRST" 567 when :last 568 sql << " NULLS LAST" 569 end 570 end
Append literalization of placeholder literal string to SQL
string.
# File lib/sequel/dataset/sql.rb 573 def placeholder_literal_string_sql_append(sql, pls) 574 args = pls.args 575 str = pls.str 576 sql << '(' if pls.parens 577 if args.is_a?(Hash) 578 if args.empty? 579 sql << str 580 else 581 re = /:(#{args.keys.map{|k| Regexp.escape(k.to_s)}.join('|')})\b/ 582 while true 583 previous, q, str = str.partition(re) 584 sql << previous 585 literal_append(sql, args[($1||q[1..-1].to_s).to_sym]) unless q.empty? 586 break if str.empty? 587 end 588 end 589 elsif str.is_a?(Array) 590 len = args.length 591 str.each_with_index do |s, i| 592 sql << s 593 literal_append(sql, args[i]) unless i == len 594 end 595 unless str.length == args.length || str.length == args.length + 1 596 raise Error, "Mismatched number of placeholders (#{str.length}) and placeholder arguments (#{args.length}) when using placeholder array" 597 end 598 else 599 i = -1 600 match_len = args.length - 1 601 while true 602 previous, q, str = str.partition('?') 603 sql << previous 604 literal_append(sql, args.at(i+=1)) unless q.empty? 605 if str.empty? 606 unless i == match_len 607 raise Error, "Mismatched number of placeholders (#{i+1}) and placeholder arguments (#{args.length}) when using placeholder string" 608 end 609 break 610 end 611 end 612 end 613 sql << ')' if pls.parens 614 end
Append literalization of qualified identifier to SQL
string. If 3 arguments are given, the 2nd should be the table/qualifier and the third should be column/qualified. If 2 arguments are given, the 2nd should be an SQL::QualifiedIdentifier
.
# File lib/sequel/dataset/sql.rb 619 def qualified_identifier_sql_append(sql, table, column=(c = table.column; table = table.table; c)) 620 identifier_append(sql, table) 621 sql << '.' 622 identifier_append(sql, column) 623 end
Append literalization of unqualified identifier to SQL
string. Adds quoting to identifiers (columns and tables). If identifiers are not being quoted, returns name as a string. If identifiers are being quoted quote the name with quoted_identifier.
# File lib/sequel/dataset/sql.rb 629 def quote_identifier_append(sql, name) 630 if name.is_a?(LiteralString) 631 sql << name 632 else 633 name = name.value if name.is_a?(SQL::Identifier) 634 name = input_identifier(name) 635 if quote_identifiers? 636 quoted_identifier_append(sql, name) 637 else 638 sql << name 639 end 640 end 641 end
Append literalization of identifier or unqualified identifier to SQL
string.
# File lib/sequel/dataset/sql.rb 644 def quote_schema_table_append(sql, table) 645 schema, table = schema_and_table(table) 646 if schema 647 quote_identifier_append(sql, schema) 648 sql << '.' 649 end 650 quote_identifier_append(sql, table) 651 end
Append literalization of quoted identifier to SQL
string. This method quotes the given name with the SQL
standard double quote. should be overridden by subclasses to provide quoting not matching the SQL
standard, such as backtick (used by MySQL
and SQLite
).
# File lib/sequel/dataset/sql.rb 657 def quoted_identifier_append(sql, name) 658 sql << '"' << name.to_s.gsub('"', '""') << '"' 659 end
Split the schema information from the table, returning two strings, one for the schema and one for the table. The returned schema may be nil, but the table will always have a string value.
Note that this function does not handle tables with more than one level of qualification (e.g. database.schema.table on Microsoft SQL
Server).
# File lib/sequel/dataset/sql.rb 668 def schema_and_table(table_name, sch=nil) 669 sch = sch.to_s if sch 670 case table_name 671 when Symbol 672 s, t, _ = split_symbol(table_name) 673 [s||sch, t] 674 when SQL::QualifiedIdentifier 675 [table_name.table.to_s, table_name.column.to_s] 676 when SQL::Identifier 677 [sch, table_name.value.to_s] 678 when String 679 [sch, table_name] 680 else 681 raise Error, 'table_name should be a Symbol, SQL::QualifiedIdentifier, SQL::Identifier, or String' 682 end 683 end
Splits table_name into an array of strings.
ds.split_qualifiers(:s) # ['s'] ds.split_qualifiers(Sequel[:t][:s]) # ['t', 's'] ds.split_qualifiers(Sequel[:d][:t][:s]) # ['d', 't', 's'] ds.split_qualifiers(Sequel.qualify(Sequel[:h][:d], Sequel[:t][:s])) # ['h', 'd', 't', 's']
# File lib/sequel/dataset/sql.rb 691 def split_qualifiers(table_name, *args) 692 case table_name 693 when SQL::QualifiedIdentifier 694 split_qualifiers(table_name.table, nil) + split_qualifiers(table_name.column, nil) 695 else 696 sch, table = schema_and_table(table_name, *args) 697 sch ? [sch, table] : [table] 698 end 699 end
Append literalization of subscripts (SQL array accesses) to SQL
string.
# File lib/sequel/dataset/sql.rb 702 def subscript_sql_append(sql, s) 703 literal_append(sql, s.expression) 704 sql << '[' 705 sub = s.sub 706 if sub.length == 1 && (range = sub.first).is_a?(Range) 707 literal_append(sql, range.begin) 708 sql << ':' 709 e = range.end 710 e -= 1 if range.exclude_end? && e.is_a?(Integer) 711 literal_append(sql, e) 712 else 713 expression_list_append(sql, s.sub) 714 end 715 sql << ']' 716 end
Append literalization of windows (for window functions) to SQL
string.
# File lib/sequel/dataset/sql.rb 719 def window_sql_append(sql, opts) 720 raise(Error, 'This dataset does not support window functions') unless supports_window_functions? 721 sql << '(' 722 window, part, order, frame = opts.values_at(:window, :partition, :order, :frame) 723 space = false 724 space_s = ' ' 725 if window 726 literal_append(sql, window) 727 space = true 728 end 729 if part 730 sql << space_s if space 731 sql << "PARTITION BY " 732 expression_list_append(sql, Array(part)) 733 space = true 734 end 735 if order 736 sql << space_s if space 737 sql << "ORDER BY " 738 expression_list_append(sql, Array(order)) 739 space = true 740 end 741 case frame 742 when nil 743 # nothing 744 when :all 745 sql << space_s if space 746 sql << "ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING" 747 when :rows 748 sql << space_s if space 749 sql << "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" 750 when String 751 sql << space_s if space 752 sql << frame 753 else 754 raise Error, "invalid window frame clause, should be :all, :rows, a string, or nil" 755 end 756 sql << ')' 757 end
Protected Instance Methods
Return a from_self
dataset if an order or limit is specified, so it works as expected with UNION, EXCEPT, and INTERSECT clauses.
# File lib/sequel/dataset/sql.rb 763 def compound_from_self 764 (@opts[:sql] || @opts[:limit] || @opts[:order] || @opts[:offset]) ? from_self : self 765 end
Private Instance Methods
Formats the truncate statement. Assumes the table given has already been literalized.
# File lib/sequel/dataset/sql.rb 771 def _truncate_sql(table) 772 "TRUNCATE TABLE #{table}" 773 end
Clone of this dataset usable in aggregate operations. Does a from_self
if dataset contains any parameters that would affect normal aggregation, or just removes an existing order if not.
# File lib/sequel/dataset/sql.rb 814 def aggregate_dataset 815 options_overlap(COUNT_FROM_SELF_OPTS) ? from_self : unordered 816 end
Returns an appropriate symbol for the alias represented by s.
# File lib/sequel/dataset/sql.rb 776 def alias_alias_symbol(s) 777 case s 778 when Symbol 779 s 780 when String 781 s.to_sym 782 when SQL::Identifier 783 s.value.to_s.to_sym 784 else 785 raise Error, "Invalid alias for alias_alias_symbol: #{s.inspect}" 786 end 787 end
Returns an appropriate alias symbol for the given object, which can be a Symbol
, String
, SQL::Identifier
, SQL::QualifiedIdentifier
, or SQL::AliasedExpression
.
# File lib/sequel/dataset/sql.rb 792 def alias_symbol(sym) 793 case sym 794 when Symbol 795 s, t, a = split_symbol(sym) 796 a || s ? (a || t).to_sym : sym 797 when String 798 sym.to_sym 799 when SQL::Identifier 800 sym.value.to_s.to_sym 801 when SQL::QualifiedIdentifier 802 alias_symbol(sym.column) 803 when SQL::AliasedExpression 804 alias_alias_symbol(sym.alias) 805 else 806 raise Error, "Invalid alias for alias_symbol: #{sym.inspect}" 807 end 808 end
Append aliasing expression to SQL
string.
# File lib/sequel/dataset/sql.rb 819 def as_sql_append(sql, aliaz, column_aliases=nil) 820 sql << ' AS ' 821 quote_identifier_append(sql, aliaz) 822 if column_aliases 823 raise Error, "#{db.database_type} does not support derived column lists" unless supports_derived_column_lists? 824 sql << '(' 825 identifier_list_append(sql, column_aliases) 826 sql << ')' 827 end 828 end
Don't allow caching SQL
if specifically marked not to.
# File lib/sequel/dataset/sql.rb 831 def cache_sql? 832 !@opts[:no_cache_sql] && !cache_get(:_no_cache_sql) 833 end
Raise an InvalidOperation exception if deletion is not allowed for this dataset.
# File lib/sequel/dataset/sql.rb 836 def check_modification_allowed! 837 raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group] 838 raise(InvalidOperation, "Joined datasets cannot be modified") if !supports_modifying_joins? && joined_dataset? 839 end
Raise error if the dataset uses limits or offsets.
# File lib/sequel/dataset/sql.rb 842 def check_not_limited!(type) 843 return if @opts[:skip_limit_check] && type != :truncate 844 raise InvalidOperation, "Dataset##{type} not supported on datasets with limits or offsets" if opts[:limit] || opts[:offset] 845 end
Alias of check_modification_allowed!
# File lib/sequel/dataset/sql.rb 848 def check_truncation_allowed! 849 check_modification_allowed! 850 end
Append column list to SQL
string. If the column list is empty, a wildcard (*) is appended.
# File lib/sequel/dataset/sql.rb 854 def column_list_append(sql, columns) 855 if (columns.nil? || columns.empty?) 856 sql << '*' 857 else 858 expression_list_append(sql, columns) 859 end 860 end
Yield each pair of arguments to the block, which should return an object representing the SQL
expression for those two arguments. For more than two arguments, the first argument to the block will be result of the previous block call.
# File lib/sequel/dataset/sql.rb 866 def complex_expression_arg_pairs(args) 867 case args.length 868 when 1 869 args[0] 870 when 2 871 yield args[0], args[1] 872 else 873 args.inject{|m, a| yield(m, a)} 874 end 875 end
Append the literalization of the args using complex_expression_arg_pairs
to the given SQL
string, used when database operator/function is 2-ary where Sequel
expression is N-ary.
# File lib/sequel/dataset/sql.rb 880 def complex_expression_arg_pairs_append(sql, args, &block) 881 literal_append(sql, complex_expression_arg_pairs(args, &block)) 882 end
Append literalization of complex expression to SQL
string, for operators unsupported by some databases. Used by adapters for databases that don't support the operators natively.
# File lib/sequel/dataset/sql.rb 887 def complex_expression_emulate_append(sql, op, args) 888 case op 889 when :% 890 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.function(:MOD, a, b)} 891 when :>> 892 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel./(a, Sequel.function(:power, 2, b))} 893 when :<< 894 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.*(a, Sequel.function(:power, 2, b))} 895 when :&, :|, :^ 896 f = BITWISE_METHOD_MAP[op] 897 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.function(f, a, b)} 898 when :'B~' 899 sql << "((0 - " 900 literal_append(sql, args[0]) 901 sql << ") - 1)" 902 end 903 end
Append literalization of dataset used in UNION/INTERSECT/EXCEPT clause to SQL
string.
# File lib/sequel/dataset/sql.rb 906 def compound_dataset_sql_append(sql, ds) 907 subselect_sql_append(sql, ds) 908 end
The alias to use for datasets, takes a number to make sure the name is unique.
# File lib/sequel/dataset/sql.rb 911 def dataset_alias(number) 912 :"t#{number}" 913 end
The strftime format to use when literalizing the time.
# File lib/sequel/dataset/sql.rb 916 def default_timestamp_format 917 requires_sql_standard_datetimes? ? "TIMESTAMP '%Y-%m-%d %H:%M:%S%N%z'" : "'%Y-%m-%d %H:%M:%S%N%z'" 918 end
# File lib/sequel/dataset/sql.rb 920 def delete_delete_sql(sql) 921 sql << 'DELETE' 922 end
# File lib/sequel/dataset/sql.rb 924 def delete_from_sql(sql) 925 if f = @opts[:from] 926 sql << ' FROM ' 927 source_list_append(sql, f) 928 end 929 end
Disable caching of SQL
for the current dataset
# File lib/sequel/dataset/sql.rb 932 def disable_sql_caching! 933 cache_set(:_no_cache_sql, true) 934 end
An expression for how to handle an empty array lookup.
# File lib/sequel/dataset/sql.rb 976 def empty_array_value(op, cols) 977 {1 => ((op == :IN) ? 0 : 1)} 978 end
An SQL
FROM clause to use in SELECT statements where the dataset has no from tables.
# File lib/sequel/dataset/sql.rb 938 def empty_from_sql 939 nil 940 end
Whether to emulate the function with the given name. This should only be true if the emulation goes beyond choosing a function with a different name.
# File lib/sequel/dataset/sql.rb 944 def emulate_function?(name) 945 false 946 end
Append literalization of array of expressions to SQL
string, separating them with commas.
# File lib/sequel/dataset/sql.rb 950 def expression_list_append(sql, columns) 951 c = false 952 co = ', ' 953 columns.each do |col| 954 sql << co if c 955 literal_append(sql, col) 956 c ||= true 957 end 958 end
Format the timestamp based on the default_timestamp_format
, with a couple of modifiers. First, allow %N to be used for fractions seconds (if the database supports them), and override %z to always use a numeric offset of hours and minutes.
# File lib/sequel/dataset/sql.rb 984 def format_timestamp(v) 985 v2 = db.from_application_timestamp(v) 986 fmt = default_timestamp_format.gsub(/%[Nz]/) do |m| 987 if m == '%N' 988 # Ruby 1.9 supports %N in timestamp formats, but Sequel has supported %N 989 # for longer in a different way, where the . is already appended and only 6 990 # decimal places are used by default. 991 format_timestamp_usec(v.is_a?(DateTime) ? v.sec_fraction*(1000000) : v.usec) if supports_timestamp_usecs? 992 else 993 if supports_timestamp_timezones? 994 # Would like to just use %z format, but it doesn't appear to work on Windows 995 # Instead, the offset fragment is constructed manually 996 minutes = (v2.is_a?(DateTime) ? v2.offset * 1440 : v2.utc_offset/60).to_i 997 format_timestamp_offset(*minutes.divmod(60)) 998 end 999 end 1000 end 1001 v2.strftime(fmt) 1002 end
Return the SQL
timestamp fragment to use for the timezone offset.
# File lib/sequel/dataset/sql.rb 1005 def format_timestamp_offset(hour, minute) 1006 sprintf("%+03i%02i", hour, minute) 1007 end
Return the SQL
timestamp fragment to use for the fractional time part. Should start with the decimal point. Uses 6 decimal places by default.
# File lib/sequel/dataset/sql.rb 1011 def format_timestamp_usec(usec, ts=timestamp_precision) 1012 unless ts == 6 1013 usec = usec/(10 ** (6 - ts)) 1014 end 1015 sprintf(".%0#{ts}d", usec) 1016 end
Append literalization of array of grouping elements to SQL
string, seperating them with commas.
# File lib/sequel/dataset/sql.rb 961 def grouping_element_list_append(sql, columns) 962 c = false 963 co = ', ' 964 columns.each do |col| 965 sql << co if c 966 if col.is_a?(Array) && col.empty? 967 sql << '()' 968 else 969 literal_append(sql, Array(col)) 970 end 971 c ||= true 972 end 973 end
Append literalization of identifier to SQL
string, considering regular strings as SQL
identifiers instead of SQL
strings.
# File lib/sequel/dataset/sql.rb 1020 def identifier_append(sql, v) 1021 if v.is_a?(String) 1022 case v 1023 when LiteralString 1024 sql << v 1025 when SQL::Blob 1026 literal_append(sql, v) 1027 else 1028 quote_identifier_append(sql, v) 1029 end 1030 else 1031 literal_append(sql, v) 1032 end 1033 end
Append literalization of array of identifiers to SQL
string.
# File lib/sequel/dataset/sql.rb 1036 def identifier_list_append(sql, args) 1037 c = false 1038 comma = ', ' 1039 args.each do |a| 1040 sql << comma if c 1041 identifier_append(sql, a) 1042 c ||= true 1043 end 1044 end
Upcase identifiers by default when inputting them into the database.
# File lib/sequel/dataset/sql.rb 1047 def input_identifier(v) 1048 v.to_s.upcase 1049 end
# File lib/sequel/dataset/sql.rb 1060 def insert_columns_sql(sql) 1061 columns = opts[:columns] 1062 if columns && !columns.empty? 1063 sql << ' (' 1064 identifier_list_append(sql, columns) 1065 sql << ')' 1066 end 1067 end
The columns and values to use for an empty insert if the database doesn't support INSERT with DEFAULT
VALUES.
# File lib/sequel/dataset/sql.rb 1071 def insert_empty_columns_values 1072 [[columns.last], [DEFAULT]] 1073 end
# File lib/sequel/dataset/sql.rb 1075 def insert_insert_sql(sql) 1076 sql << "INSERT" 1077 end
# File lib/sequel/dataset/sql.rb 1051 def insert_into_sql(sql) 1052 sql << " INTO " 1053 if (f = @opts[:from]) && f.length == 1 1054 identifier_append(sql, unaliased_identifier(f.first)) 1055 else 1056 source_list_append(sql, f) 1057 end 1058 end
# File lib/sequel/dataset/sql.rb 1098 def insert_returning_sql(sql) 1099 if opts.has_key?(:returning) 1100 sql << " RETURNING " 1101 column_list_append(sql, Array(opts[:returning])) 1102 end 1103 end
# File lib/sequel/dataset/sql.rb 1079 def insert_values_sql(sql) 1080 case values = opts[:values] 1081 when Array 1082 if values.empty? 1083 sql << " DEFAULT VALUES" 1084 else 1085 sql << " VALUES " 1086 literal_append(sql, values) 1087 end 1088 when Dataset 1089 sql << ' ' 1090 subselect_sql_append(sql, values) 1091 when LiteralString 1092 sql << ' ' << values 1093 else 1094 raise Error, "Unsupported INSERT values type, should be an Array or Dataset: #{values.inspect}" 1095 end 1096 end
SQL
fragment specifying a JOIN type, converts underscores to spaces and upcases.
# File lib/sequel/dataset/sql.rb 1109 def join_type_sql(join_type) 1110 "#{join_type.to_s.gsub('_', ' ').upcase} JOIN" 1111 end
Append a literalization of the array to SQL
string. Treats as an expression if an array of all two pairs, or as a SQL
array otherwise.
# File lib/sequel/dataset/sql.rb 1115 def literal_array_append(sql, v) 1116 if Sequel.condition_specifier?(v) 1117 literal_expression_append(sql, SQL::BooleanExpression.from_value_pairs(v)) 1118 else 1119 array_sql_append(sql, v) 1120 end 1121 end
SQL
fragment for BigDecimal
# File lib/sequel/dataset/sql.rb 1124 def literal_big_decimal(v) 1125 d = v.to_s("F") 1126 v.nan? || v.infinite? ? "'#{d}'" : d 1127 end
Append literalization of dataset to SQL
string. Does a subselect inside parantheses.
# File lib/sequel/dataset/sql.rb 1135 def literal_dataset_append(sql, v) 1136 sql << 'LATERAL ' if v.opts[:lateral] 1137 sql << '(' 1138 subselect_sql_append(sql, v) 1139 sql << ')' 1140 end
SQL
fragment for Date, using the ISO8601 format.
# File lib/sequel/dataset/sql.rb 1143 def literal_date(v) 1144 if requires_sql_standard_datetimes? 1145 v.strftime("DATE '%Y-%m-%d'") 1146 else 1147 v.strftime("'%Y-%m-%d'") 1148 end 1149 end
SQL
fragment for DateTime
# File lib/sequel/dataset/sql.rb 1152 def literal_datetime(v) 1153 format_timestamp(v) 1154 end
Append literalization of DateTime to SQL
string.
# File lib/sequel/dataset/sql.rb 1157 def literal_datetime_append(sql, v) 1158 sql << literal_datetime(v) 1159 end
Append literalization of SQL::Expression
to SQL
string.
# File lib/sequel/dataset/sql.rb 1162 def literal_expression_append(sql, v) 1163 v.to_s_append(self, sql) 1164 end
SQL
fragment for false
# File lib/sequel/dataset/sql.rb 1167 def literal_false 1168 "'f'" 1169 end
SQL
fragment for Float
# File lib/sequel/dataset/sql.rb 1172 def literal_float(v) 1173 v.to_s 1174 end
SQL
fragment for Integer
# File lib/sequel/dataset/sql.rb 1182 def literal_integer(v) 1183 v.to_s 1184 end
SQL
fragment for nil
# File lib/sequel/dataset/sql.rb 1187 def literal_nil 1188 "NULL" 1189 end
Append a literalization of the object to the given SQL
string. Calls sql_literal_append
if object responds to it, otherwise calls sql_literal
if object responds to it, otherwise raises an error. If a database specific type is allowed, this should be overriden in a subclass.
# File lib/sequel/dataset/sql.rb 1195 def literal_other_append(sql, v) 1196 # We can't be sure if v will always literalize to the same SQL, so 1197 # don't cache SQL for a dataset that uses this. 1198 disable_sql_caching! 1199 1200 if v.respond_to?(:sql_literal_append) 1201 v.sql_literal_append(self, sql) 1202 elsif v.respond_to?(:sql_literal) 1203 sql << v.sql_literal(self) 1204 else 1205 raise Error, "can't express #{v.inspect} as a SQL literal" 1206 end 1207 end
SQL
fragment for Sequel::SQLTime
, containing just the time part
# File lib/sequel/dataset/sql.rb 1210 def literal_sqltime(v) 1211 v.strftime("'%H:%M:%S#{format_timestamp_usec(v.usec, sqltime_precision) if supports_timestamp_usecs?}'") 1212 end
Append literalization of Sequel::SQLTime
to SQL
string.
# File lib/sequel/dataset/sql.rb 1215 def literal_sqltime_append(sql, v) 1216 sql << literal_sqltime(v) 1217 end
Append literalization of string to SQL
string.
# File lib/sequel/dataset/sql.rb 1220 def literal_string_append(sql, v) 1221 sql << "'" << v.gsub("'", "''") << "'" 1222 end
Append literalization of symbol to SQL
string.
# File lib/sequel/dataset/sql.rb 1225 def literal_symbol_append(sql, v) 1226 c_table, column, c_alias = split_symbol(v) 1227 if c_table 1228 quote_identifier_append(sql, c_table) 1229 sql << '.' 1230 end 1231 quote_identifier_append(sql, column) 1232 as_sql_append(sql, c_alias) if c_alias 1233 end
SQL
fragment for Time
# File lib/sequel/dataset/sql.rb 1236 def literal_time(v) 1237 format_timestamp(v) 1238 end
Append literalization of Time to SQL
string.
# File lib/sequel/dataset/sql.rb 1241 def literal_time_append(sql, v) 1242 sql << literal_time(v) 1243 end
SQL
fragment for true
# File lib/sequel/dataset/sql.rb 1246 def literal_true 1247 "'t'" 1248 end
What strategy to use for import/multi_insert. While SQL-92 defaults to allowing multiple rows in a VALUES clause, there are enough databases that don't allow that that it can't be the default. Use separate queries by default, which works everywhere.
# File lib/sequel/dataset/sql.rb 1254 def multi_insert_sql_strategy 1255 :separate 1256 end
Get the native function name given the emulated function name.
# File lib/sequel/dataset/sql.rb 1260 def native_function_name(emulated_function) 1261 emulated_function 1262 end
Returns a qualified column name (including a table name) if the column name isn't already qualified.
# File lib/sequel/dataset/sql.rb 1266 def qualified_column_name(column, table) 1267 if column.is_a?(Symbol) 1268 c_table, column, _ = split_symbol(column) 1269 unless c_table 1270 case table 1271 when Symbol 1272 schema, table, t_alias = split_symbol(table) 1273 t_alias ||= Sequel::SQL::QualifiedIdentifier.new(schema, table) if schema 1274 when Sequel::SQL::AliasedExpression 1275 t_alias = table.alias 1276 end 1277 c_table = t_alias || table 1278 end 1279 ::Sequel::SQL::QualifiedIdentifier.new(c_table, column) 1280 else 1281 column 1282 end 1283 end
Qualify the given expression to the given table.
# File lib/sequel/dataset/sql.rb 1286 def qualified_expression(e, table) 1287 Qualifier.new(table).transform(e) 1288 end
# File lib/sequel/dataset/sql.rb 1290 def select_columns_sql(sql) 1291 sql << ' ' 1292 column_list_append(sql, @opts[:select]) 1293 end
Modify the sql to add a dataset to the via an EXCEPT, INTERSECT, or UNION clause. This uses a subselect for the compound datasets used, because using parantheses doesn't work on all databases.
# File lib/sequel/dataset/sql.rb 1309 def select_compounds_sql(sql) 1310 return unless c = @opts[:compounds] 1311 c.each do |type, dataset, all| 1312 sql << ' ' << type.to_s.upcase 1313 sql << ' ALL' if all 1314 sql << ' ' 1315 compound_dataset_sql_append(sql, dataset) 1316 end 1317 end
# File lib/sequel/dataset/sql.rb 1295 def select_distinct_sql(sql) 1296 if distinct = @opts[:distinct] 1297 sql << " DISTINCT" 1298 unless distinct.empty? 1299 sql << " ON (" 1300 expression_list_append(sql, distinct) 1301 sql << ')' 1302 end 1303 end 1304 end
# File lib/sequel/dataset/sql.rb 1319 def select_from_sql(sql) 1320 if f = @opts[:from] 1321 sql << ' FROM ' 1322 source_list_append(sql, f) 1323 elsif f = empty_from_sql 1324 sql << f 1325 end 1326 end
# File lib/sequel/dataset/sql.rb 1328 def select_group_sql(sql) 1329 if group = @opts[:group] 1330 sql << " GROUP BY " 1331 if go = @opts[:group_options] 1332 if go == :"grouping sets" 1333 sql << go.to_s.upcase << '(' 1334 grouping_element_list_append(sql, group) 1335 sql << ')' 1336 elsif uses_with_rollup? 1337 expression_list_append(sql, group) 1338 sql << " WITH " << go.to_s.upcase 1339 else 1340 sql << go.to_s.upcase << '(' 1341 expression_list_append(sql, group) 1342 sql << ')' 1343 end 1344 else 1345 expression_list_append(sql, group) 1346 end 1347 end 1348 end
# File lib/sequel/dataset/sql.rb 1350 def select_having_sql(sql) 1351 if having = @opts[:having] 1352 sql << " HAVING " 1353 literal_append(sql, having) 1354 end 1355 end
# File lib/sequel/dataset/sql.rb 1357 def select_join_sql(sql) 1358 if js = @opts[:join] 1359 js.each{|j| literal_append(sql, j)} 1360 end 1361 end
# File lib/sequel/dataset/sql.rb 1363 def select_limit_sql(sql) 1364 if l = @opts[:limit] 1365 sql << " LIMIT " 1366 literal_append(sql, l) 1367 if o = @opts[:offset] 1368 sql << " OFFSET " 1369 literal_append(sql, o) 1370 end 1371 elsif @opts[:offset] 1372 select_only_offset_sql(sql) 1373 end 1374 end
# File lib/sequel/dataset/sql.rb 1376 def select_lock_sql(sql) 1377 case l = @opts[:lock] 1378 when :update 1379 sql << ' FOR UPDATE' 1380 when String 1381 sql << ' ' << l 1382 end 1383 end
Used only if there is an offset and no limit, making it easier to override in the adapter, as many databases do not support just a plain offset with no limit.
# File lib/sequel/dataset/sql.rb 1388 def select_only_offset_sql(sql) 1389 sql << " OFFSET " 1390 literal_append(sql, @opts[:offset]) 1391 end
# File lib/sequel/dataset/sql.rb 1393 def select_order_sql(sql) 1394 if o = @opts[:order] 1395 sql << " ORDER BY " 1396 expression_list_append(sql, o) 1397 end 1398 end
# File lib/sequel/dataset/sql.rb 1402 def select_select_sql(sql) 1403 sql << 'SELECT' 1404 end
# File lib/sequel/dataset/sql.rb 1406 def select_where_sql(sql) 1407 if w = @opts[:where] 1408 sql << " WHERE " 1409 literal_append(sql, w) 1410 end 1411 end
# File lib/sequel/dataset/sql.rb 1415 def select_with_sql(sql) 1416 return unless supports_cte? 1417 ws = opts[:with] 1418 return if !ws || ws.empty? 1419 sql << select_with_sql_base 1420 c = false 1421 comma = ', ' 1422 ws.each do |w| 1423 sql << comma if c 1424 quote_identifier_append(sql, w[:name]) 1425 if args = w[:args] 1426 sql << '(' 1427 identifier_list_append(sql, args) 1428 sql << ')' 1429 end 1430 sql << ' AS ' 1431 literal_dataset_append(sql, w[:dataset]) 1432 c ||= true 1433 end 1434 sql << ' ' 1435 end
# File lib/sequel/dataset/sql.rb 1440 def select_with_sql_base 1441 "WITH " 1442 end
Whether the symbol cache should be skipped when literalizing the dataset
# File lib/sequel/dataset/sql.rb 1445 def skip_symbol_cache? 1446 @opts[:skip_symbol_cache] 1447 end
Append literalization of array of sources/tables to SQL
string, raising an Error
if there are no sources.
# File lib/sequel/dataset/sql.rb 1451 def source_list_append(sql, sources) 1452 raise(Error, 'No source specified for query') if sources.nil? || sources == [] 1453 identifier_list_append(sql, sources) 1454 end
Delegate to Sequel.split_symbol
.
# File lib/sequel/dataset/sql.rb 1457 def split_symbol(sym) 1458 Sequel.split_symbol(sym) 1459 end
The string that is appended to to create the SQL
query, the empty string by default.
# File lib/sequel/dataset/sql.rb 1463 def sql_string_origin 1464 String.new 1465 end
The precision to use for SQLTime
instances (time column values without dates). Defaults to timestamp_precision.
# File lib/sequel/dataset/sql.rb 1469 def sqltime_precision 1470 timestamp_precision 1471 end
SQL
to use if this dataset uses static SQL
. Since static SQL
can be a PlaceholderLiteralString in addition to a String
, we literalize nonstrings. If there is an append_sql for this dataset, append to that SQL
instead of returning the value.
# File lib/sequel/dataset/sql.rb 1477 def static_sql(sql) 1478 if append_sql = @opts[:append_sql] 1479 if sql.is_a?(String) 1480 append_sql << sql 1481 else 1482 literal_append(append_sql, sql) 1483 end 1484 else 1485 if sql.is_a?(String) 1486 sql 1487 else 1488 literal(sql) 1489 end 1490 end 1491 end
Append literalization of the subselect to SQL
string.
# File lib/sequel/dataset/sql.rb 1494 def subselect_sql_append(sql, ds) 1495 ds.clone(:append_sql=>sql).sql 1496 end
The number of decimal digits of precision to use in timestamps.
# File lib/sequel/dataset/sql.rb 1499 def timestamp_precision 1500 supports_timestamp_usecs? ? 6 : 0 1501 end
# File lib/sequel/dataset/sql.rb 1509 def update_set_sql(sql) 1510 sql << ' SET ' 1511 values = @opts[:values] 1512 if values.is_a?(Hash) 1513 update_sql_values_hash(sql, values) 1514 else 1515 sql << values 1516 end 1517 end
# File lib/sequel/dataset/sql.rb 1519 def update_sql_values_hash(sql, values) 1520 c = false 1521 eq = ' = ' 1522 values.each do |k, v| 1523 sql << ', ' if c 1524 if k.is_a?(String) && !k.is_a?(LiteralString) 1525 quote_identifier_append(sql, k) 1526 else 1527 literal_append(sql, k) 1528 end 1529 sql << eq 1530 literal_append(sql, v) 1531 c ||= true 1532 end 1533 end
# File lib/sequel/dataset/sql.rb 1503 def update_table_sql(sql) 1504 sql << ' ' 1505 source_list_append(sql, @opts[:from]) 1506 select_join_sql(sql) if supports_modifying_joins? 1507 end
# File lib/sequel/dataset/sql.rb 1535 def update_update_sql(sql) 1536 sql << 'UPDATE' 1537 end