Spacewalk DB Schema - Package

Main Tables Views Materialized Views Indexes Constraints Triggers Procedures Functions Packages Sequences Java Sources Jobs Sanity Check Index DDL scrips

Package source Package body source

RHN_CHANNEL

DDL script

Package source

Legend: comment string keyword reserved word operator

     1: PACKAGE rhn_channel
     2: IS
     3: 	version varchar2(100) := '';
     4: 
     5:     CURSOR server_base_subscriptions(server_id_in NUMBER) IS
     6:     	   SELECT C.id
     7: 	     FROM rhnChannel C, rhnServerChannel SC
     8: 	    WHERE C.id = SC.channel_id
     9: 	      AND SC.server_id = server_id_in
    10: 	      AND C.parent_channel IS NULL;
    11: 
    12:     CURSOR check_server_subscription(server_id_in NUMBER, channel_id_in NUMBER) IS
    13:            SELECT channel_id
    14: 	     FROM rhnServerChannel
    15: 	    WHERE server_id = server_id_in
    16: 	      AND channel_id = channel_id_in;
    17: 
    18:     CURSOR check_server_parent_membership(server_id_in NUMBER, channel_id_in NUMBER) IS
    19:     	   SELECT C.id
    20: 	     FROM rhnChannel C, rhnServerChannel SC
    21: 	    WHERE C.parent_channel = channel_id_in
    22: 	      AND C.id = SC.channel_id
    23: 	      AND SC.server_id = server_id_in;
    24: 
    25:     CURSOR channel_family_perm_cursor(channel_family_id_in NUMBER, org_id_in NUMBER) IS
    26:            SELECT *
    27: 	     FROM rhnOrgChannelFamilyPermissions
    28: 	    WHERE channel_family_id = channel_family_id_in
    29: 	      AND org_id = org_id_in;
    30: 
    31: 
    32:     PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in number := 0,
    33:                                  deleting_server in number := 0);
    34:     PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in number := null);
    35: 
    36:     FUNCTION guess_server_base(server_id_in IN NUMBER) RETURN NUMBER;
    37: 
    38:     FUNCTION base_channel_for_release_arch(release_in in varchar2,
    39: 	server_arch_in in varchar2, org_id_in in number := -1,
    40: 	user_id_in in number := null) RETURN number;
    41: 
    42:     FUNCTION base_channel_rel_archid(release_in in varchar2,
    43: 	server_arch_id_in in number, org_id_in in number := -1,
    44: 	user_id_in in number := null) RETURN number;
    45: 
    46:     FUNCTION channel_priority(channel_id_in in number) RETURN number;
    47: 
    48:     PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server in number := 0);
    49: 
    50:     FUNCTION family_for_channel(channel_id_in IN NUMBER) RETURN NUMBER;
    51: 
    52:     PROCEDURE unsubscribe_server_from_family(server_id_in in number, channel_family_id_in in number);
    53: 
    54:     PROCEDURE refresh_newest_package(channel_id_in in number,
    55:                                      caller_in in varchar2 := '(unknown)',
    56:                                      package_name_id_in in number := null);
    57: 
    58:     FUNCTION get_org_id(channel_id_in in number) return number;
    59:     PRAGMA RESTRICT_REFERENCES(get_org_id, WNDS, RNPS, WNPS);
    60: 
    61:     function get_org_access(channel_id_in in number, org_id_in in number) return number;
    62:     PRAGMA RESTRICT_REFERENCES(get_org_access, WNDS, RNPS, WNPS);
    63: 
    64:     function get_cfam_org_access(cfam_id_in in number, org_id_in in number) return number;
    65: 
    66:     function user_role_check_debug(channel_id_in in number, user_id_in in number, role_in in varchar2)
    67:         RETURN VARCHAR2;
    68:     PRAGMA RESTRICT_REFERENCES(user_role_check_debug, WNDS, RNPS, WNPS);
    69: 
    70:     function user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
    71:     	RETURN NUMBER;
    72:     PRAGMA RESTRICT_REFERENCES(user_role_check, WNDS, RNPS, WNPS);
    73: 
    74:     function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
    75:     	RETURN NUMBER;
    76:     PRAGMA RESTRICT_REFERENCES(loose_user_role_check, WNDS, RNPS, WNPS);
    77: 
    78:     function direct_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
    79:     	RETURN NUMBER;
    80:     PRAGMA RESTRICT_REFERENCES(direct_user_role_check, WNDS, RNPS, WNPS);
    81: 
    82:     function shared_user_role_check(channel_id in number, user_id in number, role in varchar2)
    83:     	RETURN NUMBER;
    84:     PRAGMA RESTRICT_REFERENCES(shared_user_role_check, WNDS, RNPS, WNPS);
    85: 
    86:     function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in in varchar2)
    87:     	RETURN NUMBER;
    88: 
    89:     PROCEDURE update_channel ( channel_id_in in number, invalidate_ss in number := 0,
    90:                                date_to_use in timestamp with local time zone := current_timestamp );
    91: 
    92:     PROCEDURE  update_channels_by_package ( package_id_in in number, date_to_use in timestamp with local time zone := current_timestamp );
    93: 
    94:      PROCEDURE update_channels_by_errata ( errata_id_in number, date_to_use in timestamp with local time zone := current_timestamp );
    95: 
    96: 
    97:     PRAGMA RESTRICT_REFERENCES(org_channel_setting, WNDS, RNPS, WNPS);
    98: 
    99:     PROCEDURE update_needed_cache(channel_id_in in number);
   100: 
   101:     procedure set_comps(channel_id_in in number, path_in in varchar2, comps_type_id_in in number, timestamp_in in varchar2);
   102: 
   103: END rhn_channel;

