1package App::Netdisco::DB::Result::Virtual::PortVLANMismatch;
2
3use strict;
4use warnings;
5
6use base 'DBIx::Class::Core';
7__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
8
9__PACKAGE__->table('port_vlan_mismatch');
10__PACKAGE__->result_source_instance->is_virtual(1);
11__PACKAGE__->result_source_instance->view_definition(<<'ENDSQL');
12  WITH all_vlans AS
13    (SELECT ip, port,
14            array_to_string(array_agg( CASE WHEN native THEN 'n:' || vlan::text
15                                        ELSE vlan::text END
16                       ORDER BY vlan ASC ), ', ') AS vlist
17     FROM device_port_vlan GROUP BY ip, port)
18
19  SELECT CASE WHEN length(ld.dns) > 0 THEN ld.dns ELSE host(ld.ip) END AS left_device,
20         lp.port AS left_port,
21         (SELECT vlist FROM all_vlans WHERE ip=lp.ip AND port=lp.port) AS left_vlans,
22         CASE WHEN length(rd.dns) > 0 THEN rd.dns ELSE host(rd.ip) END AS right_device,
23         rp.port AS right_port,
24         (SELECT vlist FROM all_vlans WHERE ip=rp.ip AND port=rp.port) AS right_vlans
25  FROM device ld
26       JOIN device_port lp USING (ip)
27       JOIN device_port rp ON lp.remote_ip=rp.ip AND lp.remote_port=rp.port
28       JOIN device rd ON rp.ip=rd.ip
29  WHERE ld.ip < rd.ip AND
30        (SELECT vlist FROM all_vlans WHERE ip=lp.ip AND port=lp.port)
31        !=
32        (SELECT vlist FROM all_vlans WHERE ip=rp.ip AND port=rp.port)
33  ORDER BY left_device, left_port
34ENDSQL
35
36__PACKAGE__->add_columns(
37  'left_device'  => { data_type => 'text' },
38  'left_port'    => { data_type => 'text' },
39  'left_vlans'   => { data_type => 'text' },
40
41  'right_device' => { data_type => 'text' },
42  'right_port'   => { data_type => 'text' },
43  'right_vlans'  => { data_type => 'text' },
44);
45
461;
47