SQL Server 2012 Merge with list

I have a table named Usersettings where I want to execute this query

UPDATE US
                  SET MultiplayerEnabled = @MultiplayerEnabled,
                        Locale = @Locale
                        
                  FROM KA_UserSettings US WITH(NOLOCK)
                   INNER JOIN @UserID U ON U.UserID=US.UserID
                 
          
   
                  INSERT INTO KA_UserSettings (UserID, MultiplayerEnabled, Locale)
                  SELECT userid, @MultiplayerEnabled, @Locale FROM @UserID U
                  WHERE NOT EXISTS(SELECT 1 FROM KA_UserSettings US WITH (NOLOCK)

 WHERE U.UserID=US.UserID)

I can replace this by using merge in higher version of sql server

MERGE dbo.KA_UserSettings AS A
USING(SELECT UserID,@MultiplayerEnabled,@Locale FROM @UserID ) 

AS B (UserID, MultiplayerEnabled, Locale)
ON  (A.UserID=B.USerID)
WHEN MATCHED THEN UPDATE 

 SET A.MultiplayerEnabled = B.MultiplayerEnabled,A.Locale = B.Locale
WHEN NOT MATCHED   THEN 

INSERT(UserID, MultiplayerEnabled, Locale) 

VALUES (B.UserID,B.MultiplayerEnabled,B.Locale)
     OUTPUT $ACTION;

Create a proxy for ssis package

create a credential with some name say test having your credentials
create a proxy account using same credential in proxies ssis package execution.
While setting up a job set run as that proxy account to execute 

"SELECT  * FROM  ka_eventlog
   WHERE     EventLogID between '"+ (DT_WSTR,15) @[User::minid]+"'  AND '"+ (DT_WSTR,15) @[User::maxid]+"'"

Migrate tfs to redmine scripts

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

Amazon Dynamo DB

What’s Amazon DynamoDB?

DynamoDB is one of the most recent services offered by Amazon.com. Announced on January 18, 2012, it is a fully managed NoSQL database service that provides fast and predictable performance along with excellent scalability. Let’s quickly analyze its positive and negative aspects in the lists below:

PROS:

• Scalable
• Simple
• Hosted by Amazon
• Good SDK
• Free account for small amount of reads/writes
• Pricing based on throughput

CONS:

• Poor documentation
• Limited data types
• Poor query comparison operators
• Unable to do complex queries
• 64KB limit on row size
• 1MB limit on querying

Pros of using DynamoDB

• The major advantage of DynamoDB over other NoSQL counterparts is amazons infrastructure support
• Key + columns data model
• Composite key support
• Tuneable consistency
• Distributed counters
• Largest value supported 64KB
• Conditional updates
• Hadoop integration M/R, Hive
• Monitorable
• Backups Low-impact – opeartes manually with EMR

Cons of using DynamoDB

• Deployable Only on AWS
• Indexes on column values is not supported
• Integrated caching is not well explained in the document
• 64KB limit on row size
• Limited Data Types
• 1MB limit on Querying and Scanning
• Limited Capability of Query’s Comparision Operators

Advantages

• Automatic data replication accross multiple AWS availability zones to protect data and provide high uptime
• Scalable
•  Average service-side latencies in single-digit milliseconds
•  Fully Managed Service
•  Flexible
•  Single API call allows you to atomically increment or decrement numerical attributes
•  Plans are cheap – a free tier allows more than 40 million database operations/month
•  Uses secure algorithms to keep your data safe
•  AWS Management Console monitors your table operational metrices
•  Tightly integrated with Amazon Elastic MapReduce (Amazon EMR)
• scalability and simplicity of NoSQL
• consistent performance
• low learning curve
• it comes with a decent object mapper for Java

Flaws

DynamoDB's not ideal for storing events

