View: sl_failover_targets

8.8. View: sl_failover_targets

Structure of sl_failover_targets

set_id

integer

set_origin

integer

backup_id

integer

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