RHN_ORG
DDL scriptPackage source
Legend: string keyword reserved word operator
1: PACKAGE rhn_org
2: IS
3: version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: CURSOR server_group_by_label(org_id_in NUMBER, group_label_in VARCHAR2) IS
5: SELECT SG.*
6: FROM rhnServerGroupType SGT,
7: rhnServerGroup SG
8: WHERE SG.group_type = SGT.id
9: AND SGT.label = group_label_in
10: AND SG.org_id = org_id_in;
11: FUNCTION find_server_group_by_type(org_id_in NUMBER,
12: group_label_in VARCHAR2)
13: RETURN NUMBER;
14: procedure delete_org(org_id_in in number);
15: procedure delete_user(user_id_in in number, deleting_org in number := 0);
16: END rhn_org;
Package body source
Legend: string keyword reserved word operator
1: PACKAGE BODY rhn_org
2: IS
3: body_version varchar2(100) := '$Id: universe.satellite.sql,v 1.2 2008/06/09 08:37:56 mmraka Exp $';
4: FUNCTION find_server_group_by_type(org_id_in NUMBER, group_label_in VARCHAR2)
5: RETURN NUMBER
6: IS
7: server_group server_group_by_label%ROWTYPE;
8: BEGIN
9: OPEN server_group_by_label(org_id_in, group_label_in);
10: FETCH server_group_by_label INTO server_group;
11: CLOSE server_group_by_label;
12: RETURN server_group.id;
13: END find_server_group_by_type;
14: procedure delete_org (
15: org_id_in in number
16: )
17: is
18: cursor users is
19: select id
20: from web_contact
21: where org_id = org_id_in;
22: cursor servers(org_id_in in number) is
23: select id
24: from rhnServer
25: where org_id = org_id_in;
26: cursor config_channels is
27: select id
28: from rhnConfigChannel
29: where org_id = org_id_in;
30: begin
31: if org_id_in = 1 then
32: rhn_exception.raise_exception('cannot_delete_base_org');
33: end if;
34: for u in users loop
35: rhn_org.delete_user(u.id, 1);
36: end loop;
37: for s in servers(org_id_in) loop
38: delete_server(s.id);
39: end loop;
40: for c in config_channels loop
41: rhn_config.delete_channel(c.id);
42: end loop;
43: rhn_entitlements.remove_org_entitlements(org_id_in);
44: delete from rhnChannel where org_id = org_id_in;
45: delete from rhnDailySummaryQueue where org_id = org_id_in;
46: delete from rhnOrgQuota where org_id = org_id_in;
47: delete from rhnOrgInfo where org_id = org_id_in;
48: delete from rhnFileList where org_id = org_id_in;
49: delete from rhnServerGroup where org_id = org_id_in;
50: delete from rhn_check_suites where customer_id = org_id_in;
51: delete from rhn_command_target where customer_id = org_id_in;
52: delete from rhn_contact_groups where customer_id = org_id_in;
53: delete from rhn_notification_formats where customer_id = org_id_in;
54: delete from rhn_probe where customer_id = org_id_in;
55: delete from rhn_redirects where customer_id = org_id_in;
56: delete from rhn_sat_cluster where customer_id = org_id_in;
57: delete from rhn_schedules where customer_id = org_id_in;
58: delete from web_customer where id = org_id_in;
59: end delete_org;
60: procedure delete_user(user_id_in in number, deleting_org in number := 0) is
61: cursor is_admin is
62: select 1
63: from rhnUserGroupType ugt,
64: rhnUserGroup ug,
65: rhnUserGroupMembers ugm
66: where ugm.user_id = user_id_in
67: and ugm.user_group_id = ug.id
68: and ug.group_type = ugt.id
69: and ugt.label = 'org_admin';
70: cursor servergroups_needing_admins is
71: select usgp.server_group_id server_group_id
72: from rhnUserServerGroupPerms usgp
73: where 1=1
74: and usgp.user_id = user_id_in
75: and not exists (
76: select 1
77: from rhnUserServerGroupPerms sq_usgp
78: where 1=1
79: and sq_usgp.server_group_id = usgp.server_group_id
80: and sq_usgp.user_id != user_id_in
81: );
82: cursor messages is
83: select message_id id
84: from rhnUserMessage
85: where user_id = user_id_in;
86: users number;
87: our_org_id number;
88: other_users number;
89: other_org_admin number;
90: other_user_id number;
91: begin
92: select wc.org_id
93: into our_org_id
94: from web_contact wc
95: where id = user_id_in;
96: begin
97: select id, 1
98: into other_user_id, other_users
99: from web_contact
100: where 1=1
101: and org_id = our_org_id
102: and id != user_id_in
103: and rownum = 1;
104: exception
105: when no_data_found then
106: other_users := 0;
107: end;
108: if other_users != 0 then
109: for ignore in is_admin loop
110: begin
111: select new_ugm.user_id
112: into other_org_admin
113: from rhnUserGroupMembers new_ugm,
114: rhnUserGroupType ugt,
115: rhnUserGroup ug,
116: rhnUserGroupMembers ugm
117: where ugm.user_id = user_id_in
118: and ugm.user_group_id = ug.id
119: and ug.group_type = ugt.id
120: and ugt.label = 'org_admin'
121: and ug.id = new_ugm.user_group_id
122: and new_ugm.user_id != user_id_in
123: and rownum = 1;
124: exception
125: when no_data_found then
126: if deleting_org = 0 then
127: rhn_exception.raise_exception('cannot_delete_user');
128: end if;
129: end;
130: for sg in servergroups_needing_admins loop
131: rhn_user.add_servergroup_perm(other_org_admin,
132: sg.server_group_id);
133: end loop;
134: end loop;
135: end if;
136: for message in messages loop
137: delete
138: from rhnUserMessage
139: where user_id = user_id_in
140: and message_id = message.id;
141: begin
142: select 1
143: into users
144: from rhnUserMessage
145: where message_id = message.id
146: and rownum = 1;
147: delete
148: from rhnMessage
149: where id = message.id;
150: exception
151: when no_data_found then
152: null;
153: end;
154: end loop;
155: delete from rhn_command_queue_sessions where contact_id = user_id_in;
156: delete from rhn_contact_methods where contact_id = user_id_in;
157: delete from rhn_redirects where contact_id = user_id_in;
158: delete from rhnUserServerPerms where user_id = user_id_in;
159: if other_users != 0 then
160: update rhnRegToken
161: set user_id = nvl(other_org_admin, other_user_id)
162: where org_id = our_org_id
163: and user_id = user_id_in;
164: begin
165: delete from web_contact where id = user_id_in;
166: exception
167: when others then
168: rhn_exception.raise_exception('cannot_delete_user');
169: end;
170: else
171: begin
172: delete from web_contact where id = user_id_in;
173: exception
174: when others then
175: rhn_exception.raise_exception('cannot_delete_user');
176: end;
177: end if;
178: return;
179: end delete_user;
180: END rhn_org;