Like most websites, we store a variety of user events. A typical event has an event ID, a user ID, an event type and other attributes that describe actions performed by users. At Dailycred, we needed an event storage that is optimized for reads. For our dashboard we need to quickly filter events by type, sort events by time and group events by user. However, we don't need to record events as soon as they happen. A second of delay is fine.
Using a relational database, we can store events in a denormalized table. Add indices to columns that answer query predicates. In this setup, writes are not very fast, but reads are extremely fast. We can use the richness of SQL to query events easily.
A big limitation of DynamoDB and other non-relational database is the lack of multiple indices. In an events table, we could use the event ID as the hash key, the event time as the range key. This schema would enable us to retrieve most recent events, but we can't filter event by type without doing a full table scan. Scans are expensive in DynamoDB. You could store events in many tables, partitioned by event type or by user ID. Perhaps for each predicate, create an index table with the predicate's value as the key and the event ID as an attribute. Is the added complexity worth it? Probably not. DynamoDB is great for lookups by key, not so good for queries, and abysmal for queries with multiple predicates.
SQL was a better tool in this case, so we decided not to use DynamoDB at all for storing events.

DynamoDB overhead (compared to SQL)

DynamoDB supports transactions, but not in the traditional SQL sense. Each write operation is atomic to an item. A write operation either successfully updates all of the item's attributes or none of its attributes. There are no multi-operation transactions. For example, you have two tables, one to store orders and one to store the user-to-order mapping. When a new order comes in, you write to the order table first, then the mapping table. If the second write fails due to network outage, you are left with an orphaned item. Your application has to recognize orphaned data. Periodically, you will want to run a script to garbage collect those data, which in turn involve a full table scan. The complexity doesn't end here. Your script might need to increase the read limit temporarily. It has to wait long enough between rounds of scan to stay under the limit.

One strike, and you are out

While DynamoDB's provisioned throughput lets you fine tune the performance of individual tables, it doesn't degrade gracefully. Once you hit the read or write limit, your requests are denied until enough time has elapsed. In a perfect world, your auto-scaling script will adjust throughput based on anticipated traffic, increasing and decreasing limits as necessary, but unexpected traffic spikes is a fact of life. Say you bump up the limits as soon as DynamoDB throws a ProvisionedThroughputExceededException, the process could take a minute to complete. Until then, you are at the mercy of retries, a feature that is thankfully enabled by default by the official SDK.

Unit tests: slow or expensive (pick one)

We also run a lot of tests that use DynamoDB, which means a lot of items are written and read very quickly. We run the tests several times a day, which means our development database tables are sitting completely idle most of the time, only to be hammered with reads and writes when we run our unit tests. From a cost perspective, this isn't ideal. However, it's even worse if a developer has to wait extra time for his unit tests to complete.

You can then read data in 1 of three ways. Simple:

• You read a single row by unique key access. If you have a composite hey, provide both the hash-key and the range-key, else provide just the hash key.
• You scan the whole table.
• If you have a composite key, access by the hash-key part and scan (you may filter, but in essence, this is still a scan) on the range key.

There is nothing else you can do, and note that unless doing a full table scan, you must always provide the hash-key, i.e. if you do not know the exact hash key for the row to get, you have to do a full table scan. There is just no other way.

2 down vote

We have just migrated all of our DynamoDB tables to RDS MySQL. While using DynamoDB for specific tasks may make sense, building a new system on top of DynamoDB is really a bad idea. Best laid plans etc., you always need that extra flexibility from your DB. Here are our reasons for moving to RDS: Indexing - Changing or adding keys on-the-fly is impossible without creating a new table. Queries - Querying data is extremely limited. Especially if you want to query non-indexed data. Joins are of course impossible so you have to manage complex data relations on your code/cache layer. Backup - Such a tedious backup procedure is a disappointing surprise compared to the slick backup of RDS GUI - bad UX, limited search, no fun. Speed - Response time is problematic compared to RDS. You find yourself building elaborate caching mechanism to compensate for it in places you would have settled for RDS's internal caching. Data Integrity - While the concept of fluid data structure sounds nice to begin with, some of your data is better "set in stone". Strong typing is a blessing when a little bug tries to destroy your database. With DynamoDB anything is possible and indeed anything that can go wrong does. We now use DynamoDB as a backup for some systems and I'm sure we'll use it in the future for specific, well defined tasks. It's not a bad DB, it's just not the DB to serve 100% of your core system. As far as advantages go, I'd say Scalability and Durability. It scales incredibly and transparently and it's (sort of) always up. These are really great features, but they do not compensate in any way for the downside aspects.

