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
Support insert select for associations, so that the model code can use returning instead of a separate query.
# File lib/sequel/adapters/shared/mysql.rb 779 def insert_select(*values) 780 return unless supports_insert_select? 781 # Handle case where query does not return a row 782 server?(:default).with_sql_first(insert_select_sql(*values)) || false 783 end
The SQL
to use for an insert_select
, adds a RETURNING clause to the insert unless the RETURNING clause is already present.
# File lib/sequel/adapters/shared/mysql.rb 787 def insert_select_sql(*values) 788 ds = opts[:returning] ? self : returning 789 ds.insert_sql(*values) 790 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 821 def on_duplicate_key_update(*args) 822 clone(:on_duplicate_key_update => args) 823 end
MySQL
uses the nonstandard ‘ (backtick) for quoting identifiers.
# File lib/sequel/adapters/shared/mysql.rb 826 def quoted_identifier_append(sql, c) 827 sql << '`' << c.to_s.gsub('`', '``') << '`' 828 end
MariaDB 10.2+ and MySQL
8+ support CTEs
# File lib/sequel/adapters/shared/mysql.rb 831 def supports_cte?(type=:select) 832 if db.mariadb? 833 type == :select && db.server_version >= 100200 834 else 835 case type 836 when :select, :update, :delete 837 db.server_version >= 80000 838 end 839 end 840 end
MySQL
does not support derived column lists
# File lib/sequel/adapters/shared/mysql.rb 843 def supports_derived_column_lists? 844 false 845 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 849 def supports_distinct_on? 850 true 851 end
MySQL
supports GROUP BY WITH ROLLUP (but not CUBE)
# File lib/sequel/adapters/shared/mysql.rb 854 def supports_group_rollup? 855 true 856 end
MariaDB 10.3+ supports INTERSECT or EXCEPT
# File lib/sequel/adapters/shared/mysql.rb 859 def supports_intersect_except? 860 db.mariadb? && db.server_version >= 100300 861 end
MySQL
supports modifying joined datasets
# File lib/sequel/adapters/shared/mysql.rb 869 def supports_modifying_joins? 870 true 871 end
MySQL
8+ and MariaDB 10.3+ support NOWAIT.
# File lib/sequel/adapters/shared/mysql.rb 874 def supports_nowait? 875 db.server_version >= (db.mariadb? ? 100300 : 80000) 876 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 880 def supports_ordered_distinct_on? 881 false 882 end
MySQL
supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/mysql.rb 885 def supports_regexp? 886 true 887 end
MariaDB 10.5.0 supports INSERT RETURNING.
# File lib/sequel/adapters/shared/mysql.rb 890 def supports_returning?(type) 891 (type == :insert && db.mariadb? && db.adapter_scheme != :jdbc) ? (db.server_version >= 100500) : false 892 end
MySQL
8+ supports SKIP LOCKED.
# File lib/sequel/adapters/shared/mysql.rb 895 def supports_skip_locked? 896 !db.mariadb? && db.server_version >= 80000 897 end
Check the database setting for whether fractional timestamps are suppported.
# File lib/sequel/adapters/shared/mysql.rb 901 def supports_timestamp_usecs? 902 db.supports_timestamp_usecs? 903 end
MySQL
8+ supports WINDOW clause.
# File lib/sequel/adapters/shared/mysql.rb 906 def supports_window_clause? 907 !db.mariadb? && db.server_version >= 80000 908 end
MariaDB 10.2+ and MySQL
8+ support window functions
# File lib/sequel/adapters/shared/mysql.rb 911 def supports_window_functions? 912 db.server_version >= (db.mariadb? ? 100200 : 80000) 913 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 921 def update_ignore 922 clone(:update_ignore=>true) 923 end
Private Instance Methods
Allow update and delete for limited datasets, unless there is an offset.
# File lib/sequel/adapters/shared/mysql.rb 928 def check_not_limited!(type) 929 super if type == :truncate || @opts[:offset] 930 end
The strftime format to use when literalizing time (Sequel::SQLTime
) values.
# File lib/sequel/adapters/shared/mysql.rb 933 def default_time_format 934 db.supports_timestamp_usecs? ? super : "'%H:%M:%S'" 935 end
The strftime format to use when literalizing timestamp (Time/DateTime) values.
# File lib/sequel/adapters/shared/mysql.rb 938 def default_timestamp_format 939 db.supports_timestamp_usecs? ? super : "'%Y-%m-%d %H:%M:%S'" 940 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 944 def delete_from_sql(sql) 945 if joined_dataset? 946 sql << ' ' 947 tables = @opts[:delete_from] || @opts[:from][0..0] 948 source_list_append(sql, tables) 949 sql << ' FROM ' 950 source_list_append(sql, @opts[:from]) 951 select_join_sql(sql) 952 else 953 super 954 end 955 end
MySQL
supports INSERT IGNORE INTO
# File lib/sequel/adapters/shared/mysql.rb 968 def insert_ignore_sql(sql) 969 sql << " IGNORE" if opts[:insert_ignore] 970 end
MySQL
supports INSERT … ON DUPLICATE KEY UPDATE
# File lib/sequel/adapters/shared/mysql.rb 978 def insert_on_duplicate_key_update_sql(sql) 979 if update_cols = opts[:on_duplicate_key_update] 980 update_vals = nil 981 982 if update_cols.empty? 983 update_cols = columns 984 elsif update_cols.last.is_a?(Hash) 985 update_vals = update_cols.last 986 update_cols = update_cols[0..-2] 987 end 988 989 sql << " ON DUPLICATE KEY UPDATE " 990 c = false 991 co = ', ' 992 values = '=VALUES(' 993 endp = ')' 994 update_cols.each do |col| 995 sql << co if c 996 quote_identifier_append(sql, col) 997 sql << values 998 quote_identifier_append(sql, col) 999 sql << endp 1000 c ||= true 1001 end 1002 if update_vals 1003 eq = '=' 1004 update_vals.map do |col,v| 1005 sql << co if c 1006 quote_identifier_append(sql, col) 1007 sql << eq 1008 literal_append(sql, v) 1009 c ||= true 1010 end 1011 end 1012 end 1013 end
MySQL
doesn’t use the standard DEFAULT VALUES for empty values.
# File lib/sequel/adapters/shared/mysql.rb 1016 def insert_values_sql(sql) 1017 values = opts[:values] 1018 if values.is_a?(Array) && values.empty? 1019 sql << " VALUES ()" 1020 else 1021 super 1022 end 1023 end
Transforms :straight to STRAIGHT_JOIN.
# File lib/sequel/adapters/shared/mysql.rb 1026 def join_type_sql(join_type) 1027 if join_type == :straight 1028 'STRAIGHT_JOIN' 1029 else 1030 super 1031 end 1032 end
MySQL
allows a LIMIT in DELETE and UPDATE statements.
# File lib/sequel/adapters/shared/mysql.rb 1035 def limit_sql(sql) 1036 if l = @opts[:limit] 1037 sql << " LIMIT " 1038 literal_append(sql, l) 1039 end 1040 end
MySQL
uses a preceding X for hex escaping strings
# File lib/sequel/adapters/shared/mysql.rb 1045 def literal_blob_append(sql, v) 1046 if v.empty? 1047 sql << "''" 1048 else 1049 sql << "0x" << v.unpack("H*").first 1050 end 1051 end
Use 0 for false on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1054 def literal_false 1055 '0' 1056 end
Raise error for infinitate and NaN values
# File lib/sequel/adapters/shared/mysql.rb 1059 def literal_float(v) 1060 if v.infinite? || v.nan? 1061 raise InvalidValue, "Infinite floats and NaN values are not valid on MySQL" 1062 else 1063 super 1064 end 1065 end
Use 1 for true on MySQL
# File lib/sequel/adapters/shared/mysql.rb 1073 def literal_true 1074 '1' 1075 end
MySQL
supports multiple rows in VALUES in INSERT.
# File lib/sequel/adapters/shared/mysql.rb 1078 def multi_insert_sql_strategy 1079 :values 1080 end
# File lib/sequel/adapters/shared/mysql.rb 1082 def non_sql_option?(key) 1083 super || key == :insert_ignore || key == :update_ignore || key == :on_duplicate_key_update 1084 end
MySQL
does not natively support NULLS FIRST/LAST.
# File lib/sequel/adapters/shared/mysql.rb 1087 def requires_emulating_nulls_first? 1088 true 1089 end
MySQL
specific SQL_CALC_FOUND_ROWS option
# File lib/sequel/adapters/shared/mysql.rb 1121 def select_calc_found_rows_sql(sql) 1122 sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] 1123 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 1099 def select_lock_sql(sql) 1100 lock = @opts[:lock] 1101 if lock == :share 1102 if !db.mariadb? && db.server_version >= 80000 1103 sql << ' FOR SHARE' 1104 else 1105 sql << ' LOCK IN SHARE MODE' 1106 end 1107 else 1108 super 1109 end 1110 1111 if lock 1112 if @opts[:skip_locked] 1113 sql << " SKIP LOCKED" 1114 elsif @opts[:nowait] 1115 sql << " NOWAIT" 1116 end 1117 end 1118 end
# File lib/sequel/adapters/shared/mysql.rb 1091 def select_only_offset_sql(sql) 1092 sql << " LIMIT " 1093 literal_append(sql, @opts[:offset]) 1094 sql << ",18446744073709551615" 1095 end
Use WITH RECURSIVE instead of WITH if any of the CTEs is recursive
# File lib/sequel/adapters/shared/mysql.rb 1126 def select_with_sql_base 1127 opts[:with].any?{|w| w[:recursive]} ? "WITH RECURSIVE " : super 1128 end
MySQL
supports UPDATE IGNORE
# File lib/sequel/adapters/shared/mysql.rb 973 def update_ignore_sql(sql) 974 sql << " IGNORE" if opts[:update_ignore] 975 end
MySQL
uses WITH ROLLUP syntax.
# File lib/sequel/adapters/shared/mysql.rb 1131 def uses_with_rollup? 1132 true 1133 end