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 715 def calc_found_rows 716 clone(:calc_found_rows => true) 717 end
# File lib/sequel/adapters/shared/mysql.rb 649 def complex_expression_sql_append(sql, op, args) 650 case op 651 when :IN, :"NOT IN" 652 ds = args[1] 653 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 654 super(sql, op, [args[0], ds.from_self]) 655 else 656 super 657 end 658 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 659 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 660 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 661 func = ~func if op == :'!~' 662 return literal_append(sql, func) 663 end 664 665 sql << '(' 666 literal_append(sql, args[0]) 667 sql << ' ' 668 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 669 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 670 sql << ' ' 671 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 672 literal_append(sql, args[1]) 673 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 674 sql << " ESCAPE " 675 literal_append(sql, "\\") 676 end 677 sql << ')' 678 when :'||' 679 if args.length > 1 680 sql << "CONCAT" 681 array_sql_append(sql, args) 682 else 683 literal_append(sql, args[0]) 684 end 685 when :'B~' 686 sql << "CAST(~" 687 literal_append(sql, args[0]) 688 sql << " AS SIGNED INTEGER)" 689 else 690 super 691 end 692 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 698 def constant_sql_append(sql, constant) 699 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 700 sql << 'CURRENT_TIMESTAMP(6)' 701 else 702 super 703 end 704 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 727 def delete_from(*tables) 728 clone(:delete_from=>tables) 729 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 707 def distinct(*args) 708 args.empty? ? super : group(*args) 709 end
Return the results of an EXPLAIN query as a string. Options:
- :extended
-
Use EXPLAIN EXTENDED instead of EXPLAIN if true.
# File lib/sequel/adapters/shared/mysql.rb 733 def explain(opts=OPTS) 734 # Load the PrettyTable class, needed for explain output 735 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 736 737 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 738 rows = ds.all 739 Sequel::PrettyTable.string(rows, ds.columns) 740 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 748 def full_text_search(cols, terms, opts = OPTS) 749 where(full_text_sql(cols, terms, opts)) 750 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 753 def full_text_sql(cols, terms, opts = OPTS) 754 terms = terms.join(' ') if terms.is_a?(Array) 755 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 756 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 766 def insert_ignore 767 clone(:insert_ignore=>true) 768 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 799 def on_duplicate_key_update(*args) 800 clone(:on_duplicate_key_update => args) 801 end
MySQL
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 804 def quoted_identifier_append(sql, c) 805 sql << '`' << c.to_s.gsub('`', '``') << '`' 806 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 809 def supports_cte?(type=:select) 810 if db.mariadb? 811 type == :select && db.server_version >= 100200 812 else 813 case type 814 when :select, :update, :delete 815 db.server_version >= 80000 816 end 817 end 818 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 821 def supports_derived_column_lists? 822 false 823 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 827 def supports_distinct_on? 828 true 829 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 832 def supports_group_rollup? 833 true 834 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 837 def supports_intersect_except? 838 db.mariadb? && db.server_version >= 100300 839 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 847 def supports_modifying_joins? 848 true 849 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 852 def supports_nowait? 853 db.server_version >= (db.mariadb? ? 100300 : 80000) 854 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 858 def supports_ordered_distinct_on? 859 false 860 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 863 def supports_regexp? 864 true 865 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 868 def supports_skip_locked? 869 !db.mariadb? && db.server_version >= 80000 870 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 874 def supports_timestamp_usecs? 875 db.supports_timestamp_usecs? 876 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 879 def supports_window_clause? 880 !db.mariadb? && db.server_version >= 80000 881 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 884 def supports_window_functions? 885 db.server_version >= (db.mariadb? ? 100200 : 80000) 886 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 894 def update_ignore 895 clone(:update_ignore=>true) 896 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 901 def check_not_limited!(type) 902 super if type == :truncate || @opts[:offset] 903 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 907 def delete_from_sql(sql) 908 if joined_dataset? 909 sql << ' ' 910 tables = @opts[:delete_from] || @opts[:from][0..0] 911 source_list_append(sql, tables) 912 sql << ' FROM ' 913 source_list_append(sql, @opts[:from]) 914 select_join_sql(sql) 915 else 916 super 917 end 918 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 931 def insert_ignore_sql(sql) 932 sql << " IGNORE" if opts[:insert_ignore] 933 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 941 def insert_on_duplicate_key_update_sql(sql) 942 if update_cols = opts[:on_duplicate_key_update] 943 update_vals = nil 944 945 if update_cols.empty? 946 update_cols = columns 947 elsif update_cols.last.is_a?(Hash) 948 update_vals = update_cols.last 949 update_cols = update_cols[0..-2] 950 end 951 952 sql << " ON DUPLICATE KEY UPDATE " 953 c = false 954 co = ', ' 955 values = '=VALUES(' 956 endp = ')' 957 update_cols.each do |col| 958 sql << co if c 959 quote_identifier_append(sql, col) 960 sql << values 961 quote_identifier_append(sql, col) 962 sql << endp 963 c ||= true 964 end 965 if update_vals 966 eq = '=' 967 update_vals.map do |col,v| 968 sql << co if c 969 quote_identifier_append(sql, col) 970 sql << eq 971 literal_append(sql, v) 972 c ||= true 973 end 974 end 975 end 976 end
MySQL
doesn't use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 979 def insert_values_sql(sql) 980 values = opts[:values] 981 if values.is_a?(Array) && values.empty? 982 sql << " VALUES ()" 983 else 984 super 985 end 986 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 989 def join_type_sql(join_type) 990 if join_type == :straight 991 'STRAIGHT_JOIN' 992 else 993 super 994 end 995 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 998 def limit_sql(sql) 999 if l = @opts[:limit] 1000 sql << " LIMIT " 1001 literal_append(sql, l) 1002 end 1003 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 1008 def literal_blob_append(sql, v) 1009 if v.empty? 1010 sql << "''" 1011 else 1012 sql << "0x" << v.unpack("H*").first 1013 end 1014 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1017 def literal_false 1018 '0' 1019 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 1022 def literal_float(v) 1023 if v.infinite? || v.nan? 1024 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 1025 else 1026 super 1027 end 1028 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1036 def literal_true 1037 '1' 1038 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1041 def multi_insert_sql_strategy 1042 :values 1043 end
# File lib/sequel/adapters/shared/mysql.rb 1045 def non_sql_option?(key) 1046 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1047 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1050 def requires_emulating_nulls_first? 1051 true 1052 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1084 def select_calc_found_rows_sql(sql) 1085 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1086 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 1062 def select_lock_sql(sql) 1063 lock = @opts[:lock] 1064 if lock == :share 1065 if !db.mariadb? && db.server_version >= 80000 1066 sql << ' FOR SHARE' 1067 else 1068 sql << ' LOCK IN SHARE MODE' 1069 end 1070 else 1071 super 1072 end 1073 1074 if lock 1075 if @opts[:skip_locked] 1076 sql << " SKIP LOCKED" 1077 elsif @opts[:nowait] 1078 sql << " NOWAIT" 1079 end 1080 end 1081 end
# File lib/sequel/adapters/shared/mysql.rb 1054 def select_only_offset_sql(sql) 1055 sql << " LIMIT " 1056 literal_append(sql, @opts[:offset]) 1057 sql << ",18446744073709551615" 1058 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1089 def select_with_sql_base 1090 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1091 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 936 def update_ignore_sql(sql) 937 sql << " IGNORE" if opts[:update_ignore] 938 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1094 def uses_with_rollup? 1095 true 1096 end