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
715 def calc_found_rows
716   clone(:calc_found_rows => true)
717 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
649 def complex_expression_sql_append(sql, op, args)
650   case op
651   when :IN, :"NOT IN"
652     ds = args[1]
653     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
654       super(sql, op, [args[0], ds.from_self])
655     else
656       super
657     end
658   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
659     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
660       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
661       func = ~func if op == :'!~'
662       return literal_append(sql, func)
663     end
664 
665     sql << '('
666     literal_append(sql, args[0])
667     sql << ' '
668     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
669     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
670     sql << ' '
671     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
672     literal_append(sql, args[1])
673     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
674       sql << " ESCAPE "
675       literal_append(sql, "\\")
676     end
677     sql << ')'
678   when :'||'
679     if args.length > 1
680       sql << "CONCAT"
681       array_sql_append(sql, args)
682     else
683       literal_append(sql, args[0])
684     end
685   when :'B~'
686     sql << "CAST(~"
687     literal_append(sql, args[0])
688     sql << " AS SIGNED INTEGER)"
689   else
690     super
691   end
692 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
698 def constant_sql_append(sql, constant)
699   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
700     sql << 'CURRENT_TIMESTAMP(6)'
701   else
702     super
703   end
704 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
727 def delete_from(*tables)
728   clone(:delete_from=>tables)
729 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
707 def distinct(*args)
708   args.empty? ? super : group(*args)
709 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
733 def explain(opts=OPTS)
734   # Load the PrettyTable class, needed for explain output
735   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
736 
737   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
738   rows = ds.all
739   Sequel::PrettyTable.string(rows, ds.columns)
740 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
743 def for_share
744   lock_style(:share)
745 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
753 def full_text_sql(cols, terms, opts = OPTS)
754   terms = terms.join(' ') if terms.is_a?(Array)
755   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
756 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
766 def insert_ignore
767   clone(:insert_ignore=>true)
768 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
799 def on_duplicate_key_update(*args)
800   clone(:on_duplicate_key_update => args)
801 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
804 def quoted_identifier_append(sql, c)
805   sql << '`' << c.to_s.gsub('`', '``') << '`'
806 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
809 def supports_cte?(type=:select)
810   if db.mariadb?
811     type == :select && db.server_version >= 100200
812   else
813     case type
814     when :select, :update, :delete
815       db.server_version >= 80000
816     end
817   end
818 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
821 def supports_derived_column_lists?
822   false
823 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
827 def supports_distinct_on?
828   true
829 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
832 def supports_group_rollup?
833   true
834 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
837 def supports_intersect_except?
838   db.mariadb? && db.server_version >= 100300
839 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
842 def supports_limits_in_correlated_subqueries?
843   false
844 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
847 def supports_modifying_joins?
848   true
849 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
852 def supports_nowait?
853   db.server_version >= (db.mariadb? ? 100300 : 80000)
854 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
858 def supports_ordered_distinct_on?
859   false
860 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
863 def supports_regexp?
864   true
865 end
supports_skip_locked?() click to toggle source

MySQL 8+ supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
868 def supports_skip_locked?
869   !db.mariadb? && db.server_version >= 80000
870 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
874 def supports_timestamp_usecs?
875   db.supports_timestamp_usecs?
876 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

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

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
884 def supports_window_functions?
885   db.server_version >= (db.mariadb? ? 100200 : 80000)
886 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
894 def update_ignore
895   clone(:update_ignore=>true)
896 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
901 def check_not_limited!(type)
902   super if type == :truncate || @opts[:offset]
903 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
907 def delete_from_sql(sql)
908   if joined_dataset?
909     sql << ' '
910     tables = @opts[:delete_from] || @opts[:from][0..0]
911     source_list_append(sql, tables)
912     sql << ' FROM '
913     source_list_append(sql, @opts[:from])
914     select_join_sql(sql)
915   else
916     super
917   end
918 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
921 def insert_columns_sql(sql)
922   values = opts[:values]
923   if values.is_a?(Array) && values.empty?
924     sql << " ()"
925   else
926     super
927   end
928 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

    # File lib/sequel/adapters/shared/mysql.rb
931 def insert_ignore_sql(sql)
932   sql << " IGNORE" if opts[:insert_ignore]
933 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
941 def insert_on_duplicate_key_update_sql(sql)
942   if update_cols = opts[:on_duplicate_key_update]
943     update_vals = nil
944 
945     if update_cols.empty?
946       update_cols = columns
947     elsif update_cols.last.is_a?(Hash)
948       update_vals = update_cols.last
949       update_cols = update_cols[0..-2]
950     end
951 
952     sql << " ON DUPLICATE KEY UPDATE "
953     c = false
954     co = ', '
955     values = '=VALUES('
956     endp = ')'
957     update_cols.each do |col|
958       sql << co if c
959       quote_identifier_append(sql, col)
960       sql << values
961       quote_identifier_append(sql, col)
962       sql << endp
963       c ||= true
964     end
965     if update_vals
966       eq = '='
967       update_vals.map do |col,v| 
968         sql << co if c
969         quote_identifier_append(sql, col)
970         sql << eq
971         literal_append(sql, v)
972         c ||= true
973       end
974     end
975   end
976 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
979 def insert_values_sql(sql)
980   values = opts[:values]
981   if values.is_a?(Array) && values.empty?
982     sql << " VALUES ()"
983   else
984     super
985   end
986 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
989 def join_type_sql(join_type)
990   if join_type == :straight
991     'STRAIGHT_JOIN'
992   else
993     super
994   end
995 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

     # File lib/sequel/adapters/shared/mysql.rb
 998 def limit_sql(sql)
 999   if l = @opts[:limit]
1000     sql << " LIMIT "
1001     literal_append(sql, l)
1002   end
1003 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
1008 def literal_blob_append(sql, v)
1009   if v.empty?
1010     sql << "''"
1011   else
1012     sql << "0x" << v.unpack("H*").first
1013   end
1014 end
literal_false() click to toggle source

Use 0 for false on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1017 def literal_false
1018   '0'
1019 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
1022 def literal_float(v)
1023   if v.infinite? || v.nan?
1024     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
1025   else
1026     super
1027   end
1028 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
1031 def literal_string_append(sql, v)
1032   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
1033 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
1036 def literal_true
1037   '1'
1038 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1041 def multi_insert_sql_strategy
1042   :values
1043 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1045 def non_sql_option?(key)
1046   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1047 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1050 def requires_emulating_nulls_first?
1051   true
1052 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
1084 def select_calc_found_rows_sql(sql)
1085   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1086 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
1062 def select_lock_sql(sql)
1063   lock = @opts[:lock]
1064   if lock == :share
1065     if !db.mariadb? && db.server_version >= 80000
1066       sql << ' FOR SHARE'
1067     else
1068       sql << ' LOCK IN SHARE MODE'
1069     end
1070   else
1071     super
1072   end
1073 
1074   if lock
1075     if @opts[:skip_locked]
1076       sql << " SKIP LOCKED"
1077     elsif @opts[:nowait]
1078       sql << " NOWAIT"
1079     end
1080   end
1081 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1054 def select_only_offset_sql(sql)
1055   sql << " LIMIT "
1056   literal_append(sql, @opts[:offset])
1057   sql << ",18446744073709551615"
1058 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
1089 def select_with_sql_base
1090   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1091 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

    # File lib/sequel/adapters/shared/mysql.rb
936 def update_ignore_sql(sql)
937   sql << " IGNORE" if opts[:update_ignore]
938 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
1094 def uses_with_rollup?
1095   true
1096 end