reporting/reports/data/channels
| 4
reporting/reports/data/errata-list
| 8 -
reporting/reports/data/errata-list-all
| 7 -
reporting/reports/data/errata-systems
| 5
reporting/reports/data/inventory
| 55 ++++------
reporting/reports/data/users
| 12 +-
reporting/reports/data/users-systems
| 13 +-
schema/spacewalk/oracle/procs/concat_list_clob.sql
| 27 ----
schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql
| 32 +++++
schema/spacewalk/oracle/procs/procs.deps
| 1
schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql
| 35 ++++++
schema/spacewalk/postgres/procs/procs.deps
| 1
schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.oracle
| 3
schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.postgresql
| 2
schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.oracle
| 32 +++++
schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.postgresql
| 35 ++++++
16 files changed, 194 insertions(+), 78 deletions(-)
New commits:
commit 9095197b065fb42b9839aef89690ffdeb562b1de
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Tue Mar 22 10:39:55 2011 +0100
There is no cursor() function for inline cursors in PostgreSQL, using custom function
get_hw_info_as_clob instead.
This also makes concat_list_clob not needed, dropping.
Addressing
spacewalk.server.rhnSQL.sql_base.SQLStatementPrepareError: ('function cursor(text)
does not exist at character 1288 ...
diff --git a/reporting/reports/data/inventory b/reporting/reports/data/inventory
index 177367d..38f9811 100755
--- a/reporting/reports/data/inventory
+++ b/reporting/reports/data/inventory
@@ -61,15 +61,7 @@ sql:
(
select rhnserverarch.name from rhnserverarch where rhnserver.server_arch_id =
rhnserverarch.id
) as architecture,
- concat_list_clob('; ', cursor(
- (
- select sum(nrcpu) || ' CPUs' from rhncpu where rhnserver.id =
rhncpu.server_id
- union all
- select name||' '||ip_addr||'/'||netmask||' '||hw_addr val
- from rhnservernetinterface
- where rhnserver.id = rhnservernetinterface.server_id
- )), 1
- ) as hardware
+ get_hw_info_as_clob(rhnserver.id, '; ') as hardware
from rhnserver left outer join
(
select server_id, hostname, ipaddr
diff --git a/schema/spacewalk/oracle/procs/concat_list_clob.sql
b/schema/spacewalk/oracle/procs/concat_list_clob.sql
deleted file mode 100644
index c647c47..0000000
--- a/schema/spacewalk/oracle/procs/concat_list_clob.sql
+++ /dev/null
@@ -1,27 +0,0 @@
-
-create or replace function concat_list_clob(
- concat_string in varchar,
- cur in sys_refcursor,
- close_the_cursor in integer default 0
-)
-return clob
-is
- ret clob;
- tmp varchar2(4000);
-begin
- dbms_lob.createtemporary(ret, true);
- loop
- fetch cur into tmp;
- exit when cur%notfound;
- if cur%rowcount > 1 then
- dbms_lob.writeappend(ret, length(concat_string), concat_string);
- end if;
- dbms_lob.writeappend(ret, length(tmp), tmp);
- end loop;
- if close_the_cursor > 0 then
- close cur;
- end if;
- return ret;
-end;
-/
-
diff --git a/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql
b/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql
new file mode 100644
index 0000000..ce1b580
--- /dev/null
+++ b/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql
@@ -0,0 +1,32 @@
+
+create or replace function get_hw_info_as_clob(
+ sid in rhnserver.id%TYPE,
+ separator in varchar
+)
+return clob
+is
+ ret clob;
+ tmp varchar2(4000);
+begin
+ dbms_lob.createtemporary(ret, true);
+ for rec in (
+ select m
+ from (
+ select 1 n, sum(nrcpu) || ' CPUs' m
+ from rhncpu where rhncpu.server_id = sid
+ union all
+ select 2, name||' '||ip_addr||'/'||netmask||' '||hw_addr val
+ from rhnservernetinterface
+ where rhnservernetinterface.server_id = sid
+ )
+ order by n, m
+ ) loop
+ if dbms_lob.getlength(ret) > 0 then
+ dbms_lob.writeappend(ret, length(separator), separator);
+ end if;
+ dbms_lob.writeappend(ret, length(rec.m), rec.m);
+ end loop;
+ return ret;
+end;
+/
+
diff --git a/schema/spacewalk/oracle/procs/procs.deps
b/schema/spacewalk/oracle/procs/procs.deps
index debe0be..6e3e5e6 100644
--- a/schema/spacewalk/oracle/procs/procs.deps
+++ b/schema/spacewalk/oracle/procs/procs.deps
@@ -120,4 +120,5 @@ rhn_synch_probe_state :: rhn_probe_state rhn_deployed_probe
\
rhn_current_state_summaries
set_ks_session_history_message :: rhnKickstartSessionState \
rhnKickstartSessionHistory
+get_hw_info_as_clob :: rhnCpu rhnServerNetInterface
diff --git a/schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql
b/schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql
new file mode 100644
index 0000000..9d899f3
--- /dev/null
+++ b/schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql
@@ -0,0 +1,35 @@
+-- oracle equivalent source sha1 4a627f5b31efb66c684dd14f0514e25a5ace94f2
+
+create or replace function get_hw_info_as_clob(
+ sid in rhnserver.id%TYPE,
+ separator in varchar
+)
+returns text
+as
+$$
+declare
+ ret text;
+ rec record;
+begin
+ for rec in (
+ select m
+ from (
+ select 1 n, sum(nrcpu) || ' CPUs' m
+ from rhncpu where rhncpu.server_id = sid
+ union all
+ select 2, name||'
'||coalesce(ip_addr,'')||'/'||coalesce(netmask,'')||'
'||hw_addr val
+ from rhnservernetinterface
+ where rhnservernetinterface.server_id = sid
+ ) X
+ order by n, m
+ ) loop
+ if ret is null then
+ ret := rec.m;
+ else
+ ret := ret || separator || rec.m;
+ end if;
+ end loop;
+ return ret;
+end;
+$$ language plpgsql;
+
diff --git a/schema/spacewalk/postgres/procs/procs.deps
b/schema/spacewalk/postgres/procs/procs.deps
index 4166cf0..6bc8a9b 100644
--- a/schema/spacewalk/postgres/procs/procs.deps
+++ b/schema/spacewalk/postgres/procs/procs.deps
@@ -118,4 +118,5 @@ rhn_synch_probe_state :: rhn_probe_state rhn_deployed_probe
\
rhn_current_state_summaries
set_ks_session_history_message :: rhnKickstartSessionState \
rhnKickstartSessionHistory
+get_hw_info_as_clob :: rhnCpu rhnServerNetInterface
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.oracle
new file mode 100644
index 0000000..03fb791
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.oracle
@@ -0,0 +1,3 @@
+
+drop function concat_list_clob;
+
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.postgresql
new file mode 100644
index 0000000..80a0a49
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/010-concat_list_clob-drop.sql.postgresql
@@ -0,0 +1,2 @@
+-- oracle equivalent source sha1 bee76d7be43c246feb787552584ddac2a8e35973
+-- This file is intentionally left empty.
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.oracle
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.oracle
new file mode 100644
index 0000000..ce1b580
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.oracle
@@ -0,0 +1,32 @@
+
+create or replace function get_hw_info_as_clob(
+ sid in rhnserver.id%TYPE,
+ separator in varchar
+)
+return clob
+is
+ ret clob;
+ tmp varchar2(4000);
+begin
+ dbms_lob.createtemporary(ret, true);
+ for rec in (
+ select m
+ from (
+ select 1 n, sum(nrcpu) || ' CPUs' m
+ from rhncpu where rhncpu.server_id = sid
+ union all
+ select 2, name||' '||ip_addr||'/'||netmask||' '||hw_addr val
+ from rhnservernetinterface
+ where rhnservernetinterface.server_id = sid
+ )
+ order by n, m
+ ) loop
+ if dbms_lob.getlength(ret) > 0 then
+ dbms_lob.writeappend(ret, length(separator), separator);
+ end if;
+ dbms_lob.writeappend(ret, length(rec.m), rec.m);
+ end loop;
+ return ret;
+end;
+/
+
diff --git
a/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.postgresql
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.postgresql
new file mode 100644
index 0000000..9d899f3
--- /dev/null
+++
b/schema/spacewalk/upgrade/spacewalk-schema-1.3-to-spacewalk-schema-1.4/011-get_hw_info_as_clob.sql.postgresql
@@ -0,0 +1,35 @@
+-- oracle equivalent source sha1 4a627f5b31efb66c684dd14f0514e25a5ace94f2
+
+create or replace function get_hw_info_as_clob(
+ sid in rhnserver.id%TYPE,
+ separator in varchar
+)
+returns text
+as
+$$
+declare
+ ret text;
+ rec record;
+begin
+ for rec in (
+ select m
+ from (
+ select 1 n, sum(nrcpu) || ' CPUs' m
+ from rhncpu where rhncpu.server_id = sid
+ union all
+ select 2, name||'
'||coalesce(ip_addr,'')||'/'||coalesce(netmask,'')||'
'||hw_addr val
+ from rhnservernetinterface
+ where rhnservernetinterface.server_id = sid
+ ) X
+ order by n, m
+ ) loop
+ if ret is null then
+ ret := rec.m;
+ else
+ ret := ret || separator || rec.m;
+ end if;
+ end loop;
+ return ret;
+end;
+$$ language plpgsql;
+
commit fc86b41c7761db94d271424074416a3217e2daeb
Author: Jan Pazdziora <jpazdziora(a)redhat.com>
Date: Mon Mar 21 13:15:33 2011 +0100
Rewriting outer joins to ANSI syntax.
Addressing errors like
spacewalk.server.rhnSQL.sql_base.SQLStatementPrepareError: ('syntax error at or
near ")" at character 661
when PostgreSQL parser hits (*).
diff --git a/reporting/reports/data/channels b/reporting/reports/data/channels
index ab2258f..39651e1 100755
--- a/reporting/reports/data/channels
+++ b/reporting/reports/data/channels
@@ -17,8 +17,8 @@ columns:
sql:
select rhnchannel.label channel_label, rhnchannel.name channel_name,
count(rhnchannelpackage.channel_id) number_of_packages
- from rhnchannel, rhnchannelpackage
- where rhnchannel.id = rhnchannelpackage.channel_id (+)
+ from rhnchannel left outer join rhnchannelpackage
+ on rhnchannel.id = rhnchannelpackage.channel_id
group by rhnchannel.id, rhnchannel.label, rhnchannel.name
order by rhnchannel.label
diff --git a/reporting/reports/data/errata-list b/reporting/reports/data/errata-list
index 06a1ce0..6781810 100755
--- a/reporting/reports/data/errata-list
+++ b/reporting/reports/data/errata-list
@@ -29,10 +29,12 @@ sql:
rhnCVE.name as cve,
rhnErrata.synopsis,
count(*) as systems_affected
- from rhnErrata, rhnServerNeededErrataCache, rhnErrataCVE, rhnCVE
+ from rhnErrata left outer join rhnErrataCVE
+ on rhnErrata.id = rhnErrataCVE.errata_id
+ left outer join rhnCVE
+ on rhnErrataCVE.cve_id = rhnCVE.id
+ , rhnServerNeededErrataCache
where rhnErrata.id = rhnServerNeededErrataCache.errata_id
- and rhnErrata.id = rhnErrataCVE.errata_id (+)
- and rhnErrataCVE.cve_id = rhnCVE.id (+)
group by rhnErrata.id,
rhnErrata.advisory_name,
rhnErrata.advisory_type,
diff --git a/reporting/reports/data/errata-list-all
b/reporting/reports/data/errata-list-all
index edcea19..d8f32c0 100755
--- a/reporting/reports/data/errata-list-all
+++ b/reporting/reports/data/errata-list-all
@@ -27,7 +27,8 @@ sql:
rhnErrata.advisory_type as type,
rhnCVE.name as cve,
rhnErrata.synopsis
- from rhnErrata, rhnErrataCVE, rhnCVE
- where rhnErrata.id = rhnErrataCVE.errata_id (+)
- and rhnErrataCVE.cve_id = rhnCVE.id (+)
+ from rhnErrata left outer join rhnErrataCVE
+ on rhnErrata.id = rhnErrataCVE.errata_id
+ left outer join rhnCVE
+ on rhnErrataCVE.cve_id = rhnCVE.id
order by rhnErrata.advisory_name, rhnErrata.id, rhnCVE.name
diff --git a/reporting/reports/data/errata-systems
b/reporting/reports/data/errata-systems
index f40aa9f..314ecb9 100755
--- a/reporting/reports/data/errata-systems
+++ b/reporting/reports/data/errata-systems
@@ -21,7 +21,8 @@ sql:
rhnServerNeededErrataCache.server_id,
rhnServer.name as profile_name,
rhnservernetwork.hostname, trim(rhnservernetwork.ipaddr) as ip_address
- from rhnErrata, rhnServerNeededErrataCache, rhnServer,
+ from rhnErrata, rhnServer,
+ rhnServerNeededErrataCache left outer join
(
select server_id, hostname, ipaddr
from rhnservernetwork
@@ -31,8 +32,8 @@ sql:
group by server_id
)
) rhnservernetwork
+ on rhnServerNeededErrataCache.server_id = rhnservernetwork.server_id
where rhnErrata.id = rhnServerNeededErrataCache.errata_id
and rhnServerNeededErrataCache.server_id = rhnServer.id
- and rhnServerNeededErrataCache.server_id = rhnservernetwork.server_id (+)
order by rhnErrata.advisory_name, rhnServerNeededErrataCache.server_id
diff --git a/reporting/reports/data/inventory b/reporting/reports/data/inventory
index cbc49de..177367d 100755
--- a/reporting/reports/data/inventory
+++ b/reporting/reports/data/inventory
@@ -70,7 +70,7 @@ sql:
where rhnserver.id = rhnservernetinterface.server_id
)), 1
) as hardware
- from rhnserver,
+ from rhnserver left outer join
(
select server_id, hostname, ipaddr
from rhnservernetwork
@@ -79,34 +79,39 @@ sql:
from rhnservernetwork
group by server_id
)
- ) rhnservernetwork,
- rhnserverchannel, rhnchannel,
- rhnserverconfigchannel, rhnconfigchannel,
+ ) rhnservernetwork
+ on rhnserver.id = rhnservernetwork.server_id
+ left outer join rhnserverchannel
+ on rhnserver.id = rhnserverchannel.server_id
+ left outer join rhnchannel
+ on rhnserverchannel.channel_id = rhnchannel.id
+ left outer join rhnserverconfigchannel
+ on rhnserver.id = rhnserverconfigchannel.server_id
+ left outer join rhnconfigchannel
+ on rhnserverconfigchannel.config_channel_id = rhnconfigchannel.id
+ left outer join
(
select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id,
rhnservergroup.name
- from rhnservergroupmembers, rhnservergroup
- where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
- and rhnservergroup.group_type is not null
- ) entitlement_data,
+ from rhnservergroupmembers left outer join rhnservergroup
+ on rhnservergroupmembers.server_group_id = rhnservergroup.id
+ where rhnservergroup.group_type is not null
+ ) entitlement_data
+ on rhnserver.id = entitlement_data.server_id
+ left outer join
(
select rhnservergroupmembers.server_id, rhnservergroupmembers.server_group_id,
rhnservergroup.name
- from rhnservergroupmembers, rhnservergroup
- where rhnservergroupmembers.server_group_id = rhnservergroup.id (+)
- and rhnservergroup.group_type is null
- ) group_data,
+ from rhnservergroupmembers left outer join rhnservergroup
+ on rhnservergroupmembers.server_group_id = rhnservergroup.id
+ where rhnservergroup.group_type is null
+ ) group_data
+ on rhnserver.id = group_data.server_id
+ left outer join
(
select host_system_id, virtual_system_id
from rhnvirtualinstance
where host_system_id is not null
) virtual_data
- where rhnserver.id = rhnservernetwork.server_id (+)
- and rhnserver.id = rhnserverchannel.server_id (+)
- and rhnserverchannel.channel_id = rhnchannel.id (+)
- and rhnserver.id = rhnserverconfigchannel.server_id (+)
- and rhnserverconfigchannel.config_channel_id = rhnconfigchannel.id (+)
- and rhnserver.id = entitlement_data.server_id (+)
- and rhnserver.id = group_data.server_id (+)
- and rhnserver.id = virtual_data.virtual_system_id (+)
+ on rhnserver.id = virtual_data.virtual_system_id
order by server_id, rhnchannel.parent_channel nulls first, rhnchannel.id,
rhnconfigchannel.id, entitlement_data.server_group_id, group_data.server_group_id,
virtual_data.host_system_id
diff --git a/reporting/reports/data/users b/reporting/reports/data/users
index 0a44b02..efec511 100755
--- a/reporting/reports/data/users
+++ b/reporting/reports/data/users
@@ -52,14 +52,16 @@ sql:
where wccl.web_contact_id =
wccl_exists.web_contact_id
)
),'enabled') as active
- from web_contact, web_customer, web_user_personal_info,
- rhnUserGroupMembers, rhnUserGroup, rhnUserGroupType,
+ from web_contact left outer join rhnUserGroupMembers
+ on web_contact.id = rhnUserGroupMembers.user_id
+ left outer join rhnUserGroup
+ on rhnUserGroupMembers.user_group_id = rhnUserGroup.id
+ left outer join rhnUserGroupType
+ on rhnUserGroup.group_type = rhnUserGroupType.id
+ , web_customer, web_user_personal_info,
rhnUserInfo
where web_contact.org_id = web_customer.id
and web_contact.id = web_user_personal_info.web_user_id
- and web_contact.id = rhnUserGroupMembers.user_id (+)
- and rhnUserGroupMembers.user_group_id = rhnUserGroup.id (+)
- and rhnUserGroup.group_type = rhnUserGroupType.id (+)
and web_contact.id = rhnUserInfo.user_id
order by web_customer.id, web_contact.id
diff --git a/reporting/reports/data/users-systems b/reporting/reports/data/users-systems
index 94234cd..b805004 100755
--- a/reporting/reports/data/users-systems
+++ b/reporting/reports/data/users-systems
@@ -32,15 +32,15 @@ sql:
web_contact.id as user_id,
login as username,
case when web_contact_admin.user_id is not null then 1 else null end as admin_access
- from web_contact, (
+ from web_contact left outer join (
select rhnUserGroupMembers.user_id
from rhnUserGroupMembers, rhnUserGroup, rhnUserGroupType
where rhnUserGroupMembers.user_group_id = rhnUserGroup.id
and rhnUserGroup.group_type = rhnUserGroupType.id
and rhnUserGroupType.label = 'org_admin'
) web_contact_admin
- where web_contact.id = web_contact_admin.user_id (+)
- ) web_contact_adm, (
+ on web_contact.id = web_contact_admin.user_id
+ ) web_contact_adm left outer join (
select 1 as is_admin, web_contact.id as user_id, rhnServer.id as server_id, null as
group_name
from web_contact, rhnServer
where web_contact.org_id = rhnServer.org_id
@@ -51,9 +51,8 @@ sql:
where rhnUserServerGroupPerms.server_group_id = rhnServerGroupMembers.server_group_id
and rhnUserServerGroupPerms.server_group_id = rhnServerGroup.id
) rhn_contact_server_group
- where
- nvl(web_contact_adm.admin_access, 0) = rhn_contact_server_group.is_admin (+)
- and web_contact_adm.user_id = rhn_contact_server_group.user_id (+)
- and rhn_contact_server_group.server_id is not null
+ on ( case when web_contact_adm.admin_access is null then 0 else
web_contact_adm.admin_access end ) = rhn_contact_server_group.is_admin
+ and web_contact_adm.user_id = rhn_contact_server_group.user_id
+ where rhn_contact_server_group.server_id is not null
order by organization_id, user_id, server_id