4 die "bresto is not enabled" if (not $bresto_enable);
8 Bweb - A Bacula web interface
9 Bacula® - The Network Backup Solution
11 Copyright (C) 2000-2006 Free Software Foundation Europe e.V.
13 The main author of Bweb is Eric Bollengier.
14 The main author of Bacula is Kern Sibbald, with contributions from
15 many others, a complete list can be found in the file AUTHORS.
17 This program is Free Software; you can redistribute it and/or
18 modify it under the terms of version two of the GNU General Public
19 License as published by the Free Software Foundation plus additions
20 that are listed in the file LICENSE.
22 This program is distributed in the hope that it will be useful, but
23 WITHOUT ANY WARRANTY; without even the implied warranty of
24 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
25 General Public License for more details.
27 You should have received a copy of the GNU General Public License
28 along with this program; if not, write to the Free Software
29 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
32 Bacula® is a registered trademark of John Walker.
33 The licensor of Bacula is the Free Software Foundation Europe
34 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zurich,
35 Switzerland, email:ftf@fsfeurope.org.
51 return $self->get_pathid('');
56 my ($self, $pathid) = @_;
57 $self->{cwdid} = $pathid;
65 FROM brestore_pathhierarchy
66 WHERE PathId IN ($self->{cwdid}) ";
68 my $all = $self->dbh_selectall_arrayref($query);
69 return unless ($all); # already at root
71 my $dir = join(',', map { $_->[0] } @$all);
80 return $self->get_path($self->{cwdid});
85 my ($self, $pathid) = @_;
86 $self->debug("Call with pathid = $pathid");
88 "SELECT Path FROM Path WHERE PathId IN (?)";
90 my $sth = $self->dbh_prepare($query);
91 $sth->execute($pathid);
92 my $result = $sth->fetchrow_arrayref();
99 my ($self, @jobids) = @_;
100 $self->{curjobids} = join(',', @jobids);
101 # $self->update_brestore_table(@jobids);
106 my ($self, $dir) = @_;
108 "SELECT PathId FROM Path WHERE Path = ?";
109 my $sth = $self->dbh_prepare($query);
111 my $result = $sth->fetchall_arrayref();
114 return join(',', map { $_->[0] } @$result);
119 my ($self, $offset, $limit) = @_;
120 $self->{limit} = $limit || 100;
121 $self->{offset} = $offset || 0;
128 $self->{dbh}->begin_work();
131 SELECT JobId from Job
132 WHERE JobId NOT IN (SELECT JobId FROM brestore_knownjobid) AND JobStatus IN ('T', 'f', 'A') ORDER BY JobId";
133 my $jobs = $self->dbh_selectall_arrayref($query);
135 $self->update_brestore_table(map { $_->[0] } @$jobs);
137 print STDERR "Cleaning path visibility\n";
139 my $nb = $self->dbh_do("
140 DELETE FROM brestore_pathvisibility
142 (SELECT 1 FROM Job WHERE JobId=brestore_pathvisibility.JobId)");
144 print STDERR "$nb rows affected\n";
145 print STDERR "Cleaning known jobid\n";
147 $nb = $self->dbh_do("
148 DELETE FROM brestore_knownjobid
150 (SELECT 1 FROM Job WHERE JobId=brestore_knownjobid.JobId)");
152 print STDERR "$nb rows affected\n";
154 $self->{dbh}->commit();
157 sub update_brestore_table
159 my ($self, @jobs) = @_;
161 $self->debug(\@jobs);
163 foreach my $job (sort {$a <=> $b} @jobs)
165 my $query = "SELECT 1 FROM brestore_knownjobid WHERE JobId = $job";
166 my $retour = $self->dbh_selectrow_arrayref($query);
167 next if ($retour and ($retour->[0] == 1)); # We have allready done this one ...
169 print STDERR "Inserting path records for JobId $job\n";
170 $query = "INSERT INTO brestore_pathvisibility (PathId, JobId)
171 (SELECT DISTINCT PathId, JobId FROM File WHERE JobId = $job)";
173 $self->dbh_do($query);
175 # Now we have to do the directory recursion stuff to determine missing visibility
176 # We try to avoid recursion, to be as fast as possible
177 # We also only work on not allready hierarchised directories...
179 print STDERR "Creating missing recursion paths for $job\n";
181 $query = "SELECT brestore_pathvisibility.PathId, Path FROM brestore_pathvisibility
182 JOIN Path ON( brestore_pathvisibility.PathId = Path.PathId)
183 LEFT JOIN brestore_pathhierarchy ON (brestore_pathvisibility.PathId = brestore_pathhierarchy.PathId)
184 WHERE brestore_pathvisibility.JobId = $job
185 AND brestore_pathhierarchy.PathId IS NULL
188 my $sth = $self->dbh_prepare($query);
190 my $pathid; my $path;
191 $sth->bind_columns(\$pathid,\$path);
195 $self->build_path_hierarchy($path,$pathid);
199 # Great. We have calculated all dependancies. We can use them to add the missing pathids ...
200 # This query gives all parent pathids for a given jobid that aren't stored.
201 # It has to be called until no record is updated ...
203 INSERT INTO brestore_pathvisibility (PathId, JobId) (
206 (SELECT DISTINCT h.PPathId AS PathId
207 FROM brestore_pathhierarchy AS h
208 JOIN brestore_pathvisibility AS p ON (h.PathId=p.PathId)
209 WHERE p.JobId=$job) AS a
212 FROM brestore_pathvisibility
213 WHERE JobId=$job) AS b
214 ON (a.PathId = b.PathId)
215 WHERE b.PathId IS NULL)";
218 while (($rows_affected = $self->dbh_do($query)) and ($rows_affected !~ /^0/))
220 print STDERR "Recursively adding $rows_affected records from $job\n";
223 $query = "INSERT INTO brestore_knownjobid (JobId) VALUES ($job)";
224 $self->dbh_do($query);
236 # Root Windows case :
237 if ($path =~ /^[a-z]+:\/$/i)
242 my @tmp = split('/',$path);
243 # We remove the last ...
245 my $tmp = join ('/',@tmp) . '/';
249 sub build_path_hierarchy
251 my ($self, $path,$pathid)=@_;
252 # Does the ppathid exist for this ? we use a memory cache...
253 # In order to avoid the full loop, we consider that if a dir is allready in the
254 # brestore_pathhierarchy table, then there is no need to calculate all the hierarchy
257 if (! $self->{cache_ppathid}->{$pathid})
259 my $query = "SELECT PPathId FROM brestore_pathhierarchy WHERE PathId = ?";
260 my $sth2 = $self->{dbh}->prepare_cached($query);
261 $sth2->execute($pathid);
262 # Do we have a result ?
263 if (my $refrow = $sth2->fetchrow_arrayref)
265 $self->{cache_ppathid}->{$pathid}=$refrow->[0];
267 # This dir was in the db ...
268 # It means we can leave, the tree has allready been built for
273 # We have to create the record ...
274 # What's the current p_path ?
275 my $ppath = parent_dir($path);
276 my $ppathid = $self->return_pathid_from_path($ppath);
277 $self->{cache_ppathid}->{$pathid}= $ppathid;
279 $query = "INSERT INTO brestore_pathhierarchy (pathid, ppathid) VALUES (?,?)";
280 $sth2 = $self->{dbh}->prepare_cached($query);
281 $sth2->execute($pathid,$ppathid);
287 # It's allready in the cache.
288 # We can leave, no time to waste here, all the parent dirs have allready
296 sub return_pathid_from_path
298 my ($self, $path) = @_;
299 my $query = "SELECT PathId FROM Path WHERE Path = ?";
301 #print STDERR $query,"\n" if $debug;
302 my $sth = $self->{dbh}->prepare_cached($query);
303 $sth->execute($path);
304 my $result =$sth->fetchrow_arrayref();
311 # A bit dirty : we insert into path, and we have to be sure
312 # we aren't deleted by a purge. We still need to insert into path to get
313 # the pathid, because of mysql
314 $query = "INSERT INTO Path (Path) VALUES (?)";
315 #print STDERR $query,"\n" if $debug;
316 $sth = $self->{dbh}->prepare_cached($query);
317 $sth->execute($path);
320 $query = "SELECT PathId FROM Path WHERE Path = ?";
321 #print STDERR $query,"\n" if $debug;
322 $sth = $self->{dbh}->prepare_cached($query);
323 $sth->execute($path);
324 $result = $sth->fetchrow_arrayref();
334 return undef unless ($self->{curjobids});
336 my $inclause = $self->{curjobids};
337 my $inlistpath = $self->{cwdid};
340 "SELECT File.FilenameId, listfiles.id, listfiles.Name, File.LStat, File.JobId
342 (SELECT Filename.Name, max(File.FileId) as id
344 WHERE File.FilenameId = Filename.FilenameId
345 AND Filename.Name != ''
346 AND File.PathId IN ($inlistpath)
347 AND File.JobId IN ($inclause)
348 GROUP BY Filename.Name
349 ORDER BY Filename.Name) AS listfiles,
351 WHERE File.FileId = listfiles.id";
353 $self->debug($query);
354 my $result = $self->dbh_selectall_arrayref($query);
355 $self->debug($result);
361 # return ($dirid,$dir_basename,$lstat,$jobid)
366 return undef unless ($self->{curjobids});
368 my $pathid = $self->{cwdid};
369 my $jobclause = $self->{curjobids};
371 # Let's retrieve the list of the visible dirs in this dir ...
372 # First, I need the empty filenameid to locate efficiently the dirs in the file table
373 my $query = "SELECT FilenameId FROM Filename WHERE Name = ''";
374 my $sth = $self->dbh_prepare($query);
376 my $result = $sth->fetchrow_arrayref();
378 my $dir_filenameid = $result->[0];
380 # Then we get all the dir entries from File ...
382 SELECT PathId, Path, JobId, Lstat FROM (
384 SELECT Path1.PathId, Path1.Path, lower(Path1.Path),
385 listfile1.JobId, listfile1.Lstat
387 SELECT DISTINCT brestore_pathhierarchy1.PathId
388 FROM brestore_pathhierarchy AS brestore_pathhierarchy1
390 ON (brestore_pathhierarchy1.PathId = Path2.PathId)
391 JOIN brestore_pathvisibility AS brestore_pathvisibility1
392 ON (brestore_pathhierarchy1.PathId = brestore_pathvisibility1.PathId)
393 WHERE brestore_pathhierarchy1.PPathId = $pathid
394 AND brestore_pathvisibility1.jobid IN ($jobclause)) AS listpath1
395 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId)
397 SELECT File1.PathId, File1.JobId, File1.Lstat FROM File AS File1
398 WHERE File1.FilenameId = $dir_filenameid
399 AND File1.JobId IN ($jobclause)) AS listfile1
400 ON (listpath1.PathId = listfile1.PathId)
401 ) AS A ORDER BY 2,3 DESC
403 $self->debug($query);
404 $sth=$self->dbh_prepare($query);
406 $result = $sth->fetchall_arrayref();
409 foreach my $refrow (@{$result})
411 my $dirid = $refrow->[0];
412 my $dir = $refrow->[1];
413 my $lstat = $refrow->[3];
414 my $jobid = $refrow->[2] || 0;
415 next if ($dirid eq $prev_dir);
416 # We have to clean up this dirname ... we only want it's 'basename'
420 my @temp = split ('/',$dir);
421 $return_value = pop @temp;
427 my @return_array = ($dirid,$return_value,$lstat,$jobid);
428 push @return_list,(\@return_array);
431 $self->debug(\@return_list);
432 return \@return_list;
435 # TODO : we want be able to restore files from a bad ended backup
436 # we have JobStatus IN ('T', 'A', 'E') and we must
438 # Data acces subs from here. Interaction with SGBD and caching
440 # This sub retrieves the list of jobs corresponding to the jobs selected in the
441 # GUI and stores them in @CurrentJobIds.
442 # date must be quoted
443 sub set_job_ids_for_date
445 my ($self, $client, $date)=@_;
447 if (!$client or !$date) {
450 my $filter = $self->get_client_filter();
451 # The algorithm : for a client, we get all the backups for each
452 # fileset, in reverse order Then, for each fileset, we store the 'good'
453 # incrementals and differentials until we have found a full so it goes
454 # like this : store all incrementals until we have found a differential
455 # or a full, then find the full
456 my $query = "SELECT JobId, FileSet, Level, JobStatus
457 FROM Job JOIN FileSet USING (FileSetId)
458 JOIN Client USING (ClientId) $filter
459 WHERE EndTime <= $date
460 AND Client.Name = '$client'
462 AND JobStatus IN ('T')
463 ORDER BY FileSet, JobTDate DESC";
466 my $result = $self->dbh_selectall_arrayref($query);
468 foreach my $refrow (@$result)
470 my $jobid = $refrow->[0];
471 my $fileset = $refrow->[1];
472 my $level = $refrow->[2];
474 defined $progress{$fileset} or $progress{$fileset}='U'; # U for unknown
476 next if $progress{$fileset} eq 'F'; # It's over for this fileset...
480 next unless ($progress{$fileset} eq 'U' or $progress{$fileset} eq 'I');
481 push @CurrentJobIds,($jobid);
483 elsif ($level eq 'D')
485 next if $progress{$fileset} eq 'D'; # We allready have a differential
486 push @CurrentJobIds,($jobid);
488 elsif ($level eq 'F')
490 push @CurrentJobIds,($jobid);
493 my $status = $refrow->[3] ;
494 if ($status eq 'T') { # good end of job
495 $progress{$fileset} = $level;
499 return @CurrentJobIds;
502 sub dbh_selectrow_arrayref
504 my ($self, $query) = @_;
505 $self->debug($query, up => 1);
506 return $self->{dbh}->selectrow_arrayref($query);
509 # Returns list of versions of a file that could be restored
510 # returns an array of
511 # (jobid,fileindex,mtime,size,inchanger,md5,volname,fileid)
512 # there will be only one jobid in the array of jobids...
513 sub get_all_file_versions
515 my ($self,$pathid,$fileid,$client,$see_all)=@_;
517 defined $see_all or $see_all=0;
522 "SELECT File.JobId, File.FileId, File.Lstat,
523 File.Md5, Media.VolumeName, Media.InChanger
524 FROM File, Job, Client, JobMedia, Media
525 WHERE File.FilenameId = $fileid
526 AND File.PathId=$pathid
527 AND File.JobId = Job.JobId
528 AND Job.ClientId = Client.ClientId
529 AND Job.JobId = JobMedia.JobId
530 AND File.FileIndex >= JobMedia.FirstIndex
531 AND File.FileIndex <= JobMedia.LastIndex
532 AND JobMedia.MediaId = Media.MediaId
533 AND Client.Name = '$client'";
535 $self->debug($query);
536 my $result = $self->dbh_selectall_arrayref($query);
538 foreach my $refrow (@$result)
540 my ($jobid, $fid, $lstat, $md5, $volname, $inchanger) = @$refrow;
541 my @attribs = parse_lstat($lstat);
542 my $mtime = array_attrib('st_mtime',\@attribs);
543 my $size = array_attrib('st_size',\@attribs);
545 my @list = ($pathid,$fileid,$jobid,
546 $fid, $mtime, $size, $inchanger,
548 push @versions, (\@list);
551 # We have the list of all versions of this file.
552 # We'll sort it by mtime desc, size, md5, inchanger desc
553 # the rest of the algorithm will be simpler
554 # ('FILE:',filename,jobid,fileindex,mtime,size,inchanger,md5,volname)
555 @versions = sort { $b->[4] <=> $a->[4]
556 || $a->[5] <=> $b->[5]
557 || $a->[7] cmp $a->[7]
558 || $b->[6] <=> $a->[6]} @versions;
561 my %allready_seen_by_mtime;
562 my %allready_seen_by_md5;
563 # Now we should create a new array with only the interesting records
564 foreach my $ref (@versions)
568 # The file has a md5. We compare his md5 to other known md5...
569 # We take size into account. It may happen that 2 files
570 # have the same md5sum and are different. size is a supplementary
573 # If we allready have a (better) version
574 next if ( (not $see_all)
575 and $allready_seen_by_md5{$ref->[7] .'-'. $ref->[5]});
577 # we never met this one before...
578 $allready_seen_by_md5{$ref->[7] .'-'. $ref->[5]}=1;
580 # Even if it has a md5, we should also work with mtimes
581 # We allready have a (better) version
582 next if ( (not $see_all)
583 and $allready_seen_by_mtime{$ref->[4] .'-'. $ref->[5]});
584 $allready_seen_by_mtime{$ref->[4] .'-'. $ref->[5] . '-' . $ref->[7]}=1;
586 # We reached there. The file hasn't been seen.
587 push @good_versions,($ref);
590 # To be nice with the user, we re-sort good_versions by
591 # inchanger desc, mtime desc
592 @good_versions = sort { $b->[4] <=> $a->[4]
593 || $b->[2] <=> $a->[2]} @good_versions;
595 return \@good_versions;
598 my %attrib_name_id = ( 'st_dev' => 0,'st_ino' => 1,'st_mode' => 2,
599 'st_nlink' => 3,'st_uid' => 4,'st_gid' => 5,
600 'st_rdev' => 6,'st_size' => 7,'st_blksize' => 8,
601 'st_blocks' => 9,'st_atime' => 10,'st_mtime' => 11,
602 'st_ctime' => 12,'LinkFI' => 13,'st_flags' => 14,
603 'data_stream' => 15);;
606 my ($attrib,$ref_attrib)=@_;
607 return $ref_attrib->[$attrib_name_id{$attrib}];
611 { # $file = [filenameid,listfiles.id,listfiles.Name, File.LStat, File.JobId]
613 my ($file, $attrib)=@_;
615 if (defined $attrib_name_id{$attrib}) {
617 my @d = split(' ', $file->[3]) ; # TODO : cache this
619 return from_base64($d[$attrib_name_id{$attrib}]);
621 } elsif ($attrib eq 'jobid') {
625 } elsif ($attrib eq 'name') {
630 die "Attribute not known : $attrib.\n";
636 my ($lstat,$attrib)=@_;
637 if ($lstat and defined $attrib_name_id{$attrib})
639 my @d = split(' ', $lstat) ; # TODO : cache this
640 return from_base64($d[$attrib_name_id{$attrib}]);
647 # Base 64 functions, directly from recover.pl.
649 # Karl Hakimian <hakimian@aha.com>
650 # This section is also under GPL v2 or later.
657 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
658 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
659 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',
660 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
661 '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '+', '/'
663 @base64_map = (0) x 128;
665 for (my $i=0; $i<64; $i++) {
666 $base64_map[ord($base64_digits[$i])] = $i;
681 if (substr($where, 0, 1) eq '-') {
683 $where = substr($where, 1);
686 while ($where ne '') {
688 my $d = substr($where, 0, 1);
689 $val += $base64_map[ord(substr($where, 0, 1))];
690 $where = substr($where, 1);
698 my @attribs = split(' ',$lstat);
699 foreach my $element (@attribs)
701 $element = from_base64($element);
709 my ($self, @jobid) = @_;
710 my $filter = $self->get_client_filter();
712 my $jobids = $self->dbh_join(@jobid);
715 FROM Job JOIN Client USING (ClientId) $filter
716 WHERE Jobid IN ($jobids)";
717 my $res = $self->dbh_selectall_arrayref($q);
718 @jobid = map { $_->[0] } @$res;
723 ################################################################
730 my $conf = new Bweb::Config(config_file => $Bweb::config_file);
733 my $bvfs = new Bvfs(info => $conf);
736 my $action = CGI::param('action') || '';
738 my $args = $bvfs->get_form('pathid', 'filenameid', 'fileid', 'qdate',
739 'limit', 'offset', 'client');
741 print CGI::header('application/x-javascript');
743 if ($action eq 'list_client') {
745 my $filter = $bvfs->get_client_filter();
746 my $q = "SELECT Name FROM Client $filter";
747 my $ret = $bvfs->dbh_selectall_arrayref($q);
750 print join(',', map { "['$_->[0]']" } @$ret);
754 } elsif ($action eq 'list_job') {
756 my $filter = $bvfs->get_client_filter();
758 SELECT Job.EndTime, FileSet.FileSet, Job.Level, Job.JobStatus, Job.JobId
759 FROM Job JOIN FileSet USING (FileSetId) JOIN Client USING (ClientId) $filter
760 WHERE Client.Name = '$args->{client}'
762 AND JobStatus IN ('f', 'T')
763 ORDER BY EndTime desc";
764 my $result = $bvfs->dbh_selectall_arrayref($query);
768 print join(',', map {
769 "[$_->[4], '$_->[0]', '$_->[0] $_->[1] $_->[2] ($_->[3])']"
774 } elsif ($action eq 'list_storage') { # TODO: use .storage hier
776 my $q="SELECT Name FROM Storage";
777 my $lst = $bvfs->dbh_selectall_arrayref($q);
779 print join(',', map { "[ '$_->[0]' ]" } @$lst);
784 my @jobid = $bvfs->get_jobids(grep { /^\d+$/ } CGI::param('jobid'));
785 if (!scalar(@jobid) and $args->{qdate} and $args->{client}) {
786 @jobid = $bvfs->set_job_ids_for_date($args->{client}, $args->{qdate});
788 $bvfs->set_curjobids(@jobid);
789 $bvfs->set_limits($args->{limit}, $args->{offset});
791 if (!scalar(@jobid)) {
795 if (CGI::param('init')) {
796 $bvfs->update_brestore_table(@jobid);
799 my $pathid = CGI::param('node') || '';
800 my $path = CGI::param('path');
802 if ($pathid =~ /^(\d+)$/) {
805 $pathid = $bvfs->get_pathid($path);
807 $pathid = $bvfs->get_root();
810 $bvfs->ch_dir($pathid);
812 if ($action eq 'list_files') {
814 my $files = $bvfs->ls_files();
815 # [ 1, 2, 3, "Bill", 10, '2007-01-01 00:00:00'],
816 # File.FilenameId, listfiles.id, listfiles.Name, File.LStat, File.JobId
819 map { "[$_->[1], $_->[0], $pathid, \"$_->[2]\", 10, \"2007-01-01 00:00:00\"]" }
823 } elsif ($action eq 'list_dirs') {
826 my $dirs = $bvfs->ls_dirs();
827 # return ($dirid,$dir_basename,$lstat,$jobid)
830 map { "{ 'id': '$_->[0]', 'text': '$_->[1]', 'cls':'folder'}" }
835 } elsif ($action eq 'list_versions') {
837 my $vafv = CGI::param('vafv') || 'false'; # view all file versions
838 $vafv = ($vafv eq 'false')?0:1;
842 #($pathid,$fileid,$jobid, $fid, $mtime, $size, $inchanger, $md5, $volname);
843 my $files = $bvfs->get_all_file_versions($args->{pathid}, $args->{filenameid}, $args->{client}, $vafv);
845 map { "[ $_->[3], $_->[1], $_->[0], $_->[2], '$_->[8]', $_->[6], '$_->[7]', $_->[5], $_->[4] ]" }
849 } elsif ($action eq 'get_media') {
851 my $jobid = join(',', @jobid);
852 my $fileid = join(',', grep { /^\d+$/ } CGI::param('fileid'));
854 # TODO: use client filter
856 SELECT DISTINCT VolumeName, InChanger
858 ( -- Get all media from this job
859 SELECT MAX(FirstIndex) AS FirstIndex, MIN(LastIndex) AS LastIndex,
860 VolumeName, Inchanger
861 FROM JobMedia JOIN Media USING (MediaId)
862 WHERE JobId IN ($jobid)
863 GROUP BY VolumeName, InChanger
865 WHERE File.FileId IN ($fileid)
866 AND File.FileIndex >= allmedia.FirstIndex
867 AND File.FileIndex <= allmedia.LastIndex;
869 my $lst = $bvfs->dbh_selectall_arrayref($q);
871 print join(',', map { "[ '$_->[0]', $_->[1]]" } @$lst);
874 } elsif ($action eq 'restore') {
875 my $fileid = join(',', grep { /^\d+$/ } CGI::param('fileid'));
876 my @dirid = grep { /^\d+$/ } CGI::param('dirid');
877 my $inclause = join(',', @jobid);
882 push @union, "(SELECT JobId, FileIndex FROM File WHERE FileId IN ($fileid))";
885 # using this is not good because the sql engine doesn't know
886 # what LIKE will use. It will be better to get Path% in perl
887 # but it doesn't work with accents... :(
888 foreach my $dirid (@dirid) {
890 (SELECT File.JobId, File.FileIndex, File.FilenameId, File.PathId
891 FROM Path JOIN File USING (PathId)
893 (SELECT ". $bvfs->dbh_strcat('Path',"'\%'") ." FROM Path
894 WHERE PathId = $dirid
896 AND File.JobId IN ($inclause))";
899 return unless scalar(@union);
901 my $u = join(" UNION ", @union);
903 $bvfs->dbh_do("CREATE TEMP TABLE btemp AS ($u)");
904 # TODO: remove FilenameId et PathId
905 $bvfs->dbh_do("CREATE TABLE b2$$ AS (
906 SELECT btemp.JobId, btemp.FileIndex, btemp.FilenameId, btemp.PathId
908 (SELECT max(JobId) as JobId, PathId, FilenameId
910 GROUP BY PathId, FilenameId
911 ORDER BY JobId DESC) AS a
912 WHERE a.JobId = btemp.JobId
913 AND a.PathId= btemp.PathId
914 AND a.FilenameId = btemp.FilenameId
917 print "restore file=?b2$$ done\n";
923 SELECT path || name AS name,pathid,filenameid,fileid,jobid
924 FROM File JOIN FileName USING (FilenameId) JOIN Path USING (PathId);
926 SELECT 'drop table ' || tablename || ';'
927 FROM pg_tables WHERE tablename ~ '^b[0-9]';