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/;
17 my $dsn = $Conf{SearchDSN};
18 my $db_user = $Conf{SearchUser} || '';
20 my $hest_index_path = $Conf{HyperEstraierIndex};
25 $dbh ||= DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
33 my $sth = $dbh->prepare(qq{
36 hosts.name || ':' || shares.name as share
38 JOIN hosts on hostid = hosts.id
42 push @ret, { 'id' => '', 'share' => '-'}; # dummy any
44 while ( my $row = $sth->fetchrow_hashref() ) {
51 my $t = shift || return;
52 my $iso = BackupPC::Lib::timeStamp(undef, $t);
53 $iso =~ s/\s/ /g;
57 sub dates_from_form($) {
58 my $param = shift || return;
60 sub mk_epoch_date($$) {
61 my ($name,$suffix) = @_;
63 my $yyyy = $param->{ $name . '_year_' . $suffix} || return undef;
64 my $mm .= $param->{ $name . '_month_' . $suffix} ||
65 ( $suffix eq 'from' ? 1 : 12);
66 my $dd .= $param->{ $name . '_day_' . $suffix} ||
67 ( $suffix eq 'from' ? 1 : 31);
73 my $h = my $m = my $s = 0;
74 if ($suffix eq 'to') {
80 my $dt = new DateTime(
88 print STDERR "mk_epoch_date($name,$suffix) [$yyyy-$mm-$dd] = " . $dt->ymd . " " . $dt->hms . "\n";
89 return $dt->epoch || 'NULL';
93 mk_epoch_date('search_backup', 'from'),
94 mk_epoch_date('search_backup', 'to'),
95 mk_epoch_date('search', 'from'),
96 mk_epoch_date('search', 'to'),
105 my $param = shift || return;
107 my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);
110 push @conditions, qq{ backups.date >= $backup_from } if ($backup_from);
111 push @conditions, qq{ backups.date <= $backup_to } if ($backup_to);
112 push @conditions, qq{ files.date >= $files_from } if ($files_from);
113 push @conditions, qq{ files.date <= $files_to } if ($files_to);
115 print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" and ",@conditions);
117 push( @conditions, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'});
118 push (@conditions, " upper(files.path) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'});
120 return join(" and ", @conditions);
127 my $offset = $param->{'offset'} || 0;
135 shares.name AS sname,
136 files.backupnum AS backupnum,
137 files.path AS filepath,
145 INNER JOIN shares ON files.shareID=shares.ID
146 INNER JOIN hosts ON hosts.ID = shares.hostID
147 INNER JOIN backups ON backups.num = files.backupnum and backups.hostID = hosts.ID AND backups.shareID = files.shareID
151 my $where = getWhere($param);
152 $sql_where = " WHERE ". $where if ($where);
160 my $sql_count = qq{ select count(files.id) $sql_from $sql_where };
161 my $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order };
163 my $sth = $dbh->prepare($sql_count);
165 my ($results) = $sth->fetchrow_array();
167 $sth = $dbh->prepare($sql_results);
168 $sth->execute( $offset );
170 if ($sth->rows != $results) {
171 my $bug = "$0 BUG: [[ $sql_count ]] = $results while [[ $sql_results ]] = " . $sth->rows;
173 print STDERR "$bug\n";
178 while (my $row = $sth->fetchrow_hashref()) {
183 return ($results, \@ret);
186 sub getHyperEstraier_url($) {
189 return unless $use_hest;
192 my ($index_path, $index_node_url);
194 if ($use_hest =~ m#^http://#) {
195 $index_node_url = $use_hest;
197 $index_path = $TopDir . '/' . $index_path;
198 $index_path =~ s#//#/#g;
200 return ($index_path, $index_node_url);
203 sub getFilesHyperEstraier($) {
206 my $offset = $param->{'offset'} || 0;
209 die "no index_path?" unless ($hest_index_path);
213 my ($index_path, $index_node_url) = getHyperEstraier_url($hest_index_path);
218 $db = HyperEstraier::Database->new();
219 $db->open($index_path, $HyperEstraier::ESTDBREADER);
220 } elsif ($index_node_url) {
221 $db ||= HyperEstraier::Node->new($index_node_url);
222 $db->set_auth('admin', 'admin');
224 die "BUG: unimplemented";
227 # create a search condition object
228 my $cond = HyperEstraier::Condition->new();
230 my $q = $param->{'search_filename'};
231 my $shareid = $param->{'search_share'};
233 if (length($q) > 0) {
235 $cond->add_attr("filepath ISTRINC $q");
238 # set the search phrase to the search condition object
239 $cond->set_phrase($q);
242 my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param);
244 $cond->add_attr("backup_date NUMGE $backup_from") if ($backup_from);
245 $cond->add_attr("backup_date NUMLE $backup_to") if ($backup_to);
247 $cond->add_attr("date NUMGE $files_from") if ($files_from);
248 $cond->add_attr("date NUMLE $files_to") if ($files_to);
250 $cond->add_attr("shareid NUMEQ $shareid") if ($shareid);
252 # $cond->set_max( $offset + $on_page );
253 $cond->set_options( $HyperEstraier::Condition::SURE );
254 $cond->set_order( 'date NUMA' );
256 # get the result of search
261 $result = $db->search($cond, 0);
262 $hits = $result->size;
263 } elsif ($index_node_url) {
264 $result = $db->search($cond, 0);
265 $hits = $result->doc_num;
267 die "BUG: unimplemented";
270 # for each document in result
271 for my $i ($offset .. ($offset + $on_page - 1)) {
272 last if ($i >= $hits);
276 my $id = $result->get($i);
277 $doc = $db->get_doc($id, 0);
278 } elsif ($index_node_url) {
279 $doc = $result->get_doc($i);
281 die "BUG: unimplemented";
285 foreach my $c (qw/fid hname sname backupnum fiilename filepath date type size/) {
286 $row->{$c} = $doc->attr($c);
291 return ($hits, \@res);
296 my ($host, $share, $backupnum) = @_;
297 my $ret = $Conf{GzipSchema};
300 $ret =~ s/\\h/$host/ge;
301 $ret =~ s/\\s/$share/ge;
302 $ret =~ s/\\n/$backupnum/ge;
312 my ($hostID, $backupNum) = @_;
317 SELECT hosts.name as host,
318 shares.name as share,
319 backups.num as backupnum
320 FROM hosts, backups, shares
321 WHERE shares.id=backups.shareid AND
322 hosts.id =backups.hostid AND
326 my $sth = $dbh->prepare($sql);
327 $sth->execute($hostID, $backupNum);
329 my $row = $sth->fetchrow_hashref();
331 my (undef,undef,undef,undef,undef,undef,undef,$ret,undef,undef,undef,undef,undef) =
332 stat( $Conf{InstallDir}.'/'.$Conf{GzipTempDir}.'/'.
333 getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'}));
338 sub getBackupsNotBurned() {
344 backups.hostID AS hostID,
346 shares.name AS share,
347 backups.num AS backupnum,
348 backups.type AS type,
349 backups.date AS date,
350 backups.size AS size,
352 backups.inc_size AS inc_size
354 INNER JOIN shares ON backups.shareID=shares.ID
355 INNER JOIN hosts ON backups.hostID = hosts.ID
356 LEFT OUTER JOIN archive_backup ON archive_backup.backup_id = backups.id
357 WHERE backups.inc_size > 0 AND backups.inc_deleted is false AND archive_backup.backup_id IS NULL
369 ORDER BY backups.date
371 my $sth = $dbh->prepare( $sql );
375 while ( my $row = $sth->fetchrow_hashref() ) {
376 $row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) );
377 $row->{'size'} = sprintf("%0.2f", $row->{'size'} / 1024 / 1024);
379 # do some cluster calculation (approximate) and convert to kB
380 $row->{'inc_size'} = int(($row->{'inc_size'} + 1023 ) / ( 2 * 1024 ) * 2);
387 sub displayBackupsGrid() {
390 <form id="forma" method="POST" action="}.$MyURL.q{?action=burn">
393 $retHTML .= <<'EOF3';
394 <style type="text/css">
402 background-color: #E0F0E0;
403 border: 1px solid #00C000;
406 DIV#fixedBox, DIV#fixedBox INPUT, DIV#fixedBox TEXTAREA {
411 position: fixed !important;
412 left: 0.5em !important;
413 top: auto !important;
414 bottom: 1em !important;
415 width: 15% !important;
418 DIV#fixedBox INPUT[type=text], DIV#fixedBox TEXTAREA {
419 border: 1px solid #00C000;
427 DIV#fixedBox #submitBurner {
444 * HTML DIV#fixedBox {
448 #mContainer, #gradient, #mask, #progressIndicator {
454 vertical-align: middle;
458 #gradient, #mask, #progressIndicator {
462 border-color: #000000;
477 margin-bottom: 0.5em;
482 background-color: #FFFF00;
487 background-color: #FFFFFF;
492 background-color: transparent;
496 <script type="text/javascript">
499 var debug_div = null;
502 # take maximum archive size from configuration
503 $retHTML .= 'var media_size = '. $Conf{MaxArchiveSize} .';';
505 $retHTML .= <<'EOF3';
507 function debug(msg) {
508 // return; // Disable debugging
510 if (! debug_div) debug_div = document.getElementById('debug');
512 // this will create debug div if it doesn't exist.
514 debug_div = document.createElement('div');
515 if (document.body) document.body.appendChild(debug_div);
516 else debug_div = null;
519 debug_div.appendChild(document.createTextNode(msg));
520 debug_div.appendChild(document.createElement("br"));
525 var element_id_cache = Array();
527 function element_id(name,element) {
528 if (! element_id_cache[name]) {
529 element_id_cache[name] = self.document.getElementById(name);
531 return element_id_cache[name];
534 function checkAll(location) {
535 var f = element_id('forma') || null;
536 if (!f) return false;
538 var len = f.elements.length;
539 var check_all = element_id('allFiles');
540 var suma = check_all.checked ? (parseInt(f.elements['totalsize'].value) || 0) : 0;
542 for (var i = 0; i < len; i++) {
543 var e = f.elements[i];
544 if (e.name != 'all' && e.name.substr(0, 3) == 'fcb') {
545 if (check_all.checked) {
546 if (e.checked) continue;
547 var el = element_id("fss" + e.name.substr(3));
548 var size = parseInt(el.value) || 0;
549 debug('suma: '+suma+' size: '+size);
550 if ((suma + size) < media_size) {
564 function update_sum(suma) {
565 element_id('forma').elements['totalsize'].value = suma;
566 pbar_set(suma, media_size);
567 debug('total size: ' + suma);
570 function sumiraj(e) {
571 var suma = parseInt(element_id('forma').elements['totalsize'].value) || 0;
572 var len = element_id('forma').elements.length;
574 var size = parseInt( element_id("fss" + e.name.substr(3)).value);
582 for (var i = 0; i < len; i++) {
583 var e = element_id('forma').elements[i];
584 if (e.name != 'all' && e.checked && e.name.substr(0,3) == 'fcb') {
585 var el = element_id("fss" + e.name.substr(3));
586 if (el && el.value) suma += parseInt(el.value) || 0;
596 var _pbar_width = null;
597 var _pbar_warn = 10; // change color in last 10%
599 function pbar_reset() {
600 element_id("mask").style.left = "0px";
601 _pbar_width = element_id("mContainer").offsetWidth - 2;
602 element_id("mask").style.width = _pbar_width + "px";
603 element_id("mask").style.display = "block";
604 element_id("progressIndicator").style.zIndex = 10;
605 element_id("progressIndicator").innerHTML = "0";
608 function dec2hex(d) {
609 var hch = '0123456789ABCDEF';
611 var q = (d - a) / 16;
612 return hch.charAt(q) + hch.charAt(a);
615 function pbar_set(amount, max) {
616 debug('pbar_set('+amount+', '+max+')');
618 if (_pbar_width == null) {
619 var _mc = element_id("mContainer");
620 if (_pbar_width == null) _pbar_width = parseInt(_mc.offsetWidth ? (_mc.offsetWidth - 2) : 0) || null;
621 if (_pbar_width == null) _pbar_width = parseInt(_mc.clientWidth ? (_mc.clientWidth + 2) : 0) || null;
622 if (_pbar_width == null) _pbar_width = 0;
625 var pcnt = Math.floor(amount * 100 / max);
626 var p90 = 100 - _pbar_warn;
627 var pcol = pcnt - p90;
628 if (Math.round(pcnt) <= 100) {
629 if (pcol < 0) pcol = 0;
630 var e = element_id("submitBurner");
631 debug('enable_button');
633 var a = e.getAttributeNode('disabled') || null;
634 if (a) e.removeAttributeNode(a);
636 debug('disable button');
638 var e = element_id("submitBurner");
639 if (!e.disabled) e.disabled = true;
641 var col_g = Math.floor((_pbar_warn - pcol) * 255 / _pbar_warn);
642 var col = '#FF' + dec2hex(col_g) + '00';
644 //debug('pcol: '+pcol+' g:'+col_g+' _pbar_warn:'+ _pbar_warn + ' color: '+col);
645 element_id("gradient").style.backgroundColor = col;
647 element_id("progressIndicator").innerHTML = pcnt + '%';
648 //element_id("progressIndicator").innerHTML = amount;
650 element_id("mask").style.clip = 'rect(' + Array(
652 element_id("mask").offsetWidth + 'px',
653 element_id("mask").offsetHeight + 'px',
654 Math.round(_pbar_width * amount / max) + 'px'
658 if (!self.body) self.body = new Object();
659 self.onload = self.document.onload = self.body.onload = function() {
668 Size: <input type="text" name="totalsize" size="7" readonly="readonly" style="text-align:right;" value="0" /> kB
670 <div id="mContainer">
671 <div id="gradient"> </div>
672 <div id="mask"> </div>
673 <div id="progressIndicator">0%</div>
678 <textarea name="note" cols="10" rows="5" id="note"></textarea>
680 <input type="submit" id="submitBurner" value="Burn selected" name="submitBurner" />
684 <div id="debug" style="float: right; width: 10em; border: 1px #ff0000 solid; background-color: #ffe0e0; -moz-opacity: 0.7;">
690 <input type="hidden" value="burn" name="action">
691 <input type="hidden" value="results" name="search_results">
692 <table style="fview" border="0" cellspacing="0" cellpadding="2">
693 <tr class="tableheader">
694 <td class="tableheader">
695 <input type="checkbox" name="allFiles" id="allFiles" onClick="checkAll('allFiles');">
697 <td align="center">Share</td>
698 <td align="center">Backup no</td>
699 <td align="center">Type</td>
700 <td align="center">date</td>
701 <td align="center">age/days</td>
702 <td align="center">size/MB</td>
703 <td align="center">gzip size/kB</td>
708 my @color = (' bgcolor="#e0e0e0"', '');
713 foreach my $backup ( getBackupsNotBurned() ) {
715 if ($host ne $backup->{'host'}) {
717 $host = $backup->{'host'};
721 my $checkbox_key = $backup->{'hostid'}. '_' .$backup->{'backupnum'} . '_' . $backup->{'id'};
724 '<tr' . $color[$i %2 ] . '>
727 if (($backup->{'inc_size'} || 0) > 0) {
729 <input type="checkbox" name="fcb' . $checkbox_key . '" value="' . $checkbox_key . '" onClick="sumiraj(this);">';
734 '<td align="right">' . $backup->{'host'} . ':' . $backup->{'share'} . '</td>' .
735 '<td align="center">' . $backup->{'backupnum'} . '</td>' .
736 '<td align="center">' . $backup->{'type'} . '</td>' .
737 '<td align="center">' . epoch_to_iso( $backup->{'date'} ) . '</td>' .
738 '<td align="center">' . $backup->{'age'} . '</td>' .
739 '<td align="right">' . $backup->{'size'} . '</td>' .
740 '<td align="right">' . $backup->{'inc_size'} .
741 '<input type="hidden" iD="fss'.$checkbox_key .'" value="'. $backup->{'inc_size'} .'"></td>' .
746 $retHTML .= "</table>";
747 $retHTML .= "</form>";
755 my $offset = $param->{'offset'};
756 my $hilite = $param->{'search_filename'};
760 my $start_t = time();
762 my ($results, $files);
763 if ($param->{'use_hest'} && length($hilite) > 0) {
764 ($results, $files) = getFilesHyperEstraier($param);
766 ($results, $files) = getFiles($param);
769 my $dur_t = time() - $start_t;
770 my $dur = sprintf("%0.4fs", $dur_t);
772 my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page);
776 <p style="color: red;">No results found...</p>
782 #$retHTML .= '<pre>' . Dumper($files) . '</pre>';
788 Found <b>$results files</b> showing <b>$from - $to</b> (took $dur)
790 <table style="fview" width="100%" border="0" cellpadding="2" cellspacing="0">
791 <tr class="fviewheader">
793 <td align="center">Share</td>
794 <td align="center">Type and Name</td>
795 <td align="center">#</td>
796 <td align="center">Size</td>
797 <td align="center">Date</td>
798 <td align="center">Media</td>
804 sub hilite_html($$) {
805 my ($html, $search) = @_;
806 $html =~ s#($search)#<b>$1</b>#gis;
810 sub restore_link($$$$$$) {
812 my $action = 'RestoreFile';
813 $action = 'browse' if (lc($type) eq 'dir');
814 return sprintf(qq{<a href="?action=%s&host=%s&num=%d&share=%s&dir=%s">%s</a>}, $action, @_);
817 my $i = $offset * $on_page;
819 foreach $file (@{ $files }) {
822 my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
823 $retHTML .= qq{<tr class="fviewborder">};
825 $retHTML .= qq{<td class="fviewborder">$i</td>};
828 qq{<td class="fviewborder" align="right">} . $file->{'hname'} . ':' . $file->{'sname'} . qq{</td>} .
829 qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" alt="$typeStr" align="middle"> } . hilite_html( $file->{'filepath'}, $hilite ) . qq{</td>} .
830 qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{</td>} .
831 qq{<td class="fviewborder" align="right">} . $file->{'size'} . qq{</td>} .
832 qq{<td class="fviewborder">} . epoch_to_iso( $file->{'date'} ) . qq{</td>} .
833 qq{<td class="fviewborder">} . '?' . qq{</td>};
837 $retHTML .= "</table>";
839 # all variables which has to be transfered
840 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/) {
841 $retHTML .= qq{<INPUT TYPE="hidden" NAME="$n" VALUE="$In{$n}">\n};
845 my $max_page = int( $results / $on_page );
849 my ($param,$page,$display) = @_;
851 $param->{'offset'} = $page;
853 my $html = '<a href = "' . $MyURL;
855 foreach my $k (keys %{ $param }) {
857 $html .= $del . $k . '=' . ${EscURI( $param->{$k} )};
861 $html .= '">' . $display . '</a>';
864 $retHTML .= '<div style="text-align: center;">';
867 $retHTML .= page_link($param, $offset - 1, '<<') . ' ';
870 while ($page <= $max_page) {
871 if ($page == $offset) {
872 $retHTML .= $del . '<b>' . ($page + 1) . '</b>';
874 $retHTML .= $del . page_link($param, $page, $page + 1);
877 if ($page < $offset - $pager_pages && $page != 0) {
879 $page = $offset - $pager_pages;
881 } elsif ($page > $offset + $pager_pages && $page != $max_page) {
891 if ($offset < $max_page) {
892 $retHTML .= ' ' . page_link($param, $offset + 1, '>>');
895 $retHTML .= "</div>";
900 sub dumpArchive2XML($$)
902 my ($arcID, $filename) = @_;
911 my $output = new IO::File(">$filename");
912 my $writer = new XML::Writer(OUTPUT=>$output, NEWLINES => 1);
916 # my $bpc = BackupPC::Lib->new(undef, undef, 1) || die;
917 # my %Conf = $bpc->Conf();
919 # my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
920 # my $user = $Conf{SearchUser} || '';
924 files.name AS filename,
925 files.path AS filepath,
926 files.date AS filedate,
927 files.type AS filetype,
928 files.size AS filesize
929 FROM files, backups, shares
930 WHERE files.backupnum=backups.num AND
931 files.shareid=shares.id AND
932 shares.hostid=backups.hostid AND
937 SELECT backups.id AS backupid,
938 hosts.name AS hostname,
939 backups.num AS backupnum,
940 backups.date AS backupdate,
941 backups.type AS backuptype,
942 shares.name AS sharename,
943 backups.size AS backupsize,
944 backups.inc_size AS inc_size,
945 backups.inc_deleted AS inc_deleted
946 FROM backups, archive_backup, hosts, shares
947 WHERE archive_backup.backup_id = backups.id
948 AND hosts.id=backups.hostid
949 AND shares.id=backups.shareid
950 AND archive_backup.archive_id = ?
953 $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 1 });
955 $sth = $dbh->prepare("SELECT dvd_nr, total_size, note, username, date FROM archive WHERE ID=?");
956 $sth->execute($arcID);
957 $row = $sth->fetchrow_hashref();
958 $writer->startTag("archive", "dvd_nr" => $row->{'dvd_nr'},
959 "total_size" => $row->{'total_size'},
960 "username" => $row->{'username'},
961 "date" => $row->{'date'}
965 $writer->startTag("note");
966 $writer->characters( $row->{'note'});
967 $writer->endTag("note");
968 $sth_backups = $dbh->prepare( $backups_sql );
969 $sth_backups->execute($arcID);
970 while ($row_backups = $sth_backups->fetchrow_hashref())
972 $writer->startTag("backup",
973 "host" => $row_backups->{'hostname'},
974 "num" => $row_backups->{'backupnum'},
975 "date" => $row_backups->{'backupdate'},
976 "type" => $row_backups->{'backuptype'},
977 "share"=> $row_backups->{'sharename'},
978 "size" => $row_backups->{'backupsize'},
979 "inc_size" => $row_backups->{'inc_size'},
980 "inc_deleted" => $row_backups->{'inc_deleted'}
983 $sth_files = $dbh->prepare(
986 $sth_files->execute($row_backups->{'backupid'});
987 while ($row_files = $sth_files->fetchrow_hashref())
989 $writer->startTag("file",
990 "path" => $row_files->{'filepath'},
991 "date" => $row_files->{'filedate'},
992 "type" => $row_files->{'filetype'},
993 "size" => $row_files->{'filesize'}
995 $writer->characters( $row_files->{'filename'});
996 $writer->endTag("file");
998 $writer->endTag("backup");
1001 $writer->endTag("archive");