--- trunk/lib/BackupPC/SearchLib.pm 2005/07/31 16:16:55 31 +++ trunk/lib/BackupPC/SearchLib.pm 2005/08/21 13:44:20 55 @@ -5,16 +5,20 @@ 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 $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); my $st = $dbh->prepare( " SELECT shares.ID AS ID, shares.share AS name FROM shares;"); @@ -27,11 +31,18 @@ return @ret; } +sub epoch_to_iso { + my $t = shift || return; + my $dt = DateTime->from_epoch( epoch => $t ) || return; +print STDERR "$t == ",$dt->epoch,"\n"; + 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,18 +50,23 @@ ( $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 $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); + 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); print STDERR "backup: $backup_from - $backup_to files: $files_from - $files_to cond:",join(" | ",@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, @@ -81,9 +96,12 @@ 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, + }; + + my $sql_dvd_cols = qq{ dvds.name AS dvd }; @@ -92,6 +110,9 @@ 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 + }; + + my $sql_dvd_from = qq{ LEFT JOIN dvds ON dvds.ID = files.dvdid }; @@ -112,7 +133,7 @@ my ($results) = $sth->fetchrow_array(); - $sth = $dbh->prepare(qq{ select $sql_cols $sql_from $sql_where $sql_order }); + $sth = $dbh->prepare(qq{ select $sql_cols $sql_dvd_cols $sql_from $sql_dvd_from $sql_where $sql_order }); $sth->execute( $offset ); my @ret; @@ -122,10 +143,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'}, @@ -139,45 +160,43 @@ 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, { +sub getBackupsNotBurned() { + + my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } ); + my $sql = q{ + SELECT + hosts.ID AS hostid, + min(hosts.name) AS host, + backups.num AS backupno, + min(backups.type) AS type, + min(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, hosts.id + }; + my $sth = $dbh->prepare( $sql ); + my @ret; + $sth->execute(); + + while ( my $row = $sth->fetchrow_hashref() ) { + push(@ret, { 'host' => $row->{'host'}, - 'hostid' => $row->{'hostID'}, + 'hostid' => $row->{'hostid'}, 'backupno' => $row->{'backupno'}, 'type' => $row->{'type'}, 'date' => $row->{'date'} } - ); + ); } - return @ret; - } + return @ret; +} sub displayBackupsGrid() { @@ -242,7 +261,7 @@ $retHTML .= '' . $backup->{'host'} . '' . '' . $backup->{'backupno'} . '' . '' . $backup->{'type'} . '' . - '' . $backup->{'date'} . '' . + '' . epoch_to_iso( $backup->{'date'} ) . '' . ''; } @@ -265,12 +284,17 @@ $retHTML .= qq{}; } + 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); $retHTML .= qq{ -
Found $results files, showing $from - $to +
Found $results files showing $from - $to (took $dur) @@ -308,7 +332,7 @@ $typeStr, restore_link( $typeStr, $file->{'hname'}, $file->{'backupno'}, $file->{'sname'}, $file->{'fpath'}, $file->{'backupno'} ), $file->{'size'}, - $file->{'date'}, + epoch_to_iso( $file->{'date'} ), $file->{'dvd'} )) { $retHTML .= qq{};
Share$v