1package App::Netdisco::DB::Result::Virtual::DeviceLinks; 2 3use strict; 4use warnings; 5 6use base 'DBIx::Class::Core'; 7 8__PACKAGE__->table_class('DBIx::Class::ResultSource::View'); 9 10# note to future devs: 11# this query does not use the slave_of field in device_port table to group 12# ports because what we actually want is total b/w between devices on all 13# links, regardless of whether those links are in an aggregate. 14 15__PACKAGE__->table('device_links'); 16__PACKAGE__->result_source_instance->is_virtual(1); 17__PACKAGE__->result_source_instance->view_definition(<<ENDSQL 18 WITH BothWays AS 19 ( SELECT dp.ip AS left_ip, 20 ld.dns AS left_dns, 21 ld.name AS left_name, 22 array_agg(dp.port ORDER BY dp.port) AS left_port, 23 array_agg(dp.name ORDER BY dp.name) AS left_descr, 24 25 count(dpp.*) AS aggports, 26 sum(COALESCE(dpp.raw_speed, 0)) AS aggspeed, 27 28 di.ip AS right_ip, 29 rd.dns AS right_dns, 30 rd.name AS right_name, 31 array_agg(dp.remote_port ORDER BY dp.remote_port) AS right_port, 32 array_agg(dp2.name ORDER BY dp2.name) AS right_descr 33 34 FROM device_port dp 35 36 LEFT OUTER JOIN device_port_properties dpp ON ( 37 (dp.ip = dpp.ip) AND (dp.port = dpp.port) 38 AND (dp.type IS NULL 39 OR dp.type !~* '^(53|ieee8023adLag|propVirtual|l2vlan|l3ipvlan|135|136|137)\$') 40 AND (dp.is_master = 'false' 41 OR dp.slave_of IS NOT NULL) ) 42 43 INNER JOIN device ld ON dp.ip = ld.ip 44 INNER JOIN device_ip di ON dp.remote_ip = di.alias 45 INNER JOIN device rd ON di.ip = rd.ip 46 47 LEFT OUTER JOIN device_port dp2 ON (di.ip = dp2.ip 48 AND ((dp.remote_port = dp2.port) 49 OR (dp.remote_port = dp2.name) 50 OR (dp.remote_port = dp2.descr))) 51 52 WHERE dp.remote_port IS NOT NULL 53 AND dp.port !~* 'vlan' 54 AND (dp.descr IS NULL OR dp.descr !~* 'vlan') 55 56 GROUP BY left_ip, 57 left_dns, 58 left_name, 59 right_ip, 60 right_dns, 61 right_name ) 62 63 SELECT * 64 FROM BothWays b 65 WHERE NOT EXISTS 66 ( SELECT * 67 FROM BothWays b2 68 WHERE b2.right_ip = b.left_ip 69 AND b2.right_port = b.left_port 70 AND b2.left_ip < b.left_ip ) 71 ORDER BY aggspeed DESC, 1, 2 72ENDSQL 73); 74 75__PACKAGE__->add_columns( 76 'left_ip' => { 77 data_type => 'inet', 78 }, 79 'left_dns' => { 80 data_type => 'text', 81 }, 82 'left_name' => { 83 data_type => 'text', 84 }, 85 'left_port' => { 86 data_type => '[text]', 87 }, 88 'left_descr' => { 89 data_type => '[text]', 90 }, 91 'aggspeed' => { 92 data_type => 'bigint', 93 }, 94 'aggports' => { 95 data_type => 'integer', 96 }, 97 'right_ip' => { 98 data_type => 'inet', 99 }, 100 'right_dns' => { 101 data_type => 'text', 102 }, 103 'right_name' => { 104 data_type => 'text', 105 }, 106 'right_port' => { 107 data_type => '[text]', 108 }, 109 'right_descr' => { 110 data_type => '[text]', 111 }, 112); 113 114__PACKAGE__->has_many('left_vlans', 'App::Netdisco::DB::Result::DevicePortVlan', 115 sub { 116 my $args = shift; 117 return { 118 "$args->{foreign_alias}.ip" => { -ident => "$args->{self_alias}.left_ip" }, 119 "$args->{self_alias}.left_port" => { '@>' => \"ARRAY[$args->{foreign_alias}.port]" }, 120 }; 121 } 122); 123 124__PACKAGE__->has_many('right_vlans', 'App::Netdisco::DB::Result::DevicePortVlan', 125 sub { 126 my $args = shift; 127 return { 128 "$args->{foreign_alias}.ip" => { -ident => "$args->{self_alias}.right_ip" }, 129 "$args->{self_alias}.right_port" => { '@>' => \"ARRAY[$args->{foreign_alias}.port]" }, 130 }; 131 } 132); 133 1341; 135