begin rewrite to select backup_parts

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index e691bd8..b921594 100755 (executable)
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -224,7 +224,7 @@ if ($opt->create) {
 
        print "creating tables...\n";
 
 
        print "creating tables...\n";
 

-       $dbh->do( qq{
+       foreach my $sql ( split(/;/, qq{

                create table hosts (
                        ID      SERIAL          PRIMARY KEY,
                        name    VARCHAR(30)     NOT NULL,
                create table hosts (
                        ID      SERIAL          PRIMARY KEY,
                        name    VARCHAR(30)     NOT NULL,


@@ -259,6 +259,19 @@ if ($opt->create) {
                        PRIMARY KEY(id)
                );            
 
                        PRIMARY KEY(id)
                );            
 

+               create table backup_parts (
+                       id serial,
+                       backup_id int references backups(id),
+                       part_nr int not null check (part_nr > 0),
+                       tar_size bigint not null check (tar_size > 0),
+                       size bigint not null check (size > 0),
+                       md5 text not null,
+                       items int not null check (items > 0),
+                       date timestamp default now(),
+                       filename text not null,
+                       primary key(id)
+               );
+

                create table files (
                        ID              SERIAL,
                        shareID         INTEGER NOT NULL references shares(id),
                create table files (
                        ID              SERIAL,
                        shareID         INTEGER NOT NULL references shares(id),


@@ -281,10 +294,11 @@ if ($opt->create) {
                        primary key(id)
                );      
 
                        primary key(id)
                );      
 

-               create table archive_backup (
+               create table archive_parts (

                        archive_id      int not null references archive(id) on delete cascade,
                        archive_id      int not null references archive(id) on delete cascade,

-                       backup_id       int not null references backups(id),
-                       primary key(archive_id, backup_id)
+                       backup_id       int not null references backups(id),
+                       backup_part_id  int not null references backup_parts(id),
+                       primary key(archive_id, backup_id, backup_part_id)

                );
 
                create table archive_burned (
                );
 
                create table archive_burned (


@@ -295,41 +309,31 @@ if ($opt->create) {
                        iso_size bigint default -1
                );
 
                        iso_size bigint default -1
                );
 

-               create table backup_parts (
-                       id serial,
-                       backup_id int references backups(id),
-                       part_nr int not null check (part_nr > 0),
-                       tar_size bigint not null check (tar_size > 0),
-                       size bigint not null check (size > 0),
-                       md5 text not null,
-                       items int not null check (items > 0),
-                       date timestamp default now(),
-                       filename text not null,
-                       primary key(id)
-               );
-

                -- report backups and corresponding dvd
                -- report backups and corresponding dvd

-
-               create view backups_on_dvds as
-               select
-                       backups.id as id,
-                       hosts.name || ':' || shares.name as share,
-                       backups.num as num,
-                       backups.type as type,
-                       abstime(backups.date) as backup_date,
-                       backups.size as size,
-                       backups.inc_size as gzip_size,
-                       archive.id as archive_id,
-                       archive.dvd_nr
-               from backups
-               join shares on backups.shareid=shares.id
-               join hosts on shares.hostid = hosts.id
-               left outer join archive_backup on backups.id = archive_backup.backup_id
-               left outer join archive on archive_backup.archive_id = archive.id
-               where backups.parts > 0 and size > 0
-               order by backups.date
-               ;
-       });
+--
+--             create view backups_on_dvds as
+--             select
+--                     backups.id as id,
+--                     hosts.name || ':' || shares.name as share,
+--                     backups.num as num,
+--                     backups.type as type,
+--                     abstime(backups.date) as backup_date,
+--                     backups.size as size,
+--                     backups.inc_size as gzip_size,
+--                     archive.id as archive_id,
+--                     archive.dvd_nr
+--             from backups
+--             join shares on backups.shareid=shares.id
+--             join hosts on shares.hostid = hosts.id
+--             left outer join archive_backup on backups.id = archive_backup.backup_id
+--             left outer join archive on archive_backup.archive_id = archive.id
+--             where backups.parts > 0 and size > 0
+--             order by backups.date
+--             ;
+       })) {
+               warn "SQL: $sql\n";
+               $dbh->do( $sql );
+       }

 
        print "creating indexes: ";
 
 
        print "creating indexes: ";
 

diff --git a/lib/BackupPC/Search.pm b/lib/BackupPC/Search.pm
index f2dac44..77a68d3 100644 (file)
--- a/lib/BackupPC/Search.pm
+++ b/lib/BackupPC/Search.pm
@@ -374,35 +374,6 @@ sub getGzipSize($$)
        );
 }
 
        );
 }
 

