Module: Sequel::MySQL::DatasetMethods
- Includes:
- Dataset::Replace
- 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
- 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
- COMMA =
Dataset::COMMA
- LIMIT =
Dataset::LIMIT
- GROUP_BY =
Dataset::GROUP_BY
- ESCAPE =
Dataset::ESCAPE
- BACKSLASH =
Dataset::BACKSLASH
- 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
- 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
- BLOB_START =
"0x".freeze
- EMPTY_BLOB =
"''".freeze
- HSTAR =
"H*".freeze
- CURRENT_TIMESTAMP_56 =
'CURRENT_TIMESTAMP(6)'.freeze
- ONLY_OFFSET =
Comes directly from MySQL’s documentation, used for queries with limits without offsets
",18446744073709551615".freeze
Constants included from Dataset::Replace
Dataset::Replace::INSERT, Dataset::Replace::REPLACE
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.
-
#constant_sql_append(sql, constant) ⇒ Object
MySQL’s CURRENT_TIMESTAMP does not use fractional seconds, even if the database itself supports fractional seconds.
-
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
-
#explain(opts = 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 = OPTS) ⇒ Object
Adds full text filter.
-
#full_text_sql(cols, terms, opts = OPTS) ⇒ Object
MySQL specific full text search syntax.
-
#insert_ignore ⇒ Object
Sets up the insert methods to use INSERT IGNORE.
-
#join_table(type, table, expr = nil, opts = OPTS, &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.
-
#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.
-
#supports_derived_column_lists? ⇒ Boolean
MySQL does not support derived column lists.
-
#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_limits_in_correlated_subqueries? ⇒ Boolean
MySQL does not support limits in correlated subqueries (or any subqueries that use IN).
-
#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.
Methods included from Dataset::Replace
#multi_replace, #replace, #replace_sql, #supports_replace?
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
674 675 676 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 674 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.
614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 614 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)) if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) sql << ESCAPE literal_append(sql, BACKSLASH) end 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 |
#constant_sql_append(sql, constant) ⇒ Object
MySQL’s CURRENT_TIMESTAMP does not use fractional seconds, even if the database itself supports fractional seconds. If MySQL 5.6.4+ is being used, use a value that will return fractional seconds.
657 658 659 660 661 662 663 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 657 def constant_sql_append(sql, constant) if constant == :CURRENT_TIMESTAMP && sql << CURRENT_TIMESTAMP_56 else super end end |
#distinct(*args) ⇒ Object
Use GROUP BY instead of DISTINCT ON if arguments are provided.
666 667 668 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 666 def distinct(*args) args.empty? ? super : group(*args) end |
#explain(opts = OPTS) ⇒ Object
Return the results of an EXPLAIN query as a string. Options:
- :extended
-
Use EXPLAIN EXPTENDED instead of EXPLAIN if true.
680 681 682 683 684 685 686 687 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 680 def explain(opts=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.
690 691 692 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 690 def for_share lock_style(:share) end |
#full_text_search(cols, terms, opts = OPTS) ⇒ Object
Adds full text filter
695 696 697 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 695 def full_text_search(cols, terms, opts = OPTS) filter(full_text_sql(cols, terms, opts)) end |
#full_text_sql(cols, terms, opts = OPTS) ⇒ Object
MySQL specific full text search syntax.
700 701 702 703 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 700 def full_text_sql(cols, terms, opts = OPTS) terms = terms.join(' ') if terms.is_a?(Array) SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 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)
734 735 736 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 734 def insert_ignore clone(:insert_ignore=>true) end |
#join_table(type, table, expr = nil, opts = OPTS, &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.
707 708 709 710 711 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 707 def join_table(type, table, expr=nil, opts=OPTS, &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, opts, &block) end |
#join_type_sql(join_type) ⇒ Object
Transforms :natural_inner to NATURAL LEFT JOIN and straight to STRAIGHT_JOIN.
715 716 717 718 719 720 721 722 723 724 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 715 def join_type_sql(join_type) case join_type when :straight STRAIGHT_JOIN when :natural_inner NATURAL_LEFT_JOIN else super end 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. If you pass a hash you can customize the values (for example, to increment a numeric field).
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)
dataset.on_duplicate_key_update(
:value => Sequel.lit('value + VALUES(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=value + VALUES(value)
767 768 769 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 767 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.
772 773 774 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 772 def quoted_identifier_append(sql, c) sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK end |
#supports_derived_column_lists? ⇒ Boolean
MySQL does not support derived column lists
777 778 779 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 777 def supports_derived_column_lists? false 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.
783 784 785 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 783 def supports_distinct_on? true end |
#supports_group_rollup? ⇒ Boolean
MySQL supports GROUP BY WITH ROLLUP (but not CUBE)
788 789 790 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 788 def supports_group_rollup? true end |
#supports_intersect_except? ⇒ Boolean
MySQL does not support INTERSECT or EXCEPT
793 794 795 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 793 def supports_intersect_except? false end |
#supports_limits_in_correlated_subqueries? ⇒ Boolean
MySQL does not support limits in correlated subqueries (or any subqueries that use IN).
798 799 800 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 798 def false end |
#supports_modifying_joins? ⇒ Boolean
MySQL supports modifying joined datasets
803 804 805 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 803 def true end |
#supports_ordered_distinct_on? ⇒ Boolean
MySQL’s DISTINCT ON emulation using GROUP BY does not respect the queries ORDER BY clause.
809 810 811 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 809 def supports_ordered_distinct_on? false end |
#supports_regexp? ⇒ Boolean
MySQL supports pattern matching via regular expressions
814 815 816 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 814 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.
821 822 823 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 821 def db. 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
831 832 833 |
# File 'lib/sequel/adapters/shared/mysql.rb', line 831 def update_ignore clone(:update_ignore=>true) end |