diff options
Diffstat (limited to 'activerecord/lib/active_record/connection_adapters')
22 files changed, 2835 insertions, 1732 deletions
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb b/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb index d69f02d504..7a3d9bfd3e 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/connection_pool.rb @@ -11,9 +11,6 @@ module ActiveRecord # Raised when a connection pool is full and another connection is requested class PoolFullError < ConnectionNotEstablished - def initialize size, timeout - super("Connection pool of size #{size} and timeout #{timeout}s is full") - end end module ConnectionAdapters @@ -57,13 +54,146 @@ module ActiveRecord # # == Options # - # There are two connection-pooling-related options that you can add to + # There are several connection-pooling-related options that you can add to # your database connection configuration: # # * +pool+: number indicating size of connection pool (default 5) - # * +wait_timeout+: number of seconds to block and wait for a connection + # * +checkout_timeout+: number of seconds to block and wait for a connection # before giving up and raising a timeout error (default 5 seconds). + # * +reaping_frequency+: frequency in seconds to periodically run the + # Reaper, which attempts to find and close dead connections, which can + # occur if a programmer forgets to close a connection at the end of a + # thread or a thread dies unexpectedly. (Default nil, which means don't + # run the Reaper). + # * +dead_connection_timeout+: number of seconds from last checkout + # after which the Reaper will consider a connection reapable. (default + # 5 seconds). class ConnectionPool + # Threadsafe, fair, FIFO queue. Meant to be used by ConnectionPool + # with which it shares a Monitor. But could be a generic Queue. + # + # The Queue in stdlib's 'thread' could replace this class except + # stdlib's doesn't support waiting with a timeout. + class Queue + def initialize(lock = Monitor.new) + @lock = lock + @cond = @lock.new_cond + @num_waiting = 0 + @queue = [] + end + + # Test if any threads are currently waiting on the queue. + def any_waiting? + synchronize do + @num_waiting > 0 + end + end + + # Return the number of threads currently waiting on this + # queue. + def num_waiting + synchronize do + @num_waiting + end + end + + # Add +element+ to the queue. Never blocks. + def add(element) + synchronize do + @queue.push element + @cond.signal + end + end + + # If +element+ is in the queue, remove and return it, or nil. + def delete(element) + synchronize do + @queue.delete(element) + end + end + + # Remove all elements from the queue. + def clear + synchronize do + @queue.clear + end + end + + # Remove the head of the queue. + # + # If +timeout+ is not given, remove and return the head the + # queue if the number of available elements is strictly + # greater than the number of threads currently waiting (that + # is, don't jump ahead in line). Otherwise, return nil. + # + # If +timeout+ is given, block if it there is no element + # available, waiting up to +timeout+ seconds for an element to + # become available. + # + # Raises: + # - ConnectionTimeoutError if +timeout+ is given and no element + # becomes available after +timeout+ seconds, + def poll(timeout = nil) + synchronize do + if timeout + no_wait_poll || wait_poll(timeout) + else + no_wait_poll + end + end + end + + private + + def synchronize(&block) + @lock.synchronize(&block) + end + + # Test if the queue currently contains any elements. + def any? + !@queue.empty? + end + + # A thread can remove an element from the queue without + # waiting if an only if the number of currently available + # connections is strictly greater than the number of waiting + # threads. + def can_remove_no_wait? + @queue.size > @num_waiting + end + + # Removes and returns the head of the queue if possible, or nil. + def remove + @queue.shift + end + + # Remove and return the head the queue if the number of + # available elements is strictly greater than the number of + # threads currently waiting. Otherwise, return nil. + def no_wait_poll + remove if can_remove_no_wait? + end + + # Waits on the queue up to +timeout+ seconds, then removes and + # returns the head of the queue. + def wait_poll(timeout) + @num_waiting += 1 + + t0 = Time.now + elapsed = 0 + loop do + @cond.wait(timeout - elapsed) + + return remove if any? + + elapsed = Time.now - t0 + raise ConnectionTimeoutError if elapsed >= timeout + end + ensure + @num_waiting -= 1 + end + end + # Every +frequency+ seconds, the reaper will call +reap+ on +pool+. # A reaper instantiated with a nil frequency will never reap the # connection pool. @@ -91,7 +221,7 @@ module ActiveRecord include MonitorMixin - attr_accessor :automatic_reconnect, :timeout + attr_accessor :automatic_reconnect, :checkout_timeout, :dead_connection_timeout attr_reader :spec, :connections, :size, :reaper # Creates a new ConnectionPool object. +spec+ is a ConnectionSpecification @@ -108,7 +238,8 @@ module ActiveRecord # The cache of reserved connections mapped to threads @reserved_connections = {} - @timeout = spec.config[:wait_timeout] || 5 + @checkout_timeout = spec.config[:checkout_timeout] || 5 + @dead_connection_timeout = spec.config[:dead_connection_timeout] @reaper = Reaper.new self, spec.config[:reaping_frequency] @reaper.run @@ -117,6 +248,16 @@ module ActiveRecord @connections = [] @automatic_reconnect = true + + @available = Queue.new self + end + + # Hack for tests to be able to add connections. Do not call outside of tests + def insert_connection_for_test!(c) #:nodoc: + synchronize do + @connections << c + @available.add c + end end # Retrieve the connection associated with the current thread, or call @@ -125,21 +266,28 @@ module ActiveRecord # #connection can be called any number of times; the connection is # held in a hash keyed by the thread id. def connection - @reserved_connections[current_connection_id] ||= checkout + synchronize do + @reserved_connections[current_connection_id] ||= checkout + end end - # Check to see if there is an active connection in this connection - # pool. + # Is there an open connection that is being used for the current thread? def active_connection? - active_connections.any? + synchronize do + @reserved_connections.fetch(current_connection_id) { + return false + }.in_use? + end end # Signal that the thread is finished with the current connection. # #release_connection releases the connection-thread association # and returns the connection to the pool. def release_connection(with_id = current_connection_id) - conn = @reserved_connections.delete(with_id) - checkin conn if conn + synchronize do + conn = @reserved_connections.delete(with_id) + checkin conn if conn + end end # If a connection already exists yield it to the block. If no connection @@ -167,6 +315,7 @@ module ActiveRecord conn.disconnect! end @connections = [] + @available.clear end end @@ -181,22 +330,17 @@ module ActiveRecord @connections.delete_if do |conn| conn.requires_reloading? end - end - end - - # Verify active connections and remove and disconnect connections - # associated with stale threads. - def verify_active_connections! #:nodoc: - synchronize do - @connections.each do |connection| - connection.verify! + @available.clear + @connections.each do |conn| + @available.add conn end end end def clear_stale_cached_connections! # :nodoc: + reap end - deprecate :clear_stale_cached_connections! + deprecate :clear_stale_cached_connections! => "Please use #reap instead" # Check-out a database connection from the pool, indicating that you want # to use it. You should call #checkin when you no longer need this. @@ -213,23 +357,10 @@ module ActiveRecord # Raises: # - PoolFullError: no connection can be obtained from the pool. def checkout - # Checkout an available connection synchronize do - # Try to find a connection that hasn't been leased, and lease it - conn = connections.find { |c| c.lease } - - # If all connections were leased, and we have room to expand, - # create a new connection and lease it. - if !conn && connections.size < size - conn = checkout_new_connection - conn.lease - end - - if conn - checkout_and_verify conn - else - raise PoolFullError.new(size, timeout) - end + conn = acquire_connection + conn.lease + checkout_and_verify(conn) end end @@ -243,6 +374,10 @@ module ActiveRecord conn.run_callbacks :checkin do conn.expire end + + release conn + + @available.add conn end end @@ -251,13 +386,13 @@ module ActiveRecord def remove(conn) synchronize do @connections.delete conn + @available.delete conn # FIXME: we might want to store the key on the connection so that removing # from the reserved hash will be a little easier. - thread_id = @reserved_connections.keys.find { |k| - @reserved_connections[k] == conn - } - @reserved_connections.delete thread_id if thread_id + release conn + + @available.add checkout_new_connection if @available.any_waiting? end end @@ -266,7 +401,7 @@ module ActiveRecord # or a thread dies unexpectedly. def reap synchronize do - stale = Time.now - @timeout + stale = Time.now - @dead_connection_timeout connections.dup.each do |conn| remove conn if conn.in_use? && stale > conn.last_use && !conn.active? end @@ -275,12 +410,49 @@ module ActiveRecord private + # Acquire a connection by one of 1) immediately removing one + # from the queue of available connections, 2) creating a new + # connection if the pool is not at capacity, 3) waiting on the + # queue for a connection to become available. + # + # Raises: + # - PoolFullError if a connection could not be acquired (FIXME: + # why not ConnectionTimeoutError? + def acquire_connection + if conn = @available.poll + conn + elsif @connections.size < @size + checkout_new_connection + else + t0 = Time.now + begin + @available.poll(@checkout_timeout) + rescue ConnectionTimeoutError + msg = 'could not obtain a database connection within %0.3f seconds (waited %0.3f seconds)' % + [@checkout_timeout, Time.now - t0] + raise PoolFullError, msg + end + end + end + + def release(conn) + thread_id = if @reserved_connections[current_connection_id] == conn + current_connection_id + else + @reserved_connections.keys.find { |k| + @reserved_connections[k] == conn + } + end + + @reserved_connections.delete thread_id if thread_id + end + def new_connection - ActiveRecord::Base.send(spec.adapter_method, spec.config) + ActiveRecord::Model.send(spec.adapter_method, spec.config) end def current_connection_id #:nodoc: - ActiveRecord::Base.connection_id ||= Thread.current.object_id + ActiveRecord::Model.connection_id ||= Thread.current.object_id end def checkout_new_connection @@ -298,10 +470,6 @@ module ActiveRecord end c end - - def active_connections - @connections.find_all { |c| c.in_use? } - end end # ConnectionHandler is a collection of ConnectionPool objects. It is used @@ -328,43 +496,40 @@ module ActiveRecord # ActiveRecord::Base.connection_handler. Active Record models use this to # determine that connection pool that they should use. class ConnectionHandler - attr_reader :connection_pools + def initialize + @owner_to_pool = Hash.new { |h,k| h[k] = {} } + @class_to_pool = Hash.new { |h,k| h[k] = {} } + end - def initialize(pools = {}) - @connection_pools = pools - @class_to_pool = {} + def connection_pools + owner_to_pool.values.compact end - def establish_connection(name, spec) - @connection_pools[spec] ||= ConnectionAdapters::ConnectionPool.new(spec) - @class_to_pool[name] = @connection_pools[spec] + def establish_connection(owner, spec) + @class_to_pool.clear + owner_to_pool[owner] = ConnectionAdapters::ConnectionPool.new(spec) end # Returns true if there are any active connections among the connection # pools that the ConnectionHandler is managing. def active_connections? - connection_pools.values.any? { |pool| pool.active_connection? } + connection_pools.any?(&:active_connection?) end # Returns any connections in use by the current thread back to the pool, # and also returns connections to the pool cached by threads that are no # longer alive. def clear_active_connections! - @connection_pools.each_value {|pool| pool.release_connection } + connection_pools.each(&:release_connection) end # Clears the cache which maps classes. def clear_reloadable_connections! - @connection_pools.each_value {|pool| pool.clear_reloadable_connections! } + connection_pools.each(&:clear_reloadable_connections!) end def clear_all_connections! - @connection_pools.each_value {|pool| pool.disconnect! } - end - - # Verify active connections. - def verify_active_connections! #:nodoc: - @connection_pools.each_value {|pool| pool.verify_active_connections! } + connection_pools.each(&:disconnect!) end # Locate the connection of the nearest super class. This can be an @@ -387,21 +552,61 @@ module ActiveRecord # connection and the defined connection (if they exist). The result # can be used as an argument for establish_connection, for easily # re-establishing the connection. - def remove_connection(klass) - pool = @class_to_pool.delete(klass.name) - return nil unless pool - - @connection_pools.delete pool.spec - pool.automatic_reconnect = false - pool.disconnect! - pool.spec.config + def remove_connection(owner) + if pool = owner_to_pool.delete(owner) + @class_to_pool.clear + pool.automatic_reconnect = false + pool.disconnect! + pool.spec.config + end end + # Retrieving the connection pool happens a lot so we cache it in @class_to_pool. + # This makes retrieving the connection pool O(1) once the process is warm. + # When a connection is established or removed, we invalidate the cache. + # + # Ideally we would use #fetch here, as class_to_pool[klass] may sometimes be nil. + # However, benchmarking (https://gist.github.com/3552829) showed that #fetch is + # significantly slower than #[]. So in the nil case, no caching will take place, + # but that's ok since the nil case is not the common one that we wish to optimise + # for. def retrieve_connection_pool(klass) - pool = @class_to_pool[klass.name] - return pool if pool - return nil if ActiveRecord::Model == klass - retrieve_connection_pool klass.active_record_super + class_to_pool[klass] ||= begin + until pool = pool_for(klass) + klass = klass.superclass + break unless klass < Model::Tag + end + + class_to_pool[klass] = pool || pool_for(ActiveRecord::Model) + end + end + + private + + def owner_to_pool + @owner_to_pool[Process.pid] + end + + def class_to_pool + @class_to_pool[Process.pid] + end + + def pool_for(owner) + owner_to_pool.fetch(owner) { + if ancestor_pool = pool_from_any_process_for(owner) + # A connection was established in an ancestor process that must have + # subsequently forked. We can't reuse the connection, but we can copy + # the specification and establish a new connection with it. + establish_connection owner, ancestor_pool.spec + else + owner_to_pool[owner] = nil + end + } + end + + def pool_from_any_process_for(owner) + owner_to_pool = @owner_to_pool.values.find { |v| v[owner] } + owner_to_pool && owner_to_pool[owner] end end diff --git a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb index eb8cff9610..4e1f0e1d62 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb @@ -1,10 +1,19 @@ module ActiveRecord module ConnectionAdapters # :nodoc: module DatabaseStatements + def initialize + super + @_current_transaction_records = [] + @transaction_joinable = nil + end + # Converts an arel AST to SQL - def to_sql(arel) + def to_sql(arel, binds = []) if arel.respond_to?(:ast) - visitor.accept(arel.ast) + binds = binds.dup + visitor.accept(arel.ast) do + quote(*binds.shift.reverse) + end else arel end @@ -13,19 +22,19 @@ module ActiveRecord # Returns an array of record hashes with the column names as keys and # column values as values. def select_all(arel, name = nil, binds = []) - select(to_sql(arel), name, binds) + select(to_sql(arel, binds), name, binds) end # Returns a record hash with the column names as keys and column values # as values. - def select_one(arel, name = nil) - result = select_all(arel, name) + def select_one(arel, name = nil, binds = []) + result = select_all(arel, name, binds) result.first if result end # Returns a single value from a record - def select_value(arel, name = nil) - if result = select_one(arel, name) + def select_value(arel, name = nil, binds = []) + if result = select_one(arel, name, binds) result.values.first end end @@ -33,7 +42,7 @@ module ActiveRecord # Returns an array of the values of the first column in a select: # select_values("SELECT id FROM companies LIMIT 3") => [1,2,3] def select_values(arel, name = nil) - result = select_rows(to_sql(arel), name) + result = select_rows(to_sql(arel, []), name) result.map { |v| v[0] } end @@ -55,21 +64,21 @@ module ActiveRecord end # Executes insert +sql+ statement in the context of this connection using - # +binds+ as the bind substitutes. +name+ is the logged along with + # +binds+ as the bind substitutes. +name+ is logged along with # the executed +sql+ statement. - def exec_insert(sql, name, binds) + def exec_insert(sql, name, binds, pk = nil, sequence_name = nil) exec_query(sql, name, binds) end # Executes delete +sql+ statement in the context of this connection using - # +binds+ as the bind substitutes. +name+ is the logged along with + # +binds+ as the bind substitutes. +name+ is logged along with # the executed +sql+ statement. def exec_delete(sql, name, binds) exec_query(sql, name, binds) end # Executes update +sql+ statement in the context of this connection using - # +binds+ as the bind substitutes. +name+ is the logged along with + # +binds+ as the bind substitutes. +name+ is logged along with # the executed +sql+ statement. def exec_update(sql, name, binds) exec_query(sql, name, binds) @@ -84,19 +93,19 @@ module ActiveRecord # If the next id was calculated in advance (as in Oracle), it should be # passed in as +id_value+. def insert(arel, name = nil, pk = nil, id_value = nil, sequence_name = nil, binds = []) - sql, binds = sql_for_insert(to_sql(arel), pk, id_value, sequence_name, binds) - value = exec_insert(sql, name, binds) + sql, binds = sql_for_insert(to_sql(arel, binds), pk, id_value, sequence_name, binds) + value = exec_insert(sql, name, binds, pk, sequence_name) id_value || last_inserted_id(value) end # Executes the update statement and returns the number of rows affected. def update(arel, name = nil, binds = []) - exec_update(to_sql(arel), name, binds) + exec_update(to_sql(arel, binds), name, binds) end # Executes the delete statement and returns the number of rows affected. def delete(arel, name = nil, binds = []) - exec_delete(to_sql(arel), name, binds) + exec_delete(to_sql(arel, binds), name, binds) end # Checks whether there is currently no transaction active. This is done @@ -164,31 +173,23 @@ module ActiveRecord def transaction(options = {}) options.assert_valid_keys :requires_new, :joinable - last_transaction_joinable = defined?(@transaction_joinable) ? @transaction_joinable : nil - if options.has_key?(:joinable) - @transaction_joinable = options[:joinable] - else - @transaction_joinable = true - end - requires_new = options[:requires_new] || !last_transaction_joinable - - transaction_open = false - @_current_transaction_records ||= [] + last_transaction_joinable = @transaction_joinable + @transaction_joinable = options.fetch(:joinable, true) + requires_new = options[:requires_new] || !last_transaction_joinable + transaction_open = false begin - if block_given? - if requires_new || open_transactions == 0 - if open_transactions == 0 - begin_db_transaction - elsif requires_new - create_savepoint - end - increment_open_transactions - transaction_open = true - @_current_transaction_records.push([]) + if requires_new || open_transactions == 0 + if open_transactions == 0 + begin_db_transaction + elsif requires_new + create_savepoint end - yield + increment_open_transactions + transaction_open = true + @_current_transaction_records.push([]) end + yield rescue Exception => database_transaction_rollback if transaction_open && !outside_transaction? transaction_open = false @@ -222,7 +223,7 @@ module ActiveRecord @_current_transaction_records.last.concat(save_point_records) end end - rescue Exception => database_transaction_rollback + rescue Exception if open_transactions == 0 rollback_db_transaction rollback_transaction_records(true) @@ -310,13 +311,27 @@ module ActiveRecord # on mysql (even when aliasing the tables), but mysql allows using JOIN directly in # an UPDATE statement, so in the mysql adapters we redefine this to do that. def join_to_update(update, select) #:nodoc: - subselect = select.clone - subselect.projections = [update.key] + key = update.key + subselect = subquery_for(key, select) + + update.where key.in(subselect) + end - update.where update.key.in(subselect) + def join_to_delete(delete, select, key) #:nodoc: + subselect = subquery_for(key, select) + + delete.where key.in(subselect) end protected + + # Return a subquery for the given key using the join information. + def subquery_for(key, select) + subselect = select.clone + subselect.projections = [key] + subselect + end + # Returns an array of record hashes with the column names as keys and # column values as values. def select(sql, name = nil, binds = []) @@ -353,7 +368,7 @@ module ActiveRecord records.uniq.each do |record| begin record.rolledback!(rollback) - rescue Exception => e + rescue => e record.logger.error(e) if record.respond_to?(:logger) && record.logger end end @@ -368,7 +383,7 @@ module ActiveRecord records.uniq.each do |record| begin record.committed! - rescue Exception => e + rescue => e record.logger.error(e) if record.respond_to?(:logger) && record.logger end end diff --git a/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb b/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb index 6ba64bb88f..be6fda95b4 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb @@ -2,7 +2,7 @@ module ActiveRecord module ConnectionAdapters # :nodoc: module QueryCache class << self - def included(base) + def included(base) #:nodoc: dirties_query_cache base, :insert, :update, :delete end @@ -56,8 +56,8 @@ module ActiveRecord end def select_all(arel, name = nil, binds = []) - if @query_cache_enabled - sql = to_sql(arel) + if @query_cache_enabled && !locked?(arel) + sql = to_sql(arel, binds) cache_sql(sql, binds) { super(sql, name, binds) } else super @@ -65,6 +65,7 @@ module ActiveRecord end private + def cache_sql(sql, binds) result = if @query_cache[sql].key?(binds) @@ -83,6 +84,10 @@ module ActiveRecord result.collect { |row| row.dup } end end + + def locked?(arel) + arel.respond_to?(:locked) && arel.locked + end end end end diff --git a/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb b/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb index 44ac37c498..60a9eee7c7 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb @@ -31,9 +31,10 @@ module ActiveRecord # BigDecimals need to be put in a non-normalized form and quoted. when nil then "NULL" when BigDecimal then value.to_s('F') - when Numeric then value.to_s + when Numeric, ActiveSupport::Duration then value.to_s when Date, Time then "'#{quoted_date(value)}'" when Symbol then "'#{quote_string(value.to_s)}'" + when Class then "'#{value.to_s}'" else "'#{quote_string(YAML.dump(value))}'" end diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb index 132ca10f79..dca355aa93 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_definitions.rb @@ -1,4 +1,3 @@ -require 'active_support/core_ext/object/blank' require 'date' require 'set' require 'bigdecimal' @@ -6,7 +5,10 @@ require 'bigdecimal/util' module ActiveRecord module ConnectionAdapters #:nodoc: - class IndexDefinition < Struct.new(:table, :name, :unique, :columns, :lengths, :orders) #:nodoc: + # Abstract representation of an index definition on a table. Instances of + # this type are typically created and returned by methods in database + # adapters. e.g. ActiveRecord::ConnectionAdapters::AbstractMysqlAdapter#indexes + class IndexDefinition < Struct.new(:table, :name, :unique, :columns, :lengths, :orders, :where) #:nodoc: end # Abstract representation of a column definition. Instances of this type @@ -20,7 +22,7 @@ module ActiveRecord end def sql_type - base.type_to_sql(type.to_sym, limit, precision, scale) rescue type + base.type_to_sql(type.to_sym, limit, precision, scale) end def to_sql @@ -62,11 +64,12 @@ module ActiveRecord class TableDefinition # An array of ColumnDefinition objects, representing the column changes # that have been defined. - attr_accessor :columns + attr_accessor :columns, :indexes def initialize(base) @columns = [] @columns_hash = {} + @indexes = {} @base = base end @@ -209,19 +212,22 @@ module ActiveRecord # # TableDefinition#references will add an appropriately-named _id column, plus a corresponding _type # column if the <tt>:polymorphic</tt> option is supplied. If <tt>:polymorphic</tt> is a hash of - # options, these will be used when creating the <tt>_type</tt> column. So what can be written like this: + # options, these will be used when creating the <tt>_type</tt> column. The <tt>:index</tt> option + # will also create an index, similar to calling <tt>add_index</tt>. So what can be written like this: # # create_table :taggings do |t| # t.integer :tag_id, :tagger_id, :taggable_id # t.string :tagger_type # t.string :taggable_type, :default => 'Photo' # end + # add_index :taggings, :tag_id, :name => 'index_taggings_on_tag_id' + # add_index :taggings, [:tagger_id, :tagger_type] # # Can also be written as follows using references: # # create_table :taggings do |t| - # t.references :tag - # t.references :tagger, :polymorphic => true + # t.references :tag, :index => { :name => 'index_taggings_on_tag_id' } + # t.references :tagger, :polymorphic => true, :index => true # t.references :taggable, :polymorphic => { :default => 'Photo' } # end def column(name, type, options = {}) @@ -253,10 +259,18 @@ module ActiveRecord EOV end + # Adds index options to the indexes hash, keyed by column name + # This is primarily used to track indexes that need to be created after the table + # + # index(:account_id, :name => 'index_projects_on_account_id') + def index(column_name, options = {}) + indexes[column_name] = options + end + # Appends <tt>:datetime</tt> columns <tt>:created_at</tt> and # <tt>:updated_at</tt> to the table. def timestamps(*args) - options = { :null => false }.merge(args.extract_options!) + options = args.extract_options! column(:created_at, :datetime, options) column(:updated_at, :datetime, options) end @@ -264,9 +278,11 @@ module ActiveRecord def references(*args) options = args.extract_options! polymorphic = options.delete(:polymorphic) + index_options = options.delete(:index) args.each do |col| column("#{col}_id", :integer, options) - column("#{col}_type", :string, polymorphic.is_a?(Hash) ? polymorphic : options) unless polymorphic.nil? + column("#{col}_type", :string, polymorphic.is_a?(Hash) ? polymorphic : options) if polymorphic + index(polymorphic ? %w(id type).map { |t| "#{col}_#{t}" } : "#{col}_id", index_options.is_a?(Hash) ? index_options : nil) if index_options end end alias :belongs_to :references @@ -331,7 +347,7 @@ module ActiveRecord # Adds a new column to the named table. # See TableDefinition#column for details of the options you can use. - # ===== Example + # # ====== Creating a simple column # t.column(:name, :string) def column(column_name, type, options = {}) @@ -346,7 +362,6 @@ module ActiveRecord # Adds a new index to the table. +column_name+ can be a single Symbol, or # an Array of Symbols. See SchemaStatements#add_index # - # ===== Examples # ====== Creating a simple index # t.index(:name) # ====== Creating a unique index @@ -363,7 +378,7 @@ module ActiveRecord end # Adds timestamps (+created_at+ and +updated_at+) columns to the table. See SchemaStatements#add_timestamps - # ===== Example + # # t.timestamps def timestamps @base.add_timestamps(@table_name) @@ -371,7 +386,7 @@ module ActiveRecord # Changes the column's definition according to the new options. # See TableDefinition#column for details of the options you can use. - # ===== Examples + # # t.change(:name, :string, :limit => 80) # t.change(:description, :text) def change(column_name, type, options = {}) @@ -379,7 +394,7 @@ module ActiveRecord end # Sets a new default value for a column. See SchemaStatements#change_column_default - # ===== Examples + # # t.change_default(:qualification, 'new') # t.change_default(:authorized, 1) def change_default(column_name, default) @@ -387,16 +402,15 @@ module ActiveRecord end # Removes the column(s) from the table definition. - # ===== Examples + # # t.remove(:qualification) # t.remove(:qualification, :experience) def remove(*column_names) - @base.remove_column(@table_name, column_names) + @base.remove_column(@table_name, *column_names) end # Removes the given index from the table. # - # ===== Examples # ====== Remove the index_table_name_on_column in the table_name table # t.remove_index :column # ====== Remove the index named index_table_name_on_branch_id in the table_name table @@ -410,14 +424,14 @@ module ActiveRecord end # Removes the timestamp columns (+created_at+ and +updated_at+) from the table. - # ===== Example + # # t.remove_timestamps def remove_timestamps @base.remove_timestamps(@table_name) end # Renames a column. - # ===== Example + # # t.rename(:description, :name) def rename(column_name, new_column_name) @base.rename_column(@table_name, column_name, new_column_name) @@ -425,38 +439,34 @@ module ActiveRecord # Adds a reference. Optionally adds a +type+ column, if <tt>:polymorphic</tt> option is provided. # <tt>references</tt> and <tt>belongs_to</tt> are acceptable. - # ===== Examples - # t.references(:goat) - # t.references(:goat, :polymorphic => true) - # t.belongs_to(:goat) + # + # t.references(:user) + # t.belongs_to(:supplier, polymorphic: true) + # def references(*args) options = args.extract_options! - polymorphic = options.delete(:polymorphic) - args.each do |col| - @base.add_column(@table_name, "#{col}_id", :integer, options) - @base.add_column(@table_name, "#{col}_type", :string, polymorphic.is_a?(Hash) ? polymorphic : options) unless polymorphic.nil? + args.each do |ref_name| + @base.add_reference(@table_name, ref_name, options) end end alias :belongs_to :references # Removes a reference. Optionally removes a +type+ column. # <tt>remove_references</tt> and <tt>remove_belongs_to</tt> are acceptable. - # ===== Examples - # t.remove_references(:goat) - # t.remove_references(:goat, :polymorphic => true) - # t.remove_belongs_to(:goat) + # + # t.remove_references(:user) + # t.remove_belongs_to(:supplier, polymorphic: true) + # def remove_references(*args) options = args.extract_options! - polymorphic = options.delete(:polymorphic) - args.each do |col| - @base.remove_column(@table_name, "#{col}_id") - @base.remove_column(@table_name, "#{col}_type") unless polymorphic.nil? + args.each do |ref_name| + @base.remove_reference(@table_name, ref_name, options) end end - alias :remove_belongs_to :remove_references + alias :remove_belongs_to :remove_references # Adds a column or columns of a specified type - # ===== Examples + # # t.string(:goat) # t.string(:goat, :sheep) %w( string text integer float decimal datetime timestamp time date binary boolean ).each do |column_type| diff --git a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb index 1f9321edb6..86d6266af9 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract/schema_statements.rb @@ -1,5 +1,3 @@ -require 'active_support/deprecation/reporting' -require 'active_record/schema_migration' require 'active_record/migration/join_table' module ActiveRecord @@ -16,12 +14,11 @@ module ActiveRecord # Truncates a table alias according to the limits of the current adapter. def table_alias_for(table_name) - table_name[0...table_alias_length].gsub(/\./, '_') + table_name[0...table_alias_length].tr('.', '_') end # Checks to see if the table +table_name+ exists on the database. # - # === Example # table_exists?(:developers) def table_exists?(table_name) tables.include?(table_name.to_s) @@ -32,7 +29,6 @@ module ActiveRecord # Checks to see if an index exists on a table for a given index definition. # - # === Examples # # Check an index exists # index_exists?(:suppliers, :company_id) # @@ -60,7 +56,6 @@ module ActiveRecord # Checks to see if a column exists in a given table. # - # === Examples # # Check a column exists # column_exists?(:suppliers, :name) # @@ -68,13 +63,18 @@ module ActiveRecord # column_exists?(:suppliers, :name, :string) # # # Check a column exists with a specific definition - # column_exists?(:suppliers, :name, :string, :limit => 100) + # column_exists?(:suppliers, :name, :string, limit: 100) + # column_exists?(:suppliers, :name, :string, default: 'default') + # column_exists?(:suppliers, :name, :string, null: false) + # column_exists?(:suppliers, :tax, :decimal, precision: 8, scale: 2) def column_exists?(table_name, column_name, type = nil, options = {}) columns(table_name).any?{ |c| c.name == column_name.to_s && - (!type || c.type == type) && - (!options[:limit] || c.limit == options[:limit]) && - (!options[:precision] || c.precision == options[:precision]) && - (!options[:scale] || c.scale == options[:scale]) } + (!type || c.type == type) && + (!options.key?(:limit) || c.limit == options[:limit]) && + (!options.key?(:precision) || c.precision == options[:precision]) && + (!options.key?(:scale) || c.scale == options[:scale]) && + (!options.key?(:default) || c.default == options[:default]) && + (!options.key?(:null) || c.null == options[:null]) } end # Creates a new table with the name +table_name+. +table_name+ may either @@ -126,7 +126,6 @@ module ActiveRecord # Set to true to drop the table before creating it. # Defaults to false. # - # ===== Examples # ====== Add a backend specific option to the generated SQL (MySQL) # create_table(:suppliers, :options => 'ENGINE=InnoDB DEFAULT CHARSET=utf8') # generates: @@ -163,7 +162,7 @@ module ActiveRecord yield td if block_given? if options[:force] && table_exists?(table_name) - drop_table(table_name) + drop_table(table_name, options) end create_sql = "CREATE#{' TEMPORARY' if options[:temporary]} TABLE " @@ -171,10 +170,11 @@ module ActiveRecord create_sql << td.to_sql create_sql << ") #{options[:options]}" execute create_sql + td.indexes.each_pair { |c,o| add_index table_name, c, o } end # Creates a new join table with the name created using the lexical order of the first two - # arguments. These arguments can be be a String or a Symbol. + # arguments. These arguments can be a String or a Symbol. # # # Creates a table called 'assemblies_parts' with no id. # create_join_table(:assemblies, :parts) @@ -192,7 +192,6 @@ module ActiveRecord # Set to true to drop the table before creating it. # Defaults to false. # - # ===== Examples # ====== Add a backend specific option to the generated SQL (MySQL) # create_join_table(:assemblies, :parts, :options => 'ENGINE=InnoDB DEFAULT CHARSET=utf8') # generates: @@ -204,17 +203,19 @@ module ActiveRecord join_table_name = find_join_table_name(table_1, table_2, options) column_options = options.delete(:column_options) || {} - column_options.reverse_merge!({:null => false}) + column_options.reverse_merge!(null: false) - create_table(join_table_name, options.merge!(:id => false)) do |td| - td.integer :"#{table_1.to_s.singularize}_id", column_options - td.integer :"#{table_2.to_s.singularize}_id", column_options + t1_column, t2_column = [table_1, table_2].map{ |t| t.to_s.singularize.foreign_key } + + create_table(join_table_name, options.merge!(id: false)) do |td| + td.integer t1_column, column_options + td.integer t2_column, column_options + yield td if block_given? end end # A block for changing columns in +table+. # - # === Example # # change_table() yields a Table instance # change_table(:suppliers) do |t| # t.column :name, :string, :limit => 60 @@ -228,7 +229,6 @@ module ActiveRecord # # Defaults to false. # - # ===== Examples # ====== Add a column # change_table(:suppliers) do |t| # t.column :name, :string, :limit => 60 @@ -287,14 +287,14 @@ module ActiveRecord end # Renames a table. - # ===== Example + # # rename_table('octopuses', 'octopi') def rename_table(table_name, new_name) raise NotImplementedError, "rename_table is not implemented" end # Drops a table from the database. - def drop_table(table_name) + def drop_table(table_name, options = {}) execute "DROP TABLE #{quote_table_name(table_name)}" end @@ -307,7 +307,7 @@ module ActiveRecord end # Removes the column(s) from the table definition. - # ===== Examples + # # remove_column(:suppliers, :qualification) # remove_columns(:suppliers, :qualification, :experience) def remove_column(table_name, *column_names) @@ -317,7 +317,7 @@ module ActiveRecord # Changes the column's definition according to the new options. # See TableDefinition#column for details of the options you can use. - # ===== Examples + # # change_column(:suppliers, :name, :string, :limit => 80) # change_column(:accounts, :description, :text) def change_column(table_name, column_name, type, options = {}) @@ -325,7 +325,7 @@ module ActiveRecord end # Sets a new default value for a column. - # ===== Examples + # # change_column_default(:suppliers, :qualification, 'new') # change_column_default(:accounts, :authorized, 1) # change_column_default(:users, :email, nil) @@ -334,7 +334,7 @@ module ActiveRecord end # Renames a column. - # ===== Example + # # rename_column(:suppliers, :description, :name) def rename_column(table_name, column_name, new_column_name) raise NotImplementedError, "rename_column is not implemented" @@ -346,8 +346,6 @@ module ActiveRecord # The index will be named after the table and the column name(s), unless # you pass <tt>:name</tt> as an option. # - # ===== Examples - # # ====== Creating a simple index # add_index(:suppliers, :name) # generates @@ -375,15 +373,22 @@ module ActiveRecord # Note: SQLite doesn't support index length # # ====== Creating an index with a sort order (desc or asc, asc is the default) - # add_index(:accounts, [:branch_id, :party_id, :surname], :order => {:branch_id => :desc, :part_id => :asc}) + # add_index(:accounts, [:branch_id, :party_id, :surname], :order => {:branch_id => :desc, :party_id => :asc}) # generates # CREATE INDEX by_branch_desc_party ON accounts(branch_id DESC, party_id ASC, surname) # # Note: mysql doesn't yet support index order (it accepts the syntax but ignores it) # + # ====== Creating a partial index + # add_index(:accounts, [:branch_id, :party_id], :unique => true, :where => "active") + # generates + # CREATE UNIQUE INDEX index_accounts_on_branch_id_and_party_id ON accounts(branch_id, party_id) WHERE active + # + # Note: only supported by PostgreSQL + # def add_index(table_name, column_name, options = {}) - index_name, index_type, index_columns = add_index_options(table_name, column_name, options) - execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{index_columns})" + index_name, index_type, index_columns, index_options = add_index_options(table_name, column_name, options) + execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{index_columns})#{index_options}" end # Remove the given index from the table. @@ -440,6 +445,42 @@ module ActiveRecord indexes(table_name).detect { |i| i.name == index_name } end + # Adds a reference. Optionally adds a +type+ column, if <tt>:polymorphic</tt> option is provided. + # <tt>add_reference</tt> and <tt>add_belongs_to</tt> are acceptable. + # + # ====== Create a user_id column + # add_reference(:products, :user) + # + # ====== Create a supplier_id and supplier_type columns + # add_belongs_to(:products, :supplier, polymorphic: true) + # + # ====== Create a supplier_id, supplier_type columns and appropriate index + # add_reference(:products, :supplier, polymorphic: true, index: true) + # + def add_reference(table_name, ref_name, options = {}) + polymorphic = options.delete(:polymorphic) + index_options = options.delete(:index) + add_column(table_name, "#{ref_name}_id", :integer, options) + add_column(table_name, "#{ref_name}_type", :string, polymorphic.is_a?(Hash) ? polymorphic : options) if polymorphic + add_index(table_name, polymorphic ? %w[id type].map{ |t| "#{ref_name}_#{t}" } : "#{ref_name}_id", index_options.is_a?(Hash) ? index_options : nil) if index_options + end + alias :add_belongs_to :add_reference + + # Removes the reference(s). Also removes a +type+ column if one exists. + # <tt>remove_reference</tt>, <tt>remove_references</tt> and <tt>remove_belongs_to</tt> are acceptable. + # + # ====== Remove the reference + # remove_reference(:products, :user, index: true) + # + # ====== Remove polymorphic reference + # remove_reference(:products, :supplier, polymorphic: true) + # + def remove_reference(table_name, ref_name, options = {}) + remove_column(table_name, "#{ref_name}_id") + remove_column(table_name, "#{ref_name}_type") if options[:polymorphic] + end + alias :remove_belongs_to :remove_reference + # Returns a string of <tt>CREATE TABLE</tt> SQL statement(s) for recreating the # entire structure of the database. def structure_dump @@ -448,7 +489,7 @@ module ActiveRecord def dump_schema_information #:nodoc: sm_table = ActiveRecord::Migrator.schema_migrations_table_name - ActiveRecord::SchemaMigration.order('version').all.map { |sm| + ActiveRecord::SchemaMigration.order('version').map { |sm| "INSERT INTO #{sm_table} (version) VALUES ('#{sm.version}');" }.join "\n\n" end @@ -529,15 +570,15 @@ module ActiveRecord end # Adds timestamps (created_at and updated_at) columns to the named table. - # ===== Examples + # # add_timestamps(:suppliers) def add_timestamps(table_name) - add_column table_name, :created_at, :datetime, :null => false - add_column table_name, :updated_at, :datetime, :null => false + add_column table_name, :created_at, :datetime + add_column table_name, :updated_at, :datetime end # Removes the timestamp columns (created_at and updated_at) from the table definition. - # ===== Examples + # # remove_timestamps(:suppliers) def remove_timestamps(table_name) remove_column table_name, :updated_at @@ -549,7 +590,7 @@ module ActiveRecord if options.is_a?(Hash) && order = options[:order] case order when Hash - column_names.each {|name| option_strings[name] += " #{order[name].to_s.upcase}" if order.has_key?(name)} + column_names.each {|name| option_strings[name] += " #{order[name].upcase}" if order.has_key?(name)} when String column_names.each {|name| option_strings[name] += " #{order.upcase}"} end @@ -581,6 +622,9 @@ module ActiveRecord if Hash === options # legacy support, since this param was a string index_type = options[:unique] ? "UNIQUE" : "" index_name = options[:name].to_s if options.key?(:name) + if supports_partial_index? + index_options = options[:where] ? " WHERE #{options[:where]}" : "" + end else index_type = options end @@ -593,7 +637,7 @@ module ActiveRecord end index_columns = quoted_columns_for_index(column_names, options).join(", ") - [index_name, index_type, index_columns] + [index_name, index_type, index_columns, index_options] end def index_name_for_remove(table_name, options = {}) @@ -607,8 +651,6 @@ module ActiveRecord end def columns_for_remove(table_name, *column_names) - column_names = column_names.flatten - raise ArgumentError.new("You must specify at least one column name. Example: remove_column(:people, :first_name)") if column_names.blank? column_names.map {|column_name| quote_column_name(column_name) } end diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb index edea414db7..b3f9187429 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb @@ -2,7 +2,6 @@ require 'date' require 'bigdecimal' require 'bigdecimal/util' require 'active_support/core_ext/benchmark' -require 'active_support/deprecation' require 'active_record/connection_adapters/schema_cache' require 'monitor' @@ -13,21 +12,24 @@ module ActiveRecord autoload :Column autoload :ConnectionSpecification - autoload_under 'abstract' do - autoload :IndexDefinition, 'active_record/connection_adapters/abstract/schema_definitions' - autoload :ColumnDefinition, 'active_record/connection_adapters/abstract/schema_definitions' - autoload :TableDefinition, 'active_record/connection_adapters/abstract/schema_definitions' - autoload :Table, 'active_record/connection_adapters/abstract/schema_definitions' + autoload_at 'active_record/connection_adapters/abstract/schema_definitions' do + autoload :IndexDefinition + autoload :ColumnDefinition + autoload :TableDefinition + autoload :Table + end + autoload_at 'active_record/connection_adapters/abstract/connection_pool' do + autoload :ConnectionHandler + autoload :ConnectionManagement + end + + autoload_under 'abstract' do autoload :SchemaStatements autoload :DatabaseStatements autoload :DatabaseLimits autoload :Quoting - autoload :ConnectionPool - autoload :ConnectionHandler, 'active_record/connection_adapters/abstract/connection_pool' - autoload :ConnectionManagement, 'active_record/connection_adapters/abstract/connection_pool' - autoload :QueryCache end @@ -54,7 +56,7 @@ module ActiveRecord define_callbacks :checkout, :checkin attr_accessor :visitor, :pool - attr_reader :schema_cache, :last_use, :in_use + attr_reader :schema_cache, :last_use, :in_use, :logger alias :in_use? :in_use def initialize(connection, logger = nil, pool = nil) #:nodoc: @@ -83,6 +85,11 @@ module ActiveRecord end end + def schema_cache=(cache) + cache.connection = self + @schema_cache = cache + end + def expire @in_use = false end @@ -142,6 +149,11 @@ module ActiveRecord false end + # Does this adapter support partial indices? + def supports_partial_index? + false + end + # Does this adapter support explain? As of this writing sqlite3, # mysql2, and postgresql are the only ones that do. def supports_explain? @@ -150,15 +162,10 @@ module ActiveRecord # QUOTING ================================================== - # Override to return the quoted table name. Defaults to column quoting. - def quote_table_name(name) - quote_column_name(name) - end - # Returns a bind substitution value given a +column+ and list of current # +binds+ def substitute_at(column, index) - Arel.sql '?' + Arel::Nodes::BindParam.new '?' end # REFERENTIAL INTEGRITY ==================================== @@ -271,26 +278,25 @@ module ActiveRecord protected - def log(sql, name = "SQL", binds = []) - @instrumenter.instrument( - "sql.active_record", - :sql => sql, - :name => name, - :connection_id => object_id, - :binds => binds) { yield } - rescue Exception => e - message = "#{e.class.name}: #{e.message}: #{sql}" - @logger.debug message if @logger - exception = translate_exception(e, message) - exception.set_backtrace e.backtrace - raise exception - end - - def translate_exception(e, message) - # override in derived class - ActiveRecord::StatementInvalid.new(message) - end - + def log(sql, name = "SQL", binds = []) + @instrumenter.instrument( + "sql.active_record", + :sql => sql, + :name => name, + :connection_id => object_id, + :binds => binds) { yield } + rescue => e + message = "#{e.class.name}: #{e.message}: #{sql}" + @logger.error message if @logger + exception = translate_exception(e, message) + exception.set_backtrace e.backtrace + raise exception + end + + def translate_exception(exception, message) + # override in derived class + ActiveRecord::StatementInvalid.new(message) + end end end end diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb index e1dad5b166..1126fe7fce 100644 --- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb @@ -1,4 +1,4 @@ -require 'active_support/core_ext/object/blank' +require 'arel/visitors/bind_visitor' module ActiveRecord module ConnectionAdapters @@ -71,6 +71,8 @@ module ActiveRecord when /^mediumint/i; 3 when /^smallint/i; 2 when /^tinyint/i; 1 + when /^enum\((.+)\)/i + $1.split(',').map{|enum| enum.strip.length - 2}.max else super end @@ -122,12 +124,21 @@ module ActiveRecord :boolean => { :name => "tinyint", :limit => 1 } } + class BindSubstitution < Arel::Visitors::MySQL # :nodoc: + include Arel::Visitors::BindVisitor + end + # 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 = {}, {} - @visitor = Arel::Visitors::MySQL.new self + + if config.fetch(:prepared_statements) { true } + @visitor = Arel::Visitors::MySQL.new self + else + @visitor = BindSubstitution.new self + end end def adapter_name #:nodoc: @@ -241,7 +252,7 @@ module ActiveRecord 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 + # stuff in an 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) @@ -254,19 +265,19 @@ module ActiveRecord def begin_db_transaction execute "BEGIN" - rescue Exception + rescue # Transactions aren't supported end def commit_db_transaction #:nodoc: execute "COMMIT" - rescue Exception + rescue # Transactions aren't supported end def rollback_db_transaction #:nodoc: execute "ROLLBACK" - rescue Exception + rescue # Transactions aren't supported end @@ -284,19 +295,10 @@ module ActiveRecord # 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! + # these, we must use a subquery. 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) + super else update.table select.source update.wheres = select.constraints @@ -312,10 +314,10 @@ module ActiveRecord sql = "SHOW TABLES" end - select_all(sql).map { |table| + select_all(sql, 'SCHEMA').map { |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" + exec_query(sql, 'SCHEMA').first['Create Table'] + ";\n\n" }.join end @@ -365,7 +367,7 @@ module ActiveRecord def tables(name = nil, database = nil, like = nil) #:nodoc: sql = "SHOW TABLES " - sql << "IN #{database} " if database + sql << "IN #{quote_table_name(database)} " if database sql << "LIKE #{quote(like)}" if like execute_and_free(sql, 'SCHEMA') do |result| @@ -427,7 +429,7 @@ module ActiveRecord table, arguments = args.shift, args method = :"#{command}_sql" - if respond_to?(method) + if respond_to?(method, true) send(method, table, *arguments) else raise "Unknown method called : #{method}(#{arguments.inspect})" @@ -474,15 +476,26 @@ module ActiveRecord # 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}") + case type.to_s + when '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 + when 'text' + case limit + when 0..0xff; 'tinytext' + when nil, 0x100..0xffff; 'text' + when 0x10000..0xffffff; 'mediumtext' + when 0x1000000..0xffffffff; 'longtext' + else raise(ActiveRecordError, "No text type has character length #{limit}") + end + else + super end end @@ -496,7 +509,7 @@ module ActiveRecord # SHOW VARIABLES LIKE 'name' def show_variable(name) - variables = select_all("SHOW VARIABLES LIKE '#{name}'") + variables = select_all("SHOW VARIABLES LIKE '#{name}'", 'SCHEMA') variables.first['Value'] unless variables.empty? end @@ -504,8 +517,8 @@ module ActiveRecord def pk_and_sequence_for(table) execute_and_free("SHOW CREATE TABLE #{quote_table_name(table)}", 'SCHEMA') do |result| create_table = each_hash(result).first[:"Create Table"] - if create_table.to_s =~ /PRIMARY KEY\s+\((.+)\)/ - keys = $1.split(",").map { |key| key.gsub(/[`"]/, "") } + if create_table.to_s =~ /PRIMARY KEY\s+(?:USING\s+\w+\s+)?\((.+)\)/ + keys = $1.split(",").map { |key| key.delete('`"') } keys.length == 1 ? [keys.first, nil] : nil else nil @@ -537,6 +550,17 @@ module ActiveRecord protected + # MySQL is too stupid to create a temporary table for use subquery, so we have + # to give it some prompting in the form of a subsubquery. Ugh! + def subquery_for(key, select) + subsubselect = select.clone + subsubselect.projections = [key] + + subselect = Arel::SelectManager.new(select.engine) + subselect.project Arel.sql(key.name) + subselect.from subsubselect.as('__active_record_temp') + end + def add_index_length(option_strings, column_names, options = {}) if options.is_a?(Hash) && length = options[:length] case length @@ -607,7 +631,7 @@ module ActiveRecord 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"] + current_type = select_one("SHOW COLUMNS FROM #{quote_table_name(table_name)} LIKE '#{column_name}'", 'SCHEMA')["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 diff --git a/activerecord/lib/active_record/connection_adapters/column.rb b/activerecord/lib/active_record/connection_adapters/column.rb index 34d88edff3..0390168461 100644 --- a/activerecord/lib/active_record/connection_adapters/column.rb +++ b/activerecord/lib/active_record/connection_adapters/column.rb @@ -66,6 +66,10 @@ module ActiveRecord end end + def binary? + type == :binary + end + # Casts a Ruby value to something appropriate for writing to the database. def type_cast_for_write(value) return value unless number? @@ -90,7 +94,7 @@ module ActiveRecord case type when :string, :text then value - when :integer then value.to_i rescue value ? 1 : 0 + when :integer then value.to_i when :float then value.to_f when :decimal then klass.value_to_decimal(value) when :datetime, :timestamp then klass.string_to_time(value) @@ -98,12 +102,14 @@ module ActiveRecord when :date then klass.value_to_date(value) when :binary then klass.binary_to_string(value) when :boolean then klass.value_to_boolean(value) - when :hstore then klass.cast_hstore(value) else value end end def type_cast_code(var_name) + ActiveSupport::Deprecation.warn("Column#type_cast_code is deprecated in favor of" \ + "using Column#type_cast only, and it is going to be removed in future Rails versions.") + klass = self.class.name case type @@ -116,7 +122,9 @@ module ActiveRecord when :date then "#{klass}.value_to_date(#{var_name})" when :binary then "#{klass}.binary_to_string(#{var_name})" when :boolean then "#{klass}.value_to_boolean(#{var_name})" - when :hstore then "#{klass}.cast_hstore(#{var_name})" + when :hstore then "#{klass}.string_to_hstore(#{var_name})" + when :inet, :cidr then "#{klass}.string_to_cidr(#{var_name})" + when :json then "#{klass}.string_to_json(#{var_name})" else var_name end end @@ -151,7 +159,7 @@ module ActiveRecord def value_to_date(value) if value.is_a?(String) - return nil if value.empty? + return nil if value.blank? fast_string_to_date(value) || fallback_string_to_date(value) elsif value.respond_to?(:to_date) value.to_date @@ -162,16 +170,22 @@ module ActiveRecord def string_to_time(string) return string unless string.is_a?(String) - return nil if string.empty? + return nil if string.blank? fast_string_to_time(string) || fallback_string_to_time(string) end def string_to_dummy_time(string) return string unless string.is_a?(String) - return nil if string.empty? + return nil if string.blank? - string_to_time "2000-01-01 #{string}" + dummy_time_string = "2000-01-01 #{string}" + + fast_string_to_time(dummy_time_string) || begin + time_hash = Date._parse(dummy_time_string) + return nil if time_hash[:hour].nil? + new_time(*time_hash.values_at(:year, :mon, :mday, :hour, :min, :sec, :sec_fraction)) + end end # convert something to a boolean @@ -201,7 +215,7 @@ module ActiveRecord # '0.123456' -> 123456 # '1.123456' -> 123456 def microseconds(time) - ((time[:sec_fraction].to_f % 1) * 1_000_000).to_i + time[:sec_fraction] ? (time[:sec_fraction] * 1_000_000).to_i : 0 end def new_date(year, mon, mday) @@ -226,7 +240,7 @@ module ActiveRecord # Doesn't handle time zones. def fast_string_to_time(string) if string =~ Format::ISO_DATETIME - microsec = ($7.to_f * 1_000_000).to_i + microsec = ($7.to_r * 1_000_000).to_i new_time $1.to_i, $2.to_i, $3.to_i, $4.to_i, $5.to_i, $6.to_i, microsec end end diff --git a/activerecord/lib/active_record/connection_adapters/connection_specification.rb b/activerecord/lib/active_record/connection_adapters/connection_specification.rb index 8491d42b86..dd40351a38 100644 --- a/activerecord/lib/active_record/connection_adapters/connection_specification.rb +++ b/activerecord/lib/active_record/connection_adapters/connection_specification.rb @@ -1,3 +1,5 @@ +require 'uri' + module ActiveRecord module ConnectionAdapters class ConnectionSpecification #:nodoc: diff --git a/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb index c1332fde1a..8fc172f6e8 100644 --- a/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb @@ -32,6 +32,7 @@ module ActiveRecord def initialize(connection, logger, connection_options, config) super + @visitor = BindSubstitution.new self configure_connection end @@ -65,10 +66,6 @@ module ActiveRecord @connection.escape(string) end - def substitute_at(column, index) - Arel.sql "\0" - end - # CONNECTION MANAGEMENT ==================================== def active? @@ -80,6 +77,7 @@ module ActiveRecord disconnect! connect end + alias :reset! :reconnect! # Disconnects from the database if already connected. # Otherwise, this method does nothing. @@ -90,15 +88,10 @@ module ActiveRecord end end - def reset! - disconnect! - connect - end - # DATABASE STATEMENTS ====================================== def explain(arel, binds = []) - sql = "EXPLAIN #{to_sql(arel)}" + sql = "EXPLAIN #{to_sql(arel, binds.dup)}" start = Time.now result = exec_query(sql, 'EXPLAIN', binds) elapsed = Time.now - start @@ -224,8 +217,7 @@ module ActiveRecord # Returns an array of record hashes with the column names as keys and # column values as values. def select(sql, name = nil, binds = []) - binds = binds.dup - exec_query(sql.gsub("\0") { quote(*binds.shift.reverse) }, name) + exec_query(sql, name) end def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) @@ -234,18 +226,12 @@ module ActiveRecord end alias :create :insert_sql - def exec_insert(sql, name, binds) - binds = binds.dup - - # Pretend to support bind parameters - execute sql.gsub("\0") { quote(*binds.shift.reverse) }, name + def exec_insert(sql, name, binds, pk = nil, sequence_name = nil) + execute to_sql(sql, binds), name end def exec_delete(sql, name, binds) - binds = binds.dup - - # Pretend to support bind parameters - execute sql.gsub("\0") { quote(*binds.shift.reverse) }, name + execute to_sql(sql, binds), name @connection.affected_rows end alias :exec_update :exec_delete @@ -267,6 +253,14 @@ module ActiveRecord # By default, MySQL 'where id is null' selects the last inserted id. # Turn this off. http://dev.rubyonrails.org/ticket/6778 variable_assignments = ['SQL_AUTO_IS_NULL=0'] + + # Make MySQL reject illegal values rather than truncating or + # blanking them. See + # http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_strict_all_tables + if @config.fetch(:strict, true) + variable_assignments << "SQL_MODE='STRICT_ALL_TABLES'" + end + encoding = @config[:encoding] # make sure we set the encoding @@ -274,7 +268,7 @@ module ActiveRecord # increase timeout so mysql server doesn't disconnect us wait_timeout = @config[:wait_timeout] - wait_timeout = 2592000 unless wait_timeout.is_a?(Fixnum) + wait_timeout = 2147483 unless wait_timeout.is_a?(Fixnum) variable_assignments << "@@wait_timeout = #{wait_timeout}" execute("SET #{variable_assignments.join(', ')}", :skip_logging) diff --git a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb index 5905242747..6bf7af081f 100644 --- a/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/mysql_adapter.rb @@ -53,6 +53,7 @@ module ActiveRecord # * <tt>:database</tt> - The name of the database. No default, must be provided. # * <tt>:encoding</tt> - (Optional) Sets the client encoding by executing "SET NAMES <encoding>" after connection. # * <tt>:reconnect</tt> - Defaults to false (See MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html). + # * <tt>:strict</tt> - Defaults to true. Enable STRICT_ALL_TABLES. (See MySQL documentation: http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html) # * <tt>:sslca</tt> - Necessary to use MySQL with an SSL connection. # * <tt>:sslkey</tt> - Necessary to use MySQL with an SSL connection. # * <tt>:sslcert</tt> - Necessary to use MySQL with an SSL connection. @@ -119,7 +120,7 @@ module ActiveRecord private def cache - @cache[$$] + @cache[Process.pid] end end @@ -214,7 +215,7 @@ module ActiveRecord def select_rows(sql, name = nil) @connection.query_with_result = true - rows = exec_without_stmt(sql, name).rows + rows = exec_query(sql, name).rows @connection.more_results && @connection.next_result # invoking stored procedures with CLIENT_MULTI_RESULTS requires this to tidy up else connection will be dropped rows end @@ -278,31 +279,164 @@ module ActiveRecord end def exec_query(sql, name = 'SQL', binds = []) - log(sql, name, binds) do - exec_stmt(sql, name, binds) do |cols, stmt| - ActiveRecord::Result.new(cols, stmt.to_a) if cols - end + # If the configuration sets prepared_statements:false, binds will + # always be empty, since the bind variables will have been already + # substituted and removed from binds by BindVisitor, so this will + # effectively disable prepared statement usage completely. + if binds.empty? + result_set, affected_rows = exec_without_stmt(sql, name) + else + result_set, affected_rows = exec_stmt(sql, name, binds) end + + yield affected_rows if block_given? + + result_set end def last_inserted_id(result) @connection.insert_id end + module Fields + class Type + def type; end + + def type_cast_for_write(value) + value + end + end + + class Identity < Type + def type_cast(value); value; end + end + + class Integer < Type + def type_cast(value) + return if value.nil? + + value.to_i rescue value ? 1 : 0 + end + end + + class Date < Type + def type; :date; end + + def type_cast(value) + return if value.nil? + + # FIXME: probably we can improve this since we know it is mysql + # specific + ConnectionAdapters::Column.value_to_date value + end + end + + class DateTime < Type + def type; :datetime; end + + def type_cast(value) + return if value.nil? + + # FIXME: probably we can improve this since we know it is mysql + # specific + ConnectionAdapters::Column.string_to_time value + end + end + + class Time < Type + def type; :time; end + + def type_cast(value) + return if value.nil? + + # FIXME: probably we can improve this since we know it is mysql + # specific + ConnectionAdapters::Column.string_to_dummy_time value + end + end + + class Float < Type + def type; :float; end + + def type_cast(value) + return if value.nil? + + value.to_f + end + end + + class Decimal < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::Column.value_to_decimal value + end + end + + class Boolean < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::Column.value_to_boolean value + end + end + + TYPES = {} + + # Register an MySQL +type_id+ with a typcasting object in + # +type+. + def self.register_type(type_id, type) + TYPES[type_id] = type + end + + def self.alias_type(new, old) + TYPES[new] = TYPES[old] + end + + register_type Mysql::Field::TYPE_TINY, Fields::Boolean.new + register_type Mysql::Field::TYPE_LONG, Fields::Integer.new + alias_type Mysql::Field::TYPE_LONGLONG, Mysql::Field::TYPE_LONG + alias_type Mysql::Field::TYPE_NEWDECIMAL, Mysql::Field::TYPE_LONG + + register_type Mysql::Field::TYPE_VAR_STRING, Fields::Identity.new + register_type Mysql::Field::TYPE_BLOB, Fields::Identity.new + register_type Mysql::Field::TYPE_DATE, Fields::Date.new + register_type Mysql::Field::TYPE_DATETIME, Fields::DateTime.new + register_type Mysql::Field::TYPE_TIME, Fields::Time.new + register_type Mysql::Field::TYPE_FLOAT, Fields::Float.new + + Mysql::Field.constants.grep(/TYPE/).map { |class_name| + Mysql::Field.const_get class_name + }.reject { |const| TYPES.key? const }.each do |const| + register_type const, Fields::Identity.new + end + end + def exec_without_stmt(sql, name = 'SQL') # :nodoc: # Some queries, like SHOW CREATE TABLE don't work through the prepared # statement API. For those queries, we need to use this method. :'( log(sql, name) do result = @connection.query(sql) - cols = [] - rows = [] + affected_rows = @connection.affected_rows if result - cols = result.fetch_fields.map { |field| field.name } - rows = result.to_a + types = {} + result.fetch_fields.each { |field| + if field.decimals > 0 + types[field.name] = Fields::Decimal.new + else + types[field.name] = Fields::TYPES.fetch(field.type) { + Fields::Identity.new + } + end + } + result_set = ActiveRecord::Result.new(types.keys, result.to_a, types) result.free + else + result_set = ActiveRecord::Result.new([], []) end - ActiveRecord::Result.new(cols, rows) + + [result_set, affected_rows] end end @@ -320,16 +454,18 @@ module ActiveRecord alias :create :insert_sql def exec_delete(sql, name, binds) - log(sql, name, binds) do - exec_stmt(sql, name, binds) do |cols, stmt| - stmt.affected_rows - end + affected_rows = 0 + + exec_query(sql, name, binds) do |n| + affected_rows = n end + + affected_rows end alias :exec_update :exec_delete def begin_db_transaction #:nodoc: - exec_without_stmt "BEGIN" + exec_query "BEGIN" rescue Mysql::Error # Transactions aren't supported end @@ -338,41 +474,44 @@ module ActiveRecord def exec_stmt(sql, name, binds) cache = {} - if binds.empty? - stmt = @connection.prepare(sql) - else - cache = @statements[sql] ||= { - :stmt => @connection.prepare(sql) - } - stmt = cache[:stmt] - end + log(sql, name, binds) do + if binds.empty? + stmt = @connection.prepare(sql) + else + cache = @statements[sql] ||= { + :stmt => @connection.prepare(sql) + } + stmt = cache[:stmt] + end - begin - stmt.execute(*binds.map { |col, val| type_cast(val, col) }) - rescue Mysql::Error => e - # Older versions of MySQL leave the prepared statement in a bad - # place when an error occurs. To support older mysql versions, we - # need to close the statement and delete the statement from the - # cache. - stmt.close - @statements.delete sql - raise e - end + begin + stmt.execute(*binds.map { |col, val| type_cast(val, col) }) + rescue Mysql::Error => e + # Older versions of MySQL leave the prepared statement in a bad + # place when an error occurs. To support older mysql versions, we + # need to close the statement and delete the statement from the + # cache. + stmt.close + @statements.delete sql + raise e + end - cols = nil - if metadata = stmt.result_metadata - cols = cache[:cols] ||= metadata.fetch_fields.map { |field| - field.name - } - end + cols = nil + if metadata = stmt.result_metadata + cols = cache[:cols] ||= metadata.fetch_fields.map { |field| + field.name + } + end - result = yield [cols, stmt] + result_set = ActiveRecord::Result.new(cols, stmt.to_a) if cols + affected_rows = stmt.affected_rows - stmt.result_metadata.free if cols - stmt.free_result - stmt.close if binds.empty? + stmt.result_metadata.free if cols + stmt.free_result + stmt.close if binds.empty? - result + [result_set, affected_rows] + end end def connect @@ -404,6 +543,13 @@ module ActiveRecord # By default, MySQL 'where id is null' selects the last inserted id. # Turn this off. http://dev.rubyonrails.org/ticket/6778 execute("SET SQL_AUTO_IS_NULL=0", :skip_logging) + + # Make MySQL reject illegal values rather than truncating or + # blanking them. See + # http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_strict_all_tables + if @config.fetch(:strict, true) + execute("SET SQL_MODE='STRICT_ALL_TABLES'", :skip_logging) + end end def select(sql, name = nil, binds = []) diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb b/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb new file mode 100644 index 0000000000..b59195f98a --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/postgresql/cast.rb @@ -0,0 +1,96 @@ +module ActiveRecord + module ConnectionAdapters + class PostgreSQLColumn < Column + module Cast + def string_to_time(string) + return string unless String === string + + case string + when 'infinity'; 1.0 / 0.0 + when '-infinity'; -1.0 / 0.0 + else + super + end + end + + def hstore_to_string(object) + if Hash === object + object.map { |k,v| + "#{escape_hstore(k)}=>#{escape_hstore(v)}" + }.join ',' + else + object + end + end + + def string_to_hstore(string) + if string.nil? + nil + elsif String === string + Hash[string.scan(HstorePair).map { |k,v| + v = v.upcase == 'NULL' ? nil : v.gsub(/^"(.*)"$/,'\1').gsub(/\\(.)/, '\1') + k = k.gsub(/^"(.*)"$/,'\1').gsub(/\\(.)/, '\1') + [k,v] + }] + else + string + end + end + + def json_to_string(object) + if Hash === object + ActiveSupport::JSON.encode(object) + else + object + end + end + + def string_to_json(string) + if String === string + ActiveSupport::JSON.decode(string) + else + string + end + end + + def string_to_cidr(string) + if string.nil? + nil + elsif String === string + IPAddr.new(string) + else + string + end + end + + def cidr_to_string(object) + if IPAddr === object + "#{object.to_s}/#{object.instance_variable_get(:@mask_addr).to_s(2).count('1')}" + else + object + end + end + + private + + HstorePair = begin + quoted_string = /"[^"\\]*(?:\\.[^"\\]*)*"/ + unquoted_string = /(?:\\.|[^\s,])[^\s=,\\]*(?:\\.[^\s=,\\]*|=[^,>])*/ + /(#{quoted_string}|#{unquoted_string})\s*=>\s*(#{quoted_string}|#{unquoted_string})/ + end + + def escape_hstore(value) + if value.nil? + 'NULL' + else + if value == "" + '""' + else + '"%s"' % value.to_s.gsub(/(["\\])/, '\\\\\1') + end + end + end + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb new file mode 100644 index 0000000000..eb3084e066 --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb @@ -0,0 +1,234 @@ +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter < AbstractAdapter + module DatabaseStatements + def explain(arel, binds = []) + sql = "EXPLAIN #{to_sql(arel, binds)}" + ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds)) + end + + class ExplainPrettyPrinter # :nodoc: + # Pretty prints the result of a EXPLAIN in a way that resembles the output of the + # PostgreSQL shell: + # + # QUERY PLAN + # ------------------------------------------------------------------------------ + # Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0) + # Join Filter: (posts.user_id = users.id) + # -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4) + # Index Cond: (id = 1) + # -> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4) + # Filter: (posts.user_id = 1) + # (6 rows) + # + def pp(result) + header = result.columns.first + lines = result.rows.map(&:first) + + # We add 2 because there's one char of padding at both sides, note + # the extra hyphens in the example above. + width = [header, *lines].map(&:length).max + 2 + + pp = [] + + pp << header.center(width).rstrip + pp << '-' * width + + pp += lines.map {|line| " #{line}"} + + nrows = result.rows.length + rows_label = nrows == 1 ? 'row' : 'rows' + pp << "(#{nrows} #{rows_label})" + + pp.join("\n") + "\n" + end + end + + # Executes a SELECT query and returns an array of rows. Each row is an + # array of field values. + def select_rows(sql, name = nil) + select_raw(sql, name).last + end + + # Executes an INSERT query and returns the new record's ID + def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) + unless pk + # Extract the table from the insert sql. Yuck. + table_ref = extract_table_ref_from_insert_sql(sql) + pk = primary_key(table_ref) if table_ref + end + + if pk && use_insert_returning? + select_value("#{sql} RETURNING #{quote_column_name(pk)}") + elsif pk + super + last_insert_id_value(sequence_name || default_sequence_name(table_ref, pk)) + else + super + end + end + + def create + super.insert + end + + # create a 2D array representing the result set + def result_as_array(res) #:nodoc: + # check if we have any binary column and if they need escaping + ftypes = Array.new(res.nfields) do |i| + [i, res.ftype(i)] + end + + rows = res.values + return rows unless ftypes.any? { |_, x| + x == BYTEA_COLUMN_TYPE_OID || x == MONEY_COLUMN_TYPE_OID + } + + typehash = ftypes.group_by { |_, type| type } + binaries = typehash[BYTEA_COLUMN_TYPE_OID] || [] + monies = typehash[MONEY_COLUMN_TYPE_OID] || [] + + rows.each do |row| + # unescape string passed BYTEA field (OID == 17) + binaries.each do |index, _| + row[index] = unescape_bytea(row[index]) + end + + # If this is a money type column and there are any currency symbols, + # then strip them off. Indeed it would be prettier to do this in + # PostgreSQLColumn.string_to_decimal but would break form input + # fields that call value_before_type_cast. + monies.each do |index, _| + data = row[index] + # Because money output is formatted according to the locale, there are two + # cases to consider (note the decimal separators): + # (1) $12,345,678.12 + # (2) $12.345.678,12 + case data + when /^-?\D+[\d,]+\.\d{2}$/ # (1) + data.gsub!(/[^-\d.]/, '') + when /^-?\D+[\d.]+,\d{2}$/ # (2) + data.gsub!(/[^-\d,]/, '').sub!(/,/, '.') + end + end + end + end + + # Queries the database and returns the results in an Array-like object + def query(sql, name = nil) #:nodoc: + log(sql, name) do + result_as_array @connection.async_exec(sql) + end + end + + # Executes an SQL statement, returning a PGresult object on success + # or raising a PGError exception otherwise. + def execute(sql, name = nil) + log(sql, name) do + @connection.async_exec(sql) + end + end + + def substitute_at(column, index) + Arel::Nodes::BindParam.new "$#{index + 1}" + end + + def exec_query(sql, name = 'SQL', binds = []) + log(sql, name, binds) do + result = binds.empty? ? exec_no_cache(sql, binds) : + exec_cache(sql, binds) + + types = {} + result.fields.each_with_index do |fname, i| + ftype = result.ftype i + fmod = result.fmod i + types[fname] = OID::TYPE_MAP.fetch(ftype, fmod) { |oid, mod| + warn "unknown OID: #{fname}(#{oid}) (#{sql})" + OID::Identity.new + } + end + + ret = ActiveRecord::Result.new(result.fields, result.values, types) + result.clear + return ret + end + end + + def exec_delete(sql, name = 'SQL', binds = []) + log(sql, name, binds) do + result = binds.empty? ? exec_no_cache(sql, binds) : + exec_cache(sql, binds) + affected = result.cmd_tuples + result.clear + affected + end + end + alias :exec_update :exec_delete + + def sql_for_insert(sql, pk, id_value, sequence_name, binds) + unless pk + # Extract the table from the insert sql. Yuck. + table_ref = extract_table_ref_from_insert_sql(sql) + pk = primary_key(table_ref) if table_ref + end + + if pk && use_insert_returning? + sql = "#{sql} RETURNING #{quote_column_name(pk)}" + end + + [sql, binds] + end + + def exec_insert(sql, name, binds, pk = nil, sequence_name = nil) + val = exec_query(sql, name, binds) + if !use_insert_returning? && pk + unless sequence_name + table_ref = extract_table_ref_from_insert_sql(sql) + sequence_name = default_sequence_name(table_ref, pk) + return val unless sequence_name + end + last_insert_id_result(sequence_name) + else + val + end + end + + # Executes an UPDATE query and returns the number of affected tuples. + def update_sql(sql, name = nil) + super.cmd_tuples + end + + # Begins a transaction. + def begin_db_transaction + execute "BEGIN" + end + + # Commits a transaction. + def commit_db_transaction + execute "COMMIT" + end + + # Aborts a transaction. + def rollback_db_transaction + execute "ROLLBACK" + end + + def outside_transaction? + @connection.transaction_status == PGconn::PQTRANS_IDLE + 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 + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb new file mode 100644 index 0000000000..b8e7687b21 --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/postgresql/oid.rb @@ -0,0 +1,262 @@ +require 'active_record/connection_adapters/abstract_adapter' + +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter < AbstractAdapter + module OID + class Type + def type; end + + def type_cast_for_write(value) + value + end + end + + class Identity < Type + def type_cast(value) + value + end + end + + class Bytea < Type + def type_cast(value) + PGconn.unescape_bytea value + end + end + + class Money < Type + def type_cast(value) + return if value.nil? + + # Because money output is formatted according to the locale, there are two + # cases to consider (note the decimal separators): + # (1) $12,345,678.12 + # (2) $12.345.678,12 + + case value + when /^-?\D+[\d,]+\.\d{2}$/ # (1) + value.gsub!(/[^-\d.]/, '') + when /^-?\D+[\d.]+,\d{2}$/ # (2) + value.gsub!(/[^-\d,]/, '').sub!(/,/, '.') + end + + ConnectionAdapters::Column.value_to_decimal value + end + end + + class Vector < Type + attr_reader :delim, :subtype + + # +delim+ corresponds to the `typdelim` column in the pg_types + # table. +subtype+ is derived from the `typelem` column in the + # pg_types table. + def initialize(delim, subtype) + @delim = delim + @subtype = subtype + end + + # FIXME: this should probably split on +delim+ and use +subtype+ + # to cast the values. Unfortunately, the current Rails behavior + # is to just return the string. + def type_cast(value) + value + end + end + + class Integer < Type + def type_cast(value) + return if value.nil? + + value.to_i rescue value ? 1 : 0 + end + end + + class Boolean < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::Column.value_to_boolean value + end + end + + class Timestamp < Type + def type; :timestamp; end + + def type_cast(value) + return if value.nil? + + # FIXME: probably we can improve this since we know it is PG + # specific + ConnectionAdapters::PostgreSQLColumn.string_to_time value + end + end + + class Date < Type + def type; :datetime; end + + def type_cast(value) + return if value.nil? + + # FIXME: probably we can improve this since we know it is PG + # specific + ConnectionAdapters::Column.value_to_date value + end + end + + class Time < Type + def type_cast(value) + return if value.nil? + + # FIXME: probably we can improve this since we know it is PG + # specific + ConnectionAdapters::Column.string_to_dummy_time value + end + end + + class Float < Type + def type_cast(value) + return if value.nil? + + value.to_f + end + end + + class Decimal < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::Column.value_to_decimal value + end + end + + class Hstore < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::PostgreSQLColumn.string_to_hstore value + end + end + + class Cidr < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::PostgreSQLColumn.string_to_cidr value + end + end + + class Json < Type + def type_cast(value) + return if value.nil? + + ConnectionAdapters::PostgreSQLColumn.string_to_json value + end + end + + class TypeMap + def initialize + @mapping = {} + end + + def []=(oid, type) + @mapping[oid] = type + end + + def [](oid) + @mapping[oid] + end + + def key?(oid) + @mapping.key? oid + end + + def fetch(ftype, fmod) + # The type for the numeric depends on the width of the field, + # so we'll do something special here. + # + # When dealing with decimal columns: + # + # places after decimal = fmod - 4 & 0xffff + # places before decimal = (fmod - 4) >> 16 & 0xffff + if ftype == 1700 && (fmod - 4 & 0xffff).zero? + ftype = 23 + end + + @mapping.fetch(ftype) { |oid| yield oid, fmod } + end + end + + TYPE_MAP = TypeMap.new # :nodoc: + + # When the PG adapter connects, the pg_type table is queried. The + # key of this hash maps to the `typname` column from the table. + # TYPE_MAP is then dynamically built with oids as the key and type + # objects as values. + NAMES = Hash.new { |h,k| # :nodoc: + h[k] = OID::Identity.new + } + + # Register an OID type named +name+ with a typcasting object in + # +type+. +name+ should correspond to the `typname` column in + # the `pg_type` table. + def self.register_type(name, type) + NAMES[name] = type + end + + # Alias the +old+ type to the +new+ type. + def self.alias_type(new, old) + NAMES[new] = NAMES[old] + end + + # Is +name+ a registered type? + def self.registered_type?(name) + NAMES.key? name + end + + register_type 'int2', OID::Integer.new + alias_type 'int4', 'int2' + alias_type 'int8', 'int2' + alias_type 'oid', 'int2' + + register_type 'numeric', OID::Decimal.new + register_type 'text', OID::Identity.new + alias_type 'varchar', 'text' + alias_type 'char', 'text' + alias_type 'bpchar', 'text' + alias_type 'xml', 'text' + + # FIXME: why are we keeping these types as strings? + alias_type 'tsvector', 'text' + alias_type 'interval', 'text' + alias_type 'bit', 'text' + alias_type 'varbit', 'text' + alias_type 'macaddr', 'text' + alias_type 'uuid', 'text' + + # FIXME: I don't think this is correct. We should probably be returning a parsed date, + # but the tests pass with a string returned. + register_type 'timestamptz', OID::Identity.new + + register_type 'money', OID::Money.new + register_type 'bytea', OID::Bytea.new + register_type 'bool', OID::Boolean.new + + register_type 'float4', OID::Float.new + alias_type 'float8', 'float4' + + register_type 'timestamp', OID::Timestamp.new + register_type 'date', OID::Date.new + register_type 'time', OID::Time.new + + register_type 'path', OID::Identity.new + register_type 'polygon', OID::Identity.new + register_type 'circle', OID::Identity.new + register_type 'hstore', OID::Hstore.new + register_type 'json', OID::Json.new + + register_type 'cidr', OID::Cidr.new + alias_type 'inet', 'cidr' + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb b/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb new file mode 100644 index 0000000000..152258a2f4 --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/postgresql/quoting.rb @@ -0,0 +1,124 @@ +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter < AbstractAdapter + module Quoting + # Escapes binary strings for bytea input to the database. + def escape_bytea(value) + PGconn.escape_bytea(value) if value + end + + # Unescapes bytea output from a database to the binary string it represents. + # NOTE: This is NOT an inverse of escape_bytea! This is only to be used + # on escaped binary output from database drive. + def unescape_bytea(value) + PGconn.unescape_bytea(value) if value + end + + # Quotes PostgreSQL-specific data types for SQL input. + def quote(value, column = nil) #:nodoc: + return super unless column + + case value + when Hash + case column.sql_type + when 'hstore' then super(PostgreSQLColumn.hstore_to_string(value), column) + when 'json' then super(PostgreSQLColumn.json_to_string(value), column) + else super + end + when IPAddr + case column.sql_type + when 'inet', 'cidr' then super(PostgreSQLColumn.cidr_to_string(value), column) + else super + end + when Float + if value.infinite? && column.type == :datetime + "'#{value.to_s.downcase}'" + elsif value.infinite? || value.nan? + "'#{value.to_s}'" + else + super + end + when Numeric + return super unless column.sql_type == 'money' + # Not truly string input, so doesn't require (or allow) escape string syntax. + "'#{value}'" + when String + case column.sql_type + when 'bytea' then "'#{escape_bytea(value)}'" + when 'xml' then "xml '#{quote_string(value)}'" + when /^bit/ + case value + when /^[01]*$/ then "B'#{value}'" # Bit-string notation + when /^[0-9A-F]*$/i then "X'#{value}'" # Hexadecimal notation + end + else + super + end + else + super + end + end + + def type_cast(value, column) + return super unless column + + case value + when String + return super unless 'bytea' == column.sql_type + { :value => value, :format => 1 } + when Hash + case column.sql_type + when 'hstore' then PostgreSQLColumn.hstore_to_string(value) + when 'json' then PostgreSQLColumn.json_to_string(value) + else super + end + when IPAddr + return super unless ['inet','cidr'].includes? column.sql_type + PostgreSQLColumn.cidr_to_string(value) + else + super + end + end + + # Quotes strings for use in SQL input. + def quote_string(s) #:nodoc: + @connection.escape(s) + end + + # Checks the following cases: + # + # - table_name + # - "table.name" + # - schema_name.table_name + # - schema_name."table.name" + # - "schema.name".table_name + # - "schema.name"."table.name" + def quote_table_name(name) + schema, name_part = extract_pg_identifier_from_name(name.to_s) + + unless name_part + quote_column_name(schema) + else + table_name, name_part = extract_pg_identifier_from_name(name_part) + "#{quote_column_name(schema)}.#{quote_column_name(table_name)}" + end + end + + # Quotes column names for use in SQL queries. + def quote_column_name(name) #:nodoc: + PGconn.quote_ident(name.to_s) + end + + # Quote date/time values for use in SQL input. Includes microseconds + # if the value is a Time responding to usec. + def quoted_date(value) #:nodoc: + if value.acts_like?(:time) && value.respond_to?(:usec) + "#{super}.#{sprintf("%06d", value.usec)}" + else + super + end + end + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb b/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb new file mode 100644 index 0000000000..16da3ea732 --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb @@ -0,0 +1,22 @@ +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter < AbstractAdapter + module ReferentialIntegrity + def supports_disable_referential_integrity? #:nodoc: + true + end + + def disable_referential_integrity #:nodoc: + if supports_disable_referential_integrity? then + execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";")) + end + yield + ensure + if supports_disable_referential_integrity? then + execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";")) + end + end + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb new file mode 100644 index 0000000000..60f01c297e --- /dev/null +++ b/activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb @@ -0,0 +1,446 @@ +module ActiveRecord + module ConnectionAdapters + class PostgreSQLAdapter < AbstractAdapter + module SchemaStatements + # Drops the database specified on the +name+ attribute + # and creates it again using the provided +options+. + def recreate_database(name, options = {}) #:nodoc: + drop_database(name) + create_database(name, options) + end + + # Create a new PostgreSQL database. Options include <tt>:owner</tt>, <tt>:template</tt>, + # <tt>:encoding</tt>, <tt>:collation</tt>, <tt>:ctype</tt>, + # <tt>:tablespace</tt>, and <tt>:connection_limit</tt> (note that MySQL uses + # <tt>:charset</tt> while PostgreSQL uses <tt>:encoding</tt>). + # + # Example: + # create_database config[:database], config + # create_database 'foo_development', :encoding => 'unicode' + def create_database(name, options = {}) + options = options.reverse_merge(:encoding => "utf8") + + option_string = options.symbolize_keys.sum do |key, value| + case key + when :owner + " OWNER = \"#{value}\"" + when :template + " TEMPLATE = \"#{value}\"" + when :encoding + " ENCODING = '#{value}'" + when :collation + " LC_COLLATE = '#{value}'" + when :ctype + " LC_CTYPE = '#{value}'" + when :tablespace + " TABLESPACE = \"#{value}\"" + when :connection_limit + " CONNECTION LIMIT = #{value}" + else + "" + end + end + + execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" + end + + # Drops a PostgreSQL database. + # + # Example: + # drop_database 'matt_development' + def drop_database(name) #:nodoc: + execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}" + end + + # Returns the list of all tables in the schema search path or a specified schema. + def tables(name = nil) + query(<<-SQL, 'SCHEMA').map { |row| row[0] } + SELECT tablename + FROM pg_tables + WHERE schemaname = ANY (current_schemas(false)) + SQL + end + + # Returns true if table exists. + # If the schema is not specified as part of +name+ then it will only find tables within + # the current schema search path (regardless of permissions to access tables in other schemas) + def table_exists?(name) + schema, table = Utils.extract_schema_and_table(name.to_s) + return false unless table + + binds = [[nil, table]] + binds << [nil, schema] if schema + + exec_query(<<-SQL, 'SCHEMA').rows.first[0].to_i > 0 + SELECT COUNT(*) + FROM pg_class c + LEFT JOIN pg_namespace n ON n.oid = c.relnamespace + WHERE c.relkind in ('v','r') + AND c.relname = '#{table.gsub(/(^"|"$)/,'')}' + AND n.nspname = #{schema ? "'#{schema}'" : 'ANY (current_schemas(false))'} + SQL + end + + # Returns true if schema exists. + def schema_exists?(name) + exec_query(<<-SQL, 'SCHEMA').rows.first[0].to_i > 0 + SELECT COUNT(*) + FROM pg_namespace + WHERE nspname = '#{name}' + SQL + end + + # Returns an array of indexes for the given table. + def indexes(table_name, name = nil) + result = query(<<-SQL, 'SCHEMA') + SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid + FROM pg_class t + INNER JOIN pg_index d ON t.oid = d.indrelid + INNER JOIN pg_class i ON d.indexrelid = i.oid + WHERE i.relkind = 'i' + AND d.indisprimary = 'f' + AND t.relname = '#{table_name}' + AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) ) + ORDER BY i.relname + SQL + + result.map do |row| + index_name = row[0] + unique = row[1] == 't' + indkey = row[2].split(" ") + inddef = row[3] + oid = row[4] + + columns = Hash[query(<<-SQL, "Columns for index #{row[0]} on #{table_name}")] + SELECT a.attnum, a.attname + FROM pg_attribute a + WHERE a.attrelid = #{oid} + AND a.attnum IN (#{indkey.join(",")}) + SQL + + column_names = columns.values_at(*indkey).compact + + # add info on sort order for columns (only desc order is explicitly specified, asc is the default) + desc_order_columns = inddef.scan(/(\w+) DESC/).flatten + orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {} + where = inddef.scan(/WHERE (.+)$/).flatten[0] + + column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders, where) + end.compact + end + + # Returns the list of all column definitions for a table. + def columns(table_name) + # Limit, precision, and scale are all handled by the superclass. + column_definitions(table_name).map do |column_name, type, default, notnull, oid, fmod| + oid = OID::TYPE_MAP.fetch(oid.to_i, fmod.to_i) { + OID::Identity.new + } + PostgreSQLColumn.new(column_name, default, oid, type, notnull == 'f') + end + end + + # Returns the current database name. + def current_database + query('select current_database()', 'SCHEMA')[0][0] + end + + # Returns the current schema name. + def current_schema + query('SELECT current_schema', 'SCHEMA')[0][0] + end + + # Returns the current database encoding format. + def encoding + query(<<-end_sql, 'SCHEMA')[0][0] + SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database + WHERE pg_database.datname LIKE '#{current_database}' + end_sql + end + + # Returns the current database collation. + def collation + query(<<-end_sql, 'SCHEMA')[0][0] + SELECT pg_database.datcollate FROM pg_database WHERE pg_database.datname LIKE '#{current_database}' + end_sql + end + + # Returns the current database ctype. + def ctype + query(<<-end_sql, 'SCHEMA')[0][0] + SELECT pg_database.datctype FROM pg_database WHERE pg_database.datname LIKE '#{current_database}' + end_sql + end + + # Returns an array of schema names. + def schema_names + query(<<-SQL, 'SCHEMA').flatten + SELECT nspname + FROM pg_namespace + WHERE nspname !~ '^pg_.*' + AND nspname NOT IN ('information_schema') + ORDER by nspname; + SQL + end + + # Creates a schema for the given schema name. + def create_schema schema_name + execute "CREATE SCHEMA #{schema_name}" + end + + # Drops the schema for the given schema name. + def drop_schema schema_name + execute "DROP SCHEMA #{schema_name} CASCADE" + end + + # Sets the schema search path to a string of comma-separated schema names. + # Names beginning with $ have to be quoted (e.g. $user => '$user'). + # See: http://www.postgresql.org/docs/current/static/ddl-schemas.html + # + # This should be not be called manually but set in database.yml. + def schema_search_path=(schema_csv) + if schema_csv + execute("SET search_path TO #{schema_csv}", 'SCHEMA') + @schema_search_path = schema_csv + end + end + + # Returns the active schema search path. + def schema_search_path + @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0] + end + + # Returns the current client message level. + def client_min_messages + query('SHOW client_min_messages', 'SCHEMA')[0][0] + end + + # Set the client message level. + def client_min_messages=(level) + execute("SET client_min_messages TO '#{level}'", 'SCHEMA') + end + + # Returns the sequence name for a table's primary key or some other specified key. + def default_sequence_name(table_name, pk = nil) #:nodoc: + result = serial_sequence(table_name, pk || 'id') + return nil unless result + result.split('.').last + rescue ActiveRecord::StatementInvalid + "#{table_name}_#{pk || 'id'}_seq" + end + + def serial_sequence(table, column) + result = exec_query(<<-eosql, 'SCHEMA') + SELECT pg_get_serial_sequence('#{table}', '#{column}') + eosql + result.rows.first.first + end + + # Resets the sequence of a table's primary key to the maximum value. + def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc: + unless pk and sequence + default_pk, default_sequence = pk_and_sequence_for(table) + + pk ||= default_pk + sequence ||= default_sequence + end + + if @logger && pk && !sequence + @logger.warn "#{table} has primary key #{pk} with no default sequence" + end + + if pk && sequence + quoted_sequence = quote_table_name(sequence) + + select_value <<-end_sql, 'Reset sequence' + SELECT setval('#{quoted_sequence}', (SELECT COALESCE(MAX(#{quote_column_name pk})+(SELECT increment_by FROM #{quoted_sequence}), (SELECT min_value FROM #{quoted_sequence})) FROM #{quote_table_name(table)}), false) + end_sql + end + end + + # Returns a table's primary key and belonging sequence. + def pk_and_sequence_for(table) #:nodoc: + # First try looking for a sequence with a dependency on the + # given table's primary key. + result = query(<<-end_sql, 'PK and serial sequence')[0] + SELECT attr.attname, seq.relname + FROM pg_class seq, + pg_attribute attr, + pg_depend dep, + pg_namespace name, + pg_constraint cons + WHERE seq.oid = dep.objid + AND seq.relkind = 'S' + AND attr.attrelid = dep.refobjid + AND attr.attnum = dep.refobjsubid + AND attr.attrelid = cons.conrelid + AND attr.attnum = cons.conkey[1] + AND cons.contype = 'p' + AND dep.refobjid = '#{quote_table_name(table)}'::regclass + end_sql + + if result.nil? or result.empty? + # If that fails, try parsing the primary key's default value. + # Support the 7.x and 8.0 nextval('foo'::text) as well as + # the 8.1+ nextval('foo'::regclass). + result = query(<<-end_sql, 'PK and custom sequence')[0] + SELECT attr.attname, + CASE + WHEN split_part(def.adsrc, '''', 2) ~ '.' THEN + substr(split_part(def.adsrc, '''', 2), + strpos(split_part(def.adsrc, '''', 2), '.')+1) + ELSE split_part(def.adsrc, '''', 2) + END + FROM pg_class t + JOIN pg_attribute attr ON (t.oid = attrelid) + JOIN pg_attrdef def ON (adrelid = attrelid AND adnum = attnum) + JOIN pg_constraint cons ON (conrelid = adrelid AND adnum = conkey[1]) + WHERE t.oid = '#{quote_table_name(table)}'::regclass + AND cons.contype = 'p' + AND def.adsrc ~* 'nextval' + end_sql + end + + [result.first, result.last] + rescue + nil + end + + # Returns just a table's primary key + def primary_key(table) + row = exec_query(<<-end_sql, 'SCHEMA').rows.first + SELECT DISTINCT(attr.attname) + FROM pg_attribute attr + INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid + INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] + WHERE cons.contype = 'p' + AND dep.refobjid = '#{table}'::regclass + end_sql + + row && row.first + end + + # Renames a table. + # Also renames a table's primary key sequence if the sequence name matches the + # Active Record default. + # + # Example: + # rename_table('octopuses', 'octopi') + def rename_table(name, new_name) + clear_cache! + execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" + pk, seq = pk_and_sequence_for(new_name) + if seq == "#{name}_#{pk}_seq" + new_seq = "#{new_name}_#{pk}_seq" + execute "ALTER TABLE #{quote_table_name(seq)} RENAME TO #{quote_table_name(new_seq)}" + end + end + + # Adds a new column to the named table. + # See TableDefinition#column for details of the options you can use. + def add_column(table_name, column_name, type, options = {}) + clear_cache! + add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" + add_column_options!(add_column_sql, options) + + execute add_column_sql + end + + # Changes the column of a table. + def change_column(table_name, column_name, type, options = {}) + clear_cache! + quoted_table_name = quote_table_name(table_name) + + execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" + + change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) + change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) + end + + # Changes the default value of a table column. + def change_column_default(table_name, column_name, default) + clear_cache! + execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" + end + + def change_column_null(table_name, column_name, null, default = nil) + clear_cache! + 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 + execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") + end + + # Renames a column in a table. + def rename_column(table_name, column_name, new_column_name) + clear_cache! + execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" + end + + def remove_index!(table_name, index_name) #:nodoc: + execute "DROP INDEX #{quote_table_name(index_name)}" + end + + def rename_index(table_name, old_name, new_name) + execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}" + end + + def index_name_length + 63 + end + + # Maps logical Rails types to PostgreSQL-specific data types. + def type_to_sql(type, limit = nil, precision = nil, scale = nil) + case type.to_s + when 'binary' + # PostgreSQL doesn't support limits on binary (bytea) columns. + # The hard limit is 1Gb, because of a 32-bit size field, and TOAST. + case limit + when nil, 0..0x3fffffff; super(type) + else raise(ActiveRecordError, "No binary type has byte size #{limit}.") + end + when 'integer' + return 'integer' unless limit + + case limit + when 1, 2; 'smallint' + when 3, 4; 'integer' + when 5..8; 'bigint' + else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") + end + when 'datetime' + return super unless precision + + case precision + when 0..6; "timestamp(#{precision})" + else raise(ActiveRecordError, "No timestamp type has precision of #{precision}. The allowed range of precision is from 0 to 6") + end + else + super + end + end + + # Returns a SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause. + # + # PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and + # requires that the ORDER BY include the distinct column. + # + # distinct("posts.id", "posts.created_at desc") + def distinct(columns, orders) #:nodoc: + return "DISTINCT #{columns}" if orders.empty? + + # Construct a clean list of column names from the ORDER BY clause, removing + # any ASC/DESC modifiers + order_columns = orders.collect do |s| + s = s.to_sql unless s.is_a?(String) + s.gsub(/\s+(ASC|DESC)\s*(NULLS\s+(FIRST|LAST)\s*)?/i, '') + end + order_columns.delete_if { |c| c.blank? } + order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" } + + "DISTINCT #{columns}, #{order_columns * ', '}" + end + end + end + end +end diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb index 194c814e5b..c508afb33e 100644 --- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb @@ -1,11 +1,19 @@ require 'active_record/connection_adapters/abstract_adapter' -require 'active_support/core_ext/object/blank' require 'active_record/connection_adapters/statement_pool' +require 'active_record/connection_adapters/postgresql/oid' +require 'active_record/connection_adapters/postgresql/cast' +require 'active_record/connection_adapters/postgresql/quoting' +require 'active_record/connection_adapters/postgresql/schema_statements' +require 'active_record/connection_adapters/postgresql/database_statements' +require 'active_record/connection_adapters/postgresql/referential_integrity' +require 'arel/visitors/bind_visitor' # Make sure we're using pg high enough for PGResult#values gem 'pg', '~> 0.11' require 'pg' +require 'ipaddr' + module ActiveRecord module ConnectionHandling # Establishes a connection to the database that's used by all Active Record objects @@ -14,8 +22,8 @@ module ActiveRecord # Forward any unused config params to PGconn.connect. [:statement_limit, :encoding, :min_messages, :schema_search_path, - :schema_order, :adapter, :pool, :wait_timeout, :template, - :reaping_frequency].each do |key| + :schema_order, :adapter, :pool, :checkout_timeout, :template, + :reaping_frequency, :insert_returning].each do |key| conn_params.delete key end conn_params.delete_if { |k,v| v.nil? } @@ -34,67 +42,97 @@ module ActiveRecord # PostgreSQL-specific extensions to column definitions in a table. class PostgreSQLColumn < Column #:nodoc: # Instantiates a new PostgreSQL column definition in a table. - def initialize(name, default, sql_type = nil, null = true) + def initialize(name, default, oid_type, sql_type = nil, null = true) + @oid_type = oid_type super(name, self.class.extract_value_from_default(default), sql_type, null) end # :stopdoc: class << self - attr_accessor :money_precision - def string_to_time(string) - return string unless String === string + include ConnectionAdapters::PostgreSQLColumn::Cast - case string - when 'infinity' then 1.0 / 0.0 - when '-infinity' then -1.0 / 0.0 - else - super - end - end + attr_accessor :money_precision + end + # :startdoc: - def cast_hstore(object) - if Hash === object - object.map { |k,v| - "#{escape_hstore(k)}=>#{escape_hstore(v)}" - }.join ', ' + # Extracts the value from a PostgreSQL column default definition. + def self.extract_value_from_default(default) + # This is a performance optimization for Ruby 1.9.2 in development. + # If the value is nil, we return nil straight away without checking + # the regular expressions. If we check each regular expression, + # Regexp#=== will call NilClass#to_str, which will trigger + # method_missing (defined by whiny nil in ActiveSupport) which + # makes this method very very slow. + return default unless default + + case default + # Numeric types + when /\A\(?(-?\d+(\.\d*)?\)?)\z/ + $1 + # Character types + when /\A'(.*)'::(?:character varying|bpchar|text)\z/m + $1 + # Character types (8.1 formatting) + when /\AE'(.*)'::(?:character varying|bpchar|text)\z/m + $1.gsub(/\\(\d\d\d)/) { $1.oct.chr } + # Binary data types + when /\A'(.*)'::bytea\z/m + $1 + # Date/time types + when /\A'(.+)'::(?:time(?:stamp)? with(?:out)? time zone|date)\z/ + $1 + when /\A'(.*)'::interval\z/ + $1 + # Boolean type + when 'true' + true + when 'false' + false + # Geometric types + when /\A'(.*)'::(?:point|line|lseg|box|"?path"?|polygon|circle)\z/ + $1 + # Network address types + when /\A'(.*)'::(?:cidr|inet|macaddr)\z/ + $1 + # Bit string types + when /\AB'(.*)'::"?bit(?: varying)?"?\z/ + $1 + # XML type + when /\A'(.*)'::xml\z/m + $1 + # Arrays + when /\A'(.*)'::"?\D+"?\[\]\z/ + $1 + # Hstore + when /\A'(.*)'::hstore\z/ + $1 + # JSON + when /\A'(.*)'::json\z/ + $1 + # Object identifier types + when /\A-?\d+\z/ + $1 else - kvs = object.scan(/(?<!\\)".*?(?<!\\)"/).map { |o| - unescape_hstore(o[1...-1]) - } - Hash[kvs.each_slice(2).to_a] - end + # Anything else is blank, some user type, or some function + # and we can't know the value of that, so return nil. + nil end + end - private - HSTORE_ESCAPE = { - ' ' => '\\ ', - '\\' => '\\\\', - '"' => '\\"', - '=' => '\\=', - } - HSTORE_ESCAPE_RE = Regexp.union(HSTORE_ESCAPE.keys) - HSTORE_UNESCAPE = HSTORE_ESCAPE.invert - HSTORE_UNESCAPE_RE = Regexp.union(HSTORE_UNESCAPE.keys) - - def unescape_hstore(value) - value.gsub(HSTORE_UNESCAPE_RE) do |match| - HSTORE_UNESCAPE[match] - end - end + def type_cast(value) + return if value.nil? + return super if encoded? - def escape_hstore(value) - value.gsub(HSTORE_ESCAPE_RE) do |match| - HSTORE_ESCAPE[match] - end - end + @oid_type.type_cast value end - # :startdoc: private + def extract_limit(sql_type) case sql_type when /^bigint/i; 8 when /^smallint/i; 2 + when /^timestamp/i; nil else super end end @@ -109,6 +147,8 @@ module ActiveRecord def extract_precision(sql_type) if sql_type == 'money' self.class.money_precision + elsif sql_type =~ /timestamp/i + $1.to_i if sql_type =~ /\((\d+)\)/ else super end @@ -125,6 +165,13 @@ module ActiveRecord :decimal when 'hstore' :hstore + # Network address types + when 'inet' + :inet + when 'cidr' + :cidr + when 'macaddr' + :macaddr # Character types when /^(?:character varying|bpchar)(?:\(\d+\))?$/ :string @@ -139,9 +186,6 @@ module ActiveRecord # Geometric types when /^(?:point|line|lseg|box|"?path"?|polygon|circle)$/ :string - # Network address types - when /^(?:cidr|inet|macaddr)$/ - :string # Bit strings when /^bit(?: varying)?(?:\(\d+\))?$/ :string @@ -159,7 +203,10 @@ module ActiveRecord :integer # UUID type when 'uuid' - :string + :uuid + # JSON type + when 'json' + :json # Small and big integer types when /^(?:small|big)int$/ :integer @@ -168,64 +215,6 @@ module ActiveRecord super end end - - # Extracts the value from a PostgreSQL column default definition. - def self.extract_value_from_default(default) - # This is a performance optimization for Ruby 1.9.2 in development. - # If the value is nil, we return nil straight away without checking - # the regular expressions. If we check each regular expression, - # Regexp#=== will call NilClass#to_str, which will trigger - # method_missing (defined by whiny nil in ActiveSupport) which - # makes this method very very slow. - return default unless default - - case default - # Numeric types - when /\A\(?(-?\d+(\.\d*)?\)?)\z/ - $1 - # Character types - when /\A'(.*)'::(?:character varying|bpchar|text)\z/m - $1 - # Character types (8.1 formatting) - when /\AE'(.*)'::(?:character varying|bpchar|text)\z/m - $1.gsub(/\\(\d\d\d)/) { $1.oct.chr } - # Binary data types - when /\A'(.*)'::bytea\z/m - $1 - # Date/time types - when /\A'(.+)'::(?:time(?:stamp)? with(?:out)? time zone|date)\z/ - $1 - when /\A'(.*)'::interval\z/ - $1 - # Boolean type - when 'true' - true - when 'false' - false - # Geometric types - when /\A'(.*)'::(?:point|line|lseg|box|"?path"?|polygon|circle)\z/ - $1 - # Network address types - when /\A'(.*)'::(?:cidr|inet|macaddr)\z/ - $1 - # Bit string types - when /\AB'(.*)'::"?bit(?: varying)?"?\z/ - $1 - # XML type - when /\A'(.*)'::xml\z/m - $1 - # Arrays - when /\A'(.*)'::"?\D+"?\[\]\z/ - $1 - # Object identifier types - when /\A-?\d+\z/ - $1 - else - # Anything else is blank, some user type, or some function - # and we can't know the value of that, so return nil. - nil - end - end end # The PostgreSQL adapter works with the native C (https://bitbucket.org/ged/ruby-pg) driver. @@ -244,6 +233,8 @@ module ActiveRecord # <encoding></tt> call on the connection. # * <tt>:min_messages</tt> - An optional client min messages that is used in a # <tt>SET client_min_messages TO <min_messages></tt> call on the connection. + # * <tt>:insert_returning</tt> - An optional boolean to control the use or <tt>RETURNING</tt> for <tt>INSERT<tt> statements + # defaults to true. # # Any further options are used as connection parameters to libpq. See # http://www.postgresql.org/docs/9.1/static/libpq-connect.html for the @@ -266,27 +257,58 @@ module ActiveRecord def hstore(name, options = {}) column(name, 'hstore', options) end + + def inet(name, options = {}) + column(name, 'inet', options) + end + + def cidr(name, options = {}) + column(name, 'cidr', options) + end + + def macaddr(name, options = {}) + column(name, 'macaddr', options) + end + + def uuid(name, options = {}) + column(name, 'uuid', options) + end + + def json(name, options = {}) + column(name, 'json', options) + end end ADAPTER_NAME = 'PostgreSQL' NATIVE_DATABASE_TYPES = { - :primary_key => "serial primary key", - :string => { :name => "character varying", :limit => 255 }, - :text => { :name => "text" }, - :integer => { :name => "integer" }, - :float => { :name => "float" }, - :decimal => { :name => "decimal" }, - :datetime => { :name => "timestamp" }, - :timestamp => { :name => "timestamp" }, - :time => { :name => "time" }, - :date => { :name => "date" }, - :binary => { :name => "bytea" }, - :boolean => { :name => "boolean" }, - :xml => { :name => "xml" }, - :tsvector => { :name => "tsvector" } + primary_key: "serial primary key", + string: { name: "character varying", limit: 255 }, + text: { name: "text" }, + integer: { name: "integer" }, + float: { name: "float" }, + decimal: { name: "decimal" }, + datetime: { name: "timestamp" }, + timestamp: { name: "timestamp" }, + time: { name: "time" }, + date: { name: "date" }, + binary: { name: "bytea" }, + boolean: { name: "boolean" }, + xml: { name: "xml" }, + tsvector: { name: "tsvector" }, + hstore: { name: "hstore" }, + inet: { name: "inet" }, + cidr: { name: "cidr" }, + macaddr: { name: "macaddr" }, + uuid: { name: "uuid" }, + json: { name: "json" } } + include Quoting + include ReferentialIntegrity + include SchemaStatements + include DatabaseStatements + # Returns 'PostgreSQL' as adapter name for identification purposes. def adapter_name ADAPTER_NAME @@ -302,6 +324,10 @@ module ActiveRecord true end + def supports_partial_index? + true + end + class StatementPool < ConnectionAdapters::StatementPool def initialize(connection, max) super @@ -339,26 +365,39 @@ module ActiveRecord end private - def cache - @cache[$$] - end - def dealloc(key) - @connection.query "DEALLOCATE #{key}" if connection_active? - end + def cache + @cache[Process.pid] + end - def connection_active? - @connection.status == PGconn::CONNECTION_OK - rescue PGError - false - end + def dealloc(key) + @connection.query "DEALLOCATE #{key}" if connection_active? + end + + def connection_active? + @connection.status == PGconn::CONNECTION_OK + rescue PGError + false + end + end + + class BindSubstitution < Arel::Visitors::PostgreSQL # :nodoc: + include Arel::Visitors::BindVisitor end # Initializes and connects a PostgreSQL adapter. def initialize(connection, logger, connection_parameters, config) super(connection, logger) + + if config.fetch(:prepared_statements) { true } + @visitor = Arel::Visitors::PostgreSQL.new self + else + @visitor = BindSubstitution.new self + end + + connection_parameters.delete :prepared_statements + @connection_parameters, @config = connection_parameters, config - @visitor = Arel::Visitors::PostgreSQL.new self # @local_tz is initialized as nil to avoid warnings when connect tries to use it @local_tz = nil @@ -372,7 +411,9 @@ module ActiveRecord raise "Your version of PostgreSQL (#{postgresql_version}) is too old, please upgrade!" end + initialize_type_map @local_tz = execute('SHOW TIME ZONE', 'SCHEMA').first["TimeZone"] + @use_insert_returning = @config.key?(:insert_returning) ? @config[:insert_returning] : true end # Clears the prepared statements cache. @@ -382,7 +423,8 @@ module ActiveRecord # Is this connection alive and ready for queries? def active? - @connection.status == PGconn::CONNECTION_OK + @connection.query 'SELECT 1' + true rescue PGError false end @@ -391,6 +433,7 @@ module ActiveRecord def reconnect! clear_cache! @connection.reset + @open_transactions = 0 configure_connection end @@ -448,101 +491,7 @@ module ActiveRecord # Returns the configured supported identifier length supported by PostgreSQL def table_alias_length - @table_alias_length ||= query('SHOW max_identifier_length')[0][0].to_i - end - - # QUOTING ================================================== - - # Escapes binary strings for bytea input to the database. - def escape_bytea(value) - PGconn.escape_bytea(value) if value - end - - # Unescapes bytea output from a database to the binary string it represents. - # NOTE: This is NOT an inverse of escape_bytea! This is only to be used - # on escaped binary output from database drive. - def unescape_bytea(value) - PGconn.unescape_bytea(value) if value - end - - # Quotes PostgreSQL-specific data types for SQL input. - def quote(value, column = nil) #:nodoc: - return super unless column - - case value - when Float - return super unless value.infinite? && column.type == :datetime - "'#{value.to_s.downcase}'" - when Numeric - return super unless column.sql_type == 'money' - # Not truly string input, so doesn't require (or allow) escape string syntax. - "'#{value}'" - when String - case column.sql_type - when 'bytea' then "'#{escape_bytea(value)}'" - when 'xml' then "xml '#{quote_string(value)}'" - when /^bit/ - case value - when /^[01]*$/ then "B'#{value}'" # Bit-string notation - when /^[0-9A-F]*$/i then "X'#{value}'" # Hexadecimal notation - end - else - super - end - else - super - end - end - - def type_cast(value, column) - return super unless column - - case value - when String - return super unless 'bytea' == column.sql_type - { :value => value, :format => 1 } - else - super - end - end - - # Quotes strings for use in SQL input. - def quote_string(s) #:nodoc: - @connection.escape(s) - end - - # Checks the following cases: - # - # - table_name - # - "table.name" - # - schema_name.table_name - # - schema_name."table.name" - # - "schema.name".table_name - # - "schema.name"."table.name" - def quote_table_name(name) - schema, name_part = extract_pg_identifier_from_name(name.to_s) - - unless name_part - quote_column_name(schema) - else - table_name, name_part = extract_pg_identifier_from_name(name_part) - "#{quote_column_name(schema)}.#{quote_column_name(table_name)}" - end - end - - # Quotes column names for use in SQL queries. - def quote_column_name(name) #:nodoc: - PGconn.quote_ident(name.to_s) - end - - # Quote date/time values for use in SQL input. Includes microseconds - # if the value is a Time responding to usec. - def quoted_date(value) #:nodoc: - if value.acts_like?(:time) && value.respond_to?(:usec) - "#{super}.#{sprintf("%06d", value.usec)}" - else - super - end + @table_alias_length ||= query('SHOW max_identifier_length', 'SCHEMA')[0][0].to_i end # Set the authorized user for this session @@ -551,569 +500,6 @@ module ActiveRecord exec_query "SET SESSION AUTHORIZATION #{user}" end - # REFERENTIAL INTEGRITY ==================================== - - def supports_disable_referential_integrity? #:nodoc: - true - end - - def disable_referential_integrity #:nodoc: - if supports_disable_referential_integrity? then - execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} DISABLE TRIGGER ALL" }.join(";")) - end - yield - ensure - if supports_disable_referential_integrity? then - execute(tables.collect { |name| "ALTER TABLE #{quote_table_name(name)} ENABLE TRIGGER ALL" }.join(";")) - end - end - - # DATABASE STATEMENTS ====================================== - - def explain(arel, binds = []) - sql = "EXPLAIN #{to_sql(arel)}" - ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds)) - end - - class ExplainPrettyPrinter # :nodoc: - # Pretty prints the result of a EXPLAIN in a way that resembles the output of the - # PostgreSQL shell: - # - # QUERY PLAN - # ------------------------------------------------------------------------------ - # Nested Loop Left Join (cost=0.00..37.24 rows=8 width=0) - # Join Filter: (posts.user_id = users.id) - # -> Index Scan using users_pkey on users (cost=0.00..8.27 rows=1 width=4) - # Index Cond: (id = 1) - # -> Seq Scan on posts (cost=0.00..28.88 rows=8 width=4) - # Filter: (posts.user_id = 1) - # (6 rows) - # - def pp(result) - header = result.columns.first - lines = result.rows.map(&:first) - - # We add 2 because there's one char of padding at both sides, note - # the extra hyphens in the example above. - width = [header, *lines].map(&:length).max + 2 - - pp = [] - - pp << header.center(width).rstrip - pp << '-' * width - - pp += lines.map {|line| " #{line}"} - - nrows = result.rows.length - rows_label = nrows == 1 ? 'row' : 'rows' - pp << "(#{nrows} #{rows_label})" - - pp.join("\n") + "\n" - end - end - - # Executes a SELECT query and returns an array of rows. Each row is an - # array of field values. - def select_rows(sql, name = nil) - select_raw(sql, name).last - end - - # Executes an INSERT query and returns the new record's ID - def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) - unless pk - # Extract the table from the insert sql. Yuck. - table_ref = extract_table_ref_from_insert_sql(sql) - pk = primary_key(table_ref) if table_ref - end - - if pk - select_value("#{sql} RETURNING #{quote_column_name(pk)}") - else - super - end - end - alias :create :insert - - # create a 2D array representing the result set - def result_as_array(res) #:nodoc: - # check if we have any binary column and if they need escaping - ftypes = Array.new(res.nfields) do |i| - [i, res.ftype(i)] - end - - rows = res.values - return rows unless ftypes.any? { |_, x| - x == BYTEA_COLUMN_TYPE_OID || x == MONEY_COLUMN_TYPE_OID - } - - typehash = ftypes.group_by { |_, type| type } - binaries = typehash[BYTEA_COLUMN_TYPE_OID] || [] - monies = typehash[MONEY_COLUMN_TYPE_OID] || [] - - rows.each do |row| - # unescape string passed BYTEA field (OID == 17) - binaries.each do |index, _| - row[index] = unescape_bytea(row[index]) - end - - # If this is a money type column and there are any currency symbols, - # then strip them off. Indeed it would be prettier to do this in - # PostgreSQLColumn.string_to_decimal but would break form input - # fields that call value_before_type_cast. - monies.each do |index, _| - data = row[index] - # Because money output is formatted according to the locale, there are two - # cases to consider (note the decimal separators): - # (1) $12,345,678.12 - # (2) $12.345.678,12 - case data - when /^-?\D+[\d,]+\.\d{2}$/ # (1) - data.gsub!(/[^-\d.]/, '') - when /^-?\D+[\d.]+,\d{2}$/ # (2) - data.gsub!(/[^-\d,]/, '').sub!(/,/, '.') - end - end - end - end - - - # Queries the database and returns the results in an Array-like object - def query(sql, name = nil) #:nodoc: - log(sql, name) do - result_as_array @connection.async_exec(sql) - end - end - - # Executes an SQL statement, returning a PGresult object on success - # or raising a PGError exception otherwise. - def execute(sql, name = nil) - log(sql, name) do - @connection.async_exec(sql) - end - end - - def substitute_at(column, index) - Arel.sql("$#{index + 1}") - end - - def exec_query(sql, name = 'SQL', binds = []) - log(sql, name, binds) do - result = binds.empty? ? exec_no_cache(sql, binds) : - exec_cache(sql, binds) - - ret = ActiveRecord::Result.new(result.fields, result_as_array(result)) - result.clear - return ret - end - end - - def exec_delete(sql, name = 'SQL', binds = []) - log(sql, name, binds) do - result = binds.empty? ? exec_no_cache(sql, binds) : - exec_cache(sql, binds) - affected = result.cmd_tuples - result.clear - affected - end - end - alias :exec_update :exec_delete - - def sql_for_insert(sql, pk, id_value, sequence_name, binds) - unless pk - # Extract the table from the insert sql. Yuck. - table_ref = extract_table_ref_from_insert_sql(sql) - pk = primary_key(table_ref) if table_ref - end - - sql = "#{sql} RETURNING #{quote_column_name(pk)}" if pk - - [sql, binds] - end - - # Executes an UPDATE query and returns the number of affected tuples. - def update_sql(sql, name = nil) - super.cmd_tuples - end - - # Begins a transaction. - def begin_db_transaction - execute "BEGIN" - end - - # Commits a transaction. - def commit_db_transaction - execute "COMMIT" - end - - # Aborts a transaction. - def rollback_db_transaction - execute "ROLLBACK" - end - - def outside_transaction? - @connection.transaction_status == PGconn::PQTRANS_IDLE - 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 - - # SCHEMA STATEMENTS ======================================== - - # Drops the database specified on the +name+ attribute - # and creates it again using the provided +options+. - def recreate_database(name, options = {}) #:nodoc: - drop_database(name) - create_database(name, options) - end - - # Create a new PostgreSQL database. Options include <tt>:owner</tt>, <tt>:template</tt>, - # <tt>:encoding</tt>, <tt>:tablespace</tt>, and <tt>:connection_limit</tt> (note that MySQL uses - # <tt>:charset</tt> while PostgreSQL uses <tt>:encoding</tt>). - # - # Example: - # create_database config[:database], config - # create_database 'foo_development', :encoding => 'unicode' - def create_database(name, options = {}) - options = options.reverse_merge(:encoding => "utf8") - - option_string = options.symbolize_keys.sum do |key, value| - case key - when :owner - " OWNER = \"#{value}\"" - when :template - " TEMPLATE = \"#{value}\"" - when :encoding - " ENCODING = '#{value}'" - when :tablespace - " TABLESPACE = \"#{value}\"" - when :connection_limit - " CONNECTION LIMIT = #{value}" - else - "" - end - end - - execute "CREATE DATABASE #{quote_table_name(name)}#{option_string}" - end - - # Drops a PostgreSQL database. - # - # Example: - # drop_database 'matt_development' - def drop_database(name) #:nodoc: - execute "DROP DATABASE IF EXISTS #{quote_table_name(name)}" - end - - # Returns the list of all tables in the schema search path or a specified schema. - def tables(name = nil) - query(<<-SQL, 'SCHEMA').map { |row| row[0] } - SELECT tablename - FROM pg_tables - WHERE schemaname = ANY (current_schemas(false)) - SQL - end - - # Returns true if table exists. - # If the schema is not specified as part of +name+ then it will only find tables within - # the current schema search path (regardless of permissions to access tables in other schemas) - def table_exists?(name) - schema, table = Utils.extract_schema_and_table(name.to_s) - return false unless table - - binds = [[nil, table]] - binds << [nil, schema] if schema - - exec_query(<<-SQL, 'SCHEMA', binds).rows.first[0].to_i > 0 - SELECT COUNT(*) - FROM pg_class c - LEFT JOIN pg_namespace n ON n.oid = c.relnamespace - WHERE c.relkind in ('v','r') - AND c.relname = $1 - AND n.nspname = #{schema ? '$2' : 'ANY (current_schemas(false))'} - SQL - end - - # Returns true if schema exists. - def schema_exists?(name) - exec_query(<<-SQL, 'SCHEMA', [[nil, name]]).rows.first[0].to_i > 0 - SELECT COUNT(*) - FROM pg_namespace - WHERE nspname = $1 - SQL - end - - # Returns an array of indexes for the given table. - def indexes(table_name, name = nil) - result = query(<<-SQL, name) - SELECT distinct i.relname, d.indisunique, d.indkey, pg_get_indexdef(d.indexrelid), t.oid - FROM pg_class t - INNER JOIN pg_index d ON t.oid = d.indrelid - INNER JOIN pg_class i ON d.indexrelid = i.oid - WHERE i.relkind = 'i' - AND d.indisprimary = 'f' - AND t.relname = '#{table_name}' - AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) ) - ORDER BY i.relname - SQL - - - result.map do |row| - index_name = row[0] - unique = row[1] == 't' - indkey = row[2].split(" ") - inddef = row[3] - oid = row[4] - - columns = Hash[query(<<-SQL, "Columns for index #{row[0]} on #{table_name}")] - SELECT a.attnum, a.attname - FROM pg_attribute a - WHERE a.attrelid = #{oid} - AND a.attnum IN (#{indkey.join(",")}) - SQL - - column_names = columns.values_at(*indkey).compact - - # add info on sort order for columns (only desc order is explicitly specified, asc is the default) - desc_order_columns = inddef.scan(/(\w+) DESC/).flatten - orders = desc_order_columns.any? ? Hash[desc_order_columns.map {|order_column| [order_column, :desc]}] : {} - - column_names.empty? ? nil : IndexDefinition.new(table_name, index_name, unique, column_names, [], orders) - end.compact - end - - # Returns the list of all column definitions for a table. - def columns(table_name) - # Limit, precision, and scale are all handled by the superclass. - column_definitions(table_name).collect do |column_name, type, default, notnull| - PostgreSQLColumn.new(column_name, default, type, notnull == 'f') - end - end - - # Returns the current database name. - def current_database - query('select current_database()')[0][0] - end - - # Returns the current schema name. - def current_schema - query('SELECT current_schema', 'SCHEMA')[0][0] - end - - # Returns the current database encoding format. - def encoding - query(<<-end_sql)[0][0] - SELECT pg_encoding_to_char(pg_database.encoding) FROM pg_database - WHERE pg_database.datname LIKE '#{current_database}' - end_sql - end - - # Sets the schema search path to a string of comma-separated schema names. - # Names beginning with $ have to be quoted (e.g. $user => '$user'). - # See: http://www.postgresql.org/docs/current/static/ddl-schemas.html - # - # This should be not be called manually but set in database.yml. - def schema_search_path=(schema_csv) - if schema_csv - execute "SET search_path TO #{schema_csv}" - @schema_search_path = schema_csv - end - end - - # Returns the active schema search path. - def schema_search_path - @schema_search_path ||= query('SHOW search_path', 'SCHEMA')[0][0] - end - - # Returns the current client message level. - def client_min_messages - query('SHOW client_min_messages', 'SCHEMA')[0][0] - end - - # Set the client message level. - def client_min_messages=(level) - execute("SET client_min_messages TO '#{level}'", 'SCHEMA') - end - - # Returns the sequence name for a table's primary key or some other specified key. - def default_sequence_name(table_name, pk = nil) #:nodoc: - serial_sequence(table_name, pk || 'id').split('.').last - rescue ActiveRecord::StatementInvalid - "#{table_name}_#{pk || 'id'}_seq" - end - - def serial_sequence(table, column) - result = exec_query(<<-eosql, 'SCHEMA', [[nil, table], [nil, column]]) - SELECT pg_get_serial_sequence($1, $2) - eosql - result.rows.first.first - end - - # Resets the sequence of a table's primary key to the maximum value. - def reset_pk_sequence!(table, pk = nil, sequence = nil) #:nodoc: - unless pk and sequence - default_pk, default_sequence = pk_and_sequence_for(table) - - pk ||= default_pk - sequence ||= default_sequence - end - - if @logger && pk && !sequence - @logger.warn "#{table} has primary key #{pk} with no default sequence" - end - - if pk && sequence - quoted_sequence = quote_table_name(sequence) - - select_value <<-end_sql, 'Reset sequence' - SELECT setval('#{quoted_sequence}', (SELECT COALESCE(MAX(#{quote_column_name pk})+(SELECT increment_by FROM #{quoted_sequence}), (SELECT min_value FROM #{quoted_sequence})) FROM #{quote_table_name(table)}), false) - end_sql - end - end - - # Returns a table's primary key and belonging sequence. - def pk_and_sequence_for(table) #:nodoc: - # First try looking for a sequence with a dependency on the - # given table's primary key. - result = exec_query(<<-end_sql, 'SCHEMA').rows.first - SELECT attr.attname, ns.nspname, seq.relname - FROM pg_class seq - INNER JOIN pg_depend dep ON seq.oid = dep.objid - INNER JOIN pg_attribute attr ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid - INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] - INNER JOIN pg_namespace ns ON seq.relnamespace = ns.oid - WHERE seq.relkind = 'S' - AND cons.contype = 'p' - AND dep.refobjid = '#{quote_table_name(table)}'::regclass - end_sql - - # [primary_key, sequence] - if result.second == 'public' then - sequence = result.last - else - sequence = result.second+'.'+result.last - end - - [result.first, sequence] - rescue - nil - end - - # Returns just a table's primary key - def primary_key(table) - row = exec_query(<<-end_sql, 'SCHEMA', [[nil, table]]).rows.first - SELECT DISTINCT(attr.attname) - FROM pg_attribute attr - INNER JOIN pg_depend dep ON attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid - INNER JOIN pg_constraint cons ON attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] - WHERE cons.contype = 'p' - AND dep.refobjid = $1::regclass - end_sql - - row && row.first - end - - # Renames a table. - # - # Example: - # rename_table('octopuses', 'octopi') - def rename_table(name, new_name) - clear_cache! - execute "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" - end - - # Adds a new column to the named table. - # See TableDefinition#column for details of the options you can use. - def add_column(table_name, column_name, type, options = {}) - clear_cache! - add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD COLUMN #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" - add_column_options!(add_column_sql, options) - - execute add_column_sql - end - - # Changes the column of a table. - def change_column(table_name, column_name, type, options = {}) - clear_cache! - quoted_table_name = quote_table_name(table_name) - - execute "ALTER TABLE #{quoted_table_name} ALTER COLUMN #{quote_column_name(column_name)} TYPE #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}" - - change_column_default(table_name, column_name, options[:default]) if options_include_default?(options) - change_column_null(table_name, column_name, options[:null], options[:default]) if options.key?(:null) - end - - # Changes the default value of a table column. - def change_column_default(table_name, column_name, default) - clear_cache! - execute "ALTER TABLE #{quote_table_name(table_name)} ALTER COLUMN #{quote_column_name(column_name)} SET DEFAULT #{quote(default)}" - end - - def change_column_null(table_name, column_name, null, default = nil) - clear_cache! - 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 - execute("ALTER TABLE #{quote_table_name(table_name)} ALTER #{quote_column_name(column_name)} #{null ? 'DROP' : 'SET'} NOT NULL") - end - - # Renames a column in a table. - def rename_column(table_name, column_name, new_column_name) - clear_cache! - execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} TO #{quote_column_name(new_column_name)}" - end - - def remove_index!(table_name, index_name) #:nodoc: - execute "DROP INDEX #{quote_table_name(index_name)}" - end - - def rename_index(table_name, old_name, new_name) - execute "ALTER INDEX #{quote_column_name(old_name)} RENAME TO #{quote_table_name(new_name)}" - end - - def index_name_length - 63 - end - - # Maps logical Rails types to PostgreSQL-specific data types. - def type_to_sql(type, limit = nil, precision = nil, scale = nil) - return super unless type.to_s == 'integer' - return 'integer' unless limit - - case limit - when 1, 2; 'smallint' - when 3, 4; 'integer' - when 5..8; 'bigint' - else raise(ActiveRecordError, "No integer type has byte size #{limit}. Use a numeric with precision 0 instead.") - end - end - - # Returns a SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause. - # - # PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and - # requires that the ORDER BY include the distinct column. - # - # distinct("posts.id", "posts.created_at desc") - def distinct(columns, orders) #:nodoc: - return "DISTINCT #{columns}" if orders.empty? - - # Construct a clean list of column names from the ORDER BY clause, removing - # any ASC/DESC modifiers - order_columns = orders.collect { |s| s.gsub(/\s+(ASC|DESC)\s*/i, '') } - order_columns.delete_if { |c| c.blank? } - order_columns = order_columns.zip((0...order_columns.size).to_a).map { |s,i| "#{s} AS alias_#{i}" } - - "DISTINCT #{columns}, #{order_columns * ', '}" - end - module Utils extend self @@ -1133,17 +519,26 @@ module ActiveRecord end end + def use_insert_returning? + @use_insert_returning + end + protected + # Returns the version of the connected PostgreSQL server. def postgresql_version @connection.server_version end + # See http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html + FOREIGN_KEY_VIOLATION = "23503" + UNIQUE_VIOLATION = "23505" + def translate_exception(exception, message) - case exception.message - when /duplicate key value violates unique constraint/ + case exception.result.try(:error_field, PGresult::PG_DIAG_SQLSTATE) + when UNIQUE_VIOLATION RecordNotUnique.new(message, exception) - when /violates foreign key constraint/ + when FOREIGN_KEY_VIOLATION InvalidForeignKey.new(message, exception) else super @@ -1151,6 +546,23 @@ module ActiveRecord end private + + def initialize_type_map + result = execute('SELECT oid, typname, typelem, typdelim FROM pg_type', 'SCHEMA') + leaves, nodes = result.partition { |row| row['typelem'] == '0' } + + # populate the leaf nodes + leaves.find_all { |row| OID.registered_type? row['typname'] }.each do |row| + OID::TYPE_MAP[row['oid'].to_i] = OID::NAMES[row['typname']] + end + + # populate composite types + nodes.find_all { |row| OID::TYPE_MAP.key? row['typelem'].to_i }.each do |row| + vector = OID::Vector.new row['typdelim'], OID::TYPE_MAP[row['typelem'].to_i] + OID::TYPE_MAP[row['oid'].to_i] = vector + end + end + FEATURE_NOT_SUPPORTED = "0A000" # :nodoc: def exec_no_cache(sql, binds) @@ -1173,7 +585,11 @@ module ActiveRecord # prepared statements whose return value may have changed is # FEATURE_NOT_SUPPORTED. Check here for more details: # http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/cache/plancache.c#l573 - code = e.result.result_error_field(PGresult::PG_DIAG_SQLSTATE) + begin + code = e.result.result_error_field(PGresult::PG_DIAG_SQLSTATE) + rescue + raise e + end if FEATURE_NOT_SUPPORTED == code @statements.delete sql_key(sql) retry @@ -1225,7 +641,7 @@ module ActiveRecord if @config[:encoding] @connection.set_client_encoding(@config[:encoding]) end - self.client_min_messages = @config[:min_messages] if @config[:min_messages] + self.client_min_messages = @config[:min_messages] || 'warning' self.schema_search_path = @config[:schema_search_path] || @config[:schema_order] # Use standard-conforming strings if available so we don't have to do the E'...' dance. @@ -1242,8 +658,15 @@ module ActiveRecord # Returns the current ID of a table's sequence. def last_insert_id(sequence_name) #:nodoc: - r = exec_query("SELECT currval($1)", 'SQL', [[nil, sequence_name]]) - Integer(r.rows.first.first) + Integer(last_insert_id_value(sequence_name)) + end + + def last_insert_id_value(sequence_name) + last_insert_id_result(sequence_name).rows.first.first + end + + def last_insert_id_result(sequence_name) #:nodoc: + exec_query("SELECT currval('#{sequence_name}')", 'SQL') end # Executes a SELECT query and returns the results, performing any data type @@ -1280,7 +703,7 @@ module ActiveRecord # - ::regclass is a function that gives the id for a table name def column_definitions(table_name) #:nodoc: exec_query(<<-end_sql, 'SCHEMA').rows - SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull + SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull, a.atttypid, a.atttypmod FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum WHERE a.attrelid = '#{quote_table_name(table_name)}'::regclass diff --git a/activerecord/lib/active_record/connection_adapters/schema_cache.rb b/activerecord/lib/active_record/connection_adapters/schema_cache.rb index 962718da56..aad1f9a7ef 100644 --- a/activerecord/lib/active_record/connection_adapters/schema_cache.rb +++ b/activerecord/lib/active_record/connection_adapters/schema_cache.rb @@ -1,26 +1,17 @@ module ActiveRecord module ConnectionAdapters class SchemaCache - attr_reader :columns, :columns_hash, :primary_keys, :tables - attr_reader :connection + attr_reader :columns, :columns_hash, :primary_keys, :tables, :version + attr_accessor :connection def initialize(conn) @connection = conn - @tables = {} - @columns = Hash.new do |h, table_name| - h[table_name] = conn.columns(table_name) - end - - @columns_hash = Hash.new do |h, table_name| - h[table_name] = Hash[columns[table_name].map { |col| - [col.name, col] - }] - end - - @primary_keys = Hash.new do |h, table_name| - h[table_name] = table_exists?(table_name) ? conn.primary_key(table_name) : nil - end + @columns = {} + @columns_hash = {} + @primary_keys = {} + @tables = {} + prepare_default_proc end # A cached lookup for table existence. @@ -30,12 +21,22 @@ module ActiveRecord @tables[name] = connection.table_exists?(name) end + # Add internal cache for table with +table_name+. + def add(table_name) + if table_exists?(table_name) + @primary_keys[table_name] + @columns[table_name] + @columns_hash[table_name] + end + end + # Clears out internal caches def clear! @columns.clear @columns_hash.clear @primary_keys.clear @tables.clear + @version = nil end # Clear out internal caches for table with +table_name+. @@ -45,6 +46,37 @@ module ActiveRecord @primary_keys.delete table_name @tables.delete table_name end + + def marshal_dump + # if we get current version during initialization, it happens stack over flow. + @version = ActiveRecord::Migrator.current_version + [@version] + [:@columns, :@columns_hash, :@primary_keys, :@tables].map do |val| + self.instance_variable_get(val).inject({}) { |h, v| h[v[0]] = v[1]; h } + end + end + + def marshal_load(array) + @version, @columns, @columns_hash, @primary_keys, @tables = array + prepare_default_proc + end + + private + + def prepare_default_proc + @columns.default_proc = Proc.new do |h, table_name| + h[table_name] = connection.columns(table_name) + end + + @columns_hash.default_proc = Proc.new do |h, table_name| + h[table_name] = Hash[columns[table_name].map { |col| + [col.name, col] + }] + end + + @primary_keys.default_proc = Proc.new do |h, table_name| + h[table_name] = table_exists?(table_name) ? connection.primary_key(table_name) : nil + end + end end end end diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb index ee5d10859c..4fe0013f0f 100644 --- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb +++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb @@ -1,6 +1,8 @@ -require 'active_record/connection_adapters/sqlite_adapter' +require 'active_record/connection_adapters/abstract_adapter' +require 'active_record/connection_adapters/statement_pool' +require 'arel/visitors/bind_visitor' -gem 'sqlite3', '~> 1.3.5' +gem 'sqlite3', '~> 1.3.6' require 'sqlite3' module ActiveRecord @@ -19,10 +21,6 @@ module ActiveRecord config[:database] = File.expand_path(config[:database], Rails.root) end - unless 'sqlite3' == config[:adapter] - raise ArgumentError, 'adapter name should be "sqlite3"' - end - db = SQLite3::Database.new( config[:database], :results_as_hash => true @@ -35,7 +33,183 @@ module ActiveRecord end module ConnectionAdapters #:nodoc: - class SQLite3Adapter < SQLiteAdapter # :nodoc: + class SQLite3Column < Column #:nodoc: + class << self + def binary_to_string(value) + if value.encoding != Encoding::ASCII_8BIT + value = value.force_encoding(Encoding::ASCII_8BIT) + end + value + end + end + end + + # The SQLite3 adapter works SQLite 3.6.16 or newer + # with the sqlite3-ruby drivers (available as gem from https://rubygems.org/gems/sqlite3). + # + # Options: + # + # * <tt>:database</tt> - Path to the database file. + class SQLite3Adapter < AbstractAdapter + class Version + include Comparable + + def initialize(version_string) + @version = version_string.split('.').map { |v| v.to_i } + end + + def <=>(version_string) + @version <=> version_string.split('.').map { |v| v.to_i } + end + end + + class StatementPool < ConnectionAdapters::StatementPool + def initialize(connection, max) + super + @cache = Hash.new { |h,pid| h[pid] = {} } + end + + def each(&block); cache.each(&block); end + def key?(key); cache.key?(key); end + def [](key); cache[key]; end + def length; cache.length; end + + def []=(sql, key) + while @max <= cache.size + dealloc(cache.shift.last[:stmt]) + end + cache[sql] = key + end + + def clear + cache.values.each do |hash| + dealloc hash[:stmt] + end + cache.clear + end + + private + def cache + @cache[$$] + end + + def dealloc(stmt) + stmt.close unless stmt.closed? + end + end + + class BindSubstitution < Arel::Visitors::SQLite # :nodoc: + include Arel::Visitors::BindVisitor + end + + def initialize(connection, logger, config) + super(connection, logger) + @statements = StatementPool.new(@connection, + config.fetch(:statement_limit) { 1000 }) + @config = config + + if config.fetch(:prepared_statements) { true } + @visitor = Arel::Visitors::SQLite.new self + else + @visitor = BindSubstitution.new self + end + end + + def adapter_name #:nodoc: + 'SQLite' + end + + # Returns true + def supports_ddl_transactions? + true + end + + # Returns true if SQLite version is '3.6.8' or greater, false otherwise. + def supports_savepoints? + sqlite_version >= '3.6.8' + end + + # Returns true, since this connection adapter supports prepared statement + # caching. + def supports_statement_cache? + true + end + + # Returns true, since this connection adapter supports migrations. + def supports_migrations? #:nodoc: + true + end + + # Returns true. + def supports_primary_key? #:nodoc: + true + end + + def requires_reloading? + true + end + + # Returns true + def supports_add_column? + true + end + + # Disconnects from the database if already connected. Otherwise, this + # method does nothing. + def disconnect! + super + clear_cache! + @connection.close rescue nil + end + + # Clears the prepared statements cache. + def clear_cache! + @statements.clear + end + + # Returns true + def supports_count_distinct? #:nodoc: + true + end + + # Returns true + def supports_autoincrement? #:nodoc: + true + end + + def supports_index_sort_order? + true + end + + def native_database_types #:nodoc: + { + :primary_key => default_primary_key_type, + :string => { :name => "varchar", :limit => 255 }, + :text => { :name => "text" }, + :integer => { :name => "integer" }, + :float => { :name => "float" }, + :decimal => { :name => "decimal" }, + :datetime => { :name => "datetime" }, + :timestamp => { :name => "datetime" }, + :time => { :name => "time" }, + :date => { :name => "date" }, + :binary => { :name => "blob" }, + :boolean => { :name => "boolean" } + } + end + + # Returns the current database encoding format as a string, eg: 'UTF-8' + def encoding + @connection.encoding.to_s + end + + # Returns true. + def supports_explain? + true + 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] @@ -45,10 +219,390 @@ module ActiveRecord end end - # Returns the current database encoding format as a string, eg: 'UTF-8' - def encoding - @connection.encoding.to_s + def quote_string(s) #:nodoc: + @connection.class.quote(s) + end + + def quote_column_name(name) #:nodoc: + %Q("#{name.to_s.gsub('"', '""')}") + end + + # Quote date/time values for use in SQL input. Includes microseconds + # if the value is a Time responding to usec. + def quoted_date(value) #:nodoc: + if value.respond_to?(:usec) + "#{super}.#{sprintf("%06d", value.usec)}" + else + super + end + end + + def type_cast(value, column) # :nodoc: + return value.to_f if BigDecimal === value + return super unless String === value + return super unless column && value + + value = super + if column.type == :string && value.encoding == Encoding::ASCII_8BIT + logger.error "Binary data inserted for `string` type on column `#{column.name}`" if logger + value.encode! 'utf-8' + end + value + end + + # DATABASE STATEMENTS ====================================== + + def explain(arel, binds = []) + sql = "EXPLAIN QUERY PLAN #{to_sql(arel, binds)}" + ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds)) + end + + class ExplainPrettyPrinter + # Pretty prints the result of a EXPLAIN QUERY PLAN in a way that resembles + # the output of the SQLite shell: + # + # 0|0|0|SEARCH TABLE users USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) + # 0|1|1|SCAN TABLE posts (~100000 rows) + # + def pp(result) # :nodoc: + result.rows.map do |row| + row.join('|') + end.join("\n") + "\n" + end + end + + def exec_query(sql, name = nil, binds = []) + log(sql, name, binds) do + + # Don't cache statements without bind values + if binds.empty? + stmt = @connection.prepare(sql) + cols = stmt.columns + records = stmt.to_a + stmt.close + stmt = records + else + cache = @statements[sql] ||= { + :stmt => @connection.prepare(sql) + } + stmt = cache[:stmt] + cols = cache[:cols] ||= stmt.columns + stmt.reset! + stmt.bind_params binds.map { |col, val| + type_cast(val, col) + } + end + + ActiveRecord::Result.new(cols, stmt.to_a) + end + end + + def exec_delete(sql, name = 'SQL', binds = []) + exec_query(sql, name, binds) + @connection.changes end + alias :exec_update :exec_delete + + def last_inserted_id(result) + @connection.last_insert_row_id + end + + def execute(sql, name = nil) #:nodoc: + log(sql, name) { @connection.execute(sql) } + end + + def update_sql(sql, name = nil) #:nodoc: + super + @connection.changes + end + + def delete_sql(sql, name = nil) #:nodoc: + sql += " WHERE 1=1" unless sql =~ /WHERE/i + super sql, name + end + + def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc: + super + id_value || @connection.last_insert_row_id + end + alias :create :insert_sql + + def select_rows(sql, name = nil) + exec_query(sql, name).rows + 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 + + def begin_db_transaction #:nodoc: + log('begin transaction',nil) { @connection.transaction } + end + + def commit_db_transaction #:nodoc: + log('commit transaction',nil) { @connection.commit } + end + + def rollback_db_transaction #:nodoc: + log('rollback transaction',nil) { @connection.rollback } + end + + # SCHEMA STATEMENTS ======================================== + + def tables(name = nil, table_name = nil) #:nodoc: + sql = <<-SQL + SELECT name + FROM sqlite_master + WHERE type = 'table' AND NOT name = 'sqlite_sequence' + SQL + sql << " AND name = #{quote_table_name(table_name)}" if table_name + + exec_query(sql, 'SCHEMA').map do |row| + row['name'] + end + end + + def table_exists?(table_name) + table_name && tables(nil, table_name).any? + end + + # Returns an array of +SQLite3Column+ objects for the table specified by +table_name+. + def columns(table_name) #:nodoc: + table_structure(table_name).map do |field| + case field["dflt_value"] + when /^null$/i + field["dflt_value"] = nil + when /^'(.*)'$/m + field["dflt_value"] = $1.gsub("''", "'") + when /^"(.*)"$/m + field["dflt_value"] = $1.gsub('""', '"') + end + + SQLite3Column.new(field['name'], field['dflt_value'], field['type'], field['notnull'].to_i == 0) + end + end + + # Returns an array of indexes for the given table. + def indexes(table_name, name = nil) #:nodoc: + exec_query("PRAGMA index_list(#{quote_table_name(table_name)})", 'SCHEMA').map do |row| + IndexDefinition.new( + table_name, + row['name'], + row['unique'] != 0, + exec_query("PRAGMA index_info('#{row['name']}')", "Columns for index #{row['name']} on #{table_name}").map { |col| + col['name'] + }) + end + end + + def primary_key(table_name) #:nodoc: + column = table_structure(table_name).find { |field| + field['pk'] == 1 + } + column && column['name'] + end + + def remove_index!(table_name, index_name) #:nodoc: + exec_query "DROP INDEX #{quote_column_name(index_name)}" + end + + # Renames a table. + # + # Example: + # rename_table('octopuses', 'octopi') + def rename_table(name, new_name) + exec_query "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" + end + + # See: http://www.sqlite.org/lang_altertable.html + # SQLite has an additional restriction on the ALTER TABLE statement + def valid_alter_table_options( type, options) + type.to_sym != :primary_key + end + + def add_column(table_name, column_name, type, options = {}) #:nodoc: + if supports_add_column? && valid_alter_table_options( type, options ) + super(table_name, column_name, type, options) + else + alter_table(table_name) do |definition| + definition.column(column_name, type, options) + end + end + end + + def remove_column(table_name, *column_names) #:nodoc: + raise ArgumentError.new("You must specify at least one column name. Example: remove_column(:people, :first_name)") if column_names.empty? + column_names.each do |column_name| + alter_table(table_name) do |definition| + definition.columns.delete(definition[column_name]) + end + end + end + alias :remove_columns :remove_column + + def change_column_default(table_name, column_name, default) #:nodoc: + alter_table(table_name) do |definition| + definition[column_name].default = default + end + end + + def change_column_null(table_name, column_name, null, default = nil) + unless null || default.nil? + exec_query("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") + end + alter_table(table_name) do |definition| + definition[column_name].null = null + end + end + + def change_column(table_name, column_name, type, options = {}) #:nodoc: + alter_table(table_name) do |definition| + include_default = options_include_default?(options) + definition[column_name].instance_eval do + self.type = type + self.limit = options[:limit] if options.include?(:limit) + self.default = options[:default] if include_default + self.null = options[:null] if options.include?(:null) + self.precision = options[:precision] if options.include?(:precision) + self.scale = options[:scale] if options.include?(:scale) + end + end + end + + def rename_column(table_name, column_name, new_column_name) #:nodoc: + unless columns(table_name).detect{|c| c.name == column_name.to_s } + raise ActiveRecord::ActiveRecordError, "Missing column #{table_name}.#{column_name}" + end + alter_table(table_name, :rename => {column_name.to_s => new_column_name.to_s}) + end + + def empty_insert_statement_value + "VALUES(NULL)" + end + + protected + def select(sql, name = nil, binds = []) #:nodoc: + exec_query(sql, name, binds) + end + + def table_structure(table_name) + structure = exec_query("PRAGMA table_info(#{quote_table_name(table_name)})", 'SCHEMA').to_hash + raise(ActiveRecord::StatementInvalid, "Could not find table '#{table_name}'") if structure.empty? + structure + end + + def alter_table(table_name, options = {}) #:nodoc: + altered_table_name = "altered_#{table_name}" + caller = lambda {|definition| yield definition if block_given?} + + transaction do + move_table(table_name, altered_table_name, + options.merge(:temporary => true)) + move_table(altered_table_name, table_name, &caller) + end + end + + def move_table(from, to, options = {}, &block) #:nodoc: + copy_table(from, to, options, &block) + drop_table(from) + end + + def copy_table(from, to, options = {}) #:nodoc: + from_primary_key = primary_key(from) + options[:primary_key] = from_primary_key if from_primary_key != 'id' + unless options[:primary_key] + options[:id] = columns(from).detect{|c| c.name == 'id'}.present? && from_primary_key == 'id' + end + create_table(to, options) do |definition| + @definition = definition + columns(from).each do |column| + column_name = options[:rename] ? + (options[:rename][column.name] || + options[:rename][column.name.to_sym] || + column.name) : column.name + + @definition.column(column_name, column.type, + :limit => column.limit, :default => column.default, + :precision => column.precision, :scale => column.scale, + :null => column.null) + end + @definition.primary_key(from_primary_key) if from_primary_key + yield @definition if block_given? + end + + copy_table_indexes(from, to, options[:rename] || {}) + copy_table_contents(from, to, + @definition.columns.map {|column| column.name}, + options[:rename] || {}) + end + + def copy_table_indexes(from, to, rename = {}) #:nodoc: + indexes(from).each do |index| + name = index.name + if to == "altered_#{from}" + name = "temp_#{name}" + elsif from == "altered_#{to}" + name = name[5..-1] + end + + to_column_names = columns(to).map { |c| c.name } + columns = index.columns.map {|c| rename[c] || c }.select do |column| + to_column_names.include?(column) + end + + unless columns.empty? + # index name can't be the same + opts = { :name => name.gsub(/_(#{from})_/, "_#{to}_") } + opts[:unique] = true if index.unique + add_index(to, columns, opts) + end + end + end + + def copy_table_contents(from, to, columns, rename = {}) #:nodoc: + column_mappings = Hash[columns.map {|name| [name, name]}] + rename.each { |a| column_mappings[a.last] = a.first } + from_columns = columns(from).collect {|col| col.name} + columns = columns.find_all{|col| from_columns.include?(column_mappings[col])} + quoted_columns = columns.map { |col| quote_column_name(col) } * ',' + + quoted_to = quote_table_name(to) + exec_query("SELECT * FROM #{quote_table_name(from)}").each do |row| + sql = "INSERT INTO #{quoted_to} (#{quoted_columns}) VALUES (" + sql << columns.map {|col| quote row[column_mappings[col]]} * ', ' + sql << ')' + exec_query sql + end + end + + def sqlite_version + @sqlite_version ||= SQLite3Adapter::Version.new(select_value('select sqlite_version(*)')) + end + + def default_primary_key_type + if supports_autoincrement? + 'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL' + else + 'INTEGER PRIMARY KEY NOT NULL' + end + end + + def translate_exception(exception, message) + case exception.message + when /column(s)? .* (is|are) not unique/ + RecordNotUnique.new(message, exception) + else + super + end + end end end diff --git a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb deleted file mode 100644 index 55eca48efe..0000000000 --- a/activerecord/lib/active_record/connection_adapters/sqlite_adapter.rb +++ /dev/null @@ -1,554 +0,0 @@ -require 'active_record/connection_adapters/abstract_adapter' -require 'active_record/connection_adapters/statement_pool' -require 'active_support/core_ext/string/encoding' - -module ActiveRecord - module ConnectionAdapters #:nodoc: - class SQLiteColumn < Column #:nodoc: - class << self - def binary_to_string(value) - if value.encoding != Encoding::ASCII_8BIT - value = value.force_encoding(Encoding::ASCII_8BIT) - end - value - end - end - end - - # The SQLite adapter works with both the 2.x and 3.x series of SQLite with the sqlite-ruby - # drivers (available both as gems and from http://rubyforge.org/projects/sqlite-ruby/). - # - # Options: - # - # * <tt>:database</tt> - Path to the database file. - class SQLiteAdapter < AbstractAdapter - class Version - include Comparable - - def initialize(version_string) - @version = version_string.split('.').map { |v| v.to_i } - end - - def <=>(version_string) - @version <=> version_string.split('.').map { |v| v.to_i } - end - end - - class StatementPool < ConnectionAdapters::StatementPool - def initialize(connection, max) - super - @cache = Hash.new { |h,pid| h[pid] = {} } - end - - def each(&block); cache.each(&block); end - def key?(key); cache.key?(key); end - def [](key); cache[key]; end - def length; cache.length; end - - def []=(sql, key) - while @max <= cache.size - dealloc(cache.shift.last[:stmt]) - end - cache[sql] = key - end - - def clear - cache.values.each do |hash| - dealloc hash[:stmt] - end - cache.clear - end - - private - def cache - @cache[$$] - end - - def dealloc(stmt) - stmt.close unless stmt.closed? - end - end - - def initialize(connection, logger, config) - super(connection, logger) - @statements = StatementPool.new(@connection, - config.fetch(:statement_limit) { 1000 }) - @config = config - @visitor = Arel::Visitors::SQLite.new self - end - - def adapter_name #:nodoc: - 'SQLite' - end - - # Returns true if SQLite version is '2.0.0' or greater, false otherwise. - def supports_ddl_transactions? - sqlite_version >= '2.0.0' - end - - # Returns true if SQLite version is '3.6.8' or greater, false otherwise. - def supports_savepoints? - sqlite_version >= '3.6.8' - end - - # Returns true, since this connection adapter supports prepared statement - # caching. - def supports_statement_cache? - true - end - - # Returns true, since this connection adapter supports migrations. - def supports_migrations? #:nodoc: - true - end - - # Returns true. - def supports_primary_key? #:nodoc: - true - end - - # Returns true. - def supports_explain? - true - end - - def requires_reloading? - true - end - - # Returns true if SQLite version is '3.1.6' or greater, false otherwise. - def supports_add_column? - sqlite_version >= '3.1.6' - end - - # Disconnects from the database if already connected. Otherwise, this - # method does nothing. - def disconnect! - super - clear_cache! - @connection.close rescue nil - end - - # Clears the prepared statements cache. - def clear_cache! - @statements.clear - end - - # Returns true if SQLite version is '3.2.6' or greater, false otherwise. - def supports_count_distinct? #:nodoc: - sqlite_version >= '3.2.6' - end - - # Returns true if SQLite version is '3.1.0' or greater, false otherwise. - def supports_autoincrement? #:nodoc: - sqlite_version >= '3.1.0' - end - - def supports_index_sort_order? - sqlite_version >= '3.3.0' - end - - def native_database_types #:nodoc: - { - :primary_key => default_primary_key_type, - :string => { :name => "varchar", :limit => 255 }, - :text => { :name => "text" }, - :integer => { :name => "integer" }, - :float => { :name => "float" }, - :decimal => { :name => "decimal" }, - :datetime => { :name => "datetime" }, - :timestamp => { :name => "datetime" }, - :time => { :name => "time" }, - :date => { :name => "date" }, - :binary => { :name => "blob" }, - :boolean => { :name => "boolean" } - } - end - - - # QUOTING ================================================== - - def quote_string(s) #:nodoc: - @connection.class.quote(s) - end - - def quote_column_name(name) #:nodoc: - %Q("#{name.to_s.gsub('"', '""')}") - end - - # Quote date/time values for use in SQL input. Includes microseconds - # if the value is a Time responding to usec. - def quoted_date(value) #:nodoc: - if value.respond_to?(:usec) - "#{super}.#{sprintf("%06d", value.usec)}" - else - super - end - end - - def type_cast(value, column) # :nodoc: - return value.to_f if BigDecimal === value - return super unless String === value - return super unless column && value - - value = super - if column.type == :string && value.encoding == Encoding::ASCII_8BIT - @logger.error "Binary data inserted for `string` type on column `#{column.name}`" - value.encode! 'utf-8' - end - value - end - - # DATABASE STATEMENTS ====================================== - - def explain(arel, binds = []) - sql = "EXPLAIN QUERY PLAN #{to_sql(arel)}" - ExplainPrettyPrinter.new.pp(exec_query(sql, 'EXPLAIN', binds)) - end - - class ExplainPrettyPrinter - # Pretty prints the result of a EXPLAIN QUERY PLAN in a way that resembles - # the output of the SQLite shell: - # - # 0|0|0|SEARCH TABLE users USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) - # 0|1|1|SCAN TABLE posts (~100000 rows) - # - def pp(result) # :nodoc: - result.rows.map do |row| - row.join('|') - end.join("\n") + "\n" - end - end - - def exec_query(sql, name = nil, binds = []) - log(sql, name, binds) do - - # Don't cache statements without bind values - if binds.empty? - stmt = @connection.prepare(sql) - cols = stmt.columns - records = stmt.to_a - stmt.close - stmt = records - else - cache = @statements[sql] ||= { - :stmt => @connection.prepare(sql) - } - stmt = cache[:stmt] - cols = cache[:cols] ||= stmt.columns - stmt.reset! - stmt.bind_params binds.map { |col, val| - type_cast(val, col) - } - end - - ActiveRecord::Result.new(cols, stmt.to_a) - end - end - - def exec_delete(sql, name = 'SQL', binds = []) - exec_query(sql, name, binds) - @connection.changes - end - alias :exec_update :exec_delete - - def last_inserted_id(result) - @connection.last_insert_row_id - end - - def execute(sql, name = nil) #:nodoc: - log(sql, name) { @connection.execute(sql) } - end - - def update_sql(sql, name = nil) #:nodoc: - super - @connection.changes - end - - def delete_sql(sql, name = nil) #:nodoc: - sql += " WHERE 1=1" unless sql =~ /WHERE/i - super sql, name - end - - def insert_sql(sql, name = nil, pk = nil, id_value = nil, sequence_name = nil) #:nodoc: - super - id_value || @connection.last_insert_row_id - end - alias :create :insert_sql - - def select_rows(sql, name = nil) - exec_query(sql, name).rows - 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 - - def begin_db_transaction #:nodoc: - log('begin transaction',nil) { @connection.transaction } - end - - def commit_db_transaction #:nodoc: - log('commit transaction',nil) { @connection.commit } - end - - def rollback_db_transaction #:nodoc: - log('rollback transaction',nil) { @connection.rollback } - end - - # SCHEMA STATEMENTS ======================================== - - def tables(name = 'SCHEMA', table_name = nil) #:nodoc: - sql = <<-SQL - SELECT name - FROM sqlite_master - WHERE type = 'table' AND NOT name = 'sqlite_sequence' - SQL - sql << " AND name = #{quote_table_name(table_name)}" if table_name - - exec_query(sql, name).map do |row| - row['name'] - end - end - - def table_exists?(name) - name && tables('SCHEMA', name).any? - end - - # Returns an array of +SQLiteColumn+ objects for the table specified by +table_name+. - def columns(table_name) #:nodoc: - table_structure(table_name).map do |field| - case field["dflt_value"] - when /^null$/i - field["dflt_value"] = nil - when /^'(.*)'$/ - field["dflt_value"] = $1.gsub(/''/, "'") - when /^"(.*)"$/ - field["dflt_value"] = $1.gsub(/""/, '"') - end - - SQLiteColumn.new(field['name'], field['dflt_value'], field['type'], field['notnull'].to_i == 0) - end - end - - # Returns an array of indexes for the given table. - def indexes(table_name, name = nil) #:nodoc: - exec_query("PRAGMA index_list(#{quote_table_name(table_name)})", name).map do |row| - IndexDefinition.new( - table_name, - row['name'], - row['unique'] != 0, - exec_query("PRAGMA index_info('#{row['name']}')").map { |col| - col['name'] - }) - end - end - - def primary_key(table_name) #:nodoc: - column = table_structure(table_name).find { |field| - field['pk'] == 1 - } - column && column['name'] - end - - def remove_index!(table_name, index_name) #:nodoc: - exec_query "DROP INDEX #{quote_column_name(index_name)}" - end - - # Renames a table. - # - # Example: - # rename_table('octopuses', 'octopi') - def rename_table(name, new_name) - exec_query "ALTER TABLE #{quote_table_name(name)} RENAME TO #{quote_table_name(new_name)}" - end - - # See: http://www.sqlite.org/lang_altertable.html - # SQLite has an additional restriction on the ALTER TABLE statement - def valid_alter_table_options( type, options) - type.to_sym != :primary_key - end - - def add_column(table_name, column_name, type, options = {}) #:nodoc: - if supports_add_column? && valid_alter_table_options( type, options ) - super(table_name, column_name, type, options) - else - alter_table(table_name) do |definition| - definition.column(column_name, type, options) - end - end - end - - def remove_column(table_name, *column_names) #:nodoc: - raise ArgumentError.new("You must specify at least one column name. Example: remove_column(:people, :first_name)") if column_names.empty? - column_names.flatten.each do |column_name| - alter_table(table_name) do |definition| - definition.columns.delete(definition[column_name]) - end - end - end - alias :remove_columns :remove_column - - def change_column_default(table_name, column_name, default) #:nodoc: - alter_table(table_name) do |definition| - definition[column_name].default = default - end - end - - def change_column_null(table_name, column_name, null, default = nil) - unless null || default.nil? - exec_query("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL") - end - alter_table(table_name) do |definition| - definition[column_name].null = null - end - end - - def change_column(table_name, column_name, type, options = {}) #:nodoc: - alter_table(table_name) do |definition| - include_default = options_include_default?(options) - definition[column_name].instance_eval do - self.type = type - self.limit = options[:limit] if options.include?(:limit) - self.default = options[:default] if include_default - self.null = options[:null] if options.include?(:null) - self.precision = options[:precision] if options.include?(:precision) - self.scale = options[:scale] if options.include?(:scale) - end - end - end - - def rename_column(table_name, column_name, new_column_name) #:nodoc: - unless columns(table_name).detect{|c| c.name == column_name.to_s } - raise ActiveRecord::ActiveRecordError, "Missing column #{table_name}.#{column_name}" - end - alter_table(table_name, :rename => {column_name.to_s => new_column_name.to_s}) - end - - def empty_insert_statement_value - "VALUES(NULL)" - end - - protected - def select(sql, name = nil, binds = []) #:nodoc: - exec_query(sql, name, binds) - end - - def table_structure(table_name) - structure = exec_query("PRAGMA table_info(#{quote_table_name(table_name)})", 'SCHEMA').to_hash - raise(ActiveRecord::StatementInvalid, "Could not find table '#{table_name}'") if structure.empty? - structure - end - - def alter_table(table_name, options = {}) #:nodoc: - altered_table_name = "altered_#{table_name}" - caller = lambda {|definition| yield definition if block_given?} - - transaction do - move_table(table_name, altered_table_name, - options.merge(:temporary => true)) - move_table(altered_table_name, table_name, &caller) - end - end - - def move_table(from, to, options = {}, &block) #:nodoc: - copy_table(from, to, options, &block) - drop_table(from) - end - - def copy_table(from, to, options = {}) #:nodoc: - options = options.merge(:id => (!columns(from).detect{|c| c.name == 'id'}.nil? && 'id' == primary_key(from).to_s)) - create_table(to, options) do |definition| - @definition = definition - columns(from).each do |column| - column_name = options[:rename] ? - (options[:rename][column.name] || - options[:rename][column.name.to_sym] || - column.name) : column.name - - @definition.column(column_name, column.type, - :limit => column.limit, :default => column.default, - :precision => column.precision, :scale => column.scale, - :null => column.null) - end - @definition.primary_key(primary_key(from)) if primary_key(from) - yield @definition if block_given? - end - - copy_table_indexes(from, to, options[:rename] || {}) - copy_table_contents(from, to, - @definition.columns.map {|column| column.name}, - options[:rename] || {}) - end - - def copy_table_indexes(from, to, rename = {}) #:nodoc: - indexes(from).each do |index| - name = index.name - if to == "altered_#{from}" - name = "temp_#{name}" - elsif from == "altered_#{to}" - name = name[5..-1] - end - - to_column_names = columns(to).map { |c| c.name } - columns = index.columns.map {|c| rename[c] || c }.select do |column| - to_column_names.include?(column) - end - - unless columns.empty? - # index name can't be the same - opts = { :name => name.gsub(/_(#{from})_/, "_#{to}_") } - opts[:unique] = true if index.unique - add_index(to, columns, opts) - end - end - end - - def copy_table_contents(from, to, columns, rename = {}) #:nodoc: - column_mappings = Hash[columns.map {|name| [name, name]}] - rename.each { |a| column_mappings[a.last] = a.first } - from_columns = columns(from).collect {|col| col.name} - columns = columns.find_all{|col| from_columns.include?(column_mappings[col])} - quoted_columns = columns.map { |col| quote_column_name(col) } * ',' - - quoted_to = quote_table_name(to) - exec_query("SELECT * FROM #{quote_table_name(from)}").each do |row| - sql = "INSERT INTO #{quoted_to} (#{quoted_columns}) VALUES (" - sql << columns.map {|col| quote row[column_mappings[col]]} * ', ' - sql << ')' - exec_query sql - end - end - - def sqlite_version - @sqlite_version ||= SQLiteAdapter::Version.new(select_value('select sqlite_version(*)')) - end - - def default_primary_key_type - if supports_autoincrement? - 'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL' - else - 'INTEGER PRIMARY KEY NOT NULL' - end - end - - def translate_exception(exception, message) - case exception.message - when /column(s)? .* (is|are) not unique/ - RecordNotUnique.new(message, exception) - else - super - end - end - - end - end -end |