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 



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

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 



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

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.



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

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

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

  sql
end

#fetch_rows(sql) ⇒ Object 



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

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

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



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

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.



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

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

#insert(*values) ⇒ Object 



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

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

#insert_default_values_sql ⇒ Object 



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

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:



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

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 



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

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 



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

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 



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

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 



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

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 



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

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

#replace(*args) ⇒ Object 



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

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

#replace_sql(*values) ⇒ Object 



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

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 



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

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.



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

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

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

  sql
end