backend/server/rhnServer/server_hardware.py | 43 client/tools/rhn-virtualization/virtualization/domain_directory.py | 2 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 29 files changed, 1917 insertions(+), 212 deletions(-)
New commits: commit 29544df9fb7d2c79db36b88548cee5f36f928d88 Author: Milan Zazrivec mzazrivec@redhat.com Date: Thu Oct 27 10:33:22 2011 +0200
742811 - domain_directory: R/O access to libvirtd is sufficient
diff --git a/client/tools/rhn-virtualization/virtualization/domain_directory.py b/client/tools/rhn-virtualization/virtualization/domain_directory.py index ba069f4..6e438cc 100644 --- a/client/tools/rhn-virtualization/virtualization/domain_directory.py +++ b/client/tools/rhn-virtualization/virtualization/domain_directory.py @@ -67,7 +67,7 @@ class DomainDirectory:
def __init__(self): self.__path = CONFIG_DIR - self.conn = libvirt.open(None) + self.conn = libvirt.openReadOnly(None) if not self.conn: raise VirtualizationException, \ "Failed to open connection to hypervisor."
commit 70694d0bccdccfc3f00ba4cfc0b3dd6b52e9cc3d Author: Miroslav Suchý msuchy@redhat.com Date: Tue Oct 25 16:33:51 2011 +0200
__load_from_db: load devices using its method save()
this will put _auto_null and other stuff in game
diff --git a/backend/server/rhnServer/server_hardware.py b/backend/server/rhnServer/server_hardware.py index dd044e1..2b44250 100644 --- a/backend/server/rhnServer/server_hardware.py +++ b/backend/server/rhnServer/server_hardware.py @@ -703,29 +703,13 @@ class Hardware: self.__changed = 0 return 0
- def __load_from_db(self, db, DevClass, sysid): + def __load_from_db(self, DevClass, sysid): """ Load a certain hardware class from the database """ if not self.__hardware.has_key(DevClass): self.__hardware[DevClass] = [] - - 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 + dev = DevClass() + dev.reload(sysid) + self.__hardware[DevClass].append(dev)
def reload_hardware_byid(self, sysid): """ load all hardware devices for a server """ @@ -734,18 +718,13 @@ class Hardware: return -1 self.__hardware = {} # discard what was already loaded # load from all hardware databases - 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) + 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)
# now set the flag self.__changed = 0
commit 480c68154fce2eff3389b3e132fd28f96bc23859 Author: Miroslav Suchý msuchy@redhat.com Date: Tue Oct 25 15:42:12 2011 +0200
add oracle equivalent
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 d103a65..17fbb81 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,3 +1,4 @@ +-- oracle equivalent source sha1 6e90408fc4c3d072bf407d0eb18962e46cb6a860 -- -- Copyright (c) 2008--2010 Red Hat, Inc. --
commit f2f453196dd56c106eb451d8a5c431c93a846ba4 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 24 15:50:36 2011 +0200
insert id from sequence using trigger
diff --git a/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql b/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql index da0bb65..f31f4c4 100644 --- a/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql +++ b/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql @@ -19,6 +19,9 @@ 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 349810c..192308f 100644 --- a/schema/spacewalk/postgres/triggers/rhnServerNetInterface.sql +++ b/schema/spacewalk/postgres/triggers/rhnServerNetInterface.sql @@ -1,8 +1,10 @@ --- oracle equivalent source sha1 d37db54a176f593bd9727921ed20d00d6cd95f4b --- retrieved from ./1241102873/cdc6d42049bf86fbc9f1d3a5c54275eeacbd641d/schema/spacewalk/oracle/triggers/rhnServerNetInterface.sql +-- oracle equivalent source sha1 1c9678fc0e82b0d0e1f659358120c971bc9bda20 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 new file mode 100644 index 0000000..1387bff --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.oracle @@ -0,0 +1,12 @@ +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 new file mode 100644 index 0000000..588ece0 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/123-trigger-rhnServerNetInterface.sql.postgresql @@ -0,0 +1,11 @@ +-- 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 f7ae084b3cb319fce99e38e776f68529f7439ebe Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 24 11:07:54 2011 +0200
code cleanup - package RHN::DB::Server::NetInterface is not used anymore
diff --git a/web/modules/rhn/RHN/DB/Server.pm b/web/modules/rhn/RHN/DB/Server.pm index 1eef958..22269cb 100644 --- a/web/modules/rhn/RHN/DB/Server.pm +++ b/web/modules/rhn/RHN/DB/Server.pm @@ -25,7 +25,6 @@ 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 deleted file mode 100644 index 238b75c..0000000 --- a/web/modules/rhn/RHN/DB/Server/NetInterface.pm +++ /dev/null @@ -1,86 +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. -# - -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 38b10e49fbc8d858997219ddaf5514ce81367783 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 24 11:06:33 2011 +0200
code cleanup - function get_net_interfaces is not used anymore
diff --git a/web/modules/rhn/RHN/DB/Server.pm b/web/modules/rhn/RHN/DB/Server.pm index 36e5283..1eef958 100644 --- a/web/modules/rhn/RHN/DB/Server.pm +++ b/web/modules/rhn/RHN/DB/Server.pm @@ -1383,14 +1383,6 @@ 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 685cf4cc094cbfd72a77644010bb19393d012ae0 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 24 11:05:47 2011 +0200
code cleanup - function server_network_interfaces is not used anymore
diff --git a/web/modules/sniglets/Sniglets/Servers.pm b/web/modules/sniglets/Sniglets/Servers.pm index 270b9fa..155a69b 100644 --- a/web/modules/sniglets/Sniglets/Servers.pm +++ b/web/modules/sniglets/Sniglets/Servers.pm @@ -690,64 +690,6 @@ 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 9cb74604757119a7702f1e50ea5a941cdd209698 Author: Miroslav Suchý msuchy@redhat.com Date: Mon Oct 24 11:04:27 2011 +0200
code cleanup - tag rhn-server-network-interfaces is not used anywhere
diff --git a/web/modules/sniglets/Sniglets/Servers.pm b/web/modules/sniglets/Sniglets/Servers.pm index 1407073..270b9fa 100644 --- a/web/modules/sniglets/Sniglets/Servers.pm +++ b/web/modules/sniglets/Sniglets/Servers.pm @@ -61,10 +61,6 @@ 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 5b060db239ed2f43106b7e16378c2cabd44e89f6 Author: Milan Zazrivec mzazrivec@redhat.com Date: Fri Oct 21 10:03:49 2011 +0200
get_hw_info_as_clob schema upgrade script
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 new file mode 100644 index 0000000..819c4ca --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.oracle @@ -0,0 +1,33 @@ + +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 new file mode 100644 index 0000000..26ef11e --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/122-get_hw_info_as_clob.sql.postgresql @@ -0,0 +1,36 @@ +-- 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 aa6a12287e103d424ebf882cf0dc003ea9c93e91 Author: Milan Zazrivec mzazrivec@redhat.com Date: Fri Oct 21 10:03:03 2011 +0200
upgrade script for rhn_server.pkb
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 new file mode 100644 index 0000000..87600df --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.oracle @@ -0,0 +1,750 @@ +-- +-- 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 new file mode 100644 index 0000000..d103a65 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/121-rhn_server-get_ip_address.sql.postgresql @@ -0,0 +1,802 @@ +-- +-- 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 cee165a5cdd26112f2cc2cbb9774f894c10675b2 Author: Miroslav Suchý msuchy@redhat.com Date: Thu Oct 20 19:19:02 2011 +0200
schema upgrade
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql index 7042fb2..b451aa3 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_net_addr4_iid_addr_idx +CREATE INDEX rhn_srv_net_addr4_iid_addr_idx ON rhnServerNetAddress4 (interface_id, address) - TABLESPACE [[8m_tbs]]; + TABLESPACE [[64k_tbs]];
ALTER TABLE rhnServerNetAddress4 - ADD CONSTRAINT rhn_net_add4_iid_addr_uq UNIQUE (interface_id, address); + ADD CONSTRAINT rhn_srv_net_addr4_iid_addr_uq UNIQUE (interface_id, address);
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql index ee04fe5..79b10e4 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_net_addr6_iid_sc_addr_idx +CREATE INDEX rhn_srv_net_ad6_iid_sc_ad_idx ON rhnServerNetAddress6 (interface_id, scope, address) - TABLESPACE [[8m_tbs]]; + TABLESPACE [[64_tbs]];
ALTER TABLE rhnServerNetAddress6 - ADD CONSTRAINT rhn_net_addr6_iid_sc_addr_uq UNIQUE (interface_id, scope, address); + 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/105-alter_rhnServerNetInterface.sql.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.oracle new file mode 100644 index 0000000..1f0ab2a --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.oracle @@ -0,0 +1,9 @@ +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 new file mode 100644 index 0000000..c2b448d --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/105-alter_rhnServerNetInterface.sql.postgresql @@ -0,0 +1,11 @@ +-- 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 new file mode 100644 index 0000000..7922fdf --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.oracle @@ -0,0 +1,39 @@ +-- +-- 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 new file mode 100644 index 0000000..7b8a6b7 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/110-add_rhnServerNetAddress4.sql.postgresql @@ -0,0 +1,18 @@ +-- 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 new file mode 100644 index 0000000..bda4fb8 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/111-add_rhnServerNetAddress4_data.sql @@ -0,0 +1,6 @@ +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 new file mode 100644 index 0000000..64e42e1 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/115-drop_columns_rhnServerNetInterface.sql @@ -0,0 +1,3 @@ +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 new file mode 100644 index 0000000..42c78b2 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.oracle @@ -0,0 +1,39 @@ +-- +-- 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 new file mode 100644 index 0000000..844c601 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.5-to-spacewalk-schema-1.6/120-add_rhnServerNetAddress6.sql.postgresql @@ -0,0 +1,17 @@ +-- 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 08ea44500d91d22877f0f361735df518ae601bd7 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 c8d1e52a302aaea688b049804bec6f441577d6e1 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 7be1ad0da7a79a3a445437770540cfe0824850f5 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 deb1ff2107ca7db37389e85bfdb39a79bad8e2b9 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 72ab8d4d2e7516036ace9604eac51da399d6d1d1 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 6db6d3b..edf90ce 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql @@ -30,9 +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 dce721d..7dbde6c 100644 --- a/schema/spacewalk/common/tables/rhnServerNetAddress6.sql +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -30,9 +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 4c6f241a4cfe99cac08d18a63b969e815d10cbba 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
commit 780ba79c8d5edb50c312b522c6b8972db0d812a3 Author: Miroslav Suchý msuchy@redhat.com Date: Thu Oct 20 12:55:30 2011 +0200
schema change
diff --git a/schema/spacewalk/common/tables/rhnServerNetAddress4.sql b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql new file mode 100644 index 0000000..6db6d3b --- /dev/null +++ b/schema/spacewalk/common/tables/rhnServerNetAddress4.sql @@ -0,0 +1,38 @@ +-- +-- 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 new file mode 100644 index 0000000..dce721d --- /dev/null +++ b/schema/spacewalk/common/tables/rhnServerNetAddress6.sql @@ -0,0 +1,38 @@ +-- +-- 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 f115885..6d73c23 100644 --- a/schema/spacewalk/common/tables/rhnServerNetInterface.sql +++ b/schema/spacewalk/common/tables/rhnServerNetInterface.sql @@ -1,5 +1,5 @@ -- --- Copyright (c) 2008 Red Hat, Inc. +-- Copyright (c) 2008 -- 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 @@ -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,3 +40,4 @@ 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;
spacewalk-commits@lists.fedorahosted.org