Module: Sequel::MySQL::DatasetMethods

Includes:
Dataset::UnsupportedIntersectExcept
Included in:
JDBC::MySQL::Dataset, Dataset
Defined in:
lib/sequel_core/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
COMMA_SEPARATOR = 
', '.freeze

Instance Method Summary collapse

Methods included from Dataset::UnsupportedIntersectExcept

#except, #intersect

Instance Method Details

#complex_expression_sql(op, args) ⇒ Object

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



106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 106

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 = nil) ⇒ Object

MySQL supports ORDER and LIMIT clauses in DELETE statements.



122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 122

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

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

MySQL specific full text search syntax.



137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 137

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.



152
153
154
155
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 152

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

#insert_default_values_sql ⇒ Object

MySQL doesn’t use the SQL standard DEFAULT VALUES.



158
159
160
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 158

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:



164
165
166
167
168
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 164

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.



172
173
174
175
176
177
178
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 172

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

Override the default boolean values.



181
182
183
184
185
186
187
188
189
190
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 181

def literal(v)
  case v
  when true
    BOOL_TRUE
  when false
    BOOL_FALSE
  else
    super
  end
end

#multi_insert_sql(columns, values) ⇒ Object

MySQL specific syntax for inserting multiple values at once.



193
194
195
196
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 193

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.



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

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).



205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 205

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 = nil) ⇒ Object

MySQL supports ORDER and LIMIT clauses in UPDATE statements.



245
246
247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/sequel_core/adapters/shared/mysql.rb', line 245

def update_sql(values, 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