April 2009 - spacewalk-commits - Fedora Mailing-Lists

by Mike McCune

java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml | 4 java/code/src/com/redhat/rhn/manager/kickstart/cobbler/CobblerSystemCreateCommand.java | 14 ++- java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java | 45 +++------- 3 files changed, 34 insertions(+), 29 deletions(-) New commits: commit f6e88462d6cf522c3bf8689f854d2dcb86fa6014 Author: Mike McCune <mmccune(a)gmail.com> Date: Tue Apr 28 10:13:57 2009 -0700 497872 - skip 'fake' interfaces when looking up system records. Since system records can contain non-real interfaces such as: xenbr0: fe:ff:ff:ff:ff:ff vif0.1: fe:ff:ff:ff:ff:ff sit0 : 00:00:00:00:00:00 we want to skip these when looking up system records in cobbler since they may exist in many records. diff --git a/java/code/src/com/redhat/rhn/manager/kickstart/cobbler/CobblerSystemCreateCommand.java b/java/code/src/com/redhat/rhn/manager/kickstart/cobbler/CobblerSystemCreateCommand.java index 4b2332d..a2a7214 100644 --- a/java/code/src/com/redhat/rhn/manager/kickstart/cobbler/CobblerSystemCreateCommand.java +++ b/java/code/src/com/redhat/rhn/manager/kickstart/cobbler/CobblerSystemCreateCommand.java @@ -123,7 +123,18 @@ public class CobblerSystemCreateCommand extends CobblerCommand { // Build up list of mac addrs List macs = new LinkedList(); for (NetworkInterface n : server.getNetworkInterfaces()) { - macs.add(n.getHwaddr().toLowerCase()); + // Skip localhost and non real interfaces + if (n.getHwaddr() == null || + n.getHwaddr().equals("00:00:00:00:00:00") || + n.getHwaddr().equals("fe:ff:ff:ff:ff:ff") || + n.getIpaddr() == null || + n.getIpaddr().equals("127.0.0.1")) { + log.debug("Skipping. not a real interface"); + } + else { + macs.add(n.getHwaddr().toLowerCase()); + } + } List <String> args = new ArrayList(); @@ -143,6 +154,7 @@ public class CobblerSystemCreateCommand extends CobblerCommand { String mac = (String) iface.get("mac_address"); log.debug("getSystemMapByMac.ROW: " + row + " looking for: " + macs); + if (mac != null && macs.contains(mac.toLowerCase())) { log.debug("getSystemMapByMac.found match."); diff --git a/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java b/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java index 0160372..fba99e9 100644 --- a/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java +++ b/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java @@ -14,13 +14,6 @@ */ package com.redhat.rhn.taskomatic.task.errata; -import java.util.HashMap; -import java.util.Iterator; -import java.util.List; -import java.util.Map; - -import org.apache.log4j.Logger; - import com.redhat.rhn.common.db.datasource.ModeFactory; import com.redhat.rhn.common.db.datasource.SelectMode; import com.redhat.rhn.common.db.datasource.WriteMode; @@ -40,6 +33,13 @@ import com.redhat.rhn.taskomatic.task.TaskConstants; import com.redhat.rhn.taskomatic.task.threaded.QueueWorker; import com.redhat.rhn.taskomatic.task.threaded.TaskQueue; +import org.apache.log4j.Logger; + +import java.util.HashMap; +import java.util.Iterator; +import java.util.List; +import java.util.Map; + /** * Processes an errata for a single org * ErrataQueueWorker commit ab82cc97df777d7f4ab58a41ca8791224ea407f0 Author: Mike McCune <mmccune(a)gmail.com> Date: Mon Apr 27 14:21:36 2009 -0700 485849 - merging RELEASE-5.1 bug into spacewalk diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml index d921c76..698b727 100644 --- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml +++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Task_queries.xml @@ -154,6 +154,10 @@ SELECT DISTINCT WHERE CE.errata_id = :errata_id AND SC.channel_id = CE.channel_id AND SC.server_id = S.id) + AND EXISTS + (SELECT 1 FROM rhnServerFeaturesView SFV WHERE + SFV.server_id = S.id AND SFV.label = 'ftr_auto_errata_updates') + AND s.auto_update IS NOT NULL AND UPPER(s.auto_update) = 'Y' ORDER BY S.org_id </query> </mode> diff --git a/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java b/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java index 0422c41..0160372 100644 --- a/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java +++ b/java/code/src/com/redhat/rhn/taskomatic/task/errata/ErrataQueueWorker.java @@ -179,31 +179,20 @@ class ErrataQueueWorker implements QueueWorker { org = OrgFactory.lookupById(convertedOrgId); } Long convertedServerId = new Long(serverId.longValue()); - Server server = ServerFactory.lookupById(convertedServerId); - if (server == null) { - logger.error("Server " + serverId + " not found. " + - "Aboring auto-errata processing"); - return; - } // Only schedule an Auto Update if the server supports the // feature. We originally calculated this in the driving // query but it wasn't performant. - if (SystemManager.serverHasFeature(convertedServerId, - "ftr_auto_errata_updates") && org.isPayingCustomer() && - server.getAutoUpdate() != null && - server.getAutoUpdate().equalsIgnoreCase("y")) { - if (logger.isDebugEnabled()) { - logger.debug("Scheduling auto update for Errata: " + - errata.getId() + ", Server: " + convertedServerId + - ", Org: " + convertedOrgId); - } - ErrataAction errataAction = ActionManager. - createErrataAction(org, errata); - ActionManager.addServerToAction(convertedServerId, errataAction); - ActionManager.storeAction(errataAction); - HibernateFactory.commitTransaction(); - HibernateFactory.closeSession(); + if (logger.isDebugEnabled()) { + logger.debug("Scheduling auto update for Errata: " + + errata.getId() + ", Server: " + convertedServerId + + ", Org: " + convertedOrgId); } + ErrataAction errataAction = ActionManager. + createErrataAction(org, errata); + ActionManager.addServerToAction(convertedServerId, errataAction); + ActionManager.storeAction(errataAction); + HibernateFactory.commitTransaction(); + HibernateFactory.closeSession(); } HibernateFactory.commitTransaction(); HibernateFactory.closeSession();

14 years, 12 months

2
1
0 / 0

3 commits - rel-eng/packages spec-tree/spacewalk-repo

by Jan Pazdziora

rel-eng/packages/spacewalk-repo | 2 +- spec-tree/spacewalk-repo/Makefile | 3 +++ spec-tree/spacewalk-repo/spacewalk-repo.spec | 7 +++++-- 3 files changed, 9 insertions(+), 3 deletions(-) New commits: commit 0e3eeece4f8ef62e91b61009ddba0a668c91e381 Author: Jan Pazdziora <jpazdziora(a)redhat.com> Date: Thu Apr 30 09:35:38 2009 +0200 Automatic commit of package [spacewalk-repo] release [0.6-1]. diff --git a/rel-eng/packages/spacewalk-repo b/rel-eng/packages/spacewalk-repo index 39b451b..82c569f 100644 --- a/rel-eng/packages/spacewalk-repo +++ b/rel-eng/packages/spacewalk-repo @@ -1 +1 @@ -0.5.2-1 spec-tree/spacewalk-repo/ +0.6-1 spec-tree/spacewalk-repo/ diff --git a/spec-tree/spacewalk-repo/spacewalk-repo.spec b/spec-tree/spacewalk-repo/spacewalk-repo.spec index 7d0a713..223347c 100644 --- a/spec-tree/spacewalk-repo/spacewalk-repo.spec +++ b/spec-tree/spacewalk-repo/spacewalk-repo.spec @@ -1,6 +1,6 @@ Summary: Spacewalk packages yum repository configuration. Name: spacewalk-repo -Version: 0.5.2 +Version: 0.6 Release: 1%{?dist} License: GPL Group: Development @@ -47,6 +47,9 @@ rm -rf $RPM_BUILD_ROOT %config %{_sysconfdir}/yum.repos.d/spacewalk.repo %changelog +* Thu Apr 30 2009 Jan Pazdziora 0.6-1 +- bump version to 0.6 + * Tue Mar 31 2009 jesus m. rodriguez <jesusr(a)redhat.com> 0.5.2-1 - rebuilding for 0.5 commit 6c95fe3d68fe5a26d6dce726c9a850c70705aa76 Author: Jan Pazdziora <jpazdziora(a)redhat.com> Date: Thu Apr 30 09:31:32 2009 +0200 spacewalk-repo: fix the location of the package. diff --git a/spec-tree/spacewalk-repo/spacewalk-repo.spec b/spec-tree/spacewalk-repo/spacewalk-repo.spec index fd91fac..7d0a713 100644 --- a/spec-tree/spacewalk-repo/spacewalk-repo.spec +++ b/spec-tree/spacewalk-repo/spacewalk-repo.spec @@ -7,7 +7,7 @@ Group: Development # This src.rpm is cannonical upstream # You can obtain it using this set of commands # git clone git://git.fedorahosted.org/git/spacewalk.git/ -# cd web +# cd spec-tree/spacewalk-repo # make test-srpm URL: https://fedorahosted.org/spacewalk BuildRoot: %{_tmppath}/%{name}-%{version}-%{release}-buildroot commit b135b8203a8b9a463b9a5b070fb6fef5fa08baa0 Author: Jan Pazdziora <jpazdziora(a)redhat.com> Date: Thu Apr 30 09:31:10 2009 +0200 spacewalk-repo: the package is in fact NO_TAR_GZ. diff --git a/spec-tree/spacewalk-repo/Makefile b/spec-tree/spacewalk-repo/Makefile index f63ed35..73bbccd 100644 --- a/spec-tree/spacewalk-repo/Makefile +++ b/spec-tree/spacewalk-repo/Makefile @@ -1,2 +1,5 @@ +NO_TAR_GZ := 1 + include ../../rel-eng/Makefile +

14 years, 12 months

2
1
0 / 0

Branch 'VADER' - java/code

by Tomas Lestach

java/code/src/com/redhat/rhn/frontend/servlets/PxtCookieManager.java | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) New commits: commit f7461d4d4ddc5bab68a41ccbad11078b02fc9350 Author: Tomas Lestach <tlestach(a)redhat.com> Date: Wed Apr 29 17:37:23 2009 +0200 454876 - not setting cookie domain - domain causes troubles, when accessing the server within the local network (without FQDN) diff --git a/java/code/src/com/redhat/rhn/frontend/servlets/PxtCookieManager.java b/java/code/src/com/redhat/rhn/frontend/servlets/PxtCookieManager.java index 3959fb8..591b3ba 100644 --- a/java/code/src/com/redhat/rhn/frontend/servlets/PxtCookieManager.java +++ b/java/code/src/com/redhat/rhn/frontend/servlets/PxtCookieManager.java @@ -64,7 +64,10 @@ public class PxtCookieManager { SessionManager.generateSessionKey(pxtSessionId.toString()); Cookie pxtCookie = new Cookie(cookieName, cookieValue); - pxtCookie.setDomain(request.getServerName()); + // BZ #454876 + // when not using setDomain, default "Host" will be set for the cookie + // there's no need to use domain and besides that it causes trouble, when accessing the server within the local network (without FQDN) + // pxtCookie.setDomain(request.getServerName()); pxtCookie.setMaxAge(timeout); pxtCookie.setPath(DEFAULT_PATH); pxtCookie.setSecure(Config.get().isSSLAvailable());

14 years, 12 months

2
1
0 / 0

Branch 'pgsql' - schema/spacewalk

by Tom Lane

schema/spacewalk/postgres/triggers/rhnChannel.sql | 42 ++++------ schema/spacewalk/postgres/triggers/rhnChannelComps.sql | 10 ++ schema/spacewalk/postgres/triggers/rhnConfigChannel.sql | 17 +--- schema/spacewalk/postgres/triggers/rhnConfigRevision.sql | 17 +--- schema/spacewalk/postgres/triggers/rhnErrataPackage.sql | 4 schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql | 17 +++- schema/spacewalk/postgres/triggers/rhnKickstartSession.sql | 18 +++- schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql | 18 ++-- schema/spacewalk/postgres/triggers/rhnPackage.sql | 20 +++- schema/spacewalk/postgres/triggers/rhnServerAction.sql | 2 schema/spacewalk/postgres/triggers/rhnServerGroup.sql | 21 +---- schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql | 1 schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql | 4 schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql | 2 schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql | 2 schema/spacewalk/postgres/triggers/rhn_contact_methods.sql | 14 --- 16 files changed, 99 insertions(+), 110 deletions(-) New commits: commit 9984c41fb98d15becf3c29432c19cd7a266dece4 Author: Tom Lane <tgl(a)redhat.com> Date: Thu Apr 30 19:09:07 2009 -0400 Fix some bogosities in newly added triggers (mostly NEW/OLD confusion or accessing OLD where it's not guaranteed to exist). diff --git a/schema/spacewalk/postgres/triggers/rhnChannel.sql b/schema/spacewalk/postgres/triggers/rhnChannel.sql index b46861b..1b72993 100644 --- a/schema/spacewalk/postgres/triggers/rhnChannel.sql +++ b/schema/spacewalk/postgres/triggers/rhnChannel.sql @@ -19,19 +19,19 @@ create or replace function rhn_channel_mod_trig_fun() returns trigger as $$ begin - new.modified := current_timestamp; + new.last_modified := current_timestamp; -- this is a really bad way of saying "if all we''re -- changing is the date" if tg_op='UPDATE' then - if (old.id != new.id) or - (old.parent_channel != new.parent_channel) or - (old.org_id != new.org_id) or - (old.channel_arch_id != new.channel_arch_id) or - (old.label != new.label) or - (old.basedir != new.basedir) or - (old.name != new.name) or - (old.summary != new.summary) or - (old.description != new.description) then + if (old.id is distinct from new.id) or + (old.parent_channel is distinct from new.parent_channel) or + (old.org_id is distinct from new.org_id) or + (old.channel_arch_id is distinct from new.channel_arch_id) or + (old.label is distinct from new.label) or + (old.basedir is distinct from new.basedir) or + (old.name is distinct from new.name) or + (old.summary is distinct from new.summary) or + (old.description is distinct from new.description) then new.modified := current_timestamp; end if; end if; @@ -52,27 +52,22 @@ execute procedure rhn_channel_mod_trig_fun(); create or replace function rhn_channel_del_trig_fun() returns trigger as $$ declare - snapshots cursor for - select snapshot_id - from rhnSnapshotChannel - where channel_id = old.id; snapshot_curs_id numeric; begin - open snapshots; + for snapshot_curs_id in + select snapshot_id + from rhnSnapshotChannel + where channel_id = old.id loop - fetch snapshots into snapshot_curs_id; - EXIT WHEN not found; update rhnSnapshot set invalid = lookup_snapshot_invalid_reason('channel_removed') where id = snapshot_curs_id; delete from rhnSnapshotChannel where snapshot_id = snapshot_curs_id and channel_id = old.id; - - end loop; - return new; + return old; end; $$ language plpgsql; @@ -88,14 +83,11 @@ execute procedure rhn_channel_del_trig_fun(); create or replace function rhn_channel_access_trig_fun() returns trigger as $$ begin - if old.channel_access = 'protected' and - new.channel_access != 'protected' + if old.channel_access = 'protected' and + new.channel_access is distinct from 'protected' then delete from rhnChannelTrust where channel_id = old.id; end if; - - return new; - end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnChannelComps.sql b/schema/spacewalk/postgres/triggers/rhnChannelComps.sql index c11f904..f84f201 100644 --- a/schema/spacewalk/postgres/triggers/rhnChannelComps.sql +++ b/schema/spacewalk/postgres/triggers/rhnChannelComps.sql @@ -5,9 +5,17 @@ $$ begin new.modified := current_timestamp; - if new.last_modified = old.last_modified then + if tg_op='UPDATE' then + if new.last_modified = old.last_modified or + new.last_modified is null then new.last_modified := current_timestamp; + end if; + else + if new.last_modified is null then + new.last_modified := current_timestamp; + end if; end if; + return new; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql b/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql index b0138ef..61ad2fe 100644 --- a/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql +++ b/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql @@ -35,18 +35,13 @@ execute procedure rhn_confchan_mod_trig_fun(); create or replace function rhn_confchan_del_trig_fun() returns trigger as $$ declare - snapshots cursor is - select snapshot_id as id - from rhnSnapshotConfigChannel - where config_channel_id = old.id; - snapshot_curs_id numeric; begin - open snapshots; + for snapshot_curs_id in + select snapshot_id as id + from rhnSnapshotConfigChannel + where config_channel_id = old.id loop - fetch snapshots into snapshot_curs_id; - exit when not found; - update rhnSnapshot set invalid = lookup_snapshot_invalid_reason('cc_removed') where id = snapshot_curs_id; @@ -56,7 +51,7 @@ begin end loop; - return new; + return old; end; $$ language plpgsql; @@ -66,5 +61,5 @@ create trigger rhn_confchan_del_trig before delete on rhnConfigChannel for each row -execute procedure rhn_confchan_mod_trig_fun(); +execute procedure rhn_confchan_del_trig_fun(); diff --git a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql index 4c3795d..63db529 100644 --- a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql +++ b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql @@ -79,20 +79,13 @@ create or replace function rhn_confrevision_del_trig_fun() returns trigger as $$ declare - snapshots cursor for - select snapshot_id - from rhnSnapshotConfigRevision - where config_revision_id = old.id; - snapshot_curs_id numeric; begin - - open snapshots; - + for snapshot_curs_id in + select snapshot_id + from rhnSnapshotConfigRevision + where config_revision_id = old.id loop - fetch snapshots into snapshot_curs_id; - exit when not found; - update rhnSnapshot set invalid = lookup_snapshot_invalid_reason('cr_removed') where id = snapshot_curs_id; @@ -102,7 +95,7 @@ begin end loop; - return new; + return old; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql b/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql index fcf1dbd..e86fc46 100644 --- a/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql +++ b/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql @@ -23,14 +23,14 @@ $$ begin if tg_op='INSERT' or tg_op='UPDATE' then new.modified := current_timestamp; + return new; end if; if tg_op='DELETE' then update rhnErrata set rhnErrata.last_modified = current_timestamp where rhnErrata.id in ( old.errata_id ); + return old; end if; - - return new; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql b/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql index 1115fad..0c2ef8e 100644 --- a/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql +++ b/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql @@ -1,10 +1,19 @@ create or replace function rhn_kstreefile_mod_trig_fun() returns trigger as $$ begin - new.modified := current_timestamp; - if new.last_modified = old.last_modified then - new.last_modified := current_timestamp; - end if; + new.modified := current_timestamp; + + if tg_op='UPDATE' then + if new.last_modified = old.last_modified or + new.last_modified is null then + new.last_modified := current_timestamp; + end if; + else + if new.last_modified is null then + new.last_modified := current_timestamp; + end if; + end if; + return new; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql b/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql index 238e025..9dfd453 100644 --- a/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql +++ b/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql @@ -38,7 +38,7 @@ create or replace function rhn_ks_session_history_trigger_fun() returns trigger as $$ begin - if tg_op ='INSERT' or (tg_op='UPDATE' and new.state_id != old.state_id) then + if tg_op ='INSERT' then insert into rhnKickstartSessionHistory ( id, kickstart_session_id, action_id, state_id ) values ( @@ -48,10 +48,18 @@ begin new.state_id ); end if; - - return new; - - + if tg_op ='UPDATE' then + if new.state_id is distinct from old.state_id then + insert into rhnKickstartSessionHistory ( + id, kickstart_session_id, action_id, state_id + ) values ( + nextval('rhn_ks_sessionhist_id_seq'), + new.id, + new.action_id, + new.state_id + ); + end if; + end if; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql b/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql index fe542b3..600b022 100644 --- a/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql +++ b/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql @@ -1,13 +1,19 @@ create or replace function rhn_kstree_mod_trig_fun() returns trigger as $$ begin - if (new.last_modified = old.last_modified) or - (new.last_modified is null ) then - new.last_modified := sysdate; - end if; - new.modified := current_timestamp; - + + if tg_op='UPDATE' then + if new.last_modified = old.last_modified or + new.last_modified is null then + new.last_modified := current_timestamp; + end if; + else + if new.last_modified is null then + new.last_modified := current_timestamp; + end if; + end if; + return new; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnPackage.sql b/schema/spacewalk/postgres/triggers/rhnPackage.sql index b2bcefa..3e445e6 100644 --- a/schema/spacewalk/postgres/triggers/rhnPackage.sql +++ b/schema/spacewalk/postgres/triggers/rhnPackage.sql @@ -1,14 +1,20 @@ create or replace function rhn_package_mod_trig_fun() returns trigger as $$ begin - - if new.last_modified = old.last_modified then - new.last_modified :=current_timestamp; - end if; + new.modified := current_timestamp; - new.modified := current_timestamp; - - return new; + if tg_op='UPDATE' then + if new.last_modified = old.last_modified or + new.last_modified is null then + new.last_modified := current_timestamp; + end if; + else + if new.last_modified is null then + new.last_modified := current_timestamp; + end if; + end if; + + return new; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnServerAction.sql b/schema/spacewalk/postgres/triggers/rhnServerAction.sql index c501433..5bceaee 100644 --- a/schema/spacewalk/postgres/triggers/rhnServerAction.sql +++ b/schema/spacewalk/postgres/triggers/rhnServerAction.sql @@ -6,7 +6,7 @@ begin new.modified := current_timestamp; handle_status := 0; if TG_OP = 'UPDATE' then - if new.status != old.status then + if new.status is distinct from old.status then handle_status := 1; end if; else diff --git a/schema/spacewalk/postgres/triggers/rhnServerGroup.sql b/schema/spacewalk/postgres/triggers/rhnServerGroup.sql index 338a602..6bd7e88 100644 --- a/schema/spacewalk/postgres/triggers/rhnServerGroup.sql +++ b/schema/spacewalk/postgres/triggers/rhnServerGroup.sql @@ -73,19 +73,13 @@ create or replace function rhn_sg_del_trig_fun() returns trigger as $$ declare - snapshots cursor for + snapshot_curs_id numeric; +begin + for snapshot_curs_id in select snapshot_id from rhnSnapshotServerGroup - where server_group_id = old.id; - - snapshot_curs_id numeric; -begin - - open snapshots; + where server_group_id = old.id loop - fetch snapshots into snapshot_curs_id; - exit when not found; - update rhnSnapshot set invalid = lookup_snapshot_invalid_reason('sg_removed') where id = snapshot_curs_id; @@ -95,18 +89,13 @@ begin end loop; - return new; - + return old; end; $$ language plpgsql; - - create trigger rhn_sg_del_trig before delete on rhnServerGroup for each row execute procedure rhn_sg_del_trig_fun(); - - diff --git a/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql b/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql index 72af9d3..2e9b077 100644 --- a/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql +++ b/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql @@ -47,7 +47,6 @@ create or replace function rhn_sg_member_email_del_trig_fun() returns trigger as $$ begin perform rhn_email.add_for_server(old.server_id); - return new; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql index 9262b72..cc68dd8 100644 --- a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql +++ b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql @@ -96,7 +96,7 @@ begin set current_members = current_members - 1 where id = old.user_group_id; - return new; + return old; end; $$ language plpgsql; @@ -132,8 +132,6 @@ begin UPDATE web_contact SET password = old_password WHERE id = old.user_id; END IF; END IF; - - RETURN NEW; end; $$ LANGUAGE PLPGSQL; diff --git a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql index 7ce7940..4989b77 100644 --- a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql +++ b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql @@ -45,8 +45,6 @@ as $$ begin perform rhn_email.add_for_user(old.user_id); - - return new; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql b/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql index f2c6829..b15633b 100644 --- a/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql +++ b/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql @@ -43,7 +43,7 @@ $$ begin perform rhn_email.add_for_user(old.user_id); - return new; + return old; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql b/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql index 68867ef..0c81224 100644 --- a/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql +++ b/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql @@ -1,12 +1,6 @@ create or replace function rhn_cmeth_val_trig_fun() returns trigger as $$ -declare - msg varchar(200); - --missing_data exception; begin - - msg :='missing or invalid data for contact_methods table'; - if new.method_type_id = 1 then --- pager fields pager_email,pager_split_long_messages should be not null @@ -31,13 +25,7 @@ begin end if; end if; - - - if not found then - perform rhn_exception.raise_exception(msg); - end if; - - return new; + return new; end; $$ language plpgsql;

14 years, 12 months

1
0
0 / 0

Branch 'VADER' - java/code

by Justin Sherrill

java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) New commits: commit e387851c86d729a184b937020de397fa359ea90d Author: Justin Sherrill <jsherril(a)redhat.com> Date: Thu Apr 30 18:23:16 2009 -0400 unit test fix diff --git a/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java b/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java index 2d321ae..2f40459 100644 --- a/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java +++ b/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java @@ -229,11 +229,11 @@ public class KickstartUrlHelper { * * To be filled out by cobbler. not spacewalk. * - * @return String url , cobbler style: http://@@http_server@@$media_url + * @return String url , cobbler style: http://@@http_server@@/$media_url */ public String getCobblerMediaUrl() { StringBuilder url = new StringBuilder(); - url.append(protocol + host + "$" + COBBLER_MEDIA_VARIABLE); + url.append(protocol + host + "/$" + COBBLER_MEDIA_VARIABLE); log.debug("returning: " + url); return url.toString(); }

14 years, 12 months

1
0
0 / 0

java/code

by Justin Sherrill

java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) New commits: commit 66586bb4b122c6e06e452554fa29144e99371234 Author: Justin Sherrill <jsherril(a)redhat.com> Date: Thu Apr 30 18:23:16 2009 -0400 unit test fix diff --git a/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java b/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java index 2d321ae..2f40459 100644 --- a/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java +++ b/java/code/src/com/redhat/rhn/manager/kickstart/KickstartUrlHelper.java @@ -229,11 +229,11 @@ public class KickstartUrlHelper { * * To be filled out by cobbler. not spacewalk. * - * @return String url , cobbler style: http://@@http_server@@$media_url + * @return String url , cobbler style: http://@@http_server@@/$media_url */ public String getCobblerMediaUrl() { StringBuilder url = new StringBuilder(); - url.append(protocol + host + "$" + COBBLER_MEDIA_VARIABLE); + url.append(protocol + host + "/$" + COBBLER_MEDIA_VARIABLE); log.debug("returning: " + url); return url.toString(); }

14 years, 12 months

1
0
0 / 0

java/code

by Justin Sherrill

java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) New commits: commit 3c82b7c0607e554551e7c771529b91072e4ba68f Author: Justin Sherrill <jsherril(a)redhat.com> Date: Thu Apr 30 18:17:49 2009 -0400 fixing unit test by fixing bad hibernate mapping diff --git a/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml b/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml index 06f888a..0286420 100644 --- a/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml +++ b/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml @@ -23,7 +23,8 @@ PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" <set name="fileLists" table="RHNACTIONKICKSTARTFILELIST" lazy="true"> <key column="action_ks_id"/> - <element type="long" column="file_list_id" not-null="true"/> + <many-to-many column="file_list_id" + class="com.redhat.rhn.domain.common.FileList" /> </set> </class>

14 years, 12 months

1
0
0 / 0

Branch 'VADER' - java/code

by Justin Sherrill

java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) New commits: commit 9cecc6bb38eaf640273272fe1dc95637834761d9 Author: Justin Sherrill <jsherril(a)redhat.com> Date: Thu Apr 30 18:17:49 2009 -0400 fixing unit test by fixing bad hibernate mapping diff --git a/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml b/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml index 06f888a..0286420 100644 --- a/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml +++ b/java/code/src/com/redhat/rhn/domain/action/kickstart/KickstartActionDetails.hbm.xml @@ -23,7 +23,8 @@ PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" <set name="fileLists" table="RHNACTIONKICKSTARTFILELIST" lazy="true"> <key column="action_ks_id"/> - <element type="long" column="file_list_id" not-null="true"/> + <many-to-many column="file_list_id" + class="com.redhat.rhn.domain.common.FileList" /> </set> </class>

