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 757 def calc_found_rows 758 clone(:calc_found_rows => true) 759 end
# File lib/sequel/adapters/shared/mysql.rb 691 def complex_expression_sql_append(sql, op, args) 692 case op 693 when :IN, :"NOT IN" 694 ds = args[1] 695 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 696 super(sql, op, [args[0], ds.from_self]) 697 else 698 super 699 end 700 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 701 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 702 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 703 func = ~func if op == :'!~' 704 return literal_append(sql, func) 705 end 706 707 sql << '(' 708 literal_append(sql, args[0]) 709 sql << ' ' 710 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 711 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 712 sql << ' ' 713 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 714 literal_append(sql, args[1]) 715 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 716 sql << " ESCAPE " 717 literal_append(sql, "\\") 718 end 719 sql << ')' 720 when :'||' 721 if args.length > 1 722 sql << "CONCAT" 723 array_sql_append(sql, args) 724 else 725 literal_append(sql, args[0]) 726 end 727 when :'B~' 728 sql << "CAST(~" 729 literal_append(sql, args[0]) 730 sql << " AS SIGNED INTEGER)" 731 else 732 super 733 end 734 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 740 def constant_sql_append(sql, constant) 741 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 742 sql << 'CURRENT_TIMESTAMP(6)' 743 else 744 super 745 end 746 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 769 def delete_from(*tables) 770 clone(:delete_from=>tables) 771 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 749 def distinct(*args) 750 args.empty? ? super : group(*args) 751 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 775 def explain(opts=OPTS) 776 # Load the PrettyTable class, needed for explain output 777 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 778 779 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 780 rows = ds.all 781 Sequel::PrettyTable.string(rows, ds.columns) 782 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 790 def full_text_search(cols, terms, opts = OPTS) 791 where(full_text_sql(cols, terms, opts)) 792 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 795 def full_text_sql(cols, terms, opts = OPTS) 796 terms = terms.join(' ') if terms.is_a?(Array) 797 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 798 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 808 def insert_ignore 809 clone(:insert_ignore=>true) 810 end
Support insert select for associations, so that the model code can use returning instead of a separate query.
# File lib/sequel/adapters/shared/mysql.rb 814 def insert_select(*values) 815 return unless supports_insert_select? 816 # Handle case where query does not return a row 817 server?(:default).with_sql_first(insert_select_sql(*values)) || false 818 end
The SQL
to use for an insert_select
, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
# File lib/sequel/adapters/shared/mysql.rb 822 def insert_select_sql(*values) 823 ds = opts[:returning] ? self : returning 824 ds.insert_sql(*values) 825 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 856 def on_duplicate_key_update(*args) 857 clone(:on_duplicate_key_update => args) 858 end
MySQL
uses the nonstandard ‘ (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 861 def quoted_identifier_append(sql, c) 862 sql << '`' << c.to_s.gsub('`', '``') << '`' 863 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 866 def supports_cte?(type=:select) 867 if db.mariadb? 868 type == :select && db.server_version >= 100200 869 else 870 case type 871 when :select, :update, :delete 872 db.server_version >= 80000 873 end 874 end 875 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 878 def supports_derived_column_lists? 879 false 880 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 884 def supports_distinct_on? 885 true 886 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 889 def supports_group_rollup? 890 true 891 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 894 def supports_intersect_except? 895 db.mariadb? && db.server_version >= 100300 896 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 904 def supports_modifying_joins? 905 true 906 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 909 def supports_nowait? 910 db.server_version >= (db.mariadb? ? 100300 : 80000) 911 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 915 def supports_ordered_distinct_on? 916 false 917 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 920 def supports_regexp? 921 true 922 end
MariaDB 10.5.0 supports INSERT RETURNING.
# File lib/sequel/adapters/shared/mysql.rb 925 def supports_returning?(type) 926 (type == :insert && db.mariadb? && db.adapter_scheme != :jdbc) ? (db.server_version >= 100500) : false 927 end
MySQL
8+ and MariaDB 10.6+ support SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 930 def supports_skip_locked? 931 db.server_version >= (db.mariadb? ? 100600 : 80000) 932 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 936 def supports_timestamp_usecs? 937 db.supports_timestamp_usecs? 938 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 941 def supports_window_clause? 942 !db.mariadb? && db.server_version >= 80000 943 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 946 def supports_window_functions? 947 db.server_version >= (db.mariadb? ? 100200 : 80000) 948 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 956 def update_ignore 957 clone(:update_ignore=>true) 958 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 963 def check_not_limited!(type) 964 super if type == :truncate || @opts[:offset] 965 end
The strftime format to use when literalizing time (Sequel::SQLTime
) values.
# File lib/sequel/adapters/shared/mysql.rb 968 def default_time_format 969 db.supports_timestamp_usecs? ? super : "'%H:%M:%S'" 970 end
The strftime format to use when literalizing timestamp (Time/DateTime) values.
# File lib/sequel/adapters/shared/mysql.rb 973 def default_timestamp_format 974 db.supports_timestamp_usecs? ? super : "'%Y-%m-%d %H:%M:%S'" 975 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 979 def delete_from_sql(sql) 980 if joined_dataset? 981 sql << ' ' 982 tables = @opts[:delete_from] || @opts[:from][0..0] 983 source_list_append(sql, tables) 984 sql << ' FROM ' 985 source_list_append(sql, @opts[:from]) 986 select_join_sql(sql) 987 else 988 super 989 end 990 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 1003 def insert_ignore_sql(sql) 1004 sql << " IGNORE" if opts[:insert_ignore] 1005 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 1013 def insert_on_duplicate_key_update_sql(sql) 1014 if update_cols = opts[:on_duplicate_key_update] 1015 update_vals = nil 1016 1017 if update_cols.empty? 1018 update_cols = columns 1019 elsif update_cols.last.is_a?(Hash) 1020 update_vals = update_cols.last 1021 update_cols = update_cols[0..-2] 1022 end 1023 1024 sql << " ON DUPLICATE KEY UPDATE " 1025 c = false 1026 co = ', ' 1027 values = '=VALUES(' 1028 endp = ')' 1029 update_cols.each do |col| 1030 sql << co if c 1031 quote_identifier_append(sql, col) 1032 sql << values 1033 quote_identifier_append(sql, col) 1034 sql << endp 1035 c ||= true 1036 end 1037 if update_vals 1038 eq = '=' 1039 update_vals.map do |col,v| 1040 sql << co if c 1041 quote_identifier_append(sql, col) 1042 sql << eq 1043 literal_append(sql, v) 1044 c ||= true 1045 end 1046 end 1047 end 1048 end
MySQL
doesn’t use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 1051 def insert_values_sql(sql) 1052 values = opts[:values] 1053 if values.is_a?(Array) && values.empty? 1054 sql << " VALUES ()" 1055 else 1056 super 1057 end 1058 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 1061 def join_type_sql(join_type) 1062 if join_type == :straight 1063 'STRAIGHT_JOIN' 1064 else 1065 super 1066 end 1067 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 1070 def limit_sql(sql) 1071 if l = @opts[:limit] 1072 sql << " LIMIT " 1073 literal_append(sql, l) 1074 end 1075 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 1080 def literal_blob_append(sql, v) 1081 if v.empty? 1082 sql << "''" 1083 else 1084 sql << "0x" << v.unpack("H*").first 1085 end 1086 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1089 def literal_false 1090 '0' 1091 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 1094 def literal_float(v) 1095 if v.infinite? || v.nan? 1096 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 1097 else 1098 super 1099 end 1100 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1108 def literal_true 1109 '1' 1110 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1113 def multi_insert_sql_strategy 1114 :values 1115 end
# File lib/sequel/adapters/shared/mysql.rb 1117 def non_sql_option?(key) 1118 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1119 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1122 def requires_emulating_nulls_first? 1123 true 1124 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1156 def select_calc_found_rows_sql(sql) 1157 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1158 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 1134 def select_lock_sql(sql) 1135 lock = @opts[:lock] 1136 if lock == :share 1137 if !db.mariadb? && db.server_version >= 80000 1138 sql << ' FOR SHARE' 1139 else 1140 sql << ' LOCK IN SHARE MODE' 1141 end 1142 else 1143 super 1144 end 1145 1146 if lock 1147 if @opts[:skip_locked] 1148 sql << " SKIP LOCKED" 1149 elsif @opts[:nowait] 1150 sql << " NOWAIT" 1151 end 1152 end 1153 end
# File lib/sequel/adapters/shared/mysql.rb 1126 def select_only_offset_sql(sql) 1127 sql << " LIMIT " 1128 literal_append(sql, @opts[:offset]) 1129 sql << ",18446744073709551615" 1130 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1161 def select_with_sql_base 1162 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1163 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 1008 def update_ignore_sql(sql) 1009 sql << " IGNORE" if opts[:update_ignore] 1010 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1166 def uses_with_rollup? 1167 true 1168 end