find backups without corresponding rows in backup_parts (database consistency check)

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

diff --git a/sql/backups_without_packup_parts.sql b/sql/backups_without_packup_parts.sql
new file mode 100644 (file)
index 0000000..d41a899
--- /dev/null
+++ b/sql/backups_without_packup_parts.sql
@@ -0,0 +1,12 @@
+-- find backups without corresponding rows in backup_parts (database consistency check)
+select hosts.name as host, shares.name as share, backups.num, backups.date::abstime, type, size, inc_size, inc_deleted, parts, dvd_nr
+from backups
+join hosts on hostid=hosts.id
+join shares on shareid=shares.id
+full outer join archive_backup on archive_backup.backup_id = backups.id
+full outer join archive on archive.id = archive_backup.archive_id
+where backups.id not in (select backup_id from backup_parts)
+       and inc_deleted is false
+       and size > 0
+order by host,share,num
+;