Hello,I've modified 4 queries in order to get this work... But I don't have an
oracle set up to test my queries.Moreover, I'm not sure the queries actually
returns the legitimate results.
I insist on the fact that I'm not a DBA. I'm really not sure that the rewritten
queries are correct. Please have a look on the diffs
Here are the diff on the three modifed files, in folder
/usr/lib/perl5/vendor_perl/5.8.8/RHN/DB/DataSource/xml:diff config_queries.xml
/root/config_queries.xml.orig85,90c85,90< FROM rhnConfigFile CF< LEFT OUTER
JOIN rhnConfigRevision CR ON (CR.config_file_id = CF.id)< LEFT OUTER JOIN
rhnSnapshotConfigRevision SCR ON (SCR.config_revision_id = CR.id)< LEFT OUTER
JOIN rhnConfigFileName CFN ON (CFN.id = CF.config_file_name_id)< LEFT OUTER
JOIN rhnConfigContent CCon ON (CCon.id = CR.config_content_id)< LEFT OUTER
JOIN rhnChecksumView Csum ON (Csum.id = CCon.checksum_id)---> FROM
rhnConfigContent CCon,> rhnConfigFileName CFN,> rhnConfigFile
CF,> rhnConfigRevision CR,> rhnSnapshotConfigRevision SCR,>
rhnChecksumView Csum91a92,96> AND SCR.config_revision_id = CR.id> AND
CR.config_content_id = CCon.id (+)> AND CR.config_file_id = CF.id> AND
CF.config_file_name_id = CFN.id> AND CCon.checksum_id = Csum.id (+)
diff SystemGroup_queries.xml /root/SystemGroup_queries.xml.orig16,22c16,25<
(SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND
server_group_id = SG.id) AS USER_PERMITTED_ACCESS< FROM rhnSnapshot SN<
LEFT OUTER JOIN rhnSnapshotServerGroup SSG ON (SSG.snapshot_id = SN.id)<
LEFT OUTER JOIN rhnServerGroup SG ON (SG.id = SSG.server_group_id)< LEFT
OUTER JOIN rhnServerGroupType SGT ON (SGT.id = SG.group_type)< WHERE
SN.id = :ss_id< AND SN.server_id = :sid---> (SELECT 1 FROM
rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id)
AS USER_PERMITTED_ACCESS> FROM rhnServerGroupType SGT,>
rhnServerGroup SG,> rhnSnapshotServerGroup SSG,> rhnSnapshot
SN> WHERE SN.id = :ss_id> AND SN.server_id = :sid> AND SN.id =
SSG.snapshot_id> AND SSG.server_group_id = SG.id> AND SG.group_type =
SGT.id (+)90,101c93,107< SELECT VSGM.group_id AS ID,<
SG.name AS GROUP_NAME,< SGT.label AS GROUP_TYPE_LABEL,<
(SELECT 1 FROM rhnUserServerGroupPerms WHERE user_id = :user_id AND
server_group_id = SG.id) AS USER_PERMITTED_ACCESS< FROM rhnServerGroup
SG< LEFT OUTER JOIN rhnVisServerGroupMembership VSGM ON (VSGM.group_id =
SG.id)< LEFT OUTER JOIN rhnServerGroupType SGT ON (SGT.id =
CAST(VSGM.group_type as numeric) )< LEFT OUTER JOIN
rhnUserServerGroupPerms USGP ON (USGP.server_group_id = SG.id)< WHERE
VSGM.org_id = :org_id< AND VSGM.server_id = :sid<
AND USGP.user_id = :user_id< ORDER BY UPPER(SG.name)---> SELECT
VSGM.group_id AS ID,> SG.name AS GROUP_NAME,>
SGT.label AS GROUP_TYPE_LABEL,> (SELECT 1 FROM
rhnUserServerGroupPerms WHERE user_id = :user_id AND server_group_id = SG.id)
AS USER_PERMITTED_ACCESS> FROM rhnServerGroupType SGT,>
rhnServerGroup SG,> rhnVisServerGroupMembership VSGM,>
rhnUserServerGroupPerms USGP> WHERE VSGM.org_id = :org_id>
AND VSGM.server_id = :sid> AND VSGM.group_id = SG.id>
AND VSGM.group_type = SGT.id (+)> AND USGP.user_id =
:user_id> AND USGP.server_group_id = SG.id> ORDER BY
UPPER(SG.name)
diff Package_queries.xml /root/Package_queries.xml.orig30,52c30,75<
PE.id AS EVR_ID,< PN.name AS NAME,< PE.version
AS VERSION,< PE.release AS RELEASE,< PE.epoch AS
EPOCH,< PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,<
UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE<
FROM rhnPackageName PN< LEFT OUTER JOIN rhnPackageNEVRA
PNEVRA ON (PNEVRA.name_id = PN.id)< LEFT OUTER JOIN rhnPackageEVR PE
ON (PE.id = PNEVRA.evr_id)< LEFT OUTER JOIN rhnSnapshotPackage SP ON
(SP.nevra_id = PNEVRA.id)< LEFT OUTER JOIN rhnSnapshot S ON (S.id =
SP.snapshot_id)< LEFT OUTER JOIN rhnPackage P ON (P.id = PN.id)<
LEFT OUTER JOIN rhnChannelPackage CP ON (CP.package_id = P.id)<
LEFT OUTER JOIN rhnSnapshotChannel SC ON (SC.channel_id = CP.channel_id)<
WHERE S.id = :ss_id< AND S.server_id = :sid< AND NOT
EXISTS (SELECT 1 FROM rhnPackageSyncBlacklist PSB WHERE<
PSB.package_name_id = PN.id AND org_id is NULL OR org_id = :org_id)<
AND SC.snapshot_id = :ss_id< AND NOT EXISTS (SELECT 1 FROM
rhnServerPackage SEP WHERE< SEP.server_id = :sid
AND SEP.name_id = PN.id AND SEP.evr_id = PE.id)< ORDER BY 8---> PE.id AS
EVR_ID,> PN.name AS NAME,> PE.version AS VERSION,>
PE.release AS RELEASE,> PE.epoch AS EPOCH,> PN.name || '-' ||
evr_t_as_vre_simple(PE.evr) AS NVRE,> UPPER(PN.name || '-' ||
evr_t_as_vre_simple(PE.evr)) AS U_NVRE> FROM rhnPackageName PN,>
rhnPackageEVR PE,> rhnPackageNEVRA PNEVRA,> rhnSnapshotPackage
SP,> rhnSnapshot S> WHERE S.id = :ss_id> AND S.server_id = :sid>
AND S.id = SP.snapshot_id> AND SP.nevra_id = PNEVRA.id> AND
PNEVRA.name_id = PN.id> AND NOT EXISTS (SELECT 1 FROM
rhnPackageSyncBlacklist PSB WHERE> PSB.package_name_id =
PN.id AND org_id is NULL OR org_id = :org_id)> AND PNEVRA.evr_id = PE.id>
AND NOT EXISTS (> SELECT 1 FROM rhnServerPackage SP> WHERE SP.server_id =
:sid> AND SP.name_id = PN.id> AND SP.evr_id = PE.id> )> MINUS> SELECT
PN.id AS NAME_ID,> PE.id AS EVR_ID,> PN.name AS NAME,>
PE.version AS VERSION,> PE.release AS RELEASE,> PE.epoch AS
EPOCH,> PN.name || '-' || evr_t_as_vre_simple(PE.evr) AS NVRE,>
UPPER(PN.name || '-' || evr_t_as_vre_simple(PE.evr)) AS U_NVRE> FROM
rhnPackageName PN,> rhnPackageEVR PE,> rhnPackage P,>
rhnChannelPackage CP,> rhnSnapshotChannel SC> WHERE SC.snapshot_id =
:ss_id> AND SC.channel_id = CP.channel_id> AND CP.package_id = P.id>
AND P.name_id = PN.id> AND P.evr_id = PE.id> ORDER BY 8
> Date: Fri, 22 Jul 2011 19:44:02 +0200
> From: [email protected]
> To: [email protected]
> Subject: Re: [Spacewalk-list] Error 500 on snapshots pages
>
> On 07/22/2011 08:15 AM, Pierre Casenove wrote:
> > Here is the SQL statement executed:
> > ERROR: syntax error at or near "MINUS" at character 869
>
> https://fedorahosted.org/spacewalk/wiki/PostgreSQLPortingGuide#MINUSkeyword
>
> Patches are welcome.
>
> --
> Miroslav Suchý
> Red Hat Satellite Engineering
>
> _______________________________________________
> Spacewalk-list mailing list
> [email protected]
> https://www.redhat.com/mailman/listinfo/spacewalk-list
_______________________________________________
Spacewalk-list mailing list
[email protected]
https://www.redhat.com/mailman/listinfo/spacewalk-list