-sub getVolumes($) {
-       my $id = shift;
-
-       my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize";
-
-       my $sth = $dbh->prepare(qq{
-               select
-                       size
-               from backup_parts
-               where backup_id = ?
-               order by part_nr asc
-       });
-
-       $sth->execute($id);
-
-       my $cumulative_size = 0;
-       my $volumes = 1;
-
-       while(my ($size) = $sth->fetchrow_array) {
-               if ($cumulative_size + $size > $max_archive_size) {
-                       $volumes++;
-                       $cumulative_size = $size;
-               } else {
-                       $cumulative_size += $size;
-               }
-       }
-
-       return ($volumes,$cumulative_size);
-}

 
 sub getBackupsNotBurned($) {
 
 
 sub getBackupsNotBurned($) {
 


@@ -414,36 +385,19 @@ sub getBackupsNotBurned($) {
 print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql order: $order\n";
 
        my $sql = qq{
 print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql order: $order\n";
 
        my $sql = qq{

-               SELECT 
-                       backups.hostID AS hostID,
-                       hosts.name AS host,
-                       shares.name AS share,
-                       backups.num AS backupnum,
-                       backups.type AS type,
-                       backups.date AS date,
-                       date_part('epoch',now()) - backups.date as age,
-                       backups.size AS size,
-                       backups.id AS id,
-                       backups.inc_size AS inc_size,
-                       backups.parts AS parts
-               FROM backups 
-               INNER JOIN shares       ON backups.shareID=shares.ID
-               INNER JOIN hosts        ON backups.hostID = hosts.ID
-               LEFT OUTER JOIN archive_backup ON archive_backup.backup_id = backups.id 
-               WHERE backups.inc_size > 0 AND backups.size > 0 AND backups.inc_deleted is false AND archive_backup.backup_id IS NULL AND backups.parts > 0
-               GROUP BY
-                       backups.hostID,
-                       hosts.name,
-                       shares.name,
-                       backups.num,
-                       backups.shareid,
-                       backups.id,
-                       backups.type,
-                       backups.date,
-                       backups.size,
-                       backups.inc_size,
-                       backups.parts
-               ORDER BY $order
+               SELECT
+                       p.id,
+                       p.filename,
+                       b.date,
+                       date_part('epoch',now()) - b.date as age,
+                       p.size,
+                       count(ab.date) as copies
+               FROM backup_parts p
+               JOIN backups b          ON b.id = p.backup_id
+               LEFT OUTER JOIN archive_parts  ap ON ap.backup_part_id = p.id
+               LEFT OUTER JOIN archive_burned ab ON ab.archive_id = ap.archive_id
+               GROUP BY p.id,filename,b.date,age,p.size,p.part_nr
+               ORDER BY b.date,p.part_nr

        };
        my $sth = $dbh->prepare( $sql );
        my @ret;
        };
        my $sth = $dbh->prepare( $sql );
        my @ret;


@@ -452,15 +406,6 @@ print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql orde
        while ( my $row = $sth->fetchrow_hashref() ) {
                $row->{'age'} = sprintf("%0.1f", ( $row->{'age'} / 86400 ) );
                #$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) );
        while ( my $row = $sth->fetchrow_hashref() ) {
                $row->{'age'} = sprintf("%0.1f", ( $row->{'age'} / 86400 ) );
                #$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) );

-
-               my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize";
-               if ($row->{size} > $max_archive_size) {
-                       ($row->{volumes}, $row->{inc_size_calc}) = getVolumes($row->{id});
-               }
-
-               # do some cluster calculation (approximate)
-               $row->{inc_size} = int(( ($row->{inc_size} + 1023 ) / 2 )  * 2);
-               $row->{inc_size_calc} ||= $row->{inc_size};

                push @ret, $row;
        }
       
                push @ret, $row;
        }
       


@@ -680,8 +625,8 @@ function update_size(name, checked, suma) {
                suma -= size;
        }
 
                suma -= size;
        }
 

-       var volumes = parseInt( element_id("prt" + name).value);
-       debug('update_size('+name+','+checked+') suma: '+suma+' volumes: '+volumes);
+       debug('update_size('+name+','+checked+') suma: '+suma);
+/* FIXME

        if (volumes > 1) {
                if (checked) {
                        element_id("volumes").innerHTML = "This will take "+volumes+" mediums!";
        if (volumes > 1) {
                if (checked) {
                        element_id("volumes").innerHTML = "This will take "+volumes+" mediums!";


@@ -693,7 +638,7 @@ function update_size(name, checked, suma) {
                        element_id("volumes").style.display = 'none';
                }
        }
                        element_id("volumes").style.display = 'none';
                }
        }

-
+*/

        return suma;
 }
 
        return suma;
 }
 


