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
757 def calc_found_rows
758   clone(:calc_found_rows => true)
759 end

complex_expression_sql_append(sql, op, args)

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
691 def complex_expression_sql_append(sql, op, args)
692   case op
693   when :IN, :"NOT IN"
694     ds = args[1]
695     if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
696       super(sql, op, [args[0], ds.from_self])
697     else
698       super
699     end
700   when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
701     if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op)
702       func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c')
703       func = ~func if op == :'!~'
704       return literal_append(sql, func)
705     end
706 
707     sql << '('
708     literal_append(sql, args[0])
709     sql << ' '
710     sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
711     sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE')
712     sql << ' '
713     sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
714     literal_append(sql, args[1])
715     if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
716       sql << " ESCAPE "
717       literal_append(sql, "\\")
718     end
719     sql << ')'
720   when :'||'
721     if args.length > 1
722       sql << "CONCAT"
723       array_sql_append(sql, args)
724     else
725       literal_append(sql, args[0])
726     end
727   when :'B~'
728     sql << "CAST(~"
729     literal_append(sql, args[0])
730     sql << " AS SIGNED INTEGER)"
731   else
732     super
733   end
734 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
740 def constant_sql_append(sql, constant)
741   if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs?
742     sql << 'CURRENT_TIMESTAMP(6)'
743   else
744     super
745   end
746 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
769 def delete_from(*tables)
770   clone(:delete_from=>tables)
771 end

distinct(*args)

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

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
749 def distinct(*args)
750   args.empty? ? super : group(*args)
751 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
775 def explain(opts=OPTS)
776   # Load the PrettyTable class, needed for explain output
777   Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)
778 
779   ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked
780   rows = ds.all
781   Sequel::PrettyTable.string(rows, ds.columns)
782 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
785 def for_share
786   lock_style(:share)
787 end

full_text_search(cols, terms, opts = OPTS)

Adds full text filter

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
790 def full_text_search(cols, terms, opts = OPTS)
791   where(full_text_sql(cols, terms, opts))
792 end

full_text_sql(cols, terms, opts = OPTS)

MySQL specific full text search syntax.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
795 def full_text_sql(cols, terms, opts = OPTS)
796   terms = terms.join(' ') if terms.is_a?(Array)
797   SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
798 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
808 def insert_ignore
809   clone(:insert_ignore=>true)
810 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
814 def insert_select(*values)
815   return unless supports_insert_select?
816   # Handle case where query does not return a row
817   server?(:default).with_sql_first(insert_select_sql(*values)) || false
818 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
822 def insert_select_sql(*values)
823   ds = opts[:returning] ? self : returning
824   ds.insert_sql(*values)
825 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
856 def on_duplicate_key_update(*args)
857   clone(:on_duplicate_key_update => args)
858 end

quoted_identifier_append(sql, c)

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

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
861 def quoted_identifier_append(sql, c)
862   sql << '`' << c.to_s.gsub('`', '``') << '`'
863 end

supports_cte?(type=:select)

MariaDB 10.2+ and MySQL 8+ support CTEs

[show source]

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

supports_derived_column_lists?()

MySQL does not support derived column lists

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
878 def supports_derived_column_lists?
879   false
880 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
884 def supports_distinct_on?
885   true
886 end

supports_group_rollup?()

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

[show source]

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

supports_intersect_except?()

MariaDB 10.3+ supports INTERSECT or EXCEPT

[show source]

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

supports_modifying_joins?()

MySQL supports modifying joined datasets

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
904 def supports_modifying_joins?
905   true
906 end

supports_nowait?()

MySQL 8+ and MariaDB 10.3+ support NOWAIT.

[show source]

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

supports_regexp?()

MySQL supports pattern matching via regular expressions

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
920 def supports_regexp?
921   true
922 end

supports_returning?(type)

MariaDB 10.5.0 supports INSERT RETURNING.

[show source]

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

supports_skip_locked?()

MySQL 8+ and MariaDB 10.6+ support SKIP LOCKED.

[show source]

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

supports_timestamp_usecs?()

Check the database setting for whether fractional timestamps are suppported.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
936 def supports_timestamp_usecs?
937   db.supports_timestamp_usecs?
938 end

supports_window_clause?()

MySQL 8+ supports WINDOW clause.

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
941 def supports_window_clause?
942   !db.mariadb? && db.server_version >= 80000
943 end

supports_window_functions?()

MariaDB 10.2+ and MySQL 8+ support window functions

[show source]

    # File lib/sequel/adapters/shared/mysql.rb
946 def supports_window_functions?
947   db.server_version >= (db.mariadb? ? 100200 : 80000)
948 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
956 def update_ignore
957   clone(:update_ignore=>true)
958 end

module Sequel::MySQL::DatasetMethods

Methods

Public Instance

Included modules

Constants

Public Instance methods