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