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
666 def calc_found_rows
667   clone(:calc_found_rows => true)
668 end
complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
600 def complex_expression_sql_append(sql, op, args)
601   case op
602   when :IN, :"NOT IN"
603     ds = args[1]
604     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
605       super(sql, op, [args[0], ds.from_self])
606     else
607       super
608     end
609   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
610     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
611       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
612       func = ~func if op == :'!~'
613       return literal_append(sql, func)
614     end
615 
616     sql << '('
617     literal_append(sql, args[0])
618     sql << ' '
619     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
620     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
621     sql << ' '
622     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
623     literal_append(sql, args[1])
624     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
625       sql << " ESCAPE "
626       literal_append(sql, "\\")
627     end
628     sql << ')'
629   when :'||'
630     if args.length > 1
631       sql << "CONCAT"
632       array_sql_append(sql, args)
633     else
634       literal_append(sql, args[0])
635     end
636   when :'B~'
637     sql << "CAST(~"
638     literal_append(sql, args[0])
639     sql << " AS SIGNED INTEGER)"
640   else
641     super
642   end
643 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
649 def constant_sql_append(sql, constant)
650   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
651     sql << 'CURRENT_TIMESTAMP(6)'
652   else
653     super
654   end
655 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
678 def delete_from(*tables)
679   clone(:delete_from=>tables)
680 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
658 def distinct(*args)
659   args.empty? ? super : group(*args)
660 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
684 def explain(opts=OPTS)
685   # Load the PrettyTable class, needed for explain output
686   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
687 
688   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
689   rows = ds.all
690   Sequel::PrettyTable.string(rows, ds.columns)
691 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
694 def for_share
695   lock_style(:share)
696 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
704 def full_text_sql(cols, terms, opts = OPTS)
705   terms = terms.join(' ') if terms.is_a?(Array)
706   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
707 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
726 def insert_ignore
727   clone(:insert_ignore=>true)
728 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
710 def join_type_sql(join_type)
711   if join_type == :straight
712     'STRAIGHT_JOIN'
713   else
714     super
715   end
716 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
759 def on_duplicate_key_update(*args)
760   clone(:on_duplicate_key_update => args)
761 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
764 def quoted_identifier_append(sql, c)
765   sql << '`' << c.to_s.gsub('`', '``') << '`'
766 end
supports_cte?(type=:select) click to toggle source

MariaDB 10.2+ and MySQL 8+ support CTEs

    # File lib/sequel/adapters/shared/mysql.rb
769 def supports_cte?(type=:select)
770   if db.mariadb?
771     type == :select && db.server_version >= 100200
772   else
773     case type
774     when :select, :update, :delete
775       db.server_version >= 80000
776     end
777   end
778 end
supports_derived_column_lists?() click to toggle source

MySQL does not support derived column lists

    # File lib/sequel/adapters/shared/mysql.rb
781 def supports_derived_column_lists?
782   false
783 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
787 def supports_distinct_on?
788   true
789 end
supports_group_rollup?() click to toggle source

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

    # File lib/sequel/adapters/shared/mysql.rb
792 def supports_group_rollup?
793   true
794 end
supports_intersect_except?() click to toggle source

MySQL does not support INTERSECT or EXCEPT

    # File lib/sequel/adapters/shared/mysql.rb
797 def supports_intersect_except?
798   false
799 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
802 def supports_limits_in_correlated_subqueries?
803   false
804 end
supports_modifying_joins?() click to toggle source

MySQL supports modifying joined datasets

    # File lib/sequel/adapters/shared/mysql.rb
807 def supports_modifying_joins?
808   true
809 end
supports_nowait?() click to toggle source

MySQL 8+ supports NOWAIT.

    # File lib/sequel/adapters/shared/mysql.rb
812 def supports_nowait?
813   !db.mariadb? && db.server_version >= 80000
814 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
818 def supports_ordered_distinct_on?
819   false
820 end
supports_regexp?() click to toggle source

MySQL supports pattern matching via regular expressions

    # File lib/sequel/adapters/shared/mysql.rb
823 def supports_regexp?
824   true
825 end
supports_skip_locked?() click to toggle source

MySQL 8+ supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/mysql.rb
828 def supports_skip_locked?
829   !db.mariadb? && db.server_version >= 80000
830 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
834 def supports_timestamp_usecs?
835   db.supports_timestamp_usecs?
836 end
supports_window_functions?() click to toggle source

MariaDB 10.2+ and MySQL 8+ support window functions

    # File lib/sequel/adapters/shared/mysql.rb
