--- trunk/lib/BackupPC/SearchLib.pm 2005/06/23 12:36:22 9 +++ trunk/lib/BackupPC/SearchLib.pm 2005/08/28 10:20:31 84 @@ -4,251 +4,216 @@ use strict; use BackupPC::CGI::Lib qw(:all); use BackupPC::Attrib qw(:all); -use Data::Dumper; use DBI; +use DateTime; +use vars qw(%In $MyURL); +use Time::HiRes qw/time/; + +my $on_page = 100; +my $pager_pages = 10; + +my $dsn = $Conf{SearchDSN}; +my $db_user = $Conf{SearchUser} || ''; + +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( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}", - "", "", { 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 id, share FROM shares ORDER BY share} ); + $sth->execute(); + push @ret, { 'id' => '', 'share' => '-'}; # dummy any + + while ( my $row = $sth->fetchrow_hashref() ) { + push @ret, $row; + } + return @ret; } -sub getWhere($) { - my ($param) = @_; - my $retSQL = ""; - my @conditions = (); - my $cond; - - - - - if ( defined( $param->{'search_backup_day_from'} ) && $param->{'search_backup_day_from'} ne "") { - push( @conditions, - ' strftime("%d", datetime(backups.date, "unixepoch","localtime")) >= "' - . $param->{'search_backup_day_from'} ."\""); - } - if ( defined( $param->{'search_backup_day_to'} ) && $param->{'search_backup_day_to'} ne "") { - push( @conditions, - ' strftime("%d", datetime(backups.date, "unixepoch","localtime")) <= "' - . $param->{'search_backup_day_from'} ."\""); - } - if ( defined( $param->{'search_backup_month_from'} ) && $param->{'search_backup_month_from'} ne "") { - push( @conditions, - ' strftime("%m", datetime(backups.date, "unixepoch","localtime")) >= "' - . $param->{'search_backup_month_from'} ."\""); - } - if ( defined( $param->{'search_backup_month_to'} ) && $param->{'search_backup_month_to'} ne "") { - push( @conditions, - ' strftime("%m", datetime(backups.date, "unixepoch","localtime")) <= "' - . $param->{'search_backup_month_to'} ."\""); - } - if ( defined( $param->{'search_backup_year_from'} ) && $param->{'search_backup_year_from'} ne "") { - push( @conditions, - ' strftime("%Y", datetime(backups.date, "unixepoch","localtime")) >= "' - . $param->{'search_backup_year_from'} ."\""); - } - if ( defined( $param->{'search_backup_year_to'} ) && $param->{'search_backup_year_to'} ne "") { - push( @conditions, - ' strftime("%Y", datetime(backups.date, "unixepoch","localtime")) <= "' - . $param->{'search_backup_year_to'} ."\""); - } +sub epoch_to_iso { + my $t = shift || return; + my $iso = BackupPC::Lib::timeStamp($t); + $iso =~ s/\s/ /g; + return $iso; +} - if ( defined( $param->{'search_day_from'} ) && $param->{'search_day_from'} ne "" ) { - push( @conditions, - ' strftime("%d", datetime(files.date, "unixepoch","localtime")) >= "' - . $param->{'search_day_from'} ."\""); - } - if ( defined( $param->{'search_month_from'} ) && $param->{'search_month_from'} ne "") { - push( @conditions, - ' strftime("%m", datetime(files.date, "unixepoch","localtime")) >= "' - . $param->{'search_month_from'} ."\""); - } - if ( defined( $param->{'search_year_from'} ) && $param->{'search_year_from'} ne "") { - push( @conditions, - ' strftime("%Y", datetime(files.date, "unixepoch","localtime")) >= "' - . $param->{'search_year_from'} ."\""); - } - if ( defined( $param->{'search_day_to'} ) && $param->{'search_day_to'} ne "" ) { - push( @conditions, - ' strftime("%d", datetime(files.date, "unixepoch","localtime")) <= "' - . $param->{'search_day_to'} ."\""); - } - if ( defined( $param->{'search_month_to'} ) && $param->{'search_month_to'} ne "" ) { - push( @conditions, - ' strftime("%m", datetime(files.date, "unixepoch","localtime")) <= "' - . $param->{'search_month_to'} ."\"" ); - } - if ( defined( $param->{'search_year_to'} )&& $param->{'search_year_to'} ne "" ) { - push( @conditions, - ' strftime("%Y", datetime(files.date, "unixepoch","localtime")) <= "' - . $param->{'search_year_to'} ."\""); - } +sub dates_from_form($) { + my $param = shift || return; - if ( defined( $param->{'search_host'} ) && $param->{'search_host'} ne "") { - push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ); - } + sub mk_epoch_date($$) { + my ($name,$suffix) = @_; - if ( defined ($param->{'search_filename'}) && $param->{'search_filename'} ne "") { - push (@conditions, " files.name LIKE '".$param->{'search_filename'}."%'"); + my $yyyy = $param->{ $name . '_year_' . $suffix} || return; + my $mm .= $param->{ $name . '_month_' . $suffix} || + ( $suffix eq 'from' ? 1 : 12); + my $dd .= $param->{ $name . '_day_' . $suffix} || + ( $suffix eq 'from' ? 1 : 31); + my $dt = new DateTime( + year => $yyyy, + month => $mm, + day => $dd + ); + return $dt->epoch || 'NULL'; } - - $retSQL = ""; - foreach $cond(@conditions) - { - if ($retSQL ne "") - { - $retSQL .= " AND "; - } - $retSQL .= $cond; - } - - return $retSQL; + return ( + mk_epoch_date('search_backup', 'from'), + mk_epoch_date('search_backup', 'to'), + mk_epoch_date('search', 'from'), + mk_epoch_date('search', 'to'), + ); } -sub getFiles($$) - { - my ($where, $offset) = @_; - - - my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}", - "", "", { RaiseError => 1, AutoCommit => 1 } ); - my $sql = - q{ - SELECT files.id AS fid, - hosts.name AS hname, - shares.name AS sname, - shares.share AS sharename, - backups.num AS backupNum, - files.name AS filename, - files.path AS filepath, - shares.share||files.fullpath AS networkPath, - date(files.date, 'unixepoch', 'localtime') AS date, - files.type AS filetype, - files.size AS size, - dvds.name AS dvd - FROM - files - INNER JOIN shares ON files.shareID=shares.ID - INNER JOIN hosts ON hosts.ID = shares.hostID - INNER JOIN backups ON backups.hostID = hosts.ID - LEFT JOIN dvds ON dvds.ID = files.dvdid - - }; - if (defined($where) && $where ne "") - { - $sql .= " WHERE ". $where; - } +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(" | ",@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'}); - $sql .= - q{ - ORDER BY files.id - LIMIT 100 - OFFSET ? * 100 + 1 + return join(" and ", @conditions); +} + + +sub getFiles($$) { + my ($param, $offset) = @_; + + 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.path AS filepath, + files.date AS date, + files.type AS filetype, + files.size AS size, + -- dvds.name AS dvd + null AS dvd }; - - - - my $st = $dbh->prepare( - $sql - ); - if (!defined($offset) && $offset ne "") - { - $st->bind_param(1, $offset); - } - else - { - $st->bind_param(1,0); - } - $st->execute; - - my @ret = (); - my $tmp; - - while ($tmp = $st->fetchrow_hashref()) - { - push(@ret, { - 'hname' => $tmp->{'hname'}, - 'sname' => $tmp->{'sname'}, - 'sharename' => $tmp->{'sharename'}, - 'backupno' => $tmp->{'backupNum'}, - 'fname' => $tmp->{'filename'}, - 'fpath' => $tmp->{'filepath'}, - 'networkpath' => $tmp->{'networkPath'}, - 'date' => $tmp->{'date'}, - 'type' => $tmp->{'filetype'}, - 'size' => $tmp->{'size'}, - 'id' => $tmp->{'fid'}, - 'dvd' => $tmp->{'dvd'} - } - ); - + + 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 = shares.ID + }; + + my $sql_dvd_from = qq{ + -- LEFT JOIN dvds ON dvds.ID = files.dvdid + }; + + my $sql_where; + my $where = getWhere($param); + $sql_where = " WHERE ". $where if ($where); + + my $sql_order = qq{ + ORDER BY files.date + 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_dvd_from $sql_where $sql_order }; + + $offset ||= 0; + $offset = ($offset * $on_page); + + 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; - $st->finish(); - $dbh->disconnect(); - return @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'} + }); + } + + $sth->finish(); + return ($results, \@ret); +} -sub getBackupsNotBurned() - { - my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}", - "", "", { RaiseError => 1, AutoCommit => 1 } ); - my $sql = q{ - SELECT - hosts.ID AS hostID, - hosts.name AS host, - backups.num AS backupno, - backups.type AS type, - 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 - }; - my $st = $dbh -> prepare( $sql ); - my @ret = (); - $st -> execute(); - - while ( my $tmp = $st -> fetchrow_hashref() ) - { - push(@ret, { - 'host' => $tmp->{'host'}, - 'hostid' => $tmp->{'hostID'}, - 'backupno' => $tmp->{'backupno'}, - 'type' => $tmp->{'type'}, - 'date' => $tmp->{'date'} - } - ); +sub getBackupsNotBurned() { + + my $dbh = get_dbh(); + my $sql = q{ + SELECT + backups.hostID AS hostid, + min(hosts.name) AS host, + backups.num AS backupno, + min(backups.type) AS type, + min(backups.date) AS date, + min(backups.size) AS size + 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 = shares.ID + WHERE + files.dvdid IS NULL + GROUP BY + backups.hostID, backups.num + ORDER BY min(backups.date) + }; + my $sth = $dbh->prepare( $sql ); + my @ret; + $sth->execute(); + + while ( my $row = $sth->fetchrow_hashref() ) { + $row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) ); + $row->{'size'} = sprintf("%0.2f", $row->{'size'} / 1024 / 1024); + push @ret, $row; } - return @ret; - } + return @ret; +} sub displayBackupsGrid() { my $retHTML = ""; my $addForm = 1; - if ($addForm) - { + if ($addForm) { $retHTML .= < @@ -271,118 +236,198 @@ //--> EOF3 - $retHTML .= q{
}; $retHTML .= q{}; } - $retHTML .= ""; - $retHTML .= " "; - if ($addForm) - { + $retHTML .= qq{ +
+ + }; + + if ($addForm) { $retHTML .= ""; } - $retHTML .= ""; - my @backups = getBackupsNotBurned(); - my $backup; - - if ($addForm) - { - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - + $retHTML .= qq{ + + + + + + + + }; + + my @backups = getBackupsNotBurned(); + my $backup; + + if ($addForm) { + $retHTML .= qq{ + + }; } - foreach $backup(@backups) - { - my $ftype = ""; + + foreach $backup(@backups) { + + my $ftype = ""; - $retHTML .= ""; - if ($addForm) - { - $retHTML .= ""; - } + $retHTML .= ""; + if ($addForm) { + $retHTML .= ''; + } - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - } - $retHTML .= "
Host Backup no Type date
"; - $retHTML .= ""; - $retHTML .= "
HostBackup noTypedateage/dayssize/MB
+ +
{'hostid'}."_".$backup->{'backupno'} - ."\" value=\"".$backup->{'hostid'}."_".$backup->{'backupno'}."\">
" . $backup->{'host'} . "" . $backup->{'backupno'} . "" . $backup->{'type'} . "" . $backup->{'date'} . ""; - $retHTML .= "
"; - if ($addForm) - { - $retHTML .= "
"; - } - - return $retHTML; - - - } + $retHTML .= '' . $backup->{'host'} . '' . + '' . $backup->{'backupno'} . '' . + '' . $backup->{'type'} . '' . + '' . epoch_to_iso( $backup->{'date'} ) . '' . + '' . $backup->{'age'} . '' . + '' . $backup->{'size'} . '' . + ''; + } -sub displayGrid($$$) - { - my ($where, $addForm, $offset) = @_; - my $retHTML = ""; + $retHTML .= ""; + + if ($addForm) { + $retHTML .= ""; + } - if ($addForm) - { - $retHTML .= q{
}; - $retHTML .= q{}; + return $retHTML; +} + +sub displayGrid($$) { + my ($param, $addForm) = @_; + + my $offset = $param->{'offset'}; + my $hilite = $param->{'search_filename'}; + + my $retHTML = ""; + + my $start_t = time(); + + my ($results, $files) = getFiles($param, $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 .= ""; - $retHTML .= " "; - $retHTML .= ""; - my @files = getFiles($where, $offset); - my $file; - - foreach $file(@files) - { - my $ftype = ""; - - if ($file->{'type'} == BPC_FTYPE_DIR) - { - $ftype = "dir"; - } - else - { - $ftype = "file"; - } - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; - $retHTML .= ""; + + + if ($addForm) { + $retHTML .= qq{}; + $retHTML.= qq{}; + $retHTML .= qq{}; } - $retHTML .= "
Host Name Type backup no. size date Media
" . $file->{'hname'} ."" . $file->{'fname'} . "" . $ftype . "" . $file->{'backupno'} . "" . $file->{'size'} . "" . $file->{'date'} . "" . $file->{'dvd'} . "
"; - - $retHTML .= ""; - for (my $ii = 1; $ii <= $#files; $ii++) - { - $retHTML .= "$ii"; - if ($ii < $#files) - { - $retHTML .= " | "; - } - } + $retHTML .= qq{ +
+ Found $results files showing $from - $to (took $dur) +
+ + + + + + + + + + }; + my $file; - if ($addForm) - { - $retHTML .= ""; - } - - return $retHTML; - } + 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, @_); + } + + foreach $file (@{ $files }) { + my $typeStr = BackupPC::Attrib::fileType2Text(undef, $file->{'type'}); + $retHTML .= qq{}; + + $retHTML .= + qq{} . + qq{} . + qq{} . + qq{} . + qq{} . + qq{}; + + $retHTML .= ""; + } + $retHTML .= "
ShareType and Name#SizeDateMedia
} . $file->{'sharename'} . qq{$typeStr } . hilite_html( $file->{'fpath'}, $hilite ) . qq{} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupno'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'fpath'} )}, $file->{'backupno'} ) . qq{} . $file->{'size'} . qq{} . epoch_to_iso( $file->{'date'} ) . qq{} . $file->{'dvd'} . 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; + + my $link_fmt = '%s'; + + $retHTML .= '
'; + + if ($offset > 0) { + $retHTML .= sprintf($link_fmt, $offset - 1, '<<') . ' '; + } + + while ($page <= $max_page) { + if ($page == $offset) { + $retHTML .= $del . '' . ($page + 1) . ''; + } else { + $retHTML .= $del . sprintf($link_fmt, $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 .= ' ' . sprintf($link_fmt, $offset + 1, '>>'); + } + + $retHTML .= "
"; + + $retHTML .= "" if ($addForm); + + return $retHTML; +} 1;