Module Sequel::MySQL::DatasetMethods
In: lib/sequel/adapters/shared/mysql.rb

Dataset methods shared by datasets that use MySQL databases.

Methods

Constants

BOOL_TRUE = '1'.freeze
BOOL_FALSE = '0'.freeze
COMMA_SEPARATOR = ', '.freeze
SELECT_CLAUSE_ORDER = %w'distinct columns from join where group having compounds order limit'.freeze

Public Instance methods

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

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 206
206:       def complex_expression_sql(op, args)
207:         case op
208:         when :~, '!~''!~', '~*''~*', '!~*''!~*', :LIKE, 'NOT LIKE''NOT LIKE', :ILIKE, 'NOT ILIKE''NOT ILIKE'
209:           "(#{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))})"
210:         when '||''||'
211:           if args.length > 1
212:             "CONCAT(#{args.collect{|a| literal(a)}.join(', ')})"
213:           else
214:             literal(args.at(0))
215:           end
216:         else
217:           super(op, args)
218:         end
219:       end

MySQL supports ORDER and LIMIT clauses in DELETE statements.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 222
222:       def delete_sql
223:         sql = super
224:         sql << " ORDER BY #{expression_list(opts[:order])}" if opts[:order]
225:         sql << " LIMIT #{opts[:limit]}" if opts[:limit]
226:         sql
227:       end

Adds full text filter

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 230
230:       def full_text_search(cols, terms, opts = {})
231:         filter(full_text_sql(cols, terms, opts))
232:       end

MySQL specific full text search syntax.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 235
235:       def full_text_sql(cols, term, opts = {})
236:         "MATCH #{literal(Array(cols))} AGAINST (#{literal(Array(term).join(' '))}#{" IN BOOLEAN MODE" if opts[:boolean]})"
237:       end

MySQL allows HAVING clause on ungrouped datasets.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 240
240:       def having(*cond, &block)
241:         _filter(:having, *cond, &block)
242:       end

Sets up multi_insert or import to use INSERT IGNORE. Useful if you have a unique key and want to just skip inserting rows that violate the unique key restriction.

Example:

dataset.insert_ignore.multi_insert(

 [{:name => 'a', :value => 1}, {:name => 'b', :value => 2}]

)

INSERT IGNORE INTO tablename (name, value) VALUES (a, 1), (b, 2)

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 274
274:       def insert_ignore
275:         clone(:insert_ignore=>true)
276:       end

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.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 246
246:       def join_table(type, table, expr=nil, table_alias={})
247:         type = :inner if (type == :cross) && !expr.nil?
248:         raise(Sequel::Error, "MySQL doesn't support FULL OUTER JOIN") if type == :full_outer
249:         super(type, table, expr, table_alias)
250:       end

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

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 254
254:       def join_type_sql(join_type)
255:         case join_type
256:         when :straight then 'STRAIGHT_JOIN'
257:         when :natural_inner then 'NATURAL LEFT JOIN'
258:         else super
259:         end
260:       end

MySQL specific syntax for inserting multiple values at once.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 307
307:       def multi_insert_sql(columns, values)
308:         values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)
309:         ["#{insert_sql_base}#{source_list(@opts[:from])} (#{identifier_list(columns)}) VALUES #{values}#{insert_sql_suffix}"]
310:       end

Sets up multi_insert or import 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.

Examples:

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)

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 302
302:       def on_duplicate_key_update(*args)
303:         clone(:on_duplicate_key_update => args)
304:       end

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

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 313
313:       def quoted_identifier(c)
314:         "`#{c}`"
315:       end

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

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 319
319:       def replace_sql(*values)
320:         from = source_list(@opts[:from])
321:         if values.empty?
322:           "REPLACE INTO #{from} DEFAULT VALUES"
323:         else
324:           values = values[0] if values.size == 1
325:           
326:           case values
327:           when Array
328:             if values.empty?
329:               "REPLACE INTO #{from} DEFAULT VALUES"
330:             else
331:               "REPLACE INTO #{from} VALUES #{literal(values)}"
332:             end
333:           when Hash
334:             if values.empty?
335:               "REPLACE INTO #{from} DEFAULT VALUES"
336:             else
337:               fl, vl = [], []
338:               values.each {|k, v| fl << literal(k.is_a?(String) ? k.to_sym : k); vl << literal(v)}
339:               "REPLACE INTO #{from} (#{fl.join(COMMA_SEPARATOR)}) VALUES (#{vl.join(COMMA_SEPARATOR)})"
340:             end
341:           when Dataset
342:             "REPLACE INTO #{from} #{literal(values)}"
343:           else
344:             if values.respond_to?(:values)
345:               replace_sql(values.values)
346:             else  
347:               "REPLACE INTO #{from} VALUES (#{literal(values)})"
348:             end
349:           end
350:         end
351:       end

does not support DISTINCT ON

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 354
354:       def supports_distinct_on?
355:         false
356:       end

MySQL does not support INTERSECT or EXCEPT

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 359
359:       def supports_intersect_except?
360:         false
361:       end

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.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 366
366:       def supports_timestamp_usecs?
367:         false
368:       end

MySQL supports ORDER and LIMIT clauses in UPDATE statements.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 371
371:       def update_sql(values)
372:         sql = super
373:         sql << " ORDER BY #{expression_list(opts[:order])}" if opts[:order]
374:         sql << " LIMIT #{opts[:limit]}" if opts[:limit]
375:         sql
376:       end

[Validate]