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
722 def calc_found_rows
723   clone(:calc_found_rows => true)
724 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
656 def complex_expression_sql_append(sql, op, args)
657   case op
658   when :IN, :"NOT IN"
659     ds = args[1]
660     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
661       super(sql, op, [args[0], ds.from_self])
662     else
663       super
664     end
665   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
666     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
667       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
668       func = ~func if op == :'!~'
669       return literal_append(sql, func)
670     end
671 
672     sql << '('
673     literal_append(sql, args[0])
674     sql << ' '
675     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
676     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
677     sql << ' '
678     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
679     literal_append(sql, args[1])
680     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
681       sql << " ESCAPE "
682       literal_append(sql, "\\")
683     end
684     sql << ')'
685   when :'||'
686     if args.length > 1
687       sql << "CONCAT"
688       array_sql_append(sql, args)
689     else
690       literal_append(sql, args[0])
691     end
692   when :'B~'
693     sql << "CAST(~"
694     literal_append(sql, args[0])
695     sql << " AS SIGNED INTEGER)"
696   else
697     super
698   end
699 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
705 def constant_sql_append(sql, constant)
706   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
707     sql << 'CURRENT_TIMESTAMP(6)'
708   else
709     super
710   end
711 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
734 def delete_from(*tables)
735   clone(:delete_from=>tables)
736 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
714 def distinct(*args)
715   args.empty? ? super : group(*args)
716 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
740 def explain(opts=OPTS)
741   # Load the PrettyTable class, needed for explain output
742   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
743 
744   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
745   rows = ds.all
746   Sequel::PrettyTable.string(rows, ds.columns)
747 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
750 def for_share
751   lock_style(:share)
752 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
760 def full_text_sql(cols, terms, opts = OPTS)
761   terms = terms.join(' ') if terms.is_a?(Array)
762   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
763 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
773 def insert_ignore
774   clone(:insert_ignore=>true)
775 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
779 def insert_select(*values)
780   return unless supports_insert_select?
781   # Handle case where query does not return a row
782   server?(:default).with_sql_first(insert_select_sql(*values)) || false
783 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
787 def insert_select_sql(*values)
788   ds = opts[:returning] ? self : returning
789   ds.insert_sql(*values)
790 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
821 def on_duplicate_key_update(*args)
822   clone(:on_duplicate_key_update => args)
823 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
826 def quoted_identifier_append(sql, c)
827   sql << '`' << c.to_s.gsub('`', '``') << '`'
828 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
831 def supports_cte?(type=:select)
832   if db.mariadb?
833     type == :select && db.server_version >= 100200
834   else
835     case type
836     when :select, :update, :delete
837       db.server_version >= 80000
838     end
839   end
840 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
843 def supports_derived_column_lists?
844   false
845 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
849 def supports_distinct_on?
850   true
851 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
854 def supports_group_rollup?
855   true
856 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
859 def supports_intersect_except?
860   db.mariadb? && db.server_version >= 100300
861 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
864 def supports_limits_in_correlated_subqueries?
865   false
866 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
869 def supports_modifying_joins?
870   true
871 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
874 def supports_nowait?
875   db.server_version >= (db.mariadb? ? 100300 : 80000)
876 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
880 def supports_ordered_distinct_on?
881   false
882 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
885 def supports_regexp?
886   true
887 end
supports_returning?(type) click to toggle source

MariaDB 10.5.0 supports INSERT RETURNING.

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

MySQL 8+ supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
895 def supports_skip_locked?
896   !db.mariadb? && db.server_version >= 80000
897 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
901 def supports_timestamp_usecs?
902   db.supports_timestamp_usecs?
903 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

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

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
911 def supports_window_functions?
912   db.server_version >= (db.mariadb? ? 100200 : 80000)
913 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
921 def update_ignore
922   clone(:update_ignore=>true)
923 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
928 def check_not_limited!(type)
929   super if type == :truncate || @opts[:offset]
930 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
933 def default_time_format
934   db.supports_timestamp_usecs? ? super : "'%H:%M:%S'"
935 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
938 def default_timestamp_format
939   db.supports_timestamp_usecs? ? super : "'%Y-%m-%d %H:%M:%S'"
940 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
944 def delete_from_sql(sql)
945   if joined_dataset?
946     sql << ' '
947     tables = @opts[:delete_from] || @opts[:from][0..0]
948     source_list_append(sql, tables)
949     sql << ' FROM '
950     source_list_append(sql, @opts[:from])
951     select_join_sql(sql)
952   else
953     super
954   end
955 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
958 def insert_columns_sql(sql)
959   values = opts[:values]
960   if values.is_a?(Array) && values.empty?
961     sql << " ()"
962   else
963     super
964   end
965 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

    # File lib/sequel/adapters/shared/mysql.rb