The supported datatypes aren't overly exciting either: Number, String and a Set of Number and String. The string type is UTF-8 and the Number is a signed 38 precision number. Other notable limits is that there is a max of 64 K per row limit, and that a scan will only scan up to a max of 1Mb of data. Note that there is no binary datatype (we have binary data in out MongoDB setup and use base64 encoding on that in DynamoDB).

Pricing is interesting. What you pay for is throughput and storage, which is pretty different from what you may be used to. Throughput may adjusted to what you need, and it's calculated in kb of row data per second, i.e. a table with rows of up to 1Kb in size that with a requirement of 10 reads per second will mean you need 10 units of read capacity (there is a similar throughput number for write capacity). Read more on pricing here.
We are still testing, but so far I am reasonably happy with DynamoDB, despite the issues listed above. The lack of tools (no, there are no DynamoDB tools. At all. No backup tool, no import / export, nothing) means that a certain amount of app development is necessary to access it, even for the simplest of things. Also, there is no Backup, but I am sure this will be fixed soon.

1) 64KB limit on row size: We have many records in our HBase with data much more than 64KB in one row. We even had some records as big as 100MB. Limiting row size to such a tiny amounts rules out lots of use cases for us instantly. In one of the user forum threads here is what one of the Amazon guys say about the limit :
“64KB cumulative attribute size limit, which isn’t too hard to reach if you have a set of Number values for a single attribute When you have so many values for a set, you should consider switching your table to a hash-range schema.”
I think what he is missing is that there could be genuine cases where switching your table to hash range schema may not be possible. In some cases  single piece of data is bigger than 64KB. You cannot simply change your schema to use more rows instead of more columns in these cases. For example a page crawler may store the entire page content in one field. Somebody might want to store an Image as a field in your NoSQL database. You simply cannot use DynamoDB for such use cases. This is my biggest complaint.
2) Limited Data Types: It doesn’t accept binary data. You have to have strings or numbers or sets of strings or numbers. You cannot store images, byte arrays etc. You can get around it by encoding everything in string using base64 encoding, but base64 encoding produces bigger data. And you are counting your bytes as you cannot hit the 64KB limit!
3) 1MB limit on Querying and Scanning: You cannot get a result bigger than 1MB from Query or Scan operations. You are required to make LastEvaluatedKey call to start from wherever you stopped in the earlier scan request. This is not that bad, but it doesn’t allow you to optimize it for your use cases. In most of our use cases, making one trip for 1MB of data could be too much. Amazon should think about increasing this limit or allowing clients to specify this limit.
DynamoDB is supposed to be scalable. I think these limitation seriously challange the scalability claim. It makes me feel like Amazon cannot make it scalable without imposing these limitations.
4) Limited Capability of Query’s Comparision Operators: You cannot use CONTAINS, NOT_NULL and some other operators when you use Query features of DynamoDB. And the documentation may be wrong! Please read this thread for more information:
https://forums.aws.amazon.com/thread.jspa?threadID=85511&tstart=0
You can always use ‘Scan’ instead of ‘Query’ but then you will be forced to go through each and every record. It’s not necessarily any worse than any of the existing NoSQL solution. But since they offered Query mechanism (in additon to Scan) operation, I was little disapointed.
5) Time Required for Creation of a Table or API Call to know when the table is ready: When you create a table programatically (or even using AWS Console), the table doesn’t become available instantly. The call returns before the table is ready. This means you cannot create a table and use it instantly. Sometimes, we use dynamically created tables. I can undestand why it may take time, but it would be nice if they have an api call that can tell us when the table is ready.
Overall, I am really impressed by the simplicity of DynamoDB. The APIs (even though I don’t like the way they are designed) are pretty simple and schema modeling is also very simple. The forums have started buzzing and I think more and more people are trying DynamoDB out. What I will be watching is whether the points discussed above are preventing people from switching their existing NoSQL solution to Amazon’s managed DynamoDB. At GumGum, the first three issues are blockers and unless they are resolved, we are less likely to switch from HBase to DynamoDB.

