Module: Sequel::MySQL::DatasetMethods

Includes:
Dataset::UnsupportedIntersectExcept
Included in:
DataObjects::MySQL::Dataset, JDBC::MySQL::Dataset, Dataset
Defined in:
lib/sequel/adapters/shared/mysql.rb

Overview

Dataset methods shared by datasets that use MySQL databases.

Constant Summary collapse

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

Instance Method Summary collapse

Instance Method Details

#cast_sql(expr, type) ⇒ Object

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



156
157
158
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 156

def cast_sql(expr, type)
  "CAST(#{literal(expr)} AS #{CAST_TYPES[type] || db.send(:type_literal_base, :type=>type)})"
end

#complex_expression_sql(op, args) ⇒ Object

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



162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 162

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_sql(opts = (defarg=true;nil)) ⇒ Object

MySQL supports ORDER and LIMIT clauses in DELETE statements.



178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 178

def delete_sql(opts = (defarg=true;nil))
  if defarg
    sql = super()
    opts = @opts
  else
    sql = super
    opts = opts ? @opts.merge(opts) : @opts
  end

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

  sql
end

#distinct(*columns) ⇒ Object

MySQL doesn’t support DISTINCT ON

Raises:



198
199
200
201
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 198

def distinct(*columns)
  raise(Error, "DISTINCT ON not supported by MySQL") unless columns.empty?
  super
end

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

MySQL specific full text search syntax.



204
205
206
207
208
209
210
211
212
213
214
215
216
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 204

def full_text_search(cols, terms, opts = {})
  mode = opts[:boolean] ? " IN BOOLEAN MODE" : ""
  s = if Array === terms
    if mode.empty?
      "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.



219
220
221
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 219

def having(*cond, &block)
  _filter(:having, *cond, &block)
end

#insert_default_values_sql ⇒ Object

MySQL doesn’t use the SQL standard DEFAULT VALUES.



224
225
226
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 224

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

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

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.

Raises:



230
231
232
233
234
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 230

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

#join_type_sql(join_type) ⇒ Object

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



238
239
240
241
242
243
244
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 238

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

#multi_insert_sql(columns, values) ⇒ Object

MySQL specific syntax for inserting multiple values at once.



247
248
249
250
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 247

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

#quoted_identifier(c) ⇒ Object

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



253
254
255
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 253

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

#replace_sql(*values) ⇒ Object

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



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 259

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_sql(values, opts = (defarg=true;nil)) ⇒ Object

MySQL supports ORDER and LIMIT clauses in UPDATE statements.



299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
 
# File 'lib/sequel/adapters/shared/mysql.rb', line 299

def update_sql(values, opts = (defarg=true;nil))
  if defarg
    sql = super(values)
    opts = @opts
  else
    sql = super
    opts = opts ? @opts.merge(opts) : @opts
  end

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

  sql
end