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(a)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;
Show replies by date