2 package BackupPC::Search;
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/;
13 use Data::Dump qw(dump);
16 our @ISA=qw(Exporter);
24 my $bpc = BackupPC::Lib->new || die;
25 $bpc->ConfigRead('_search_archive');
26 my %Conf = $bpc->Conf();
30 my $search_module = $Conf{SearchModule} || die "search is disabled";
31 eval "use $search_module";
33 warn "ERROR: $search_module: $!";
35 warn "# using $search_module for full-text search";
38 return $search_module->new( %Conf );
44 $dbh ||= DBI->connect($Conf{SearchDSN}, $Conf{SearchUser}, "", { RaiseError => 1, AutoCommit => 1 } );
52 my $sth = $dbh->prepare(qq{
55 hosts.name || ':' || shares.name as share
57 JOIN hosts on hostid = hosts.id
61 push @ret, { 'id' => '', 'share' => '-'}; # dummy any
63 while ( my $row = $sth->fetchrow_hashref() ) {
70 my $t = shift || return;
71 my $iso = BackupPC::Lib::timeStamp(undef, $t);
72 $iso =~ s/\s/ /g;
76 sub dates_from_form($) {
77 my $param = shift || return;
79 sub mk_epoch_date($$) {
80 my ($name,$suffix) = @_;
82 my $yyyy = $param->{ $name . '_year_' . $suffix} || return undef;
83 my $mm = $param->{ $name . '_month_' . $suffix};
84 my $dd = $param->{ $name . '_day_' . $suffix};
90 my $dt = new DateTime(
98 if ( $suffix eq 'to' && ( ! $mm || ! $dd ) ) {
99 $dt += DateTime::Duration->new( years => 1 ) if ! $mm;
100 $dt += DateTime::Duration->new( months => 1 ) if ! $dd;
101 $dt -= DateTime::Duration->new( days => 1 );
104 print STDERR "mk_epoch_date($name,$suffix) [$yyyy-$mm-$dd] = " . $dt->ymd . " " . $dt->hms . "\n";
105 return $dt->epoch || 'NULL';
109 mk_epoch_date('search_backup', 'from'),
110 mk_epoch_date('search_backup', 'to'),
111 mk_epoch_date('search', 'from'),
112 mk_epoch_date('search', 'to'),
121 my $param = shift || return;
123 my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);
126 push @conditions, qq{ backups.date >= $backup_from } if ($backup_from);
127 push @conditions, qq{ backups.date <= $backup_to } if ($backup_to);
128 push @conditions, qq{ files.date >= $files_from } if ($files_from);
129 push @conditions, qq{ files.date <= $files_to } if ($files_to);
131 print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" and ",@conditions);
133 push( @conditions, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'});
134 push (@conditions, " upper(files.path) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'});
136 push @conditions, join(' ' , 'burned is', $param->{burned} eq 'burned' ? '' : 'not', 'true') if $param->{burned};
138 return join(" and ", @conditions);
145 sname_d => 'shares.name DESC',
146 sname_a => 'shares.name ASC',
147 filepath_d => 'files.path DESC',
148 filepath_a => 'files.path ASC',
149 backupnum_d => 'files.backupnum DESC',
150 backupnum_a => 'files.backupnum ASC',
151 size_d => 'files.size DESC',
152 size_a => 'files.size ASC',
153 date_d => 'files.date DESC',
154 date_a => 'files.date ASC',
159 sname_d => 'host DESC, share DESC',
160 sname_a => 'host ASC, share ASC',
161 num_d => 'backupnum DESC',
162 num_a => 'backupnum ASC',
163 date_d => 'date DESC',
164 date_a => 'date ASC',
167 size_d => 'size DESC',
168 size_a => 'size ASC',
169 incsize_d => 'inc_size DESC',
170 incsize_a => 'inc_size ASC',
176 my ($part,$type, $sort_order) = @_;
178 die "unknown part: $part" unless ($sort_def->{$part});
179 die "unknown type: $type" unless ($sort_def->{$part}->{$type});
181 $sort_order ||= $sort_def->{$part}->{'default'};
183 if (my $ret = $sort_def->{$part}->{$type}->{$sort_order}) {
186 # fallback to default sort order
187 return $sort_def->{$part}->{$type}->{ $sort_def->{$part}->{'default'} };
194 my $offset = $param->{'offset'} || 0;
202 shares.name AS sname,
203 files.backupnum AS backupnum,
204 files.path AS filepath,
212 INNER JOIN shares ON files.shareID=shares.ID
213 INNER JOIN hosts ON hosts.ID = shares.hostID
214 INNER JOIN backups ON backups.num = files.backupnum and backups.hostID = hosts.ID AND backups.shareID = files.shareID
218 my $where = getWhere($param);
219 $sql_where = " WHERE ". $where if ($where);
221 # do we have to add tables for burned media?
222 if ( $param->{burned} ) {
224 LEFT OUTER JOIN backups_burned on backup_id = backups.id
228 my $order = getSort('search', 'sql', $param->{'sort'});
236 my $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order };
237 my $sth = $dbh->prepare($sql_results);
238 $sth->execute( $offset );
242 while (my $row = $sth->fetchrow_hashref()) {
247 return ($sth->rows, \@ret);
250 sub getFilesHyperEstraier($) {
253 my $offset = $param->{'offset'} || 0;
256 my $q = $param->{'search_filename'};
257 my $shareid = $param->{'search_share'};
258 my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);
260 return search_module->search(
261 $offset, $on_page, $param->{sort},
262 $q, $shareid, $backup_from, $backup_to, $files_from, $files_to
269 my ($host, $share, $backupnum) = @_;
270 my $ret = $Conf{GzipSchema};
273 $ret =~ s/\\h/$host/ge;
274 $ret =~ s/\\s/$share/ge;
275 $ret =~ s/\\n/$backupnum/ge;
283 sub get_tgz_size_by_name($) {
286 my $tgz = $Conf{GzipTempDir}.'/'.$name;
290 if (-f "${tgz}.tar.gz") {
291 $size = (stat("${tgz}.tar.gz"))[7];
293 opendir(my $dir, $tgz) || die "can't opendir $tgz: $!";
294 my @parts = grep { !/^\./ && !/md5/ && -f "$tgz/$_" } readdir($dir);
296 foreach my $part (@parts) {
297 my $currSize = (stat("$tgz/$part"))[7];
298 $size += (stat("$tgz/$part"))[7] || die "can't stat $tgz/$part: $!";
309 sub getGzipSizeFromBackupID($) {
313 SELECT hosts.name as host,
314 shares.name as share,
315 backups.num as backupnum
316 FROM hosts, backups, shares
317 WHERE shares.id=backups.shareid AND
318 hosts.id =backups.hostid AND
321 my $sth = $dbh->prepare($sql);
322 $sth->execute($backupID);
323 my $row = $sth->fetchrow_hashref();
325 return get_tgz_size_by_name(
326 getGzipName($row->{'host'}, $row->{share}, $row->{backupnum})
332 my ($hostID, $backupNum) = @_;
337 SELECT hosts.name as host,
338 shares.name as share,
339 backups.num as backupnum
340 FROM hosts, backups, shares
341 WHERE shares.id=backups.shareid AND
342 hosts.id =backups.hostid AND
346 my $sth = $dbh->prepare($sql);
347 $sth->execute($hostID, $backupNum);
349 my $row = $sth->fetchrow_hashref();
351 return get_tgz_size_by_name(
352 getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'})
357 sub getBackupsNotBurned($) {
362 my $order = getSort('burn', 'sql', $param->{'sort'});
364 print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql order: $order\n";
371 date_part('epoch',now()) - b.date as age,
373 count(ap.*) as scheduled,
374 count(ab.*) as burned
376 JOIN backups b ON b.id = p.backup_id
377 LEFT OUTER JOIN archive_parts ap ON ap.backup_part_id = p.id
378 LEFT OUTER JOIN archive_burned ab ON ab.archive_id = ap.archive_id
379 GROUP BY p.id,filename,b.date,age,p.size,p.part_nr
383 HAVING count(ap.*) = 0
384 } unless $param->{scheduled};
387 ORDER BY b.date,p.part_nr
389 my $sth = $dbh->prepare( $sql );
393 while ( my $row = $sth->fetchrow_hashref() ) {
394 $row->{'age'} = sprintf("%0.1f", ( $row->{'age'} / 86400 ) );
395 #$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) );
402 sub displayBackupsGrid($) {
406 my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize";
409 <form id="forma" method="POST" action="$MyURL?action=burn">
411 <script type="text/javascript">
412 var media_size = $max_archive_size ;
417 { local $/ = undef; $retHTML .= <DATA> }
420 <input type="hidden" value="burn" name="action">
421 <input type="hidden" value="results" name="search_results">
422 <table style="fview" border="0" cellspacing="0" cellpadding="2">
423 <tr class="tableheader">
424 <td class="tableheader">
425 <input type="checkbox" name="allFiles" id="allFiles" onClick="checkAll('allFiles');">
428 sort_header($param, 'Filename', 'filename', 'left') .
429 sort_header($param, 'Date', 'date', 'center') .
430 sort_header($param, 'Age/days', 'age', 'center') .
431 sort_header($param, 'Size', 'size', 'center') .
433 <td align="center" title="scheduled">sc</td>
434 <td align="center" title="burned">bu</td>
438 my @color = (' bgcolor="#e0e0e0"', '');
441 # my $img_url = $Conf{CgiImageDirURL};
443 foreach my $backup ( getBackupsNotBurned($param) ) {
446 ,'<tr',$color[$i++%2],'>'
447 ,'<td class="fview">'
448 ,'<input type="checkbox" name="fcb',$backup->{id},'" value="',$backup->{id},'" onClick="sumiraj(this);">'
449 ,'<input type="hidden" id="fss',$backup->{id},'" value="',$backup->{size},'">'
451 ,'<td align="left">', $backup->{'filename'}, '</td>'
452 ,'<td align="center">', epoch_to_iso( $backup->{'date'} ), '</td>'
453 ,'<td align="center">', $backup->{'age'}, '</td>'
454 ,'<td align="right">', unit($backup->{'size'}), '</td>'
455 ,'<td align="center">', $backup->{scheduled}, '</td>'
456 ,'<td align="center">', $backup->{burned}, '</td>'
461 $retHTML .= "</table>";
464 <input type=submit name="scheduled" value="Show scheduled parts">
465 } unless $param->{scheduled};
467 $retHTML .= "</form>";
475 my $offset = $param->{'offset'};
476 my $hilite = $param->{'search_filename'};
480 my $start_t = time();
482 my ($results, $files);
483 if ($param->{'use_hest'} && length($hilite) > 0) {
484 ($results, $files) = getFilesHyperEstraier($param);
486 ($results, $files) = getFiles($param);
489 my $dur_t = time() - $start_t;
490 my $dur = sprintf("%0.4fs", $dur_t);
492 my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page);
496 <p style="color: red;">No results found...</p>
502 #$retHTML .= '<pre>' . Dumper($files) . '</pre>';
508 Found <b>$results files</b> showing <b>$from - $to</b> (took $dur)
510 <table style="fview" width="100%" border="0" cellpadding="2" cellspacing="0">
511 <tr class="fviewheader">
515 sub sort_header($$$$) {
516 my ($param, $display, $name, $align) = @_;
518 my ($sort_what, $sort_direction) = split(/_/,$param->{'sort'},2);
520 my $old_sort = $param->{'sort'};
522 my $html = qq{<td align="$align"};
525 if (lc($sort_what) eq lc($name)) {
526 my $direction = lc($sort_direction);
528 # swap direction or fallback to default
529 $direction =~ tr/ad/da/;
530 $direction = 'a' unless ($direction =~ /[ad]/);
532 $param->{'sort'} = $name . '_' . $direction;
533 $html .= ' style="border: 1px solid #808080;"';
535 # add unicode arrow for direction
537 $arrow .= $direction eq 'a' ? '▲'
538 : $direction eq 'd' ? '▼'
543 $param->{'sort'} = $name . '_a';
546 $html .= '><a href="' . page_uri($param) . '">' . $display . '</a>' . $arrow . '</td>';
547 $param->{'sort'} = $old_sort;
553 sort_header($param, 'Share', 'sname', 'center') .
554 sort_header($param, 'Type and Name', 'filepath', 'center') .
555 sort_header($param, '#', 'backupnum', 'center') .
556 sort_header($param, 'Size', 'size', 'center') .
557 sort_header($param, 'Date', 'date', 'center');
560 <td align="center">Media</td>
566 sub hilite_html($$) {
567 my ($html, $search) = @_;
568 $html =~ s#($search)#<b>$1</b>#gis;
572 sub restore_link($$$$$$) {
574 my $action = 'RestoreFile';
575 $action = 'browse' if (lc($type) eq 'dir');
576 return sprintf(qq{<a href="?action=%s&host=%s&num=%d&share=%s&dir=%s">%s</a>}, $action, @_);
582 sub check_archived($$$) {
583 my ($host, $share, $num) = @_;
585 if (my $html = $archived_cache{"$host $share $num"}) {
589 $sth_archived ||= $dbh->prepare(qq{
591 archive.dvd_nr, note,
592 count(archive_burned.copy) as copies
594 inner join archive_burned on archive_burned.archive_id = archive.id
595 inner join archive_backup_parts on archive.id = archive_backup_parts.archive_id
596 inner join backups on backups.id = archive_backup_parts.backup_id
597 inner join hosts on hosts.id = backups.hostid
598 inner join shares on shares.id = backups.shareid
599 where hosts.name = ? and shares.name = ? and backups.num = ?
600 group by archive.dvd_nr, note
605 $sth_archived->execute($host, $share, $num);
606 while (my $row = $sth_archived->fetchrow_hashref()) {
607 push @mediums, '<abbr title="' .
609 ' [' . $row->{'copies'} . ']' .
610 '">' .$row->{'dvd_nr'} .
614 my $html = join(", ",@mediums);
615 $archived_cache{"$host $share $num"} = $html;
619 my $i = $offset * $on_page;
621 foreach $file (@{ $files }) {
624 my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
625 $retHTML .= qq{<tr class="fviewborder">};
627 $retHTML .= qq{<td class="fviewborder">$i</td>};
630 qq{<td class="fviewborder" align="right">} . $file->{'hname'} . ':' . $file->{'sname'} . qq{</td>} .
631 qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.png" alt="$typeStr" align="middle"> } . hilite_html( $file->{'filepath'}, $hilite ) . qq{</td>} .
632 qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{</td>} .
633 qq{<td class="fviewborder" align="right">} . $file->{'size'} . qq{</td>} .
634 qq{<td class="fviewborder">} . epoch_to_iso( $file->{'date'} ) . qq{</td>} .
635 qq{<td class="fviewborder">} . check_archived( $file->{'hname'}, $file->{'sname'}, $file->{'backupnum'} ) . qq{</td>};
639 $retHTML .= "</table>";
641 # all variables which has to be transfered
642 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/) {
643 $retHTML .= qq{<INPUT TYPE="hidden" NAME="$n" VALUE="$In{$n}">\n};
647 my $max_page = int( $results / $on_page );
651 my $param = shift || die "no param?";
655 foreach my $k (keys %{ $param }) {
657 $uri .= $del . $k . '=' . ${EscURI( $param->{$k} )};
665 my ($param,$page,$display) = @_;
667 $param->{'offset'} = $page if (defined($page));
669 my $html = '<a href = "' . page_uri($param) . '">' . $display . '</a>';
672 $retHTML .= '<div style="text-align: center;">';
675 $retHTML .= page_link($param, $offset - 1, '<<') . ' ';
678 while ($page <= $max_page) {
679 if ($page == $offset) {
680 $retHTML .= $del . '<b>' . ($page + 1) . '</b>';
682 $retHTML .= $del . page_link($param, $page, $page + 1);
685 if ($page < $offset - $pager_pages && $page != 0) {
687 $page = $offset - $pager_pages;
689 } elsif ($page > $offset + $pager_pages && $page != $max_page) {
699 if ($offset < $max_page) {
700 $retHTML .= ' ' . page_link($param, $offset + 1, '>>');
703 $retHTML .= "</div>";
708 my @units = qw/b k M G/;
714 while ( ( $v / 10000 ) >= 1 ) {
720 return sprintf("%d%s", $v, $units[$o]);
721 } elsif ( $v == 0 ) {
724 return sprintf("%.1f%s", $v, $units[$o]);
733 <style type="text/css">
741 background-color: #E0F0E0;
742 border: 1px solid #00C000;
745 DIV#fixedBox, DIV#fixedBox INPUT, DIV#fixedBox TEXTAREA {
750 position: fixed !important;
751 left: 0.5em !important;
752 top: auto !important;
753 bottom: 1em !important;
754 width: 15% !important;
757 DIV#fixedBox INPUT[type=text], DIV#fixedBox TEXTAREA {
758 border: 1px solid #00C000;
766 DIV#fixedBox #submitBurner {
783 * HTML DIV#fixedBox {
787 #mContainer, #gradient, #mask, #progressIndicator {
793 vertical-align: middle;
797 #gradient, #mask, #progressIndicator {
801 border-color: #000000;
816 margin-bottom: 0.5em;
821 background-color: #FFFF00;
826 background-color: #FFFFFF;
831 background-color: transparent;
844 <script type="text/javascript">
849 function debug(msg) {
850 return; // Disable debugging
852 if (! debug_div) debug_div = document.getElementById('debug');
854 // this will create debug div if it doesn't exist.
856 debug_div = document.createElement('div');
857 if (document.body) document.body.appendChild(debug_div);
858 else debug_div = null;
861 debug_div.appendChild(document.createTextNode(msg));
862 debug_div.appendChild(document.createElement("br"));
867 var element_id_cache = Array();
869 function element_id(name,element) {
870 if (! element_id_cache[name]) {
871 element_id_cache[name] = self.document.getElementById(name);
873 return element_id_cache[name];
876 function checkAll(location) {
877 var f = element_id('forma') || null;
878 if (!f) return false;
880 var len = f.elements.length;
881 var check_all = element_id('allFiles');
882 var suma = check_all.checked ? (parseInt(f.elements['totalsize'].value) || 0) : 0;
884 for (var i = 0; i < len; i++) {
885 var e = f.elements[i];
886 if (e.name != 'all' && e.name.substr(0, 3) == 'fcb') {
887 if (check_all.checked) {
888 if (e.checked) continue;
889 var el = element_id("fss" + e.name.substr(3));
890 var size = parseInt(el.value) || 0;
891 debug('suma: '+suma+' size: '+size);
892 if ((suma + size) < media_size) {
906 function update_sum(suma, suma_disp) {
907 if (! suma_disp) suma_disp = suma;
908 suma_disp = Math.floor(suma_disp / 1024);
909 element_id('forma').elements['totalsize_kb'].value = suma_disp;
910 element_id('forma').elements['totalsize'].value = suma;
911 pbar_set(suma, media_size);
912 debug('total size: ' + suma);
915 function update_size(name, checked, suma) {
916 var size = parseInt( element_id("fss" + name).value);
924 debug('update_size('+name+','+checked+') suma: '+suma);
928 element_id("volumes").innerHTML = "This will take "+volumes+" mediums!";
929 element_id("volumes").style.display = 'block';
934 element_id("volumes").style.display = 'none';
941 function sumiraj(e) {
942 var suma = parseInt(element_id('forma').elements['totalsize'].value) || 0;
943 var len = element_id('forma').elements.length;
945 suma = update_size(e.name.substr(3), e.checked, suma);
946 if (suma < 0) suma = 0;
949 for (var i = 0; i < len; i++) {
950 var fel = element_id('forma').elements[i];
951 if (fel.name != 'all' && fel.checked && fel.name.substr(0,3) == 'fcb') {
952 suma = update_size(fel.name.substr(3), fel.checked, suma);
962 var _pbar_width = null;
963 var _pbar_warn = 10; // change color in last 10%
965 function pbar_reset() {
966 element_id("mask").style.left = "0px";
967 _pbar_width = element_id("mContainer").offsetWidth - 2;
968 element_id("mask").style.width = _pbar_width + "px";
969 element_id("mask").style.display = "block";
970 element_id("progressIndicator").style.zIndex = 10;
971 element_id("progressIndicator").innerHTML = "0";
974 function dec2hex(d) {
975 var hch = '0123456789ABCDEF';
977 var q = (d - a) / 16;
978 return hch.charAt(q) + hch.charAt(a);
981 function pbar_set(amount, max) {
982 debug('pbar_set('+amount+', '+max+')');
984 if (_pbar_width == null) {
985 var _mc = element_id("mContainer");
986 if (_pbar_width == null) _pbar_width = parseInt(_mc.offsetWidth ? (_mc.offsetWidth - 2) : 0) || null;
987 if (_pbar_width == null) _pbar_width = parseInt(_mc.clientWidth ? (_mc.clientWidth + 2) : 0) || null;
988 if (_pbar_width == null) _pbar_width = 0;
991 var pcnt = Math.floor(amount * 100 / max);
992 var p90 = 100 - _pbar_warn;
993 var pcol = pcnt - p90;
994 if (Math.round(pcnt) <= 100) {
995 if (pcol < 0) pcol = 0;
996 var e = element_id("submitBurner");
997 debug('enable_button');
999 var a = e.getAttributeNode('disabled') || null;
1000 if (a) e.removeAttributeNode(a);
1002 debug('disable button');
1004 var e = element_id("submitBurner");
1005 if (!e.disabled) e.disabled = true;
1007 var col_g = Math.floor((_pbar_warn - pcol) * 255 / _pbar_warn);
1008 var col = '#FF' + dec2hex(col_g) + '00';
1010 //debug('pcol: '+pcol+' g:'+col_g+' _pbar_warn:'+ _pbar_warn + ' color: '+col);
1011 element_id("gradient").style.backgroundColor = col;
1013 element_id("progressIndicator").innerHTML = pcnt + '%';
1014 //element_id("progressIndicator").innerHTML = amount;
1016 element_id("mask").style.clip = 'rect(' + Array(
1018 element_id("mask").offsetWidth + 'px',
1019 element_id("mask").offsetHeight + 'px',
1020 Math.round(_pbar_width * amount / max) + 'px'
1024 if (!self.body) self.body = new Object();
1025 self.onload = self.document.onload = self.body.onload = function() {
1034 <input type="hidden" name="totalsize"/>
1035 Size: <input type="text" name="totalsize_kb" size="7" readonly="readonly" style="text-align:right;" value="0" /> kB
1037 <div id="mContainer">
1038 <div id="gradient"> </div>
1039 <div id="mask"> </div>
1040 <div id="progressIndicator">0%</div>
1044 <div id="volumes"> </div>
1047 <textarea name="note" cols="10" rows="5" id="note"></textarea>
1049 <input type="submit" id="submitBurner" value="Burn selected" name="submitBurner" />
1053 <div id="debug" style="float: right; width: 10em; border: 1px #ff0000 solid; background-color: #ffe0e0; -moz-opacity: 0.7;">