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 722 def calc_found_rows 723 clone(:calc_found_rows => true) 724 end
# File lib/sequel/adapters/shared/mysql.rb 656 def complex_expression_sql_append(sql, op, args) 657 case op 658 when :IN, :"NOT IN" 659 ds = args[1] 660 if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] 661 super(sql, op, [args[0], ds.from_self]) 662 else 663 super 664 end 665 when :~, :'!~', :'~*', :'!~*', :LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE' 666 if !db.mariadb? && db.server_version >= 80000 && [:~, :'!~'].include?(op) 667 func = Sequel.function(:REGEXP_LIKE, args[0], args[1], 'c') 668 func = ~func if op == :'!~' 669 return literal_append(sql, func) 670 end 671 672 sql << '(' 673 literal_append(sql, args[0]) 674 sql << ' ' 675 sql << 'NOT ' if [:'NOT LIKE', :'NOT ILIKE', :'!~', :'!~*'].include?(op) 676 sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') 677 sql << ' ' 678 sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) 679 literal_append(sql, args[1]) 680 if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) 681 sql << " ESCAPE " 682 literal_append(sql, "\\") 683 end 684 sql << ')' 685 when :'||' 686 if args.length > 1 687 sql << "CONCAT" 688 array_sql_append(sql, args) 689 else 690 literal_append(sql, args[0]) 691 end 692 when :'B~' 693 sql << "CAST(~" 694 literal_append(sql, args[0]) 695 sql << " AS SIGNED INTEGER)" 696 else 697 super 698 end 699 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 705 def constant_sql_append(sql, constant) 706 if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? 707 sql << 'CURRENT_TIMESTAMP(6)' 708 else 709 super 710 end 711 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 734 def delete_from(*tables) 735 clone(:delete_from=>tables) 736 end
Use GROUP BY instead of DISTINCT ON if arguments are provided.
# File lib/sequel/adapters/shared/mysql.rb 714 def distinct(*args) 715 args.empty? ? super : group(*args) 716 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 740 def explain(opts=OPTS) 741 # Load the PrettyTable class, needed for explain output 742 Sequel.extension(:_pretty_table) unless defined?(Sequel::PrettyTable) 743 744 ds = db.send(:metadata_dataset).with_sql(((opts[:extended] && (db.mariadb? || db.server_version < 50700)) ? 'EXPLAIN EXTENDED ' : 'EXPLAIN ') + select_sql).naked 745 rows = ds.all 746 Sequel::PrettyTable.string(rows, ds.columns) 747 end
Adds full text filter
# File lib/sequel/adapters/shared/mysql.rb 755 def full_text_search(cols, terms, opts = OPTS) 756 where(full_text_sql(cols, terms, opts)) 757 end
MySQL
specific full text search syntax.
# File lib/sequel/adapters/shared/mysql.rb 760 def full_text_sql(cols, terms, opts = OPTS) 761 terms = terms.join(' ') if terms.is_a?(Array) 762 SQL::PlaceholderLiteralString.new((opts[:boolean] ? MATCH_AGAINST_BOOLEAN : MATCH_AGAINST), [Array(cols), terms]) 763 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 773 def insert_ignore 774 clone(:insert_ignore=>true) 775 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 806 def on_duplicate_key_update(*args) 807 clone(:on_duplicate_key_update => args) 808 end
MySQL
uses the nonstandard ` (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 811 def quoted_identifier_append(sql, c) 812 sql << '`' << c.to_s.gsub('`', '``') << '`' 813 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 816 def supports_cte?(type=:select) 817 if db.mariadb? 818 type == :select && db.server_version >= 100200 819 else 820 case type 821 when :select, :update, :delete 822 db.server_version >= 80000 823 end 824 end 825 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 828 def supports_derived_column_lists? 829 false 830 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 834 def supports_distinct_on? 835 true 836 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 839 def supports_group_rollup? 840 true 841 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 844 def supports_intersect_except? 845 db.mariadb? && db.server_version >= 100300 846 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 854 def supports_modifying_joins? 855 true 856 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 859 def supports_nowait? 860 db.server_version >= (db.mariadb? ? 100300 : 80000) 861 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 865 def supports_ordered_distinct_on? 866 false 867 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 870 def supports_regexp? 871 true 872 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 875 def supports_skip_locked? 876 !db.mariadb? && db.server_version >= 80000 877 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 881 def supports_timestamp_usecs? 882 db.supports_timestamp_usecs? 883 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 886 def supports_window_clause? 887 !db.mariadb? && db.server_version >= 80000 888 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 891 def supports_window_functions? 892 db.server_version >= (db.mariadb? ? 100200 : 80000) 893 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 901 def update_ignore 902 clone(:update_ignore=>true) 903 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 908 def check_not_limited!(type) 909 super if type == :truncate || @opts[:offset] 910 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 914 def delete_from_sql(sql) 915 if joined_dataset? 916 sql << ' ' 917 tables = @opts[:delete_from] || @opts[:from][0..0] 918 source_list_append(sql, tables) 919 sql << ' FROM ' 920 source_list_append(sql, @opts[:from]) 921 select_join_sql(sql) 922 else 923 super 924 end 925 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 938 def insert_ignore_sql(sql) 939 sql << " IGNORE" if opts[:insert_ignore] 940 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 948 def insert_on_duplicate_key_update_sql(sql) 949 if update_cols = opts[:on_duplicate_key_update] 950 update_vals = nil 951 952 if update_cols.empty? 953 update_cols = columns 954 elsif update_cols.last.is_a?(Hash) 955 update_vals = update_cols.last 956 update_cols = update_cols[0..-2] 957 end 958 959 sql << " ON DUPLICATE KEY UPDATE " 960 c = false 961 co = ', ' 962 values = '=VALUES(' 963 endp = ')' 964 update_cols.each do |col| 965 sql << co if c 966 quote_identifier_append(sql, col) 967 sql << values 968 quote_identifier_append(sql, col) 969 sql << endp 970 c ||= true 971 end 972 if update_vals 973 eq = '=' 974 update_vals.map do |col,v| 975 sql << co if c 976 quote_identifier_append(sql, col) 977 sql << eq 978 literal_append(sql, v) 979 c ||= true 980 end 981 end 982 end 983 end
MySQL
doesn't use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 986 def insert_values_sql(sql) 987 values = opts[:values] 988 if values.is_a?(Array) && values.empty? 989 sql << " VALUES ()" 990 else 991 super 992 end 993 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 996 def join_type_sql(join_type) 997 if join_type == :straight 998 'STRAIGHT_JOIN' 999 else 1000 super 1001 end 1002 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 1005 def limit_sql(sql) 1006 if l = @opts[:limit] 1007 sql << " LIMIT " 1008 literal_append(sql, l) 1009 end 1010 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 1015 def literal_blob_append(sql, v) 1016 if v.empty? 1017 sql << "''" 1018 else 1019 sql << "0x" << v.unpack("H*").first 1020 end 1021 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1024 def literal_false 1025 '0' 1026 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 1029 def literal_float(v) 1030 if v.infinite? || v.nan? 1031 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 1032 else 1033 super 1034 end 1035 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1043 def literal_true 1044 '1' 1045 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1048 def multi_insert_sql_strategy 1049 :values 1050 end
# File lib/sequel/adapters/shared/mysql.rb 1052 def non_sql_option?(key) 1053 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1054 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1057 def requires_emulating_nulls_first? 1058 true 1059 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1091 def select_calc_found_rows_sql(sql) 1092 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1093 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 1069 def select_lock_sql(sql) 1070 lock = @opts[:lock] 1071 if lock == :share 1072 if !db.mariadb? && db.server_version >= 80000 1073 sql << ' FOR SHARE' 1074 else 1075 sql << ' LOCK IN SHARE MODE' 1076 end 1077 else 1078 super 1079 end 1080 1081 if lock 1082 if @opts[:skip_locked] 1083 sql << " SKIP LOCKED" 1084 elsif @opts[:nowait] 1085 sql << " NOWAIT" 1086 end 1087 end 1088 end
# File lib/sequel/adapters/shared/mysql.rb 1061 def select_only_offset_sql(sql) 1062 sql << " LIMIT " 1063 literal_append(sql, @opts[:offset]) 1064 sql << ",18446744073709551615" 1065 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1096 def select_with_sql_base 1097 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1098 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 943 def update_ignore_sql(sql) 944 sql << " IGNORE" if opts[:update_ignore] 945 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1101 def uses_with_rollup? 1102 true 1103 end