968 def insert_ignore_sql(sql)
969   sql << " IGNORE" if opts[:insert_ignore]
970 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
 978 def insert_on_duplicate_key_update_sql(sql)
 979   if update_cols = opts[:on_duplicate_key_update]
 980     update_vals = nil
 981 
 982     if update_cols.empty?
 983       update_cols = columns
 984     elsif update_cols.last.is_a?(Hash)
 985       update_vals = update_cols.last
 986       update_cols = update_cols[0..-2]
 987     end
 988 
 989     sql << " ON DUPLICATE KEY UPDATE "
 990     c = false
 991     co = ', '
 992     values = '=VALUES('
 993     endp = ')'
 994     update_cols.each do |col|
 995       sql << co if c
 996       quote_identifier_append(sql, col)
 997       sql << values
 998       quote_identifier_append(sql, col)
 999       sql << endp
1000       c ||= true
1001     end
1002     if update_vals
1003       eq = '='
1004       update_vals.map do |col,v| 
1005         sql << co if c
1006         quote_identifier_append(sql, col)
1007         sql << eq
1008         literal_append(sql, v)
1009         c ||= true
1010       end
1011     end
1012   end
1013 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
1016 def insert_values_sql(sql)
1017   values = opts[:values]
1018   if values.is_a?(Array) && values.empty?
1019     sql << " VALUES ()"
1020   else
1021     super
1022   end
1023 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
1026 def join_type_sql(join_type)
1027   if join_type == :straight
1028     'STRAIGHT_JOIN'
1029   else
1030     super
1031   end
1032 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

     # File lib/sequel/adapters/shared/mysql.rb
1035 def limit_sql(sql)
1036   if l = @opts[:limit]
1037     sql << " LIMIT "
1038     literal_append(sql, l)
1039   end
1040 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
1045 def literal_blob_append(sql, v)
1046   if v.empty?
1047     sql << "''"
1048   else
1049     sql << "0x" << v.unpack("H*").first
1050   end
1051 end
literal_false() click to toggle source

Use 0 for false on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1054 def literal_false
1055   '0'
1056 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
1059 def literal_float(v)
1060   if v.infinite? || v.nan?
1061     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1062   else
1063     super
1064   end
1065 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
1068 def literal_string_append(sql, v)
1069   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1070 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1073 def literal_true
1074   '1'
1075 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1078 def multi_insert_sql_strategy
1079   :values
1080 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1082 def non_sql_option?(key)
1083   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1084 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1087 def requires_emulating_nulls_first?
1088   true
1089 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
1121 def select_calc_found_rows_sql(sql)
1122   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1123 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
1099 def select_lock_sql(sql)
1100   lock = @opts[:lock]
1101   if lock == :share
1102     if !db.mariadb? && db.server_version >= 80000
1103       sql << ' FOR SHARE'
1104     else
1105       sql << ' LOCK IN SHARE MODE'
1106     end
1107   else
1108     super
1109   end
1110 
1111   if lock
1112     if @opts[:skip_locked]
1113       sql << " SKIP LOCKED"
1114     elsif @opts[:nowait]
1115       sql << " NOWAIT"
1116     end
1117   end
1118 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1091 def select_only_offset_sql(sql)
1092   sql << " LIMIT "
1093   literal_append(sql, @opts[:offset])
1094   sql << ",18446744073709551615"
1095 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
1126 def select_with_sql_base
1127   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1128 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

    # File lib/sequel/adapters/shared/mysql.rb
973 def update_ignore_sql(sql)
974   sql << " IGNORE" if opts[:update_ignore]
975 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
1131 def uses_with_rollup?
1132   true
1133 end