Mysql and Rails live data transformation

Hello again

I decided to post a series of steps i needed to take to add a table and repair a table in a running application with live data.

It may be helpful to someone even though its very specific to a particular scenario

If you need help with a data transform you are welcome to ask - I will help if I can :)

I use a combination of mysql client as well as the rails console (Active Record) to get things done

1) Get latest data (shell prompt)
% mysql -u root lotus_development < id="st" name="st" class="st">script/console)
Quote.destroy_all(["accepted_status='cancelled'

"])
3) Delete duplicate invoices (mysql prompt)
mysql> delete from invoices where care=0;
4) Add invoice column to deliveries (mysql prompt)
mysql> alter table deliveries add invoice_no varchar(100) default "none";
5) Dump invoices table (shell prompt)
% mysqldump -u root lotus_development invoices > invoices.sql
6) Add temp id column (mysql prompt)
ALTER TABLE `lotus_development`.`invoices` ADD COLUMN `id_bc` INTEGER NOT NULL FIRST;
7) Copy invoice numbers to new id column (./script/console)
a = Invoice.find (:all)
a.each do |li|
li.update_attribute(:id_bc, li.context_number)
end
8) Remove the autoincrement from original id (mysql prompt)
ALTER TABLE `lotus_development`.`invoices` MODIFY COLUMN `id` INTEGER DEFAULT NULL;
9) Add the autoincrement to the new id (mysql prompt)
ALTER TABLE `lotus_development`.`invoices` MODIFY COLUMN `id_bc` INTEGER NOT NULL AUTO_INCREMENT,
DROP PRIMARY KEY,
ADD PRIMARY KEY(`id_bc`, `id`);
10) Change the old id to id_old(mysql prompt)
ALTER TABLE `lotus_development`.`invoices` CHANGE COLUMN `id` `id_old` INTEGER NOT NULL DEFAULT 0,
DROP PRIMARY KEY,
ADD PRIMARY KEY(`id_bc`, `id_old`);
11) Drop the old id as primary key(mysql prompt)
ALTER TABLE `lotus_development`.`invoices` CHANGE COLUMN `id_bc` `id` INTEGER NOT NULL DEFAULT NULL AUTO_INCREMENT,
DROP PRIMARY KEY,
ADD PRIMARY KEY(`id`, `id_old`);
12) Add the new id as primary key(mysql prompt)
ALTER TABLE `lotus_development`.`invoices` CHANGE COLUMN `id_bc` `id` INTEGER NOT NULL DEFAULT NULL AUTO_INCREMENT,
MODIFY COLUMN `id_old` INTEGER DEFAULT 0,
DROP PRIMARY KEY,
ADD PRIMARY KEY(`id`);

The invoice data should now look like this (sample)
+------+--------+---------------------+----------+------+----------------+---------------+----------------+--------------+------+----------------+
| id | id_old | created_at | quote_id | note | printed_remark | first_printed | print_discount | date_payable | care | context_number |
+------+--------+---------------------+----------+------+----------------+---------------+----------------+--------------+------+----------------+
| 5780 | 5780 | 2007-06-16 12:29:19 | 3058 | NULL | NULL | 0 | 0 | NULL | 1 | 5780 |
| 2897 | 5776 | 2007-05-24 13:21:18 | 3065 | NULL | | 1 | 0 | 2007-06-25 | 1 | 2897 |
+------+--------+---------------------+----------+------+----------------+---------------+----------------+--------------+------+----------------+
top is a new record and bottom is an old record - notice that new id now has the same value as the context number - context number left for compatablilty

This is an example console script to create a new table from existing data

a = Invoice.find(:all)
a.each do |li|
count = 0
li.quoted.line_items.each do |b|
count = count + 1
d = Delivery.new
d.save
d.update_attribute(:quote_id, li.quote_id)
d.update_attribute(:delivery_no, li.id)
d.update_attribute(:invoice_no, li.id)
d.update_attribute(:line_item_id, b.id)
d.update_attribute(:line_item_no, count)
d.update_attribute(:line_item_sub, li.quoted.line_items.size)
d.update_attribute(:total_line_items, li.quoted.line_items.size)
d.update_attribute(:created_at, li.created_at)
b.add_invoice(li)
end
end