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 708 def calc_found_rows 709 clone(:calc_found_rows => true) 710 end
# File lib/sequel/adapters/shared/mysql.rb 642 def complex_expression_sql_append(sql, op, args) 643 case op 644 when :IN, :"NOT IN" 645 ds = args[1] 646 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 647 super(sql, op, [args[0], ds.from_self]) 648 else 649 super 650 end 651 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 652 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 653 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 654 func = ~func if op == :'!~' 655 return literal_append(sql, func) 656 end 657 658 sql << '(' 659 literal_append(sql, args[0]) 660 sql << ' ' 661 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 662 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 663 sql << ' ' 664 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 665 literal_append(sql, args[1]) 666 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 667 sql << " ESCAPE " 668 literal_append(sql, "\\") 669 end 670 sql << ')' 671 when :'||' 672 if args.length > 1 673 sql << "CONCAT" 674 array_sql_append(sql, args) 675 else 676 literal_append(sql, args[0]) 677 end 678 when :'B~' 679 sql << "CAST(~" 680 literal_append(sql, args[0]) 681 sql << " AS SIGNED INTEGER)" 682 else 683 super 684 end 685 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 691 def constant_sql_append(sql, constant) 692 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 693 sql << 'CURRENT_TIMESTAMP(6)' 694 else 695 super 696 end 697 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 720 def delete_from(*tables) 721 clone(:delete_from=>tables) 722 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 700 def distinct(*args) 701 args.empty? ? super : group(*args) 702 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 726 def explain(opts=OPTS) 727 # Load the PrettyTable class, needed for explain output 728 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 729 730 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 731 rows = ds.all 732 Sequel::PrettyTable.string(rows, ds.columns) 733 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 741 def full_text_search(cols, terms, opts = OPTS) 742 where(full_text_sql(cols, terms, opts)) 743 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 746 def full_text_sql(cols, terms, opts = OPTS) 747 terms = terms.join(' ') if terms.is_a?(Array) 748 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 749 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 759 def insert_ignore 760 clone(:insert_ignore=>true) 761 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 792 def on_duplicate_key_update(*args) 793 clone(:on_duplicate_key_update => args) 794 end
MySQL
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 797 def quoted_identifier_append(sql, c) 798 sql << '`' << c.to_s.gsub('`', '``') << '`' 799 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 802 def supports_cte?(type=:select) 803 if db.mariadb? 804 type == :select && db.server_version >= 100200 805 else 806 case type 807 when :select, :update, :delete 808 db.server_version >= 80000 809 end 810 end 811 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 814 def supports_derived_column_lists? 815 false 816 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 820 def supports_distinct_on? 821 true 822 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 825 def supports_group_rollup? 826 true 827 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 830 def supports_intersect_except? 831 db.mariadb? && db.server_version >= 100300 832 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 840 def supports_modifying_joins? 841 true 842 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 845 def supports_nowait? 846 db.server_version >= (db.mariadb? ? 100300 : 80000) 847 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 851 def supports_ordered_distinct_on? 852 false 853 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 856 def supports_regexp? 857 true 858 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 861 def supports_skip_locked? 862 !db.mariadb? && db.server_version >= 80000 863 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 867 def supports_timestamp_usecs? 868 db.supports_timestamp_usecs? 869 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 872 def supports_window_clause? 873 !db.mariadb? && db.server_version >= 80000 874 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 877 def supports_window_functions? 878 db.server_version >= (db.mariadb? ? 100200 : 80000) 879 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 887 def update_ignore 888 clone(:update_ignore=>true) 889 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 894 def check_not_limited!(type) 895 super if type == :truncate || @opts[:offset] 896 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 900 def delete_from_sql(sql) 901 if joined_dataset? 902 sql << ' ' 903 tables = @opts[:delete_from] || @opts[:from][0..0] 904 source_list_append(sql, tables) 905 sql << ' FROM ' 906 source_list_append(sql, @opts[:from]) 907 select_join_sql(sql) 908 else 909 super 910 end 911 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 924 def insert_ignore_sql(sql) 925 sql << " IGNORE" if opts[:insert_ignore] 926 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 934 def insert_on_duplicate_key_update_sql(sql) 935 if update_cols = opts[:on_duplicate_key_update] 936 update_vals = nil 937 938 if update_cols.empty? 939 update_cols = columns 940 elsif update_cols.last.is_a?(Hash) 941 update_vals = update_cols.last 942 update_cols = update_cols[0..-2] 943 end 944 945 sql << " ON DUPLICATE KEY UPDATE " 946 c = false 947 co = ', ' 948 values = '=VALUES(' 949 endp = ')' 950 update_cols.each do |col| 951 sql << co if c 952 quote_identifier_append(sql, col) 953 sql << values 954 quote_identifier_append(sql, col) 955 sql << endp 956 c ||= true 957 end 958 if update_vals 959 eq = '=' 960 update_vals.map do |col,v| 961 sql << co if c 962 quote_identifier_append(sql, col) 963 sql << eq 964 literal_append(sql, v) 965 c ||= true 966 end 967 end 968 end 969 end
MySQL
doesn't use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 972 def insert_values_sql(sql) 973 values = opts[:values] 974 if values.is_a?(Array) && values.empty? 975 sql << " VALUES ()" 976 else 977 super 978 end 979 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 982 def join_type_sql(join_type) 983 if join_type == :straight 984 'STRAIGHT_JOIN' 985 else 986 super 987 end 988 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 991 def limit_sql(sql) 992 if l = @opts[:limit] 993 sql << " LIMIT " 994 literal_append(sql, l) 995 end 996 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 1001 def literal_blob_append(sql, v) 1002 if v.empty? 1003 sql << "''" 1004 else 1005 sql << "0x" << v.unpack("H*").first 1006 end 1007 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1010 def literal_false 1011 '0' 1012 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 1015 def literal_float(v) 1016 if v.infinite? || v.nan? 1017 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 1018 else 1019 super 1020 end 1021 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1029 def literal_true 1030 '1' 1031 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1034 def multi_insert_sql_strategy 1035 :values 1036 end
# File lib/sequel/adapters/shared/mysql.rb 1038 def non_sql_option?(key) 1039 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1040 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1043 def requires_emulating_nulls_first? 1044 true 1045 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1077 def select_calc_found_rows_sql(sql) 1078 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1079 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 1055 def select_lock_sql(sql) 1056 lock = @opts[:lock] 1057 if lock == :share 1058 if !db.mariadb? && db.server_version >= 80000 1059 sql << ' FOR SHARE' 1060 else 1061 sql << ' LOCK IN SHARE MODE' 1062 end 1063 else 1064 super 1065 end 1066 1067 if lock 1068 if @opts[:skip_locked] 1069 sql << " SKIP LOCKED" 1070 elsif @opts[:nowait] 1071 sql << " NOWAIT" 1072 end 1073 end 1074 end
# File lib/sequel/adapters/shared/mysql.rb 1047 def select_only_offset_sql(sql) 1048 sql << " LIMIT " 1049 literal_append(sql, @opts[:offset]) 1050 sql << ",18446744073709551615" 1051 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1082 def select_with_sql_base 1083 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1084 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 929 def update_ignore_sql(sql) 930 sql << " IGNORE" if opts[:update_ignore] 931 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1087 def uses_with_rollup? 1088 true 1089 end