-- create a temporary table for the mapping
create table mig_prj (old_kee varchar(400), new_kee varchar(400)) ENGINE=InnoDB;
insert into mig_prj values( "com.company.ams.prj1:prj1:1.6.0", "com.company.ams.prj1:prj1:prj1-1.6.0");
insert into mig_prj values( "com.company.ams.prj1:prj1:1.7.0", "com.company.ams.prj1:prj1:prj1-1.7.0");
insert into mig_prj values( "com.company.ams.prj1:prj1:trunk", "com.company.ams.prj1:prj1:prj1-trunk");
insert into mig_prj values( "com.company.ams.prj2:prj2:1.0.0", "com.company.ams.prj2:prj2:prj2-1.0.0");
insert into mig_prj values( "com.company.ams.prj2:prj2:1.1.0", "com.company.ams.prj2:prj2:prj2-1.1.0");
insert into mig_prj values( "com.company.ams.prj2:prj2:trunk", "com.company.ams.prj2:prj2:prj2-trunk");
insert into mig_prj values( "com.company.ams.prj3:prj3:trunk", "com.company.ams.prj3:prj3:prj3-trunk");
insert into mig_prj values( "com.company.ams.prj4:prj4:2.20.8", "com.company.ams.prj4:prj4:prj4-2.20.8");
insert into mig_prj values( "com.company.ams.prj4:prj4:trunk", "com.company.ams.prj4:prj4:prj4-trunk");
insert into mig_prj values( "com.company.ams.prj5:prj5:1.6.0", "com.company.ams.prj5:prj5:prj5-1.6.0");
insert into mig_prj values( "com.company.ams.prj5:prj5:trunk", "com.company.ams.prj5:prj5:prj5-trunk");
insert into mig_prj values( "com.company.ams.prj6:prj6:11.2.58", "com.company.ams.prj6:prj6:prj6-11.2.58");
insert into mig_prj values( "com.company.ams.prj6:prj6:trunk", "com.company.ams.prj6:prj6:prj6-trunk");
insert into mig_prj values( "com.company.ams.prj7:prj7-comp1:trunk", "com.company.ams.prj7:prj7-comp1:prj7-comp1-trunk");
insert into mig_prj values( "com.company.ams.prj7:prj7-comp2:4.0.0", "com.company.ams.prj7:prj7-comp2:prj7-comp2-4.0.0");
insert into mig_prj values( "com.company.ams.prj7:prj7-comp2:trunk", "com.company.ams.prj7:prj7-comp2:prj7-comp2-trunk");
insert into mig_prj values( "com.company.ams.prj7:prj7-comp3:trunk", "com.company.ams.prj7:prj7-comp3:prj7-comp3-trunk");
insert into mig_prj values( "com.company.ams.prj7:prj7-comp4:trunk", "com.company.ams.prj7:prj7-comp4:prj7-comp4-trunk");
insert into mig_prj values( "com.company.ams.prj7:prj7-comp5:trunk", "com.company.ams.prj7:prj7-comp5:prj7-comp5-trunk");
insert into mig_prj values( "com.company.ams.commons:prj8:4.0.25", "com.company.ams.commons:prj8:prj8-4.0.25");
insert into mig_prj values( "com.company.ams.commons:prj8:trunk", "com.company.ams.commons:prj8:prj8-trunk");
insert into mig_prj values( "com.company.ams.prj9:ktfakt:trunk", "com.company.ams.prj9:prj9-ktfakt:prj9-ktfakt-trunk");
insert into mig_prj values( "com.company.ams.prj10:prj10:2.8.0", "com.company.ams.prj10:prj10:prj10-2.8.0");
insert into mig_prj values( "com.company.ams.prj10:prj10:trunk", "com.company.ams.prj10:prj10:prj10-trunk");
insert into mig_prj values( "com.company.ams.prj11:prj11-comp6:1.4.0", "com.company.ams.prj11:prj11-comp6:prj11-comp6-1.4.0");
insert into mig_prj values( "com.company.ams.prj11:prj11-comp6:1.4.1", "com.company.ams.prj11:prj11-comp6:prj11-comp6-1.4.1");
insert into mig_prj values( "com.company.ams.prj11:prj11-comp6:1.6.0", "com.company.ams.prj11:prj11-comp6:prj11-comp6-1.6.0");
insert into mig_prj values( "com.company.ams.prj11:prj11-comp6:trunk", "com.company.ams.prj11:prj11-comp6:prj11-comp6-trunk");
insert into mig_prj values( "com.company.ams.prj11:prj11-rcp:1.4.0", "com.company.ams.prj11:prj11:prj11-1.4.0");
insert into mig_prj values( "com.company.ams.prj11:prj11-rcp:1.4.1", "com.company.ams.prj11:prj11:prj11-1.4.1");
insert into mig_prj values( "com.company.ams.prj11:prj11-rcp:1.6.0", "com.company.ams.prj11:prj11:prj11-1.6.0");
insert into mig_prj values( "com.company.ams.prj11:prj11-rcp:trunk", "com.company.ams.prj11:prj11:prj11-trunk");
insert into mig_prj values( "com.company.ams.prj12:prj12:trunk", "com.company.ams.prj12:prj12:prj12-trunk");
insert into mig_prj values( "com.company.ams.prj13:prj13:11.2.159", "com.company.ams.prj13:prj13:prj13-11.2.159");
insert into mig_prj values( "com.company.ams.prj13:prj13:trunk", "com.company.ams.prj13:prj13:prj13-trunk");
insert into mig_prj values( "com.company.ams.prj14:prj14:2.1.6", "com.company.ams.prj14:prj14:prj14-2.1.6");
insert into mig_prj values( "com.company.ams.prj14:prj14:2.2.0", "com.company.ams.prj14:prj14:prj14-2.2.0");
insert into mig_prj values( "com.company.ams.prj14:prj14:trunk", "com.company.ams.prj14:prj14:prj14-trunk");
insert into mig_prj values( "com.company.ams.prj15:prj15:1.10.0", "com.company.ams.prj15:prj15:prj15-1.10.0");
insert into mig_prj values( "com.company.ams.prj15:prj15:1.10.1", "com.company.ams.prj15:prj15:prj15-1.10.1");
insert into mig_prj values( "com.company.ams.prj15:prj15:trunk", "com.company.ams.prj15:prj15:prj15-trunk");
insert into mig_prj values( "com.company.ams.prj16:prj16:16.0.0", "com.company.ams.prj16:prj16:prj16-16.0.0");
insert into mig_prj values( "com.company.ams.prj16:prj16:trunk", "com.company.ams.prj16:prj16:prj16-trunk");
insert into mig_prj values( "com.company.ams.prj17:prj17:1.1.2", "com.company.ams.prj17:prj17:prj17-1.1.2");
insert into mig_prj values( "com.company.ams.prj17:prj17:1.1.3", "com.company.ams.prj17:prj17:prj17-1.1.3");
insert into mig_prj values( "com.company.ams.prj17:prj17:trunk", "com.company.ams.prj17:prj17:prj17-trunk");
insert into mig_prj values( "com.company.ams.prj18-comp7:prj18:2.14.0", "com.company.ams.prj18:prj18:prj18-2.14.0");
insert into mig_prj values( "com.company.ams.prj18-comp7:prj18:2.14.1", "com.company.ams.prj18:prj18:prj18-2.14.1");
insert into mig_prj values( "com.company.ams.prj18-comp7:prj18:trunk", "com.company.ams.prj18:prj18:prj18-trunk");
insert into mig_prj values( "com.company.ams.prj19:prj19:trunk", "com.company.ams.prj19:prj19:prj19-trunk");
insert into mig_prj values( "com.company.ams.prj20:prj20:trunk", "com.company.ams.prj20:prj20:prj20-trunk");
-- make a backup of the table
create table projects_bak select * from projects;
-- update the entries
UPDATE projects p, mig_prj m
SET kee = IF(LENGTH(p.kee) - LENGTH(REPLACE(p.kee, ':', '')) = 3, concat(m.new_kee, ':', SUBSTRING_INDEX(p.kee, ':', -1) ), m.new_kee)
WHERE p.kee like concat(m.old_kee, '%');
-- check the result (no old entries should be visible anymore)
select distinct IF(LENGTH(p.kee) - LENGTH(REPLACE(p.kee, ':', '')) = 3, SUBSTRING_INDEX(p.kee, ':', 3), p.kee)
FROM projects p, mig_prj m
WHERE p.kee not like 'com.company.ams.prj21:%' and p.kee not like 'com.company.prj22:%' and p.qualifier not like 'LIB'
ORDER BY p.kee
LIMIT 0, 50000;
This is important in case you need to change the "groupId:ArtifactId:branch" key.
When you need to do that you should be able to make sure that the new Sonar analysis gets associated to the "old" Sonar project correctly, that means without showing any existing violations as new for example.
See http://old.nabble.com/How-to-rename-a-Sonar-project--td32973893.html