module Sequel::MySQL::DatasetMethods
Dataset
methods shared by datasets that use MySQL
databases.
Constants
- MATCH_AGAINST
- MATCH_AGAINST_BOOLEAN
Public Instance Methods
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
# File lib/sequel/adapters/shared/mysql.rb 666 def calc_found_rows 667 clone(:calc_found_rows => true) 668 end
# File lib/sequel/adapters/shared/mysql.rb 600 def complex_expression_sql_append(sql, op, args) 601 case op 602 when :IN, :"NOT IN" 603 ds = args[1] 604 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 605 super(sql, op, [args[0], ds.from_self]) 606 else 607 super 608 end 609 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 610 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 611 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 612 func = ~func if op == :'!~' 613 return literal_append(sql, func) 614 end 615 616 sql << '(' 617 literal_append(sql, args[0]) 618 sql << ' ' 619 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 620 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 621 sql << ' ' 622 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 623 literal_append(sql, args[1]) 624 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 625 sql << " ESCAPE " 626 literal_append(sql, "\\") 627 end 628 sql << ')' 629 when :'||' 630 if args.length > 1 631 sql << "CONCAT" 632 array_sql_append(sql, args) 633 else 634 literal_append(sql, args[0]) 635 end 636 when :'B~' 637 sql << "CAST(~" 638 literal_append(sql, args[0]) 639 sql << " AS SIGNED INTEGER)" 640 else 641 super 642 end 643 end
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.
# File lib/sequel/adapters/shared/mysql.rb 649 def constant_sql_append(sql, constant) 650 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 651 sql << 'CURRENT_TIMESTAMP(6)' 652 else 653 super 654 end 655 end
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)
# File lib/sequel/adapters/shared/mysql.rb 678 def delete_from(*tables) 679 clone(:delete_from=>tables) 680 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 658 def distinct(*args) 659 args.empty? ? super : group(*args) 660 end
Return the results of an EXPLAIN query as a string. Options:
- :extended
-
Use EXPLAIN EXPTENDED instead of EXPLAIN if true.
# File lib/sequel/adapters/shared/mysql.rb 684 def explain(opts=OPTS) 685 # Load the PrettyTable class, needed for explain output 686 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 687 688 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 689 rows = ds.all 690 Sequel::PrettyTable.string(rows, ds.columns) 691 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 699 def full_text_search(cols, terms, opts = OPTS) 700 where(full_text_sql(cols, terms, opts)) 701 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 704 def full_text_sql(cols, terms, opts = OPTS) 705 terms = terms.join(' ') if terms.is_a?(Array) 706 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 707 end
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)
# File lib/sequel/adapters/shared/mysql.rb 726 def insert_ignore 727 clone(:insert_ignore=>true) 728 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 710 def join_type_sql(join_type) 711 if join_type == :straight 712 'STRAIGHT_JOIN' 713 else 714 super 715 end 716 end
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)
# File lib/sequel/adapters/shared/mysql.rb 759 def on_duplicate_key_update(*args) 760 clone(:on_duplicate_key_update => args) 761 end
MySQL
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 764 def quoted_identifier_append(sql, c) 765 sql << '`' << c.to_s.gsub('`', '``') << '`' 766 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 769 def supports_cte?(type=:select) 770 if db.mariadb? 771 type == :select && db.server_version >= 100200 772 else 773 case type 774 when :select, :update, :delete 775 db.server_version >= 80000 776 end 777 end 778 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 781 def supports_derived_column_lists? 782 false 783 end
MySQL
can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.
# File lib/sequel/adapters/shared/mysql.rb 787 def supports_distinct_on? 788 true 789 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 792 def supports_group_rollup? 793 true 794 end
MySQL
does not support INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 797 def supports_intersect_except? 798 false 799 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 807 def supports_modifying_joins? 808 true 809 end
MySQL
8+ supports NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 812 def supports_nowait? 813 !db.mariadb? && db.server_version >= 80000 814 end
MySQL's DISTINCT ON emulation using GROUP BY does not respect the query's ORDER BY clause.
# File lib/sequel/adapters/shared/mysql.rb 818 def supports_ordered_distinct_on? 819 false 820 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 823 def supports_regexp? 824 true 825 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 828 def supports_skip_locked? 829 !db.mariadb? && db.server_version >= 80000 830 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 834 def supports_timestamp_usecs? 835 db.supports_timestamp_usecs? 836 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 839 def supports_window_functions? 840 db.server_version >= (db.mariadb? ? 100200 : 80000) 841 end
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
# File lib/sequel/adapters/shared/mysql.rb 849 def update_ignore 850 clone(:update_ignore=>true) 851 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 856 def check_not_limited!(type) 857 super if type == :truncate || @opts[:offset] 858 end
Consider the first table in the joined dataset is the table to delete from, but include the others for the purposes of selecting rows.
# File lib/sequel/adapters/shared/mysql.rb 862 def delete_from_sql(sql) 863 if joined_dataset? 864 sql << ' ' 865 tables = @opts[:delete_from] || @opts[:from][0..0] 866 source_list_append(sql, tables) 867 sql << ' FROM ' 868 source_list_append(sql, @opts[:from]) 869 select_join_sql(sql) 870 else 871 super 872 end 873 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 886 def insert_ignore_sql(sql) 887 sql << " IGNORE" if opts[:insert_ignore] 888 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 896 def insert_on_duplicate_key_update_sql(sql) 897 if update_cols = opts[:on_duplicate_key_update] 898 update_vals = nil 899 900 if update_cols.empty? 901 update_cols = columns 902 elsif update_cols.last.is_a?(Hash) 903 update_vals = update_cols.last 904 update_cols = update_cols[0..-2] 905 end 906 907 sql << " ON DUPLICATE KEY UPDATE " 908 c = false 909 co = ', ' 910 values = '=VALUES(' 911 endp = ')' 912 update_cols.each do |col| 913 sql << co if c 914 quote_identifier_append(sql, col) 915 sql << values 916 quote_identifier_append(sql, col) 917 sql << endp 918 c ||= true 919 end 920 if update_vals 921 eq = '=' 922 update_vals.map do |col,v| 923 sql << co if c 924 quote_identifier_append(sql, col) 925 sql << eq 926 literal_append(sql, v) 927 c ||= true 928 end 929 end 930 end 931 end
MySQL
doesn't use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 934 def insert_values_sql(sql) 935 values = opts[:values] 936 if values.is_a?(Array) && values.empty? 937 sql << " VALUES ()" 938 else 939 super 940 end 941 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 944 def limit_sql(sql) 945 if l = @opts[:limit] 946 sql << " LIMIT " 947 literal_append(sql, l) 948 end 949 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 954 def literal_blob_append(sql, v) 955 if v.empty? 956 sql << "''" 957 else 958 sql << "0x" << v.unpack("H*").first 959 end 960 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 963 def literal_false 964 '0' 965 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 968 def literal_float(v) 969 if v.infinite? || v.nan? 970 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 971 else 972 super 973 end 974 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 982 def literal_true 983 '1' 984 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 987 def multi_insert_sql_strategy 988 :values 989 end
# File lib/sequel/adapters/shared/mysql.rb 991 def non_sql_option?(key) 992 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 993 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1025 def select_calc_found_rows_sql(sql) 1026 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1027 end
Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.
# File lib/sequel/adapters/shared/mysql.rb 1003 def select_lock_sql(sql) 1004 lock = @opts[:lock] 1005 if lock == :share 1006 if !db.mariadb? && db.server_version >= 80000 1007 sql << ' FOR SHARE' 1008 else 1009 sql << ' LOCK IN SHARE MODE' 1010 end 1011 else 1012 super 1013 end 1014 1015 if lock 1016 if @opts[:skip_locked] 1017 sql << " SKIP LOCKED" 1018 elsif @opts[:nowait] 1019 sql << " NOWAIT" 1020 end 1021 end 1022 end
# File lib/sequel/adapters/shared/mysql.rb 995 def select_only_offset_sql(sql) 996 sql << " LIMIT " 997 literal_append(sql, @opts[:offset]) 998 sql << ",18446744073709551615" 999 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1030 def select_with_sql_base 1031 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1032 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 891 def update_ignore_sql(sql) 892 sql << " IGNORE" if opts[:update_ignore] 893 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1035 def uses_with_rollup? 1036 true 1037 end