On 04/13/2012 02:30 PM, Tomas Lestach wrote:
A separate query for a single system would definitelly be more efficient.
Looking at what you pointed out in IRC: what happens if the errata is
applied on the client side. So I modified them to look at the package
install time instead:
For the system list:
SELECT DISTINCT S.id, S.NAME,
(SELECT 1
FROM rhnServerFeaturesView SFV
WHERE SFV.server_id = S.id
AND SFV.label = 'ftr_system_grouping') AS selectable
FROM rhnServer S,
rhnErrata E,
rhnServerInfo SI,
rhnServerPackage SP,
rhnPackage P,
rhnActionErrataUpdate EA,
rhnServerAction SA,
rhnErrataPackage EP,
rhnPackageName PN
WHERE S.org_id = :org_id
AND EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE
USP.user_id=:user_id AND USP.server_id = S.id)
AND SI.server_id = S.id
AND SP.server_id = S.id
AND P.evr_id = SP.evr_id
AND P.name_id = SP.name_id
AND EA.errata_id = E.id
AND SA.action_id = EA.action_id AND SA.server_id = S.id
AND EP.errata_id = E.id AND EP.package_id = P.id
AND (to_timestamp(S.last_boot) < SP.installtime)
AND E.id IN (SELECT EK.errata_id FROM rhnErrataKeyword EK WHERE
EK.keyword = :keyword)
AND PN.id = P.name_id
For the boolean function:
SELECT EXISTS (
SELECT 1
FROM rhnServer S,
rhnErrata E,
rhnServerInfo SI,
rhnServerPackage SP,
rhnPackage P,
rhnActionErrataUpdate EA,
rhnServerAction SA,
rhnErrataPackage EP,
rhnPackageName PN
WHERE S.org_id = :org_id
AND S.id = :sid
AND EXISTS (SELECT 1 FROM rhnUserServerPerms USP WHERE
USP.user_id=:user_id AND USP.server_id = S.id)
AND SI.server_id = S.id
AND SP.server_id = S.id
AND P.evr_id = SP.evr_id
AND P.name_id = SP.name_id
AND EA.errata_id = E.id
AND SA.action_id = EA.action_id AND SA.server_id = S.id
AND EP.errata_id = E.id AND EP.package_id = P.id
AND (to_timestamp(S.last_boot) < SP.installtime)
AND E.id IN (SELECT EK.errata_id FROM rhnErrataKeyword EK WHERE
EK.keyword = :keyword)
AND PN.id = P.name_id
)
https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide Tomas
Thanks, I will look into it.
Duncan
_______________________________________________
Spacewalk-devel mailing list
Spacewalk-devel@redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-devel