14 years, 12 months

1
0
0 / 0

Branch 'pgsql' - 6 commits - schema/spacewalk

by Tom Lane

schema/spacewalk/common/data/rhnException.sql | 4 schema/spacewalk/postgres/packages/rhn_bel.pkb | 20 schema/spacewalk/postgres/packages/rhn_cache.pkb | 17 schema/spacewalk/postgres/packages/rhn_channel.pkb | 211 - schema/spacewalk/postgres/packages/rhn_config.pkb | 77 schema/spacewalk/postgres/packages/rhn_config.pks | 21 schema/spacewalk/postgres/packages/rhn_config_channel.pkb | 40 schema/spacewalk/postgres/packages/rhn_config_channel.pks | 37 schema/spacewalk/postgres/packages/rhn_date_manip.pkb | 59 schema/spacewalk/postgres/packages/rhn_date_manip.pks | 23 schema/spacewalk/postgres/packages/rhn_entitlements.pkb | 1684 ++++++++++ schema/spacewalk/postgres/packages/rhn_entitlements.pks | 308 + schema/spacewalk/postgres/packages/rhn_exception.pks | 3 schema/spacewalk/postgres/packages/rhn_org.pkb | 212 - schema/spacewalk/postgres/packages/rhn_org.pks | 9 schema/spacewalk/postgres/packages/rhn_package.pkb | 24 schema/spacewalk/postgres/packages/rhn_package.pks | 10 schema/spacewalk/postgres/packages/rhn_qos.pkb | 93 schema/spacewalk/postgres/packages/rhn_qos.pks | 74 schema/spacewalk/postgres/packages/rhn_quota.pkb | 19 schema/spacewalk/postgres/packages/rhn_server.pkb | 61 schema/spacewalk/postgres/packages/rhn_user.pkb | 4 schema/spacewalk/postgres/procs/pxt_session_cleanup.sql | 4 schema/spacewalk/postgres/procs/queue_server.sql | 9 schema/spacewalk/postgres/procs/queue_server_overloaded.sql | 32 schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql | 39 schema/spacewalk/postgres/procs/set_ks_session_history_message.sql | 10 schema/spacewalk/postgres/procs/truncateCacheQueue.sql | 2 schema/spacewalk/postgres/triggers/rhnErrata.sql | 28 29 files changed, 2359 insertions(+), 775 deletions(-) New commits: commit b7388a10ea8ae0d7f966a37f8b9a27908c5ff453 Author: Tom Lane <tgl(a)redhat.com> Date: Thu Apr 30 17:44:24 2009 -0400 First pass of package code fixes based on manual testing. Mostly getting rid of NO_DATA_FOUND exceptions in favor of cheaper IF NOT FOUND tests, using LIMIT/OFFSET in place of rownum, and qualifying function names where needed. diff --git a/schema/spacewalk/postgres/packages/rhn_bel.pkb b/schema/spacewalk/postgres/packages/rhn_bel.pkb index a48f6c2..0c5d868 100644 --- a/schema/spacewalk/postgres/packages/rhn_bel.pkb +++ b/schema/spacewalk/postgres/packages/rhn_bel.pkb @@ -25,23 +25,15 @@ update pg_settings set setting = 'rhn_bel,' || setting where name = 'search_path create or replace function is_org_paid (org_id_in in numeric) returns numeric as $$ -declare - paids cursor for + begin + if exists( select 1 from rhnPaidOrgs - where org_id = org_id_in; - - paids_curs_1 numeric; - begin - - open paids; - loop - fetch paids into paids_curs_1; - exit when not found; + where org_id = org_id_in ) then return 1; - end loop; - - return 0; + else + return 0; + end if; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/packages/rhn_cache.pkb b/schema/spacewalk/postgres/packages/rhn_cache.pkb index d8a15ef..8abc68b 100644 --- a/schema/spacewalk/postgres/packages/rhn_cache.pkb +++ b/schema/spacewalk/postgres/packages/rhn_cache.pkb @@ -64,6 +64,8 @@ begin end; $$ language plpgsql; + -- this means a server got added or removed, so we + -- can't key off of a server anywhere. create or replace function update_perms_for_server_group( server_group_id_in in numeric ) returns void @@ -72,7 +74,7 @@ $$ declare users cursor for -- org admins aren't affected, so don't test for them - select usgp.user_id + select usgp.user_id as id from rhnUserServerGroupPerms usgp where usgp.server_group_id = server_group_id_in and not exists ( @@ -87,21 +89,12 @@ declare and ug.org_id = sg.org_id and ugm.user_group_id = ug.id ); - - users_curs_uid numeric; begin - open users; - loop - fetch users into users_curs_uid; - exit when not found; - perform update_perms_for_user(users_curs_uid); + for u in users loop + perform update_perms_for_user(u.id); end loop; - end; $$ language plpgsql; - - -- restore the original setting update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_cache')+1) ) where name = 'search_path'; - diff --git a/schema/spacewalk/postgres/packages/rhn_channel.pkb b/schema/spacewalk/postgres/packages/rhn_channel.pkb index c8b7331..fdb35e6 100644 --- a/schema/spacewalk/postgres/packages/rhn_channel.pkb +++ b/schema/spacewalk/postgres/packages/rhn_channel.pkb @@ -46,13 +46,12 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ FROM rhnChannelFamilyLicense CFL, rhnChannelFamilyMembers CFM WHERE CFM.channel_id = channel_id_in AND CFM.channel_family_id = CFL.channel_family_id; + + IF NOT FOUND THEN + RETURN NULL; + END IF; RETURN license_val; - - EXCEPTION - WHEN NO_DATA_FOUND - THEN - RETURN NULL; END$$ language plpgsql; @@ -87,7 +86,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ server_org_id_val NUMERIC; available_subscriptions NUMERIC; consenting_user NUMERIC; - allowed numeric := 0; + allowed numeric; current_members_val numeric; BEGIN if user_id_in is not null then @@ -106,7 +105,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ IF channel_parent_val IS NOT NULL THEN -- child channel; if attempting to cross-subscribe a child to the wrong base, silently ignore - parent_subscribed := check_server_subscription(server_id_in, channel_parent_val); + parent_subscribed := rhn_channel.check_server_subscription(server_id_in, channel_parent_val); IF NOT parent_subscribed THEN @@ -114,7 +113,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ END IF; ELSE -- base channel - server_has_base_chan := server_base_subscriptions(server_id_in); + server_has_base_chan := rhn_channel.server_base_subscriptions(server_id_in); IF server_has_base_chan THEN @@ -122,7 +121,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ END IF; END IF; - server_already_in_chan := check_server_subscription(server_id_in, channel_id_in); + server_already_in_chan := rhn_channel.check_server_subscription(server_id_in, channel_id_in); IF server_already_in_chan THEN @@ -139,7 +138,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ -- Use the org_id of the server only if the org_id of the channel = NULL. -- This is required for subscribing to shared channels. -- - SELECT NVL(org_id, (SELECT org_id FROM rhnServer WHERE id = server_id_in)) + SELECT COALESCE(org_id, (SELECT org_id FROM rhnServer WHERE id = server_id_in)) INTO server_org_id_val FROM rhnChannel WHERE id = channel_id_in; @@ -148,28 +147,25 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ into current_members_val from rhnPrivateChannelFamily where org_id = server_org_id_val and channel_family_id = channel_family_id_val - for update of current_members; + for update of rhnPrivateChannelFamily; available_subscriptions := rhn_channel.available_family_subscriptions(channel_family_id_val, server_org_id_val); IF available_subscriptions IS NULL OR available_subscriptions > 0 or - can_server_consume_virt_channl(server_id_in, channel_family_id_val) = 1 + rhn_channel.can_server_consume_virt_channl(server_id_in, channel_family_id_val) = 1 THEN IF rhn_channel.get_license_path(channel_id_in) IS NOT NULL THEN - BEGIN - SELECT user_id INTO consenting_user FROM rhnChannelFamilyLicenseConsent WHERE channel_family_id = channel_family_id_val AND server_id = server_id_in; - - EXCEPTION - WHEN NO_DATA_FOUND THEN + + IF NOT FOUND THEN perform rhn_exception.raise_exception('channel_subscribe_no_consent'); - END; + END IF; END IF; insert into rhnServerHistory (id,server_id,summary,details) ( @@ -196,9 +192,9 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ family_id_in in numeric ) returns numeric as $$ - declare - server_virt_families cursor for - select vi.virtual_system_id, cfvsl.channel_family_id + begin + if exists( + select 1 from rhnChannelFamilyVirtSubLevel cfvsl, rhnSGTypeVirtSubLevel sgtvsl, @@ -211,15 +207,12 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ select 1 from rhnServerEntitlementView sev where vi.host_system_id = sev.server_id - and sev.server_group_type_id = sgtvsl.server_group_type_id ); - begin - - for server_virt_family in server_virt_families loop - return 1; - end loop; - + and sev.server_group_type_id = sgtvsl.server_group_type_id )) + then + return 1; + else return 0; - + end if; end$$ language plpgsql; @@ -251,7 +244,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ LOOP IF rhn_server.can_change_base_channel(server.element) = 1 THEN - perform rhn_channel.clear_subscriptions(TO_NUMERIC(server.element)); + perform rhn_channel.clear_subscriptions(server.element); perform rhn_channel.subscribe_server(server.element, channel_id_in, 0, set_uid_in); END IF; END LOOP; @@ -299,7 +292,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ s.id ); end loop; - perform bulk_server_base_change(new_channel_id_in, + perform rhn_channel.bulk_server_base_change(new_channel_id_in, set_label_in || 'basechange', set_uid_in); delete from rhnSet @@ -326,9 +319,9 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ begin if rhn_server.can_change_base_channel(server.element) = 1 then - channel_id := guess_server_base(TO_NUMERIC(server.element)); - perform rhn_channel.clear_subscriptions(TO_NUMERIC(server.element)); - perform rhn_channel.subscribe_server(TO_NUMERIC(server.element), channel_id, 0, set_uid_in); + channel_id := rhn_channel.guess_server_base(server.element); + perform rhn_channel.clear_subscriptions(server.element); + perform rhn_channel.subscribe_server(server.element, channel_id, 0, set_uid_in); end if; exception when others then null; @@ -383,12 +376,11 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ as $$ declare suffix VARCHAR(128) := '-redhat-linux'; - suffix_len NUMERIC := length(suffix); begin if server_arch_in is NULL then return NULL; end if; - if instr(server_arch_in, '-') > 0 + if position('-' IN server_arch_in) > 0 then -- Suffix already present return server_arch_in; @@ -407,20 +399,19 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ user_id_in in numeric default null ) returns numeric as $$ declare - server_arch varchar(256) := normalize_server_arch(server_arch_in); + server_arch varchar(256) := rhn_channel.normalize_server_arch(server_arch_in); server_arch_id numeric; begin -- Look up the server arch - begin select id into server_arch_id from rhnServerArch where label = server_arch; - exception - when no_data_found then + if not found then perform rhn_exception.raise_exception('server_arch_not_found'); - end; - return base_channel_rel_archid(release_in, server_arch_id, + end if; + + return rhn_channel.base_channel_rel_archid(release_in, server_arch_id, org_id_in, user_id_in); end$$ language plpgsql; @@ -459,18 +450,18 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ begin if org_id_in = -1 and user_id_in is not null then -- Get the org id from the user id - begin + select org_id into valid_org_id from web_contact where id = user_id_in; - exception - when no_data_found then + + if not found then -- User doesn't exist -- XXX Only list public stuff for now valid_user_id := null; valid_org_id := -1; - end; + end if; end if; for c in base_channel_cursor(release_in, server_arch_id_in, valid_org_id) @@ -483,9 +474,8 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ end if; -- Check user to channel permissions - select loose_user_role_check(c.id, user_id_in, 'subscribe') - into channel_subscribable - from dual; + select rhn_channel.loose_user_role_check(c.id, user_id_in, 'subscribe') + into channel_subscribable; if channel_subscribable = 1 then return c.id; @@ -532,7 +522,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ end if; end loop; end loop; - perform bulk_guess_server_base(set_label_in||'baseguess',set_uid_in); + perform rhn_channel.bulk_guess_server_base(set_label_in||'baseguess',set_uid_in); delete from rhnSet where label = set_label_in||'baseguess' and user_id = set_uid_in; end$$ language plpgsql; @@ -551,7 +541,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ BEGIN for channel in server_channels(server_id_in) loop - perform unsubscribe_server(server_id_in, channel.channel_id, 1, 1, deleting_server); + perform rhn_channel.unsubscribe_server(server_id_in, channel.channel_id, 1, 1, deleting_server); perform rhn_channel.update_family_counts(channel.channel_family_id, channel.org_id); end loop; END$$ language plpgsql; @@ -588,17 +578,17 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in) LOOP if unsubscribe_children_in = 1 then - perform unsubscribe_server(server_id_in => server_id_in, - channel_id_in => child.id, - immediate_in => immediate_in, - unsubscribe_children_in => unsubscribe_children_in, - deleting_server => deleting_server); + perform rhn_channel.unsubscribe_server(server_id_in, + child.id, + immediate_in, + unsubscribe_children_in, + deleting_server); else perform rhn_exception.raise_exception('channel_unsubscribe_child_exists'); end if; END LOOP; - server_already_in_chan := check_server_subscription(server_id_in, channel_id_in); + server_already_in_chan := rhn_channel.check_server_subscription(server_id_in, channel_id_in); IF NOT server_already_in_chan THEN @@ -675,12 +665,12 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ SELECT channel_family_id INTO channel_family_id_val FROM rhnChannelFamilyMembers WHERE channel_id = channel_id_in; + + IF NOT FOUND THEN + RETURN NULL; + END IF; RETURN channel_family_id_val; - EXCEPTION - WHEN NO_DATA_FOUND - THEN - RETURN NULL; END$$ language plpgsql; CREATE OR REPLACE FUNCTION available_family_subscriptions(channel_family_id_in IN NUMERIC, org_id_in IN NUMERIC) @@ -757,9 +747,8 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ AS $$ BEGIN update rhnPrivateChannelFamily - set current_members = ( - channel_family_current_members(channel_family_id_in, org_id_in) - ) + set current_members = + rhn_channel.channel_family_current_members(channel_family_id_in, org_id_in) where org_id = org_id_in and channel_family_id = channel_family_id_in; END$$ language plpgsql; @@ -771,7 +760,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ declare channel_family_id_val NUMERIC; BEGIN - SELECT channel_family_id INTO channel_family_id_val + SELECT channel_family_id INTO STRICT channel_family_id_val FROM rhnChannelFamilyMembers WHERE channel_id = channel_id_in; @@ -798,7 +787,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ and pcf.channel_family_id = channel_family_id_in; begin for perm in permissions loop - perform set_family_maxmembers( + perform rhn_channel.set_family_maxmembers( customer_id_in, channel_family_id_in, quantity_in @@ -830,17 +819,13 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ as $$ declare servers cursor for - select server_id from ( - select rownum row_number, server_id, modified from ( select rcfsp.server_id, rcfsp.modified from rhnChannelFamilyServerPhysical rcfsp where rcfsp.customer_id = customer_id_in and rcfsp.channel_family_id = channel_family_id_in order by modified - ) ss - where rownum > quantity_in - ) ss2; + offset quantity_in; begin -- prune subscribed servers for server in servers loop @@ -848,10 +833,10 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ channel_family_id_in); end loop; - update rhnPrivateChannelFamily pcf - set pcf.max_members = quantity_in - where pcf.org_id = customer_id_in - and pcf.channel_family_id = channel_family_id_in; + update rhnPrivateChannelFamily + set max_members = quantity_in + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; end$$ language plpgsql; create or replace function unsubscribe_server_from_family(server_id_in in numeric, @@ -874,7 +859,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ declare org_id_out numeric; begin - select org_id into org_id_out + select org_id into strict org_id_out from rhnChannel where id = channel_id_in; @@ -884,41 +869,37 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ create or replace function get_cfam_org_access(cfam_id_in in numeric, org_id_in in numeric) returns numeric as $$ - declare - families cursor for + begin + if exists( select 1 from rhnOrgChannelFamilyPermissions cfp - where cfp.org_id = org_id_in; - begin - -- the idea: if we get past this query, - -- the user has the role, else catch the exception and return 0 - for family in families loop + where cfp.org_id = org_id_in + ) then return 1; - end loop; + else return 0; + end if; end$$ language plpgsql; create or replace function get_org_access(channel_id_in in numeric, org_id_in in numeric) - returns numeric + returns integer as $$ - declare - throwaway numeric; begin -- the idea: if we get past this query, - -- the org has access to the channel, else catch the exception and return 0 - select distinct 1 into throwaway + -- the org has access to the channel, else not + if exists( + select 1 from rhnChannelFamilyMembers CFM, rhnOrgChannelFamilyPermissions CFP where cfp.org_id = org_id_in and CFM.channel_family_id = CFP.channel_family_id and CFM.channel_id = channel_id_in - and (CFP.max_members > 0 or CFP.max_members is null or CFP.org_id = 1); - - return 1; - exception - when no_data_found - then - return 0; + and (CFP.max_members > 0 or CFP.max_members is null or CFP.org_id = 1) ) + then + return 1; + else + return 0; + end if; end$$ language plpgsql; -- check if a user has a given role, or if such a role is inferrable @@ -941,7 +922,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ end if; if role_in = 'manage' and - NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then + COALESCE(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then reason_out := 'channel_not_owned'; status := 0; return; @@ -1007,10 +988,12 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ select 1 into n from rhnSharedChannelView s where s.id = channel_id and s.org_trust_id = oid; + + if not found then + return 0; + end if; + return 1; - exception - when no_data_found then - return 0; end$$ language plpgsql; -- same as above, but returns 1 if user_id_in is null @@ -1022,7 +1005,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ if user_id_in is null then return 1; end if; - return user_role_check(channel_id_in, user_id_in, role_in); + return rhn_channel.user_role_check(channel_id_in, user_id_in, role_in); end$$ language plpgsql; -- directly checks the table, no inferred permissions @@ -1032,7 +1015,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ declare throwaway numeric; begin - -- the idea: if we get past this query, the user has the role, else catch the exception and return 0 + -- the idea: if we get past this query, the user has the role, else no select 1 into throwaway from rhnChannelPermissionRole CPR, rhnChannelPermission CP @@ -1040,12 +1023,12 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ and CP.channel_id = channel_id_in and CPR.label = role_in and CP.role_id = CPR.id; + + if not found then + return 0; + end if; - return 1; - exception - when no_data_found - then - return 0; + return 1; end$$ language plpgsql; -- check if an org has a certain setting @@ -1055,7 +1038,7 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ declare throwaway numeric; begin - -- the idea: if we get past this query, the org has the setting, else catch the exception and return 0 + -- the idea: if we get past this query, the org has the setting select 1 into throwaway from rhnOrgChannelSettingsType OCST, rhnOrgChannelSettings OCS @@ -1063,12 +1046,12 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ and OCS.channel_id = channel_id_in and OCST.label = setting_in and OCS.setting_id = OCST.id; - - return 1; - exception - when no_data_found - then - return 0; + + if not found then + return 0; + end if; + + return 1; end$$ language plpgsql; CREATE OR REPLACE FUNCTION channel_priority(channel_id_in IN numeric) diff --git a/schema/spacewalk/postgres/packages/rhn_config.pkb b/schema/spacewalk/postgres/packages/rhn_config.pkb index 6621d8d..8d5d78a 100644 --- a/schema/spacewalk/postgres/packages/rhn_config.pkb +++ b/schema/spacewalk/postgres/packages/rhn_config.pkb @@ -20,16 +20,15 @@ update pg_settings set setting = 'rhn_config,' || setting where name = 'search_path'; -Create or replace function prune_org_configs ( + -- just a stub for now +create or replace function prune_org_configs ( org_id_in in numeric, total_in in numeric ) -returns numeric +returns void as $$ begin - null; -return 0; - end; +end; $$ LANGUAGE 'plpgsql'; @@ -37,10 +36,10 @@ $$ LANGUAGE 'plpgsql'; revision_in in numeric, config_file_id_in in numeric, config_content_id_in in numeric, - config_info_id_in in numeric --- delim_start_in in varchar := '{@', --- delim_end_in in varchar := '@}', --- config_file_type_id_in in numeric := 1 + config_info_id_in in numeric, + delim_start_in in varchar default '{@', + delim_end_in in varchar default '@}', + config_file_type_id_in in numeric default 1 ) returns numeric as $$ declare retval numeric; @@ -49,11 +48,6 @@ $$ LANGUAGE 'plpgsql'; rhnConfigFile cf where cf.id = config_file_id_in and cf.config_channel_id = cc.id; - delim_start_in varchar(10) := '{@'; - delim_end_in varchar(10) := '@}'; - config_file_type_id_in numeric := 1; - org record; - begin insert into rhnConfigRevision(id, revision, config_file_id, @@ -66,28 +60,18 @@ $$ LANGUAGE 'plpgsql'; ) returning id into retval; - for org in select cc.org_id as id - from rhnConfigChannel cc, rhnConfigFile cf - where cf.id = config_file_id_in - and cf.config_channel_id = cc.id - loop + for org in affected_orgs loop perform rhn_quota.update_org_quota(org.id); end loop; return retval; - end ; - $$ language 'plpgsql'; - --------------------------------------------------------------------- + end$$ language 'plpgsql'; create or replace function delete_revision ( config_revision_id_in in numeric, - org_id_in in numeric + org_id_in in numeric default -1 ) returns void as $$ -declare -org_id_in numeric = 1; -other_revision record; -snapshot record; + declare cfid numeric; ccid numeric; oid numeric; @@ -105,13 +89,7 @@ snapshot record; from rhnConfigRevision where config_content_id = config_content_id_in; begin - for snapshot in select scr.snapshot_id as id - from rhnSnapshot s, - rhnSnapshotConfigRevision scr - where scr.config_revision_id = config_revision_id_in - and scr.snapshot_id = s.id - and s.invalid is null - loop + for snapshot in snapshots loop update rhnSnapshot s set s.invalid = lookup_snapshot_invalid_reason('cr_removed') where s.id = snapshot.id; @@ -141,11 +119,7 @@ snapshot record; -- now prune away content if there aren't any other revisions pointing -- at it --- for other_revision in other_revisions(ccid) loop -open other_revisions(ccid); -fetch other_revisions into other_revision; -exit when not found; -loop + for other_revision in other_revisions(ccid) loop others := 1; exit; end loop; @@ -181,7 +155,6 @@ loop end ; $$ LANGUAGE 'plpgsql'; ------------------------------------------------------------ create or replace function get_latest_revision ( config_file_id_in in numeric @@ -193,16 +166,15 @@ $$ LANGUAGE 'plpgsql'; for revision1 in select cr.id from rhnConfigRevision cr - where cr.config_file_id = config_file_id_in order by revision desc + where cr.config_file_id = config_file_id_in + order by revision desc loop - return revision1.id; - end loop; - return null; + return revision1.id; + end loop; + return null; end; $$ LANGUAGE 'plpgsql'; -------------------------------------------------------------------- - create or replace function insert_file ( config_channel_id_in in numeric, name_in in varchar @@ -227,7 +199,7 @@ $$ LANGUAGE 'plpgsql'; return retval; end; $$ LANGUAGE 'plpgsql'; --------------------------------------------------- + create or replace function delete_file ( config_file_id_in in numeric ) returns void as $$ @@ -246,14 +218,14 @@ revision record; and cr.config_file_id = cf.id loop - perform delete_revision(revision.id, revision.org_id); + perform rh_config.delete_revision(revision.id, revision.org_id); org_id := revision.org_id; end loop; perform rhn_quota.update_org_quota(org_id); delete from rhnConfigFile where id = config_file_id_in; end; $$ LANGUAGE 'plpgsql'; ------------------------------------------------- + create or replace function insert_channel ( org_id_in in numeric, type_in in varchar, @@ -282,7 +254,7 @@ declare return retval; end; $$ LANGUAGE 'plpgsql'; --------------------------------------------- + create or replace function delete_channel ( config_channel_id_in in numeric ) returns void as $$ @@ -294,7 +266,7 @@ declare from rhnConfigFile where config_channel_id = config_channel_id_in loop - perform delete_file(config_file.id); + perform rhn_config.delete_file(config_file.id); end loop; delete from rhnConfigChannel where id = config_channel_id_in; @@ -302,39 +274,3 @@ end; $$ LANGUAGE 'plpgsql'; update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_config')+1) ) where name = 'search_path'; - --- --- --- Revision 1.9 2005/02/16 14:03:35 jslagle --- bz #148844 --- Changed insert_revision function to take a config_file_type_id instead of label --- --- Revision 1.8 2005/02/15 02:42:59 jslagle --- bz #147860 --- insert_revision function now takes a rhnConfigFileType label as a parameter instead of an id --- --- Revision 1.7 2005/02/14 22:45:23 jslagle --- bz#147860 --- Update rhn_config package body and specification for additional column to rhnConfigRevision --- --- Revision 1.6 2004/10/11 14:02:53 pjones --- bugzilla: 133169 -- somehow, we just never update the quota in this case. --- Amazingly, I thought this worked _and_ QA passed it... --- --- Revision 1.5 2004/01/09 17:39:45 pjones --- bugzilla: 113029 -- need to do functions for deleting rhnConfigChannel, --- too, or we can't prune rhnConfigFile when we do. --- --- Revision 1.4 2004/01/08 19:46:31 pjones --- bugzilla: 113029 -- insert/delete for rhnConfigFile and rhnConfigRevision --- --- Revision 1.3 2004/01/08 00:30:10 pjones --- bugzilla: 113029 -- more deletion of config files and revisions --- --- Revision 1.2 2004/01/08 00:03:37 pjones --- bugzilla: 113029 -- rhn_config.delete_revision() and delete trigger on --- rhnConfigFile --- --- Revision 1.1 2003/12/19 22:07:30 pjones --- bugzilla: 112392 -- quota support for config files --- diff --git a/schema/spacewalk/postgres/packages/rhn_config.pks b/schema/spacewalk/postgres/packages/rhn_config.pks index ff1986d..647bdbb 100644 --- a/schema/spacewalk/postgres/packages/rhn_config.pks +++ b/schema/spacewalk/postgres/packages/rhn_config.pks @@ -19,12 +19,27 @@ create schema rhn_config; -- setup search_path so that these functions are created in appropriate schema. update pg_settings set setting = 'rhn_config,' || setting where name = 'search_path'; -Create or replace function prune_org_configs +create or replace function prune_org_configs ( org_id_in in numeric, total_in in numeric ) -returns numeric +returns void +as $$ +begin + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +end; +$$ LANGUAGE 'plpgsql'; + +create or replace function insert_revision ( + revision_in in numeric, + config_file_id_in in numeric, + config_content_id_in in numeric, + config_info_id_in in numeric, + delim_start_in in varchar default '{@', + delim_end_in in varchar default '@}', + config_file_type_id_in numeric default 1 + ) returns numeric as $$ begin RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; @@ -34,7 +49,7 @@ $$ LANGUAGE 'plpgsql'; create or replace function delete_revision ( config_revision_id_in in numeric, - org_id_in in numeric + org_id_in in numeric default -1 ) returns void as $$ diff --git a/schema/spacewalk/postgres/packages/rhn_config_channel.pkb b/schema/spacewalk/postgres/packages/rhn_config_channel.pkb index c7be25c..180a9f2 100644 --- a/schema/spacewalk/postgres/packages/rhn_config_channel.pkb +++ b/schema/spacewalk/postgres/packages/rhn_config_channel.pkb @@ -1,9 +1,9 @@ -- setup search_path so that these functions are created in appropriate schema. -update pg_settings set setting = 'rhn_channel_config,' || setting where name = 'search_path'; +update pg_settings set setting = 'rhn_config_channel,' || setting where name = 'search_path'; CREATE OR REPLACE FUNCTION action_diff_revision_status(action_config_revision_id_in numeric) -RETURNS VARCHAR(255) +RETURNS VARCHAR -- result_is_null obviously wants NVL2, but stupid 8.1.7.3.0 doesn't -- have that. Or case. So we're using union, instead. AS $$ @@ -41,7 +41,7 @@ BEGIN END; $$ LANGUAGE plpgsql; -Create or replace FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC, user_id_in IN NUMERIC) +CREATE OR REPLACE FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC, user_id_in IN NUMERIC) RETURNS NUMERIC as $$ Declare server_id NUMERIC; @@ -50,19 +50,16 @@ Create or replace FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC, any_visible_servers_subscribed NUMERIC; BEGIN - org_matches := 0; - BEGIN SELECT 1 INTO org_matches FROM rhnConfigChannel CC, web_contact WC WHERE WC.id = user_id_in AND CC.id = config_channel_id_in AND WC.org_id = CC.org_id; - --EXCEPTION - if noT found then + + IF NOT FOUND THEN RETURN 0; - end if; - END; + END IF; global_channel := 'unknown'; SELECT CCT.label INTO global_channel @@ -70,13 +67,12 @@ Create or replace FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC, rhnConfigChannelType CCT WHERE CC.id = config_channel_id_in AND CCT.id = CC.confchan_type_id; + IF (rhn_user.check_role_implied(user_id_in, 'config_admin') = 1) AND (global_channel = 'normal') THEN RETURN 1; END IF; - any_visible_servers_subscribed := 0; - BEGIN SELECT 1 INTO any_visible_servers_subscribed FROM DUAL WHERE EXISTS ( @@ -87,11 +83,10 @@ Create or replace FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC, AND USPD.server_id = SCC.server_id AND SCC.config_channel_id = config_channel_id_in ); - --EXCEPTION - if not found + + IF NOT FOUND THEN RETURN 0; - end if; - END; + END IF; RETURN any_visible_servers_subscribed; @@ -103,36 +98,35 @@ CREATE OR REPLACE FUNCTION get_user_revision_access(config_revision_id_in IN NUM DECLARE config_channel_id NUMERIC; BEGIN - BEGIN + SELECT CF.config_channel_id INTO config_channel_id FROM rhnConfigFile CF, rhnConfigRevision CR WHERE CF.id = CR.config_file_id AND CR.id = config_revision_id_in; + if not found then RETURN 0; end if; - END; RETURN rhn_config_channel.get_user_chan_access(config_channel_id, user_id_in); END; $$ language 'plpgsql'; - Create or replace FUNCTION get_user_file_access(config_file_id_in IN NUMERIC, user_id_in IN NUMERIC) + CREATE OR REPLACE FUNCTION get_user_file_access(config_file_id_in IN NUMERIC, user_id_in IN NUMERIC) RETURNS NUMERIC as $$ declare config_channel_id NUMERIC; BEGIN - BEGIN SELECT CF.config_channel_id INTO config_channel_id FROM rhnConfigFile CF WHERE CF.id = config_file_id_in; - if not found then - RETURN 0; - end if; - END; + + IF NOT FOUND THEN + RETURN 0; + END IF; RETURN rhn_config_channel.get_user_chan_access(config_channel_id, user_id_in); END ; diff --git a/schema/spacewalk/postgres/packages/rhn_config_channel.pks b/schema/spacewalk/postgres/packages/rhn_config_channel.pks index 4113e19..1cd2c2a 100644 --- a/schema/spacewalk/postgres/packages/rhn_config_channel.pks +++ b/schema/spacewalk/postgres/packages/rhn_config_channel.pks @@ -1,22 +1,12 @@ -create schema rhn_channel_config; +create schema rhn_config_channel; -- setup search_path so that these functions are created in appropriate schema. -update pg_settings set setting = 'rhn_channel_config,' || setting where name = 'search_path'; +update pg_settings set setting = 'rhn_config_channel,' || setting where name = 'search_path'; -CREATE OR REPLACE FUNCTION action_diff_revision_status(action_config_revision_id_in numeric) -RETURNS VARCHAR(255) -AS $$ -BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; - RETURN NULL; -END; -$$ LANGUAGE plpgsql; - -Create or replace FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC, user_id_in IN NUMERIC) +CREATE OR REPLACE FUNCTION get_user_chan_access(config_channel_id_in IN NUMERIC, user_id_in IN NUMERIC) RETURNS NUMERIC as $$ BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; - RETURN 0; + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; END; $$ language 'plpgsql'; @@ -24,11 +14,26 @@ CREATE OR REPLACE FUNCTION get_user_revision_access(config_revision_id_in IN NUM RETURNS NUMERIC AS $$ BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; - RETURN 0; + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_user_file_access(config_file_id_in IN NUMERIC, user_id_in IN NUMERIC) +RETURNS NUMERIC +AS $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; END; $$ language 'plpgsql'; +CREATE OR REPLACE FUNCTION action_diff_revision_status(action_config_revision_id_in numeric) +RETURNS VARCHAR +AS $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ LANGUAGE plpgsql; + -- restore the original setting update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_config_channel')+1) ) where name = 'search_path'; diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb index c6fdcf0..c6fe71b 100644 --- a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb @@ -187,9 +187,15 @@ as $$ where label = type_label_in; if array_upper(previous_ent, 1) = 0 then - if (is_base_in = 'Y' and rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid)) then - -- rhn_server.insert_into_servergroup (server_id_in, sgid); - return 1; + if is_base_in = 'Y' then + sgid := rhn_entitlements.find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; else -- rhn_exception.raise_exception ('invalid_base_entitlement'); return 0; @@ -224,7 +230,8 @@ as $$ -- this for loop verifies the validity of the addon path for addon_servergroup in addon_servergroups (previous_ent[i], type_label_in) loop -- find an appropriate sgid for the addon and entitle the server - if rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid) then + sgid := rhn_entitlements.find_compatible_sg (server_id_in, type_label_in); + if sgid is not null then -- rhn_server.insert_into_servergroup (server_id_in, sgid); return 1; else @@ -252,22 +259,24 @@ as $$ begin - begin - select is_base into type_label_in_is_base - from rhnServerGroupType - where label = type_label_in; - exception - when no_data_found then - perform rhn_exception.raise_exception ( 'invalid_entitlement' ); - end; + select is_base into type_label_in_is_base + from rhnServerGroupType + where label = type_label_in; + + if not found then + perform rhn_exception.raise_exception ( 'invalid_entitlement' ); + end if; if type_label_in_is_base = 'N' then perform rhn_exception.raise_exception ( 'invalid_entitlement' ); - elsif rhn_entitlements.find_compatible_sg ( server_id_in, - type_label_in, sgid ) then - return 1; else - return 0; + sgid := rhn_entitlements.find_compatible_sg ( server_id_in, + type_label_in ); + if sgid is not null then + return 1; + else + return 0; + end if; end if; end$$ @@ -321,15 +330,14 @@ as $$ begin - begin - select 1 into is_virt - from rhnServerEntitlementView - where server_id = server_id_in - and label in ('virtualization_host', 'virtualization_host_platform'); - exception - when no_data_found then - is_virt := 0; - end; + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + + if not found then + is_virt := 0; + end if; if is_virt = 0 and (type_label_in = 'virtualization_host' or type_label_in = 'virtualization_host_platform') then @@ -337,12 +345,11 @@ as $$ is_virt := 1; end if; - - if rhn_entitlements.can_entitle_server(server_id_in, type_label_in) = 1 then - if rhn_entitlements.find_compatible_sg (server_id_in, - type_label_in, sgid) then + sgid := rhn_entitlements.find_compatible_sg (server_id_in, + type_label_in); + if sgid is not null then insert into rhnServerHistory ( id, server_id, summary, details ) values ( nextval('rhn_event_id_seq'), server_id_in, 'added system entitlement ', @@ -381,22 +388,17 @@ as $$ type_is_base char; is_virt numeric := 0; begin - begin - - -- would be nice if there were a virt attribute of entitlement types, not have to specify 2 different ones... - begin - select 1 into is_virt - from rhnServerEntitlementView - where server_id = server_id_in - and label in ('virtualization_host', 'virtualization_host_platform'); - exception - when no_data_found then + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + if not found then is_virt := 0; - end; + end if; select sg.id, sgt.is_base - into group_id, type_is_base + into group_id, type_is_base from rhnServerGroupType sgt, rhnServerGroup sg, rhnServerGroupMembers sgm, @@ -408,9 +410,13 @@ as $$ and sgt.label = type_label_in and sgt.id = sg.group_type; + if not found then + perform rhn_exception.raise_exception('invalid_server_group_member'); + end if; + if ( type_is_base = 'Y' ) then -- unentitle_server should handle everything, don't really need to do anything else special here - perform unentitle_server ( server_id_in ); + perform rhn_entitlements.unentitle_server ( server_id_in ); else insert into rhnServerHistory ( id, server_id, summary, details ) @@ -436,8 +442,8 @@ as $$ WHERE host_id = server_id_in); DELETE FROM time_series - WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1, - (INSTR(o_id, '-', INSTR(o_id, '-') + 1) - INSTR(o_id, '-')) - 1) + WHERE substring(o_id FROM position('-' IN o_id) + 1 + FOR position('-' IN substring(o_id FROM position('-' IN o_id) + 1)) - 1) IN (SELECT probe_id FROM rhn_check_probe WHERE host_id = server_id_in); @@ -453,11 +459,6 @@ as $$ end if; end if; - exception - when no_data_found then - perform rhn_exception.raise_exception('invalid_server_group_member'); - end; - end$$ language plpgsql; @@ -482,15 +483,14 @@ as $$ begin - begin - select 1 into is_virt - from rhnServerEntitlementView - where server_id = server_id_in - and label in ('virtualization_host', 'virtualization_host_platform'); - exception - when no_data_found then + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + + if not found then is_virt := 0; - end; + end if; for servergroup in servergroups loop @@ -1038,7 +1038,6 @@ as $$ group_type numeric; begin - begin select max_members into prev_ent_count from rhnServerGroupType sgt, @@ -1046,13 +1045,12 @@ as $$ where sg.org_id = from_org_id_in and sg.group_type = sgt.id and sgt.label = group_label_in; - exception - when NO_DATA_FOUND then + + if not found then perform rhn_exception.raise_exception( 'not_enough_entitlements_in_base_org'); - end; + end if; - begin select max_members into to_org_prev_ent_count from rhnServerGroupType sgt, @@ -1060,21 +1058,20 @@ as $$ where sg.org_id = to_org_id_in and sg.group_type = sgt.id and sgt.label = group_label_in; - exception - when NO_DATA_FOUND then + + if not found then to_org_prev_ent_count := 0; - end; + end if; - begin select id into group_type from rhnServerGroupType where label = group_label_in; - exception - when NO_DATA_FOUND then + + if not found then perform rhn_exception.raise_exception( 'invalid_server_group'); - end; + end if; new_ent_count := prev_ent_count - quantity_in; @@ -1101,25 +1098,25 @@ as $$ -- Create or delete the entries in rhnOrgEntitlementType if group_label_in = 'enterprise_entitled' then if new_quantity > 0 then - perform set_customer_enterprise(to_org_id_in); + perform rhn_entitlements.set_customer_enterprise(to_org_id_in); else - perform unset_customer_enterprise(to_org_id_in); + perform rhn_entitlements.unset_customer_enterprise(to_org_id_in); end if; end if; if group_label_in = 'provisioning_entitled' then if new_quantity > 0 then - perform set_customer_provisioning(to_org_id_in); + perform rhn_entitlements.set_customer_provisioning(to_org_id_in); else - perform unset_customer_provisioning(to_org_id_in); + perform rhn_entitlements.unset_customer_provisioning(to_org_id_in); end if; end if; if group_label_in = 'monitoring_entitled' then if new_quantity > 0 then - perform set_customer_monitoring(to_org_id_in); + perform rhn_entitlements.set_customer_monitoring(to_org_id_in); else - perform unset_customer_monitoring(to_org_id_in); + perform rhn_entitlements.unset_customer_monitoring(to_org_id_in); end if; end if; @@ -1150,7 +1147,6 @@ as $$ cfam_id numeric; begin - begin select max_members into prev_ent_count from rhnChannelFamily cf, @@ -1158,13 +1154,12 @@ as $$ where pcf.org_id = from_org_id_in and pcf.channel_family_id = cf.id and cf.label = channel_family_label_in; - exception - when NO_DATA_FOUND then + + if not found then perform rhn_exception.raise_exception( 'not_enough_entitlements_in_base_org'); - end; + end if; - begin select max_members into to_org_prev_ent_count from rhnChannelFamily cf, @@ -1172,22 +1167,20 @@ as $$ where pcf.org_id = to_org_id_in and pcf.channel_family_id = cf.id and cf.label = channel_family_label_in; - exception - when NO_DATA_FOUND then + + if not found then to_org_prev_ent_count := 0; - end; - + end if; - begin select id into cfam_id from rhnChannelFamily where label = channel_family_label_in; - exception - when NO_DATA_FOUND then + + if not found then perform rhn_exception.raise_exception( 'invalid_channel_family'); - end; + end if; new_ent_count := prev_ent_count - quantity_in; @@ -1238,7 +1231,7 @@ as $$ -- Fetch the current entitlement count for the org -- into prev_ent_count - begin + select current_members into prev_ent_count from rhnServerGroupType sgt, @@ -1246,21 +1239,20 @@ as $$ where sg.group_type = sgt.id and sgt.label = group_label_in and sg.org_id = org_id_in; - exception - when NO_DATA_FOUND then + + if not found then prev_ent_count := 0; - end; + end if; - begin select id into group_type from rhnServerGroupType where label = group_label_in; - exception - when NO_DATA_FOUND then + + if not found then perform rhn_exception.raise_exception( 'invalid_server_group'); - end; + end if; -- If we're setting the total entitlemnt count to a lower value, -- and that value is less than the allocated count in this org, @@ -1306,7 +1298,6 @@ as $$ -- Fetch the current entitlement count for the org -- into prev_ent_count - begin select current_members into prev_ent_count from rhnChannelFamily cf, @@ -1314,21 +1305,20 @@ as $$ where pcf.org_id = org_id_in and pcf.channel_family_id = cf.id and cf.label = channel_family_label_in; - exception - when NO_DATA_FOUND then + + if not found then prev_ent_count := 0; - end; + end if; - begin select id into cfam_id from rhnChannelFamily where label = channel_family_label_in; - exception - when NO_DATA_FOUND then + + if not found then perform rhn_exception.raise_exception( 'invalid_channel_family'); - end; + end if; -- If we're setting the total entitlemnt count to a lower value, -- and that value is less than the count in that one org, @@ -1368,45 +1358,47 @@ as $$ where 1=1 and rug.org_id = customer_id_in and rug.group_type = group_type_in; - elsif type_in = 'S' then - select rsg.id - into group_id - from rhnServerGroup rsg - where 1=1 - and rsg.org_id = customer_id_in - and rsg.group_type = group_type_in; - end if; - perform rhn_entitlements.prune_group( - group_id, - type_in, - quantity - ); - exception - when no_data_found then - if type_in = 'U' then + if not found then insert into rhnUserGroup ( id, name, description, max_members, current_members, group_type, org_id, created, modified ) ( select nextval('rhn_user_group_id_seq'), name, name, quantity, 0, id, customer_id_in, - sysdate, sysdate + current_timestamp, current_timestamp from rhnUserGroupType where id = group_type_in ); - elsif type_in = 'S' then + end if; + + elsif type_in = 'S' then + select rsg.id + into group_id + from rhnServerGroup rsg + where 1=1 + and rsg.org_id = customer_id_in + and rsg.group_type = group_type_in; + + if not found then insert into rhnServerGroup ( id, name, description, max_members, current_members, group_type, org_id, created, modified ) ( select nextval('rhn_server_group_id_seq'), name, name, quantity, 0, id, customer_id_in, - sysdate, sysdate + current_timestamp, current_timestamp from rhnServerGroupType where id = group_type_in ); end if; + end if; + + perform rhn_entitlements.prune_group( + group_id, + type_in, + quantity + ); end$$ language plpgsql; diff --git a/schema/spacewalk/postgres/packages/rhn_exception.pks b/schema/spacewalk/postgres/packages/rhn_exception.pks index ec991c2..f9ccc69 100644 --- a/schema/spacewalk/postgres/packages/rhn_exception.pks +++ b/schema/spacewalk/postgres/packages/rhn_exception.pks @@ -3,8 +3,7 @@ create schema rhn_exception; -- setup search_path so that these functions are created in appropriate schema. update pg_settings set setting = 'rhn_exception,' || setting where name = 'search_path'; -CREATE OR REPLACE FUNCTION is_org_paid (org_id_in IN NUMERIC) -RETURNS NUMERIC +CREATE OR REPLACE FUNCTION lookup_exception(exception_label_in IN VARCHAR, exception_id_out OUT NUMERIC, exception_message_out OUT VARCHAR) AS $$ BEGIN RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; diff --git a/schema/spacewalk/postgres/packages/rhn_org.pkb b/schema/spacewalk/postgres/packages/rhn_org.pkb index 1f532d9..ce774a0 100644 --- a/schema/spacewalk/postgres/packages/rhn_org.pkb +++ b/schema/spacewalk/postgres/packages/rhn_org.pkb @@ -31,13 +31,8 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la AND SGT.label = group_label_in AND SG.org_id = org_id_in; - - server_group record; --server_group_by_label%ROWTYPE; - - + server_group record; BEGIN - - OPEN server_group_by_label(org_id_in, group_label_in); FETCH server_group_by_label INTO server_group; CLOSE server_group_by_label; @@ -47,9 +42,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la $$ LANGUAGE PLPGSQL; - - - create or replace function delete_org ( +create or replace function delete_org ( org_id_in in numeric ) returns void as @@ -60,36 +53,26 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la from web_contact where org_id = org_id_in; - user_curs_id numeric; - servers cursor (org_id_in numeric) for select id from rhnServer where org_id = org_id_in; - servers_curs_id numeric; - config_channels cursor for select id from rhnConfigChannel where org_id = org_id_in; - conf_channel_curs_id numeric; - custom_channels cursor for select id from rhnChannel where org_id = org_id_in; - cust_channel_curs_id numeric; - errata cursor for select id from rhnErrata where org_id = org_id_in; - errata_curs_id numeric; - begin if org_id_in = 1 then @@ -97,57 +80,33 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la end if; -- Delete all users. - open users; - loop - fetch users into user_curs_id; - exit when not found; - perform rhn_org.delete_user(user_curs_id, 1); + for u in users loop + perform rhn_org.delete_user(u.id, 1); end loop; - close users; - -- Delete all servers. - open servers(org_id_in); - loop - fetch servers into servers_curs_id; - exit when not found; - perform delete_server(servers_curs_id); + for s in servers(org_id_in) loop + perform delete_server(s.id); end loop; - close servers; - + -- Delete all config channels. - open config_channels; - loop - fetch config_channels into conf_channel_curs_id; - exit when not found; - perform rhn_config.delete_channel(conf_channel_curs_id); - end loop; - close config_channels; - + for c in config_channels loop + perform rhn_config.delete_channel(c.id); + end loop; -- Delete all custom channels. - open custom_channels; - loop - fetch custom_channels into cust_channel_curs_id; - exit when not found; - delete from rhnServerChannel where channel_id = cust_channel_curs_id; + for cc in custom_channels loop + delete from rhnServerChannel where channel_id = cc.id; delete from rhnServerProfilePackage where server_profile_id in ( - select id from rhnServerProfile where base_channel = cust_channel_curs_id + select id from rhnServerProfile where base_channel = cc.id ); - delete from rhnServerProfile where base_channel = cust_channel_curs_id; - + delete from rhnServerProfile where base_channel = cc.id; + end loop; + + -- Delete all errata packages + for e in errata loop + delete from rhnErrataPackage where errata_id = e.id; end loop; - close custom_channels; - - -- Delete all errata packages - open errata; - loop - fetch errata into errata_curs_id; - exit when not found; - delete from rhnErrataPackage where errata_id = errata_curs_id; - end loop; - close errata; - -- Give the org's entitlements back to the main org. perform rhn_entitlements.remove_org_entitlements(org_id_in); @@ -175,9 +134,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la $$ language plpgsql; - -- //////////////////////////////////////////////////////// - - create or replace function delete_user(user_id_in in numeric, deleting_org in numeric) returns void +create or replace function delete_user(user_id_in in numeric, deleting_org in numeric default 0) returns void as $$ declare @@ -191,11 +148,8 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la and ug.group_type = ugt.id and ugt.label = 'org_admin'; - iadmin_curs_counter numeric; - - servergroups_needing_admins cursor for - select usgp.server_group_id + select usgp.server_group_id from rhnUserServerGroupPerms usgp where 1=1 and usgp.user_id = user_id_in @@ -206,23 +160,17 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la and sq_usgp.server_group_id = usgp.server_group_id and sq_usgp.user_id != user_id_in ); - - sg_curs_id numeric; messages cursor for select message_id from rhnUserMessage where user_id = user_id_in; - - msg_curs_id numeric; users numeric; our_org_id numeric; other_users numeric; other_org_admin numeric; - - - other_user_id numeric; + other_user_id numeric; begin select wc.org_id into our_org_id @@ -230,87 +178,63 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la where id = user_id_in; -- find any other users - begin - select id, 1 - into other_user_id, other_users - from web_contact - where 1=1 - and org_id = our_org_id - and id != user_id_in - and rownum = 1; - exception - when division_by_zero then - other_users := 0; - end; + select id, 1 + into other_user_id, other_users + from web_contact + where 1=1 + and org_id = our_org_id + and id != user_id_in + limit 1; + + if not found then + other_users := 0; + end if; -- now do org admin stuff if other_users != 0 then - open is_admin; - --for ignore in is_admin loop - loop - fetch is_admin into iadmin_curs_counter; - exit when not found; - begin - select new_ugm.user_id - into other_org_admin - from rhnUserGroupMembers new_ugm, - rhnUserGroupType ugt, - rhnUserGroup ug, - rhnUserGroupMembers ugm - where ugm.user_id = user_id_in - and ugm.user_group_id = ug.id - and ug.group_type = ugt.id - and ugt.label = 'org_admin' - and ug.id = new_ugm.user_group_id - and new_ugm.user_id != user_id_in - and rownum = 1; - if not found then + for ignore in is_admin loop + select new_ugm.user_id + into other_org_admin + from rhnUserGroupMembers new_ugm, + rhnUserGroupType ugt, + rhnUserGroup ug, + rhnUserGroupMembers ugm + where ugm.user_id = user_id_in + and ugm.user_group_id = ug.id + and ug.group_type = ugt.id + and ugt.label = 'org_admin' + and ug.id = new_ugm.user_group_id + and new_ugm.user_id != user_id_in + limit 1; + + if not found then + -- If we're deleting the org, we don't want + -- to raise the exception. + if deleting_org = 0 then + perform rhn_exception.raise_exception('cannot_delete_user'); end if; - - --end if; - -- If we're deleting the org, we don't want to raise - -- the exception. - if deleting_org = 0 then - perform rhn_exception.raise_exception('cannot_delete_user'); - end if; - end; - open servergroups_needing_admins; - loop - fetch servergroups_needing_admins into sg_curs_id; - exit when not found; - --for sg in servergroups_needing_admins loop - perform rhn_user.add_servergroup_perm(other_org_admin,sg_curs_id); - end loop; - - close servergroups_needing_admins; - end loop; + end if; - close is_admin; + for sg in servergroups_needing_admins loop + perform rhn_user.add_servergroup_perm(other_org_admin,sg.server_group_id); + end loop; + end loop; end if; -- and now things for every user - open messages; - loop - fetch messages into msg_curs_id; - exit when not found; - --for message in messages loop + for message in messages loop delete from rhnUserMessage where user_id = user_id_in - and message_id = msg_curs_id; - begin - select 1 - into users + and message_id = message.id; + + if exists(select 1 from rhnUserMessage - where message_id = msg_curs_id - and rownum = 1; - delete + where message_id = message.id) then + delete from rhnMessage - where id = msgs_curs_id; - if not founf then - null; - end if; - end; + where id = message.id; + end if; end loop; @@ -337,7 +261,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la delete from rhnAppInstallSession where user_id = user_id_in; if other_users != 0 then update rhnRegToken - set user_id = nvl(other_org_admin, other_user_id) + set user_id = coalesce(other_org_admin, other_user_id) where org_id = our_org_id and user_id = user_id_in; begin @@ -363,5 +287,3 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la -- restore the original setting update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_org')+1) ) where name = 'search_path'; - - diff --git a/schema/spacewalk/postgres/packages/rhn_org.pks b/schema/spacewalk/postgres/packages/rhn_org.pks index 8327364..cdbd452 100644 --- a/schema/spacewalk/postgres/packages/rhn_org.pks +++ b/schema/spacewalk/postgres/packages/rhn_org.pks @@ -22,8 +22,7 @@ CREATE OR REPLACE FUNCTION find_server_group_by_type(org_id_in NUMERIC, group_la RETURNS NUMERIC AS $$ BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; - RETURN 0; + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; END; $$ LANGUAGE PLPGSQL; @@ -31,16 +30,16 @@ CREATE OR REPLACE FUNCTION delete_org (org_id_in in numeric) RETURNS VOID AS $$ BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; END; $$ language plpgsql; -CREATE OR REPLACE FUNCTION delete_user(user_id_in in numeric, deleting_org in numeric) +CREATE OR REPLACE FUNCTION delete_user(user_id_in in numeric, deleting_org in numeric default 0) RETURNS VOID AS $$ BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; END; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/packages/rhn_package.pkb b/schema/spacewalk/postgres/packages/rhn_package.pkb index 69419e4..ce110bd 100644 --- a/schema/spacewalk/postgres/packages/rhn_package.pkb +++ b/schema/spacewalk/postgres/packages/rhn_package.pkb @@ -6,15 +6,15 @@ update pg_settings set setting = 'rhn_package,' || setting where name = 'search_ create or replace FUNCTION canonical_name(name_in IN VARCHAR, evr_in IN EVR_T, - arch_in IN VARCHAR(400)) - RETURNs VARCHAR as $$ + arch_in IN VARCHAR DEFAULT NULL) + RETURNS VARCHAR as $$ declare name_out VARCHAR(256); BEGIN - name_out := name_in || '-' ||evr_t_as_vre_simple(evr_in); + name_out := name_in || '-' || evr_t_as_vre_simple(evr_in); - IF arch_in IS NOT NULL + IF arch_in <> '' THEN name_out := name_out || '-' || arch_in; END IF; @@ -24,28 +24,20 @@ create or replace FUNCTION canonical_name(name_in IN VARCHAR, evr_in IN EVR_T, $$ language 'plpgsql'; -create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC, separator_in VARCHAR) +create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC, separator_in VARCHAR DEFAULT ', ') RETURNS VARCHAR as $$ declare list_out VARCHAR(4000); - channel_occupancy_cursor CURSOR (package_id_in NUMeric) for + channel_occupancy_cursor CURSOR (package_id_in numeric) for SELECT C.id AS channel_id, C.name AS channel_name FROM rhnChannel C, rhnChannelPackage CP WHERE C.id = CP.channel_id AND CP.package_id = package_id_in ORDER BY C.name DESC; - channel record; - separator_in varchar := ', '; - BEGIN - - --FOR channel IN channel_occupancy_cursor(package_id_in) - open channel_occupancy_cursor(package_id_in); - LOOP -fetch channel_occupancy_cursor into channel; -exit when not found; - + FOR channel IN channel_occupancy_cursor(package_id_in) + LOOP IF list_out IS NULL THEN list_out := channel.channel_name; diff --git a/schema/spacewalk/postgres/packages/rhn_package.pks b/schema/spacewalk/postgres/packages/rhn_package.pks index 30faeb8..7676c7f 100644 --- a/schema/spacewalk/postgres/packages/rhn_package.pks +++ b/schema/spacewalk/postgres/packages/rhn_package.pks @@ -21,24 +21,22 @@ create or replace FUNCTION canonical_name ( name_in IN VARCHAR, evr_in IN EVR_T, - arch_in IN VARCHAR(400) + arch_in IN VARCHAR DEFAULT NULL ) RETURNS VARCHAR AS $$ BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; - RETURN NULL; + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; END; $$ language 'plpgsql'; -create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC, separator_in VARCHAR) +create or replace FUNCTION channel_occupancy_string(package_id_in IN NUMERIC, separator_in VARCHAR DEFAULT ', ') RETURNS VARCHAR AS $$ BEGIN - RAISE EXCEPTION 'Stub called, must be replace by .pkb'; - RETURN NULL; + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; END; $$ language 'plpgsql'; diff --git a/schema/spacewalk/postgres/packages/rhn_quota.pkb b/schema/spacewalk/postgres/packages/rhn_quota.pkb index 6ae8983..8cd481a 100644 --- a/schema/spacewalk/postgres/packages/rhn_quota.pkb +++ b/schema/spacewalk/postgres/packages/rhn_quota.pkb @@ -24,10 +24,9 @@ create or replace function recompute_org_quota_used ( ) returns numeric as $$ declare - retval numeric := 0; + retval numeric; begin - begin - select NVL(sum(a.file_size),0) + select COALESCE(sum(a.file_size),0) into retval from ( select distinct content.id, content.file_size @@ -40,10 +39,6 @@ create or replace function recompute_org_quota_used ( and cf.id = cr.config_file_id and cr.config_content_id = content.id ) a; - exception - when others then - null; - end; return retval; end; @@ -87,16 +82,18 @@ create or replace function set_org_quota_total ( from rhnOrgQuota oq where oq.org_id = org_id_in; + if not found then + insert into rhnOrgQuota ( org_id, total ) + values (org_id_in, total_in); + return; + end if; + perform rhn_config.prune_org_configs(org_id_in, available); update rhnOrgQuota set total = total_in where org_id = org_id_in; - if not found then - insert into rhnOrgQuota ( org_id, total ) - values (org_id_in, total_in); - end if; -- right now, we completely ignore failure in setting the total to a -- lower number than is subscribed, because we have no prune. prune -- will be in the next version, sometime in the not too distant future, diff --git a/schema/spacewalk/postgres/packages/rhn_server.pkb b/schema/spacewalk/postgres/packages/rhn_server.pkb index 0534010..777699b 100644 --- a/schema/spacewalk/postgres/packages/rhn_server.pkb +++ b/schema/spacewalk/postgres/packages/rhn_server.pkb @@ -59,12 +59,12 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p 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; - exception - when no_data_found - then - return 0; end$$ language plpgsql; create or replace function set_custom_value( @@ -77,7 +77,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p declare key_id_val numeric; begin - select CDK.id into key_id_val + select CDK.id into strict key_id_val from rhnCustomDataKey CDK, rhnServer S where S.id = server_id_in @@ -144,18 +144,17 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p AND user_id = 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 + + 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; + end if; -- now have a snapshot_id to work with... begin @@ -279,6 +278,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p 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) @@ -293,7 +293,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p and (nevra.package_arch_id = sp.package_arch_id or (nevra.package_arch_id is null and sp.package_arch_id is null))); - exit; + exit iloop; exception when unique_violation then if locked = 1 then raise; @@ -341,7 +341,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p -- (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 r(id, prerequisite) as ( + with recursive r(id, prerequisite) as ( select id, prerequisite from rhnAction where id = action_id_in @@ -420,6 +420,10 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p 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 @@ -431,14 +435,13 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p 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; + and USG.user_id = user_id_in; + + if not found then + return 0; + end if; return 1; - exception - when no_data_found - then - return 0; end$$ language plpgsql; -- ******************************************************************* @@ -448,8 +451,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p -- 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 - rhnServerGroupType.label%TYPE) + group_type_in in varchar) returns numeric as $$ declare @@ -499,10 +501,10 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p prov_available numeric; prov_upgrade numeric; prov_sgid numeric; - group_label rhnServerGroupType.label%TYPE; + group_label varchar; group_type numeric; begin - -- frist, group_type = null, because it's easy... + -- 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 @@ -511,7 +513,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p 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; + for update of sg; if group_type is null then if used_slots >= max_slots then @@ -548,7 +550,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p 'virtualization_host', 'virtualization_host_platform') then if used_slots >= max_slots and - (can_server_consume_virt_slot(server_id_in, group_label) != 1) + (rhn_server.can_server_consume_virt_slot(server_id_in, group_label) != 1) then perform rhn_exception.raise_exception('servergroup_max_members'); end if; @@ -561,7 +563,7 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p -- 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 + 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; @@ -648,10 +650,9 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p oid numeric; mgmt_sgid numeric; - label rhnServerGroupType.label%TYPE; + label varchar; group_type numeric; begin - begin select sg.group_type, sg.org_id into group_type, oid from rhnServerGroupMembers sgm, @@ -659,11 +660,11 @@ update pg_settings set setting = 'rhn_server,' || setting where name = 'search_p 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 + for update of sg; + + if not found then perform rhn_exception.raise_exception('server_not_in_group'); - end; + end if; -- do group_type is null first if group_type is null then diff --git a/schema/spacewalk/postgres/packages/rhn_user.pkb b/schema/spacewalk/postgres/packages/rhn_user.pkb index 25058ff..42ab1c9 100644 --- a/schema/spacewalk/postgres/packages/rhn_user.pkb +++ b/schema/spacewalk/postgres/packages/rhn_user.pkb @@ -274,9 +274,9 @@ create or replace rhnUserGroupMembers ugm, rhnUserGroup ug where 1=1 - and ugr.id = user_group_id_in + and ug.id = user_group_id_in and ugm.user_group_id = user_group_id_in - and ugr.group_type = ugt.id + and ug.group_type = ugt.id and ugm.user_id = user_id_in loop delete from rhnUserGroupMembers commit 94bfa8b2132b80e5f1c1fe4c758333af3198ad2d Author: Tom Lane <tgl(a)redhat.com> Date: Thu Apr 30 15:13:35 2009 -0400 Remove no-longer-used rhn_qos package. Jeff confirms this is dead code. diff --git a/schema/spacewalk/postgres/packages/rhn_qos.pkb b/schema/spacewalk/postgres/packages/rhn_qos.pkb deleted file mode 100644 index 67d6b3e..0000000 --- a/schema/spacewalk/postgres/packages/rhn_qos.pkb +++ /dev/null @@ -1,93 +0,0 @@ --- --- Copyright (c) 2008 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- 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_qos; - ---update in pg_setting -update pg_settings set setting = 'rhn_qos,' || setting where name = 'search_path'; - -create or replace function slot_count - (org_id_in in numeric, - label_in in varchar) - returns numeric as $$ - Declare - tally numeric; - begin - select max_members - into tally - from rhnServerGroupType rsgt, - rhnServerGroup rsg - where 1=1 - and rsgt.label = label_in - and rsgt.id = rsg.group_type - and rsg.org_id = org_id_in; - - if not found then - return 0; - end if; - return tally; - - end ; -$$ language plpgsql; - - -Create or replace function basic_slot_count(org_id_in in numeric) - returns numeric as $$ - begin - return slot_count(org_id_in, 'sw_mgr_entitled'); - end ; - $$ language plpgsql; - - - -Create or replace function workgroup_slot_count(org_id_in in numeric) - returns numeric as $$ - begin - return slot_count(org_id_in, 'enterprise_entitled'); - end; - $$ language plpgsql; - - - create or replace function channel_slot_count(org_id_in in numeric, label_in in numeric) - returns numeric as $$ - declare - tally numeric; - begin - select max_members - into tally - from rhnChannelFamily rcf, - rhnOrgChannelFamilyPermissions rcfp - where 1=1 - and rcf.label = label_in - and rcf.id = rcfp.channel_family_id - and rcfp.org_id = org_id_in; - - if not found then - return 0; - end if; - return tally; - end; - $$ language plpgsql; - -Create or replace function as_slot_count(org_id_in in numeric) - returns numeric as $$ - begin - return channel_slot_count(org_id_in, 'rh-advanced-server'); - end ; - $$ language plpgsql; - --- restore the original setting -update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_qos')+1) ) where name = 'search_path'; - diff --git a/schema/spacewalk/postgres/packages/rhn_qos.pks b/schema/spacewalk/postgres/packages/rhn_qos.pks deleted file mode 100644 index d359a61..0000000 --- a/schema/spacewalk/postgres/packages/rhn_qos.pks +++ /dev/null @@ -1,74 +0,0 @@ --- --- Copyright (c) 2008 Red Hat, Inc. --- --- This software is licensed to you under the GNU General Public License, --- version 2 (GPLv2). There is NO WARRANTY for this software, express or --- 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_qos; - ---update in pg_setting -update pg_settings set setting = 'rhn_qos,' || setting where name = 'search_path'; - -create or replace function slot_count -( - org_id_in in numeric, - label_in in varchar -) -returns numeric -as $$ -begin - raise exception 'Stub called, must be replace by .pkb'; - return 0; -end; -$$ language plpgsql; - - -create or replace function basic_slot_count(org_id_in in numeric) -returns numeric -as $$ -begin - raise exception 'Stub called, must be replace by .pkb'; - return 0; -end; -$$ language plpgsql; - -create or replace function workgroup_slot_count(org_id_in in numeric) - returns numeric -as $$ -begin - raise exception 'Stub called, must be replace by .pkb'; - return 0; -end; -$$ language plpgsql; - - -create or replace function channel_slot_count(org_id_in in numeric, label_in in numeric) -returns numeric -as $$ -begin - raise exception 'Stub called, must be replace by .pkb'; - return 0; -end; -$$ language plpgsql; - -create or replace function as_slot_count(org_id_in in numeric) -returns numeric -as $$ -begin - raise exception 'Stub called, must be replace by .pkb'; - return 0; -end; -$$ language plpgsql; - --- restore the original setting -update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_qos')+1) ) where name = 'search_path'; - commit 47754b3014b8f6ac8ed6c312c871a08f9801b763 Author: Tom Lane <tgl(a)redhat.com> Date: Thu Apr 30 15:11:12 2009 -0400 Fix spelling errors. diff --git a/schema/spacewalk/common/data/rhnException.sql b/schema/spacewalk/common/data/rhnException.sql index 69b12d9..77c1cc9 100644 --- a/schema/spacewalk/common/data/rhnException.sql +++ b/schema/spacewalk/common/data/rhnException.sql @@ -42,7 +42,7 @@ insert into rhnException values (-20238, 'channel_unsubscribe_no_family','Attemp insert into rhnException values (-20239, 'arch_not_found','Architecture could not be found'); insert into rhnException values (-20240, 'channel_consent_no_license','No license agreement exists for that channel'); -insert into rhnException values (-20241, 'channel_subscrib_no_consent','Channel requires consent to license for subscription'); +insert into rhnException values (-20241, 'channel_subscribe_no_consent','Channel requires consent to license for subscription'); insert into rhnException values (-20242, 'channel_arch_not_found','Channel architecture could not be found'); insert into rhnException values (-20243, 'package_arch_not_found','Package architecture could not be found'); @@ -87,7 +87,7 @@ insert into rhnException values (-20278, 'webreg_duplicate', 'Registration numbe insert into rhnException values (-20279, 'webreg_not_active', 'Registration number is not active'); insert into rhnException values (-20280, 'webreg_sync_error', 'Error synchronizing, xxrh_oai_wrapper.sync_registration_uber'); insert into rhnException values (-20281, 'webreg_not_found', 'Registration number does not exist'); -insert into rhnException values (-20282, 'webreg_unkown_error', 'Unknown error during web registration'); +insert into rhnException values (-20282, 'webreg_unknown_error', 'Unknown error during web registration'); insert into rhnException values (-20283, 'invalid_feature', 'The specified feature does not exist'); insert into rhnException values (-20284, 'invalid_base_entitlement', 'The base entitlement is not valid for adding on other entitlements'); insert into rhnException values (-20285, 'invalid_addon_entitlement', 'The addon entitlement is not valid for adding onto other entitlements'); commit af64bdc44b3e9dadb8e72fed215109cd3d722d09 Author: Tom Lane <tgl(a)redhat.com> Date: Thu Apr 30 15:10:03 2009 -0400 First-order translation of rhn_entitlements package. Passes syntax checks, but untested. diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pkb b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb new file mode 100644 index 0000000..c6fdcf0 --- /dev/null +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pkb @@ -0,0 +1,1692 @@ +-- +-- Copyright (c) 2008 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- 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_entitlements; + +-- setup search_path so that these functions are created in appropriate schema. +update pg_settings set setting = 'rhn_entitlements,' || setting where name = 'search_path'; + + -- ******************************************************************* + -- PROCEDURE: remove_org_entitlements + -- + -- Removes both system entitlements and channel subscriptions + -- that are currently assigned to an org and re-assigns to the + -- master org (org_id = 1). + -- + -- When we call this we expect everything to already be unentitled + -- which shoul be handled by delete_org. + -- + -- Called by: delete_org + -- ******************************************************************* + create or replace function remove_org_entitlements ( + org_id_in numeric + ) returns void +as $$ + declare + system_ents cursor for + select sg.id, sg.max_members, sg.group_type + from rhnServerGroup sg + where group_type is not null + and org_id = org_id_in; + + channel_subs cursor for + select pcf.channel_family_id, pcf.max_members + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.org_id is null; + + begin + + for system_ent in system_ents loop + update rhnServerGroup + set max_members = max_members + system_ent.max_members + where org_id = 1 + and group_type = system_ent.group_type; + end loop; + + update rhnServerGroup + set max_members = 0 + where org_id = org_id_in; + + for channel_sub in channel_subs loop + update rhnPrivateChannelFamily + set max_members = max_members + channel_sub.max_members + where org_id = 1 + and channel_family_id = channel_sub.channel_family_id; + end loop; + + update rhnPrivateChannelFamily + set max_members = 0 + where org_id = org_id_in; + + end$$ +language plpgsql; + + create or replace function entitlement_grants_service ( + entitlement_in in varchar, + service_level_in in varchar + ) returns numeric +as $$ + begin + if service_level_in = 'provisioning' then + if entitlement_in = 'provisioning_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'management' then + if entitlement_in = 'enterprise_entitled' then + return 1; + else + return 0; + end if; + elsif service_level_in = 'monitoring' then + if entitlement_in = 'monitoring_entitled' then + return 1; + end if; + elsif service_level_in = 'updates' then + return 1; + else + return 0; + end if; + end$$ +language plpgsql; + + create or replace function lookup_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ + declare + server_groups cursor for + select sg.id server_group_id + from rhnServerGroup sg, + rhnServerGroupType sgt + where sgt.label = type_label_in + and sgt.id = sg.group_type + and sg.org_id = org_id_in; + begin + for sg in server_groups loop + return sg.server_group_id; + end loop; + return rhn_entitlements.create_entitlement_group( + org_id_in, + type_label_in + ); + end$$ +language plpgsql; + + create or replace function create_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ + declare + sg_id_val numeric; + begin + select nextval('rhn_server_group_id_seq') + into sg_id_val; + + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id + ) ( + select sg_id_val, sgt.label, sgt.label, + 0, 0, sgt.id, org_id_in + from rhnServerGroupType sgt + where sgt.label = type_label_in + ); + + return sg_id_val; + end$$ +language plpgsql; + + create or replace function can_entitle_server ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ + declare + addon_servergroups cursor (base_label_in varchar, + addon_label_in varchar) for + select + addon_id + from + rhnSGTypeBaseAddonCompat + where base_id = lookup_sg_type (base_label_in) + and addon_id = lookup_sg_type (addon_label_in); + + previous_ent varchar[]; + is_base_in char := 'N'; + is_base_current char := 'N'; + i numeric := 0; + sgid numeric := 0; + + begin + + previous_ent := rhn_entitlements.get_server_entitlement(server_id_in); + + select distinct is_base + into is_base_in + from rhnServerGroupType + where label = type_label_in; + + if array_upper(previous_ent, 1) = 0 then + if (is_base_in = 'Y' and rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid)) then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- there are previous ents, first make sure we're not trying to entitle a base ent + elsif is_base_in = 'Y' then + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + + -- it must be an addon, so proceed with the entitlement + else + + -- find the servers base ent + is_base_current := 'N'; + i := 0; + while is_base_current = 'N' and i < array_upper(previous_ent, 1) + loop + i := i + 1; + select is_base + into is_base_current + from rhnServerGroupType + where label = previous_ent[i]; + end loop; + + -- never found a base ent, that would be strange + if is_base_current = 'N' then + -- rhn_exception.raise_exception ('invalid_base_entitlement'); + return 0; + end if; + + -- this for loop verifies the validity of the addon path + for addon_servergroup in addon_servergroups (previous_ent[i], type_label_in) loop + -- find an appropriate sgid for the addon and entitle the server + if rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid) then + -- rhn_server.insert_into_servergroup (server_id_in, sgid); + return 1; + else + -- rhn_exception.raise_exception ('invalid_addon_entitlement'); + return 0; + end if; + end loop; + + end if; + + return 0; + + end$$ +language plpgsql; + + create or replace function can_switch_base ( + server_id_in in integer, + type_label_in in varchar + ) + returns numeric +as $$ + declare + type_label_in_is_base char(1); + sgid numeric; + + begin + + begin + select is_base into type_label_in_is_base + from rhnServerGroupType + where label = type_label_in; + exception + when no_data_found then + perform rhn_exception.raise_exception ( 'invalid_entitlement' ); + end; + + if type_label_in_is_base = 'N' then + perform rhn_exception.raise_exception ( 'invalid_entitlement' ); + elsif rhn_entitlements.find_compatible_sg ( server_id_in, + type_label_in, sgid ) then + return 1; + else + return 0; + end if; + + end$$ +language plpgsql; + + -- returns NULL if no match found; this is different from the Oracle coding + create or replace function find_compatible_sg ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ + declare + servergroups cursor for + select sg.id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerServerGroupArchCompat ssgac + where s.id = server_id_in + and s.org_id = sg.org_id + and sgt.label = type_label_in + and sg.group_type = sgt.id + and ssgac.server_group_type = sgt.id + and ssgac.server_arch_id = s.server_arch_id + and not exists ( + select 1 + from rhnServerGroupMembers sgm + where sgm.server_group_id = sg.id + and sgm.server_id = s.id); + + + begin + for servergroup in servergroups loop + return servergroup.id; + end loop; + + --no servergroup found + return NULL; + end$$ +language plpgsql; + + create or replace function entitle_server ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns void +as $$ + declare + sgid numeric := 0; + is_virt numeric := 0; + + begin + + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + if is_virt = 0 and (type_label_in = 'virtualization_host' or + type_label_in = 'virtualization_host_platform') then + + is_virt := 1; + end if; + + + + if rhn_entitlements.can_entitle_server(server_id_in, + type_label_in) = 1 then + if rhn_entitlements.find_compatible_sg (server_id_in, + type_label_in, sgid) then + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'added system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + perform rhn_server.insert_into_servergroup (server_id_in, sgid); + + if is_virt = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + else + perform rhn_exception.raise_exception ('no_available_server_group'); + end if; + else + perform rhn_exception.raise_exception ('invalid_entitlement'); + end if; + end$$ +language plpgsql; + + create or replace function remove_server_entitlement ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled', + repoll_virt_guests in numeric default 1 + ) returns void +as $$ + declare + group_id numeric; + type_is_base char; + is_virt numeric := 0; + begin + begin + + + -- would be nice if there were a virt attribute of entitlement types, not have to specify 2 different ones... + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + select sg.id, sgt.is_base + into group_id, type_is_base + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnServer s + where s.id = server_id_in + and s.id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.org_id = s.org_id + and sgt.label = type_label_in + and sgt.id = sg.group_type; + + if ( type_is_base = 'Y' ) then + -- unentitle_server should handle everything, don't really need to do anything else special here + perform unentitle_server ( server_id_in ); + else + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'removed system entitlement ', + case type_label_in + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platforrm' end ); + + perform rhn_server.delete_from_servergroup(server_id_in, group_id); + + -- special case: clean up related monitornig data + if type_label_in = 'monitoring_entitled' then + DELETE + FROM state_change + WHERE o_id IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + DELETE + FROM time_series + WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1, + (INSTR(o_id, '-', INSTR(o_id, '-') + 1) - INSTR(o_id, '-')) - 1) + IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + DELETE + FROM rhn_probe + WHERE recid IN (SELECT probe_id + FROM rhn_check_probe + WHERE host_id = server_id_in); + end if; + + if is_virt = 1 and repoll_virt_guests = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + end if; + + exception + when no_data_found then + perform rhn_exception.raise_exception('invalid_server_group_member'); + end; + + end$$ +language plpgsql; + + create or replace function unentitle_server ( + server_id_in in numeric + ) returns void +as $$ + declare + servergroups cursor for + select distinct sgt.label, sg.id server_group_id + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServer s, + rhnServerGroupMembers sgm + where s.id = server_id_in + and s.org_id = sg.org_id + and sg.group_type = sgt.id + and sgm.server_group_id = sg.id + and sgm.server_id = s.id; + + is_virt numeric := 0; + + begin + + begin + select 1 into is_virt + from rhnServerEntitlementView + where server_id = server_id_in + and label in ('virtualization_host', 'virtualization_host_platform'); + exception + when no_data_found then + is_virt := 0; + end; + + for servergroup in servergroups loop + + insert into rhnServerHistory ( id, server_id, summary, details ) + values ( nextval('rhn_event_id_seq'), server_id_in, + 'removed system entitlement ', + case servergroup.label + when 'enterprise_entitled' then 'Management' + when 'sw_mgr_entitled' then 'Update' + when 'provisioning_entitled' then 'Provisioning' + when 'monitoring_entitled' then 'Monitoring' + when 'virtualization_host' then 'Virtualization' + when 'virtualization_host_platform' then + 'Virtualization Platform' end ); + + perform rhn_server.delete_from_servergroup(server_id_in, + servergroup.server_group_id ); + end loop; + + if is_virt = 1 then + perform rhn_entitlements.repoll_virt_guest_entitlements(server_id_in); + end if; + + end$$ +language plpgsql; + + + -- ******************************************************************* + -- PROCEDURE: repoll_virt_guest_entitlements + -- + -- Whenever we add/remove a virtualization_host* entitlement from + -- a host, we can call this procedure to update what type of slots + -- the guests are consuming. + -- + -- If you're removing the entitlement, it's + -- possible the guests will become unentitled if you don't have enough + -- physical slots to cover them. + -- + -- If you're adding the entitlement, you end up freeing up physical + -- slots for other systems. + -- + -- ******************************************************************* + create or replace function repoll_virt_guest_entitlements( + server_id_in in numeric + ) returns void +as $$ + declare + -- All channel families associated with the guests of server_id_in + families cursor for + select distinct cfs.channel_family_id + from + rhnChannelFamilyServers cfs, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = cfs.server_id; + + -- All of server group types associated with the guests of + -- server_id_in + group_types cursor for + select distinct sg.group_type, sgt.label + from + rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + + -- Virtual servers from a certain family belonging to a specific + -- host that are consuming physical channel slots over the limit. + virt_servers_cfam cursor(family_id_in numeric, quantity_in numeric) for + select vi.virtual_system_id + from + rhnChannelFamilyMembers cfm, + rhnServerChannel sc, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sc.server_id + and sc.channel_id = cfm.channel_id + and cfm.channel_family_id = family_id_in + order by sc.modified desc + limit quantity_in; + + -- Virtual servers from a certain family belonging to a specific + -- host that are consuming physical system slots over the limit. + virt_servers_sgt cursor(group_type_in numeric, quantity_in numeric) for + select vi.virtual_system_id + from + rhnServerGroup sg, + rhnServerGroupMembers sgm, + rhnVirtualInstance vi + where + vi.host_system_id = server_id_in + and vi.virtual_system_id = sgm.server_id + and sgm.server_group_id = sg.id + and sg.group_type = group_type_in + order by sgm.modified desc + limit quantity_in; + + org_id_val numeric; + max_members_val numeric; + current_members_calc numeric; + sg_id numeric; + + begin + + select org_id + into org_id_val + from rhnServer + where id = server_id_in; + + -- deal w/ channel entitlements first ... + for family in families loop + -- get the current (physical) members of the family + current_members_calc := + rhn_channel.channel_family_current_members(family.channel_family_id, + org_id_val); -- fixed transposed args + + -- get the max members of the family + select max_members + into max_members_val + from rhnPrivateChannelFamily + where channel_family_id = family.channel_family_id + and org_id = org_id_val; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, so we'll + -- unsubscribe guests from the host first. + + -- hm, i don't think max_members - current_members_calc yielding a negative number + -- will work w/ rownum, swaping 'em in the body of this if... + for virt_server in virt_servers_cfam(family.channel_family_id, + current_members_calc - max_members_val) loop + + perform rhn_channel.unsubscribe_server_from_family( + virt_server.virtual_system_id, + family.channel_family_id); + end loop; + + -- if we're still over the limit, which would be odd, + -- just prune the group to max_members + -- + -- er... wouldn't we actually have to refresh the values of + -- current_members_calc and max_members_val to actually ever + -- *skip this?? + if current_members_calc > max_members_val then + -- argh, transposed again?! + perform rhn_entitlements.set_family_count(org_id_val, + family.channel_family_id, + max_members_val); + end if; + + end if; + + -- update current_members for the family. This will set the value + -- to reflect adding/removing the entitlement. + -- + -- what's the difference of doing this vs the unavoidable set_family_count above? + perform rhn_channel.update_family_counts(family.channel_family_id, + org_id_val); + end loop; + + for a_group_type in group_types loop + -- get the current *physical* members of the system entitlement type for the org... + -- + -- unlike channel families, it appears the standard rhnServerGroup.max_members represents + -- *physical* slots, vs physical+virt ... boy that's confusing... + + select max_members, id + into max_members_val, sg_id + from rhnServerGroup + where group_type = a_group_type.group_type + and org_id = org_id_val; + + + select count(sep.server_id) into current_members_calc + from rhnServerEntitlementPhysical sep + where sep.server_group_id = sg_id + and sep.server_group_type_id = a_group_type.group_type; + + if current_members_calc > max_members_val then + -- A virtualization_host* ent must have been removed, and we're over the limit, so unsubscribe guests + for virt_server in virt_servers_sgt(a_group_type.group_type, + current_members_calc - max_members_val) loop + perform rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label); + + -- decrement current_members_calc, we'll use it to reset current_members for the group at the end... + current_members_calc := current_members_calc - 1; + end loop; + + end if; + + update rhnServerGroup set current_members = current_members_calc + where org_id = org_id_val + and group_type = a_group_type.group_type; + + -- I think that's all the house-keeping we have to do... + end loop; + + end$$ +language plpgsql; + + create or replace function get_server_entitlement ( + server_id_in in numeric + ) returns varchar[] +as $$ + declare + server_groups cursor for + select sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_id = server_id_in + and sg.id = sgm.server_group_id + and sgt.id = sg.group_type + and sgt.label in ( + 'sw_mgr_entitled','enterprise_entitled', + 'provisioning_entitled', 'nonlinux_entitled', + 'monitoring_entitled', 'virtualization_host', + 'virtualization_host_platform' + ); + + ent_array varchar[]; + + begin + + ent_array := '{}'; + + for sg in server_groups loop + ent_array := ent_array || sg.label; + end loop; + + return ent_array; + + end$$ +language plpgsql; + + -- this desperately needs to be table driven. + create or replace function modify_org_service ( + org_id_in in numeric, + service_label_in in varchar, + enable_in in char + ) returns void +as $$ + declare + roles_to_process varchar[]; + roles cursor(role_label_in varchar) for + select label, id + from rhnUserGroupType + where label = role_label_in; + org_roles cursor(role_label_in varchar) for + select 1 + from rhnUserGroup ug, + rhnUserGroupType ugt + where ugt.label = role_label_in + and ug.org_id = org_id_in + and ugt.id = ug.group_type; + + ents_to_process varchar[]; + ents cursor(ent_label_in varchar) for + select label, id + from rhnOrgEntitlementType + where label = ent_label_in; + org_ents cursor(ent_label_in varchar) for + select 1 + from rhnOrgEntitlements oe, + rhnOrgEntitlementType oet + where oet.label = ent_label_in + and oe.org_id = org_id_in + and oet.id = oe.entitlement_id; + create_row char(1); + begin + ents_to_process := '{}'; + roles_to_process := '{}'; + -- a bit kludgy, but only for 3.4 really. Certainly no + -- worse than the old code... + if service_label_in = 'enterprise' or + service_label_in = 'management' then + ents_to_process := ents_to_process || 'sw_mgr_enterprise'; + + roles_to_process := roles_to_process || 'org_admin'; + + roles_to_process := roles_to_process || 'system_group_admin'; + + roles_to_process := roles_to_process || 'activation_key_admin'; + + roles_to_process := roles_to_process || 'org_applicant'; + elsif service_label_in = 'provisioning' then + ents_to_process := ents_to_process || 'rhn_provisioning'; + + roles_to_process := roles_to_process || 'system_group_admin'; + + roles_to_process := roles_to_process || 'activation_key_admin'; + + roles_to_process := roles_to_process || 'config_admin'; + -- another nasty special case... + if enable_in = 'Y' then + ents_to_process := ents_to_process || 'sw_mgr_enterprise'; + end if; + elsif service_label_in = 'monitoring' then + ents_to_process := ents_to_process || 'rhn_monitor'; + + roles_to_process := roles_to_process || 'monitoring_admin'; + elsif service_label_in = 'virtualization' then + ents_to_process := ents_to_process || 'rhn_virtualization'; + + roles_to_process := roles_to_process || 'config_admin'; + elsif service_label_in = 'virtualization_platform' then + ents_to_process := ents_to_process || 'rhn_virtualization_platform'; + roles_to_process := roles_to_process || 'config_admin'; + elsif service_label_in = 'nonlinux' then + ents_to_process := ents_to_process || 'rhn_nonlinux'; + roles_to_process := roles_to_process || 'config_admin'; + end if; + + if enable_in = 'Y' then + for i in 1..array_upper(ents_to_process, 1) loop + for ent in ents(ents_to_process[i]) loop + create_row := 'Y'; + for oe in org_ents(ent.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnOrgEntitlements(org_id, entitlement_id) + values (org_id_in, ent.id); + end if; + end loop; + end loop; + for i in 1..array_upper(roles_to_process, 1) loop + for role in roles(roles_to_process[i]) loop + create_row := 'Y'; + for o_r in org_roles(role.label) loop + create_row := 'N'; + end loop; + if create_row = 'Y' then + insert into rhnUserGroup( + id, name, description, current_members, + group_type, org_id + ) ( + select nextval('rhn_user_group_id_seq'), + ugt.name || 's', + ugt.name || 's for Org ' || + o.name || ' ('|| o.id ||')', + 0, ugt.id, o.id + from rhnUserGroupType ugt, + web_customer o + where o.id = org_id_in + and ugt.id = role.id + ); + end if; + end loop; + end loop; + else + for i in 1..array_upper(ents_to_process, 1) loop + for ent in ents(ents_to_process[i]) loop + delete from rhnOrgEntitlements + where org_id = org_id_in + and entitlement_id = ent.id; + end loop; + end loop; + end if; + end$$ +language plpgsql; + + create or replace function set_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'enterprise', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'provisioning', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_monitoring ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'monitoring', 'Y'); + end$$ +language plpgsql; + + create or replace function set_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'nonlinux', 'Y'); + end$$ +language plpgsql; + + create or replace function unset_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'enterprise', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'provisioning', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_monitoring ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'monitoring', 'N'); + end$$ +language plpgsql; + + create or replace function unset_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ + begin + perform modify_org_service(customer_id_in, 'nonlinux', 'N'); + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: prune_group + -- Unsubscribes servers consuming physical slots that over the org's + -- limit. + -- Called by: set_group_count, prune_everything, repoll_virt_guest_entitlements + -- ******************************************************************* + create or replace function prune_group ( + group_id_in in numeric, + type_in in char, + quantity_in in numeric + ) returns void +as $$ + declare + usergroups cursor for + select user_id, user_group_id, ugt.label + from rhnUserGroupType ugt, + rhnUserGroup ug, + rhnUserGroupMembers ugm + where 1=1 + and ugm.user_group_id = group_id_in + and ugm.user_id in ( + select user_id + from rhnUserGroupMembers + where user_group_id = group_id_in + order by modified asc + offset quantity_in + ) + and ugm.user_group_id = ug.id + and ug.group_type = ugt.id; + servergroups cursor for + select server_id, server_group_id, sgt.id group_type_id, sgt.label + from rhnServerGroupType sgt, + rhnServerGroup sg, + rhnServerGroupMembers sgm + where 1=1 + and sgm.server_group_id = group_id_in + and sgm.server_id in ( + select sep.server_id + from + rhnServerEntitlementPhysical sep + where + sep.server_group_id = group_id_in + order by sep.modified asc + offset quantity_in + ) + and sgm.server_group_id = sg.id + and sg.group_type = sgt.id; + type_is_base char; + begin + if type_in = 'U' then + update rhnUserGroup + set max_members = quantity_in + where id = group_id_in; + + for ug in usergroups loop + perform rhn_user.remove_from_usergroup(ug.user_id, ug.user_group_id); + end loop; + elsif type_in = 'S' then + update rhnServerGroup + set max_members = quantity_in + where id = group_id_in; + + for sg in servergroups loop + perform rhn_entitlements.remove_server_entitlement(sg.server_id, sg.label); + + select is_base + into type_is_base + from rhnServerGroupType sgt + where sgt.id = sg.group_type_id; + + -- if we're removing a base ent, then be sure to + -- remove the server's channel subscriptions. + if ( type_is_base = 'Y' ) then + perform rhn_channel.clear_subscriptions(sg.server_id); + end if; + + end loop; + end if; + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: assign_system_entitlement + -- + -- Moves system entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_group_count + -- ******************************************************************* + create or replace function assign_system_entitlement( + group_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + to_org_prev_ent_count numeric; + new_ent_count numeric; + new_quantity numeric; + group_type numeric; + begin + + begin + select max_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = from_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + exception + when NO_DATA_FOUND then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end; + + begin + select max_members + into to_org_prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.org_id = to_org_id_in + and sg.group_type = sgt.id + and sgt.label = group_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count := 0; + end; + + begin + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + exception + when NO_DATA_FOUND then + perform rhn_exception.raise_exception( + 'invalid_server_group'); + end; + + new_ent_count := prev_ent_count - quantity_in; + + if prev_ent_count > new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + if new_ent_count < 0 then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + + perform rhn_entitlements.set_group_count(from_org_id_in, + 'S', + group_type, + new_ent_count); + + perform rhn_entitlements.set_group_count(to_org_id_in, + 'S', + group_type, + new_quantity); + + -- Create or delete the entries in rhnOrgEntitlementType + if group_label_in = 'enterprise_entitled' then + if new_quantity > 0 then + perform set_customer_enterprise(to_org_id_in); + else + perform unset_customer_enterprise(to_org_id_in); + end if; + end if; + + if group_label_in = 'provisioning_entitled' then + if new_quantity > 0 then + perform set_customer_provisioning(to_org_id_in); + else + perform unset_customer_provisioning(to_org_id_in); + end if; + end if; + + if group_label_in = 'monitoring_entitled' then + if new_quantity > 0 then + perform set_customer_monitoring(to_org_id_in); + else + perform unset_customer_monitoring(to_org_id_in); + end if; + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: assign_channel_entitlement + -- + -- Moves channel entitlements from from_org_id_in to to_org_id_in. + -- Can raise not_enough_entitlements_in_base_org if from_org_id_in + -- does not have enough entitlements to cover the move. + -- Takes care of unentitling systems if necessary by calling + -- set_family_count + -- ******************************************************************* + create or replace function assign_channel_entitlement( + channel_family_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + new_ent_count numeric; + to_org_prev_ent_count numeric; + new_quantity numeric; + cfam_id numeric; + begin + + begin + select max_members + into prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = from_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end; + + begin + select max_members + into to_org_prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = to_org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + to_org_prev_ent_count := 0; + end; + + + begin + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + exception + when NO_DATA_FOUND then + perform rhn_exception.raise_exception( + 'invalid_channel_family'); + end; + + new_ent_count := prev_ent_count - quantity_in; + + if prev_ent_count > new_ent_count then + new_quantity := to_org_prev_ent_count + quantity_in; + end if; + + + if new_ent_count < 0 then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + end if; + + perform rhn_entitlements.set_family_count(from_org_id_in, + cfam_id, + new_ent_count); + + perform rhn_entitlements.set_family_count(to_org_id_in, + cfam_id, + new_quantity); + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: activate_system_entitlement + -- + -- Sets the values in rhnServerGroup for a given rhnServerGroupType. + -- + -- Calls: set_group_count to update, prune, or create the group. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if all entitlements + -- in the org are used so the free entitlements would not cover + -- the difference when descreasing the number of entitlements. + -- ******************************************************************* + create or replace function activate_system_entitlement( + org_id_in in numeric, + group_label_in in varchar, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + prev_ent_count_sum numeric; + group_type numeric; + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + begin + select current_members + into prev_ent_count + from rhnServerGroupType sgt, + rhnServerGroup sg + where sg.group_type = sgt.id + and sgt.label = group_label_in + and sg.org_id = org_id_in; + exception + when NO_DATA_FOUND then + prev_ent_count := 0; + end; + + begin + select id + into group_type + from rhnServerGroupType + where label = group_label_in; + exception + when NO_DATA_FOUND then + perform rhn_exception.raise_exception( + 'invalid_server_group'); + end; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the allocated count in this org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + perform rhn_entitlements.set_group_count(org_id_in, + 'S', + group_type, + quantity_in); + end if; + + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: activate_channel_entitlement + -- + -- Calls: set_family_count to update, prune, or create the family + -- permission bucket. + -- Called by: the code that activates a satellite cert. + -- + -- Raises not_enough_entitlements_in_base_org if there are not enough + -- entitlements in the org to cover the difference when you are + -- descreasing the number of entitlements. + -- + -- The backend code in Python is expected to do whatever arithmetics + -- is needed. + -- ******************************************************************* + create or replace function activate_channel_entitlement( + org_id_in in numeric, + channel_family_label_in in varchar, + quantity_in in numeric + ) returns void +as $$ + declare + prev_ent_count numeric; + prev_ent_count_sum numeric; + cfam_id numeric; + begin + + -- Fetch the current entitlement count for the org + -- into prev_ent_count + begin + select current_members + into prev_ent_count + from rhnChannelFamily cf, + rhnPrivateChannelFamily pcf + where pcf.org_id = org_id_in + and pcf.channel_family_id = cf.id + and cf.label = channel_family_label_in; + exception + when NO_DATA_FOUND then + prev_ent_count := 0; + end; + + begin + select id + into cfam_id + from rhnChannelFamily + where label = channel_family_label_in; + exception + when NO_DATA_FOUND then + perform rhn_exception.raise_exception( + 'invalid_channel_family'); + end; + + -- If we're setting the total entitlemnt count to a lower value, + -- and that value is less than the count in that one org, + -- we need to raise an exception. + if quantity_in < prev_ent_count then + perform rhn_exception.raise_exception( + 'not_enough_entitlements_in_base_org'); + else + perform rhn_entitlements.set_family_count(org_id_in, + cfam_id, + quantity_in); + end if; + + end$$ +language plpgsql; + + create or replace function set_group_count ( + customer_id_in in numeric, -- customer_id + type_in in char, -- 'U' or 'S' + group_type_in in numeric, -- rhn[User|Server]GroupType.id + quantity_in in numeric -- quantity + ) returns void +as $$ + declare + group_id numeric; + quantity numeric; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + + if type_in = 'U' then + select rug.id + into group_id + from rhnUserGroup rug + where 1=1 + and rug.org_id = customer_id_in + and rug.group_type = group_type_in; + elsif type_in = 'S' then + select rsg.id + into group_id + from rhnServerGroup rsg + where 1=1 + and rsg.org_id = customer_id_in + and rsg.group_type = group_type_in; + end if; + + perform rhn_entitlements.prune_group( + group_id, + type_in, + quantity + ); + exception + when no_data_found then + if type_in = 'U' then + insert into rhnUserGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select nextval('rhn_user_group_id_seq'), name, name, + quantity, 0, id, customer_id_in, + sysdate, sysdate + from rhnUserGroupType + where id = group_type_in + ); + elsif type_in = 'S' then + insert into rhnServerGroup ( + id, name, description, max_members, current_members, + group_type, org_id, created, modified + ) ( + select nextval('rhn_server_group_id_seq'), name, name, + quantity, 0, id, customer_id_in, + sysdate, sysdate + from rhnServerGroupType + where id = group_type_in + ); + end if; + end$$ +language plpgsql; + + -- ******************************************************************* + -- PROCEDURE: prune_family + -- Unsubscribes servers consuming physical slots from the channel family + -- that are over the org's limit. + -- Called by: set_family_count, prune_everything + -- ******************************************************************* + create or replace function prune_family ( + customer_id_in in numeric, + channel_family_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ + declare + serverchannels cursor for + select sc.server_id, + sc.channel_id + from rhnServerChannel sc, + rhnChannelFamilyMembers cfm + where 1=1 + and cfm.channel_family_id = channel_family_id_in + and cfm.channel_id = sc.channel_id + and server_id in ( + select rs.id as server_id + from + rhnServerChannel rsc, + rhnChannelFamilyMembers rcfm, + rhnServer rs + where 1=1 + and rs.org_id = customer_id_in + and rs.id = rsc.server_id + and rsc.channel_id = rcfm.channel_id + and rcfm.channel_family_id = + channel_family_id_in + -- we only want to grab servers consuming + -- physical slots. + and exists ( + select 1 + from rhnChannelFamilyServerPhysical cfsp + where cfsp.server_id = rs.id + and cfsp.channel_family_id = + channel_family_id_in + ) + order by rcfm.modified asc + offset quantity_in + ); + begin + -- if we get a null customer_id, this is completely bogus. + if customer_id_in is null then + return; + end if; + + update rhnPrivateChannelFamily + set max_members = quantity_in + where 1=1 + and org_id = customer_id_in + and channel_family_id = channel_family_id_in; + + for sc in serverchannels loop + perform rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1); + end loop; + end$$ +language plpgsql; + + create or replace function set_family_count ( + customer_id_in in numeric, -- customer_id + channel_family_id_in in numeric, -- 246 + quantity_in in numeric -- 3 + ) returns void +as $$ + declare + privperms cursor for + select 1 + from rhnPrivateChannelFamily + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + pubperms cursor for + select o.id org_id + from web_customer o, + rhnPublicChannelFamily pcf + where pcf.channel_family_id = channel_family_id_in; + quantity numeric; + done numeric := 0; + begin + quantity := quantity_in; + if quantity is not null and quantity < 0 then + quantity := 0; + end if; + + if customer_id_in is not null then + for perm in privperms loop + perform rhn_entitlements.prune_family( + customer_id_in, + channel_family_id_in, + quantity + ); + update rhnPrivateChannelFamily + set max_members = quantity + where org_id = customer_id_in + and channel_family_id = channel_family_id_in; + return; + end loop; + + insert into rhnPrivateChannelFamily ( + channel_family_id, org_id, max_members, current_members + ) values ( + channel_family_id_in, customer_id_in, quantity, 0 + ); + return; + end if; + + for perm in pubperms loop + if quantity = 0 then + perform rhn_entitlements.prune_family( + perm.org_id, + channel_family_id_in, + quantity + ); + if done = 0 then + delete from rhnPublicChannelFamily + where channel_family_id = channel_family_id_in; + end if; + end if; + done := 1; + end loop; + -- if done's not 1, then we don't have any entitlements + if done != 1 then + insert into rhnPublicChannelFamily ( + channel_family_id + ) values ( + channel_family_id_in + ); + end if; + end$$ +language plpgsql; + + -- this expects quantity_in to be the number of available slots, not the + -- max_members of the server group. If you give it too many, it'll fail + -- and raise servergroup_max_members. + -- We should NEVER run this unless we're SURE that we won't + -- be violating the max. + create or replace function entitle_last_modified_servers ( + customer_id_in in numeric, -- customer_id + type_label_in in varchar, -- 'enterprise_entitled' + quantity_in in numeric -- 3 + ) returns void +as $$ + declare + -- find the servers that aren't currently in slots + servers cursor(cid_in numeric, quant_in numeric) for + select rs.id as server_id + from rhnServer rs + where 1=1 + and rs.org_id = cid_in + and not exists ( + select 1 + from rhnServerGroup sg, + rhnServerGroupMembers rsgm + where rsgm.server_id = rs.id + and rsgm.server_group_id = sg.id + and sg.group_type is not null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = + rs.id + ) + order by modified desc + limit quant_in; + begin + for server in servers(customer_id_in, quantity_in) loop + perform rhn_entitlements.entitle_server(server.server_id, type_label_in); + end loop; + end$$ +language plpgsql; + + create or replace function prune_everything ( + customer_id_in in numeric + ) returns void +as $$ + declare + everything cursor for + -- all our server groups + select sg.id id, + 'S' as type, + sg.max_members quantity + from rhnServerGroup sg + where sg.org_id = customer_id_in + union + -- all our user groups + select ug.id id, + 'U' as type, + ug.max_members quantity + from rhnUserGroup ug + where ug.org_id = customer_id_in + union ( + -- all the channel families we have perms to + select cfp.channel_family_id id, + 'C' as type, + cfp.max_members quantity + from rhnOrgChannelFamilyPermissions cfp + where cfp.org_id = customer_id_in + union + -- plus all the ones we're using that we have no perms for + select cfm.channel_family_id id, + 'C' as type, + 0 quantity + from rhnChannelFamily cf, + rhnChannelFamilyMembers cfm, + rhnServerChannel sc, + rhnServer s + where s.org_id = customer_id_in + and s.id = sc.server_id + and sc.channel_id = cfm.channel_id + and cfm.channel_family_id = cf.id + and cf.org_id is not null + and cf.org_id != customer_id_in + and not exists ( + select 1 + from rhnOrgChannelFamilyPermissions cfp + where cfp.org_id = customer_id_in + and cfp.channel_family_id = cfm.channel_family_id + ) + ); + begin + for one in everything loop + if one.type in ('U','S') then + perform rhn_entitlements.prune_group(one.id, one.type, one.quantity); + else + perform rhn_entitlements.prune_family(customer_id_in, one.id, one.quantity); + end if; + end loop; + end$$ +language plpgsql; + + create or replace function subscribe_newest_servers ( + customer_id_in in numeric + ) returns void +as $$ + declare + -- find servers without base channels + servers cursor(cid_in numeric) for + select s.id + from rhnServer s + where 1=1 + and s.org_id = cid_in + and not exists ( + select 1 + from rhnChannel c, + rhnServerChannel sc + where sc.server_id = s.id + and sc.channel_id = c.id + and c.parent_channel is null + ) + and not exists ( + select 1 + from rhnVirtualInstance vi + where vi.virtual_system_id = s.id + ) + order by s.modified desc; + channel_id numeric; + begin + for server in servers(customer_id_in) loop + channel_id := rhn_channel.guess_server_base(server.id); + if channel_id is not null then + begin + perform rhn_channel.subscribe_server(server.id, channel_id); + -- exception is really channel_family_no_subscriptions + exception + when others then + null; + end; + end if; + end loop; + end$$ +language plpgsql; + + +-- restore the original setting +update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_entitlements')+1) ) where name = 'search_path'; diff --git a/schema/spacewalk/postgres/packages/rhn_entitlements.pks b/schema/spacewalk/postgres/packages/rhn_entitlements.pks new file mode 100644 index 0000000..f00d3b6 --- /dev/null +++ b/schema/spacewalk/postgres/packages/rhn_entitlements.pks @@ -0,0 +1,308 @@ +-- +-- Copyright (c) 2008 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- 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_entitlements; + +-- setup search_path so that these functions are created in appropriate schema. +update pg_settings set setting = 'rhn_entitlements,' || setting where name = 'search_path'; + + create or replace function remove_org_entitlements ( + org_id_in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function entitlement_grants_service ( + entitlement_in in varchar, + service_level_in in varchar + ) returns numeric +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function lookup_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function create_entitlement_group ( + org_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns numeric +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function can_entitle_server ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function can_switch_base ( + server_id_in in integer, + type_label_in in varchar + ) + returns numeric +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function find_compatible_sg ( + server_id_in in numeric, + type_label_in in varchar + ) + returns numeric +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function entitle_server ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled' + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function remove_server_entitlement ( + server_id_in in numeric, + type_label_in in varchar default 'sw_mgr_entitled', + repoll_virt_guests in numeric default 1 + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function unentitle_server ( + server_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function repoll_virt_guest_entitlements( + server_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function get_server_entitlement ( + server_id_in in numeric + ) returns varchar[] +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function modify_org_service ( + org_id_in in numeric, + service_label_in in varchar, + enable_in in char + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function set_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function set_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function set_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function unset_customer_enterprise ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function unset_customer_provisioning ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function unset_customer_nonlinux ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function assign_system_entitlement( + group_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function assign_channel_entitlement( + channel_family_label_in in varchar, + from_org_id_in in numeric, + to_org_id_in in numeric, + quantity_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function activate_system_entitlement( + org_id_in in numeric, + group_label_in in varchar, + quantity_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function activate_channel_entitlement( + org_id_in in numeric, + channel_family_label_in in varchar, + quantity_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function set_group_count ( + customer_id_in in numeric, -- customer_id + type_in in char, -- 'U' or 'S' + group_type_in in numeric, -- rhn[User|Server]GroupType.id + quantity_in in numeric -- quantity + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function set_family_count ( + customer_id_in in numeric, -- customer_id + channel_family_id_in in numeric, -- 246 + quantity_in in numeric -- 3 + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + -- this makes NO checks that the quantity is within max, + -- so we should NEVER run this unless we KNOW that we won't be + -- violating the max + create or replace function entitle_last_modified_servers ( + customer_id_in in numeric, -- customer_id + type_label_in in varchar, -- 'enterprise_entitled' + quantity_in in numeric -- 3 + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function prune_everything ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + + create or replace function subscribe_newest_servers ( + customer_id_in in numeric + ) returns void +as $$ +BEGIN + RAISE EXCEPTION 'Stub called, must be replaced by .pkb'; +END; +$$ language plpgsql; + +-- restore the original setting +update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_entitlements')+1) ) where name = 'search_path'; commit f48310463f7ed55b24b4e742b9d58182167057f8 Author: Tom Lane <tgl(a)redhat.com> Date: Thu Apr 30 13:20:08 2009 -0400 The rhn_date_manip package is not used anywhere. Rather than try to think about how to fix its API for Postgres (which has not got package variables), I'm just removing it. The code still exists in the oracle subdirectory if anyone ever makes a start at using it. diff --git a/schema/spacewalk/postgres/packages/rhn_date_manip.pkb b/schema/spacewalk/postgres/packages/rhn_date_manip.pkb deleted file mode 100644 index deaeef0..0000000 --- a/schema/spacewalk/postgres/packages/rhn_date_manip.pkb +++ /dev/null @@ -1,59 +0,0 @@ - ---create schema rhn_date_manip; - -update pg_settings set setting = 'rhn_date_manip,' || setting where name = 'search_path'; - -create or replace function get_reporting_period_start() - returns timestamptz as $$ - declare - months_ago numeric; - weeks_ago numeric; - target_date timestamptz; - day_number numeric; - periods_ago numeric; - begin - months_ago := periods_ago/2; - weeks_ago := mod(periods_ago,2); - -- target_date := trunc(add_months(current_timestamp::date,-months_ago)-(7*weeks_ago)); - target_date := trunc((current_timestamp + (-months_ago||' months')::interval)-((7*weeks_ago)||' days')::interval); - day_number := to_char(target_date,'DD')::numeric; - -- squish the date to the 1st or the 16th - if day_number > 16 then - target_date := target_date - ((day_number||' day')::interval - (16||'day')::interval); - else - target_date := target_date - ((day_number||' day')::interval - (1||'day')::interval); - end if; - return target_date; - end ; - $$ language 'plpgsql'; - - - - create or replace function get_reporting_period_end() - returns timestamptz as $$ - declare - months_ago numeric; - weeks_ago numeric; - target_date timestamptz; - day_number numeric; - periods_ago numeric; - begin - months_ago := periods_ago/2; - weeks_ago := mod(periods_ago,2); - - --target_date := trunc(add_months(current_timestamp,-months_ago)-(7*weeks_ago)); - target_date := trunc((current_timestamp + (-months_ago||' months')::interval)-((7*weeks_ago)||' days')::interval); - day_number := to_char(target_date,'DD')::numeric; - -- squish the date to the 1st or the 16th - if day_number > 16 then - target_date := last_day(target_date); - else - target_date := target_date + ((-day_number||' day')::interval + (15||' day')::interval) + ((1||' day')::interval-(1/86400||' day')::interval); - end if; - return target_date; - end ; - $$ language 'plpgsql'; - - --- restore the original setting -update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_date_manip')+1) ) where name = 'search_path'; diff --git a/schema/spacewalk/postgres/packages/rhn_date_manip.pks b/schema/spacewalk/postgres/packages/rhn_date_manip.pks deleted file mode 100644 index 89f657d..0000000 --- a/schema/spacewalk/postgres/packages/rhn_date_manip.pks +++ /dev/null @@ -1,23 +0,0 @@ - -create schema rhn_date_manip; - -update pg_settings set setting = 'rhn_date_manip,' || setting where name = 'search_path'; - -create or replace function get_reporting_period_start() -returns timestamptz -as $$ -begin - raise exception 'Stub called, must be replaced by .pkb'; -end; -$$ language 'plpgsql'; - -create or replace function get_reporting_period_end() -returns timestamptz as -$$ -begin - raise exception 'Stub called, must be replaced by .pkb'; -end; -$$ language 'plpgsql'; - --- restore the original setting -update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_date_manip')+1) ) where name = 'search_path'; commit 984a347f2afbd47756e90584364799dd670b62db Author: Tom Lane <tgl(a)redhat.com> Date: Thu Apr 30 01:49:20 2009 -0400 Some more fixes from manual function testing. diff --git a/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql b/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql index d5e83fa..6de2349 100644 --- a/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql +++ b/schema/spacewalk/postgres/procs/pxt_session_cleanup.sql @@ -30,7 +30,7 @@ as $$ declare sessions cursor (bound_val numeric) for - select rowid from PXTSessions + select ctid from PXTSessions where expires < bound_val; counter numeric := 0; @@ -38,7 +38,7 @@ begin for session in sessions (bound_in) loop - delete from PXTSessions where rowid = session.rowid; + delete from PXTSessions where ctid = session.ctid; counter := counter + 1; diff --git a/schema/spacewalk/postgres/procs/queue_server.sql b/schema/spacewalk/postgres/procs/queue_server.sql index 0436bb7..81e4d41 100644 --- a/schema/spacewalk/postgres/procs/queue_server.sql +++ b/schema/spacewalk/postgres/procs/queue_server.sql @@ -22,16 +22,14 @@ CREATE OR REPLACE FUNCTION -queue_server(server_id_in IN NUMERIC, immediate_in IN NUMERIC) +queue_server(server_id_in IN NUMERIC, immediate_in IN NUMERIC DEFAULT 1) RETURNS VOID AS $$ DECLARE org_id_tmp NUMERIC; - immediate_in_tmp NUMERIC; BEGIN - immediate_in_tmp := immediate_in; - IF immediate_in_tmp > 0 + IF immediate_in > 0 THEN DELETE FROM rhnServerNeededCache WHERE server_id = server_id_in; INSERT INTO rhnServerNeededCache @@ -40,7 +38,8 @@ BEGIN WHERE server_id = server_id_in); ELSE - SELECT org_id INTO org_id_tmp FROM rhnServer WHERE id = server_id_in; + SELECT org_id INTO STRICT org_id_tmp + FROM rhnServer WHERE id = server_id_in; INSERT INTO rhnTaskQueue diff --git a/schema/spacewalk/postgres/procs/queue_server_overloaded.sql b/schema/spacewalk/postgres/procs/queue_server_overloaded.sql deleted file mode 100644 index 304eb1f..0000000 --- a/schema/spacewalk/postgres/procs/queue_server_overloaded.sql +++ /dev/null @@ -1,32 +0,0 @@ --- Creating an overloaded function for queue_server so that it can be called from app with one parameter. - -CREATE OR REPLACE FUNCTION -queue_server(server_id_in IN NUMERIC) -RETURNS VOID -AS -$$ -DECLARE - org_id_tmp NUMERIC; - immediate_in_tmp NUMERIC; -BEGIN - immediate_in_tmp := 1; - IF immediate_in_tmp > 0 - THEN - DELETE FROM rhnServerNeededCache WHERE server_id = server_id_in; - INSERT INTO rhnServerNeededCache - (SELECT server_id, errata_id, package_id - FROM rhnServerNeededView - WHERE server_id = server_id_in); - - ELSE - SELECT org_id INTO org_id_tmp FROM rhnServer WHERE id = server_id_in; - - INSERT - INTO rhnTaskQueue - (org_id, task_name, task_data) - VALUES (org_id_tmp, - 'update_server_errata_cache', - server_id_in); - END IF; -END; -$$ LANGUAGE plpgsql; diff --git a/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql b/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql index ef3aa07..7f57a0c 100644 --- a/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql +++ b/schema/spacewalk/postgres/procs/rhn_synch_probe_state.sql @@ -29,34 +29,33 @@ begin set state = 'PENDING', output = 'Awaiting update' where last_check < ( - select ( - current_timestamp - greatest(15 / 60 / 24, - ((3 * rhn_deployed_probe.check_interval_minutes) / 60 / 24))) + select + current_timestamp - interval '1 minute' * greatest(15, + (3 * rhn_deployed_probe.check_interval_minutes)) from rhn_deployed_probe where rhn_deployed_probe.recid = rhn_probe_state.probe_id ); - - update rhn_multi_scout_threshold - set scout_warning_threshold = (select - decode(scout_warning_threshold_is_all,0, - scout_warning_threshold,count(scout_id)) - from rhn_probe_state p, rhn_multi_scout_threshold t + update rhn_multi_scout_threshold t + set scout_warning_threshold = ( + select + case scout_warning_threshold_is_all + when '0' then scout_warning_threshold + else count(scout_id) end + from rhn_probe_state p where t.probe_id=p.probe_id and state in ('OK', 'WARNING', 'CRITICAL') - group by t.probe_id -) , - - - scout_critical_threshold=( - select - decode(scout_crit_threshold_is_all,0, - scout_critical_threshold,count(scout_id)) - from rhn_probe_state p, rhn_multi_scout_threshold t + group by t.probe_id + ), + scout_critical_threshold = ( + select + case scout_crit_threshold_is_all + when '0' then scout_critical_threshold + else count(scout_id) end + from rhn_probe_state p where t.probe_id=p.probe_id and state in ('OK', 'WARNING', 'CRITICAL') - group by t.probe_id - + group by t.probe_id ); end; $$ diff --git a/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql b/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql index 3535917..582c814 100644 --- a/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql +++ b/schema/spacewalk/postgres/procs/set_ks_session_history_message.sql @@ -44,20 +44,16 @@ declare id_history_items_curs numeric; begin - open states; + for id_states_curs in states loop - fetch states into id_states_curs; - exit when not found; - open history_items(id_states_curs); + for id_history_items_curs in history_items(id_states_curs) loop - fetch history_items into id_history_items_curs; - exit when not found; update rhnKickstartSessionHistory set message = message_in where id = id_history_items; return; end loop; - close history_items; + insert into rhnKickstartSessionHistory ( id, kickstart_session_id, state_id, message ) values ( diff --git a/schema/spacewalk/postgres/procs/truncateCacheQueue.sql b/schema/spacewalk/postgres/procs/truncateCacheQueue.sql index c741990..3e95df1 100644 --- a/schema/spacewalk/postgres/procs/truncateCacheQueue.sql +++ b/schema/spacewalk/postgres/procs/truncateCacheQueue.sql @@ -19,7 +19,7 @@ create or replace function truncateCacheQueue() returns void as $$ begin - execute 'Truncate Table rhnOrgErrataCacheQueue'; + Truncate Table rhnOrgErrataCacheQueue; end; $$ language plpgsql; diff --git a/schema/spacewalk/postgres/triggers/rhnErrata.sql b/schema/spacewalk/postgres/triggers/rhnErrata.sql index 2461d46..57d875a 100644 --- a/schema/spacewalk/postgres/triggers/rhnErrata.sql +++ b/schema/spacewalk/postgres/triggers/rhnErrata.sql @@ -16,11 +16,23 @@ -- -- - -create or replace function rhn_errata_mod_trig_fun() returns trigger +create or replace function rhn_errata_ins_trig_fun() returns trigger as $$ +begin + if ( new.last_modified is null ) then + new.last_modified := current_timestamp; + end if; + new.modified := current_timestamp; + + return new; +end; +$$ language plpgsql; + +create or replace function rhn_errata_upd_trig_fun() returns trigger +as +$$ begin if ( new.last_modified = old.last_modified ) or ( new.last_modified is null ) then @@ -35,7 +47,13 @@ $$ language plpgsql; create trigger -rhn_errata_mod_trig -before insert or update on rhnErrata +rhn_errata_ins_trig +before insert on rhnErrata +for each row +execute procedure rhn_errata_ins_trig_fun(); + +create trigger +rhn_errata_upd_trig +before update on rhnErrata for each row -execute procedure rhn_errata_mod_trig_fun(); +execute procedure rhn_errata_upd_trig_fun();