@@ -823,59 +768,34 @@ EOF3
                                <input type="checkbox" name="allFiles" id="allFiles" onClick="checkAll('allFiles');">
                        </td>
        } .
                                <input type="checkbox" name="allFiles" id="allFiles" onClick="checkAll('allFiles');">
                        </td>
        } .

-               sort_header($param, 'Share', 'share', 'center') .
-               sort_header($param, '#', 'num', 'center') .
-       qq{
-                       <td align="center">Type</td>
-       } .
+               sort_header($param, 'Filename', 'filename', 'left') .

                sort_header($param, 'Date', 'date', 'center') .
                sort_header($param, 'Age/days', 'age', 'center') .
                sort_header($param, 'Size', 'size', 'center') .
                sort_header($param, 'Date', 'date', 'center') .
                sort_header($param, 'Age/days', 'age', 'center') .
                sort_header($param, 'Size', 'size', 'center') .

-               sort_header($param, 'gzip size', 'incsize', 'center') .

        qq{
        qq{

-                       <td align="center">medias</td></tr>
+                       <td align="center">copies</td></tr>

        };
 
        my @color = (' bgcolor="#e0e0e0"', '');
 
        };
 
        my @color = (' bgcolor="#e0e0e0"', '');
 

-       my $i = 0;
-       my $host = '';
+       my $i = 1;
+#      my $img_url = $Conf{CgiImageDirURL};

 
        foreach my $backup ( getBackupsNotBurned($param) ) {
 
 
        foreach my $backup ( getBackupsNotBurned($param) ) {
 

-               if ($host ne $backup->{'host'}) {
-                       $i++;
-                       $host = $backup->{'host'};
-               }
-               my $ftype = "";
-
-               my $checkbox_key = $backup->{'hostid'}. '_' .$backup->{'backupnum'} . '_' . $backup->{'id'};
-
-               $retHTML .=
-                       '<tr' . $color[$i %2 ] . '>
-                       <td class="fview">';
-
-               if (($backup->{'inc_size'} || 0) > 0) {
-                       $retHTML .= '
-                       <input type="checkbox" name="fcb' . $checkbox_key . '" value="' . $checkbox_key . '" onClick="sumiraj(this);">';
-               }
-
-               my $img_url = $Conf{CgiImageDirURL};
-
-               $retHTML .=
-                       '</td>' .
-                       '<td align="right">' . $backup->{'host'} . ':' . $backup->{'share'} . '</td>' .
-                       '<td align="center">' . $backup->{'backupnum'} . '</td>' .
-                       '<td align="center">' . $backup->{'type'} . '</td>' .
-                       '<td align="center">' . epoch_to_iso( $backup->{'date'} ) . '</td>' .
-                       '<td align="center">' . $backup->{'age'} . '</td>' .
-                       '<td align="right">' . unit($backup->{'size'}) . '</td>' .
-                       '<td align="right">' . unit($backup->{'inc_size'}) . '</td>' .
-                       '<input type="hidden" id="fss'.$checkbox_key .'" value="'. $backup->{'inc_size_calc'} .'"></td>' .
-                       '<input type="hidden" id="prt'.$checkbox_key .'" value="'. $backup->{'volumes'} .'"></td>' .
-                       '<td align="left">' . ( qq{<img src="$img_url/icon-cd.gif" alt="media">} x $backup->{volumes} ) . '</td>' .
-
-                       "</tr>\n";
+               $retHTML .= join(''
+                       ,'<tr',$color[$i++%2],'>'
+                       ,'<td class="fview">'
+                               ,'<input type="checkbox" name="fcb',$backup->{id},'" value="',$backup->{id},'" onClick="sumiraj(this);">'
+                               ,'<input type="hidden" id="fss',$backup->{id},'" value="',$backup->{size},'">'
+                       ,'</td>'
+                       ,'<td align="left">', $backup->{'filename'}, '</td>'
+                       ,'<td align="center">', epoch_to_iso( $backup->{'date'} ), '</td>'
+                       ,'<td align="center">', $backup->{'age'}, '</td>'
+                       ,'<td align="right">', unit($backup->{'size'}), '</td>'
+                       ,'<td align="left">', '*' x $backup->{copies}, '</td>'
+                       ,"</tr>\n"
+               );

        }
 
        $retHTML .= "</table>";
        }
 
        $retHTML .= "</table>";