module Sequel::MySQL::DatasetMethods

  1. lib/sequel/adapters/shared/mysql.rb
Parent: MySQL

Dataset methods shared by datasets that use MySQL databases.

Included modules

  1. Sequel::Dataset::Replace

Constants

APOS = Dataset::APOS  
APOS_RE = Dataset::APOS_RE  
BACKSLASH = Dataset::BACKSLASH  
BACKSLASH_RE = /\\/.freeze  
BACKTICK = '`'.freeze  
BACKTICK_RE = /`/.freeze  
BINARY = 'BINARY '.freeze  
BLOB_START = "0x".freeze  
BOOL_FALSE = '0'.freeze  
BOOL_TRUE = '1'.freeze  
CAST_BITCOMP_CLOSE = " AS SIGNED INTEGER)".freeze  
CAST_BITCOMP_OPEN = "CAST(~".freeze  
COMMA = Dataset::COMMA  
COMMA_SEPARATOR = ', '.freeze  
CONCAT = "CONCAT".freeze  
DELETE_CLAUSE_METHODS = Dataset.clause_methods(:delete, %w'delete from where order limit')  
DOUBLE_APOS = Dataset::DOUBLE_APOS  
DOUBLE_BACKTICK = '``'.freeze  
EMPTY_BLOB = "''".freeze  
EMPTY_COLUMNS = " ()".freeze  
EMPTY_VALUES = " VALUES ()".freeze  
EQ = '='.freeze  
EQ_VALUES = '=VALUES('.freeze  
ESCAPE = Dataset::ESCAPE  
EXPLAIN = 'EXPLAIN '.freeze  
EXPLAIN_EXTENDED = 'EXPLAIN EXTENDED '.freeze  
FOR_SHARE = ' LOCK IN SHARE MODE'.freeze  
FROM = Dataset::FROM  
GROUP_BY = Dataset::GROUP_BY  
HSTAR = "H*".freeze  
IGNORE = " IGNORE".freeze  
INSERT_CLAUSE_METHODS = Dataset.clause_methods(:insert, %w'insert ignore into columns values on_duplicate_key_update')  
LIKE = 'LIKE'.freeze  
LIMIT = Dataset::LIMIT  
MATCH_AGAINST = ["(MATCH ".freeze, " AGAINST (".freeze, "))".freeze].freeze  
MATCH_AGAINST_BOOLEAN = ["(MATCH ".freeze, " AGAINST (".freeze, " IN BOOLEAN MODE))".freeze].freeze  
NATURAL_LEFT_JOIN = 'NATURAL LEFT JOIN'.freeze  
NOT_SPACE = Dataset::NOT_SPACE  
ON_DUPLICATE_KEY_UPDATE = " ON DUPLICATE KEY UPDATE ".freeze  
PAREN_CLOSE = Dataset::PAREN_CLOSE  
PAREN_OPEN = Dataset::PAREN_OPEN  
QUAD_BACKSLASH = "\\\\\\\\".freeze  
REGEXP = 'REGEXP'.freeze  
SELECT_CLAUSE_METHODS = Dataset.clause_methods(:select, %w'select distinct calc_found_rows columns from join where group having compounds order limit lock')  
SPACE = Dataset::SPACE  
SQL_CALC_FOUND_ROWS = ' SQL_CALC_FOUND_ROWS'.freeze  
STRAIGHT_JOIN = 'STRAIGHT_JOIN'.freeze  
UPDATE_CLAUSE_METHODS = Dataset.clause_methods(:update, %w'update ignore table set where order limit')  
WITH_ROLLUP = ' WITH ROLLUP'.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, line 622
def calc_found_rows
  clone(:calc_found_rows => true)
end
complex_expression_sql_append (sql, op, args)

MySQL specific syntax for LIKE/REGEXP searches, as well as string concatenation.

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 574
def complex_expression_sql_append(sql, op, args)
  case op
  when :IN, :"NOT IN"
    ds = args.at(1)
    if ds.is_a?(Sequel::Dataset) && ds.opts[:limit]
      super(sql, op, [args.at(0), ds.from_self])
    else
      super
    end
  when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
    sql << PAREN_OPEN
    literal_append(sql, args.at(0))
    sql << SPACE
    sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)
    sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? REGEXP : LIKE)
    sql << SPACE
    sql << BINARY if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)
    literal_append(sql, args.at(1))
    if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op)
      sql << ESCAPE
      literal_append(sql, BACKSLASH)
    end
    sql << PAREN_CLOSE
  when :'||'
    if args.length > 1
      sql << CONCAT
      array_sql_append(sql, args)
    else
      literal_append(sql, args.at(0))
    end
  when :'B~'
    sql << CAST_BITCOMP_OPEN
    literal_append(sql, args.at(0))
    sql << CAST_BITCOMP_CLOSE
  else
    super
  end
end
distinct (*args)

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

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 614
def distinct(*args)
  args.empty? ? super : group(*args)
end
explain (opts=OPTS)

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

:extended

Use EXPLAIN EXPTENDED instead of EXPLAIN if true.

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 628
def explain(opts=OPTS)
  # Load the PrettyTable class, needed for explain output
  Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable)

  ds = db.send(:metadata_dataset).with_sql((opts[:extended] ? EXPLAIN_EXTENDED : EXPLAIN) + select_sql).naked
  rows = ds.all
  Sequel::PrettyTable.string(rows, ds.columns)
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, line 638
def for_share
  lock_style(:share)
end
full_text_search (cols, terms, opts = OPTS)

Adds full text filter

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 643
def full_text_search(cols, terms, opts = OPTS)
  filter(full_text_sql(cols, terms, opts))
end
full_text_sql (cols, terms, opts = OPTS)

MySQL specific full text search syntax.

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 648
def full_text_sql(cols, terms, opts = OPTS)
  terms = terms.join(' ') if terms.is_a?(Array)
  SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
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, line 682
def insert_ignore
  clone(:insert_ignore=>true)
end
join_table (type, table, expr=nil, opts=OPTS, &block)

Transforms an CROSS JOIN to an INNER JOIN if the expr is not nil. Raises an error on use of :full_outer type, since MySQL doesn’t support it.

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 655
def join_table(type, table, expr=nil, opts=OPTS, &block)
  type = :inner if (type == :cross) && !expr.nil?
  raise(Sequel::Error, "MySQL doesn't support FULL OUTER JOIN") if type == :full_outer
  super(type, table, expr, opts, &block)
end
join_type_sql (join_type)

Transforms :natural_inner to NATURAL LEFT JOIN and straight to STRAIGHT_JOIN.

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 663
def join_type_sql(join_type)
  case join_type
  when :straight
    STRAIGHT_JOIN
  when :natural_inner
    NATURAL_LEFT_JOIN
  else
    super
  end
end
multi_insert_sql (columns, values)

MySQL specific syntax for inserting multiple values at once.

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 710
def multi_insert_sql(columns, values)
  sql = LiteralString.new('VALUES ')
  expression_list_append(sql, values.map{|r| Array(r)})
  [insert_sql(columns, sql)]
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.

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)
[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 705
def on_duplicate_key_update(*args)
  clone(:on_duplicate_key_update => args)
end
quoted_identifier_append (sql, c)

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

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 717
def quoted_identifier_append(sql, c)
  sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK
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, line 723
def supports_distinct_on?
  true
end
supports_group_rollup? ()

MySQL supports GROUP BY WITH ROLLUP (but not CUBE)

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 728
def supports_group_rollup?
  true
end
supports_intersect_except? ()

MySQL does not support INTERSECT or EXCEPT

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 733
def supports_intersect_except?
  false
end
supports_modifying_joins? ()

MySQL supports modifying joined datasets

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 738
def supports_modifying_joins?
  true
end
supports_ordered_distinct_on? ()

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

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 744
def supports_ordered_distinct_on?
  false
end
supports_regexp? ()

MySQL supports pattern matching via regular expressions

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 749
def supports_regexp?
  true
end
supports_timestamp_usecs? ()

MySQL does support fractional timestamps in literal timestamps, but it ignores them. Also, using them seems to cause problems on 1.9. Since they are ignored anyway, not using them is probably best.

[show source] 
# File lib/sequel/adapters/shared/mysql.rb, line 756
def supports_timestamp_usecs?
  false
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, line 766
def update_ignore
  clone(:update_ignore=>true)
end