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