#!/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); require Exporter; our @ISA=qw(Exporter); our @EXPORT=qw(unit); my $on_page = 100; my $pager_pages = 10; my $dbh; my $bpc = BackupPC::Lib->new || die; $bpc->ConfigRead('_search_archive'); my %Conf = $bpc->Conf(); sub search_module { 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($Conf{SearchDSN}, $Conf{SearchUser}, "", { 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}; my $dd = $param->{ $name . '_day_' . $suffix}; $yyyy =~ s/\D//g; $mm =~ s/\D//g; $dd =~ s/\D//g; my $dt = new DateTime( year => $yyyy, month => $mm || 1, day => $dd || 1, hour => 0, minute => 0, second => 0, ); if ( $suffix eq 'to' && ( ! $mm || ! $dd ) ) { $dt += DateTime::Duration->new( years => 1 ) if ! $mm; $dt += DateTime::Duration->new( months => 1 ) if ! $dd; $dt -= DateTime::Duration->new( days => 1 ); } 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'}); push @conditions, join(' ' , 'burned is', $param->{burned} eq 'burned' ? '' : 'not', 'true') if $param->{burned}; 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 backups_burned on backup_id = backups.id }; } my $order = getSort('search', 'sql', $param->{'sort'}); my $sql_order = qq{ ORDER BY $order LIMIT $on_page OFFSET ? }; my $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order }; my $sth = $dbh->prepare($sql_results); $sth->execute( $offset ); my @ret; while (my $row = $sth->fetchrow_hashref()) { push @ret, $row; } $sth->finish(); return ($sth->rows, \@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{GzipTempDir}.'/'.$name; my $size = -1; $|=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 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 p.id, p.filename, b.date, date_part('epoch',now()) - b.date as age, p.size, count(ap.*) as scheduled, count(ab.*) as burned FROM backup_parts p JOIN backups b ON b.id = p.backup_id LEFT OUTER JOIN archive_parts ap ON ap.backup_part_id = p.id LEFT OUTER JOIN archive_burned ab ON ab.archive_id = ap.archive_id GROUP BY p.id,filename,b.date,age,p.size,p.part_nr }; $sql .= qq{ HAVING count(ap.*) = 0 } unless $param->{scheduled}; $sql .= qq{ ORDER BY b.date,p.part_nr }; 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 ) ); push @ret, $row; } return @ret; } sub displayBackupsGrid($) { my $param = shift; my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize"; my $retHTML .= qq|
"; 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 .= "