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 570 def cast_sql_append(sql, expr, type) 571 if type == Time or type == DateTime 572 sql << "datetime(" 573 literal_append(sql, expr) 574 sql << ')' 575 elsif type == Date 576 sql << "date(" 577 literal_append(sql, expr) 578 sql << ')' 579 else 580 super 581 end 582 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 586 def complex_expression_sql_append(sql, op, args) 587 case op 588 when :"NOT LIKE", :"NOT ILIKE" 589 sql << 'NOT ' 590 complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) 591 when :^ 592 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} 593 when :** 594 unless (exp = args[1]).is_a?(Integer) 595 raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") 596 end 597 case exp 598 when 0 599 sql << '1' 600 else 601 sql << '(' 602 arg = args[0] 603 if exp < 0 604 invert = true 605 exp = exp.abs 606 sql << '(1.0 / (' 607 end 608 (exp - 1).times do 609 literal_append(sql, arg) 610 sql << " * " 611 end 612 literal_append(sql, arg) 613 sql << ')' 614 if invert 615 sql << "))" 616 end 617 end 618 when :extract 619 part = args[0] 620 raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] 621 sql << "CAST(strftime(" << format << ', ' 622 literal_append(sql, args[1]) 623 sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' 624 else 625 super 626 end 627 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 631 def constant_sql_append(sql, constant) 632 if (c = CONSTANT_MAP[constant]) && !db.current_timestamp_utc 633 sql << c 634 else 635 super 636 end 637 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 642 def delete 643 @opts[:where] ? super : where(1=>1).delete 644 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 649 def explain(opts=nil) 650 # Load the PrettyTable class, needed for explain output 651 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 652 653 ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") 654 rows = ds.all 655 Sequel::PrettyTable.string(rows, ds.columns) 656 end
HAVING requires GROUP BY on SQLite
# File lib/sequel/adapters/shared/sqlite.rb 659 def having(*cond) 660 raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") unless @opts[:group] 661 super 662 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 722 def insert_conflict(opts = :ignore) 723 case opts 724 when Symbol, String 725 unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) 726 raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" 727 end 728 clone(:insert_conflict => opts) 729 when Hash 730 clone(:insert_on_conflict => opts) 731 else 732 raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" 733 end 734 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 741 def insert_ignore 742 insert_conflict(:ignore) 743 end
SQLite
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/sqlite.rb 665 def quoted_identifier_append(sql, c) 666 sql << '`' << c.to_s.gsub('`', '``') << '`' 667 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 673 def select(*cols) 674 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)}) 675 super(*cols.map{|c| alias_qualified_column(c)}) 676 else 677 super 678 end 679 end
SQLite
3.8.3+ supports common table expressions.
# File lib/sequel/adapters/shared/sqlite.rb 746 def supports_cte?(type=:select) 747 db.sqlite_version >= 30803 748 end
SQLite
supports CTEs in subqueries if it supports CTEs.
# File lib/sequel/adapters/shared/sqlite.rb 751 def supports_cte_in_subqueries? 752 supports_cte? 753 end
SQLite
does not support deleting from a joined dataset
# File lib/sequel/adapters/shared/sqlite.rb 761 def supports_deleting_joins? 762 false 763 end
SQLite
does not support table aliases with column aliases
# File lib/sequel/adapters/shared/sqlite.rb 756 def supports_derived_column_lists? 757 false 758 end
SQLite
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/sqlite.rb 766 def supports_intersect_except_all? 767 false 768 end
SQLite
does not support IS TRUE
# File lib/sequel/adapters/shared/sqlite.rb 771 def supports_is_true? 772 false 773 end
SQLite
3.33.0 supports modifying joined datasets
# File lib/sequel/adapters/shared/sqlite.rb 776 def supports_modifying_joins? 777 db.sqlite_version >= 33300 778 end
SQLite
does not support multiple columns for the IN/NOT IN operators
# File lib/sequel/adapters/shared/sqlite.rb 781 def supports_multiple_column_in? 782 false 783 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 788 def supports_timestamp_timezones? 789 db.use_timestamp_timezones? 790 end
SQLite
cannot use WHERE 't'.
# File lib/sequel/adapters/shared/sqlite.rb 793 def supports_where_true? 794 false 795 end
SQLite
3.28+ supports the WINDOW clause.
# File lib/sequel/adapters/shared/sqlite.rb 798 def supports_window_clause? 799 db.sqlite_version >= 32800 800 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 806 def supports_window_functions? 807 db.sqlite_version >= 32600 808 end
Private Instance Methods
SQLite
treats a DELETE with no WHERE clause as a TRUNCATE
# File lib/sequel/adapters/shared/sqlite.rb 965 def _truncate_sql(table) 966 "DELETE FROM #{table}" 967 end
If col is a qualified column, alias it to the same as the column name
# File lib/sequel/adapters/shared/sqlite.rb 826 def alias_qualified_column(col) 827 case col 828 when Symbol 829 t, c, a = split_symbol(col) 830 if t && !a 831 alias_qualified_column(SQL::QualifiedIdentifier.new(t, c)) 832 else 833 col 834 end 835 when SQL::QualifiedIdentifier 836 SQL::AliasedExpression.new(col, col.column) 837 else 838 col 839 end 840 end
SQLite
uses string literals instead of identifiers in AS clauses.
# File lib/sequel/adapters/shared/sqlite.rb 818 def as_sql_append(sql, aliaz, column_aliases=nil) 819 raise Error, "sqlite does not support derived column lists" if column_aliases 820 aliaz = aliaz.value if aliaz.is_a?(SQL::Identifier) 821 sql << ' AS ' 822 literal_append(sql, aliaz.to_s) 823 end
Raise an InvalidOperation exception if insert is not allowed for this dataset.
# File lib/sequel/adapters/shared/sqlite.rb 843 def check_insert_allowed! 844 raise(InvalidOperation, "Grouped datasets cannot be modified") if opts[:group] 845 raise(InvalidOperation, "Joined datasets cannot be modified") if joined_dataset? 846 end
SQLite
supports a maximum of 500 rows in a VALUES clause.
# File lib/sequel/adapters/shared/sqlite.rb 850 def default_import_slice 851 500 852 end
SQL
fragment specifying a list of identifiers
# File lib/sequel/adapters/shared/sqlite.rb 855 def identifier_list(columns) 856 columns.map{|i| quote_identifier(i)}.join(', ') 857 end
Add OR clauses to SQLite
INSERT statements
# File lib/sequel/adapters/shared/sqlite.rb 860 def insert_conflict_sql(sql) 861 if resolution = @opts[:insert_conflict] 862 sql << " OR " << resolution.to_s.upcase 863 end 864 end
Add ON CONFLICT clause if it should be used
# File lib/sequel/adapters/shared/sqlite.rb 867 def insert_on_conflict_sql(sql) 868 if opts = @opts[:insert_on_conflict] 869 sql << " ON CONFLICT" 870 871 if target = opts[:constraint] 872 sql << " ON CONSTRAINT " 873 identifier_append(sql, target) 874 elsif target = opts[:target] 875 sql << ' ' 876 identifier_append(sql, Array(target)) 877 if conflict_where = opts[:conflict_where] 878 sql << " WHERE " 879 literal_append(sql, conflict_where) 880 end 881 end 882 883 if values = opts[:update] 884 sql << " DO UPDATE SET " 885 update_sql_values_hash(sql, values) 886 if update_where = opts[:update_where] 887 sql << " WHERE " 888 literal_append(sql, update_where) 889 end 890 else 891 sql << " DO NOTHING" 892 end 893 end 894 end
SQLite
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/sqlite.rb 897 def literal_blob_append(sql, v) 898 sql << "X'" << v.unpack("H*").first << "'" 899 end
Respect the database integer_booleans setting, using 0 or 'f'.
# File lib/sequel/adapters/shared/sqlite.rb 902 def literal_false 903 @db.integer_booleans ? '0' : "'f'" 904 end
Respect the database integer_booleans setting, using 1 or 't'.
# File lib/sequel/adapters/shared/sqlite.rb 907 def literal_true 908 @db.integer_booleans ? '1' : "'t'" 909 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 913 def multi_insert_sql_strategy 914 db.sqlite_version >= 30711 ? :values : :union 915 end
Emulate the char_length function with length
# File lib/sequel/adapters/shared/sqlite.rb 918 def native_function_name(emulated_function) 919 if emulated_function == :char_length 920 'length' 921 else 922 super 923 end 924 end
SQLite
supports NULLS FIRST/LAST natively in 3.30+.
# File lib/sequel/adapters/shared/sqlite.rb 927 def requires_emulating_nulls_first? 928 db.sqlite_version < 33000 929 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 934 def select_lock_sql(sql) 935 super unless @opts[:lock] == :update 936 end
# File lib/sequel/adapters/shared/sqlite.rb 938 def select_only_offset_sql(sql) 939 sql << " LIMIT -1 OFFSET " 940 literal_append(sql, @opts[:offset]) 941 end
Support VALUES clause instead of the SELECT clause to return rows.
# File lib/sequel/adapters/shared/sqlite.rb 944 def select_values_sql(sql) 945 sql << "VALUES " 946 expression_list_append(sql, opts[:values]) 947 end
SQLite
does not support CTEs directly inside UNION/INTERSECT/EXCEPT.
# File lib/sequel/adapters/shared/sqlite.rb 950 def supports_cte_in_compounds? 951 false 952 end
SQLite
3.30 supports the FILTER clause for aggregate functions.
# File lib/sequel/adapters/shared/sqlite.rb 955 def supports_filtered_aggregates? 956 db.sqlite_version >= 33000 957 end
SQLite
supports quoted function names.
# File lib/sequel/adapters/shared/sqlite.rb 960 def supports_quoted_function_names? 961 true 962 end
Use FROM to specify additional tables in an update query
# File lib/sequel/adapters/shared/sqlite.rb 970 def update_from_sql(sql) 971 if(from = @opts[:from][1..-1]).empty? 972 raise(Error, 'Need multiple FROM tables if updating/deleting a dataset with JOINs') if @opts[:join] 973 else 974 sql << ' FROM ' 975 source_list_append(sql, from) 976 select_join_sql(sql) 977 end 978 end
Only include the primary table in the main update clause
# File lib/sequel/adapters/shared/sqlite.rb 981 def update_table_sql(sql) 982 sql << ' ' 983 source_list_append(sql, @opts[:from][0..0]) 984 end