RHN_SERVER
DDL scriptPackage source
Legend: string keyword reserved word operator
1: package rhn_server
2: is
3:
4:
5: function system_service_level(
6: server_id_in in number,
7: service_level_in in varchar2
8: ) return number;
9:
10: function can_change_base_channel(
11: server_id_in in number
12: ) return number;
13:
14: procedure set_custom_value(
15: server_id_in in number,
16: user_id_in in number,
17: key_label_in varchar2,
18: value_in in varchar2
19: );
20:
21: function bulk_set_custom_value(
22: key_label_in in varchar2,
23: value_in in varchar2,
24: set_label_in in varchar2,
25: set_uid_in in number
26: ) return integer;
27:
28: procedure snapshot_server(
29: server_id_in in number,
30: reason_in in varchar2
31: );
32:
33: procedure bulk_snapshot(
34: reason_in in varchar2,
35: set_label_in in varchar2,
36: set_uid_in in number
37: );
38:
39: procedure tag_delete(
40: server_id_in in number,
41: tag_id_in in number
42: );
43:
44: procedure tag_snapshot(
45: snapshot_id_in in number,
46: org_id_in in number,
47: tagname_in in varchar2
48: );
49:
50: procedure bulk_snapshot_tag(
51: org_id_in in number,
52: tagname_in varchar2,
53: set_label_in in varchar2,
54: set_uid_in in number
55: );
56:
57: procedure remove_action(
58: server_id_in in number,
59: action_id_in in number
60: );
61:
62: function check_user_access(server_id_in in number, user_id_in in number) return number;
63:
64: procedure insert_into_servergroup (
65: server_id_in in number,
66: server_group_id_in in number
67: );
68:
69: function insert_into_servergroup_maybe (
70: server_id_in in number,
71: server_group_id_in in number
72: ) return number;
73:
74: procedure insert_set_into_servergroup (
75: server_group_id_in in number,
76: user_id_in in number,
77: set_label_in in varchar2
78: );
79:
80: procedure delete_from_servergroup (
81: server_id_in in number,
82: server_group_id_in in number
83: );
84:
85: procedure delete_set_from_servergroup (
86: server_group_id_in in number,
87: user_id_in in number,
88: set_label_in in varchar2
89: );
90:
91: procedure clear_servergroup (
92: server_group_id_in in number
93: );
94:
95: procedure delete_from_org_servergroups (
96: server_id_in in number
97: );
98:
99: function get_ip_address (
100: server_id_in in number
101: ) return varchar2;
102:
103: procedure update_needed_cache(
104: server_id_in in number
105: );
106:
107: end rhn_server;
Package body source
Legend: string keyword reserved word operator
1: package body rhn_server
2: is
3: function system_service_level(
4: server_id_in in number,
5: service_level_in in varchar2
6: ) return number is
7:
8: cursor ents is
9: select label from rhnServerEntitlementView
10: where server_id = server_id_in;
11:
12: retval number := 0;
13:
14: begin
15: for ent in ents loop
16: retval := rhn_entitlements.entitlement_grants_service (ent.label, service_level_in);
17: if retval = 1 then
18: return retval;
19: end if;
20: end loop;
21:
22: return retval;
23:
24: end system_service_level;
25:
26:
27: function can_change_base_channel(server_id_in IN NUMBER)
28: return number
29: is
30: throwaway number;
31: begin
32:
33:
34:
35: select 1 into throwaway
36: from rhnServer S
37: where S.id = server_id_in
38: and not exists (select 1 from rhnSatelliteInfo SI where SI.server_id = S.id)
39: and not exists (select 1 from rhnProxyInfo PI where PI.server_id = S.id);
40:
41: return 1;
42: exception
43: when no_data_found
44: then
45: return 0;
46: end can_change_base_channel;
47:
48: procedure set_custom_value(
49: server_id_in in number,
50: user_id_in in number,
51: key_label_in in varchar2,
52: value_in in varchar2
53: ) is
54: key_id_val number;
55: begin
56: select CDK.id into key_id_val
57: from rhnCustomDataKey CDK,
58: rhnServer S
59: where S.id = server_id_in
60: and S.org_id = CDK.org_id
61: and CDK.label = key_label_in;
62:
63: begin
64: insert into rhnServerCustomDataValue (server_id, key_id, value, created_by, last_modified_by)
65: values (server_id_in, key_id_val, value_in, user_id_in, user_id_in);
66: exception
67: when DUP_VAL_ON_INDEX
68: then
69: update rhnServerCustomDataValue
70: set value = value_in,
71: last_modified_by = user_id_in
72: where server_id = server_id_in
73: and key_id = key_id_val;
74: end;
75:
76: end set_custom_value;
77:
78: function bulk_set_custom_value(
79: key_label_in in varchar2,
80: value_in in varchar2,
81: set_label_in in varchar2,
82: set_uid_in in number
83: )
84: return integer
85: is
86: i integer := 0;
87: begin
88: i := 0;
89: for server in (
90: SELECT user_id, label, element, element_two
91: FROM rhnSet
92: WHERE label = set_label_in
93: AND user_id = set_uid_in
94: ) loop
95: if rhn_server.system_service_level(server.element, 'management') = 1 then
96: rhn_server.set_custom_value(server.element, set_uid_in, key_label_in, value_in);
97: i := i + 1;
98: end if;
99: end loop server;
100: return i;
101: end bulk_set_custom_value;
102:
103: procedure bulk_snapshot_tag(
104: org_id_in in number,
105: tagname_in in varchar2,
106: set_label_in in varchar2,
107: set_uid_in in number
108: ) is
109: snapshot_id number;
110: begin
111: for server in (
112: SELECT user_id, label, element, element_two
113: FROM rhnSet
114: WHERE label = set_label_in
115: AND user_id = set_uid_in
116: ) loop
117: if rhn_server.system_service_level(server.element, 'management') = 1 then
118: begin
119: select max(id) into snapshot_id
120: from rhnSnapshot
121: where server_id = server.element;
122: exception
123: when NO_DATA_FOUND then
124: rhn_server.snapshot_server(server.element, 'tagging system: ' || tagname_in);
125:
126: select max(id) into snapshot_id
127: from rhnSnapshot
128: where server_id = server.element;
129: end;
130:
131:
132: begin
133: rhn_server.tag_snapshot(snapshot_id, org_id_in, tagname_in);
134: exception
135: when DUP_VAL_ON_INDEX
136: then
137:
138: null;
139: end;
140: end if;
141: end loop server;
142: end bulk_snapshot_tag;
143:
144: procedure tag_delete(
145: server_id_in in number,
146: tag_id_in in number
147: ) is
148: cursor snapshots is
149: select snapshot_id
150: from rhnSnapshotTag
151: where tag_id = tag_id_in;
152: tag_id_tmp number;
153: begin
154: select id into tag_id_tmp
155: from rhnTag
156: where id = tag_id_in
157: for update;
158:
159: delete
160: from rhnSnapshotTag
161: where server_id = server_id_in
162: and tag_id = tag_id_in;
163: for snapshot in snapshots loop
164: return;
165: end loop;
166: delete
167: from rhnTag
168: where id = tag_id_in;
169: end tag_delete;
170:
171: procedure tag_snapshot(
172: snapshot_id_in in number,
173: org_id_in in number,
174: tagname_in in varchar2
175: ) is
176: begin
177: insert into rhnSnapshotTag (snapshot_id, server_id, tag_id)
178: select snapshot_id_in, server_id, lookup_tag(org_id_in, tagname_in)
179: from rhnSnapshot
180: where id = snapshot_id_in;
181: end tag_snapshot;
182:
183: procedure bulk_snapshot(
184: reason_in in varchar2,
185: set_label_in in varchar2,
186: set_uid_in in number
187: ) is
188: begin
189: for server in (
190: SELECT user_id, label, element, element_two
191: FROM rhnSet
192: WHERE label = set_label_in
193: AND user_id = set_uid_in
194: ) loop
195: if rhn_server.system_service_level(server.element, 'management') = 1 then
196: rhn_server.snapshot_server(server.element, reason_in);
197: end if;
198: end loop server;
199: end bulk_snapshot;
200:
201: procedure snapshot_server(
202: server_id_in in number,
203: reason_in in varchar2
204: ) is
205: snapshot_id number;
206: cursor revisions is
207: select distinct
208: cr.id
209: from rhnConfigRevision cr,
210: rhnConfigFileName cfn,
211: rhnConfigFile cf,
212: rhnConfigChannel cc,
213: rhnServerConfigChannel scc
214: where 1=1
215: and scc.server_id = server_id_in
216: and scc.config_channel_id = cc.id
217: and cc.id = cf.config_channel_id
218: and cf.id = cr.config_file_id
219: and cr.id = cf.latest_config_revision_id
220: and cf.config_file_name_id = cfn.id
221: and cf.id = lookup_first_matching_cf(scc.server_id, cfn.path);
222: locked integer;
223: begin
224: select rhn_snapshot_id_seq.nextval into snapshot_id from dual;
225:
226: insert into rhnSnapshot (id, org_id, server_id, reason) (
227: select snapshot_id,
228: s.org_id,
229: server_id_in,
230: reason_in
231: from rhnServer s
232: where s.id = server_id_in
233: );
234: insert into rhnSnapshotChannel (snapshot_id, channel_id) (
235: select snapshot_id, sc.channel_id
236: from rhnServerChannel sc
237: where sc.server_id = server_id_in
238: );
239: insert into rhnSnapshotServerGroup (snapshot_id, server_group_id) (
240: select snapshot_id, sgm.server_group_id
241: from rhnServerGroupMembers sgm
242: where sgm.server_id = server_id_in
243: );
244: locked := 0;
245: while true loop
246: begin
247: insert into rhnPackageNEVRA (id, name_id, evr_id, package_arch_id)
248: select rhn_pkgnevra_id_seq.nextval, sp.name_id, sp.evr_id, sp.package_arch_id
249: from rhnServerPackage sp
250: where sp.server_id = server_id_in
251: and not exists
252: (select 1
253: from rhnPackageNEVRA nevra
254: where nevra.name_id = sp.name_id
255: and nevra.evr_id = sp.evr_id
256: and (nevra.package_arch_id = sp.package_arch_id
257: or (nevra.package_arch_id is null
258: and sp.package_arch_id is null)));
259: exit;
260: exception when dup_val_on_index then
261: if locked = 1 then
262: raise;
263: else
264: lock table rhnPackageNEVRA in exclusive mode;
265: locked := 1;
266: end if;
267: end;
268: end loop;
269: insert into rhnSnapshotPackage (snapshot_id, nevra_id) (
270: select distinct snapshot_id, nevra.id
271: from rhnServerPackage sp, rhnPackageNEVRA nevra
272: where sp.server_id = server_id_in
273: and nevra.name_id = sp.name_id
274: and nevra.evr_id = sp.evr_id
275: and (nevra.package_arch_id = sp.package_arch_id
276: or (nevra.package_arch_id is null
277: and sp.package_arch_id is null))
278: );
279:
280: insert into rhnSnapshotConfigChannel ( snapshot_id, config_channel_id ) (
281: select snapshot_id, scc.config_channel_id
282: from rhnServerConfigChannel scc
283: where server_id = server_id_in
284: );
285:
286: for revision in revisions loop
287: insert into rhnSnapshotConfigRevision (
288: snapshot_id, config_revision_id
289: ) values (
290: snapshot_id, revision.id
291: );
292: end loop;
293: end snapshot_server;
294:
295: procedure remove_action(
296: server_id_in in number,
297: action_id_in in number
298: ) is
299:
300:
301:
302:
303:
304: cursor chained_actions is
305: select id, prerequisite
306: from rhnAction
307: start with id = action_id_in
308: connect by prior id = prerequisite
309: order by prerequisite desc;
310: cursor sessions is
311: select s.id
312: from rhnKickstartSession s
313: where server_id_in in (s.old_server_id, s.new_server_id)
314: and s.action_id = action_id_in
315: and not exists (
316: select 1
317: from rhnKickstartSessionState ss
318: where ss.id = s.state_id
319: and ss.label in ('failed','complete')
320: );
321: type chain_end_type is table of number index by binary_integer;
322: chain_ends chain_end_type;
323: i number;
324: prereq number := 1;
325: begin
326: select prerequisite
327: into prereq
328: from rhnAction
329: where id = action_id_in;
330:
331: if prereq is not null then
332: rhn_exception.raise_exception('action_is_child');
333: end if;
334:
335: i := 0;
336: for action in chained_actions loop
337: if action.prerequisite is null then
338: chain_ends(i) := action.id;
339: i := i + 1;
340: else
341: delete from rhnServerAction
342: where server_id = server_id_in
343: and action_id = action.id;
344: end if;
345: end loop;
346: i := chain_ends.first;
347: while i is not null loop
348: delete from rhnServerAction
349: where server_id = server_id_in
350: and action_id = chain_ends(i);
351: i := chain_ends.next(i);
352: end loop;
353: for s in sessions loop
354: update rhnKickstartSession
355: set state_id = (
356: select id
357: from rhnKickstartSessionState
358: where label = 'failed'
359: ),
360: action_id = null
361: where id = s.id;
362: set_ks_session_history_message(s.id, 'failed', 'Kickstart cancelled due to action removal');
363: end loop;
364: end remove_action;
365:
366: function check_user_access(server_id_in in number, user_id_in in number)
367: return number
368: is
369: has_access number;
370: begin
371:
372: select 1 into has_access
373: from rhnServer S,
374: web_contact wc
375: where wc.org_id = s.org_id
376: and s.id = server_id_in
377: and wc.id = user_id_in;
378:
379:
380: if rhn_user.check_role(user_id_in, 'org_admin') = 1
381: then
382: return 1;
383: end if;
384:
385: select 1 into has_access
386: from rhnServerGroupMembers SGM,
387: rhnUserServerGroupPerms USG
388: where SGM.server_group_id = USG.server_group_id
389: and SGM.server_id = server_id_in
390: and USG.user_id = user_id_in
391: and rownum = 1;
392:
393: return 1;
394: exception
395: when no_data_found
396: then
397: return 0;
398: end check_user_access;
399:
400: procedure insert_into_servergroup (
401: server_id_in in number,
402: server_group_id_in in number
403: ) is
404: group_type number;
405: begin
406:
407:
408:
409: select sg.group_type
410: into group_type
411: from rhnServerGroup sg
412: where sg.id = server_group_id_in
413: for update of sg.current_members;
414:
415: insert into rhnServerGroupMembers(server_id, server_group_id)
416: values (server_id_in, server_group_id_in);
417:
418: update rhnServerGroup
419: set current_members = current_members + 1
420: where id = server_group_id_in;
421:
422: if group_type is null then
423: rhn_cache.update_perms_for_server_group(server_group_id_in);
424: end if;
425:
426: return;
427: end;
428:
429: function insert_into_servergroup_maybe (
430: server_id_in in number,
431: server_group_id_in in number
432: ) return number is
433: retval number := 0;
434: cursor servergroups is
435: select s.id server_id,
436: sg.id server_group_id
437: from rhnServerGroup sg,
438: rhnServer s
439: where s.id = server_id_in
440: and sg.id = server_group_id_in
441: and s.org_id = sg.org_id
442: and not exists (
443: select 1
444: from rhnServerGroupMembers sgm
445: where sgm.server_id = s.id
446: and sgm.server_group_id = sg.id
447: );
448: begin
449: for sgm in servergroups loop
450: rhn_server.insert_into_servergroup(sgm.server_id, sgm.server_group_id);
451: retval := retval + 1;
452: end loop;
453: return retval;
454: end insert_into_servergroup_maybe;
455:
456: procedure insert_set_into_servergroup (
457: server_group_id_in in number,
458: user_id_in in number,
459: set_label_in in varchar2
460: ) is
461: cursor servers is
462: select st.element id
463: from rhnSet st
464: where st.user_id = user_id_in
465: and st.label = set_label_in
466: and exists (
467: select 1
468: from rhnUserManagedServerGroups umsg
469: where umsg.server_group_id = server_group_id_in
470: and umsg.user_id = user_id_in
471: )
472: and not exists (
473: select 1
474: from rhnServerGroupMembers sgm
475: where sgm.server_id = st.element
476: and sgm.server_group_id = server_group_id_in
477: );
478: begin
479: for s in servers loop
480: rhn_server.insert_into_servergroup(s.id, server_group_id_in);
481: end loop;
482: end insert_set_into_servergroup;
483:
484: procedure delete_from_servergroup (
485: server_id_in in number,
486: server_group_id_in in number
487: ) is
488:
489: oid number;
490: group_type number;
491: begin
492: begin
493: select sg.group_type, sg.org_id
494: into group_type, oid
495: from rhnServerGroupMembers sgm,
496: rhnServerGroup sg
497: where sg.id = server_group_id_in
498: and sg.id = sgm.server_group_id
499: and sgm.server_id = server_id_in
500: for update of sg.current_members;
501: exception
502: when no_data_found then
503: rhn_exception.raise_exception('server_not_in_group');
504: end;
505:
506: delete from rhnServerGroupMembers
507: where server_group_id = server_group_id_in
508: and server_id = server_id_in;
509:
510: update rhnServerGroup
511: set current_members = current_members - 1
512: where id = server_group_id_in;
513:
514: if group_type is null then
515: rhn_cache.update_perms_for_server_group(server_group_id_in);
516: end if;
517: end;
518:
519: procedure delete_set_from_servergroup (
520: server_group_id_in in number,
521: user_id_in in number,
522: set_label_in in varchar2
523: ) is
524: cursor servergroups is
525: select sgm.server_id, sgm.server_group_id
526: from rhnSet st,
527: rhnServerGroupMembers sgm
528: where sgm.server_group_id = server_group_id_in
529: and st.user_id = user_id_in
530: and st.label = set_label_in
531: and sgm.server_id = st.element
532: and exists (
533: select 1
534: from rhnUserManagedServerGroups usgp
535: where usgp.server_group_id = server_group_id_in
536: and usgp.user_id = user_id_in
537: );
538: begin
539: for sgm in servergroups loop
540: rhn_server.delete_from_servergroup(sgm.server_id, server_group_id_in);
541: end loop;
542: end delete_set_from_servergroup;
543:
544: procedure clear_servergroup (
545: server_group_id_in in number
546: ) is
547: cursor servers is
548: select sgm.server_id id
549: from rhnServerGroupMembers sgm
550: where sgm.server_group_id = server_group_id_in;
551: begin
552: for s in servers loop
553: rhn_server.delete_from_servergroup(s.id, server_group_id_in);
554: end loop;
555: end clear_servergroup;
556:
557: procedure delete_from_org_servergroups (
558: server_id_in in number
559: ) is
560: cursor servergroups is
561: select sgm.server_group_id id
562: from rhnServerGroup sg,
563: rhnServerGroupMembers sgm
564: where sgm.server_id = server_id_in
565: and sgm.server_group_id = sg.id
566: and sg.group_type is null;
567: begin
568: for sg in servergroups loop
569: rhn_server.delete_from_servergroup(server_id_in, sg.id);
570: end loop;
571: end delete_from_org_servergroups;
572:
573: function get_ip_address (
574: server_id_in in number
575: ) return varchar2 is
576: cursor interfaces is
577: select ni.name as name, na4.address as address
578: from rhnServerNetInterface ni,
579: rhnServerNetAddress4 na4
580: where ni.server_id = server_id_in
581: and ni.id = na4.interface_id
582: and na4.address != '127.0.0.1';
583: cursor addresses is
584: select ipaddr ip_addr
585: from rhnServerNetwork
586: where server_id = server_id_in
587: and ipaddr != '127.0.0.1';
588: begin
589: for addr in addresses loop
590: return addr.ip_addr;
591: end loop;
592: for iface in interfaces loop
593: return iface.address;
594: end loop;
595: return NULL;
596: end get_ip_address;
597:
598: procedure update_needed_cache(server_id_in in number)
599: is
600: update_lock number;
601: begin
602: begin
603: select id into update_lock from rhnServer where id = server_id_in for update;
604: exception when NO_DATA_FOUND then NULL;
605: end;
606: delete from rhnServerNeededCache
607: where server_id = server_id_in;
608: insert into rhnServerNeededCache
609: (server_id, errata_id, package_id, channel_id)
610: (select distinct sp.server_id, x.errata_id, p.id, x.channel_id
611: FROM (SELECT sp_sp.server_id, sp_sp.name_id,
612: sp_sp.package_arch_id, max(sp_pe.evr) AS max_evr
613: FROM rhnServerPackage sp_sp
614: join rhnPackageEvr sp_pe ON sp_pe.id = sp_sp.evr_id
615: GROUP BY sp_sp.server_id, sp_sp.name_id, sp_sp.package_arch_id) sp
616: join rhnPackage p ON p.name_id = sp.name_id
617: join rhnPackageEvr pe ON pe.id = p.evr_id
618: AND sp.max_evr < pe.evr
619: join rhnPackageUpgradeArchCompat puac
620: ON puac.package_arch_id = sp.package_arch_id
621: AND puac.package_upgrade_arch_id = p.package_arch_id
622: join rhnServerChannel sc ON sc.server_id = sp.server_id
623: join rhnChannelPackage cp ON cp.package_id = p.id
624: AND cp.channel_id = sc.channel_id
625: left join (SELECT ep.errata_id, ce.channel_id, ep.package_id
626: FROM rhnChannelErrata ce
627: join rhnErrataPackage ep
628: ON ep.errata_id = ce.errata_id
629: join rhnServerChannel sc_sc
630: ON sc_sc.channel_id = ce.channel_id
631: WHERE sc_sc.server_id = server_id_in) x
632: ON x.channel_id = sc.channel_id
633: AND x.package_id = cp.package_id
634: where sp.server_id = server_id_in);
635: end update_needed_cache;
636:
637: end rhn_server;