Module: Sequel::MySQL::DatasetMethods
- 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
- COMMA_SEPARATOR =
', '.freeze
- FOR_SHARE =
' LOCK IN SHARE MODE'.freeze
- DELETE_CLAUSE_METHODS =
Dataset.clause_methods(:delete, %w'from where order limit')
- INSERT_CLAUSE_METHODS =
Dataset.clause_methods(:insert, %w'ignore into columns values on_duplicate_key_update')
- SELECT_CLAUSE_METHODS =
Dataset.clause_methods(:select, %w'distinct columns from join where group having compounds order limit lock')
- UPDATE_CLAUSE_METHODS =
Dataset.clause_methods(:update, %w'table set where order limit')
Instance Method Summary collapse
-
#complex_expression_sql(op, args) ⇒ Object
MySQL specific syntax for LIKE/REGEXP searches, as well as string concatenation.
-
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
-
#for_share ⇒ Object
Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.
-
#full_text_search(cols, terms, opts = {}) ⇒ Object
Adds full text filter.
-
#full_text_sql(cols, term, opts = {}) ⇒ Object
MySQL specific full text search syntax.
-
#having(*cond, &block) ⇒ Object
MySQL allows HAVING clause on ungrouped datasets.
-
#insert_ignore ⇒ Object
Sets up the insert methods to use INSERT IGNORE.
-
#join_table(type, table, expr = nil, table_alias = {}) ⇒ Object
Transforms an CROSS JOIN to an INNER JOIN if the expr is not nil.
-
#join_type_sql(join_type) ⇒ Object
Transforms :natural_inner to NATURAL LEFT JOIN and straight to STRAIGHT_JOIN.
-
#multi_insert_sql(columns, values) ⇒ Object
MySQL specific syntax for inserting multiple values at once.
-
#on_duplicate_key_update(*args) ⇒ Object
Sets up the insert methods to use ON DUPLICATE KEY UPDATE If you pass no arguments, ALL fields will be updated with the new values.
-
#provides_accurate_rows_matched? ⇒ Boolean
MySQL uses the number of rows actually modified in the update, instead of the number of matched by the filter.
-
#quoted_identifier(c) ⇒ Object
MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.
-
#replace_sql(*values) ⇒ Object
MySQL specific syntax for REPLACE (aka UPSERT, or update if exists, insert if it doesn’t).
-
#supports_distinct_on? ⇒ Boolean
MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.
-
#supports_intersect_except? ⇒ Boolean
MySQL does not support INTERSECT or EXCEPT.
-
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets.
-
#supports_timestamp_usecs? ⇒ Boolean
MySQL does support fractional timestamps in literal timestamps, but it ignores them.
Instance Method Details
#complex_expression_sql(op, args) ⇒ Object
MySQL specific syntax for LIKE/REGEXP searches, as well as string concatenation.
302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 302 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 when :'B~' "CAST(~#{literal(args.at(0))} AS SIGNED INTEGER)" else super(op, args) end end |
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
320 321 322 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 320 def distinct(*args) args.empty? ? super : group(*args) end |
#for_share ⇒ Object
Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.
325 326 327 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 325 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = {}) ⇒ Object
Adds full text filter
330 331 332 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 330 def full_text_search(cols, terms, opts = {}) filter(full_text_sql(cols, terms, opts)) end |
#full_text_sql(cols, term, opts = {}) ⇒ Object
MySQL specific full text search syntax.
335 336 337 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 335 def full_text_sql(cols, term, opts = {}) "MATCH #{literal(Array(cols))} AGAINST (#{literal(Array(term).join(' '))}#{" IN BOOLEAN MODE" if opts[:boolean]})" end |
#having(*cond, &block) ⇒ Object
MySQL allows HAVING clause on ungrouped datasets.
340 341 342 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 340 def having(*cond, &block) _filter(:having, *cond, &block) end |
#insert_ignore ⇒ Object
Sets up the insert methods to use INSERT IGNORE. Useful if you have a unique key and want to just skip inserting rows that violate the unique key restriction.
dataset.insert_ignore.multi_insert(
[{:name => 'a', :value => 1}, {:name => 'b', :value => 2}]
)
# INSERT IGNORE INTO tablename (name, value) VALUES (a, 1), (b, 2)
370 371 372 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 370 def insert_ignore clone(:insert_ignore=>true) 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.
346 347 348 349 350 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 346 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.
354 355 356 357 358 359 360 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 354 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.
398 399 400 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 398 def multi_insert_sql(columns, values) [insert_sql(columns, LiteralString.new('VALUES ' + values.map {|r| literal(Array(r))}.join(COMMA_SEPARATOR)))] end |
#on_duplicate_key_update(*args) ⇒ Object
Sets up the insert methods 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.
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)
393 394 395 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 393 def on_duplicate_key_update(*args) clone(:on_duplicate_key_update => args) end |
#provides_accurate_rows_matched? ⇒ Boolean
MySQL uses the number of rows actually modified in the update, instead of the number of matched by the filter.
404 405 406 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 404 def provides_accurate_rows_matched? false end |
#quoted_identifier(c) ⇒ Object
MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.
409 410 411 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 409 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).
415 416 417 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 415 def replace_sql(*values) clone(:replace=>true).insert_sql(*values) end |
#supports_distinct_on? ⇒ Boolean
MySQL can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.
421 422 423 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 421 def supports_distinct_on? true end |
#supports_intersect_except? ⇒ Boolean
MySQL does not support INTERSECT or EXCEPT
426 427 428 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 426 def supports_intersect_except? false end |
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets
431 432 433 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 431 def true end |
#supports_timestamp_usecs? ⇒ Boolean
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.
438 439 440 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 438 def false end |