Module: Sequel::MySQL::DatasetMethods

Included in:

DataObjects::MySQL::Dataset, JDBC::MySQL::Dataset, Dataset, Sequel::Mysql2::Dataset, Swift::MySQL::Dataset

Defined in:

lib/sequel/adapters/shared/mysql.rb

Overview

Constant Summary

BOOL_TRUE =

'1'.freeze

BOOL_FALSE =

'0'.freeze

COMMA_SEPARATOR =

', '.freeze

FOR_SHARE =

' LOCK IN SHARE MODE'.freeze

SQL_CALC_FOUND_ROWS =

' SQL_CALC_FOUND_ROWS'.freeze

DELETE_CLAUSE_METHODS =

Dataset.clause_methods(:delete, %w'delete from where order limit')

INSERT_CLAUSE_METHODS =

Dataset.clause_methods(:insert, %w'insert ignore into columns values on_duplicate_key_update')

SELECT_CLAUSE_METHODS =

Dataset.clause_methods(:select, %w'select distinct calc_found_rows columns from join where group having compounds order limit lock')

UPDATE_CLAUSE_METHODS =

Dataset.clause_methods(:update, %w'update ignore table set where order limit')

SPACE =

Dataset::SPACE

PAREN_OPEN =

Dataset::PAREN_OPEN

PAREN_CLOSE =

Dataset::PAREN_CLOSE

NOT_SPACE =

Dataset::NOT_SPACE

FROM =

Dataset::FROM

INSERT =

Dataset::INSERT

COMMA =

Dataset::COMMA

LIMIT =

Dataset::LIMIT

GROUP_BY =

Dataset::GROUP_BY

REGEXP =

'REGEXP'.freeze

LIKE =

'LIKE'.freeze

BINARY =

'BINARY '.freeze

CONCAT =

"CONCAT".freeze

CAST_BITCOMP_OPEN =

"CAST(~".freeze

CAST_BITCOMP_CLOSE =

" AS SIGNED INTEGER)".freeze

STRAIGHT_JOIN =

'STRAIGHT_JOIN'.freeze

NATURAL_LEFT_JOIN =

'NATURAL LEFT JOIN'.freeze

BACKTICK =

'`'.freeze

BACKTICK_RE =

