-# we have JobStatus IN ('T', 'A', 'E') and we must
-
-# Data acces subs from here. Interaction with SGBD and caching
-
-# This sub retrieves the list of jobs corresponding to the jobs selected in the
-# GUI and stores them in @CurrentJobIds
-sub set_job_ids_for_date
-{
- my ($dbh, $client, $date, $only_ok)=@_;
-
- if (!$client or !$date) {
- return ();
- }
-
- my $status = get_wanted_job_status($only_ok);
-
- # The algorithm : for a client, we get all the backups for each
- # fileset, in reverse order Then, for each fileset, we store the 'good'
- # incrementals and differentials until we have found a full so it goes
- # like this : store all incrementals until we have found a differential
- # or a full, then find the full #
-
- my $query = "SELECT JobId, FileSet, Level, JobStatus
- FROM Job, Client, FileSet
- WHERE Job.ClientId = Client.ClientId
- AND FileSet.FileSetId = Job.FileSetId
- AND EndTime <= '$date'
- AND Client.Name = '$client'
- AND Type IN ('B')
- AND JobStatus IN ($status)
- ORDER BY FileSet, JobTDate DESC";
-
- print $query,"\n" if $debug;
- my @CurrentJobIds;
- my $result = $dbh->selectall_arrayref($query);
- my %progress;
- foreach my $refrow (@$result)
- {
- my $jobid = $refrow->[0];
- my $fileset = $refrow->[1];
- my $level = $refrow->[2];
-
- defined $progress{$fileset} or $progress{$fileset}='U'; # U for unknown
-
- next if $progress{$fileset} eq 'F'; # It's over for this fileset...
-
- if ($level eq 'I')
- {
- next unless ($progress{$fileset} eq 'U' or $progress{$fileset} eq 'I');
- push @CurrentJobIds,($jobid);
- }
- elsif ($level eq 'D')
- {
- next if $progress{$fileset} eq 'D'; # We allready have a differential
- push @CurrentJobIds,($jobid);
- }
- elsif ($level eq 'F')
- {
- push @CurrentJobIds,($jobid);
- }
-
- my $status = $refrow->[3] ;
- if ($status eq 'T') { # good end of job
- $progress{$fileset} = $level;
- }
- }
- print Data::Dumper::Dumper(\@CurrentJobIds) if $debug;
-
- return @CurrentJobIds;
-}
-
-# Lists all directories contained inside a directory.
-# Uses the current dir, the client name, and CurrentJobIds for visibility.
-# Returns an array of dirs
-sub list_dirs
-{
- my ($self,$dir,$client)=@_;
- print "list_dirs($dir, $client)\n";
-
- # Is data allready cached ?
- if (not $self->{dirtree}->{$client})
- {
- $self->cache_dirs($client);
- }
-
- if ($dir ne '' and substr $dir,-1 ne '/')
- {
- $dir .= '/'; # In the db, there is a / at the end of the dirs ...
- }
- # Here, the tree is cached in ram
- my @dir = split('/',$dir,-1);
- pop @dir; # We don't need the empty trailing element
-
- # We have to get the reference of the hash containing $dir contents
- # Get to the root
- my $refdir=$self->{dirtree}->{$client};
-
- # Find the leaf
- foreach my $subdir (@dir)
- {
- if ($subdir eq '')
- {
- $subdir = '/';
- }
- $refdir = $refdir->[0]->{$subdir};
- }
-
- # We reached the directory
- my @return_list;
- DIRLOOP:
- foreach my $dir (sort(keys %{$refdir->[0]}))
- {
- # We return the directory's content : only visible directories
- foreach my $jobid (reverse(sort(@{$self->{CurrentJobIds}})))
- {
- if (defined $refdir->[0]->{$dir}->[1]->{$jobid})
- {
- my $dirname = $refdir->[0]->{$dir}->[2]; # The real dirname...
- push @return_list,($dirname);
- next DIRLOOP; # No need to waste more CPU cycles...
- }
- }
- }
- print "LIST DIR : ", Data::Dumper::Dumper(\@return_list),"\n";
- return @return_list;
-}
-
-
-# List all files in a directory. dir as parameter, CurrentJobIds for visibility
-# Returns an array of dirs
-sub list_files
-{
- my ($self, $dir)=@_;
- my $dbh = $self->{dbh};
-
- my $empty = [];
-
- print "list_files($dir)\n";
-
- if ($dir ne '' and substr $dir,-1 ne '/')
- {
- $dir .= '/'; # In the db, there is a / at the end of the dirs ...
- }
-
- my $query = "SELECT Path.PathId FROM Path WHERE Path.Path = '$dir'";
- print $query,"\n" if $debug;
- my @list_pathid=();
- my $result = $dbh->selectall_arrayref($query);
- foreach my $refrow (@$result)
- {
- push @list_pathid,($refrow->[0]);
- }
-
- if (@list_pathid == 0)
- {
- print "No pathid found for $dir\n" if $debug;
- return $empty;
- }
-
- my $inlistpath = join (',', @list_pathid);
- my $inclause = join (',', @{$self->{CurrentJobIds}});
- if ($inclause eq '')
- {
- return $empty;
- }
-
- $query =
-"SELECT listfiles.id, listfiles.Name, File.LStat, File.JobId
- FROM
- (SELECT Filename.Name, max(File.FileId) as id
- FROM File, Filename
- WHERE File.FilenameId = Filename.FilenameId
- AND Filename.Name != ''
- AND File.PathId IN ($inlistpath)
- AND File.JobId IN ($inclause)
- GROUP BY Filename.Name
- ORDER BY Filename.Name) AS listfiles,
-File
-WHERE File.FileId = listfiles.id";
-
- print $query,"\n" if $debug;
- $result = $dbh->selectall_arrayref($query);
-
- return $result;
-}
-
-sub refresh_screen
-{
- Gtk2->main_iteration while (Gtk2->events_pending);
-}
-
-# For the dirs, because of the db schema, it's inefficient to get the
-# directories contained inside other directories (regexp match or tossing
-# lots of records...). So we load all the tree and cache it. The data is
-# stored in a structure of this form :
-# Each directory is an array.
-# - In this array, the first element is a ref to next dir (hash)
-# - The second element is a hash containing all jobids pointing
-# on an array containing their lstat (or 1 if this jobid is there because
-# of dependencies)
-# - The third is the filename itself (it could get mangled because of
-# the hashing...)
-
-# So it looks like this :
-# $reftree->[ { 'dir1' => $refdir1
-# 'dir2' => $refdir2
-# ......
-# },
-# { 'jobid1' => 'lstat1',
-# 'jobid2' => 'lstat2',
-# 'jobid3' => 1 # This one is here for "visibility"
-# },
-# 'dirname'
-# ]
-
-# Client as a parameter
-# Returns an array of dirs
-sub cache_dirs
-{
- my ($self, $client) = @_;
- print "cache_dirs()\n";
-
- $self->{dirtree}->{$client} = []; # reset cache
- my $dbh = $self->{dbh};
-
- # TODO : If we get here, things could get lenghty ... draw a popup window .
- my $widget = Gtk2::MessageDialog->new($self->{mainwin},
- 'destroy-with-parent',
- 'info', 'none',
- 'Populating cache');
- $widget->show;
-
- # We have to build the tree, as it's the first time it is asked...
-
-
- # First, we only need the jobids of the selected server.
- # It's not the same as @CurrentJobIds (we need ALL the jobs)
- # We get the JobIds first in order to have the best execution
- # plan possible for the big query, with an in clause.
- my $query;
- my $status = get_wanted_job_status($self->{pref}->{use_ok_bkp_only});
- $query =
-"SELECT JobId
- FROM Job,Client
- WHERE Job.ClientId = Client.ClientId
- AND Client.Name = '$client'
- AND Job.JobStatus IN ($status)
- AND Job.Type = 'B'";
-
- print $query,"\n" if $debug;
- my $result = $dbh->selectall_arrayref($query);
- refresh_screen();
-
- my @jobids;
- foreach my $record (@{$result})
- {
- push @jobids,($record->[0]);
- }
- my $inclause = join(',',@jobids);
- if ($inclause eq '')
- {
- $widget->destroy();
- $self->set_status("No previous backup found for $client");
- return ();
- }
-
-# Then, still to help dear mysql, we'll retrieve the PathId from empty Path (directory entries...)
- my @dirids;
- $query =
-"SELECT Filename.FilenameId FROM Filename WHERE Filename.Name=''";
-
- print $query,"\n" if $debug;
- $result = $dbh->selectall_arrayref($query);
- refresh_screen();
-
- foreach my $record (@{$result})
- {
- push @dirids,$record->[0];
- }
- my $dirinclause = join(',',@dirids);
-
- # This query is a bit complicated :
- # whe need to find all dir entries that should be displayed, even
- # if the directory itself has no entry in File table (it means a file
- # is explicitely chosen in the backup configuration)
- # Here's what I wanted to do :
-# $query =
-# "
-# SELECT T1.Path, T2.Lstat, T2.JobId
-# FROM ( SELECT DISTINCT Path.PathId, Path.Path FROM File, Path
-# WHERE File.PathId = Path.PathId
-# AND File.JobId IN ($inclause)) AS T1
-# LEFT JOIN
-# ( SELECT File.Lstat, File.JobId, File.PathId FROM File
-# WHERE File.FilenameId IN ($dirinclause)
-# AND File.JobId IN ($inclause)) AS T2
-# ON (T1.PathId = T2.PathId)
-# ";
- # It works perfectly with postgresql, but mysql doesn't seem to be able
- # to do the hash join correcty, so the performance sucks.
- # So it will be done in 4 steps :
- # o create T1 and T2 as temp tables
- # o create an index on T2.PathId
- # o do the query
- # o remove the temp tables
- $query = "
-CREATE TEMPORARY TABLE T1 AS
-SELECT DISTINCT Path.PathId, Path.Path FROM File, Path
-WHERE File.PathId = Path.PathId
- AND File.JobId IN ($inclause)
-";
- print $query,"\n" if $debug;
- $dbh->do($query);
- refresh_screen();
-
- $query = "
-CREATE TEMPORARY TABLE T2 AS
-SELECT File.Lstat, File.JobId, File.PathId FROM File
-WHERE File.FilenameId IN ($dirinclause)
- AND File.JobId IN ($inclause)
-";
- print $query,"\n" if $debug;
- $dbh->do($query);
- refresh_screen();
-
- $query = "
-CREATE INDEX tmp2 ON T2(PathId)
-";
- print $query,"\n" if $debug;
- $dbh->do($query);
- refresh_screen();
-
- $query = "
-SELECT T1.Path, T2.Lstat, T2.JobId
-FROM T1 LEFT JOIN T2
-ON (T1.PathId = T2.PathId)
-";
- print $query,"\n" if $debug;
- $result = $dbh->selectall_arrayref($query);
- refresh_screen();
-
- my $rcount=0;
- foreach my $record (@{$result})
- {
- if ($rcount > 15000) {
- refresh_screen();
- $rcount=0;
- } else {
- $rcount++;
- }
- # Dirty hack to force the string encoding on perl... we don't
- # want implicit conversions
- my $path = pack "U0C*", unpack "C*",$record->[0];
-
- my @path = split('/',$path,-1);
- pop @path; # we don't need the trailing empty element
- my $lstat = $record->[1];
- my $jobid = $record->[2];
-
- # We're going to store all the data on the cache tree.
- # We find the leaf, then store data there
- my $reftree=$self->{dirtree}->{$client};
- foreach my $dir(@path)
- {
- if ($dir eq '')
- {
- $dir = '/';
- }
- if (not defined($reftree->[0]->{$dir}))
- {
- my @tmparray;
- $reftree->[0]->{$dir}=\@tmparray;
- }
- $reftree=$reftree->[0]->{$dir};
- $reftree->[2]=$dir;
- }
- # We can now add the metadata for this dir ...
-
-# $result = $dbh->selectall_arrayref($query);
- if ($lstat)
- {
- # contains something
- $reftree->[1]->{$jobid}=$lstat;
- }
- else
- {
- # We have a very special case here...
- # lstat is not defined.
- # it means the directory is there because a file has been
- # backuped. so the dir has no entry in File table.
- # That's a rare case, so we can afford to determine it's
- # visibility with a query
- my $select_path=$record->[0];
- $select_path=$dbh->quote($select_path); # gotta be careful
- my $query = "
-SELECT File.JobId
-FROM File, Path
-WHERE File.PathId = Path.PathId
-AND Path.Path = $select_path
-";
- print $query,"\n" if $debug;
- my $result2 = $dbh->selectall_arrayref($query);
- foreach my $record (@{$result2})
- {
- my $jobid=$record->[0];
- $reftree->[1]->{$jobid}=1;
- }
- }
-
- }
- $query = "
-DROP TABLE T1;
-";
- print $query,"\n" if $debug;
- $dbh->do($query);
- $query = "
-DROP TABLE T2;
-";
- print $query,"\n" if $debug;
- $dbh->do($query);
-
-
- list_visible($self->{dirtree}->{$client});
- $widget->destroy();
-
-# print Data::Dumper::Dumper($self->{dirtree});
-}
-
-# Recursive function to calculate the visibility of each directory in the cache
-# tree Working with references to save time and memory
-# For each directory, we want to propagate it's visible jobids onto it's
-# parents directory.
-# A tree is visible if
-# - it's been in a backup pointed by the CurrentJobIds
-# - one of it's subdirs is in a backup pointed by the CurrentJobIds
-# In the second case, the directory is visible but has no metadata.
-# We symbolize this with lstat = 1 for this jobid in the cache.
-
-# Input : reference directory
-# Output : visibility of this dir. Has to know visibility of all subdirs
-# to know it's visibility, hence the recursing.
-sub list_visible
-{
- my ($refdir)=@_;
-
- my %visibility;
- # Get the subdirs array references list
- my @list_ref_subdirs;
- while( my (undef,$ref_subdir) = each (%{$refdir->[0]}))
- {
- push @list_ref_subdirs,($ref_subdir);
- }
-
- # Now lets recurse over these subdirs and retrieve the reference of a hash
- # containing the jobs where they are visible
- foreach my $ref_subdir (@list_ref_subdirs)
- {
- my $ref_list_jobs = list_visible($ref_subdir);
- foreach my $jobid (keys %$ref_list_jobs)
- {
- $visibility{$jobid}=1;
- }
- }
-
- # Ok. Now, we've got the list of those jobs. We are going to update our
- # hash (element 1 of the dir array) containing our jobs Do NOT overwrite
- # the lstat for the known jobids. Put 1 in the new elements... But first,
- # let's store the current jobids
- my @known_jobids;
- foreach my $jobid (keys %{$refdir->[1]})
- {
- push @known_jobids,($jobid);
- }
-
- # Add the new jobs
- foreach my $jobid (keys %visibility)
- {
- next if ($refdir->[1]->{$jobid});
- $refdir->[1]->{$jobid} = 1;
- }
- # Add the known_jobids to %visibility
- foreach my $jobid (@known_jobids)
- {
- $visibility{$jobid}=1;
- }
- return \%visibility;
-}
-
-# Returns the list of media required for a list of jobids.
-# Input : dbh, jobid1, jobid2...
-# Output : reference to array of (joibd, inchanger)
-sub get_required_media_from_jobid
-{
- my ($dbh, @jobids)=@_;
- my $inclause = join(',',@jobids);
- my $query = "
-SELECT DISTINCT JobMedia.MediaId, Media.InChanger
-FROM JobMedia, Media
-WHERE JobMedia.MediaId=Media.MediaId
-AND JobId In ($inclause)
-ORDER BY MediaId";
- my $result = $dbh->selectall_arrayref($query);
- return $result;
-}
-
-# Returns the fileindex from dirname and jobid.
-# Input : dbh, dirname, jobid
-# Output : fileindex
-sub get_fileindex_from_dir_jobid
-{
- my ($dbh, $dirname, $jobid)=@_;
- my $query;
- $query = "SELECT File.FileIndex
- FROM File, Filename, Path
- WHERE File.FilenameId = Filename.FilenameId
- AND File.PathId = Path.PathId
- AND Filename.Name = ''
- AND Path.Path = '$dirname'
- AND File.JobId = '$jobid'
- ";
-
- print $query,"\n" if $debug;
- my $result = $dbh->selectall_arrayref($query);
- return $result->[0]->[0];
-}
-
-# Returns the fileindex from filename and jobid.
-# Input : dbh, filename, jobid
-# Output : fileindex
-sub get_fileindex_from_file_jobid
-{
- my ($dbh, $filename, $jobid)=@_;
-
- my @dirs = File::Spec->splitdir ($filename);
- $filename=pop(@dirs);
- my $dirname = File::Spec->catdir(@dirs) . '/';
-
-
- my $query;
- $query =
-"SELECT File.FileIndex
- FROM File, Filename, Path
- WHERE File.FilenameId = Filename.FilenameId
- AND File.PathId = Path.PathId
- AND Filename.Name = '$filename'
- AND Path.Path = '$dirname'
- AND File.JobId = '$jobid'";
-
- print $query,"\n" if $debug;
- my $result = $dbh->selectall_arrayref($query);
- return $result->[0]->[0];
-}