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