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