module Sequel::MySQL::DatasetMethods

Dataset methods shared by datasets that use MySQL databases.

Constants

MATCH_AGAINST
MATCH_AGAINST_BOOLEAN

Public Instance Methods

Source

    # File lib/sequel/adapters/shared/mysql.rb
761 def calc_found_rows
762   clone(:calc_found_rows => true)
763 end

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

complex_expression_sql_append (sql, op, args)

Source

    # 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

Calls superclass method

constant_sql_append (sql, constant)

Source

    # 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

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

delete_from (*tables)

Source

    # File lib/sequel/adapters/shared/mysql.rb
773 def delete_from(*tables)
774   clone(:delete_from=>tables)
775 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)

distinct (*args)

Source

    # File lib/sequel/adapters/shared/mysql.rb
753 def distinct(*args)
754   args.empty? ? super : group(*args)
755 end

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

Calls superclass method

explain (opts=OPTS)

Source

    # 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

Return the results of an EXPLAIN query as a string. Options:

:extended: Use EXPLAIN EXTENDED instead of EXPLAIN if true.

for_share ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
790 def for_share
791   cached_lock_style_dataset(:_for_share_ds, :share)
792 end

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

full_text_search (cols, terms, opts = OPTS)

Source

    # 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

Adds full text filter

full_text_sql (cols, terms, opts = OPTS)

Source

    # 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

MySQL specific full text search syntax.

insert_ignore ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
813 def insert_ignore
814   clone(:insert_ignore=>true)
815 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)

insert_select (*values)

Source

    # 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

Support insert select for associations, so that the model code can use returning instead of a separate query.

insert_select_sql (*values)

Source

    # 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

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

on_duplicate_key_update (*args)

Source

    # File lib/sequel/adapters/shared/mysql.rb
861 def on_duplicate_key_update(*args)
862   clone(:on_duplicate_key_update => args)
863 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)

quoted_identifier_append (sql, c)

Source

    # File lib/sequel/adapters/shared/mysql.rb
866 def quoted_identifier_append(sql, c)
867   sql << '`' << c.to_s.gsub('`', '``') << '`'
868 end

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

supports_cte? (type=:select)

Source

    # 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

MariaDB 10.2+ and MySQL 8+ support CTEs

supports_derived_column_lists? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
883 def supports_derived_column_lists?
884   false
885 end

MySQL does not support derived column lists

supports_distinct_on? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
889 def supports_distinct_on?
890   true
891 end

MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.

supports_group_rollup? ()

Source

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

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

supports_intersect_except? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
899 def supports_intersect_except?
900   db.mariadb? && db.server_version >= 100300
901 end

MariaDB 10.3+ supports INTERSECT or EXCEPT

supports_limits_in_correlated_subqueries? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
904 def supports_limits_in_correlated_subqueries?
905   false
906 end

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

supports_modifying_joins? ()

Source

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

MySQL supports modifying joined datasets

supports_nowait? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
914 def supports_nowait?
915   db.server_version >= (db.mariadb? ? 100300 : 80000)
916 end

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

supports_ordered_distinct_on? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
920 def supports_ordered_distinct_on?
921   false
922 end

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

supports_regexp? ()

Source

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

MySQL supports pattern matching via regular expressions

supports_returning? (type)

Source

    # 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

MariaDB 10.5.0 supports INSERT RETURNING.

supports_skip_locked? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
935 def supports_skip_locked?
936   db.server_version >= (db.mariadb? ? 100600 : 80000)
937 end

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

supports_timestamp_usecs? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
941 def supports_timestamp_usecs?
942   db.supports_timestamp_usecs?
943 end

Check the database setting for whether fractional timestamps are suppported.

supports_window_clause? ()

Source

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

MySQL 8+ supports WINDOW clause.

supports_window_functions? ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
951 def supports_window_functions?
952   db.server_version >= (db.mariadb? ? 100200 : 80000)
953 end

MariaDB 10.2+ and MySQL 8+ support window functions

update_ignore ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
961 def update_ignore
962   clone(:update_ignore=>true)
963 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

Private Instance Methods

check_not_limited! (type)

Source

    # File lib/sequel/adapters/shared/mysql.rb
968 def check_not_limited!(type)
969   super if type == :truncate || @opts[:offset]
970 end

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

Calls superclass method

default_time_format ()

Source

    # File lib/sequel/adapters/shared/mysql.rb
973 def default_time_format
974   db.supports_timestamp_usecs? ? super : "'%H:%M:%S'"
975 end

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

Calls superclass method

default_timestamp_format ()

Source

    # 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

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

Calls superclass method

delete_from_sql (sql)

Source

    # 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

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

delete_limit_sql (sql)

Alias for: limit_sql

insert_columns_sql (sql)

Source

     # 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

MySQL doesn’t use the SQL standard DEFAULT VALUES.

Calls superclass method

insert_ignore_sql (sql)

Source

     # File lib/sequel/adapters/shared/mysql.rb
1008 def insert_ignore_sql(sql)
1009   sql << " IGNORE" if opts[:insert_ignore]
1010 end

MySQL supports INSERT IGNORE INTO

insert_on_duplicate_key_update_sql (sql)

Source

     # 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

MySQL supports INSERT … ON DUPLICATE KEY UPDATE

insert_values_sql (sql)

Source

     # 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

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

Calls superclass method

join_type_sql (join_type)

Source

     # 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

Transforms :straight to STRAIGHT_JOIN.

Calls superclass method

limit_sql (sql)

Source

     # 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

MySQL allows a LIMIT in DELETE and UPDATE statements.

Also aliased as: delete_limit_sql, update_limit_sql

literal_blob_append (sql, v)

Source

     # 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

MySQL uses a preceding X for hex escaping strings

literal_false ()

Source

     # File lib/sequel/adapters/shared/mysql.rb
1094 def literal_false
1095   '0'
1096 end

Use 0 for false on MySQL

literal_float (v)

Source

     # 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

Raise error for infinitate and NaN values

Calls superclass method

literal_string_append (sql, v)

Source

     # File lib/sequel/adapters/shared/mysql.rb
1108 def literal_string_append(sql, v)
1109   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1110 end

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

literal_true ()

Source

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

Use 1 for true on MySQL

multi_insert_sql_strategy ()

Source

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

MySQL supports multiple rows in VALUES in INSERT.

non_sql_option? (key)

Source

     # 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

Calls superclass method

requires_emulating_nulls_first? ()

Source

     # File lib/sequel/adapters/shared/mysql.rb
1127 def requires_emulating_nulls_first?
1128   true
1129 end

MySQL does not natively support NULLS FIRST/LAST.

select_calc_found_rows_sql (sql)

Source

     # 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

MySQL specific SQL_CALC_FOUND_ROWS option

select_lock_sql (sql)

Source

     # 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

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

Calls superclass method

select_only_offset_sql (sql)

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 ()

Source

     # 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

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

Calls superclass method

update_ignore_sql (sql)

Source

     # File lib/sequel/adapters/shared/mysql.rb
1013 def update_ignore_sql(sql)
1014   sql << " IGNORE" if opts[:update_ignore]
1015 end

MySQL supports UPDATE IGNORE

update_limit_sql (sql)

Alias for: limit_sql

uses_with_rollup? ()

Source

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

MySQL uses WITH ROLLUP syntax.