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

Dataset methods shared by datasets that use MySQL databases.

Methods

Included Modules

Dataset::UnsupportedIntersectExcept

Constants

BOOL_TRUE = '1'.freeze
BOOL_FALSE = '0'.freeze
CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}
TIMESTAMP_FORMAT = "'%Y-%m-%d %H:%M:%S'".freeze
COMMA_SEPARATOR = ', '.freeze

Public Instance methods

MySQL can‘t use the varchar type in a cast.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 156
156:       def cast_sql(expr, type)
157:         "CAST(#{literal(expr)} AS #{CAST_TYPES[type] || db.send(:type_literal_base, :type=>type)})"
158:       end

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

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 162
162:       def complex_expression_sql(op, args)
163:         case op
164:         when :~, '!~''!~', '~*''~*', '!~*''!~*', :LIKE, 'NOT LIKE''NOT LIKE', :ILIKE, 'NOT ILIKE''NOT ILIKE'
165:           "(#{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))})"
166:         when '||''||'
167:           if args.length > 1
168:             "CONCAT(#{args.collect{|a| literal(a)}.join(', ')})"
169:           else
170:             literal(args.at(0))
171:           end
172:         else
173:           super(op, args)
174:         end
175:       end

MySQL supports ORDER and LIMIT clauses in DELETE statements.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 178
178:       def delete_sql(opts = (defarg=true;nil))
179:         if defarg
180:           sql = super()
181:           opts = @opts
182:         else
183:           sql = super
184:           opts = opts ? @opts.merge(opts) : @opts
185:         end
186: 
187:         if order = opts[:order]
188:           sql << " ORDER BY #{expression_list(order)}"
189:         end
190:         if limit = opts[:limit]
191:           sql << " LIMIT #{limit}"
192:         end
193: 
194:         sql
195:       end

MySQL doesn‘t support DISTINCT ON

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 198
198:       def distinct(*columns)
199:         raise(Error, "DISTINCT ON not supported by MySQL") unless columns.empty?
200:         super
201:       end

MySQL specific full text search syntax.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 204
204:       def full_text_search(cols, terms, opts = {})
205:         mode = opts[:boolean] ? " IN BOOLEAN MODE" : ""
206:         s = if Array === terms
207:           if mode.empty?
208:             "MATCH #{literal(Array(cols))} AGAINST #{literal(terms)}"
209:           else
210:             "MATCH #{literal(Array(cols))} AGAINST (#{literal(terms)[1...-1]}#{mode})"
211:           end
212:         else
213:           "MATCH #{literal(Array(cols))} AGAINST (#{literal(terms)}#{mode})"
214:         end
215:         filter(s)
216:       end

MySQL allows HAVING clause on ungrouped datasets.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 219
219:       def having(*cond, &block)
220:         _filter(:having, *cond, &block)
221:       end

MySQL doesn‘t use the SQL standard DEFAULT VALUES.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 224
224:       def insert_default_values_sql
225:         "INSERT INTO #{source_list(@opts[:from])} () VALUES ()"
226:       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 258
258:       def insert_ignore
259:         clone(:insert_ignore=>true)
260:       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 230
230:       def join_table(type, table, expr=nil, table_alias={})
231:         type = :inner if (type == :cross) && !expr.nil?
232:         raise(Sequel::Error, "MySQL doesn't support FULL OUTER JOIN") if type == :full_outer
233:         super(type, table, expr, table_alias)
234:       end

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

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 238
238:       def join_type_sql(join_type)
239:         case join_type
240:         when :straight then 'STRAIGHT_JOIN'
241:         when :natural_inner then 'NATURAL LEFT JOIN'
242:         else super
243:         end
244:       end

MySQL specific syntax for inserting multiple values at once.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 291
291:       def multi_insert_sql(columns, values)
292:         if update_cols = opts[:on_duplicate_key_update]
293:           update_cols = columns if update_cols.empty?
294:           update_string = update_cols.map{|c| "#{quote_identifier(c)}=VALUES(#{quote_identifier(c)})"}.join(COMMA_SEPARATOR)
295:         end
296:         values = values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)
297:         ["INSERT#{' IGNORE' if opts[:insert_ignore]} INTO #{source_list(@opts[:from])} (#{identifier_list(columns)}) VALUES #{values}#{" ON DUPLICATE KEY UPDATE #{update_string}" if update_string}"]
298:       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 286
286:       def on_duplicate_key_update(*args)
287:         clone(:on_duplicate_key_update => args)
288:       end

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

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 301
301:       def quoted_identifier(c)
302:         "`#{c}`"
303:       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 307
307:       def replace_sql(*values)
308:         from = source_list(@opts[:from])
309:         if values.empty?
310:           "REPLACE INTO #{from} DEFAULT VALUES"
311:         else
312:           values = values[0] if values.size == 1
313:           
314:           # if hash or array with keys we need to transform the values
315:           if @transform && (values.is_a?(Hash) || (values.is_a?(Array) && values.keys))
316:             values = transform_save(values)
317:           end
318: 
319:           case values
320:           when Array
321:             if values.empty?
322:               "REPLACE INTO #{from} DEFAULT VALUES"
323:             else
324:               "REPLACE INTO #{from} VALUES #{literal(values)}"
325:             end
326:           when Hash
327:             if values.empty?
328:               "REPLACE INTO #{from} DEFAULT VALUES"
329:             else
330:               fl, vl = [], []
331:               values.each {|k, v| fl << literal(k.is_a?(String) ? k.to_sym : k); vl << literal(v)}
332:               "REPLACE INTO #{from} (#{fl.join(COMMA_SEPARATOR)}) VALUES (#{vl.join(COMMA_SEPARATOR)})"
333:             end
334:           when Dataset
335:             "REPLACE INTO #{from} #{literal(values)}"
336:           else
337:             if values.respond_to?(:values)
338:               replace_sql(values.values)
339:             else  
340:               "REPLACE INTO #{from} VALUES (#{literal(values)})"
341:             end
342:           end
343:         end
344:       end

MySQL supports ORDER and LIMIT clauses in UPDATE statements.

[Source]

     # File lib/sequel/adapters/shared/mysql.rb, line 347
347:       def update_sql(values, opts = (defarg=true;nil))
348:         if defarg
349:           sql = super(values)
350:           opts = @opts
351:         else
352:           sql = super
353:           opts = opts ? @opts.merge(opts) : @opts
354:         end
355: 
356:         if order = opts[:order]
357:           sql << " ORDER BY #{expression_list(order)}"
358:         end
359:         if limit = opts[:limit]
360:           sql << " LIMIT #{limit}"
361:         end
362: 
363:         sql
364:       end

[Validate]