From: Dobrica Pavlinusic Date: Sun, 9 Jan 2011 14:35:15 +0000 (+0000) Subject: rename BackupPC::SearchLib -> BackupPC::Search X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=commitdiff_plain;h=be11cc4fd68cb4f99554d1d26233d52a854ed7f4 rename BackupPC::SearchLib -> BackupPC::Search --- diff --git a/lib/BackupPC/Search.pm b/lib/BackupPC/Search.pm new file mode 100644 index 0000000..8fe2541 --- /dev/null +++ b/lib/BackupPC/Search.pm @@ -0,0 +1,1110 @@ +#!/usr/bin/perl +package BackupPC::SearchLib; + +use strict; +use BackupPC::CGI::Lib qw(:all); +use BackupPC::Attrib qw(:all); +use DBI; +use DateTime; +use vars qw(%In $MyURL); +use Time::HiRes qw/time/; +use XML::Writer; +use IO::File; +use BackupPC::Search::Estraier; + +my $on_page = 100; +my $pager_pages = 10; + +my $dsn = $Conf{SearchDSN}; +my $db_user = $Conf{SearchUser} || ''; + +my $hest_node_url = $Conf{HyperEstraierIndex}; + +my $dbh; + + + +sub get_dbh { + $dbh ||= DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); + return $dbh; +} + +sub getUnits() { + 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 $iso = BackupPC::Lib::timeStamp(undef, $t); + $iso =~ s/\s/ /g; + return $iso; +} + +sub dates_from_form($) { + my $param = shift || return; + + sub mk_epoch_date($$) { + my ($name,$suffix) = @_; + + 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 $h = my $m = my $s = 0; + if ($suffix eq 'to') { + $h = 23; + $m = 59; + $s = 59; + } + + my $dt = new DateTime( + year => $yyyy, + month => $mm, + day => $dd, + hour => $h, + minute => $m, + second => $s, + ); + print STDERR "mk_epoch_date($name,$suffix) [$yyyy-$mm-$dd] = " . $dt->ymd . " " . $dt->hms . "\n"; + return $dt->epoch || 'NULL'; + } + + 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); + push @conditions, qq{ backups.date <= $backup_to } if ($backup_to); + push @conditions, qq{ files.date >= $files_from } if ($files_from); + push @conditions, qq{ files.date <= $files_to } if ($files_to); + + print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" and ",@conditions); + + 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'}); + + if ( $param->{burned} ) { + my $is_what = 'is null'; + $is_what = '= 1' if ($param->{burned} eq 'burned'); + push @conditions, "archive_burned.part $is_what"; + push @conditions, "archive_burned.copy $is_what"; + } + + return join(" and ", @conditions); +} + +my $sort_def = { + search => { + default => 'date_a', + sql => { + share_d => 'shares.name DESC', + share_a => 'shares.name ASC', + path_d => 'files.path DESC', + path_a => 'files.path ASC', + num_d => 'files.backupnum DESC', + num_a => 'files.backupnum ASC', + size_d => 'files.size DESC', + size_a => 'files.size ASC', + date_d => 'files.date DESC', + date_a => 'files.date ASC', + }, + }, burn => { + default => 'date_a', + sql => { + share_d => 'host DESC, share DESC', + share_a => 'host ASC, share ASC', + num_d => 'backupnum DESC', + num_a => 'backupnum ASC', + date_d => 'date DESC', + date_a => 'date ASC', + age_d => 'age DESC', + age_a => 'age ASC', + size_d => 'size DESC', + size_a => 'size ASC', + incsize_d => 'inc_size DESC', + incsize_a => 'inc_size ASC', + } + } +}; + +sub getSort($$$) { + my ($part,$type, $sort_order) = @_; + + die "unknown part: $part" unless ($sort_def->{$part}); + die "unknown type: $type" unless ($sort_def->{$part}->{$type}); + + $sort_order ||= $sort_def->{$part}->{'default'}; + + if (my $ret = $sort_def->{$part}->{$type}->{$sort_order}) { + return $ret; + } else { + # fallback to default sort order + return $sort_def->{$part}->{$type}->{ $sort_def->{$part}->{'default'} }; + } +} + +sub getFiles($) { + my ($param) = @_; + + my $offset = $param->{'offset'} || 0; + $offset *= $on_page; + + my $dbh = get_dbh(); + + my $sql_cols = qq{ + files.id AS fid, + hosts.name AS hname, + shares.name AS sname, + files.backupnum AS backupnum, + files.path AS filepath, + files.date AS date, + 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 AND backups.shareID = files.shareID + }; + + my $sql_where; + my $where = getWhere($param); + $sql_where = " WHERE ". $where if ($where); + + # do we have to add tables for burned media? + if ( $param->{burned} ) { + $sql_from .= qq{ + LEFT OUTER JOIN archive_backup on archive_backup.backup_id = backups.id + LEFT OUTER JOIN archive_burned on archive_burned.archive_id = archive_backup.archive_id + }; + } + + my $order = getSort('search', 'sql', $param->{'sort'}); + + my $sql_order = qq{ + ORDER BY $order + LIMIT $on_page + OFFSET ? + }; + + 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($sql_count); + $sth->execute(); + my ($results) = $sth->fetchrow_array(); + + $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, $row; + } + + $sth->finish(); + return ($results, \@ret); +} + +sub getFilesHyperEstraier($) { + my ($param) = @_; + + my $offset = $param->{'offset'} || 0; + $offset *= $on_page; + + my $q = $param->{'search_filename'}; + my $shareid = $param->{'search_share'}; + my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param); + + return BackupPC::Search::Estraier->new( $hest_node_url )->search( + $offset, $on_page, $param->{sort}, + $q, $shareid, $backup_from, $backup_to, $files_from, $files_to + ); + +} + +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; + + $ret =~ s/__+/_/g; + + return $ret; + +} + +sub get_tgz_size_by_name($) { + my $name = shift; + + my $tgz = $Conf{InstallDir}.'/'.$Conf{GzipTempDir}.'/'.$name; + my $size = -1; + + my $Dir = $Conf{InstallDir}."/data/log"; + $|=1; + if (-f "${tgz}.tar.gz") { + $size = (stat("${tgz}.tar.gz"))[7]; + } elsif (-d $tgz) { + opendir(my $dir, $tgz) || die "can't opendir $tgz: $!"; + my @parts = grep { !/^\./ && !/md5/ && -f "$tgz/$_" } readdir($dir); + $size = 0; + foreach my $part (@parts) { + my $currSize = (stat("$tgz/$part"))[7]; + $size += (stat("$tgz/$part"))[7] || die "can't stat $tgz/$part: $!"; + } + + closedir $dir; + } else { + return -1; + } + + return $size; +} + +sub getGzipSizeFromBackupID($) { + my ($backupID) = @_; + my $dbh = get_dbh(); + my $sql = q{ + SELECT hosts.name as host, + shares.name as share, + backups.num as backupnum + FROM hosts, backups, shares + WHERE shares.id=backups.shareid AND + hosts.id =backups.hostid AND + backups.id = ? + }; + my $sth = $dbh->prepare($sql); + $sth->execute($backupID); + my $row = $sth->fetchrow_hashref(); + + return get_tgz_size_by_name( + getGzipName($row->{'host'}, $row->{share}, $row->{backupnum}) + ); +} + +sub getGzipSize($$) +{ + my ($hostID, $backupNum) = @_; + my $sql; + my $dbh = get_dbh(); + + $sql = q{ + SELECT hosts.name as host, + shares.name as share, + backups.num as backupnum + FROM hosts, backups, shares + WHERE shares.id=backups.shareid AND + hosts.id =backups.hostid AND + hosts.id=? AND + backups.num=? + }; + my $sth = $dbh->prepare($sql); + $sth->execute($hostID, $backupNum); + + my $row = $sth->fetchrow_hashref(); + + return get_tgz_size_by_name( + getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'}) + ); +} + +sub getVolumes($) { + my $id = shift; + + my $max_archive_size = $Conf{MaxArchiveSize} || die "no MaxArchiveSize"; + + my $sth = $dbh->prepare(qq{ + select + size + from backup_parts + where backup_id = ? + order by part_nr asc + }); + + $sth->execute($id); + + my $cumulative_size = 0; + my $volumes = 1; + + while(my ($size) = $sth->fetchrow_array) { + if ($cumulative_size + $size > $max_archive_size) { + $volumes++; + $cumulative_size = $size; + } else { + $cumulative_size += $size; + } + } + + return ($volumes,$cumulative_size); +} + +sub getBackupsNotBurned($) { + + my $param = shift; + my $dbh = get_dbh(); + + my $order = getSort('burn', 'sql', $param->{'sort'}); + +print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql order: $order\n"; + + my $sql = qq{ + SELECT + backups.hostID AS hostID, + hosts.name AS host, + shares.name AS share, + backups.num AS backupnum, + backups.type AS type, + backups.date AS date, + date_part('epoch',now()) - backups.date as age, + backups.size AS size, + backups.id AS id, + backups.inc_size AS inc_size, + backups.parts AS parts + 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 + WHERE backups.inc_size > 0 AND backups.size > 0 AND backups.inc_deleted is false AND archive_backup.backup_id IS NULL AND backups.parts > 0 + GROUP BY + backups.hostID, + hosts.name, + shares.name, + backups.num, + backups.shareid, + backups.id, + backups.type, + backups.date, + backups.size, + backups.inc_size, + backups.parts + ORDER BY $order + }; + my $sth = $dbh->prepare( $sql ); + my @ret; + $sth->execute(); + + while ( my $row = $sth->fetchrow_hashref() ) { + $row->{'age'} = sprintf("%0.1f", ( $row->{'age'} / 86400 ) ); + #$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) ); + + my $max_archive_size = $Conf{MaxArchiveSize} || die "no MaxArchiveSize"; + if ($row->{size} > $max_archive_size) { + ($row->{volumes}, $row->{inc_size_calc}) = getVolumes($row->{id}); + } + + $row->{size} = sprintf("%0.2f", $row->{size} / 1024 / 1024); + + # do some cluster calculation (approximate) + $row->{inc_size} = int(( ($row->{inc_size} + 1023 ) / 2 ) * 2); + $row->{inc_size_calc} ||= $row->{inc_size}; + push @ret, $row; + } + + return @ret; +} + +sub displayBackupsGrid($) { + + my $param = shift; + + my $max_archive_size = $Conf{MaxArchiveSize} || die "no MaxArchiveSize"; + my $max_archive_file_size = $Conf{MaxArchiveFileSize} || die "no MaxFileInSize"; + + my $retHTML .= q{ +
+ }; + + $retHTML .= <<'EOF3'; + + +
+ + +Size: kB + +
+
 
