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   sql = ((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql
784   ds = db.send(:metadata_dataset).with_sql(sql.freeze).naked
785   rows = ds.all
786   Sequel::PrettyTable.string(rows, ds.columns)
787 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
790 def for_share
791   cached_lock_style_dataset(:_for_share_ds, :share)
792 end

full_text_search(cols, terms, opts = OPTS) click to toggle source

Adds full text filter

    # File lib/sequel/adapters/shared/mysql.rb
795 def full_text_search(cols, terms, opts = OPTS)
796   where(full_text_sql(cols, terms, opts))
797 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
800 def full_text_sql(cols, terms, opts = OPTS)
801   terms = Sequel.array_or_set_join(terms, ' ') if terms.is_a?(Array) || terms.is_a?(Set)
802   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
803 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
813 def insert_ignore
814   clone(:insert_ignore=>true)
815 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
819 def insert_select(*values)
820   return unless supports_insert_select?
821   # Handle case where query does not return a row
822   server?(:default).with_sql_first(insert_select_sql(*values)) || false
823 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
827 def insert_select_sql(*values)
828   ds = opts[:returning] ? self : returning
829   ds.insert_sql(*values)
830 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
861 def on_duplicate_key_update(*args)
862   clone(:on_duplicate_key_update => args)
863 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
866 def quoted_identifier_append(sql, c)
867   sql << '`' << c.to_s.gsub('`', '``') << '`'
868 end

supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

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

supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
883 def supports_derived_column_lists?
884   false
885 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
889 def supports_distinct_on?
890   true
891 end

supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
894 def supports_group_rollup?
895   true
896 end

supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
899 def supports_intersect_except?
900   db.mariadb? && db.server_version >= 100300
901 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
904 def supports_limits_in_correlated_subqueries?
905   false
906 end

supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
909 def supports_modifying_joins?
910   true
911 end

supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
914 def supports_nowait?
915   db.server_version >= (db.mariadb? ? 100300 : 80000)
916 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
920 def supports_ordered_distinct_on?
921   false
922 end

supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
925 def supports_regexp?
926   true
927 end

supports_returning?(type) click to toggle source

MariaDB 10.5.0 supports INSERT RETURNING.

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

supports_skip_locked?() click to toggle source

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
935 def supports_skip_locked?
936   db.server_version >= (db.mariadb? ? 100600 : 80000)
937 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
941 def supports_timestamp_usecs?
942   db.supports_timestamp_usecs?
943 end

supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

    # File lib/sequel/adapters/shared/mysql.rb
946 def supports_window_clause?
947   !db.mariadb? && db.server_version >= 80000
948 end

supports_window_functions?() click to toggle source

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
951 def supports_window_functions?
952   db.server_version >= (db.mariadb? ? 100200 : 80000)
953 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
961 def update_ignore
962   clone(:update_ignore=>true)
963 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
968 def check_not_limited!(type)
969   super if type == :truncate || @opts[:offset]
970 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
973 def default_time_format
974   db.supports_timestamp_usecs? ? super : "'%H:%M:%S'"
975 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
978 def default_timestamp_format
979   db.supports_timestamp_usecs? ? super : "'%Y-%m-%d %H:%M:%S'"
980 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
984 def delete_from_sql(sql)
985   if joined_dataset?
986     sql << ' '
987     tables = @opts[:delete_from] || @opts[:from][0..0]
988     source_list_append(sql, tables)
989     sql << ' FROM '
990     source_list_append(sql, @opts[:from])
991     select_join_sql(sql)
992   else
993     super
994   end
995 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
 998 def insert_columns_sql(sql)
 999   values = opts[:values]
1000   if values.is_a?(Array) && values.empty?
1001     sql << " ()"
1002   else
1003     super
1004   end
1005 end

insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

     # File lib/sequel/adapters/shared/mysql.rb
1008 def insert_ignore_sql(sql)
1009   sql << " IGNORE" if opts[:insert_ignore]
1010 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
1018 def insert_on_duplicate_key_update_sql(sql)
1019   if update_cols = opts[:on_duplicate_key_update]
1020     update_vals = nil
1021 
1022     if update_cols.empty?
1023       update_cols = columns
1024     elsif update_cols.last.is_a?(Hash)
1025       update_vals = update_cols.last
1026       update_cols = update_cols[0..-2]
1027     end
1028 
1029     sql << " ON DUPLICATE KEY UPDATE "
1030     c = false
1031     co = ', '
1032     values = '=VALUES('
1033     endp = ')'
1034     update_cols.each do |col|
1035       sql << co if c
1036       quote_identifier_append(sql, col)
1037       sql << values
1038       quote_identifier_append(sql, col)
1039       sql << endp
1040       c ||= true
1041     end
1042     if update_vals
1043       eq = '='
1044       update_vals.map do |col,v| 
1045         sql << co if c
1046         quote_identifier_append(sql, col)
1047         sql << eq
1048         literal_append(sql, v)
1049         c ||= true
1050       end
1051     end
1052   end
1053 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
1056 def insert_values_sql(sql)
1057   values = opts[:values]
1058   if values.is_a?(Array) && values.empty?
1059     sql << " VALUES ()"
1060   else
1061     super
1062   end
1063 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
1066 def join_type_sql(join_type)
1067   if join_type == :straight
1068     'STRAIGHT_JOIN'
1069   else
1070     super
1071   end
1072 end

limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

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

Also aliased as: delete_limit_sql, update_limit_sql

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
1085 def literal_blob_append(sql, v)
1086   if v.empty?
1087     sql << "''"
1088   else
1089     sql << "0x" << v.unpack("H*").first
1090   end
1091 end

literal_false() click to toggle source

Use 0 for false on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1094 def literal_false
1095   '0'
1096 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
1099 def literal_float(v)
1100   if v.infinite? || v.nan?
1101     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1102   else
1103     super
1104   end
1105 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
1108 def literal_string_append(sql, v)
1109   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1110 end

literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1113 def literal_true
1114   '1'
1115 end

multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1118 def multi_insert_sql_strategy
1119   :values
1120 end

non_sql_option?(key) click to toggle source

Calls superclass method

     # File lib/sequel/adapters/shared/mysql.rb
1122 def non_sql_option?(key)
1123   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1124 end

requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1127 def requires_emulating_nulls_first?
1128   true
1129 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
1161 def select_calc_found_rows_sql(sql)
1162   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1163 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
1139 def select_lock_sql(sql)
1140   lock = @opts[:lock]
1141   if lock == :share
1142     if !db.mariadb? && db.server_version >= 80000
1143       sql << ' FOR SHARE'
1144     else
1145       sql << ' LOCK IN SHARE MODE'
1146     end
1147   else
1148     super
1149   end
1150 
1151   if lock
1152     if @opts[:skip_locked]
1153       sql << " SKIP LOCKED"
1154     elsif @opts[:nowait]
1155       sql << " NOWAIT"
1156     end
1157   end
1158 end

select_only_offset_sql(sql) click to toggle source

     # File lib/sequel/adapters/shared/mysql.rb
1131 def select_only_offset_sql(sql)
1132   sql << " LIMIT "
1133   literal_append(sql, @opts[:offset])
1134   sql << ",18446744073709551615"
1135 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
1166 def select_with_sql_base
1167   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1168 end

update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

     # File lib/sequel/adapters/shared/mysql.rb
1013 def update_ignore_sql(sql)
1014   sql << " IGNORE" if opts[:update_ignore]
1015 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
1171 def uses_with_rollup?
1172   true
1173 end