begin rewrite to select backup_parts

[BackupPC.git] / bin / BackupPC_ASA_SearchUpdate

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index 2ce01fc..b921594 100755 (executable)
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -78,6 +78,19 @@ warn "hosts: ",dump( $opt->host );
 
 #---- subs ----
 
+sub status {
+       my $text = shift;
+       $text =~ s{\s+$}{};
+       my $new = $0;
+       $new =~ s{^[\w\/]+/(\w+) }{$1 }; # strip path from process name
+       if ( $text =~ m/^\|/ ) {
+               $new =~ s/\|.*/$text/ or $new .= " $text";
+       } else {
+               $new =~ s/\s+.*/ $text/ or $new .= " $text";
+       }
+       $0 = $new;
+}
+
 sub fmt_time {
        my $t = shift || return;
        my $out = "";
@@ -170,6 +183,7 @@ sub hest_update {
                }
 
                print "$added";
+               status "| $added";
 
                $offset += EST_CHUNK;
 
@@ -210,7 +224,7 @@ if ($opt->create) {
 
        print "creating tables...\n";
 
-       $dbh->do( qq{
+       foreach my $sql ( split(/;/, qq{
                create table hosts (
                        ID      SERIAL          PRIMARY KEY,
                        name    VARCHAR(30)     NOT NULL,
@@ -245,6 +259,19 @@ if ($opt->create) {
                        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),
@@ -267,10 +294,11 @@ if ($opt->create) {
                        primary key(id)
                );      
 
-               create table archive_backup (
+               create table archive_parts (
                        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 (
@@ -281,40 +309,31 @@ if ($opt->create) {
                        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(),
-                       primary key(id)
-               );
-
                -- 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: ";
 
@@ -343,8 +362,6 @@ if ($opt->create) {
                $dbh->do( qq{ CREATE SEQUENCE $seq } );
        }
 
-=for later
-
        print " creating triggers ";
        $dbh->do( <<__END_OF_TRIGGER__ );
 
@@ -406,8 +423,6 @@ create trigger do_backup_backup_parts_check
 
 __END_OF_TRIGGER__
 
-=cut
-
        print "...\n";
 
        $dbh->commit;
@@ -440,7 +455,7 @@ INSERT INTO hosts (name, IP) VALUES (?,?)
 });
 
 $sth->{hosts_by_name} = $dbh->prepare(qq{
-SELECT ID FROM hosts WHERE name=?
+SELECT id FROM hosts WHERE name=?
 });
 
 $sth->{backups_count} = $dbh->prepare(qq{
@@ -518,8 +533,9 @@ foreach my $host_key (@hosts) {
                        fmt_time($backup->{endTime} - $backup->{startTime})
                );
                print $share_header unless $opt->quiet;
+               status "$hostname $backupNum $share_header";
 
-               my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_first => 1 });
+               my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_increment => 1 });
 
                foreach my $share ($files->shareList($backupNum)) {
 
@@ -572,16 +588,21 @@ foreach my $host_key (@hosts) {
                        }
 
                        my $dur = (time() - $t) || 1;
-                       printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
+                       my $status = sprintf("%d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]",
                                $nf, $f, $nd, $d,
                                ($size / 1024 / 1024),
                                ( ($f+$d) / $dur ),
                                fmt_time($dur)
                        );
+                       print " $status\n";
+                       status "$hostname $backupNum $status";
 
                        if ($nf + $nd > 0) {
-                               eval { hest_update($hostID, $shareID, $backupNum) };
-                               warn "ERROR: $@" if $@;
+                               status "$hostname $backupNum full-text | indexing";
+                               #eval { hest_update($hostID, $shareID, $backupNum) };
+                               #warn "ERROR: $@" if $@;
+                               hest_update($hostID, $shareID, $backupNum);
+                               # eval breaks our re-try logic
                        }
                }