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