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