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 696 def calc_found_rows 697 clone(:calc_found_rows => true) 698 end
# File lib/sequel/adapters/shared/mysql.rb 630 def complex_expression_sql_append(sql, op, args) 631 case op 632 when :IN, :"NOT IN" 633 ds = args[1] 634 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 635 super(sql, op, [args[0], ds.from_self]) 636 else 637 super 638 end 639 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 640 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 641 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 642 func = ~func if op == :'!~' 643 return literal_append(sql, func) 644 end 645 646 sql << '(' 647 literal_append(sql, args[0]) 648 sql << ' ' 649 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 650 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 651 sql << ' ' 652 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 653 literal_append(sql, args[1]) 654 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 655 sql << " ESCAPE " 656 literal_append(sql, "\\") 657 end 658 sql << ')' 659 when :'||' 660 if args.length > 1 661 sql << "CONCAT" 662 array_sql_append(sql, args) 663 else 664 literal_append(sql, args[0]) 665 end 666 when :'B~' 667 sql << "CAST(~" 668 literal_append(sql, args[0]) 669 sql << " AS SIGNED INTEGER)" 670 else 671 super 672 end 673 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 679 def constant_sql_append(sql, constant) 680 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 681 sql << 'CURRENT_TIMESTAMP(6)' 682 else 683 super 684 end 685 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 708 def delete_from(*tables) 709 clone(:delete_from=>tables) 710 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 688 def distinct(*args) 689 args.empty? ? super : group(*args) 690 end
Return the results of an EXPLAIN query as a string. Options:
- :extended
-
Use EXPLAIN EXTENDED instead of EXPLAIN if true.
# File lib/sequel/adapters/shared/mysql.rb 714 def explain(opts=OPTS) 715 # Load the PrettyTable class, needed for explain output 716 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 717 718 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 719 rows = ds.all 720 Sequel::PrettyTable.string(rows, ds.columns) 721 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 729 def full_text_search(cols, terms, opts = OPTS) 730 where(full_text_sql(cols, terms, opts)) 731 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 734 def full_text_sql(cols, terms, opts = OPTS) 735 terms = terms.join(' ') if terms.is_a?(Array) 736 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 737 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 747 def insert_ignore 748 clone(:insert_ignore=>true) 749 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 780 def on_duplicate_key_update(*args) 781 clone(:on_duplicate_key_update => args) 782 end
MySQL
uses the nonstandard ‘ (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 785 def quoted_identifier_append(sql, c) 786 sql << '`' << c.to_s.gsub('`', '``') << '`' 787 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 790 def supports_cte?(type=:select) 791 if db.mariadb? 792 type == :select && db.server_version >= 100200 793 else 794 case type 795 when :select, :update, :delete 796 db.server_version >= 80000 797 end 798 end 799 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 802 def supports_derived_column_lists? 803 false 804 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 808 def supports_distinct_on? 809 true 810 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 813 def supports_group_rollup? 814 true 815 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 818 def supports_intersect_except? 819 db.mariadb? && db.server_version >= 100300 820 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 828 def supports_modifying_joins? 829 true 830 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 833 def supports_nowait? 834 db.server_version >= (db.mariadb? ? 100300 : 80000) 835 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 839 def supports_ordered_distinct_on? 840 false 841 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 844 def supports_regexp? 845 true 846 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 849 def supports_skip_locked? 850 !db.mariadb? && db.server_version >= 80000 851 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 855 def supports_timestamp_usecs? 856 db.supports_timestamp_usecs? 857 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 860 def supports_window_clause? 861 !db.mariadb? && db.server_version >= 80000 862 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 865 def supports_window_functions? 866 db.server_version >= (db.mariadb? ? 100200 : 80000) 867 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 875 def update_ignore 876 clone(:update_ignore=>true) 877 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 882 def check_not_limited!(type) 883 super if type == :truncate || @opts[:offset] 884 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 888 def delete_from_sql(sql) 889 if joined_dataset? 890 sql << ' ' 891 tables = @opts[:delete_from] || @opts[:from][0..0] 892 source_list_append(sql, tables) 893 sql << ' FROM ' 894 source_list_append(sql, @opts[:from]) 895 select_join_sql(sql) 896 else 897 super 898 end 899 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 912 def insert_ignore_sql(sql) 913 sql << " IGNORE" if opts[:insert_ignore] 914 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 922 def insert_on_duplicate_key_update_sql(sql) 923 if update_cols = opts[:on_duplicate_key_update] 924 update_vals = nil 925 926 if update_cols.empty? 927 update_cols = columns 928 elsif update_cols.last.is_a?(Hash) 929 update_vals = update_cols.last 930 update_cols = update_cols[0..-2] 931 end 932 933 sql << " ON DUPLICATE KEY UPDATE " 934 c = false 935 co = ', ' 936 values = '=VALUES(' 937 endp = ')' 938 update_cols.each do |col| 939 sql << co if c 940 quote_identifier_append(sql, col) 941 sql << values 942 quote_identifier_append(sql, col) 943 sql << endp 944 c ||= true 945 end 946 if update_vals 947 eq = '=' 948 update_vals.map do |col,v| 949 sql << co if c 950 quote_identifier_append(sql, col) 951 sql << eq 952 literal_append(sql, v) 953 c ||= true 954 end 955 end 956 end 957 end
MySQL
doesn’t use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 960 def insert_values_sql(sql) 961 values = opts[:values] 962 if values.is_a?(Array) && values.empty? 963 sql << " VALUES ()" 964 else 965 super 966 end 967 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 970 def join_type_sql(join_type) 971 if join_type == :straight 972 'STRAIGHT_JOIN' 973 else 974 super 975 end 976 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 979 def limit_sql(sql) 980 if l = @opts[:limit] 981 sql << " LIMIT " 982 literal_append(sql, l) 983 end 984 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 989 def literal_blob_append(sql, v) 990 if v.empty? 991 sql << "''" 992 else 993 sql << "0x" << v.unpack("H*").first 994 end 995 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 998 def literal_false 999 '0' 1000 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 1003 def literal_float(v) 1004 if v.infinite? || v.nan? 1005 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 1006 else 1007 super 1008 end 1009 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1017 def literal_true 1018 '1' 1019 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1022 def multi_insert_sql_strategy 1023 :values 1024 end
# File lib/sequel/adapters/shared/mysql.rb 1026 def non_sql_option?(key) 1027 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1028 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1031 def requires_emulating_nulls_first? 1032 true 1033 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1065 def select_calc_found_rows_sql(sql) 1066 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1067 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 1043 def select_lock_sql(sql) 1044 lock = @opts[:lock] 1045 if lock == :share 1046 if !db.mariadb? && db.server_version >= 80000 1047 sql << ' FOR SHARE' 1048 else 1049 sql << ' LOCK IN SHARE MODE' 1050 end 1051 else 1052 super 1053 end 1054 1055 if lock 1056 if @opts[:skip_locked] 1057 sql << " SKIP LOCKED" 1058 elsif @opts[:nowait] 1059 sql << " NOWAIT" 1060 end 1061 end 1062 end
# File lib/sequel/adapters/shared/mysql.rb 1035 def select_only_offset_sql(sql) 1036 sql << " LIMIT " 1037 literal_append(sql, @opts[:offset]) 1038 sql << ",18446744073709551615" 1039 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1070 def select_with_sql_base 1071 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1072 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 917 def update_ignore_sql(sql) 918 sql << " IGNORE" if opts[:update_ignore] 919 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1075 def uses_with_rollup? 1076 true 1077 end