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)
                 )
           )
     )
);