schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql | 78 ++-------- 1 file changed, 23 insertions(+), 55 deletions(-)
New commits: commit 220aa08351af2365ecea664c07416ea24a6ea2a2 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 25 11:51:55 2012 +0200
869985 - fix unique constraint violation occuring during schema upgrade
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql index 87e130e..44dd7bd 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql @@ -1,61 +1,29 @@ --- relink reference to group with trailing \n to correct one -update rhnpackage set package_group = (select n.id - from rhnpackagegroup n - join rhnpackagegroup o - on substr(o.name, 1, length(o.name)-1) = n.name - and o.id <> n.id - where o.id = package_group) - where package_group in (select o.id - from rhnpackagegroup n - join rhnpackagegroup o - on substr(o.name, 1, length(o.name)-1) = n.name - and o.id <> n.id); +begin + for rec in ( + select id as old_id, + min(id) over ( partition by rtrim(translate(name, chr(10), ' ')) ) as new_id + from rhnPackageGroup + ) loop + if rec.new_id < rec.old_id then
--- delete unused groups -delete from rhnpackagegroup pg - where not exists (select 1 from rhnpackage p where p.package_group = pg.id) - and not exists (select 1 from rhnpackagesource p where p.package_group = pg.id) - -- don't remove 'NoGroup' - and id > 1; + update rhnPackage + set package_group = rec.new_id + where package_group = rec.old_id;
--- remove trailing \n -update rhnpackagegroup set name = substr(name, 1, length(name)-1) - where name like '%' || chr(10); + update rhnPackageSource + set package_group = rec.new_id + where package_group = rec.old_id;
--- relink reference to group with trailing space to correct one (without space) -update rhnpackage set package_group = (select n.id - from rhnpackagegroup n - join rhnpackagegroup o - on trim(o.name) = n.name - and o.id <> n.id - where o.id = package_group) - where package_group in (select o.id - from rhnpackagegroup n - join rhnpackagegroup o - on trim(o.name) = n.name - and o.id <> n.id); + delete + from rhnPackageGroup + where id = rec.old_id; + end if; + end loop;
-update rhnPackageSource set package_group = (select n.id - from rhnPackageGroup n - join rhnPackageGroup o - on trim(o.name) = n.name - and o.id <> n.id - where o.id = package_group) - where package_group in (select o.id - from rhnPackageGroup n - join rhnPackageGroup o - on trim(o.name) = n.name - and o.id <> n.id); - --- delete unused groups -delete from rhnpackagegroup pg - where not exists (select 1 from rhnpackage p where p.package_group = pg.id) - and not exists (select 1 from rhnpackagesource p where p.package_group = pg.id) - -- don't remove 'NoGroup' - and id > 1; - --- remove trailing spaces from rest of packages -update rhnpackagegroup set name = trim(name) - where name like '% '; + update rhnPackageGroup + set name = rtrim(translate(name, chr(10), ' ')) + where name <> rtrim(translate(name, chr(10), ' ')); +end; +/
commit;
spacewalk-commits@lists.fedorahosted.org