module Sequel::MySQL::DatasetMethods
Dataset
methods shared by datasets that use MySQL
databases.
Constants
- MATCH_AGAINST
- MATCH_AGAINST_BOOLEAN
Public Instance Methods
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
# File lib/sequel/adapters/shared/mysql.rb 677 def calc_found_rows 678 clone(:calc_found_rows => true) 679 end
# File lib/sequel/adapters/shared/mysql.rb 611 def complex_expression_sql_append(sql, op, args) 612 case op 613 when :IN, :"NOT IN" 614 ds = args[1] 615 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 616 super(sql, op, [args[0], ds.from_self]) 617 else 618 super 619 end 620 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 621 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 622 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 623 func = ~func if op == :'!~' 624 return literal_append(sql, func) 625 end 626 627 sql << '(' 628 literal_append(sql, args[0]) 629 sql << ' ' 630 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 631 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 632 sql << ' ' 633 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 634 literal_append(sql, args[1]) 635 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 636 sql << " ESCAPE " 637 literal_append(sql, "\\") 638 end 639 sql << ')' 640 when :'||' 641 if args.length > 1 642 sql << "CONCAT" 643 array_sql_append(sql, args) 644 else 645 literal_append(sql, args[0]) 646 end 647 when :'B~' 648 sql << "CAST(~" 649 literal_append(sql, args[0]) 650 sql << " AS SIGNED INTEGER)" 651 else 652 super 653 end 654 end
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.
# File lib/sequel/adapters/shared/mysql.rb 660 def constant_sql_append(sql, constant) 661 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 662 sql << 'CURRENT_TIMESTAMP(6)' 663 else 664 super 665 end 666 end
Sets up the select methods to delete from if deleting from a joined dataset:
DB[:a].join(:b, a_id: :id).delete # DELETE a FROM a INNER JOIN b ON (b.a_id = a.id) DB[:a].join(:b, a_id: :id).delete_from(:a, :b).delete # DELETE a, b FROM a INNER JOIN b ON (b.a_id = a.id)
# File lib/sequel/adapters/shared/mysql.rb 689 def delete_from(*tables) 690 clone(:delete_from=>tables) 691 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 669 def distinct(*args) 670 args.empty? ? super : group(*args) 671 end
Return the results of an EXPLAIN query as a string. Options:
- :extended
-
Use EXPLAIN EXPTENDED instead of EXPLAIN if true.
# File lib/sequel/adapters/shared/mysql.rb 695 def explain(opts=OPTS) 696 # Load the PrettyTable class, needed for explain output 697 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 698 699 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 700 rows = ds.all 701 Sequel::PrettyTable.string(rows, ds.columns) 702 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 710 def full_text_search(cols, terms, opts = OPTS) 711 where(full_text_sql(cols, terms, opts)) 712 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 715 def full_text_sql(cols, terms, opts = OPTS) 716 terms = terms.join(' ') if terms.is_a?(Array) 717 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 718 end
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)
# File lib/sequel/adapters/shared/mysql.rb 737 def insert_ignore 738 clone(:insert_ignore=>true) 739 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 721 def join_type_sql(join_type) 722 if join_type == :straight 723 'STRAIGHT_JOIN' 724 else 725 super 726 end 727 end
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)
# File lib/sequel/adapters/shared/mysql.rb 770 def on_duplicate_key_update(*args) 771 clone(:on_duplicate_key_update => args) 772 end
MySQL
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 775 def quoted_identifier_append(sql, c) 776 sql << '`' << c.to_s.gsub('`', '``') << '`' 777 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 780 def supports_cte?(type=:select) 781 if db.mariadb? 782 type == :select && db.server_version >= 100200 783 else 784 case type 785 when :select, :update, :delete 786 db.server_version >= 80000 787 end 788 end 789 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 792 def supports_derived_column_lists? 793 false 794 end
MySQL
can emulate DISTINCT ON with its non-standard GROUP BY implementation, though the rows returned cannot be made deterministic through ordering.
# File lib/sequel/adapters/shared/mysql.rb 798 def supports_distinct_on? 799 true 800 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 803 def supports_group_rollup? 804 true 805 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 808 def supports_intersect_except? 809 db.mariadb? && db.server_version >= 100300 810 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 818 def supports_modifying_joins? 819 true 820 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 823 def supports_nowait? 824 db.server_version >= (db.mariadb? ? 100300 : 80000) 825 end
MySQL's DISTINCT ON emulation using GROUP BY does not respect the query's ORDER BY clause.
# File lib/sequel/adapters/shared/mysql.rb 829 def supports_ordered_distinct_on? 830 false 831 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 834 def supports_regexp? 835 true 836 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 839 def supports_skip_locked? 840 !db.mariadb? && db.server_version >= 80000 841 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 845 def supports_timestamp_usecs? 846 db.supports_timestamp_usecs? 847 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 850 def supports_window_clause? 851 !db.mariadb? && db.server_version >= 80000 852 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 855 def supports_window_functions? 856 db.server_version >= (db.mariadb? ? 100200 : 80000) 857 end
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
# File lib/sequel/adapters/shared/mysql.rb 865 def update_ignore 866 clone(:update_ignore=>true) 867 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 872 def check_not_limited!(type) 873 super if type == :truncate || @opts[:offset] 874 end
Consider the first table in the joined dataset is the table to delete from, but include the others for the purposes of selecting rows.
# File lib/sequel/adapters/shared/mysql.rb 878 def delete_from_sql(sql) 879 if joined_dataset? 880 sql << ' ' 881 tables = @opts[:delete_from] || @opts[:from][0..0] 882 source_list_append(sql, tables) 883 sql << ' FROM ' 884 source_list_append(sql, @opts[:from]) 885 select_join_sql(sql) 886 else 887 super 888 end 889 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 902 def insert_ignore_sql(sql) 903 sql << " IGNORE" if opts[:insert_ignore] 904 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 912 def insert_on_duplicate_key_update_sql(sql) 913 if update_cols = opts[:on_duplicate_key_update] 914 update_vals = nil 915 916 if update_cols.empty? 917 update_cols = columns 918 elsif update_cols.last.is_a?(Hash) 919 update_vals = update_cols.last 920 update_cols = update_cols[0..-2] 921 end 922 923 sql << " ON DUPLICATE KEY UPDATE " 924 c = false 925 co = ', ' 926 values = '=VALUES(' 927 endp = ')' 928 update_cols.each do |col| 929 sql << co if c 930 quote_identifier_append(sql, col) 931 sql << values 932 quote_identifier_append(sql, col) 933 sql << endp 934 c ||= true 935 end 936 if update_vals 937 eq = '=' 938 update_vals.map do |col,v| 939 sql << co if c 940 quote_identifier_append(sql, col) 941 sql << eq 942 literal_append(sql, v) 943 c ||= true 944 end 945 end 946 end 947 end
MySQL
doesn't use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 950 def insert_values_sql(sql) 951 values = opts[:values] 952 if values.is_a?(Array) && values.empty? 953 sql << " VALUES ()" 954 else 955 super 956 end 957 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 960 def limit_sql(sql) 961 if l = @opts[:limit] 962 sql << " LIMIT " 963 literal_append(sql, l) 964 end 965 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 970 def literal_blob_append(sql, v) 971 if v.empty? 972 sql << "''" 973 else 974 sql << "0x" << v.unpack("H*").first 975 end 976 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 979 def literal_false 980 '0' 981 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 984 def literal_float(v) 985 if v.infinite? || v.nan? 986 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 987 else 988 super 989 end 990 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 998 def literal_true 999 '1' 1000 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1003 def multi_insert_sql_strategy 1004 :values 1005 end
# File lib/sequel/adapters/shared/mysql.rb 1007 def non_sql_option?(key) 1008 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1009 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1012 def requires_emulating_nulls_first? 1013 true 1014 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1046 def select_calc_found_rows_sql(sql) 1047 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1048 end
Support FOR SHARE locking when using the :share lock style. Use SKIP LOCKED if skipping locked rows.
# File lib/sequel/adapters/shared/mysql.rb 1024 def select_lock_sql(sql) 1025 lock = @opts[:lock] 1026 if lock == :share 1027 if !db.mariadb? && db.server_version >= 80000 1028 sql << ' FOR SHARE' 1029 else 1030 sql << ' LOCK IN SHARE MODE' 1031 end 1032 else 1033 super 1034 end 1035 1036 if lock 1037 if @opts[:skip_locked] 1038 sql << " SKIP LOCKED" 1039 elsif @opts[:nowait] 1040 sql << " NOWAIT" 1041 end 1042 end 1043 end
# File lib/sequel/adapters/shared/mysql.rb 1016 def select_only_offset_sql(sql) 1017 sql << " LIMIT " 1018 literal_append(sql, @opts[:offset]) 1019 sql << ",18446744073709551615" 1020 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1051 def select_with_sql_base 1052 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1053 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 907 def update_ignore_sql(sql) 908 sql << " IGNORE" if opts[:update_ignore] 909 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1056 def uses_with_rollup? 1057 true 1058 end