module Sequel::SQLite::DatasetMethods
Constants
- CONSTANT_MAP
- EXTRACT_MAP
- INSERT_CONFLICT_RESOLUTIONS
-
The allowed values for
insert_conflict
Public Instance Methods
Source
# File lib/sequel/adapters/shared/sqlite.rb 598 def cast_sql_append(sql, expr, type) 599 if type == Time or type == DateTime 600 sql << "datetime(" 601 literal_append(sql, expr) 602 sql << ')' 603 elsif type == Date 604 sql << "date(" 605 literal_append(sql, expr) 606 sql << ')' 607 else 608 super 609 end 610 end
Source
# File lib/sequel/adapters/shared/sqlite.rb 614 def complex_expression_sql_append(sql, op, args) 615 case op 616 when :"NOT LIKE", :"NOT ILIKE" 617 sql << 'NOT ' 618 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 619 when :^ 620 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 621 when :** 622 unless (exp = args[1]).is_a?(Integer) 623 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 624 end 625 case exp 626 when 0 627 sql << '1' 628 else 629 sql << '(' 630 arg = args[0] 631 if exp < 0 632 invert = true 633 exp = exp.abs 634 sql << '(1.0 / (' 635 end 636 (exp - 1).times do 637 literal_append(sql, arg) 638 sql << " * " 639 end 640 literal_append(sql, arg) 641 sql << ')' 642 if invert 643 sql << "))" 644 end 645 end 646 when :extract 647 part = args[0] 648 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 649 sql << "CAST(strftime(" << format << ', ' 650 literal_append(sql, args[1]) 651 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 652 else 653 super 654 end 655 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.
Source
# File lib/sequel/adapters/shared/sqlite.rb 659 def constant_sql_append(sql, constant) 660 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 661 sql << c 662 else 663 super 664 end 665 end
SQLite
has CURRENT_TIMESTAMP and related constants in UTC instead of in localtime, so convert those constants to local time.
Source
# File lib/sequel/adapters/shared/sqlite.rb 670 def delete(&block) 671 @opts[:where] ? super : where(1=>1).delete(&block) 672 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.
Source
# File lib/sequel/adapters/shared/sqlite.rb 675 def empty? 676 return false if @opts[:values] 677 super 678 end
Always return false when using VALUES
Source
# File lib/sequel/adapters/shared/sqlite.rb 683 def explain(opts=nil) 684 # Load the PrettyTable class, needed for explain output 685 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 686 687 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 688 rows = ds.all 689 Sequel::PrettyTable.string(rows, ds.columns) 690 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.
Source
# File lib/sequel/adapters/shared/sqlite.rb 693 def having(*cond) 694 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900 695 super 696 end
HAVING requires GROUP BY on SQLite
Source
# File lib/sequel/adapters/shared/sqlite.rb 771 def insert_conflict(opts = :ignore) 772 case opts 773 when Symbol, String 774 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 775 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 776 end 777 clone(:insert_conflict => opts) 778 when Hash 779 clone(:insert_on_conflict => opts) 780 else 781 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 782 end 783 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)
Source
# File lib/sequel/adapters/shared/sqlite.rb 790 def insert_ignore 791 insert_conflict(:ignore) 792 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)
Source
# File lib/sequel/adapters/shared/sqlite.rb 700 def insert_select(*values) 701 return unless supports_insert_select? 702 # Handle case where query does not return a row 703 server?(:default).with_sql_first(insert_select_sql(*values)) || false 704 end
Support insert select for associations, so that the model code can use returning instead of a separate query.
Source
# File lib/sequel/adapters/shared/sqlite.rb 708 def insert_select_sql(*values) 709 ds = opts[:returning] ? self : returning 710 ds.insert_sql(*values) 711 end
The SQL
to use for an insert_select
, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
Source
# File lib/sequel/adapters/shared/sqlite.rb 714 def quoted_identifier_append(sql, c) 715 sql << '`' << c.to_s.gsub('`', '``') << '`' 716 end
SQLite
uses the nonstandard ‘ (backtick) for quoting identifiers.
Source
# File lib/sequel/adapters/shared/sqlite.rb 795 def returning(*values) 796 return super if values.empty? 797 raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) 798 clone(:returning=>_returning_values(values).freeze) 799 end
Automatically add aliases to RETURNING values to work around SQLite
bug.
Source
# File lib/sequel/adapters/shared/sqlite.rb 722 def select(*cols) 723 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)}) 724 super(*cols.map{|c| alias_qualified_column(c)}) 725 else 726 super 727 end 728 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.
Source
# File lib/sequel/adapters/shared/sqlite.rb 802 def supports_cte?(type=:select) 803 db.sqlite_version >= 30803 804 end
SQLite
3.8.3+ supports common table expressions.
Source
# File lib/sequel/adapters/shared/sqlite.rb 807 def supports_cte_in_subqueries? 808 supports_cte? 809 end
SQLite
supports CTEs in subqueries if it supports CTEs.
Source
# File lib/sequel/adapters/shared/sqlite.rb 817 def supports_deleting_joins? 818 false 819 end
SQLite
does not support deleting from a joined dataset
Source
# File lib/sequel/adapters/shared/sqlite.rb 812 def supports_derived_column_lists? 813 false 814 end
SQLite
does not support table aliases with column aliases
Source
# File lib/sequel/adapters/shared/sqlite.rb 822 def supports_intersect_except_all? 823 false 824 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
Source
# File lib/sequel/adapters/shared/sqlite.rb 827 def supports_is_true? 828 false 829 end
SQLite
does not support IS TRUE
Source
# File lib/sequel/adapters/shared/sqlite.rb 832 def supports_modifying_joins? 833 db.sqlite_version >= 33300 834 end
SQLite
3.33.0 supports modifying joined datasets
Source
# File lib/sequel/adapters/shared/sqlite.rb 837 def supports_multiple_column_in? 838 false 839 end
SQLite
does not support multiple columns for the IN/NOT IN operators
Source
# File lib/sequel/adapters/shared/sqlite.rb 842 def supports_returning?(_) 843 db.sqlite_version >= 33500 844 end
SQLite
3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
Source
# File lib/sequel/adapters/shared/sqlite.rb 849 def supports_timestamp_timezones? 850 db.use_timestamp_timezones? 851 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.
Source
# File lib/sequel/adapters/shared/sqlite.rb 854 def supports_where_true? 855 false 856 end
SQLite
cannot use WHERE ‘t’.
Source
# File lib/sequel/adapters/shared/sqlite.rb 859 def supports_window_clause? 860 db.sqlite_version >= 32800 861 end
SQLite
3.28+ supports the WINDOW clause.
Source
Source
# File lib/sequel/adapters/shared/sqlite.rb 867 def supports_window_functions? 868 db.sqlite_version >= 32600 869 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.
Private Instance Methods
Source
# File lib/sequel/adapters/shared/sqlite.rb 879 def _returning_values(values) 880 values.map do |v| 881 case v 882 when Symbol 883 _, c, a = split_symbol(v) 884 a ? v : Sequel.as(v, c) 885 when SQL::Identifier, SQL::QualifiedIdentifier 886 Sequel.as(v, unqualified_column_for(v)) 887 else 888 v 889 end 890 end 891 end
Add aliases to symbols and identifiers to work around SQLite
bug.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1051 def _truncate_sql(table) 1052 "DELETE FROM #{table}" 1053 end
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
Source
# File lib/sequel/adapters/shared/sqlite.rb 894 def aggreate_dataset_use_from_self? 895 super || @opts[:values] 896 end
Use from_self for aggregate dataset using VALUES.
Source
# File lib/sequel/adapters/shared/sqlite.rb 907 def alias_qualified_column(col) 908 case col 909 when Symbol 910 t, c, a = split_symbol(col) 911 if t && !a 912 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 913 else 914 col 915 end 916 when SQL::QualifiedIdentifier 917 SQL::AliasedExpression.new(col, col.column) 918 else 919 col 920 end 921 end
If col is a qualified column, alias it to the same as the column name
Source
# File lib/sequel/adapters/shared/sqlite.rb 899 def as_sql_append(sql, aliaz, column_aliases=nil) 900 raise Error, "sqlite does not support derived column lists" if column_aliases 901 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 902 sql << ' AS ' 903 literal_append(sql, aliaz.to_s) 904 end
SQLite
uses string literals instead of identifiers in AS clauses.
Source
# File lib/sequel/adapters/shared/sqlite.rb 924 def check_insert_allowed! 925 raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group] 926 raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset? 927 end
Raise an InvalidOperation exception if insert is not allowed for this dataset.
Source
# File lib/sequel/adapters/shared/sqlite.rb 931 def default_import_slice 932 500 933 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
Source
# File lib/sequel/adapters/shared/sqlite.rb 936 def default_timestamp_format 937 db.use_timestamp_timezones? ? "'%Y-%m-%d %H:%M:%S.%6N%z'" : super 938 end
The strftime format to use when literalizing the time.
Source
# File lib/sequel/adapters/shared/sqlite.rb 941 def identifier_list(columns) 942 columns.map{|i| quote_identifier(i)}.join(', ') 943 end
SQL
fragment specifying a list of identifiers
Source
# File lib/sequel/adapters/shared/sqlite.rb 946 def insert_conflict_sql(sql) 947 if resolution = @opts[:insert_conflict] 948 sql << " OR " << resolution.to_s.upcase 949 end 950 end
Add OR clauses to SQLite
INSERT statements
Source
# File lib/sequel/adapters/shared/sqlite.rb 953 def insert_on_conflict_sql(sql) 954 if opts = @opts[:insert_on_conflict] 955 sql << " ON CONFLICT" 956 957 if target = opts[:constraint] 958 sql << " ON CONSTRAINT " 959 identifier_append(sql, target) 960 elsif target = opts[:target] 961 sql << ' ' 962 identifier_append(sql, Array(target)) 963 if conflict_where = opts[:conflict_where] 964 sql << " WHERE " 965 literal_append(sql, conflict_where) 966 end 967 end 968 969 if values = opts[:update] 970 sql << " DO UPDATE SET " 971 update_sql_values_hash(sql, values) 972 if update_where = opts[:update_where] 973 sql << " WHERE " 974 literal_append(sql, update_where) 975 end 976 else 977 sql << " DO NOTHING" 978 end 979 end 980 end
Add ON CONFLICT clause if it should be used
Source
# File lib/sequel/adapters/shared/sqlite.rb 983 def literal_blob_append(sql, v) 984 sql << "X'" << v.unpack("H*").first << "'" 985 end
SQLite
uses a preceding X for hex escaping strings
Source
# File lib/sequel/adapters/shared/sqlite.rb 988 def literal_false 989 @db.integer_booleans ? '0' : "'f'" 990 end
Respect the database integer_booleans setting, using 0 or ‘f’.
Source
# File lib/sequel/adapters/shared/sqlite.rb 993 def literal_true 994 @db.integer_booleans ? '1' : "'t'" 995 end
Respect the database integer_booleans setting, using 1 or ‘t’.
Source
# File lib/sequel/adapters/shared/sqlite.rb 999 def multi_insert_sql_strategy 1000 db.sqlite_version >= 30711 ? :values : :union 1001 end
SQLite
only supporting multiple rows in the VALUES clause starting in 3.7.11. On older versions, fallback to using a UNION.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1004 def native_function_name(emulated_function) 1005 if emulated_function == :char_length 1006 'length' 1007 else 1008 super 1009 end 1010 end
Emulate the char_length function with length
Source
# File lib/sequel/adapters/shared/sqlite.rb 1013 def requires_emulating_nulls_first? 1014 db.sqlite_version < 33000 1015 end
SQLite
supports NULLS FIRST/LAST natively in 3.30+.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1020 def select_lock_sql(sql) 1021 super unless @opts[:lock] == :update 1022 end
SQLite
does not support FOR UPDATE, but silently ignore it instead of raising an error for compatibility with other databases.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1024 def select_only_offset_sql(sql) 1025 sql << " LIMIT -1 OFFSET " 1026 literal_append(sql, @opts[:offset]) 1027 end
Source
# File lib/sequel/adapters/shared/sqlite.rb 1030 def select_values_sql(sql) 1031 sql << "VALUES " 1032 expression_list_append(sql, opts[:values]) 1033 end
Support VALUES clause instead of the SELECT clause to return rows.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1036 def supports_cte_in_compounds? 1037 false 1038 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1041 def supports_filtered_aggregates? 1042 db.sqlite_version >= 33000 1043 end
SQLite
3.30 supports the FILTER clause for aggregate functions.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1046 def supports_quoted_function_names? 1047 true 1048 end
SQLite
supports quoted function names.
Source
# File lib/sequel/adapters/shared/sqlite.rb 1056 def update_from_sql(sql) 1057 if(from = @opts[:from][1..-1]).empty? 1058 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 1059 else 1060 sql << ' FROM ' 1061 source_list_append(sql, from) 1062 select_join_sql(sql) 1063 end 1064 end
Use FROM to specify additional tables in an update query
Source
# File lib/sequel/adapters/shared/sqlite.rb 1067 def update_table_sql(sql) 1068 sql << ' ' 1069 source_list_append(sql, @opts[:from][0..0]) 1070 end
Only include the primary table in the main update clause