# Insert records in bulk with a select statement # # == Parameters # * +options+ - the options used for the finder sql (select) # # === Options # Any valid finder options (options for ActiveRecord::Base.find(:all) )such as :joins, :conditions, :include, etc including: # * :from - the symbol, class name or class used for the finder SQL (select) # * :on_duplicate_key_update - an array of fields to update, or a custom string # * :select - An array of fields to select or custom string. The SQL will be sanitized and ? replaced with values as with :conditions. # * :ignore => true - will ignore any duplicates # * :into - Specifies the columns for which data will be inserted. An array of fields to select or custom string. # # == Examples # Create cart items for all books for shopping cart @cart+ # setting the +copies+ field to 1, the +updated_at+ field to Time.now and the +created_at+ field to the database function now() # CartItem.insert_select(:from => :book, # :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now], # :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at]}) # # GENERATED SQL example (MySQL): # INSERT INTO `cart_items` ( `book_id`, `shopping_cart_id`, `copies`, `updated_at`, `created_at` ) # SELECT books.id, '134', 1, '2009-03-02 18:28:25', now() FROM `books` # # A similar example that # * uses the class +Book+ instead of symbol :book # * a custom string (instead of an Array) for the :select of the +insert_options+ # * Updates the +updated_at+ field of all existing cart item. This assumes there is a unique composite index on the +book_id+ and +shopping_cart_id+ fields # # CartItem.insert_select(:from => Book, # :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now], # :into => 'cart_items.book_id, shopping_cart_id, copies, updated_at, created_at', # :on_duplicate_key_update => [:updated_at]) # GENERATED SQL example (MySQL): # INSERT INTO `cart_items` ( cart_items.book_id, shopping_cart_id, copies, updated_at, created_at ) # SELECT books.id, '138', 1, '2009-03-02 18:32:34', now() FROM `books` # ON DUPLICATE KEY UPDATE `cart_items`.`updated_at`=VALUES(`updated_at`) # # # Similar example ignoring duplicates # CartItem.insert_select(:from => :book, # :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now], # :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at], # :ignore => true) # # == Developers # * Blythe Dunham http://blythedunham.com # # == Homepage # * Project Site: http://www.continuousthinking.com/tags/arext # * Rubyforge Project: http://rubyforge.org/projects/arext # * Anonymous SVN: svn checkout svn://rubyforge.org/var/svn/arext # module ActiveRecord::Extensions::ConnectionAdapters; end module ActiveRecord::Extensions::InsertSelectSupport #:nodoc: def supports_insert_select? #:nodoc: true end end class ActiveRecord::Base include ActiveRecord::Extensions::SqlGeneration class << self # Insert records in bulk with a select statement # # == Parameters # * +options+ - the options used for the finder sql (select) # # === Options # Any valid finder options (options for ActiveRecord::Base.find(:all) )such as :joins, :conditions, :include, etc including: # * :from - the symbol, class name or class used for the finder SQL (select) # * :on_duplicate_key_update - an array of fields to update, or a custom string # * :select - An array of fields to select or custom string. The SQL will be sanitized and ? replaced with values as with :conditions. # * :ignore => true - will ignore any duplicates # * :into - Specifies the columns for which data will be inserted. An array of fields to select or custom string. # # == Examples # Create cart items for all books for shopping cart @cart+ # setting the +copies+ field to 1, the +updated_at+ field to Time.now and the +created_at+ field to the database function now() # CartItem.insert_select(:from => :book, # :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now], # :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at]}) # # GENERATED SQL example (MySQL): # INSERT INTO `cart_items` ( `book_id`, `shopping_cart_id`, `copies`, `updated_at`, `created_at` ) # SELECT books.id, '134', 1, '2009-03-02 18:28:25', now() FROM `books` # # A similar example that # * uses the class +Book+ instead of symbol :book # * a custom string (instead of an Array) for the :select of the +insert_options+ # * Updates the +updated_at+ field of all existing cart item. This assumes there is a unique composite index on the +book_id+ and +shopping_cart_id+ fields # # CartItem.insert_select(:from => Book, # :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now], # :into => 'cart_items.book_id, shopping_cart_id, copies, updated_at, created_at', # :on_duplicate_key_update => [:updated_at]) # GENERATED SQL example (MySQL): # INSERT INTO `cart_items` ( cart_items.book_id, shopping_cart_id, copies, updated_at, created_at ) # SELECT books.id, '138', 1, '2009-03-02 18:32:34', now() FROM `books` # ON DUPLICATE KEY UPDATE `cart_items`.`updated_at`=VALUES(`updated_at`) # # # Similar example ignoring duplicates # CartItem.insert_select(:from => :book, # :select => ['books.id, ?, ?, ?, now()', @cart.to_param, 1, Time.now], # :into => [:book_id, :shopping_cart_id, :copies, :updated_at, :created_at], # :ignore => true) def insert_select(options={}) select_obj = options.delete(:from).to_s.classify.constantize #TODO: add batch support for high volume inserts #return insert_select_batch(select_obj, select_options, insert_options) if insert_options[:batch] sql = construct_insert_select_sql(select_obj, options) connection.insert(sql, "#{name} Insert Select #{select_obj}") end protected def construct_insert_select_sql(select_obj, options)#:nodoc: construct_ar_extension_sql(gather_insert_options(options), valid_insert_select_options) do |sql, into_op| sql << " INTO #{quoted_table_name} " sql << "( #{into_column_sql(options.delete(:into))} ) " #sanitize the select sql based on the select object sql << select_obj.send(:finder_sql_to_string, sanitize_select_options(options)) sql end end # return a list of the column names quoted accordingly # nil => All columns except primary key (auto update) # String => Exact String # Array # needs sanitation ["?, ?", 5, 'test'] => "5, 'test'" or [":date", {:date => Date.today}] => "12-30-2006"] # list of strings or symbols returns quoted values [:start, :name] => `start`, `name` or ['abc'] => `start` def select_column_sql(field_list=nil)#:nodoc: if field_list.kind_of?(String) field_list.dup elsif ((field_list.kind_of?(Array) && field_list.first.is_a?(String)) && (field_list.last.is_a?(Hash) || field_list.first.include?('?'))) sanitize_sql(field_list) else field_list = field_list.blank? ? self.column_names - [self.primary_key] : [field_list].flatten field_list.collect{|field| self.connection.quote_column_name(field.to_s) }.join(", ") end end alias_method :into_column_sql, :select_column_sql #sanitize the select options for insert select def sanitize_select_options(options)#:nodoc: o = options.dup select = o.delete :select o[:override_select] = select ? select_column_sql(select) : ' * ' o end def valid_insert_select_options#:nodoc: @@valid_insert_select_options ||= [:command, :into_pre, :into_post, :into_keywords, :ignore, :on_duplicate_key_update] end #move all the insert options to a seperate map def gather_insert_options(options)#:nodoc: into_options = valid_insert_select_options.inject(:command => 'INSERT') do |map, o| v = options.delete(o) map[o] = v if v map end end end end