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
- SQL_CALC_FOUND_ROWS =
' SQL_CALC_FOUND_ROWS'.freeze
- DELETE_CLAUSE_METHODS =
Dataset.clause_methods(:delete, %w'delete from where order limit')
- INSERT_CLAUSE_METHODS =
Dataset.clause_methods(:insert, %w'insert ignore into columns values on_duplicate_key_update')
- SELECT_CLAUSE_METHODS =
Dataset.clause_methods(:select, %w'select distinct calc_found_rows columns from join where group having compounds order limit lock')
- UPDATE_CLAUSE_METHODS =
Dataset.clause_methods(:update, %w'update ignore table set where order limit')
- APOS =
Dataset::APOS
- APOS_RE =
Dataset::APOS_RE
- DOUBLE_APOS =
Dataset::DOUBLE_APOS
- SPACE =
Dataset::SPACE
- PAREN_OPEN =
Dataset::PAREN_OPEN
- PAREN_CLOSE =
Dataset::PAREN_CLOSE
- NOT_SPACE =
Dataset::NOT_SPACE
- FROM =
Dataset::FROM
- INSERT =
Dataset::INSERT
- COMMA =
Dataset::COMMA
- LIMIT =
Dataset::LIMIT
- GROUP_BY =
Dataset::GROUP_BY
- REGEXP =
'REGEXP'.freeze
- LIKE =
'LIKE'.freeze
- BINARY =
'BINARY '.freeze
- CONCAT =
"CONCAT".freeze
- CAST_BITCOMP_OPEN =
"CAST(~".freeze
- CAST_BITCOMP_CLOSE =
" AS SIGNED INTEGER)".freeze
- STRAIGHT_JOIN =
'STRAIGHT_JOIN'.freeze
- NATURAL_LEFT_JOIN =
'NATURAL LEFT JOIN'.freeze
- BACKTICK =
'`'.freeze
- BACKTICK_RE =
/`/.freeze
- DOUBLE_BACKTICK =
'``'.freeze
- EMPTY_COLUMNS =
" ()".freeze
- EMPTY_VALUES =
" VALUES ()".freeze
- IGNORE =
" IGNORE".freeze
- REPLACE =
'REPLACE'.freeze
- ON_DUPLICATE_KEY_UPDATE =
" ON DUPLICATE KEY UPDATE ".freeze
- EQ_VALUES =
'=VALUES('.freeze
- EQ =
'='.freeze
- WITH_ROLLUP =
' WITH ROLLUP'.freeze
- MATCH_AGAINST =
["(MATCH ".freeze, " AGAINST (".freeze, "))".freeze].freeze
- MATCH_AGAINST_BOOLEAN =
["(MATCH ".freeze, " AGAINST (".freeze, " IN BOOLEAN MODE))".freeze].freeze
- EXPLAIN =
'EXPLAIN '.freeze
- EXPLAIN_EXTENDED =
'EXPLAIN EXTENDED '.freeze
- BACKSLASH_RE =
/\\/.freeze
- QUAD_BACKSLASH =
"\\\\\\\\".freeze
Instance Method Summary collapse
-
#calc_found_rows ⇒ Object
Sets up the select methods to use SQL_CALC_FOUND_ROWS option.
-
#complex_expression_sql_append(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.
-
#explain(opts = {}) ⇒ Object
Return the results of an EXPLAIN query as a string.
-
#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, terms, 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 = {}, &block) ⇒ 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.
-
#multi_replace(*values) ⇒ Object
Replace multiple rows in a single query.
-
#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.
-
#quoted_identifier_append(sql, c) ⇒ Object
MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.
-
#replace(*values) ⇒ Object
Execute a REPLACE statement on the database.
-
#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_group_rollup? ⇒ Boolean
MySQL supports GROUP BY WITH ROLLUP (but not CUBE).
-
#supports_intersect_except? ⇒ Boolean
MySQL does not support INTERSECT or EXCEPT.
-
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets.
-
#supports_ordered_distinct_on? ⇒ Boolean
MySQL’s DISTINCT ON emulation using GROUP BY does not respect the queries ORDER BY clause.
-
#supports_regexp? ⇒ Boolean
MySQL supports pattern matching via regular expressions.
-
#supports_timestamp_usecs? ⇒ Boolean
MySQL does support fractional timestamps in literal timestamps, but it ignores them.
-
#update_ignore ⇒ Object
Sets up the update methods to use UPDATE IGNORE.
Instance Method Details
#calc_found_rows ⇒ Object
Sets up the select methods to use SQL_CALC_FOUND_ROWS option.
dataset.calc_found_rows.limit(10)
# SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10
566 567 568 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 566 def calc_found_rows clone(:calc_found_rows => true) end |
#complex_expression_sql_append(sql, op, args) ⇒ Object
MySQL specific syntax for LIKE/REGEXP searches, as well as string concatenation.
522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 522 def complex_expression_sql_append(sql, op, args) case op when :IN, :"NOT IN" ds = args.at(1) if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] super(sql, op, [args.at(0), ds.from_self]) else super end when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' sql << PAREN_OPEN literal_append(sql, args.at(0)) sql << SPACE sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? REGEXP : LIKE) sql << SPACE sql << BINARY if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) literal_append(sql, args.at(1)) sql << PAREN_CLOSE when :'||' if args.length > 1 sql << CONCAT array_sql_append(sql, args) else literal_append(sql, args.at(0)) end when :'B~' sql << CAST_BITCOMP_OPEN literal_append(sql, args.at(0)) sql << CAST_BITCOMP_CLOSE else super end end |
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
558 559 560 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 558 def distinct(*args) args.empty? ? super : group(*args) end |
#explain(opts = {}) ⇒ Object
Return the results of an EXPLAIN query as a string. Options:
- :extended
-
Use EXPLAIN EXPTENDED instead of EXPLAIN if true.
572 573 574 575 576 577 578 579 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 572 def explain(opts={}) # Load the PrettyTable class, needed for explain output Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) ds = db.send(:metadata_dataset).with_sql((opts[:extended] ? EXPLAIN_EXTENDED : EXPLAIN) + select_sql).naked rows = ds.all Sequel::PrettyTable.string(rows, ds.columns) end |
#for_share ⇒ Object
Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows.
582 583 584 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 582 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = {}) ⇒ Object
Adds full text filter
587 588 589 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 587 def full_text_search(cols, terms, opts = {}) filter(full_text_sql(cols, terms, opts)) end |
#full_text_sql(cols, terms, opts = {}) ⇒ Object
MySQL specific full text search syntax.
592 593 594 595 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 592 def full_text_sql(cols, terms, opts = {}) terms = terms.join(' ') if terms.is_a?(Array) SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) end |
#having(*cond, &block) ⇒ Object
MySQL allows HAVING clause on ungrouped datasets.
598 599 600 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 598 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)
631 632 633 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 631 def insert_ignore clone(:insert_ignore=>true) end |
#join_table(type, table, expr = nil, table_alias = {}, &block) ⇒ 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.
604 605 606 607 608 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 604 def join_table(type, table, expr=nil, table_alias={}, &block) 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, &block) end |
#join_type_sql(join_type) ⇒ Object
Transforms :natural_inner to NATURAL LEFT JOIN and straight to STRAIGHT_JOIN.
612 613 614 615 616 617 618 619 620 621 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 612 def join_type_sql(join_type) case join_type when :straight STRAIGHT_JOIN when :natural_inner NATURAL_LEFT_JOIN else super end end |
#multi_insert_sql(columns, values) ⇒ Object
MySQL specific syntax for inserting multiple values at once.
659 660 661 662 663 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 659 def multi_insert_sql(columns, values) sql = LiteralString.new('VALUES ') expression_list_append(sql, values.map{|r| Array(r)}) [insert_sql(columns, sql)] end |
#multi_replace(*values) ⇒ Object
Replace multiple rows in a single query.
682 683 684 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 682 def multi_replace(*values) clone(:replace=>true).multi_insert(*values) 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)
654 655 656 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 654 def on_duplicate_key_update(*args) clone(:on_duplicate_key_update => args) end |
#quoted_identifier_append(sql, c) ⇒ Object
MySQL uses the nonstandard ‘ (backtick) for quoting identifiers.
666 667 668 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 666 def quoted_identifier_append(sql, c) sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK end |
#replace(*values) ⇒ Object
Execute a REPLACE statement on the database.
671 672 673 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 671 def replace(*values) execute_insert(replace_sql(*values)) end |
#replace_sql(*values) ⇒ Object
MySQL specific syntax for REPLACE (aka UPSERT, or update if exists, insert if it doesn’t).
677 678 679 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 677 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.
688 689 690 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 688 def supports_distinct_on? true end |
#supports_group_rollup? ⇒ Boolean
MySQL supports GROUP BY WITH ROLLUP (but not CUBE)
693 694 695 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 693 def supports_group_rollup? true end |
#supports_intersect_except? ⇒ Boolean
MySQL does not support INTERSECT or EXCEPT
698 699 700 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 698 def supports_intersect_except? false end |
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets
703 704 705 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 703 def true end |
#supports_ordered_distinct_on? ⇒ Boolean
MySQL’s DISTINCT ON emulation using GROUP BY does not respect the queries ORDER BY clause.
709 710 711 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 709 def supports_ordered_distinct_on? false end |
#supports_regexp? ⇒ Boolean
MySQL supports pattern matching via regular expressions
714 715 716 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 714 def supports_regexp? 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.
721 722 723 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 721 def false end |
#update_ignore ⇒ Object
Sets up the update methods to use UPDATE IGNORE. Useful if you have a unique key and want to just skip updating rows that violate the unique key restriction.
dataset.update_ignore.update({:name => 'a', :value => 1})
# UPDATE IGNORE tablename SET name = 'a', value = 1
731 732 733 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 731 def update_ignore clone(:update_ignore=>true) end |