--- trunk/lib/BackupPC/SearchLib.pm 2005/07/31 16:16:55 31 +++ trunk/lib/BackupPC/SearchLib.pm 2005/08/26 21:43:01 78 @@ -5,33 +5,44 @@ 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/; my $on_page = 100; my $pager_pages = 10; +my $dsn = $Conf{SearchDSN}; +my $db_user = $Conf{SearchUser} || ''; + 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 = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); + 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; + } + $dbh->disconnect(); + return @ret; +} + +sub epoch_to_iso { + my $t = shift || return; + $t += 60 * 60 * +2; # FIXME add TZ + my $dt = DateTime->from_epoch( epoch => $t ) || return; + print STDERR "BUG: $t != " . $dt->epoch . "\n" unless ($t == $dt->epoch); + return $dt->ymd . ' ' . $dt->hms; } sub getWhere($) { my ($param) = @_; my @conditions; - sub mk_iso_date($$) { + sub mk_epoch_date($$) { my ($name,$suffix) = @_; my $yyyy = $param->{ $name . '_year_' . $suffix} || return; @@ -39,24 +50,29 @@ ( $suffix eq 'from' ? 1 : 12); my $dd .= $param->{ $name . '_day_' . $suffix} || ( $suffix eq 'from' ? 1 : 31); - return sprintf("%04d-%02d-%02d", $yyyy, $mm, $dd); - } + my $dt = new DateTime( + year => $yyyy, + month => $mm, + day => $dd + ); + return $dt->epoch || 'NULL'; + } + + my $backup_from = mk_epoch_date('search_backup', 'from'); + push @conditions, qq{ backups.date >= $backup_from } if ($backup_from); + my $backup_to = mk_epoch_date('search_backup', 'to'); + push @conditions, qq{ backups.date <= $backup_to } if ($backup_to); + + my $files_from = mk_epoch_date('search', 'from'); + push @conditions, qq{ files.date >= $files_from } if ($files_from); + my $files_to = mk_epoch_date('search', 'to'); + push @conditions, qq{ files.date <= $files_to } if ($files_to); - my $backup_from = mk_iso_date('search_backup', 'from'); - push @conditions, qq{ date(backups.date, 'unixepoch','localtime') >= '$backup_from' } if ($backup_from); - my $backup_to = mk_iso_date('search_backup', 'to'); - push @conditions, qq{ date(backups.date, 'unixepoch','localtime') <= '$backup_to' } if ($backup_to); - - my $files_from = mk_iso_date('search', 'from'); - push @conditions, qq{ date(files.date, 'unixepoch','localtime') >= '$files_from' } if ($files_from); - my $files_to = mk_iso_date('search', 'to'); - push @conditions, qq{ date(files.date, 'unixepoch','localtime') <= '$files_to' } if ($files_to); - - print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:",join(" | ",@conditions); + print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:" . join(" | ",@conditions); - push( @conditions, ' backups.hostID = ' . $param->{'search_host'} ) if ($param->{'search_host'}); + push( @conditions, ' files.shareid = ' . $param->{'search_share'} ) if ($param->{'search_share'}); - push (@conditions, " upper(files.name) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'}); + push (@conditions, " upper(files.path) LIKE upper('%".$param->{'search_filename'}."%')") if ($param->{'search_filename'}); return ( join(" and ", @conditions), @@ -69,8 +85,7 @@ sub getFiles($$) { my ($where, $offset) = @_; - my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}", - "", "", { RaiseError => 1, AutoCommit => 1 } ); + my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); my $sql_cols = qq{ files.id AS fid, @@ -80,8 +95,7 @@ files.backupNum AS backupNum, files.name AS filename, files.path AS filepath, - shares.share||files.fullpath AS networkPath, - date(files.date, 'unixepoch', 'localtime') AS date, + files.date AS date, files.type AS filetype, files.size AS size, dvds.name AS dvd @@ -91,7 +105,10 @@ 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 + 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 }; @@ -99,22 +116,30 @@ $sql_where = " WHERE ". $where if ($where); my $sql_order = qq{ - ORDER BY files.id - LIMIT $on_page - OFFSET ? + 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) + 1; + $offset = ($offset * $on_page); - my $sth = $dbh->prepare(qq{ select count(files.id) $sql_from $sql_where }); + my $sth = $dbh->prepare($sql_count); $sth->execute(); - my ($results) = $sth->fetchrow_array(); - $sth = $dbh->prepare(qq{ select $sql_cols $sql_from $sql_where $sql_order }); + $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()) { @@ -122,10 +147,10 @@ 'hname' => $row->{'hname'}, 'sname' => $row->{'sname'}, 'sharename' => $row->{'sharename'}, - 'backupno' => $row->{'backupNum'}, + 'backupno' => $row->{'backupnum'}, 'fname' => $row->{'filename'}, 'fpath' => $row->{'filepath'}, - 'networkpath' => $row->{'networkPath'}, + 'networkpath' => $row->{'networkpath'}, 'date' => $row->{'date'}, 'type' => $row->{'filetype'}, 'size' => $row->{'size'}, @@ -133,51 +158,43 @@ 'dvd' => $row->{'dvd'} }); } - + $sth->finish(); $dbh->disconnect(); 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 $row = $st -> fetchrow_hashref() ) - { - push(@ret, { - 'host' => $row->{'host'}, - 'hostid' => $row->{'hostID'}, - 'backupno' => $row->{'backupno'}, - 'type' => $row->{'type'}, - 'date' => $row->{'date'} - } - ); +sub getBackupsNotBurned() { + + my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); + 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 backups + INNER JOIN hosts ON hosts.ID = backups.hostID + 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() { @@ -216,15 +233,25 @@ if ($addForm) { $retHTML .= ""; } - $retHTML .= qq{HostBackup noTypedate}; + $retHTML .= qq{ + Host + Backup no + Type + date + age/days + size/MB + + }; my @backups = getBackupsNotBurned(); my $backup; if ($addForm) { - $retHTML .= qq{ + $retHTML .= qq{ + - }; + + }; } foreach $backup(@backups) { @@ -233,16 +260,18 @@ $retHTML .= ""; if ($addForm) { - $retHTML .= qq{{'backupno'} . - qq{" value="} . $backup->{'hostid'}."_".$backup->{'backupno'} . - qq{">}; + $retHTML .= ''; } $retHTML .= '' . $backup->{'host'} . '' . '' . $backup->{'backupno'} . '' . '' . $backup->{'type'} . '' . - '' . $backup->{'date'} . '' . + '' . epoch_to_iso( $backup->{'date'} ) . '' . + '' . $backup->{'age'} . '' . + '' . $backup->{'size'} . '' . ''; } @@ -259,18 +288,36 @@ my ($where, $addForm, $offset, $hilite) = @_; my $retHTML = ""; + my $start_t = time(); + + my ($results, $files) = getFiles($where, $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) . '
'; + } + + if ($addForm) { $retHTML .= qq{
}; $retHTML.= qq{}; $retHTML .= qq{}; } - my ($results, $files) = getFiles($where, $offset); - - my ($from, $to) = (($offset * $on_page) + 1, ($offset * $on_page) + $on_page); $retHTML .= qq{ -
Found $results files, showing $from - $to +
Found $results files showing $from - $to (took $dur) @@ -306,9 +353,9 @@ $file->{'sharename'}, qq{ } . hilite_html( $file->{'fpath'}, $hilite ), $typeStr, - restore_link( $typeStr, $file->{'hname'}, $file->{'backupno'}, $file->{'sname'}, $file->{'fpath'}, $file->{'backupno'} ), + restore_link( $typeStr, ${EscURI( $file->{'hname'} )}, $file->{'backupno'}, ${EscURI( $file->{'sname'})}, ${EscURI( $file->{'fpath'} )}, $file->{'backupno'} ), $file->{'size'}, - $file->{'date'}, + epoch_to_iso( $file->{'date'} ), $file->{'dvd'} )) { $retHTML .= qq{};
Share$v