RHN_CHANNEL
DDL scriptPackage source
Legend: string keyword reserved word operator
1: PACKAGE rhn_channel
2: IS
3: version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: CURSOR server_base_subscriptions(server_id_in NUMBER) IS
5: SELECT C.id
6: FROM rhnChannel C, rhnServerChannel SC
7: WHERE C.id = SC.channel_id
8: AND SC.server_id = server_id_in
9: AND C.parent_channel IS NULL;
10: CURSOR check_server_subscription(server_id_in NUMBER, channel_id_in NUMBER) IS
11: SELECT channel_id
12: FROM rhnServerChannel
13: WHERE server_id = server_id_in
14: AND channel_id = channel_id_in;
15: CURSOR check_server_parent_membership(server_id_in NUMBER, channel_id_in NUMBER) IS
16: SELECT C.id
17: FROM rhnChannel C, rhnServerChannel SC
18: WHERE C.parent_channel = channel_id_in
19: AND C.id = SC.channel_id
20: AND SC.server_id = server_id_in;
21: CURSOR channel_family_perm_cursor(channel_family_id_in NUMBER, org_id_in NUMBER) IS
22: SELECT *
23: FROM rhnOrgChannelFamilyPermissions
24: WHERE channel_family_id = channel_family_id_in
25: AND org_id = org_id_in;
26: PROCEDURE license_consent(channel_id_in IN NUMBER, user_id_in IN NUMBER, server_id_in IN NUMBER);
27: FUNCTION get_license_path(channel_id_in IN NUMBER) RETURN VARCHAR2;
28: PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in number := 0,
29: deleting_server in number := 0 );
30: PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in number := null);
31: function can_server_consume_virt_channl(
32: server_id_in IN NUMBER,
33: family_id_in in number)
34: return number;
35: FUNCTION guess_server_base(server_id_in IN NUMBER) RETURN NUMBER;
36: FUNCTION base_channel_for_release_arch(release_in in varchar2,
37: server_arch_in in varchar2, org_id_in in number := -1,
38: user_id_in in number := null) RETURN number;
39: FUNCTION base_channel_rel_archid(release_in in varchar2,
40: server_arch_id_in in number, org_id_in in number := -1,
41: user_id_in in number := null) RETURN number;
42: FUNCTION channel_priority(channel_id_in in number) RETURN number;
43: PROCEDURE bulk_subscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER);
44: PROCEDURE bulk_unsubscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER);
45: PROCEDURE bulk_server_base_change(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER);
46: procedure bulk_server_basechange_from(
47: set_label_in in varchar2,
48: set_uid_in in number,
49: old_channel_id_in in number,
50: new_channel_id_in in number);
51: procedure bulk_guess_server_base(
52: set_label_in in varchar2,
53: set_uid_in in number);
54: procedure bulk_guess_server_base_from(
55: set_label_in in varchar2,
56: set_uid_in in number,
57: channel_id_in in number);
58: PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server in number := 0 );
59: FUNCTION available_family_subscriptions(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) RETURN NUMBER;
60: function channel_family_current_members(channel_family_id_in IN NUMBER,
61: org_id_in IN NUMBER)
62: return number;
63: PROCEDURE update_family_counts(channel_family_id_in IN NUMBER, org_id_in IN NUMBER);
64: FUNCTION family_for_channel(channel_id_in IN NUMBER) RETURN NUMBER;
65: FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER, org_id_in IN NUMBER) RETURN NUMBER;
66: procedure entitle_customer(customer_id_in in number, channel_family_id_in in number, quantity_in in number);
67: procedure set_family_maxmembers(customer_id_in in number, channel_family_id_in in number, quantity_in in number);
68: procedure unsubscribe_server_from_family(server_id_in in number, channel_family_id_in in number);
69: procedure delete_server_channels(server_id_in in number);
70: procedure refresh_newest_package(channel_id_in in number, caller_in in varchar2 := '(unknown)');
71: function get_org_id(channel_id_in in number) return number;
72: PRAGMA RESTRICT_REFERENCES(get_org_id, WNDS, RNPS, WNPS);
73: function get_org_access(channel_id_in in number, org_id_in in number) return number;
74: PRAGMA RESTRICT_REFERENCES(get_org_access, WNDS, RNPS, WNPS);
75: function get_cfam_org_access(cfam_id_in in number, org_id_in in number) return number;
76: function user_role_check_debug(channel_id_in in number, user_id_in in number, role_in in varchar2, reason_out out varchar2)
77: RETURN NUMBER;
78: PRAGMA RESTRICT_REFERENCES(user_role_check_debug, WNDS, RNPS, WNPS);
79: function user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
80: RETURN NUMBER;
81: PRAGMA RESTRICT_REFERENCES(user_role_check, WNDS, RNPS, WNPS);
82: function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
83: RETURN NUMBER;
84: PRAGMA RESTRICT_REFERENCES(loose_user_role_check, WNDS, RNPS, WNPS);
85: function direct_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
86: RETURN NUMBER;
87: PRAGMA RESTRICT_REFERENCES(direct_user_role_check, WNDS, RNPS, WNPS);
88: function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in in varchar2)
89: RETURN NUMBER;
90: PROCEDURE update_channel ( channel_id_in in number, invalidate_ss in number := 0,
91: date_to_use in date := sysdate );
92: PROCEDURE update_channels_by_package ( package_id_in in number, date_to_use in date := sysdate );
93: PROCEDURE update_channels_by_errata ( errata_id_in number, date_to_use in date := sysdate );
94: PRAGMA RESTRICT_REFERENCES(org_channel_setting, WNDS, RNPS, WNPS);
95: END rhn_channel;
Package body source
Legend: string keyword reserved word operator
1: PACKAGE BODY rhn_channel
2: IS
3: body_version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: cursor base_channel_cursor(
5: release_in in varchar2,
6: server_arch_id_in in number,
7: org_id_in in number
8: ) return rhnChannel%ROWTYPE is
9: select distinct c.*
10: from rhnDistChannelMap dcm,
11: rhnServerChannelArchCompat scac,
12: rhnChannel c,
13: rhnChannelPermissions cp
14: where cp.org_id = org_id_in
15: and cp.channel_id = c.id
16: and c.parent_channel is null
17: and c.id = dcm.channel_id
18: and c.channel_arch_id = dcm.channel_arch_id
19: and dcm.release = release_in
20: and scac.server_arch_id = server_arch_id_in
21: and scac.channel_arch_id = c.channel_arch_id;
22: FUNCTION get_license_path(channel_id_in IN NUMBER)
23: RETURN VARCHAR2
24: IS
25: license_val VARCHAR2(1000);
26: BEGIN
27: SELECT CFL.license_path INTO license_val
28: FROM rhnChannelFamilyLicense CFL, rhnChannelFamilyMembers CFM
29: WHERE CFM.channel_id = channel_id_in
30: AND CFM.channel_family_id = CFL.channel_family_id;
31: RETURN license_val;
32: EXCEPTION
33: WHEN NO_DATA_FOUND
34: THEN
35: RETURN NULL;
36: END get_license_path;
37: PROCEDURE license_consent(channel_id_in IN NUMBER, user_id_in IN NUMBER, server_id_in IN NUMBER)
38: IS
39: channel_family_id_val NUMBER;
40: BEGIN
41: channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
42: IF channel_family_id_val IS NULL
43: THEN
44: rhn_exception.raise_exception('channel_subscribe_no_family');
45: END IF;
46: IF rhn_channel.get_license_path(channel_id_in) IS NULL
47: THEN
48: rhn_exception.raise_exception('channel_consent_no_license');
49: END IF;
50: INSERT INTO rhnChannelFamilyLicenseConsent (channel_family_id, user_id, server_id)
51: VALUES (channel_family_id_val, user_id_in, server_id_in);
52: END license_consent;
53: PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in in number := null)
54: IS
55: channel_parent_val rhnChannel.parent_channel%TYPE;
56: parent_subscribed BOOLEAN;
57: server_has_base_chan BOOLEAN;
58: server_already_in_chan BOOLEAN;
59: channel_family_id_val NUMBER;
60: server_org_id_val NUMBER;
61: available_subscriptions NUMBER;
62: consenting_user NUMBER;
63: allowed number := 0;
64: current_members_val number;
65: BEGIN
66: if user_id_in is not null then
67: allowed := rhn_channel.user_role_check(channel_id_in, user_id_in, 'subscribe');
68: else
69: allowed := 1;
70: end if;
71: if allowed = 0 then
72: rhn_exception.raise_exception('no_subscribe_permissions');
73: end if;
74: SELECT parent_channel INTO channel_parent_val FROM rhnChannel WHERE id = channel_id_in;
75: IF channel_parent_val IS NOT NULL
76: THEN
77: parent_subscribed := FALSE;
78: FOR check_subscription IN check_server_subscription(server_id_in, channel_parent_val)
79: LOOP
80: parent_subscribed := TRUE;
81: END LOOP check_subscription;
82: IF NOT parent_subscribed
83: THEN
84: RETURN;
85: END IF;
86: ELSE
87: server_has_base_chan := FALSE;
88: FOR base IN server_base_subscriptions(server_id_in)
89: LOOP
90: server_has_base_chan := TRUE;
91: END LOOP base;
92: IF server_has_base_chan
93: THEN
94: rhn_exception.raise_exception('channel_server_one_base');
95: END IF;
96: END IF;
97: FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in)
98: LOOP
99: server_already_in_chan := TRUE;
100: END LOOP check_subscription;
101: IF server_already_in_chan
102: THEN
103: RETURN;
104: END IF;
105: channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
106: IF channel_family_id_val IS NULL
107: THEN
108: rhn_exception.raise_exception('channel_subscribe_no_family');
109: END IF;
110: SELECT org_id INTO server_org_id_val
111: FROM rhnServer
112: WHERE id = server_id_in;
113: select current_members
114: into current_members_val
115: from rhnPrivateChannelFamily
116: where org_id = server_org_id_val and channel_family_id = channel_family_id_val
117: for update of current_members;
118: available_subscriptions := rhn_channel.available_family_subscriptions(channel_family_id_val, server_org_id_val);
119: IF available_subscriptions IS NULL OR
120: available_subscriptions > 0 or
121: can_server_consume_virt_channl(server_id_in, channel_family_id_val) = 1
122: THEN
123: IF rhn_channel.get_license_path(channel_id_in) IS NOT NULL
124: THEN
125: BEGIN
126: SELECT user_id INTO consenting_user
127: FROM rhnChannelFamilyLicenseConsent
128: WHERE channel_family_id = channel_family_id_val
129: AND server_id = server_id_in;
130: EXCEPTION
131: WHEN NO_DATA_FOUND
132: THEN
133: rhn_exception.raise_exception('channel_subscribe_no_consent');
134: END;
135: END IF;
136: insert into rhnServerHistory (id,server_id,summary,details) (
137: select rhn_event_id_seq.nextval,
138: server_id_in,
139: 'subscribed to channel ' || SUBSTR(c.label, 0, 106),
140: c.label
141: from rhnChannel c
142: where c.id = channel_id_in
143: );
144: UPDATE rhnServer SET channels_changed = sysdate WHERE id = server_id_in;
145: INSERT INTO rhnServerChannel (server_id, channel_id) VALUES (server_id_in, channel_id_in);
146: rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val);
147: queue_server(server_id_in, immediate_in);
148: ELSE
149: rhn_exception.raise_exception('channel_family_no_subscriptions');
150: END IF;
151: END subscribe_server;
152: function can_server_consume_virt_channl(
153: server_id_in in number,
154: family_id_in in number )
155: return number
156: is
157: cursor server_virt_families is
158: select vi.virtual_system_id, cfvsl.channel_family_id
159: from
160: rhnChannelFamilyVirtSubLevel cfvsl,
161: rhnSGTypeVirtSubLevel sgtvsl,
162: rhnVirtualInstance vi
163: where
164: vi.virtual_system_id = server_id_in
165: and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id
166: and cfvsl.channel_family_id = family_id_in
167: and exists (
168: select 1
169: from rhnServerEntitlementView sev
170: where vi.host_system_id = sev.server_id
171: and sev.server_group_type_id = sgtvsl.server_group_type_id );
172: begin
173: for server_virt_family in server_virt_families loop
174: return 1;
175: end loop;
176: return 0;
177: end;
178: PROCEDURE bulk_subscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER)
179: IS
180: BEGIN
181: FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
182: LOOP
183: rhn_channel.subscribe_server(server.element, channel_id_in, 0, set_uid_in);
184: END LOOP server;
185: END bulk_subscribe_server;
186: PROCEDURE bulk_server_base_change(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER)
187: IS
188: BEGIN
189: FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
190: LOOP
191: IF rhn_server.can_change_base_channel(server.element) = 1
192: THEN
193: rhn_channel.clear_subscriptions(TO_NUMBER(server.element));
194: rhn_channel.subscribe_server(server.element, channel_id_in, 0, set_uid_in);
195: END IF;
196: END LOOP server;
197: END bulk_server_base_change;
198: procedure bulk_server_basechange_from(
199: set_label_in in varchar2,
200: set_uid_in in number,
201: old_channel_id_in in number,
202: new_channel_id_in in number
203: ) is
204: cursor servers is
205: select sc.server_id id
206: from rhnChannel nc,
207: rhnServerChannelArchCompat scac,
208: rhnServer s,
209: rhnChannel oc,
210: rhnServerChannel sc,
211: rhnSet st
212: where 1=1
213: and st.label = set_label_in
214: and st.user_id = set_uid_in
215: and st.element = sc.server_id
216: and sc.channel_id = old_channel_id_in
217: and sc.channel_id = oc.id
218: and oc.parent_channel is null
219: and nc.id = new_channel_id_in
220: and nc.parent_channel is null
221: and sc.server_id = s.id
222: and s.server_arch_id = scac.server_arch_id
223: and scac.channel_arch_id = nc.channel_arch_id;
224: begin
225: for s in servers loop
226: insert into rhnSet (
227: user_id, label, element
228: ) values (
229: set_uid_in,
230: set_label_in || 'basechange',
231: s.id
232: );
233: end loop channel;
234: bulk_server_base_change(new_channel_id_in,
235: set_label_in || 'basechange',
236: set_uid_in);
237: delete from rhnSet
238: where label = set_label_in||'basechange'
239: and user_id = set_uid_in;
240: end bulk_server_basechange_from;
241: procedure bulk_guess_server_base(
242: set_label_in in varchar2,
243: set_uid_in in number
244: ) is
245: channel_id number;
246: begin
247: for server in rhn_set.set_iterator(set_label_in, set_uid_in)
248: loop
249: begin
250: if rhn_server.can_change_base_channel(server.element) = 1
251: then
252: channel_id := guess_server_base(TO_NUMBER(server.element));
253: rhn_channel.clear_subscriptions(TO_NUMBER(server.element));
254: rhn_channel.subscribe_server(TO_NUMBER(server.element), channel_id, 0, set_uid_in);
255: end if;
256: exception when others then
257: null;
258: end;
259: end loop server;
260: end;
261: function guess_server_base(
262: server_id_in in number
263: ) RETURN number is
264: cursor server_cursor is
265: select s.server_arch_id, s.release, s.org_id
266: from rhnServer s
267: where s.id = server_id_in;
268: begin
269: for s in server_cursor loop
270: for channel in base_channel_cursor(s.release,
271: s.server_arch_id, s.org_id)
272: loop
273: return channel.id;
274: end loop base_channel_cursor;
275: end loop server_cursor;
276: return null;
277: end;
278: function normalize_server_arch(server_arch_in in varchar2)
279: return varchar2
280: is
281: suffix VARCHAR2(128) := '-redhat-linux';
282: suffix_len NUMBER := length(suffix);
283: begin
284: if server_arch_in is NULL then
285: return NULL;
286: end if;
287: if instr(server_arch_in, '-') > 0
288: then
289: return server_arch_in;
290: end if;
291: return server_arch_in || suffix;
292: end normalize_server_arch;
293: function base_channel_for_release_arch(
294: release_in in varchar2,
295: server_arch_in in varchar2,
296: org_id_in in number := -1,
297: user_id_in in number := null
298: ) return number is
299: server_arch varchar2(256) := normalize_server_arch(server_arch_in);
300: server_arch_id number;
301: begin
302: begin
303: select id
304: into server_arch_id
305: from rhnServerArch
306: where label = server_arch;
307: exception
308: when no_data_found then
309: rhn_exception.raise_exception('server_arch_not_found');
310: end;
311: return base_channel_rel_archid(release_in, server_arch_id,
312: org_id_in, user_id_in);
313: end base_channel_for_release_arch;
314: function base_channel_rel_archid(
315: release_in in varchar2,
316: server_arch_id_in in number,
317: org_id_in in number := -1,
318: user_id_in in number := null
319: ) return number is
320: denied_channel_id number := null;
321: valid_org_id number := org_id_in;
322: valid_user_id number := user_id_in;
323: channel_subscribable number;
324: begin
325: if org_id_in = -1 and user_id_in is not null then
326: begin
327: select org_id
328: into valid_org_id
329: from web_contact
330: where id = user_id_in;
331: exception
332: when no_data_found then
333: valid_user_id := null;
334: valid_org_id := -1;
335: end;
336: end if;
337: for c in base_channel_cursor(release_in, server_arch_id_in, valid_org_id)
338: loop
339: if valid_user_id is null then
340: return c.id;
341: end if;
342: select loose_user_role_check(c.id, user_id_in, 'subscribe')
343: into channel_subscribable
344: from dual;
345: if channel_subscribable = 1 then
346: return c.id;
347: end if;
348: denied_channel_id := c.id;
349: end loop base_channel_fetch;
350: if denied_channel_id is not null then
351: rhn_exception.raise_exception('no_subscribe_permissions');
352: end if;
353: return NULL;
354: end base_channel_rel_archid;
355: procedure bulk_guess_server_base_from(
356: set_label_in in varchar2,
357: set_uid_in in number,
358: channel_id_in in number
359: ) is
360: cursor channels(server_id_in in number) is
361: select rsc.channel_id
362: from rhnServerChannel rsc,
363: rhnChannel rc
364: where server_id_in = rsc.server_id
365: and rsc.channel_id = rc.id
366: and rc.parent_channel is null;
367: begin
368: for server in rhn_set.set_iterator(set_label_in, set_uid_in)
369: loop
370: for channel in channels(server.element)
371: loop
372: if channel.channel_id = channel_id_in
373: then
374: insert into rhnSet (user_id, label, element) values (set_uid_in, set_label_in || 'baseguess', server.element);
375: end if;
376: end loop channel;
377: end loop server;
378: bulk_guess_server_base(set_label_in||'baseguess',set_uid_in);
379: delete from rhnSet where label = set_label_in||'baseguess' and user_id = set_uid_in;
380: end;
381: PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server IN NUMBER := 0 )
382: IS
383: cursor server_channels(server_id_in in number) is
384: select s.org_id, sc.channel_id, cfm.channel_family_id
385: from rhnServer s,
386: rhnServerChannel sc,
387: rhnChannelFamilyMembers cfm
388: where s.id = server_id_in
389: and s.id = sc.server_id
390: and sc.channel_id = cfm.channel_id;
391: BEGIN
392: for channel in server_channels(server_id_in)
393: loop
394: unsubscribe_server(server_id_in, channel.channel_id, 1, 1, deleting_server);
395: rhn_channel.update_family_counts(channel.channel_family_id, channel.org_id);
396: end loop channel;
397: END clear_subscriptions;
398: PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in number := 0,
399: deleting_server IN NUMBER := 0 )
400: IS
401: channel_family_id_val NUMBER;
402: server_org_id_val NUMBER;
403: available_subscriptions NUMBER;
404: server_already_in_chan BOOLEAN;
405: cursor channel_family_is_proxy(channel_family_id_in in number) is
406: select 1
407: from rhnChannelFamily
408: where id = channel_family_id_in
409: and label = 'rhn-proxy';
410: cursor channel_family_is_satellite(channel_family_id_in in number) is
411: select 1
412: from rhnChannelFamily
413: where id = channel_family_id_in
414: and label = 'rhn-satellite';
415: cursor local_chk_server_parent_memb (
416: server_id_in number,
417: channel_id_in number ) is
418: select c.id
419: from rhnChannel c,
420: rhnServerChannel sc
421: where 1=1
422: and c.parent_channel = channel_id_in
423: and c.id = sc.channel_id
424: and sc.server_id = server_id_in;
425: BEGIN
426: FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in)
427: LOOP
428: if unsubscribe_children_in = 1 then
429: unsubscribe_server(server_id_in => server_id_in,
430: channel_id_in => child.id,
431: immediate_in => immediate_in,
432: unsubscribe_children_in => unsubscribe_children_in,
433: deleting_server => deleting_server);
434: else
435: rhn_exception.raise_exception('channel_unsubscribe_child_exists');
436: end if;
437: END LOOP child;
438: server_already_in_chan := FALSE;
439: FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in)
440: LOOP
441: server_already_in_chan := TRUE;
442: END LOOP check_subscription;
443: IF NOT server_already_in_chan
444: THEN
445: RETURN;
446: END IF;
447: if deleting_server = 0 then
448: insert into rhnServerHistory (id,server_id,summary,details) (
449: select rhn_event_id_seq.nextval,
450: server_id_in,
451: 'unsubscribed from channel ' || SUBSTR(c.label, 0, 106),
452: c.label
453: from rhnChannel c
454: where c.id = channel_id_in
455: );
456: UPDATE rhnServer SET channels_changed = sysdate WHERE id = server_id_in;
457: end if;
458: DELETE FROM rhnServerChannel WHERE server_id = server_id_in AND channel_id = channel_id_in;
459: if deleting_server = 0 then
460: queue_server(server_id_in, immediate_in);
461: end if;
462: channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
463: IF channel_family_id_val IS NULL
464: THEN
465: rhn_exception.raise_exception('channel_unsubscribe_no_family');
466: END IF;
467: for ignore in channel_family_is_satellite(channel_family_id_val) loop
468: delete from rhnSatelliteInfo where server_id = server_id_in;
469: delete from rhnSatelliteChannelFamily where server_id = server_id_in;
470: end loop;
471: for ignore in channel_family_is_proxy(channel_family_id_val) loop
472: delete from rhnProxyInfo where server_id = server_id_in;
473: end loop;
474: DELETE FROM rhnChannelFamilyLicenseConsent
475: WHERE channel_family_id = channel_family_id_val
476: AND server_id = server_id_in;
477: SELECT org_id INTO server_org_id_val
478: FROM rhnServer
479: WHERE id = server_id_in;
480: rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val);
481: END unsubscribe_server;
482: PROCEDURE bulk_unsubscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER)
483: IS
484: BEGIN
485: FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in)
486: LOOP
487: rhn_channel.unsubscribe_server(server.element, channel_id_in, 0);
488: END LOOP server;
489: END bulk_unsubscribe_server;
490: FUNCTION family_for_channel(channel_id_in IN NUMBER)
491: RETURN NUMBER
492: IS
493: channel_family_id_val NUMBER;
494: BEGIN
495: SELECT channel_family_id INTO channel_family_id_val
496: FROM rhnChannelFamilyMembers
497: WHERE channel_id = channel_id_in;
498: RETURN channel_family_id_val;
499: EXCEPTION
500: WHEN NO_DATA_FOUND
501: THEN
502: RETURN NULL;
503: END family_for_channel;
504: FUNCTION available_family_subscriptions(channel_family_id_in IN NUMBER, org_id_in IN NUMBER)
505: RETURN NUMBER
506: IS
507: cfp channel_family_perm_cursor%ROWTYPE;
508: current_members_val NUMBER;
509: max_members_val NUMBER;
510: found NUMBER;
511: BEGIN
512: IF NOT channel_family_perm_cursor%ISOPEN
513: THEN
514: OPEN channel_family_perm_cursor(channel_family_id_in, org_id_in);
515: END IF;
516: FETCH channel_family_perm_cursor INTO cfp;
517: WHILE channel_family_perm_cursor%FOUND
518: LOOP
519: found := 1;
520: current_members_val := cfp.current_members;
521: max_members_val := cfp.max_members;
522: FETCH channel_family_perm_cursor INTO cfp;
523: END LOOP;
524: IF channel_family_perm_cursor%ISOPEN
525: THEN
526: CLOSE channel_family_perm_cursor;
527: END IF;
528: IF found IS NULL
529: THEN
530: RETURN 0;
531: END IF;
532: IF max_members_val IS NULL
533: THEN
534: RETURN NULL;
535: END IF;
536: RETURN max_members_val - current_members_val;
537: END available_family_subscriptions;
538: function channel_family_current_members(channel_family_id_in IN NUMBER,
539: org_id_in IN NUMBER)
540: return number
541: is
542: current_members_count number := 0;
543: begin
544: select count(sc.server_id)
545: into current_members_count
546: from rhnChannelFamilyMembers cfm,
547: rhnServerChannel sc,
548: rhnServer s
549: where s.org_id = org_id_in
550: and s.id = sc.server_id
551: and cfm.channel_family_id = channel_family_id_in
552: and cfm.channel_id = sc.channel_id
553: and exists (
554: select 1
555: from rhnChannelFamilyServerPhysical cfsp
556: where cfsp.CHANNEL_FAMILY_ID = channel_family_id_in
557: and cfsp.server_id = s.id
558: );
559: return current_members_count;
560: end;
561: PROCEDURE update_family_counts(channel_family_id_in IN NUMBER,
562: org_id_in IN NUMBER)
563: IS
564: BEGIN
565: update rhnPrivateChannelFamily
566: set current_members = (
567: channel_family_current_members(channel_family_id_in, org_id_in)
568: )
569: where org_id = org_id_in
570: and channel_family_id = channel_family_id_in;
571: END update_family_counts;
572: FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER,
573: org_id_in IN NUMBER)
574: RETURN NUMBER
575: IS
576: channel_family_id_val NUMBER;
577: BEGIN
578: SELECT channel_family_id INTO channel_family_id_val
579: FROM rhnChannelFamilyMembers
580: WHERE channel_id = channel_id_in;
581: RETURN rhn_channel.available_family_subscriptions(
582: channel_family_id_val, org_id_in);
583: END available_chan_subscriptions;
584: procedure entitle_customer(customer_id_in in number,
585: channel_family_id_in in number,
586: quantity_in in number)
587: is
588: cursor permissions is
589: select 1
590: from rhnPrivateChannelFamily pcf
591: where pcf.org_id = customer_id_in
592: and pcf.channel_family_id = channel_family_id_in;
593: begin
594: for perm in permissions loop
595: set_family_maxmembers(
596: customer_id_in,
597: channel_family_id_in,
598: quantity_in
599: );
600: rhn_channel.update_family_counts(
601: channel_family_id_in,
602: customer_id_in
603: );
604: return;
605: end loop;
606: insert into rhnPrivateChannelFamily pcf (
607: channel_family_id, org_id, max_members, current_members
608: ) values (
609: channel_family_id_in, customer_id_in, quantity_in, 0
610: );
611: end;
612: procedure set_family_maxmembers(customer_id_in in number,
613: channel_family_id_in in number,
614: quantity_in in number)
615: is
616: cursor servers is
617: select server_id from (
618: select rownum row_number, server_id, modified from (
619: select rcfsp.server_id,
620: rcfsp.modified
621: from rhnChannelFamilyServerPhysical rcfsp
622: where rcfsp.customer_id = customer_id_in
623: and rcfsp.channel_family_id = channel_family_id_in
624: order by modified
625: )
626: where rownum > quantity_in
627: );
628: begin
629: for server in servers loop
630: rhn_channel.unsubscribe_server_from_family(server.server_id,
631: channel_family_id_in);
632: end loop;
633: update rhnPrivateChannelFamily pcf
634: set pcf.max_members = quantity_in
635: where pcf.org_id = customer_id_in
636: and pcf.channel_family_id = channel_family_id_in;
637: end;
638: procedure unsubscribe_server_from_family(server_id_in in number,
639: channel_family_id_in in number)
640: is
641: begin
642: delete
643: from rhnServerChannel rsc
644: where rsc.server_id = server_id_in
645: and channel_id in (
646: select rcfm.channel_id
647: from rhnChannelFamilyMembers rcfm
648: where rcfm.channel_family_id = channel_family_id_in);
649: end;
650: function get_org_id(channel_id_in in number)
651: return number
652: is
653: org_id_out number;
654: begin
655: select org_id into org_id_out
656: from rhnChannel
657: where id = channel_id_in;
658: return org_id_out;
659: end get_org_id;
660: function get_cfam_org_access(cfam_id_in in number, org_id_in in number)
661: return number
662: is
663: cursor families is
664: select 1
665: from rhnOrgChannelFamilyPermissions cfp
666: where cfp.org_id = org_id_in;
667: begin
668: for family in families loop
669: return 1;
670: end loop;
671: return 0;
672: end;
673: function get_org_access(channel_id_in in number, org_id_in in number)
674: return number
675: is
676: throwaway number;
677: begin
678: select distinct 1 into throwaway
679: from rhnChannelFamilyMembers CFM,
680: rhnOrgChannelFamilyPermissions CFP
681: where cfp.org_id = org_id_in
682: and CFM.channel_family_id = CFP.channel_family_id
683: and CFM.channel_id = channel_id_in
684: and (CFP.max_members > 0 or CFP.max_members is null or CFP.org_id = 1);
685: return 1;
686: exception
687: when no_data_found
688: then
689: return 0;
690: end;
691: function user_role_check_debug(channel_id_in in number,
692: user_id_in in number,
693: role_in in varchar2,
694: reason_out out varchar2)
695: return number
696: is
697: org_id number;
698: begin
699: org_id := rhn_user.get_org_id(user_id_in);
700: if role_in = 'manage' and
701: NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then
702: reason_out := 'channel_not_owned';
703: return 0;
704: end if;
705: if role_in = 'subscribe' and
706: rhn_channel.get_org_access(channel_id_in, org_id) = 0 then
707: reason_out := 'channel_not_available';
708: return 0;
709: end if;
710: if rhn_user.check_role_implied(user_id_in, 'channel_admin') = 1 then
711: reason_out := 'channel_admin';
712: return 1;
713: end if;
714: if role_in = 'subscribe'
715: then
716: if rhn_channel.org_channel_setting(channel_id_in,
717: org_id,
718: 'not_globally_subscribable') = 0 then
719: reason_out := 'globally_subscribable';
720: return 1;
721: end if;
722: end if;
723: reason_out := 'direct_permission';
724: return rhn_channel.direct_user_role_check(channel_id_in,
725: user_id_in, role_in);
726: end;
727: function user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
728: return number
729: is
730: throwaway varchar2(256);
731: begin
732: return rhn_channel.user_role_check_debug(channel_id_in, user_id_in, role_in, throwaway);
733: end;
734: function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
735: return number
736: is
737: begin
738: if user_id_in is null then
739: return 1;
740: end if;
741: return user_role_check(channel_id_in, user_id_in, role_in);
742: end loose_user_role_check;
743: function direct_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
744: return number
745: is
746: throwaway number;
747: begin
748: select 1 into throwaway
749: from rhnChannelPermissionRole CPR,
750: rhnChannelPermission CP
751: where CP.user_id = user_id_in
752: and CP.channel_id = channel_id_in
753: and CPR.label = role_in
754: and CP.role_id = CPR.id;
755: return 1;
756: exception
757: when no_data_found
758: then
759: return 0;
760: end;
761: function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in in varchar2)
762: return number
763: is
764: throwaway number;
765: begin
766: select 1 into throwaway
767: from rhnOrgChannelSettingsType OCST,
768: rhnOrgChannelSettings OCS
769: where OCS.org_id = org_id_in
770: and OCS.channel_id = channel_id_in
771: and OCST.label = setting_in
772: and OCS.setting_id = OCST.id;
773: return 1;
774: exception
775: when no_data_found
776: then
777: return 0;
778: end;
779: FUNCTION channel_priority(channel_id_in IN number)
780: RETURN number
781: IS
782: channel_name varchar2(64);
783: priority number;
784: end_of_life_val date;
785: org_id_val number;
786: BEGIN
787: select name, end_of_life, org_id
788: into channel_name, end_of_life_val, org_id_val
789: from rhnChannel
790: where id = channel_id_in;
791: if end_of_life_val is not null then
792: return -400;
793: end if;
794: if channel_name like 'Red Hat Enterprise Linux%' or channel_name like 'RHEL%' then
795: priority := 1000;
796: if channel_name not like '%Beta%' then
797: priority := priority + 1000;
798: end if;
799: priority := priority +
800: case
801: when channel_name like '%v. 5%' then 600
802: when channel_name like '%v. 4%' then 500
803: when channel_name like '%v. 3%' then 400
804: when channel_name like '%v. 2%' then 300
805: when channel_name like '%v. 1%' then 200
806: else 0
807: end;
808: priority := priority +
809: case
810: when channel_name like 'Red Hat Enterprise Linux (v. 5%' then 60
811: when (channel_name like '%AS%' and channel_name not like '%Extras%') then 50
812: when (channel_name like '%ES%' and channel_name not like '%Extras%') then 40
813: when (channel_name like '%WS%' and channel_name not like '%Extras%') then 30
814: when (channel_name like '%Desktop%' and channel_name not like '%Extras%') then 20
815: when channel_name like '%Extras%' then 10
816: else 0
817: end;
818: priority := priority +
819: case
820: when channel_name like '%)' then 5
821: else 0
822: end;
823: priority := priority +
824: case
825: when channel_name like '%32-bit x86%' then 4
826: when channel_name like '%64-bit Intel Itanium%' then 3
827: when channel_name like '%64-bit AMD64/Intel EM64T%' then 2
828: else 0
829: end;
830: elsif channel_name like 'Red Hat Desktop%' then
831: priority := 900;
832: if channel_name not like '%Beta%' then
833: priority := priority + 50;
834: end if;
835: priority := priority +
836: case
837: when channel_name like '%v. 4%' then 40
838: when channel_name like '%v. 3%' then 30
839: when channel_name like '%v. 2%' then 20
840: when channel_name like '%v. 1%' then 10
841: else 0
842: end;
843: priority := priority +
844: case
845: when channel_name like '%32-bit x86%' then 4
846: when channel_name like '%64-bit Intel Itanium%' then 3
847: when channel_name like '%64-bit AMD64/Intel EM64T%' then 2
848: else 0
849: end;
850: elsif org_id_val is not null then
851: priority := 600;
852: else
853: priority := 500;
854: end if;
855: return -priority;
856: end channel_priority;
857: procedure delete_server_channels(server_id_in in number)
858: is
859: begin
860: update rhnPrivateChannelFamily
861: set current_members = current_members -1
862: where org_id in (
863: select org_id
864: from rhnServer
865: where id = server_id_in
866: )
867: and channel_family_id in (
868: select rcfm.channel_family_id
869: from rhnChannelFamilyMembers rcfm,
870: rhnServerChannel rsc
871: where rsc.server_id = server_id_in
872: and rsc.channel_id = rcfm.channel_id
873: and not exists (
874: select 1
875: from
876: rhnChannelFamilyVirtSubLevel cfvsl,
877: rhnSGTypeVirtSubLevel sgtvsl,
878: rhnServerEntitlementView sev,
879: rhnVirtualInstance vi
880: where
881: vi.virtual_system_id = server_id_in
882: and vi.host_system_id = sev.server_id
883: and sev.label in ('virtualization_host',
884: 'virtualization_host_platform')
885: and sev.server_group_type_id =
886: sgtvsl.server_group_type_id
887: and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id
888: and cfvsl.channel_family_id = rcfm.channel_family_id
889: )
890: );
891: end;
892: procedure refresh_newest_package(channel_id_in in number, caller_in in varchar2 := '(unknown)')
893: is
894: begin
895: delete from rhnChannelNewestPackage where channel_id = channel_id_in;
896: insert into rhnChannelNewestPackage
897: ( channel_id, name_id, evr_id, package_id, package_arch_id )
898: ( select channel_id,
899: name_id, evr_id,
900: package_id, package_arch_id
901: from rhnChannelNewestPackageView
902: where channel_id = channel_id_in
903: );
904: insert into rhnChannelNewestPackageAudit (channel_id, caller)
905: values (channel_id_in, caller_in);
906: update rhnChannel
907: set last_modified = greatest(sysdate, last_modified + 1/86400)
908: where id = channel_id_in;
909: end;
910: procedure update_channel ( channel_id_in in number, invalidate_ss in number := 0,
911: date_to_use in date := sysdate )
912: is
913: channel_last_modified date;
914: last_modified_value date;
915: cursor snapshots is
916: select snapshot_id id
917: from rhnSnapshotChannel
918: where channel_id = channel_id_in;
919: begin
920: select last_modified
921: into channel_last_modified
922: from rhnChannel
923: where id = channel_id_in;
924: last_modified_value := date_to_use;
925: if last_modified_value <= channel_last_modified then
926: last_modified_value := last_modified_value + 1/86400;
927: end if;
928: update rhnChannel set last_modified = last_modified_value
929: where id = channel_id_in;
930: if invalidate_ss = 1 then
931: for snapshot in snapshots loop
932: update rhnSnapshot
933: set invalid = lookup_snapshot_invalid_reason('channel_modified')
934: where id = snapshot.id;
935: end loop;
936: end if;
937: end update_channel;
938: procedure update_channels_by_package ( package_id_in in number, date_to_use in date := sysdate )
939: is
940: cursor channels is
941: select channel_id
942: from rhnChannelPackage
943: where package_id = package_id_in
944: order by channel_id;
945: begin
946: for channel in channels loop
947: rhn_channel.update_channel ( channel.channel_id, 1, date_to_use );
948: end loop;
949: end update_channels_by_package;
950: procedure update_channels_by_errata ( errata_id_in number, date_to_use in date := sysdate )
951: is
952: cursor channels is
953: select channel_id
954: from rhnChannelErrata
955: where errata_id = errata_id_in
956: order by channel_id;
957: begin
958: for channel in channels loop
959: rhn_channel.update_channel ( channel.channel_id, 0, date_to_use );
960: end loop;
961: end update_channels_by_errata;
962: END rhn_channel;