module Sequel::MySQL::DatasetMethods

Dataset methods shared by datasets that use MySQL databases.

Constants

MATCH_AGAINST
MATCH_AGAINST_BOOLEAN

Public Instance Methods

calc_found_rows() click to toggle source

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
761 def calc_found_rows
762   clone(:calc_found_rows => true)
763 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
695 def complex_expression_sql_append(sql, op, args)
696   case op
697   when :IN, :"NOT IN"
698     ds = args[1]
699     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
700       super(sql, op, [args[0], ds.from_self])
701     else
702       super
703     end
704   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
705     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
706       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
707       func = ~func if op == :'!~'
708       return literal_append(sql, func)
709     end
710 
711     sql << '('
712     literal_append(sql, args[0])
713     sql << ' '
714     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
715     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
716     sql << ' '
717     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
718     literal_append(sql, args[1])
719     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
720       sql << " ESCAPE "
721       literal_append(sql, "\\")
722     end
723     sql << ')'
724   when :'||'
725     if args.length > 1
726       sql << "CONCAT"
727       array_sql_append(sql, args)
728     else
729       literal_append(sql, args[0])
730     end
731   when :'B~'
732     sql << "CAST(~"
733     literal_append(sql, args[0])
734     sql << " AS SIGNED INTEGER)"
735   else
736     super
737   end
738 end
constant_sql_append(sql, constant) click to toggle source

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.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
744 def constant_sql_append(sql, constant)
745   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
746     sql << 'CURRENT_TIMESTAMP(6)'
747   else
748     super
749   end
750 end
delete_from(*tables) click to toggle source

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
773 def delete_from(*tables)
774   clone(:delete_from=>tables)
775 end
distinct(*args) click to toggle source

Use GROUP BY instead of DISTINCT ON if arguments are provided.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
753 def distinct(*args)
754   args.empty? ? super : group(*args)
755 end
explain(opts=OPTS) click to toggle source

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
779 def explain(opts=OPTS)
780   # Load the PrettyTable class, needed for explain output
781   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
782 
783   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
784   rows = ds.all
785   Sequel::PrettyTable.string(rows, ds.columns)
786 end
for_share() click to toggle source

Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.

    # File lib/sequel/adapters/shared/mysql.rb
789 def for_share
790   lock_style(:share)
791 end
full_text_sql(cols, terms, opts = OPTS) click to toggle source

MySQL specific full text search syntax.

    # File lib/sequel/adapters/shared/mysql.rb
799 def full_text_sql(cols, terms, opts = OPTS)
800   terms = terms.join(' ') if terms.is_a?(Array)
801   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
802 end
insert_ignore() click to toggle source

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
812 def insert_ignore
813   clone(:insert_ignore=>true)
814 end
insert_select(*values) click to toggle source

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
818 def insert_select(*values)
819   return unless supports_insert_select?
820   # Handle case where query does not return a row
821   server?(:default).with_sql_first(insert_select_sql(*values)) || false
822 end
insert_select_sql(*values) click to toggle source

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
826 def insert_select_sql(*values)
827   ds = opts[:returning] ? self : returning
828   ds.insert_sql(*values)
829 end
on_duplicate_key_update(*args) click to toggle source

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
860 def on_duplicate_key_update(*args)
861   clone(:on_duplicate_key_update => args)
862 end
quoted_identifier_append(sql, c) click to toggle source

MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.

    # File lib/sequel/adapters/shared/mysql.rb
865 def quoted_identifier_append(sql, c)
866   sql << '`' << c.to_s.gsub('`', '``') << '`'
867 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
870 def supports_cte?(type=:select)
871   if db.mariadb?
872     type == :select && db.server_version >= 100200
873   else
874     case type
875     when :select, :update, :delete
876       db.server_version >= 80000
877     end
878   end
879 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
882 def supports_derived_column_lists?
883   false
884 end
supports_distinct_on?() click to toggle source

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
888 def supports_distinct_on?
889   true
890 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
893 def supports_group_rollup?
894   true
895 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
898 def supports_intersect_except?
899   db.mariadb? && db.server_version >= 100300
900 end
supports_limits_in_correlated_subqueries?() click to toggle source

