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 566 def cast_sql_append(sql, expr, type) 567 if type == Time or type == DateTime 568 sql << "datetime(" 569 literal_append(sql, expr) 570 sql << ')' 571 elsif type == Date 572 sql << "date(" 573 literal_append(sql, expr) 574 sql << ')' 575 else 576 super 577 end 578 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 582 def complex_expression_sql_append(sql, op, args) 583 case op 584 when :"NOT LIKE", :"NOT ILIKE" 585 sql << 'NOT ' 586 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 587 when :^ 588 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 589 when :** 590 unless (exp = args[1]).is_a?(Integer) 591 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 592 end 593 case exp 594 when 0 595 sql << '1' 596 else 597 sql << '(' 598 arg = args[0] 599 if exp < 0 600 invert = true 601 exp = exp.abs 602 sql << '(1.0 / (' 603 end 604 (exp - 1).times do 605 literal_append(sql, arg) 606 sql << " * " 607 end 608 literal_append(sql, arg) 609 sql << ')' 610 if invert 611 sql << "))" 612 end 613 end 614 when :extract 615 part = args[0] 616 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 617 sql << "CAST(strftime(" << format << ', ' 618 literal_append(sql, args[1]) 619 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 620 else 621 super 622 end 623 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 627 def constant_sql_append(sql, constant) 628 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 629 sql << c 630 else 631 super 632 end 633 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 638 def delete 639 @opts[:where] ? super : where(1=>1).delete 640 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 645 def explain(opts=nil) 646 # Load the PrettyTable class, needed for explain output 647 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 648 649 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 650 rows = ds.all 651 Sequel::PrettyTable.string(rows, ds.columns) 652 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 655 def having(*cond) 656 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group] 657 super 658 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 718 def insert_conflict(opts = :ignore) 719 case opts 720 when Symbol, String 721 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 722 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 723 end 724 clone(:insert_conflict => opts) 725 when Hash 726 clone(:insert_on_conflict => opts) 727 else 728 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 729 end 730 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 737 def insert_ignore 738 insert_conflict(:ignore) 739 end
SQLite
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 661 def quoted_identifier_append(sql, c) 662 sql << '`' << c.to_s.gsub('`', '``') << '`' 663 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 669 def select(*cols) 670 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)}) 671 super(*cols.map{|c| alias_qualified_column(c)}) 672 else 673 super 674 end 675 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 742 def supports_cte?(type=:select) 743 db.sqlite_version >= 30803 744 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 747 def supports_cte_in_subqueries? 748 supports_cte? 749 end
SQLite
does not support deleting from a joined dataset
# File lib/sequel/adapters/shared/sqlite.rb 757 def supports_deleting_joins? 758 false 759 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 752 def supports_derived_column_lists? 753 false 754 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 762 def supports_intersect_except_all? 763 false 764 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 767 def supports_is_true? 768 false 769 end
SQLite
3.33.0 supports modifying joined datasets
# File lib/sequel/adapters/shared/sqlite.rb 772 def supports_modifying_joins? 773 db.sqlite_version >= 33300 774 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 777 def supports_multiple_column_in? 778 false 779 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 784 def supports_timestamp_timezones? 785 db.use_timestamp_timezones? 786 end
SQLite
cannot use WHERE 't'.
# File lib/sequel/adapters/shared/sqlite.rb 789 def supports_where_true? 790 false 791 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 794 def supports_window_clause? 795 db.sqlite_version >= 32800 796 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 802 def supports_window_functions? 803 db.sqlite_version >= 32600 804 end
Private Instance Methods
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 961 def _truncate_sql(table) 962 "DELETE FROM #{table}" 963 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 822 def alias_qualified_column(col) 823 case col 824 when Symbol 825 t, c, a = split_symbol(col) 826 if t && !a 827 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 828 else 829 col 830 end 831 when SQL::QualifiedIdentifier 832 SQL::AliasedExpression.new(col, col.column) 833 else 834 col 835 end 836 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 814 def as_sql_append(sql, aliaz, column_aliases=nil) 815 raise Error, "sqlite does not support derived column lists" if column_aliases 816 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 817 sql << ' AS ' 818 literal_append(sql, aliaz.to_s) 819 end
Raise an InvalidOperation exception if insert is not allowed for this dataset.
# File lib/sequel/adapters/shared/sqlite.rb 839 def check_insert_allowed! 840 raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group] 841 raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset? 842 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 846 def default_import_slice 847 500 848 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 851 def identifier_list(columns) 852 columns.map{|i| quote_identifier(i)}.join(', ') 853 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 856 def insert_conflict_sql(sql) 857 if resolution = @opts[:insert_conflict] 858 sql << " OR " << resolution.to_s.upcase 859 end 860 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 863 def insert_on_conflict_sql(sql) 864 if opts = @opts[:insert_on_conflict] 865 sql << " ON CONFLICT" 866 867 if target = opts[:constraint] 868 sql << " ON CONSTRAINT " 869 identifier_append(sql, target) 870 elsif target = opts[:target] 871 sql << ' ' 872 identifier_append(sql, Array(target)) 873 if conflict_where = opts[:conflict_where] 874 sql << " WHERE " 875 literal_append(sql, conflict_where) 876 end 877 end 878 879 if values = opts[:update] 880 sql << " DO UPDATE SET " 881 update_sql_values_hash(sql, values) 882 if update_where = opts[:update_where] 883 sql << " WHERE " 884 literal_append(sql, update_where) 885 end 886 else 887 sql << " DO NOTHING" 888 end 889 end 890 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 893 def literal_blob_append(sql, v) 894 sql << "X'" << v.unpack("H*").first << "'" 895 end
Respect the database integer_booleans setting, using 0 or 'f'.
# File lib/sequel/adapters/shared/sqlite.rb 898 def literal_false 899 @db.integer_booleans ? '0' : "'f'" 900 end
Respect the database integer_booleans setting, using 1 or 't'.
# File lib/sequel/adapters/shared/sqlite.rb 903 def literal_true 904 @db.integer_booleans ? '1' : "'t'" 905 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 909 def multi_insert_sql_strategy 910 db.sqlite_version >= 30711 ? :values : :union 911 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 914 def native_function_name(emulated_function) 915 if emulated_function == :char_length 916 'length' 917 else 918 super 919 end 920 end
SQLite
supports NULLS FIRST/LAST natively in 3.30+.
# File lib/sequel/adapters/shared/sqlite.rb 923 def requires_emulating_nulls_first? 924 db.sqlite_version < 33000 925 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 930 def select_lock_sql(sql) 931 super unless @opts[:lock] == :update 932 end
# File lib/sequel/adapters/shared/sqlite.rb 934 def select_only_offset_sql(sql) 935 sql << " LIMIT -1 OFFSET " 936 literal_append(sql, @opts[:offset]) 937 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 940 def select_values_sql(sql) 941 sql << "VALUES " 942 expression_list_append(sql, opts[:values]) 943 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 946 def supports_cte_in_compounds? 947 false 948 end
SQLite
3.30 supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/sqlite.rb 951 def supports_filtered_aggregates? 952 db.sqlite_version >= 33000 953 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 956 def supports_quoted_function_names? 957 true 958 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/sqlite.rb 966 def update_from_sql(sql) 967 if(from = @opts[:from][1..-1]).empty? 968 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 969 else 970 sql << ' FROM ' 971 source_list_append(sql, from) 972 select_join_sql(sql) 973 end 974 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/sqlite.rb 977 def update_table_sql(sql) 978 sql << ' ' 979 source_list_append(sql, @opts[:from][0..0]) 980 end