DELIMITER $$
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateIssues`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateIssues`()
BEGIN
declare bug int;
declare cont int;
insert into issues(id,project_id,tracker_id,subject,description,due_date,status_id,assigned_to_id,priority_id,author_id,created_on,updated_on,start_date,done_ratio,estimated_hours,parent_id,root_id,lft,rgt)
select bugid,getprojectid(project),gettrackerid(category),subject,description,duedate,getstatusid(state),assignedtoid,getpriorityid(priority),getuserid(createdby),createdon,updatedon,startedon,doneratio,estimatedhours,null,bugid,1,2 from worktableissuesare where not exists(select id from issues where id=bugid) and isprocessed=0;
SET cont = (SELECT COUNT(1) FROM worktableissuesare WHERE isprocessed =0);
while (cont>0) do
set bug=(select bugid from worktableissuesare where isprocessed=0 limit 1);
select getcustomfield(bug);
set cont=cont-1;
End while;
END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateJournal`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateJournal`()
BEGIN
declare cnt int;
declare bug int;
SELECT COUNT(bugid) into cnt FROM worktableissueswere WHERE isprocessed =0 and numberofiterations>1;
while (cnt>0) do
set bug=(select bugid from worktableissueswere where isprocessed=0 and numberofiterations=1 limit 1);
select UpdateJournalEntries(bug);
set cnt=cnt-1;
update worktableissueswere
set isprocessed=1 where bugid=bug and numberofiterations=1;
end while;
END $$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`UpdateJournalEntries`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`UpdateJournalEntries`(Bug int(11)) RETURNS int(11)
BEGIN
declare description varchar(30);
declare oldsubject varchar(300);
declare newsubject varchar(300);
declare olddescription varchar(300);
declare newdescription varchar(300);
declare oldduedate varchar(30);
declare newduedate varchar(30);
declare oldstatus varchar(30);
declare newstatus varchar(30);
declare oldassignedtoid varchar(30);
declare newassignedtoid varchar(30);
declare oldpriority varchar(30);
declare newpriority varchar(30);
declare oldauthorid varchar(30);
declare newauthorid varchar(30);
declare oldcreatedon varchar(30);
declare newcreatedon varchar(30);
declare oldupdatedon varchar(30);
declare newupdatedon varchar(30);
declare oldstartdate varchar(30);
declare newstartdate varchar(30);
declare olddoneratio varchar(30);
declare newdoneratio varchar(30);
declare oldestimatedhours varchar(30);
declare newestimatedhours varchar(30);
declare oldenv varchar(30);
declare newenv varchar(30);
declare oldbrowser varchar(130);
declare newbrowser varchar(130);
declare oldseverity varchar(30);
declare newseverity varchar(30);
declare oldtype varchar(30);
declare newtype varchar(30);
declare oldyear varchar(30);
declare newyear varchar(30);
declare oldrepro varchar(30);
declare newrepro varchar(30);
declare oldattempts varchar(30);
declare newattempts varchar(30);
declare oldos varchar(30);
declare newos varchar(30);
declare oldlang varchar(30);
declare newlang varchar(30);
declare oldproject varchar(30);
declare newproject varchar(30);
declare oldtracker varchar(30);
declare newtracker varchar(30);
declare oldfixbuild varchar(30);
declare newfixbuild varchar(30);
declare oldfoundbuild varchar(30);
declare newfoundbuild varchar(30);
declare rev int;
declare pid int;
declare newNOI int;
set rev=(select count(1) from worktableissueswere where bugid=bug and numberofiterations>0);
while rev>1 do
select subject into oldsubject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select description into olddescription from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select duedate into oldduedate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select state into oldstatus from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select assignedtoid into oldassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select priority into oldpriority from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select authorid into oldauthorid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select updatedon into oldupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select startedon into oldstartdate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select project into oldproject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select doneratio into olddoneratio from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select estimatedhours into oldestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev-1 and bugid=bug;
select project into newproject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select subject into newsubject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select description into newdescription from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select duedate into newduedate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select state into newstatus from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select assignedtoid into newassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select priority into newpriority from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select authorid into newauthorid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select updatedon into newupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select startedon into newstartdate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select doneratio into newdoneratio from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select estimatedhours into newestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev and bugid=bug;
select environment into oldenv from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select browser into oldbrowser from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select severity into oldseverity from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select bugtype into oldtype from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select year into oldyear from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select ReproducedPercent into oldRepro from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select reproattempts into oldattempts from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select os into oldos from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select fixbuild into oldfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select language into oldlang from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select foundbuild into oldfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select environment into newenv from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select browser into newbrowser from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select severity into newseverity from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select bugtype into newtype from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select year into newyear from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select ReproducedPercent into newrepro from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select reproattempts into newattempts from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select os into newos from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select fixbuild into newfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select language into newlang from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select foundbuild into newfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
insert into journals(journalized_id,journalized_type,user_id,notes,created_on)
select bugid,'issue',newauthorid,newdescription,updatedon from worktableissueswere where isprocessed=0 and bugid=bug and numberofiterations=rev;
set pid=(select id from journals inner join worktableissueswere where journalized_id=bug and numberofiterations=rev and updatedon=created_on limit 1);
if oldenv != newenv then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('environment'),oldenv,newenv;
end if;
if oldbrowser != newbrowser then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('browser/device'),oldbrowser,newbrowser;
end if;
if oldseverity != newseverity then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('severity'),oldseverity,newseverity;
end if;
if oldtype != newtype then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('bug type'),oldtype,newtype;
end if;
if oldyear != newyear then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('year'),oldyear,newyear;
end if;
if oldrepro != newrepro then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('% Reproduced'),oldrepro,newrepro;
end if;
if oldattempts != newattempts then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Repro Attempts'),oldattempts,newattempts;
end if;
if oldos != newos then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('OS/Firmware'),oldos,newos;
end if;
if oldlang != newlang then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('language'),oldlang,newlang;
end if;
if oldfixbuild != newfixbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('fix Build'),oldfixbuild,newfixbuild;
end if;
if oldfoundbuild != newfoundbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Found Build'),oldfoundbuild,newfoundbuild;
end if;
if oldassignedtoid != newassignedtoid then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','assigned_to_id',oldassignedtoid,newassignedtoid;
end if;
if oldstatus != newstatus then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','status_id',getstatusid(oldstatus),getstatusid(newstatus);
end if;
if oldestimatedhours != newestimatedhours then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','estimated_hours',oldestimatedhours,newestimatedhours;
end if;
if olddoneratio != newdoneratio then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','done_ratio',olddoneratio,newdoneratio;
end if;
if oldstartdate != newstartdate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','start_date',oldstartdate,newstartdate;
end if;
if oldpriority != newpriority then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','priority_id',getpriorityid(oldpriority),getpriorityid(newpriority);
end if;
if oldduedate != newduedate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','due_date',oldduedate,newduedate;
end if;
if oldsubject != newsubject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','subject',oldsubject,newsubject;
end if;
if oldproject != newproject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','project_id',getprojectid(oldproject),getprojectid(newproject);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and bugid=bug;
set rev=rev-1;
end while;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUpdatedField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUpdatedField`(bug int(11)) RETURNS int(11)
BEGIN
declare oldv varchar(30);
declare newv varchar(30);
declare rev int(11);
update worktableissueswere
set isprocessed=1 where numberofiterations=1 and isprocessed=0;
select min(numberofiterations) into rev from worktableissueswere where isprocessed=0 and bugid=bug;
set oldv=(select getpriorityid(w.priority) from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select getpriorityid(priority) into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','priority_id',oldv,newv);
end if;
set oldv=(select w.severity from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select severity into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','severity',oldv,newv);
end if;
set oldv=(select w.assignedtoid from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select assignedtoid into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','assigned_to_id',oldv,newv);
end if;
set oldv=(select w.doneratio from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select doneratio into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','done_ratio',oldv,newv);
end if;
select getstatusid(wiw.state) into oldv
from worktableissueswere wiw
where
wiw.numberofiterations=rev-1
and wiw.bugid=bug
;
select getstatusid(w.state) into newv from worktableissueswere w where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','status_id',oldv,newv);
end if;
set oldv=(select w.estimatedhours from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select estimatedhours into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','estimatedhours',oldv,newv);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and isprocessed=0 and bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUserID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUserID`(ufield varchar(30)) RETURNS int(11)
BEGIN
declare id8 int;
select constid into id8 from tfsusers where displaypart=ufield;
return id8;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetTrackerID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetTrackerID`(vtracker varchar(30)) RETURNS int(11)
BEGIN
declare id3 int;
select id into id3 from trackers where name=vtracker;
return id3;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetStatusID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetStatusID`(vstatus varchar(30)) RETURNS int(11)
BEGIN
declare id1 int;
select id into id1 from issue_statuses where name=vstatus;
return id1;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetProjectID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetProjectID`(vproject varchar(30)) RETURNS int(11)
BEGIN
declare id4 int;
select id into id4 from projects where name=vproject;
return id4;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetPriorityID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetPriorityID`(vpriority varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from enumerations where name=vpriority;
return id2;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetCustomField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetCustomField`(bug int(11)) RETURNS int(11)
BEGIN
declare cvalue varchar(30);
declare cfield varchar(130);
set cvalue=(select environment from worktableissuesare w where bugid=bug);
select getfieldid('environment') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select source from worktableissuesare w where bugid=bug);
select getfieldid('source') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select browser from worktableissuesare w where bugid=bug);
select getfieldid('browser/device') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select severity from worktableissuesare w where bugid=bug);
select getfieldid('severity') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select bugtype from worktableissuesare w where bugid=bug);
select getfieldid('bug type') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select year from worktableissuesare w where bugid=bug);
select getfieldid('year') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproducedpercent from worktableissuesare w where bugid=bug);
select getfieldid('% Reproduced') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproattempts from worktableissuesare w where bugid=bug);
select getfieldid('Repro Attempts') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select os from worktableissuesare w where bugid=bug);
select getfieldid('os/firmware') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select language from worktableissuesare w where bugid=bug);
select getfieldid('language') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select fixbuild from worktableissuesare w where bugid=bug);
select getfieldid('fix build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select foundbuild from worktableissuesare w where bugid=bug);
select getfieldid('found build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
update worktableissuesare
set isprocessed=1 where bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetFieldID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetFieldID`(vfield varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from custom_fields where name=vfield;
return id2;
END;
$$
DELIMITER ;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissuesare`;
CREATE TABLE `bitnami_redmine`.`worktableissuesare` (
`BugID` varchar(20) NOT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`areapath` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`BugID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissueswere`;
CREATE TABLE `bitnami_redmine`.`worktableissueswere` (
`BugID` varchar(20) DEFAULT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`areapath` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
KEY `IX_ID_Iterations` (`BugID`,`NumberOfIterations`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`tfsauthors`;
CREATE TABLE `bitnami_redmine`.`tfsauthors` (
`bugid` int(10) unsigned NOT NULL,
`assignedtoid` int(10) unsigned NOT NULL,
`authorid` int(10) unsigned NOT NULL,
PRIMARY KEY (`bugid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`tfsusers`;
CREATE TABLE `bitnami_redmine`.`tfsusers` (
`constid` int(10) unsigned NOT NULL,
`displaypart` varchar(100) NOT NULL,
`FirstName` varchar(100) DEFAULT NULL,
`LastName` varchar(100) DEFAULT NULL,
`MiddleName` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionare`;
CREATE TABLE `bitnami_redmine`.`descriptionare` (
`ID` int(10) unsigned NOT NULL,
`Iteration` int(10) unsigned NOT NULL,
`Description` varchar(4500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionwere`;
CREATE TABLE `bitnami_redmine`.`descriptionwere` (
`Id` int(11) NOT NULL,
`Iteration` varchar(45) NOT NULL,
`Description` varchar(545) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SELECT * FROM users u limit 3;
SELECT * FROM tfsusers u where displaypart like '%[%' order by displaypart desc limit 300;
#delete from users where login not like 'administrator';
#delete from tfsusers ;
update issues i left join tfsauthors t on t.bugid=i.id
set assigned_to_id=t.assignedtoid;
update issues i left join tfsauthors t on t.bugid=i.id
set author_id=t.authorid;
load data local infile 'c:/users.csv' into table users
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(ID,login,hashed_password,firstname,lastname,mail,admin,status,last_login_on,language,auth_source_id,created_on,updated_on,type,identity_url,mail_notification,salt);
load data local infile 'c:/tfsusers.csv' into table tfsusers
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(constID,displaypart);
load data local infile 'c:/authorid.csv' into table tfsauthors
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(bugID,assignedtoid,authorid);
load data local infile 'c:/tfsarea.csv' into table tfsarea
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(area,subareapath,areapath);
update tfsarea
set area='BuddyBooth' where subareapath like 'MMO/BuddyBooth';
update tfsarea
set subareapath=' ' where subareapath not like '%/%';
DROP TABLE IF EXISTS `bitnami_redmine`.`tfsarea`;
CREATE TABLE `bitnami_redmine`.`tfsarea` (
`area` varchar(100) NOT NULL,
`subareapath` varchar(300) NOT NULL,
`areapath` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionare`;
CREATE TABLE `bitnami_redmine`.`descriptionare` (
`ID` int(10) unsigned NOT NULL,
`Iteration` int(10) unsigned NOT NULL,
`Description` varchar(4500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionwere`;
CREATE TABLE `bitnami_redmine`.`descriptionwere` (
`Id` int(11) NOT NULL,
`Iteration` varchar(45) NOT NULL,
`Description` varchar(545) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissueswere`;
CREATE TABLE `bitnami_redmine`.`worktableissueswere` (
`BugID` varchar(20) DEFAULT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`Comment` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
KEY `IX_ID_Iterations` (`BugID`,`NumberOfIterations`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissuesare`;
CREATE TABLE `bitnami_redmine`.`worktableissuesare` (
`BugID` varchar(20) NOT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`Comment` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`BugID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetCustomField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetCustomField`(bug int(11)) RETURNS int(11)
BEGIN
declare cvalue varchar(30);
declare cfield varchar(130);
set cvalue=(select environment from worktableissuesare w where bugid=bug);
select getfieldid('environment') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select source from worktableissuesare w where bugid=bug);
select getfieldid('source') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select browser from worktableissuesare w where bugid=bug);
select getfieldid('browser/device') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select severity from worktableissuesare w where bugid=bug);
select getfieldid('severity') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select bugtype from worktableissuesare w where bugid=bug);
select getfieldid('bug type') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select year from worktableissuesare w where bugid=bug);
select getfieldid('year') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproducedpercent from worktableissuesare w where bugid=bug);
select getfieldid('% Reproduced') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproattempts from worktableissuesare w where bugid=bug);
select getfieldid('Repro Attempts') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select os from worktableissuesare w where bugid=bug);
select getfieldid('os/firmware') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select language from worktableissuesare w where bugid=bug);
select getfieldid('language') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select fixbuild from worktableissuesare w where bugid=bug);
select getfieldid('fix build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select foundbuild from worktableissuesare w where bugid=bug);
select getfieldid('found build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
update worktableissuesare
set isprocessed=1 where bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetFieldID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetFieldID`(vfield varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from custom_fields where name=vfield;
return id2;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetPriorityID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetPriorityID`(vpriority varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from enumerations where name=vpriority;
return id2;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetProjectID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetProjectID`(vproject varchar(30)) RETURNS int(11)
BEGIN
declare id4 int;
select id into id4 from projects where name=vproject;
return id4;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetStatusID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetStatusID`(vstatus varchar(30)) RETURNS int(11)
BEGIN
declare id1 int;
select id into id1 from issue_statuses where name=vstatus;
return id1;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetTrackerID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetTrackerID`(vtracker varchar(30)) RETURNS int(11)
BEGIN
declare id3 int;
select id into id3 from trackers where name=vtracker;
return id3;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUpdatedField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUpdatedField`(bug int(11)) RETURNS int(11)
BEGIN
declare oldv varchar(30);
declare newv varchar(30);
declare rev int(11);
update worktableissueswere
set isprocessed=1 where numberofiterations=1 and isprocessed=0;
select min(numberofiterations) into rev from worktableissueswere where isprocessed=0 and bugid=bug;
set oldv=(select getpriorityid(w.priority) from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select getpriorityid(priority) into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','priority_id',oldv,newv);
end if;
set oldv=(select w.severity from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select severity into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','severity',oldv,newv);
end if;
set oldv=(select w.assignedtoid from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select assignedtoid into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','assigned_to_id',oldv,newv);
end if;
set oldv=(select w.doneratio from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select doneratio into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','done_ratio',oldv,newv);
end if;
select getstatusid(wiw.state) into oldv
from worktableissueswere wiw
where
wiw.numberofiterations=rev-1
and wiw.bugid=bug
;
select getstatusid(w.state) into newv from worktableissueswere w where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','status_id',oldv,newv);
end if;
set oldv=(select w.estimatedhours from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select estimatedhours into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','estimatedhours',oldv,newv);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and isprocessed=0 and bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUserID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUserID`(ufield varchar(30)) RETURNS int(11)
BEGIN
declare id8 int;
select constid into id8 from tfsusers where displaypart=ufield;
return id8;
END;
$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateIssues`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateIssues`()
BEGIN
declare bug int;
declare cont int;
insert into issues(id,project_id,tracker_id,subject,description,due_date,status_id,assigned_to_id,priority_id,author_id,created_on,updated_on,start_date,done_ratio,estimated_hours,parent_id,root_id,lft,rgt)
select bugid,getprojectid(project),gettrackerid(category),subject,description,duedate,getstatusid(state),assignedtoid,getpriorityid(priority),getuserid(createdby),createdon,updatedon,startedon,doneratio,estimatedhours,null,bugid,1,2 from worktableissuesare where not exists(select id from issues where id=bugid);
SET cont = (SELECT COUNT(1) FROM worktableissuesare WHERE isprocessed =0);
while (cont>0) do
set bug=(select bugid from worktableissuesare where isprocessed=0 limit 1);
select getcustomfield(bug);
set cont=cont-1;
End while;
END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateJournal`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateJournal`()
BEGIN
declare cnt int;
declare bug int;
SELECT COUNT(bugid) into cnt FROM worktableissueswere WHERE isprocessed =0 and numberofiterations>1;
while (cnt>0) do
set bug=(select bugid from worktableissueswere where isprocessed=0 and numberofiterations=1 limit 1);
select UpdateJournalEntries(bug);
set cnt=cnt-1;
update worktableissueswere
set isprocessed=1 where bugid=bug and numberofiterations=1;
end while;
END $$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`UpdateJournalEntries`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`UpdateJournalEntries`(Bug int(11)) RETURNS int(11)
BEGIN
declare description varchar(30);
declare oldsubject varchar(300);
declare newsubject varchar(300);
declare olddescription varchar(300);
declare newdescription varchar(300);
declare oldduedate varchar(30);
declare newduedate varchar(30);
declare oldstatus varchar(30);
declare newstatus varchar(30);
declare oldassignedtoid varchar(30);
declare newassignedtoid varchar(30);
declare oldpriority varchar(30);
declare newpriority varchar(30);
declare oldauthorid varchar(30);
declare newauthorid varchar(30);
declare oldcreatedon varchar(30);
declare newcreatedon varchar(30);
declare oldupdatedon varchar(30);
declare newupdatedon varchar(30);
declare oldstartdate varchar(30);
declare newstartdate varchar(30);
declare olddoneratio varchar(30);
declare newdoneratio varchar(30);
declare oldestimatedhours varchar(30);
declare newestimatedhours varchar(30);
declare oldenv varchar(30);
declare newenv varchar(30);
declare oldbrowser varchar(130);
declare newbrowser varchar(130);
declare oldseverity varchar(30);
declare newseverity varchar(30);
declare oldtype varchar(30);
declare newtype varchar(30);
declare oldyear varchar(30);
declare newyear varchar(30);
declare oldrepro varchar(30);
declare newrepro varchar(30);
declare oldattempts varchar(30);
declare newattempts varchar(30);
declare oldos varchar(30);
declare newos varchar(30);
declare oldlang varchar(30);
declare newlang varchar(30);
declare oldproject varchar(30);
declare newproject varchar(30);
declare oldtracker varchar(30);
declare newtracker varchar(30);
declare oldfixbuild varchar(30);
declare newfixbuild varchar(30);
declare oldfoundbuild varchar(30);
declare newfoundbuild varchar(30);
declare rev int;
declare pid int;
declare newNOI int;
set rev=(select count(1) from worktableissueswere where bugid=bug and numberofiterations>0);
while rev>1 do
select subject into oldsubject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select description into olddescription from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select duedate into oldduedate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select state into oldstatus from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select assignedtoid into oldassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select priority into oldpriority from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select authorid into oldauthorid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select updatedon into oldupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select startedon into oldstartdate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select project into oldproject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select doneratio into olddoneratio from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select estimatedhours into oldestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev-1 and bugid=bug;
select project into newproject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select subject into newsubject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select description into newdescription from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select duedate into newduedate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select state into newstatus from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select assignedtoid into newassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select priority into newpriority from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select authorid into newauthorid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select updatedon into newupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select startedon into newstartdate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select doneratio into newdoneratio from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select estimatedhours into newestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev and bugid=bug;
select environment into oldenv from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select browser into oldbrowser from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select severity into oldseverity from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select bugtype into oldtype from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select year into oldyear from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select ReproducedPercent into oldRepro from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select reproattempts into oldattempts from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select os into oldos from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select fixbuild into oldfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select language into oldlang from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select foundbuild into oldfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select environment into newenv from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select browser into newbrowser from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select severity into newseverity from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select bugtype into newtype from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select year into newyear from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select ReproducedPercent into newrepro from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select reproattempts into newattempts from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select os into newos from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select fixbuild into newfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select language into newlang from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select foundbuild into newfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
insert into journals(journalized_id,journalized_type,user_id,notes,created_on)
select bugid,'issue',newauthorid,newdescription,updatedon from worktableissueswere where isprocessed=0 and bugid=bug and numberofiterations=rev;
set pid=(select id from journals inner join worktableissueswere where journalized_id=bug and numberofiterations=rev and updatedon=created_on limit 1);
if oldenv != newenv then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('environment'),oldenv,newenv;
end if;
if oldbrowser != newbrowser then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('browser/device'),oldbrowser,newbrowser;
end if;
if oldseverity != newseverity then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('severity'),oldseverity,newseverity;
end if;
if oldtype != newtype then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('bug type'),oldtype,newtype;
end if;
if oldyear != newyear then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('year'),oldyear,newyear;
end if;
if oldrepro != newrepro then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('% Reproduced'),oldrepro,newrepro;
end if;
if oldattempts != newattempts then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Repro Attempts'),oldattempts,newattempts;
end if;
if oldos != newos then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('OS/Firmware'),oldos,newos;
end if;
if oldlang != newlang then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('language'),oldlang,newlang;
end if;
if oldfixbuild != newfixbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('fix Build'),oldfixbuild,newfixbuild;
end if;
if oldfoundbuild != newfoundbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Found Build'),oldfoundbuild,newfoundbuild;
end if;
if oldassignedtoid != newassignedtoid then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','assigned_to_id',oldassignedtoid,newassignedtoid;
end if;
if oldstatus != newstatus then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','status_id',getstatusid(oldstatus),getstatusid(newstatus);
end if;
if oldestimatedhours != newestimatedhours then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','estimated_hours',oldestimatedhours,newestimatedhours;
end if;
if olddoneratio != newdoneratio then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','done_ratio',olddoneratio,newdoneratio;
end if;
if oldstartdate != newstartdate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','start_date',oldstartdate,newstartdate;
end if;
if oldpriority != newpriority then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','priority_id',getpriorityid(oldpriority),getpriorityid(newpriority);
end if;
if oldduedate != newduedate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','due_date',oldduedate,newduedate;
end if;
if oldsubject != newsubject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','subject',oldsubject,newsubject;
end if;
if olddescription != newdescription and olddescription is not null and newdescription is not null then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','description',olddescription,newdescription;
end if;
if oldproject != newproject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','project_id',getprojectid(oldproject),getprojectid(newproject);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and bugid=bug;
set rev=rev-1;
end while;
return 0;
END;
$$
DELIMITER ;
delete from worktableissuesare;
delete from worktableissueswere;
delete from descriptionare;
delete from descriptionwere;
delete from issues;
delete from journals;
delete from journal_details;
delete from custom_values;
#run dts pkg are and were which copies files in d:\bugsare n bugswere directly
load data local infile 'c:/penguinsbugsare.csv' into table WorkTableIssuesAre fields terminated by '|' optionally enclosed by '`' lines terminated by '\r\n' ignore 1 lines(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
load data local infile 'c:/penguinsbugswere.csv' into table WorkTableIssueswere fields terminated by '|' enclosed by '`' lines terminated by '\r\n'(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
load data local infile 'c:/dragonsbugsare.csv' into table WorkTableIssuesAre fields terminated by '|' optionally enclosed by '`' lines terminated by '\r\n' ignore 1 lines(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
load data local infile 'c:/dragonsbugswere.csv' into table WorkTableIssueswere fields terminated by '|' enclosed by '`' lines terminated by '\r\n'(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
#run dts pkg desc are n were in updated...use total html converter ,remove tthis version line n copy to update then apply script n copy to descarefinal n descwerefinal
load data local infile 'c:/penguinsdescarefinal.txt' into table descriptionAre fields terminated by '|' optionally enclosed by '`' lines terminated by ';'(ID,Description,iteration) ;
load data local infile 'c:/penguinsdescwerefinal.txt' into table descriptionwere fields terminated by '|'optionally enclosed by '`' lines terminated by ';' (ID,description,iteration) ;
update descriptionare
set id=22848 where iteration=7 and id=0;
update descriptionwere
set id=25026 where iteration=1 and id=0;
load data local infile 'c:/dragonsdescarefinal.txt' into table descriptionAre fields terminated by '|' optionally enclosed by '`' lines terminated by ';'(ID,Description,iteration) ;
load data local infile 'c:/dragonsdescwerefinal.txt' into table descriptionwere fields terminated by '|' enclosed by '`' lines terminated by ';'(ID,description,iteration) ;
#load data local infile 'd:/update/penguinsdescwerefinal1.txt' into table descriptionwere fields terminated by '|'optionally enclosed by '`' lines terminated by ';'(ID,description,iteration) ;
update descriptionare
set id=24840 where iteration=2 and id=0;
#select * from descriptionwere where id=25026;
update descriptionwere
set id=25293 where iteration=1 and id=0;
delete from descriptionAre where id=0;
delete from descriptionWere where id=0;
update worktableissueswere w left join descriptionwere s on s.id=w.bugid and s.iteration=w.numberofiterations
set w.description=s.description;
update worktableissuesare w left join descriptionare s on s.id=w.bugid and s.iteration=w.numberofiterations
set w.description=s.description;
update worktableissuesare
set project='dragons' where project like '%dragons%';
update worktableissueswere
set project='dragons' where project like '%dragons%';
CALL UpdateIssues();
CALL UpdateJournal();
DROP TABLE IF EXISTS `bitnami_redmine`.`wrkattachments`;
CREATE TABLE `bitnami_redmine`.`wrkattachments` (
`bigid` int(11) DEFAULT NULL,
`filename` varchar(255) DEFAULT NULL,
`oiginalfilename` varchar(255) DEFAULT NULL,
`FileSizeInMB` decimal(13,2) NOT NULL,
`FileSizeINKB` decimal(13,2) NOT NULL,
`CreationDate` datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
load data local infile 'c:/attachments.txt' into table `bitnami_redmine`.`wrkattachments`
fields terminated by ',' (bigid,filename,oiginalfilename,FileSizeInMB,FileSizeINKB,creationdate);
SELECT * FROM wrkattachments w;
update wrkattachments
set creationdate='2012-11-12 12:28:50.133' where bigid=25134;
update wrkattachments
set bigid=25134 where bigid=0;
insert into `bitnami_redmine`.`attachments` (
`container_id` ,
`container_type` ,
`filename` ,
`disk_filename`,
`filesize` ,
`content_type` ,
`digest` ,
`downloads` ,
`author_id` ,
`created_on` ,
`description`
)
select bigid container_id,'Issue' container_type,filename filename,oiginalfilename disk_filename,
filesizeinkb*1024 filesize,null content_type,'' digest,0 downloads,null author_id,NOW() created_on,'' description
from wrkattachments;
update
`bitnami_redmine`.`attachments` att inner join issues i on i.id=att.container_id
set
att.author_id=i.author_id;
update worktableissuesare
set isprocessed=0;
update worktableissueswere
set isprocessed=0;
select * from projects;
select * from issues;
select * from journals;
select * from journal_details;
select * from custom_values;
select * from descriptionare;
select * from descriptionwere where id=0;
where id=23118;
select * from descriptionwere where iteration like '%5%';
select * from worktableissueswere where bugid like '%5%';
select * from worktableissuesare;
select * from worktableissueswere;
select * from worktableissueswere where bugid=22849;
select * from worktableissuesare where bugid=22849;
select * from issues where id=22849;
select * from journals where journalized_id in (22849);
select * from journal_details where journal_id between 12935 and 12945;
select * from custom_values where customized_id=22849;
update journals
set journalized_type='Issue';
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Dim FILE_NAME As String = "D:\update\dragonsdescare.txt"
' Dim FILE_NAMEWRITE As String = "D:\update\dragonsdescarefinal.TXT"
Dim FILE_NAME As String = "D:\update\dragonsdescwere.txt"
Dim FILE_NAMEWRITE As String = "D:\update\dragonsdescwerefinal.TXT"
'Dim FILE_NAME As String = "D:\update\penguinsdescare.txt"
'Dim FILE_NAMEWRITE As String = "D:\update\penguinsdescarefinal.TXT"
'Dim FILE_NAME As String = "D:\update\penguinsdescwere.txt"
'Dim FILE_NAMEWRITE As String = "D:\update\penguinsdescwerefinal.txt"
Dim str1 As String
Dim str2 As String
str1 = " "
str2 = " "
Dim str As String
str = " " + "This version of Total HTML Converter is unregistered."
' Dim strr As String
' strr = " "
'Dim FILE_NAME As String = "D:\update\deschtmlare.txt"
'Dim FILE_NAMEWRITE As String = "D:\update\descarefinal.TXT"
Dim I As Integer
I = 0
Dim TextLine As String
TextLine = ""
If System.IO.File.Exists(FILE_NAME) = True Then
Dim objReader As New System.IO.StreamReader(FILE_NAME)
Dim objWRITER As New System.IO.StreamWriter(FILE_NAMEWRITE)
Do While objReader.Peek() <> -1
'TextLine = Replace(objReader.ReadLine, vbTab, " ")
'TextLine = Replace(objReader.ReadLine, vbCrLf & vbCrLf, "")
TextLine = Replace(Replace(objReader.ReadLine, vbTab, " "), " ", " ")
' TextLine = Replace(TextLine, strr, "")
'If Len(TextLine) > 0 Then
' MsgBox(TextLine)
' MsgBox(Asc(TextLine))
'End If
If TextLine = vbNewLine Then
str1 = TextLine
End If
If Len(str2) = 0 And Len(str1) = 0 And Len(TextLine) = 0 Then
TextLine = vbNewLine
ElseIf Len(TextLine) = 0 Then
TextLine = Replace(Replace(Replace(objReader.ReadLine, vbTab, " "), " ", " "), vbNewLine, " ")
End If
TextLine = Replace(TextLine, str, Nothing)
'TextLine = Replace(TextLine, vbCr, "")
'TextLine = Replace(TextLine, vbLf, "")
'TextLine = Replace(TextLine, vbCrLf, "")
'TextLine = Replace(Replace(Replace(Replace(Replace(objReader.ReadLine, vbNewLine, ""), vbCrLf, ""), vbLf, ""), vbTab, " ")
' TextLine = Replace(TextLine, "�", " ")
TextLine = Replace(TextLine, "�", " ")
'TextLine = Replace(TextLine, "?????", vbCrLf)
'TextLine = Replace(Replace(Replace(TextLine, "-" + vbCrLf, "-"), "-" + vbLf, "-"), "-" + vbCr, "-")
' If LTrim(RTrim(TextLine)) <> "" And LTrim(RTrim(TextLine)) <> " " Then
'objWRITER.WriteLine(TextLine)
objWRITER.WriteLine(LTrim(RTrim(TextLine)))
TextLine = ""
' End If
str2 = str1
Loop
objWRITER.Close()
Else
MsgBox("File Does Not Exist")
End If
MsgBox("File CLOSED")
End Sub
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateIssues`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateIssues`()
BEGIN
declare bug int;
declare cont int;
insert into issues(id,project_id,tracker_id,subject,description,due_date,status_id,assigned_to_id,priority_id,author_id,created_on,updated_on,start_date,done_ratio,estimated_hours,parent_id,root_id,lft,rgt)
select bugid,getprojectid(project),gettrackerid(category),subject,description,duedate,getstatusid(state),assignedtoid,getpriorityid(priority),getuserid(createdby),createdon,updatedon,startedon,doneratio,estimatedhours,null,bugid,1,2 from worktableissuesare where not exists(select id from issues where id=bugid) and isprocessed=0;
SET cont = (SELECT COUNT(1) FROM worktableissuesare WHERE isprocessed =0);
while (cont>0) do
set bug=(select bugid from worktableissuesare where isprocessed=0 limit 1);
select getcustomfield(bug);
set cont=cont-1;
End while;
END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateJournal`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateJournal`()
BEGIN
declare cnt int;
declare bug int;
SELECT COUNT(bugid) into cnt FROM worktableissueswere WHERE isprocessed =0 and numberofiterations>1;
while (cnt>0) do
set bug=(select bugid from worktableissueswere where isprocessed=0 and numberofiterations=1 limit 1);
select UpdateJournalEntries(bug);
set cnt=cnt-1;
update worktableissueswere
set isprocessed=1 where bugid=bug and numberofiterations=1;
end while;
END $$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`UpdateJournalEntries`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`UpdateJournalEntries`(Bug int(11)) RETURNS int(11)
BEGIN
declare description varchar(30);
declare oldsubject varchar(300);
declare newsubject varchar(300);
declare olddescription varchar(300);
declare newdescription varchar(300);
declare oldduedate varchar(30);
declare newduedate varchar(30);
declare oldstatus varchar(30);
declare newstatus varchar(30);
declare oldassignedtoid varchar(30);
declare newassignedtoid varchar(30);
declare oldpriority varchar(30);
declare newpriority varchar(30);
declare oldauthorid varchar(30);
declare newauthorid varchar(30);
declare oldcreatedon varchar(30);
declare newcreatedon varchar(30);
declare oldupdatedon varchar(30);
declare newupdatedon varchar(30);
declare oldstartdate varchar(30);
declare newstartdate varchar(30);
declare olddoneratio varchar(30);
declare newdoneratio varchar(30);
declare oldestimatedhours varchar(30);
declare newestimatedhours varchar(30);
declare oldenv varchar(30);
declare newenv varchar(30);
declare oldbrowser varchar(130);
declare newbrowser varchar(130);
declare oldseverity varchar(30);
declare newseverity varchar(30);
declare oldtype varchar(30);
declare newtype varchar(30);
declare oldyear varchar(30);
declare newyear varchar(30);
declare oldrepro varchar(30);
declare newrepro varchar(30);
declare oldattempts varchar(30);
declare newattempts varchar(30);
declare oldos varchar(30);
declare newos varchar(30);
declare oldlang varchar(30);
declare newlang varchar(30);
declare oldproject varchar(30);
declare newproject varchar(30);
declare oldtracker varchar(30);
declare newtracker varchar(30);
declare oldfixbuild varchar(30);
declare newfixbuild varchar(30);
declare oldfoundbuild varchar(30);
declare newfoundbuild varchar(30);
declare rev int;
declare pid int;
declare newNOI int;
set rev=(select count(1) from worktableissueswere where bugid=bug and numberofiterations>0);
while rev>1 do
select subject into oldsubject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select description into olddescription from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select duedate into oldduedate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select state into oldstatus from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select assignedtoid into oldassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select priority into oldpriority from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select authorid into oldauthorid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select updatedon into oldupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select startedon into oldstartdate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select project into oldproject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select doneratio into olddoneratio from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select estimatedhours into oldestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev-1 and bugid=bug;
select project into newproject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select subject into newsubject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select description into newdescription from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select duedate into newduedate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select state into newstatus from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select assignedtoid into newassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select priority into newpriority from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select authorid into newauthorid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select updatedon into newupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select startedon into newstartdate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select doneratio into newdoneratio from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select estimatedhours into newestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev and bugid=bug;
select environment into oldenv from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select browser into oldbrowser from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select severity into oldseverity from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select bugtype into oldtype from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select year into oldyear from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select ReproducedPercent into oldRepro from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select reproattempts into oldattempts from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select os into oldos from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select fixbuild into oldfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select language into oldlang from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select foundbuild into oldfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select environment into newenv from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select browser into newbrowser from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select severity into newseverity from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select bugtype into newtype from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select year into newyear from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select ReproducedPercent into newrepro from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select reproattempts into newattempts from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select os into newos from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select fixbuild into newfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select language into newlang from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select foundbuild into newfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
insert into journals(journalized_id,journalized_type,user_id,notes,created_on)
select bugid,'issue',newauthorid,newdescription,updatedon from worktableissueswere where isprocessed=0 and bugid=bug and numberofiterations=rev;
set pid=(select id from journals inner join worktableissueswere where journalized_id=bug and numberofiterations=rev and updatedon=created_on limit 1);
if oldenv != newenv then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('environment'),oldenv,newenv;
end if;
if oldbrowser != newbrowser then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('browser/device'),oldbrowser,newbrowser;
end if;
if oldseverity != newseverity then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('severity'),oldseverity,newseverity;
end if;
if oldtype != newtype then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('bug type'),oldtype,newtype;
end if;
if oldyear != newyear then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('year'),oldyear,newyear;
end if;
if oldrepro != newrepro then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('% Reproduced'),oldrepro,newrepro;
end if;
if oldattempts != newattempts then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Repro Attempts'),oldattempts,newattempts;
end if;
if oldos != newos then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('OS/Firmware'),oldos,newos;
end if;
if oldlang != newlang then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('language'),oldlang,newlang;
end if;
if oldfixbuild != newfixbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('fix Build'),oldfixbuild,newfixbuild;
end if;
if oldfoundbuild != newfoundbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Found Build'),oldfoundbuild,newfoundbuild;
end if;
if oldassignedtoid != newassignedtoid then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','assigned_to_id',oldassignedtoid,newassignedtoid;
end if;
if oldstatus != newstatus then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','status_id',getstatusid(oldstatus),getstatusid(newstatus);
end if;
if oldestimatedhours != newestimatedhours then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','estimated_hours',oldestimatedhours,newestimatedhours;
end if;
if olddoneratio != newdoneratio then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','done_ratio',olddoneratio,newdoneratio;
end if;
if oldstartdate != newstartdate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','start_date',oldstartdate,newstartdate;
end if;
if oldpriority != newpriority then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','priority_id',getpriorityid(oldpriority),getpriorityid(newpriority);
end if;
if oldduedate != newduedate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','due_date',oldduedate,newduedate;
end if;
if oldsubject != newsubject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','subject',oldsubject,newsubject;
end if;
if oldproject != newproject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','project_id',getprojectid(oldproject),getprojectid(newproject);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and bugid=bug;
set rev=rev-1;
end while;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUpdatedField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUpdatedField`(bug int(11)) RETURNS int(11)
BEGIN
declare oldv varchar(30);
declare newv varchar(30);
declare rev int(11);
update worktableissueswere
set isprocessed=1 where numberofiterations=1 and isprocessed=0;
select min(numberofiterations) into rev from worktableissueswere where isprocessed=0 and bugid=bug;
set oldv=(select getpriorityid(w.priority) from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select getpriorityid(priority) into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','priority_id',oldv,newv);
end if;
set oldv=(select w.severity from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select severity into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','severity',oldv,newv);
end if;
set oldv=(select w.assignedtoid from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select assignedtoid into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','assigned_to_id',oldv,newv);
end if;
set oldv=(select w.doneratio from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select doneratio into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','done_ratio',oldv,newv);
end if;
select getstatusid(wiw.state) into oldv
from worktableissueswere wiw
where
wiw.numberofiterations=rev-1
and wiw.bugid=bug
;
select getstatusid(w.state) into newv from worktableissueswere w where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','status_id',oldv,newv);
end if;
set oldv=(select w.estimatedhours from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select estimatedhours into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','estimatedhours',oldv,newv);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and isprocessed=0 and bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUserID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUserID`(ufield varchar(30)) RETURNS int(11)
BEGIN
declare id8 int;
select constid into id8 from tfsusers where displaypart=ufield;
return id8;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetTrackerID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetTrackerID`(vtracker varchar(30)) RETURNS int(11)
BEGIN
declare id3 int;
select id into id3 from trackers where name=vtracker;
return id3;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetStatusID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetStatusID`(vstatus varchar(30)) RETURNS int(11)
BEGIN
declare id1 int;
select id into id1 from issue_statuses where name=vstatus;
return id1;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetProjectID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetProjectID`(vproject varchar(30)) RETURNS int(11)
BEGIN
declare id4 int;
select id into id4 from projects where name=vproject;
return id4;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetPriorityID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetPriorityID`(vpriority varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from enumerations where name=vpriority;
return id2;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetCustomField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetCustomField`(bug int(11)) RETURNS int(11)
BEGIN
declare cvalue varchar(30);
declare cfield varchar(130);
set cvalue=(select environment from worktableissuesare w where bugid=bug);
select getfieldid('environment') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select source from worktableissuesare w where bugid=bug);
select getfieldid('source') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select browser from worktableissuesare w where bugid=bug);
select getfieldid('browser/device') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select severity from worktableissuesare w where bugid=bug);
select getfieldid('severity') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select bugtype from worktableissuesare w where bugid=bug);
select getfieldid('bug type') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select year from worktableissuesare w where bugid=bug);
select getfieldid('year') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproducedpercent from worktableissuesare w where bugid=bug);
select getfieldid('% Reproduced') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproattempts from worktableissuesare w where bugid=bug);
select getfieldid('Repro Attempts') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select os from worktableissuesare w where bugid=bug);
select getfieldid('os/firmware') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select language from worktableissuesare w where bugid=bug);
select getfieldid('language') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select fixbuild from worktableissuesare w where bugid=bug);
select getfieldid('fix build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select foundbuild from worktableissuesare w where bugid=bug);
select getfieldid('found build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
update worktableissuesare
set isprocessed=1 where bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetFieldID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetFieldID`(vfield varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from custom_fields where name=vfield;
return id2;
END;
$$
DELIMITER ;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissuesare`;
CREATE TABLE `bitnami_redmine`.`worktableissuesare` (
`BugID` varchar(20) NOT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`areapath` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`BugID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissueswere`;
CREATE TABLE `bitnami_redmine`.`worktableissueswere` (
`BugID` varchar(20) DEFAULT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`areapath` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
KEY `IX_ID_Iterations` (`BugID`,`NumberOfIterations`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`tfsauthors`;
CREATE TABLE `bitnami_redmine`.`tfsauthors` (
`bugid` int(10) unsigned NOT NULL,
`assignedtoid` int(10) unsigned NOT NULL,
`authorid` int(10) unsigned NOT NULL,
PRIMARY KEY (`bugid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`tfsusers`;
CREATE TABLE `bitnami_redmine`.`tfsusers` (
`constid` int(10) unsigned NOT NULL,
`displaypart` varchar(100) NOT NULL,
`FirstName` varchar(100) DEFAULT NULL,
`LastName` varchar(100) DEFAULT NULL,
`MiddleName` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionare`;
CREATE TABLE `bitnami_redmine`.`descriptionare` (
`ID` int(10) unsigned NOT NULL,
`Iteration` int(10) unsigned NOT NULL,
`Description` varchar(4500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionwere`;
CREATE TABLE `bitnami_redmine`.`descriptionwere` (
`Id` int(11) NOT NULL,
`Iteration` varchar(45) NOT NULL,
`Description` varchar(545) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SELECT * FROM users u limit 3;
SELECT * FROM tfsusers u where displaypart like '%[%' order by displaypart desc limit 300;
#delete from users where login not like 'administrator';
#delete from tfsusers ;
update issues i left join tfsauthors t on t.bugid=i.id
set assigned_to_id=t.assignedtoid;
update issues i left join tfsauthors t on t.bugid=i.id
set author_id=t.authorid;
load data local infile 'c:/users.csv' into table users
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(ID,login,hashed_password,firstname,lastname,mail,admin,status,last_login_on,language,auth_source_id,created_on,updated_on,type,identity_url,mail_notification,salt);
load data local infile 'c:/tfsusers.csv' into table tfsusers
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(constID,displaypart);
load data local infile 'c:/authorid.csv' into table tfsauthors
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(bugID,assignedtoid,authorid);
load data local infile 'c:/tfsarea.csv' into table tfsarea
fields terminated by '|' optionally enclosed by '`'
lines terminated by '\r\n'(area,subareapath,areapath);
update tfsarea
set area='BuddyBooth' where subareapath like 'MMO/BuddyBooth';
update tfsarea
set subareapath=' ' where subareapath not like '%/%';
DROP TABLE IF EXISTS `bitnami_redmine`.`tfsarea`;
CREATE TABLE `bitnami_redmine`.`tfsarea` (
`area` varchar(100) NOT NULL,
`subareapath` varchar(300) NOT NULL,
`areapath` varchar(300) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionare`;
CREATE TABLE `bitnami_redmine`.`descriptionare` (
`ID` int(10) unsigned NOT NULL,
`Iteration` int(10) unsigned NOT NULL,
`Description` varchar(4500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`descriptionwere`;
CREATE TABLE `bitnami_redmine`.`descriptionwere` (
`Id` int(11) NOT NULL,
`Iteration` varchar(45) NOT NULL,
`Description` varchar(545) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissueswere`;
CREATE TABLE `bitnami_redmine`.`worktableissueswere` (
`BugID` varchar(20) DEFAULT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`Comment` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
KEY `IX_ID_Iterations` (`BugID`,`NumberOfIterations`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `bitnami_redmine`.`worktableissuesare`;
CREATE TABLE `bitnami_redmine`.`worktableissuesare` (
`BugID` varchar(20) NOT NULL,
`project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text,
`DueDate` date DEFAULT NULL,
`AssignedToID` varchar(300) DEFAULT NULL,
`AuthorID` varchar(300) DEFAULT NULL,
`CreatedOn` datetime DEFAULT NULL,
`UpdatedOn` datetime DEFAULT NULL,
`StartedOn` datetime DEFAULT NULL,
`DoneRatio` varchar(20) DEFAULT NULL,
`EstimatedHours` varchar(20) DEFAULT NULL,
`State` varchar(30) DEFAULT NULL,
`Reason` varchar(30) DEFAULT NULL,
`Assignee` varchar(50) DEFAULT NULL,
`Category` varchar(30) DEFAULT NULL,
`CreatedBy` varchar(30) DEFAULT NULL,
`NumberOfIterations` int(3) DEFAULT NULL,
`Environment` varchar(50) DEFAULT NULL,
`Severity` varchar(50) DEFAULT NULL,
`Priority` varchar(50) DEFAULT NULL,
`BugType` varchar(50) DEFAULT NULL,
`FoundIn` varchar(150) DEFAULT NULL,
`Source` varchar(150) DEFAULT NULL,
`FoundBuild` varchar(50) DEFAULT NULL,
`Year` varchar(50) DEFAULT NULL,
`ReproducedPercent` varchar(50) DEFAULT NULL,
`ReproAttempts` varchar(50) DEFAULT NULL,
`FixBuild` varchar(50) DEFAULT NULL,
`OS` varchar(50) DEFAULT NULL,
`Browser` varchar(50) DEFAULT NULL,
`Language` varchar(50) DEFAULT NULL,
`ResolvedBy` varchar(50) DEFAULT NULL,
`ResolvedReason` varchar(150) DEFAULT NULL,
`ResolvedDate` date DEFAULT NULL,
`RemainingWork` varchar(50) DEFAULT NULL,
`CompletedWork` varchar(50) DEFAULT NULL,
`Comment` varchar(500) DEFAULT NULL,
`IsProcessed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`BugID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetCustomField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetCustomField`(bug int(11)) RETURNS int(11)
BEGIN
declare cvalue varchar(30);
declare cfield varchar(130);
set cvalue=(select environment from worktableissuesare w where bugid=bug);
select getfieldid('environment') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select source from worktableissuesare w where bugid=bug);
select getfieldid('source') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select browser from worktableissuesare w where bugid=bug);
select getfieldid('browser/device') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select severity from worktableissuesare w where bugid=bug);
select getfieldid('severity') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select bugtype from worktableissuesare w where bugid=bug);
select getfieldid('bug type') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select year from worktableissuesare w where bugid=bug);
select getfieldid('year') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproducedpercent from worktableissuesare w where bugid=bug);
select getfieldid('% Reproduced') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select reproattempts from worktableissuesare w where bugid=bug);
select getfieldid('Repro Attempts') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select os from worktableissuesare w where bugid=bug);
select getfieldid('os/firmware') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select language from worktableissuesare w where bugid=bug);
select getfieldid('language') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select fixbuild from worktableissuesare w where bugid=bug);
select getfieldid('fix build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
set cvalue=(select foundbuild from worktableissuesare w where bugid=bug);
select getfieldid('found build') into cfield from worktableissuesare where bugid=bug and isprocessed=0;
if cvalue!='' then
insert into custom_values(customized_type,customized_id,custom_field_id,value)values('issue',bug,cfield,cvalue);
end if;
update worktableissuesare
set isprocessed=1 where bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetFieldID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetFieldID`(vfield varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from custom_fields where name=vfield;
return id2;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetPriorityID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetPriorityID`(vpriority varchar(30)) RETURNS int(11)
BEGIN
declare id2 int;
select id into id2 from enumerations where name=vpriority;
return id2;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetProjectID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetProjectID`(vproject varchar(30)) RETURNS int(11)
BEGIN
declare id4 int;
select id into id4 from projects where name=vproject;
return id4;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetStatusID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetStatusID`(vstatus varchar(30)) RETURNS int(11)
BEGIN
declare id1 int;
select id into id1 from issue_statuses where name=vstatus;
return id1;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetTrackerID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetTrackerID`(vtracker varchar(30)) RETURNS int(11)
BEGIN
declare id3 int;
select id into id3 from trackers where name=vtracker;
return id3;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUpdatedField`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUpdatedField`(bug int(11)) RETURNS int(11)
BEGIN
declare oldv varchar(30);
declare newv varchar(30);
declare rev int(11);
update worktableissueswere
set isprocessed=1 where numberofiterations=1 and isprocessed=0;
select min(numberofiterations) into rev from worktableissueswere where isprocessed=0 and bugid=bug;
set oldv=(select getpriorityid(w.priority) from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select getpriorityid(priority) into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','priority_id',oldv,newv);
end if;
set oldv=(select w.severity from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select severity into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','severity',oldv,newv);
end if;
set oldv=(select w.assignedtoid from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select assignedtoid into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','assigned_to_id',oldv,newv);
end if;
set oldv=(select w.doneratio from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select doneratio into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','done_ratio',oldv,newv);
end if;
select getstatusid(wiw.state) into oldv
from worktableissueswere wiw
where
wiw.numberofiterations=rev-1
and wiw.bugid=bug
;
select getstatusid(w.state) into newv from worktableissueswere w where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','status_id',oldv,newv);
end if;
set oldv=(select w.estimatedhours from worktableissueswere w where numberofiterations=rev-1 and bugid=bug);
select estimatedhours into newv from worktableissueswere where numberofiterations=rev and isprocessed=0 and bugid=bug;
if oldv not like newv then
insert into journal_details(journal_id,property,prop_key,old_value,value)values(bug,'attr','estimatedhours',oldv,newv);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and isprocessed=0 and bugid=bug;
return 0;
END;
$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`GetUserID`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`GetUserID`(ufield varchar(30)) RETURNS int(11)
BEGIN
declare id8 int;
select constid into id8 from tfsusers where displaypart=ufield;
return id8;
END;
$$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateIssues`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateIssues`()
BEGIN
declare bug int;
declare cont int;
insert into issues(id,project_id,tracker_id,subject,description,due_date,status_id,assigned_to_id,priority_id,author_id,created_on,updated_on,start_date,done_ratio,estimated_hours,parent_id,root_id,lft,rgt)
select bugid,getprojectid(project),gettrackerid(category),subject,description,duedate,getstatusid(state),assignedtoid,getpriorityid(priority),getuserid(createdby),createdon,updatedon,startedon,doneratio,estimatedhours,null,bugid,1,2 from worktableissuesare where not exists(select id from issues where id=bugid);
SET cont = (SELECT COUNT(1) FROM worktableissuesare WHERE isprocessed =0);
while (cont>0) do
set bug=(select bugid from worktableissuesare where isprocessed=0 limit 1);
select getcustomfield(bug);
set cont=cont-1;
End while;
END $$
DELIMITER ;
DELIMITER $$
DROP PROCEDURE IF EXISTS `bitnami_redmine`.`UpdateJournal`$$
CREATE DEFINER=`root`@`%` PROCEDURE `bitnami_redmine`.`UpdateJournal`()
BEGIN
declare cnt int;
declare bug int;
SELECT COUNT(bugid) into cnt FROM worktableissueswere WHERE isprocessed =0 and numberofiterations>1;
while (cnt>0) do
set bug=(select bugid from worktableissueswere where isprocessed=0 and numberofiterations=1 limit 1);
select UpdateJournalEntries(bug);
set cnt=cnt-1;
update worktableissueswere
set isprocessed=1 where bugid=bug and numberofiterations=1;
end while;
END $$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `bitnami_redmine`.`UpdateJournalEntries`$$
CREATE DEFINER=`root`@`%` FUNCTION `bitnami_redmine`.`UpdateJournalEntries`(Bug int(11)) RETURNS int(11)
BEGIN
declare description varchar(30);
declare oldsubject varchar(300);
declare newsubject varchar(300);
declare olddescription varchar(300);
declare newdescription varchar(300);
declare oldduedate varchar(30);
declare newduedate varchar(30);
declare oldstatus varchar(30);
declare newstatus varchar(30);
declare oldassignedtoid varchar(30);
declare newassignedtoid varchar(30);
declare oldpriority varchar(30);
declare newpriority varchar(30);
declare oldauthorid varchar(30);
declare newauthorid varchar(30);
declare oldcreatedon varchar(30);
declare newcreatedon varchar(30);
declare oldupdatedon varchar(30);
declare newupdatedon varchar(30);
declare oldstartdate varchar(30);
declare newstartdate varchar(30);
declare olddoneratio varchar(30);
declare newdoneratio varchar(30);
declare oldestimatedhours varchar(30);
declare newestimatedhours varchar(30);
declare oldenv varchar(30);
declare newenv varchar(30);
declare oldbrowser varchar(130);
declare newbrowser varchar(130);
declare oldseverity varchar(30);
declare newseverity varchar(30);
declare oldtype varchar(30);
declare newtype varchar(30);
declare oldyear varchar(30);
declare newyear varchar(30);
declare oldrepro varchar(30);
declare newrepro varchar(30);
declare oldattempts varchar(30);
declare newattempts varchar(30);
declare oldos varchar(30);
declare newos varchar(30);
declare oldlang varchar(30);
declare newlang varchar(30);
declare oldproject varchar(30);
declare newproject varchar(30);
declare oldtracker varchar(30);
declare newtracker varchar(30);
declare oldfixbuild varchar(30);
declare newfixbuild varchar(30);
declare oldfoundbuild varchar(30);
declare newfoundbuild varchar(30);
declare rev int;
declare pid int;
declare newNOI int;
set rev=(select count(1) from worktableissueswere where bugid=bug and numberofiterations>0);
while rev>1 do
select subject into oldsubject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select description into olddescription from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select duedate into oldduedate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select state into oldstatus from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select assignedtoid into oldassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select priority into oldpriority from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select authorid into oldauthorid from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select updatedon into oldupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select startedon into oldstartdate from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select project into oldproject from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select doneratio into olddoneratio from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select estimatedhours into oldestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev-1 and bugid=bug;
select project into newproject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select subject into newsubject from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select description into newdescription from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select duedate into newduedate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select state into newstatus from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select assignedtoid into newassignedtoid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select priority into newpriority from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select authorid into newauthorid from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select updatedon into newupdatedon from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select startedon into newstartdate from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select doneratio into newdoneratio from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select estimatedhours into newestimatedhours from worktableissueswere where isprocessed=0 and numberofiterations=rev and bugid=bug;
select environment into oldenv from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select browser into oldbrowser from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select severity into oldseverity from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select bugtype into oldtype from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select year into oldyear from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select ReproducedPercent into oldRepro from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select reproattempts into oldattempts from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select os into oldos from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select fixbuild into oldfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select language into oldlang from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select foundbuild into oldfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev-1 and isprocessed=0;
select environment into newenv from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select browser into newbrowser from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select severity into newseverity from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select bugtype into newtype from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select year into newyear from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select ReproducedPercent into newrepro from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select reproattempts into newattempts from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select os into newos from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select fixbuild into newfixbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select language into newlang from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
select foundbuild into newfoundbuild from worktableissueswere where bugid=bug and numberofiterations=rev and isprocessed=0;
insert into journals(journalized_id,journalized_type,user_id,notes,created_on)
select bugid,'issue',newauthorid,newdescription,updatedon from worktableissueswere where isprocessed=0 and bugid=bug and numberofiterations=rev;
set pid=(select id from journals inner join worktableissueswere where journalized_id=bug and numberofiterations=rev and updatedon=created_on limit 1);
if oldenv != newenv then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('environment'),oldenv,newenv;
end if;
if oldbrowser != newbrowser then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('browser/device'),oldbrowser,newbrowser;
end if;
if oldseverity != newseverity then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('severity'),oldseverity,newseverity;
end if;
if oldtype != newtype then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('bug type'),oldtype,newtype;
end if;
if oldyear != newyear then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('year'),oldyear,newyear;
end if;
if oldrepro != newrepro then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('% Reproduced'),oldrepro,newrepro;
end if;
if oldattempts != newattempts then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Repro Attempts'),oldattempts,newattempts;
end if;
if oldos != newos then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('OS/Firmware'),oldos,newos;
end if;
if oldlang != newlang then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('language'),oldlang,newlang;
end if;
if oldfixbuild != newfixbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('fix Build'),oldfixbuild,newfixbuild;
end if;
if oldfoundbuild != newfoundbuild then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'cf',getfieldid('Found Build'),oldfoundbuild,newfoundbuild;
end if;
if oldassignedtoid != newassignedtoid then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','assigned_to_id',oldassignedtoid,newassignedtoid;
end if;
if oldstatus != newstatus then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','status_id',getstatusid(oldstatus),getstatusid(newstatus);
end if;
if oldestimatedhours != newestimatedhours then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','estimated_hours',oldestimatedhours,newestimatedhours;
end if;
if olddoneratio != newdoneratio then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','done_ratio',olddoneratio,newdoneratio;
end if;
if oldstartdate != newstartdate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','start_date',oldstartdate,newstartdate;
end if;
if oldpriority != newpriority then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','priority_id',getpriorityid(oldpriority),getpriorityid(newpriority);
end if;
if oldduedate != newduedate then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','due_date',oldduedate,newduedate;
end if;
if oldsubject != newsubject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','subject',oldsubject,newsubject;
end if;
if olddescription != newdescription and olddescription is not null and newdescription is not null then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','description',olddescription,newdescription;
end if;
if oldproject != newproject then
insert into journal_details(journal_id,property,prop_key,old_value,value)
select pid,'attr','project_id',getprojectid(oldproject),getprojectid(newproject);
end if;
update worktableissueswere
set isprocessed=1 where numberofiterations=rev and bugid=bug;
set rev=rev-1;
end while;
return 0;
END;
$$
DELIMITER ;
delete from worktableissuesare;
delete from worktableissueswere;
delete from descriptionare;
delete from descriptionwere;
delete from issues;
delete from journals;
delete from journal_details;
delete from custom_values;
#run dts pkg are and were which copies files in d:\bugsare n bugswere directly
load data local infile 'c:/penguinsbugsare.csv' into table WorkTableIssuesAre fields terminated by '|' optionally enclosed by '`' lines terminated by '\r\n' ignore 1 lines(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
load data local infile 'c:/penguinsbugswere.csv' into table WorkTableIssueswere fields terminated by '|' enclosed by '`' lines terminated by '\r\n'(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
load data local infile 'c:/dragonsbugsare.csv' into table WorkTableIssuesAre fields terminated by '|' optionally enclosed by '`' lines terminated by '\r\n' ignore 1 lines(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
load data local infile 'c:/dragonsbugswere.csv' into table WorkTableIssueswere fields terminated by '|' enclosed by '`' lines terminated by '\r\n'(BugID,Subject,Description,DueDate,AssignedToID,AuthorID,CreatedOn,UpdatedOn,StartedOn,DoneRatio,EstimatedHours,State,Reason,Assignee,Category,CreatedBy,NumberOfIterations,Environment,Severity,Priority,BugType,FoundIn,Source,FoundBuild,Year,ReproducedPercent,ReproAttempts,FixBuild,OS,Browser,Language,ResolvedBy,ResolvedReason,ResolvedDate,RemainingWork,CompletedWork,AreaPath,project);
#run dts pkg desc are n were in updated...use total html converter ,remove tthis version line n copy to update then apply script n copy to descarefinal n descwerefinal
load data local infile 'c:/penguinsdescarefinal.txt' into table descriptionAre fields terminated by '|' optionally enclosed by '`' lines terminated by ';'(ID,Description,iteration) ;
load data local infile 'c:/penguinsdescwerefinal.txt' into table descriptionwere fields terminated by '|'optionally enclosed by '`' lines terminated by ';' (ID,description,iteration) ;
update descriptionare
set id=22848 where iteration=7 and id=0;
update descriptionwere
set id=25026 where iteration=1 and id=0;
load data local infile 'c:/dragonsdescarefinal.txt' into table descriptionAre fields terminated by '|' optionally enclosed by '`' lines terminated by ';'(ID,Description,iteration) ;
load data local infile 'c:/dragonsdescwerefinal.txt' into table descriptionwere fields terminated by '|' enclosed by '`' lines terminated by ';'(ID,description,iteration) ;
#load data local infile 'd:/update/penguinsdescwerefinal1.txt' into table descriptionwere fields terminated by '|'optionally enclosed by '`' lines terminated by ';'(ID,description,iteration) ;
update descriptionare
set id=24840 where iteration=2 and id=0;
#select * from descriptionwere where id=25026;
update descriptionwere
set id=25293 where iteration=1 and id=0;
delete from descriptionAre where id=0;
delete from descriptionWere where id=0;
update worktableissueswere w left join descriptionwere s on s.id=w.bugid and s.iteration=w.numberofiterations
set w.description=s.description;
update worktableissuesare w left join descriptionare s on s.id=w.bugid and s.iteration=w.numberofiterations
set w.description=s.description;
update worktableissuesare
set project='dragons' where project like '%dragons%';
update worktableissueswere
set project='dragons' where project like '%dragons%';
CALL UpdateIssues();
CALL UpdateJournal();
DROP TABLE IF EXISTS `bitnami_redmine`.`wrkattachments`;
CREATE TABLE `bitnami_redmine`.`wrkattachments` (
`bigid` int(11) DEFAULT NULL,
`filename` varchar(255) DEFAULT NULL,
`oiginalfilename` varchar(255) DEFAULT NULL,
`FileSizeInMB` decimal(13,2) NOT NULL,
`FileSizeINKB` decimal(13,2) NOT NULL,
`CreationDate` datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
load data local infile 'c:/attachments.txt' into table `bitnami_redmine`.`wrkattachments`
fields terminated by ',' (bigid,filename,oiginalfilename,FileSizeInMB,FileSizeINKB,creationdate);
SELECT * FROM wrkattachments w;
update wrkattachments
set creationdate='2012-11-12 12:28:50.133' where bigid=25134;
update wrkattachments
set bigid=25134 where bigid=0;
insert into `bitnami_redmine`.`attachments` (
`container_id` ,
`container_type` ,
`filename` ,
`disk_filename`,
`filesize` ,
`content_type` ,
`digest` ,
`downloads` ,
`author_id` ,
`created_on` ,
`description`
)
select bigid container_id,'Issue' container_type,filename filename,oiginalfilename disk_filename,
filesizeinkb*1024 filesize,null content_type,'' digest,0 downloads,null author_id,NOW() created_on,'' description
from wrkattachments;
update
`bitnami_redmine`.`attachments` att inner join issues i on i.id=att.container_id
set
att.author_id=i.author_id;
update worktableissuesare
set isprocessed=0;
update worktableissueswere
set isprocessed=0;
select * from projects;
select * from issues;
select * from journals;
select * from journal_details;
select * from custom_values;
select * from descriptionare;
select * from descriptionwere where id=0;
where id=23118;
select * from descriptionwere where iteration like '%5%';
select * from worktableissueswere where bugid like '%5%';
select * from worktableissuesare;
select * from worktableissueswere;
select * from worktableissueswere where bugid=22849;
select * from worktableissuesare where bugid=22849;
select * from issues where id=22849;
select * from journals where journalized_id in (22849);
select * from journal_details where journal_id between 12935 and 12945;
select * from custom_values where customized_id=22849;
update journals
set journalized_type='Issue';
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Dim FILE_NAME As String = "D:\update\dragonsdescare.txt"
' Dim FILE_NAMEWRITE As String = "D:\update\dragonsdescarefinal.TXT"
Dim FILE_NAME As String = "D:\update\dragonsdescwere.txt"
Dim FILE_NAMEWRITE As String = "D:\update\dragonsdescwerefinal.TXT"
'Dim FILE_NAME As String = "D:\update\penguinsdescare.txt"
'Dim FILE_NAMEWRITE As String = "D:\update\penguinsdescarefinal.TXT"
'Dim FILE_NAME As String = "D:\update\penguinsdescwere.txt"
'Dim FILE_NAMEWRITE As String = "D:\update\penguinsdescwerefinal.txt"
Dim str1 As String
Dim str2 As String
str1 = " "
str2 = " "
Dim str As String
str = " " + "This version of Total HTML Converter is unregistered."
' Dim strr As String
' strr = " "
'Dim FILE_NAME As String = "D:\update\deschtmlare.txt"
'Dim FILE_NAMEWRITE As String = "D:\update\descarefinal.TXT"
Dim I As Integer
I = 0
Dim TextLine As String
TextLine = ""
If System.IO.File.Exists(FILE_NAME) = True Then
Dim objReader As New System.IO.StreamReader(FILE_NAME)
Dim objWRITER As New System.IO.StreamWriter(FILE_NAMEWRITE)
Do While objReader.Peek() <> -1
'TextLine = Replace(objReader.ReadLine, vbTab, " ")
'TextLine = Replace(objReader.ReadLine, vbCrLf & vbCrLf, "")
TextLine = Replace(Replace(objReader.ReadLine, vbTab, " "), " ", " ")
' TextLine = Replace(TextLine, strr, "")
'If Len(TextLine) > 0 Then
' MsgBox(TextLine)
' MsgBox(Asc(TextLine))
'End If
If TextLine = vbNewLine Then
str1 = TextLine
End If
If Len(str2) = 0 And Len(str1) = 0 And Len(TextLine) = 0 Then
TextLine = vbNewLine
ElseIf Len(TextLine) = 0 Then
TextLine = Replace(Replace(Replace(objReader.ReadLine, vbTab, " "), " ", " "), vbNewLine, " ")
End If
TextLine = Replace(TextLine, str, Nothing)
'TextLine = Replace(TextLine, vbCr, "")
'TextLine = Replace(TextLine, vbLf, "")
'TextLine = Replace(TextLine, vbCrLf, "")
'TextLine = Replace(Replace(Replace(Replace(Replace(objReader.ReadLine, vbNewLine, ""), vbCrLf, ""), vbLf, ""), vbTab, " ")
' TextLine = Replace(TextLine, "�", " ")
TextLine = Replace(TextLine, "�", " ")
'TextLine = Replace(TextLine, "?????", vbCrLf)
'TextLine = Replace(Replace(Replace(TextLine, "-" + vbCrLf, "-"), "-" + vbLf, "-"), "-" + vbCr, "-")
' If LTrim(RTrim(TextLine)) <> "" And LTrim(RTrim(TextLine)) <> " " Then
'objWRITER.WriteLine(TextLine)
objWRITER.WriteLine(LTrim(RTrim(TextLine)))
TextLine = ""
' End If
str2 = str1
Loop
objWRITER.Close()
Else
MsgBox("File Does Not Exist")
End If
MsgBox("File CLOSED")
End Sub
No comments:
Post a Comment