2 package BackupPC::SearchLib;
5 use BackupPC::CGI::Lib qw(:all);
6 use BackupPC::Attrib qw(:all);
9 use vars qw(%In $MyURL);
10 use Time::HiRes qw/time/;
15 my $dsn = $Conf{SearchDSN};
16 my $db_user = $Conf{SearchUser} || '';
18 my $index_path = $Conf{HyperEstraierIndex};
20 $index_path = $TopDir . '/' . $index_path;
21 $index_path =~ s#//#/#g;
27 $dbh ||= DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
35 my $sth = $dbh->prepare(qq{
38 hosts.name || ':' || shares.name as share
40 JOIN hosts on hostid = hosts.id
44 push @ret, { 'id' => '', 'share' => '-'}; # dummy any
46 while ( my $row = $sth->fetchrow_hashref() ) {
53 my $t = shift || return;
54 my $iso = BackupPC::Lib::timeStamp(undef, $t);
55 $iso =~ s/\s/ /g;
59 sub dates_from_form($) {
60 my $param = shift || return;
62 sub mk_epoch_date($$) {
63 my ($name,$suffix) = @_;
65 my $yyyy = $param->{ $name . '_year_' . $suffix} || return undef;
66 my $mm .= $param->{ $name . '_month_' . $suffix} ||
67 ( $suffix eq 'from' ? 1 : 12);
68 my $dd .= $param->{ $name . '_day_' . $suffix} ||
69 ( $suffix eq 'from' ? 1 : 31);
75 my $dt = new DateTime(
80 print STDERR "mk_epoch_date($name,$suffix) [$yyyy-$mm-$dd] = " . $dt->ymd . " " . $dt->hms . "\n";
81 return $dt->epoch || 'NULL';
85 mk_epoch_date('search_backup', 'from'),
86 mk_epoch_date('search_backup', 'to'),
87 mk_epoch_date('search', 'from'),
88 mk_epoch_date('search', 'to'),
97 my $param = shift || return;
99 my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);
102 push @conditions, qq{ backups.date >= $backup_from } if ($backup_from);
103 push @conditions, qq{ backups.date <= $backup_to } if ($backup_to);
104 push @conditions, qq{ files.date >= $files_from } if ($files_from);
105 push @conditions, qq{ files.date <= $files_to } if ($files_to);
107 print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" | ",@conditions);
109 push( @conditions, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'});
110 push (@conditions, " upper(files.path) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'});
112 return join(" and ", @conditions);
119 my $offset = $param->{'offset'} || 0;
127 shares.name AS sname,
128 files.backupnum AS backupnum,
129 files.path AS filepath,
137 INNER JOIN shares ON files.shareID=shares.ID
138 INNER JOIN hosts ON hosts.ID = shares.hostID
139 INNER JOIN backups ON backups.num = files.backupnum and backups.hostID = hosts.ID AND backups.shareID = files.shareID
143 my $where = getWhere($param);
144 $sql_where = " WHERE ". $where if ($where);
152 my $sql_count = qq{ select count(files.id) $sql_from $sql_where };
153 my $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order };
155 my $sth = $dbh->prepare($sql_count);
157 my ($results) = $sth->fetchrow_array();
159 $sth = $dbh->prepare($sql_results);
160 $sth->execute( $offset );
162 if ($sth->rows != $results) {
163 my $bug = "$0 BUG: [[ $sql_count ]] = $results while [[ $sql_results ]] = " . $sth->rows;
165 print STDERR "$bug\n";
170 while (my $row = $sth->fetchrow_hashref()) {
175 return ($results, \@ret);
178 sub getHyperEstraier_url($) {
181 return unless $use_hest;
184 my ($index_path, $index_node_url);
186 if ($use_hest =~ m#^http://#) {
187 $index_node_url = $use_hest;
189 $index_path = $TopDir . '/' . $index_path;
190 $index_path =~ s#//#/#g;
192 return ($index_path, $index_node_url);
195 sub getFilesHyperEstraier($) {
198 my $offset = $param->{'offset'} || 0;
201 die "no index_path?" unless ($index_path);
205 my ($index_path, $index_node_url) = getHyperEstraier_url($index_path);
210 $db = HyperEstraier::Database->new();
211 $db->open($index_path, $HyperEstraier::ESTDBREADER);
212 } elsif ($index_node_url) {
213 $db ||= HyperEstraier::Node->new($index_node_url);
214 $db->set_auth('admin', 'admin');
216 die "BUG: unimplemented";
219 # create a search condition object
220 my $cond = HyperEstraier::Condition->new();
222 my $q = $param->{'search_filename'};
223 my $shareid = $param->{'search_share'};
225 if (length($q) > 0) {
227 $cond->add_attr("filepath ISTRINC $q");
230 # set the search phrase to the search condition object
231 $cond->set_phrase($q);
234 my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);
236 $cond->add_attr("backup_date NUMGE $backup_from") if ($backup_from);
237 $cond->add_attr("backup_date NUMLE $backup_to") if ($backup_to);
239 $cond->add_attr("date NUMGE $files_from") if ($files_from);
240 $cond->add_attr("date NUMLE $files_to") if ($files_to);
242 $cond->add_attr("shareid NUMEQ $shareid") if ($shareid);
244 # $cond->set_max( $offset + $on_page );
245 $cond->set_options( $HyperEstraier::Condition::SURE );
246 $cond->set_order( 'date NUMA' );
248 # get the result of search
253 $result = $db->search($cond, 0);
254 $hits = $result->size;
255 } elsif ($index_node_url) {
256 $result = $db->search($cond, 0);
257 $hits = $result->doc_num;
259 die "BUG: unimplemented";
262 # for each document in result
263 for my $i ($offset .. ($offset + $on_page - 1)) {
264 last if ($i >= $hits);
268 my $id = $result->get($i);
269 $doc = $db->get_doc($id, 0);
270 } elsif ($index_node_url) {
271 $doc = $result->get_doc($i);
273 die "BUG: unimplemented";
277 foreach my $c (qw/fid hname sname backupnum fiilename filepath date type size/) {
278 $row->{$c} = $doc->attr($c);
283 return ($hits, \@res);
288 my ($host, $share, $backupnum) = @_;
289 my $ret = $Conf{GzipSchema};
292 $ret =~ s/\\h/$host/ge;
293 $ret =~ s/\\s/$share/ge;
294 $ret =~ s/\\n/$backupnum/ge;
300 sub getBackupsNotBurned() {
305 backups.hostID AS hostid,
306 min(hosts.name) AS host,
307 min(shares.name) AS share,
308 backups.num AS backupnum,
309 min(backups.type) AS type,
310 min(backups.date) AS date,
311 min(backups.size) AS size
313 INNER JOIN shares ON files.shareID=shares.ID
314 INNER JOIN hosts ON hosts.ID = shares.hostID
315 INNER JOIN backups ON backups.num = files.backupnum and backups.hostID = hosts.ID AND backups.shareID = shares.ID
317 backups.hostID, backups.num, backups.shareid
318 ORDER BY min(backups.date)
320 my $sth = $dbh->prepare( $sql );
324 while ( my $row = $sth->fetchrow_hashref() ) {
325 $row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) );
326 $row->{'size'} = sprintf("%0.2f", $row->{'size'} / 1024 / 1024);
327 my (undef,undef,undef,undef,undef,undef,undef,$fs_size,undef,undef,undef,undef,undef) =
328 stat( $Conf{InstallDir}.'/'.$Conf{GzipTempDir}.'/'.
329 getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'}));
330 $row->{'fs_size'} = $fs_size;
337 sub displayBackupsGrid()
342 <script language="javascript" type="text/javascript">
345 function checkAll(location)
347 for (var i=0;i<document.forma.elements.length;i++)
349 var e = document.forma.elements[i];
350 if ((e.checked || !e.checked) && e.name != \'all\') {
351 if (eval("document.forma."+location+".checked")) {
363 <form name="forma" method="GET" action="$MyURL?action=burn">
364 <input type="hidden" value="burn" name="action">
365 <input type="hidden" value="results" name="search_results">
366 <table style="fview" border="0" cellspacing="0" cellpadding="2">
367 <tr class="tableheader">
368 <td class="tableheader">
369 <input type="checkbox" name="allFiles" onClick="checkAll('allFiles');">
371 <td align="center">Share</td>
372 <td align="center">Backup no</td>
373 <td align="center">Type</td>
374 <td align="center">date</td>
375 <td align="center">age/days</td>
376 <td align="center">size/MB</td>
377 <td align="center">gzip size</td>
380 <tr><td colspan=7 style="tableheader">
381 <input type="submit" value="Burn selected backups on medium" name="submitBurner">
385 my @color = (' bgcolor="#e0e0e0"', '');
390 foreach my $backup ( getBackupsNotBurned() ) {
392 if ($host ne $backup->{'host'}) {
394 $host = $backup->{'host'};
398 $retHTML .= "<tr" . $color[$i %2 ] . ">";
399 $retHTML .= '<td class="fview"><input type="checkbox" name="fcb' .
400 $backup->{'hostid'}.'_'.$backup->{'backupnum'} .
401 '" value="' . $backup->{'hostid'}.'_'.$backup->{'backupnum'} .
405 '<td align="right">' . $backup->{'host'} . ':' . $backup->{'share'} . '</td>' .
406 '<td align="center">' . $backup->{'backupnum'} . '</td>' .
407 '<td align="center">' . $backup->{'type'} . '</td>' .
408 '<td align="center">' . epoch_to_iso( $backup->{'date'} ) . '</td>' .
409 '<td align="center">' . $backup->{'age'} . '</td>' .
410 '<td align="right">' . $backup->{'size'} . '</td>' .
411 '<td align="right">' . $backup->{'fs_size'} .'</td>' .
417 $retHTML .= "</table>";
418 $retHTML .= "</form>";
426 my $offset = $param->{'offset'};
427 my $hilite = $param->{'search_filename'};
431 my $start_t = time();
433 my ($results, $files);
434 if ($param->{'use_hest'} && length($hilite) > 0) {
435 ($results, $files) = getFilesHyperEstraier($param);
437 ($results, $files) = getFiles($param);
440 my $dur_t = time() - $start_t;
441 my $dur = sprintf("%0.4fs", $dur_t);
443 my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page);
447 <p style="color: red;">No results found...</p>
453 #$retHTML .= '<pre>' . Dumper($files) . '</pre>';
459 Found <b>$results files</b> showing <b>$from - $to</b> (took $dur)
461 <table style="fview" width="100%" border="0" cellpadding="2" cellspacing="0">
462 <tr class="fviewheader">
464 <td align="center">Share</td>
465 <td align="center">Type and Name</td>
466 <td align="center">#</td>
467 <td align="center">Size</td>
468 <td align="center">Date</td>
469 <td align="center">Media</td>
475 sub hilite_html($$) {
476 my ($html, $search) = @_;
477 $html =~ s#($search)#<b>$1</b>#gis;
481 sub restore_link($$$$$$) {
483 my $action = 'RestoreFile';
484 $action = 'browse' if (lc($type) eq 'dir');
485 return sprintf(qq{<a href="?action=%s&host=%s&num=%d&share=%s&dir=%s">%s</a>}, $action, @_);
488 my $i = $offset * $on_page;
490 foreach $file (@{ $files }) {
493 my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
494 $retHTML .= qq{<tr class="fviewborder">};
496 $retHTML .= qq{<td class="fviewborder">$i</td>};
499 qq{<td class="fviewborder" align="right">} . $file->{'hname'} . ':' . $file->{'sname'} . qq{</td>} .
500 qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" alt="$typeStr" align="middle"> } . hilite_html( $file->{'filepath'}, $hilite ) . qq{</td>} .
501 qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{</td>} .
502 qq{<td class="fviewborder" align="right">} . $file->{'size'} . qq{</td>} .
503 qq{<td class="fviewborder">} . epoch_to_iso( $file->{'date'} ) . qq{</td>} .
504 qq{<td class="fviewborder">} . '?' . qq{</td>};
508 $retHTML .= "</table>";
510 # all variables which has to be transfered
511 foreach my $n (qw/search_day_from search_month_from search_year_from search_day_to search_month_to search_year_to search_backup_day_from search_backup_month_from search_backup_year_from search_backup_day_to search_backup_month_to search_backup_year_to search_filename offset/) {
512 $retHTML .= qq{<INPUT TYPE="hidden" NAME="$n" VALUE="$In{$n}">\n};
516 my $max_page = int( $results / $on_page );
520 my ($param,$page,$display) = @_;
522 $param->{'offset'} = $page;
524 my $html = '<a href = "' . $MyURL;
526 foreach my $k (keys %{ $param }) {
528 $html .= $del . $k . '=' . ${EscURI( $param->{$k} )};
532 $html .= '">' . $display . '</a>';
535 $retHTML .= '<div style="text-align: center;">';
538 $retHTML .= page_link($param, $offset - 1, '<<') . ' ';
541 while ($page <= $max_page) {
542 if ($page == $offset) {
543 $retHTML .= $del . '<b>' . ($page + 1) . '</b>';
545 $retHTML .= $del . page_link($param, $page, $page + 1);
548 if ($page < $offset - $pager_pages && $page != 0) {
550 $page = $offset - $pager_pages;
552 } elsif ($page > $offset + $pager_pages && $page != $max_page) {
562 if ($offset < $max_page) {
563 $retHTML .= ' ' . page_link($param, $offset + 1, '>>');
566 $retHTML .= "</div>";