Re: [Bacula-devel] Incrementally building the file tree for restores

[Kern S. <ke...@si...>]

On Wednesday 13 April 2005 00:20, Mike Acar wrote:
> Kern Sibbald <ke...@si...> wrote:
> > > > or probably better yet, simply fill in the subdirectory when the user
> > > > clicks on it?
> > >
> > > This is exactly what I want to do :) But if I have a backup that
> > > contains /var/spool/mail/mike, then I naturally want to have my
> > > listview first display /, and expand that to contain /var/, and then
> > > /var/spool/, etc. Do you see an efficient way to do it?
> >
> > I think I am missing something here since the answer seems obvious.
>
> Perhaps I should have said "Do you see an efficient way to query for
> each subdirectory in turn as the user clicks?".

Well, let me start with your next question, then at the bottom, I will propose 
an "efficient" method.

>
> > I would simply do the root directory first. All the subdirectories are
> > collapsed (i.e. their contents not visible) then when the user clicks
> > on a subdirectory, it would be expanded exactly as the root was, and
> > so on.
>
> Right. So: How do I find the subdirectories of the root? Something like
>
>     select Path from Path where Path like '/%/' and Path not like '/%/%/'

You need to do something like:

  select distinct Path.Path from File,Path where File.JobId=xxx and
    Path.PathId=File.PathId and Path.Pathid like '/%/' and Path.Path not
    like '/%/%/';

For finding the subdirectories of /etc, it would be:
 select distinct Path.Path from File,Path where File.JobId=xxx and
    Path.PathId=File.PathId and Path.Pathid like '/etc/%/' and Path.Path not
    like '/etc/%/%/';

Now, for a full backup, there can be quite a number of JobIds so the 
File.JobId=xxx would in fact probably be something like
File.JobId in (xxx, yyy, zzz)

Here is a Query that I use to get all files in a directory with no recursion:

   "SELECT Job.JobId,File.FileIndex FROM Job,File,Path,Filename,Client "
   "WHERE Job.JobId IN (%s) "
   "WHERE Job.JobId=File.JobId "
   "AND Path.Path='%s' "
   "AND Client.Name='%s' "
   "AND Job.ClientId=Client.ClientId "
   "AND Path.PathId=File.Pathid "
   "AND Filename.FilenameId=File.FilenameId "
   "GROUP BY File.FileIndex ";



>
> will find every subdirectory of /, in every backup. Clearly this is
> wrong, so we should only show the subdirectories which are ancestors of
> files backed up in this job. The only place a Path has a relation to a
> JobId is in the File record, right? And the only Path the File has a
> relation to is the directory which contains it. So in order to see if I
> should show a particular subdirectory of /, I would have to traverse all
> of its subdirectories looking for a File with the JobId I want to
> restore.

Now how could you make this *efficient*.  Some SQL guru could probably answer 
this better than I can, but my solution would be to create a new table of 
correspondence between JobId and Path.  For MySQL it might be something like:

CREATE TABLE JobIdPath (
   JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
   PRIMARY KEY(JobId),
   );
 
Fill it in with:

INSERT INTO JobIdPath SELECT DISTINCT  Job.JobId, File.PathId 
  FROM Job, File WHERE Job.JobId=File.JobId;

Now, the first time you run this, it will undoubtedly take some time.  
However, you can then run it after each job and it will be quite fast with:

INSERT INTO JobIdPath SELECT DISTINCT  Job.JobId, File.PathId 
  FROM Job, File WHERE Job.JobId=xxx AND Job.JobId=File.JobId;

Now you will have a table that will immediately give you want you want. To 
pull a full list of all the directories by JobId, you would do:

SELECT Path.Path FROM JobIdPath,Path WHERE JobIdPath.JobId=xxx ORDER BY 
Path.Path; 

This will be blindingly fast.

I haven't run most of the SQL commands above so there are undoubtedly some 
glitches, but hopefully it will give you the idea.
  
-- 
Best regards,

Kern

  (">
  /\
  V_V