8.8. View: sl_failover_targets

Figure 8-1. Definition of view sl_failover_targets

SELECT set1.set_id
,
    set1.set_origin
,
    sub1.sub_receiver AS backup_id
   
FROM sl_subscribe sub1
,
    sl_set set1
  
WHERE (
     (sub1.sub_set = set1.set_id)
   AND (sub1.sub_forward = true)
   AND (NOT 
           (sub1.sub_receiver IN 
                 (
                  SELECT p1.pa_client
           
                    FROM (sl_path p1
             
                     LEFT JOIN sl_path p2 
                            ON (
                                   (
                                         (p2.pa_client = p1.pa_client)
                                       AND (p2.pa_server = sub1.sub_receiver)
                                   )
                             )
                       )
          
                   WHERE (
                             (p2.pa_client IS NULL)
                           AND (p1.pa_server = set1.set_origin)
                           AND (p1.pa_client <> sub1.sub_receiver)
                       )
                 )
           )
     )
   AND (sub1.sub_provider = set1.set_origin)
   AND (NOT 
           (sub1.sub_receiver IN 
                 (
                  SELECT direct_recv.sub_receiver
           
                    FROM (
                             (
                                   (
                                    SELECT subs2.sub_receiver
                   
                                      FROM sl_subscribe subs2
                  
                                     WHERE (
                                               (subs2.sub_provider = set1.set_origin)
                                             AND (subs2.sub_set = set1.set_id)
                                         )
                                   ) direct_recv
             
                                JOIN (
                                    SELECT set2.set_id
                   
                                      FROM sl_set set2
                  
                                     WHERE (
                                               (set2.set_origin = set1.set_origin)
                                             AND (set2.set_id <> sub1.sub_set)
                                         )
                                   ) othersets 
                                  ON (true)
                             )
             
                     LEFT JOIN sl_subscribe subs3 
                            ON (
                                   (
                                         (subs3.sub_set = othersets.set_id)
                                       AND (subs3.sub_forward = true)
                                       AND (subs3.sub_provider = set1.set_origin)
                                       AND (direct_recv.sub_receiver = subs3.sub_receiver)
                                   )
                             )
                       )
          
                   WHERE (subs3.sub_receiver IS NULL)
                 )
           )
     )
);