#!/usr/bin/perl package BackupPC::Search; 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 Data::Dump qw(dump); my $on_page = 100; my $pager_pages = 10; my $dsn = $Conf{SearchDSN}; my $db_user = $Conf{SearchUser} || ''; sub search_module { my $bpc = BackupPC::Lib->new || die; my %Conf = $bpc->Conf(); my $search_module = $Conf{SearchModule} || die "search is disabled"; eval "use $search_module"; if ( $@ ) { warn "ERROR: $search_module: $!"; } else { warn "# using $search_module for full-text search"; } return $search_module->new( %Conf ); } 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 => { sname_d => 'shares.name DESC', sname_a => 'shares.name ASC', filepath_d => 'files.path DESC', filepath_a => 'files.path ASC', backupnum_d => 'files.backupnum DESC', backupnum_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 => { sname_d => 'host DESC, share DESC', sname_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 search_module->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{
"; 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{
}; 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{ | {'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 . ' | '; $param->{'sort'} = $old_sort; return $html; } $retHTML .= sort_header($param, 'Share', 'sname', 'center') . sort_header($param, 'Type and Name', 'filepath', 'center') . sort_header($param, '#', 'backupnum', 'center') . sort_header($param, 'Size', 'size', 'center') . sort_header($param, 'Date', 'date', 'center'); $retHTML .= qq{Media | ||||
$i | }; $retHTML .= qq{} . $file->{'hname'} . ':' . $file->{'sname'} . qq{ | } . qq{} . hilite_html( $file->{'filepath'}, $hilite ) . qq{ | } . qq{} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{ | } . qq{} . $file->{'size'} . qq{ | } . qq{} . epoch_to_iso( $file->{'date'} ) . qq{ | } . qq{} . check_archived( $file->{'hname'}, $file->{'sname'}, $file->{'backupnum'} ) . qq{ | }; $retHTML .= "