14 years, 12 months

1
0
0 / 0

Branch 'VADER' - java/code

by Justin Sherrill

java/code/src/com/redhat/rhn/frontend/action/common/test/DownloadActionTest.java | 8 ++++++++ 1 file changed, 8 insertions(+) New commits: commit ce983a7032fb6cae771367b693600aa556d5736e Author: Justin Sherrill <jsherril(a)redhat.com> Date: Thu Apr 30 17:46:21 2009 -0400 unit test fix diff --git a/java/code/src/com/redhat/rhn/frontend/action/common/test/DownloadActionTest.java b/java/code/src/com/redhat/rhn/frontend/action/common/test/DownloadActionTest.java index dd3ca58..f57463c 100644 --- a/java/code/src/com/redhat/rhn/frontend/action/common/test/DownloadActionTest.java +++ b/java/code/src/com/redhat/rhn/frontend/action/common/test/DownloadActionTest.java @@ -57,6 +57,7 @@ public class DownloadActionTest extends RhnMockStrutsTestCase { public void testKsDownload() throws Exception { // /ks/dist/f9-x86_64-distro/images/boot.iso addRequestParameter("url", "/ks/dist/" + tree.getLabel() + "/images/boot.iso"); + request.setQueryString("url=/ks/dist/" + tree.getLabel() + "/images/boot.iso"); actionPerform(); assertNull(getActualForward()); assertEquals("application/octet-stream", getResponse().getContentType()); @@ -77,6 +78,7 @@ public class DownloadActionTest extends RhnMockStrutsTestCase { TestUtils.saveAndFlush(p); addRequestParameter("url", "/ks/dist/" + tree.getLabel() + "/Server/" + fileName); + request.setQueryString("url=/ks/dist/" + tree.getLabel() + "/Server/" + fileName); actionPerform(); // assertEquals("/kickstart/DownloadFile.do", getActualForward()); assertNotNull(request.getAttribute("params")); @@ -94,6 +96,9 @@ public class DownloadActionTest extends RhnMockStrutsTestCase { addRequestParameter("url", "/ks/dist/session/" + encodedSession + "/" + tree.getLabel() + "/images/boot.iso"); + request.setQueryString("url=/ks/dist/session/" + encodedSession + "/" + + tree.getLabel() + "/images/boot.iso"); + actionPerform(); assertNull(getActualForward()); assertNotNull(request.getAttribute("params")); @@ -119,6 +124,8 @@ public class DownloadActionTest extends RhnMockStrutsTestCase { addRequestParameter("url", "/ks/dist/session/" + encodedSession + "/" + tree.getLabel() + "/Server/" + fileName); + request.setQueryString("url=/ks/dist/session/" + encodedSession + "/" + + tree.getLabel() + "/Server/" + fileName); actionPerform(); assertNotNull(request.getAttribute("params")); @@ -136,6 +143,7 @@ public class DownloadActionTest extends RhnMockStrutsTestCase { KickstartSessionTest.createKickstartSession(ksdata, user); TestUtils.saveAndFlush(ksession); addRequestParameter("url", "/ks/dist/" + tree.getLabel() + "/images/"); + request.setQueryString("url=/ks/dist/" + tree.getLabel() + "/images/"); actionPerform(); assertNull(getActualForward()); assertEquals("text/plain", getResponse().getContentType());

14 years, 12 months

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

spacewalk-commits April 2009