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('');
54 # change the current directory
57 my ($self, $pathid) = @_;
58 $self->{cwdid} = $pathid;
67 FROM brestore_pathhierarchy
68 WHERE PathId IN ($self->{cwdid}) ";
70 my $all = $self->dbh_selectall_arrayref($query);
71 return unless ($all); # already at root
73 my $dir = join(',', map { $_->[0] } @$all);
79 # return the current PWD
83 return $self->get_path($self->{cwdid});
86 # get the Path from a PathId
89 my ($self, $pathid) = @_;
90 $self->debug("Call with pathid = $pathid");
91 my $query = "SELECT Path FROM Path WHERE PathId = ?";
92 my $sth = $self->dbh_prepare($query);
93 $sth->execute($pathid);
94 my $result = $sth->fetchrow_arrayref();
99 # we are working with these jobids
102 my ($self, @jobids) = @_;
103 $self->{curjobids} = join(',', @jobids);
104 # $self->update_brestore_table(@jobids);
107 # get the PathId from a Path
110 my ($self, $dir) = @_;
112 "SELECT PathId FROM Path WHERE Path = ?";
113 my $sth = $self->dbh_prepare($query);
115 my $result = $sth->fetchrow_arrayref();
123 my ($self, $offset, $limit) = @_;
124 $self->{limit} = $limit || 100;
125 $self->{offset} = $offset || 0;
128 # fill brestore_xxx tables for speedup
133 $self->{dbh}->begin_work();
135 # getting all Jobs to "cache"
137 SELECT JobId from Job
138 WHERE JobId NOT IN (SELECT JobId FROM brestore_knownjobid)
139 AND Type IN ('B') AND JobStatus IN ('T', 'f', 'A')
141 my $jobs = $self->dbh_selectall_arrayref($query);
143 $self->update_brestore_table(map { $_->[0] } @$jobs);
145 $self->{dbh}->commit();
146 $self->{dbh}->begin_work(); # we can break here
148 print STDERR "Cleaning path visibility\n";
150 my $nb = $self->dbh_do("
151 DELETE FROM brestore_pathvisibility
153 (SELECT 1 FROM Job WHERE JobId=brestore_pathvisibility.JobId)");
155 print STDERR "$nb rows affected\n";
156 print STDERR "Cleaning known jobid\n";
158 $nb = $self->dbh_do("
159 DELETE FROM brestore_knownjobid
161 (SELECT 1 FROM Job WHERE JobId=brestore_knownjobid.JobId)");
163 print STDERR "$nb rows affected\n";
165 $self->{dbh}->commit();
168 sub update_brestore_table
170 my ($self, @jobs) = @_;
172 $self->debug(\@jobs);
174 foreach my $job (sort {$a <=> $b} @jobs)
176 my $query = "SELECT 1 FROM brestore_knownjobid WHERE JobId = $job";
177 my $retour = $self->dbh_selectrow_arrayref($query);
178 next if ($retour and ($retour->[0] == 1)); # We have allready done this one ...
180 print STDERR "Inserting path records for JobId $job\n";
181 $query = "INSERT INTO brestore_pathvisibility (PathId, JobId)
182 (SELECT DISTINCT PathId, JobId FROM File WHERE JobId = $job)";
184 $self->dbh_do($query);
186 # Now we have to do the directory recursion stuff to determine missing visibility
187 # We try to avoid recursion, to be as fast as possible
188 # We also only work on not allready hierarchised directories...
190 print STDERR "Creating missing recursion paths for $job\n";
193 SELECT brestore_pathvisibility.PathId, Path FROM brestore_pathvisibility
194 JOIN Path ON( brestore_pathvisibility.PathId = Path.PathId)
195 LEFT JOIN brestore_pathhierarchy ON (brestore_pathvisibility.PathId = brestore_pathhierarchy.PathId)
196 WHERE brestore_pathvisibility.JobId = $job
197 AND brestore_pathhierarchy.PathId IS NULL
200 my $sth = $self->dbh_prepare($query);
202 my $pathid; my $path;
203 $sth->bind_columns(\$pathid,\$path);
207 $self->build_path_hierarchy($path,$pathid);
211 # Great. We have calculated all dependancies. We can use them to add the missing pathids ...
212 # This query gives all parent pathids for a given jobid that aren't stored.
213 # It has to be called until no record is updated ...
215 INSERT INTO brestore_pathvisibility (PathId, JobId) (
218 SELECT DISTINCT h.PPathId AS PathId
219 FROM brestore_pathhierarchy AS h
220 JOIN brestore_pathvisibility AS p ON (h.PathId=p.PathId)
221 WHERE p.JobId=$job) AS a LEFT JOIN
223 FROM brestore_pathvisibility
224 WHERE JobId=$job) AS b ON (a.PathId = b.PathId)
225 WHERE b.PathId IS NULL)";
228 while (($rows_affected = $self->dbh_do($query)) and ($rows_affected !~ /^0/))
230 print STDERR "Recursively adding $rows_affected records from $job\n";
233 $query = "INSERT INTO brestore_knownjobid (JobId) VALUES ($job)";
234 $self->dbh_do($query);
238 # compute the parent directorie
247 # Root Windows case :
248 if ($path =~ /^[a-z]+:\/$/i)
253 my @tmp = split('/',$path);
254 # We remove the last ...
256 my $tmp = join ('/',@tmp) . '/';
260 sub build_path_hierarchy
262 my ($self, $path,$pathid)=@_;
263 # Does the ppathid exist for this ? we use a memory cache...
264 # In order to avoid the full loop, we consider that if a dir is allready in the
265 # brestore_pathhierarchy table, then there is no need to calculate all the hierarchy
268 if (! $self->{cache_ppathid}->{$pathid})
270 my $query = "SELECT PPathId FROM brestore_pathhierarchy WHERE PathId = ?";
271 my $sth2 = $self->{dbh}->prepare_cached($query);
272 $sth2->execute($pathid);
273 # Do we have a result ?
274 if (my $refrow = $sth2->fetchrow_arrayref)
276 $self->{cache_ppathid}->{$pathid}=$refrow->[0];
278 # This dir was in the db ...
279 # It means we can leave, the tree has allready been built for
284 # We have to create the record ...
285 # What's the current p_path ?
286 my $ppath = parent_dir($path);
287 my $ppathid = $self->return_pathid_from_path($ppath);
288 $self->{cache_ppathid}->{$pathid}= $ppathid;
290 $query = "INSERT INTO brestore_pathhierarchy (pathid, ppathid) VALUES (?,?)";
291 $sth2 = $self->{dbh}->prepare_cached($query);
292 $sth2->execute($pathid,$ppathid);
298 # It's allready in the cache.
299 # We can leave, no time to waste here, all the parent dirs have allready
307 sub return_pathid_from_path
309 my ($self, $path) = @_;
310 my $query = "SELECT PathId FROM Path WHERE Path = ?";
312 #print STDERR $query,"\n" if $debug;
313 my $sth = $self->{dbh}->prepare_cached($query);
314 $sth->execute($path);
315 my $result =$sth->fetchrow_arrayref();
322 # A bit dirty : we insert into path, and we have to be sure
323 # we aren't deleted by a purge. We still need to insert into path to get
324 # the pathid, because of mysql
325 $query = "INSERT INTO Path (Path) VALUES (?)";
326 #print STDERR $query,"\n" if $debug;
327 $sth = $self->{dbh}->prepare_cached($query);
328 $sth->execute($path);
331 $query = "SELECT PathId FROM Path WHERE Path = ?";
332 #print STDERR $query,"\n" if $debug;
333 $sth = $self->{dbh}->prepare_cached($query);
334 $sth->execute($path);
335 $result = $sth->fetchrow_arrayref();
341 # list all files in a directory, accross curjobids
346 return undef unless ($self->{curjobids});
348 my $inclause = $self->{curjobids};
349 my $inpath = $self->{cwdid};
352 "SELECT File.FilenameId, listfiles.id, listfiles.Name, File.LStat, File.JobId
354 SELECT Filename.Name, max(File.FileId) as id
356 WHERE File.FilenameId = Filename.FilenameId
357 AND Filename.Name != ''
358 AND File.PathId = $inpath
359 AND File.JobId IN ($inclause)
360 GROUP BY Filename.Name
361 ORDER BY Filename.Name) AS listfiles
362 WHERE File.FileId = listfiles.id";
364 $self->debug($query);
365 my $result = $self->dbh_selectall_arrayref($query);
366 $self->debug($result);
371 # list all directories in a directory, accross curjobids
372 # return ($dirid,$dir_basename,$lstat,$jobid)
377 return undef unless ($self->{curjobids});
379 my $pathid = $self->{cwdid};
380 my $jobclause = $self->{curjobids};
382 # Let's retrieve the list of the visible dirs in this dir ...
383 # First, I need the empty filenameid to locate efficiently the dirs in the file table
384 my $query = "SELECT FilenameId FROM Filename WHERE Name = ''";
385 my $sth = $self->dbh_prepare($query);
387 my $result = $sth->fetchrow_arrayref();
389 my $dir_filenameid = $result->[0];
391 # Then we get all the dir entries from File ...
393 SELECT PathId, Path, JobId, Lstat FROM (
395 SELECT Path1.PathId, Path1.Path, lower(Path1.Path),
396 listfile1.JobId, listfile1.Lstat
398 SELECT DISTINCT brestore_pathhierarchy1.PathId
399 FROM brestore_pathhierarchy AS brestore_pathhierarchy1
401 ON (brestore_pathhierarchy1.PathId = Path2.PathId)
402 JOIN brestore_pathvisibility AS brestore_pathvisibility1
403 ON (brestore_pathhierarchy1.PathId = brestore_pathvisibility1.PathId)
404 WHERE brestore_pathhierarchy1.PPathId = $pathid
405 AND brestore_pathvisibility1.jobid IN ($jobclause)) AS listpath1
406 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId)
408 SELECT File1.PathId, File1.JobId, File1.Lstat FROM File AS File1
409 WHERE File1.FilenameId = $dir_filenameid
410 AND File1.JobId IN ($jobclause)) AS listfile1
411 ON (listpath1.PathId = listfile1.PathId)
412 ) AS A ORDER BY 2,3 DESC
414 $self->debug($query);
415 $sth=$self->dbh_prepare($query);
417 $result = $sth->fetchall_arrayref();
420 foreach my $refrow (@{$result})
422 my $dirid = $refrow->[0];
423 my $dir = $refrow->[1];
424 my $lstat = $refrow->[3];
425 my $jobid = $refrow->[2] || 0;
426 next if ($dirid eq $prev_dir);
427 # We have to clean up this dirname ... we only want it's 'basename'
431 my @temp = split ('/',$dir);
432 $return_value = pop @temp;
438 my @return_array = ($dirid,$return_value,$lstat,$jobid);
439 push @return_list,(\@return_array);
442 $self->debug(\@return_list);
443 return \@return_list;
446 # TODO : we want be able to restore files from a bad ended backup
447 # we have JobStatus IN ('T', 'A', 'E') and we must
449 # Data acces subs from here. Interaction with SGBD and caching
451 # This sub retrieves the list of jobs corresponding to the jobs selected in the
452 # GUI and stores them in @CurrentJobIds.
453 # date must be quoted
454 sub set_job_ids_for_date
456 my ($self, $client, $date)=@_;
458 if (!$client or !$date) {
461 my $filter = $self->get_client_filter();
462 # The algorithm : for a client, we get all the backups for each
463 # fileset, in reverse order Then, for each fileset, we store the 'good'
464 # incrementals and differentials until we have found a full so it goes
465 # like this : store all incrementals until we have found a differential
466 # or a full, then find the full
468 SELECT JobId, FileSet, Level, JobStatus
470 JOIN FileSet USING (FileSetId)
471 JOIN Client USING (ClientId) $filter
472 WHERE EndTime <= $date
473 AND Client.Name = '$client'
475 AND JobStatus IN ('T')
476 ORDER BY FileSet, JobTDate DESC";
479 my $result = $self->dbh_selectall_arrayref($query);
481 foreach my $refrow (@$result)
483 my $jobid = $refrow->[0];
484 my $fileset = $refrow->[1];
485 my $level = $refrow->[2];
487 defined $progress{$fileset} or $progress{$fileset}='U'; # U for unknown
489 next if $progress{$fileset} eq 'F'; # It's over for this fileset...
493 next unless ($progress{$fileset} eq 'U' or $progress{$fileset} eq 'I');
494 push @CurrentJobIds,($jobid);
496 elsif ($level eq 'D')
498 next if $progress{$fileset} eq 'D'; # We allready have a differential
499 push @CurrentJobIds,($jobid);
501 elsif ($level eq 'F')
503 push @CurrentJobIds,($jobid);
506 my $status = $refrow->[3] ;
507 if ($status eq 'T') { # good end of job
508 $progress{$fileset} = $level;
512 return @CurrentJobIds;
515 sub dbh_selectrow_arrayref
517 my ($self, $query) = @_;
518 $self->debug($query, up => 1);
519 return $self->{dbh}->selectrow_arrayref($query);
522 # Returns list of versions of a file that could be restored
523 # returns an array of
524 # (jobid,fileindex,mtime,size,inchanger,md5,volname,fileid)
525 # there will be only one jobid in the array of jobids...
526 sub get_all_file_versions
528 my ($self,$pathid,$fileid,$client,$see_all)=@_;
530 defined $see_all or $see_all=0;
535 "SELECT File.JobId, File.FileId, File.Lstat,
536 File.Md5, Media.VolumeName, Media.InChanger
537 FROM File, Job, Client, JobMedia, Media
538 WHERE File.FilenameId = $fileid
539 AND File.PathId=$pathid
540 AND File.JobId = Job.JobId
541 AND Job.ClientId = Client.ClientId
542 AND Job.JobId = JobMedia.JobId
543 AND File.FileIndex >= JobMedia.FirstIndex
544 AND File.FileIndex <= JobMedia.LastIndex
545 AND JobMedia.MediaId = Media.MediaId
546 AND Client.Name = '$client'";
548 $self->debug($query);
549 my $result = $self->dbh_selectall_arrayref($query);
551 foreach my $refrow (@$result)
553 my ($jobid, $fid, $lstat, $md5, $volname, $inchanger) = @$refrow;
554 my @attribs = parse_lstat($lstat);
555 my $mtime = array_attrib('st_mtime',\@attribs);
556 my $size = array_attrib('st_size',\@attribs);
558 my @list = ($pathid,$fileid,$jobid,
559 $fid, $mtime, $size, $inchanger,
561 push @versions, (\@list);
564 # We have the list of all versions of this file.
565 # We'll sort it by mtime desc, size, md5, inchanger desc
566 # the rest of the algorithm will be simpler
567 # ('FILE:',filename,jobid,fileindex,mtime,size,inchanger,md5,volname)
568 @versions = sort { $b->[4] <=> $a->[4]
569 || $a->[5] <=> $b->[5]
570 || $a->[7] cmp $a->[7]
571 || $b->[6] <=> $a->[6]} @versions;
574 my %allready_seen_by_mtime;
575 my %allready_seen_by_md5;
576 # Now we should create a new array with only the interesting records
577 foreach my $ref (@versions)
581 # The file has a md5. We compare his md5 to other known md5...
582 # We take size into account. It may happen that 2 files
583 # have the same md5sum and are different. size is a supplementary
586 # If we allready have a (better) version
587 next if ( (not $see_all)
588 and $allready_seen_by_md5{$ref->[7] .'-'. $ref->[5]});
590 # we never met this one before...
591 $allready_seen_by_md5{$ref->[7] .'-'. $ref->[5]}=1;
593 # Even if it has a md5, we should also work with mtimes
594 # We allready have a (better) version
595 next if ( (not $see_all)
596 and $allready_seen_by_mtime{$ref->[4] .'-'. $ref->[5]});
597 $allready_seen_by_mtime{$ref->[4] .'-'. $ref->[5] . '-' . $ref->[7]}=1;
599 # We reached there. The file hasn't been seen.
600 push @good_versions,($ref);
603 # To be nice with the user, we re-sort good_versions by
604 # inchanger desc, mtime desc
605 @good_versions = sort { $b->[4] <=> $a->[4]
606 || $b->[2] <=> $a->[2]} @good_versions;
608 return \@good_versions;
611 my %attrib_name_id = ( 'st_dev' => 0,'st_ino' => 1,'st_mode' => 2,
612 'st_nlink' => 3,'st_uid' => 4,'st_gid' => 5,
613 'st_rdev' => 6,'st_size' => 7,'st_blksize' => 8,
614 'st_blocks' => 9,'st_atime' => 10,'st_mtime' => 11,
615 'st_ctime' => 12,'LinkFI' => 13,'st_flags' => 14,
616 'data_stream' => 15);;
619 my ($attrib,$ref_attrib)=@_;
620 return $ref_attrib->[$attrib_name_id{$attrib}];
624 { # $file = [filenameid,listfiles.id,listfiles.Name, File.LStat, File.JobId]
626 my ($file, $attrib)=@_;
628 if (defined $attrib_name_id{$attrib}) {
630 my @d = split(' ', $file->[3]) ; # TODO : cache this
632 return from_base64($d[$attrib_name_id{$attrib}]);
634 } elsif ($attrib eq 'jobid') {
638 } elsif ($attrib eq 'name') {
643 die "Attribute not known : $attrib.\n";
649 my ($lstat,$attrib)=@_;
650 if ($lstat and defined $attrib_name_id{$attrib})
652 my @d = split(' ', $lstat) ; # TODO : cache this
653 return from_base64($d[$attrib_name_id{$attrib}]);
660 # Base 64 functions, directly from recover.pl.
662 # Karl Hakimian <hakimian@aha.com>
663 # This section is also under GPL v2 or later.
670 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
671 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
672 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',
673 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
674 '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '+', '/'
676 @base64_map = (0) x 128;
678 for (my $i=0; $i<64; $i++) {
679 $base64_map[ord($base64_digits[$i])] = $i;
694 if (substr($where, 0, 1) eq '-') {
696 $where = substr($where, 1);
699 while ($where ne '') {
701 my $d = substr($where, 0, 1);
702 $val += $base64_map[ord(substr($where, 0, 1))];
703 $where = substr($where, 1);
711 my @attribs = split(' ',$lstat);
712 foreach my $element (@attribs)
714 $element = from_base64($element);
720 # get jobids that the current user can view (ACL)
723 my ($self, @jobid) = @_;
724 my $filter = $self->get_client_filter();
726 my $jobids = $self->dbh_join(@jobid);
729 FROM Job JOIN Client USING (ClientId) $filter
730 WHERE Jobid IN ($jobids)";
731 my $res = $self->dbh_selectall_arrayref($q);
732 @jobid = map { $_->[0] } @$res;
737 ################################################################
742 use POSIX qw/strftime/;
745 my $conf = new Bweb::Config(config_file => $Bweb::config_file);
748 my $bvfs = new Bvfs(info => $conf);
751 my $action = CGI::param('action') || '';
753 my $args = $bvfs->get_form('pathid', 'filenameid', 'fileid', 'qdate',
754 'limit', 'offset', 'client');
756 if ($action eq 'batch') {
757 $bvfs->update_cache();
761 # All these functions are returning JSON compatible data
762 # for javascript parsing
764 if ($action eq 'list_client') { # list all client [ ['c1'],['c2']..]
765 print CGI::header('application/x-javascript');
767 my $filter = $bvfs->get_client_filter();
768 my $q = "SELECT Name FROM Client $filter";
769 my $ret = $bvfs->dbh_selectall_arrayref($q);
772 print join(',', map { "['$_->[0]']" } @$ret);
776 } elsif ($action eq 'list_job') { # list jobs for a client [[jobid,endtime,'desc'],..]
777 print CGI::header('application/x-javascript');
779 my $filter = $bvfs->get_client_filter();
781 SELECT Job.JobId,Job.EndTime, FileSet.FileSet, Job.Level, Job.JobStatus
782 FROM Job JOIN FileSet USING (FileSetId) JOIN Client USING (ClientId) $filter
783 WHERE Client.Name = '$args->{client}'
785 AND JobStatus IN ('f', 'T')
786 ORDER BY EndTime desc";
787 my $result = $bvfs->dbh_selectall_arrayref($query);
791 print join(',', map {
792 "[$_->[0], '$_->[1]', '$_->[1] $_->[2] $_->[3] ($_->[4]) $_->[0]']"
797 } elsif ($action eq 'list_storage') { # TODO: use .storage hier
798 print CGI::header('application/x-javascript');
800 my $q="SELECT Name FROM Storage";
801 my $lst = $bvfs->dbh_selectall_arrayref($q);
803 print join(',', map { "[ '$_->[0]' ]" } @$lst);
808 # get jobid param and apply user filter
809 my @jobid = $bvfs->get_jobids(grep { /^\d+(,\d+)*$/ } CGI::param('jobid'));
810 # get jobid from date arg
811 if (!scalar(@jobid) and $args->{qdate} and $args->{client}) {
812 @jobid = $bvfs->set_job_ids_for_date($args->{client}, $args->{qdate});
814 $bvfs->set_curjobids(@jobid);
815 $bvfs->set_limits($args->{limit}, $args->{offset});
817 if (!scalar(@jobid)) {
821 if (CGI::param('init')) { # used when choosing a job
822 $bvfs->update_brestore_table(@jobid);
825 my $pathid = CGI::param('node') || '';
826 my $path = CGI::param('path');
828 if ($pathid =~ /^(\d+)$/) {
831 $pathid = $bvfs->get_pathid($path);
833 $pathid = $bvfs->get_root();
836 $bvfs->ch_dir($pathid);
838 if ($action eq 'restore') {
840 # TODO: pouvoir choisir le replace et le jobname
841 my $arg = $bvfs->get_form(qw/client storage regexwhere where/);
843 if (!$arg->{client}) {
844 print "ERROR: missing client\n";
848 my $fileid = join(',', grep { /^\d+$/ } CGI::param('fileid'));
849 my @dirid = grep { /^\d+$/ } CGI::param('dirid');
850 my $inclause = join(',', @jobid);
856 "(SELECT JobId, FileIndex, FilenameId, PathId FROM File WHERE FileId IN ($fileid))";
859 # using this is not good because the sql engine doesn't know
860 # what LIKE will use. It will be better to get Path% in perl
861 # but it doesn't work with accents... :(
862 foreach my $dirid (@dirid) {
864 (SELECT File.JobId, File.FileIndex, File.FilenameId, File.PathId
865 FROM Path JOIN File USING (PathId)
867 (SELECT ". $bvfs->dbh_strcat('Path',"'\%'") ." FROM Path
868 WHERE PathId = $dirid
870 AND File.JobId IN ($inclause))";
873 return unless scalar(@union);
875 my $u = join(" UNION ", @union);
877 $bvfs->dbh_do("CREATE TEMPORARY TABLE btemp AS $u");
878 # TODO: remove FilenameId et PathId
880 # now we have to choose the file with the max(jobid)
881 # for each file of btemp
882 if ($bvfs->dbh_is_mysql()) {
883 $bvfs->dbh_do("CREATE TEMPORARY TABLE btemp2 AS (
884 SELECT max(JobId) as JobId, PathId, FilenameId
886 GROUP BY PathId, FilenameId
888 $bvfs->dbh_do("CREATE TABLE b2$$ AS (
889 SELECT btemp.JobId, btemp.FileIndex, btemp.FilenameId, btemp.PathId
891 WHERE btemp2.JobId = btemp.JobId
892 AND btemp2.PathId= btemp.PathId
893 AND btemp2.FilenameId = btemp.FilenameId
895 } else { # postgresql have distinct with more than one criteria...
896 $bvfs->dbh_do("CREATE TABLE b2$$ AS (
897 SELECT DISTINCT ON (PathId, FilenameId) JobId, FileIndex
899 ORDER BY PathId, FilenameId, JobId DESC
903 my $bconsole = $bvfs->get_bconsole();
904 # TODO: pouvoir choisir le replace et le jobname
905 my $jobid = $bconsole->run(client => $arg->{client},
906 storage => $arg->{storage},
907 where => $arg->{where},
908 regexwhere=> $arg->{regexwhere},
912 $bvfs->dbh_do("DROP TABLE b2$$");
915 print CGI::header('text/html');
916 $bvfs->display_begin();
917 $bvfs->error("Can't start your job:<br/>" . $bconsole->before());
918 $bvfs->display_end();
922 print CGI::redirect("bweb.pl?action=dsp_cur_job;jobid=$jobid") ;
926 print CGI::header('application/x-javascript');
928 if ($action eq 'list_files') {
930 my $files = $bvfs->ls_files();
931 # [ 1, 2, 3, "Bill", 10, '2007-01-01 00:00:00'],
932 # File.FilenameId, listfiles.id, listfiles.Name, File.LStat, File.JobId
935 map { my @p=Bvfs::parse_lstat($_->[3]); "[$_->[1],$_->[0],$pathid,$_->[4],\"$_->[2]\"," . $p[7] . ",'" . strftime('%Y-%m-%d %H:%m:%S', localtime($p[11])) . "']" } @$files);
938 } elsif ($action eq 'list_dirs') {
941 my $dirs = $bvfs->ls_dirs();
942 # return ($dirid,$dir_basename,$lstat,$jobid)
945 map { "{ 'jobid': '$bvfs->{curjobids}', 'id': '$_->[0]', 'text': '$_->[1]', 'cls':'folder'}" }
950 } elsif ($action eq 'list_versions') {
952 my $vafv = CGI::param('vafv') || 'false'; # view all file versions
953 $vafv = ($vafv eq 'false')?0:1;
957 #($pathid,$fileid,$jobid, $fid, $mtime, $size, $inchanger, $md5, $volname);
958 my $files = $bvfs->get_all_file_versions($args->{pathid}, $args->{filenameid}, $args->{client}, $vafv);
960 map { "[ $_->[3], $_->[1], $_->[0], $_->[2], '$_->[8]', $_->[6], '$_->[7]', $_->[5],'" . strftime('%Y-%m-%d %H:%m:%S', localtime($_->[4])) . "']" }
964 } elsif ($action eq 'get_media') {
966 my $jobid = join(',', @jobid);
967 my $fileid = join(',', grep { /^\d+(,\d+)*$/ } CGI::param('fileid'));
970 SELECT DISTINCT VolumeName, Enabled, InChanger
972 ( -- Get all media from this job
973 SELECT MIN(FirstIndex) AS FirstIndex, MAX(LastIndex) AS LastIndex,
974 VolumeName, Enabled, Inchanger
975 FROM JobMedia JOIN Media USING (MediaId)
976 WHERE JobId IN ($jobid)
977 GROUP BY VolumeName,Enabled,InChanger
979 WHERE File.FileId IN ($fileid)
980 AND File.FileIndex >= allmedia.FirstIndex
981 AND File.FileIndex <= allmedia.LastIndex
983 my $lst = $bvfs->dbh_selectall_arrayref($q);
985 print join(',', map { "['$_->[0]',$_->[1],$_->[2]]" } @$lst);
993 SELECT path || name AS name,pathid,filenameid,fileid,jobid
994 FROM File JOIN FileName USING (FilenameId) JOIN Path USING (PathId);
996 SELECT 'drop table ' || tablename || ';'
997 FROM pg_tables WHERE tablename ~ '^b[0-9]';