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 584 def cast_sql_append(sql, expr, type) 585 if type == Time or type == DateTime 586 sql << "datetime(" 587 literal_append(sql, expr) 588 sql << ')' 589 elsif type == Date 590 sql << "date(" 591 literal_append(sql, expr) 592 sql << ')' 593 else 594 super 595 end 596 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 600 def complex_expression_sql_append(sql, op, args) 601 case op 602 when :"NOT LIKE", :"NOT ILIKE" 603 sql << 'NOT ' 604 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 605 when :^ 606 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 607 when :** 608 unless (exp = args[1]).is_a?(Integer) 609 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 610 end 611 case exp 612 when 0 613 sql << '1' 614 else 615 sql << '(' 616 arg = args[0] 617 if exp < 0 618 invert = true 619 exp = exp.abs 620 sql << '(1.0 / (' 621 end 622 (exp - 1).times do 623 literal_append(sql, arg) 624 sql << " * " 625 end 626 literal_append(sql, arg) 627 sql << ')' 628 if invert 629 sql << "))" 630 end 631 end 632 when :extract 633 part = args[0] 634 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 635 sql << "CAST(strftime(" << format << ', ' 636 literal_append(sql, args[1]) 637 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 638 else 639 super 640 end 641 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 645 def constant_sql_append(sql, constant) 646 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 647 sql << c 648 else 649 super 650 end 651 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 656 def delete(&block) 657 @opts[:where] ? super : where(1=>1).delete(&block) 658 end
Always return false when using VALUES
# File lib/sequel/adapters/shared/sqlite.rb 661 def empty? 662 return false if @opts[:values] 663 super 664 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 669 def explain(opts=nil) 670 # Load the PrettyTable class, needed for explain output 671 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 672 673 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 674 rows = ds.all 675 Sequel::PrettyTable.string(rows, ds.columns) 676 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 679 def having(*cond) 680 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900 681 super 682 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 757 def insert_conflict(opts = :ignore) 758 case opts 759 when Symbol, String 760 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 761 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 762 end 763 clone(:insert_conflict => opts) 764 when Hash 765 clone(:insert_on_conflict => opts) 766 else 767 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 768 end 769 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 776 def insert_ignore 777 insert_conflict(:ignore) 778 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 686 def insert_select(*values) 687 return unless supports_insert_select? 688 # Handle case where query does not return a row 689 server?(:default).with_sql_first(insert_select_sql(*values)) || false 690 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 694 def insert_select_sql(*values) 695 ds = opts[:returning] ? self : returning 696 ds.insert_sql(*values) 697 end
SQLite
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 700 def quoted_identifier_append(sql, c) 701 sql << '`' << c.to_s.gsub('`', '``') << '`' 702 end
Automatically add aliases to RETURNING values to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 781 def returning(*values) 782 return super if values.empty? 783 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 784 clone(:returning=>_returning_values(values).freeze) 785 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 708 def select(*cols) 709 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)}) 710 super(*cols.map{|c| alias_qualified_column(c)}) 711 else 712 super 713 end 714 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 788 def supports_cte?(type=:select) 789 db.sqlite_version >= 30803 790 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 793 def supports_cte_in_subqueries? 794 supports_cte? 795 end
SQLite
does not support deleting from a joined dataset
# File lib/sequel/adapters/shared/sqlite.rb 803 def supports_deleting_joins? 804 false 805 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 798 def supports_derived_column_lists? 799 false 800 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 808 def supports_intersect_except_all? 809 false 810 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 813 def supports_is_true? 814 false 815 end
SQLite
3.33.0 supports modifying joined datasets
# File lib/sequel/adapters/shared/sqlite.rb 818 def supports_modifying_joins? 819 db.sqlite_version >= 33300 820 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 823 def supports_multiple_column_in? 824 false 825 end
SQLite
3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
# File lib/sequel/adapters/shared/sqlite.rb 828 def supports_returning?(_) 829 db.sqlite_version >= 33500 830 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 835 def supports_timestamp_timezones? 836 db.use_timestamp_timezones? 837 end
SQLite
cannot use WHERE 't'.
# File lib/sequel/adapters/shared/sqlite.rb 840 def supports_where_true? 841 false 842 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 845 def supports_window_clause? 846 db.sqlite_version >= 32800 847 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 853 def supports_window_functions? 854 db.sqlite_version >= 32600 855 end
Private Instance Methods
Add aliases to symbols and identifiers to work around SQLite
bug.
# File lib/sequel/adapters/shared/sqlite.rb 865 def _returning_values(values) 866 values.map do |v| 867 case v 868 when Symbol 869 _, c, a = split_symbol(v) 870 a ? v : Sequel.as(v, c) 871 when SQL::Identifier, SQL::QualifiedIdentifier 872 Sequel.as(v, unqualified_column_for(v)) 873 else 874 v 875 end 876 end 877 end
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 1032 def _truncate_sql(table) 1033 "DELETE FROM #{table}" 1034 end
Use from_self for aggregate dataset using VALUES.
# File lib/sequel/adapters/shared/sqlite.rb 880 def aggreate_dataset_use_from_self? 881 super || @opts[:values] 882 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 893 def alias_qualified_column(col) 894 case col 895 when Symbol 896 t, c, a = split_symbol(col) 897 if t && !a 898 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 899 else 900 col 901 end 902 when SQL::QualifiedIdentifier 903 SQL::AliasedExpression.new(col, col.column) 904 else 905 col 906 end 907 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 885 def as_sql_append(sql, aliaz, column_aliases=nil) 886 raise Error, "sqlite does not support derived column lists" if column_aliases 887 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 888 sql << ' AS ' 889 literal_append(sql, aliaz.to_s) 890 end
Raise an InvalidOperation exception if insert is not allowed for this dataset.
# File lib/sequel/adapters/shared/sqlite.rb 910 def check_insert_allowed! 911 raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group] 912 raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset? 913 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 917 def default_import_slice 918 500 919 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 922 def identifier_list(columns) 923 columns.map{|i| quote_identifier(i)}.join(', ') 924 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 927 def insert_conflict_sql(sql) 928 if resolution = @opts[:insert_conflict] 929 sql << " OR " << resolution.to_s.upcase 930 end 931 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 934 def insert_on_conflict_sql(sql) 935 if opts = @opts[:insert_on_conflict] 936 sql << " ON CONFLICT" 937 938 if target = opts[:constraint] 939 sql << " ON CONSTRAINT " 940 identifier_append(sql, target) 941 elsif target = opts[:target] 942 sql << ' ' 943 identifier_append(sql, Array(target)) 944 if conflict_where = opts[:conflict_where] 945 sql << " WHERE " 946 literal_append(sql, conflict_where) 947 end 948 end 949 950 if values = opts[:update] 951 sql << " DO UPDATE SET " 952 update_sql_values_hash(sql, values) 953 if update_where = opts[:update_where] 954 sql << " WHERE " 955 literal_append(sql, update_where) 956 end 957 else 958 sql << " DO NOTHING" 959 end 960 end 961 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 964 def literal_blob_append(sql, v) 965 sql << "X'" << v.unpack("H*").first << "'" 966 end
Respect the database integer_booleans setting, using 0 or 'f'.
# File lib/sequel/adapters/shared/sqlite.rb 969 def literal_false 970 @db.integer_booleans ? '0' : "'f'" 971 end
Respect the database integer_booleans setting, using 1 or 't'.
# File lib/sequel/adapters/shared/sqlite.rb 974 def literal_true 975 @db.integer_booleans ? '1' : "'t'" 976 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 980 def multi_insert_sql_strategy 981 db.sqlite_version >= 30711 ? :values : :union 982 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 985 def native_function_name(emulated_function) 986 if emulated_function == :char_length 987 'length' 988 else 989 super 990 end 991 end
SQLite
supports NULLS FIRST/LAST natively in 3.30+.
# File lib/sequel/adapters/shared/sqlite.rb 994 def requires_emulating_nulls_first? 995 db.sqlite_version < 33000 996 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 1001 def select_lock_sql(sql) 1002 super unless @opts[:lock] == :update 1003 end
# File lib/sequel/adapters/shared/sqlite.rb 1005 def select_only_offset_sql(sql) 1006 sql << " LIMIT -1 OFFSET " 1007 literal_append(sql, @opts[:offset]) 1008 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 1011 def select_values_sql(sql) 1012 sql << "VALUES " 1013 expression_list_append(sql, opts[:values]) 1014 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 1017 def supports_cte_in_compounds? 1018 false 1019 end
SQLite
3.30 supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/sqlite.rb 1022 def supports_filtered_aggregates? 1023 db.sqlite_version >= 33000 1024 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 1027 def supports_quoted_function_names? 1028 true 1029 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/sqlite.rb 1037 def update_from_sql(sql) 1038 if(from = @opts[:from][1..-1]).empty? 1039 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1040 else 1041 sql << ' FROM ' 1042 source_list_append(sql, from) 1043 select_join_sql(sql) 1044 end 1045 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/sqlite.rb 1048 def update_table_sql(sql) 1049 sql << ' ' 1050 source_list_append(sql, @opts[:from][0..0]) 1051 end