trunk/sql/dvd_backup_parts_consistency_report.sql

-- check consistency of data for one DVD image. This is more detailed than
-- error message from BackupPC_Bur
select
        hosts.name || '_' ||
        shares.name || '_' ||
        backups.num as increment_name,
        backups.date::abstime,
        backups.type,
        backups.size,
        backups.parts,
        count(backup_parts.backup_id) as backup_parts,
        backups.inc_size,
        backups.inc_deleted
from archive_backup
join archive on archive_id = archive.id
join backups on backup_id = backups.id
join shares on shareid = shares.id
join hosts on backups.hostid = hosts.id
full outer join backup_parts on backups.id = backup_parts.backup_id
-- where dvd_nr = 14
group by
        hosts.name,
        shares.name,
        backups.num,
        backups.date,
        backups.type,
        backups.size,
        backups.parts,
        backups.inc_size,
        backups.inc_deleted
having count(backup_parts.backup_id) <> parts
order by inc_size desc
;
1	dpavlin	301	-- check consistency of data for one DVD image. This is more detailed than
2			-- error message from BackupPC_Bur
3			select
4			hosts.name \|\| '_' \|\|
5			shares.name \|\| '_' \|\|
6			backups.num as increment_name,
7			backups.date::abstime,
8			backups.type,
9			backups.size,
10			backups.parts,
11			count(backup_parts.backup_id) as backup_parts,
12			backups.inc_size,
13			backups.inc_deleted
14			from archive_backup
15			join archive on archive_id = archive.id
16			join backups on backup_id = backups.id
17			join shares on shareid = shares.id
18			join hosts on backups.hostid = hosts.id
19			full outer join backup_parts on backups.id = backup_parts.backup_id
20			-- where dvd_nr = 14
21			group by
22			hosts.name,
23			shares.name,
24			backups.num,
25			backups.date,
26			backups.type,
27			backups.size,
28			backups.parts,
29			backups.inc_size,
30			backups.inc_deleted
31			having count(backup_parts.backup_id) <> parts
32			order by inc_size desc
33			;