-- report backups and corresponding dvd

create view backups_on_dvds as
select
	backups.id as id,
	hosts.name || ':' || shares.name as share,
	backups.num as num,
	backups.type as type,
	abstime(backups.date) as backup_date,
	backups.size as size,
	backups.inc_size as gzip_size,
	archive.id as archive_id,
	archive.dvd_nr
from backups
join shares on backups.shareid=shares.id
join hosts on shares.hostid = hosts.id
left outer join archive_backup on backups.id = archive_backup.backup_id
left outer join archive on archive_backup.archive_id = archive.id
where backups.parts > 0 and size > 0
order by backups.date
;
