backend/satellite_tools/satsync.py | 24 backend/satellite_tools/spacewalk-remove-channel | 13 backend/satellite_tools/sync_handlers.py | 34 backend/server/rhnSQL/__init__.py | 4 backend/server/rhnSQL/driver_cx_Oracle.py | 2 backend/server/rhnSQL/driver_postgresql.py | 17 backend/server/rhnServer/server_lib.py | 22 backend/spacewalk-backend.spec | 10 rel-eng/packages/spacewalk-backend | 2 rel-eng/packages/spacewalk-schema | 2 schema/spacewalk/oracle/packages/rhn_channel.pkb | 21 schema/spacewalk/oracle/packages/rhn_channel.pks | 3 schema/spacewalk/postgres/packages/rhn_channel.pkb | 25 schema/spacewalk/postgres/packages/rhn_channel.pks | 2 schema/spacewalk/spacewalk-schema.spec | 5 schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.oracle | 149 + schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.postgresql | 2 schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.oracle | 1239 ++++++++++ schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.postgresql | 47 19 files changed, 1527 insertions(+), 96 deletions(-)
New commits: commit 436a6130a179d8e09732d02358ad22c9751e84ed Author: Jan Pazdziora jpazdziora@redhat.com Date: Fri Jan 25 09:35:48 2013 +0100
Automatic commit of package [spacewalk-backend] release [1.9.19-1].
diff --git a/backend/spacewalk-backend.spec b/backend/spacewalk-backend.spec index 1235ae4..0b59375 100644 --- a/backend/spacewalk-backend.spec +++ b/backend/spacewalk-backend.spec @@ -12,7 +12,7 @@ Name: spacewalk-backend Summary: Common programs needed to be installed on the Spacewalk servers/proxies Group: Applications/Internet License: GPLv2 -Version: 1.9.18 +Version: 1.9.19 Release: 1%{?dist} URL: https://fedorahosted.org/spacewalk Source0: https://fedorahosted.org/releases/s/p/spacewalk/%%7Bname%7D-%%7Bversion%7D.t... @@ -637,6 +637,14 @@ rm -f %{rhnconf}/rhnSecret.py*
# $Id$ %changelog +* Fri Jan 25 2013 Jan Pazdziora 1.9.19-1 +- The rhn_asdf_* anonymous logic should not be needed anymore. +- Reimplement anonymous block with update or insert. +- Reimplement anonymous block with rhnSQL.Procedure. +- The _query_purge_extra_channel_families_1 seems unused, removing. +- Reimplement anonymous block. +- Reimplement _set_comps_for_channel as stored procedure. + * Tue Jan 22 2013 Jan Pazdziora 1.9.18-1 - Use SSL options from rhncontentsourcessl during spacewalk-repo-sync.
diff --git a/rel-eng/packages/spacewalk-backend b/rel-eng/packages/spacewalk-backend index 991b716..e2b7b10 100644 --- a/rel-eng/packages/spacewalk-backend +++ b/rel-eng/packages/spacewalk-backend @@ -1 +1 @@ -1.9.18-1 backend/ +1.9.19-1 backend/
commit acf1e6c6e2e6527b7d18927bd21fdd1ba8a78f9c Author: Jan Pazdziora jpazdziora@redhat.com Date: Fri Jan 25 09:35:42 2013 +0100
Automatic commit of package [spacewalk-schema] release [1.9.18-1].
diff --git a/rel-eng/packages/spacewalk-schema b/rel-eng/packages/spacewalk-schema index b641793..cb5d753 100644 --- a/rel-eng/packages/spacewalk-schema +++ b/rel-eng/packages/spacewalk-schema @@ -1 +1 @@ -1.9.17-1 schema/spacewalk/ +1.9.18-1 schema/spacewalk/ diff --git a/schema/spacewalk/spacewalk-schema.spec b/schema/spacewalk/spacewalk-schema.spec index a31ea7a..10220f1 100644 --- a/schema/spacewalk/spacewalk-schema.spec +++ b/schema/spacewalk/spacewalk-schema.spec @@ -2,7 +2,7 @@ Name: spacewalk-schema Group: Applications/Internet Summary: Oracle SQL schema for Spacewalk server
-Version: 1.9.17 +Version: 1.9.18 Release: 1%{?dist} Source0: %{name}-%{version}.tar.gz
@@ -68,6 +68,9 @@ rm -rf $RPM_BUILD_ROOT %{_mandir}/man1/spacewalk-sql*
%changelog +* Fri Jan 25 2013 Jan Pazdziora 1.9.18-1 +- Reimplement _set_comps_for_channel as stored procedure. + * Wed Jan 23 2013 Jan Pazdziora 1.9.17-1 - Avoid exceeding maximal identifier length.
commit 54671b0dfb3e88d7cad9a901df91b26c95a98f7b Author: Jan Pazdziora jpazdziora@redhat.com Date: Fri Jan 25 08:43:54 2013 +0100
The rhn_asdf_* anonymous logic should not be needed anymore.
diff --git a/backend/server/rhnSQL/__init__.py b/backend/server/rhnSQL/__init__.py index 6178e40..ef394ac 100644 --- a/backend/server/rhnSQL/__init__.py +++ b/backend/server/rhnSQL/__init__.py @@ -189,11 +189,11 @@ def database(): def cursor(): db = __test_DB() return db.cursor() -def prepare(sql, params=None, blob_map=None): +def prepare(sql, blob_map=None): db = __test_DB() if isinstance(sql, Statement): sql = sql.statement - return db.prepare(sql, params=params, blob_map=blob_map) + return db.prepare(sql, blob_map=blob_map) def execute(sql, *args, **kwargs): db = __test_DB() return apply(db.execute, (sql, ) + args, kwargs) diff --git a/backend/server/rhnSQL/driver_cx_Oracle.py b/backend/server/rhnSQL/driver_cx_Oracle.py index cce647a..5246e98 100644 --- a/backend/server/rhnSQL/driver_cx_Oracle.py +++ b/backend/server/rhnSQL/driver_cx_Oracle.py @@ -449,7 +449,7 @@ class Database(sql_base.Database): return self._cursor_class(dbh=self.dbh)
# pass-through functions for when you want to do SQL yourself - def prepare(self, sql, force=0, params=None, blob_map = None): + def prepare(self, sql, force=0, blob_map = None): # Abuse the map calls to get rid of SQL comments and extra spaces sql = string.join(filter(lambda a: len(a), map(string.strip, diff --git a/backend/server/rhnSQL/driver_postgresql.py b/backend/server/rhnSQL/driver_postgresql.py index 78ce281..54f3e7d 100644 --- a/backend/server/rhnSQL/driver_postgresql.py +++ b/backend/server/rhnSQL/driver_postgresql.py @@ -20,7 +20,6 @@ import sys import re import psycopg2 -import hashlib
# workaround for python-psycopg2 = 2.0.13 (RHEL6) # which does not import extensions by default @@ -193,19 +192,7 @@ class Database(sql_base.Database): "Exception information: %s" % sys.exc_info()[1]) self.connect() # only allow one try
- def prepare(self, sql, force=0, params=None, blob_map=None): - if params != None: # support for anonymour plpgsql - sql = re.sub(r':(\w+)', '\g<1>', sql) - s = hashlib.new('sha1') - s.update(sql) - sha1 = s.hexdigest() - c = self.prepare("select 1 from information_schema.routines where routine_name = 'rhn_asdf_' || :sha1"); - c.execute(sha1=sha1) - if not c.fetchone(): - c = self.prepare("create function rhn_asdf_%s (%s) returns void as $x%s$%s$x%s$ language plpgsql" % ( sha1, ','.join(params), sha1, sql, sha1 )) - c.execute() - qparams = ','.join(map(lambda x: re.sub(r'^(\w+).*', ':\g<1>', x), params)) - sql = "select rhn_asdf_%s(%s)" % (sha1, qparams) + def prepare(self, sql, force=0, blob_map=None): return Cursor(dbh=self.dbh, sql=sql, force=force, blob_map=blob_map)
def execute(self, sql, *args, **kwargs):
commit 866e05068ef232c62de09940644509ceb87be034 Author: Jan Pazdziora jpazdziora@redhat.com Date: Fri Jan 25 08:38:22 2013 +0100
Reimplement anonymous block with update or insert.
diff --git a/backend/server/rhnServer/server_lib.py b/backend/server/rhnServer/server_lib.py index 1531e2b..5ee78ca 100644 --- a/backend/server/rhnServer/server_lib.py +++ b/backend/server/rhnServer/server_lib.py @@ -302,9 +302,6 @@ def entitlement_grants_service(entitlement, service): # Push client related # XXX should be moved to a different file? _query_update_push_client_registration = rhnSQL.Statement(""" -declare - updated_id numeric; -begin update rhnPushClient set name = :name_in, shared_key = :shared_key_in, @@ -312,14 +309,12 @@ begin next_action_time = NULL, last_ping_time = NULL where server_id = :server_id_in - returning server_id into updated_id; - if updated_id is null then +""") +_query_insert_push_client_registration = rhnSQL.Statement(""" insert into rhnPushClient (id, server_id, name, shared_key, state_id) values (sequence_nextval('rhn_pclient_id_seq'), :server_id_in, :name_in, - :shared_key_in, :state_id_in); - end if; -end; + :shared_key_in, :state_id_in) """) def update_push_client_registration(server_id): # Generate a new a new client name and shared key @@ -330,11 +325,14 @@ def update_push_client_registration(server_id): assert row is not None state_id = row['id']
- h = rhnSQL.prepare(_query_update_push_client_registration, - params = ('server_id_in numeric', 'name_in varchar', - 'shared_key_in varchar', 'state_id_in numeric')) - h.execute(server_id_in=server_id, name_in=client_name, + h = rhnSQL.prepare(_query_update_push_client_registration) + rowcount = h.execute(server_id_in=server_id, name_in=client_name, shared_key_in=shared_key, state_id_in=state_id) + if not rowcount: + h = rhnSQL.prepare(_query_insert_push_client_registration) + h.execute(server_id_in=server_id, name_in=client_name, + shared_key_in=shared_key, state_id_in=state_id) + # Get the server's (database) time # XXX timestamp = int(time.time())
commit 25cf179adbb160026e9ae3132eff8c7c3e1fc109 Author: Jan Pazdziora jpazdziora@redhat.com Date: Thu Jan 24 14:22:52 2013 +0100
Reimplement anonymous block with rhnSQL.Procedure.
diff --git a/backend/satellite_tools/spacewalk-remove-channel b/backend/satellite_tools/spacewalk-remove-channel index 081c862..627f4ea 100755 --- a/backend/satellite_tools/spacewalk-remove-channel +++ b/backend/satellite_tools/spacewalk-remove-channel @@ -218,23 +218,14 @@ def __unsubscribeServers(labels): finalSize=len(list), finalBarLength=40, stream=sys.stdout) pb.printAll(1)
+ unsubscribe_server_proc = rhnSQL.Procedure("rhn_channel.unsubscribe_server") for i in list: - __unbsubscribeServer(i['server_id'], i['channel_id']) + unsubscribe_server_proc(i['server_id'], i['channel_id']) pb.addTo(1) pb.printIncrement() pb.printComplete()
-def __unbsubscribeServer(server_id, channel_id): - sql = """ - begin - /*pg perform*/ rhn_channel.unsubscribe_server(:server_id, :channel_id); - end; - """ - h = rhnSQL.prepare(sql, params=('server_id numeric', 'channel_id numeric')) - h.execute(server_id=server_id, channel_id=channel_id) - - def __kickstartCheck(labels): sql = """ select K.org_id, K.label diff --git a/backend/server/rhnSQL/driver_postgresql.py b/backend/server/rhnSQL/driver_postgresql.py index 321d2ca..78ce281 100644 --- a/backend/server/rhnSQL/driver_postgresql.py +++ b/backend/server/rhnSQL/driver_postgresql.py @@ -195,7 +195,6 @@ class Database(sql_base.Database):
def prepare(self, sql, force=0, params=None, blob_map=None): if params != None: # support for anonymour plpgsql - sql = re.sub(r'/*pg (.+?)*/', '\g<1>', sql) sql = re.sub(r':(\w+)', '\g<1>', sql) s = hashlib.new('sha1') s.update(sql)
commit b3dc2092ffe2a99fecb06826e999366ab0800704 Author: Jan Pazdziora jpazdziora@redhat.com Date: Thu Jan 24 14:15:06 2013 +0100
The _query_purge_extra_channel_families_1 seems unused, removing.
diff --git a/backend/satellite_tools/sync_handlers.py b/backend/satellite_tools/sync_handlers.py index 73e5190..fa59627 100644 --- a/backend/satellite_tools/sync_handlers.py +++ b/backend/satellite_tools/sync_handlers.py @@ -689,16 +689,6 @@ def _fetch_channel_family_permissions(): return h.fetchall_dict() or []
-_query_purge_extra_channel_families_1 = rhnSQL.Statement(""" - delete from rhnPrivateChannelFamily cfp - where max_members = 0 - and not exists ( - select 1 from rhnChannelFamilyMembers - where channel_family_id = cfp.channel_family_id - ) -""") - - _query_purge_private_channel_families = rhnSQL.Statement(""" delete from rhnChannelFamily where org_id is null
commit 2accdd06c3d6cc63f2d211d4a730df299c0390a2 Author: Jan Pazdziora jpazdziora@redhat.com Date: Thu Jan 24 14:11:13 2013 +0100
Reimplement anonymous block.
Since rhn_channel.update_family_counts merely updates rhnPrivateChannelFamily, there is no need to do cartesian product.
diff --git a/backend/satellite_tools/sync_handlers.py b/backend/satellite_tools/sync_handlers.py index 2eeb4fd..73e5190 100644 --- a/backend/satellite_tools/sync_handlers.py +++ b/backend/satellite_tools/sync_handlers.py @@ -717,21 +717,21 @@ def purge_extra_channel_families(): syncLib.log(-1, str(e))
-_query_update_family_counts = rhnSQL.Statement(""" - declare - /*pg_cs*/ cursor ch_fam_cursor /*pg cursor*/ is - select cf.id channel_family_id, wc.id org_id - from rhnChannelFamily cf, web_customer wc; - begin - for row in ch_fam_cursor loop - /*pg perform*/ rhn_channel.update_family_counts(row.channel_family_id, - row.org_id); - end loop; - end; +_query_private_families = rhnSQL.Statement(""" + select channel_family_id, org_id + from rhnPrivateChannelFamily + order by channel_family_id, org_id """) def update_channel_family_counts(): - h = rhnSQL.prepare(_query_update_family_counts, params=()) + update_family_counts_proc = rhnSQL.Procedure("rhn_channel.update_family_counts") + h = rhnSQL.prepare(_query_private_families) h.execute() + while 1: + row = h.fetchone_dict() + if not row: + break + update_family_counts_proc(row['channel_family_id'], row['org_id']) + rhnSQL.commit()
diff --git a/backend/server/rhnSQL/driver_postgresql.py b/backend/server/rhnSQL/driver_postgresql.py index 47a1973..321d2ca 100644 --- a/backend/server/rhnSQL/driver_postgresql.py +++ b/backend/server/rhnSQL/driver_postgresql.py @@ -195,7 +195,6 @@ class Database(sql_base.Database):
def prepare(self, sql, force=0, params=None, blob_map=None): if params != None: # support for anonymour plpgsql - sql = re.sub(r'/*pg_cs*/\s*cursor', '', sql) sql = re.sub(r'/*pg (.+?)*/', '\g<1>', sql) sql = re.sub(r':(\w+)', '\g<1>', sql) s = hashlib.new('sha1')
commit 6ba10fdc5e8976ee16ebc70e2687fae4f1196594 Author: Jan Pazdziora jpazdziora@redhat.com Date: Thu Jan 24 13:00:47 2013 +0100
Reimplement _set_comps_for_channel as stored procedure.
diff --git a/backend/satellite_tools/satsync.py b/backend/satellite_tools/satsync.py index a0cfa5e..e9c8f27 100644 --- a/backend/satellite_tools/satsync.py +++ b/backend/satellite_tools/satsync.py @@ -601,28 +601,6 @@ Please contact your RHN representative""") % (generation, sat_cert.generation)) except Exception: None
- def _set_comps_for_channel(self, backend, channel_id, path, timestamp): - sth = backend.dbmodule.prepare(""" - declare - /*pg_cs*/ cursor comps_data /*pg cursor*/ (cid_in numeric) is - select relative_filename, last_modified - from rhnChannelComps - where channel_id = cid_in; - begin - for row in comps_data(:channel_id_in) loop - if row.relative_filename = :path_in - and row.last_modified = to_date(:timestamp_in, 'YYYYMMDDHH24MISS') then - return; - end if; - end loop; - delete from rhnChannelComps - where channel_id = :channel_id_in; - insert into rhnChannelComps (id, channel_id, relative_filename, last_modified, created, modified) - values (sequence_nextval('rhn_channelcomps_id_seq'), :channel_id_in, :path_in, to_date(:timestamp_in, 'YYYYMMDDHH24MISS'), current_timestamp, current_timestamp); - end; - """, params = ( 'channel_id_in numeric', 'path_in varchar', 'timestamp_in varchar' )) - sth.execute(channel_id_in = channel_id, path_in = path, timestamp_in = timestamp) - def _process_comps(self, backend, label, timestamp): comps_path = 'rhn/comps/%s/comps-%s.xml' % (label, timestamp) full_path = os.path.join(CFG.MOUNT_POINT, comps_path) @@ -636,7 +614,7 @@ Please contact your RHN representative""") % (generation, sat_cert.generation)) f.write_file(stream) data = { label : None } backend.lookupChannels(data) - self._set_comps_for_channel(backend, data[label]['id'], comps_path, timestamp) + rhnSQL.Procedure('rhn_channel.set_comps')(data[label]['id'], comps_path, timestamp)
def process_channels(self): """ push channels, channel-family and dist. map information diff --git a/schema/spacewalk/oracle/packages/rhn_channel.pkb b/schema/spacewalk/oracle/packages/rhn_channel.pkb index e44c9e0..2869947 100644 --- a/schema/spacewalk/oracle/packages/rhn_channel.pkb +++ b/schema/spacewalk/oracle/packages/rhn_channel.pkb @@ -1,5 +1,5 @@ -- --- Copyright (c) 2008--2012 Red Hat, Inc. +-- Copyright (c) 2008--2013 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 @@ -1215,6 +1215,25 @@ IS end loop; end update_needed_cache;
+ procedure set_comps(channel_id_in in number, path_in in varchar2, timestamp_in in varchar2) + is + begin + for row in ( + select relative_filename, last_modified + from rhnChannelComps + where channel_id = channel_id_in + ) channel_id_in) loop + if row.relative_filename = :path_in + and row.last_modified = to_date(:timestamp_in, 'YYYYMMDDHH24MISS') then + return; + end if; + end loop; + delete from rhnChannelComps + where channel_id = :channel_id_in; + insert into rhnChannelComps (id, channel_id, relative_filename, last_modified, created, modified) + values (sequence_nextval('rhn_channelcomps_id_seq'), :channel_id_in, :path_in, to_date(:timestamp_in, 'YYYYMMDDHH24MISS'), current_timestamp, current_timestamp); + end set_comps; + END rhn_channel; / SHOW ERRORS diff --git a/schema/spacewalk/oracle/packages/rhn_channel.pks b/schema/spacewalk/oracle/packages/rhn_channel.pks index 084c727..d496e79 100644 --- a/schema/spacewalk/oracle/packages/rhn_channel.pks +++ b/schema/spacewalk/oracle/packages/rhn_channel.pks @@ -1,5 +1,5 @@ -- --- Copyright (c) 2008--2012 Red Hat, Inc. +-- Copyright (c) 2008--2013 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 @@ -142,6 +142,7 @@ IS
PROCEDURE update_needed_cache(channel_id_in in number);
+ procedure set_comps(channel_id_in in number, path_in in varchar2, timestamp_in in varchar2);
END rhn_channel; / diff --git a/schema/spacewalk/postgres/packages/rhn_channel.pkb b/schema/spacewalk/postgres/packages/rhn_channel.pkb index b123890..c2d6e1c 100644 --- a/schema/spacewalk/postgres/packages/rhn_channel.pkb +++ b/schema/spacewalk/postgres/packages/rhn_channel.pkb @@ -1,6 +1,6 @@ --- oracle equivalent source sha1 a57a1f03708569c16c3cd0d747a0736a2877a714 +-- oracle equivalent source sha1 e53c181a3223101f00b87b186f283b3ce4f3ca8a -- --- Copyright (c) 2008--2012 Red Hat, Inc. +-- Copyright (c) 2008--2013 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 @@ -1237,5 +1237,26 @@ update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_ end loop; end$$ language plpgsql;
+ create or replace function set_comps(channel_id_in in numeric, path_in in varchar, timestamp_in in varchar) returns void + as $$ + declare + row record; + begin + for row in ( + select relative_filename, last_modified + from rhnChannelComps + where channel_id = channel_id_in + ) loop + if row.relative_filename = path_in + and row.last_modified = to_date(timestamp_in, 'YYYYMMDDHH24MISS') then + return; + end if; + end loop; + delete from rhnChannelComps + where channel_id = channel_id_in; + insert into rhnChannelComps (id, channel_id, relative_filename, last_modified, created, modified) + values (sequence_nextval('rhn_channelcomps_id_seq'), channel_id_in, path_in, to_date(timestamp_in, 'YYYYMMDDHH24MISS'), current_timestamp, current_timestamp); + end$$ language plpgsql; + -- restore the original setting update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_channel')+1) ) where name = 'search_path'; diff --git a/schema/spacewalk/postgres/packages/rhn_channel.pks b/schema/spacewalk/postgres/packages/rhn_channel.pks index d940387..b5fe5d1 100644 --- a/schema/spacewalk/postgres/packages/rhn_channel.pks +++ b/schema/spacewalk/postgres/packages/rhn_channel.pks @@ -1,4 +1,4 @@ --- oracle equivalent source sha1 0e7482cf888f18025cbf2e6a8a37ac1ad2f07518 +-- oracle equivalent source sha1 c80b7928c9bea064eb735e7b67ac876c623696f6
create schema rhn_channel;
diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.oracle new file mode 100644 index 0000000..d496e79 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.oracle @@ -0,0 +1,149 @@ +-- +-- Copyright (c) 2008--2013 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- implied, including the implied warranties of MERCHANTABILITY or FITNESS +-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 +-- along with this software; if not, see +-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. +-- +-- Red Hat trademarks are not licensed under GPLv2. No permission is +-- granted to use or replicate Red Hat trademarks that are incorporated +-- in this software or its documentation. +-- + +CREATE OR REPLACE +PACKAGE rhn_channel +IS + version varchar2(100) := ''; + + CURSOR server_base_subscriptions(server_id_in NUMBER) IS + SELECT C.id + FROM rhnChannel C, rhnServerChannel SC + WHERE C.id = SC.channel_id + AND SC.server_id = server_id_in + AND C.parent_channel IS NULL; + + CURSOR check_server_subscription(server_id_in NUMBER, channel_id_in NUMBER) IS + SELECT channel_id + FROM rhnServerChannel + WHERE server_id = server_id_in + AND channel_id = channel_id_in; + + CURSOR check_server_parent_membership(server_id_in NUMBER, channel_id_in NUMBER) IS + SELECT C.id + FROM rhnChannel C, rhnServerChannel SC + WHERE C.parent_channel = channel_id_in + AND C.id = SC.channel_id + AND SC.server_id = server_id_in; + + CURSOR channel_family_perm_cursor(channel_family_id_in NUMBER, org_id_in NUMBER) IS + SELECT * + FROM rhnOrgChannelFamilyPermissions + WHERE channel_family_id = channel_family_id_in + AND org_id = org_id_in; + + + PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in number := 0, + deleting_server in number := 0, + update_family_countsYN IN NUMBER := 1); + PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in number := null, recalcfamily_in number := 1); + + PROCEDURE convert_to_fve(server_id_in IN NUMBER, channel_family_id_val IN NUMBER); + + FUNCTION can_convert_to_fve(server_id_in IN NUMBER, channel_family_id_val IN NUMBER) RETURN NUMBER; + + function can_server_consume_virt_channl( + server_id_in IN NUMBER, + family_id_in in number) + return number; + FUNCTION can_server_consume_fve( server_id_in IN NUMBER) RETURN NUMBER; + + FUNCTION guess_server_base(server_id_in IN NUMBER) RETURN NUMBER; + + FUNCTION base_channel_for_release_arch(release_in in varchar2, + server_arch_in in varchar2, org_id_in in number := -1, + user_id_in in number := null) RETURN number; + + FUNCTION base_channel_rel_archid(release_in in varchar2, + server_arch_id_in in number, org_id_in in number := -1, + user_id_in in number := null) RETURN number; + + FUNCTION channel_priority(channel_id_in in number) RETURN number; + + PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server in number := 0, + update_family_countsYN IN NUMBER := 1); + + FUNCTION available_family_subscriptions(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) RETURN NUMBER; + + FUNCTION available_fve_family_subs(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) RETURN NUMBER; + + FUNCTION channel_family_current_members(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) return number; + + FUNCTION cfam_curr_fve_members(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) return number; + + PROCEDURE update_family_counts(channel_family_id_in IN NUMBER, org_id_in IN NUMBER); + PROCEDURE update_group_family_counts(group_label_in IN VARCHAR2, org_id_in IN NUMBER); + FUNCTION family_for_channel(channel_id_in IN NUMBER) RETURN NUMBER; + + FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER, org_id_in IN NUMBER) RETURN NUMBER; + + FUNCTION available_fve_chan_subs(channel_id_in IN NUMBER, org_id_in IN NUMBER) RETURN NUMBER; + + PROCEDURE unsubscribe_server_from_family(server_id_in in number, channel_family_id_in in number); + + PROCEDURE delete_server_channels(server_id_in in number); + + PROCEDURE refresh_newest_package(channel_id_in in number, + caller_in in varchar2 := '(unknown)', + package_name_id_in in number := null); + + FUNCTION get_org_id(channel_id_in in number) return number; + PRAGMA RESTRICT_REFERENCES(get_org_id, WNDS, RNPS, WNPS); + + function get_org_access(channel_id_in in number, org_id_in in number) return number; + PRAGMA RESTRICT_REFERENCES(get_org_access, WNDS, RNPS, WNPS); + + function get_cfam_org_access(cfam_id_in in number, org_id_in in number) return number; + + function user_role_check_debug(channel_id_in in number, user_id_in in number, role_in in varchar2) + RETURN VARCHAR2; + PRAGMA RESTRICT_REFERENCES(user_role_check_debug, WNDS, RNPS, WNPS); + + function user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) + RETURN NUMBER; + PRAGMA RESTRICT_REFERENCES(user_role_check, WNDS, RNPS, WNPS); + + function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) + RETURN NUMBER; + PRAGMA RESTRICT_REFERENCES(loose_user_role_check, WNDS, RNPS, WNPS); + + function direct_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) + RETURN NUMBER; + PRAGMA RESTRICT_REFERENCES(direct_user_role_check, WNDS, RNPS, WNPS); + + function shared_user_role_check(channel_id in number, user_id in number, role in varchar2) + RETURN NUMBER; + PRAGMA RESTRICT_REFERENCES(shared_user_role_check, WNDS, RNPS, WNPS); + + function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in in varchar2) + RETURN NUMBER; + + PROCEDURE update_channel ( channel_id_in in number, invalidate_ss in number := 0, + date_to_use in timestamp with local time zone := current_timestamp ); + + PROCEDURE update_channels_by_package ( package_id_in in number, date_to_use in timestamp with local time zone := current_timestamp ); + + PROCEDURE update_channels_by_errata ( errata_id_in number, date_to_use in timestamp with local time zone := current_timestamp ); + + + PRAGMA RESTRICT_REFERENCES(org_channel_setting, WNDS, RNPS, WNPS); + + PROCEDURE update_needed_cache(channel_id_in in number); + + procedure set_comps(channel_id_in in number, path_in in varchar2, timestamp_in in varchar2); + +END rhn_channel; +/ +SHOW ERRORS diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.postgresql new file mode 100644 index 0000000..163f556 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/010-rhn_channel.pks.postgresql @@ -0,0 +1,2 @@ +-- oracle equivalent source sha1 c80b7928c9bea064eb735e7b67ac876c623696f6 +-- This file has intentionally been left empty. diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.oracle b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.oracle new file mode 100644 index 0000000..2869947 --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.oracle @@ -0,0 +1,1239 @@ +-- +-- Copyright (c) 2008--2013 Red Hat, Inc. +-- +-- This software is licensed to you under the GNU General Public License, +-- version 2 (GPLv2). There is NO WARRANTY for this software, express or +-- implied, including the implied warranties of MERCHANTABILITY or FITNESS +-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2 +-- along with this software; if not, see +-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt. +-- +-- Red Hat trademarks are not licensed under GPLv2. No permission is +-- granted to use or replicate Red Hat trademarks that are incorporated +-- in this software or its documentation. +-- + +CREATE OR REPLACE +PACKAGE BODY rhn_channel +IS + body_version varchar2(100) := ''; + + -- Cursor that fetches all the possible base channels for a + -- (server_arch_id, release, org_id) combination + cursor base_channel_cursor( + release_in in varchar2, + server_arch_id_in in number, + org_id_in in number + ) return rhnChannel%ROWTYPE is + select distinct c.* + from rhnOrgDistChannelMap odcm, + rhnServerChannelArchCompat scac, + rhnChannel c + where c.parent_channel is null + and c.id = odcm.channel_id + and c.channel_arch_id = odcm.channel_arch_id + and odcm.release = release_in + and odcm.for_org_id = org_id_in + and scac.server_arch_id = server_arch_id_in + and scac.channel_arch_id = c.channel_arch_id; + + procedure obtain_read_lock(channel_family_id_in in number, org_id_in in number) + is + read_lock timestamp with local time zone; + + begin + select created into read_lock + from rhnPrivateChannelFamily + where channel_family_id = channel_family_id_in and org_id = org_id_in + for update; + end obtain_read_lock; + + PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in in number := null, recalcfamily_in number := 1) + IS + channel_parent_val rhnChannel.parent_channel%TYPE; + parent_subscribed BOOLEAN; + server_has_base_chan BOOLEAN; + server_already_in_chan BOOLEAN; + channel_family_id_val NUMBER; + server_org_id_val NUMBER; + available_subscriptions NUMBER; + available_fve_subs NUMBER; + consenting_user NUMBER; + allowed number := 0; + is_fve CHAR(1) := 'N'; + BEGIN + if user_id_in is not null then + allowed := rhn_channel.user_role_check(channel_id_in, user_id_in, 'subscribe'); + else + allowed := 1; + end if; + + if allowed = 0 then + rhn_exception.raise_exception('no_subscribe_permissions'); + end if; + + + SELECT parent_channel INTO channel_parent_val FROM rhnChannel WHERE id = channel_id_in; + + 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 := FALSE; + + FOR check_subscription IN check_server_subscription(server_id_in, channel_parent_val) + LOOP + parent_subscribed := TRUE; + END LOOP check_subscription; + + IF NOT parent_subscribed + THEN + RETURN; + END IF; + ELSE + -- base channel + server_has_base_chan := FALSE; + FOR base IN server_base_subscriptions(server_id_in) + LOOP + server_has_base_chan := TRUE; + END LOOP base; + + IF server_has_base_chan + THEN + rhn_exception.raise_exception('channel_server_one_base'); + END IF; + END IF; + + FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in) + LOOP + server_already_in_chan := TRUE; + END LOOP check_subscription; + + IF server_already_in_chan + THEN + RETURN; + END IF; + + channel_family_id_val := rhn_channel.family_for_channel(channel_id_in); + IF channel_family_id_val IS NULL + THEN + rhn_exception.raise_exception('channel_subscribe_no_family'); + END IF; + + -- + -- 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)) + INTO server_org_id_val + FROM rhnChannel + WHERE id = channel_id_in; + + begin + obtain_read_lock(channel_family_id_val, server_org_id_val); + exception + when no_data_found then + rhn_exception.raise_exception('channel_family_no_subscriptions'); + end; + + available_subscriptions := rhn_channel.available_family_subscriptions(channel_family_id_val, server_org_id_val); + available_fve_subs := rhn_channel.available_fve_family_subs(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 OR + (available_fve_subs > 0 AND can_server_consume_fve(server_id_in) = 1) + THEN + if can_server_consume_virt_channl(server_id_in, channel_family_id_val) = 0 AND available_fve_subs > 0 AND can_server_consume_fve(server_id_in) = 1 THEN + is_fve := 'Y'; + END IF; + insert into rhnServerHistory (id,server_id,summary,details) ( + select rhn_event_id_seq.nextval, + server_id_in, + 'subscribed to channel ' || SUBSTR(c.label, 0, 106), + c.label + from rhnChannel c + where c.id = channel_id_in + ); + UPDATE rhnServer SET channels_changed = current_timestamp WHERE id = server_id_in; + INSERT INTO rhnServerChannel (server_id, channel_id, is_fve) VALUES (server_id_in, channel_id_in, is_fve); + IF recalcfamily_in > 0 + THEN + rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val); + END IF; + queue_server(server_id_in, immediate_in); + ELSE + rhn_exception.raise_exception('channel_family_no_subscriptions'); + END IF; + + END subscribe_server; + + + + FUNCTION can_convert_to_fve(server_id_in IN NUMBER, channel_family_id_val IN NUMBER) + RETURN NUMBER + IS + CURSOR fve_convertible_entries IS + select 1 from + rhnServerFveCapable cap + where cap.server_id = server_id_in + AND cap.channel_family_id = channel_family_id_val; + BEGIN + FOR entry IN fve_convertible_entries LOOP + return 1; + END LOOP; + RETURN 0; + END can_convert_to_fve; + + + + -- Converts server channel_family to use a flex entitlement + PROCEDURE convert_to_fve(server_id_in IN NUMBER, channel_family_id_val IN NUMBER) + IS + available_fve_subs NUMBER; + server_org_id_val NUMBER; + BEGIN + + -- + -- 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 org_id + INTO server_org_id_val + FROM rhnServer + WHERE id = server_id_in; + + begin + obtain_read_lock(channel_family_id_val, server_org_id_val); + exception + when no_data_found then + rhn_exception.raise_exception('channel_family_no_subscriptions'); + end; + IF (can_convert_to_fve(server_id_in, channel_family_id_val ) = 0) + THEN + rhn_exception.raise_exception('server_cannot_convert_to_flex'); + END IF; + + available_fve_subs := rhn_channel.available_fve_family_subs(channel_family_id_val, server_org_id_val); + + IF (available_fve_subs > 0) + THEN + + insert into rhnServerHistory (id,server_id,summary,details) ( + select rhn_event_id_seq.nextval, + server_id_in, + 'converted to flex entitlement' || SUBSTR(cf.label, 0, 99), + cf.label + from rhnChannelFamily cf + where cf.id = channel_family_id_val + ); + + UPDATE rhnServerChannel sc set sc.is_fve = 'Y' + where sc.server_id = server_id_in and + sc.channel_id in + (select cfm.channel_id from rhnChannelFamilyMembers cfm + where cfm.CHANNEL_FAMILY_ID = channel_family_id_val); + + rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val); + ELSE + rhn_exception.raise_exception('not_enough_flex_entitlements'); + END IF; + + END convert_to_fve; + + function can_server_consume_virt_channl( + server_id_in in number, + family_id_in in number ) + return number + is + + cursor server_virt_families is + select vi.virtual_system_id, cfvsl.channel_family_id + from + rhnChannelFamilyVirtSubLevel cfvsl, + rhnSGTypeVirtSubLevel sgtvsl, + rhnVirtualInstance vi + where + vi.virtual_system_id = server_id_in + and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id + and cfvsl.channel_family_id = family_id_in + and exists ( + 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; + + return 0; + + end; + + FUNCTION can_server_consume_fve(server_id_in IN NUMBER) + RETURN NUMBER + IS + CURSOR vi_entries IS + SELECT 1 + FROM rhnVirtualInstance vi + WHERE vi.virtual_system_id = server_id_in; + vi_count NUMBER; + + BEGIN + FOR vi_entry IN VI_ENTRIES LOOP + return 1; + END LOOP; + RETURN 0; + END; + + function guess_server_base( + server_id_in in number + ) RETURN number is + cursor server_cursor is + select s.server_arch_id, s.release, s.org_id + from rhnServer s + where s.id = server_id_in; + begin + for s in server_cursor loop + for channel in base_channel_cursor(s.release, + s.server_arch_id, s.org_id) + loop + return channel.id; + end loop base_channel_cursor; + end loop server_cursor; + -- Server not found, or no base channel applies to it + return null; + end; + + -- Private function + function normalize_server_arch(server_arch_in in varchar2) + return varchar2 + deterministic + is + suffix VARCHAR2(128) := '-redhat-linux'; + suffix_len NUMBER := length(suffix); + begin + if server_arch_in is NULL then + return NULL; + end if; + if instr(server_arch_in, '-') > 0 + then + -- Suffix already present + return server_arch_in; + end if; + return server_arch_in || suffix; + end normalize_server_arch; + + -- + -- + -- Raises: + -- server_arch_not_found + -- no_subscribe_permissions + function base_channel_for_release_arch( + release_in in varchar2, + server_arch_in in varchar2, + org_id_in in number := -1, + user_id_in in number := null + ) return number is + server_arch varchar2(256) := normalize_server_arch(server_arch_in); + server_arch_id number; + 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 + rhn_exception.raise_exception('server_arch_not_found'); + end; + return base_channel_rel_archid(release_in, server_arch_id, + org_id_in, user_id_in); + end base_channel_for_release_arch; + + function base_channel_rel_archid( + release_in in varchar2, + server_arch_id_in in number, + org_id_in in number := -1, + user_id_in in number := null + ) return number is + denied_channel_id number := null; + valid_org_id number := org_id_in; + valid_user_id number := user_id_in; + channel_subscribable number; + 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 + -- User doesn't exist + -- XXX Only list public stuff for now + valid_user_id := null; + valid_org_id := -1; + end; + end if; + + for c in base_channel_cursor(release_in, server_arch_id_in, valid_org_id) + loop + -- This row is a possible match + if valid_user_id is null then + -- User ID not specified, so no user to channel permissions to + -- check + return c.id; + end if; + + -- Check user to channel permissions + select loose_user_role_check(c.id, user_id_in, 'subscribe') + into channel_subscribable + from dual; + + if channel_subscribable = 1 then + return c.id; + end if; + + -- Base channel exists, but is not subscribable; keep trying + denied_channel_id := c.id; + end loop base_channel_fetch; + + if denied_channel_id is not null then + rhn_exception.raise_exception('no_subscribe_permissions'); + end if; + -- No base channel applies + return NULL; + end base_channel_rel_archid; + + PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server IN NUMBER := 0, + update_family_countsYN IN NUMBER := 1) + IS + cursor server_channels(server_id_in in number) is + select s.org_id, sc.channel_id, cfm.channel_family_id + from rhnServer s, + rhnServerChannel sc, + rhnChannelFamilyMembers cfm + where s.id = server_id_in + and s.id = sc.server_id + and sc.channel_id = cfm.channel_id + order by cfm.channel_family_id; + last_channel_family_id rhnChannelFamilyMembers.channel_family_id%type := -1; + last_channel_org_id rhnServer.org_id%type := -1; + BEGIN + for channel in server_channels(server_id_in) + loop + unsubscribe_server(server_id_in, channel.channel_id, 1, 1, deleting_server, 0); + if update_family_countsYN > 0 + and channel.channel_family_id != last_channel_family_id then + -- update family counts only once + -- after all channels with same family has been fetched + if last_channel_family_id != -1 then + update_family_counts(last_channel_family_id, last_channel_org_id); + end if; + last_channel_family_id := channel.channel_family_id; + last_channel_org_id := channel.org_id; + end if; + end loop channel; + if update_family_countsYN > 0 and last_channel_family_id != -1 then + -- update the last family fetched + update_family_counts(last_channel_family_id, last_channel_org_id); + end if; + END clear_subscriptions; + + PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in number := 0, + deleting_server IN NUMBER := 0, + update_family_countsYN IN NUMBER := 1) + IS + channel_family_id_val NUMBER; + server_org_id_val NUMBER; + available_subscriptions NUMBER; + server_already_in_chan BOOLEAN; + cursor channel_family_is_proxy(channel_family_id_in in number) is + select 1 + from rhnChannelFamily + where id = channel_family_id_in + and label = 'rhn-proxy'; + cursor channel_family_is_satellite(channel_family_id_in in number) is + select 1 + from rhnChannelFamily + where id = channel_family_id_in + and label = 'rhn-satellite'; + -- this is *EXACTLY* like check_server_parent_membership, but if we recurse + -- with the package-level one, we get a "cursor already open", so we need a + -- copy on our call stack instead. GROAN. + cursor local_chk_server_parent_memb ( + server_id_in number, + channel_id_in number ) is + select c.id + from rhnChannel c, + rhnServerChannel sc + where 1=1 + and c.parent_channel = channel_id_in + and c.id = sc.channel_id + and sc.server_id = server_id_in; + BEGIN + FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in) + LOOP + if unsubscribe_children_in = 1 then + 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, + update_family_countsYN => update_family_countsYN); + else + rhn_exception.raise_exception('channel_unsubscribe_child_exists'); + end if; + END LOOP child; + + server_already_in_chan := FALSE; + + FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in) + LOOP + server_already_in_chan := TRUE; + END LOOP check_subscription; + + IF NOT server_already_in_chan + THEN + RETURN; + END IF; + + if deleting_server = 0 then + + insert into rhnServerHistory (id,server_id,summary,details) ( + select rhn_event_id_seq.nextval, + server_id_in, + 'unsubscribed from channel ' || SUBSTR(c.label, 0, 106), + c.label + from rhnChannel c + where c.id = channel_id_in + ); + + UPDATE rhnServer SET channels_changed = current_timestamp WHERE id = server_id_in; + end if; + + DELETE FROM rhnServerChannel WHERE server_id = server_id_in AND channel_id = channel_id_in; + + if deleting_server = 0 then + queue_server(server_id_in, immediate_in); + end if; + + channel_family_id_val := rhn_channel.family_for_channel(channel_id_in); + IF channel_family_id_val IS NULL + THEN + rhn_exception.raise_exception('channel_unsubscribe_no_family'); + END IF; + + for ignore in channel_family_is_satellite(channel_family_id_val) loop + delete from rhnSatelliteInfo where server_id = server_id_in; + end loop; + + for ignore in channel_family_is_proxy(channel_family_id_val) loop + delete from rhnProxyInfo where server_id = server_id_in; + end loop; + SELECT org_id INTO server_org_id_val + FROM rhnServer + WHERE id = server_id_in; + + if update_family_countsYN = 1 then + rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val); + end if; + END unsubscribe_server; + + + FUNCTION family_for_channel(channel_id_in IN NUMBER) + RETURN NUMBER + IS + channel_family_id_val NUMBER; + BEGIN + SELECT channel_family_id INTO channel_family_id_val + FROM rhnChannelFamilyMembers + WHERE channel_id = channel_id_in; + + RETURN channel_family_id_val; + EXCEPTION + WHEN NO_DATA_FOUND + THEN + RETURN NULL; + END family_for_channel; + + FUNCTION available_family_subscriptions(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) + RETURN NUMBER + IS + cfp channel_family_perm_cursor%ROWTYPE; + current_members_val NUMBER; + max_members_val NUMBER; + found NUMBER; + BEGIN + IF NOT channel_family_perm_cursor%ISOPEN + THEN + OPEN channel_family_perm_cursor(channel_family_id_in, org_id_in); + END IF; + + FETCH channel_family_perm_cursor INTO cfp; + + WHILE channel_family_perm_cursor%FOUND + LOOP + found := 1; + + current_members_val := cfp.current_members; + max_members_val := cfp.max_members; + + FETCH channel_family_perm_cursor INTO cfp; + END LOOP; + + IF channel_family_perm_cursor%ISOPEN + THEN + CLOSE channel_family_perm_cursor; + END IF; + + -- not found: either the channel fam doesn't have an entry in cfp, or the org doesn't have access to it. + -- either way, there are no available subscriptions + + IF found IS NULL + THEN + RETURN 0; + END IF; + + -- null max members? in that case, pass it on; NULL means infinite + IF max_members_val IS NULL + THEN + RETURN NULL; + END IF; + + -- otherwise, return the delta + RETURN max_members_val - current_members_val; + END available_family_subscriptions; + + FUNCTION available_fve_family_subs(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) + RETURN NUMBER + IS + cfp channel_family_perm_cursor%ROWTYPE; + fve_current_members_val NUMBER; + fve_max_members_val NUMBER; + found NUMBER; + + BEGIN + IF NOT channel_family_perm_cursor%ISOPEN THEN + OPEN channel_family_perm_cursor(channel_family_id_in, org_id_in); + END IF; + + FETCH channel_family_perm_cursor INTO cfp; + + WHILE channel_family_perm_cursor%FOUND LOOP + found := 1; + fve_current_members_val := cfp.fve_current_members; + fve_max_members_val := cfp.fve_max_members; + FETCH channel_family_perm_cursor INTO cfp; + END LOOP; + + IF channel_family_perm_cursor%ISOPEN THEN + CLOSE channel_family_perm_cursor; + END IF; + + IF found IS NULL THEN + RETURN 0; + END IF; + + IF fve_max_members_val IS NULL THEN + RETURN NULL; + END IF; + + RETURN fve_max_members_val - fve_current_members_val; + + END available_fve_family_subs; + + + -- ******************************************************************* + -- FUNCTION: channel_family_current_members + -- Calculates and returns the actual count of systems consuming + -- physical channel subscriptions. + -- Called by: update_family_counts + -- rhn_entitlements.repoll_virt_guest_entitlements + -- ******************************************************************* + function channel_family_current_members(channel_family_id_in IN NUMBER, + org_id_in IN NUMBER) + return number + is + current_members_count number := 0; + begin + select count(distinct server_id) + into current_members_count + from rhnChannelFamilyServerPhysical cfsp + where cfsp.channel_family_id = channel_family_id_in + and cfsp.customer_id = org_id_in; + return current_members_count; + end; + + + function cfam_curr_fve_members( + channel_family_id_in IN NUMBER, + org_id_in IN NUMBER) + return number + is + current_members_count number := 0; + + begin + select count(sc.server_id) + into current_members_count + from rhnServerChannel sc, + rhnChannelFamilyMembers cfm, + rhnServer s + where s.org_id = org_id_in + and s.id = sc.server_id + and cfm.channel_family_id = channel_family_id_in + and cfm.channel_id = sc.channel_id + and exists ( + select 1 + from rhnChannelFamilyServerFve cfsp + where cfsp.CHANNEL_FAMILY_ID = channel_family_id_in + and cfsp.server_id = s.id + ); + + return current_members_count; + end; + PROCEDURE update_family_counts(channel_family_id_in IN NUMBER, + org_id_in IN NUMBER) + IS + BEGIN + update rhnPrivateChannelFamily + set current_members = ( channel_family_current_members(channel_family_id_in, org_id_in)), + fve_current_members = ( cfam_curr_fve_members(channel_family_id_in, org_id_in)) + where org_id = org_id_in + and channel_family_id = channel_family_id_in; + END update_family_counts; + + PROCEDURE update_group_family_counts(group_label_in IN VARCHAR2, + org_id_in IN NUMBER) + IS + BEGIN + FOR i IN ( + SELECT DISTINCT CFM.channel_family_id, SG.org_id + FROM rhnChannelFamilyMembers CFM + JOIN rhnServerChannel SC + ON SC.channel_id = CFM.channel_id + JOIN rhnServerGroupMembers SGM + ON SC.server_id = SGM.server_id + JOIN rhnServerGroup SG + ON SGM.server_group_id = SG.id + JOIN rhnServerGroupType SGT + ON SG.group_type = SGT.id + WHERE SGT.label = group_label_in + AND SG.org_id = org_id_in + AND SGT.is_base = 'Y' + ) LOOP + rhn_channel.update_family_counts(i.channel_family_id, i.org_id); + END LOOP; + END update_group_family_counts; + + FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER, + org_id_in IN NUMBER) + RETURN NUMBER + IS + channel_family_id_val NUMBER; + BEGIN + SELECT channel_family_id INTO channel_family_id_val + FROM rhnChannelFamilyMembers + WHERE channel_id = channel_id_in; + + RETURN rhn_channel.available_family_subscriptions( + channel_family_id_val, org_id_in); + END available_chan_subscriptions; + + FUNCTION available_fve_chan_subs(channel_id_in IN NUMBER, + org_id_in IN NUMBER) + RETURN NUMBER + IS + channel_family_id_val NUMBER; + + BEGIN + SELECT channel_family_id INTO channel_family_id_val + FROM rhnChannelFamilyMembers + WHERE channel_id = channel_id_in; + + RETURN rhn_channel.available_fve_family_subs( channel_family_id_val, org_id_in); + END available_fve_chan_subs; + + procedure unsubscribe_server_from_family(server_id_in in number, + channel_family_id_in in number) + is + begin + delete + from rhnServerChannel rsc + where rsc.server_id = server_id_in + and channel_id in ( + select rcfm.channel_id + from rhnChannelFamilyMembers rcfm + where rcfm.channel_family_id = channel_family_id_in); + end; + + function get_org_id(channel_id_in in number) + return number + is + org_id_out number; + begin + select org_id into org_id_out + from rhnChannel + where id = channel_id_in; + + return org_id_out; + end get_org_id; + + function get_cfam_org_access(cfam_id_in in number, org_id_in in number) + return number + is + cursor families is + 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 + return 1; + end loop; + return 0; + end; + + function get_org_access(channel_id_in in number, org_id_in in number) + return number + is + throwaway number; + 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 + 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.fve_max_members > 0 or CFP.fve_max_members is null or CFP.org_id = 1); + + return 1; + exception + when no_data_found + then + return 0; + end; + + -- check if a user has a given role, or if such a role is inferrable + -- returns NULL if OK, error message otherwise + function user_role_check_debug(channel_id_in in number, + user_id_in in number, + role_in in varchar2) + return varchar2 + is + org_id number; + begin + org_id := rhn_user.get_org_id(user_id_in); + + -- channel might be shared + if role_in = 'subscribe' and + rhn_channel.shared_user_role_check(channel_id_in, user_id_in, role_in) = 1 then + return NULL; + end if; + + if role_in = 'manage' and + NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then + return 'channel_not_owned'; + end if; + + if role_in = 'subscribe' and + rhn_channel.get_org_access(channel_id_in, org_id) = 0 then + return 'channel_not_available'; + end if; + + -- channel admins have all roles + if rhn_user.check_role_implied(user_id_in, 'channel_admin') = 1 then + return NULL; + end if; + + -- the subscribe permission is inferred + -- UNLESS the not_globally_subscribable flag is set + if role_in = 'subscribe' + then + if rhn_channel.org_channel_setting(channel_id_in, + org_id, + 'not_globally_subscribable') = 0 then + return NULL; + end if; + end if; + + -- all other roles (manage right now) are explicitly granted + if rhn_channel.direct_user_role_check(channel_id_in, + user_id_in, role_in) = 1 then + return NULL; + end if; + return 'direct_permission'; + end; + + -- same as above, but with 1/0 output; useful in views, etc + function user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) + return number + is + begin + if rhn_channel.user_role_check_debug(channel_id_in, + user_id_in, role_in) is NULL then + return 1; + else + return 0; + end if; + end; + + -- + -- For multiorg phase II, this function simply checks to see if the user's + -- has a trust relationship that includes this channel by id. + -- + function shared_user_role_check(channel_id in number, user_id in number, role in varchar2) + return number + is + n number; + oid number; + begin + oid := rhn_user.get_org_id(user_id); + select 1 into n + from rhnSharedChannelView s + where s.id = channel_id and s.org_trust_id = oid; + return 1; + exception + when no_data_found then + return 0; + end; + + -- same as above, but returns 1 if user_id_in is null + -- This is useful in queries where user_id is not specified + function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) + return number + is + begin + if user_id_in is null then + return 1; + end if; + return user_role_check(channel_id_in, user_id_in, role_in); + end loose_user_role_check; + + -- directly checks the table, no inferred permissions + function direct_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) + return number + is + throwaway number; + begin + -- the idea: if we get past this query, the user has the role, else catch the exception and return 0 + select 1 into throwaway + from rhnChannelPermissionRole CPR, + rhnChannelPermission CP + where CP.user_id = user_id_in + and CP.channel_id = channel_id_in + and CPR.label = role_in + and CP.role_id = CPR.id; + + return 1; + exception + when no_data_found + then + return 0; + end; + + -- check if an org has a certain setting + function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in in varchar2) + return number + is + throwaway number; + begin + -- the idea: if we get past this query, the org has the setting, else catch the exception and return 0 + select 1 into throwaway + from rhnOrgChannelSettingsType OCST, + rhnOrgChannelSettings OCS + where OCS.org_id = org_id_in + 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; + end; + + FUNCTION channel_priority(channel_id_in IN number) + RETURN number + IS + channel_name varchar2(256); + priority number; + end_of_life_val timestamp with local time zone; + org_id_val number; + BEGIN + + select name, end_of_life, org_id + into channel_name, end_of_life_val, org_id_val + from rhnChannel + where id = channel_id_in; + + if end_of_life_val is not null then + return -400; + end if; + + if channel_name like 'Red Hat Enterprise Linux%' or channel_name like 'RHEL%' then + priority := 1000; + if channel_name not like '%Beta%' then + priority := priority + 1000; + end if; + + priority := priority + + case + when channel_name like '%v. 5%' then 600 + when channel_name like '%v. 4%' then 500 + when channel_name like '%v. 3%' then 400 + when channel_name like '%v. 2%' then 300 + when channel_name like '%v. 1%' then 200 + else 0 + end; + + priority := priority + + case + when channel_name like 'Red Hat Enterprise Linux (v. 5%' then 60 + when (channel_name like '%AS%' and channel_name not like '%Extras%') then 50 + when (channel_name like '%ES%' and channel_name not like '%Extras%') then 40 + when (channel_name like '%WS%' and channel_name not like '%Extras%') then 30 + when (channel_name like '%Desktop%' and channel_name not like '%Extras%') then 20 + when channel_name like '%Extras%' then 10 + else 0 + end; + + priority := priority + + case + when channel_name like '%)' then 5 + else 0 + end; + + priority := priority + + case + when channel_name like '%32-bit x86%' then 4 + when channel_name like '%64-bit Intel Itanium%' then 3 + when channel_name like '%64-bit AMD64/Intel EM64T%' then 2 + else 0 + end; + elsif channel_name like 'Red Hat Desktop%' then + priority := 900; + + if channel_name not like '%Beta%' then + priority := priority + 50; + end if; + + priority := priority + + case + when channel_name like '%v. 4%' then 40 + when channel_name like '%v. 3%' then 30 + when channel_name like '%v. 2%' then 20 + when channel_name like '%v. 1%' then 10 + else 0 + end; + + priority := priority + + case + when channel_name like '%32-bit x86%' then 4 + when channel_name like '%64-bit Intel Itanium%' then 3 + when channel_name like '%64-bit AMD64/Intel EM64T%' then 2 + else 0 + end; + + elsif org_id_val is not null then + priority := 600; + else + priority := 500; + end if; + + return -priority; + + end channel_priority; + + -- right now this only does the accounting changes; the cascade + -- actually does the rhnServerChannel delete. + procedure delete_server_channels(server_id_in in number) + is + begin + update rhnPrivateChannelFamily + set current_members = current_members -1 + where org_id in ( + select org_id + from rhnServer + where id = server_id_in + ) + and channel_family_id in ( + select rcfm.channel_family_id + from rhnChannelFamilyMembers rcfm, + rhnServerChannel rsc + where rsc.server_id = server_id_in + and rsc.channel_id = rcfm.channel_id + and not exists ( + select 1 + from + rhnChannelFamilyVirtSubLevel cfvsl, + rhnSGTypeVirtSubLevel sgtvsl, + rhnServerEntitlementView sev, + rhnVirtualInstance vi + where + -- system is a virtual instance + vi.virtual_system_id = server_id_in + and vi.host_system_id = sev.server_id + -- system's host has a virt ent + and sev.label in ('virtualization_host', + 'virtualization_host_platform') + and sev.server_group_type_id = + sgtvsl.server_group_type_id + -- the host's virt ent grants a cf virt sub level + and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id + -- the cf is in that virt sub level + and cfvsl.channel_family_id = rcfm.channel_family_id + ) + ); + end; + + -- this could certainly be optimized to do updates if needs be + procedure refresh_newest_package(channel_id_in in number, + caller_in in varchar2 := '(unknown)', + package_name_id_in in number := null) + is + -- procedure refreshes rows for name_id = package_name_id_in or + -- all rows if package_name_id_in is null + begin + delete from rhnChannelNewestPackage + where channel_id = channel_id_in + and (package_name_id_in is null + or name_id = package_name_id_in); + insert into rhnChannelNewestPackage + (channel_id, name_id, evr_id, package_id, package_arch_id) + (select channel_id, + name_id, evr_id, + package_id, package_arch_id + from rhnChannelNewestPackageView + where channel_id = channel_id_in + and (package_name_id_in is null + or name_id = package_name_id_in) + ); + insert into rhnChannelNewestPackageAudit (channel_id, caller) + values (channel_id_in, caller_in); + update rhnChannel + set last_modified = greatest(current_timestamp, last_modified + interval '1' second) + where id = channel_id_in; + end; + + procedure update_channel ( channel_id_in in number, invalidate_ss in number := 0, + date_to_use in timestamp with local time zone := current_timestamp ) + is + + channel_last_modified timestamp with local time zone; + last_modified_value timestamp with local time zone; + + cursor snapshots is + select snapshot_id id + from rhnSnapshotChannel + where channel_id = channel_id_in; + + begin + + select last_modified + into channel_last_modified + from rhnChannel + where id = channel_id_in; + + last_modified_value := date_to_use; + + if last_modified_value <= channel_last_modified then + last_modified_value := last_modified_value + 1/86400; + end if; + + update rhnChannel set last_modified = last_modified_value + where id = channel_id_in; + + if invalidate_ss = 1 then + for snapshot in snapshots loop + update rhnSnapshot + set invalid = lookup_snapshot_invalid_reason('channel_modified') + where id = snapshot.id; + end loop; + end if; + + end update_channel; + + procedure update_channels_by_package ( package_id_in in number, date_to_use in timestamp with local time zone := current_timestamp ) + is + + cursor channels is + select channel_id + from rhnChannelPackage + where package_id = package_id_in + order by channel_id; + + begin + for channel in channels loop + -- we want to invalidate the snapshot assocated with the channel when we + -- do this b/c we know we've added or removed or packages + rhn_channel.update_channel ( channel.channel_id, 1, date_to_use ); + end loop; + end update_channels_by_package; + + + procedure update_channels_by_errata ( errata_id_in number, date_to_use in timestamp with local time zone := current_timestamp ) + is + + cursor channels is + select channel_id + from rhnChannelErrata + where errata_id = errata_id_in + order by channel_id; + + begin + for channel in channels loop + -- we won't invalidate snapshots, b/c just changing the errata associated with + -- a channel shouldn't invalidate snapshots + rhn_channel.update_channel ( channel.channel_id, 0, date_to_use ); + end loop; + end update_channels_by_errata; + + procedure update_needed_cache(channel_id_in in number) + is + -- update of needed cache ican be commited on a per server basis + -- b/c failure of update for a server means nothing for the other servers + begin + -- we intentionaly do a loop here instead of one huge select + -- b/c we want to break update into smaller transaction to unblock other sessions + -- querying rhnServerNeededCache + for server in ( + select sc.server_id as id + from rhnServerChannel sc + where sc.channel_id = channel_id_in + ) loop + rhn_server.update_needed_cache(server.id); + commit; + end loop; + end update_needed_cache; + + procedure set_comps(channel_id_in in number, path_in in varchar2, timestamp_in in varchar2) + is + begin + for row in ( + select relative_filename, last_modified + from rhnChannelComps + where channel_id = channel_id_in + ) channel_id_in) loop + if row.relative_filename = :path_in + and row.last_modified = to_date(:timestamp_in, 'YYYYMMDDHH24MISS') then + return; + end if; + end loop; + delete from rhnChannelComps + where channel_id = :channel_id_in; + insert into rhnChannelComps (id, channel_id, relative_filename, last_modified, created, modified) + values (sequence_nextval('rhn_channelcomps_id_seq'), :channel_id_in, :path_in, to_date(:timestamp_in, 'YYYYMMDDHH24MISS'), current_timestamp, current_timestamp); + end set_comps; + +END rhn_channel; +/ +SHOW ERRORS diff --git a/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.postgresql b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.postgresql new file mode 100644 index 0000000..feda1ce --- /dev/null +++ b/schema/spacewalk/upgrade/spacewalk-schema-1.8-to-spacewalk-schema-1.9/011-rhn_channel.pkb.postgresql @@ -0,0 +1,47 @@ +-- oracle equivalent source sha1 e53c181a3223101f00b87b186f283b3ce4f3ca8a +-- +-- Copyright (c) 2008--2013 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_channel; + +--update pg_setting +update pg_settings set setting = 'rhn_channel,' || setting where name = 'search_path'; + + create or replace function set_comps(channel_id_in in numeric, path_in in varchar, timestamp_in in varchar) returns void + as $$ + declare + row record; + begin + for row in ( + select relative_filename, last_modified + from rhnChannelComps + where channel_id = channel_id_in + ) loop + if row.relative_filename = path_in + and row.last_modified = to_date(timestamp_in, 'YYYYMMDDHH24MISS') then + return; + end if; + end loop; + delete from rhnChannelComps + where channel_id = channel_id_in; + insert into rhnChannelComps (id, channel_id, relative_filename, last_modified, created, modified) + values (sequence_nextval('rhn_channelcomps_id_seq'), channel_id_in, path_in, to_date(timestamp_in, 'YYYYMMDDHH24MISS'), current_timestamp, current_timestamp); + end$$ language plpgsql; + +-- restore the original setting +update pg_settings set setting = overlay( setting placing '' from 1 for (length('rhn_channel')+1) ) where name = 'search_path';