1use utf8; 2package App::Netdisco::DB::Result::DevicePort; 3 4 5use strict; 6use warnings; 7 8use NetAddr::MAC; 9 10use MIME::Base64 'encode_base64url'; 11 12use base 'App::Netdisco::DB::Result'; 13__PACKAGE__->table("device_port"); 14__PACKAGE__->add_columns( 15 "ip", 16 { data_type => "inet", is_nullable => 0 }, 17 "port", 18 { data_type => "text", is_nullable => 0 }, 19 "creation", 20 { 21 data_type => "timestamp", 22 default_value => \"current_timestamp", 23 is_nullable => 1, 24 original => { default_value => \"now()" }, 25 }, 26 "descr", 27 { data_type => "text", is_nullable => 1 }, 28 "up", 29 { data_type => "text", is_nullable => 1 }, 30 "up_admin", 31 { data_type => "text", is_nullable => 1 }, 32 "type", 33 { data_type => "text", is_nullable => 1 }, 34 "duplex", 35 { data_type => "text", is_nullable => 1 }, 36 "duplex_admin", 37 { data_type => "text", is_nullable => 1 }, 38 "speed", 39 { data_type => "text", is_nullable => 1 }, 40 "speed_admin", 41 { data_type => "text", is_nullable => 1 }, 42 "name", 43 { data_type => "text", is_nullable => 1 }, 44 "mac", 45 { data_type => "macaddr", is_nullable => 1 }, 46 "mtu", 47 { data_type => "integer", is_nullable => 1 }, 48 "stp", 49 { data_type => "text", is_nullable => 1 }, 50 "remote_ip", 51 { data_type => "inet", is_nullable => 1 }, 52 "remote_port", 53 { data_type => "text", is_nullable => 1 }, 54 "remote_type", 55 { data_type => "text", is_nullable => 1 }, 56 "remote_id", 57 { data_type => "text", is_nullable => 1 }, 58 "is_master", 59 { data_type => "boolean", is_nullable => 0, default_value => \"false" }, 60 "slave_of", 61 { data_type => "text", is_nullable => 1 }, 62 "manual_topo", 63 { data_type => "boolean", is_nullable => 0, default_value => \"false" }, 64 "is_uplink", 65 { data_type => "boolean", is_nullable => 1 }, 66 "vlan", 67 { data_type => "text", is_nullable => 1 }, 68 "pvid", 69 { data_type => "integer", is_nullable => 1 }, 70 "lastchange", 71 { data_type => "bigint", is_nullable => 1 }, 72); 73__PACKAGE__->set_primary_key("port", "ip"); 74 75 76 77=head1 RELATIONSHIPS 78 79=head2 device 80 81Returns the Device table entry to which the given Port is related. 82 83=cut 84 85__PACKAGE__->belongs_to( device => 'App::Netdisco::DB::Result::Device', 'ip' ); 86 87=head2 port_vlans 88 89Returns the set of C<device_port_vlan> entries associated with this Port. 90These will be both tagged and untagged. Use this relation in search conditions. 91 92=cut 93 94__PACKAGE__->has_many( port_vlans => 'App::Netdisco::DB::Result::DevicePortVlan', 95 { 'foreign.ip' => 'self.ip', 'foreign.port' => 'self.port' } ); 96 97=head2 nodes / active_nodes / nodes_with_age / active_nodes_with_age 98 99Returns the set of Nodes whose MAC addresses are associated with this Device 100Port. 101 102The C<active> variants return only the subset of nodes currently in the switch 103MAC address table, that is the active ones. 104 105The C<with_age> variants add an additional column C<time_last_age>, a 106preformatted value for the Node's C<time_last> field, which reads as "X 107days/weeks/months/years". 108 109=cut 110 111__PACKAGE__->has_many( nodes => 'App::Netdisco::DB::Result::Node', 112 { 113 'foreign.switch' => 'self.ip', 114 'foreign.port' => 'self.port', 115 }, 116 { join_type => 'LEFT' }, 117); 118 119__PACKAGE__->has_many( nodes_with_age => 'App::Netdisco::DB::Result::Virtual::NodeWithAge', 120 { 121 'foreign.switch' => 'self.ip', 122 'foreign.port' => 'self.port', 123 }, 124 { join_type => 'LEFT', 125 cascade_copy => 0, cascade_update => 0, cascade_delete => 0 }, 126); 127 128__PACKAGE__->has_many( active_nodes => 'App::Netdisco::DB::Result::Virtual::ActiveNode', 129 { 130 'foreign.switch' => 'self.ip', 131 'foreign.port' => 'self.port', 132 }, 133 { join_type => 'LEFT', 134 cascade_copy => 0, cascade_update => 0, cascade_delete => 0 }, 135); 136 137__PACKAGE__->has_many( active_nodes_with_age => 'App::Netdisco::DB::Result::Virtual::ActiveNodeWithAge', 138 { 139 'foreign.switch' => 'self.ip', 140 'foreign.port' => 'self.port', 141 }, 142 { join_type => 'LEFT', 143 cascade_copy => 0, cascade_update => 0, cascade_delete => 0 }, 144); 145 146=head2 logs 147 148Returns the set of C<device_port_log> entries associated with this Port. 149 150=cut 151 152__PACKAGE__->has_many( logs => 'App::Netdisco::DB::Result::DevicePortLog', 153 { 'foreign.ip' => 'self.ip', 'foreign.port' => 'self.port' }, 154); 155 156=head2 power 157 158Returns a row from the C<device_port_power> table if one refers to this 159device port. 160 161=cut 162 163__PACKAGE__->might_have( power => 'App::Netdisco::DB::Result::DevicePortPower', { 164 'foreign.ip' => 'self.ip', 'foreign.port' => 'self.port', 165}); 166 167=head2 properties 168 169Returns a row from the C<device_port_properties> table if one refers to this 170device port. 171 172=cut 173 174__PACKAGE__->might_have( properties => 'App::Netdisco::DB::Result::DevicePortProperties', { 175 'foreign.ip' => 'self.ip', 'foreign.port' => 'self.port', 176}); 177 178=head2 ssid 179 180Returns a row from the C<device_port_ssid> table if one refers to this 181device port. 182 183=cut 184 185__PACKAGE__->might_have( 186 ssid => 'App::Netdisco::DB::Result::DevicePortSsid', 187 { 'foreign.ip' => 'self.ip', 188 'foreign.port' => 'self.port', 189 } 190); 191 192=head2 wireless 193 194Returns a row from the C<device_port_wireless> table if one refers to this 195device port. 196 197=cut 198 199__PACKAGE__->might_have( 200 wireless => 'App::Netdisco::DB::Result::DevicePortWireless', 201 { 'foreign.ip' => 'self.ip', 202 'foreign.port' => 'self.port', 203 } 204); 205 206=head2 agg_master 207 208Returns another row from the C<device_port> table if this port is slave 209to another in a link aggregate. 210 211=cut 212 213__PACKAGE__->belongs_to( 214 agg_master => 'App::Netdisco::DB::Result::DevicePort', { 215 'foreign.ip' => 'self.ip', 216 'foreign.port' => 'self.slave_of', 217 }, { 218 join_type => 'LEFT', 219 } 220); 221 222=head2 neighbor_alias 223 224When a device port has an attached neighbor device, this relationship will 225return the IP address of the neighbor. See the C<neighbor> helper method if 226what you really want is to retrieve the Device entry for that neighbor. 227 228The JOIN is of type "LEFT" in case the neighbor device is known but has not 229been fully discovered by Netdisco and so does not exist itself in the 230database. 231 232=cut 233 234__PACKAGE__->belongs_to( neighbor_alias => 'App::Netdisco::DB::Result::DeviceIp', 235 sub { 236 my $args = shift; 237 return { 238 "$args->{foreign_alias}.alias" => { '=' => 239 $args->{self_resultsource}->schema->resultset('DeviceIp') 240 ->search({alias => { -ident => "$args->{self_alias}.remote_ip"}}, 241 {rows => 1, columns => 'ip', alias => 'devipsub'})->as_query } 242 }; 243 }, 244 { join_type => 'LEFT' }, 245); 246 247=head2 last_node 248 249This relationship will return the last node that was seen on the port. 250 251The JOIN is of type "LEFT" in case there isn't any such node. 252 253=cut 254 255__PACKAGE__->belongs_to( 256 last_node => 'App::Netdisco::DB::Result::Virtual::LastNode', { 257 'foreign.switch' => 'self.ip', 258 'foreign.port' => 'self.port', 259 }, { 260 join_type => 'LEFT', 261 } 262); 263 264=head2 vlans 265 266As compared to C<port_vlans>, this relationship returns a set of Device VLAN 267row objects for the VLANs on the given port, which might be more useful if you 268want to find out details such as the VLAN name. 269 270See also C<vlan_count>. 271 272=cut 273 274__PACKAGE__->many_to_many( vlans => 'port_vlans', 'vlan' ); 275 276 277=head2 oui 278 279Returns the C<oui> table entry matching this Port. You can then join on this 280relation and retrieve the Company name from the related table. 281 282The JOIN is of type LEFT, in case the OUI table has not been populated. 283 284=cut 285 286__PACKAGE__->belongs_to( oui => 'App::Netdisco::DB::Result::Oui', 287 sub { 288 my $args = shift; 289 return { 290 "$args->{foreign_alias}.oui" => 291 { '=' => \"substring(cast($args->{self_alias}.mac as varchar) for 8)" } 292 }; 293 }, 294 { join_type => 'LEFT' } 295); 296 297=head1 ADDITIONAL METHODS 298 299=head2 neighbor 300 301Returns the Device entry for the neighbour Device on the given port. 302 303Might return an undefined value if there is no neighbor on the port, or if the 304neighbor has not been fully discovered by Netdisco and so does not exist in 305the database. 306 307=cut 308 309sub neighbor { 310 my $row = shift; 311 return eval { $row->neighbor_alias->device || undef }; 312} 313 314=head1 ADDITIONAL COLUMNS 315 316=head2 native 317 318An alias for the C<vlan> column, which stores the PVID (that is, the VLAN 319ID assigned to untagged frames received on the port). 320 321=cut 322 323sub native { return (shift)->vlan } 324 325=head2 error_disable_cause 326 327Returns the textual reason given by the device if the port is in an error 328state, or else `undef` if the port is not in an error state. 329 330=cut 331 332sub error_disable_cause { return (shift)->get_column('error_disable_cause') } 333 334=head2 remote_is_wap 335 336Returns true if the remote LLDP neighbor has reported Wireless Access Point 337capability. 338 339=cut 340 341sub remote_is_wap { return (shift)->get_column('remote_is_wap') } 342 343=head2 remote_is_phone 344 345Returns true if the remote LLDP neighbor has reported Telephone capability. 346 347=cut 348 349sub remote_is_phone { return (shift)->get_column('remote_is_phone') } 350 351=head2 remote_inventory 352 353Returns a synthesized description of the remote LLDP device if inventory 354information was given, including vendor, model, OS version, and serial number. 355 356=cut 357 358sub remote_inventory { 359 my $port = shift; 360 my $os_ver = ($port->get_column('remote_os_ver') 361 ? ('running '. $port->get_column('remote_os_ver')) : ''); 362 my $serial = ($port->get_column('remote_serial') 363 ? ('('. $port->get_column('remote_serial') .')') : ''); 364 365 my $retval = join ' ', ($port->get_column('remote_vendor') || ''), 366 ($port->get_column('remote_model') || ''), $serial, $os_ver; 367 368 return (($retval =~ m/[[:alnum:]]/) ? $retval : ''); 369} 370 371=head2 vlan_count 372 373Returns the number of VLANs active on this device port. Enable this column by 374applying the C<with_vlan_count()> modifier to C<search()>. 375 376=cut 377 378sub vlan_count { return (shift)->get_column('vlan_count') } 379 380=head2 lastchange_stamp 381 382Formatted version of the C<lastchange> field, accurate to the minute. Enable 383this column by applying the C<with_times()> modifier to C<search()>. 384 385The format is somewhat like ISO 8601 or RFC3339 but without the middle C<T> 386between the date stamp and time stamp. That is: 387 388 2012-02-06 12:49 389 390=cut 391 392sub lastchange_stamp { return (shift)->get_column('lastchange_stamp') } 393 394=head2 is_free 395 396This method can be used to evaluate whether a device port could be considered 397unused, based on the last time it changed from the "up" state to a "down" 398state. 399 400See the C<with_is_free> and C<only_free_ports> modifiers to C<search()>. 401 402=cut 403 404sub is_free { return (shift)->get_column('is_free') } 405 406=head2 base64url_port 407 408Returns a Base64 encoded version of the C<port> column value suitable for use 409in a URL. 410 411=cut 412 413sub base64url_port { return encode_base64url((shift)->port) } 414 415=head2 net_mac 416 417Returns the C<mac> column instantiated into a L<NetAddr::MAC> object. 418 419=cut 420 421sub net_mac { return NetAddr::MAC->new(mac => ((shift)->mac || '')) } 422 423=head2 last_comment 424 425Returns the most recent comment from the logs for this device port. 426 427=cut 428 429sub last_comment { 430 my $row = (shift)->logs->search(undef, 431 { order_by => { -desc => 'creation' }, rows => 1 })->first; 432 return ($row ? $row->log : ''); 433} 434 4351; 436