Class: Sequel::MySQL::Dataset

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

Constant Summary collapse

TRUE = 
'1'
FALSE = 
'0'

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_FROM_SELF_OPTS, 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

#<<, #[], #[]=, #aliased_expression_sql, #all, #and, #as, #avg, #case_expression_sql, #clone, #column_all_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, #irregular_function_sql, #join_clause_sql, #join_on_clause_sql, #join_using_clause_sql, #last, #limit, #map, #max, #min, #model_classes, #multi_insert, #naked, #or, #order, #order_more, #ordered_expression_sql, #paginate, #polymorphic_key, #print, #qualified_identifier_sql, #query, #quote_identifier, #quote_identifiers?, #range, #reverse_order, #select, #select_all, #select_more, #select_sql, #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 



339
340
341
342
343
344
345
346
347
348
349
350
351
352
 
# File 'lib/sequel_core/adapters/mysql.rb', line 339

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 



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

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.



440
441
442
443
444
445
446
447
448
449
450
451
452
 
# File 'lib/sequel_core/adapters/mysql.rb', line 440

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 



470
471
472
473
474
475
476
 
# File 'lib/sequel_core/adapters/mysql.rb', line 470

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 



365
366
367
368
369
370
371
372
373
374
375
376
377
 
# File 'lib/sequel_core/adapters/mysql.rb', line 365

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.



380
381
382
383
 
# File 'lib/sequel_core/adapters/mysql.rb', line 380

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

#insert(*values) ⇒ Object 



454
455
456
 
# File 'lib/sequel_core/adapters/mysql.rb', line 454

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

#insert_default_values_sql ⇒ Object 



335
336
337
 
# File 'lib/sequel_core/adapters/mysql.rb', line 335

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:



320
321
322
323
324
 
# File 'lib/sequel_core/adapters/mysql.rb', line 320

def join_table(type, table, expr=nil, table_alias=nil)
  type = :inner if (type == :cross) && !expr.nil?
  raise(Sequel::Error::InvalidJoinType, "MySQL doesn't support FULL OUTER JOIN") if type == :full_outer
  super(type, table, expr, table_alias)
end

#join_type_sql(join_type) ⇒ Object 



327
328
329
330
331
332
333
 
# File 'lib/sequel_core/adapters/mysql.rb', line 327

def join_type_sql(join_type)
  case join_type
  when :straight then 'STRAIGHT_JOIN'
  when :natural_inner then 'NATURAL LEFT JOIN'
  else super
  end
end

#literal(v) ⇒ Object 



288
289
290
291
292
293
294
295
296
297
298
299
300
301
 
# File 'lib/sequel_core/adapters/mysql.rb', line 288

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 



354
355
356
357
358
359
360
361
362
363
 
# File 'lib/sequel_core/adapters/mysql.rb', line 354

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 



478
479
480
481
482
 
# File 'lib/sequel_core/adapters/mysql.rb', line 478

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 



462
463
464
 
# File 'lib/sequel_core/adapters/mysql.rb', line 462

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

#replace_sql(*values) ⇒ Object 



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
 
# File 'lib/sequel_core/adapters/mysql.rb', line 400

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

#update(*args, &block) ⇒ Object 



458
459
460
 
# File 'lib/sequel_core/adapters/mysql.rb', line 458

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.



386
387
388
389
390
391
392
393
394
395
396
397
398
 
# File 'lib/sequel_core/adapters/mysql.rb', line 386

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