1package App::Netdisco::DB::Result::Virtual::NodesDiscovered;
2
3use strict;
4use warnings;
5
6use base 'DBIx::Class::Core';
7
8__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
9
10__PACKAGE__->table('nodes_discovered');
11__PACKAGE__->result_source_instance->is_virtual(1);
12__PACKAGE__->result_source_instance->view_definition(<<ENDSQL
13SELECT d.ip,
14       d.dns,
15       d.name,
16       p.port,
17       p.remote_ip,
18       p.remote_port,
19       p.remote_type,
20       p.remote_id
21FROM device_port p,
22     device d
23WHERE d.ip = p.ip
24  AND NOT EXISTS
25    (SELECT 1
26     FROM device_port q
27     WHERE q.ip = p.remote_ip
28       AND q.port = p.remote_port)
29  AND NOT EXISTS
30    (SELECT 1
31     FROM device_ip a,
32          device_port q
33     WHERE a.alias = p.remote_ip
34       AND q.ip = a.ip
35       AND q.port = p.remote_port)
36  AND (p.remote_id IS NOT NULL OR p.remote_type IS NOT NULL)
37  ORDER BY d.name, p.port
38ENDSQL
39);
40
41__PACKAGE__->add_columns(
42  'ip' => {
43    data_type => 'inet',
44  },
45  'dns' => {
46    data_type => 'text',
47  },
48  'name' => {
49    data_type => 'text',
50  },
51  'port' => {
52    data_type => 'text',
53  },
54  'remote_ip' => {
55    data_type => 'inet',
56  },
57  'remote_port' => {
58    data_type => 'text',
59  },
60  'remote_type' => {
61    data_type => 'text',
62  },
63  'remote_id' => {
64    data_type => 'text',
65  },
66);
67
681;
69