require 'active_support/core_ext/object/blank'
module ActiveRecord
module ConnectionAdapters
class AbstractMysqlAdapter < AbstractAdapter
class Column < ConnectionAdapters::Column # :nodoc:
attr_reader :collation
def initialize(name, default, sql_type = nil, null = true, collation = nil)
super(name, default, sql_type, null)
@collation = collation
end
def extract_default(default)
if sql_type =~ /blob/i || type == :text
if default.blank?
return null ? nil : ''
else
raise ArgumentError, "#{type} columns cannot have a default value: #{default.inspect}"
end
elsif missing_default_forged_as_empty_string?(default)
nil
else
super
end
end
def has_default?
return false if sql_type =~ /blob/i || type == :text #mysql forbids defaults on blob and text columns
super
end
# Must return the relevant concrete adapter
def adapter
raise NotImplementedError
end
def case_sensitive?
collation && !collation.match(/_ci$/)
end
private
def simplified_type(field_type)
return :boolean if adapter.emulate_booleans && field_type.downcase.index("tinyint(1)")
case field_type
when /enum/i, /set/i then :string
when /year/i then :integer
when /bit/i then :binary
else
super
end
end
def extract_limit(sql_type)
case sql_type
when /blob|text/i
case sql_type
when /tiny/i
255
when /medium/i
16777215
when /long/i
2147483647 # mysql only allows 2^31-1, not 2^32-1, somewhat inconsistently with the tiny/medium/normal cases
else
super # we could return 65535 here, but we leave it undecorated by default
end
when /^bigint/i; 8
when /^int/i; 4
when /^mediumint/i; 3
when /^smallint/i; 2
when /^tinyint/i; 1
else
super
end
end
# MySQL misreports NOT NULL column default when none is given.
# We can't detect this for columns which may have a legitimate ''
# default (string) but we can for others (integer, datetime, boolean,
# and the rest).
#
# Test whether the column has default '', is not null, and is not
# a type allowing default ''.
def missing_default_forged_as_empty_string?(default)
type != :string && !null && default == ''
end
end
##
# :singleton-method:
# By default, the MysqlAdapter will consider all columns of type tinyint(1)
# as boolean. If you wish to disable this emulation (which was the default
# behavior in versions 0.13.1 and earlier) you can add the following line
# to your application.rb file:
#
# ActiveRecord::ConnectionAdapters::Mysql[2]Adapter.emulate_booleans = false
class_attribute :emulate_booleans
self.emulate_booleans = true
LOST_CONNECTION_ERROR_MESSAGES = [
"Server shutdown in progress",
"Broken pipe",
"Lost connection to MySQL server during query",
"MySQL server has gone away" ]
QUOTED_TRUE, QUOTED_FALSE = '1', '0'
NATIVE_DATABASE_TYPES = {
:primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
:string => { :name => "varchar", :limit => 255 },
:text => { :name => "text" },
:integer => { :name => "int", :limit => 4 },
:float => { :name => "float" },
:decimal => { :name => "decimal" },
:datetime => { :name => "datetime" },
:timestamp => { :name => "datetime" },
:time => { :name => "time" },
:date => { :name => "date" },
:binary => { :name => "blob" },
:boolean => { :name => "tinyint", :limit => 1 }
}
# FIXME: Make the first parameter more similar for the two adapters
def initialize(connection, logger, connection_options, config)
super(connection, logger)
@connection_options, @config = connection_options, config
@quoted_column_names, @quoted_table_names = {}, {}
end
def self.visitor_for(pool) # :nodoc:
Arel::Visitors::MySQL.new(pool)
end
def adapter_name #:nodoc:
self.class::ADAPTER_NAME
end
# Returns true, since this connection adapter supports migrations.
def supports_migrations?
true
end
def supports_primary_key?
true
end
# Returns true, since this connection adapter supports savepoints.
def supports_savepoints?
true
end
def supports_bulk_alter? #:nodoc:
true
end
# Technically MySQL allows to create indexes with the sort order syntax
# but at the moment (5.5) it doesn't yet implement them
def supports_index_sort_order?
true
end
def native_database_types
NATIVE_DATABASE_TYPES
end
# HELPER METHODS ===========================================
# The two drivers have slightly different ways of yielding hashes of results, so
# this method must be implemented to provide a uniform interface.
def each_hash(result) # :nodoc:
raise NotImplementedError
end
# Overridden by the adapters to instantiate their specific Column type.
def new_column(field, default, type, null, collation) # :nodoc:
Column.new(field, default, type, null, collation)
end
# Must return the Mysql error number from the exception, if the exception has an
# error number.
def error_number(exception) # :nodoc:
raise NotImplementedError
end
# QUOTING ==================================================
def quote(value, column = nil)
if value.kind_of?(String) && column && column.type == :binary && column.class.respond_to?(:string_to_binary)
s = column.class.string_to_binary(value).unpack("H*")[0]
"x'#{s}'"
elsif value.kind_of?(BigDecimal)
value.to_s("F")
else
super
end
end
def quote_column_name(name) #:nodoc:
@quoted_column_names[name] ||= "`#{name.to_s.gsub('`', '``')}`"
end
def quote_table_name(name) #:nodoc:
@quoted_table_names[name] ||= quote_column_name(name).gsub('.', '`.`')
end
def quoted_true
QUOTED_TRUE
end
def quoted_false
QUOTED_FALSE
end
# REFERENTIAL INTEGRITY ====================================
def disable_referential_integrity(&block) #:nodoc:
old = select_value("SELECT @@FOREIGN_KEY_CHECKS")
begin
update("SET FOREIGN_KEY_CHECKS = 0")
yield
ensure
update("SET FOREIGN_KEY_CHECKS = #{old}")
end
end
# DATABASE STATEMENTS ======================================
# Executes the SQL statement in the context of this connection.
def execute(sql, name = nil)
if name == :skip_logging
@connection.query(sql)
else
log(sql, name) { @connection.query(sql) }
end
rescue ActiveRecord::StatementInvalid => exception
if exception.message.split(":").first =~ /Packets out of order/
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."
else
raise
end
end
# MysqlAdapter has to free a result after using it, so we use this method to write
# stuff in a abstract way without concerning ourselves about whether it needs to be
# explicitly freed or not.
def execute_and_free(sql, name = nil) #:nodoc:
yield execute(sql, name)
end
def update_sql(sql, name = nil) #:nodoc:
super
@connection.affected_rows
end
def begin_db_transaction
execute "BEGIN"
rescue Exception
# Transactions aren't supported
end
def commit_db_transaction #:nodoc:
execute "COMMIT"
rescue Exception
# Transactions aren't supported
end
def rollback_db_transaction #:nodoc:
execute "ROLLBACK"
rescue Exception
# Transactions aren't supported
end
def create_savepoint
execute("SAVEPOINT #{current_savepoint_name}")
end
def rollback_to_savepoint
execute("ROLLBACK TO SAVEPOINT #{current_savepoint_name}")
end
def release_savepoint
execute("RELEASE SAVEPOINT #{current_savepoint_name}")
end
# In the simple case, MySQL allows us to place JOINs directly into the UPDATE
# query. However, this does not allow for LIMIT, OFFSET and ORDER. To support
# these, we must use a subquery. However, MySQL is too stupid to create a
# temporary table for this automatically, so we have to give it some prompting
# in the form of a subsubquery. Ugh!
def join_to_update(update, select) #:nodoc:
if select.limit || select.offset || select.orders.any?
subsubselect = select.clone
subsubselect.projections = [update.key]
subselect = Arel::SelectManager.new(select.engine)
subselect.project Arel.sql(update.key.name)
subselect.from subsubselect.as('__active_record_temp')
update.where update.key.in(subselect)
else
update.table select.source
update.wheres = select.constraints
end
end
# SCHEMA STATEMENTS ========================================
def structure_dump #:nodoc:
if supports_views?
sql = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"
else
sql = "SHOW TABLES"
end
select_all(sql).map do |table|
table.delete('Table_type')
sql = "SHOW CREATE TABLE #{quote_table_name(table.to_a.first.last)}"
exec_without_stmt(sql).first['Create Table'] + ";\n\n"
end.join("")
end
# Drops the database specified on the +name+ attribute
# and creates it again using the provided +options+.
def recreate_database(name, options = {})
drop_database(name)
create_database(name, options)
end
# Create a new MySQL database with optional :charset and :collation.
# Charset defaults to utf8.
#
# Example:
# create_database 'charset_test', :charset => 'latin1', :collation => 'latin1_bin'
# create_database 'matt_development'
# create_database 'matt_development', :charset => :big5
def create_database(name, options = {})
if options[:collation]
execute "CREATE DATABASE `#{name}` DEFAULT CHARACTER SET `#{options[:charset] || 'utf8'}` COLLATE `#{options[:collation]}`"
else
execute "CREATE DATABASE `#{name}` DEFAULT CHARACTER SET `#{options[:charset] || 'utf8'}`"
end
end
# Drops a MySQL database.
#
# Example:
# drop_database('sebastian_development')
def drop_database(name) #:nodoc:
execute "DROP DATABASE IF EXISTS `#{name}`"
end
def current_database
select_value 'SELECT DATABASE() as db'
end
# Returns the database character set.
def charset
show_variable 'character_set_database'
end
# Returns the database collation strategy.
def collation
show_variable 'collation_database'
end
def tables(name = nil, database = nil) #:nodoc:
sql = ["SHOW TABLES", database].compact.join(' IN ')
execute_and_free(sql, 'SCHEMA') do |result|
result.collect { |field| field.first }
end
end
def table_exists?(name)
return true if super
name = name.to_s
schema, table = name.split('.', 2)
unless table # A table was provided without a schema
table = schema
schema = nil
end
tables(nil, schema).include? table
end
# Returns an array of indexes for the given table.
def indexes(table_name, name = nil) #:nodoc:
indexes = []
current_index = nil
execute_and_free("SHOW KEYS FROM #{quote_table_name(table_name)}", 'SCHEMA') do |result|
each_hash(result) do |row|
if current_index != row[:Key_name]
next if row[:Key_name] == 'PRIMARY' # skip the primary key
current_index = row[:Key_name]
indexes << IndexDefinition.new(row[:Table], row[:Key_name], row[:Non_unique].to_i == 0, [], [])
end
indexes.last.columns << row[:Column_name]
indexes.last.lengths << row[:Sub_part]
end
end
indexes
end
# Returns an array of +Column+ objects for the table specified by +table_name+.
def columns(table_name, name = nil)#:nodoc:
sql = "SHOW FULL FIELDS FROM #{quote_table_name(table_name)}"
execute_and_free(sql, 'SCHEMA') do |result|
each_hash(result).map do |field|
new_column(field[:Field], field[:Default], field[:Type], field[:Null] == "YES", field[:Collation])
end
end
end
def create_table(table_name, options = {}) #:nodoc:
super(table_name, options.reverse_merge(:options => "ENGINE=InnoDB"))
end
def bulk_change_table(table_name, operations) #:nodoc:
sqls = operations.map do |command, args|
table, arguments = args.shift, args
method = :"#{command}_sql"
if respond_to?(method)
send(method, table, *arguments)
else
raise "Unknown method called : #{method}(#{arguments.inspect})"
end
end.flatten.join(", ")
execute("ALTER TABLE #{quote_table_name(table_name)} #{sqls}")
end
# Renames a table.
#
# Example:
# rename_table('octopuses', 'octopi')
def rename_table(table_name, new_name)
execute "RENAME TABLE #{quote_table_name(table_name)} TO #{quote_table_name(new_name)}"
end
def add_column(table_name, column_name, type, options = {})
execute("ALTER TABLE #{quote_table_name(table_name)} #{add_column_sql(table_name, column_name, type, options)}")
end
def change_column_default(table_name, column_name, default)
column = column_for(table_name, column_name)
change_column table_name, column_name, column.sql_type, :default => default
end
def change_column_null(table_name, column_name, null, default = nil)
column = column_for(table_name, column_name)
unless null || default.nil?
execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
end
change_column table_name, column_name, column.sql_type, :null => null
end
def change_column(table_name, column_name, type, options = {}) #:nodoc:
execute("ALTER TABLE #{quote_table_name(table_name)} #{change_column_sql(table_name, column_name, type, options)}")
end
def rename_column(table_name, column_name, new_column_name) #:nodoc:
execute("ALTER TABLE #{quote_table_name(table_name)} #{rename_column_sql(table_name, column_name, new_column_name)}")
end
# Maps logical Rails types to MySQL-specific data types.
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
return super unless type.to_s == 'integer'
case limit
when 1; 'tinyint'
when 2; 'smallint'
when 3; 'mediumint'
when nil, 4, 11; 'int(11)' # compatibility with MySQL default
when 5..8; 'bigint'
else raise(ActiveRecordError, "No integer type has byte size #{limit}")
end
end
def add_column_position!(sql, options)
if options[:first]
sql << " FIRST"
elsif options[:after]
sql << " AFTER #{quote_column_name(options[:after])}"
end
end
# SHOW VARIABLES LIKE 'name'
def show_variable(name)
variables = select_all("SHOW VARIABLES LIKE '#{name}'")
variables.first['Value'] unless variables.empty?
end
# Returns a table's primary key and belonging sequence.
def pk_and_sequence_for(table)
execute_and_free("DESCRIBE #{quote_table_name(table)}", 'SCHEMA') do |result|
keys = each_hash(result).select { |row| row[:Key] == 'PRI' }.map { |row| row[:Field] }
keys.length == 1 ? [keys.first, nil] : nil
end
end
# Returns just a table's primary key
def primary_key(table)
pk_and_sequence = pk_and_sequence_for(table)
pk_and_sequence && pk_and_sequence.first
end
def case_sensitive_modifier(node)
Arel::Nodes::Bin.new(node)
end
def case_insensitive_comparison(table, attribute, column, value)
if column.case_sensitive?
super
else
table[attribute].eq(value)
end
end
def limited_update_conditions(where_sql, quoted_table_name, quoted_primary_key)
where_sql
end
protected
def add_index_length(option_strings, column_names, options = {})
if options.is_a?(Hash) && length = options[:length]
case length
when Hash
column_names.each {|name| option_strings[name] += "(#{length[name]})" if length.has_key?(name)}
when Fixnum
column_names.each {|name| option_strings[name] += "(#{length})"}
end
end
return option_strings
end
def quoted_columns_for_index(column_names, options = {})
option_strings = Hash[column_names.map {|name| [name, '']}]
# add index length
option_strings = add_index_length(option_strings, column_names, options)
# add index sort order
option_strings = add_index_sort_order(option_strings, column_names, options)
column_names.map {|name| quote_column_name(name) + option_strings[name]}
end
def translate_exception(exception, message)
case error_number(exception)
when 1062
RecordNotUnique.new(message, exception)
when 1452
InvalidForeignKey.new(message, exception)
else
super
end
end
def add_column_sql(table_name, column_name, type, options = {})
add_column_sql = "ADD #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
add_column_options!(add_column_sql, options)
add_column_position!(add_column_sql, options)
add_column_sql
end
def change_column_sql(table_name, column_name, type, options = {})
column = column_for(table_name, column_name)
unless options_include_default?(options)
options[:default] = column.default
end
unless options.has_key?(:null)
options[:null] = column.null
end
change_column_sql = "CHANGE #{quote_column_name(column_name)} #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
add_column_options!(change_column_sql, options)
add_column_position!(change_column_sql, options)
change_column_sql
end
def rename_column_sql(table_name, column_name, new_column_name)
options = {}
if column = columns(table_name).find { |c| c.name == column_name.to_s }
options[:default] = column.default
options[:null] = column.null
else
raise ActiveRecordError, "No such column: #{table_name}.#{column_name}"
end
current_type = select_one("SHOW COLUMNS FROM #{quote_table_name(table_name)} LIKE '#{column_name}'")["Type"]
rename_column_sql = "CHANGE #{quote_column_name(column_name)} #{quote_column_name(new_column_name)} #{current_type}"
add_column_options!(rename_column_sql, options)
rename_column_sql
end
def remove_column_sql(table_name, *column_names)
columns_for_remove(table_name, *column_names).map {|column_name| "DROP #{column_name}" }
end
alias :remove_columns_sql :remove_column
def add_index_sql(table_name, column_name, options = {})
index_name, index_type, index_columns = add_index_options(table_name, column_name, options)
"ADD #{index_type} INDEX #{index_name} (#{index_columns})"
end
def remove_index_sql(table_name, options = {})
index_name = index_name_for_remove(table_name, options)
"DROP INDEX #{index_name}"
end
def add_timestamps_sql(table_name)
[add_column_sql(table_name, :created_at, :datetime), add_column_sql(table_name, :updated_at, :datetime)]
end
def remove_timestamps_sql(table_name)
[remove_column_sql(table_name, :updated_at), remove_column_sql(table_name, :created_at)]
end
private
def supports_views?
version[0] >= 5
end
def column_for(table_name, column_name)
unless column = columns(table_name).find { |c| c.name == column_name.to_s }
raise "No such column: #{table_name}.#{column_name}"
end
column
end
end
end
end