1package App::Netdisco::DB::Result::Virtual::DevicePortSpeed; 2 3use strict; 4use warnings; 5 6use base 'DBIx::Class::Core'; 7 8__PACKAGE__->table_class('DBIx::Class::ResultSource::View'); 9 10__PACKAGE__->table('device_port_speed'); 11__PACKAGE__->result_source_instance->is_virtual(1); 12__PACKAGE__->result_source_instance->view_definition(<<ENDSQL 13 SELECT ip, 14 GREATEST(1, sum( COALESCE(dpp.raw_speed,1) )) as total 15 FROM device_port 16 LEFT OUTER JOIN device_port_properties dpp USING (ip, port) 17 WHERE port !~* 'vlan' 18 AND (descr IS NULL OR descr !~* 'vlan') 19 AND (type IS NULL OR type !~* '^(53|ieee8023adLag|propVirtual|l2vlan|l3ipvlan|135|136|137)\$') 20 AND (is_master = 'false' OR slave_of IS NOT NULL) 21 GROUP BY ip 22 ORDER BY total DESC, ip ASC 23ENDSQL 24); 25 26__PACKAGE__->add_columns( 27 'total' => { 28 data_type => 'integer', 29 }, 30); 31 32__PACKAGE__->belongs_to('device', 'App::Netdisco::DB::Result::Device', 33 { 'foreign.ip' => 'self.ip' }); 34 351; 36