added SearchDSN and SearchUser configuration directives. Defaults are

[BackupPC.git] / lib / BackupPC / SearchLib.pm

diff --git a/lib/BackupPC/SearchLib.pm b/lib/BackupPC/SearchLib.pm
index 3521452..b182e7d 100644 (file)
--- a/lib/BackupPC/SearchLib.pm
+++ b/lib/BackupPC/SearchLib.pm
@@ -5,16 +5,19 @@ use strict;
 use BackupPC::CGI::Lib qw(:all);
 use BackupPC::Attrib qw(:all);
 use DBI;
+use DateTime;
 use vars qw(%In $MyURL);
 
 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 +30,18 @@ sub getUnits() {
     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 +49,23 @@ sub getWhere($) {
                        ( $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_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 $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_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 $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 +84,7 @@ sub getWhere($) {
 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,7 +95,7 @@ sub getFiles($$) {
                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
@@ -122,10 +136,10 @@ sub getFiles($$) {
                        '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,10 +153,9 @@ sub getFiles($$) {
        return ($results, \@ret);
 }
 
-sub getBackupsNotBurned()
-  {
-      my $dbh = DBI->connect( "dbi:SQLite:dbname=${TopDir}/$Conf{SearchDB}",
-        "", "", { RaiseError => 1, AutoCommit => 1 } );      
+sub getBackupsNotBurned() {
+
+       my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 1 } );
       my $sql = q{ 
          SELECT
            hosts.ID         AS hostID,
@@ -242,7 +255,7 @@ EOF3
                $retHTML .= '<td class="fviewborder">' . $backup->{'host'} . '</td>' .
                        '<td class="fviewborder">' . $backup->{'backupno'} . '</td>' .
                        '<td class="fviewborder">' . $backup->{'type'} . '</td>' .
-                       '<td class="fviewborder">' . $backup->{'date'} . '<td>' .
+                       '<td class="fviewborder">' . epoch_to_iso( $backup->{'date'} ) . '<td>' .
                        '</tr>';
        }
 
@@ -308,7 +321,7 @@ sub displayGrid($$$$) {
                        $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{<td class="fviewborder">$v</td>};