[Spacewalk-list] Ubuntu 18.04 package management in Spacewalk 2.8

[Paul-Andre Panon]

On Tuesday, December 18, 2018 2:59 PM, I wrote that I thought I had a
solution to the phantom packages with Ubuntu that I hadn't tested yet.

OK, there were a few typos and syntax errors, but this corrected stored
proc appears to work well.

    SET search_path = rpm, pg_catalog;
    create or replace FUNCTION rpmstrcmp (string1 IN VARCHAR, string2 IN
VARCHAR)
    RETURNS INTEGER as $$
    declare
        str1 VARCHAR := string1;
        str2 VARCHAR := string2;
        digits VARCHAR(10) := '0123456789';
        lc_alpha VARCHAR(27) := 'abcdefghijklmnopqrstuvwxyz';
        uc_alpha VARCHAR(27) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        alpha VARCHAR(54) := lc_alpha || uc_alpha;
        one VARCHAR;
        two VARCHAR;
        sep1 VARCHAR;
        sep2 VARCHAR;
        isnum BOOLEAN;
    BEGIN
        if str1 is NULL or str2 is NULL
        then
            RAISE EXCEPTION 'VALUE_ERROR.';
        end if;

        if str1 = str2
        then
            return 0;
        end if;
        one := str1;
        two := str2;

        <<segment_loop>>
        while one <> '' and two <> ''
        loop
            declare
                segm1 VARCHAR;
                segm2 VARCHAR;
            begin
                sep1 := '';
                sep2 := '';
                --DBMS_OUTPUT.PUT_LINE('Params: ' || one || ',' || two);
                -- Pull out all separating non-alphanum characters
                while one <> '' and not rpm.isalphanum(one)
                loop
                    sep1 := sep1 || substr(one, 1, 1);
                    one := substr(one, 2);
                end loop;
                while two <> '' and not rpm.isalphanum(two)
                loop
                    sep2 := sep2 || substr(two, 1, 1);
                    two := substr(two, 2);
                end loop;

                str1 := one;
                str2 := two;
                if str1 <> '' and rpm.isdigit(str1)
                then
                    str1 := ltrim(str1, digits);
                    str2 := ltrim(str2, digits);
                    isnum := true;
                else
                    str1 := ltrim(str1, alpha);
                    str2 := ltrim(str2, alpha);
                    isnum := false;
                end if;
                if str1 <> ''
                then segm1 := substr(one, 1, length(one) - length(str1));
                else segm1 := one;
                end if;

                if str2 <> ''
                then segm2 := substr(two, 1, length(two) - length(str2));
                else segm2 := two;
                end if;

                -- if one of the separators is for a point subversion
indicator and the other isn't, then the point subversion is considered
more recent.
                if isnum and sep1 <> '' and sep2 <> ''
                then
                    if sep1 = '.' and sep2 <> '.'
                    then
                        return 1;
					elsif sep2 = '.' and sep1 <> '.'
                    then
                        return -1;
					end if;
                end if;

                if segm1 = '' then return -1; end if; /* arbitrary */
                if segm2 = '' then
                    if isnum then
                        return 1;
                    else
                        return -1;
                    end if;
                end if;
                if isnum
                then
                    segm1 := ltrim(segm1, '0');
                    segm2 := ltrim(segm2, '0');

                    if segm1 = '' and segm2 <> ''
                    then
                        return -1;
                    end if;
                    if segm1 <> '' and segm2 = ''
                    then
                        return 1;
                    end if;
                    if length(segm1) > length(segm2) then return 1; end
if;
                    if length(segm2) > length(segm1) then return -1; end
if;
                end if;
                if segm1 < segm2 then return -1; end if;
                if segm1 > segm2 then return 1; end if;
                one := str1;
                two := str2;
            end;
        end loop segment_loop;

        if one = '' and two = '' then return 0; end if;
        if one = '' then return -1; end if;
        return 1;
    END ;
$$ language 'plpgsql';

ALTER FUNCTION rpm.rpmstrcmp(string1 character varying, string2 character
varying) OWNER TO spaceuser;


I incorporated that into a duplicate of our postgres database. Then I ran
the following query on both the unpatched and modified DBs
select sp.server_id, count(*)
           FROM (SELECT sp_sp.server_id, sp_sp.name_id,
        sp_sp.package_arch_id, max(sp_pe.evr) AS max_evr
                   FROM rhnServerPackage sp_sp
                   join rhnPackageEvr sp_pe ON sp_pe.id = sp_sp.evr_id
                  GROUP BY sp_sp.server_id, sp_sp.name_id,
sp_sp.package_arch_id) sp
           join rhnPackage p ON p.name_id = sp.name_id
           join rhnPackageEvr pe ON pe.id = p.evr_id AND sp.max_evr <
pe.evr
           join rhnPackageUpgradeArchCompat puac
            ON puac.package_arch_id = sp.package_arch_id
        AND puac.package_upgrade_arch_id = p.package_arch_id
           join rhnServerChannel sc ON sc.server_id = sp.server_id
           join rhnChannelPackage cp ON cp.package_id = p.id
            AND cp.channel_id = sc.channel_id
group by sp.server_id
order by sp.server_id;

Wherever there were differences in package counts, I looked at the
individual package evrs with
select sp.server_id, p.id, sp.max_evr, pe.evr
           FROM (SELECT sp_sp.server_id, sp_sp.name_id,
        sp_sp.package_arch_id, max(sp_pe.evr) AS max_evr
                   FROM rhnServerPackage sp_sp
                   join rhnPackageEvr sp_pe ON sp_pe.id = sp_sp.evr_id
                  GROUP BY sp_sp.server_id, sp_sp.name_id,
sp_sp.package_arch_id) sp
           join rhnPackage p ON p.name_id = sp.name_id
           join rhnPackageEvr pe ON pe.id = p.evr_id AND sp.max_evr <
pe.evr
           join rhnPackageUpgradeArchCompat puac
            ON puac.package_arch_id = sp.package_arch_id
        AND puac.package_upgrade_arch_id = p.package_arch_id
           join rhnServerChannel sc ON sc.server_id = sp.server_id
           join rhnChannelPackage cp ON cp.package_id = p.id
            AND cp.channel_id = sc.channel_id
where sp.server_id IN (<server id list with discrepancies>) order by
sp.server_id, p.id;

The updated SP corrected the erroneous false positives, and also caught
some packages which needed to be updated but weren't listed. Can somebody
put this in a PR?

Thanks,

Paul-Andre