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'});
230 # XXX LIMIT $on_page doesn't work since we don't get correct number of results
236 my $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order };
237 my $sth = $dbh->prepare($sql_results);
238 my $rows = $sth->execute( $offset );
242 while (my $row = $sth->fetchrow_hashref()) {
244 last if $#ret + 1 >= $on_page;
248 return ($rows, \@ret);
251 sub getFilesHyperEstraier($) {
254 my $offset = $param->{'offset'} || 0;
257 my $q = $param->{'search_filename'};
258 my $shareid = $param->{'search_share'};
259 my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);
261 return search_module->search(
262 $offset, $on_page, $param->{sort},
263 $q, $shareid, $backup_from, $backup_to, $files_from, $files_to
270 my ($host, $share, $backupnum) = @_;
271 my $ret = $Conf{GzipSchema};
274 $ret =~ s/\\h/$host/ge;
275 $ret =~ s/\\s/$share/ge;
276 $ret =~ s/\\n/$backupnum/ge;
284 sub get_tgz_size_by_name($) {
287 my $tgz = $Conf{GzipTempDir}.'/'.$name;
291 if (-f "${tgz}.tar.gz") {
292 $size = (stat("${tgz}.tar.gz"))[7];
294 opendir(my $dir, $tgz) || die "can't opendir $tgz: $!";
295 my @parts = grep { !/^\./ && !/md5/ && -f "$tgz/$_" } readdir($dir);
297 foreach my $part (@parts) {
298 my $currSize = (stat("$tgz/$part"))[7];
299 $size += (stat("$tgz/$part"))[7] || die "can't stat $tgz/$part: $!";
310 sub getGzipSizeFromBackupID($) {
314 SELECT hosts.name as host,
315 shares.name as share,
316 backups.num as backupnum
317 FROM hosts, backups, shares
318 WHERE shares.id=backups.shareid AND
319 hosts.id =backups.hostid AND
322 my $sth = $dbh->prepare($sql);
323 $sth->execute($backupID);
324 my $row = $sth->fetchrow_hashref();
326 return get_tgz_size_by_name(
327 getGzipName($row->{'host'}, $row->{share}, $row->{backupnum})
333 my ($hostID, $backupNum) = @_;
338 SELECT hosts.name as host,
339 shares.name as share,
340 backups.num as backupnum
341 FROM hosts, backups, shares
342 WHERE shares.id=backups.shareid AND
343 hosts.id =backups.hostid AND
347 my $sth = $dbh->prepare($sql);
348 $sth->execute($hostID, $backupNum);
350 my $row = $sth->fetchrow_hashref();
352 return get_tgz_size_by_name(
353 getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'})
358 sub getBackupsNotBurned($) {
363 my $order = getSort('burn', 'sql', $param->{'sort'});
365 print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql order: $order\n";
372 date_part('epoch',now()) - b.date as age,
374 count(ap.*) as scheduled,
375 count(ab.*) as burned
377 JOIN backups b ON b.id = p.backup_id
378 LEFT OUTER JOIN archive_parts ap ON ap.backup_part_id = p.id
379 LEFT OUTER JOIN archive_burned ab ON ab.archive_id = ap.archive_id
380 GROUP BY p.id,filename,b.date,age,p.size,p.part_nr
384 HAVING count(ap.*) = 0
385 } unless $param->{scheduled};
388 ORDER BY b.date,p.part_nr
390 my $sth = $dbh->prepare( $sql );
394 while ( my $row = $sth->fetchrow_hashref() ) {
395 $row->{'age'} = sprintf("%0.1f", ( $row->{'age'} / 86400 ) );
396 #$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) );
403 sub displayBackupsGrid($) {
407 my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize";
410 <form id="forma" method="POST" action="$MyURL?action=burn">
412 <script type="text/javascript">
413 var media_size = $max_archive_size ;
418 { local $/ = undef; $retHTML .= <DATA> }
421 <input type="hidden" value="burn" name="action">
422 <input type="hidden" value="results" name="search_results">
423 <table style="fview" border="0" cellspacing="0" cellpadding="2">
424 <tr class="tableheader">
425 <td class="tableheader">
426 <input type="checkbox" name="allFiles" id="allFiles" onClick="checkAll('allFiles');">
429 sort_header($param, 'Filename', 'filename', 'left') .
430 sort_header($param, 'Date', 'date', 'center') .
431 sort_header($param, 'Age/days', 'age', 'center') .
432 sort_header($param, 'Size', 'size', 'center') .
434 <td align="center" title="scheduled">sc</td>
435 <td align="center" title="burned">bu</td>
439 my @color = (' bgcolor="#e0e0e0"', '');
442 # my $img_url = $Conf{CgiImageDirURL};
444 foreach my $backup ( getBackupsNotBurned($param) ) {
447 ,'<tr',$color[$i++%2],'>'
448 ,'<td class="fview">'
449 ,'<input type="checkbox" name="fcb',$backup->{id},'" value="',$backup->{id},'" onClick="sumiraj(this);">'
450 ,'<input type="hidden" id="fss',$backup->{id},'" value="',$backup->{size},'">'
452 ,'<td align="left">', $backup->{'filename'}, '</td>'
453 ,'<td align="center">', epoch_to_iso( $backup->{'date'} ), '</td>'
454 ,'<td align="center">', $backup->{'age'}, '</td>'
455 ,'<td align="right">', unit($backup->{'size'}), '</td>'
456 ,'<td align="center">', $backup->{scheduled}, '</td>'
457 ,'<td align="center">', $backup->{burned}, '</td>'
462 $retHTML .= "</table>";
465 <input type=submit name="scheduled" value="Show scheduled parts">
466 } unless $param->{scheduled};
468 $retHTML .= "</form>";
476 my $offset = $param->{'offset'};
477 my $hilite = $param->{'search_filename'};
481 my $start_t = time();
483 my ($results, $files);
484 if ($param->{'use_hest'} && length($hilite) > 0) {
485 ($results, $files) = getFilesHyperEstraier($param);
487 ($results, $files) = getFiles($param);
490 my $dur_t = time() - $start_t;
491 my $dur = sprintf("%0.4fs", $dur_t);
493 my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page);
497 <p style="color: red;">No results found...</p>
503 #$retHTML .= '<pre>' . Dumper($files) . '</pre>';
509 Found <b>$results files</b> showing <b>$from - $to</b> (took $dur)
511 <table style="fview" width="100%" border="0" cellpadding="2" cellspacing="0">
512 <tr class="fviewheader">
516 sub sort_header($$$$) {
517 my ($param, $display, $name, $align) = @_;
519 my ($sort_what, $sort_direction) = split(/_/,$param->{'sort'},2);
521 my $old_sort = $param->{'sort'};
523 my $html = qq{<td align="$align"};
526 if (lc($sort_what) eq lc($name)) {
527 my $direction = lc($sort_direction);
529 # swap direction or fallback to default
530 $direction =~ tr/ad/da/;
531 $direction = 'a' unless ($direction =~ /[ad]/);
533 $param->{'sort'} = $name . '_' . $direction;
534 $html .= ' style="border: 1px solid #808080;"';
536 # add unicode arrow for direction
538 $arrow .= $direction eq 'a' ? '▲'
539 : $direction eq 'd' ? '▼'
544 $param->{'sort'} = $name . '_a';
547 $html .= '><a href="' . page_uri($param) . '">' . $display . '</a>' . $arrow . '</td>';
548 $param->{'sort'} = $old_sort;
554 sort_header($param, 'Share', 'sname', 'center') .
555 sort_header($param, 'Type and Name', 'filepath', 'center') .
556 sort_header($param, '#', 'backupnum', 'center') .
557 sort_header($param, 'Size', 'size', 'center') .
558 sort_header($param, 'Date', 'date', 'center');
561 <td align="center">Media</td>
567 sub hilite_html($$) {
568 my ($html, $search) = @_;
569 $html =~ s#($search)#<b>$1</b>#gis;
573 sub restore_link($$$$$$) {
575 my $action = 'RestoreFile';
576 $action = 'browse' if (lc($type) eq 'dir');
577 return sprintf(qq{<a href="?action=%s&host=%s&num=%d&share=%s&dir=%s">%s</a>}, $action, @_);
583 sub check_archived($$$) {
584 my ($host, $share, $num) = @_;
586 if (my $html = $archived_cache{"$host $share $num"}) {
590 $sth_archived ||= $dbh->prepare(qq{
592 archive.dvd_nr, note,
593 count(archive_burned.copy) as copies
595 inner join archive_burned on archive_burned.archive_id = archive.id
596 inner join archive_backup_parts on archive.id = archive_backup_parts.archive_id
597 inner join backups on backups.id = archive_backup_parts.backup_id
598 inner join hosts on hosts.id = backups.hostid
599 inner join shares on shares.id = backups.shareid
600 where hosts.name = ? and shares.name = ? and backups.num = ?
601 group by archive.dvd_nr, note
606 $sth_archived->execute($host, $share, $num);
607 while (my $row = $sth_archived->fetchrow_hashref()) {
608 push @mediums, '<abbr title="' .
610 ' [' . $row->{'copies'} . ']' .
611 '">' .$row->{'dvd_nr'} .
615 my $html = join(", ",@mediums);
616 $archived_cache{"$host $share $num"} = $html;
620 my $i = $offset * $on_page;
622 foreach $file (@{ $files }) {
625 my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
626 $retHTML .= qq{<tr class="fviewborder">};
628 $retHTML .= qq{<td class="fviewborder">$i</td>};
631 qq{<td class="fviewborder" align="right">} . $file->{'hname'} . ':' . $file->{'sname'} . qq{</td>} .
632 qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.png" alt="$typeStr" align="middle"> } . hilite_html( $file->{'filepath'}, $hilite ) . qq{</td>} .
633 qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{</td>} .
634 qq{<td class="fviewborder" align="right">} . $file->{'size'} . qq{</td>} .
635 qq{<td class="fviewborder">} . epoch_to_iso( $file->{'date'} ) . qq{</td>} .
636 qq{<td class="fviewborder">} . check_archived( $file->{'hname'}, $file->{'sname'}, $file->{'backupnum'} ) . qq{</td>};
640 $retHTML .= "</table>";
642 # all variables which has to be transfered
643 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/) {
644 $retHTML .= qq{<INPUT TYPE="hidden" NAME="$n" VALUE="$In{$n}">\n};
648 my $max_page = int( $results / $on_page );
652 my $param = shift || die "no param?";
656 foreach my $k (keys %{ $param }) {
658 $uri .= $del . $k . '=' . ${EscURI( $param->{$k} )};
666 my ($param,$page,$display) = @_;
668 $param->{'offset'} = $page if (defined($page));
670 my $html = '<a href = "' . page_uri($param) . '">' . $display . '</a>';
673 $retHTML .= '<div style="text-align: center;">';
676 $retHTML .= page_link($param, $offset - 1, '<<') . ' ';
679 while ($page <= $max_page) {
680 if ($page == $offset) {
681 $retHTML .= $del . '<b>' . ($page + 1) . '</b>';
683 $retHTML .= $del . page_link($param, $page, $page + 1);
686 if ($page < $offset - $pager_pages && $page != 0) {
688 $page = $offset - $pager_pages;
690 } elsif ($page > $offset + $pager_pages && $page != $max_page) {
700 if ($offset < $max_page) {
701 $retHTML .= ' ' . page_link($param, $offset + 1, '>>');
704 $retHTML .= "</div>";
709 my @units = qw/b k M G/;
715 while ( ( $v / 10000 ) >= 1 ) {
721 return sprintf("%d%s", $v, $units[$o]);
722 } elsif ( $v == 0 ) {
725 return sprintf("%.1f%s", $v, $units[$o]);
734 <style type="text/css">
742 background-color: #E0F0E0;
743 border: 1px solid #00C000;
746 DIV#fixedBox, DIV#fixedBox INPUT, DIV#fixedBox TEXTAREA {
751 position: fixed !important;
752 left: 0.5em !important;
753 top: auto !important;
754 bottom: 1em !important;
755 width: 15% !important;
758 DIV#fixedBox INPUT[type=text], DIV#fixedBox TEXTAREA {
759 border: 1px solid #00C000;
767 DIV#fixedBox #submitBurner {
784 * HTML DIV#fixedBox {
788 #mContainer, #gradient, #mask, #progressIndicator {
794 vertical-align: middle;
798 #gradient, #mask, #progressIndicator {
802 border-color: #000000;
817 margin-bottom: 0.5em;
822 background-color: #FFFF00;
827 background-color: #FFFFFF;
832 background-color: transparent;
845 <script type="text/javascript">
850 function debug(msg) {
851 return; // Disable debugging
853 if (! debug_div) debug_div = document.getElementById('debug');
855 // this will create debug div if it doesn't exist.
857 debug_div = document.createElement('div');
858 if (document.body) document.body.appendChild(debug_div);
859 else debug_div = null;
862 debug_div.appendChild(document.createTextNode(msg));
863 debug_div.appendChild(document.createElement("br"));
868 var element_id_cache = Array();
870 function element_id(name,element) {
871 if (! element_id_cache[name]) {
872 element_id_cache[name] = self.document.getElementById(name);
874 return element_id_cache[name];
877 function checkAll(location) {
878 var f = element_id('forma') || null;
879 if (!f) return false;
881 var len = f.elements.length;
882 var check_all = element_id('allFiles');
883 var suma = check_all.checked ? (parseInt(f.elements['totalsize'].value) || 0) : 0;
885 for (var i = 0; i < len; i++) {
886 var e = f.elements[i];
887 if (e.name != 'all' && e.name.substr(0, 3) == 'fcb') {
888 if (check_all.checked) {
889 if (e.checked) continue;
890 var el = element_id("fss" + e.name.substr(3));
891 var size = parseInt(el.value) || 0;
892 debug('suma: '+suma+' size: '+size);
893 if ((suma + size) < media_size) {
907 function update_sum(suma, suma_disp) {
908 if (! suma_disp) suma_disp = suma;
909 suma_disp = Math.floor(suma_disp / 1024);
910 element_id('forma').elements['totalsize_kb'].value = suma_disp;
911 element_id('forma').elements['totalsize'].value = suma;
912 pbar_set(suma, media_size);
913 debug('total size: ' + suma);
916 function update_size(name, checked, suma) {
917 var size = parseInt( element_id("fss" + name).value);
925 debug('update_size('+name+','+checked+') suma: '+suma);
929 element_id("volumes").innerHTML = "This will take "+volumes+" mediums!";
930 element_id("volumes").style.display = 'block';
935 element_id("volumes").style.display = 'none';
942 function sumiraj(e) {
943 var suma = parseInt(element_id('forma').elements['totalsize'].value) || 0;
944 var len = element_id('forma').elements.length;
946 suma = update_size(e.name.substr(3), e.checked, suma);
947 if (suma < 0) suma = 0;
950 for (var i = 0; i < len; i++) {
951 var fel = element_id('forma').elements[i];
952 if (fel.name != 'all' && fel.checked && fel.name.substr(0,3) == 'fcb') {
953 suma = update_size(fel.name.substr(3), fel.checked, suma);
963 var _pbar_width = null;
964 var _pbar_warn = 10; // change color in last 10%
966 function pbar_reset() {
967 element_id("mask").style.left = "0px";
968 _pbar_width = element_id("mContainer").offsetWidth - 2;
969 element_id("mask").style.width = _pbar_width + "px";
970 element_id("mask").style.display = "block";
971 element_id("progressIndicator").style.zIndex = 10;
972 element_id("progressIndicator").innerHTML = "0";
975 function dec2hex(d) {
976 var hch = '0123456789ABCDEF';
978 var q = (d - a) / 16;
979 return hch.charAt(q) + hch.charAt(a);
982 function pbar_set(amount, max) {
983 debug('pbar_set('+amount+', '+max+')');
985 if (_pbar_width == null) {
986 var _mc = element_id("mContainer");
987 if (_pbar_width == null) _pbar_width = parseInt(_mc.offsetWidth ? (_mc.offsetWidth - 2) : 0) || null;
988 if (_pbar_width == null) _pbar_width = parseInt(_mc.clientWidth ? (_mc.clientWidth + 2) : 0) || null;
989 if (_pbar_width == null) _pbar_width = 0;
992 var pcnt = Math.floor(amount * 100 / max);
993 var p90 = 100 - _pbar_warn;
994 var pcol = pcnt - p90;
995 if (Math.round(pcnt) <= 100) {
996 if (pcol < 0) pcol = 0;
997 var e = element_id("submitBurner");
998 debug('enable_button');
1000 var a = e.getAttributeNode('disabled') || null;
1001 if (a) e.removeAttributeNode(a);
1003 debug('disable button');
1005 var e = element_id("submitBurner");
1006 if (!e.disabled) e.disabled = true;
1008 var col_g = Math.floor((_pbar_warn - pcol) * 255 / _pbar_warn);
1009 var col = '#FF' + dec2hex(col_g) + '00';
1011 //debug('pcol: '+pcol+' g:'+col_g+' _pbar_warn:'+ _pbar_warn + ' color: '+col);
1012 element_id("gradient").style.backgroundColor = col;
1014 element_id("progressIndicator").innerHTML = pcnt + '%';
1015 //element_id("progressIndicator").innerHTML = amount;
1017 element_id("mask").style.clip = 'rect(' + Array(
1019 element_id("mask").offsetWidth + 'px',
1020 element_id("mask").offsetHeight + 'px',
1021 Math.round(_pbar_width * amount / max) + 'px'
1025 if (!self.body) self.body = new Object();
1026 self.onload = self.document.onload = self.body.onload = function() {
1035 <input type="hidden" name="totalsize"/>
1036 Size: <input type="text" name="totalsize_kb" size="7" readonly="readonly" style="text-align:right;" value="0" /> kB
1038 <div id="mContainer">
1039 <div id="gradient"> </div>
1040 <div id="mask"> </div>
1041 <div id="progressIndicator">0%</div>
1045 <div id="volumes"> </div>
1048 <textarea name="note" cols="10" rows="5" id="note"></textarea>
1050 <input type="submit" id="submitBurner" value="Burn selected" name="submitBurner" />
1054 <div id="debug" style="float: right; width: 10em; border: 1px #ff0000 solid; background-color: #ffe0e0; -moz-opacity: 0.7;">