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
694 def calc_found_rows
695   clone(:calc_found_rows => true)
696 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
628 def complex_expression_sql_append(sql, op, args)
629   case op
630   when :IN, :"NOT IN"
631     ds = args[1]
632     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
633       super(sql, op, [args[0], ds.from_self])
634     else
635       super
636     end
637   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
638     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
639       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
640       func = ~func if op == :'!~'
641       return literal_append(sql, func)
642     end
643 
644     sql << '('
645     literal_append(sql, args[0])
646     sql << ' '
647     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
648     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
649     sql << ' '
650     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
651     literal_append(sql, args[1])
652     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
653       sql << " ESCAPE "
654       literal_append(sql, "\\")
655     end
656     sql << ')'
657   when :'||'
658     if args.length > 1
659       sql << "CONCAT"
660       array_sql_append(sql, args)
661     else
662       literal_append(sql, args[0])
663     end
664   when :'B~'
665     sql << "CAST(~"
666     literal_append(sql, args[0])
667     sql << " AS SIGNED INTEGER)"
668   else
669     super
670   end
671 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
677 def constant_sql_append(sql, constant)
678   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
679     sql << 'CURRENT_TIMESTAMP(6)'
680   else
681     super
682   end
683 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
706 def delete_from(*tables)
707   clone(:delete_from=>tables)
708 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
686 def distinct(*args)
687   args.empty? ? super : group(*args)
688 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
712 def explain(opts=OPTS)
713   # Load the PrettyTable class, needed for explain output
714   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
715 
716   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
717   rows = ds.all
718   Sequel::PrettyTable.string(rows, ds.columns)
719 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
722 def for_share
723   lock_style(:share)
724 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
732 def full_text_sql(cols, terms, opts = OPTS)
733   terms = terms.join(' ') if terms.is_a?(Array)
734   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
735 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
745 def insert_ignore
746   clone(:insert_ignore=>true)
747 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
778 def on_duplicate_key_update(*args)
779   clone(:on_duplicate_key_update => args)
780 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
783 def quoted_identifier_append(sql, c)
784   sql << '`' << c.to_s.gsub('`', '``') << '`'
785 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
788 def supports_cte?(type=:select)
789   if db.mariadb?
790     type == :select && db.server_version >= 100200
791   else
792     case type
793     when :select, :update, :delete
794       db.server_version >= 80000
795     end
796   end
797 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
800 def supports_derived_column_lists?
801   false
802 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
806 def supports_distinct_on?
807   true
808 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
811 def supports_group_rollup?
812   true
813 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
816 def supports_intersect_except?
817   db.mariadb? && db.server_version >= 100300
818 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
821 def supports_limits_in_correlated_subqueries?
822   false
823 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
826 def supports_modifying_joins?
827   true
828 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
831 def supports_nowait?
832   db.server_version >= (db.mariadb? ? 100300 : 80000)
833 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
837 def supports_ordered_distinct_on?
838   false
839 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
842 def supports_regexp?
843   true
844 end
supports_skip_locked?() click to toggle source

MySQL 8+ supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
847 def supports_skip_locked?
848   !db.mariadb? && db.server_version >= 80000
849 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
853 def supports_timestamp_usecs?
854   db.supports_timestamp_usecs?
855 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

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

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
863 def supports_window_functions?
864   db.server_version >= (db.mariadb? ? 100200 : 80000)
865 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
873 def update_ignore
874   clone(:update_ignore=>true)
875 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
880 def check_not_limited!(type)
881   super if type == :truncate || @opts[:offset]
882 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
886 def delete_from_sql(sql)
887   if joined_dataset?
888     sql << ' '
889     tables = @opts[:delete_from] || @opts[:from][0..0]
890     source_list_append(sql, tables)
891     sql << ' FROM '
892     source_list_append(sql, @opts[:from])
893     select_join_sql(sql)
894   else
895     super
896   end
897 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
900 def insert_columns_sql(sql)
901   values = opts[:values]
902   if values.is_a?(Array) && values.empty?
903     sql << " ()"
904   else
905     super
906   end
907 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

    # File lib/sequel/adapters/shared/mysql.rb
910 def insert_ignore_sql(sql)
911   sql << " IGNORE" if opts[:insert_ignore]
912 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
920 def insert_on_duplicate_key_update_sql(sql)
921   if update_cols = opts[:on_duplicate_key_update]
922     update_vals = nil
923 
924     if update_cols.empty?
925       update_cols = columns
926     elsif update_cols.last.is_a?(Hash)
927       update_vals = update_cols.last
928       update_cols = update_cols[0..-2]
929     end
930 
931     sql << " ON DUPLICATE KEY UPDATE "
932     c = false
933     co = ', '
934     values = '=VALUES('
935     endp = ')'
936     update_cols.each do |col|
937       sql << co if c
938       quote_identifier_append(sql, col)
939       sql << values
940       quote_identifier_append(sql, col)
941       sql << endp
942       c ||= true
943     end
944     if update_vals
945       eq = '='
946       update_vals.map do |col,v| 
947         sql << co if c
948         quote_identifier_append(sql, col)
949         sql << eq
950         literal_append(sql, v)
951         c ||= true
952       end
953     end
954   end
955 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
958 def insert_values_sql(sql)
959   values = opts[:values]
960   if values.is_a?(Array) && values.empty?
961     sql << " VALUES ()"
962   else
963     super
964   end
965 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
968 def join_type_sql(join_type)
969   if join_type == :straight
970     'STRAIGHT_JOIN'
971   else
972     super
973   end
974 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

    # File lib/sequel/adapters/shared/mysql.rb
977 def limit_sql(sql)
978   if l = @opts[:limit]
979     sql << " LIMIT "
980     literal_append(sql, l)
981   end
982 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
987 def literal_blob_append(sql, v)
988   if v.empty?
989     sql << "''"
990   else
991     sql << "0x" << v.unpack("H*").first
992   end
993 end
literal_false() click to toggle source

Use 0 for false on MySQL

    # File lib/sequel/adapters/shared/mysql.rb
996 def literal_false
997   '0'
998 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
1001 def literal_float(v)
1002   if v.infinite? || v.nan?
1003     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1004   else
1005     super
1006   end
1007 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
1010 def literal_string_append(sql, v)
1011   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1012 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1015 def literal_true
1016   '1'
1017 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1020 def multi_insert_sql_strategy
1021   :values
1022 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1024 def non_sql_option?(key)
1025   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1026 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1029 def requires_emulating_nulls_first?
1030   true
1031 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
1063 def select_calc_found_rows_sql(sql)
1064   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1065 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
1041 def select_lock_sql(sql)
1042   lock = @opts[:lock]
1043   if lock == :share
1044     if !db.mariadb? && db.server_version >= 80000
1045       sql << ' FOR SHARE'
1046     else
1047       sql << ' LOCK IN SHARE MODE'
1048     end
1049   else
1050     super
1051   end
1052 
1053   if lock
1054     if @opts[:skip_locked]
1055       sql << " SKIP LOCKED"
1056     elsif @opts[:nowait]
1057       sql << " NOWAIT"
1058     end
1059   end
1060 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1033 def select_only_offset_sql(sql)
1034   sql << " LIMIT "
1035   literal_append(sql, @opts[:offset])
1036   sql << ",18446744073709551615"
1037 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
1068 def select_with_sql_base
1069   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1070 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

    # File lib/sequel/adapters/shared/mysql.rb
915 def update_ignore_sql(sql)
916   sql << " IGNORE" if opts[:update_ignore]
917 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
1073 def uses_with_rollup?
1074   true
1075 end