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