Module: Sequel::MySQL::DatasetMethods
- Included in:
- Dataset, Sequel::Mysql2::Dataset, Trilogy::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
722 723 724 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 722 def calc_found_rows clone(:calc_found_rows => true) end |
#complex_expression_sql_append(sql, op, args) ⇒ Object
656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 656 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.
705 706 707 708 709 710 711 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 705 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)
734 735 736 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 734 def delete_from(*tables) clone(:delete_from=>tables) end |
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
714 715 716 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 714 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.
740 741 742 743 744 745 746 747 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 740 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.
750 751 752 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 750 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
Adds full text filter
755 756 757 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 755 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.
760 761 762 763 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 760 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)
773 774 775 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 773 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)
806 807 808 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 806 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.
811 812 813 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 811 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
816 817 818 819 820 821 822 823 824 825 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 816 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
828 829 830 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 828 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.
834 835 836 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 834 def supports_distinct_on? true end |
#supports_group_rollup? ⇒ Boolean
MySQL supports GROUP BY WITH ROLLUP (but not CUBE)
839 840 841 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 839 def supports_group_rollup? true end |
#supports_intersect_except? ⇒ Boolean
MariaDB 10.3+ supports INTERSECT or EXCEPT
844 845 846 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 844 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).
849 850 851 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 849 def false end |
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets
854 855 856 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 854 def true end |
#supports_nowait? ⇒ Boolean
MySQL 8+ and MariaDB 10.3+ support NOWAIT.
859 860 861 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 859 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.
865 866 867 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 865 def supports_ordered_distinct_on? false end |
#supports_regexp? ⇒ Boolean
MySQL supports pattern matching via regular expressions
870 871 872 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 870 def supports_regexp? true end |
#supports_skip_locked? ⇒ Boolean
MySQL 8+ supports SKIP LOCKED.
875 876 877 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 875 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.
881 882 883 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 881 def db. end |
#supports_window_clause? ⇒ Boolean
MySQL 8+ supports WINDOW clause.
886 887 888 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 886 def supports_window_clause? !db.mariadb? && db.server_version >= 80000 end |
#supports_window_functions? ⇒ Boolean
MariaDB 10.2+ and MySQL 8+ support window functions
891 892 893 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 891 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
901 902 903 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 901 def update_ignore clone(:update_ignore=>true) end |