alter table backups alter column parts set default 0;

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;

drop trigger do_backup_parts_check on backups;

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();

