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