Module: Sequel::MySQL::DatasetMethods
- Included in:
- Dataset, Sequel::Mysql2::Dataset
- Defined in:
- lib/sequel/adapters/shared/mysql.rb
Overview
Dataset methods shared by datasets that use MySQL databases.
Constant Summary collapse
- MATCH_AGAINST =
["MATCH ".freeze, " AGAINST (".freeze, ")".freeze].freeze
- MATCH_AGAINST_BOOLEAN =
["MATCH ".freeze, " AGAINST (".freeze, " IN BOOLEAN MODE)".freeze].freeze
Instance Method Summary collapse
-
#calc_found_rows ⇒ Object
Sets up the select methods to use SQL_CALC_FOUND_ROWS option.
- #complex_expression_sql_append(sql, op, args) ⇒ Object
-
#constant_sql_append(sql, constant) ⇒ Object
MySQL's CURRENT_TIMESTAMP does not use fractional seconds, even if the database itself supports fractional seconds.
-
#delete_from(*tables) ⇒ Object
Sets up the select methods to delete from if deleting from a joined dataset:.
-
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
-
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string.
-
#for_share ⇒ Object
Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.
-
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
Adds full text filter.
-
#full_text_sql(cols, terms, opts = OPTS) ⇒ Object
MySQL specific full text search syntax.
-
#insert_ignore ⇒ Object
Sets up the insert methods to use INSERT IGNORE.
-
#on_duplicate_key_update(*args) ⇒ Object
Sets up the insert methods to use ON DUPLICATE KEY UPDATE If you pass no arguments, ALL fields will be updated with the new values.
-
#quoted_identifier_append(sql, c) ⇒ Object
MySQL uses the nonstandard ` (backtick) for quoting identifiers.
-
#supports_cte?(type = :select) ⇒ Boolean
MariaDB 10.2+ and MySQL 8+ support CTEs.
-
#supports_derived_column_lists? ⇒ Boolean
MySQL does not support derived column lists.
-
#supports_distinct_on? ⇒ Boolean
MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.
-
#supports_group_rollup? ⇒ Boolean
MySQL supports GROUP BY WITH ROLLUP (but not CUBE).
-
#supports_intersect_except? ⇒ Boolean
MariaDB 10.3+ supports INTERSECT or EXCEPT.
-
#supports_limits_in_correlated_subqueries? ⇒ Boolean
MySQL does not support limits in correlated subqueries (or any subqueries that use IN).
-
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets.
-
#supports_nowait? ⇒ Boolean
MySQL 8+ and MariaDB 10.3+ support NOWAIT.
-
#supports_ordered_distinct_on? ⇒ Boolean
MySQL's DISTINCT ON emulation using GROUP BY does not respect the query's ORDER BY clause.
-
#supports_regexp? ⇒ Boolean
MySQL supports pattern matching via regular expressions.
-
#supports_skip_locked? ⇒ Boolean
MySQL 8+ supports SKIP LOCKED.
-
#supports_timestamp_usecs? ⇒ Boolean
Check the database setting for whether fractional timestamps are suppported.
-
#supports_window_clause? ⇒ Boolean
MySQL 8+ supports WINDOW clause.
-
#supports_window_functions? ⇒ Boolean
MariaDB 10.2+ and MySQL 8+ support window functions.
-
#update_ignore ⇒ Object
Sets up the update methods to use UPDATE IGNORE.
Methods included from Dataset::Replace
#multi_replace, #replace, #replace_sql, #supports_replace?
Instance Method Details
#calc_found_rows ⇒ Object
Sets up the select methods to use SQL_CALC_FOUND_ROWS option.
dataset.calc_found_rows.limit(10)
# SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10
677 678 679 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 677 def calc_found_rows clone(:calc_found_rows => true) end |
#complex_expression_sql_append(sql, op, args) ⇒ Object
611 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 654 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 611 def complex_expression_sql_append(sql, op, args) case op when :IN, :"NOT IN" ds = args[1] if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] super(sql, op, [args[0], ds.from_self]) else super end when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') func = ~func if op == :'!~' return literal_append(sql, func) end sql << '(' literal_append(sql, args[0]) sql << ' ' sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') sql << ' ' sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) literal_append(sql, args[1]) if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) sql << " ESCAPE " literal_append(sql, "\\") end sql << ')' when :'||' if args.length > 1 sql << "CONCAT" array_sql_append(sql, args) else literal_append(sql, args[0]) end when :'B~' sql << "CAST(~" literal_append(sql, args[0]) sql << " AS SIGNED INTEGER)" else super end end |
#constant_sql_append(sql, constant) ⇒ Object
MySQL's CURRENT_TIMESTAMP does not use fractional seconds, even if the database itself supports fractional seconds. If MySQL 5.6.4+ is being used, use a value that will return fractional seconds.
660 661 662 663 664 665 666 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 660 def constant_sql_append(sql, constant) if constant == :CURRENT_TIMESTAMP && sql << 'CURRENT_TIMESTAMP(6)' else super end end |
#delete_from(*tables) ⇒ Object
Sets up the select methods to delete from if deleting from a joined dataset:
DB[:a].join(:b, a_id: :id).delete
# DELETE a FROM a INNER JOIN b ON (b.a_id = a.id)
DB[:a].join(:b, a_id: :id).delete_from(:a, :b).delete
# DELETE a, b FROM a INNER JOIN b ON (b.a_id = a.id)
689 690 691 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 689 def delete_from(*tables) clone(:delete_from=>tables) end |
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
669 670 671 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 669 def distinct(*args) args.empty? ? super : group(*args) end |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string. Options:
- :extended
-
Use EXPLAIN EXTENDED instead of EXPLAIN if true.
695 696 697 698 699 700 701 702 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 695 def explain(opts=OPTS) # Load the PrettyTable class, needed for explain output Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked rows = ds.all Sequel::PrettyTable.string(rows, ds.columns) end |
#for_share ⇒ Object
Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.
705 706 707 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 705 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
Adds full text filter
710 711 712 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 710 def full_text_search(cols, terms, opts = OPTS) where(full_text_sql(cols, terms, opts)) end |
#full_text_sql(cols, terms, opts = OPTS) ⇒ Object
MySQL specific full text search syntax.
715 716 717 718 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 715 def full_text_sql(cols, terms, opts = OPTS) terms = terms.join(' ') if terms.is_a?(Array) SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) end |
#insert_ignore ⇒ Object
Sets up the insert methods to use INSERT IGNORE. Useful if you have a unique key and want to just skip inserting rows that violate the unique key restriction.
dataset.insert_ignore.multi_insert(
[{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT IGNORE INTO tablename (name, value) VALUES (a, 1), (b, 2)
728 729 730 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 728 def insert_ignore clone(:insert_ignore=>true) end |
#on_duplicate_key_update(*args) ⇒ Object
Sets up the insert methods to use ON DUPLICATE KEY UPDATE If you pass no arguments, ALL fields will be updated with the new values. If you pass the fields you want then ONLY those field will be updated. If you pass a hash you can customize the values (for example, to increment a numeric field).
Useful if you have a unique key and want to update inserting rows that violate the unique key restriction.
dataset.on_duplicate_key_update.multi_insert(
[{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE name=VALUES(name), value=VALUES(value)
dataset.on_duplicate_key_update(:value).multi_insert(
[{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE value=VALUES(value)
dataset.on_duplicate_key_update(
value: Sequel.lit('value + VALUES(value)')
).multi_insert(
[{name: 'a', value: 1}, {name: 'b', value: 2}]
)
# INSERT INTO tablename (name, value) VALUES (a, 1), (b, 2)
# ON DUPLICATE KEY UPDATE value=value + VALUES(value)
761 762 763 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 761 def on_duplicate_key_update(*args) clone(:on_duplicate_key_update => args) end |
#quoted_identifier_append(sql, c) ⇒ Object
MySQL uses the nonstandard ` (backtick) for quoting identifiers.
766 767 768 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 766 def quoted_identifier_append(sql, c) sql << '`' << c.to_s.gsub('`', '``') << '`' end |
#supports_cte?(type = :select) ⇒ Boolean
MariaDB 10.2+ and MySQL 8+ support CTEs
771 772 773 774 775 776 777 778 779 780 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 771 def supports_cte?(type=:select) if db.mariadb? type == :select && db.server_version >= 100200 else case type when :select, :update, :delete db.server_version >= 80000 end end end |
#supports_derived_column_lists? ⇒ Boolean
MySQL does not support derived column lists
783 784 785 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 783 def supports_derived_column_lists? false end |
#supports_distinct_on? ⇒ Boolean
MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.
789 790 791 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 789 def supports_distinct_on? true end |
#supports_group_rollup? ⇒ Boolean
MySQL supports GROUP BY WITH ROLLUP (but not CUBE)
794 795 796 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 794 def supports_group_rollup? true end |
#supports_intersect_except? ⇒ Boolean
MariaDB 10.3+ supports INTERSECT or EXCEPT
799 800 801 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 799 def supports_intersect_except? db.mariadb? && db.server_version >= 100300 end |
#supports_limits_in_correlated_subqueries? ⇒ Boolean
MySQL does not support limits in correlated subqueries (or any subqueries that use IN).
804 805 806 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 804 def false end |
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets
809 810 811 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 809 def true end |
#supports_nowait? ⇒ Boolean
MySQL 8+ and MariaDB 10.3+ support NOWAIT.
814 815 816 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 814 def supports_nowait? db.server_version >= (db.mariadb? ? 100300 : 80000) end |
#supports_ordered_distinct_on? ⇒ Boolean
MySQL's DISTINCT ON emulation using GROUP BY does not respect the query's ORDER BY clause.
820 821 822 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 820 def supports_ordered_distinct_on? false end |
#supports_regexp? ⇒ Boolean
MySQL supports pattern matching via regular expressions
825 826 827 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 825 def supports_regexp? true end |
#supports_skip_locked? ⇒ Boolean
MySQL 8+ supports SKIP LOCKED.
830 831 832 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 830 def supports_skip_locked? !db.mariadb? && db.server_version >= 80000 end |
#supports_timestamp_usecs? ⇒ Boolean
Check the database setting for whether fractional timestamps are suppported.
836 837 838 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 836 def db. end |
#supports_window_clause? ⇒ Boolean
MySQL 8+ supports WINDOW clause.
841 842 843 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 841 def supports_window_clause? !db.mariadb? && db.server_version >= 80000 end |
#supports_window_functions? ⇒ Boolean
MariaDB 10.2+ and MySQL 8+ support window functions
846 847 848 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 846 def supports_window_functions? db.server_version >= (db.mariadb? ? 100200 : 80000) end |
#update_ignore ⇒ Object
Sets up the update methods to use UPDATE IGNORE. Useful if you have a unique key and want to just skip updating rows that violate the unique key restriction.
dataset.update_ignore.update(name: 'a', value: 1)
# UPDATE IGNORE tablename SET name = 'a', value = 1
856 857 858 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 856 def update_ignore clone(:update_ignore=>true) end |