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

diff --git a/BUGS b/BUGS
index a2617d0..6453666 100644 (file)
--- 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 (file)
--- 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($) {
        </div>
        <table style="fview" width="100%" border="0" cellpadding="2" cellspacing="0">
                <tr class="fviewheader"> 
+               <td></td>
                <td align="center">Share</td>
                <td align="center">Type and Name</td>
                <td align="center">#</td>
@@ -426,17 +427,23 @@ sub displayGrid($) {
                return sprintf(qq{<a href="?action=%s&host=%s&num=%d&share=%s&dir=%s">%s</a>}, $action, @_);
        }
 
+       my $i = $offset * $on_page;
+
        foreach $file (@{ $files }) {
+               $i++;
+
                my $typeStr  = BackupPC::Attrib::fileType2Text(undef, $file->{'type'});
                $retHTML .= qq{<tr class="fviewborder">};
 
+               $retHTML .= qq{<td>$i</td>};
+
                $retHTML .=
                        qq{<td class="fviewborder" align="right">} . $file->{'hname'} . ':' . $file->{'sname'} . qq{</td>} .
                        qq{<td class="fviewborder"><img src="$Conf{CgiImageDirURL}/icon-$typeStr.gif" alt="$typeStr" align="middle">&nbsp;} . hilite_html( $file->{'filepath'}, $hilite ) . qq{</td>} .
                        qq{<td class="fviewborder" align="center">} . restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupnum'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'filepath'} )}, $file->{'backupnum'} ) . qq{</td>} .
                        qq{<td class="fviewborder" align="right">} . $file->{'size'} . qq{</td>} .
                        qq{<td class="fviewborder">} . epoch_to_iso( $file->{'date'} ) . qq{</td>} .
-                       qq{<td class="fviewborder">} . $file->{'dvd'} . qq{</td>};
+                       qq{<td class="fviewborder">} . '?' . qq{</td>};
 
                $retHTML .= "</tr>";
        }

diff --git a/sql/drop_unneeded_colums.sql b/sql/drop_unneeded_colums.sql
new file mode 100644 (file)
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;