SQL retention to job beaker migration

1.ALTER TABLE job ADD COLUMN (retention_tag_id int(11) default NULL);
2.SELECT job_id, COUNT(distinct retention_tag_id) AS c FROM recipe_set group
BY job_id HAVING c > 1
3.UPDATE job,recipe_set SET job.retention_tag_id = recipe_set.retention_tag_id
WHERE job.id= recipe_set.job_id
4.ALTER TABLE job ADD FOREIGN KEY (`retention_tag_id`) REFERENCES
`retention_tag` (`id`);
5.ALTER TABLE recipe_set DROP FOREIGN KEY recipe_set_ibfk_1;
6.ALTER TABLE recipe_set DROP COLUMN retention_tag_id;


1. Add rentention_tag_id column to Job
2. Find rentention_tags with > 1 per job. Keep list. For time being just
select the first found
3. Add into job.retention_tag_id
4. Add retention_tag FK to Job (This could fail if incorrect values are in
job.retention_tag_id)
5. Remove rentention_tag FK from recipe_set table (the actual key name will
depend, do a 'SHOW CREATE TABLE recipe_set;' to see what the actual key name
is
6. Drop column recipe_set.retention_tag_id




SQL needed for bz649483:
use beaker;
ALTER TABLE retention_tag ADD COLUMN (needs_product tinyint default 0);
UPDATE retention_tag SET needs_product = 1 WHERE id IN (SELECT id from beaker_tag where tag IN ('active','audit'));
ALTER TABLE job ADD COLUMN (product_id varchar(100) default null);





Rollback:
DROP table product;
ALTER TABLE retention_tag DROP COLUMN needs_product;
ALTER TABLE job DROP COLUMN product_id;
