migration with lock, customer_to_lists

20100326124521_create_customer_to_lists.rb

class CreateCustomerToLists < ActiveRecord::Migration
  def self.up
    create_table :customer_to_lists, :force => true do |t|
      t.integer "customer_id", :null => false
      t.integer "customer_list_id", :null => false
    end

    conn = ActiveRecord::Base.connection

    #convert
    #Customer.all.each do |cust|
    #  list_id = cust.customer_list_id

    #  unless list_id.nil?
    #    execute "INSERT INTO customer_to_lists(customer_id, customer_list_id) VALUES ( #{cust.id}, #{list_id} )"
    #  end
    #end

    queries = []

    # delete junk
    queries << 'DELETE FROM customers WHERE customer_id IS NULL'

    # lock for write
    queries << 'LOCK TABLES customers         READ,
                            customers c       READ,
                            customer_lists    READ,
                            customer_lists cl READ,
                            customer_to_lists WRITE'

    # insert
    # tested at stage db without locking:
    # Query OK, 5913605 rows affected, 12659 warnings (3 min 57.42 sec)
    queries << 'INSERT INTO customer_to_lists (customer_id, customer_list_id)
                SELECT c.id, c.customer_list_id FROM customers c
                WHERE c.customer_list_id IS NOT NULL'

    # insert customers without lists
    # tested at stage db without locking:
    # Query OK, 12659 rows affected, 2090 warnings (0.83 sec)
    queries << 'INSERT INTO customer_to_lists (customer_id, customer_list_id)
                SELECT c.id,
                  (SELECT id FROM customer_lists cl
                    WHERE cl.user_id = c.user_id
                    AND (cl.name IN ("Unassigned", "New Customers")
                  ) LIMIT 1) AS customer_list_id
                FROM customers c WHERE c.customer_list_id IS NULL'

    # unlock
    queries << 'UNLOCK TABLES'

    queries.each do |query|
      say query
      conn.execute query
    end

    add_index :customer_to_lists, [:customer_id], :unique => false
    add_index :customer_to_lists, [:customer_list_id], :unique => false
  end

  def self.down
    drop_table :customer_to_lists
  end
end