schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql | 29 --- schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.oracle | 29 +++ schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.postgresql | 73 ++++++++++ 3 files changed, 102 insertions(+), 29 deletions(-)
New commits: commit 7b83198641109da47a4f5f47448dec5efe07ba13 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 25 13:07:01 2012 +0200
869985 - adapt the upgrade script for PostgreSQL
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 deleted file mode 100644 index 44dd7bd..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql +++ /dev/null @@ -1,29 +0,0 @@ -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 - - update rhnPackage - set package_group = rec.new_id - where package_group = rec.old_id; - - update rhnPackageSource - set package_group = rec.new_id - where package_group = rec.old_id; - - delete - from rhnPackageGroup - where id = rec.old_id; - end if; - end loop; - - update rhnPackageGroup - set name = rtrim(translate(name, chr(10), ' ')) - where name <> rtrim(translate(name, chr(10), ' ')); -end; -/ - -commit; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.oracle new file mode 100644 index 0000000..44dd7bd --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.oracle @@ -0,0 +1,29 @@ +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 + + update rhnPackage + set package_group = rec.new_id + where package_group = rec.old_id; + + update rhnPackageSource + set package_group = rec.new_id + where package_group = rec.old_id; + + delete + from rhnPackageGroup + where id = rec.old_id; + end if; + end loop; + + update rhnPackageGroup + set name = rtrim(translate(name, chr(10), ' ')) + where name <> rtrim(translate(name, chr(10), ' ')); +end; +/ + +commit; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.postgresql new file mode 100644 index 0000000..2f28b38 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7/007-rhnPackageGroup-trim.sql.postgresql @@ -0,0 +1,73 @@ +-- oracle equivalent source sha1 364257f39ba9bdce9cc90e9a31b10fd19c31babd + +-- 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); + +update rhnpackagesource 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); + +-- 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 \n +update rhnpackagegroup set name = substr(name, 1, length(name)-1) + where name like '%' || chr(10); + +-- 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); + +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 '% ';
spacewalk-commits@lists.fedorahosted.org