lib/sequel/adapters/shared/mysql.rb in sequel-4.45.0 vs lib/sequel/adapters/shared/mysql.rb in sequel-4.46.0

- old
+ new

@@ -38,15 +38,19 @@ # currently supported by the native and JDBC adapters. module DatabaseMethods include UnmodifiedIdentifiers::DatabaseMethods AUTO_INCREMENT = 'AUTO_INCREMENT'.freeze - CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY} - COLUMN_DEFINITION_ORDER = [:collate, :null, :default, :unique, :primary_key, :auto_increment, :references] + Sequel::Deprecation.deprecate_constant(self, :AUTO_INCREMENT) PRIMARY = 'PRIMARY'.freeze + Sequel::Deprecation.deprecate_constant(self, :PRIMARY) MYSQL_TIMESTAMP_RE = /\ACURRENT_(?:DATE|TIMESTAMP)?\z/ + Sequel::Deprecation.deprecate_constant(self, :MYSQL_TIMESTAMP_RE) + CAST_TYPES = {String=>:CHAR, Integer=>:SIGNED, Time=>:DATETIME, DateTime=>:DATETIME, Numeric=>:DECIMAL, BigDecimal=>:DECIMAL, File=>:BINARY}#.freeze # SEQUEL5 + COLUMN_DEFINITION_ORDER = [:collate, :null, :default, :unique, :primary_key, :auto_increment, :references]#.freeze # SEQUEL5 + include Sequel::Database::SplitAlterTable # MySQL's cast rules are restrictive in that you can't just cast to any possible # database type. def cast_type_literal(type) @@ -119,11 +123,11 @@ sql += " FROM #{literal(schema)}" end metadata_dataset.with_sql(sql).each do |r| name = r[:Key_name] - next if name == PRIMARY + next if name == 'PRIMARY' name = m.call(name) remove_indexes << name if r[:Sub_part] && ! opts[:partial] i = indexes[name] ||= {:columns=>[], :unique=>r[:Non_unique] != 1} i[:columns] << m.call(r[:Column_name]) end @@ -288,11 +292,11 @@ end # Handle MySQL specific default format. def column_schema_normalize_default(default, type) if column_schema_default_string_type?(type) - return if [:date, :datetime, :time].include?(type) && MYSQL_TIMESTAMP_RE.match(default) + return if [:date, :datetime, :time].include?(type) && /\ACURRENT_(?:DATE|TIMESTAMP)?\z/.match(default) default = "'#{default.gsub("'", "''").gsub('\\', '\\\\')}'" end super(default, type) end @@ -325,11 +329,11 @@ sqls end # Use MySQL specific AUTO_INCREMENT text. def auto_increment_sql - AUTO_INCREMENT + 'AUTO_INCREMENT' end # MySQL needs to set transaction isolation before begining a transaction def begin_new_transaction(conn, opts) set_transaction_isolation(conn, opts) @@ -574,59 +578,102 @@ end # Dataset methods shared by datasets that use MySQL databases. module DatasetMethods BOOL_TRUE = '1'.freeze + Sequel::Deprecation.deprecate_constant(self, :BOOL_TRUE) BOOL_FALSE = '0'.freeze + Sequel::Deprecation.deprecate_constant(self, :BOOL_FALSE) COMMA_SEPARATOR = ', '.freeze + Sequel::Deprecation.deprecate_constant(self, :COMMA_SEPARATOR) FOR_SHARE = ' LOCK IN SHARE MODE'.freeze + Sequel::Deprecation.deprecate_constant(self, :FOR_SHARE) 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 + Sequel::Deprecation.deprecate_constant(self, :SQL_CALC_FOUND_ROWS) + APOS = "'".freeze + Sequel::Deprecation.deprecate_constant(self, :APOS) + APOS_RE = /'/.freeze + Sequel::Deprecation.deprecate_constant(self, :APOS_RE) + DOUBLE_APOS = "''".freeze + Sequel::Deprecation.deprecate_constant(self, :DOUBLE_APOS) + SPACE = ' '.freeze + Sequel::Deprecation.deprecate_constant(self, :SPACE) + PAREN_CLOSE = ')'.freeze + Sequel::Deprecation.deprecate_constant(self, :PAREN_CLOSE) + PAREN_OPEN = '('.freeze + Sequel::Deprecation.deprecate_constant(self, :PAREN_OPEN) + NOT_SPACE = 'NOT '.freeze + Sequel::Deprecation.deprecate_constant(self, :NOT_SPACE) + FROM = ' FROM '.freeze + Sequel::Deprecation.deprecate_constant(self, :FROM) + COMMA = ', '.freeze + Sequel::Deprecation.deprecate_constant(self, :COMMA) + LIMIT = " LIMIT ".freeze + Sequel::Deprecation.deprecate_constant(self, :LIMIT) + GROUP_BY = " GROUP BY ".freeze + Sequel::Deprecation.deprecate_constant(self, :GROUP_BY) + ESCAPE = " ESCAPE ".freeze + Sequel::Deprecation.deprecate_constant(self, :ESCAPE) + BACKSLASH = "\\".freeze + Sequel::Deprecation.deprecate_constant(self, :BACKSLASH) REGEXP = 'REGEXP'.freeze + Sequel::Deprecation.deprecate_constant(self, :REGEXP) LIKE = 'LIKE'.freeze + Sequel::Deprecation.deprecate_constant(self, :LIKE) BINARY = 'BINARY '.freeze + Sequel::Deprecation.deprecate_constant(self, :BINARY) CONCAT = "CONCAT".freeze + Sequel::Deprecation.deprecate_constant(self, :CONCAT) CAST_BITCOMP_OPEN = "CAST(~".freeze + Sequel::Deprecation.deprecate_constant(self, :CAST_BITCOMP_OPEN) CAST_BITCOMP_CLOSE = " AS SIGNED INTEGER)".freeze + Sequel::Deprecation.deprecate_constant(self, :CAST_BITCOMP_CLOSE) STRAIGHT_JOIN = 'STRAIGHT_JOIN'.freeze + Sequel::Deprecation.deprecate_constant(self, :STRAIGHT_JOIN) NATURAL_LEFT_JOIN = 'NATURAL LEFT JOIN'.freeze + Sequel::Deprecation.deprecate_constant(self, :NATURAL_LEFT_JOIN) BACKTICK = '`'.freeze + Sequel::Deprecation.deprecate_constant(self, :BACKTICK) BACKTICK_RE = /`/.freeze + Sequel::Deprecation.deprecate_constant(self, :BACKTICK_RE) DOUBLE_BACKTICK = '``'.freeze + Sequel::Deprecation.deprecate_constant(self, :DOUBLE_BACKTICK) EMPTY_COLUMNS = " ()".freeze + Sequel::Deprecation.deprecate_constant(self, :EMPTY_COLUMNS) EMPTY_VALUES = " VALUES ()".freeze + Sequel::Deprecation.deprecate_constant(self, :EMPTY_VALUES) IGNORE = " IGNORE".freeze + Sequel::Deprecation.deprecate_constant(self, :IGNORE) ON_DUPLICATE_KEY_UPDATE = " ON DUPLICATE KEY UPDATE ".freeze + Sequel::Deprecation.deprecate_constant(self, :ON_DUPLICATE_KEY_UPDATE) EQ_VALUES = '=VALUES('.freeze + Sequel::Deprecation.deprecate_constant(self, :EQ_VALUES) EQ = '='.freeze + Sequel::Deprecation.deprecate_constant(self, :EQ) 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 + Sequel::Deprecation.deprecate_constant(self, :WITH_ROLLUP) EXPLAIN = 'EXPLAIN '.freeze + Sequel::Deprecation.deprecate_constant(self, :EXPLAIN) EXPLAIN_EXTENDED = 'EXPLAIN EXTENDED '.freeze + Sequel::Deprecation.deprecate_constant(self, :EXPLAIN_EXTENDED) BACKSLASH_RE = /\\/.freeze + Sequel::Deprecation.deprecate_constant(self, :BACKSLASH_RE) QUAD_BACKSLASH = "\\\\\\\\".freeze + Sequel::Deprecation.deprecate_constant(self, :QUAD_BACKSLASH) BLOB_START = "0x".freeze + Sequel::Deprecation.deprecate_constant(self, :BLOB_START) EMPTY_BLOB = "''".freeze + Sequel::Deprecation.deprecate_constant(self, :EMPTY_BLOB) HSTAR = "H*".freeze + Sequel::Deprecation.deprecate_constant(self, :HSTAR) CURRENT_TIMESTAMP_56 = 'CURRENT_TIMESTAMP(6)'.freeze - - # Comes directly from MySQL's documentation, used for queries with limits without offsets + Sequel::Deprecation.deprecate_constant(self, :CURRENT_TIMESTAMP_56) ONLY_OFFSET = ",18446744073709551615".freeze + Sequel::Deprecation.deprecate_constant(self, :ONLY_OFFSET) + MATCH_AGAINST = ["MATCH ".freeze, " AGAINST (".freeze, ")".freeze].freeze + MATCH_AGAINST_BOOLEAN = ["MATCH ".freeze, " AGAINST (".freeze, " IN BOOLEAN MODE)".freeze].freeze NON_SQL_OPTIONS = (Dataset::NON_SQL_OPTIONS + [:insert_ignore, :update_ignore, :on_duplicate_key_update]).freeze Dataset.def_sql_method(self, :delete, %w'delete from where order limit') Dataset.def_sql_method(self, :insert, %w'insert ignore into columns values on_duplicate_key_update') Dataset.def_sql_method(self, :select, %w'select distinct calc_found_rows columns from join where group having compounds order limit lock') @@ -638,41 +685,41 @@ # MySQL specific syntax for LIKE/REGEXP searches, as well as # string concatenation. def complex_expression_sql_append(sql, op, args) case op when :IN, :"NOT IN" - ds = args.at(1) + ds = args[1] if ds.is_a?(Sequel::Dataset) && ds.opts[:limit] - super(sql, op, [args.at(0), ds.from_self]) + super(sql, op, [args[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 << '(' + literal_append(sql, args[0]) + sql << ' ' 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)) + sql << ([:~, :'!~', :'~*', :'!~*'].include?(op) ? 'REGEXP' : 'LIKE') + sql << ' ' + sql << 'BINARY ' if [:~, :'!~', :LIKE, :'NOT LIKE'].include?(op) + literal_append(sql, args[1]) if [:LIKE, :'NOT LIKE', :ILIKE, :'NOT ILIKE'].include?(op) - sql << ESCAPE - literal_append(sql, BACKSLASH) + sql << " ESCAPE " + literal_append(sql, "\\") end - sql << PAREN_CLOSE + sql << ')' when :'||' if args.length > 1 - sql << CONCAT + sql << "CONCAT" array_sql_append(sql, args) else - literal_append(sql, args.at(0)) + literal_append(sql, args[0]) end when :'B~' - sql << CAST_BITCOMP_OPEN - literal_append(sql, args.at(0)) - sql << CAST_BITCOMP_CLOSE + sql << "CAST(~" + literal_append(sql, args[0]) + sql << " AS SIGNED INTEGER)" else super end end @@ -680,11 +727,11 @@ # even if the database itself supports fractional seconds. If # MySQL 5.6.4+ is being used, use a value that will return # fractional seconds. def constant_sql_append(sql, constant) if constant == :CURRENT_TIMESTAMP && supports_timestamp_usecs? - sql << CURRENT_TIMESTAMP_56 + sql << 'CURRENT_TIMESTAMP(6)' else super end end @@ -717,11 +764,11 @@ # :extended :: Use EXPLAIN EXPTENDED instead of EXPLAIN if true. 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 + 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 # Return a cloned dataset which will use LOCK IN SHARE MODE to lock returned rows. @@ -751,13 +798,13 @@ # Transforms :natural_inner to NATURAL LEFT JOIN and straight to # STRAIGHT_JOIN. def join_type_sql(join_type) case join_type when :straight - STRAIGHT_JOIN + 'STRAIGHT_JOIN' when :natural_inner - NATURAL_LEFT_JOIN + 'NATURAL LEFT JOIN' else super end end @@ -806,11 +853,11 @@ clone(:on_duplicate_key_update => args) end # MySQL uses the nonstandard ` (backtick) for quoting identifiers. def quoted_identifier_append(sql, c) - sql << BACKTICK << c.to_s.gsub(BACKTICK_RE, DOUBLE_BACKTICK) << BACKTICK + sql << '`' << c.to_s.gsub('`', '``') << '`' end # MySQL does not support derived column lists def supports_derived_column_lists? false @@ -879,14 +926,14 @@ # Consider the first table in the joined dataset is the table to delete # from, but include the others for the purposes of selecting rows. def delete_from_sql(sql) if joined_dataset? - sql << SPACE + sql << ' ' tables = @opts[:delete_from] || @opts[:from][0..0] source_list_append(sql, tables) - sql << FROM + sql << ' FROM ' source_list_append(sql, @opts[:from]) select_join_sql(sql) else super end @@ -894,24 +941,24 @@ # MySQL doesn't use the SQL standard DEFAULT VALUES. def insert_columns_sql(sql) values = opts[:values] if values.is_a?(Array) && values.empty? - sql << EMPTY_COLUMNS + sql << " ()" else super end end # MySQL supports INSERT IGNORE INTO def insert_ignore_sql(sql) - sql << IGNORE if opts[:insert_ignore] + sql << " IGNORE" if opts[:insert_ignore] end # MySQL supports UPDATE IGNORE def update_ignore_sql(sql) - sql << IGNORE if opts[:update_ignore] + sql << " IGNORE" if opts[:update_ignore] end # MySQL supports INSERT ... ON DUPLICATE KEY UPDATE def insert_on_duplicate_key_update_sql(sql) if update_cols = opts[:on_duplicate_key_update] @@ -922,25 +969,25 @@ elsif update_cols.last.is_a?(Hash) update_vals = update_cols.last update_cols = update_cols[0..-2] end - sql << ON_DUPLICATE_KEY_UPDATE + sql << " ON DUPLICATE KEY UPDATE " c = false - co = COMMA - values = EQ_VALUES - endp = PAREN_CLOSE + co = ', ' + values = '=VALUES(' + endp = ')' update_cols.each do |col| sql << co if c quote_identifier_append(sql, col) sql << values quote_identifier_append(sql, col) sql << endp c ||= true end if update_vals - eq = EQ + eq = '=' update_vals.map do |col,v| sql << co if c quote_identifier_append(sql, col) sql << eq literal_append(sql, v) @@ -952,38 +999,38 @@ # MySQL doesn't use the standard DEFAULT VALUES for empty values. def insert_values_sql(sql) values = opts[:values] if values.is_a?(Array) && values.empty? - sql << EMPTY_VALUES + sql << " VALUES ()" else super end end # MySQL allows a LIMIT in DELETE and UPDATE statements. def limit_sql(sql) if l = @opts[:limit] - sql << LIMIT + sql << " LIMIT " literal_append(sql, l) end end alias delete_limit_sql limit_sql alias update_limit_sql limit_sql # MySQL uses a preceding X for hex escaping strings def literal_blob_append(sql, v) if v.empty? - sql << EMPTY_BLOB + sql << "''" else - sql << BLOB_START << v.unpack(HSTAR).first + sql << "0x" << v.unpack("H*").first end end # Use 0 for false on MySQL def literal_false - BOOL_FALSE + '0' end # Raise error for infinitate and NaN values def literal_float(v) if v.infinite? || v.nan? @@ -993,16 +1040,16 @@ end end # SQL fragment for String. Doubles \ and ' by default. def literal_string_append(sql, v) - sql << APOS << v.gsub(BACKSLASH_RE, QUAD_BACKSLASH).gsub(APOS_RE, DOUBLE_APOS) << APOS + sql << "'" << v.gsub("\\", "\\\\\\\\").gsub("'", "''") << "'" end # Use 1 for true on MySQL def literal_true - BOOL_TRUE + '1' end # MySQL supports multiple rows in INSERT. def multi_insert_sql_strategy :values @@ -1012,22 +1059,22 @@ def non_sql_options NON_SQL_OPTIONS end def select_only_offset_sql(sql) - sql << LIMIT + sql << " LIMIT " literal_append(sql, @opts[:offset]) - sql << ONLY_OFFSET + sql << ",18446744073709551615" end # Support FOR SHARE locking when using the :share lock style. def select_lock_sql(sql) - @opts[:lock] == :share ? (sql << FOR_SHARE) : super + @opts[:lock] == :share ? (sql << ' LOCK IN SHARE MODE') : super end # MySQL specific SQL_CALC_FOUND_ROWS option def select_calc_found_rows_sql(sql) - sql << SQL_CALC_FOUND_ROWS if opts[:calc_found_rows] + sql << ' SQL_CALC_FOUND_ROWS' if opts[:calc_found_rows] end # MySQL uses WITH ROLLUP syntax. def uses_with_rollup? true