From 67be83ac2e8e04940a177ad216c83f98801d5c5b Mon Sep 17 00:00:00 2001 From: dpavlin Date: Sun, 28 Aug 2005 14:05:08 +0000 Subject: [PATCH] bug fixes: date limit now works again, correct number of displayed results, added SQL script to remove unneeded columns, dates only selection now works with HyperEstraier (but, it's MUCH SLOWER than from database), added ordinal number of result git-svn-id: svn+ssh://llin/home/dpavlin/private/svn/BackupPC/trunk@87 8392b6e1-25fa-0310-8288-cc32f8e212ea --- BUGS | 1 + lib/BackupPC/SearchLib.pm | 81 ++++++++++++++++++++---------------- sql/drop_unneeded_colums.sql | 7 ++++ 3 files changed, 52 insertions(+), 37 deletions(-) create mode 100644 sql/drop_unneeded_colums.sql diff --git a/BUGS b/BUGS index a2617d0..6453666 100644 --- a/BUGS +++ b/BUGS @@ -3,5 +3,6 @@ + plus (+) in filenames isn't escaped when creating restore link + sort share-ova u drop-downu + fixed found_in_db +- check for invalid dates in input form - custom logo - DVD burning diff --git a/lib/BackupPC/SearchLib.pm b/lib/BackupPC/SearchLib.pm index 7f83bab..d0d27d0 100644 --- a/lib/BackupPC/SearchLib.pm +++ b/lib/BackupPC/SearchLib.pm @@ -62,25 +62,34 @@ sub dates_from_form($) { 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'; } - return ( + 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; + } @@ -104,8 +113,11 @@ sub getWhere($) { } -sub getFiles($$) { - my ($param, $offset) = @_; +sub getFiles($) { + my ($param) = @_; + + my $offset = $param->{'offset'} || 0; + $offset *= $on_page; my $dbh = get_dbh(); @@ -113,26 +125,18 @@ sub getFiles($$) { 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 type, - files.size AS size, - -- dvds.name AS dvd - null AS dvd + 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 = shares.ID - }; - - my $sql_dvd_from = qq{ - -- 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; @@ -146,10 +150,7 @@ sub getFiles($$) { }; 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 $sql_results = qq{ select $sql_cols $sql_from $sql_where $sql_order }; my $sth = $dbh->prepare($sql_count); $sth->execute(); @@ -174,8 +175,11 @@ sub getFiles($$) { return ($results, \@ret); } -sub getFilesHyperEstraier($$) { - my ($param, $offset) = @_; +sub getFilesHyperEstraier($) { + my ($param) = @_; + + my $offset = $param->{'offset'} || 0; + $offset *= $on_page; die "no index_path?" unless ($index_path); @@ -196,20 +200,17 @@ sub getFilesHyperEstraier($$) { # 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); + my ($backup_from, $backup_to, $files_from, $files_to) = dates_from_form($param); - $cond->add_attr("date NUMGE $files_from") if ($files_from); - $cond->add_attr("date NUMLE $files_to") if ($files_to); + $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("shareid NUMEQ $shareid") if ($shareid); - } + $cond->add_attr("date NUMGE $files_from") if ($files_from); + $cond->add_attr("date NUMLE $files_to") if ($files_to); - $offset ||= 0; - $offset = ($offset * $on_page); + $cond->add_attr("shareid NUMEQ $shareid") if ($shareid); # $cond->set_max( $offset + $on_page ); $cond->set_options( $HyperEstraier::Condition::SURE ); @@ -217,13 +218,14 @@ sub getFilesHyperEstraier($$) { # get the result of search my $result = $db->search($cond, 0); - $result->get(0); my @res; my $hits = $result->size; # for each document in result - for my $i ($offset .. $result->size-1) { + for my $i ($offset .. ($offset + $on_page - 1)) { + last if ($i >= $hits); + my $id = $result->get($i); my $doc = $db->get_doc($id, 0); @@ -252,8 +254,6 @@ sub getBackupsNotBurned() { 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) @@ -374,9 +374,9 @@ sub displayGrid($) { my ($results, $files); if ($param->{'use_hest'}) { - ($results, $files) = getFilesHyperEstraier($param, $offset); + ($results, $files) = getFilesHyperEstraier($param); } else { - ($results, $files) = getFiles($param, $offset); + ($results, $files) = getFiles($param); } my $dur_t = time() - $start_t; @@ -402,6 +402,7 @@ sub displayGrid($) { + @@ -426,17 +427,23 @@ sub displayGrid($) { return sprintf(qq{%s}, $action, @_); } + 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{}; + qq{}; $retHTML .= ""; } diff --git a/sql/drop_unneeded_colums.sql b/sql/drop_unneeded_colums.sql new file mode 100644 index 0000000..dde3579 --- /dev/null +++ b/sql/drop_unneeded_colums.sql @@ -0,0 +1,7 @@ +-- this is file to convert your database to new format which is used +-- from r70 commit. + +begin; +alter table shares drop column share ; +alter table files drop column dvdid ; +commit; -- 2.20.1
Share Type and Name #
$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{} . $file->{'dvd'} . qq{} . '?' . qq{