update_from_1.0_to_1.1.sql

tinypm敏捷开发工具是一界面简单功能齐全的敏捷开发工具 适合初学者使用

ALTER TABLE `tpm_wiki_pages` ADD COLUMN `projectId` INTEGER AFTER `lastModifiedBy`;
ALTER TABLE `tpm_wiki_pages`
 ADD CONSTRAINT `FK5D89720374EB8C8D` FOREIGN KEY `FK5D89720374EB8C8D` (`projectId`)
    REFERENCES `tpm_projects` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT;


CREATE TABLE  `tpm_project_estimation_scale` (
  `projectId` int(11) NOT NULL,
  `element` double default NULL,
  KEY `FKF5C8BF8A74EB8C8D` (`projectId`),
  CONSTRAINT `FKF5C8BF8A74EB8C8D` FOREIGN KEY (`projectId`) REFERENCES `tpm_projects` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `tpm_project_estimation_scale` select p.id, 0 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 0.5 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 1 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 2 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 3 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 5 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 8 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 13 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 20 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 40 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 80 from tpm_projects p;
insert into `tpm_project_estimation_scale` select p.id, 100 from tpm_projects p;

ALTER TABLE `tpm_projects` ADD COLUMN `iterationLength` int(11) default NULL;
UPDATE `tpm_projects` SET `iterationLength` = 14;

ALTER TABLE `tpm_user_stories` ADD COLUMN `color` VARCHAR(255);
UPDATE `tpm_user_stories` SET `color` = 'YELLOW' WHERE `color` IS NULL;

UPDATE `tpm_priorities` SET name = 'MUST_HAVE' WHERE priority = 1;
UPDATE `tpm_priorities` SET name = 'SHOULD_HAVE' WHERE priority = 2;
UPDATE `tpm_priorities` SET name = 'COULD_HAVE' WHERE priority = 3;
UPDATE `tpm_priorities` SET name = 'WONT_HAVE' WHERE priority = 4;

INSERT INTO tpm_user_properties(code, userId, value)
SELECT 'com.agilers.tpm.locale', u.Id, 'en' FROM tpm_users u;

UPDATE tpm_iterations SET name = 'label.iteration';



UPDATE `tpm_privilege_groups` SET `position` = 0 where code = 'GENERAL';
UPDATE `tpm_privilege_groups` SET `position` = 1 where code = 'ITERATIONS';
UPDATE `tpm_privilege_groups` SET `position` = 2 where code = 'USER_STORIES';
UPDATE `tpm_privilege_groups` SET `position` = 3 where code = 'TASKS';
UPDATE `tpm_privilege_groups` SET `position` = 4 where code = 'MANAGE_USERS';
UPDATE `tpm_privilege_groups` SET `position` = 5 where code = 'MANAGE_ROLES';
UPDATE `tpm_privilege_groups` SET `position` = 6 where code = 'MANAGE_PROJECTS';

UPDATE `tpm_features` SET `position` = 0 where code = 'accountManager.save';
UPDATE `tpm_features` SET `position` = 1 where code = 'applicationSettings.save';
UPDATE `tpm_features` SET `position` = 2 where code = 'userProjects.backlog';
UPDATE `tpm_features` SET `position` = 3 where code = 'userProjects.taskboard';
UPDATE `tpm_features` SET `position` = 4 where code = 'historyManager.list';

UPDATE `tpm_privilege_group_features` SET `privilegeGroupCode` = 'GENERAL' where featureCode = 'userProjects.backlog';
UPDATE `tpm_privilege_group_features` SET `privilegeGroupCode` = 'GENERAL' where featureCode = 'userProjects.taskboard';

insert into `tpm_features` (code, `position`) values
('wiki.pages',5);

insert into `tpm_privilege_group_features` (privilegeGroupCode, featureCode) values
('GENERAL','wiki.pages');

insert into `tpm_role_features` (roleId, featureCode) select rf.roleId, 'wiki.pages' from `tpm_role_features` rf where rf.featureCode = 'dashboard.startupwiki';
insert into `tpm_role_features` (roleId, featureCode) select rf.roleId, 'wiki.pages' from `tpm_role_features` rf where rf.featureCode = 'userProjects.wiki';

delete from `tpm_role_features` where featureCode = 'dashboard.startupwiki';
delete from `tpm_role_features` where featureCode = 'userProjects.wiki';
delete from `tpm_role_features` where featureCode = 'userProjects.activities';

delete from `tpm_privilege_group_features` where featureCode = 'dashboard.startupwiki';
delete from `tpm_privilege_group_features` where featureCode = 'userProjects.wiki';
delete from `tpm_privilege_group_features` where featureCode = 'userProjects.activities';

delete from `tpm_features` where code = 'dashboard.startupwiki';
delete from `tpm_features` where code = 'userProjects.wiki';
delete from `tpm_features` where code = 'userProjects.activities';

delete from `tpm_privilege_groups` where code = 'MY_PROJECTS';

ALTER TABLE `tpm_projects` ADD COLUMN `defaultTask` VARCHAR(255);
update `tpm_projects` set `defaultTask` = 'Implement and test user story "{0}".';


insert into `tpm_features` (code, `position`) values ('iterationManager.close', 6);
insert into `tpm_privilege_group_features` (privilegeGroupCode, featureCode) values
('ITERATIONS','iterationManager.close');

insert into `tpm_role_features` (roleId, featureCode) select rf.roleId, 'iterationManager.close' from `tpm_role_features` rf;