module Sequel::SQLite::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- INSERT_CONFLICT_RESOLUTIONS
The allowed values for
insert_conflict
Public Instance Methods
# File lib/sequel/adapters/shared/sqlite.rb 555 def cast_sql_append(sql, expr, type) 556 if type == Time or type == DateTime 557 sql << "datetime(" 558 literal_append(sql, expr) 559 sql << ')' 560 elsif type == Date 561 sql << "date(" 562 literal_append(sql, expr) 563 sql << ')' 564 else 565 super 566 end 567 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 571 def complex_expression_sql_append(sql, op, args) 572 case op 573 when :"NOT LIKE", :"NOT ILIKE" 574 sql << 'NOT ' 575 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 576 when :^ 577 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 578 when :** 579 unless (exp = args[1]).is_a?(Integer) 580 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 581 end 582 case exp 583 when 0 584 sql << '1' 585 else 586 sql << '(' 587 arg = args[0] 588 if exp < 0 589 invert = true 590 exp = exp.abs 591 sql << '(1.0 / (' 592 end 593 (exp - 1).times do 594 literal_append(sql, arg) 595 sql << " * " 596 end 597 literal_append(sql, arg) 598 sql << ')' 599 if invert 600 sql << "))" 601 end 602 end 603 when :extract 604 part = args[0] 605 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 606 sql << "CAST(strftime(" << format << ', ' 607 literal_append(sql, args[1]) 608 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 609 else 610 super 611 end 612 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 616 def constant_sql_append(sql, constant) 617 if c = CONSTANT_MAP[constant] 618 sql << c 619 else 620 super 621 end 622 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 627 def delete 628 @opts[:where] ? super : where(1=>1).delete 629 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 634 def explain(opts=nil) 635 # Load the PrettyTable class, needed for explain output 636 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 637 638 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 639 rows = ds.all 640 Sequel::PrettyTable.string(rows, ds.columns) 641 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 644 def having(*cond) 645 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group] 646 super 647 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 707 def insert_conflict(opts = :ignore) 708 case opts 709 when Symbol, String 710 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 711 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 712 end 713 clone(:insert_conflict => opts) 714 when Hash 715 clone(:insert_on_conflict => opts) 716 else 717 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 718 end 719 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 726 def insert_ignore 727 insert_conflict(:ignore) 728 end
SQLite
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 650 def quoted_identifier_append(sql, c) 651 sql << '`' << c.to_s.gsub('`', '``') << '`' 652 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 658 def select(*cols) 659 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)}) 660 super(*cols.map{|c| alias_qualified_column(c)}) 661 else 662 super 663 end 664 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 731 def supports_cte?(type=:select) 732 db.sqlite_version >= 30803 733 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 736 def supports_cte_in_subqueries? 737 supports_cte? 738 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 741 def supports_derived_column_lists? 742 false 743 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 746 def supports_intersect_except_all? 747 false 748 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 751 def supports_is_true? 752 false 753 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 756 def supports_multiple_column_in? 757 false 758 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 763 def supports_timestamp_timezones? 764 db.use_timestamp_timezones? 765 end
SQLite
cannot use WHERE 't'.
# File lib/sequel/adapters/shared/sqlite.rb 768 def supports_where_true? 769 false 770 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 773 def supports_window_clause? 774 db.sqlite_version >= 32800 775 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 781 def supports_window_functions? 782 db.sqlite_version >= 32600 783 end
Private Instance Methods
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 933 def _truncate_sql(table) 934 "DELETE FROM #{table}" 935 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 801 def alias_qualified_column(col) 802 case col 803 when Symbol 804 t, c, a = split_symbol(col) 805 if t && !a 806 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 807 else 808 col 809 end 810 when SQL::QualifiedIdentifier 811 SQL::AliasedExpression.new(col, col.column) 812 else 813 col 814 end 815 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 793 def as_sql_append(sql, aliaz, column_aliases=nil) 794 raise Error, "sqlite does not support derived column lists" if column_aliases 795 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 796 sql << ' AS ' 797 literal_append(sql, aliaz.to_s) 798 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 818 def default_import_slice 819 500 820 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 823 def identifier_list(columns) 824 columns.map{|i| quote_identifier(i)}.join(', ') 825 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 828 def insert_conflict_sql(sql) 829 if resolution = @opts[:insert_conflict] 830 sql << " OR " << resolution.to_s.upcase 831 end 832 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 835 def insert_on_conflict_sql(sql) 836 if opts = @opts[:insert_on_conflict] 837 sql << " ON CONFLICT" 838 839 if target = opts[:constraint] 840 sql << " ON CONSTRAINT " 841 identifier_append(sql, target) 842 elsif target = opts[:target] 843 sql << ' ' 844 identifier_append(sql, Array(target)) 845 if conflict_where = opts[:conflict_where] 846 sql << " WHERE " 847 literal_append(sql, conflict_where) 848 end 849 end 850 851 if values = opts[:update] 852 sql << " DO UPDATE SET " 853 update_sql_values_hash(sql, values) 854 if update_where = opts[:update_where] 855 sql << " WHERE " 856 literal_append(sql, update_where) 857 end 858 else 859 sql << " DO NOTHING" 860 end 861 end 862 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 865 def literal_blob_append(sql, v) 866 sql << "X'" << v.unpack("H*").first << "'" 867 end
Respect the database integer_booleans setting, using 0 or 'f'.
# File lib/sequel/adapters/shared/sqlite.rb 870 def literal_false 871 @db.integer_booleans ? '0' : "'f'" 872 end
Respect the database integer_booleans setting, using 1 or 't'.
# File lib/sequel/adapters/shared/sqlite.rb 875 def literal_true 876 @db.integer_booleans ? '1' : "'t'" 877 end
SQLite
only supporting multiple rows in the VALUES clause starting in 3.7.11. On older versions, fallback to using a UNION.
# File lib/sequel/adapters/shared/sqlite.rb 881 def multi_insert_sql_strategy 882 db.sqlite_version >= 30711 ? :values : :union 883 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 886 def native_function_name(emulated_function) 887 if emulated_function == :char_length 888 'length' 889 else 890 super 891 end 892 end
SQLite
supports NULLS FIRST/LAST natively in 3.30+.
# File lib/sequel/adapters/shared/sqlite.rb 895 def requires_emulating_nulls_first? 896 db.sqlite_version < 33000 897 end
SQLite
does not support FOR UPDATE, but silently ignore it instead of raising an error for compatibility with other databases.
# File lib/sequel/adapters/shared/sqlite.rb 902 def select_lock_sql(sql) 903 super unless @opts[:lock] == :update 904 end
# File lib/sequel/adapters/shared/sqlite.rb 906 def select_only_offset_sql(sql) 907 sql << " LIMIT -1 OFFSET " 908 literal_append(sql, @opts[:offset]) 909 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 912 def select_values_sql(sql) 913 sql << "VALUES " 914 expression_list_append(sql, opts[:values]) 915 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 918 def supports_cte_in_compounds? 919 false 920 end
SQLite
3.30 supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/sqlite.rb 923 def supports_filtered_aggregates? 924 db.sqlite_version >= 33000 925 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 928 def supports_quoted_function_names? 929 true 930 end