Package body source

Legend: comment string keyword reserved word operator

     1: PACKAGE BODY rhn_channel
     2: IS
     3:         body_version varchar2(100) := '';
     4: 
     5:     -- Cursor that fetches all the possible base channels for a
     6:     -- (server_arch_id, release, org_id) combination
     7:         cursor  base_channel_cursor(
     8:                 release_in in varchar2,
     9:                 server_arch_id_in in number,
    10:                 org_id_in in number
    11:         ) return rhnChannel%ROWTYPE is
    12:                 select distinct c.*
    13:                 from    rhnOrgDistChannelMap                       odcm,
    14:                                 rhnServerChannelArchCompat      scac,
    15:                                 rhnChannel                                      c
    16:                 where   c.parent_channel is null
    17:                         and c.id = odcm.channel_id
    18:                         and c.channel_arch_id = odcm.channel_arch_id
    19:                         and odcm.release = release_in
    20:                         and odcm.for_org_id = org_id_in
    21:                         and scac.server_arch_id = server_arch_id_in
    22:                         and scac.channel_arch_id = c.channel_arch_id;
    23: 
    24:     PROCEDURE subscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, user_id_in in number := null)
    25:     IS
    26:         channel_parent_val      rhnChannel.parent_channel%TYPE;
    27:         parent_subscribed       BOOLEAN;
    28:         server_has_base_chan    BOOLEAN;
    29:         server_already_in_chan  BOOLEAN;
    30:         channel_family_id_val   NUMBER;
    31:         allowed                 number := 0;
    32:     BEGIN
    33:         if user_id_in is not null then
    34:             allowed := rhn_channel.user_role_check(channel_id_in, user_id_in, 'subscribe');
    35:         else
    36:             allowed := 1;
    37:         end if;
    38: 
    39:         if allowed = 0 then
    40:             rhn_exception.raise_exception('no_subscribe_permissions');
    41:         end if;
    42: 
    43: 
    44:         SELECT parent_channel INTO channel_parent_val FROM rhnChannel WHERE id = channel_id_in;
    45: 
    46:         IF channel_parent_val IS NOT NULL
    47:         THEN
    48:             -- child channel; if attempting to cross-subscribe a child to the wrong base, silently ignore
    49:             parent_subscribed := FALSE;
    50: 
    51:             FOR check_subscription IN check_server_subscription(server_id_in, channel_parent_val)
    52:             LOOP
    53:                 parent_subscribed := TRUE;
    54:             END LOOP check_subscription;
    55: 
    56:             IF NOT parent_subscribed
    57:             THEN
    58:                 RETURN;
    59:             END IF;
    60:         ELSE
    61:             -- base channel
    62:             server_has_base_chan := FALSE;
    63:             FOR base IN server_base_subscriptions(server_id_in)
    64:             LOOP
    65:                 server_has_base_chan := TRUE;
    66:             END LOOP base;
    67: 
    68:             IF server_has_base_chan
    69:             THEN
    70:                 rhn_exception.raise_exception('channel_server_one_base');
    71:             END IF;
    72:         END IF;
    73: 
    74:         FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in)
    75:         LOOP
    76:             server_already_in_chan := TRUE;
    77:         END LOOP check_subscription;
    78: 
    79:         IF server_already_in_chan
    80:         THEN
    81:             RETURN;
    82:         END IF;
    83: 
    84:         channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
    85:         IF channel_family_id_val IS NULL
    86:         THEN
    87:             rhn_exception.raise_exception('channel_subscribe_no_family');
    88:         END IF;
    89: 
    90:         insert into rhnServerHistory (id,server_id,summary,details) (
    91:             select  rhn_event_id_seq.nextval,
    92:                     server_id_in,
    93:                     'subscribed to channel ' || SUBSTR(c.label, 0, 106),
    94:                     c.label
    95:             from    rhnChannel c
    96:             where   c.id = channel_id_in
    97:         );
    98: 
    99:         INSERT INTO rhnServerChannel (server_id, channel_id) VALUES (server_id_in, channel_id_in);
   100:         queue_server(server_id_in, immediate_in);
   101: 
   102:         update rhnServer
   103:            set channels_changed = current_timestamp
   104:          where id = server_id_in;
   105:     END subscribe_server;
   106: 
   107:     function guess_server_base(
   108:         server_id_in in number
   109:     ) RETURN number is
   110:         cursor server_cursor is
   111:             select s.server_arch_id, s.release, s.org_id
   112:               from rhnServer s
   113:              where s.id = server_id_in;
   114:     begin
   115:         for s in server_cursor loop
   116:             for channel in base_channel_cursor(s.release,
   117:                 s.server_arch_id, s.org_id)
   118:             loop
   119:                 return channel.id;
   120:             end loop base_channel_cursor;
   121:         end loop server_cursor;
   122:         -- Server not found, or no base channel applies to it
   123:         return null;
   124:     end;
   125: 
   126:     -- Private function
   127:     function normalize_server_arch(server_arch_in in varchar2)
   128:     return varchar2
   129:     deterministic
   130:     is
   131:         suffix VARCHAR2(128) := '-redhat-linux';
   132:         suffix_len NUMBER := length(suffix);
   133:     begin
   134:         if server_arch_in is NULL then
   135:             return NULL;
   136:         end if;
   137:         if instr(server_arch_in, '-') > 0
   138:         then
   139:             -- Suffix already present
   140:             return server_arch_in;
   141:         end if;
   142:         return server_arch_in || suffix;
   143:     end normalize_server_arch;
   144: 
   145:     --
   146:     --
   147:     -- Raises:
   148:     --   server_arch_not_found
   149:     --   no_subscribe_permissions
   150:     function base_channel_for_release_arch(
   151:         release_in in varchar2,
   152:         server_arch_in in varchar2,
   153:         org_id_in in number := -1,
   154:         user_id_in in number := null
   155:     ) return number is
   156:         server_arch varchar2(256) := normalize_server_arch(server_arch_in);
   157:         server_arch_id number;
   158:     begin
   159:         -- Look up the server arch
   160:         begin
   161:             select id
   162:               into server_arch_id
   163:               from rhnServerArch
   164:              where label = server_arch;
   165:         exception
   166:             when no_data_found then
   167:                 rhn_exception.raise_exception('server_arch_not_found');
   168:         end;
   169:         return base_channel_rel_archid(release_in, server_arch_id,
   170:             org_id_in, user_id_in);
   171:     end base_channel_for_release_arch;
   172: 
   173:     function base_channel_rel_archid(
   174:         release_in in varchar2,
   175:         server_arch_id_in in number,
   176:         org_id_in in number := -1,
   177:         user_id_in in number := null
   178:     ) return number is
   179:         denied_channel_id number := null;
   180:         valid_org_id number := org_id_in;
   181:         valid_user_id number := user_id_in;
   182:         channel_subscribable number;
   183:     begin
   184:         if org_id_in = -1 and user_id_in is not null then
   185:             -- Get the org id from the user id
   186:             begin
   187:                 select org_id
   188:                   into valid_org_id
   189:                   from web_contact
   190:                  where id = user_id_in;
   191:             exception
   192:                 when no_data_found then
   193:                     -- User doesn't exist
   194:                     -- XXX Only list public stuff for now
   195:                     valid_user_id := null;
   196:                     valid_org_id := -1;
   197:             end;
   198:         end if;
   199: 
   200:         for c in base_channel_cursor(release_in, server_arch_id_in, valid_org_id)
   201:         loop
   202:             -- This row is a possible match
   203:             if valid_user_id is null then
   204:                 -- User ID not specified, so no user to channel permissions to
   205:                 -- check
   206:                 return c.id;
   207:             end if;
   208: 
   209:             -- Check user to channel permissions
   210:             select loose_user_role_check(c.id, user_id_in, 'subscribe')
   211:               into channel_subscribable
   212:               from dual;
   213: 
   214:             if channel_subscribable = 1 then
   215:                 return c.id;
   216:             end if;
   217: 
   218:             -- Base channel exists, but is not subscribable; keep trying
   219:             denied_channel_id := c.id;
   220:         end loop base_channel_fetch;
   221: 
   222:         if denied_channel_id is not null then
   223:             rhn_exception.raise_exception('no_subscribe_permissions');
   224:         end if;
   225:         -- No base channel applies
   226:         return NULL;
   227:     end base_channel_rel_archid;
   228: 
   229:     PROCEDURE clear_subscriptions(server_id_in IN NUMBER, deleting_server IN NUMBER := 0)
   230:     IS
   231:         cursor server_channels(server_id_in in number) is
   232:                 select  s.org_id, sc.channel_id, cfm.channel_family_id
   233:                 from    rhnServer s,
   234:                         rhnServerChannel sc,
   235:                         rhnChannelFamilyMembers cfm
   236:                 where   s.id = server_id_in
   237:                         and s.id = sc.server_id
   238:                         and sc.channel_id = cfm.channel_id
   239:                 order by cfm.channel_family_id;
   240:         last_channel_family_id rhnChannelFamilyMembers.channel_family_id%type := -1;
   241:         last_channel_org_id    rhnServer.org_id%type := -1;
   242:     BEGIN
   243:         for channel in server_channels(server_id_in)
   244:         loop
   245:                 unsubscribe_server(server_id_in, channel.channel_id, 1, 1, deleting_server);
   246:         end loop channel;
   247:     END clear_subscriptions;
   248: 
   249:     PROCEDURE unsubscribe_server(server_id_in IN NUMBER, channel_id_in NUMBER, immediate_in NUMBER := 1, unsubscribe_children_in number := 0,
   250:                                  deleting_server IN NUMBER := 0)
   251:     IS
   252:         channel_family_id_val   NUMBER;
   253:         server_org_id_val       NUMBER;
   254:         server_already_in_chan  BOOLEAN;
   255:         cursor  channel_family_is_proxy(channel_family_id_in in number) is
   256:                 select  1
   257:                 from    rhnChannelFamily
   258:                 where   id = channel_family_id_in
   259:                     and label = 'rhn-proxy';
   260:         cursor  channel_family_is_satellite(channel_family_id_in in number) is
   261:                 select  1
   262:                 from    rhnChannelFamily
   263:                 where   id = channel_family_id_in
   264:                     and label = 'rhn-satellite';
   265:         -- this is *EXACTLY* like check_server_parent_membership, but if we recurse
   266:         -- with the package-level one, we get a "cursor already open", so we need a
   267:         -- copy on our call stack instead.  GROAN.
   268:         cursor local_chk_server_parent_memb (
   269:                         server_id_in number,
   270:                         channel_id_in number ) is
   271:                 select  c.id
   272:                 from    rhnChannel                      c,
   273:                                 rhnServerChannel        sc
   274:                 where   1=1
   275:                         and c.parent_channel = channel_id_in
   276:                         and c.id = sc.channel_id
   277:                         and sc.server_id = server_id_in;
   278:     BEGIN
   279:         FOR child IN local_chk_server_parent_memb(server_id_in, channel_id_in)
   280:         LOOP
   281:             if unsubscribe_children_in = 1 then
   282:                 unsubscribe_server(server_id_in => server_id_in,
   283:                                                                 channel_id_in => child.id,
   284:                                                                 immediate_in => immediate_in,
   285:                                                                 unsubscribe_children_in => unsubscribe_children_in,
   286:                         deleting_server => deleting_server);
   287:             else
   288:                 rhn_exception.raise_exception('channel_unsubscribe_child_exists');
   289:             end if;
   290:         END LOOP child;
   291: 
   292:         server_already_in_chan := FALSE;
   293: 
   294:         FOR check_subscription IN check_server_subscription(server_id_in, channel_id_in)
   295:         LOOP
   296:             server_already_in_chan := TRUE;
   297:         END LOOP check_subscription;
   298: 
   299:         IF NOT server_already_in_chan
   300:         THEN
   301:             RETURN;
   302:         END IF;
   303: 
   304:    if deleting_server = 0 then
   305:       insert into rhnServerHistory (id,server_id,summary,details) (
   306:           select  rhn_event_id_seq.nextval,
   307:                 server_id_in,
   308:              'unsubscribed from channel ' || SUBSTR(c.label, 0, 106),
   309:              c.label
   310:           from    rhnChannel c
   311:           where   c.id = channel_id_in
   312:       );
   313:    end if;
   314: 
   315:    DELETE FROM rhnServerChannel WHERE server_id = server_id_in AND channel_id = channel_id_in;
   316: 
   317:    if deleting_server = 0 then
   318:         queue_server(server_id_in, immediate_in);
   319: 
   320:         update rhnServer
   321:            set channels_changed = current_timestamp
   322:          where id = server_id_in;
   323:    end if;
   324: 
   325:         channel_family_id_val := rhn_channel.family_for_channel(channel_id_in);
   326:         IF channel_family_id_val IS NULL
   327:         THEN
   328:             rhn_exception.raise_exception('channel_unsubscribe_no_family');
   329:         END IF;
   330: 
   331:         for ignore in channel_family_is_satellite(channel_family_id_val) loop
   332:                 delete from rhnSatelliteInfo where server_id = server_id_in;
   333:         end loop;
   334: 
   335:         for ignore in channel_family_is_proxy(channel_family_id_val) loop
   336:                 delete from rhnProxyInfo where server_id = server_id_in;
   337:         end loop;
   338:         SELECT org_id INTO server_org_id_val
   339:           FROM rhnServer
   340:          WHERE id = server_id_in;
   341: 
   342:     END unsubscribe_server;
   343: 
   344: 
   345:     FUNCTION family_for_channel(channel_id_in IN NUMBER)
   346:     RETURN NUMBER
   347:     IS
   348:         channel_family_id_val NUMBER;
   349:     BEGIN
   350:         SELECT channel_family_id INTO channel_family_id_val
   351:           FROM rhnChannelFamilyMembers
   352:          WHERE channel_id = channel_id_in;
   353: 
   354:         RETURN channel_family_id_val;
   355:     EXCEPTION
   356:         WHEN NO_DATA_FOUND
   357:         THEN
   358:             RETURN NULL;
   359:     END family_for_channel;
   360: 
   361:     procedure unsubscribe_server_from_family(server_id_in in number,
   362:                                              channel_family_id_in in number)
   363:     is
   364:     begin
   365:         delete
   366:         from    rhnServerChannel rsc
   367:         where   rsc.server_id = server_id_in
   368:             and channel_id in (
   369:                 select  rcfm.channel_id
   370:                 from    rhnChannelFamilyMembers rcfm
   371:                 where   rcfm.channel_family_id = channel_family_id_in);
   372:     end;
   373: 
   374:     function get_org_id(channel_id_in in number)
   375:     return number
   376:     is
   377:         org_id_out number;
   378:     begin
   379:         select org_id into org_id_out
   380:             from rhnChannel
   381:             where id = channel_id_in;
   382: 
   383:             return org_id_out;
   384:     end get_org_id;
   385: 
   386:     function get_cfam_org_access(cfam_id_in in number, org_id_in in number)
   387:     return number
   388:     is
   389:         cursor  families is
   390:                         select  1
   391:                         from    rhnOrgChannelFamilyPermissions cfp
   392:                         where   cfp.org_id = org_id_in;
   393:     begin
   394:                 -- the idea: if we get past this query,
   395:         -- the user has the role, else catch the exception and return 0
   396:                 for family in families loop
   397:                 return 1;
   398:                 end loop;
   399:                 return 0;
   400:     end;
   401: 
   402:     function get_org_access(channel_id_in in number, org_id_in in number)
   403:     return number
   404:     is
   405:         throwaway number;
   406:     begin
   407:         -- the idea: if we get past this query,
   408:         -- the org has access to the channel, else catch the exception and return 0
   409:         select distinct 1 into throwaway
   410:           from rhnChannelFamilyMembers CFM,
   411:                rhnOrgChannelFamilyPermissions CFP
   412:          where cfp.org_id = org_id_in
   413:            and CFM.channel_family_id = CFP.channel_family_id
   414:            and CFM.channel_id = channel_id_in;
   415: 
   416:         return 1;
   417:         exception
   418:             when no_data_found
   419:             then
   420:             return 0;
   421:     end;
   422: 
   423:     -- check if a user has a given role, or if such a role is inferrable
   424:     -- returns NULL if OK, error message otherwise
   425:     function user_role_check_debug(channel_id_in in number,
   426:                                    user_id_in in number,
   427:                                    role_in in varchar2)
   428:     return varchar2
   429:     is
   430:         org_id number;
   431:     begin
   432:         org_id := rhn_user.get_org_id(user_id_in);
   433: 
   434:         -- channel might be shared
   435:         if role_in = 'subscribe' and
   436:            rhn_channel.shared_user_role_check(channel_id_in, user_id_in, role_in) = 1 then
   437:             return NULL;
   438:         end if;
   439: 
   440:         if role_in = 'manage' and
   441:            NVL(rhn_channel.get_org_id(channel_id_in), -1) <> org_id then
   442:                return 'channel_not_owned';
   443:         end if;
   444: 
   445:         if role_in = 'subscribe' and
   446:            rhn_channel.get_org_access(channel_id_in, org_id) = 0 then
   447:                 return 'channel_not_available';
   448:         end if;
   449: 
   450:         -- channel admins have all roles
   451:         if rhn_user.check_role_implied(user_id_in, 'channel_admin') = 1 then
   452:             return NULL;
   453:         end if;
   454: 
   455:         -- the subscribe permission is inferred
   456:         -- UNLESS the not_globally_subscribable flag is set
   457:         if role_in = 'subscribe'
   458:         then
   459:             if rhn_channel.org_channel_setting(channel_id_in,
   460:                        org_id,
   461:                        'not_globally_subscribable') = 0 then
   462:                     return NULL;
   463:             end if;
   464:         end if;
   465: 
   466:         -- all other roles (manage right now) are explicitly granted
   467:         if rhn_channel.direct_user_role_check(channel_id_in,
   468:                                               user_id_in, role_in) = 1 then
   469:             return NULL;
   470:         end if;
   471:         return 'direct_permission';
   472:     end;
   473: 
   474:     -- same as above, but with 1/0 output; useful in views, etc
   475:     function user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
   476:     return number
   477:     is
   478:     begin
   479:         if rhn_channel.user_role_check_debug(channel_id_in,
   480:                                              user_id_in, role_in) is NULL then
   481:             return 1;
   482:         else
   483:             return 0;
   484:         end if;
   485:     end;
   486: 
   487:     --
   488:     -- For multiorg phase II, this function simply checks to see if the user's
   489:     -- has a trust relationship that includes this channel by id.
   490:     --
   491:     function shared_user_role_check(channel_id in number, user_id in number, role in varchar2)
   492:     return number
   493:     is
   494:       n number;
   495:       oid number;
   496:     begin
   497:       oid := rhn_user.get_org_id(user_id);
   498:       select 1 into n
   499:       from rhnSharedChannelView s
   500:       where s.id = channel_id and s.org_trust_id = oid;
   501:       return 1;
   502:       exception
   503:         when no_data_found then
   504:           return 0;
   505:     end;
   506: 
   507:     -- same as above, but returns 1 if user_id_in is null
   508:     -- This is useful in queries where user_id is not specified
   509:     function loose_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
   510:     return number
   511:     is
   512:     begin
   513:         if user_id_in is null then
   514:             return 1;
   515:         end if;
   516:         return user_role_check(channel_id_in, user_id_in, role_in);
   517:     end loose_user_role_check;
   518: 
   519:     -- directly checks the table, no inferred permissions
   520:     function direct_user_role_check(channel_id_in in number, user_id_in in number, role_in in varchar2)
   521:     return number
   522:     is
   523:         throwaway number;
   524:     begin
   525:         -- the idea: if we get past this query, the user has the role, else catch the exception and return 0
   526:         select 1 into throwaway
   527:           from rhnChannelPermissionRole CPR,
   528:                rhnChannelPermission CP
   529:          where CP.user_id = user_id_in
   530:            and CP.channel_id = channel_id_in
   531:            and CPR.label = role_in
   532:            and CP.role_id = CPR.id;
   533: 
   534:         return 1;
   535:     exception
   536:         when no_data_found
   537:             then
   538:             return 0;
   539:     end;
   540: 
   541:     -- check if an org has a certain setting
   542:     function org_channel_setting(channel_id_in in number, org_id_in in number, setting_in in varchar2)
   543:     return number
   544:     is
   545:         throwaway number;
   546:     begin
   547:         -- the idea: if we get past this query, the org has the setting, else catch the exception and return 0
   548:         select 1 into throwaway
   549:           from rhnOrgChannelSettingsType OCST,
   550:                rhnOrgChannelSettings OCS
   551:          where OCS.org_id = org_id_in
   552:            and OCS.channel_id = channel_id_in
   553:            and OCST.label = setting_in
   554:            and OCS.setting_id = OCST.id;
   555: 
   556:         return 1;
   557:     exception
   558:         when no_data_found
   559:             then
   560:             return 0;
   561:     end;
   562: 
   563:     FUNCTION channel_priority(channel_id_in IN number)
   564:     RETURN number
   565:     IS
   566:          channel_name varchar2(256);
   567:          priority number;
   568:          end_of_life_val timestamp with local time zone;
   569:          org_id_val number;
   570:     BEGIN
   571: 
   572:         select name, end_of_life, org_id
   573:         into channel_name, end_of_life_val, org_id_val
   574:         from rhnChannel
   575:         where id = channel_id_in;
   576: 
   577:         if end_of_life_val is not null then
   578:           return -400;
   579:         end if;
   580: 
   581:         if channel_name like 'Red Hat Enterprise Linux%' or channel_name like 'RHEL%' then
   582:           priority := 1000;
   583:           if channel_name not like '%Beta%' then
   584:             priority := priority + 1000;
   585:           end if;
   586: 
   587:           priority := priority +
   588:             case
   589:               when channel_name like '%v. 5%' then 600
   590:               when channel_name like '%v. 4%' then 500
   591:               when channel_name like '%v. 3%' then 400
   592:               when channel_name like '%v. 2%' then 300
   593:               when channel_name like '%v. 1%' then 200
   594:               else 0
   595:             end;
   596: 
   597:           priority := priority +
   598:             case
   599:               when channel_name like 'Red Hat Enterprise Linux (v. 5%' then 60
   600:               when (channel_name like '%AS%' and channel_name not like '%Extras%') then 50
   601:               when (channel_name like '%ES%' and channel_name not like '%Extras%') then 40
   602:               when (channel_name like '%WS%' and channel_name not like '%Extras%') then 30
   603:               when (channel_name like '%Desktop%' and channel_name not like '%Extras%') then 20
   604:               when channel_name like '%Extras%' then 10
   605:               else 0
   606:             end;
   607: 
   608:           priority := priority +
   609:             case
   610:               when channel_name like '%)' then 5
   611:               else 0
   612:             end;
   613: 
   614:           priority := priority +
   615:             case
   616:               when channel_name like '%32-bit x86%' then 4
   617:               when channel_name like '%64-bit Intel Itanium%' then 3
   618:               when channel_name like '%64-bit AMD64/Intel EM64T%' then 2
   619:               else 0
   620:             end;
   621:         elsif channel_name like 'Red Hat Desktop%' then
   622:             priority := 900;
   623: 
   624:             if channel_name not like '%Beta%' then
   625:                priority := priority + 50;
   626:             end if;
   627: 
   628:           priority := priority +
   629:             case
   630:               when channel_name like '%v. 4%' then 40
   631:               when channel_name like '%v. 3%' then 30
   632:               when channel_name like '%v. 2%' then 20
   633:               when channel_name like '%v. 1%' then 10
   634:               else 0
   635:             end;
   636: 
   637:           priority := priority +
   638:             case
   639:               when channel_name like '%32-bit x86%' then 4
   640:               when channel_name like '%64-bit Intel Itanium%' then 3
   641:               when channel_name like '%64-bit AMD64/Intel EM64T%' then 2
   642:               else 0
   643:             end;
   644: 
   645:         elsif org_id_val is not null then
   646:           priority := 600;
   647:         else
   648:           priority := 500;
   649:         end if;
   650: 
   651:       return -priority;
   652: 
   653:     end channel_priority;
   654: 
   655:     -- this could certainly be optimized to do updates if needs be
   656:     procedure refresh_newest_package(channel_id_in in number,
   657:                                      caller_in in varchar2 := '(unknown)',
   658:                                      package_name_id_in in number := null)
   659:     is
   660:     -- procedure refreshes rows for name_id = package_name_id_in or
   661:     -- all rows if package_name_id_in is null
   662:     begin
   663:         delete from rhnChannelNewestPackage
   664:               where channel_id = channel_id_in
   665:                 and (package_name_id_in is null
   666:                      or name_id = package_name_id_in);
   667:         insert into rhnChannelNewestPackage
   668:                 (channel_id, name_id, evr_id, package_id, package_arch_id)
   669:                 (select channel_id,
   670:                         name_id, evr_id,
   671:                         package_id, package_arch_id
   672:                    from rhnChannelNewestPackageView
   673:                   where channel_id = channel_id_in
   674:                     and (package_name_id_in is null
   675:                          or name_id = package_name_id_in)
   676:                 );
   677:         insert into rhnChannelNewestPackageAudit (channel_id, caller)
   678:              values (channel_id_in, caller_in);
   679:         update rhnChannel
   680:            set last_modified = greatest(current_timestamp, last_modified + interval '1' second)
   681:          where id = channel_id_in;
   682:     end;
   683: 
   684:    procedure update_channel ( channel_id_in in number, invalidate_ss in number := 0,
   685:                               date_to_use in timestamp with local time zone := current_timestamp )
   686:    is
   687: 
   688:    channel_last_modified timestamp with local time zone;
   689:    last_modified_value timestamp with local time zone;
   690: 
   691:    cursor snapshots is
   692:    select  snapshot_id id
   693:    from    rhnSnapshotChannel
   694:    where   channel_id = channel_id_in;
   695: 
   696:    begin
   697: 
   698:       select last_modified
   699:       into channel_last_modified
   700:       from rhnChannel
   701:       where id = channel_id_in;
   702: 
   703:       last_modified_value := date_to_use;
   704: 
   705:       if last_modified_value <= channel_last_modified then
   706:           last_modified_value := last_modified_value + 1/86400;
   707:       end if;
   708: 
   709:       update rhnChannel set last_modified = last_modified_value
   710:       where id = channel_id_in;
   711: 
   712:       if invalidate_ss = 1 then
   713:         for snapshot in snapshots loop
   714:             update rhnSnapshot
   715:             set invalid = lookup_snapshot_invalid_reason('channel_modified')
   716:             where id = snapshot.id;
   717:         end loop;
   718:       end if;
   719: 
   720:    end update_channel;
   721: 
   722:    procedure update_channels_by_package ( package_id_in in number, date_to_use in timestamp with local time zone := current_timestamp )
   723:    is
   724: 
   725:    cursor channels is
   726:    select channel_id
   727:    from rhnChannelPackage
   728:    where package_id = package_id_in
   729:    order by channel_id;
   730: 
   731:    begin
   732:       for channel in channels loop
   733:          -- we want to invalidate the snapshot assocated with the channel when we
   734:          -- do this b/c we know we've added or removed or packages
   735:          rhn_channel.update_channel ( channel.channel_id, 1, date_to_use );
   736:       end loop;
   737:    end update_channels_by_package;
   738: 
   739: 
   740:    procedure update_channels_by_errata ( errata_id_in number, date_to_use in timestamp with local time zone := current_timestamp )
   741:    is
   742: 
   743:    cursor channels is
   744:    select channel_id
   745:    from rhnChannelErrata
   746:    where errata_id = errata_id_in
   747:    order by channel_id;
   748: 
   749:    begin
   750:       for channel in channels loop
   751:          -- we won't invalidate snapshots, b/c just changing the errata associated with
   752:          -- a channel shouldn't invalidate snapshots
   753:          rhn_channel.update_channel ( channel.channel_id, 0, date_to_use );
   754:       end loop;
   755:    end update_channels_by_errata;
   756: 
   757:    procedure update_needed_cache(channel_id_in in number)
   758:    is
   759:        -- update of needed cache ican be commited on a per server basis
   760:        -- b/c failure of update for a server means nothing for the other servers
   761:    begin
   762:       -- we intentionaly do a loop here instead of one huge select
   763:       -- b/c we want to break update into smaller transaction to unblock other sessions
   764:       -- querying rhnServerNeededCache
   765:       for server in (
   766:                 select sc.server_id as id
   767:                   from rhnServerChannel sc
   768:                  where sc.channel_id = channel_id_in
   769:                  order by id asc
   770:       ) loop
   771:          queue_server(server.id, 0); -- NOT IMMEDIATELY
   772:       end loop;
   773:    end update_needed_cache;
   774: 
   775:     procedure set_comps(channel_id_in in number, path_in in varchar2, comps_type_id_in in number, timestamp_in in varchar2)
   776:     is
   777:     begin
   778:         for row in (
   779:             select relative_filename, last_modified
   780:             from rhnChannelComps
   781:             where channel_id = channel_id_in
   782:             and comps_type_id = comps_type_id_in
   783:             ) loop
   784:             if row.relative_filename = path_in
   785:                 and row.last_modified = to_date(timestamp_in, 'YYYYMMDDHH24MISS') then
   786:                 return;
   787:             end if;
   788:         end loop;
   789:         delete from rhnChannelComps
   790:         where channel_id = channel_id_in
   791:         and comps_type_id = comps_type_id_in;
   792:         insert into rhnChannelComps (id, channel_id, relative_filename, comps_type_id, last_modified, created, modified)
   793:         values (sequence_nextval('rhn_channelcomps_id_seq'), channel_id_in, path_in, comps_type_id_in, to_date(timestamp_in, 'YYYYMMDDHH24MISS'), current_timestamp, current_timestamp);
   794:     end set_comps;
   795: 
   796: END rhn_channel;