migration.sql

--
-- changes: rename type -> kind
--

ALTER TABLE changes CHANGE type kind VARCHAR(255);

--
-- stories: add state, drop complete
--

ALTER TABLE stories ADD state VARCHAR(255);
ALTER TABLE stories DROP complete;

--
-- stories: determine state
--

UPDATE stories SET state = IF(completed_at IS NOT NULL,'completed',NULL);
UPDATE stories SET state = IF(assigned_user_id IS NULL,'ready','working') WHERE completed_at IS NULL AND iteration_id IS NOT NULL;

--
-- users: add login_email
--

ALTER TABLE users add login_email VARCHAR(255);

--
-- users: determine login_email
--

UPDATE users SET login_email = email;

--
-- users: alter restrictions add NOT NULL
--

ALTER TABLE users MODIFY login_email VARCHAR(255) NOT NULL;

--
-- tags: add color, project_id
--

ALTER TABLE tags ADD color VARCHAR(255);
ALTER TABLE tags ADD project_id INT(11);

--
-- rename taggings to story taggings
--

RENAME TABLE taggings TO story_taggings;
ALTER TABLE story_taggings CHANGE taggable_id story_id INT(11);
ALTER TABLE story_taggings DROP context;
ALTER TABLE story_taggings DROP created_at;
ALTER TABLE story_taggings DROP taggable_type;
ALTER TABLE story_taggings DROP tagger_id;
ALTER TABLE story_taggings DROP tagger_type;

--
-- authorization_tokens: add updated_at
--

ALTER TABLE authorization_tokens ADD updated_at DATETIME;

--
-- drop tables
--

DROP TABLE days;
DROP TABLE pages;
DROP TABLE schema_migrations; /* ActiveRecord migrations */
DROP TABLE settings;
DROP TABLE stories_tags;
DROP TABLE story_assigned_emails;
DROP TABLE story_completed_emails;
DROP TABLE versions;

--
-- drop indices
--

DROP INDEX index_taggings_on_tag_id ON story_taggings;
DROP INDEX index_taggings_on_taggable_id_and_taggable_type_and_context ON story_taggings;

UPDATE stories s
INNER JOIN iterations i on
    i.id = s.iteration_id
set s.iteration_id = i.number