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