Dynamo is an expensive, extremely low latency solution.  If you are trying to store more than 64KB per item, you're doing it wrong, and will end up paying through the nose for your read/write throughput anyway.  If you have data that large, take the latency hit and store it in S3 as others have suggested.

CONS :

• In MySQL you'll get ACID guarantee, but in Dynamo-db there is no such guarantee.
• Also in MySQL you can write complex while in Dynamo-db you can't write complex queries.

PROS :

• It has the property of distributed hash tables hence more performance booster as compared to MySQL

Name	DynamoDB  X	Microsoft SQL Server  X
Description	Hosted, scalable database service by Amazon	Microsofts relational DBMS
Website	aws.amazon.com/­en/­dynamodb	www.microsoft.com/­sqlserver
Technical documentation	aws.amazon.com/­documentation/­dynamodb	www.microsoft.com/­sqlserver/­en/­us/­default.aspx
Developer	Amazon	Microsoft
Initial release	2012	1989
License	n.a.	commercial
Implementation language		C++
Server operating systems	hosted	Windows
Database model	Key-value store	Relational DBMS
Data scheme	schema-free	yes
Typing	yes	yes
Secondary indexes	no	yes
SQL	no	yes
APIs and other access methods	RESTful HTTP API	OLE DB Tabular Data Stream (TDS) ADO.NET JDBC ODBC
Supported programming languages	.Net ColdFusion Erlang Groovy Java JavaScript Perl PHP Python Ruby	.Net Java PHP Python Ruby Visual Basic
Server-side scripts	no	Transact-SQL and .NET languages
Triggers	no	yes
Partitioning methods	Sharding	tables can be distributed across several files (horizontal partitioning), but no sharding
Replication methods	yes	yes, but depending on the SQL-Server Edition
MapReduce	no	no
Consistency concepts	Eventual Consistency Immediate Consistency
Foreign keys	no	yes
Transaction concepts	no	ACID
Concurrency	yes	yes
Durability	yes	yes
User concepts	Access rights for users and roles can be defined via the AWS Identity and Access Management (IAM)	Users with fine-grained authorization concept
Specific characteristics	Data stored in Amazon cloud	Is one of the "Big 3" commercial database management systems besides Oracle and DB2

So, what should you do when you’re building a new application and looking for the right database option? My recommendation is as follows: Start by looking at DynamoDB and see if that meets your needs. If it does, you will benefit from its scalability, availability, resilience, low cost, and minimal operational overhead. If a subset of your database workload requires features specific to relational databases, then I recommend moving that portion of your workload into a relational database engine like those supported by Amazon RDS. In the end, you’ll probably end up using a mix of database options, but you will be using the right tool for the right job in your application.

References

http://blog.grio.com/2012/03/getting-started-with-amazon-dynamodb.html

http://www.quizld.com/2012/03/what-is-amazon-dynamodb/

http://db-engines.com/en/system/DynamoDB%3BMicrosoft+SQL+Server

https://www.dailycred.com/blog/10/dynamodb-shortcomings-and-our-work-arounds

http://www.allthingsdistributed.com/2013/03/dynamodb-one-year-later.html

http://stackoverflow.com/questions/13966368/aws-mysql-rds-vs-aws-dynamodb

http://whynosql.com/my-disappointments-with-amazon-dynamodb/