1package App::Netdisco::DB::ResultSet::DevicePort; 2use base 'App::Netdisco::DB::ResultSet'; 3 4use strict; 5use warnings; 6 7use Try::Tiny; 8require Dancer::Logger; 9 10__PACKAGE__->load_components(qw/ 11 +App::Netdisco::DB::ExplicitLocking 12/); 13 14=head1 ADDITIONAL METHODS 15 16=head2 with_times 17 18This is a modifier for any C<search()> (including the helpers below) which 19will add the following additional synthesized columns to the result set: 20 21=over 4 22 23=item lastchange_stamp 24 25=back 26 27=cut 28 29sub with_times { 30 my ($rs, $cond, $attrs) = @_; 31 32 return $rs 33 ->search_rs($cond, $attrs) 34 ->search({}, 35 { 36 '+columns' => { lastchange_stamp => 37 \("to_char(device.last_discover - (device.uptime - me.lastchange) / 100 * interval '1 second', " 38 ."'YYYY-MM-DD HH24:MI:SS')") }, 39 join => 'device', 40 }); 41} 42 43=head2 with_is_free 44 45This is a modifier for any C<search()> (including the helpers below) which 46will add the following additional synthesized columns to the result set: 47 48=over 4 49 50=item is_free 51 52=back 53 54In the C<$cond> hash (the first parameter) pass in the C<age_num> which must 55be an integer, and the C<age_unit> which must be a string of either C<days>, 56C<weeks>, C<months> or C<years>. 57 58=cut 59 60sub with_is_free { 61 my ($rs, $cond, $attrs) = @_; 62 63 my $interval = (delete $cond->{age_num}) .' '. (delete $cond->{age_unit}); 64 65 return $rs 66 ->search_rs($cond, $attrs) 67 ->search({}, 68 { 69 '+columns' => { is_free => 70 # NOTE this query is in `git grep 'THREE PLACES'` 71 \["me.up_admin = 'up' AND me.up != 'up' AND me.type != 'propVirtual' AND " 72 ."((age(now(), to_timestamp(extract(epoch from device.last_discover) - (device.uptime/100))) < ?::interval " 73 ."AND (last_node.time_last IS NULL OR age(now(), last_node.time_last) > ?::interval)) " 74 ."OR age(now(), to_timestamp(extract(epoch from device.last_discover) - (device.uptime - me.lastchange)/100)) > ?::interval)", 75 [{} => $interval],[ {} => $interval],[ {} => $interval]] }, 76 join => [qw/device last_node/], 77 }); 78} 79 80=head2 only_free_ports 81 82This is a modifier for any C<search()> (including the helpers below) which 83will restrict results based on whether the port is considered "free". 84 85In the C<$cond> hash (the first parameter) pass in the C<age_num> which must 86be an integer, and the C<age_unit> which must be a string of either C<days>, 87C<weeks>, C<months> or C<years>. 88 89=cut 90 91sub only_free_ports { 92 my ($rs, $cond, $attrs) = @_; 93 94 my $interval = (delete $cond->{age_num}) .' '. (delete $cond->{age_unit}); 95 96 return $rs 97 ->search_rs($cond, $attrs) 98 ->search( 99 { 100 # NOTE this query is in `git grep 'THREE PLACES'` 101 'me.up_admin' => 'up', 102 'me.up' => { '!=' => 'up' }, 103 'me.type' => { '!=' => 'propVirtual' }, 104 -or => [ 105 -and => [ 106 \["age(now(), to_timestamp(extract(epoch from device.last_discover) - (device.uptime/100))) < ?::interval", 107 [{} => $interval]], 108 -or => [ 109 'last_node.time_last' => undef, 110 \["age(now(), last_node.time_last) > ?::interval", [{} => $interval]], 111 ] 112 ], 113 \["age(now(), to_timestamp(extract(epoch from device.last_discover) - (device.uptime - me.lastchange)/100)) > ?::interval", 114 [{} => $interval]], 115 ], 116 },{ join => [qw/device last_node/] }, 117 ); 118} 119 120=head2 with_properties 121 122This is a modifier for any C<search()> which 123will add the following additional synthesized columns to the result set: 124 125=over 4 126 127=item error_disable_cause 128 129=item remote_is_wap (boolean) 130 131=item remote_is_phone (boolean) 132 133=back 134 135=cut 136 137sub with_properties { 138 my ($rs, $cond, $attrs) = @_; 139 140 return $rs 141 ->search_rs($cond, $attrs) 142 ->search({}, 143 { 144 '+select' => [qw/ 145 properties.error_disable_cause 146 properties.remote_is_wap 147 properties.remote_is_phone 148 /], 149 '+as' => [qw/ 150 error_disable_cause 151 remote_is_wap remote_is_phone 152 /], 153 join => 'properties', 154 }); 155} 156 157=head2 with_remote_inventory 158 159This is a modifier for any C<search()> which 160will add the following additional synthesized columns to the result set: 161 162=over 4 163 164=item remote_vendor 165 166=item remote_model 167 168=item remote_os_ver 169 170=item remote_serial 171 172=back 173 174=cut 175 176sub with_remote_inventory { 177 my ($rs, $cond, $attrs) = @_; 178 179 return $rs 180 ->search_rs($cond, $attrs) 181 ->search({}, 182 { 183 '+select' => [qw/ 184 properties.remote_vendor 185 properties.remote_model 186 properties.remote_os_ver 187 properties.remote_serial 188 /], 189 '+as' => [qw/ 190 remote_vendor remote_model remote_os_ver remote_serial 191 /], 192 join => 'properties', 193 }); 194} 195 196=head2 with_vlan_count 197 198This is a modifier for any C<search()> (including the helpers below) which 199will add the following additional synthesized columns to the result set: 200 201=over 4 202 203=item vlan_count 204 205=back 206 207=cut 208 209sub with_vlan_count { 210 my ($rs, $cond, $attrs) = @_; 211 212 return $rs 213 ->search_rs($cond, $attrs) 214 ->search({}, 215 { 216 '+columns' => { vlan_count => 217 $rs->result_source->schema->resultset('DevicePortVlan') 218 ->search( 219 { 220 'dpv.ip' => { -ident => 'me.ip' }, 221 'dpv.port' => { -ident => 'me.port' }, 222 }, 223 { alias => 'dpv' } 224 )->count_rs->as_query 225 }, 226 }); 227} 228 229=head1 SPECIAL METHODS 230 231=head2 delete( \%options? ) 232 233Overrides the built-in L<DBIx::Class> delete method to more efficiently 234handle the removal or archiving of nodes. 235 236=cut 237 238sub _plural { (shift || 0) == 1 ? 'entry' : 'entries' }; 239 240sub delete { 241 my $self = shift; 242 243 my $schema = $self->result_source->schema; 244 my $ports = $self->search(undef, { columns => 'ip' }); 245 246 my $ip = undef; 247 { 248 no autovivification; 249 try { $ip ||= ${ $ports->{attrs}->{where}->{ip}->{'-in'} }->[1]->[1] }; 250 try { $ip ||= $ports->{attrs}->{where}->{'me.ip'} }; 251 } 252 $ip ||= 'netdisco'; 253 254 foreach my $set (qw/ 255 DevicePortPower 256 DevicePortProperties 257 DevicePortVlan 258 DevicePortWireless 259 DevicePortSsid 260 /) { 261 my $gone = $schema->resultset($set)->search( 262 { ip => { '-in' => $ports->as_query }}, 263 )->delete; 264 265 Dancer::Logger::debug sprintf ' [%s] db/ports - removed %d port %s from %s', 266 $ip, $gone, _plural($gone), $set if defined Dancer::Logger::logger(); 267 } 268 269 $schema->resultset('Node')->search( 270 { switch => { '-in' => $ports->as_query }}, 271 )->delete(@_); 272 273 # now let DBIC do its thing 274 return $self->next::method(); 275} 276 2771; 278