--- trunk/lib/BackupPC/SearchLib.pm 2005/08/20 17:19:48 53 +++ trunk/lib/BackupPC/SearchLib.pm 2005/09/21 11:18:29 126 @@ -7,6 +7,7 @@ use DBI; use DateTime; use vars qw(%In $MyURL); +use Time::HiRes qw/time/; my $on_page = 100; my $pager_pages = 10; @@ -14,284 +15,545 @@ my $dsn = $Conf{SearchDSN}; my $db_user = $Conf{SearchUser} || ''; +my $hest_index_path = $Conf{HyperEstraierIndex}; + +my $dbh; + +sub get_dbh { + $dbh ||= DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); + return $dbh; +} + sub getUnits() { - my @ret = (); - my $tmp; - my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); - my $st = - $dbh->prepare( - " SELECT shares.ID AS ID, shares.share AS name FROM shares;"); - $st->execute(); - push (@ret, { 'ID' => '', 'name' => '-'}); - while ( $tmp = $st->fetchrow_hashref() ) { - push( @ret, { 'ID' => $tmp->{'ID'}, 'name' => $tmp->{'name'} } ); - } - $dbh->disconnect(); - return @ret; + my @ret; + + my $dbh = get_dbh(); + my $sth = $dbh->prepare(qq{ + SELECT + shares.id as id, + hosts.name || ':' || shares.name as share + FROM shares + JOIN hosts on hostid = hosts.id + ORDER BY share + } ); + $sth->execute(); + push @ret, { 'id' => '', 'share' => '-'}; # dummy any + + while ( my $row = $sth->fetchrow_hashref() ) { + push @ret, $row; + } + return @ret; } sub epoch_to_iso { my $t = shift || return; - my $dt = DateTime->from_epoch( epoch => $t ) || return; -print STDERR "$t == ",$dt->epoch,"\n"; - return $dt->ymd . ' ' . $dt->hms; + my $iso = BackupPC::Lib::timeStamp(undef, $t); + $iso =~ s/\s/ /g; + return $iso; } -sub getWhere($) { - my ($param) = @_; - my @conditions; +sub dates_from_form($) { + my $param = shift || return; sub mk_epoch_date($$) { my ($name,$suffix) = @_; - my $yyyy = $param->{ $name . '_year_' . $suffix} || return; + my $yyyy = $param->{ $name . '_year_' . $suffix} || return undef; my $mm .= $param->{ $name . '_month_' . $suffix} || ( $suffix eq 'from' ? 1 : 12); my $dd .= $param->{ $name . '_day_' . $suffix} || ( $suffix eq 'from' ? 1 : 31); + + $yyyy =~ s/\D//g; + $mm =~ s/\D//g; + $dd =~ s/\D//g; + my $dt = new DateTime( year => $yyyy, month => $mm, day => $dd ); + print STDERR "mk_epoch_date($name,$suffix) [$yyyy-$mm-$dd] = " . $dt->ymd . " " . $dt->hms . "\n"; return $dt->epoch || 'NULL'; } - my $backup_from = mk_epoch_date('search_backup', 'from'); + my @ret = ( + mk_epoch_date('search_backup', 'from'), + mk_epoch_date('search_backup', 'to'), + mk_epoch_date('search', 'from'), + mk_epoch_date('search', 'to'), + ); + + return @ret; + +} + + +sub getWhere($) { + my $param = shift || return; + + my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param); + + my @conditions; push @conditions, qq{ backups.date >= $backup_from } if ($backup_from); - my $backup_to = mk_epoch_date('search_backup', 'to'); push @conditions, qq{ backups.date <= $backup_to } if ($backup_to); - - my $files_from = mk_epoch_date('search', 'from'); push @conditions, qq{ files.date >= $files_from } if ($files_from); - my $files_to = mk_epoch_date('search', 'to'); push @conditions, qq{ files.date <= $files_to } if ($files_to); - print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:",join(" | ",@conditions); - - push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'}); + print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" | ",@conditions); - push (@conditions, " upper(files.name) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'}); + push( @conditions, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'}); + push (@conditions, " upper(files.path) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'}); - return ( - join(" and ", @conditions), - $files_from, $files_to, - $backup_from, $backup_to - ); + return join(" and ", @conditions); } -sub getFiles($$) { - my ($where, $offset) = @_; +sub getFiles($) { + my ($param) = @_; + + my $offset = $param->{'offset'} || 0; + $offset *= $on_page; - my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); + my $dbh = get_dbh(); my $sql_cols = qq{ files.id AS fid, hosts.name AS hname, shares.name AS sname, - shares.share AS sharename, - files.backupNum AS backupNum, - files.name AS filename, + files.backupnum AS backupnum, files.path AS filepath, - shares.share||files.fullpath AS networkPath, files.date AS date, - files.type AS filetype, - files.size AS size, - dvds.name AS dvd + files.type AS type, + files.size AS size }; my $sql_from = qq{ FROM files INNER JOIN shares ON files.shareID=shares.ID INNER JOIN hosts ON hosts.ID = shares.hostID - INNER JOIN backups ON backups.num = files.backupNum and backups.hostID = hosts.ID - LEFT JOIN dvds ON dvds.ID = files.dvdid + INNER JOIN backups ON backups.num = files.backupnum and backups.hostID = hosts.ID AND backups.shareID = files.shareID }; my $sql_where; + my $where = getWhere($param); $sql_where = " WHERE ". $where if ($where); my $sql_order = qq{ - ORDER BY files.id - LIMIT $on_page - OFFSET ? + ORDER BY files.date + LIMIT $on_page + OFFSET ? }; - $offset ||= 0; - $offset = ($offset * $on_page) + 1; + my $sql_count = qq{ select count(files.id) $sql_from $sql_where }; + my $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order }; - my $sth = $dbh->prepare(qq{ select count(files.id) $sql_from $sql_where }); + my $sth = $dbh->prepare($sql_count); $sth->execute(); - my ($results) = $sth->fetchrow_array(); - $sth = $dbh->prepare(qq{ select $sql_cols $sql_from $sql_where $sql_order }); + $sth = $dbh->prepare($sql_results); $sth->execute( $offset ); + if ($sth->rows != $results) { + my $bug = "$0 BUG: [[ $sql_count ]] = $results while [[ $sql_results ]] = " . $sth->rows; + $bug =~ s/\s+/ /gs; + print STDERR "$bug\n"; + } + my @ret; while (my $row = $sth->fetchrow_hashref()) { - push(@ret, { - 'hname' => $row->{'hname'}, - 'sname' => $row->{'sname'}, - 'sharename' => $row->{'sharename'}, - 'backupno' => $row->{'backupnum'}, - 'fname' => $row->{'filename'}, - 'fpath' => $row->{'filepath'}, - 'networkpath' => $row->{'networkpath'}, - 'date' => $row->{'date'}, - 'type' => $row->{'filetype'}, - 'size' => $row->{'size'}, - 'id' => $row->{'fid'}, - 'dvd' => $row->{'dvd'} - }); + push @ret, $row; } - + $sth->finish(); - $dbh->disconnect(); return ($results, \@ret); } +sub getHyperEstraier_url($) { + my ($use_hest) = @_; + + return unless $use_hest; + + use HyperEstraier; + my ($index_path, $index_node_url); + + if ($use_hest =~ m#^http://#) { + $index_node_url = $use_hest; + } else { + $index_path = $TopDir . '/' . $index_path; + $index_path =~ s#//#/#g; + } + return ($index_path, $index_node_url); +} + +sub getFilesHyperEstraier($) { + my ($param) = @_; + + my $offset = $param->{'offset'} || 0; + $offset *= $on_page; + + die "no index_path?" unless ($hest_index_path); + + use HyperEstraier; + + my ($index_path, $index_node_url) = getHyperEstraier_url($hest_index_path); + + # open the database + my $db; + if ($index_path) { + $db = HyperEstraier::Database->new(); + $db->open($index_path, $HyperEstraier::ESTDBREADER); + } elsif ($index_node_url) { + $db ||= HyperEstraier::Node->new($index_node_url); + $db->set_auth('admin', 'admin'); + } else { + die "BUG: unimplemented"; + } + + # create a search condition object + my $cond = HyperEstraier::Condition->new(); + + my $q = $param->{'search_filename'}; + my $shareid = $param->{'search_share'}; + + if (length($q) > 0) { + # exact match + $cond->add_attr("filepath ISTRINC $q"); + + $q =~ s/(.)/$1 /g; + # set the search phrase to the search condition object + $cond->set_phrase($q); + } + + my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param); + + $cond->add_attr("backup_date NUMGE $backup_from") if ($backup_from); + $cond->add_attr("backup_date NUMLE $backup_to") if ($backup_to); + + $cond->add_attr("date NUMGE $files_from") if ($files_from); + $cond->add_attr("date NUMLE $files_to") if ($files_to); + + $cond->add_attr("shareid NUMEQ $shareid") if ($shareid); + +# $cond->set_max( $offset + $on_page ); + $cond->set_options( $HyperEstraier::Condition::SURE ); + $cond->set_order( 'date NUMA' ); + + # get the result of search + my @res; + my ($result, $hits); + + if ($index_path) { + $result = $db->search($cond, 0); + $hits = $result->size; + } elsif ($index_node_url) { + $result = $db->search($cond, 0); + $hits = $result->doc_num; + } else { + die "BUG: unimplemented"; + } + + # for each document in result + for my $i ($offset .. ($offset + $on_page - 1)) { + last if ($i >= $hits); + + my $doc; + if ($index_path) { + my $id = $result->get($i); + $doc = $db->get_doc($id, 0); + } elsif ($index_node_url) { + $doc = $result->get_doc($i); + } else { + die "BUG: unimplemented"; + } + + my $row; + foreach my $c (qw/fid hname sname backupnum fiilename filepath date type size/) { + $row->{$c} = $doc->attr($c); + } + push @res, $row; + } + + return ($hits, \@res); +} + +sub getGzipName($$$) +{ + my ($host, $share, $backupnum) = @_; + my $ret = $Conf{GzipSchema}; + + $share =~ s/\//_/g; + $ret =~ s/\\h/$host/ge; + $ret =~ s/\\s/$share/ge; + $ret =~ s/\\n/$backupnum/ge; + + return $ret; + +} + sub getBackupsNotBurned() { - my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); - my $sql = q{ - SELECT - hosts.ID AS hostid, - min(hosts.name) AS host, - backups.num AS backupno, - min(backups.type) AS type, - min(backups.date) AS date - FROM backups, shares, files, hosts - WHERE - backups.num = files.backupNum AND - shares.ID = files.shareID AND - backups.hostID = shares.hostID AND - hosts.ID = backups.hostID AND - files.dvdid IS NULL - GROUP BY - backups.hostID, backups.num, hosts.id + my $dbh = get_dbh(); + + my $sql = q{ + SELECT + backups.hostID AS hostID, + hosts.name AS host, + shares.name AS share, + backups.id AS backupnum, + backups.type AS type, + backups.date AS date, + backups.size AS size + FROM backups + INNER JOIN shares ON backups.shareID=shares.ID + INNER JOIN hosts ON backups.hostID = hosts.ID + LEFT OUTER JOIN archive_backup ON archive_backup.backup_id = backups.id AND archive_backup.backup_id IS NULL + WHERE backups.size > 0 + GROUP BY + backups.hostID, + hosts.name, + shares.name, + backups.num, + backups.shareid, + backups.id, + backups.type, + backups.date, + backups.size + ORDER BY backups.date }; my $sth = $dbh->prepare( $sql ); my @ret; $sth->execute(); - while ( my $row = $sth->fetchrow_hashref() ) { - push(@ret, { - 'host' => $row->{'host'}, - 'hostid' => $row->{'hostid'}, - 'backupno' => $row->{'backupno'}, - 'type' => $row->{'type'}, - 'date' => $row->{'date'} - } - ); + while ( my $row = $sth->fetchrow_hashref() ) { + $row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) ); + $row->{'size'} = sprintf("%0.2f", $row->{'size'} / 1024 / 1024); + my (undef,undef,undef,undef,undef,undef,undef,$fs_size,undef,undef,undef,undef,undef) = + stat( $Conf{InstallDir}.'/'.$Conf{GzipTempDir}.'/'. + getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'})); + $row->{'fs_size'} = $fs_size; + push @ret, $row; } return @ret; } -sub displayBackupsGrid() - { - my $retHTML = ""; - my $addForm = 1; - - if ($addForm) { +sub displayBackupsGrid() { - $retHTML .= < + }; + + $retHTML .= < - -EOF3 - $retHTML .= q{
}; - $retHTML .= q{}; +var debug_div = null; + +function debug(msg) { +// return; // Disable debugging + + if (! debug_div) debug_div = document.getElementById('debug'); + + // this will create debug div if it doesn't exist. + if (! debug_div) { + debug_div = document.createElement('div'); + if (document.body) document.body.appendChild(debug_div); + else debug_div = null; + } + if (debug_div) { + debug_div.appendChild(document.createTextNode(msg)); + debug_div.appendChild(document.createElement("br")); } - $retHTML .= qq{}; +} - if ($addForm) { - $retHTML .= ""; + +var element_id_cache = Array(); + +function element_id(name,element) { + if (! element_id_cache[name]) { + element_id_cache[name] = self.document.getElementById(name); } - $retHTML .= qq{}; + return element_id_cache[name]; +} - my @backups = getBackupsNotBurned(); - my $backup; +function checkAll(location) { + var len = element_id('forma').elements.length; + var check_all = element_id('allFiles'); + + for (var i = 0; i < len; i++) { + + var e = element_id('forma').elements[i]; + if ((e.checked || !e.checked) && e.name != \'all\') { + if (check_all.checked) { + e.checked = true; + } else { + e.checked = false; + } + } + } - if ($addForm) { - $retHTML .= qq{}; + sumiraj(); +} + +function sumiraj(e) { + var suma = parseInt(element_id('forma').totalsize.value) || 0; + var len = element_id('forma').elements.length; + if (e) { + var size = parseInt( element_id("fss" + e.name.substr(3)).value ); + if (e.checked) { + suma += size; + } else { + suma -= size; + } + } else { + suma = 0; + for (var i = 0; i < len; i++) { + var e = element_id('forma').elements[i]; + if (e.name != \'all\' && e.checked && e.name.substr(0,3) == 'fcb') { + var el = element_id("fss" + e.name.substr(3)); + if (el && el.value) suma += parseInt(el.value) || 0; + } + } } + element_id('forma').totalsize.value = suma; + debug('total size: '+suma); + return suma; +} - foreach $backup(@backups) { +if (!self.body) self.body = new Object(); +self.onload = self.document.onload = self.body.onload = function() { + sumiraj(); +} + +//--> + +
+Total size: + +
+Note: + +
+ +
+EOF3 + $retHTML .= q{ + + +
HostBackup noTypedate
- -
+ + + + + + + + + + + + }; + + my @color = (' bgcolor="#e0e0e0"', ''); + + my $i = 0; + my $host = ''; + foreach my $backup ( getBackupsNotBurned() ) { + + if ($host ne $backup->{'host'}) { + $i++; + $host = $backup->{'host'}; + } my $ftype = ""; - - $retHTML .= ""; - if ($addForm) { - $retHTML .= qq{}; - } - - $retHTML .= '' . - '' . - '' . - ''; - } - $retHTML .= "
+ + ShareBackup noTypedateage/dayssize/MBgzip size
{'backupno'} . - qq{" value="} . $backup->{'hostid'}."_".$backup->{'backupno'} . - qq{">' . $backup->{'host'} . '' . $backup->{'backupno'} . '' . $backup->{'type'} . '' . epoch_to_iso( $backup->{'date'} ) . '' . - '
"; + $retHTML .= + ' + '; + # FIXME + #$backup->{'fs_size'} = int($backup->{'size'} * 1024); + if (($backup->{'fs_size'} || 0) > 0) { + $retHTML .= ' + '; + } + $retHTML .= + '' . + '' . $backup->{'host'} . ':' . $backup->{'share'} . '' . + '' . $backup->{'backupnum'} . '' . + '' . $backup->{'type'} . '' . + '' . epoch_to_iso( $backup->{'date'} ) . '' . + '' . $backup->{'age'} . '' . + '' . $backup->{'size'} . '' . + '' . $backup->{'fs_size'} . + '' . - if ($addForm) { - $retHTML .= ""; + "\n"; } + + $retHTML .= ""; + $retHTML .= ""; return $retHTML; } -sub displayGrid($$$$) { - my ($where, $addForm, $offset, $hilite) = @_; +sub displayGrid($) { + my ($param) = @_; + + my $offset = $param->{'offset'}; + my $hilite = $param->{'search_filename'}; + my $retHTML = ""; - if ($addForm) { - $retHTML .= qq{
}; - $retHTML.= qq{}; - $retHTML .= qq{}; + my $start_t = time(); + + my ($results, $files); + if ($param->{'use_hest'} && length($hilite) > 0) { + ($results, $files) = getFilesHyperEstraier($param); + } else { + ($results, $files) = getFiles($param); } - my ($results, $files) = getFiles($where, $offset); + my $dur_t = time() - $start_t; + my $dur = sprintf("%0.4fs", $dur_t); my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page); + if ($results <= 0) { + $retHTML .= qq{ +

No results found...

+ }; + return $retHTML; + } else { + # DEBUG + #use Data::Dumper; + #$retHTML .= '
' . Dumper($files) . '
'; + } + + $retHTML .= qq{ -
Found $results files, showing $from - $to - - - - - - - - - +
+ Found $results files showing $from - $to (took $dur) +
+
ShareNameType#SizeDateMedia
+ + + + + + + + }; @@ -310,21 +572,23 @@ return sprintf(qq{%s}, $action, @_); } + my $i = $offset * $on_page; + foreach $file (@{ $files }) { + $i++; + my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'}); - $retHTML .= ""; + $retHTML .= qq{}; - foreach my $v (( - $file->{'sharename'}, - qq{ } . hilite_html( $file->{'fpath'}, $hilite ), - $typeStr, - restore_link( $typeStr, $file->{'hname'}, $file->{'backupno'}, $file->{'sname'}, $file->{'fpath'}, $file->{'backupno'} ), - $file->{'size'}, - epoch_to_iso( $file->{'date'} ), - $file->{'dvd'} - )) { - $retHTML .= qq{}; - } + $retHTML .= qq{}; + + $retHTML .= + qq{} . + qq{} . + qq{} . + qq{} . + qq{} . + qq{}; $retHTML .= ""; } @@ -339,19 +603,33 @@ my $max_page = int( $results / $on_page ); my $page = 0; - my $link_fmt = '%s'; + sub page_link($$$) { + my ($param,$page,$display) = @_; + + $param->{'offset'} = $page; + + my $html = '' . $display . ''; + } $retHTML .= '
'; if ($offset > 0) { - $retHTML .= sprintf($link_fmt, $offset - 1, '<<') . ' '; + $retHTML .= page_link($param, $offset - 1, '<<') . ' '; } while ($page <= $max_page) { if ($page == $offset) { $retHTML .= $del . '' . ($page + 1) . ''; } else { - $retHTML .= $del . sprintf($link_fmt, $page, $page + 1); + $retHTML .= $del . page_link($param, $page, $page + 1); } if ($page < $offset - $pager_pages && $page != 0) { @@ -369,13 +647,11 @@ } if ($offset < $max_page) { - $retHTML .= ' ' . sprintf($link_fmt, $offset + 1, '>>'); + $retHTML .= ' ' . page_link($param, $offset + 1, '>>'); } $retHTML .= "
"; - $retHTML .= "" if ($addForm); - return $retHTML; }
ShareType and Name#SizeDateMedia
$v$i} . $file->{'hname'} . ':' . $file->{'sname'} . qq{$typeStr } . hilite_html( $file->{'filepath'}, $hilite ) . qq{} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{} . $file->{'size'} . qq{} . epoch_to_iso( $file->{'date'} ) . qq{} . '?' . qq{