MySQL does not support limits in correlated subqueries (or any subqueries that use IN).

    # File lib/sequel/adapters/shared/mysql.rb
903 def supports_limits_in_correlated_subqueries?
904   false
905 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
908 def supports_modifying_joins?
909   true
910 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
913 def supports_nowait?
914   db.server_version >= (db.mariadb? ? 100300 : 80000)
915 end
supports_ordered_distinct_on?() click to toggle source

MySQL’s DISTINCT ON emulation using GROUP BY does not respect the query’s ORDER BY clause.

    # File lib/sequel/adapters/shared/mysql.rb
919 def supports_ordered_distinct_on?
920   false
921 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
924 def supports_regexp?
925   true
926 end
supports_returning?(type) click to toggle source

MariaDB 10.5.0 supports INSERT RETURNING.

    # File lib/sequel/adapters/shared/mysql.rb
929 def supports_returning?(type)
930   (type == :insert && db.mariadb? && db.adapter_scheme != :jdbc) ? (db.server_version >= 100500) : false
931 end
supports_skip_locked?() click to toggle source

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
934 def supports_skip_locked?
935   db.server_version >= (db.mariadb? ? 100600 : 80000)
936 end
supports_timestamp_usecs?() click to toggle source

Check the database setting for whether fractional timestamps are suppported.

    # File lib/sequel/adapters/shared/mysql.rb
940 def supports_timestamp_usecs?
941   db.supports_timestamp_usecs?
942 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

    # File lib/sequel/adapters/shared/mysql.rb
945 def supports_window_clause?
946   !db.mariadb? && db.server_version >= 80000
947 end
supports_window_functions?() click to toggle source

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
950 def supports_window_functions?
951   db.server_version >= (db.mariadb? ? 100200 : 80000)
952 end
update_ignore() click to toggle source

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
960 def update_ignore
961   clone(:update_ignore=>true)
962 end

Private Instance Methods

check_not_limited!(type) click to toggle source

Allow update and delete for limited datasets, unless there is an offset.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
967 def check_not_limited!(type)
968   super if type == :truncate || @opts[:offset]
969 end
default_time_format() click to toggle source

The strftime format to use when literalizing time (Sequel::SQLTime) values.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
972 def default_time_format
973   db.supports_timestamp_usecs? ? super : "'%H:%M:%S'"
974 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing timestamp (Time/DateTime) values.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
977 def default_timestamp_format
978   db.supports_timestamp_usecs? ? super : "'%Y-%m-%d %H:%M:%S'"
979 end
delete_from_sql(sql) click to toggle source

Consider the first table in the joined dataset is the table to delete from, but include the others for the purposes of selecting rows.

Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
983 def delete_from_sql(sql)
984   if joined_dataset?
985     sql << ' '
986     tables = @opts[:delete_from] || @opts[:from][0..0]
987     source_list_append(sql, tables)
988     sql << ' FROM '
989     source_list_append(sql, @opts[:from])
990     select_join_sql(sql)
991   else
992     super
993   end
994 end
delete_limit_sql(sql)
Alias for: limit_sql
insert_columns_sql(sql) click to toggle source

MySQL doesn’t use the SQL standard DEFAULT VALUES.

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
 997 def insert_columns_sql(sql)
 998   values = opts[:values]
 999   if values.is_a?(Array) && values.empty?
1000     sql << " ()"
1001   else
1002     super
1003   end
1004 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

     # File lib/sequel/adapters/shared/mysql.rb
1007 def insert_ignore_sql(sql)
1008   sql << " IGNORE" if opts[:insert_ignore]
1009 end
insert_on_duplicate_key_update_sql(sql) click to toggle source

MySQL supports INSERT … ON DUPLICATE KEY UPDATE

     # File lib/sequel/adapters/shared/mysql.rb
