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
677 def calc_found_rows
678   clone(:calc_found_rows => true)
679 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
611 def complex_expression_sql_append(sql, op, args)
612   case op
613   when :IN, :"NOT IN"
614     ds = args[1]
615     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
616       super(sql, op, [args[0], ds.from_self])
617     else
618       super
619     end
620   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
621     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
622       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
623       func = ~func if op == :'!~'
624       return literal_append(sql, func)
625     end
626 
627     sql << '('
628     literal_append(sql, args[0])
629     sql << ' '
630     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
631     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
632     sql << ' '
633     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
634     literal_append(sql, args[1])
635     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
636       sql << " ESCAPE "
637       literal_append(sql, "\\")
638     end
639     sql << ')'
640   when :'||'
641     if args.length > 1
642       sql << "CONCAT"
643       array_sql_append(sql, args)
644     else
645       literal_append(sql, args[0])
646     end
647   when :'B~'
648     sql << "CAST(~"
649     literal_append(sql, args[0])
650     sql << " AS SIGNED INTEGER)"
651   else
652     super
653   end
654 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
660 def constant_sql_append(sql, constant)
661   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
662     sql << 'CURRENT_TIMESTAMP(6)'
663   else
664     super
665   end
666 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
689 def delete_from(*tables)
690   clone(:delete_from=>tables)
691 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
669 def distinct(*args)
670   args.empty? ? super : group(*args)
671 end
explain(opts=OPTS) click to toggle source

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

:extended

Use EXPLAIN EXPTENDED instead of EXPLAIN if true.

    # File lib/sequel/adapters/shared/mysql.rb
695 def explain(opts=OPTS)
696   # Load the PrettyTable class, needed for explain output
697   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
698 
699   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
700   rows = ds.all
701   Sequel::PrettyTable.string(rows, ds.columns)
702 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
705 def for_share
706   lock_style(:share)
707 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
715 def full_text_sql(cols, terms, opts = OPTS)
716   terms = terms.join(' ') if terms.is_a?(Array)
717   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
718 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
737 def insert_ignore
738   clone(:insert_ignore=>true)
739 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
721 def join_type_sql(join_type)
722   if join_type == :straight
723     'STRAIGHT_JOIN'
724   else
725     super
726   end
727 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
770 def on_duplicate_key_update(*args)
771   clone(:on_duplicate_key_update => args)
772 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
775 def quoted_identifier_append(sql, c)
776   sql << '`' << c.to_s.gsub('`', '``') << '`'
777 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
780 def supports_cte?(type=:select)
781   if db.mariadb?
782     type == :select && db.server_version >= 100200
783   else
784     case type
785     when :select, :update, :delete
786       db.server_version >= 80000
787     end
788   end
789 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
792 def supports_derived_column_lists?
793   false
794 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
798 def supports_distinct_on?
799   true
800 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
803 def supports_group_rollup?
804   true
805 end
supports_intersect_except?() click to toggle source

MariaDB 10.3+ supports INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
808 def supports_intersect_except?
809   db.mariadb? && db.server_version >= 100300
810 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
813 def supports_limits_in_correlated_subqueries?
814   false
815 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
818 def supports_modifying_joins?
819   true
820 end
supports_nowait?() click to toggle source

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
823 def supports_nowait?
824   db.server_version >= (db.mariadb? ? 100300 : 80000)
825 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
829 def supports_ordered_distinct_on?
830   false
831 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
834 def supports_regexp?
835   true
836 end
supports_skip_locked?() click to toggle source

MySQL 8+ supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
839 def supports_skip_locked?
840   !db.mariadb? && db.server_version >= 80000
841 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
845 def supports_timestamp_usecs?
846   db.supports_timestamp_usecs?
847 end
supports_window_clause?() click to toggle source

MySQL 8+ supports WINDOW clause.

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

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
855 def supports_window_functions?
856   db.server_version >= (db.mariadb? ? 100200 : 80000)
857 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
865 def update_ignore
866   clone(:update_ignore=>true)
867 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
872 def check_not_limited!(type)
873   super if type == :truncate || @opts[:offset]
874 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
878 def delete_from_sql(sql)
879   if joined_dataset?
880     sql << ' '
881     tables = @opts[:delete_from] || @opts[:from][0..0]
882     source_list_append(sql, tables)
883     sql << ' FROM '
884     source_list_append(sql, @opts[:from])
885     select_join_sql(sql)
886   else
887     super
888   end
889 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
892 def insert_columns_sql(sql)
893   values = opts[:values]
894   if values.is_a?(Array) && values.empty?
895     sql << " ()"
896   else
897     super
898   end
899 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

    # File lib/sequel/adapters/shared/mysql.rb
