--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
+--left outer join archive_backup_parts on backups.id = archive_backup_parts.backup_id
+--left outer join archive on archive_backup_parts.archive_id = archive.id
--where backups.parts > 0 and size > 0
--order by backups.date
--;
FROM backups
INNER JOIN hosts ON (hosts.id=backups.hostid)
INNER JOIN shares ON (shares.id=backups.shareid)
- INNER JOIN archive_backup ON (archive_backup.backup_id=backups.id)
- INNER JOIN archive_burned ON (archive_burned.archive_id=archive_backup.archive_id)
+ INNER JOIN archive_backup_parts ON (archive_backup_parts.backup_id=backups.id)
+ INNER JOIN archive_burned ON (archive_burned.archive_id=archive_backup_parts.archive_id)
GROUP BY 1, 2, 3
HAVING MAX(archive_burned.copy)>=2;
};
# do we have to add tables for burned media?
if ( $param->{burned} ) {
$sql_from .= qq{
- LEFT OUTER JOIN archive_backup on archive_backup.backup_id = backups.id
- LEFT OUTER JOIN archive_burned on archive_burned.archive_id = archive_backup.archive_id
+ LEFT OUTER JOIN archive_backup_parts on backup_id = backups.id
+ LEFT OUTER JOIN archive_burned on archive_burned.archive_id = archive_id
};
}
$sth_archived ||= $dbh->prepare(qq{
select
- dvd_nr, note,
+ archive.dvd_nr, note,
count(archive_burned.copy) as copies
from archive
inner join archive_burned on archive_burned.archive_id = archive.id
- inner join archive_backup on archive.id = archive_backup.archive_id
- inner join backups on backups.id = archive_backup.backup_id
+ inner join archive_backup_parts on archive.id = archive_backup_parts.archive_id
+ inner join backups on backups.id = archive_backup_parts.backup_id
inner join hosts on hosts.id = backups.hostid
inner join shares on shares.id = backups.shareid
where hosts.name = ? and shares.name = ? and backups.num = ?
- group by dvd_nr, note
+ group by archive.dvd_nr, note
});
my @mediums;