added common BackupPC::Search::host_backup_nums

[BackupPC.git] / bin / BackupPC_ASA_SearchUpdate

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index 0e4df2f..ba499f9 100755 (executable)
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -249,12 +249,6 @@ if ($opt->create) {
                do_index($index);
        }
 
-       print " creating sequence: ";
-       foreach my $seq (qw/dvd_nr/) {
-               print "$seq ";
-               $dbh->do( qq{ CREATE SEQUENCE $seq } );
-       }
-
        print "...\n";
 
        $dbh->commit;
@@ -318,14 +312,15 @@ my $host_nr = 0;
 foreach my $host_key (@hosts) {
 
        my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
+       $hostname = lc $hostname;
 
-       next if $opt->host && ! grep { m/^$hostname$/ } @{ $opt->host };
+       next if $opt->host && ! grep { m/^$hostname$/i } @{ $opt->host };
 
        $sth->{hosts_by_name}->execute($hostname);
 
        unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
                $sth->{insert_hosts}->execute(
-                       $hosts->{$host_key}->{'host'},
+                       $hostname,
                        $hosts->{$host_key}->{'ip'}
                );
 
@@ -674,6 +669,8 @@ create table files (
        primary key(id)
 );
 
+create sequence dvd_nr;
+
 create table archive (
        id              serial,
        dvd_nr          int not null,
@@ -714,13 +711,52 @@ create table archive_burned (
 --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
+--left outer join archive_backup_parts on backups.id = archive_backup_parts.backup_id
+--left outer join archive on archive_backup_parts.archive_id = archive.id
 --where backups.parts > 0 and size > 0
 --order by backups.date
 --;
 
 
+-- used by BackupPC_ASA_BurnArchiveMedia
+CREATE VIEW archive_backup_parts AS
+SELECT
+       backup_parts.backup_id,
+       archive_id,
+       dvd_nr,
+       backup_part_id,
+       hosts.name as host,
+       shares.name as share,
+       backups.num as num,
+       backups.date as date,
+       backup_parts.part_nr as part_nr,
+       backups.parts as parts,
+       backup_parts.size as size,
+       backup_parts.md5 as md5,
+       backup_parts.items,
+       backup_parts.filename
+FROM backup_parts
+JOIN archive_parts ON backup_parts.id = backup_part_id
+JOIN archive       ON archive_id = archive.id
+JOIN backups       ON backup_id = backups.id
+JOIN hosts         ON hostid = hosts.id
+JOIN shares        ON shareid = shares.id
+;
+
+
+CREATE VIEW backups_burned AS
+SELECT backup_parts.backup_id,
+count(backup_parts.backup_id) as backup_parts,
+count(archive_burned.archive_id) AS burned_parts,
+count(backup_parts.backup_id) = count(archive_burned.archive_id) as burned
+   FROM backup_parts 
+   left outer JOIN archive_parts ON backup_part_id = backup_parts.id
+   left join archive on archive.id = archive_id
+   left outer join archive_burned on archive_burned.archive_id = archive.id
+  GROUP BY backup_parts.backup_id ;
+
+
+-- triggers for backup_parts consistency
 create or replace function backup_parts_check() returns trigger as '
 declare
        b_parts integer;