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 596 def cast_sql_append(sql, expr, type) 597 if type == Time or type == DateTime 598 sql << "datetime(" 599 literal_append(sql, expr) 600 sql << ')' 601 elsif type == Date 602 sql << "date(" 603 literal_append(sql, expr) 604 sql << ')' 605 else 606 super 607 end 608 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 612 def complex_expression_sql_append(sql, op, args) 613 case op 614 when :"NOT LIKE", :"NOT ILIKE" 615 sql << 'NOT ' 616 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 617 when :^ 618 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 619 when :** 620 unless (exp = args[1]).is_a?(Integer) 621 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 622 end 623 case exp 624 when 0 625 sql << '1' 626 else 627 sql << '(' 628 arg = args[0] 629 if exp < 0 630 invert = true 631 exp = exp.abs 632 sql << '(1.0 / (' 633 end 634 (exp - 1).times do 635 literal_append(sql, arg) 636 sql << " * " 637 end 638 literal_append(sql, arg) 639 sql << ')' 640 if invert 641 sql << "))" 642 end 643 end 644 when :extract 645 part = args[0] 646 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 647 sql << "CAST(strftime(" << format << ', ' 648 literal_append(sql, args[1]) 649 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 650 else 651 super 652 end 653 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 657 def constant_sql_append(sql, constant) 658 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 659 sql << c 660 else 661 super 662 end 663 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 668 def delete(&block) 669 @opts[:where] ? super : where(1=>1).delete(&block) 670 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/sqlite.rb 673 def empty? 674 return false if @opts[:values] 675 super 676 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 681 def explain(opts=nil) 682 # Load the PrettyTable class, needed for explain output 683 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 684 685 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 686 rows = ds.all 687 Sequel::PrettyTable.string(rows, ds.columns) 688 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 691 def having(*cond) 692 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900 693 super 694 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 769 def insert_conflict(opts = :ignore) 770 case opts 771 when Symbol, String 772 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 773 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 774 end 775 clone(:insert_conflict => opts) 776 when Hash 777 clone(:insert_on_conflict => opts) 778 else 779 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 780 end 781 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 788 def insert_ignore 789 insert_conflict(:ignore) 790 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 698 def insert_select(*values) 699 return unless supports_insert_select? 700 # Handle case where query does not return a row 701 server?(:default).with_sql_first(insert_select_sql(*values)) || false 702 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 706 def insert_select_sql(*values) 707 ds = opts[:returning] ? self : returning 708 ds.insert_sql(*values) 709 end
SQLite
uses the nonstandard ‘ (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 712 def quoted_identifier_append(sql, c) 713 sql << '`' << c.to_s.gsub('`', '``') << '`' 714 end
Automatically add aliases to RETURNING values to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 793 def returning(*values) 794 return super if values.empty? 795 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 796 clone(:returning=>_returning_values(values).freeze) 797 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 720 def select(*cols) 721 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)}) 722 super(*cols.map{|c| alias_qualified_column(c)}) 723 else 724 super 725 end 726 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 800 def supports_cte?(type=:select) 801 db.sqlite_version >= 30803 802 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 805 def supports_cte_in_subqueries? 806 supports_cte? 807 end
SQLite
does not support deleting from a joined dataset
# File lib/sequel/adapters/shared/sqlite.rb 815 def supports_deleting_joins? 816 false 817 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 810 def supports_derived_column_lists? 811 false 812 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 820 def supports_intersect_except_all? 821 false 822 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 825 def supports_is_true? 826 false 827 end
SQLite
3.33.0 supports modifying joined datasets
# File lib/sequel/adapters/shared/sqlite.rb 830 def supports_modifying_joins? 831 db.sqlite_version >= 33300 832 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 835 def supports_multiple_column_in? 836 false 837 end
SQLite
3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
# File lib/sequel/adapters/shared/sqlite.rb 840 def supports_returning?(_) 841 db.sqlite_version >= 33500 842 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 847 def supports_timestamp_timezones? 848 db.use_timestamp_timezones? 849 end
SQLite
cannot use WHERE ‘t’.
# File lib/sequel/adapters/shared/sqlite.rb 852 def supports_where_true? 853 false 854 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 857 def supports_window_clause? 858 db.sqlite_version >= 32800 859 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 865 def supports_window_functions? 866 db.sqlite_version >= 32600 867 end
Private Instance Methods
Add aliases to symbols and identifiers to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 877 def _returning_values(values) 878 values.map do |v| 879 case v 880 when Symbol 881 _, c, a = split_symbol(v) 882 a ? v : Sequel.as(v, c) 883 when SQL::Identifier, SQL::QualifiedIdentifier 884 Sequel.as(v, unqualified_column_for(v)) 885 else 886 v 887 end 888 end 889 end
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 1049 def _truncate_sql(table) 1050 "DELETE FROM #{table}" 1051 end
Use from_self for aggregate dataset using VALUES.
# File lib/sequel/adapters/shared/sqlite.rb 892 def aggreate_dataset_use_from_self? 893 super || @opts[:values] 894 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 905 def alias_qualified_column(col) 906 case col 907 when Symbol 908 t, c, a = split_symbol(col) 909 if t && !a 910 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 911 else 912 col 913 end 914 when SQL::QualifiedIdentifier 915 SQL::AliasedExpression.new(col, col.column) 916 else 917 col 918 end 919 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 897 def as_sql_append(sql, aliaz, column_aliases=nil) 898 raise Error, "sqlite does not support derived column lists" if column_aliases 899 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 900 sql << ' AS ' 901 literal_append(sql, aliaz.to_s) 902 end
Raise an InvalidOperation exception if insert is not allowed for this dataset.
# File lib/sequel/adapters/shared/sqlite.rb 922 def check_insert_allowed! 923 raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group] 924 raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset? 925 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 929 def default_import_slice 930 500 931 end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/sqlite.rb 934 def default_timestamp_format 935 db.use_timestamp_timezones? ? "'%Y-%m-%d %H:%M:%S.%6N%z'" : super 936 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 939 def identifier_list(columns) 940 columns.map{|i| quote_identifier(i)}.join(', ') 941 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 944 def insert_conflict_sql(sql) 945 if resolution = @opts[:insert_conflict] 946 sql << " OR " << resolution.to_s.upcase 947 end 948 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 951 def insert_on_conflict_sql(sql) 952 if opts = @opts[:insert_on_conflict] 953 sql << " ON CONFLICT" 954 955 if target = opts[:constraint] 956 sql << " ON CONSTRAINT " 957 identifier_append(sql, target) 958 elsif target = opts[:target] 959 sql << ' ' 960 identifier_append(sql, Array(target)) 961 if conflict_where = opts[:conflict_where] 962 sql << " WHERE " 963 literal_append(sql, conflict_where) 964 end 965 end 966 967 if values = opts[:update] 968 sql << " DO UPDATE SET " 969 update_sql_values_hash(sql, values) 970 if update_where = opts[:update_where] 971 sql << " WHERE " 972 literal_append(sql, update_where) 973 end 974 else 975 sql << " DO NOTHING" 976 end 977 end 978 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 981 def literal_blob_append(sql, v) 982 sql << "X'" << v.unpack("H*").first << "'" 983 end
Respect the database integer_booleans setting, using 0 or ‘f’.
# File lib/sequel/adapters/shared/sqlite.rb 986 def literal_false 987 @db.integer_booleans ? '0' : "'f'" 988 end
Respect the database integer_booleans setting, using 1 or ‘t’.
# File lib/sequel/adapters/shared/sqlite.rb 991 def literal_true 992 @db.integer_booleans ? '1' : "'t'" 993 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 997 def multi_insert_sql_strategy 998 db.sqlite_version >= 30711 ? :values : :union 999 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 1002 def native_function_name(emulated_function) 1003 if emulated_function == :char_length 1004 'length' 1005 else 1006 super 1007 end 1008 end
SQLite
supports NULLS FIRST/LAST natively in 3.30+.
# File lib/sequel/adapters/shared/sqlite.rb 1011 def requires_emulating_nulls_first? 1012 db.sqlite_version < 33000 1013 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 1018 def select_lock_sql(sql) 1019 super unless @opts[:lock] == :update 1020 end
# File lib/sequel/adapters/shared/sqlite.rb 1022 def select_only_offset_sql(sql) 1023 sql << " LIMIT -1 OFFSET " 1024 literal_append(sql, @opts[:offset]) 1025 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 1028 def select_values_sql(sql) 1029 sql << "VALUES " 1030 expression_list_append(sql, opts[:values]) 1031 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 1034 def supports_cte_in_compounds? 1035 false 1036 end
SQLite
3.30 supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/sqlite.rb 1039 def supports_filtered_aggregates? 1040 db.sqlite_version >= 33000 1041 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 1044 def supports_quoted_function_names? 1045 true 1046 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/sqlite.rb 1054 def update_from_sql(sql) 1055 if(from = @opts[:from][1..-1]).empty? 1056 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1057 else 1058 sql << ' FROM ' 1059 source_list_append(sql, from) 1060 select_join_sql(sql) 1061 end 1062 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/sqlite.rb 1065 def update_table_sql(sql) 1066 sql << ' ' 1067 source_list_append(sql, @opts[:from][0..0]) 1068 end