Sequel::MySQL::DatasetMethods

Dataset methods shared by datasets that use MySQL databases.

Included modules

Constants

MATCH_AGAINST	=	["MATCH ".freeze, " AGAINST (".freeze, ")".freeze].freeze
MATCH_AGAINST_BOOLEAN	=	["MATCH ".freeze, " AGAINST (".freeze, " IN BOOLEAN MODE)".freeze].freeze

Public Instance methods

calc_found_rows()

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

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
761 def calc_found_rows
762   clone(:calc_found_rows => true)
763 end

complex_expression_sql_append(sql, op, args)

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
695 def complex_expression_sql_append(sql, op, args)
696   case op
697   when :IN, :"NOT IN"
698     ds = args[1]
699     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
700       super(sql, op, [args[0], ds.from_self])
701     else
702       super
703     end
704   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
705     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
706       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
707       func = ~func if op == :'!~'
708       return literal_append(sql, func)
709     end
710 
711     sql << '('
712     literal_append(sql, args[0])
713     sql << ' '
714     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
715     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
716     sql << ' '
717     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
718     literal_append(sql, args[1])
719     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
720       sql << " ESCAPE "
721       literal_append(sql, "\\")
722     end
723     sql << ')'
724   when :'||'
725     if args.length > 1
726       sql << "CONCAT"
727       array_sql_append(sql, args)
728     else
729       literal_append(sql, args[0])
730     end
731   when :'B~'
732     sql << "CAST(~"
733     literal_append(sql, args[0])
734     sql << " AS SIGNED INTEGER)"
735   else
736     super
737   end
738 end

constant_sql_append(sql, constant)

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.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
744 def constant_sql_append(sql, constant)
745   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
746     sql << 'CURRENT_TIMESTAMP(6)'
747   else
748     super
749   end
750 end

delete_from(*tables)

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)

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
773 def delete_from(*tables)
774   clone(:delete_from=>tables)
775 end

distinct(*args)

Use GROUP BY instead of DISTINCT ON if arguments are provided.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
753 def distinct(*args)
754   args.empty? ? super : group(*args)
755 end

explain(opts=OPTS)

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

:extended

Use EXPLAIN EXTENDED instead of EXPLAIN if true.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
779 def explain(opts=OPTS)
780   # Load the PrettyTable class, needed for explain output
781   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
782 
783   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
784   rows = ds.all
785   Sequel::PrettyTable.string(rows, ds.columns)
786 end

for_share()

Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
789 def for_share
790   lock_style(:share)
791 end

full_text_search(cols, terms, opts = OPTS)

Adds full text filter

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
794 def full_text_search(cols, terms, opts = OPTS)
795   where(full_text_sql(cols, terms, opts))
796 end

full_text_sql(cols, terms, opts = OPTS)

MySQL specific full text search syntax.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
799 def full_text_sql(cols, terms, opts = OPTS)
800   terms = terms.join(' ') if terms.is_a?(Array)
801   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
802 end

insert_ignore()

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)

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
812 def insert_ignore
813   clone(:insert_ignore=>true)
814 end

insert_select(*values)

Support insert select for associations, so that the model code can use returning instead of a separate query.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
818 def insert_select(*values)
819   return unless supports_insert_select?
820   # Handle case where query does not return a row
821   server?(:default).with_sql_first(insert_select_sql(*values)) || false
822 end

insert_select_sql(*values)

The SQL to use for an insert_select, adds a RETURNING clause to the insert unless the RETURNING clause is already present.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
826 def insert_select_sql(*values)
827   ds = opts[:returning] ? self : returning
828   ds.insert_sql(*values)
829 end

on_duplicate_key_update(*args)

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)

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
860 def on_duplicate_key_update(*args)
861   clone(:on_duplicate_key_update => args)
862 end

quoted_identifier_append(sql, c)

MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
865 def quoted_identifier_append(sql, c)
866   sql << '`' << c.to_s.gsub('`', '``') << '`'
867 end

supports_cte?(type=:select)

MariaDB 10.2+ and MySQL 8+ support CTEs

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
870 def supports_cte?(type=:select)
871   if db.mariadb?
872     type == :select && db.server_version >= 100200
873   else
874     case type
875     when :select, :update, :delete
876       db.server_version >= 80000
877     end
878   end
879 end

supports_derived_column_lists?()

MySQL does not support derived column lists

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
882 def supports_derived_column_lists?
883   false
884 end

supports_distinct_on?()

MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
888 def supports_distinct_on?
889   true
890 end

supports_group_rollup?()

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
893 def supports_group_rollup?
894   true
895 end

supports_intersect_except?()

MariaDB 10.3+ supports INTERSECT or EXCEPT

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
898 def supports_intersect_except?
899   db.mariadb? && db.server_version >= 100300
900 end

supports_limits_in_correlated_subqueries?()

MySQL does not support limits in correlated subqueries (or any subqueries that use IN).

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
903 def supports_limits_in_correlated_subqueries?
904   false
905 end

supports_modifying_joins?()

MySQL supports modifying joined datasets

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
908 def supports_modifying_joins?
909   true
910 end

supports_nowait?()

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
913 def supports_nowait?
914   db.server_version >= (db.mariadb? ? 100300 : 80000)
915 end

supports_ordered_distinct_on?()

MySQL’s DISTINCT ON emulation using GROUP BY does not respect the query’s ORDER BY clause.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
919 def supports_ordered_distinct_on?
920   false
921 end

supports_regexp?()

MySQL supports pattern matching via regular expressions

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
924 def supports_regexp?
925   true
926 end

supports_returning?(type)

MariaDB 10.5.0 supports INSERT RETURNING.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
929 def supports_returning?(type)
930   (type == :insert && db.mariadb? && db.adapter_scheme != :jdbc) ? (db.server_version >= 100500) : false
931 end

supports_skip_locked?()

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
934 def supports_skip_locked?
935   db.server_version >= (db.mariadb? ? 100600 : 80000)
936 end

supports_timestamp_usecs?()

Check the database setting for whether fractional timestamps are suppported.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
940 def supports_timestamp_usecs?
941   db.supports_timestamp_usecs?
942 end

supports_window_clause?()

MySQL 8+ supports WINDOW clause.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
945 def supports_window_clause?
946   !db.mariadb? && db.server_version >= 80000
947 end

supports_window_functions?()

MariaDB 10.2+ and MySQL 8+ support window functions

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
950 def supports_window_functions?
951   db.server_version >= (db.mariadb? ? 100200 : 80000)
952 end

update_ignore()

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

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
960 def update_ignore
961   clone(:update_ignore=>true)
962 end

module Sequel::MySQL::DatasetMethods

Methods

Public Instance

Included modules

Constants

Public Instance methods