trunk/sql/backups_on_dvds.sql

-- 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
;
1	dpavlin	364	-- report backups and corresponding dvd
2
3	dpavlin	365	create view backups_on_dvds as
4	dpavlin	364	select
5			backups.id as id,
6			hosts.name \|\| ':' \|\| shares.name as share,
7			backups.num as num,
8			backups.type as type,
9			abstime(backups.date) as backup_date,
10			backups.size as size,
11			backups.inc_size as gzip_size,
12			archive.id as archive_id,
13			archive.dvd_nr
14			from backups
15			join shares on backups.shareid=shares.id
16			join hosts on shares.hostid = hosts.id
17			left outer join archive_backup on backups.id = archive_backup.backup_id
18			left outer join archive on archive_backup.archive_id = archive.id
19			where backups.parts > 0 and size > 0
20			order by backups.date
21			;