backend/server/rhnServer/server_hardware.py | 43 schema/spacewalk/common/tables/rhnServerNetAddress4.sql | 40 schema/spacewalk/common/tables/rhnServerNetAddress6.sql | 40 schema/spacewalk/common/tables/rhnServerNetInterface.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/oracle/triggers/rhnServerNetInterface.sql | 3 schema/spacewalk/postgres/packages/rhn_server.pkb | 10 schema/spacewalk/postgres/procs/get_hw_info_as_clob.sql | 10 schema/spacewalk/postgres/triggers/rhnServerNetInterface.sql | 6 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.oracle | 9 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.postgresql | 11 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.oracle | 39 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.postgresql | 18 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/111-add_rhnServerNetAddress4_data.sql | 6 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/115-drop_columns_rhnServerNetInterface.sql | 3 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.oracle | 39 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.postgresql | 17 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.oracle | 750 --------- schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql | 803 ---------- schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.oracle | 33 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.postgresql | 36 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.oracle | 12 schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.postgresql | 11 web/modules/rhn/RHN/DB/Server.pm | 9 web/modules/rhn/RHN/DB/Server/NetInterface.pm | 86 + web/modules/sniglets/Sniglets/Servers.pm | 62 28 files changed, 211 insertions(+), 1916 deletions(-)
New commits: commit e86ed29355375fa9d90366732980e8a9e3cf9276 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:56 2011 +0200
Revert "schema change"
This reverts commit 780ba79c8d5edb50c312b522c6b8972db0d812a3.
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql deleted file mode 100644 index 6db6d3b..0000000 --- a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql +++ /dev/null @@ -1,38 +0,0 @@ --- --- Copyright (c) 2011 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- implied, including the implied warranties of MERCHANTABILITY or FITNESS --- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 --- along with this software; if not, see --- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. --- --- Red Hat trademarks are not licensed under GPLv2. No permission is --- granted to use or replicate Red Hat trademarks that are incorporated --- in this software or its documentation. --- - - -CREATE TABLE rhnServerNetAddress4 -( - interface_id NUMBER NOT NULL - CONSTRAINT rhn_srv_net_iaddress4_iid_fk - REFERENCES rhnServerNetInterface (id), - address VARCHAR2(64), - netmask VARCHAR2(64), - broadcast VARCHAR2(64), - created DATE - DEFAULT (sysdate) NOT NULL, - modified DATE - DEFAULT (sysdate) NOT NULL -) -ENABLE ROW MOVEMENT -; - -CREATE INDEX rhn_srv_net_address4_iid_address_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); diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql deleted file mode 100644 index dce721d..0000000 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ /dev/null @@ -1,38 +0,0 @@ --- --- Copyright (c) 2011 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- implied, including the implied warranties of MERCHANTABILITY or FITNESS --- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 --- along with this software; if not, see --- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. --- --- Red Hat trademarks are not licensed under GPLv2. No permission is --- granted to use or replicate Red Hat trademarks that are incorporated --- in this software or its documentation. --- - - -CREATE TABLE rhnServerNetAddress6 -( - interface_id NUMBER NOT NULL - CONSTRAINT rhn_srv_net_iaddress6_iid_fk - REFERENCES rhnServerNetInterface (id), - address VARCHAR2(45), - netmask VARCHAR2(49), - scope VARCHAR2(64), - created DATE - DEFAULT (sysdate) NOT NULL, - modified DATE - DEFAULT (sysdate) NOT NULL -) -ENABLE ROW MOVEMENT -; - -CREATE INDEX rhn_srv_net_address6_iid_scope_address_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); diff --git a/schema/spacewalk/common/tables/rhnServerNetInterface.sql b/schema/spacewalk/common/tables/rhnServerNetInterface.sql index 6d73c23..f115885 100644 --- a/schema/spacewalk/common/tables/rhnServerNetInterface.sql +++ b/schema/spacewalk/common/tables/rhnServerNetInterface.sql @@ -1,5 +1,5 @@ -- --- Copyright (c) 2008 -- 2011 Red Hat, Inc. +-- Copyright (c) 2008 Red Hat, Inc. -- -- This software is licensed to you under the GNU General Public License, -- version 2 (GPLv2). There is NO WARRANTY for this software, express or @@ -16,13 +16,13 @@
CREATE TABLE rhnServerNetInterface ( - id NUMBER NOT NULL - CONSTRAINT rhn_srv_net_iface_id_pk PRIMARY KEY - USING INDEX TABLESPACE [[4m_tbs]], server_id NUMBER NOT NULL CONSTRAINT rhn_srv_net_iface_sid_fk REFERENCES rhnServer (id), name VARCHAR2(32) NOT NULL, + ip_addr VARCHAR2(64), + netmask VARCHAR2(64), + broadcast VARCHAR2(64), hw_addr VARCHAR2(18), module VARCHAR2(128), created DATE @@ -40,4 +40,3 @@ CREATE INDEX rhn_srv_net_iface_sid_name_idx ALTER TABLE rhnServerNetInterface ADD CONSTRAINT rhn_srv_net_iface_sid_name_uq UNIQUE (server_id, name);
-CREATE SEQUENCE rhn_srv_net_iface_id_seq;
commit 592902bad18cbc1a8c872ef9f222245683f92fe4 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:51 2011 +0200
Revert "Fix table dependencies"
This reverts commit 4c6f241a4cfe99cac08d18a63b969e815d10cbba.
diff --git a/schema/spacewalk/common/tables/tables.deps b/schema/spacewalk/common/tables/tables.deps index 692e4b4..53fc6c7 100644 --- a/schema/spacewalk/common/tables/tables.deps +++ b/schema/spacewalk/common/tables/tables.deps @@ -105,8 +105,6 @@ rhnKickstartSessionHistory :: rhnKickstartSession rhnKickstartSessionState rhnKSTreeFile :: rhnKickstartableTree rhnChannelErrata rhnErrata \ rhnChecksum rhnKickstartTimezone :: rhnKSInstallType -rhnServerNetAddress4 :: rhnServerNetInterface -rhnServerNetAddress6 :: rhnServerNetInterface rhnOrgChannelSettings :: web_customer rhnChannel rhnOrgChannelSettingsType rhnOrgEntitlements :: rhnOrgEntitlementType rhnOrgErrataCacheQueue :: web_customer
commit 91f3c5ca6d6969ef73f6cad1352e95658a60888a Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:46 2011 +0200
Revert "shorten constraint identifiers to fit into given limits"
This reverts commit 72ab8d4d2e7516036ace9604eac51da399d6d1d1.
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql index edf90ce..6db6d3b 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql @@ -30,10 +30,9 @@ CREATE TABLE rhnServerNetAddress4 ENABLE ROW MOVEMENT ;
-CREATE INDEX rhn_net_addr4_iid_addr_idx +CREATE INDEX rhn_srv_net_address4_iid_address_idx ON rhnServerNetAddress4 (interface_id, address) TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress4 - ADD CONSTRAINT rhn_net_add4_iid_addr_uq UNIQUE (interface_id, address); - + ADD CONSTRAINT rhn_srv_net_address4_iid_address_uq UNIQUE (interface_id, address); diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql index 7dbde6c..dce721d 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -30,10 +30,9 @@ CREATE TABLE rhnServerNetAddress6 ENABLE ROW MOVEMENT ;
-CREATE INDEX rhn_net_addr6_iid_sc_addr_idx +CREATE INDEX rhn_srv_net_address6_iid_scope_address_idx ON rhnServerNetAddress4 (interface_id, scope, address) TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress6 - ADD CONSTRAINT rhn_net_addr6_iid_sc_addr_uq UNIQUE (interface_id, scope, address); - + ADD CONSTRAINT rhn_srv_net_address6_iid_scope_address_uq UNIQUE (interface_id, scope, address);
commit d6efd7c83469ae9aad804bc2e38f1393a346d556 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:40 2011 +0200
Revert "correct index creation"
This reverts commit deb1ff2107ca7db37389e85bfdb39a79bad8e2b9.
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql index 6413ba5..7dbde6c 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 rhnServerNetAddress6 (interface_id, scope, address) + ON rhnServerNetAddress4 (interface_id, scope, address) TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress6
commit f58d2f93f07b60fdf96fcfb138cd7dd8b165f0a5 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:35 2011 +0200
Revert "delete from rhnServerNetInterface: add on delete cascade"
This reverts commit 7be1ad0da7a79a3a445437770540cfe0824850f5.
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql index 7042fb2..edf90ce 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql @@ -18,8 +18,7 @@ CREATE TABLE rhnServerNetAddress4 ( interface_id NUMBER NOT NULL CONSTRAINT rhn_srv_net_iaddress4_iid_fk - REFERENCES rhnServerNetInterface (id) - ON DELETE CASCADE, + REFERENCES rhnServerNetInterface (id), 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 ee04fe5..6413ba5 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -18,8 +18,7 @@ CREATE TABLE rhnServerNetAddress6 ( interface_id NUMBER NOT NULL CONSTRAINT rhn_srv_net_iaddress6_iid_fk - REFERENCES rhnServerNetInterface (id) - ON DELETE CASCADE, + REFERENCES rhnServerNetInterface (id), address VARCHAR2(45), netmask VARCHAR2(49), scope VARCHAR2(64),
commit 46e8fe27e393fe995595c79dfbc7a4bc6dc8f63f Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:29 2011 +0200
Revert "update rhn_server.get_ip_address() to reflect IPv6 changes"
This reverts commit c8d1e52a302aaea688b049804bec6f441577d6e1.
diff --git a/schema/spacewalk/oracle/packages/rhn_server.pkb b/schema/spacewalk/oracle/packages/rhn_server.pkb index 8e3c640..72392fd 100644 --- a/schema/spacewalk/oracle/packages/rhn_server.pkb +++ b/schema/spacewalk/oracle/packages/rhn_server.pkb @@ -712,12 +712,10 @@ is server_id_in in number ) return varchar2 is cursor interfaces is - 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'; + select name, ip_addr + from rhnServerNetInterface + where server_id = server_id_in + and ip_addr != '127.0.0.1'; cursor addresses is select ipaddr ip_addr from rhnServerNetwork @@ -728,7 +726,7 @@ is return addr.ip_addr; end loop; for iface in interfaces loop - return iface.address; + return iface.ip_addr; 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 a1f7aca..3533094 100644 --- a/schema/spacewalk/postgres/packages/rhn_server.pkb +++ b/schema/spacewalk/postgres/packages/rhn_server.pkb @@ -764,12 +764,10 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p ) returns varchar as $$ declare interfaces cursor is - select ni.name as name, na4.address as address - from rhnServerNetInterface ni, - rhnServerNetAddress4 na4 + select name, ip_addr + from rhnServerNetInterface where server_id = server_id_in - and ni.id = na4.interface_id - and na4.address != '127.0.0.1'; + and ip_addr != '127.0.0.1'; addresses cursor is select ipaddr ip_addr from rhnServerNetwork @@ -780,7 +778,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.address; + return iface.ip_addr; end loop; return NULL; end$$ language plpgsql;
commit 5fac4e23c3a26784c0ba8a2d9c9e30b3be5dca93 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:23 2011 +0200
Revert "get_hw_info_as_clob: adapt to IPv6 changes"
This reverts commit 08ea44500d91d22877f0f361735df518ae601bd7.
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 d6fea07..ce1b580 100644 --- a/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql +++ b/schema/spacewalk/oracle/procs/get_hw_info_as_clob.sql @@ -15,11 +15,9 @@ begin select 1 n, sum(nrcpu) || ' CPUs' m from rhncpu where rhncpu.server_id = sid union all - 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 + select 2, name||' '||ip_addr||'/'||netmask||' '||hw_addr val + from rhnservernetinterface + where rhnservernetinterface.server_id = sid ) 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 6cc1982..9d899f3 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 6a538f3b05d412810baec118fbb7860348c60cd4 +-- oracle equivalent source sha1 4a627f5b31efb66c684dd14f0514e25a5ace94f2
create or replace function get_hw_info_as_clob( sid in rhnserver.id%TYPE, @@ -17,11 +17,9 @@ begin select 1 n, sum(nrcpu) || ' CPUs' m from rhncpu where rhncpu.server_id = sid union all - 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 + select 2, name||' '||coalesce(ip_addr,'')||'/'||coalesce(netmask,'')||' '||hw_addr val + from rhnservernetinterface + where rhnservernetinterface.server_id = sid ) X order by n, m ) loop
commit ece6447ff83de66444d7e939135c231c37419ce4 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:59:06 2011 +0200
Revert "schema upgrade"
This reverts commit cee165a5cdd26112f2cc2cbb9774f894c10675b2.
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql index b451aa3..7042fb2 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql @@ -31,10 +31,10 @@ CREATE TABLE rhnServerNetAddress4 ENABLE ROW MOVEMENT ;
-CREATE INDEX rhn_srv_net_addr4_iid_addr_idx +CREATE INDEX rhn_net_addr4_iid_addr_idx ON rhnServerNetAddress4 (interface_id, address) - TABLESPACE [[64k_tbs]]; + TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress4 - ADD CONSTRAINT rhn_srv_net_addr4_iid_addr_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 79b10e4..ee04fe5 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -31,10 +31,10 @@ CREATE TABLE rhnServerNetAddress6 ENABLE ROW MOVEMENT ;
-CREATE INDEX rhn_srv_net_ad6_iid_sc_ad_idx +CREATE INDEX rhn_net_addr6_iid_sc_addr_idx ON rhnServerNetAddress6 (interface_id, scope, address) - TABLESPACE [[64_tbs]]; + TABLESPACE [[8m_tbs]];
ALTER TABLE rhnServerNetAddress6 - ADD CONSTRAINT rhn_srv_net_ad6_iid_sc_ad_uq UNIQUE (interface_id, scope, address); + ADD CONSTRAINT rhn_net_addr6_iid_sc_addr_uq UNIQUE (interface_id, scope, address);
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.oracle deleted file mode 100644 index 1f0ab2a..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.oracle +++ /dev/null @@ -1,9 +0,0 @@ -alter table rhnServerNetInterface add id number; - -create sequence rhn_srv_net_iface_id_seq; - -update rhnServerNetInterface set id = rhn_srv_net_iface_id_seq.nextval; - -alter table rhnServerNetInterface add constraint rhn_srv_net_iface_id_nn check (id is not null); - -alter table rhnServerNetInterface add constraint rhn_srv_net_iface_id_pk primary key ( id ); diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.postgresql deleted file mode 100644 index c2b448d..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.postgresql +++ /dev/null @@ -1,11 +0,0 @@ --- oracle equivalent source sha1 f5872e029cf1b053c88bd876afc8f253461772ac - -alter table rhnServerNetInterface add id numeric; - -create sequence rhn_srv_net_iface_id_seq; - -update rhnServerNetInterface set id = nextval('rhn_srv_net_iface_id_seq'); - -alter table rhnServerNetInterface add constraint rhn_srv_net_iface_id_nn check (id is not null); - -alter table rhnServerNetInterface add constraint rhn_srv_net_iface_id_pk primary key ( id ); diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.oracle deleted file mode 100644 index 7922fdf..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.oracle +++ /dev/null @@ -1,39 +0,0 @@ --- --- Copyright (c) 2011 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- implied, including the implied warranties of MERCHANTABILITY or FITNESS --- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 --- along with this software; if not, see --- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. --- --- Red Hat trademarks are not licensed under GPLv2. No permission is --- granted to use or replicate Red Hat trademarks that are incorporated --- in this software or its documentation. --- - - -CREATE TABLE rhnServerNetAddress4 -( - interface_id NUMBER NOT NULL - CONSTRAINT rhn_srv_net_iaddress4_iid_fk - REFERENCES rhnServerNetInterface (id) - ON DELETE CASCADE, - address VARCHAR2(64), - netmask VARCHAR2(64), - broadcast VARCHAR2(64), - created DATE - DEFAULT (sysdate) NOT NULL, - modified DATE - DEFAULT (sysdate) NOT NULL -) -ENABLE ROW MOVEMENT -; - -CREATE INDEX rhn_srv_net_addr4_iid_addr_idx - ON rhnServerNetAddress4 (interface_id, address) - TABLESPACE [[64k_tbs]]; - -ALTER TABLE rhnServerNetAddress4 - ADD CONSTRAINT rhn_srv_net_addr4_iid_addr_uq UNIQUE (interface_id, address); diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.postgresql deleted file mode 100644 index 7b8a6b7..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.postgresql +++ /dev/null @@ -1,18 +0,0 @@ --- oracle equivalent source sha1 af31dbcccd3799c2cd577137f6f7fd608288b2c4 - -CREATE TABLE rhnServerNetAddress4 -( - interface_id NUMERIC NOT NULL - CONSTRAINT rhn_srv_net_iaddress4_iid_fk - REFERENCES rhnServerNetInterface (id) - ON DELETE CASCADE, - address VARCHAR(64), - netmask VARCHAR(64), - broadcast VARCHAR(64), - created timestamptz default current_timestamp NOT NULL, - modified timestamptz default current_timestamp NOT NULL -); - -CREATE UNIQUE INDEX rhn_srv_net_addr4_iid_addr_uq - ON rhnServerNetAddress4 (interface_id, address) - ; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/111-add_rhnServerNetAddress4_data.sql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/111-add_rhnServerNetAddress4_data.sql deleted file mode 100644 index bda4fb8..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/111-add_rhnServerNetAddress4_data.sql +++ /dev/null @@ -1,6 +0,0 @@ -INSERT INTO rhnServerNetAddress4 - (interface_id, address, netmask, broadcast, created) - SELECT id, ip_addr, netmask, broadcast, created - FROM rhnServerNetInterface; - -COMMIT; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/115-drop_columns_rhnServerNetInterface.sql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/115-drop_columns_rhnServerNetInterface.sql deleted file mode 100644 index 64e42e1..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/115-drop_columns_rhnServerNetInterface.sql +++ /dev/null @@ -1,3 +0,0 @@ -ALTER TABLE rhnServerNetInterface DROP COLUMN ip_addr; -ALTER TABLE rhnServerNetInterface DROP COLUMN netmask; -ALTER TABLE rhnServerNetInterface DROP COLUMN broadcast; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.oracle deleted file mode 100644 index 42c78b2..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.oracle +++ /dev/null @@ -1,39 +0,0 @@ --- --- Copyright (c) 2011 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- implied, including the implied warranties of MERCHANTABILITY or FITNESS --- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 --- along with this software; if not, see --- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. --- --- Red Hat trademarks are not licensed under GPLv2. No permission is --- granted to use or replicate Red Hat trademarks that are incorporated --- in this software or its documentation. --- - - -CREATE TABLE rhnServerNetAddress6 -( - interface_id NUMBER NOT NULL - CONSTRAINT rhn_srv_net_iaddress6_iid_fk - REFERENCES rhnServerNetInterface (id) - ON DELETE CASCADE, - address VARCHAR2(45), - netmask VARCHAR2(49), - scope VARCHAR2(64), - created DATE - DEFAULT (sysdate) NOT NULL, - modified DATE - DEFAULT (sysdate) NOT NULL -) -ENABLE ROW MOVEMENT -; - -CREATE INDEX rhn_srv_net_ad6_iid_sc_ad_idx - ON rhnServerNetAddress6 (interface_id, scope, address) - TABLESPACE [[64k_tbs]]; - -ALTER TABLE rhnServerNetAddress6 - ADD CONSTRAINT rhn_srv_net_ad6_iid_sc_ad_uq UNIQUE (interface_id, scope, address); diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.postgresql deleted file mode 100644 index 844c601..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.postgresql +++ /dev/null @@ -1,17 +0,0 @@ --- oracle equivalent source sha1 7c11068302cdb144f446828d4db0f244dbf12293 - -CREATE TABLE rhnServerNetAddress6 -( - interface_id NUMERIC NOT NULL - CONSTRAINT rhn_srv_net_iaddress6_iid_fk - REFERENCES rhnServerNetInterface (id) - ON DELETE CASCADE, - address VARCHAR(45), - netmask VARCHAR(49), - scope VARCHAR(64), - created timestamptz default current_timestamp NOT NULL, - modified timestamptz default current_timestamp NOT NULL -); - -CREATE UNIQUE INDEX rhn_srv_net_ad6_iid_sc_ad_uq - ON rhnServerNetAddress6 (interface_id, scope, address);
commit ad7c7f9205397e99007de90b14c7be5ad39f9500 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:58:07 2011 +0200
Revert "upgrade script for rhn_server.pkb"
This reverts commit aa6a12287e103d424ebf882cf0dc003ea9c93e91.
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.oracle deleted file mode 100644 index 87600df..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.oracle +++ /dev/null @@ -1,750 +0,0 @@ --- --- Copyright (c) 2008--2010 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- implied, including the implied warranties of MERCHANTABILITY or FITNESS --- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 --- along with this software; if not, see --- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. --- --- Red Hat trademarks are not licensed under GPLv2. No permission is --- granted to use or replicate Red Hat trademarks that are incorporated --- in this software or its documentation. --- --- --- --- - -create or replace -package body rhn_server -is - function system_service_level( - server_id_in in number, - service_level_in in varchar2 - ) return number is - - cursor ents is - select label from rhnServerEntitlementView - where server_id = server_id_in; - - retval number := 0; - - begin - for ent in ents loop - retval := rhn_entitlements.entitlement_grants_service (ent.label, service_level_in); - if retval = 1 then - return retval; - end if; - end loop; - - return retval; - - end system_service_level; - - - function can_change_base_channel(server_id_in IN NUMBER) - return number - is - throwaway number; - begin - -- the idea: if we get past this query, the server is - -- neither sat nor proxy, so base channel is changeable - - select 1 into throwaway - from rhnServer S - where S.id = server_id_in - and not exists (select 1 from rhnSatelliteInfo SI where SI.server_id = S.id) - and not exists (select 1 from rhnProxyInfo PI where PI.server_id = S.id); - - return 1; - exception - when no_data_found - then - return 0; - end can_change_base_channel; - - procedure set_custom_value( - server_id_in in number, - user_id_in in number, - key_label_in in varchar2, - value_in in varchar2 - ) is - key_id_val number; - begin - select CDK.id into key_id_val - from rhnCustomDataKey CDK, - rhnServer S - where S.id = server_id_in - and S.org_id = CDK.org_id - and CDK.label = key_label_in; - - begin - insert into rhnServerCustomDataValue (server_id, key_id, value, created_by, last_modified_by) - values (server_id_in, key_id_val, value_in, user_id_in, user_id_in); - exception - when DUP_VAL_ON_INDEX - then - update rhnServerCustomDataValue - set value = value_in, - last_modified_by = user_id_in - where server_id = server_id_in - and key_id = key_id_val; - end; - - end set_custom_value; - - function bulk_set_custom_value( - key_label_in in varchar2, - value_in in varchar2, - set_label_in in varchar2, - set_uid_in in number - ) - return integer - is - i integer := 0; - begin - i := 0; - for server in rhn_set.set_iterator(set_label_in, set_uid_in) - loop - if rhn_server.system_service_level(server.element, 'provisioning') = 1 then - rhn_server.set_custom_value(server.element, set_uid_in, key_label_in, value_in); - i := i + 1; - end if; - end loop server; - return i; - end bulk_set_custom_value; - - procedure bulk_snapshot_tag( - org_id_in in number, - tagname_in in varchar2, - set_label_in in varchar2, - set_uid_in in number - ) is - snapshot_id number; - begin - for server in rhn_set.set_iterator(set_label_in, set_uid_in) - loop - if rhn_server.system_service_level(server.element, 'provisioning') = 1 then - begin - select max(id) into snapshot_id - from rhnSnapshot - where server_id = server.element; - exception - when NO_DATA_FOUND then - rhn_server.snapshot_server(server.element, 'tagging system: ' || tagname_in); - - select max(id) into snapshot_id - from rhnSnapshot - where server_id = server.element; - end; - - -- now have a snapshot_id to work with... - begin - rhn_server.tag_snapshot(snapshot_id, org_id_in, tagname_in); - exception - when DUP_VAL_ON_INDEX - then - -- do nothing, be forgiving... - null; - end; - end if; - end loop server; - end bulk_snapshot_tag; - - procedure tag_delete( - server_id_in in number, - tag_id_in in number - ) is - cursor snapshots is - select snapshot_id - from rhnSnapshotTag - where tag_id = tag_id_in; - tag_id_tmp number; - begin - select id into tag_id_tmp - from rhnTag - where id = tag_id_in - for update; - - delete - from rhnSnapshotTag - where server_id = server_id_in - and tag_id = tag_id_in; - for snapshot in snapshots loop - return; - end loop; - delete - from rhnTag - where id = tag_id_in; - end tag_delete; - - procedure tag_snapshot( - snapshot_id_in in number, - org_id_in in number, - tagname_in in varchar2 - ) is - begin - insert into rhnSnapshotTag (snapshot_id, server_id, tag_id) - select snapshot_id_in, server_id, lookup_tag(org_id_in, tagname_in) - from rhnSnapshot - where id = snapshot_id_in; - end tag_snapshot; - - procedure bulk_snapshot( - reason_in in varchar2, - set_label_in in varchar2, - set_uid_in in number - ) is - begin - for server in rhn_set.set_iterator(set_label_in, set_uid_in) - loop - if rhn_server.system_service_level(server.element, 'provisioning') = 1 then - rhn_server.snapshot_server(server.element, reason_in); - end if; - end loop server; - end bulk_snapshot; - - procedure snapshot_server( - server_id_in in number, - reason_in in varchar2 - ) is - snapshot_id number; - cursor revisions is - select distinct - cr.id - from rhnConfigRevision cr, - rhnConfigFileName cfn, - rhnConfigFile cf, - rhnConfigChannel cc, - rhnServerConfigChannel scc - where 1=1 - and scc.server_id = server_id_in - and scc.config_channel_id = cc.id - and cc.id = cf.config_channel_id - and cf.id = cr.config_file_id - and cr.id = cf.latest_config_revision_id - and cf.config_file_name_id = cfn.id - and cf.id = lookup_first_matching_cf(scc.server_id, cfn.path); - locked integer; - begin - select rhn_snapshot_id_seq.nextval into snapshot_id from dual; - - insert into rhnSnapshot (id, org_id, server_id, reason) ( - select snapshot_id, - s.org_id, - server_id_in, - reason_in - from rhnServer s - where s.id = server_id_in - ); - insert into rhnSnapshotChannel (snapshot_id, channel_id) ( - select snapshot_id, sc.channel_id - from rhnServerChannel sc - where sc.server_id = server_id_in - ); - insert into rhnSnapshotServerGroup (snapshot_id, server_group_id) ( - select snapshot_id, sgm.server_group_id - from rhnServerGroupMembers sgm - where sgm.server_id = server_id_in - ); - locked := 0; - while true loop - begin - insert into rhnPackageNEVRA (id, name_id, evr_id, package_arch_id) - select rhn_pkgnevra_id_seq.nextval, sp.name_id, sp.evr_id, sp.package_arch_id - from rhnServerPackage sp - where sp.server_id = server_id_in - and not exists - (select 1 - from rhnPackageNEVRA nevra - where nevra.name_id = sp.name_id - and nevra.evr_id = sp.evr_id - and (nevra.package_arch_id = sp.package_arch_id - or (nevra.package_arch_id is null - and sp.package_arch_id is null))); - exit; - exception when dup_val_on_index then - if locked = 1 then - raise; - else - lock table rhnPackageNEVRA in exclusive mode; - locked := 1; - end if; - end; - end loop; - insert into rhnSnapshotPackage (snapshot_id, nevra_id) ( - select distinct snapshot_id, nevra.id - from rhnServerPackage sp, rhnPackageNEVRA nevra - where sp.server_id = server_id_in - and nevra.name_id = sp.name_id - and nevra.evr_id = sp.evr_id - and (nevra.package_arch_id = sp.package_arch_id - or (nevra.package_arch_id is null - and sp.package_arch_id is null)) - ); - - insert into rhnSnapshotConfigChannel ( snapshot_id, config_channel_id ) ( - select snapshot_id, scc.config_channel_id - from rhnServerConfigChannel scc - where server_id = server_id_in - ); - - for revision in revisions loop - insert into rhnSnapshotConfigRevision ( - snapshot_id, config_revision_id - ) values ( - snapshot_id, revision.id - ); - end loop; - end snapshot_server; - - procedure remove_action( - server_id_in in number, - action_id_in in number - ) is - -- this really wants "nulls last", but 8.1.7.3.0 sucks ass. - -- instead, we make a local table that holds our - -- list of ids with null prereqs. There's surely a better way - -- (an array instead of a table maybe? who knows...) - -- but I've got code to do this handy that I can look at ;) - cursor chained_actions is - select id, prerequisite - from rhnAction - start with id = action_id_in - connect by prior id = prerequisite - order by prerequisite desc; - cursor sessions is - select s.id - from rhnKickstartSession s - where server_id_in in (s.old_server_id, s.new_server_id) - and s.action_id = action_id_in - and not exists ( - select 1 - from rhnKickstartSessionState ss - where ss.id = s.state_id - and ss.label in ('failed','complete') - ); - type chain_end_type is table of number index by binary_integer; - chain_ends chain_end_type; - i number; - prereq number := 1; - begin - select prerequisite - into prereq - from rhnAction - where id = action_id_in; - - if prereq is not null then - rhn_exception.raise_exception('action_is_child'); - end if; - - i := 0; - for action in chained_actions loop - if action.prerequisite is null then - chain_ends(i) := action.id; - i := i + 1; - else - delete from rhnServerAction - where server_id = server_id_in - and action_id = action.id; - end if; - end loop; - i := chain_ends.first; - while i is not null loop - delete from rhnServerAction - where server_id = server_id_in - and action_id = chain_ends(i); - i := chain_ends.next(i); - end loop; - for s in sessions loop - update rhnKickstartSession - set state_id = ( - select id - from rhnKickstartSessionState - where label = 'failed' - ), - action_id = null - where id = s.id; - set_ks_session_history_message(s.id, 'failed', 'Kickstart cancelled due to action removal'); - end loop; - end remove_action; - - function check_user_access(server_id_in in number, user_id_in in number) - return number - is - has_access number; - begin - -- first check; if this returns no rows, then the server/user are in different orgs, and we bail - select 1 into has_access - from rhnServer S, - web_contact wc - where wc.org_id = s.org_id - and s.id = server_id_in - and wc.id = user_id_in; - - -- okay, so they're in the same org. if we have an org admin, they get a free pass - if rhn_user.check_role(user_id_in, 'org_admin') = 1 - then - return 1; - end if; - - select 1 into has_access - from rhnServerGroupMembers SGM, - rhnUserServerGroupPerms USG - where SGM.server_group_id = USG.server_group_id - and SGM.server_id = server_id_in - and USG.user_id = user_id_in - and rownum = 1; - - return 1; - exception - when no_data_found - then - return 0; - end check_user_access; - - -- ******************************************************************* - -- FUNCTION: can_server_consume_virt_slot - -- Returns 1 if the server id is eligible to consume a virtual slot, - -- else returns 0. - -- Called by: insert_into_servergroup, delete_from_servergroup - -- ******************************************************************* - function can_server_consume_virt_slot(server_id_in in number, - group_type_in in - rhnServerGroupType.label%TYPE) - return number - is - - cursor server_virt_slots is - select vi.VIRTUAL_SYSTEM_ID - from - rhnVirtualInstance vi - where - -- server id is a virtual instance - vi.VIRTUAL_SYSTEM_ID = server_id_in - -- server id's host is virt entitled - and exists ( select 1 - from rhnServerEntitlementView sev - where vi.HOST_SYSTEM_ID = sev.server_id - and sev.label in ('virtualization_host', - 'virtualization_host_platform') ) - -- server id's host also has the ent we want - and exists ( select 1 - from rhnServerEntitlementView sev2 - where vi.HOST_SYSTEM_ID = sev2.server_id - and sev2.label = group_type_in ); - - begin - for server_virt_slot in server_virt_slots loop - return 1; - end loop; - return 0; - end can_server_consume_virt_slot; - - - procedure insert_into_servergroup ( - server_id_in in number, - server_group_id_in in number - ) is - used_slots number; - max_slots number; - org_id number; - mgmt_available number; - mgmt_upgrade number; - mgmt_sgid number; - prov_available number; - prov_upgrade number; - prov_sgid number; - group_label rhnServerGroupType.label%TYPE; - group_type number; - begin - -- frist, group_type = null, because it's easy... - - -- this will rowlock the servergroup we're trying to change; - -- we probably need to lock the other one, but I think the chances - -- of it being a real issue are very small for now... - select sg.group_type, sg.org_id, sg.current_members, sg.max_members - into group_type, org_id, used_slots, max_slots - from rhnServerGroup sg - where sg.id = server_group_id_in - for update of sg.current_members; - - if group_type is null then - if used_slots >= max_slots then - rhn_exception.raise_exception('servergroup_max_members'); - end if; - - insert into rhnServerGroupMembers( - server_id, server_group_id - ) values ( - server_id_in, server_group_id_in - ); - update rhnServerGroup - set current_members = current_members + 1 - where id = server_group_id_in; - - rhn_cache.update_perms_for_server_group(server_group_id_in); - return; - end if; - - -- now for group_type != null - -- - select label - into group_label - from rhnServerGroupType sgt - where sgt.id = group_type; - - -- the naive easy path that gets hit most often and has to be quickest. - if group_label in ('sw_mgr_entitled', - 'enterprise_entitled', - 'monitoring_entitled', - 'provisioning_entitled', - 'virtualization_host', - 'virtualization_host_platform') then - if used_slots >= max_slots and - (can_server_consume_virt_slot(server_id_in, group_label) != 1) - then - rhn_exception.raise_exception('servergroup_max_members'); - end if; - - insert into rhnServerGroupMembers( - server_id, server_group_id - ) values ( - server_id_in, server_group_id_in - ); - - -- Only update current members if the system in consuming a - -- physical slot. - if can_server_consume_virt_slot(server_id_in, group_label) = 0 then - update rhnServerGroup - set current_members = current_members + 1 - where id = server_group_id_in; - end if; - - return; - end if; - end; - - function insert_into_servergroup_maybe ( - server_id_in in number, - server_group_id_in in number - ) return number is - retval number := 0; - cursor servergroups is - select s.id server_id, - sg.id server_group_id - from rhnServerGroup sg, - rhnServer s - where s.id = server_id_in - and sg.id = server_group_id_in - and s.org_id = sg.org_id - and not exists ( - select 1 - from rhnServerGroupMembers sgm - where sgm.server_id = s.id - and sgm.server_group_id = sg.id - ); - begin - for sgm in servergroups loop - rhn_server.insert_into_servergroup(sgm.server_id, sgm.server_group_id); - retval := retval + 1; - end loop; - return retval; - end insert_into_servergroup_maybe; - - procedure insert_set_into_servergroup ( - server_group_id_in in number, - user_id_in in number, - set_label_in in varchar2 - ) is - cursor servers is - select st.element id - from rhnSet st - where st.user_id = user_id_in - and st.label = set_label_in - and exists ( - select 1 - from rhnUserManagedServerGroups umsg - where umsg.server_group_id = server_group_id_in - and umsg.user_id = user_id_in - ) - and not exists ( - select 1 - from rhnServerGroupMembers sgm - where sgm.server_id = st.element - and sgm.server_group_id = server_group_id_in - ); - begin - for s in servers loop - rhn_server.insert_into_servergroup(s.id, server_group_id_in); - end loop; - end insert_set_into_servergroup; - - procedure delete_from_servergroup ( - server_id_in in number, - server_group_id_in in number - ) is - cursor server_virt_groups is - select 1 - from rhnServerEntitlementVirtual sev - where sev.server_id = server_id_in - and sev.server_group_id = server_group_id_in; - - oid number; - mgmt_sgid number; - label rhnServerGroupType.label%TYPE; - group_type number; - begin - begin - select sg.group_type, sg.org_id - into group_type, oid - from rhnServerGroupMembers sgm, - rhnServerGroup sg - where sg.id = server_group_id_in - and sg.id = sgm.server_group_id - and sgm.server_id = server_id_in - for update of sg.current_members; - exception - when no_data_found then - rhn_exception.raise_exception('server_not_in_group'); - end; - - -- do group_type is null first - if group_type is null then - delete from rhnServerGroupMembers - where server_group_id = server_group_id_in - and server_id = server_id_in; - update rhnServerGroup - set current_members = current_members - 1 - where id = server_group_id_in; - rhn_cache.update_perms_for_server_group(server_group_id_in); - return; - end if; - - select sgt.label - into label - from rhnServerGroupType sgt - where sgt.id = group_type; - - if label in ('sw_mgr_entitled', - 'enterprise_entitled', - 'provisioning_entitled', - 'monitoring_entitled', - 'virtualization_host', - 'virtualization_host_platform') then - - -- Only update current members if the system is consuming - -- a physical slot. - for server_virt_group in server_virt_groups loop - delete from rhnServerGroupMembers - where server_group_id = server_group_id_in - and server_id = server_id_in; - return; - end loop; - - delete from rhnServerGroupMembers - where server_group_id = server_group_id_in - and server_id = server_id_in; - - update rhnServerGroup - set current_members = current_members - 1 - where id = server_group_id_in; - - end if; - end; - - procedure delete_set_from_servergroup ( - server_group_id_in in number, - user_id_in in number, - set_label_in in varchar2 - ) is - cursor servergroups is - select sgm.server_id, sgm.server_group_id - from rhnSet st, - rhnServerGroupMembers sgm - where sgm.server_group_id = server_group_id_in - and st.user_id = user_id_in - and st.label = set_label_in - and sgm.server_id = st.element - and exists ( - select 1 - from rhnUserManagedServerGroups usgp - where usgp.server_group_id = server_group_id_in - and usgp.user_id = user_id_in - ); - begin - for sgm in servergroups loop - rhn_server.delete_from_servergroup(sgm.server_id, server_group_id_in); - end loop; - end delete_set_from_servergroup; - - procedure clear_servergroup ( - server_group_id_in in number - ) is - cursor servers is - select sgm.server_id id - from rhnServerGroupMembers sgm - where sgm.server_group_id = server_group_id_in; - begin - for s in servers loop - rhn_server.delete_from_servergroup(s.id, server_group_id_in); - end loop; - end clear_servergroup; - - procedure delete_from_org_servergroups ( - server_id_in in number - ) is - cursor servergroups is - select sgm.server_group_id id - from rhnServerGroup sg, - rhnServerGroupMembers sgm - where sgm.server_id = server_id_in - and sgm.server_group_id = sg.id - and sg.group_type is null; - begin - for sg in servergroups loop - rhn_server.delete_from_servergroup(server_id_in, sg.id); - end loop; - end delete_from_org_servergroups; - - function get_ip_address ( - server_id_in in number - ) return varchar2 is - cursor interfaces is - 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 - where server_id = server_id_in - and ipaddr != '127.0.0.1'; - begin - for addr in addresses loop - return addr.ip_addr; - end loop; - for iface in interfaces loop - return iface.address; - end loop; - return NULL; - end get_ip_address; - - procedure update_needed_cache(server_id_in in number) - is - begin - delete from rhnServerNeededCache - where server_id = server_id_in; - insert into rhnServerNeededCache - (server_id, errata_id, package_id) - (select distinct server_id, errata_id, package_id - from rhnServerNeededView - where server_id = server_id_in); - end update_needed_cache; - -end rhn_server; -/ -SHOW ERRORS diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql deleted file mode 100644 index d103a65..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql +++ /dev/null @@ -1,802 +0,0 @@ --- --- Copyright (c) 2008--2010 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- implied, including the implied warranties of MERCHANTABILITY or FITNESS --- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 --- along with this software; if not, see --- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. --- --- Red Hat trademarks are not licensed under GPLv2. No permission is --- granted to use or replicate Red Hat trademarks that are incorporated --- in this software or its documentation. --- --- --- --- - -create schema rhn_server; - ---update pg_setting -update pg_settings set setting = 'rhn_server,' || setting where name = 'search_path'; - - create or replace function system_service_level( - server_id_in in numeric, - service_level_in in varchar - ) returns numeric as $$ - declare - ents cursor is - select label from rhnServerEntitlementView - where server_id = server_id_in; - - retval numeric := 0; - - begin - for ent in ents loop - retval := rhn_entitlements.entitlement_grants_service (ent.label, service_level_in); - if retval = 1 then - return retval; - end if; - end loop; - - return retval; - - end$$ language plpgsql; - - - create or replace function can_change_base_channel(server_id_in IN NUMERIC) - returns numeric - as $$ - declare - throwaway numeric; - begin - -- the idea: if we get past this query, the server is - -- neither sat nor proxy, so base channel is changeable - - select 1 into throwaway - from rhnServer S - where S.id = server_id_in - and not exists (select 1 from rhnSatelliteInfo SI where SI.server_id = S.id) - and not exists (select 1 from rhnProxyInfo PI where PI.server_id = S.id); - - if not found then - return 0; - end if; - - return 1; - end$$ language plpgsql; - - create or replace function set_custom_value( - server_id_in in numeric, - user_id_in in numeric, - key_label_in in varchar, - value_in in varchar - ) returns void - as $$ - declare - key_id_val numeric; - begin - select CDK.id into strict key_id_val - from rhnCustomDataKey CDK, - rhnServer S - where S.id = server_id_in - and S.org_id = CDK.org_id - and CDK.label = key_label_in; - - begin - insert into rhnServerCustomDataValue (server_id, key_id, value, created_by, last_modified_by) - values (server_id_in, key_id_val, value_in, user_id_in, user_id_in); - exception - when UNIQUE_VIOLATION - then - update rhnServerCustomDataValue - set value = value_in, - last_modified_by = user_id_in - where server_id = server_id_in - and key_id = key_id_val; - end; - - end$$ language plpgsql; - - create or replace function bulk_set_custom_value( - key_label_in in varchar, - value_in in varchar, - set_label_in in varchar, - set_uid_in in numeric - ) - returns integer - as $$ - declare - i integer; - server record; - begin - i := 0; - for server in - SELECT user_id, label, element, element_two - FROM rhnSet - WHERE label = set_label_in - AND user_id = set_uid_in - loop - if rhn_server.system_service_level(server.element, 'provisioning') = 1 then - perform rhn_server.set_custom_value(server.element, set_uid_in, key_label_in, value_in); - i := i + 1; - end if; - end loop; - return i; - end$$ language plpgsql; - - create or replace function bulk_snapshot_tag( - org_id_in in numeric, - tagname_in in varchar, - set_label_in in varchar, - set_uid_in in numeric - ) returns void - as $$ - declare - server record; - snapshot_id numeric; - begin - for server in - SELECT user_id, label, element, element_two - FROM rhnSet - WHERE label = set_label_in - AND user_id = set_uid_in - loop - if rhn_server.system_service_level(server.element, 'provisioning') = 1 then - select max(id) into snapshot_id - from rhnSnapshot - where server_id = server.element; - - if snapshot_id is null then - perform rhn_server.snapshot_server(server.element, 'tagging system: ' || tagname_in); - - select max(id) into snapshot_id - from rhnSnapshot - where server_id = server.element; - end if; - - -- now have a snapshot_id to work with... - begin - perform rhn_server.tag_snapshot(snapshot_id, org_id_in, tagname_in); - exception - when UNIQUE_VIOLATION - then - -- do nothing, be forgiving... - null; - end; - end if; - end loop; - end$$ language plpgsql; - - create or replace function tag_delete( - server_id_in in numeric, - tag_id_in in numeric - ) returns void - as $$ - declare - snapshots cursor is - select snapshot_id - from rhnSnapshotTag - where tag_id = tag_id_in; - tag_id_tmp numeric; - begin - select id into tag_id_tmp - from rhnTag - where id = tag_id_in - for update; - - delete - from rhnSnapshotTag - where server_id = server_id_in - and tag_id = tag_id_in; - for snapshot in snapshots loop - return; - end loop; - delete - from rhnTag - where id = tag_id_in; - end$$ language plpgsql; - - create or replace function tag_snapshot( - snapshot_id_in in numeric, - org_id_in in numeric, - tagname_in in varchar - ) returns void - as $$ - begin - insert into rhnSnapshotTag (snapshot_id, server_id, tag_id) - select snapshot_id_in, server_id, lookup_tag(org_id_in, tagname_in) - from rhnSnapshot - where id = snapshot_id_in; - end$$ language plpgsql; - - create or replace function bulk_snapshot( - reason_in in varchar, - set_label_in in varchar, - set_uid_in in numeric - ) returns void - as $$ - declare - server record; - begin - for server in - SELECT user_id, label, element, element_two - FROM rhnSet - WHERE label = set_label_in - AND user_id = set_uid_in - loop - if rhn_server.system_service_level(server.element, 'provisioning') = 1 then - perform rhn_server.snapshot_server(server.element, reason_in); - end if; - end loop; - end$$ language plpgsql; - - create or replace function snapshot_server( - server_id_in in numeric, - reason_in in varchar - ) returns void - as $$ - declare - snapshot_id_v numeric; - revisions cursor is - select distinct - cr.id - from rhnConfigRevision cr, - rhnConfigFileName cfn, - rhnConfigFile cf, - rhnConfigChannel cc, - rhnServerConfigChannel scc - where 1=1 - and scc.server_id = server_id_in - and scc.config_channel_id = cc.id - and cc.id = cf.config_channel_id - and cf.id = cr.config_file_id - and cr.id = cf.latest_config_revision_id - and cf.config_file_name_id = cfn.id - and cf.id = lookup_first_matching_cf(scc.server_id, cfn.path); - locked integer; - begin - select nextval('rhn_snapshot_id_seq') into snapshot_id_v; - - insert into rhnSnapshot (id, org_id, server_id, reason) ( - select snapshot_id_v, - s.org_id, - server_id_in, - reason_in - from rhnServer s - where s.id = server_id_in - ); - insert into rhnSnapshotChannel (snapshot_id, channel_id) ( - select snapshot_id_v, sc.channel_id - from rhnServerChannel sc - where sc.server_id = server_id_in - ); - insert into rhnSnapshotServerGroup (snapshot_id, server_group_id) ( - select snapshot_id_v, sgm.server_group_id - from rhnServerGroupMembers sgm - where sgm.server_id = server_id_in - ); - locked := 0; - <<iloop>> - while true loop - begin - insert into rhnPackageNEVRA (id, name_id, evr_id, package_arch_id) - select nextval('rhn_pkgnevra_id_seq'), sp.name_id, sp.evr_id, sp.package_arch_id - from rhnServerPackage sp - where sp.server_id = server_id_in - and not exists - (select 1 - from rhnPackageNEVRA nevra - where nevra.name_id = sp.name_id - and nevra.evr_id = sp.evr_id - and (nevra.package_arch_id = sp.package_arch_id - or (nevra.package_arch_id is null - and sp.package_arch_id is null))); - exit iloop; - exception when unique_violation then - if locked = 1 then - raise; - else - lock table rhnPackageNEVRA in exclusive mode; - locked := 1; - end if; - end; - end loop; - insert into rhnSnapshotPackage (snapshot_id, nevra_id) ( - select distinct snapshot_id_v, nevra.id - from rhnServerPackage sp, rhnPackageNEVRA nevra - where sp.server_id = server_id_in - and nevra.name_id = sp.name_id - and nevra.evr_id = sp.evr_id - and (nevra.package_arch_id = sp.package_arch_id - or (nevra.package_arch_id is null - and sp.package_arch_id is null)) - ); - - insert into rhnSnapshotConfigChannel ( snapshot_id, config_channel_id ) ( - select snapshot_id_v, scc.config_channel_id - from rhnServerConfigChannel scc - where server_id = server_id_in - ); - - for revision in revisions loop - insert into rhnSnapshotConfigRevision ( - snapshot_id, config_revision_id - ) values ( - snapshot_id_v, revision.id - ); - end loop; - end$$ language plpgsql; - - create or replace function remove_action( - server_id_in in numeric, - action_id_in in numeric - ) returns void - as $$ - declare - -- this really wants "nulls last", but 8.1.7.3.0 sucks ass. - -- instead, we make a local table that holds our - -- list of ids with null prereqs. There's surely a better way - -- (an array instead of a table maybe? who knows...) - -- but I've got code to do this handy that I can look at ;) - chained_actions cursor is - with recursive r(id, prerequisite) as ( - select id, prerequisite - from rhnAction - where id = action_id_in - union all - select r1.id, r1.prerequisite - from rhnAction r1, r - where r.id = r1.prerequisite - ) - select * from r - order by prerequisite desc; - sessions cursor is - select s.id - from rhnKickstartSession s - where server_id_in in (s.old_server_id, s.new_server_id) - and s.action_id = action_id_in - and not exists ( - select 1 - from rhnKickstartSessionState ss - where ss.id = s.state_id - and ss.label in ('failed','complete') - ); - chain_ends numeric[]; - i numeric; - prereq numeric := 1; - begin - select prerequisite - into prereq - from rhnAction - where id = action_id_in; - - if prereq is not null then - perform rhn_exception.raise_exception('action_is_child'); - end if; - - chain_ends := '{}'; - i := 1; - for action in chained_actions loop - if action.prerequisite is null then - chain_ends[i] := action.id; - i := i + 1; - else - delete from rhnServerAction - where server_id = server_id_in - and action_id = action.id; - end if; - end loop; - - delete from rhnServerAction - where server_id = server_id_in - and action_id = any(chain_ends); - - for s in sessions loop - update rhnKickstartSession - set state_id = ( - select id - from rhnKickstartSessionState - where label = 'failed' - ), - action_id = null - where id = s.id; - perform set_ks_session_history_message(s.id, 'failed', 'Kickstart cancelled due to action removal'); - end loop; - end$$ language plpgsql; - - create or replace function check_user_access(server_id_in in numeric, user_id_in in numeric) - returns numeric - as $$ - declare - has_access numeric; - begin - -- first check; if this returns no rows, then the server/user are in different orgs, and we bail - select 1 into has_access - from rhnServer S, - web_contact wc - where wc.org_id = s.org_id - and s.id = server_id_in - and wc.id = user_id_in; - - if not found then - return 0; - end if; - - -- okay, so they're in the same org. if we have an org admin, they get a free pass - if rhn_user.check_role(user_id_in, 'org_admin') = 1 - then - return 1; - end if; - - select 1 into has_access - from rhnServerGroupMembers SGM, - rhnUserServerGroupPerms USG - where SGM.server_group_id = USG.server_group_id - and SGM.server_id = server_id_in - and USG.user_id = user_id_in; - - if not found then - return 0; - end if; - - return 1; - end$$ language plpgsql; - - -- ******************************************************************* - -- FUNCTION: can_server_consume_virt_slot - -- Returns 1 if the server id is eligible to consume a virtual slot, - -- else returns 0. - -- Called by: insert_into_servergroup, delete_from_servergroup - -- ******************************************************************* - create or replace function can_server_consume_virt_slot(server_id_in in numeric, - group_type_in in varchar) - returns numeric - as $$ - declare - server_virt_slots cursor is - select vi.VIRTUAL_SYSTEM_ID - from - rhnVirtualInstance vi - where - -- server id is a virtual instance - vi.VIRTUAL_SYSTEM_ID = server_id_in - -- server id's host is virt entitled - and exists ( select 1 - from rhnServerEntitlementView sev - where vi.HOST_SYSTEM_ID = sev.server_id - and sev.label in ('virtualization_host', - 'virtualization_host_platform') ) - -- server id's host also has the ent we want - and exists ( select 1 - from rhnServerEntitlementView sev2 - where vi.HOST_SYSTEM_ID = sev2.server_id - and sev2.label = group_type_in ); - - begin - for server_virt_slot in server_virt_slots loop - return 1; - end loop; - return 0; - end$$ language plpgsql; - - - create or replace function insert_into_servergroup ( - server_id_in in numeric, - server_group_id_in in numeric - ) returns void - as $$ - declare - used_slots numeric; - max_slots numeric; - org_id numeric; - mgmt_available numeric; - mgmt_upgrade numeric; - mgmt_sgid numeric; - prov_available numeric; - prov_upgrade numeric; - prov_sgid numeric; - group_label varchar; - group_type numeric; - begin - -- first, group_type = null, because it's easy... - - -- this will rowlock the servergroup we're trying to change; - -- we probably need to lock the other one, but I think the chances - -- of it being a real issue are very small for now... - select sg.group_type, sg.org_id, sg.current_members, sg.max_members - into group_type, org_id, used_slots, max_slots - from rhnServerGroup sg - where sg.id = server_group_id_in - for update of sg; - - if group_type is null then - if used_slots >= max_slots then - perform rhn_exception.raise_exception('servergroup_max_members'); - end if; - - insert into rhnServerGroupMembers( - server_id, server_group_id - ) values ( - server_id_in, server_group_id_in - ); - update rhnServerGroup - set current_members = current_members + 1 - where id = server_group_id_in; - - perform rhn_cache.update_perms_for_server_group(server_group_id_in); - return; - end if; - - -- now for group_type != null - -- - select label - into group_label - from rhnServerGroupType sgt - where sgt.id = group_type; - - -- the naive easy path that gets hit most often and has to be quickest. - if group_label in ('sw_mgr_entitled', - 'enterprise_entitled', - 'monitoring_entitled', - 'provisioning_entitled', - 'virtualization_host', - 'virtualization_host_platform') then - if used_slots >= max_slots and - (rhn_server.can_server_consume_virt_slot(server_id_in, group_label) != 1) - then - perform rhn_exception.raise_exception('servergroup_max_members'); - end if; - - insert into rhnServerGroupMembers( - server_id, server_group_id - ) values ( - server_id_in, server_group_id_in - ); - - -- Only update current members if the system in consuming a - -- physical slot. - if rhn_server.can_server_consume_virt_slot(server_id_in, group_label) = 0 then - update rhnServerGroup - set current_members = current_members + 1 - where id = server_group_id_in; - end if; - - return; - end if; - end$$ language plpgsql; - - create or replace function insert_into_servergroup_maybe ( - server_id_in in numeric, - server_group_id_in in numeric - ) returns numeric as $$ - declare - retval numeric := 0; - servergroups cursor is - select s.id server_id, - sg.id server_group_id - from rhnServerGroup sg, - rhnServer s - where s.id = server_id_in - and sg.id = server_group_id_in - and s.org_id = sg.org_id - and not exists ( - select 1 - from rhnServerGroupMembers sgm - where sgm.server_id = s.id - and sgm.server_group_id = sg.id - ); - begin - for sgm in servergroups loop - perform rhn_server.insert_into_servergroup(sgm.server_id, sgm.server_group_id); - retval := retval + 1; - end loop; - return retval; - end$$ language plpgsql; - - create or replace function insert_set_into_servergroup ( - server_group_id_in in numeric, - user_id_in in numeric, - set_label_in in varchar - ) returns void - as $$ - declare - servers cursor is - select st.element id - from rhnSet st - where st.user_id = user_id_in - and st.label = set_label_in - and exists ( - select 1 - from rhnUserManagedServerGroups umsg - where umsg.server_group_id = server_group_id_in - and umsg.user_id = user_id_in - ) - and not exists ( - select 1 - from rhnServerGroupMembers sgm - where sgm.server_id = st.element - and sgm.server_group_id = server_group_id_in - ); - begin - for s in servers loop - perform rhn_server.insert_into_servergroup(s.id, server_group_id_in); - end loop; - end$$ language plpgsql; - - create or replace function delete_from_servergroup ( - server_id_in in numeric, - server_group_id_in in numeric - ) returns void - as $$ - declare - server_virt_groups cursor is - select 1 - from rhnServerEntitlementVirtual sev - where sev.server_id = server_id_in - and sev.server_group_id = server_group_id_in; - - oid numeric; - mgmt_sgid numeric; - label varchar; - group_type numeric; - begin - select sg.group_type, sg.org_id - into group_type, oid - from rhnServerGroupMembers sgm, - rhnServerGroup sg - where sg.id = server_group_id_in - and sg.id = sgm.server_group_id - and sgm.server_id = server_id_in - for update of sg; - - if not found then - perform rhn_exception.raise_exception('server_not_in_group'); - end if; - - -- do group_type is null first - if group_type is null then - delete from rhnServerGroupMembers - where server_group_id = server_group_id_in - and server_id = server_id_in; - update rhnServerGroup - set current_members = current_members - 1 - where id = server_group_id_in; - perform rhn_cache.update_perms_for_server_group(server_group_id_in); - return; - end if; - - select sgt.label - into label - from rhnServerGroupType sgt - where sgt.id = group_type; - - if label in ('sw_mgr_entitled', - 'enterprise_entitled', - 'provisioning_entitled', - 'monitoring_entitled', - 'virtualization_host', - 'virtualization_host_platform') then - - -- Only update current members if the system is consuming - -- a physical slot. - for server_virt_group in server_virt_groups loop - delete from rhnServerGroupMembers - where server_group_id = server_group_id_in - and server_id = server_id_in; - return; - end loop; - - delete from rhnServerGroupMembers - where server_group_id = server_group_id_in - and server_id = server_id_in; - - update rhnServerGroup - set current_members = current_members - 1 - where id = server_group_id_in; - - end if; - end$$ language plpgsql; - - create or replace function delete_set_from_servergroup ( - server_group_id_in in numeric, - user_id_in in numeric, - set_label_in in varchar - ) returns void - as $$ - declare - servergroups cursor is - select sgm.server_id, sgm.server_group_id - from rhnSet st, - rhnServerGroupMembers sgm - where sgm.server_group_id = server_group_id_in - and st.user_id = user_id_in - and st.label = set_label_in - and sgm.server_id = st.element - and exists ( - select 1 - from rhnUserManagedServerGroups usgp - where usgp.server_group_id = server_group_id_in - and usgp.user_id = user_id_in - ); - begin - for sgm in servergroups loop - perform rhn_server.delete_from_servergroup(sgm.server_id, server_group_id_in); - end loop; - end$$ language plpgsql; - - create or replace function clear_servergroup ( - server_group_id_in in numeric - ) returns void - as $$ - declare - servers cursor is - select sgm.server_id id - from rhnServerGroupMembers sgm - where sgm.server_group_id = server_group_id_in; - begin - for s in servers loop - perform rhn_server.delete_from_servergroup(s.id, server_group_id_in); - end loop; - end$$ language plpgsql; - - create or replace function delete_from_org_servergroups ( - server_id_in in numeric - ) returns void - as $$ - declare - servergroups cursor is - select sgm.server_group_id id - from rhnServerGroup sg, - rhnServerGroupMembers sgm - where sgm.server_id = server_id_in - and sgm.server_group_id = sg.id - and sg.group_type is null; - begin - for sg in servergroups loop - perform rhn_server.delete_from_servergroup(server_id_in, sg.id); - end loop; - end$$ language plpgsql; - - create or replace function get_ip_address ( - server_id_in in numeric - ) returns varchar as $$ - declare - interfaces cursor is - select ni.name as name, na4.address as address - from rhnServerNetInterface ni, - rhnServerNetAddress4 na4 - where server_id = server_id_in - and ni.id = na4.interface_id - and na4.address != '127.0.0.1'; - addresses cursor is - select ipaddr ip_addr - from rhnServerNetwork - where server_id = server_id_in - and ipaddr != '127.0.0.1'; - begin - for addr in addresses loop - return addr.ip_addr; - end loop; - for iface in interfaces loop - return iface.address; - end loop; - return NULL; - end$$ language plpgsql; - - create or replace function update_needed_cache( - server_id_in in numeric - ) returns void as $$ - begin - delete from rhnServerNeededCache - where server_id = server_id_in; - insert into rhnServerNeededCache - (server_id, errata_id, package_id) - (select distinct server_id, errata_id, package_id - from rhnServerNeededView - where server_id = server_id_in); - end$$ language plpgsql; - --- restore the original setting -update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_server')+1) ) where name = 'search_path';
commit 7dcc23a410119cd9d346a502e73b62536344a279 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:57:55 2011 +0200
Revert "get_hw_info_as_clob schema upgrade script"
This reverts commit 5b060db239ed2f43106b7e16378c2cabd44e89f6.
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.oracle deleted file mode 100644 index 819c4ca..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.oracle +++ /dev/null @@ -1,33 +0,0 @@ - -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, 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 - 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.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.postgresql deleted file mode 100644 index 26ef11e..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.postgresql +++ /dev/null @@ -1,36 +0,0 @@ --- oracle equivalent source sha1 6a538f3b05d412810baec118fbb7860348c60cd4 - -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, 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 - if ret is null then - ret := rec.m; - else - ret := ret || separator || rec.m; - end if; - end loop; - return ret; -end; -$$ language plpgsql;
commit 2a8bb2a4e4a106a69d611664b1e018dcfc338f53 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:57:48 2011 +0200
Revert "code cleanup - tag rhn-server-network-interfaces is not used anywhere"
This reverts commit 9cb74604757119a7702f1e50ea5a941cdd209698.
diff --git a/web/modules/sniglets/Sniglets/Servers.pm b/web/modules/sniglets/Sniglets/Servers.pm index 270b9fa..1407073 100644 --- a/web/modules/sniglets/Sniglets/Servers.pm +++ b/web/modules/sniglets/Sniglets/Servers.pm @@ -61,6 +61,10 @@ sub register_tags {
# has to run after server_details $pxt->register_tag('rhn-server-network-details' => &server_network_details, 2); + # slightly different than the rhn-server-network-details, this gives access to + # more detailed info about the network interfaces, as opposed to just hostname/ipaddy + $pxt->register_tag('rhn-server-network-interfaces' => &server_network_interfaces, 2); +
$pxt->register_tag('rhn-server-history-event-details' => &server_history_event_details);
commit aa56e8ff2a499b98ae13af805e15991414b59a39 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:57:36 2011 +0200
Revert "code cleanup - function server_network_interfaces is not used anymore"
This reverts commit 685cf4cc094cbfd72a77644010bb19393d012ae0.
diff --git a/web/modules/sniglets/Sniglets/Servers.pm b/web/modules/sniglets/Sniglets/Servers.pm index 155a69b..270b9fa 100644 --- a/web/modules/sniglets/Sniglets/Servers.pm +++ b/web/modules/sniglets/Sniglets/Servers.pm @@ -690,6 +690,64 @@ sub server_network_details { return $ret; }
+ +sub server_network_interfaces { + my $pxt = shift; + my %params = @_; + my $ret = ''; + my $current; + + my $server = $pxt->pnotes('server'); + + throw "No server." unless $server; + + my @net_interfaces = $server->get_net_interfaces; + + my %subst; + + if (not @net_interfaces) { + return ''; + } + + PXT::Debug->log(7, "got net_interfaces..."); + + my $block = $params{__block__}; + $block =~ m/<rhn-interface-data>(.*?)</rhn-interface-data>/gism; + my $device_data_block = $1; + + + my $unknown = '<span class="no-details">(unknown)</span>'; + my $html = ''; + my $counter = 1; + foreach my $interface (@net_interfaces) { + + my %subst; + if ($counter % 2) { + $subst{row_class} = 'list-row-odd'; + } + else { + $subst{row_class} = 'list-row-even'; + } + + + $subst{interface_name} = $interface->name; # NOT NULL + $subst{interface_ip_addr} = defined $interface->ip_addr ? PXT::Utils->escapeHTML($interface->ip_addr) : $unknown; + $subst{interface_netmask} = defined $interface->netmask ? PXT::Utils->escapeHTML($interface->netmask) : $unknown; + $subst{interface_broadcast} = defined $interface->broadcast ? PXT::Utils->escapeHTML($interface->broadcast) : $unknown; + $subst{interface_hw_addr} = defined $interface->hw_addr ? PXT::Utils->escapeHTML($interface->hw_addr) : $unknown; + $subst{interface_module} = defined $interface->module ? PXT::Utils->escapeHTML($interface->module) : $unknown; + + $html .= PXT::Utils->perform_substitutions($device_data_block, %subst); + $counter++; + } + + PXT::Debug->log(7, "html: $html"); + + $block =~ s{<rhn-interface-data>.*?</rhn-interface-data>}{$html}ism; + + return $block; +} + # must happen *after* server_hardware_profile... so use tags # correctly! sub server_device {
commit 7c2545a8a7956e20eef1d334ca80c7c3080f1e73 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:57:27 2011 +0200
Revert "code cleanup - function get_net_interfaces is not used anymore"
This reverts commit 38b10e49fbc8d858997219ddaf5514ce81367783.
diff --git a/web/modules/rhn/RHN/DB/Server.pm b/web/modules/rhn/RHN/DB/Server.pm index 1eef958..36e5283 100644 --- a/web/modules/rhn/RHN/DB/Server.pm +++ b/web/modules/rhn/RHN/DB/Server.pm @@ -1383,6 +1383,14 @@ sub get_net_infos { return @net_infos; }
+# returns an array of all the net info objects related to a server +sub get_net_interfaces { + my $self = shift; + + my @net_interfaces = RHN::DB::Server::NetInterface->lookup_net_interface_by_server($self->id); + return @net_interfaces; +} +
# # build some accessors
commit 2ea40592bf2e19a84dfc784eab195b62995ce843 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:57:12 2011 +0200
Revert "code cleanup - package RHN::DB::Server::NetInterface is not used anymore"
This reverts commit f7ae084b3cb319fce99e38e776f68529f7439ebe.
diff --git a/web/modules/rhn/RHN/DB/Server.pm b/web/modules/rhn/RHN/DB/Server.pm index 22269cb..1eef958 100644 --- a/web/modules/rhn/RHN/DB/Server.pm +++ b/web/modules/rhn/RHN/DB/Server.pm @@ -25,6 +25,7 @@ use RHN::DB::Server::CdDevice; use RHN::DB::Server::HwDevice; use RHN::DB::Server::StorageDevice; use RHN::DB::Server::NetInfo; +use RHN::DB::Server::NetInterface; use RHN::DB::TableClass;
use RHN::DataSource::Errata; diff --git a/web/modules/rhn/RHN/DB/Server/NetInterface.pm b/web/modules/rhn/RHN/DB/Server/NetInterface.pm new file mode 100644 index 0000000..238b75c --- /dev/null +++ b/web/modules/rhn/RHN/DB/Server/NetInterface.pm @@ -0,0 +1,86 @@ +# +# Copyright (c) 2008--2010 Red Hat, Inc. +# +# This software is licensed to you under the GNU General Public License, +# version 2 (GPLv2). There is NO WARRANTY for this software, express or +# implied, including the implied warranties of MERCHANTABILITY or FITNESS +# FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 +# along with this software; if not, see +# http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. +# +# Red Hat trademarks are not licensed under GPLv2. No permission is +# granted to use or replicate Red Hat trademarks that are incorporated +# in this software or its documentation. +# + +use strict; + +package RHN::DB::Server::NetInterface; + +use RHN::DB; +use Carp; +use RHN::DB::TableClass; + +# Corresponds to an entry in the rhnServerNetInterface table... + +my @net_interface_fields = qw/SERVER_ID NAME IP_ADDR NETMASK BROADCAST HW_ADDR MODULE/; + +my $n = new RHN::DB::TableClass("rhnServerNetInterface", "N", "", @net_interface_fields); + +# create a blank net info object +sub _blank_net_interface { + my $class = shift; + + my $self = bless {}, $class; + + return $self; +} + +# returns an array of net info objects given a server id +sub lookup_net_interface_by_server { + my $class = shift; + my $server_id = shift; + + my $dbh = RHN::DB->connect; + + my $query; + my $sth; + + $query = $n->select_query("N.SERVER_ID = ?"); + $sth = $dbh->prepare($query); + $sth->execute($server_id); + + my @ret; + my $count = 0; + + while (my @columns = $sth->fetchrow) { + if ($columns[0]) { + $ret[$count] = $class->_blank_net_interface; + $ret[$count]->{"__".$_."__"} = shift @columns foreach $n->method_names; + } + $count++; + } + + $sth->finish; + return @ret; +} + +# build some read-only accessors +foreach my $field ($n->method_names) { + my $sub = q { + sub RHN::DB::Server::NetInterface::[[field]] { + my $self = shift; + return $self->{__[[field]]__}; + } + }; + + $sub =~ s/[[field]]/$field/g; + eval $sub; + + if ($@) { + die $@; + } +} + + +return 1;
commit e5443ef04c88d56a385a527eaa3f54cf1f03ddee Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:56:34 2011 +0200
Revert "insert id from sequence using trigger"
This reverts commit f2f453196dd56c106eb451d8a5c431c93a846ba4.
diff --git a/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql b/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql index f31f4c4..da0bb65 100644 --- a/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql +++ b/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql @@ -19,9 +19,6 @@ rhn_srv_net_iface_mod_trig before insert or update on rhnServerNetInterface for each row begin - if :new.id is null then - select rhn_srv_net_iface_id_seq.nextval into :new.id from dual; - end if; :new.modified := sysdate; end; / diff --git a/schema/spacewalk/postgres/triggers/rhnServerNetInterface.sql b/schema/spacewalk/postgres/triggers/rhnServerNetInterface.sql index 192308f..349810c 100644 --- a/schema/spacewalk/postgres/triggers/rhnServerNetInterface.sql +++ b/schema/spacewalk/postgres/triggers/rhnServerNetInterface.sql @@ -1,10 +1,8 @@ --- oracle equivalent source sha1 1c9678fc0e82b0d0e1f659358120c971bc9bda20 +-- oracle equivalent source sha1 d37db54a176f593bd9727921ed20d00d6cd95f4b +-- retrieved from ./1241102873/cdc6d42049bf86fbc9f1d3a5c54275eeacbd641d/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql create or replace function rhn_srv_net_iface_mod_trig_fun() returns trigger as $$ begin - if new.id is null then - new.id := nextval('rhn_srv_net_iface_id_seq'); - end if; new.modified := current_timestamp; return new; end; diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.oracle deleted file mode 100644 index 1387bff..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.oracle +++ /dev/null @@ -1,12 +0,0 @@ -create or replace trigger -rhn_srv_net_iface_mod_trig -before insert or update on rhnServerNetInterface -for each row -begin - if :new.id is null then - select rhn_srv_net_iface_id_seq.nextval into :new.id from dual; - end if; - :new.modified := sysdate; -end; -/ -show errors diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.postgresql deleted file mode 100644 index 588ece0..0000000 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.postgresql +++ /dev/null @@ -1,11 +0,0 @@ --- oracle equivalent source sha1 a3c3cb4940f2e96320a833521f352b83ddddaed1 -create or replace function rhn_srv_net_iface_mod_trig_fun() returns trigger as -$$ -begin - if new.id is null then - new.id := nextval('rhn_srv_net_iface_id_seq'); - end if; - new.modified := current_timestamp; - return new; -end; -$$ language plpgsql;
commit d8aefa93800f54b18b990bb40076d05a8ead3505 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:56:18 2011 +0200
Revert "add oracle equivalent"
This reverts commit 480c68154fce2eff3389b3e132fd28f96bc23859.
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql index 17fbb81..d103a65 100644 --- a/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql @@ -1,4 +1,3 @@ --- oracle equivalent source sha1 6e90408fc4c3d072bf407d0eb18962e46cb6a860 -- -- Copyright (c) 2008--2010 Red Hat, Inc. --
commit 26474e88d3cdc3ee3d833fcc78426bf562a7bac7 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:56:07 2011 +0200
Revert "__load_from_db: load devices using its method save()"
This reverts commit 70694d0bccdccfc3f00ba4cfc0b3dd6b52e9cc3d.
diff --git a/backend/server/rhnServer/server_hardware.py b/backend/server/rhnServer/server_hardware.py index 2b44250..dd044e1 100644 --- a/backend/server/rhnServer/server_hardware.py +++ b/backend/server/rhnServer/server_hardware.py @@ -703,13 +703,29 @@ class Hardware: self.__changed = 0 return 0
- def __load_from_db(self, DevClass, sysid): + def __load_from_db(self, db, DevClass, sysid): """ Load a certain hardware class from the database """ if not self.__hardware.has_key(DevClass): self.__hardware[DevClass] = [] - dev = DevClass() - dev.reload(sysid) - self.__hardware[DevClass].append(dev) + + h = rhnSQL.prepare("select * from %s where server_id = :sysid" % db) + h.execute(sysid = sysid) + rows = h.fetchall_dict() or [] + + for device in rows: + dev_id = device['id'] + + # get rid of the keys we do not support + for k in ["server_id", "created", "modified", "id"]: + if device.has_key(k): + del device[k] + dev = DevClass(device) + + # we know better + dev.id = dev_id + dev.status = 0 + self.__hardware[DevClass].append(dev) + return 0
def reload_hardware_byid(self, sysid): """ load all hardware devices for a server """ @@ -718,13 +734,18 @@ class Hardware: return -1 self.__hardware = {} # discard what was already loaded # load from all hardware databases - self.__load_from_db(HardwareDevice, sysid) - self.__load_from_db(CPUDevice, sysid) - self.__load_from_db(DMIInformation, sysid) - self.__load_from_db(NetworkInformation, sysid) - self.__load_from_db(MemoryInformation, sysid) - self.__load_from_db(InstallInformation, sysid) - self.__load_from_db(NetIfaceInformation, sysid) + self.__load_from_db("rhnDevice", HardwareDevice, sysid) + self.__load_from_db("rhnCPU", CPUDevice, sysid) + self.__load_from_db("rhnServerDMI", DMIInformation, sysid) + self.__load_from_db("rhnServerNetwork", NetworkInformation, sysid) + self.__load_from_db("rhnRAM", MemoryInformation, sysid) + self.__load_from_db("rhnServerInstallInfo", InstallInformation, sysid) + + net_iface_info = NetIfaceInformation() + net_iface_info.reload(sysid) + + self.__hardware[NetIfaceInformation] = [] + self.__hardware[NetIfaceInformation].append(net_iface_info)
# now set the flag self.__changed = 0
spacewalk-commits@lists.fedorahosted.org