From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Sun, 30 Jan 2011 16:01:23 +0000 (+0100)
Subject: fix sql filter for burned/pending
X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=commitdiff_plain;h=5da6f2d3b119993d9def86b5481a86ce6fd84059;ds=sidebyside

fix sql filter for burned/pending
---

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index 929e848..d860037 100755
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -740,10 +740,20 @@ JOIN archive       ON archive_id = archive.id
 JOIN backups       ON backup_id = backups.id
 JOIN hosts         ON hostid = hosts.id
 JOIN shares        ON shareid = shares.id
-ORDER BY backup_id, part_nr
 ;
 
 
+CREATE VIEW backups_burned AS
+SELECT
+	backup_id,
+	count(backup_id) = count(backup_part_id) as burned
+FROM archive
+JOIN archive_parts ON archive.id = archive_id
+JOIN backup_parts  ON backup_part_id = backup_id
+GROUP BY backup_id
+;
+
+-- triggers for backup_parts consistency
 create or replace function backup_parts_check() returns trigger as '
 declare
 	b_parts	integer;
diff --git a/lib/BackupPC/Search.pm b/lib/BackupPC/Search.pm
index 70f084b..cbd8c84 100644
--- a/lib/BackupPC/Search.pm
+++ b/lib/BackupPC/Search.pm
@@ -133,12 +133,7 @@ sub getWhere($) {
     	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";
-	}
+	push @conditions, join(' ' , 'burned is', $param->{burned} eq 'burned' ? '' : 'not', 'true') if $param->{burned};
 
 	return join(" and ", @conditions);
 }
@@ -226,8 +221,7 @@ sub getFiles($) {
 	# do we have to add tables for burned media?
 	if ( $param->{burned} ) {
 		$sql_from .= qq{
-			LEFT OUTER JOIN archive_backup_parts on backup_id = backups.id
-			LEFT OUTER JOIN archive_burned on archive_burned.archive_id = archive_id
+			LEFT OUTER JOIN backups_burned on backup_id = backups.id
 		};
 	}
 
@@ -239,22 +233,10 @@ sub getFiles($) {
 		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);
+	my $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()) {
@@ -262,7 +244,7 @@ sub getFiles($) {
 	}
      
 	$sth->finish();
-	return ($results, \@ret);
+	return ($sth->rows, \@ret);
 }
 
 sub getFilesHyperEstraier($) {