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   sql = ((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql
784   ds = db.send(:metadata_dataset).with_sql(sql.freeze).naked
785   rows = ds.all
786   Sequel::PrettyTable.string(rows, ds.columns)
787 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
790 def for_share
791   cached_lock_style_dataset(:_for_share_ds, :share)
792 end

full_text_search(cols, terms, opts = OPTS)

Adds full text filter

[show source]

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

full_text_sql(cols, terms, opts = OPTS)

MySQL specific full text search syntax.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
800 def full_text_sql(cols, terms, opts = OPTS)
801   terms = Sequel.array_or_set_join(terms, ' ') if terms.is_a?(Array) || terms.is_a?(Set)
802   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
803 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
813 def insert_ignore
814   clone(:insert_ignore=>true)
815 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
819 def insert_select(*values)
820   return unless supports_insert_select?
821   # Handle case where query does not return a row
822   server?(:default).with_sql_first(insert_select_sql(*values)) || false
823 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
827 def insert_select_sql(*values)
828   ds = opts[:returning] ? self : returning
829   ds.insert_sql(*values)
830 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
861 def on_duplicate_key_update(*args)
862   clone(:on_duplicate_key_update => args)
863 end

quoted_identifier_append(sql, c)

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

[show source]

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

supports_cte?(type=:select)

MariaDB 10.2+ and MySQL 8+ support CTEs

[show source]

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

supports_derived_column_lists?()

MySQL does not support derived column lists

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
883 def supports_derived_column_lists?
884   false
885 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
889 def supports_distinct_on?
890   true
891 end

supports_group_rollup?()

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

[show source]

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

supports_intersect_except?()

MariaDB 10.3+ supports INTERSECT or EXCEPT

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
899 def supports_intersect_except?
900   db.mariadb? && db.server_version >= 100300
901 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
904 def supports_limits_in_correlated_subqueries?
905   false
906 end

supports_modifying_joins?()

MySQL supports modifying joined datasets

[show source]

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

supports_nowait?()

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
914 def supports_nowait?
915   db.server_version >= (db.mariadb? ? 100300 : 80000)
916 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
920 def supports_ordered_distinct_on?
921   false
922 end

supports_regexp?()

MySQL supports pattern matching via regular expressions

[show source]

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

supports_returning?(type)

MariaDB 10.5.0 supports INSERT RETURNING.

[show source]

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

supports_skip_locked?()

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

[show source]

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

supports_timestamp_usecs?()

Check the database setting for whether fractional timestamps are suppported.

[show source]

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

supports_window_clause?()

MySQL 8+ supports WINDOW clause.

[show source]

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

supports_window_functions?()

MariaDB 10.2+ and MySQL 8+ support window functions

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
951 def supports_window_functions?
952   db.server_version >= (db.mariadb? ? 100200 : 80000)
953 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
961 def update_ignore
962   clone(:update_ignore=>true)
963 end

module Sequel::MySQL::DatasetMethods

Methods

Public Instance

Included modules

Constants

Public Instance methods