Module: Sequel::SQLite::DatasetMethods
- Includes:
- Dataset::Replace, UnmodifiedIdentifiers::DatasetMethods
- Included in:
- Amalgalite::Dataset, Dataset
- Defined in:
- lib/sequel/adapters/shared/sqlite.rb
Constant Summary collapse
- INSERT_CONFLICT_RESOLUTIONS =
The allowed values for insert_conflict
%w'ROLLBACK ABORT FAIL IGNORE REPLACE'.each(&:freeze).freeze
- CONSTANT_MAP =
{:CURRENT_DATE=>"date(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIMESTAMP=>"datetime(CURRENT_TIMESTAMP, 'localtime')".freeze, :CURRENT_TIME=>"time(CURRENT_TIMESTAMP, 'localtime')".freeze}.freeze
- EXTRACT_MAP =
{:year=>"'%Y'", :month=>"'%m'", :day=>"'%d'", :hour=>"'%H'", :minute=>"'%M'", :second=>"'%f'"}.freeze
Instance Method Summary collapse
- #cast_sql_append(sql, expr, type) ⇒ Object
-
#complex_expression_sql_append(sql, op, args) ⇒ Object
SQLite doesn’t support a NOT LIKE b, you need to use NOT (a LIKE b).
-
#constant_sql_append(sql, constant) ⇒ Object
SQLite has CURRENT_TIMESTAMP and related constants in UTC instead of in localtime, so convert those constants to local time.
-
#delete(&block) ⇒ Object
SQLite performs a TRUNCATE style DELETE if no filter is specified.
-
#empty? ⇒ Boolean
Always return false when using VALUES.
-
#explain(opts = nil) ⇒ Object
Return an array of strings specifying a query explanation for a SELECT of the current dataset.
-
#having(*cond) ⇒ Object
HAVING requires GROUP BY on SQLite.
-
#insert_conflict(opts = :ignore) ⇒ Object
Handle uniqueness violations when inserting, by using a specified resolution algorithm.
-
#insert_ignore ⇒ Object
Ignore uniqueness/exclusion violations when inserting, using INSERT OR IGNORE.
-
#insert_select(*values) ⇒ Object
Support insert select for associations, so that the model code can use returning instead of a separate query.
-
#insert_select_sql(*values) ⇒ Object
The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
-
#quoted_identifier_append(sql, c) ⇒ Object
SQLite uses the nonstandard ‘ (backtick) for quoting identifiers.
-
#returning(*values) ⇒ Object
Automatically add aliases to RETURNING values to work around SQLite bug.
-
#select(*cols) ⇒ Object
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.
-
#supports_cte?(type = :select) ⇒ Boolean
SQLite 3.8.3+ supports common table expressions.
-
#supports_cte_in_subqueries? ⇒ Boolean
SQLite supports CTEs in subqueries if it supports CTEs.
-
#supports_deleting_joins? ⇒ Boolean
SQLite does not support deleting from a joined dataset.
-
#supports_derived_column_lists? ⇒ Boolean
SQLite does not support table aliases with column aliases.
-
#supports_intersect_except_all? ⇒ Boolean
SQLite does not support INTERSECT ALL or EXCEPT ALL.
-
#supports_is_true? ⇒ Boolean
SQLite does not support IS TRUE.
-
#supports_modifying_joins? ⇒ Boolean
SQLite 3.33.0 supports modifying joined datasets.
-
#supports_multiple_column_in? ⇒ Boolean
SQLite does not support multiple columns for the IN/NOT IN operators.
-
#supports_returning?(_) ⇒ Boolean
SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
-
#supports_timestamp_timezones? ⇒ Boolean
SQLite supports timezones in literal timestamps, since it stores them as text.
-
#supports_where_true? ⇒ Boolean
SQLite cannot use WHERE ‘t’.
-
#supports_window_clause? ⇒ Boolean
SQLite 3.28+ supports the WINDOW clause.
-
#supports_window_function_frame_option?(option) ⇒ Boolean
SQLite 3.28.0+ supports all window frame options that Sequel supports.
-
#supports_window_functions? ⇒ Boolean
SQLite 3.25+ supports window functions.
Instance Method Details
#cast_sql_append(sql, expr, type) ⇒ Object
596 597 598 599 600 601 602 603 604 605 606 607 608 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 596 def cast_sql_append(sql, expr, type) if type == Time or type == DateTime sql << "datetime(" literal_append(sql, expr) sql << ')' elsif type == Date sql << "date(" literal_append(sql, expr) sql << ')' else super end end |
#complex_expression_sql_append(sql, op, args) ⇒ Object
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.
612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 612 def complex_expression_sql_append(sql, op, args) case op when :"NOT LIKE", :"NOT ILIKE" sql << 'NOT ' complex_expression_sql_append(sql, (op == :"NOT ILIKE" ? :ILIKE : :LIKE), args) when :^ complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["((~(", " & ", ")) & (", " | ", "))"], a, b, a, b)} when :** unless (exp = args[1]).is_a?(Integer) raise(Sequel::Error, "can only emulate exponentiation on SQLite if exponent is an integer, given #{exp.inspect}") end case exp when 0 sql << '1' else sql << '(' arg = args[0] if exp < 0 invert = true exp = exp.abs sql << '(1.0 / (' end (exp - 1).times do literal_append(sql, arg) sql << " * " end literal_append(sql, arg) sql << ')' if invert sql << "))" end end when :extract part = args[0] raise(Sequel::Error, "unsupported extract argument: #{part.inspect}") unless format = EXTRACT_MAP[part] sql << "CAST(strftime(" << format << ', ' literal_append(sql, args[1]) sql << ') AS ' << (part == :second ? 'NUMERIC' : 'INTEGER') << ')' else super end end |
#constant_sql_append(sql, constant) ⇒ Object
SQLite has CURRENT_TIMESTAMP and related constants in UTC instead of in localtime, so convert those constants to local time.
657 658 659 660 661 662 663 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 657 def constant_sql_append(sql, constant) if (c = CONSTANT_MAP[constant]) && !db. sql << c else super end end |
#delete(&block) ⇒ Object
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.
668 669 670 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 668 def delete(&block) @opts[:where] ? super : where(1=>1).delete(&block) end |
#empty? ⇒ Boolean
Always return false when using VALUES
673 674 675 676 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 673 def empty? return false if @opts[:values] super end |
#explain(opts = nil) ⇒ Object
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.
681 682 683 684 685 686 687 688 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 681 def explain(opts=nil) # Load the PrettyTable class, needed for explain output Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) ds = db.send(:metadata_dataset).clone(:sql=>"EXPLAIN #{select_sql}") rows = ds.all Sequel::PrettyTable.string(rows, ds.columns) end |
#having(*cond) ⇒ Object
HAVING requires GROUP BY on SQLite
691 692 693 694 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 691 def having(*cond) raise(InvalidOperation, "Can only specify a HAVING clause on a grouped dataset") if !@opts[:group] && db.sqlite_version < 33900 super end |
#insert_conflict(opts = :ignore) ⇒ Object
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)
769 770 771 772 773 774 775 776 777 778 779 780 781 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 769 def insert_conflict(opts = :ignore) case opts when Symbol, String unless INSERT_CONFLICT_RESOLUTIONS.include?(opts.to_s.upcase) raise Error, "Invalid symbol or string passed to Dataset#insert_conflict: #{opts.inspect}. The allowed values are: :rollback, :abort, :fail, :ignore, or :replace" end clone(:insert_conflict => opts) when Hash clone(:insert_on_conflict => opts) else raise Error, "Invalid value passed to Dataset#insert_conflict: #{opts.inspect}, should use a symbol or a hash" end end |
#insert_ignore ⇒ Object
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)
788 789 790 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 788 def insert_ignore insert_conflict(:ignore) end |
#insert_select(*values) ⇒ Object
Support insert select for associations, so that the model code can use returning instead of a separate query.
698 699 700 701 702 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 698 def insert_select(*values) return unless supports_insert_select? # Handle case where query does not return a row server?(:default).with_sql_first(insert_select_sql(*values)) || false end |
#insert_select_sql(*values) ⇒ Object
The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
706 707 708 709 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 706 def insert_select_sql(*values) ds = opts[:returning] ? self : returning ds.insert_sql(*values) end |
#quoted_identifier_append(sql, c) ⇒ Object
SQLite uses the nonstandard ‘ (backtick) for quoting identifiers.
712 713 714 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 712 def quoted_identifier_append(sql, c) sql << '`' << c.to_s.gsub('`', '``') << '`' end |
#returning(*values) ⇒ Object
Automatically add aliases to RETURNING values to work around SQLite bug.
793 794 795 796 797 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 793 def returning(*values) return super if values.empty? raise Error, "RETURNING is not supported on #{db.database_type}" unless supports_returning?(:insert) clone(:returning=>_returning_values(values).freeze) end |
#select(*cols) ⇒ Object
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.
720 721 722 723 724 725 726 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 720 def select(*cols) 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)}) super(*cols.map{|c| alias_qualified_column(c)}) else super end end |
#supports_cte?(type = :select) ⇒ Boolean
SQLite 3.8.3+ supports common table expressions.
800 801 802 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 800 def supports_cte?(type=:select) db.sqlite_version >= 30803 end |
#supports_cte_in_subqueries? ⇒ Boolean
SQLite supports CTEs in subqueries if it supports CTEs.
805 806 807 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 805 def supports_cte_in_subqueries? supports_cte? end |
#supports_deleting_joins? ⇒ Boolean
SQLite does not support deleting from a joined dataset
815 816 817 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 815 def supports_deleting_joins? false end |
#supports_derived_column_lists? ⇒ Boolean
SQLite does not support table aliases with column aliases
810 811 812 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 810 def supports_derived_column_lists? false end |
#supports_intersect_except_all? ⇒ Boolean
SQLite does not support INTERSECT ALL or EXCEPT ALL
820 821 822 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 820 def supports_intersect_except_all? false end |
#supports_is_true? ⇒ Boolean
SQLite does not support IS TRUE
825 826 827 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 825 def supports_is_true? false end |
#supports_modifying_joins? ⇒ Boolean
SQLite 3.33.0 supports modifying joined datasets
830 831 832 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 830 def db.sqlite_version >= 33300 end |
#supports_multiple_column_in? ⇒ Boolean
SQLite does not support multiple columns for the IN/NOT IN operators
835 836 837 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 835 def supports_multiple_column_in? false end |
#supports_returning?(_) ⇒ Boolean
SQLite 3.35.0 supports RETURNING on INSERT/UPDATE/DELETE.
840 841 842 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 840 def supports_returning?(_) db.sqlite_version >= 33500 end |
#supports_timestamp_timezones? ⇒ Boolean
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.
847 848 849 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 847 def db. end |
#supports_where_true? ⇒ Boolean
SQLite cannot use WHERE ‘t’.
852 853 854 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 852 def supports_where_true? false end |
#supports_window_clause? ⇒ Boolean
SQLite 3.28+ supports the WINDOW clause.
857 858 859 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 857 def supports_window_clause? db.sqlite_version >= 32800 end |
#supports_window_function_frame_option?(option) ⇒ Boolean
SQLite 3.28.0+ supports all window frame options that Sequel supports
870 871 872 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 870 def supports_window_function_frame_option?(option) db.sqlite_version >= 32800 ? true : super end |
#supports_window_functions? ⇒ Boolean
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.
865 866 867 |
# File 'lib/sequel/adapters/shared/sqlite.rb', line 865 def supports_window_functions? db.sqlite_version >= 32600 end |