check backup_parts against data in backups

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

diff --git a/sql/backup_parts_consistency_report.sql b/sql/backup_parts_consistency_report.sql
new file mode 100644 (file)
index 0000000..8655c8d
--- /dev/null
+++ b/sql/backup_parts_consistency_report.sql
@@ -0,0 +1,31 @@
+-- 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 backups
+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 backups.size > 0
+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 date asc
+;