Class: Sequel::MySQL::Dataset

Inherits:
Dataset show all
Defined in:
lib/sequel_core/adapters/mysql.rb

Constant Summary collapse

TRUE = 
'1'
FALSE = 
'0'
JOIN_TYPES =

Join processing changed after MySQL v5.0.12. NATURAL joins are SQL:2003 consistent.

{ :cross => 'INNER JOIN'.freeze,
  :straight => 'STRAIGHT_JOIN'.freeze,
  :natural_left => 'NATURAL LEFT JOIN'.freeze,
  :natural_right => 'NATURAL RIGHT JOIN'.freeze,
  :natural_left_outer => 'NATURAL LEFT OUTER JOIN'.freeze,
  :natural_right_outer => 'NATURAL RIGHT OUTER JOIN'.freeze,
  :left => 'LEFT JOIN'.freeze,
  :right => 'RIGHT JOIN'.freeze,
  :left_outer => 'LEFT OUTER JOIN'.freeze,
  :right_outer => 'RIGHT OUTER JOIN'.freeze,
  :natural_inner => 'NATURAL LEFT JOIN'.freeze,
  # :full_outer => 'FULL OUTER JOIN'.freeze,
  #
  # A full outer join, nor a workaround implementation of
  # :full_outer, is not yet possible in Sequel. See issue
  # #195 which probably depends on issue #113 being
  # resolved.
  :inner => 'INNER JOIN'.freeze
}

Constants inherited from Dataset

Dataset::AND_SEPARATOR, Dataset::BOOL_FALSE, Dataset::BOOL_TRUE, Dataset::COLUMN_CHANGE_OPTS, Dataset::COLUMN_REF_RE1, Dataset::COLUMN_REF_RE2, Dataset::COLUMN_REF_RE3, Dataset::COMMA_SEPARATOR, Dataset::COUNT_OF_ALL_AS_COUNT, Dataset::DATASET_CLASSES, Dataset::DATE_FORMAT, Dataset::MUTATION_METHODS, Dataset::NOTIMPL_MSG, Dataset::NULL, Dataset::N_ARITY_OPERATORS, Dataset::QUESTION_MARK, Dataset::STOCK_COUNT_OPTS, Dataset::STOCK_TRANSFORMS, Dataset::TIMESTAMP_FORMAT, Dataset::TWO_ARITY_OPERATORS, Dataset::WILDCARD

Instance Attribute Summary

Attributes inherited from Dataset

#db, #opts, #quote_identifiers, #row_proc

Instance Method Summary collapse

Methods inherited from Dataset

#<<, #[], #[]=, #all, #and, #as, #avg, #clone, #column_all_sql, #column_expr_sql, #columns, #columns!, #count, #create_or_replace_view, #create_view, dataset_classes, #def_mutation_method, def_mutation_method, #each, #each_page, #empty?, #except, #exclude, #exists, #filter, #first, #first_source, #from, #from_self, #function_sql, #get, #graph, #grep, #group, #group_and_count, inherited, #initialize, #insert_multiple, #insert_sql, #inspect, #intersect, #interval, #invert, #last, #limit, #map, #max, #min, #model_classes, #multi_insert, #naked, #or, #order, #order_more, #paginate, #polymorphic_key, #print, #qualified_column_ref_sql, #query, #quote_identifier, #quote_identifiers?, #range, #reverse_order, #select, #select_all, #select_more, #set, #set_graph_aliases, #set_model, #single_record, #single_value, #subscript_sql, #sum, #symbol_to_column_ref, #table_exists?, #to_csv, #to_hash, #transform, #transform_load, #transform_save, #unfiltered, #union, #uniq, #unordered

Methods included from Enumerable

#send_each

Constructor Details

This class inherits a constructor from Sequel::Dataset

Instance Method Details

#complex_expression_sql(op, args) ⇒ Object 



361
362
363
364
365
366
367
368
369
370
371
372
373
374
 
# File 'lib/sequel_core/adapters/mysql.rb', line 361

def complex_expression_sql(op, args)
  case op
  when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'
    "(#{literal(args.at(0))} #{'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op)}#{[:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE'} #{'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op)}#{literal(args.at(1))})"
  when :'||'
    if args.length > 1
      "CONCAT(#{args.collect{|a| literal(a)}.join(', ')})"
    else
      literal(args.at(0))
    end
  else
    super(op, args)
  end
end

#delete(opts = nil) ⇒ Object 



552
553
554
 
# File 'lib/sequel_core/adapters/mysql.rb', line 552

def delete(opts = nil)
  @db.execute(delete_sql(opts)) {|c| c.affected_rows}
end

#delete_sql(opts = nil) ⇒ Object

MySQL supports ORDER and LIMIT clauses in DELETE statements.



