From ffbf3b45b24613d268bbb64a901131612b86b4d6 Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Fri, 28 Jan 2011 19:33:45 +0100 Subject: [PATCH] create archive_backup_parts view and use it --- bin/BackupPC_ASA_BurnArchiveMedia | 80 ++++++++++--------------------- bin/BackupPC_ASA_SearchUpdate | 33 ++++++++++--- 2 files changed, 51 insertions(+), 62 deletions(-) diff --git a/bin/BackupPC_ASA_BurnArchiveMedia b/bin/BackupPC_ASA_BurnArchiveMedia index f75be34..24107cc 100755 --- a/bin/BackupPC_ASA_BurnArchiveMedia +++ b/bin/BackupPC_ASA_BurnArchiveMedia @@ -15,7 +15,7 @@ use File::Path; use Cwd qw/abs_path/; use Data::Dumper; -my $debug = 0; +my $debug = $ENV{DEBUG} || 1; # set this to 1 to prompt for DVD removal for each selected item. my $prompt_for_delete = shift @ARGV; $|=1; @@ -112,20 +112,14 @@ sub dumpArchive2XML($$$) my $backups_sql = q{ SELECT - backups.id AS backup_id, - hosts.name AS host, - backups.num AS num, - backups.date AS date, - shares.name AS share, - backups.size AS backup_size, - backups.inc_size AS compress_size - FROM backups - JOIN backup_parts ON backup_parts.backup_id = backups.id - JOIN archive_parts ON archive_parts.backup_id = backups.id - JOIN hosts ON hosts.id = backups.hostid - JOIN shares ON shares.id = backups.shareid - WHERE archive_parts.backup_id = backups.id - AND archive_parts.archive_id = ? + backup_id, + host, + num, + date, + share, + size + FROM archive_backup_parts + WHERE archive_id = ? ORDER BY hosts.name, shares.name, backups.num }; @@ -318,36 +312,22 @@ sub delete_dvd($) { my $sth_archive_backup_parts = $dbh->prepare( qq{ SELECT - backup_parts.backup_id, + backup_id, archive_id, - hosts.name as host, - shares.name as share, - backups.num as num, - backups.parts as parts, - backup_parts.part_nr as part_nr, - backup_parts.size as part_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 + host, + share, + num, + part_nr, + parts, + size, + md5, + items, + filename + FROM archive_backup_parts WHERE archive.dvd_nr = ? ORDER BY backup_id, part_nr }); -my $sth_archive_backup_check = $dbh->prepare( qq{ - SELECT - count(*) - FROM archive - JOIN archive_parts ON archive_id = archive.id - JOIN backup_parts ON backup_part_id = backup_parts.id - WHERE dvd_nr = ? -}); - my $sth_archive_burned = $dbh->prepare( qq{ insert into archive_burned (archive_id, iso_size, part, copy) @@ -372,18 +352,6 @@ foreach my $copy_nr ( 1 .. $copies ) { $sth_archive_backup_parts->execute($dvd_nr); - $sth_archive_backup_check->execute($dvd_nr); - - my ($parts_nr, $check_nr) = ($sth_archive_backup_parts->rows, $sth_archive_backup_check->fetchrow_array); - - if ($parts_nr != $check_nr) { - warn "ERROR: DVD #$dvd_nr is still not inconsistent state. Some backup parts are ", - ($parts_nr < $check_nr) ? "missing ($parts_nr < $check_nr)" : "extra ($parts_nr > $check_nr)", - " you should re-create this DVD after BackupPC_incPartsUpdate finish\n"; - delete_dvd( $dvd_nr ); - next; - } - if ($sth_archive_backup_parts->rows == 0) { warn "ERROR: no backup parts found for $dvd_nr. You should re-create that DVD.\n"; next if delete_dvd( $dvd_nr ); @@ -399,12 +367,12 @@ foreach my $copy_nr ( 1 .. $copies ) { my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize"; while (my $row = $sth_archive_backup_parts->fetchrow_hashref) { - if (($v->{size} || 0) + $row->{part_size} > $max_archive_size) { + if (($v->{size} || 0) + $row->{size} > $max_archive_size) { push @volumes, $v; $v = {}; } - $v->{size} += $row->{part_size}; - $v_size += $row->{part_size}; + $v->{size} += $row->{size}; + $v_size += $row->{size}; # this part my $p; @@ -416,7 +384,7 @@ foreach my $copy_nr ( 1 .. $copies ) { } push @volumes, $v if ($v); - #warn "# volumes: ",Dumper(\@volumes)," total size: ", fmt_mb($v_size), "\n"; + warn "# volumes: ",Dumper(\@volumes)," total size: ", fmt_mb($v_size), "\n" if $debug; # check available disk space diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate index 0e4df2f..e39ffec 100755 --- 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; @@ -674,6 +668,8 @@ create table files ( primary key(id) ); +create sequence dvd_nr; + create table archive ( id serial, dvd_nr int not null, @@ -721,6 +717,31 @@ create table archive_burned ( --; +-- used by BackupPC_ASA_BurnArchiveMedia +CREATE VIEW archive_backup_parts AS +SELECT + backup_parts.backup_id, + archive_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 +ORDER BY backup_id, part_nr +; + + create or replace function backup_parts_check() returns trigger as ' declare b_parts integer; -- 2.20.1