Methods
Public Instance
- cast_sql_append
- complex_expression_sql_append
- constant_sql_append
- delete
- empty?
- explain
- having
- insert_conflict
- insert_ignore
- insert_select
- insert_select_sql
- quoted_identifier_append
- returning
- select
- supports_cte?
- supports_cte_in_subqueries?
- supports_deleting_joins?
- supports_derived_column_lists?
- supports_intersect_except_all?
- supports_is_true?
- supports_modifying_joins?
- supports_multiple_column_in?
- supports_returning?
- supports_timestamp_timezones?
- supports_where_true?
- supports_window_clause?
- supports_window_function_frame_option?
- supports_window_functions?
Included modules
Constants
CONSTANT_MAP | = | {:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".freeze}.freeze | ||
EXTRACT_MAP | = | {:year=>"'%Y'", :month=>"'%m'", :day=>"'%d'", :hour=>"'%H'", :minute=>"'%M'", :second=>"'%f'"}.freeze | ||
INSERT_CONFLICT_RESOLUTIONS | = | %w'ROLLBACK ABORT FAIL IGNORE REPLACE'.each(&:freeze).freeze |
The allowed values for |
Public Instance methods
# File lib/sequel/adapters/shared/sqlite.rb 596 def cast_sql_append(sql, expr, type) 597 if type == Time or type == DateTime 598 sql << "datetime(" 599 literal_append(sql, expr) 600 sql << ')' 601 elsif type == Date 602 sql << "date(" 603 literal_append(sql, expr) 604 sql << ')' 605 else 606 super 607 end 608 end
SQLite
doesn’t support a NOT LIKE b, you need to use NOT (a LIKE b). It doesn’t support xor, power, or the extract function natively, so those have to be emulated.
# File lib/sequel/adapters/shared/sqlite.rb 612 def complex_expression_sql_append(sql, op, args) 613 case op 614 when :"NOT LIKE", :"NOT ILIKE" 615 sql << 'NOT ' 616 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 617 when :^ 618 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 619 when :** 620 unless (exp = args[1]).is_a?(Integer) 621 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 622 end 623 case exp 624 when 0 625 sql << '1' 626 else 627 sql << '(' 628 arg = args[0] 629 if exp < 0 630 invert = true 631 exp = exp.abs 632 sql << '(1.0 / (' 633 end 634 (exp - 1).times do 635 literal_append(sql, arg) 636 sql << " * " 637 end 638 literal_append(sql, arg) 639 sql << ')' 640 if invert 641 sql << "))" 642 end 643 end 644 when :extract 645 part = args[0] 646 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 647 sql << "CAST(strftime(" << format << ', ' 648 literal_append(sql, args[1]) 649 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 650 else 651 super 652 end 653 end
SQLite
has CURRENT_TIMESTAMP and related constants in UTC instead of in localtime, so convert those constants to local time.
# File lib/sequel/adapters/shared/sqlite.rb 657 def constant_sql_append(sql, constant) 658 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 659 sql << c 660 else 661 super 662 end 663 end
SQLite
performs a TRUNCATE style DELETE if no filter is specified. Since we want to always return the count of records, add a condition that is always true and then delete.
# File lib/sequel/adapters/shared/sqlite.rb 668 def delete(&block) 669 @opts[:where] ? super : where(1=>1).delete(&block) 670 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/sqlite.rb 673 def empty? 674 return false if @opts[:values] 675 super 676 end
Return an array of strings specifying a query explanation for a SELECT of the current dataset. Currently, the options are ignored, but it accepts options to be compatible with other adapters.
# File lib/sequel/adapters/shared/sqlite.rb 681 def explain(opts=nil) 682 # Load the PrettyTable class, needed for explain output 683 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 684 685 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 686 rows = ds.all 687 Sequel::PrettyTable.string(rows, ds.columns) 688 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 691 def having(*cond) 692 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900 693 super 694 end
Handle uniqueness violations when inserting, by using a specified resolution algorithm. With no options, uses INSERT OR REPLACE. SQLite
supports the following conflict resolution algoriths: ROLLBACK, ABORT, FAIL, IGNORE and REPLACE.
On SQLite
3.24.0+, you can pass a hash to use an ON CONFLICT clause. With out :update option, uses ON CONFLICT DO NOTHING. Options:
:conflict_where |
The index filter, when using a partial index to determine uniqueness. |
:target |
The column name or expression to handle uniqueness violations on. |
:update |
A hash of columns and values to set. Uses ON CONFLICT DO UPDATE. |
:update_where |
A WHERE condition to use for the update. |
Examples:
DB[:table].insert_conflict.insert(a: 1, b: 2) # INSERT OR IGNORE INTO TABLE (a, b) VALUES (1, 2) DB[:table].insert_conflict(:replace).insert(a: 1, b: 2) # INSERT OR REPLACE INTO TABLE (a, b) VALUES (1, 2) DB[:table].insert_conflict({}).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT DO NOTHING DB[:table].insert_conflict(target: :a).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO NOTHING DB[:table].insert_conflict(target: :a, conflict_where: {c: true}).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) WHERE (c IS TRUE) DO NOTHING DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO UPDATE SET b = excluded.b DB[:table].insert_conflict(target: :a, update: {b: Sequel[:excluded][:b]}, update_where: {Sequel[:table][:status_id] => 1}).insert(a: 1, b: 2) # INSERT INTO TABLE (a, b) VALUES (1, 2) # ON CONFLICT (a) DO UPDATE SET b = excluded.b WHERE (table.status_id = 1)
# File lib/sequel/adapters/shared/sqlite.rb 769 def insert_conflict(opts = :ignore) 770 case opts 771 when Symbol, String 772 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 773 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 774 end 775 clone(:insert_conflict => opts) 776 when Hash 777 clone(:insert_on_conflict => opts) 778 else 779 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 780 end 781 end
Ignore uniqueness/exclusion violations when inserting, using INSERT OR IGNORE. Exists mostly for compatibility to MySQL’s insert_ignore. Example:
DB[:table].insert_ignore.insert(a: 1, b: 2) # INSERT OR IGNORE INTO TABLE (a, b) VALUES (1, 2)
# File lib/sequel/adapters/shared/sqlite.rb 788 def insert_ignore 789 insert_conflict(:ignore) 790 end
Support insert select for associations, so that the model code can use returning instead of a separate query.
# File lib/sequel/adapters/shared/sqlite.rb 698 def insert_select(*values) 699 return unless supports_insert_select? 700 # Handle case where query does not return a row 701 server?(:default).with_sql_first(insert_select_sql(*values)) || false 702 end
The SQL to use for an insert_select
, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
# File lib/sequel/adapters/shared/sqlite.rb 706 def insert_select_sql(*values) 707 ds = opts[:returning] ? self : returning 708 ds.insert_sql(*values) 709 end
SQLite
uses the nonstandard ‘ (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 712 def quoted_identifier_append(sql, c) 713 sql << '`' << c.to_s.gsub('`', '``') << '`' 714 end
Automatically add aliases to RETURNING values to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 793 def returning(*values) 794 return super if values.empty? 795 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 796 clone(:returning=>_returning_values(values).freeze) 797 end
When a qualified column is selected on SQLite
and the qualifier is a subselect, the column name used is the full qualified name (including the qualifier) instead of just the column name. To get correct column names, you must use an alias.
# File lib/sequel/adapters/shared/sqlite.rb 720 def select(*cols) 721 if ((f = @opts[:from]) && f.any?{|t| t.is_a?(Dataset) || (t.is_a?(SQL::AliasedExpression) && t.expression.is_a?(Dataset))}) || ((j = @opts[:join]) && j.any?{|t| t.table.is_a?(Dataset)}) 722 super(*cols.map{|c| alias_qualified_column(c)}) 723 else 724 super 725 end 726 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 800 def supports_cte?(type=:select) 801 db.sqlite_version >= 30803 802 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 805 def supports_cte_in_subqueries? 806 supports_cte? 807 end
SQLite
does not support deleting from a joined dataset
# File lib/sequel/adapters/shared/sqlite.rb 815 def supports_deleting_joins? 816 false 817 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 810 def supports_derived_column_lists? 811 false 812 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 820 def supports_intersect_except_all? 821 false 822 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 825 def supports_is_true? 826 false 827 end
SQLite
3.33.0 supports modifying joined datasets
# File lib/sequel/adapters/shared/sqlite.rb 830 def supports_modifying_joins? 831 db.sqlite_version >= 33300 832 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 835 def supports_multiple_column_in? 836 false 837 end
SQLite
3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
# File lib/sequel/adapters/shared/sqlite.rb 840 def supports_returning?(_) 841 db.sqlite_version >= 33500 842 end
SQLite
supports timezones in literal timestamps, since it stores them as text. But using timezones in timestamps breaks SQLite
datetime functions, so we allow the user to override the default per database.
# File lib/sequel/adapters/shared/sqlite.rb 847 def supports_timestamp_timezones? 848 db.use_timestamp_timezones? 849 end
SQLite
cannot use WHERE ‘t’.
# File lib/sequel/adapters/shared/sqlite.rb 852 def supports_where_true? 853 false 854 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 857 def supports_window_clause? 858 db.sqlite_version >= 32800 859 end
# File lib/sequel/adapters/shared/sqlite.rb 870 def supports_window_function_frame_option?(option) 871 db.sqlite_version >= 32800 ? true : super 872 end
SQLite
3.25+ supports window functions. However, support is only enabled on SQLite
3.26.0+ because internal Sequel
usage of window functions to implement eager loading of limited associations triggers an SQLite
crash bug in versions 3.25.0-3.25.3.
# File lib/sequel/adapters/shared/sqlite.rb 865 def supports_window_functions? 866 db.sqlite_version >= 32600 867 end