On 20/08/10, Martin Simmons (martin AT lispworks DOT com) wrote:
> >>>>> On Wed, 18 Aug 2010 23:15:59 +0100, Rory Campbell-Lange said:
> > > > Can someone point me to the relationship between Media and File?
...
> > This shows that each backup starts from 1 (0 for one of them?!), so there 
> > is no
> > direct relationship between the jobmedia and file tables. Correct?
> 
> No, you are still using the wrong columns.  It should be min(fileindex),
> max(fileindex) in the first query.

Oh -- my apologies for the foolish error, Martin. Thanks for pointing
that out to me. I'm much obliged. My query now works.

For anyone who might be googling this, here is a postgres query that
will show you the tape on which a particular file is stored:

   SELECT j.jobid, j.name AS jobname, jmlj.volumename, jmlj.slot, p.path, 
fn.name AS filename, f.md5
   FROM job j, filename fn, path p, file f
   LEFT JOIN ( SELECT jj.jobid, jj.mediaid, m.volumename, m.slot, 
jj.firstindex, jj.lastindex
           FROM jobmedia jj, media m
          WHERE jj.mediaid = m.mediaid
          ) jmlj ON jmlj.jobid = f.jobid AND f.fileindex >= jmlj.firstindex AND 
f.fileindex <= jmlj.lastindex
  WHERE j.jobid = 9 AND j.jobid = f.jobid AND f.pathid = p.pathid AND 
f.filenameid = fn.filenameid
  ORDER BY f.fileid;

-- 
Rory Campbell-Lange
rory AT campbell-lange DOT net

------------------------------------------------------------------------------
This SF.net email is sponsored by 

Make an app they can't live without
Enter the BlackBerry Developer Challenge
http://p.sf.net/sfu/RIM-dev2dev 
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users