902 def insert_ignore_sql(sql)
903   sql << " IGNORE" if opts[:insert_ignore]
904 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
912 def insert_on_duplicate_key_update_sql(sql)
913   if update_cols = opts[:on_duplicate_key_update]
914     update_vals = nil
915 
916     if update_cols.empty?
917       update_cols = columns
918     elsif update_cols.last.is_a?(Hash)
919       update_vals = update_cols.last
920       update_cols = update_cols[0..-2]
921     end
922 
923     sql << " ON DUPLICATE KEY UPDATE "
924     c = false
925     co = ', '
926     values = '=VALUES('
927     endp = ')'
928     update_cols.each do |col|
929       sql << co if c
930       quote_identifier_append(sql, col)
931       sql << values
932       quote_identifier_append(sql, col)
933       sql << endp
934       c ||= true
935     end
936     if update_vals
937       eq = '='
938       update_vals.map do |col,v| 
939         sql << co if c
940         quote_identifier_append(sql, col)
941         sql << eq
942         literal_append(sql, v)
943         c ||= true
944       end
945     end
946   end
947 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
950 def insert_values_sql(sql)
951   values = opts[:values]
952   if values.is_a?(Array) && values.empty?
953     sql << " VALUES ()"
954   else
955     super
956   end
957 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

    # File lib/sequel/adapters/shared/mysql.rb
960 def limit_sql(sql)
961   if l = @opts[:limit]
962     sql << " LIMIT "
963     literal_append(sql, l)
964   end
965 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
970 def literal_blob_append(sql, v)
971   if v.empty?
972     sql << "''"
973   else
974     sql << "0x" << v.unpack("H*").first
975   end
976 end
literal_false() click to toggle source

Use 0 for false on MySQL

    # File lib/sequel/adapters/shared/mysql.rb
979 def literal_false
980   '0'
981 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
984 def literal_float(v)
985   if v.infinite? || v.nan?
986     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
987   else
988     super
989   end
990 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
993 def literal_string_append(sql, v)
994   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
995 end
literal_true() click to toggle source

Use 1 for true on MySQL

     # File lib/sequel/adapters/shared/mysql.rb
 998 def literal_true
 999   '1'
1000 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

     # File lib/sequel/adapters/shared/mysql.rb
1003 def multi_insert_sql_strategy
1004   :values
1005 end
non_sql_option?(key) click to toggle source
Calls superclass method
     # File lib/sequel/adapters/shared/mysql.rb
1007 def non_sql_option?(key)
1008   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
1009 end
requires_emulating_nulls_first?() click to toggle source

MySQL does not natively support NULLS FIRST/LAST.

     # File lib/sequel/adapters/shared/mysql.rb
1012 def requires_emulating_nulls_first?
1013   true
1014 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
1046 def select_calc_found_rows_sql(sql)
1047   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1048 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
1024 def select_lock_sql(sql)
1025   lock = @opts[:lock]
1026   if lock == :share
1027     if !db.mariadb? && db.server_version >= 80000
1028       sql << ' FOR SHARE'
1029     else
1030       sql << ' LOCK IN SHARE MODE'
1031     end
1032   else
1033     super
1034   end
1035 
1036   if lock
1037     if @opts[:skip_locked]
1038       sql << " SKIP LOCKED"
1039     elsif @opts[:nowait]
1040       sql << " NOWAIT"
1041     end
1042   end
1043 end
select_only_offset_sql(sql) click to toggle source
     # File lib/sequel/adapters/shared/mysql.rb
1016 def select_only_offset_sql(sql)
1017   sql << " LIMIT "
1018   literal_append(sql, @opts[:offset])
1019   sql << ",18446744073709551615"
1020 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
1051 def select_with_sql_base
1052   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1053 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

    # File lib/sequel/adapters/shared/mysql.rb
907 def update_ignore_sql(sql)
908   sql << " IGNORE" if opts[:update_ignore]
909 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
1056 def uses_with_rollup?
1057   true
1058 end