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
384 # the dirs in the file table
385 my $query = "SELECT FilenameId FROM Filename WHERE Name = ''";
386 my $sth = $self->dbh_prepare($query);
388 my $result = $sth->fetchrow_arrayref();
390 my $dir_filenameid = $result->[0];
392 # Then we get all the dir entries from File ...
394 SELECT PathId, Path, JobId, Lstat FROM (
396 SELECT Path1.PathId, Path1.Path, lower(Path1.Path),
397 listfile1.JobId, listfile1.Lstat
399 SELECT DISTINCT brestore_pathhierarchy1.PathId
400 FROM brestore_pathhierarchy AS brestore_pathhierarchy1
402 ON (brestore_pathhierarchy1.PathId = Path2.PathId)
403 JOIN brestore_pathvisibility AS brestore_pathvisibility1
404 ON (brestore_pathhierarchy1.PathId = brestore_pathvisibility1.PathId)
405 WHERE brestore_pathhierarchy1.PPathId = $pathid
406 AND brestore_pathvisibility1.jobid IN ($jobclause)) AS listpath1
407 JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId)
409 SELECT File1.PathId, File1.JobId, File1.Lstat FROM File AS File1
410 WHERE File1.FilenameId = $dir_filenameid
411 AND File1.JobId IN ($jobclause)) AS listfile1
412 ON (listpath1.PathId = listfile1.PathId)
413 ) AS A ORDER BY 2,3 DESC
415 $self->debug($query);
416 $sth=$self->dbh_prepare($query);
418 $result = $sth->fetchall_arrayref();
421 foreach my $refrow (@{$result})
423 my $dirid = $refrow->[0];
424 my $dir = $refrow->[1];
425 my $lstat = $refrow->[3];
426 my $jobid = $refrow->[2] || 0;
427 next if ($dirid eq $prev_dir);
428 # We have to clean up this dirname ... we only want it's 'basename'
432 my @temp = split ('/',$dir);
433 $return_value = pop @temp;
439 my @return_array = ($dirid,$return_value,$lstat,$jobid);
440 push @return_list,(\@return_array);
443 $self->debug(\@return_list);
444 return \@return_list;
447 # TODO : we want be able to restore files from a bad ended backup
448 # we have JobStatus IN ('T', 'A', 'E') and we must
450 # Data acces subs from here. Interaction with SGBD and caching
452 # This sub retrieves the list of jobs corresponding to the jobs selected in the
453 # GUI and stores them in @CurrentJobIds.
454 # date must be quoted
455 sub set_job_ids_for_date
457 my ($self, $client, $date)=@_;
459 if (!$client or !$date) {
462 my $filter = $self->get_client_filter();
463 # The algorithm : for a client, we get all the backups for each
464 # fileset, in reverse order Then, for each fileset, we store the 'good'
465 # incrementals and differentials until we have found a full so it goes
466 # like this : store all incrementals until we have found a differential
467 # or a full, then find the full
469 SELECT JobId, FileSet, Level, JobStatus
471 JOIN FileSet USING (FileSetId)
472 JOIN Client USING (ClientId) $filter
473 WHERE EndTime <= $date
474 AND Client.Name = '$client'
476 AND JobStatus IN ('T')
477 ORDER BY FileSet, JobTDate DESC";
480 my $result = $self->dbh_selectall_arrayref($query);
482 foreach my $refrow (@$result)
484 my $jobid = $refrow->[0];
485 my $fileset = $refrow->[1];
486 my $level = $refrow->[2];
488 defined $progress{$fileset} or $progress{$fileset}='U'; # U for unknown
490 next if $progress{$fileset} eq 'F'; # It's over for this fileset...
494 next unless ($progress{$fileset} eq 'U' or $progress{$fileset} eq 'I');
495 push @CurrentJobIds,($jobid);
497 elsif ($level eq 'D')
499 next if $progress{$fileset} eq 'D'; # We allready have a differential
500 push @CurrentJobIds,($jobid);
502 elsif ($level eq 'F')
504 push @CurrentJobIds,($jobid);
507 my $status = $refrow->[3] ;
508 if ($status eq 'T') { # good end of job
509 $progress{$fileset} = $level;
513 return @CurrentJobIds;
516 sub dbh_selectrow_arrayref
518 my ($self, $query) = @_;
519 $self->debug($query, up => 1);
520 return $self->{dbh}->selectrow_arrayref($query);
523 # Returns list of versions of a file that could be restored
524 # returns an array of
525 # (jobid,fileindex,mtime,size,inchanger,md5,volname,fileid)
526 # there will be only one jobid in the array of jobids...
527 sub get_all_file_versions
529 my ($self,$pathid,$fileid,$client,$see_all)=@_;
531 defined $see_all or $see_all=0;
536 "SELECT File.JobId, File.FileId, File.Lstat,
537 File.Md5, Media.VolumeName, Media.InChanger
538 FROM File, Job, Client, JobMedia, Media
539 WHERE File.FilenameId = $fileid
540 AND File.PathId=$pathid
541 AND File.JobId = Job.JobId
542 AND Job.ClientId = Client.ClientId
543 AND Job.JobId = JobMedia.JobId
544 AND File.FileIndex >= JobMedia.FirstIndex
545 AND File.FileIndex <= JobMedia.LastIndex
546 AND JobMedia.MediaId = Media.MediaId
547 AND Client.Name = '$client'";
549 $self->debug($query);
550 my $result = $self->dbh_selectall_arrayref($query);
552 foreach my $refrow (@$result)
554 my ($jobid, $fid, $lstat, $md5, $volname, $inchanger) = @$refrow;
555 my @attribs = parse_lstat($lstat);
556 my $mtime = array_attrib('st_mtime',\@attribs);
557 my $size = array_attrib('st_size',\@attribs);
559 my @list = ($pathid,$fileid,$jobid,
560 $fid, $mtime, $size, $inchanger,
562 push @versions, (\@list);
565 # We have the list of all versions of this file.
566 # We'll sort it by mtime desc, size, md5, inchanger desc
567 # the rest of the algorithm will be simpler
568 # ('FILE:',filename,jobid,fileindex,mtime,size,inchanger,md5,volname)
569 @versions = sort { $b->[4] <=> $a->[4]
570 || $a->[5] <=> $b->[5]
571 || $a->[7] cmp $a->[7]
572 || $b->[6] <=> $a->[6]} @versions;
575 my %allready_seen_by_mtime;
576 my %allready_seen_by_md5;
577 # Now we should create a new array with only the interesting records
578 foreach my $ref (@versions)
582 # The file has a md5. We compare his md5 to other known md5...
583 # We take size into account. It may happen that 2 files
584 # have the same md5sum and are different. size is a supplementary
587 # If we allready have a (better) version
588 next if ( (not $see_all)
589 and $allready_seen_by_md5{$ref->[7] .'-'. $ref->[5]});
591 # we never met this one before...
592 $allready_seen_by_md5{$ref->[7] .'-'. $ref->[5]}=1;
594 # Even if it has a md5, we should also work with mtimes
595 # We allready have a (better) version
596 next if ( (not $see_all)
597 and $allready_seen_by_mtime{$ref->[4] .'-'. $ref->[5]});
598 $allready_seen_by_mtime{$ref->[4] .'-'. $ref->[5] . '-' . $ref->[7]}=1;
600 # We reached there. The file hasn't been seen.
601 push @good_versions,($ref);
604 # To be nice with the user, we re-sort good_versions by
605 # inchanger desc, mtime desc
606 @good_versions = sort { $b->[4] <=> $a->[4]
607 || $b->[2] <=> $a->[2]} @good_versions;
609 return \@good_versions;
612 my %attrib_name_id = ( 'st_dev' => 0,'st_ino' => 1,'st_mode' => 2,
613 'st_nlink' => 3,'st_uid' => 4,'st_gid' => 5,
614 'st_rdev' => 6,'st_size' => 7,'st_blksize' => 8,
615 'st_blocks' => 9,'st_atime' => 10,'st_mtime' => 11,
616 'st_ctime' => 12,'LinkFI' => 13,'st_flags' => 14,
617 'data_stream' => 15);;
620 my ($attrib,$ref_attrib)=@_;
621 return $ref_attrib->[$attrib_name_id{$attrib}];
625 { # $file = [filenameid,listfiles.id,listfiles.Name, File.LStat, File.JobId]
627 my ($file, $attrib)=@_;
629 if (defined $attrib_name_id{$attrib}) {
631 my @d = split(' ', $file->[3]) ; # TODO : cache this
633 return from_base64($d[$attrib_name_id{$attrib}]);
635 } elsif ($attrib eq 'jobid') {
639 } elsif ($attrib eq 'name') {
644 die "Attribute not known : $attrib.\n";
650 my ($lstat,$attrib)=@_;
651 if ($lstat and defined $attrib_name_id{$attrib})
653 my @d = split(' ', $lstat) ; # TODO : cache this
654 return from_base64($d[$attrib_name_id{$attrib}]);
661 # Base 64 functions, directly from recover.pl.
663 # Karl Hakimian <hakimian@aha.com>
664 # This section is also under GPL v2 or later.
671 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
672 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
673 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm',
674 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z',
675 '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '+', '/'
677 @base64_map = (0) x 128;
679 for (my $i=0; $i<64; $i++) {
680 $base64_map[ord($base64_digits[$i])] = $i;
695 if (substr($where, 0, 1) eq '-') {
697 $where = substr($where, 1);
700 while ($where ne '') {
702 my $d = substr($where, 0, 1);
703 $val += $base64_map[ord(substr($where, 0, 1))];
704 $where = substr($where, 1);
712 my @attribs = split(' ',$lstat);
713 foreach my $element (@attribs)
715 $element = from_base64($element);
721 # get jobids that the current user can view (ACL)
724 my ($self, @jobid) = @_;
725 my $filter = $self->get_client_filter();
727 my $jobids = $self->dbh_join(@jobid);
730 FROM Job JOIN Client USING (ClientId) $filter
731 WHERE Jobid IN ($jobids)";
732 my $res = $self->dbh_selectall_arrayref($q);
733 @jobid = map { $_->[0] } @$res;
738 ################################################################
743 use POSIX qw/strftime/;
746 my $conf = new Bweb::Config(config_file => $Bweb::config_file);
749 my $bvfs = new Bvfs(info => $conf);
752 my $action = CGI::param('action') || '';
754 my $args = $bvfs->get_form('pathid', 'filenameid', 'fileid', 'qdate',
755 'limit', 'offset', 'client');
757 if ($action eq 'batch') {
758 $bvfs->update_cache();
762 # All these functions are returning JSON compatible data
763 # for javascript parsing
765 if ($action eq 'list_client') { # list all client [ ['c1'],['c2']..]
766 print CGI::header('application/x-javascript');
768 my $filter = $bvfs->get_client_filter();
769 my $q = "SELECT Name FROM Client $filter";
770 my $ret = $bvfs->dbh_selectall_arrayref($q);
773 print join(',', map { "['$_->[0]']" } @$ret);
777 } elsif ($action eq 'list_job') { # list jobs for a client [[jobid,endtime,'desc'],..]
778 print CGI::header('application/x-javascript');
780 my $filter = $bvfs->get_client_filter();
782 SELECT Job.JobId,Job.EndTime, FileSet.FileSet, Job.Level, Job.JobStatus
783 FROM Job JOIN FileSet USING (FileSetId) JOIN Client USING (ClientId) $filter
784 WHERE Client.Name = '$args->{client}'
786 AND JobStatus IN ('f', 'T')
787 ORDER BY EndTime desc";
788 my $result = $bvfs->dbh_selectall_arrayref($query);
792 print join(',', map {
793 "[$_->[0], '$_->[1]', '$_->[1] $_->[2] $_->[3] ($_->[4]) $_->[0]']"
798 } elsif ($action eq 'list_storage') { # TODO: use .storage here
799 print CGI::header('application/x-javascript');
801 my $q="SELECT Name FROM Storage";
802 my $lst = $bvfs->dbh_selectall_arrayref($q);
804 print join(',', map { "[ '$_->[0]' ]" } @$lst);
809 # get jobid param and apply user filter
810 my @jobid = $bvfs->get_jobids(grep { /^\d+(,\d+)*$/ } CGI::param('jobid'));
811 # get jobid from date arg
812 if (!scalar(@jobid) and $args->{qdate} and $args->{client}) {
813 @jobid = $bvfs->set_job_ids_for_date($args->{client}, $args->{qdate});
815 $bvfs->set_curjobids(@jobid);
816 $bvfs->set_limits($args->{limit}, $args->{offset});
818 if (!scalar(@jobid)) {
822 if (CGI::param('init')) { # used when choosing a job
823 $bvfs->update_brestore_table(@jobid);
826 my $pathid = CGI::param('node') || '';
827 my $path = CGI::param('path');
829 if ($pathid =~ /^(\d+)$/) {
832 $pathid = $bvfs->get_pathid($path);
834 $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
889 $bvfs->dbh_do("CREATE TABLE b2$$ AS (
890 SELECT btemp.JobId, btemp.FileIndex, btemp.FilenameId, btemp.PathId
892 WHERE btemp2.JobId = btemp.JobId
893 AND btemp2.PathId= btemp.PathId
894 AND btemp2.FilenameId = btemp.FilenameId
896 } else { # postgresql have distinct with more than one criteria...
897 $bvfs->dbh_do("CREATE TABLE b2$$ AS (
898 SELECT JobId, FileIndex
900 SELECT DISTINCT ON (PathId, FilenameId) JobId, FileIndex
902 ORDER BY PathId, FilenameId, JobId DESC
908 my $bconsole = $bvfs->get_bconsole();
909 # TODO: pouvoir choisir le replace et le jobname
910 my $jobid = $bconsole->run(client => $arg->{client},
911 storage => $arg->{storage},
912 where => $arg->{where},
913 regexwhere=> $arg->{regexwhere},
917 $bvfs->dbh_do("DROP TABLE b2$$");
920 print CGI::header('text/html');
921 $bvfs->display_begin();
922 $bvfs->error("Can't start your job:<br/>" . $bconsole->before());
923 $bvfs->display_end();
927 print CGI::redirect("bweb.pl?action=dsp_cur_job;jobid=$jobid") ;
938 print CGI::header('application/x-javascript');
940 if ($action eq 'list_files') {
941 print "[[0,0,0,0,'.',4096,'1970-01-01 00:00:00'],";
942 my $files = $bvfs->ls_files();
943 # [ 1, 2, 3, "Bill", 10, '2007-01-01 00:00:00'],
944 # File.FilenameId, listfiles.id, listfiles.Name, File.LStat, File.JobId
947 map { my @p=Bvfs::parse_lstat($_->[3]);
953 "'" . escape_quote($_->[2]) . "'",
955 "'" . strftime('%Y-%m-%d %H:%m:%S', localtime($p[11])) . "'") .
960 } elsif ($action eq 'list_dirs') {
963 my $dirs = $bvfs->ls_dirs();
964 # return ($dirid,$dir_basename,$lstat,$jobid)
967 map { "{ 'jobid': '$bvfs->{curjobids}', 'id': '$_->[0]'," .
968 "'text': '" . escape_quote($_->[1]) . "', 'cls':'folder'}" }
972 } elsif ($action eq 'list_versions') {
974 my $vafv = CGI::param('vafv') || 'false'; # view all file versions
975 $vafv = ($vafv eq 'false')?0:1;
979 #($pathid,$fileid,$jobid, $fid, $mtime, $size, $inchanger, $md5, $volname);
980 my $files = $bvfs->get_all_file_versions($args->{pathid}, $args->{filenameid}, $args->{client}, $vafv);
982 map { "[ $_->[3], $_->[1], $_->[0], $_->[2], '$_->[8]', $_->[6], '$_->[7]', $_->[5],'" . strftime('%Y-%m-%d %H:%m:%S', localtime($_->[4])) . "']" }
986 } elsif ($action eq 'get_media') {
988 my $jobid = join(',', @jobid);
989 my $fileid = join(',', grep { /^\d+(,\d+)*$/ } CGI::param('fileid'));
992 SELECT DISTINCT VolumeName, Enabled, InChanger
994 ( -- Get all media from this job
995 SELECT MIN(FirstIndex) AS FirstIndex, MAX(LastIndex) AS LastIndex,
996 VolumeName, Enabled, Inchanger
997 FROM JobMedia JOIN Media USING (MediaId)
998 WHERE JobId IN ($jobid)
999 GROUP BY VolumeName,Enabled,InChanger
1001 WHERE File.FileId IN ($fileid)
1002 AND File.FileIndex >= allmedia.FirstIndex
1003 AND File.FileIndex <= allmedia.LastIndex
1005 my $lst = $bvfs->dbh_selectall_arrayref($q);
1007 print join(',', map { "['$_->[0]',$_->[1],$_->[2]]" } @$lst);
1014 CREATE VIEW files AS
1015 SELECT path || name AS name,pathid,filenameid,fileid,jobid
1016 FROM File JOIN FileName USING (FilenameId) JOIN Path USING (PathId);
1018 SELECT 'drop table ' || tablename || ';'
1019 FROM pg_tables WHERE tablename ~ '^b[0-9]';