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