+
 
+
0%
+
+
+ +
 
+ +Note: + + + + +
+ +EOF3 + $retHTML .= q{ + + + + + + } . + sort_header($param, 'Share', 'share', 'center') . + sort_header($param, '#', 'num', 'center') . + qq{ + + } . + sort_header($param, 'Date', 'date', 'center') . + sort_header($param, 'Age/days', 'age', 'center') . + sort_header($param, 'Size/Mb', 'size', 'center') . + sort_header($param, 'gzip size/Kb', 'incsize', 'center') . + qq{ + + }; + + my @color = (' bgcolor="#e0e0e0"', ''); + + my $i = 0; + my $host = ''; + + foreach my $backup ( getBackupsNotBurned($param) ) { + + if ($host ne $backup->{'host'}) { + $i++; + $host = $backup->{'host'}; + } + my $ftype = ""; + + my $checkbox_key = $backup->{'hostid'}. '_' .$backup->{'backupnum'} . '_' . $backup->{'id'}; + + $retHTML .= + ' + ' . + '' . + '' . + '' . + '' . + '' . + '' . + '' . + '' . + '' . + + "\n"; + } + + $retHTML .= "
+ + Typemedias
'; + + if (($backup->{'inc_size'} || 0) > 0) { + $retHTML .= ' + '; + } + + my $img_url = $Conf{CgiImageDirURL}; + + $retHTML .= + '' . $backup->{'host'} . ':' . $backup->{'share'} . '' . $backup->{'backupnum'} . '' . $backup->{'type'} . '' . epoch_to_iso( $backup->{'date'} ) . '' . $backup->{'age'} . '' . $backup->{'size'} . '' . sprintf("%0.1f", $backup->{'inc_size'} / 1024 ) . + '' . ( qq{media} x $backup->{volumes} ) . '
"; + $retHTML .= "
"; + + return $retHTML; +} + +sub displayGrid($) { + my ($param) = @_; + + my $offset = $param->{'offset'}; + my $hilite = $param->{'search_filename'}; + + my $retHTML = ""; + + my $start_t = time(); + + my ($results, $files); + if ($param->{'use_hest'} && length($hilite) > 0) { + ($results, $files) = getFilesHyperEstraier($param); + } else { + ($results, $files) = getFiles($param); + } + + 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 (took $dur) +
+ + + + }; + + sub sort_header($$$$) { + my ($param, $display, $name, $align) = @_; + + my ($sort_what, $sort_direction) = split(/_/,$param->{'sort'},2); + + my $old_sort = $param->{'sort'}; + + my $html = qq{'; + $param->{'sort'} = $old_sort; + + return $html; + } + + $retHTML .= + sort_header($param, 'Share', 'share', 'center') . + sort_header($param, 'Type and Name', 'path', 'center') . + sort_header($param, '#', 'num', 'center') . + sort_header($param, 'Size', 'size', 'center') . + sort_header($param, 'Date', 'date', 'center'); + + $retHTML .= qq{ + + + }; + + my $file; + + sub hilite_html($$) { + my ($html, $search) = @_; + $html =~ s#($search)#$1#gis; + return $html; + } + + sub restore_link($$$$$$) { + my $type = shift; + my $action = 'RestoreFile'; + $action = 'browse' if (lc($type) eq 'dir'); + return sprintf(qq{%s}, $action, @_); + } + + my $sth_archived; + my %archived_cache; + + sub check_archived($$$) { + my ($host, $share, $num) = @_; + + if (my $html = $archived_cache{"$host $share $num"}) { + return $html; + } + + $sth_archived ||= $dbh->prepare(qq{ + select + dvd_nr, note, + count(archive_burned.copy) as copies + from archive + inner join archive_burned on archive_burned.archive_id = archive.id + inner join archive_backup on archive.id = archive_backup.archive_id + inner join backups on backups.id = archive_backup.backup_id + inner join hosts on hosts.id = backups.hostid + inner join shares on shares.id = backups.shareid + where hosts.name = ? and shares.name = ? and backups.num = ? + group by dvd_nr, note + }); + + my @mediums; + + $sth_archived->execute($host, $share, $num); + while (my $row = $sth_archived->fetchrow_hashref()) { + push @mediums, '' .$row->{'dvd_nr'} . + ''; + } + + my $html = join(", ",@mediums); + $archived_cache{"$host $share $num"} = $html; + return $html; + } + + my $i = $offset * $on_page; + + foreach $file (@{ $files }) { + $i++; + + my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'}); + $retHTML .= qq{}; + + $retHTML .= qq{}; + + $retHTML .= + qq{} . + qq{} . + qq{} . + qq{} . + qq{} . + qq{}; + + $retHTML .= ""; + } + $retHTML .= "
{'sort'} = $name . '_' . $direction; + $html .= ' style="border: 1px solid #808080;"'; + + # add unicode arrow for direction + $arrow .= ' '; + $arrow .= $direction eq 'a' ? '▲' + : $direction eq 'd' ? '▼' + : '' + ; + + } else { + $param->{'sort'} = $name . '_a'; + } + + $html .= '>' . $display . '' . $arrow . 'Media
$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{} . check_archived( $file->{'hname'}, $file->{'sname'}, $file->{'backupnum'} ) . qq{
"; + + # all variables which has to be transfered + 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/) { + $retHTML .= qq{\n}; + } + + my $del = ''; + my $max_page = int( $results / $on_page ); + my $page = 0; + + sub page_uri($) { + my $param = shift || die "no param?"; + + my $uri = $MyURL; + my $del = '?'; + foreach my $k (keys %{ $param }) { + if ($param->{$k}) { + $uri .= $del . $k . '=' . ${EscURI( $param->{$k} )}; + $del = '&'; + } + } + return $uri; + } + + sub page_link($$$) { + my ($param,$page,$display) = @_; + + $param->{'offset'} = $page if (defined($page)); + + my $html = '' . $display . ''; + } + + $retHTML .= '
'; + + if ($offset > 0) { + $retHTML .= page_link($param, $offset - 1, '<<') . ' '; + } + + while ($page <= $max_page) { + if ($page == $offset) { + $retHTML .= $del . '' . ($page + 1) . ''; + } else { + $retHTML .= $del . page_link($param, $page, $page + 1); + } + + if ($page < $offset - $pager_pages && $page != 0) { + $retHTML .= " ... "; + $page = $offset - $pager_pages; + $del = ''; + } elsif ($page > $offset + $pager_pages && $page != $max_page) { + $retHTML .= " ... "; + $page = $max_page; + $del = ''; + } else { + $del = ' | '; + $page++; + } + } + + if ($offset < $max_page) { + $retHTML .= ' ' . page_link($param, $offset + 1, '>>'); + } + + $retHTML .= "
"; + + return $retHTML; +} + +1; diff --git a/lib/BackupPC/SearchLib.pm b/lib/BackupPC/SearchLib.pm deleted file mode 100644 index 8fe2541..0000000 --- a/lib/BackupPC/SearchLib.pm +++ /dev/null @@ -1,1110 +0,0 @@ -#!/usr/bin/perl -package BackupPC::SearchLib; - -use strict; -use BackupPC::CGI::Lib qw(:all); -use BackupPC::Attrib qw(:all); -use DBI; -use DateTime; -use vars qw(%In $MyURL); -use Time::HiRes qw/time/; -use XML::Writer; -use IO::File; -use BackupPC::Search::Estraier; - -my $on_page = 100; -my $pager_pages = 10; - -my $dsn = $Conf{SearchDSN}; -my $db_user = $Conf{SearchUser} || ''; - -my $hest_node_url = $Conf{HyperEstraierIndex}; - -my $dbh; - - - -sub get_dbh { - $dbh ||= DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); - return $dbh; -} - -sub getUnits() { - 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 $iso = BackupPC::Lib::timeStamp(undef, $t); - $iso =~ s/\s/ /g; - return $iso; -} - -sub dates_from_form($) { - my $param = shift || return; - - sub mk_epoch_date($$) { - my ($name,$suffix) = @_; - - 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 $h = my $m = my $s = 0; - if ($suffix eq 'to') { - $h = 23; - $m = 59; - $s = 59; - } - - my $dt = new DateTime( - year => $yyyy, - month => $mm, - day => $dd, - hour => $h, - minute => $m, - second => $s, - ); - print STDERR "mk_epoch_date($name,$suffix) [$yyyy-$mm-$dd] = " . $dt->ymd . " " . $dt->hms . "\n"; - return $dt->epoch || 'NULL'; - } - - 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); - push @conditions, qq{ backups.date <= $backup_to } if ($backup_to); - push @conditions, qq{ files.date >= $files_from } if ($files_from); - push @conditions, qq{ files.date <= $files_to } if ($files_to); - - print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" and ",@conditions); - - 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'}); - - if ( $param->{burned} ) { - my $is_what = 'is null'; - $is_what = '= 1' if ($param->{burned} eq 'burned'); - push @conditions, "archive_burned.part $is_what"; - push @conditions, "archive_burned.copy $is_what"; - } - - return join(" and ", @conditions); -} - -my $sort_def = { - search => { - default => 'date_a', - sql => { - share_d => 'shares.name DESC', - share_a => 'shares.name ASC', - path_d => 'files.path DESC', - path_a => 'files.path ASC', - num_d => 'files.backupnum DESC', - num_a => 'files.backupnum ASC', - size_d => 'files.size DESC', - size_a => 'files.size ASC', - date_d => 'files.date DESC', - date_a => 'files.date ASC', - }, - }, burn => { - default => 'date_a', - sql => { - share_d => 'host DESC, share DESC', - share_a => 'host ASC, share ASC', - num_d => 'backupnum DESC', - num_a => 'backupnum ASC', - date_d => 'date DESC', - date_a => 'date ASC', - age_d => 'age DESC', - age_a => 'age ASC', - size_d => 'size DESC', - size_a => 'size ASC', - incsize_d => 'inc_size DESC', - incsize_a => 'inc_size ASC', - } - } -}; - -sub getSort($$$) { - my ($part,$type, $sort_order) = @_; - - die "unknown part: $part" unless ($sort_def->{$part}); - die "unknown type: $type" unless ($sort_def->{$part}->{$type}); - - $sort_order ||= $sort_def->{$part}->{'default'}; - - if (my $ret = $sort_def->{$part}->{$type}->{$sort_order}) { - return $ret; - } else { - # fallback to default sort order - return $sort_def->{$part}->{$type}->{ $sort_def->{$part}->{'default'} }; - } -} - -sub getFiles($) { - my ($param) = @_; - - my $offset = $param->{'offset'} || 0; - $offset *= $on_page; - - my $dbh = get_dbh(); - - my $sql_cols = qq{ - files.id AS fid, - hosts.name AS hname, - shares.name AS sname, - files.backupnum AS backupnum, - files.path AS filepath, - files.date AS date, - 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 AND backups.shareID = files.shareID - }; - - my $sql_where; - my $where = getWhere($param); - $sql_where = " WHERE ". $where if ($where); - - # do we have to add tables for burned media? - if ( $param->{burned} ) { - $sql_from .= qq{ - LEFT OUTER JOIN archive_backup on archive_backup.backup_id = backups.id - LEFT OUTER JOIN archive_burned on archive_burned.archive_id = archive_backup.archive_id - }; - } - - my $order = getSort('search', 'sql', $param->{'sort'}); - - my $sql_order = qq{ - ORDER BY $order - LIMIT $on_page - OFFSET ? - }; - - 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($sql_count); - $sth->execute(); - my ($results) = $sth->fetchrow_array(); - - $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, $row; - } - - $sth->finish(); - return ($results, \@ret); -} - -sub getFilesHyperEstraier($) { - my ($param) = @_; - - my $offset = $param->{'offset'} || 0; - $offset *= $on_page; - - my $q = $param->{'search_filename'}; - my $shareid = $param->{'search_share'}; - my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param); - - return BackupPC::Search::Estraier->new( $hest_node_url )->search( - $offset, $on_page, $param->{sort}, - $q, $shareid, $backup_from, $backup_to, $files_from, $files_to - ); - -} - -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; - - $ret =~ s/__+/_/g; - - return $ret; - -} - -sub get_tgz_size_by_name($) { - my $name = shift; - - my $tgz = $Conf{InstallDir}.'/'.$Conf{GzipTempDir}.'/'.$name; - my $size = -1; - - my $Dir = $Conf{InstallDir}."/data/log"; - $|=1; - if (-f "${tgz}.tar.gz") { - $size = (stat("${tgz}.tar.gz"))[7]; - } elsif (-d $tgz) { - opendir(my $dir, $tgz) || die "can't opendir $tgz: $!"; - my @parts = grep { !/^\./ && !/md5/ && -f "$tgz/$_" } readdir($dir); - $size = 0; - foreach my $part (@parts) { - my $currSize = (stat("$tgz/$part"))[7]; - $size += (stat("$tgz/$part"))[7] || die "can't stat $tgz/$part: $!"; - } - - closedir $dir; - } else { - return -1; - } - - return $size; -} - -sub getGzipSizeFromBackupID($) { - my ($backupID) = @_; - my $dbh = get_dbh(); - my $sql = q{ - SELECT hosts.name as host, - shares.name as share, - backups.num as backupnum - FROM hosts, backups, shares - WHERE shares.id=backups.shareid AND - hosts.id =backups.hostid AND - backups.id = ? - }; - my $sth = $dbh->prepare($sql); - $sth->execute($backupID); - my $row = $sth->fetchrow_hashref(); - - return get_tgz_size_by_name( - getGzipName($row->{'host'}, $row->{share}, $row->{backupnum}) - ); -} - -sub getGzipSize($$) -{ - my ($hostID, $backupNum) = @_; - my $sql; - my $dbh = get_dbh(); - - $sql = q{ - SELECT hosts.name as host, - shares.name as share, - backups.num as backupnum - FROM hosts, backups, shares - WHERE shares.id=backups.shareid AND - hosts.id =backups.hostid AND - hosts.id=? AND - backups.num=? - }; - my $sth = $dbh->prepare($sql); - $sth->execute($hostID, $backupNum); - - my $row = $sth->fetchrow_hashref(); - - return get_tgz_size_by_name( - getGzipName($row->{'host'}, $row->{share}, $row->{'backupnum'}) - ); -} - -sub getVolumes($) { - my $id = shift; - - my $max_archive_size = $Conf{MaxArchiveSize} || die "no MaxArchiveSize"; - - my $sth = $dbh->prepare(qq{ - select - size - from backup_parts - where backup_id = ? - order by part_nr asc - }); - - $sth->execute($id); - - my $cumulative_size = 0; - my $volumes = 1; - - while(my ($size) = $sth->fetchrow_array) { - if ($cumulative_size + $size > $max_archive_size) { - $volumes++; - $cumulative_size = $size; - } else { - $cumulative_size += $size; - } - } - - return ($volumes,$cumulative_size); -} - -sub getBackupsNotBurned($) { - - my $param = shift; - my $dbh = get_dbh(); - - my $order = getSort('burn', 'sql', $param->{'sort'}); - -print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql order: $order\n"; - - my $sql = qq{ - SELECT - backups.hostID AS hostID, - hosts.name AS host, - shares.name AS share, - backups.num AS backupnum, - backups.type AS type, - backups.date AS date, - date_part('epoch',now()) - backups.date as age, - backups.size AS size, - backups.id AS id, - backups.inc_size AS inc_size, - backups.parts AS parts - 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 - WHERE backups.inc_size > 0 AND backups.size > 0 AND backups.inc_deleted is false AND archive_backup.backup_id IS NULL AND backups.parts > 0 - GROUP BY - backups.hostID, - hosts.name, - shares.name, - backups.num, - backups.shareid, - backups.id, - backups.type, - backups.date, - backups.size, - backups.inc_size, - backups.parts - ORDER BY $order - }; - my $sth = $dbh->prepare( $sql ); - my @ret; - $sth->execute(); - - while ( my $row = $sth->fetchrow_hashref() ) { - $row->{'age'} = sprintf("%0.1f", ( $row->{'age'} / 86400 ) ); - #$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) ); - - my $max_archive_size = $Conf{MaxArchiveSize} || die "no MaxArchiveSize"; - if ($row->{size} > $max_archive_size) { - ($row->{volumes}, $row->{inc_size_calc}) = getVolumes($row->{id}); - } - - $row->{size} = sprintf("%0.2f", $row->{size} / 1024 / 1024); - - # do some cluster calculation (approximate) - $row->{inc_size} = int(( ($row->{inc_size} + 1023 ) / 2 ) * 2); - $row->{inc_size_calc} ||= $row->{inc_size}; - push @ret, $row; - } - - return @ret; -} - -sub displayBackupsGrid($) { - - my $param = shift; - - my $max_archive_size = $Conf{MaxArchiveSize} || die "no MaxArchiveSize"; - my $max_archive_file_size = $Conf{MaxArchiveFileSize} || die "no MaxFileInSize"; - - my $retHTML .= q{ -
- }; - - $retHTML .= <<'EOF3'; - - -
- - -Size: kB - -
-
 
-
 
-
0%
-
-
- -
 
- -Note: - - - - -
- -EOF3 - $retHTML .= q{ - - - - - - } . - sort_header($param, 'Share', 'share', 'center') . - sort_header($param, '#', 'num', 'center') . - qq{ - - } . - sort_header($param, 'Date', 'date', 'center') . - sort_header($param, 'Age/days', 'age', 'center') . - sort_header($param, 'Size/Mb', 'size', 'center') . - sort_header($param, 'gzip size/Kb', 'incsize', 'center') . - qq{ - - }; - - my @color = (' bgcolor="#e0e0e0"', ''); - - my $i = 0; - my $host = ''; - - foreach my $backup ( getBackupsNotBurned($param) ) { - - if ($host ne $backup->{'host'}) { - $i++; - $host = $backup->{'host'}; - } - my $ftype = ""; - - my $checkbox_key = $backup->{'hostid'}. '_' .$backup->{'backupnum'} . '_' . $backup->{'id'}; - - $retHTML .= - ' - ' . - '' . - '' . - '' . - '' . - '' . - '' . - '' . - '' . - '' . - - "\n"; - } - - $retHTML .= "
- - Typemedias
'; - - if (($backup->{'inc_size'} || 0) > 0) { - $retHTML .= ' - '; - } - - my $img_url = $Conf{CgiImageDirURL}; - - $retHTML .= - '' . $backup->{'host'} . ':' . $backup->{'share'} . '' . $backup->{'backupnum'} . '' . $backup->{'type'} . '' . epoch_to_iso( $backup->{'date'} ) . '' . $backup->{'age'} . '' . $backup->{'size'} . '' . sprintf("%0.1f", $backup->{'inc_size'} / 1024 ) . - '' . ( qq{media} x $backup->{volumes} ) . '
"; - $retHTML .= "
"; - - return $retHTML; -} - -sub displayGrid($) { - my ($param) = @_; - - my $offset = $param->{'offset'}; - my $hilite = $param->{'search_filename'}; - - my $retHTML = ""; - - my $start_t = time(); - - my ($results, $files); - if ($param->{'use_hest'} && length($hilite) > 0) { - ($results, $files) = getFilesHyperEstraier($param); - } else { - ($results, $files) = getFiles($param); - } - - 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 (took $dur) -
- - - - }; - - sub sort_header($$$$) { - my ($param, $display, $name, $align) = @_; - - my ($sort_what, $sort_direction) = split(/_/,$param->{'sort'},2); - - my $old_sort = $param->{'sort'}; - - my $html = qq{'; - $param->{'sort'} = $old_sort; - - return $html; - } - - $retHTML .= - sort_header($param, 'Share', 'share', 'center') . - sort_header($param, 'Type and Name', 'path', 'center') . - sort_header($param, '#', 'num', 'center') . - sort_header($param, 'Size', 'size', 'center') . - sort_header($param, 'Date', 'date', 'center'); - - $retHTML .= qq{ - - - }; - - my $file; - - sub hilite_html($$) { - my ($html, $search) = @_; - $html =~ s#($search)#$1#gis; - return $html; - } - - sub restore_link($$$$$$) { - my $type = shift; - my $action = 'RestoreFile'; - $action = 'browse' if (lc($type) eq 'dir'); - return sprintf(qq{%s}, $action, @_); - } - - my $sth_archived; - my %archived_cache; - - sub check_archived($$$) { - my ($host, $share, $num) = @_; - - if (my $html = $archived_cache{"$host $share $num"}) { - return $html; - } - - $sth_archived ||= $dbh->prepare(qq{ - select - dvd_nr, note, - count(archive_burned.copy) as copies - from archive - inner join archive_burned on archive_burned.archive_id = archive.id - inner join archive_backup on archive.id = archive_backup.archive_id - inner join backups on backups.id = archive_backup.backup_id - inner join hosts on hosts.id = backups.hostid - inner join shares on shares.id = backups.shareid - where hosts.name = ? and shares.name = ? and backups.num = ? - group by dvd_nr, note - }); - - my @mediums; - - $sth_archived->execute($host, $share, $num); - while (my $row = $sth_archived->fetchrow_hashref()) { - push @mediums, '' .$row->{'dvd_nr'} . - ''; - } - - my $html = join(", ",@mediums); - $archived_cache{"$host $share $num"} = $html; - return $html; - } - - my $i = $offset * $on_page; - - foreach $file (@{ $files }) { - $i++; - - my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'}); - $retHTML .= qq{}; - - $retHTML .= qq{}; - - $retHTML .= - qq{} . - qq{} . - qq{} . - qq{} . - qq{} . - qq{}; - - $retHTML .= ""; - } - $retHTML .= "
{'sort'} = $name . '_' . $direction; - $html .= ' style="border: 1px solid #808080;"'; - - # add unicode arrow for direction - $arrow .= ' '; - $arrow .= $direction eq 'a' ? '▲' - : $direction eq 'd' ? '▼' - : '' - ; - - } else { - $param->{'sort'} = $name . '_a'; - } - - $html .= '>' . $display . '' . $arrow . 'Media
$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{} . check_archived( $file->{'hname'}, $file->{'sname'}, $file->{'backupnum'} ) . qq{
"; - - # all variables which has to be transfered - 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/) { - $retHTML .= qq{\n}; - } - - my $del = ''; - my $max_page = int( $results / $on_page ); - my $page = 0; - - sub page_uri($) { - my $param = shift || die "no param?"; - - my $uri = $MyURL; - my $del = '?'; - foreach my $k (keys %{ $param }) { - if ($param->{$k}) { - $uri .= $del . $k . '=' . ${EscURI( $param->{$k} )}; - $del = '&'; - } - } - return $uri; - } - - sub page_link($$$) { - my ($param,$page,$display) = @_; - - $param->{'offset'} = $page if (defined($page)); - - my $html = '' . $display . ''; - } - - $retHTML .= '
'; - - if ($offset > 0) { - $retHTML .= page_link($param, $offset - 1, '<<') . ' '; - } - - while ($page <= $max_page) { - if ($page == $offset) { - $retHTML .= $del . '' . ($page + 1) . ''; - } else { - $retHTML .= $del . page_link($param, $page, $page + 1); - } - - if ($page < $offset - $pager_pages && $page != 0) { - $retHTML .= " ... "; - $page = $offset - $pager_pages; - $del = ''; - } elsif ($page > $offset + $pager_pages && $page != $max_page) { - $retHTML .= " ... "; - $page = $max_page; - $del = ''; - } else { - $del = ' | '; - $page++; - } - } - - if ($offset < $max_page) { - $retHTML .= ' ' . page_link($param, $offset + 1, '>>'); - } - - $retHTML .= "
"; - - return $retHTML; -} - -1;