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 694 def calc_found_rows 695 clone(:calc_found_rows => true) 696 end
# File lib/sequel/adapters/shared/mysql.rb 628 def complex_expression_sql_append(sql, op, args) 629 case op 630 when :IN, :"NOT IN" 631 ds = args[1] 632 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 633 super(sql, op, [args[0], ds.from_self]) 634 else 635 super 636 end 637 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 638 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 639 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 640 func = ~func if op == :'!~' 641 return literal_append(sql, func) 642 end 643 644 sql << '(' 645 literal_append(sql, args[0]) 646 sql << ' ' 647 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 648 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 649 sql << ' ' 650 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 651 literal_append(sql, args[1]) 652 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 653 sql << " ESCAPE " 654 literal_append(sql, "\\") 655 end 656 sql << ')' 657 when :'||' 658 if args.length > 1 659 sql << "CONCAT" 660 array_sql_append(sql, args) 661 else 662 literal_append(sql, args[0]) 663 end 664 when :'B~' 665 sql << "CAST(~" 666 literal_append(sql, args[0]) 667 sql << " AS SIGNED INTEGER)" 668 else 669 super 670 end 671 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 677 def constant_sql_append(sql, constant) 678 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 679 sql << 'CURRENT_TIMESTAMP(6)' 680 else 681 super 682 end 683 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 706 def delete_from(*tables) 707 clone(:delete_from=>tables) 708 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 686 def distinct(*args) 687 args.empty? ? super : group(*args) 688 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 712 def explain(opts=OPTS) 713 # Load the PrettyTable class, needed for explain output 714 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 715 716 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 717 rows = ds.all 718 Sequel::PrettyTable.string(rows, ds.columns) 719 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 727 def full_text_search(cols, terms, opts = OPTS) 728 where(full_text_sql(cols, terms, opts)) 729 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 732 def full_text_sql(cols, terms, opts = OPTS) 733 terms = terms.join(' ') if terms.is_a?(Array) 734 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 735 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 745 def insert_ignore 746 clone(:insert_ignore=>true) 747 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 778 def on_duplicate_key_update(*args) 779 clone(:on_duplicate_key_update => args) 780 end
MySQL
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 783 def quoted_identifier_append(sql, c) 784 sql << '`' << c.to_s.gsub('`', '``') << '`' 785 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 788 def supports_cte?(type=:select) 789 if db.mariadb? 790 type == :select && db.server_version >= 100200 791 else 792 case type 793 when :select, :update, :delete 794 db.server_version >= 80000 795 end 796 end 797 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 800 def supports_derived_column_lists? 801 false 802 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 806 def supports_distinct_on? 807 true 808 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 811 def supports_group_rollup? 812 true 813 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 816 def supports_intersect_except? 817 db.mariadb? && db.server_version >= 100300 818 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 826 def supports_modifying_joins? 827 true 828 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 831 def supports_nowait? 832 db.server_version >= (db.mariadb? ? 100300 : 80000) 833 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 837 def supports_ordered_distinct_on? 838 false 839 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 842 def supports_regexp? 843 true 844 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 847 def supports_skip_locked? 848 !db.mariadb? && db.server_version >= 80000 849 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 853 def supports_timestamp_usecs? 854 db.supports_timestamp_usecs? 855 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 858 def supports_window_clause? 859 !db.mariadb? && db.server_version >= 80000 860 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 863 def supports_window_functions? 864 db.server_version >= (db.mariadb? ? 100200 : 80000) 865 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 873 def update_ignore 874 clone(:update_ignore=>true) 875 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 880 def check_not_limited!(type) 881 super if type == :truncate || @opts[:offset] 882 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 886 def delete_from_sql(sql) 887 if joined_dataset? 888 sql << ' ' 889 tables = @opts[:delete_from] || @opts[:from][0..0] 890 source_list_append(sql, tables) 891 sql << ' FROM ' 892 source_list_append(sql, @opts[:from]) 893 select_join_sql(sql) 894 else 895 super 896 end 897 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 910 def insert_ignore_sql(sql) 911 sql << " IGNORE" if opts[:insert_ignore] 912 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 920 def insert_on_duplicate_key_update_sql(sql) 921 if update_cols = opts[:on_duplicate_key_update] 922 update_vals = nil 923 924 if update_cols.empty? 925 update_cols = columns 926 elsif update_cols.last.is_a?(Hash) 927 update_vals = update_cols.last 928 update_cols = update_cols[0..-2] 929 end 930 931 sql << " ON DUPLICATE KEY UPDATE " 932 c = false 933 co = ', ' 934 values = '=VALUES(' 935 endp = ')' 936 update_cols.each do |col| 937 sql << co if c 938 quote_identifier_append(sql, col) 939 sql << values 940 quote_identifier_append(sql, col) 941 sql << endp 942 c ||= true 943 end 944 if update_vals 945 eq = '=' 946 update_vals.map do |col,v| 947 sql << co if c 948 quote_identifier_append(sql, col) 949 sql << eq 950 literal_append(sql, v) 951 c ||= true 952 end 953 end 954 end 955 end
MySQL
doesn't use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 958 def insert_values_sql(sql) 959 values = opts[:values] 960 if values.is_a?(Array) && values.empty? 961 sql << " VALUES ()" 962 else 963 super 964 end 965 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 968 def join_type_sql(join_type) 969 if join_type == :straight 970 'STRAIGHT_JOIN' 971 else 972 super 973 end 974 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 977 def limit_sql(sql) 978 if l = @opts[:limit] 979 sql << " LIMIT " 980 literal_append(sql, l) 981 end 982 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 987 def literal_blob_append(sql, v) 988 if v.empty? 989 sql << "''" 990 else 991 sql << "0x" << v.unpack("H*").first 992 end 993 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 996 def literal_false 997 '0' 998 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 1001 def literal_float(v) 1002 if v.infinite? || v.nan? 1003 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 1004 else 1005 super 1006 end 1007 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1015 def literal_true 1016 '1' 1017 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1020 def multi_insert_sql_strategy 1021 :values 1022 end
# File lib/sequel/adapters/shared/mysql.rb 1024 def non_sql_option?(key) 1025 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1026 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1029 def requires_emulating_nulls_first? 1030 true 1031 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1063 def select_calc_found_rows_sql(sql) 1064 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1065 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 1041 def select_lock_sql(sql) 1042 lock = @opts[:lock] 1043 if lock == :share 1044 if !db.mariadb? && db.server_version >= 80000 1045 sql << ' FOR SHARE' 1046 else 1047 sql << ' LOCK IN SHARE MODE' 1048 end 1049 else 1050 super 1051 end 1052 1053 if lock 1054 if @opts[:skip_locked] 1055 sql << " SKIP LOCKED" 1056 elsif @opts[:nowait] 1057 sql << " NOWAIT" 1058 end 1059 end 1060 end
# File lib/sequel/adapters/shared/mysql.rb 1033 def select_only_offset_sql(sql) 1034 sql << " LIMIT " 1035 literal_append(sql, @opts[:offset]) 1036 sql << ",18446744073709551615" 1037 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1068 def select_with_sql_base 1069 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1070 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 915 def update_ignore_sql(sql) 916 sql << " IGNORE" if opts[:update_ignore] 917 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1073 def uses_with_rollup? 1074 true 1075 end