526
527
528
529
530
531
532
533
534
535
536
537
538
 
# File 'lib/sequel_core/adapters/mysql.rb', line 526

def delete_sql(opts = nil)
  sql = super
  opts = opts ? @opts.merge(opts) : @opts

  if order = opts[:order]
    sql << " ORDER BY #{column_list(order)}"
  end
  if limit = opts[:limit]
    sql << " LIMIT #{limit}"
  end

  sql
end

#fetch_rows(sql) ⇒ Object 



556
557
558
559
560
561
562
 
# File 'lib/sequel_core/adapters/mysql.rb', line 556

def fetch_rows(sql)
  @db.execute_select(sql) do |r|
    @columns = r.columns
    r.each_hash {|row| yield row}
  end
  self
end

#full_text_search(cols, terms, opts = {}) ⇒ Object 



451
452
453
454
455
456
457
458
459
460
461
462
463
 
# File 'lib/sequel_core/adapters/mysql.rb', line 451

def full_text_search(cols, terms, opts = {})
  mode = opts[:boolean] ? " IN BOOLEAN MODE" : ""
  s = if Array === terms
    if mode.blank?
      "MATCH #{literal(Array(cols))} AGAINST #{literal(terms)}"
    else
      "MATCH #{literal(Array(cols))} AGAINST (#{literal(terms)[1...-1]}#{mode})"
    end
  else
    "MATCH #{literal(Array(cols))} AGAINST (#{literal(terms)}#{mode})"
  end
  filter(s)
end

#having(*cond, &block) ⇒ Object

MySQL allows HAVING clause on ungrouped datasets.



466
467
468
469
 
# File 'lib/sequel_core/adapters/mysql.rb', line 466

def having(*cond, &block)
  @opts[:having] = {}
  x = filter(*cond, &block)
end

#insert(*values) ⇒ Object 



540
541
542
 
# File 'lib/sequel_core/adapters/mysql.rb', line 540

def insert(*values)
  @db.execute(insert_sql(*values)) {|c| c.insert_id}
end

#insert_default_values_sql ⇒ Object 



357
358
359
 
# File 'lib/sequel_core/adapters/mysql.rb', line 357

def insert_default_values_sql
  "INSERT INTO #{source_list(@opts[:from])} () VALUES ()"
end

#join_table(type, table, expr = nil, table_alias = nil) ⇒ Object

Returns a join clause based on the specified join type and condition. MySQL’s NATURAL join is ‘semantically equivalent to a JOIN with a USING clause that names all columns that exist in both tables. The constraint expression may be nil, so join expression can accept two arguments.

Note

Full outer joins (:full_outer) are not implemented in MySQL (as of v6.0), nor is there currently a work around implementation in Sequel. Straight joins with ‘ON <condition>’ are not yet implemented.

Example

@ds = MYSQL_DB[:nodes]
@ds.join_table(:natural_left_outer, :nodes)
# join SQL is 'NATURAL LEFT OUTER JOIN nodes'

Raises:



342
343
344
345
346
347
348
349
350
351
352
353
354
355
 
# File 'lib/sequel_core/adapters/mysql.rb', line 342

def join_table(type, table, expr=nil, table_alias=nil)
  raise(Error::InvalidJoinType, "Invalid join type: #{type}") unless join_type = JOIN_TYPES[type || :inner]

  server_version = (@opts[:server_version] ||= @db.server_version)
  type = :inner if (type == :cross) && !expr.nil?
  return super(type, table, expr, table_alias) unless (server_version >= 50014) && /natural|cross|straight/.match(type.to_s)
  
  table = if Array === table
    "( #{table.collect{|t| quote_identifier(t)}.join(', ')} )"
  else
    quote_identifier(table)
  end
  clone(:join => "#{@opts[:join]} #{join_type} #{table}")
end

#literal(v) ⇒ Object 



310
311
312
313
314
315
316
317
318
319
320
321
322
323
 
# File 'lib/sequel_core/adapters/mysql.rb', line 310

def literal(v)
  case v
  when LiteralString
    v
  when String
    "'#{::Mysql.quote(v)}'"
  when true
    TRUE
  when false
    FALSE
  else
    super
  end
end

#match_expr(l, r) ⇒ Object 



376
377
378
379
380
381
382
383
384
385
 
# File 'lib/sequel_core/adapters/mysql.rb', line 376

def match_expr(l, r)
  case r
  when Regexp
    r.casefold? ? \
    "(#{literal(l)} REGEXP #{literal(r.source)})" :
    "(#{literal(l)} REGEXP BINARY #{literal(r.source)})"
  else
    super
  end
end

#multi_insert_sql(columns, values) ⇒ Object 



564
565
566
567
568
 
# File 'lib/sequel_core/adapters/mysql.rb', line 564

