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 519 def cast_sql_append(sql, expr, type) 520 if type == Time or type == DateTime 521 sql << "datetime(" 522 literal_append(sql, expr) 523 sql << ')' 524 elsif type == Date 525 sql << "date(" 526 literal_append(sql, expr) 527 sql << ')' 528 else 529 super 530 end 531 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 535 def complex_expression_sql_append(sql, op, args) 536 case op 537 when :"NOT LIKE", :"NOT ILIKE" 538 sql << 'NOT ' 539 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 540 when :^ 541 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 542 when :** 543 unless (exp = args[1]).is_a?(Integer) 544 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 545 end 546 case exp 547 when 0 548 sql << '1' 549 else 550 sql << '(' 551 arg = args[0] 552 if exp < 0 553 invert = true 554 exp = exp.abs 555 sql << '(1.0 / (' 556 end 557 (exp - 1).times do 558 literal_append(sql, arg) 559 sql << " * " 560 end 561 literal_append(sql, arg) 562 sql << ')' 563 if invert 564 sql << "))" 565 end 566 end 567 when :extract 568 part = args[0] 569 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 570 sql << "CAST(strftime(" << format << ', ' 571 literal_append(sql, args[1]) 572 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 573 else 574 super 575 end 576 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 580 def constant_sql_append(sql, constant) 581 if c = CONSTANT_MAP[constant] 582 sql << c 583 else 584 super 585 end 586 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 591 def delete 592 @opts[:where] ? super : where(1=>1).delete 593 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 598 def explain(opts=nil) 599 # Load the PrettyTable class, needed for explain output 600 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 601 602 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 603 rows = ds.all 604 Sequel::PrettyTable.string(rows, ds.columns) 605 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 608 def having(*cond) 609 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group] 610 super 611 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 671 def insert_conflict(opts = :ignore) 672 case opts 673 when Symbol, String 674 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 675 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 676 end 677 clone(:insert_conflict => opts) 678 when Hash 679 clone(:insert_on_conflict => opts) 680 else 681 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 682 end 683 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 690 def insert_ignore 691 insert_conflict(:ignore) 692 end
SQLite
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 614 def quoted_identifier_append(sql, c) 615 sql << '`' << c.to_s.gsub('`', '``') << '`' 616 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 622 def select(*cols) 623 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)}) 624 super(*cols.map{|c| alias_qualified_column(c)}) 625 else 626 super 627 end 628 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 695 def supports_cte?(type=:select) 696 db.sqlite_version >= 30803 697 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 700 def supports_cte_in_subqueries? 701 supports_cte? 702 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 705 def supports_derived_column_lists? 706 false 707 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 710 def supports_intersect_except_all? 711 false 712 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 715 def supports_is_true? 716 false 717 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 720 def supports_multiple_column_in? 721 false 722 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 727 def supports_timestamp_timezones? 728 db.use_timestamp_timezones? 729 end
SQLite
cannot use WHERE 't'.
# File lib/sequel/adapters/shared/sqlite.rb 732 def supports_where_true? 733 false 734 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 737 def supports_window_clause? 738 db.sqlite_version >= 32800 739 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 745 def supports_window_functions? 746 db.sqlite_version >= 32600 747 end
Private Instance Methods
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 892 def _truncate_sql(table) 893 "DELETE FROM #{table}" 894 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 765 def alias_qualified_column(col) 766 case col 767 when Symbol 768 t, c, a = split_symbol(col) 769 if t && !a 770 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 771 else 772 col 773 end 774 when SQL::QualifiedIdentifier 775 SQL::AliasedExpression.new(col, col.column) 776 else 777 col 778 end 779 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 757 def as_sql_append(sql, aliaz, column_aliases=nil) 758 raise Error, "sqlite does not support derived column lists" if column_aliases 759 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 760 sql << ' AS ' 761 literal_append(sql, aliaz.to_s) 762 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 782 def default_import_slice 783 500 784 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 787 def identifier_list(columns) 788 columns.map{|i| quote_identifier(i)}.join(', ') 789 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 792 def insert_conflict_sql(sql) 793 if resolution = @opts[:insert_conflict] 794 sql << " OR " << resolution.to_s.upcase 795 end 796 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 799 def insert_on_conflict_sql(sql) 800 if opts = @opts[:insert_on_conflict] 801 sql << " ON CONFLICT" 802 803 if target = opts[:constraint] 804 sql << " ON CONSTRAINT " 805 identifier_append(sql, target) 806 elsif target = opts[:target] 807 sql << ' ' 808 identifier_append(sql, Array(target)) 809 if conflict_where = opts[:conflict_where] 810 sql << " WHERE " 811 literal_append(sql, conflict_where) 812 end 813 end 814 815 if values = opts[:update] 816 sql << " DO UPDATE SET " 817 update_sql_values_hash(sql, values) 818 if update_where = opts[:update_where] 819 sql << " WHERE " 820 literal_append(sql, update_where) 821 end 822 else 823 sql << " DO NOTHING" 824 end 825 end 826 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 829 def literal_blob_append(sql, v) 830 sql << "X'" << v.unpack("H*").first << "'" 831 end
Respect the database integer_booleans setting, using 0 or 'f'.
# File lib/sequel/adapters/shared/sqlite.rb 834 def literal_false 835 @db.integer_booleans ? '0' : "'f'" 836 end
Respect the database integer_booleans setting, using 1 or 't'.
# File lib/sequel/adapters/shared/sqlite.rb 839 def literal_true 840 @db.integer_booleans ? '1' : "'t'" 841 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 845 def multi_insert_sql_strategy 846 db.sqlite_version >= 30711 ? :values : :union 847 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 850 def native_function_name(emulated_function) 851 if emulated_function == :char_length 852 'length' 853 else 854 super 855 end 856 end
SQLite
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/sqlite.rb 859 def requires_emulating_nulls_first? 860 true 861 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 866 def select_lock_sql(sql) 867 super unless @opts[:lock] == :update 868 end
# File lib/sequel/adapters/shared/sqlite.rb 870 def select_only_offset_sql(sql) 871 sql << " LIMIT -1 OFFSET " 872 literal_append(sql, @opts[:offset]) 873 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 876 def select_values_sql(sql) 877 sql << "VALUES " 878 expression_list_append(sql, opts[:values]) 879 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 882 def supports_cte_in_compounds? 883 false 884 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 887 def supports_quoted_function_names? 888 true 889 end