vendor/rails/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb

   1 require 'active_record/connection_adapters/abstract_adapter'
   2 require 'set'
   3
   4 module MysqlCompat #:nodoc:
   5   # add all_hashes method to standard mysql-c bindings or pure ruby version
   6   def self.define_all_hashes_method!
   7     raise 'Mysql not loaded' unless defined?(::Mysql)
   8
   9     target = defined?(Mysql::Result) ? Mysql::Result : MysqlRes
  10     return if target.instance_methods.include?('all_hashes')
  11
  12     # Ruby driver has a version string and returns null values in each_hash
  13     # C driver >= 2.7 returns null values in each_hash
  14     if Mysql.const_defined?(:VERSION) && (Mysql::VERSION.is_a?(String) || Mysql::VERSION >= 20700)
  15       target.class_eval <<-'end_eval'
  16       def all_hashes
  17         rows = []
  18         each_hash { |row| rows << row }
  19         rows
  20       end
  21       end_eval
  22
  23     # adapters before 2.7 don't have a version constant
  24     # and don't return null values in each_hash
  25     else
  26       target.class_eval <<-'end_eval'
  27       def all_hashes
  28         rows = []
  29         all_fields = fetch_fields.inject({}) { |fields, f| fields[f.name] = nil; fields }
  30         each_hash { |row| rows << all_fields.dup.update(row) }
  31         rows
  32       end
  33       end_eval
  34     end
  35
  36     unless target.instance_methods.include?('all_hashes') ||
  37            target.instance_methods.include?(:all_hashes)
  38       raise "Failed to defined #{target.name}#all_hashes method. Mysql::VERSION = #{Mysql::VERSION.inspect}"
  39     end
  40   end
  41 end
  42
  43 module ActiveRecord
  44   class Base
  45     # Establishes a connection to the database that's used by all Active Record objects.
  46     def self.mysql_connection(config) # :nodoc:
  47       config = config.symbolize_keys
  48       host     = config[:host]
  49       port     = config[:port]
  50       socket   = config[:socket]
  51       username = config[:username] ? config[:username].to_s : 'root'
  52       password = config[:password].to_s
  53
  54       if config.has_key?(:database)
  55         database = config[:database]
  56       else
  57         raise ArgumentError, "No database specified. Missing argument: database."
  58       end
  59
  60       # Require the MySQL driver and define Mysql::Result.all_hashes
  61       unless defined? Mysql
  62         begin
  63           require_library_or_gem('mysql')
  64         rescue LoadError
  65           $stderr.puts '!!! The bundled mysql.rb driver has been removed from Rails 2.2. Please install the mysql gem and try again: gem install mysql.'
  66           raise
  67         end
  68       end
  69       MysqlCompat.define_all_hashes_method!
  70
  71       mysql = Mysql.init
  72       mysql.ssl_set(config[:sslkey], config[:sslcert], config[:sslca], config[:sslcapath], config[:sslcipher]) if config[:sslca] || config[:sslkey]
  73
  74       ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket], config)
  75     end
  76   end
  77
  78   module ConnectionAdapters
  79     class MysqlColumn < Column #:nodoc:
  80       def extract_default(default)
  81         if type == :binary || type == :text
  82           if default.blank?
  83             return null ? nil : ''
  84           else
  85             raise ArgumentError, "#{type} columns cannot have a default value: #{default.inspect}"
  86           end
  87         elsif missing_default_forged_as_empty_string?(default)
  88           nil
  89         else
  90           super
  91         end
  92       end
  93
  94       def has_default?
  95         return false if type == :binary || type == :text #mysql forbids defaults on blob and text columns
  96         super
  97       end
  98
  99       private
 100         def simplified_type(field_type)
 101           return :boolean if MysqlAdapter.emulate_booleans && field_type.downcase.index("tinyint(1)")
 102           return :string  if field_type =~ /enum/i
 103           super
 104         end
 105
 106         def extract_limit(sql_type)
 107           case sql_type
 108           when /blob|text/i
 109             case sql_type
 110             when /tiny/i
 111               255
 112             when /medium/i
 113               16777215
 114             when /long/i
 115               2147483647 # mysql only allows 2^31-1, not 2^32-1, somewhat inconsistently with the tiny/medium/normal cases
 116             else
 117               super # we could return 65535 here, but we leave it undecorated by default
 118             end
 119           when /^bigint/i;    8
 120           when /^int/i;       4
 121           when /^mediumint/i; 3
 122           when /^smallint/i;  2
 123           when /^tinyint/i;   1
 124           else
 125             super
 126           end
 127         end
 128
 129         # MySQL misreports NOT NULL column default when none is given.
 130         # We can't detect this for columns which may have a legitimate ''
 131         # default (string) but we can for others (integer, datetime, boolean,
 132         # and the rest).
 133         #
 134         # Test whether the column has default '', is not null, and is not
 135         # a type allowing default ''.
 136         def missing_default_forged_as_empty_string?(default)
 137           type != :string && !null && default == ''
 138         end
 139     end
 140
 141     # The MySQL adapter will work with both Ruby/MySQL, which is a Ruby-based MySQL adapter that comes bundled with Active Record, and with
 142     # the faster C-based MySQL/Ruby adapter (available both as a gem and from http://www.tmtm.org/en/mysql/ruby/).
 143     #
 144     # Options:
 145     #
 146     # * <tt>:host</tt> - Defaults to "localhost".
 147     # * <tt>:port</tt> - Defaults to 3306.
 148     # * <tt>:socket</tt> - Defaults to "/tmp/mysql.sock".
 149     # * <tt>:username</tt> - Defaults to "root"
 150     # * <tt>:password</tt> - Defaults to nothing.
 151     # * <tt>:database</tt> - The name of the database. No default, must be provided.
 152     # * <tt>:encoding</tt> - (Optional) Sets the client encoding by executing "SET NAMES <encoding>" after connection.
 153     # * <tt>:sslca</tt> - Necessary to use MySQL with an SSL connection.
 154     # * <tt>:sslkey</tt> - Necessary to use MySQL with an SSL connection.
 155     # * <tt>:sslcert</tt> - Necessary to use MySQL with an SSL connection.
 156     # * <tt>:sslcapath</tt> - Necessary to use MySQL with an SSL connection.
 157     # * <tt>:sslcipher</tt> - Necessary to use MySQL with an SSL connection.
 158     #
 159     # By default, the MysqlAdapter will consider all columns of type <tt>tinyint(1)</tt>
 160     # as boolean. If you wish to disable this emulation (which was the default
 161     # behavior in versions 0.13.1 and earlier) you can add the following line
 162     # to your environment.rb file:
 163     #
 164     #   ActiveRecord::ConnectionAdapters::MysqlAdapter.emulate_booleans = false
 165     class MysqlAdapter < AbstractAdapter
 166       cattr_accessor :emulate_booleans
 167       self.emulate_booleans = true
 168
 169       ADAPTER_NAME = 'MySQL'.freeze
 170
 171       LOST_CONNECTION_ERROR_MESSAGES = [
 172         "Server shutdown in progress",
 173         "Broken pipe",
 174         "Lost connection to MySQL server during query",
 175         "MySQL server has gone away" ]
 176
 177       QUOTED_TRUE, QUOTED_FALSE = '1'.freeze, '0'.freeze
 178
 179       NATIVE_DATABASE_TYPES = {
 180         :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY".freeze,
 181         :string      => { :name => "varchar", :limit => 255 },
 182         :text        => { :name => "text" },
 183         :integer     => { :name => "int", :limit => 4 },
 184         :float       => { :name => "float" },
 185         :decimal     => { :name => "decimal" },
 186         :datetime    => { :name => "datetime" },
 187         :timestamp   => { :name => "datetime" },
 188         :time        => { :name => "time" },
 189         :date        => { :name => "date" },
 190         :binary      => { :name => "blob" },
 191         :boolean     => { :name => "tinyint", :limit => 1 }
 192       }
 193
 194       def initialize(connection, logger, connection_options, config)
 195         super(connection, logger)
 196         @connection_options, @config = connection_options, config
 197         @quoted_column_names, @quoted_table_names = {}, {}
 198         connect
 199       end
 200
 201       def adapter_name #:nodoc:
 202         ADAPTER_NAME
 203       end
 204
 205       def supports_migrations? #:nodoc:
 206         true
 207       end
 208
 209       def native_database_types #:nodoc:
 210         NATIVE_DATABASE_TYPES
 211       end
 212
 213
 214       # QUOTING ==================================================
 215
 216       def quote(value, column = nil)
 217         if value.kind_of?(String) && column && column.type == :binary && column.class.respond_to?(:string_to_binary)
 218           s = column.class.string_to_binary(value).unpack("H*")[0]
 219           "x'#{s}'"
 220         elsif value.kind_of?(BigDecimal)
 221           value.to_s("F")
 222         else
 223           super
 224         end
 225       end
 226
 227       def quote_column_name(name) #:nodoc:
 228         @quoted_column_names[name] ||= "`#{name}`"
 229       end
 230
 231       def quote_table_name(name) #:nodoc:
 232         @quoted_table_names[name] ||= quote_column_name(name).gsub('.', '`.`')
 233       end
 234
 235       def quote_string(string) #:nodoc:
 236         @connection.quote(string)
 237       end
 238
 239       def quoted_true
 240         QUOTED_TRUE
 241       end
 242
 243       def quoted_false
 244         QUOTED_FALSE
 245       end
 246
 247       # REFERENTIAL INTEGRITY ====================================
 248
 249       def disable_referential_integrity(&block) #:nodoc:
 250         old = select_value("SELECT @@FOREIGN_KEY_CHECKS")
 251
 252         begin
 253           update("SET FOREIGN_KEY_CHECKS = 0")
 254           yield
 255         ensure
 256           update("SET FOREIGN_KEY_CHECKS = #{old}")
 257         end
 258       end
 259
 260       # CONNECTION MANAGEMENT ====================================
 261
 262       def active?
 263         if @connection.respond_to?(:stat)
 264           @connection.stat
 265         else
 266           @connection.query 'select 1'
 267         end
 268
 269         # mysql-ruby doesn't raise an exception when stat fails.
 270         if @connection.respond_to?(:errno)
 271           @connection.errno.zero?
 272         else
 273           true
 274         end
 275       rescue Mysql::Error
 276         false
 277       end
 278
 279       def reconnect!
 280         disconnect!
 281         connect
 282       end
 283
 284       def disconnect!
 285         @connection.close rescue nil
 286       end
 287
 288       def reset!
 289         if @connection.respond_to?(:change_user)
 290           # See http://bugs.mysql.com/bug.php?id=33540 -- the workaround way to
 291           # reset the connection is to change the user to the same user.
 292           @connection.change_user(@config[:username], @config[:password], @config[:database])
 293           configure_connection
 294         end
 295       end
 296
 297       # DATABASE STATEMENTS ======================================
 298
 299       def select_rows(sql, name = nil)
 300         @connection.query_with_result = true
 301         result = execute(sql, name)
 302         rows = []
 303         result.each { |row| rows << row }
 304         result.free
 305         rows
 306       end
 307
 308       def execute(sql, name = nil) #:nodoc:
 309         log(sql, name) { @connection.query(sql) }
 310       rescue ActiveRecord::StatementInvalid => exception
 311         if exception.message.split(":").first =~ /Packets out of order/
 312           raise ActiveRecord::StatementInvalid, "'Packets out of order' error was received from the database. Please update your mysql bindings (gem install mysql) and read http://dev.mysql.com/doc/mysql/en/password-hashing.html for more information.  If you're on Windows, use the Instant Rails installer to get the updated mysql bindings."
 313         else
 314           raise
 315         end
 316       end
 317
 318       def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc:
 319         super sql, name
 320         id_value || @connection.insert_id
 321       end
 322
 323       def update_sql(sql, name = nil) #:nodoc:
 324         super
 325         @connection.affected_rows
 326       end
 327
 328       def begin_db_transaction #:nodoc:
 329         execute "BEGIN"
 330       rescue Exception
 331         # Transactions aren't supported
 332       end
 333
 334       def commit_db_transaction #:nodoc:
 335         execute "COMMIT"
 336       rescue Exception
 337         # Transactions aren't supported
 338       end
 339
 340       def rollback_db_transaction #:nodoc:
 341         execute "ROLLBACK"
 342       rescue Exception
 343         # Transactions aren't supported
 344       end
 345
 346
 347       def add_limit_offset!(sql, options) #:nodoc:
 348         if limit = options[:limit]
 349           limit = sanitize_limit(limit)
 350           unless offset = options[:offset]
 351             sql << " LIMIT #{limit}"
 352           else
 353             sql << " LIMIT #{offset.to_i}, #{limit}"
 354           end
 355         end
 356       end
 357
 358
 359       # SCHEMA STATEMENTS ========================================
 360
 361       def structure_dump #:nodoc:
 362         if supports_views?
 363           sql = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"
 364         else
 365           sql = "SHOW TABLES"
 366         end
 367
 368         select_all(sql).inject("") do |structure, table|
 369           table.delete('Table_type')
 370           structure += select_one("SHOW CREATE TABLE #{quote_table_name(table.to_a.first.last)}")["Create Table"] + ";\n\n"
 371         end
 372       end
 373
 374       def recreate_database(name, options = {}) #:nodoc:
 375         drop_database(name)
 376         create_database(name, options)
 377       end
 378
 379       # Create a new MySQL database with optional <tt>:charset</tt> and <tt>:collation</tt>.
 380       # Charset defaults to utf8.
 381       #
 382       # Example:
 383       #   create_database 'charset_test', :charset => 'latin1', :collation => 'latin1_bin'
 384       #   create_database 'matt_development'
 385       #   create_database 'matt_development', :charset => :big5
 386       def create_database(name, options = {})
 387         if options[:collation]
 388           execute "CREATE DATABASE `#{name}` DEFAULT CHARACTER SET `#{options[:charset] || 'utf8'}` COLLATE `#{options[:collation]}`"
 389         else
 390           execute "CREATE DATABASE `#{name}` DEFAULT CHARACTER SET `#{options[:charset] || 'utf8'}`"
 391         end
 392       end
 393
 394       def drop_database(name) #:nodoc:
 395         execute "DROP DATABASE IF EXISTS `#{name}`"
 396       end
 397
 398       def current_database
 399         select_value 'SELECT DATABASE() as db'
 400       end
 401
 402       # Returns the database character set.
 403       def charset
 404         show_variable 'character_set_database'
 405       end
 406
 407       # Returns the database collation strategy.
 408       def collation
 409         show_variable 'collation_database'
 410       end
 411
 412       def tables(name = nil) #:nodoc:
 413         tables = []
 414         execute("SHOW TABLES", name).each { |field| tables << field[0] }
 415         tables
 416       end
 417
 418       def drop_table(table_name, options = {})
 419         super(table_name, options)
 420       end
 421
 422       def indexes(table_name, name = nil)#:nodoc:
 423         indexes = []
 424         current_index = nil
 425         execute("SHOW KEYS FROM #{quote_table_name(table_name)}", name).each do |row|
 426           if current_index != row[2]
 427             next if row[2] == "PRIMARY" # skip the primary key
 428             current_index = row[2]
 429             indexes << IndexDefinition.new(row[0], row[2], row[1] == "0", [])
 430           end
 431
 432           indexes.last.columns << row[4]
 433         end
 434         indexes
 435       end
 436
 437       def columns(table_name, name = nil)#:nodoc:
 438         sql = "SHOW FIELDS FROM #{quote_table_name(table_name)}"
 439         columns = []
 440         execute(sql, name).each { |field| columns << MysqlColumn.new(field[0], field[4], field[1], field[2] == "YES") }
 441         columns
 442       end
 443
 444       def create_table(table_name, options = {}) #:nodoc:
 445         super(table_name, options.reverse_merge(:options => "ENGINE=InnoDB"))
 446       end
 447
 448       def rename_table(table_name, new_name)
 449         execute "RENAME TABLE #{quote_table_name(table_name)} TO #{quote_table_name(new_name)}"
 450       end
 451
 452       def change_column_default(table_name, column_name, default) #:nodoc:
 453         column = column_for(table_name, column_name)
 454         change_column table_name, column_name, column.sql_type, :default => default
 455       end
 456
 457       def change_column_null(table_name, column_name, null, default = nil)
 458         column = column_for(table_name, column_name)
 459
 460         unless null || default.nil?
 461           execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
 462         end
 463
 464         change_column table_name, column_name, column.sql_type, :null => null
 465       end
 466
 467       def change_column(table_name, column_name, type, options = {}) #:nodoc:
 468         column = column_for(table_name, column_name)
 469
 470         unless options_include_default?(options)
 471           options[:default] = column.default
 472         end
 473
 474         unless options.has_key?(:null)
 475           options[:null] = column.null
 476         end
 477
 478         change_column_sql = "ALTER TABLE #{quote_table_name(table_name)} CHANGE #{quote_column_name(column_name)} #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
 479         add_column_options!(change_column_sql, options)
 480         execute(change_column_sql)
 481       end
 482
 483       def rename_column(table_name, column_name, new_column_name) #:nodoc:
 484         options = {}
 485         if column = columns(table_name).find { |c| c.name == column_name.to_s }
 486           options[:default] = column.default
 487           options[:null] = column.null
 488         else
 489           raise ActiveRecordError, "No such column: #{table_name}.#{column_name}"
 490         end
 491         current_type = select_one("SHOW COLUMNS FROM #{quote_table_name(table_name)} LIKE '#{column_name}'")["Type"]
 492         rename_column_sql = "ALTER TABLE #{quote_table_name(table_name)} CHANGE #{quote_column_name(column_name)} #{quote_column_name(new_column_name)} #{current_type}"
 493         add_column_options!(rename_column_sql, options)
 494         execute(rename_column_sql)
 495       end
 496
 497       # Maps logical Rails types to MySQL-specific data types.
 498       def type_to_sql(type, limit = nil, precision = nil, scale = nil)
 499         return super unless type.to_s == 'integer'
 500
 501         case limit
 502         when 1; 'tinyint'
 503         when 2; 'smallint'
 504         when 3; 'mediumint'
 505         when nil, 4, 11; 'int(11)'  # compatibility with MySQL default
 506         when 5..8; 'bigint'
 507         else raise(ActiveRecordError, "No integer type has byte size #{limit}")
 508         end
 509       end
 510
 511
 512       # SHOW VARIABLES LIKE 'name'
 513       def show_variable(name)
 514         variables = select_all("SHOW VARIABLES LIKE '#{name}'")
 515         variables.first['Value'] unless variables.empty?
 516       end
 517
 518       # Returns a table's primary key and belonging sequence.
 519       def pk_and_sequence_for(table) #:nodoc:
 520         keys = []
 521         execute("describe #{quote_table_name(table)}").each_hash do |h|
 522           keys << h["Field"]if h["Key"] == "PRI"
 523         end
 524         keys.length == 1 ? [keys.first, nil] : nil
 525       end
 526
 527       def case_sensitive_equality_operator
 528         "= BINARY"
 529       end
 530
 531       def limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key)
 532         where_sql
 533       end
 534
 535       private
 536         def connect
 537           @connection.reconnect = true if @connection.respond_to?(:reconnect=)
 538
 539           encoding = @config[:encoding]
 540           if encoding
 541             @connection.options(Mysql::SET_CHARSET_NAME, encoding) rescue nil
 542           end
 543
 544           if @config[:sslca] || @config[:sslkey]
 545             @connection.ssl_set(@config[:sslkey], @config[:sslcert], @config[:sslca], @config[:sslcapath], @config[:sslcipher])
 546           end
 547
 548           @connection.real_connect(*@connection_options)
 549           configure_connection
 550         end
 551
 552         def configure_connection
 553           encoding = @config[:encoding]
 554           execute("SET NAMES '#{encoding}'") if encoding
 555
 556           # By default, MySQL 'where id is null' selects the last inserted id.
 557           # Turn this off. http://dev.rubyonrails.org/ticket/6778
 558           execute("SET SQL_AUTO_IS_NULL=0")
 559         end
 560
 561         def select(sql, name = nil)
 562           @connection.query_with_result = true
 563           result = execute(sql, name)
 564           rows = result.all_hashes
 565           result.free
 566           rows
 567         end
 568
 569         def supports_views?
 570           version[0] >= 5
 571         end
 572
 573         def version
 574           @version ||= @connection.server_info.scan(/^(\d+)\.(\d+)\.(\d+)/).flatten.map { |v| v.to_i }
 575         end
 576
 577         def column_for(table_name, column_name)
 578           unless column = columns(table_name).find { |c| c.name == column_name.to_s }
 579             raise "No such column: #{table_name}.#{column_name}"
 580           end
 581           column
 582         end
 583     end
 584   end
 585 end