trunk/sql/backups_full.sql

-- report number of files and staticics for each backup

select
        hosts.name as host,
        shares.name as share,
        backupnum as num,
        abstime(backups.date) as backup_date,
        backups.type as type,
        abstime(min(files.date)) as from,
        abstime(max(files.date)) as to,
        count(*) as files,
        sum(files.size) as size,
        sum(backups.size) as backup_size
from files
join shares on files.shareid=shares.id
join hosts on shares.hostid = hosts.id
join backups on shares.hostid = backups.hostid
        and files.backupnum = backups.num
        and shares.id = backups.shareid
group by shares.name, hosts.name, backupnum, backups.date, backups.type
order by backups.date;
1	dpavlin	103	-- report number of files and staticics for each backup
2
3			select
4			hosts.name as host,
5			shares.name as share,
6			backupnum as num,
7	dpavlin	238	abstime(backups.date) as backup_date,
8	dpavlin	103	backups.type as type,
9	dpavlin	238	abstime(min(files.date)) as from,
10			abstime(max(files.date)) as to,
11	dpavlin	103	count(*) as files,
12			sum(files.size) as size,
13			sum(backups.size) as backup_size
14			from files
15			join shares on files.shareid=shares.id
16			join hosts on shares.hostid = hosts.id
17			join backups on shares.hostid = backups.hostid
18			and files.backupnum = backups.num
19			and shares.id = backups.shareid
20			group by shares.name, hosts.name, backupnum, backups.date, backups.type
21			order by backups.date;