Data Infrastructure Management Software Discussions

Highlighted

WFA sub-select in update query for reservations

I am still working on reservations for export rules. My issue is that rules get different RuleIndex value if new rules are inserted "on top".

 

Example Policy:

             Policy          Rule    Access   Client                RO
Vserver      Name            Index   Protocol Match                 Rule
------------ --------------- ------  -------- --------------------- ---------
test
             esx             1       nfs      10.0.0.1              sys
test
             esx             2       nfs      10.0.0.2              sys
test
             esx             3       nfs      10.0.0.3              sys

 

I add a new client at Index 1, other rules get "pushed down".

 

             Policy          Rule    Access   Client                RO
Vserver      Name            Index   Protocol Match                 Rule
------------ --------------- ------  -------- --------------------- ---------
test
             esx             1       nfs      10.0.0.4              sys
test
             esx             2       nfs      10.0.0.1              sys
test
             esx             3       nfs      10.0.0.2              sys
test
             esx             4       nfs      10.0.0.3              sys

 

I wanted to replicate this behaviour with my reservation script but failed. It seems the reservation doesn't support a sub-select in the update statement.

 

Here is my SQL Statement for the reservation:

UPDATE
    cm_storage.export_rule rule      
JOIN
    cm_storage.export_policy policy
        ON policy.id = rule.policy_id
SET
    rule.rule_index = rule.rule_index + 1       
WHERE
    rule.rule_index >= '${RuleIndex}'
    AND policy.name = '${PolicyName}'
    AND  (
        SELECT
            cm_storage.export_rule.rule_index
        FROM
            cm_storage.export_rule          
        JOIN
            cm_storage.export_policy         
                ON cm_storage.export_rule.policy_id = cm_storage.export_policy.id
        JOIN
            cm_storage.vserver
                ON cm_storage.export_policy.vserver_id = cm_storage.vserver.id
        WHERE
            cm_storage.export_policy.name = '${VolumeName}_${QtreeName}'
            AND  cm_storage.vserver.name = '${VserverName}'     
            AND  cm_storage.export_rule.rule_index = '${RuleIndex}'
    ) is not null;

 

Has anyone an idea how to solve that?

Check out the KB!
Knowledge Base
All Community Forums