1017 def insert_on_duplicate_key_update_sql(sql)
1018   if update_cols = opts[:on_duplicate_key_update]
1019     update_vals = nil
1020 
1021     if update_cols.empty?
1022       update_cols = columns
1023     elsif update_cols.last.is_a?(Hash)
1024       update_vals = update_cols.last
1025       update_cols = update_cols[0..-2]
1026     end
1027 
1028     sql << " ON DUPLICATE KEY UPDATE "
1029     c = false
1030     co = ', '
1031     values = '=VALUES('
1032     endp = ')'
1033     update_cols.each do |col|
1034       sql << co if c
1035       quote_identifier_append(sql, col)
1036       sql << values
1037       quote_identifier_append(sql, col)
1038       sql << endp
1039       c ||= true
1040     end
1041     if update_vals
1042       eq = '='
1043       update_vals.map do |col,v| 
1044         sql << co if c
1045         quote_identifier_append(sql, col)
1046         sql << eq
1047         literal_append(sql, v)
1048         c ||= true
1049       end
1050     end
1051   end
1052 end
insert_values_sql(sql) click to toggle source

MySQL doesn’t use the standard DEFAULT VALUES for empty values.

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1055 def insert_values_sql(sql)
1056   values = opts[:values]
1057   if values.is_a?(Array) && values.empty?
1058     sql << " VALUES ()"
1059   else
1060     super
1061   end
1062 end
join_type_sql(join_type) click to toggle source

Transforms :straight to STRAIGHT_JOIN.

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1065 def join_type_sql(join_type)
1066   if join_type == :straight
1067     'STRAIGHT_JOIN'
1068   else
1069     super
1070   end
1071 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

     # File lib/sequel/adapters/shared/mysql.rb
1074 def limit_sql(sql)
1075   if l = @opts[:limit]
1076     sql << " LIMIT "
1077     literal_append(sql, l)
1078   end
1079 end
literal_blob_append(sql, v) click to toggle source

MySQL uses a preceding X for hex escaping strings

     # File lib/sequel/adapters/shared/mysql.rb
1084 def literal_blob_append(sql, v)
1085   if v.empty?
1086     sql << "''"
1087   else
1088     sql << "0x" << v.unpack("H*").first
1089   end
1090 end
literal_false() click to toggle source

Use 0 for false on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1093 def literal_false
1094   '0'
1095 end
literal_float(v) click to toggle source

Raise error for infinitate and NaN values

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1098 def literal_float(v)
1099   if v.infinite? || v.nan?
1100     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1101   else
1102     super
1103   end
1104 end
literal_string_append(sql, v) click to toggle source

SQL fragment for String. Doubles \ and ‘ by default.

     # File lib/sequel/adapters/shared/mysql.rb
1107 def literal_string_append(sql, v)
1108   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1109 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1112 def literal_true
1113   '1'
1114 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1117 def multi_insert_sql_strategy
1118   :values
1119 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1121 def non_sql_option?(key)
1122   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1123 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1126 def requires_emulating_nulls_first?
1127   true
1128 end
select_calc_found_rows_sql(sql) click to toggle source

MySQL specific SQL_CALC_FOUND_ROWS option

     # File lib/sequel/adapters/shared/mysql.rb
1160 def select_calc_found_rows_sql(sql)
1161   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1162 end
select_lock_sql(sql) click to toggle source

Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1138 def select_lock_sql(sql)
1139   lock = @opts[:lock]
1140   if lock == :share
1141     if !db.mariadb? && db.server_version >= 80000
1142       sql << ' FOR SHARE'
1143     else
1144       sql << ' LOCK IN SHARE MODE'
1145     end
1146   else
1147     super
1148   end
1149 
1150   if lock
1151     if @opts[:skip_locked]
1152       sql << " SKIP LOCKED"
1153     elsif @opts[:nowait]
1154       sql << " NOWAIT"
1155     end
1156   end
1157 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1130 def select_only_offset_sql(sql)
1131   sql << " LIMIT "
1132   literal_append(sql, @opts[:offset])
1133   sql << ",18446744073709551615"
1134 end
select_with_sql_base() click to toggle source

Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive

Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1165 def select_with_sql_base
1166   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1167 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

     # File lib/sequel/adapters/shared/mysql.rb
1012 def update_ignore_sql(sql)
1013   sql << " IGNORE" if opts[:update_ignore]
1014 end
update_limit_sql(sql)
Alias for: limit_sql
uses_with_rollup?() click to toggle source

MySQL uses WITH ROLLUP syntax.

     # File lib/sequel/adapters/shared/mysql.rb
1170 def uses_with_rollup?
1171   true
1172 end