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 533 def cast_sql_append(sql, expr, type) 534 if type == Time or type == DateTime 535 sql << "datetime(" 536 literal_append(sql, expr) 537 sql << ')' 538 elsif type == Date 539 sql << "date(" 540 literal_append(sql, expr) 541 sql << ')' 542 else 543 super 544 end 545 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 549 def complex_expression_sql_append(sql, op, args) 550 case op 551 when :"NOT LIKE", :"NOT ILIKE" 552 sql << 'NOT ' 553 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 554 when :^ 555 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 556 when :** 557 unless (exp = args[1]).is_a?(Integer) 558 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 559 end 560 case exp 561 when 0 562 sql << '1' 563 else 564 sql << '(' 565 arg = args[0] 566 if exp < 0 567 invert = true 568 exp = exp.abs 569 sql << '(1.0 / (' 570 end 571 (exp - 1).times do 572 literal_append(sql, arg) 573 sql << " * " 574 end 575 literal_append(sql, arg) 576 sql << ')' 577 if invert 578 sql << "))" 579 end 580 end 581 when :extract 582 part = args[0] 583 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 584 sql << "CAST(strftime(" << format << ', ' 585 literal_append(sql, args[1]) 586 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 587 else 588 super 589 end 590 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 594 def constant_sql_append(sql, constant) 595 if c = CONSTANT_MAP[constant] 596 sql << c 597 else 598 super 599 end 600 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 605 def delete 606 @opts[:where] ? super : where(1=>1).delete 607 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 612 def explain(opts=nil) 613 # Load the PrettyTable class, needed for explain output 614 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 615 616 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 617 rows = ds.all 618 Sequel::PrettyTable.string(rows, ds.columns) 619 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 622 def having(*cond) 623 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group] 624 super 625 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 685 def insert_conflict(opts = :ignore) 686 case opts 687 when Symbol, String 688 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 689 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 690 end 691 clone(:insert_conflict => opts) 692 when Hash 693 clone(:insert_on_conflict => opts) 694 else 695 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 696 end 697 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 704 def insert_ignore 705 insert_conflict(:ignore) 706 end
SQLite
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 628 def quoted_identifier_append(sql, c) 629 sql << '`' << c.to_s.gsub('`', '``') << '`' 630 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 636 def select(*cols) 637 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)}) 638 super(*cols.map{|c| alias_qualified_column(c)}) 639 else 640 super 641 end 642 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 709 def supports_cte?(type=:select) 710 db.sqlite_version >= 30803 711 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 714 def supports_cte_in_subqueries? 715 supports_cte? 716 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 719 def supports_derived_column_lists? 720 false 721 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 724 def supports_intersect_except_all? 725 false 726 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 729 def supports_is_true? 730 false 731 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 734 def supports_multiple_column_in? 735 false 736 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 741 def supports_timestamp_timezones? 742 db.use_timestamp_timezones? 743 end
SQLite
cannot use WHERE 't'.
# File lib/sequel/adapters/shared/sqlite.rb 746 def supports_where_true? 747 false 748 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 751 def supports_window_clause? 752 db.sqlite_version >= 32800 753 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 759 def supports_window_functions? 760 db.sqlite_version >= 32600 761 end
Private Instance Methods
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 906 def _truncate_sql(table) 907 "DELETE FROM #{table}" 908 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 779 def alias_qualified_column(col) 780 case col 781 when Symbol 782 t, c, a = split_symbol(col) 783 if t && !a 784 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 785 else 786 col 787 end 788 when SQL::QualifiedIdentifier 789 SQL::AliasedExpression.new(col, col.column) 790 else 791 col 792 end 793 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 771 def as_sql_append(sql, aliaz, column_aliases=nil) 772 raise Error, "sqlite does not support derived column lists" if column_aliases 773 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 774 sql << ' AS ' 775 literal_append(sql, aliaz.to_s) 776 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 796 def default_import_slice 797 500 798 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 801 def identifier_list(columns) 802 columns.map{|i| quote_identifier(i)}.join(', ') 803 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 806 def insert_conflict_sql(sql) 807 if resolution = @opts[:insert_conflict] 808 sql << " OR " << resolution.to_s.upcase 809 end 810 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 813 def insert_on_conflict_sql(sql) 814 if opts = @opts[:insert_on_conflict] 815 sql << " ON CONFLICT" 816 817 if target = opts[:constraint] 818 sql << " ON CONSTRAINT " 819 identifier_append(sql, target) 820 elsif target = opts[:target] 821 sql << ' ' 822 identifier_append(sql, Array(target)) 823 if conflict_where = opts[:conflict_where] 824 sql << " WHERE " 825 literal_append(sql, conflict_where) 826 end 827 end 828 829 if values = opts[:update] 830 sql << " DO UPDATE SET " 831 update_sql_values_hash(sql, values) 832 if update_where = opts[:update_where] 833 sql << " WHERE " 834 literal_append(sql, update_where) 835 end 836 else 837 sql << " DO NOTHING" 838 end 839 end 840 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 843 def literal_blob_append(sql, v) 844 sql << "X'" << v.unpack("H*").first << "'" 845 end
Respect the database integer_booleans setting, using 0 or 'f'.
# File lib/sequel/adapters/shared/sqlite.rb 848 def literal_false 849 @db.integer_booleans ? '0' : "'f'" 850 end
Respect the database integer_booleans setting, using 1 or 't'.
# File lib/sequel/adapters/shared/sqlite.rb 853 def literal_true 854 @db.integer_booleans ? '1' : "'t'" 855 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 859 def multi_insert_sql_strategy 860 db.sqlite_version >= 30711 ? :values : :union 861 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 864 def native_function_name(emulated_function) 865 if emulated_function == :char_length 866 'length' 867 else 868 super 869 end 870 end
SQLite
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/sqlite.rb 873 def requires_emulating_nulls_first? 874 true 875 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 880 def select_lock_sql(sql) 881 super unless @opts[:lock] == :update 882 end
# File lib/sequel/adapters/shared/sqlite.rb 884 def select_only_offset_sql(sql) 885 sql << " LIMIT -1 OFFSET " 886 literal_append(sql, @opts[:offset]) 887 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 890 def select_values_sql(sql) 891 sql << "VALUES " 892 expression_list_append(sql, opts[:values]) 893 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 896 def supports_cte_in_compounds? 897 false 898 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 901 def supports_quoted_function_names? 902 true 903 end