def multi_insert_sql(columns, values)
  columns = column_list(columns)
  values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)
  ["INSERT INTO #{source_list(@opts[:from])} (#{columns}) VALUES #{values}"]
end

#quoted_identifier(c) ⇒ Object 



281
282
283
 
# File 'lib/sequel_core/adapters/mysql.rb', line 281

def quoted_identifier(c)
  "`#{c}`"
end

#replace(*args) ⇒ Object 



548
549
550
 
# File 'lib/sequel_core/adapters/mysql.rb', line 548

def replace(*args)
  @db.execute(replace_sql(*args)) {|c| c.insert_id}
end

#replace_sql(*values) ⇒ Object 



486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
 
# File 'lib/sequel_core/adapters/mysql.rb', line 486

def replace_sql(*values)
  from = source_list(@opts[:from])
  if values.empty?
    "REPLACE INTO #{from} DEFAULT VALUES"
  else
    values = values[0] if values.size == 1
    
    # if hash or array with keys we need to transform the values
    if @transform && (values.is_a?(Hash) || (values.is_a?(Array) && values.keys))
      values = transform_save(values)
    end

    case values
    when Array
      if values.empty?
        "REPLACE INTO #{from} DEFAULT VALUES"
      else
        "REPLACE INTO #{from} VALUES #{literal(values)}"
      end
    when Hash
      if values.empty?
        "REPLACE INTO #{from} DEFAULT VALUES"
      else
        fl, vl = [], []
        values.each {|k, v| fl << literal(k.is_a?(String) ? k.to_sym : k); vl << literal(v)}
        "REPLACE INTO #{from} (#{fl.join(COMMA_SEPARATOR)}) VALUES (#{vl.join(COMMA_SEPARATOR)})"
      end
    when Dataset
      "REPLACE INTO #{from} #{literal(values)}"
    else
      if values.respond_to?(:values)
        replace_sql(values.values)
      else  
        "REPLACE INTO #{from} VALUES (#{literal(values)})"
      end
    end
  end
end

#select_sql(opts = nil) ⇒ Object Also known as: sql

MySQL expects the having clause before the order by clause.



388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
 
# File 'lib/sequel_core/adapters/mysql.rb', line 388

def select_sql(opts = nil)
  opts = opts ? @opts.merge(opts) : @opts
  
  if sql = opts[:sql]
    return sql
  end

  columns = opts[:select]
  select_columns = columns ? column_list(columns) : WILDCARD

  if distinct = opts[:distinct]
    distinct_clause = distinct.empty? ? "DISTINCT" : "DISTINCT ON (#{column_list(distinct)})"
    sql = "SELECT #{distinct_clause} #{select_columns}"
  else
    sql = "SELECT #{select_columns}"
  end
  
  if opts[:from]
    sql << " FROM #{source_list(opts[:from])}"
  end
  
  if join = opts[:join]
    sql << join
  end

  if where = opts[:where]
    sql << " WHERE #{literal(where)}"
  end

  if group = opts[:group]
    sql << " GROUP BY #{column_list(group)}"
  end

  if having = opts[:having]
    sql << " HAVING #{literal(having)}"
  end

  if order = opts[:order]
    sql << " ORDER BY #{column_list(order)}"
  end

  if limit = opts[:limit]
    sql << " LIMIT #{limit}"
    if offset = opts[:offset]
      sql << " OFFSET #{offset}"
    end
  end

  if union = opts[:union]
    sql << (opts[:union_all] ? \
      " UNION ALL #{union.sql}" : " UNION #{union.sql}")
  elsif intersect = opts[:intersect]
    sql << (opts[:intersect_all] ? \
      " INTERSECT ALL #{intersect.sql}" : " INTERSECT #{intersect.sql}")
  elsif except = opts[:except]
    sql << (opts[:except_all] ? \
      " EXCEPT ALL #{except.sql}" : " EXCEPT #{except.sql}")
  end

  sql
end

#update(*args, &block) ⇒ Object 



544
545
546
 
# File 'lib/sequel_core/adapters/mysql.rb', line 544

def update(*args, &block)
  @db.execute(update_sql(*args, &block)) {|c| c.affected_rows}
end

#update_sql(values, opts = nil, &block) ⇒ Object

MySQL supports ORDER and LIMIT clauses in UPDATE statements.



472
473
474
475
476
477
478
479
480
481
482
483
484
 
# File 'lib/sequel_core/adapters/mysql.rb', line 472

def update_sql(values, opts = nil, &block)
  sql = super
  opts = opts ? @opts.merge(opts) : @opts

  if order = opts[:order]
    sql << " ORDER BY #{column_list(order)}"
  end
  if limit = opts[:limit]
    sql << " LIMIT #{limit}"
  end

  sql
end