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 570 def cast_sql_append(sql, expr, type) 571 if type == Time or type == DateTime 572 sql << "datetime(" 573 literal_append(sql, expr) 574 sql << ')' 575 elsif type == Date 576 sql << "date(" 577 literal_append(sql, expr) 578 sql << ')' 579 else 580 super 581 end 582 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 586 def complex_expression_sql_append(sql, op, args) 587 case op 588 when :"NOT LIKE", :"NOT ILIKE" 589 sql << 'NOT ' 590 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 591 when :^ 592 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 593 when :** 594 unless (exp = args[1]).is_a?(Integer) 595 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 596 end 597 case exp 598 when 0 599 sql << '1' 600 else 601 sql << '(' 602 arg = args[0] 603 if exp < 0 604 invert = true 605 exp = exp.abs 606 sql << '(1.0 / (' 607 end 608 (exp - 1).times do 609 literal_append(sql, arg) 610 sql << " * " 611 end 612 literal_append(sql, arg) 613 sql << ')' 614 if invert 615 sql << "))" 616 end 617 end 618 when :extract 619 part = args[0] 620 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 621 sql << "CAST(strftime(" << format << ', ' 622 literal_append(sql, args[1]) 623 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 624 else 625 super 626 end 627 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 631 def constant_sql_append(sql, constant) 632 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 633 sql << c 634 else 635 super 636 end 637 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 642 def delete(&block) 643 @opts[:where] ? super : where(1=>1).delete(&block) 644 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 649 def explain(opts=nil) 650 # Load the PrettyTable class, needed for explain output 651 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 652 653 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 654 rows = ds.all 655 Sequel::PrettyTable.string(rows, ds.columns) 656 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 659 def having(*cond) 660 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group] 661 super 662 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 737 def insert_conflict(opts = :ignore) 738 case opts 739 when Symbol, String 740 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 741 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 742 end 743 clone(:insert_conflict => opts) 744 when Hash 745 clone(:insert_on_conflict => opts) 746 else 747 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 748 end 749 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 756 def insert_ignore 757 insert_conflict(:ignore) 758 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 666 def insert_select(*values) 667 return unless supports_insert_select? 668 # Handle case where query does not return a row 669 server?(:default).with_sql_first(insert_select_sql(*values)) || false 670 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 674 def insert_select_sql(*values) 675 ds = opts[:returning] ? self : returning 676 ds.insert_sql(*values) 677 end
SQLite
uses the nonstandard ‘ (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 680 def quoted_identifier_append(sql, c) 681 sql << '`' << c.to_s.gsub('`', '``') << '`' 682 end
Automatically add aliases to RETURNING values to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 761 def returning(*values) 762 return super if values.empty? 763 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 764 clone(:returning=>_returning_values(values).freeze) 765 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 688 def select(*cols) 689 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)}) 690 super(*cols.map{|c| alias_qualified_column(c)}) 691 else 692 super 693 end 694 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 768 def supports_cte?(type=:select) 769 db.sqlite_version >= 30803 770 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 773 def supports_cte_in_subqueries? 774 supports_cte? 775 end
SQLite
does not support deleting from a joined dataset
# File lib/sequel/adapters/shared/sqlite.rb 783 def supports_deleting_joins? 784 false 785 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 778 def supports_derived_column_lists? 779 false 780 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 788 def supports_intersect_except_all? 789 false 790 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 793 def supports_is_true? 794 false 795 end
SQLite
3.33.0 supports modifying joined datasets
# File lib/sequel/adapters/shared/sqlite.rb 798 def supports_modifying_joins? 799 db.sqlite_version >= 33300 800 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 803 def supports_multiple_column_in? 804 false 805 end
SQLite
3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
# File lib/sequel/adapters/shared/sqlite.rb 808 def supports_returning?(_) 809 db.sqlite_version >= 33500 810 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 815 def supports_timestamp_timezones? 816 db.use_timestamp_timezones? 817 end
SQLite
cannot use WHERE ‘t’.
# File lib/sequel/adapters/shared/sqlite.rb 820 def supports_where_true? 821 false 822 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 825 def supports_window_clause? 826 db.sqlite_version >= 32800 827 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 833 def supports_window_functions? 834 db.sqlite_version >= 32600 835 end
Private Instance Methods
Add aliases to symbols and identifiers to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 845 def _returning_values(values) 846 values.map do |v| 847 case v 848 when Symbol 849 _, c, a = split_symbol(v) 850 a ? v : Sequel.as(v, c) 851 when SQL::Identifier, SQL::QualifiedIdentifier 852 Sequel.as(v, unqualified_column_for(v)) 853 else 854 v 855 end 856 end 857 end
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 1007 def _truncate_sql(table) 1008 "DELETE FROM #{table}" 1009 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 868 def alias_qualified_column(col) 869 case col 870 when Symbol 871 t, c, a = split_symbol(col) 872 if t && !a 873 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 874 else 875 col 876 end 877 when SQL::QualifiedIdentifier 878 SQL::AliasedExpression.new(col, col.column) 879 else 880 col 881 end 882 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 860 def as_sql_append(sql, aliaz, column_aliases=nil) 861 raise Error, "sqlite does not support derived column lists" if column_aliases 862 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 863 sql << ' AS ' 864 literal_append(sql, aliaz.to_s) 865 end
Raise an InvalidOperation exception if insert is not allowed for this dataset.
# File lib/sequel/adapters/shared/sqlite.rb 885 def check_insert_allowed! 886 raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group] 887 raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset? 888 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 892 def default_import_slice 893 500 894 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 897 def identifier_list(columns) 898 columns.map{|i| quote_identifier(i)}.join(', ') 899 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 902 def insert_conflict_sql(sql) 903 if resolution = @opts[:insert_conflict] 904 sql << " OR " << resolution.to_s.upcase 905 end 906 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 909 def insert_on_conflict_sql(sql) 910 if opts = @opts[:insert_on_conflict] 911 sql << " ON CONFLICT" 912 913 if target = opts[:constraint] 914 sql << " ON CONSTRAINT " 915 identifier_append(sql, target) 916 elsif target = opts[:target] 917 sql << ' ' 918 identifier_append(sql, Array(target)) 919 if conflict_where = opts[:conflict_where] 920 sql << " WHERE " 921 literal_append(sql, conflict_where) 922 end 923 end 924 925 if values = opts[:update] 926 sql << " DO UPDATE SET " 927 update_sql_values_hash(sql, values) 928 if update_where = opts[:update_where] 929 sql << " WHERE " 930 literal_append(sql, update_where) 931 end 932 else 933 sql << " DO NOTHING" 934 end 935 end 936 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 939 def literal_blob_append(sql, v) 940 sql << "X'" << v.unpack("H*").first << "'" 941 end
Respect the database integer_booleans setting, using 0 or ‘f’.
# File lib/sequel/adapters/shared/sqlite.rb 944 def literal_false 945 @db.integer_booleans ? '0' : "'f'" 946 end
Respect the database integer_booleans setting, using 1 or ‘t’.
# File lib/sequel/adapters/shared/sqlite.rb 949 def literal_true 950 @db.integer_booleans ? '1' : "'t'" 951 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 955 def multi_insert_sql_strategy 956 db.sqlite_version >= 30711 ? :values : :union 957 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 960 def native_function_name(emulated_function) 961 if emulated_function == :char_length 962 'length' 963 else 964 super 965 end 966 end
SQLite
supports NULLS FIRST/LAST natively in 3.30+.
# File lib/sequel/adapters/shared/sqlite.rb 969 def requires_emulating_nulls_first? 970 db.sqlite_version < 33000 971 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 976 def select_lock_sql(sql) 977 super unless @opts[:lock] == :update 978 end
# File lib/sequel/adapters/shared/sqlite.rb 980 def select_only_offset_sql(sql) 981 sql << " LIMIT -1 OFFSET " 982 literal_append(sql, @opts[:offset]) 983 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 986 def select_values_sql(sql) 987 sql << "VALUES " 988 expression_list_append(sql, opts[:values]) 989 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 992 def supports_cte_in_compounds? 993 false 994 end
SQLite
3.30 supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/sqlite.rb 997 def supports_filtered_aggregates? 998 db.sqlite_version >= 33000 999 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 1002 def supports_quoted_function_names? 1003 true 1004 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/sqlite.rb 1012 def update_from_sql(sql) 1013 if(from = @opts[:from][1..-1]).empty? 1014 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1015 else 1016 sql << ' FROM ' 1017 source_list_append(sql, from) 1018 select_join_sql(sql) 1019 end 1020 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/sqlite.rb 1023 def update_table_sql(sql) 1024 sql << ' ' 1025 source_list_append(sql, @opts[:from][0..0]) 1026 end