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 598 def cast_sql_append(sql, expr, type) 599 if type == Time or type == DateTime 600 sql << "datetime(" 601 literal_append(sql, expr) 602 sql << ')' 603 elsif type == Date 604 sql << "date(" 605 literal_append(sql, expr) 606 sql << ')' 607 else 608 super 609 end 610 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 614 def complex_expression_sql_append(sql, op, args) 615 case op 616 when :"NOT LIKE", :"NOT ILIKE" 617 sql << 'NOT ' 618 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 619 when :^ 620 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 621 when :** 622 unless (exp = args[1]).is_a?(Integer) 623 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 624 end 625 case exp 626 when 0 627 sql << '1' 628 else 629 sql << '(' 630 arg = args[0] 631 if exp < 0 632 invert = true 633 exp = exp.abs 634 sql << '(1.0 / (' 635 end 636 (exp - 1).times do 637 literal_append(sql, arg) 638 sql << " * " 639 end 640 literal_append(sql, arg) 641 sql << ')' 642 if invert 643 sql << "))" 644 end 645 end 646 when :extract 647 part = args[0] 648 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 649 sql << "CAST(strftime(" << format << ', ' 650 literal_append(sql, args[1]) 651 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 652 else 653 super 654 end 655 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 659 def constant_sql_append(sql, constant) 660 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 661 sql << c 662 else 663 super 664 end 665 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 670 def delete(&block) 671 @opts[:where] ? super : where(1=>1).delete(&block) 672 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/sqlite.rb 675 def empty? 676 return false if @opts[:values] 677 super 678 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 683 def explain(opts=nil) 684 # Load the PrettyTable class, needed for explain output 685 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 686 687 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 688 rows = ds.all 689 Sequel::PrettyTable.string(rows, ds.columns) 690 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 693 def having(*cond) 694 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900 695 super 696 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 771 def insert_conflict(opts = :ignore) 772 case opts 773 when Symbol, String 774 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 775 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 776 end 777 clone(:insert_conflict => opts) 778 when Hash 779 clone(:insert_on_conflict => opts) 780 else 781 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 782 end 783 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 790 def insert_ignore 791 insert_conflict(:ignore) 792 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 700 def insert_select(*values) 701 return unless supports_insert_select? 702 # Handle case where query does not return a row 703 server?(:default).with_sql_first(insert_select_sql(*values)) || false 704 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 708 def insert_select_sql(*values) 709 ds = opts[:returning] ? self : returning 710 ds.insert_sql(*values) 711 end
SQLite
uses the nonstandard ‘ (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 714 def quoted_identifier_append(sql, c) 715 sql << '`' << c.to_s.gsub('`', '``') << '`' 716 end
Automatically add aliases to RETURNING values to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 795 def returning(*values) 796 return super if values.empty? 797 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 798 clone(:returning=>_returning_values(values).freeze) 799 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 722 def select(*cols) 723 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)}) 724 super(*cols.map{|c| alias_qualified_column(c)}) 725 else 726 super 727 end 728 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 802 def supports_cte?(type=:select) 803 db.sqlite_version >= 30803 804 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 807 def supports_cte_in_subqueries? 808 supports_cte? 809 end
SQLite
does not support deleting from a joined dataset
# File lib/sequel/adapters/shared/sqlite.rb 817 def supports_deleting_joins? 818 false 819 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 812 def supports_derived_column_lists? 813 false 814 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 822 def supports_intersect_except_all? 823 false 824 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 827 def supports_is_true? 828 false 829 end
SQLite
3.33.0 supports modifying joined datasets
# File lib/sequel/adapters/shared/sqlite.rb 832 def supports_modifying_joins? 833 db.sqlite_version >= 33300 834 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 837 def supports_multiple_column_in? 838 false 839 end
SQLite
3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
# File lib/sequel/adapters/shared/sqlite.rb 842 def supports_returning?(_) 843 db.sqlite_version >= 33500 844 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 849 def supports_timestamp_timezones? 850 db.use_timestamp_timezones? 851 end
SQLite
cannot use WHERE ‘t’.
# File lib/sequel/adapters/shared/sqlite.rb 854 def supports_where_true? 855 false 856 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 859 def supports_window_clause? 860 db.sqlite_version >= 32800 861 end
# File lib/sequel/adapters/shared/sqlite.rb 872 def supports_window_function_frame_option?(option) 873 db.sqlite_version >= 32800 ? true : super 874 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 867 def supports_window_functions? 868 db.sqlite_version >= 32600 869 end