839 def supports_window_functions?
840   db.server_version >= (db.mariadb? ? 100200 : 80000)
841 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
849 def update_ignore
850   clone(:update_ignore=>true)
851 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
856 def check_not_limited!(type)
857   super if type == :truncate || @opts[:offset]
858 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
862 def delete_from_sql(sql)
863   if joined_dataset?
864     sql << ' '
865     tables = @opts[:delete_from] || @opts[:from][0..0]
866     source_list_append(sql, tables)
867     sql << ' FROM '
868     source_list_append(sql, @opts[:from])
869     select_join_sql(sql)
870   else
871     super
872   end
873 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
876 def insert_columns_sql(sql)
877   values = opts[:values]
878   if values.is_a?(Array) && values.empty?
879     sql << " ()"
880   else
881     super
882   end
883 end
insert_ignore_sql(sql) click to toggle source

MySQL supports INSERT IGNORE INTO

    # File lib/sequel/adapters/shared/mysql.rb
886 def insert_ignore_sql(sql)
887   sql << " IGNORE" if opts[:insert_ignore]
888 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
896 def insert_on_duplicate_key_update_sql(sql)
897   if update_cols = opts[:on_duplicate_key_update]
898     update_vals = nil
899 
900     if update_cols.empty?
901       update_cols = columns
902     elsif update_cols.last.is_a?(Hash)
903       update_vals = update_cols.last
904       update_cols = update_cols[0..-2]
905     end
906 
907     sql << " ON DUPLICATE KEY UPDATE "
908     c = false
909     co = ', '
910     values = '=VALUES('
911     endp = ')'
912     update_cols.each do |col|
913       sql << co if c
914       quote_identifier_append(sql, col)
915       sql << values
916       quote_identifier_append(sql, col)
917       sql << endp
918       c ||= true
919     end
920     if update_vals
921       eq = '='
922       update_vals.map do |col,v| 
923         sql << co if c
924         quote_identifier_append(sql, col)
925         sql << eq
926         literal_append(sql, v)
927         c ||= true
928       end
929     end
930   end
931 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
934 def insert_values_sql(sql)
935   values = opts[:values]
936   if values.is_a?(Array) && values.empty?
937     sql << " VALUES ()"
938   else
939     super
940   end
941 end
limit_sql(sql) click to toggle source

MySQL allows a LIMIT in DELETE and UPDATE statements.

    # File lib/sequel/adapters/shared/mysql.rb
944 def limit_sql(sql)
945   if l = @opts[:limit]
946     sql << " LIMIT "
947     literal_append(sql, l)
948   end
949 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
954 def literal_blob_append(sql, v)
955   if v.empty?
956     sql << "''"
957   else
958     sql << "0x" << v.unpack("H*").first
959   end
960 end
literal_false() click to toggle source

Use 0 for false on MySQL

    # File lib/sequel/adapters/shared/mysql.rb
963 def literal_false
964   '0'
965 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
968 def literal_float(v)
969   if v.infinite? || v.nan?
970     raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL"
971   else
972     super
973   end
974 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
977 def literal_string_append(sql, v)
978   sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'"
979 end
literal_true() click to toggle source

Use 1 for true on MySQL

    # File lib/sequel/adapters/shared/mysql.rb
982 def literal_true
983   '1'
984 end
multi_insert_sql_strategy() click to toggle source

MySQL supports multiple rows in VALUES in INSERT.

    # File lib/sequel/adapters/shared/mysql.rb
987 def multi_insert_sql_strategy
988   :values
989 end
non_sql_option?(key) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/mysql.rb
991 def non_sql_option?(key)
992   super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update
993 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
1025 def select_calc_found_rows_sql(sql)
1026   sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows]
1027 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
1003 def select_lock_sql(sql)
1004   lock = @opts[:lock]
1005   if lock == :share
1006     if !db.mariadb? && db.server_version >= 80000
1007       sql << ' FOR SHARE'
1008     else
1009       sql << ' LOCK IN SHARE MODE'
1010     end
1011   else
1012     super
1013   end
1014 
1015   if lock
1016     if @opts[:skip_locked]
1017       sql << " SKIP LOCKED"
1018     elsif @opts[:nowait]
1019       sql << " NOWAIT"
1020     end
1021   end
1022 end
select_only_offset_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/mysql.rb
995 def select_only_offset_sql(sql)
996   sql << " LIMIT "
997   literal_append(sql, @opts[:offset])
998   sql << ",18446744073709551615"
999 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
1030 def select_with_sql_base
1031   opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super
1032 end
update_ignore_sql(sql) click to toggle source

MySQL supports UPDATE IGNORE

    # File lib/sequel/adapters/shared/mysql.rb
891 def update_ignore_sql(sql)
892   sql << " IGNORE" if opts[:update_ignore]
893 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
1035 def uses_with_rollup?
1036   true
1037 end