[Spacewalk-list] [Solaris support] installed patches not refreshed

[Michael Mraka]

Pierre Casenove wrote:
% Hello,
% I can't find where the issue is. Could you please explain how I can debug
% the sql query, to see why it doesn't find that the patch is indeed already
% installed. As I don't understand the query, I don't know which table I
% should check to try to find the bug.

Hello Pierre,

I've spent some time investigating the query and ... simply it's wrong :).

The fixed query is in the attachment. It isn't commited into master yet,
I'd like to hear from you whether it works for you or not.

You can download test packages at
 RHEL6: http://koji.spacewalkproject.org/koji/taskinfo?taskID=117229
 RHEL5: http://koji.spacewalkproject.org/koji/taskinfo?taskID=117230

Regards,

--
Michael Mráka
Satellite Engineering, Red Hat




-------------- next part --------------
diff --git a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
index c786505..cd3a82d 100644
--- a/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
+++ b/java/code/src/com/redhat/rhn/common/db/datasource/xml/Package_queries.xml
@@ -1046,40 +1046,47 @@ ORDER BY UPPER(PN.name), UPPER(evr_t_as_vre_simple(PE.evr))
 
 <mode name="system_available_patch_list" class="com.redhat.rhn.frontend.dto.PackageListItem">
   <query params="sid">
-SELECT  pn.name AS NAME,
-        pn.name || '-' || evr_t_as_vre_simple(full_list.evr) AS NVRE,
-        pn.id || '|' || lookup_evr((full_list.evr).epoch, (full_list.evr).version, (full_list.evr).release) AS ID_COMBO,
-        full_list.id,
-        full_list.PATCH_TYPE
-  FROM  (
-        SELECT  p.name_id name_id, max(pe.evr) evr, p.id, pt.name AS PATCH_TYPE
-          FROM  rhnPackageEVR PE, rhnPackage P,
-                 rhnChannelPackage CP, rhnServerChannel SC, rhnSolarisPatch SSP, rhnSolarisPatchType PT
-         WHERE  sc.server_id = :sid
-           AND  sc.channel_id = cp.channel_id
-           AND  cp.package_id = p.id
-           AND  ssp.package_id = p.id
-           AND  p.evr_id = pe.id
-           AND  pt.id = ssp.patch_type
-      GROUP BY  p.name_id, p.id, pt.name
-      ) full_list,
-      rhnPackageName pn
- WHERE full_list.name_id = pn.id
-   AND EXISTS (SELECT 1
-            FROM rhnPackageNEVRA PNEVRA,
-                 rhnSolarisPatchPackages SPP,
-                 rhnServerPackage SP
-          WHERE SP.server_id = :sid
-            AND SPP.patch_id = full_list.id
-            AND PNEVRA.id = SPP.package_nevra_id
-            AND PNEVRA.name_id = SP.name_id
-            AND ((PNEVRA.package_arch_id IS NULL AND SP.package_arch_id IS NULL) OR PNEVRA.package_arch_id = SP.package_arch_id)
-            AND NOT EXISTS (SELECT 1
-                            FROM rhnSolarisPatchedPackage SPdP
-                            WHERE SPdP.server_id = :sid
-                            AND SPdP.patch_id = full_list.id
-                            AND SPdP.package_nevra_id = PNEVRA.id))
-ORDER BY  UPPER(name || '-' || evr_t_as_vre_simple(full_list.evr))
+select pn.name as name,
+       pkg.id as id,
+       pn.name || '-' || evr_t_as_vre_simple(full_list.evr) as nvre,
+       pn.id || '|' || lookup_evr((full_list.evr).epoch, (full_list.evr).version, (full_list.evr).release) as id_combo,
+        pt.name as patch_type
+  from (
+        select p.name_id name_id,
+               max(pe.evr) evr,
+               pa.id as arch_id
+          from rhnpackageevr pe,
+               rhnpackage p,
+               rhnchannelnewestpackage cnp,
+               rhnpackagearch pa,
+               rhnserverchannel sc
+         where sc.server_id = :sid
+           and sc.channel_id = cnp.channel_id
+           and cnp.package_id = p.id
+           and p.evr_id = pe.id
+         group by p.name_id, pa.id
+       ) full_list
+  join rhnpackage pkg
+    on full_list.name_id = pkg.name_id and full_list.arch_id = pkg.package_arch_id
+  join rhnpackagename pn
+    on pkg.name_id = pn.id
+  join rhnpackageevr pevr
+    on pkg.evr_id = pevr.id and full_list.evr = pevr.evr
+  join rhnchannelpackage cp
+    on cp.package_id = pkg.id
+  join rhnserverchannel sc
+    on sc.channel_id = cp.channel_id
+  join rhnsolarispatch ssp
+    on ssp.package_id = pkg.id
+  join rhnsolarispatchtype pt
+    on pt.id = ssp.patch_type
+ where sc.server_id = :sid
+   and not exists (select 1
+                     from rhnserverpackage sp
+                    where sp.server_id = :sid
+                      and sp.name_id = full_list.name_id
+                      and (sp.package_arch_id = full_list.arch_id or sp.package_arch_id is null))
+order by  upper(name || '-' || evr_t_as_vre_simple(full_list.evr))
   </query>
   <elaborator params="sid" multiple="t">
 SELECT DISTINCT PN.name AS NAME,