/`/.freeze

DOUBLE_BACKTICK =

'``'.freeze

EMPTY_COLUMNS =

" ()".freeze

EMPTY_VALUES =

" VALUES ()".freeze

IGNORE =

" IGNORE".freeze

REPLACE =

'REPLACE'.freeze

ON_DUPLICATE_KEY_UPDATE =

" ON DUPLICATE KEY UPDATE ".freeze

EQ_VALUES =

'=VALUES('.freeze

EQ =

'='.freeze

WITH_ROLLUP =

' WITH ROLLUP'.freeze

MATCH_AGAINST =

["(MATCH ".freeze, " AGAINST (".freeze, "))".freeze].freeze

MATCH_AGAINST_BOOLEAN =

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

Instance Method Summary (collapse)

• - (Object) calc_found_rows

  Sets up the select methods to use SQL_CALC_FOUND_ROWS option.

• - (Object) complex_expression_sql_append(sql, op, args)

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

• - (Object) distinct(*args)

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

• - (Object) for_share

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

• - (Object) full_text_search(cols, terms, opts = {})

  Adds full text filter.

• - (Object) full_text_sql(cols, terms, opts = {})

  MySQL specific full text search syntax.

• - (Object) having(*cond, &block)

  MySQL allows HAVING clause on ungrouped datasets.

• - (Object) insert_ignore

  Sets up the insert methods to use INSERT IGNORE.

• - (Object) join_table(type, table, expr = nil, table_alias = {}, &block)

  Transforms an CROSS JOIN to an INNER JOIN if the expr is not nil.

• - (Object) join_type_sql(join_type)

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

• - (Object) multi_insert_sql(columns, values)

  MySQL specific syntax for inserting multiple values at once.

• - (Object) 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.

• - (Object) quoted_identifier_append(sql, c)

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

• - (Object) replace_sql(*values)

  MySQL specific syntax for REPLACE (aka UPSERT, or update if exists, insert if it doesn't).

• - (Boolean) 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.

• - (Boolean) supports_group_rollup?

  MySQL supports GROUP BY WITH ROLLUP (but not CUBE).

• - (Boolean) supports_intersect_except?

  MySQL does not support INTERSECT or EXCEPT.

• - (Boolean) supports_modifying_joins?

  MySQL supports modifying joined datasets.

• - (Boolean) supports_ordered_distinct_on?

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

• - (Boolean) supports_timestamp_usecs?

  MySQL does support fractional timestamps in literal timestamps, but it ignores them.

• - (Object) update_ignore

  Sets up the update methods to use UPDATE IGNORE.

dataset.calc_found_rows.limit(10)
# SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10

469
470
471

# File 'lib/sequel/adapters/shared/mysql.rb', line 469

def calc_found_rows
  clone(:calc_found_rows => true)
end

425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458

# File 'lib/sequel/adapters/shared/mysql.rb', line 425

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))
    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

461
462
463

# File 'lib/sequel/adapters/shared/mysql.rb', line 461

def distinct(*args)
  args.empty? ? super : group(*args)
end

474
475
476

# File 'lib/sequel/adapters/shared/mysql.rb', line 474

def for_share
  lock_style(:share)
end

479
480
481

# File 'lib/sequel/adapters/shared/mysql.rb', line 479

def full_text_search(cols, terms, opts = {})
  filter(full_text_sql(cols, terms, opts))
end

484
485
486
487

# File 'lib/sequel/adapters/shared/mysql.rb', line 484

def full_text_sql(cols, terms, opts = {})
  terms = terms.join(' ') if terms.is_a?(Array)
  SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms])
end

490
491
492

# File 'lib/sequel/adapters/shared/mysql.rb', line 490

def having(*cond, &block)
  _filter(:having, *cond, &block)
end

dataset.insert_ignore.multi_insert(
 [{:name => 'a', :value => 1}, {:name => 'b', :value => 2}]
)
# INSERT IGNORE INTO tablename (name, value) VALUES (a, 1), (b, 2)

523
524
525

# File 'lib/sequel/adapters/shared/mysql.rb', line 523

def insert_ignore
  clone(:insert_ignore=>true)
end

496
497
498
499
500

# File 'lib/sequel/adapters/shared/mysql.rb', line 496

def join_table(type, table, expr=nil, table_alias={}, &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, table_alias, &block)
end

504
505
506
507
508
509
510
511
512
513

# File 'lib/sequel/adapters/shared/mysql.rb', line 504

def join_type_sql(join_type)
  case join_type
  when :straight
    STRAIGHT_JOIN
  when :natural_inner
    NATURAL_LEFT_JOIN
  else
    super
  end
end

551
552
553
554
555

# File 'lib/sequel/adapters/shared/mysql.rb', line 551

def multi_insert_sql(columns, values)
  sql = LiteralString.new('VALUES ')
  expression_list_append(sql, values.map{|r| Array(r)})
  [insert_sql(columns, sql)]
end

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)

546
547
548

# File 'lib/sequel/adapters/shared/mysql.rb', line 546

def on_duplicate_key_update(*args)
  clone(:on_duplicate_key_update => args)
end

558
559
560

# File 'lib/sequel/adapters/shared/mysql.rb', line 558

def quoted_identifier_append(sql, c)
  sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK
end

564
565
566

# File 'lib/sequel/adapters/shared/mysql.rb', line 564

def replace_sql(*values)
  clone(:replace=>true).insert_sql(*values)
end

570
571
572

# File 'lib/sequel/adapters/shared/mysql.rb', line 570

def supports_distinct_on?
  true
end

575
576
577

# File 'lib/sequel/adapters/shared/mysql.rb', line 575

def supports_group_rollup?
  true
end

580
581
582

# File 'lib/sequel/adapters/shared/mysql.rb', line 580

def supports_intersect_except?
  false
end

585
586
587

# File 'lib/sequel/adapters/shared/mysql.rb', line 585

def supports_modifying_joins?
  true
end

591
592
593

# File 'lib/sequel/adapters/shared/mysql.rb', line 591

def supports_ordered_distinct_on?
  false
end

598
599
600

# File 'lib/sequel/adapters/shared/mysql.rb', line 598

def supports_timestamp_usecs?
  false
end

dataset.update_ignore.update({:name => 'a', :value => 1})
# UPDATE IGNORE tablename SET name = 'a', value = 1

608
609
610

# File 'lib/sequel/adapters/shared/mysql.rb', line 608

def update_ignore
  clone(:update_ignore=>true)
end