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