schema/spacewalk/common/tables/rhnServerNetAddress4.sql | 7 ++++--- schema/spacewalk/common/tables/rhnServerNetAddress6.sql | 9 +++++---- schema/spacewalk/common/tables/tables.deps | 2 ++ schema/spacewalk/oracle/packages/rhn_server.pkb | 12 +++++++----- schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql | 8 +++++--- schema/spacewalk/postgres/packages/rhn_server.pkb | 10 ++++++---- schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql | 10 ++++++---- 7 files changed, 35 insertions(+), 23 deletions(-)
New commits: commit 5d39ae0d5e02650eb39c13638d156222187ba965 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 20 18:38:45 2011 +0200
get_hw_info_as_clob: adapt to IPv6 changes
diff --git a/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql b/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql index ce1b580..d6fea07 100644 --- a/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql +++ b/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql @@ -15,9 +15,11 @@ begin 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 + select 2, ni.name||' '||na4.address||'/'||na4.netmask||' '||ni.hw_addr val + from rhnservernetinterface ni, + rhnServerNetAddress4 na4 + where ni.server_id = sid + and ni.id = na4.interface_id ) order by n, m ) loop diff --git a/schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql b/schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql index 9d899f3..6cc1982 100644 --- a/schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql +++ b/schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 4a627f5b31efb66c684dd14f0514e25a5ace94f2 +-- oracle equivalent source sha1 6a538f3b05d412810baec118fbb7860348c60cd4
create or replace function get_hw_info_as_clob( sid in rhnserver.id%TYPE, @@ -17,9 +17,11 @@ begin 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 + select 2, ni.name||' '||coalesce(na4.address,'')||'/'||coalesce(na4.netmask,'')||' '||ni.hw_addr val + from rhnservernetinterface, + rhnServerNetAddress4 na4 + where ni.server_id = sid + and ni.id = na4.interface_id ) X order by n, m ) loop
commit 53509147ce6344cf53c8684fa0fda3627815fb7e Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 20 17:48:58 2011 +0200
update rhn_server.get_ip_address() to reflect IPv6 changes
diff --git a/schema/spacewalk/oracle/packages/rhn_server.pkb b/schema/spacewalk/oracle/packages/rhn_server.pkb index 72392fd..8e3c640 100644 --- a/schema/spacewalk/oracle/packages/rhn_server.pkb +++ b/schema/spacewalk/oracle/packages/rhn_server.pkb @@ -712,10 +712,12 @@ is server_id_in in number ) return varchar2 is cursor interfaces is - select name, ip_addr - from rhnServerNetInterface - where server_id = server_id_in - and ip_addr != '127.0.0.1'; + select ni.name as name, na4.address as address + from rhnServerNetInterface ni, + rhnServerNetAddress4 na4 + where ni.server_id = server_id_in + and ni.id = na4.interface_id + and na4.address != '127.0.0.1'; cursor addresses is select ipaddr ip_addr from rhnServerNetwork @@ -726,7 +728,7 @@ is return addr.ip_addr; end loop; for iface in interfaces loop - return iface.ip_addr; + return iface.address; end loop; return NULL; end get_ip_address; diff --git a/schema/spacewalk/postgres/packages/rhn_server.pkb b/schema/spacewalk/postgres/packages/rhn_server.pkb index 3533094..a1f7aca 100644 --- a/schema/spacewalk/postgres/packages/rhn_server.pkb +++ b/schema/spacewalk/postgres/packages/rhn_server.pkb @@ -764,10 +764,12 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p ) returns varchar as $$ declare interfaces cursor is - select name, ip_addr - from rhnServerNetInterface + select ni.name as name, na4.address as address + from rhnServerNetInterface ni, + rhnServerNetAddress4 na4 where server_id = server_id_in - and ip_addr != '127.0.0.1'; + and ni.id = na4.interface_id + and na4.address != '127.0.0.1'; addresses cursor is select ipaddr ip_addr from rhnServerNetwork @@ -778,7 +780,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p return addr.ip_addr; end loop; for iface in interfaces loop - return iface.ip_addr; + return iface.address; end loop; return NULL; end$$ language plpgsql;
commit 7d3b8d0ec1627c84d73d4383cd9f3d31cd2f1784 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 20 17:38:13 2011 +0200
delete from rhnServerNetInterface: add on delete cascade
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql index edf90ce..7042fb2 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql @@ -18,7 +18,8 @@ CREATE TABLE rhnServerNetAddress4 ( interface_id NUMBER NOT NULL CONSTRAINT rhn_srv_net_iaddress4_iid_fk - REFERENCES rhnServerNetInterface (id), + REFERENCES rhnServerNetInterface (id) + ON DELETE CASCADE, address VARCHAR2(64), netmask VARCHAR2(64), broadcast VARCHAR2(64), diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql index 6413ba5..ee04fe5 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -18,7 +18,8 @@ CREATE TABLE rhnServerNetAddress6 ( interface_id NUMBER NOT NULL CONSTRAINT rhn_srv_net_iaddress6_iid_fk - REFERENCES rhnServerNetInterface (id), + REFERENCES rhnServerNetInterface (id) + ON DELETE CASCADE, address VARCHAR2(45), netmask VARCHAR2(49), scope VARCHAR2(64),
commit 8d3f10911dc8e3f5dd046e8fc4623e50e1df91f6 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 20 17:12:56 2011 +0200
correct index creation
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql index 7dbde6c..6413ba5 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -31,7 +31,7 @@ ENABLE ROW MOVEMENT ;
CREATE INDEX rhn_net_addr6_iid_sc_addr_idx - ON rhnServerNetAddress4 (interface_id, scope, address) + ON rhnServerNetAddress6 (interface_id, scope, address) TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress6
commit 64d71fb3ec0b5f28e8fc18b41536e330c486b163 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 20 17:07:28 2011 +0200
shorten constraint identifiers to fit into given limits
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql index f6ca8d0..edf90ce 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql @@ -30,10 +30,10 @@ CREATE TABLE rhnServerNetAddress4 ENABLE ROW MOVEMENT ;
-CREATE INDEX rhn_srv_net_address4_iid_address_idx +CREATE INDEX rhn_net_addr4_iid_addr_idx ON rhnServerNetAddress4 (interface_id, address) TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress4 - ADD CONSTRAINT rhn_srv_net_address4_iid_address_uq UNIQUE (interface_id, address); + ADD CONSTRAINT rhn_net_add4_iid_addr_uq UNIQUE (interface_id, address);
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql index ca32f30..7dbde6c 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -30,10 +30,10 @@ CREATE TABLE rhnServerNetAddress6 ENABLE ROW MOVEMENT ;
-CREATE INDEX rhn_srv_net_address6_iid_scope_address_idx +CREATE INDEX rhn_net_addr6_iid_sc_addr_idx ON rhnServerNetAddress4 (interface_id, scope, address) TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress6 - ADD CONSTRAINT rhn_srv_net_address6_iid_scope_address_uq UNIQUE (interface_id, scope, address); + ADD CONSTRAINT rhn_net_addr6_iid_sc_addr_uq UNIQUE (interface_id, scope, address);
commit e241c929a850b522940f70783fd0cc2cbc73ffdd Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 20 16:56:40 2011 +0200
Fix table dependencies
diff --git a/schema/spacewalk/common/tables/tables.deps b/schema/spacewalk/common/tables/tables.deps index 53fc6c7..692e4b4 100644 --- a/schema/spacewalk/common/tables/tables.deps +++ b/schema/spacewalk/common/tables/tables.deps @@ -105,6 +105,8 @@ rhnKickstartSessionHistory :: rhnKickstartSession rhnKickstartSessionState rhnKSTreeFile :: rhnKickstartableTree rhnChannelErrata rhnErrata \ rhnChecksum rhnKickstartTimezone :: rhnKSInstallType +rhnServerNetAddress4 :: rhnServerNetInterface +rhnServerNetAddress6 :: rhnServerNetInterface rhnOrgChannelSettings :: web_customer rhnChannel rhnOrgChannelSettingsType rhnOrgEntitlements :: rhnOrgEntitlementType rhnOrgErrataCacheQueue :: web_customer