3:37 a.m.
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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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(a)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;