r9171@llin: dpavlin | 2006-01-31 23:04:30 +0100

 removed all direct updates of parts which is now handled by custom referential triggers on backups and backup_parts (which
 also check part_nr)

git-svn-id: svn+ssh://llin/home/dpavlin/private/svn/BackupPC/trunk@329 8392b6e1-25fa-0310-8288-cc32f8e212ea

diff --git a/bin/BackupPC_incPartsUpdate b/bin/BackupPC_incPartsUpdate
index 3a247ba..72a5ba0 100755 (executable)
--- a/bin/BackupPC_incPartsUpdate
+++ b/bin/BackupPC_incPartsUpdate
@@ -146,8 +146,7 @@ sub backup_inc_deleted($) {
        my $backup_id = shift;
        my $sth_inc_deleted = $dbh->prepare(qq{
                update backups set
-                       inc_deleted = true,
-                       parts = 0
+                       inc_deleted = true
                where id = ?
        });
        $sth_inc_deleted->execute($backup_id);

diff --git a/bin/BackupPC_tarIncCreate b/bin/BackupPC_tarIncCreate
index 0dc5c21..cb11239 100755 (executable)
--- a/bin/BackupPC_tarIncCreate
+++ b/bin/BackupPC_tarIncCreate
@@ -160,7 +160,6 @@ my $dbh = DBI->connect($dsn, $db_user, "", { RaiseError => 1, AutoCommit => 0} )
 my $sth_inc_size = $dbh->prepare(qq{
        update backups set
                inc_size = ?,
-               parts = ?,
                inc_deleted = false
        where id = ?
 });
@@ -310,7 +309,6 @@ sub new_tar_part {
 
                        $sth_inc_size->execute(
                                $total_increment_size,
-                               $part,
                                $backup_id
                        );
 
@@ -378,7 +376,7 @@ if (seedCache($Host, $ShareName, $Num)) {
        rmtree($tar_path);
 
        my $sth = $dbh->prepare(qq{
-               update backups set inc_size = 0, parts = 0, inc_deleted = true
+               update backups set inc_size = 0, inc_deleted = true
                where id = ?
        });
        $sth->execute($backup_id);

diff --git a/bin/BackupPC_updatedb b/bin/BackupPC_updatedb
index fc282ac..3657d28 100755 (executable)
--- a/bin/BackupPC_updatedb
+++ b/bin/BackupPC_updatedb
@@ -380,32 +380,65 @@ if ($opt{c}) {
        }
 
        print " creating triggers ";
-       $dbh->do( qq{
-               create or replace function backup_parts_check() returns trigger as '
-               declare
-                       b_parts integer;
-                       b_counted integer;
-                       b_id    integer;
-               begin
-                       if (TG_OP=''UPDATE'') then
-                               b_id := old.id;
-                               b_parts := old.parts;
-                       elsif (TG_OP = ''INSERT'') then
-                               b_id := new.id;
-                               b_parts := new.parts;
-                       end if;
-                       b_counted := (select count(*) from backup_parts where backup_id = b_id);
-                       if ( b_parts != b_counted ) then
-                               raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
-                       end if;
-                       return null;
-               end;
-               ' language plpgsql;
-
-               create trigger do_backup_parts_check
-                       after insert or update or delete on backups
-                       for each row execute procedure backup_parts_check();
-       });
+       $dbh->do( <<__END_OF_TRIGGER__ );
+
+create or replace function backup_parts_check() returns trigger as '
+declare
+       b_parts integer;
+       b_counted integer;
+       b_id    integer;
+begin
+       -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
+       if (TG_OP=''UPDATE'') then
+               b_id := new.id;
+               b_parts := new.parts;
+       elsif (TG_OP = ''INSERT'') then
+               b_id := new.id;
+               b_parts := new.parts;
+       end if;
+       b_counted := (select count(*) from backup_parts where backup_id = b_id);
+       -- raise notice ''backup % parts %'', b_id, b_parts;
+       if ( b_parts != b_counted ) then
+               raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
+       end if;
+       return null;
+end;
+' language plpgsql;
+
+create trigger do_backup_parts_check
+       after insert or update or delete on backups
+       for each row execute procedure backup_parts_check();
+
+create or replace function backup_backup_parts_check() returns trigger as '
+declare
+       b_id            integer;
+       my_part_nr      integer;
+       calc_part       integer;
+begin
+       if (TG_OP = ''INSERT'') then
+               -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
+               b_id = new.backup_id;
+               my_part_nr = new.part_nr;
+               execute ''update backups set parts = parts + 1 where id = '' || b_id;
+       elsif (TG_OP = ''DELETE'') then
+               -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
+               b_id = old.backup_id;
+               my_part_nr = old.part_nr;
+               execute ''update backups set parts = parts - 1 where id = '' || b_id;
+       end if;
+       calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
+       if ( my_part_nr != calc_part ) then
+               raise exception ''Update of backup_parts with backup_id % aborted, requested part_nr is % and calulated next is %'', b_id, my_part_nr, calc_part;
+       end if;
+       return null;
+end;
+' language plpgsql;
+
+create trigger do_backup_backup_parts_check
+       after insert or update or delete on backup_parts
+       for each row execute procedure backup_backup_parts_check();
+
+__END_OF_TRIGGER__
 
        print "...\n";
 

diff --git a/sql/09_backup_parts_trigger.sql b/sql/09_backup_parts_trigger.sql
index 163c538..c4fe684 100644 (file)
--- a/sql/09_backup_parts_trigger.sql
+++ b/sql/09_backup_parts_trigger.sql
@@ -6,9 +6,10 @@ declare
        b_counted integer;
        b_id    integer;
 begin
+       -- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
        if (TG_OP=''UPDATE'') then
-               b_id := old.id;
-               b_parts := old.parts;
+               b_id := new.id;
+               b_parts := new.parts;
        elsif (TG_OP = ''INSERT'') then
                b_id := new.id;
                b_parts := new.parts;
@@ -28,3 +29,34 @@ create trigger do_backup_parts_check
        after insert or update or delete on backups
        for each row execute procedure backup_parts_check();
 
+create or replace function backup_backup_parts_check() returns trigger as '
+declare
+       b_id            integer;
+       my_part_nr      integer;
+       calc_part       integer;
+begin
+       if (TG_OP = ''INSERT'') then
+               -- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
+               b_id = new.backup_id;
+               my_part_nr = new.part_nr;
+               execute ''update backups set parts = parts + 1 where id = '' || b_id;
+       elsif (TG_OP = ''DELETE'') then
+               -- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
+               b_id = old.backup_id;
+               my_part_nr = old.part_nr;
+               execute ''update backups set parts = parts - 1 where id = '' || b_id;
+       end if;
+       calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
+       if ( my_part_nr != calc_part ) then
+               raise exception ''Update of backup_parts with backup_id % aborted, requested part_nr is % and calulated next is %'', b_id, my_part_nr, calc_part;
+       end if;
+       return null;
+end;
+' language plpgsql;
+
+drop trigger do_backup_backup_parts_check on backup_parts;
+
+create trigger do_backup_backup_parts_check
+       after insert or update or delete on backup_parts
+       for each row execute procedure backup_backup_parts_check();
+