Login
[x]
Log in using an account from:
Fedora Account System
Red Hat Associate
Red Hat Customer
Or login using a Red Hat Bugzilla account
Forgot Password
Login:
Hide Forgot
Create an Account
Red Hat Bugzilla – Attachment 318601 Details for
Bug 464927
Database error while Importing from 9i to 10g database on test-db-3
[?]
New
Simple Search
Advanced Search
My Links
Browse
Requests
Reports
Current State
Search
Tabular reports
Graphical reports
Duplicates
Other Reports
User Changes
Plotly Reports
Bug Status
Bug Severity
Non-Defaults
|
Product Dashboard
Help
Page Help!
Bug Writing Guidelines
What's new
Browser Support Policy
5.0.4.rh83 Release notes
FAQ
Guides index
User guide
Web Services
Contact
Legal
This site requires JavaScript to be enabled to function correctly, please enable it.
9i->10g 511->520 schema upgrade log
9i->10gschemaup.log (text/plain), 81.05 KB, created by
Steve Salevan
on 2008-10-01 16:24:09 UTC
(
hide
)
Description:
9i->10g 511->520 schema upgrade log
Filename:
MIME Type:
Creator:
Steve Salevan
Created:
2008-10-01 16:24:09 UTC
Size:
81.05 KB
patch
obsolete
>[root@test05-64 satellite-upgrade]# sqlplus ssalevan/ssalevan@ssalevan @satellite-5.1-to-5.2.sql > >SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 1 11:59:37 2008 > >Copyright (c) 1982, 2006, Oracle. All Rights Reserved. > > >Connected to: >Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production >With the Partitioning, OLAP, Data Mining and Real Application Testing options > >SQL> whenever sqlerror exit failure; >SQL> >SQL> spool satellite-5.1-to-5.2.log; >SQL> >SQL> variable evr_id number; >SQL> variable epoch varchar2(16); >SQL> variable version varchar2(64); >SQL> variable release varchar2(64); >SQL> >SQL> variable message varchar2(80); >SQL> >SQL> declare > 2 cursor evrs is > 3 select e.id, e.epoch, e.version, e.release, e.evr > 4 from rhnPackageEVR e, > 5 rhnVersionInfo rvi > 6 where rvi.label = 'schema' > 7 and rvi.name_id = > 8 lookup_package_name('rhn-satellite-schema') > 9 and rvi.evr_id = e.id; > 10 cursor valid_evrs is > 11 select 1 > 12 from dual > 13 where :evr_id in ( > 14 lookup_evr('','5.1.0','27') > 15 ); > 16 begin > 17 :evr_id := null; > 18 :message := 'XXX Invalid satellite schema version.'; > 19 for evr in evrs loop > 20 :evr_id := evr.id; > 21 :epoch := evr.epoch; > 22 :version := evr.version; > 23 :release := evr.release; > 24 :message := '*** Schema version is currently ' || > 25 evr.evr.as_vre_simple() || > 26 ', and will NOT be upgraded'; > 27 for vevr in valid_evrs loop > 28 :message := '*** Schema version is currently ' || > 29 evr.evr.as_vre_simple() || > 30 ', and will be upgraded'; > 31 end loop; > 32 return; > 33 end loop; > 34 end; > 35 / > >PL/SQL procedure successfully completed. > >SQL> show errors; >No errors. >SQL> >SQL> >SQL> select :message from dual; > >:MESSAGE >-------------------------------------------------------------------------------- >*** Schema version is currently 5.1.0-27, and will be upgraded > >SQL> >SQL> declare > 2 invalid_schema_version exception; > 3 cursor valid_evrs is > 4 select 1 > 5 from dual > 6 where :evr_id in ( > 7 lookup_evr('','5.1.0','27') > 8 ); > 9 begin > 10 for vevr in valid_evrs loop > 11 return; > 12 end loop; > 13 raise invalid_schema_version; > 14 end; > 15 / > >PL/SQL procedure successfully completed. > >SQL> show errors; >No errors. >SQL> >SQL> set define off; >SQL> >SQL> -- Verify that min(id) from web_customer is either 1 or web_customer is empty. >SQL> -- This logic used to live in install.pl before [ do_precondition_checks() ] >SQL> >SQL> declare > 2 web_customer_exception exception; > 3 c number; > 4 begin > 5 select min(id) into c from web_customer; > 6 if c > 1 then > 7 raise web_customer_exception; > 8 end if; > 9 end; > 10 / > >PL/SQL procedure successfully completed. > >SQL> show errors; >No errors. >SQL> >SQL> -- Upgrade body >SQL> >SQL> alter table web_customer drop column password ; > >Table altered. > >SQL> >SQL> -- bugzilla: 444841 >SQL> -- views/rhnPrivateErrataMail.sql >SQL> create or replace view > 2 rhnPrivateErrataMail > 3 as > 4 with rhnSPmaxEVR as ( > 5 select sq2_sp.server_id, sq2_sp.name_id, max(sq2_pe.evr) max_evr > 6 from rhnServerPackage sq2_sp, > 7 rhnPackageEVR sq2_pe > 8 where sq2_sp.evr_id = sq2_pe.id > 9 group by sq2_sp.server_id, sq2_sp.name_id) > 10 select > 11 w.login, > 12 w.login_uc, > 13 wpi.email, > 14 w.id user_id, > 15 s.id server_id, > 16 -- use sg here so we can start with org and work to errata from there > 17 w.org_id org_id, > 18 s.name server_name, > 19 sa.name server_arch, > 20 s.release server_release, > 21 ce.errata_id errata_id, > 22 e.advisory > 23 from > 24 rhnServer s, > 25 web_user_personal_info wpi, > 26 rhnUserInfo ui, > 27 rhnErrata e, > 28 rhnServerArch sa, > 29 rhnChannelErrata ce, > 30 web_contact w, > 31 rhnServerChannel sc, > 32 rhnServerGroupMembers sgm, > 33 rhnServerGroup sg > 34 where 1=1 > 35 -- we plan on starting with org_id, and server group is the > 36 -- best place to find that that's near servers > 37 and sg.id = sgm.server_group_id > 38 and sgm.server_id = sc.server_id > 39 -- then find the contacts, because permission checking is next > 40 and sg.org_id = w.org_id > 41 -- filter out users who don't want mail about this server > 42 -- they get an entry if they _don't_ want mail > 43 and not exists ( > 44 select usprefs.server_id > 45 from rhnUserServerPrefs usprefs > 46 where 1=1 > 47 and w.id = usprefs.user_id > 48 and sc.server_id = usprefs.server_id > 49 and usprefs.name = 'receive_notifications' > 50 ) > 51 -- filter out users who don't want/can't get email > 52 and w.id = wpi.web_user_id > 53 and wpi.email is not null > 54 and w.id = ui.user_id > 55 and ui.email_notify = 1 > 56 -- check permissions. For this query being an org admin is the > 57 -- most common thing, so we test for that first > 58 and exists ( > 59 select 1 > 60 from > 61 rhnUserGroupType ugt, > 62 rhnUserGroup ug, > 63 rhnUserGroupMembers ugm > 64 where 1=1 > 65 and ugt.label = 'org_admin' > 66 and ugt.id = ug.group_type > 67 and ug.id = ugm.user_group_id > 68 and ugm.user_id = w.id > 69 union all > 70 select 1 > 71 from > 72 rhnServerGroupMembers sq_sgm, > 73 rhnUserServerGroupPerms usg > 74 where sc.server_id = sq_sgm.server_id > 75 and sq_sgm.server_group_id = usg.server_group_id > 76 and usg.user_id = w.id > 77 ) > 78 -- filter out servers that aren't in useful channels > 79 and sc.channel_id = ce.channel_id > 80 -- find the server, so we can do s.arch comparisons > 81 and sc.server_id = s.id > 82 and exists ( > 83 select 1 > 84 from > 85 rhnPackageEVR p_evr, > 86 rhnPackageEVR sp_evr, > 87 rhnServerPackage sp, > 88 rhnChannelPackage cp, > 89 rhnPackage p, > 90 rhnErrataPackage ep, > 91 rhnServerPackageArchCompat spac > 92 where 1=1 > 93 -- packages from channels this server is subscribed to > 94 and sc.channel_id = cp.channel_id > 95 and cp.package_id = p.id > 96 -- part of an errata > 97 and ce.errata_id = ep.errata_id > 98 and ep.package_id = p.id > 99 -- and that errata maps back to the server channel >100 and sc.channel_id = ce.channel_id >101 and ce.errata_id = ep.errata_id >102 -- also installed on this server >103 and sc.server_id = sp.server_id >104 and sp.name_id = p.name_id >105 and sp.evr_id = sp_evr.id >106 -- different evr >107 and p.evr_id = p_evr.id >108 and sp.evr_id != p.evr_id >109 -- and newer evr >110 and sp_evr.evr < p_evr.evr >111 and sp_evr.evr = ( >112 select max_evr from rhnSPmaxEVR rsme >113 where sp.server_id = rsme.server_id >114 and sp.name_id = rsme.name_id >115 ) >116 -- compat arch >117 and p.package_arch_id = spac.package_arch_id >118 and s.server_arch_id = spac.server_arch_id >119 ) >120 -- below here isn't needed except for output >121 and s.server_arch_id = sa.id >122 and ce.errata_id = e.id >123 and not exists ( select 1 >124 from rhnWebContactDisabled wcd >125 where wcd.id = w.id ) >126 / > >View created. > >SQL> >SQL> -- deterministic hint fix >SQL> -- svn r174754 >SQL> >SQL> CREATE OR REPLACE > 2 PACKAGE BODY rhn_channel > 3 IS > 4 body_version varchar2(100) := '$Id: rhn_channel.pkb 174675 2008-07-02 13:32:24Z mmraka $'; > 5 > 6 -- Cursor that fetches all the possible base channels for a > 7 -- (server_arch_id, release, org_id) combination > 8 cursor base_channel_cursor( > 9 release_in in varchar2, > 10 server_arch_id_in in number, > 11 org_id_in in number > 12 ) return rhnChannel%ROWTYPE is > 13 select distinct c.* > 14 from rhnDistChannelMap dcm, > 15 rhnServerChannelArchCompat scac, > 16 rhnChannel c, > 17 rhnChannelPermissions cp > 18 where cp.org_id = org_id_in > 19 and cp.channel_id = c.id > 20 and c.parent_channel is null > 21 and c.id = dcm.channel_id > 22 and c.channel_arch_id = dcm.channel_arch_id > 23 and dcm.release = release_in > 24 and scac.server_arch_id = server_arch_id_in > 25 and scac.channel_arch_id = c.channel_arch_id; > 26 > 27 FUNCTION get_license_path(channel_id_in IN NUMBER) > 28 RETURN VARCHAR2 > 29 IS > 30 license_val VARCHAR2(1000); > 31 BEGIN > 32 SELECT CFL.license_path INTO license_val > 33 FROM rhnChannelFamilyLicense CFL, rhnChannelFamilyMembers CFM > 34 WHERE CFM.channel_id = channel_id_in > 35 AND CFM.channel_family_id = CFL.channel_family_id; > 36 > 37 RETURN license_val; > 38 > 39 EXCEPTION > 40 WHEN NO_DATA_FOUND > 41 THEN > 42 RETURN NULL; > 43 END get_license_path; > 44 > 45 > 46 PROCEDURE license_consent(channel_id_in IN NUMBER, user_id_in IN NUMBER, server_id_in IN NUMBER) > 47 IS > 48 channel_family_id_val NUMBER; > 49 BEGIN > 50 channel_family_id_val := rhn_channel.family_for_channel(channel_id_in); > 51 IF channel_family_id_val IS NULL > 52 THEN > 53 rhn_exception.raise_exception('channel_subscribe_no_family'); > 54 END IF; > 55 > 56 IF rhn_channel.get_license_path(channel_id_in) IS NULL > 57 THEN > 58 rhn_exception.raise_exception('channel_consent_no_license'); > 59 END IF; > 60 > 61 INSERT INTO rhnChannelFamilyLicenseConsent (channel_family_id, user_id, server_id) > 62 VALUES (channel_family_id_val, user_id_in, server_id_in); > 63 END license_consent; > 64 > 65 PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in in number := null) > 66 IS > 67 channel_parent_val rhnChannel.parent_channel%TYPE; > 68 parent_subscribed BOOLEAN; > 69 server_has_base_chan BOOLEAN; > 70 server_already_in_chan BOOLEAN; > 71 channel_family_id_val NUMBER; > 72 server_org_id_val NUMBER; > 73 available_subscriptions NUMBER; > 74 consenting_user NUMBER; > 75 allowed number := 0; > 76 current_members_val number; > 77 BEGIN > 78 if user_id_in is not null then > 79 allowed := rhn_channel.user_role_check(channel_id_in, user_id_in, 'subscribe'); > 80 else > 81 allowed := 1; > 82 end if; > 83 > 84 if allowed = 0 then > 85 rhn_exception.raise_exception('no_subscribe_permissions'); > 86 end if; > 87 > 88 > 89 SELECT parent_channel INTO channel_parent_val FROM rhnChannel WHERE id = channel_id_in; > 90 > 91 IF channel_parent_val IS NOT NULL > 92 THEN > 93 -- child channel; if attempting to cross-subscribe a child to the wrong base, silently ignore > 94 parent_subscribed := FALSE; > 95 > 96 FOR check_subscription IN check_server_subscription(server_id_in, channel_parent_val) > 97 LOOP > 98 parent_subscribed := TRUE; > 99 END LOOP check_subscription; >100 >101 IF NOT parent_subscribed >102 THEN >103 RETURN; >104 END IF; >105 ELSE >106 -- base channel >107 server_has_base_chan := FALSE; >108 FOR base IN server_base_subscriptions(server_id_in) >109 LOOP >110 server_has_base_chan := TRUE; >111 END LOOP base; >112 >113 IF server_has_base_chan >114 THEN >115 rhn_exception.raise_exception('channel_server_one_base'); >116 END IF; >117 END IF; >118 >119 FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in) >120 LOOP >121 server_already_in_chan := TRUE; >122 END LOOP check_subscription; >123 >124 IF server_already_in_chan >125 THEN >126 RETURN; >127 END IF; >128 >129 channel_family_id_val := rhn_channel.family_for_channel(channel_id_in); >130 IF channel_family_id_val IS NULL >131 THEN >132 rhn_exception.raise_exception('channel_subscribe_no_family'); >133 END IF; >134 >135 SELECT org_id INTO server_org_id_val >136 FROM rhnServer >137 WHERE id = server_id_in; >138 >139 select current_members >140 into current_members_val >141 from rhnPrivateChannelFamily >142 where org_id = server_org_id_val and channel_family_id = channel_family_id_val >143 for update of current_members; >144 >145 available_subscriptions := rhn_channel.available_family_subscriptions(channel_family_id_val, server_org_id_val); >146 >147 IF available_subscriptions IS NULL OR >148 available_subscriptions > 0 or >149 can_server_consume_virt_channl(server_id_in, channel_family_id_val) = 1 >150 THEN >151 >152 IF rhn_channel.get_license_path(channel_id_in) IS NOT NULL >153 THEN >154 BEGIN >155 >156 SELECT user_id INTO consenting_user >157 FROM rhnChannelFamilyLicenseConsent >158 WHERE channel_family_id = channel_family_id_val >159 AND server_id = server_id_in; >160 >161 EXCEPTION >162 WHEN NO_DATA_FOUND >163 THEN >164 rhn_exception.raise_exception('channel_subscribe_no_consent'); >165 END; >166 END IF; >167 >168 insert into rhnServerHistory (id,server_id,summary,details) ( >169 select rhn_event_id_seq.nextval, >170 server_id_in, >171 'subscribed to channel ' || SUBSTR(c.label, 0, 106), >172 c.label >173 from rhnChannel c >174 where c.id = channel_id_in >175 ); >176 UPDATE rhnServer SET channels_changed = sysdate WHERE id = server_id_in; >177 INSERT INTO rhnServerChannel (server_id, channel_id) VALUES (server_id_in, channel_id_in); >178 >179 rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val); >180 queue_server(server_id_in, immediate_in); >181 ELSE >182 rhn_exception.raise_exception('channel_family_no_subscriptions'); >183 END IF; >184 >185 END subscribe_server; >186 >187 function can_server_consume_virt_channl( >188 server_id_in in number, >189 family_id_in in number ) >190 return number >191 is >192 >193 cursor server_virt_families is >194 select vi.virtual_system_id, cfvsl.channel_family_id >195 from >196 rhnChannelFamilyVirtSubLevel cfvsl, >197 rhnSGTypeVirtSubLevel sgtvsl, >198 rhnVirtualInstance vi >199 where >200 vi.virtual_system_id = server_id_in >201 and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id >202 and cfvsl.channel_family_id = family_id_in >203 and exists ( >204 select 1 >205 from rhnServerEntitlementView sev >206 where vi.host_system_id = sev.server_id >207 and sev.server_group_type_id = sgtvsl.server_group_type_id ); >208 begin >209 >210 for server_virt_family in server_virt_families loop >211 return 1; >212 end loop; >213 >214 return 0; >215 >216 end; >217 >218 >219 PROCEDURE bulk_subscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER) >220 IS >221 BEGIN >222 FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in) >223 LOOP >224 rhn_channel.subscribe_server(server.element, channel_id_in, 0, set_uid_in); >225 END LOOP server; >226 END bulk_subscribe_server; >227 >228 PROCEDURE bulk_server_base_change(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER) >229 IS >230 BEGIN >231 FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in) >232 LOOP >233 IF rhn_server.can_change_base_channel(server.element) = 1 >234 THEN >235 rhn_channel.clear_subscriptions(TO_NUMBER(server.element)); >236 rhn_channel.subscribe_server(server.element, channel_id_in, 0, set_uid_in); >237 END IF; >238 END LOOP server; >239 END bulk_server_base_change; >240 >241 procedure bulk_server_basechange_from( >242 set_label_in in varchar2, >243 set_uid_in in number, >244 old_channel_id_in in number, >245 new_channel_id_in in number >246 ) is >247 cursor servers is >248 select sc.server_id id >249 from rhnChannel nc, >250 rhnServerChannelArchCompat scac, >251 rhnServer s, >252 rhnChannel oc, >253 rhnServerChannel sc, >254 rhnSet st >255 where 1=1 >256 -- first, find the servers we're looking for. >257 and st.label = set_label_in >258 and st.user_id = set_uid_in >259 and st.element = sc.server_id >260 -- now, filter out anything that's not in the >261 -- old base channel. >262 and sc.channel_id = old_channel_id_in >263 and sc.channel_id = oc.id >264 and oc.parent_channel is null >265 -- now, see if it's compatible with the new base channel >266 and nc.id = new_channel_id_in >267 and nc.parent_channel is null >268 and sc.server_id = s.id >269 and s.server_arch_id = scac.server_arch_id >270 and scac.channel_arch_id = nc.channel_arch_id; >271 begin >272 for s in servers loop >273 insert into rhnSet ( >274 user_id, label, element >275 ) values ( >276 set_uid_in, >277 set_label_in || 'basechange', >278 s.id >279 ); >280 end loop channel; >281 bulk_server_base_change(new_channel_id_in, >282 set_label_in || 'basechange', >283 set_uid_in); >284 delete from rhnSet >285 where label = set_label_in||'basechange' >286 and user_id = set_uid_in; >287 end bulk_server_basechange_from; >288 >289 procedure bulk_guess_server_base( >290 set_label_in in varchar2, >291 set_uid_in in number >292 ) is >293 channel_id number; >294 begin >295 for server in rhn_set.set_iterator(set_label_in, set_uid_in) >296 loop >297 -- anything that doesn't work, we just ignore >298 begin >299 if rhn_server.can_change_base_channel(server.element) = 1 >300 then >301 channel_id := guess_server_base(TO_NUMBER(server.element)); >302 rhn_channel.clear_subscriptions(TO_NUMBER(server.element)); >303 rhn_channel.subscribe_server(TO_NUMBER(server.element), channel_id, 0, set_uid_in); >304 end if; >305 exception when others then >306 null; >307 end; >308 end loop server; >309 end; >310 >311 function guess_server_base( >312 server_id_in in number >313 ) RETURN number is >314 cursor server_cursor is >315 select s.server_arch_id, s.release, s.org_id >316 from rhnServer s >317 where s.id = server_id_in; >318 begin >319 for s in server_cursor loop >320 for channel in base_channel_cursor(s.release, >321 s.server_arch_id, s.org_id) >322 loop >323 return channel.id; >324 end loop base_channel_cursor; >325 end loop server_cursor; >326 -- Server not found, or no base channel applies to it >327 return null; >328 end; >329 >330 -- Private function >331 function normalize_server_arch(server_arch_in in varchar2) >332 return varchar2 >333 deterministic >334 is >335 suffix VARCHAR2(128) := '-redhat-linux'; >336 suffix_len NUMBER := length(suffix); >337 begin >338 if server_arch_in is NULL then >339 return NULL; >340 end if; >341 if instr(server_arch_in, '-') > 0 >342 then >343 -- Suffix already present >344 return server_arch_in; >345 end if; >346 return server_arch_in || suffix; >347 end normalize_server_arch; >348 >349 -- >350 -- >351 -- Raises: >352 -- server_arch_not_found >353 -- no_subscribe_permissions >354 function base_channel_for_release_arch( >355 release_in in varchar2, >356 server_arch_in in varchar2, >357 org_id_in in number := -1, >358 user_id_in in number := null >359 ) return number is >360 server_arch varchar2(256) := normalize_server_arch(server_arch_in); >361 server_arch_id number; >362 begin >363 -- Look up the server arch >364 begin >365 select id >366 into server_arch_id >367 from rhnServerArch >368 where label = server_arch; >369 exception >370 when no_data_found then >371 rhn_exception.raise_exception('server_arch_not_found'); >372 end; >373 return base_channel_rel_archid(release_in, server_arch_id, >374 org_id_in, user_id_in); >375 end base_channel_for_release_arch; >376 >377 function base_channel_rel_archid( >378 release_in in varchar2, >379 server_arch_id_in in number, >380 org_id_in in number := -1, >381 user_id_in in number := null >382 ) return number is >383 denied_channel_id number := null; >384 valid_org_id number := org_id_in; >385 valid_user_id number := user_id_in; >386 channel_subscribable number; >387 begin >388 if org_id_in = -1 and user_id_in is not null then >389 -- Get the org id from the user id >390 begin >391 select org_id >392 into valid_org_id >393 from web_contact >394 where id = user_id_in; >395 exception >396 when no_data_found then >397 -- User doesn't exist >398 -- XXX Only list public stuff for now >399 valid_user_id := null; >400 valid_org_id := -1; >401 end; >402 end if; >403 >404 for c in base_channel_cursor(release_in, server_arch_id_in, valid_org_id) >405 loop >406 -- This row is a possible match >407 if valid_user_id is null then >408 -- User ID not specified, so no user to channel permissions to >409 -- check >410 return c.id; >411 end if; >412 >413 -- Check user to channel permissions >414 select loose_user_role_check(c.id, user_id_in, 'subscribe') >415 into channel_subscribable >416 from dual; >417 >418 if channel_subscribable = 1 then >419 return c.id; >420 end if; >421 >422 -- Base channel exists, but is not subscribable; keep trying >423 denied_channel_id := c.id; >424 end loop base_channel_fetch; >425 >426 if denied_channel_id is not null then >427 rhn_exception.raise_exception('no_subscribe_permissions'); >428 end if; >429 -- No base channel applies >430 return NULL; >431 end base_channel_rel_archid; >432 >433 procedure bulk_guess_server_base_from( >434 set_label_in in varchar2, >435 set_uid_in in number, >436 channel_id_in in number >437 ) is >438 cursor channels(server_id_in in number) is >439 select rsc.channel_id >440 from rhnServerChannel rsc, >441 rhnChannel rc >442 where server_id_in = rsc.server_id >443 and rsc.channel_id = rc.id >444 and rc.parent_channel is null; >445 begin >446 for server in rhn_set.set_iterator(set_label_in, set_uid_in) >447 loop >448 for channel in channels(server.element) >449 loop >450 if channel.channel_id = channel_id_in >451 then >452 insert into rhnSet (user_id, label, element) values (set_uid_in, set_label_in || 'baseguess', server.element); >453 end if; >454 end loop channel; >455 end loop server; >456 bulk_guess_server_base(set_label_in||'baseguess',set_uid_in); >457 delete from rhnSet where label = set_label_in||'baseguess' and user_id = set_uid_in; >458 end; >459 >460 >461 PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server IN NUMBER := 0 ) >462 IS >463 cursor server_channels(server_id_in in number) is >464 select s.org_id, sc.channel_id, cfm.channel_family_id >465 from rhnServer s, >466 rhnServerChannel sc, >467 rhnChannelFamilyMembers cfm >468 where s.id = server_id_in >469 and s.id = sc.server_id >470 and sc.channel_id = cfm.channel_id; >471 BEGIN >472 for channel in server_channels(server_id_in) >473 loop >474 unsubscribe_server(server_id_in, channel.channel_id, 1, 1, deleting_server); >475 rhn_channel.update_family_counts(channel.channel_family_id, channel.org_id); >476 end loop channel; >477 END clear_subscriptions; >478 >479 PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in number := 0, >480 deleting_server IN NUMBER := 0 ) >481 IS >482 channel_family_id_val NUMBER; >483 server_org_id_val NUMBER; >484 available_subscriptions NUMBER; >485 server_already_in_chan BOOLEAN; >486 cursor channel_family_is_proxy(channel_family_id_in in number) is >487 select 1 >488 from rhnChannelFamily >489 where id = channel_family_id_in >490 and label = 'rhn-proxy'; >491 cursor channel_family_is_satellite(channel_family_id_in in number) is >492 select 1 >493 from rhnChannelFamily >494 where id = channel_family_id_in >495 and label = 'rhn-satellite'; >496 -- this is *EXACTLY* like check_server_parent_membership, but if we recurse >497 -- with the package-level one, we get a "cursor already open", so we need a >498 -- copy on our call stack instead. GROAN. >499 cursor local_chk_server_parent_memb ( >500 server_id_in number, >501 channel_id_in number ) is >502 select c.id >503 from rhnChannel c, >504 rhnServerChannel sc >505 where 1=1 >506 and c.parent_channel = channel_id_in >507 and c.id = sc.channel_id >508 and sc.server_id = server_id_in; >509 BEGIN >510 FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in) >511 LOOP >512 if unsubscribe_children_in = 1 then >513 unsubscribe_server(server_id_in => server_id_in, >514 channel_id_in => child.id, >515 immediate_in => immediate_in, >516 unsubscribe_children_in => unsubscribe_children_in, >517 deleting_server => deleting_server); >518 else >519 rhn_exception.raise_exception('channel_unsubscribe_child_exists'); >520 end if; >521 END LOOP child; >522 >523 server_already_in_chan := FALSE; >524 >525 FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in) >526 LOOP >527 server_already_in_chan := TRUE; >528 END LOOP check_subscription; >529 >530 IF NOT server_already_in_chan >531 THEN >532 RETURN; >533 END IF; >534 >535 if deleting_server = 0 then >536 >537 insert into rhnServerHistory (id,server_id,summary,details) ( >538 select rhn_event_id_seq.nextval, >539 server_id_in, >540 'unsubscribed from channel ' || SUBSTR(c.label, 0, 106), >541 c.label >542 from rhnChannel c >543 where c.id = channel_id_in >544 ); >545 >546 UPDATE rhnServer SET channels_changed = sysdate WHERE id = server_id_in; >547 end if; >548 >549 DELETE FROM rhnServerChannel WHERE server_id = server_id_in AND channel_id = channel_id_in; >550 >551 if deleting_server = 0 then >552 queue_server(server_id_in, immediate_in); >553 end if; >554 >555 channel_family_id_val := rhn_channel.family_for_channel(channel_id_in); >556 IF channel_family_id_val IS NULL >557 THEN >558 rhn_exception.raise_exception('channel_unsubscribe_no_family'); >559 END IF; >560 >561 for ignore in channel_family_is_satellite(channel_family_id_val) loop >562 delete from rhnSatelliteInfo where server_id = server_id_in; >563 delete from rhnSatelliteChannelFamily where server_id = server_id_in; >564 end loop; >565 >566 for ignore in channel_family_is_proxy(channel_family_id_val) loop >567 delete from rhnProxyInfo where server_id = server_id_in; >568 end loop; >569 >570 DELETE FROM rhnChannelFamilyLicenseConsent >571 WHERE channel_family_id = channel_family_id_val >572 AND server_id = server_id_in; >573 >574 SELECT org_id INTO server_org_id_val >575 FROM rhnServer >576 WHERE id = server_id_in; >577 >578 rhn_channel.update_family_counts(channel_family_id_val, server_org_id_val); >579 END unsubscribe_server; >580 >581 PROCEDURE bulk_unsubscribe_server(channel_id_in IN NUMBER, set_label_in IN VARCHAR2, set_uid_in IN NUMBER) >582 IS >583 BEGIN >584 FOR server IN rhn_set.set_iterator(set_label_in, set_uid_in) >585 LOOP >586 rhn_channel.unsubscribe_server(server.element, channel_id_in, 0); >587 END LOOP server; >588 END bulk_unsubscribe_server; >589 >590 FUNCTION family_for_channel(channel_id_in IN NUMBER) >591 RETURN NUMBER >592 IS >593 channel_family_id_val NUMBER; >594 BEGIN >595 SELECT channel_family_id INTO channel_family_id_val >596 FROM rhnChannelFamilyMembers >597 WHERE channel_id = channel_id_in; >598 >599 RETURN channel_family_id_val; >600 EXCEPTION >601 WHEN NO_DATA_FOUND >602 THEN >603 RETURN NULL; >604 END family_for_channel; >605 >606 FUNCTION available_family_subscriptions(channel_family_id_in IN NUMBER, org_id_in IN NUMBER) >607 RETURN NUMBER >608 IS >609 cfp channel_family_perm_cursor%ROWTYPE; >610 current_members_val NUMBER; >611 max_members_val NUMBER; >612 found NUMBER; >613 BEGIN >614 IF NOT channel_family_perm_cursor%ISOPEN >615 THEN >616 OPEN channel_family_perm_cursor(channel_family_id_in, org_id_in); >617 END IF; >618 >619 FETCH channel_family_perm_cursor INTO cfp; >620 >621 WHILE channel_family_perm_cursor%FOUND >622 LOOP >623 found := 1; >624 >625 current_members_val := cfp.current_members; >626 max_members_val := cfp.max_members; >627 >628 FETCH channel_family_perm_cursor INTO cfp; >629 END LOOP; >630 >631 IF channel_family_perm_cursor%ISOPEN >632 THEN >633 CLOSE channel_family_perm_cursor; >634 END IF; >635 >636 -- not found: either the channel fam doesn't have an entry in cfp, or the org doesn't have access to it. >637 -- either way, there are no available subscriptions >638 >639 IF found IS NULL >640 THEN >641 RETURN 0; >642 END IF; >643 >644 -- null max members? in that case, pass it on; NULL means infinite >645 IF max_members_val IS NULL >646 THEN >647 RETURN NULL; >648 END IF; >649 >650 -- otherwise, return the delta >651 RETURN max_members_val - current_members_val; >652 END available_family_subscriptions; >653 >654 -- ******************************************************************* >655 -- FUNCTION: channel_family_current_members >656 -- Calculates and returns the actual count of systems consuming >657 -- physical channel subscriptions. >658 -- Called by: update_family_counts >659 -- rhn_entitlements.repoll_virt_guest_entitlements >660 -- ******************************************************************* >661 function channel_family_current_members(channel_family_id_in IN NUMBER, >662 org_id_in IN NUMBER) >663 return number >664 is >665 current_members_count number := 0; >666 begin >667 select count(sc.server_id) >668 into current_members_count >669 from rhnChannelFamilyMembers cfm, >670 rhnServerChannel sc, >671 rhnServer s >672 where s.org_id = org_id_in >673 and s.id = sc.server_id >674 and cfm.channel_family_id = channel_family_id_in >675 and cfm.channel_id = sc.channel_id >676 and exists ( >677 select 1 >678 from rhnChannelFamilyServerPhysical cfsp >679 where cfsp.CHANNEL_FAMILY_ID = channel_family_id_in >680 and cfsp.server_id = s.id >681 ); >682 >683 return current_members_count; >684 end; >685 >686 PROCEDURE update_family_counts(channel_family_id_in IN NUMBER, >687 org_id_in IN NUMBER) >688 IS >689 BEGIN >690 update rhnPrivateChannelFamily >691 set current_members = ( >692 channel_family_current_members(channel_family_id_in, org_id_in) >693 ) >694 where org_id = org_id_in >695 and channel_family_id = channel_family_id_in; >696 END update_family_counts; >697 >698 FUNCTION available_chan_subscriptions(channel_id_in IN NUMBER, >699 org_id_in IN NUMBER) >700 RETURN NUMBER >701 IS >702 channel_family_id_val NUMBER; >703 BEGIN >704 SELECT channel_family_id INTO channel_family_id_val >705 FROM rhnChannelFamilyMembers >706 WHERE channel_id = channel_id_in; >707 >708 RETURN rhn_channel.available_family_subscriptions( >709 channel_family_id_val, org_id_in); >710 END available_chan_subscriptions; >711 >712 -- ******************************************************************* >713 -- PROCEDURE: entitle_customer >714 -- Creates a chan fam bucket, or sets max_members for an existing bucket >715 -- Called by: rhn_ep.poll_customer_internal >716 -- Calls: set_family_maxmembers + update_family_counts if the row >717 -- already exists, else it creates it in rhnPrivateChannelFamily. >718 -- ******************************************************************* >719 procedure entitle_customer(customer_id_in in number, >720 channel_family_id_in in number, >721 quantity_in in number) >722 is >723 cursor permissions is >724 select 1 >725 from rhnPrivateChannelFamily pcf >726 where pcf.org_id = customer_id_in >727 and pcf.channel_family_id = channel_family_id_in; >728 begin >729 for perm in permissions loop >730 set_family_maxmembers( >731 customer_id_in, >732 channel_family_id_in, >733 quantity_in >734 ); >735 rhn_channel.update_family_counts( >736 channel_family_id_in, >737 customer_id_in >738 ); >739 return; >740 end loop; >741 >742 insert into rhnPrivateChannelFamily pcf ( >743 channel_family_id, org_id, max_members, current_members >744 ) values ( >745 channel_family_id_in, customer_id_in, quantity_in, 0 >746 ); >747 end; >748 >749 -- ******************************************************************* >750 -- PROCEDURE: set_family_maxmembers >751 -- Prunes an existing channel family bucket by unsubscribing the >752 -- necessary servers and sets max_members. >753 -- Called by: rhn_channel.entitle_customer >754 -- Calls: unsubscribe_server_from_family >755 -- ******************************************************************* >756 procedure set_family_maxmembers(customer_id_in in number, >757 channel_family_id_in in number, >758 quantity_in in number) >759 is >760 cursor servers is >761 select server_id from ( >762 select rownum row_number, server_id, modified from ( >763 select rcfsp.server_id, >764 rcfsp.modified >765 from rhnChannelFamilyServerPhysical rcfsp >766 where rcfsp.customer_id = customer_id_in >767 and rcfsp.channel_family_id = channel_family_id_in >768 order by modified >769 ) >770 where rownum > quantity_in >771 ); >772 begin >773 -- prune subscribed servers >774 for server in servers loop >775 rhn_channel.unsubscribe_server_from_family(server.server_id, >776 channel_family_id_in); >777 end loop; >778 >779 update rhnPrivateChannelFamily pcf >780 set pcf.max_members = quantity_in >781 where pcf.org_id = customer_id_in >782 and pcf.channel_family_id = channel_family_id_in; >783 end; >784 >785 procedure unsubscribe_server_from_family(server_id_in in number, >786 channel_family_id_in in number) >787 is >788 begin >789 delete >790 from rhnServerChannel rsc >791 where rsc.server_id = server_id_in >792 and channel_id in ( >793 select rcfm.channel_id >794 from rhnChannelFamilyMembers rcfm >795 where rcfm.channel_family_id = channel_family_id_in); >796 end; >797 >798 function get_org_id(channel_id_in in number) >799 return number >800 is >801 org_id_out number; >802 begin >803 select org_id into org_id_out >804 from rhnChannel >805 where id = channel_id_in; >806 >807 return org_id_out; >808 end get_org_id; >809 >810 function get_cfam_org_access(cfam_id_in in number, org_id_in in number) >811 return number >812 is >813 cursor families is >814 select 1 >815 from rhnOrgChannelFamilyPermissions cfp >816 where cfp.org_id = org_id_in; >817 begin >818 -- the idea: if we get past this query, >819 -- the user has the role, else catch the exception and return 0 >820 for family in families loop >821 return 1; >822 end loop; >823 return 0; >824 end; >825 >826 function get_org_access(channel_id_in in number, org_id_in in number) >827 return number >828 is >829 throwaway number; >830 begin >831 -- the idea: if we get past this query, >832 -- the org has access to the channel, else catch the exception and return 0 >833 select distinct 1 into throwaway >834 from rhnChannelFamilyMembers CFM, >835 rhnOrgChannelFamilyPermissions CFP >836 where cfp.org_id = org_id_in >837 and CFM.channel_family_id = CFP.channel_family_id >838 and CFM.channel_id = channel_id_in >839 and (CFP.max_members > 0 or CFP.max_members is null or CFP.org_id = 1); >840 >841 return 1; >842 exception >843 when no_data_found >844 then >845 return 0; >846 end; >847 >848 -- check if a user has a given role, or if such a role is inferrable >849 function user_role_check_debug(channel_id_in in number, >850 user_id_in in number, >851 role_in in varchar2, >852 reason_out out varchar2) >853 return number >854 is >855 org_id number; >856 begin >857 org_id := rhn_user.get_org_id(user_id_in); >858 >859 if role_in = 'manage' and >860 NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then >861 reason_out := 'channel_not_owned'; >862 return 0; >863 end if; >864 >865 if role_in = 'subscribe' and >866 rhn_channel.get_org_access(channel_id_in, org_id) = 0 then >867 reason_out := 'channel_not_available'; >868 return 0; >869 end if; >870 >871 -- channel admins have all roles >872 if rhn_user.check_role_implied(user_id_in, 'channel_admin') = 1 then >873 reason_out := 'channel_admin'; >874 return 1; >875 end if; >876 >877 -- the subscribe permission is inferred >878 -- UNLESS the not_globally_subscribable flag is set >879 if role_in = 'subscribe' >880 then >881 if rhn_channel.org_channel_setting(channel_id_in, >882 org_id, >883 'not_globally_subscribable') = 0 then >884 reason_out := 'globally_subscribable'; >885 return 1; >886 end if; >887 end if; >888 >889 -- all other roles (manage right now) are explicitly granted >890 reason_out := 'direct_permission'; >891 return rhn_channel.direct_user_role_check(channel_id_in, >892 user_id_in, role_in); >893 end; >894 >895 -- same as above, but with no OUT param; useful in views, etc >896 function user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) >897 return number >898 is >899 throwaway varchar2(256); >900 begin >901 return rhn_channel.user_role_check_debug(channel_id_in, user_id_in, role_in, throwaway); >902 end; >903 >904 -- same as above, but returns 1 if user_id_in is null >905 -- This is useful in queries where user_id is not specified >906 function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) >907 return number >908 is >909 begin >910 if user_id_in is null then >911 return 1; >912 end if; >913 return user_role_check(channel_id_in, user_id_in, role_in); >914 end loose_user_role_check; >915 >916 -- directly checks the table, no inferred permissions >917 function direct_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2) >918 return number >919 is >920 throwaway number; >921 begin >922 -- the idea: if we get past this query, the user has the role, else catch the exception and return 0 >923 select 1 into throwaway >924 from rhnChannelPermissionRole CPR, >925 rhnChannelPermission CP >926 where CP.user_id = user_id_in >927 and CP.channel_id = channel_id_in >928 and CPR.label = role_in >929 and CP.role_id = CPR.id; >930 >931 return 1; >932 exception >933 when no_data_found >934 then >935 return 0; >936 end; >937 >938 -- check if an org has a certain setting >939 function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in in varchar2) >940 return number >941 is >942 throwaway number; >943 begin >944 -- the idea: if we get past this query, the org has the setting, else catch the exception and return 0 >945 select 1 into throwaway >946 from rhnOrgChannelSettingsType OCST, >947 rhnOrgChannelSettings OCS >948 where OCS.org_id = org_id_in >949 and OCS.channel_id = channel_id_in >950 and OCST.label = setting_in >951 and OCS.setting_id = OCST.id; >952 >953 return 1; >954 exception >955 when no_data_found >956 then >957 return 0; >958 end; >959 >960 FUNCTION channel_priority(channel_id_in IN number) >961 RETURN number >962 IS >963 channel_name varchar2(64); >964 priority number; >965 end_of_life_val date; >966 org_id_val number; >967 BEGIN >968 >969 select name, end_of_life, org_id >970 into channel_name, end_of_life_val, org_id_val >971 from rhnChannel >972 where id = channel_id_in; >973 >974 if end_of_life_val is not null then >975 return -400; >976 end if; >977 >978 if channel_name like 'Red Hat Enterprise Linux%' or channel_name like 'RHEL%' then >979 priority := 1000; >980 if channel_name not like '%Beta%' then >981 priority := priority + 1000; >982 end if; >983 >984 priority := priority + >985 case >986 when channel_name like '%v. 5%' then 600 >987 when channel_name like '%v. 4%' then 500 >988 when channel_name like '%v. 3%' then 400 >989 when channel_name like '%v. 2%' then 300 >990 when channel_name like '%v. 1%' then 200 >991 else 0 >992 end; >993 >994 priority := priority + >995 case >996 when channel_name like 'Red Hat Enterprise Linux (v. 5%' then 60 >997 when (channel_name like '%AS%' and channel_name not like '%Extras%') then 50 >998 when (channel_name like '%ES%' and channel_name not like '%Extras%') then 40 >999 when (channel_name like '%WS%' and channel_name not like '%Extras%') then 30 >1000 when (channel_name like '%Desktop%' and channel_name not like '%Extras%') then 20 >1001 when channel_name like '%Extras%' then 10 >1002 else 0 >1003 end; >1004 >1005 priority := priority + >1006 case >1007 when channel_name like '%)' then 5 >1008 else 0 >1009 end; >1010 >1011 priority := priority + >1012 case >1013 when channel_name like '%32-bit x86%' then 4 >1014 when channel_name like '%64-bit Intel Itanium%' then 3 >1015 when channel_name like '%64-bit AMD64/Intel EM64T%' then 2 >1016 else 0 >1017 end; >1018 elsif channel_name like 'Red Hat Desktop%' then >1019 priority := 900; >1020 >1021 if channel_name not like '%Beta%' then >1022 priority := priority + 50; >1023 end if; >1024 >1025 priority := priority + >1026 case >1027 when channel_name like '%v. 4%' then 40 >1028 when channel_name like '%v. 3%' then 30 >1029 when channel_name like '%v. 2%' then 20 >1030 when channel_name like '%v. 1%' then 10 >1031 else 0 >1032 end; >1033 >1034 priority := priority + >1035 case >1036 when channel_name like '%32-bit x86%' then 4 >1037 when channel_name like '%64-bit Intel Itanium%' then 3 >1038 when channel_name like '%64-bit AMD64/Intel EM64T%' then 2 >1039 else 0 >1040 end; >1041 >1042 elsif org_id_val is not null then >1043 priority := 600; >1044 else >1045 priority := 500; >1046 end if; >1047 >1048 return -priority; >1049 >1050 end channel_priority; >1051 >1052 -- right now this only does the accounting changes; the cascade >1053 -- actually does the rhnServerChannel delete. >1054 procedure delete_server_channels(server_id_in in number) >1055 is >1056 begin >1057 update rhnPrivateChannelFamily >1058 set current_members = current_members -1 >1059 where org_id in ( >1060 select org_id >1061 from rhnServer >1062 where id = server_id_in >1063 ) >1064 and channel_family_id in ( >1065 select rcfm.channel_family_id >1066 from rhnChannelFamilyMembers rcfm, >1067 rhnServerChannel rsc >1068 where rsc.server_id = server_id_in >1069 and rsc.channel_id = rcfm.channel_id >1070 and not exists ( >1071 select 1 >1072 from >1073 rhnChannelFamilyVirtSubLevel cfvsl, >1074 rhnSGTypeVirtSubLevel sgtvsl, >1075 rhnServerEntitlementView sev, >1076 rhnVirtualInstance vi >1077 where >1078 -- system is a virtual instance >1079 vi.virtual_system_id = server_id_in >1080 and vi.host_system_id = sev.server_id >1081 -- system's host has a virt ent >1082 and sev.label in ('virtualization_host', >1083 'virtualization_host_platform') >1084 and sev.server_group_type_id = >1085 sgtvsl.server_group_type_id >1086 -- the host's virt ent grants a cf virt sub level >1087 and sgtvsl.virt_sub_level_id = cfvsl.virt_sub_level_id >1088 -- the cf is in that virt sub level >1089 and cfvsl.channel_family_id = rcfm.channel_family_id >1090 ) >1091 ); >1092 end; >1093 >1094 -- this could certainly be optimized to do updates if needs be >1095 procedure refresh_newest_package(channel_id_in in number, caller_in in varchar2 := '(unknown)') >1096 is >1097 begin >1098 delete from rhnChannelNewestPackage where channel_id = channel_id_in; >1099 insert into rhnChannelNewestPackage >1100 ( channel_id, name_id, evr_id, package_id, package_arch_id ) >1101 ( select channel_id, >1102 name_id, evr_id, >1103 package_id, package_arch_id >1104 from rhnChannelNewestPackageView >1105 where channel_id = channel_id_in >1106 ); >1107 insert into rhnChannelNewestPackageAudit (channel_id, caller) >1108 values (channel_id_in, caller_in); >1109 update rhnChannel >1110 set last_modified = greatest(sysdate, last_modified + 1/86400) >1111 where id = channel_id_in; >1112 end; >1113 >1114 procedure update_channel ( channel_id_in in number, invalidate_ss in number := 0, >1115 date_to_use in date := sysdate ) >1116 is >1117 >1118 channel_last_modified date; >1119 last_modified_value date; >1120 >1121 cursor snapshots is >1122 select snapshot_id id >1123 from rhnSnapshotChannel >1124 where channel_id = channel_id_in; >1125 >1126 begin >1127 >1128 select last_modified >1129 into channel_last_modified >1130 from rhnChannel >1131 where id = channel_id_in; >1132 >1133 last_modified_value := date_to_use; >1134 >1135 if last_modified_value <= channel_last_modified then >1136 last_modified_value := last_modified_value + 1/86400; >1137 end if; >1138 >1139 update rhnChannel set last_modified = last_modified_value >1140 where id = channel_id_in; >1141 >1142 if invalidate_ss = 1 then >1143 for snapshot in snapshots loop >1144 update rhnSnapshot >1145 set invalid = lookup_snapshot_invalid_reason('channel_modified') >1146 where id = snapshot.id; >1147 end loop; >1148 end if; >1149 >1150 end update_channel; >1151 >1152 procedure update_channels_by_package ( package_id_in in number, date_to_use in date := sysdate ) >1153 is >1154 >1155 cursor channels is >1156 select channel_id >1157 from rhnChannelPackage >1158 where package_id = package_id_in >1159 order by channel_id; >1160 >1161 begin >1162 for channel in channels loop >1163 -- we want to invalidate the snapshot assocated with the channel when we >1164 -- do this b/c we know we've added or removed or packages >1165 rhn_channel.update_channel ( channel.channel_id, 1, date_to_use ); >1166 end loop; >1167 end update_channels_by_package; >1168 >1169 >1170 procedure update_channels_by_errata ( errata_id_in number, date_to_use in date := sysdate ) >1171 is >1172 >1173 cursor channels is >1174 select channel_id >1175 from rhnChannelErrata >1176 where errata_id = errata_id_in >1177 order by channel_id; >1178 >1179 begin >1180 for channel in channels loop >1181 -- we won't invalidate snapshots, b/c just changing the errata associated with >1182 -- a channel shouldn't invalidate snapshots >1183 rhn_channel.update_channel ( channel.channel_id, 0, date_to_use ); >1184 end loop; >1185 end update_channels_by_errata; >1186 >1187 END rhn_channel; >1188 / > >Warning: Package Body created with compilation errors. > >SQL> >SQL> CREATE OR REPLACE > 2 PACKAGE rhn_package > 3 IS > 4 CURSOR channel_occupancy_cursor(package_id_in IN NUMBER) IS > 5 SELECT C.id channel_id, C.name channel_name > 6 FROM rhnChannel C, > 7 rhnChannelPackage CP > 8 WHERE C.id = CP.channel_id > 9 AND CP.package_id = package_id_in > 10 ORDER BY C.name DESC; > 11 > 12 FUNCTION canonical_name(name_in IN VARCHAR2, evr_in IN EVR_T, > 13 arch_in IN VARCHAR2 := NULL) > 14 RETURN VARCHAR2 > 15 DETERMINISTIC; > 16 > 17 FUNCTION channel_occupancy_string(package_id_in IN NUMBER, separator_in VARCHAR2 := ', ') > 18 RETURN VARCHAR2; > 19 > 20 END rhn_package; > 21 / > >Package created. > >SQL> >SQL> CREATE OR REPLACE PACKAGE rpm AS > 2 FUNCTION vercmp( > 3 e1 VARCHAR2, v1 VARCHAR2, r1 VARCHAR2, > 4 e2 VARCHAR2, v2 VARCHAR2, r2 VARCHAR2) > 5 RETURN NUMBER > 6 DETERMINISTIC > 7 PARALLEL_ENABLE; > 8 PRAGMA RESTRICT_REFERENCES(vercmp, WNDS, RNDS); > 9 > 10 FUNCTION vercmpCounter > 11 return NUMBER > 12 PARALLEL_ENABLE; > 13 PRAGMA RESTRICT_REFERENCES(vercmpCounter, WNDS, RNDS); > 14 > 15 FUNCTION vercmpResetCounter > 16 return NUMBER > 17 PARALLEL_ENABLE; > 18 PRAGMA RESTRICT_REFERENCES(vercmpResetCounter, WNDS, RNDS); > 19 > 20 END rpm; > 21 / > >Package created. > >SQL> >SQL> CREATE OR REPLACE PACKAGE BODY rpm AS > 2 vercmp_counter NUMBER := 0; > 3 > 4 FUNCTION isdigit(ch CHAR) > 5 RETURN BOOLEAN > 6 deterministic > 7 IS > 8 BEGIN > 9 if ascii(ch) between ascii('0') and ascii('9') > 10 then > 11 return TRUE; > 12 end if; > 13 return FALSE; > 14 END isdigit; > 15 > 16 > 17 FUNCTION isalpha(ch CHAR) > 18 RETURN BOOLEAN > 19 deterministic > 20 IS > 21 BEGIN > 22 if ascii(ch) between ascii('a') and ascii('z') or > 23 ascii(ch) between ascii('A') and ascii('Z') > 24 then > 25 return TRUE; > 26 end if; > 27 return FALSE; > 28 END isalpha; > 29 > 30 > 31 FUNCTION isalphanum(ch CHAR) > 32 RETURN BOOLEAN > 33 deterministic > 34 IS > 35 BEGIN > 36 if ascii(ch) between ascii('a') and ascii('z') or > 37 ascii(ch) between ascii('A') and ascii('Z') or > 38 ascii(ch) between ascii('0') and ascii('9') > 39 then > 40 return TRUE; > 41 end if; > 42 return FALSE; > 43 END isalphanum; > 44 > 45 > 46 FUNCTION rpmstrcmp (string1 IN VARCHAR2, string2 IN VARCHAR2) > 47 RETURN NUMBER > 48 deterministic > 49 IS > 50 digits CHAR(10) := '0123456789'; > 51 lc_alpha CHAR(27) := 'abcdefghijklmnopqrstuvwxyz'; > 52 uc_alpha CHAR(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; > 53 alpha CHAR(54) := lc_alpha || uc_alpha; > 54 str1 VARCHAR2(32767) := string1; > 55 str2 VARCHAR2(32767) := string2; > 56 one VARCHAR2(32767); > 57 two VARCHAR2(32767); > 58 isnum BOOLEAN; > 59 BEGIN > 60 if str1 is NULL or str2 is NULL > 61 then > 62 raise VALUE_ERROR; > 63 end if; > 64 -- easy comparison to see if versions are identical > 65 if str1 = str2 > 66 then > 67 return 0; > 68 end if; > 69 -- loop through each version segment of str1 and str2 and compare them > 70 one := str1; > 71 two := str2; > 72 > 73 <<segment_loop>> > 74 while one is not null and two is not null > 75 loop > 76 declare > 77 segm1 VARCHAR2(32767); > 78 segm2 VARCHAR2(32767); > 79 begin > 80 --DBMS_OUTPUT.PUT_LINE('Params: ' || one || ',' || two); > 81 -- Throw out all non-alphanum characters > 82 while one is not null and not isalphanum(one) > 83 loop > 84 one := substr(one, 2); > 85 end loop; > 86 while two is not null and not isalphanum(two) > 87 loop > 88 two := substr(two, 2); > 89 end loop; > 90 --DBMS_OUTPUT.PUT_LINE('new params: ' || one || ',' || two); > 91 > 92 str1 := one; > 93 str2 := two; > 94 > 95 /* grab first completely alpha or completely numeric segment */ > 96 /* leave one and two pointing to the start of the alpha or numeric */ > 97 /* segment and walk str1 and str2 to end of segment */ > 98 > 99 if str1 is not null and isdigit(str1) >100 then >101 str1 := ltrim(str1, digits); >102 str2 := ltrim(str2, digits); >103 isnum := true; >104 else >105 str1 := ltrim(str1, alpha); >106 str2 := ltrim(str2, alpha); >107 isnum := false; >108 end if; >109 >110 --DBMS_OUTPUT.PUT_LINE('Len: ' || length(str1) || ',' || length(str2)); >111 -- Oracle trats the length of an empty string as null >112 if str1 is not null >113 then segm1 := substr(one, 1, length(one) - length(str1)); >114 else segm1 := one; >115 end if; >116 >117 if str2 is not null >118 then segm2 := substr(two, 1, length(two) - length(str2)); >119 else segm2 := two; >120 end if; >121 >122 --DBMS_OUTPUT.PUT_LINE('Segments: ' || segm1 || ',' || segm2); >123 --DBMS_OUTPUT.PUT_LINE('Rest: ' || str1 || ',' || str2); >124 /* take care of the case where the two version segments are */ >125 /* different types: one numeric and one alpha */ >126 if segm1 is null then return -1; end if; /* arbitrary */ >127 if segm2 is null then >128 if isnum then >129 return 1; >130 else >131 return -1; >132 end if; >133 end if; >134 >135 if isnum >136 then >137 /* this used to be done by converting the digit segments */ >138 /* to ints using atoi() - it's changed because long */ >139 /* digit segments can overflow an int - this should fix that. */ >140 >141 /* throw away any leading zeros - it's a number, right? */ >142 segm1 := ltrim(segm1, '0'); >143 segm2 := ltrim(segm2, '0'); >144 >145 /* whichever number has more digits wins */ >146 -- length of empty string is null >147 if segm1 is null and segm2 is not null >148 then >149 return -1; >150 end if; >151 if segm1 is not null and segm2 is null >152 then >153 return 1; >154 end if; >155 if length(segm1) > length(segm2) then return 1; end if; >156 if length(segm2) > length(segm1) then return -1; end if; >157 end if; >158 >159 /* strcmp will return which one is greater - even if the two */ >160 /* segments are alpha or if they are numeric. don't return */ >161 /* if they are equal because there might be more segments to */ >162 /* compare */ >163 >164 if segm1 < segm2 then return -1; end if; >165 if segm1 > segm2 then return 1; end if; >166 >167 one := str1; >168 two := str2; >169 end; >170 end loop segment_loop; >171 /* this catches the case where all numeric and alpha segments have */ >172 /* compared identically but the segment sepparating characters were */ >173 /* different */ >174 if one is null and two is null then return 0; end if; >175 >176 /* whichever version still has characters left over wins */ >177 if one is null then return -1; end if; >178 return 1; >179 END rpmstrcmp; >180 >181 >182 FUNCTION vercmp( >183 e1 VARCHAR2, v1 VARCHAR2, r1 VARCHAR2, >184 e2 VARCHAR2, v2 VARCHAR2, r2 VARCHAR2) >185 RETURN NUMBER >186 IS >187 rc NUMBER; >188 BEGIN >189 DECLARE >190 ep1 NUMBER; >191 ep2 NUMBER; >192 BEGIN >193 vercmp_counter := vercmp_counter + 1; >194 if e1 is null then >195 ep1 := 0; >196 else >197 ep1 := TO_NUMBER(e1); >198 end if; >199 if e2 is null then >200 ep2 := 0; >201 else >202 ep2 := TO_NUMBER(e2); >203 end if; >204 -- Epochs are non-null; compare them >205 if ep1 < ep2 then return -1; end if; >206 if ep1 > ep2 then return 1; end if; >207 rc := rpmstrcmp(v1, v2); >208 if rc != 0 then return rc; end if; >209 return rpmstrcmp(r1, r2); >210 END; >211 >212 END vercmp; >213 >214 FUNCTION vercmpCounter >215 RETURN NUMBER >216 IS >217 BEGIN >218 return vercmp_counter; >219 END vercmpCounter; >220 >221 FUNCTION vercmpResetCounter >222 RETURN NUMBER >223 IS >224 result NUMBER; >225 BEGIN >226 result := vercmp_counter; >227 vercmp_counter := 0; >228 return result; >229 END vercmpResetCounter; >230 END rpm; >231 / > >Package body created. > >SQL> >SQL> create or replace function > 2 channel_name_join(sep_in in varchar2, ch_in in channel_name_t) > 3 return varchar2 > 4 deterministic > 5 is > 6 ret varchar2(4000); > 7 i binary_integer; > 8 begin > 9 ret := ''; > 10 i := ch_in.first; > 11 > 12 if i is null > 13 then > 14 return ret; > 15 end if; > 16 > 17 ret := ch_in(i); > 18 i := ch_in.next(i); > 19 > 20 while i is not null > 21 loop > 22 ret := ret || sep_in || ch_in(i); > 23 i := ch_in.next(i); > 24 end loop; > 25 > 26 return ret; > 27 end; > 28 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 ID_JOIN(sep_in IN VARCHAR2, ugi_in IN user_group_id_t) > 3 RETURN VARCHAR2 > 4 deterministic > 5 IS > 6 ret VARCHAR2(4000); > 7 i BINARY_INTEGER; > 8 BEGIN > 9 ret := ''; > 10 i := ugi_in.FIRST; > 11 > 12 IF i IS NULL > 13 THEN > 14 RETURN ret; > 15 END IF; > 16 > 17 ret := ugi_in(i); > 18 i := ugi_in.NEXT(i); > 19 > 20 WHILE i IS NOT NULL > 21 LOOP > 22 ret := ret || sep_in || ugi_in(i); > 23 i := ugi_in.NEXT(i); > 24 END LOOP; > 25 > 26 RETURN ret; > 27 END; > 28 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LABEL_JOIN(sep_in IN VARCHAR2, ugi_in IN user_group_label_t) > 3 RETURN VARCHAR2 > 4 deterministic > 5 IS > 6 ret VARCHAR2(4000); > 7 i BINARY_INTEGER; > 8 BEGIN > 9 ret := ''; > 10 i := ugi_in.FIRST; > 11 > 12 IF i IS NULL > 13 THEN > 14 RETURN ret; > 15 END IF; > 16 > 17 ret := ugi_in(i); > 18 i := ugi_in.NEXT(i); > 19 > 20 WHILE i IS NOT NULL > 21 LOOP > 22 ret := ret || sep_in || ugi_in(i); > 23 i := ugi_in.NEXT(i); > 24 END LOOP; > 25 > 26 RETURN ret; > 27 END; > 28 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_CHANNEL_ARCH(label_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 channel_arch_id NUMBER; > 6 BEGIN > 7 SELECT id > 8 INTO channel_arch_id > 9 FROM rhnChannelArch > 10 WHERE label = label_in; > 11 > 12 RETURN channel_arch_id; > 13 EXCEPTION > 14 WHEN NO_DATA_FOUND THEN > 15 rhn_exception.raise_exception('channel_arch_not_found'); > 16 END; > 17 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_CLIENT_CAPABILITY(name_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 cap_name_id NUMBER; > 7 BEGIN > 8 SELECT id > 9 INTO cap_name_id > 10 FROM rhnClientCapabilityName > 11 WHERE name = name_in; > 12 > 13 RETURN cap_name_id; > 14 EXCEPTION > 15 WHEN NO_DATA_FOUND THEN > 16 INSERT INTO rhnClientCapabilityName (id, name) > 17 VALUES (rhn_client_capname_id_seq.nextval, name_in) > 18 RETURNING id INTO cap_name_id; > 19 COMMIT; > 20 RETURN cap_name_id; > 21 END; > 22 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_CONFIG_FILENAME(name_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 name_id NUMBER; > 7 BEGIN > 8 SELECT id > 9 INTO name_id > 10 FROM rhnConfigFileName > 11 WHERE path = name_in; > 12 > 13 RETURN name_id; > 14 EXCEPTION > 15 WHEN NO_DATA_FOUND THEN > 16 INSERT INTO rhnConfigFileName (id, path) > 17 VALUES (rhn_cfname_id_seq.nextval, name_in) > 18 RETURNING id INTO name_id; > 19 COMMIT; > 20 RETURN name_id; > 21 END; > 22 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_CVE(name_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 name_id NUMBER; > 7 BEGIN > 8 SELECT id > 9 INTO name_id > 10 FROM rhnCve > 11 WHERE name = name_in; > 12 > 13 RETURN name_id; > 14 EXCEPTION > 15 WHEN NO_DATA_FOUND THEN > 16 INSERT INTO rhnCve (id, name) > 17 VALUES (rhn_cve_id_seq.nextval, name_in) > 18 RETURNING id INTO name_id; > 19 COMMIT; > 20 RETURN name_id; > 21 END LOOKUP_CVE; > 22 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_EVR(e_in IN VARCHAR2, v_in IN VARCHAR2, r_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 evr_id NUMBER; > 7 BEGIN > 8 SELECT id INTO evr_id > 9 FROM rhnPackageEvr > 10 WHERE ((epoch IS NULL and e_in IS NULL) OR (epoch = e_in)) > 11 AND version = v_in AND release = r_in; > 12 > 13 RETURN evr_id; > 14 EXCEPTION > 15 WHEN NO_DATA_FOUND THEN > 16 INSERT INTO rhnPackageEvr (id, epoch, version, release, evr) > 17 VALUES (rhn_pkg_evr_seq.nextval, e_in, v_in, r_in, > 18 EVR_T(e_in, v_in, r_in)) > 19 RETURNING id INTO evr_id; > 20 COMMIT; > 21 RETURN evr_id; > 22 END; > 23 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_PACKAGE_ARCH(label_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 package_arch_id NUMBER; > 6 BEGIN > 7 if label_in is null then > 8 return null; > 9 end if; > 10 > 11 SELECT id > 12 INTO package_arch_id > 13 FROM rhnPackageArch > 14 WHERE label = label_in; > 15 > 16 RETURN package_arch_id; > 17 EXCEPTION > 18 WHEN NO_DATA_FOUND THEN > 19 rhn_exception.raise_exception('package_arch_not_found'); > 20 END; > 21 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_PACKAGE_CAPABILITY(name_in IN VARCHAR2, > 3 version_in IN VARCHAR2 DEFAULT NULL) > 4 RETURN NUMBER > 5 IS > 6 PRAGMA AUTONOMOUS_TRANSACTION; > 7 name_id NUMBER; > 8 BEGIN > 9 IF version_in IS NULL THEN > 10 SELECT id > 11 INTO name_id > 12 FROM rhnPackageCapability > 13 WHERE name = name_in > 14 AND version IS NULL; > 15 ELSE > 16 SELECT id > 17 INTO name_id > 18 FROM rhnPackageCapability > 19 WHERE name = name_in > 20 AND version = version_in; > 21 END IF; > 22 RETURN name_id; > 23 EXCEPTION > 24 WHEN NO_DATA_FOUND THEN > 25 INSERT INTO rhnPackageCapability (id, name, version) > 26 VALUES (rhn_pkg_capability_id_seq.nextval, name_in, version_in) > 27 RETURNING id INTO name_id; > 28 COMMIT; > 29 RETURN name_id; > 30 END; > 31 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_PACKAGE_DELTA(n_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 name_id NUMBER; > 7 BEGIN > 8 SELECT id INTO name_id > 9 FROM rhnPackageDelta > 10 WHERE label = n_in; > 11 > 12 RETURN name_id; > 13 EXCEPTION > 14 WHEN NO_DATA_FOUND THEN > 15 INSERT INTO rhnPackageDelta (id, label) > 16 VALUES (rhn_packagedelta_id_seq.nextval, n_in) > 17 RETURNING id INTO name_id; > 18 COMMIT; > 19 RETURN name_id; > 20 END; > 21 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_PACKAGE_NAME(name_in IN VARCHAR2, ignore_null in number := 0) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 name_id NUMBER; > 7 BEGIN > 8 if ignore_null = 1 and name_in is null then > 9 return null; > 10 end if; > 11 > 12 SELECT id > 13 INTO name_id > 14 FROM rhnPackageName > 15 WHERE name = name_in; > 16 > 17 RETURN name_id; > 18 EXCEPTION > 19 WHEN NO_DATA_FOUND THEN > 20 INSERT INTO rhnPackageName (id, name) > 21 VALUES (rhn_pkg_name_seq.nextval, name_in) > 22 RETURNING id INTO name_id; > 23 COMMIT; > 24 RETURN name_id; > 25 END; > 26 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_SERVER_ARCH(label_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 server_arch_id NUMBER; > 6 BEGIN > 7 SELECT id > 8 INTO server_arch_id > 9 FROM rhnServerArch > 10 WHERE label = label_in; > 11 > 12 RETURN server_arch_id; > 13 EXCEPTION > 14 WHEN NO_DATA_FOUND THEN > 15 rhn_exception.raise_exception('server_arch_not_found'); > 16 END; > 17 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 lookup_snapshot_invalid_reason(label_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 snapshot_invalid_reason_id number; > 7 BEGIN > 8 SELECT id > 9 INTO snapshot_invalid_reason_id > 10 FROM rhnSnapshotInvalidReason > 11 WHERE label = label_in; > 12 > 13 RETURN snapshot_invalid_reason_id; > 14 EXCEPTION > 15 WHEN NO_DATA_FOUND THEN > 16 rhn_exception.raise_exception('invalid_snapshot_invalid_reason'); > 17 END; > 18 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_SOURCE_NAME(name_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 source_id NUMBER; > 7 BEGIN > 8 select id into source_id > 9 from rhnSourceRPM > 10 where name = name_in; > 11 > 12 RETURN source_id; > 13 EXCEPTION > 14 WHEN NO_DATA_FOUND THEN > 15 insert into rhnSourceRPM(id, name) > 16 values (rhn_sourcerpm_id_seq.nextval, name_in) > 17 returning id into source_id; > 18 COMMIT; > 19 RETURN source_id; > 20 END; > 21 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_TAG(org_id_in IN NUMBER, name_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 tag_id NUMBER; > 7 BEGIN > 8 select id into tag_id > 9 from rhnTag > 10 where org_id = org_id_in > 11 and name_id = lookup_tag_name(name_in); > 12 > 13 RETURN tag_id; > 14 EXCEPTION > 15 WHEN NO_DATA_FOUND THEN > 16 insert into rhnTag(id, org_id, name_id) > 17 values (rhn_tag_id_seq.nextval, org_id_in, lookup_tag_name(name_in)) > 18 returning id into tag_id; > 19 COMMIT; > 20 RETURN tag_id; > 21 END; > 22 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_TAG_NAME(name_in IN VARCHAR2) > 3 RETURN NUMBER > 4 IS > 5 PRAGMA AUTONOMOUS_TRANSACTION; > 6 name_id NUMBER; > 7 BEGIN > 8 select id into name_id > 9 from rhnTagName > 10 where name = name_in; > 11 > 12 RETURN name_id; > 13 EXCEPTION > 14 WHEN NO_DATA_FOUND THEN > 15 insert into rhnTagName(id, name) > 16 values (rhn_tagname_id_seq.nextval, name_in) > 17 returning id into name_id; > 18 COMMIT; > 19 RETURN name_id; > 20 END; > 21 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 LOOKUP_TRANSACTION_PACKAGE(o_in IN VARCHAR2, n_in IN VARCHAR2, > 3 e_in IN VARCHAR2, v_in IN VARCHAR2, r_in IN VARCHAR2, a_in IN VARCHAR2) > 4 RETURN NUMBER > 5 IS > 6 PRAGMA AUTONOMOUS_TRANSACTION; > 7 o_id NUMBER; > 8 n_id NUMBER; > 9 e_id NUMBER; > 10 p_arch_id NUMBER; > 11 tp_id NUMBER; > 12 BEGIN > 13 BEGIN > 14 SELECT id > 15 INTO o_id > 16 FROM rhnTransactionOperation > 17 WHERE label = o_in; > 18 EXCEPTION > 19 WHEN NO_DATA_FOUND THEN > 20 rhn_exception.raise_exception('invalid_transaction_operation'); > 21 END; > 22 > 23 SELECT LOOKUP_PACKAGE_NAME(n_in) > 24 INTO n_id > 25 FROM dual; > 26 > 27 SELECT LOOKUP_EVR(e_in, v_in, r_in) > 28 INTO e_id > 29 FROM dual; > 30 > 31 p_arch_id := NULL; > 32 IF a_in IS NOT NULL > 33 THEN > 34 SELECT LOOKUP_PACKAGE_ARCH(a_in) > 35 INTO p_arch_id > 36 FROM dual; > 37 END IF; > 38 > 39 SELECT id > 40 INTO tp_id > 41 FROM rhnTransactionPackage > 42 WHERE operation = o_id > 43 AND name_id = n_id > 44 AND evr_id = e_id > 45 AND (package_arch_id = p_arch_id OR (p_arch_id IS NULL AND package_arch_id IS NULL)); > 46 RETURN tp_id; > 47 EXCEPTION > 48 WHEN NO_DATA_FOUND THEN > 49 INSERT INTO rhnTransactionPackage > 50 (id, operation, name_id, evr_id, package_arch_id) > 51 VALUES (rhn_transpack_id_seq.nextval, o_id, n_id, e_id, p_arch_id) > 52 RETURNING id INTO tp_id; > 53 COMMIT; > 54 RETURN tp_id; > 55 END; > 56 / > >Function created. > >SQL> >SQL> CREATE OR REPLACE FUNCTION > 2 NAME_JOIN(sep_in IN VARCHAR2, ugi_in IN user_group_name_t) > 3 RETURN VARCHAR2 > 4 deterministic > 5 IS > 6 ret VARCHAR2(4000); > 7 i BINARY_INTEGER; > 8 BEGIN > 9 ret := ''; > 10 i := ugi_in.FIRST; > 11 > 12 IF i IS NULL > 13 THEN > 14 RETURN ret; > 15 END IF; > 16 > 17 ret := ugi_in(i); > 18 i := ugi_in.NEXT(i); > 19 > 20 WHILE i IS NOT NULL > 21 LOOP > 22 ret := ret || sep_in || ugi_in(i); > 23 i := ugi_in.NEXT(i); > 24 END LOOP; > 25 > 26 RETURN ret; > 27 END; > 28 / > >Function created. > >SQL> >SQL> create or replace procedure truncateCacheQueue as > 2 begin > 3 execute immediate 'Truncate Table rhnOrgErrataCacheQueue'; > 4 end; > 5 / > >Procedure created. > >SQL> >SQL> -- Bugzilla 453664 >SQL> -- svn r175413 >SQL> create or replace procedure > 2 create_first_org > 3 ( > 4 name_in in varchar2, > 5 password_in in varchar2 > 6 ) is > 7 ug_type number; > 8 group_val number; > 9 begin > 10 insert into web_customer ( > 11 id, name, > 12 oracle_customer_id, oracle_customer_number, > 13 customer_type > 14 ) values ( > 15 1, name_in, > 16 1, 1, 'B' > 17 ); > 18 > 19 select rhn_user_group_id_seq.nextval into group_val from dual; > 20 > 21 select id > 22 into ug_type > 23 from rhnUserGroupType > 24 where label = 'org_admin'; > 25 > 26 insert into rhnUserGroup ( > 27 id, name, > 28 description, > 29 max_members, group_type, org_id > 30 ) values ( > 31 group_val, 'Organization Administrators', > 32 'Organization Administrators for Org ' || name_in || ' (1)', > 33 NULL, ug_type, 1 > 34 ); > 35 > 36 select rhn_user_group_id_seq.nextval into group_val from dual; > 37 > 38 select id > 39 into ug_type > 40 from rhnUserGroupType > 41 where label = 'org_applicant'; > 42 > 43 insert into rhnUserGroup ( > 44 id, name, > 45 description, > 46 max_members, group_type, org_id > 47 ) VALues ( > 48 group_val, 'Organization Applicants', > 49 'Organization Applicants for Org ' || name_in || ' (1)', > 50 NULL, ug_type, 1 > 51 ); > 52 > 53 select rhn_user_group_id_seq.nextval into group_val from dual; > 54 > 55 select id > 56 into ug_type > 57 from rhnUserGroupType > 58 where label = 'system_group_admin'; > 59 > 60 insert into rhnUserGroup ( > 61 id, name, > 62 description, > 63 max_members, group_type, org_id > 64 ) values ( > 65 group_val, 'System Group Administrators', > 66 'System Group Administrators for Org ' || name_in || ' (1)', > 67 NULL, ug_type, 1 > 68 ); > 69 > 70 > 71 select rhn_user_group_id_seq.nextval into group_val from dual; > 72 > 73 select id > 74 into ug_type > 75 from rhnUserGroupType > 76 where label = 'activation_key_admin'; > 77 > 78 insert into rhnUserGroup ( > 79 id, name, > 80 description, > 81 max_members, group_type, org_id > 82 ) values ( > 83 group_val, 'Activation Key Administrators', > 84 'Activation Key Administrators for Org ' || name_in || ' (1)', > 85 NULL, ug_type, 1 > 86 ); > 87 > 88 -- config admin is special; it gets created in > 89 -- rhn_entitlements.set_customer_provisioning instead. > 90 > 91 select rhn_user_group_id_seq.nextval into group_val from dual; > 92 > 93 select id > 94 into ug_type > 95 from rhnUserGroupType > 96 where label = 'channel_admin'; > 97 > 98 insert into rhnUserGroup ( > 99 id, name, >100 description, >101 max_members, group_type, org_id >102 ) values ( >103 group_val, 'Channel Administrators', >104 'Channel Administrators for Org ' || name_in || ' (1)', >105 NULL, ug_type, 1 >106 ); >107 >108 select rhn_user_group_id_seq.nextval into group_val from dual; >109 >110 select id >111 into ug_type >112 from rhnUserGroupType >113 where label = 'satellite_admin'; >114 >115 insert into rhnUserGroup ( >116 id, name, >117 description, >118 max_members, group_type, org_id >119 ) values ( >120 group_val, 'Satellite Administrators', >121 'Satellite Administrators for Org ' || name_in || ' (1)', >122 NULL, ug_type, 1 >123 ); >124 >125 >126 -- if they need more than 16GB, they'll call us and we'll whip >127 -- out a "can be null" patch, which we should do for next >128 -- version anyway. (I thought we did that for this version?) >129 insert into rhnOrgQuota( >130 org_id, total >131 ) values ( >132 1, 1024*1024*1024*16 >133 ); >134 >135 >136 -- there aren't any users yet, so we don't need to update >137 -- rhnUserServerPerms >138 insert into rhnServerGroup >139 ( id, name, description, max_members, group_type, org_id ) >140 select rhn_server_group_id_seq.nextval, sgt.name, sgt.name, >141 0, sgt.id, 1 >142 from rhnServerGroupType sgt >143 where sgt.label = 'sw_mgr_entitled'; >144 >145 end create_first_org; >146 / > >Procedure created. > >SQL> >SQL> create or replace procedure > 2 create_new_org > 3 ( > 4 name_in in varchar2, > 5 password_in in varchar2, > 6 org_id_out out number > 7 ) is > 8 ug_type number; > 9 group_val number; > 10 new_org_id number; > 11 begin > 12 > 13 select web_customer_id_seq.nextval into new_org_id from dual; > 14 > 15 insert into web_customer ( > 16 id, name, > 17 oracle_customer_id, oracle_customer_number, > 18 customer_type > 19 ) values ( > 20 new_org_id, name_in, > 21 new_org_id, new_org_id, 'B' > 22 ); > 23 > 24 select rhn_user_group_id_seq.nextval into group_val from dual; > 25 > 26 select id > 27 into ug_type > 28 from rhnUserGroupType > 29 where label = 'org_admin'; > 30 > 31 insert into rhnUserGroup ( > 32 id, name, > 33 description, > 34 max_members, group_type, org_id > 35 ) values ( > 36 group_val, 'Organization Administrators', > 37 'Organization Administrators for Org ' || name_in, > 38 NULL, ug_type, new_org_id > 39 ); > 40 > 41 select rhn_user_group_id_seq.nextval into group_val from dual; > 42 > 43 select id > 44 into ug_type > 45 from rhnUserGroupType > 46 where label = 'org_applicant'; > 47 > 48 insert into rhnUserGroup ( > 49 id, name, > 50 description, > 51 max_members, group_type, org_id > 52 ) VALues ( > 53 group_val, 'Organization Applicants', > 54 'Organization Applicants for Org ' || name_in, > 55 NULL, ug_type, new_org_id > 56 ); > 57 > 58 select rhn_user_group_id_seq.nextval into group_val from dual; > 59 > 60 select id > 61 into ug_type > 62 from rhnUserGroupType > 63 where label = 'system_group_admin'; > 64 > 65 insert into rhnUserGroup ( > 66 id, name, > 67 description, > 68 max_members, group_type, org_id > 69 ) values ( > 70 group_val, 'System Group Administrators', > 71 'System Group Administrators for Org ' || name_in, > 72 NULL, ug_type, new_org_id > 73 ); > 74 > 75 > 76 select rhn_user_group_id_seq.nextval into group_val from dual; > 77 > 78 select id > 79 into ug_type > 80 from rhnUserGroupType > 81 where label = 'activation_key_admin'; > 82 > 83 insert into rhnUserGroup ( > 84 id, name, > 85 description, > 86 max_members, group_type, org_id > 87 ) values ( > 88 group_val, 'Activation Key Administrators', > 89 'Activation Key Administrators for Org ' || name_in, > 90 NULL, ug_type, new_org_id > 91 ); > 92 > 93 -- config admin is special; it gets created in > 94 -- rhn_entitlements.set_customer_provisioning instead. > 95 > 96 select rhn_user_group_id_seq.nextval into group_val from dual; > 97 > 98 select id > 99 into ug_type >100 from rhnUserGroupType >101 where label = 'channel_admin'; >102 >103 insert into rhnUserGroup ( >104 id, name, >105 description, >106 max_members, group_type, org_id >107 ) values ( >108 group_val, 'Channel Administrators', >109 'Channel Administrators for Org ' || name_in, >110 NULL, ug_type, new_org_id >111 ); >112 >113 -- there aren't any users yet, so we don't need to update >114 -- rhnUserServerPerms >115 insert into rhnServerGroup >116 ( id, name, description, max_members, group_type, org_id ) >117 select rhn_server_group_id_seq.nextval, sgt.name, sgt.name, >118 0, sgt.id, new_org_id >119 from rhnServerGroupType sgt >120 where sgt.label = 'sw_mgr_entitled'; >121 >122 org_id_out := new_org_id; >123 >124 end create_new_org; >125 / > >Procedure created. > >SQL> >SQL> >SQL> -- optimize indexes on rhnServerNeededPackageCache >SQL> -- svn r174738 >SQL> drop index rhn_snpc_pid_eid_sid_idx; > >Index dropped. > >SQL> create index rhn_snpc_pid_idx > 2 on rhnServerNeededPackageCache(package_id) > 3 parallel nologging; > >Index created. > >SQL> >SQL> drop index rhn_snpc_sid_pid_eid_idx; > >Index dropped. > >SQL> create index rhn_snpc_sid_idx > 2 on rhnServerNeededPackageCache(server_id) > 3 parallel nologging; > >Index created. > >SQL> >SQL> drop index rhn_snpc_eid_sid_pid_idx; > >Index dropped. > >SQL> create index rhn_snpc_eid_idx > 2 on rhnServerNeededPackageCache(errata_id) > 3 parallel nologging; > >Index created. > >SQL> >SQL> drop index rhn_snpc_oid_eid_sid_idx; > >Index dropped. > >SQL> create index rhn_snpc_oid_idx > 2 on rhnServerNeededPackageCache(org_id) > 3 parallel nologging; > >Index created. > >SQL> >SQL> -- enable row movement on all tables >SQL> begin > 2 for i in (select * from user_tables) loop > 3 execute immediate 'alter table "' || i.table_name || '" enable row movement'; > 4 end loop; > 5 end; > 6 / > >PL/SQL procedure successfully completed. > >SQL> >SQL> -- recompile erv_t type to avoid ORA-04061 in the next select >SQL> alter type evr_t compile body; > >Type body altered. > >SQL> >SQL> -- End of upgrade body >SQL> >SQL> >SQL> >SQL> update rhnVersionInfo set evr_id = lookup_evr(null, '5.2.0', '8') > 2 where label = 'schema' > 3 and name_id = lookup_package_name('rhn-satellite-schema'); > >1 row updated. > >SQL> >SQL> select '*** Schema version is now ' || e.evr.as_vre_simple() > 2 from rhnPackageEVR e, rhnVersionInfo vi > 3 where vi.evr_id = e.id > 4 and vi.label = 'schema'; > >'***SCHEMAVERSIONISNOW'||E.EVR.AS_VRE_SIMPLE() >-------------------------------------------------------------------------------- >*** Schema version is now 5.2.0-8 > >SQL> >SQL> commit; > >Commit complete. > >SQL> >SQL> >SQL> spool off; >SQL> exit; >Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production >With the Partitioning, OLAP, Data Mining and Real Application Testing options
<b>You cannot view the attachment while viewing its details because your browser does not support IFRAMEs. <a href="attachment.cgi?id=318601">View the attachment on a separate page</a>.</b>
View Attachment As Raw
Actions:
View
Attachments on
bug 464927
:
318600
| 318601 |
318603
|
319056