1package App::Netdisco::DB::Result::Virtual::UndiscoveredNeighbors;
2
3use strict;
4use warnings;
5
6use utf8;
7use base 'DBIx::Class::Core';
8
9__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
10
11__PACKAGE__->table('undiscovered_neighbors');
12__PACKAGE__->result_source_instance->is_virtual(1);
13__PACKAGE__->result_source_instance->view_definition(<<'ENDSQL');
14  SELECT DISTINCT ON (p.remote_ip, p.port)
15    d.ip, d.name, d.dns,
16    p.port, p.name AS port_description,
17    p.remote_ip, p.remote_id, p.remote_type, p.remote_port,
18    dpp.remote_is_wap, dpp.remote_is_phone,
19    l.log AS comment,
20    a.log, a.finished
21
22  FROM device_port p
23
24  INNER JOIN device d USING (ip)
25  LEFT OUTER JOIN device_skip ds
26    ON ('discover' = ANY(ds.actionset) AND p.remote_ip = ds.device)
27  LEFT OUTER JOIN device_port_properties dpp USING (ip, port)
28  LEFT OUTER JOIN device_port_log l USING (ip, port)
29  LEFT OUTER JOIN admin a
30    ON (p.remote_ip = a.device AND a.action = 'discover')
31
32  WHERE
33    ds.device IS NULL
34    AND ((p.remote_ip NOT IN (SELECT alias FROM device_ip))
35         OR ((p.remote_ip IS NULL) AND p.is_uplink))
36
37  ORDER BY
38    p.remote_ip ASC,
39    p.port ASC,
40    l.creation DESC,
41    a.finished DESC
42ENDSQL
43
44__PACKAGE__->add_columns(
45  "ip",
46  { data_type => "inet", is_nullable => 0 },
47  "name",
48  { data_type => "text", is_nullable => 1 },
49  "dns",
50  { data_type => "text", is_nullable => 1 },
51  "port",
52  { data_type => "text", is_nullable => 0 },
53  "port_description",
54  { data_type => "text", is_nullable => 0 },
55  "remote_ip",
56  { data_type => "inet", is_nullable => 1 },
57  "remote_port",
58  { data_type => "text", is_nullable => 1 },
59  "remote_type",
60  { data_type => "text", is_nullable => 1 },
61  "remote_id",
62  { data_type => "text", is_nullable => 1 },
63  "remote_is_wap",
64  { data_type => "boolean", is_nullable => 1 },
65  "remote_is_phone",
66  { data_type => "boolean", is_nullable => 1 },
67  "comment",
68  { data_type => "text", is_nullable => 1 },
69  "log",
70  { data_type => "text", is_nullable => 1 },
71  "finished",
72  { data_type => "timestamp", is_nullable => 1 },
73);
74
751;
76