1 ################################################################
6 Bweb - A Bacula web interface
7 Bacula® - The Network Backup Solution
9 Copyright (C) 2000-2006 Free Software Foundation Europe e.V.
11 The main author of Bweb is Eric Bollengier.
12 The main author of Bacula is Kern Sibbald, with contributions from
13 many others, a complete list can be found in the file AUTHORS.
15 This program is Free Software; you can redistribute it and/or
16 modify it under the terms of version two of the GNU General Public
17 License as published by the Free Software Foundation plus additions
18 that are listed in the file LICENSE.
20 This program is distributed in the hope that it will be useful, but
21 WITHOUT ANY WARRANTY; without even the implied warranty of
22 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
23 General Public License for more details.
25 You should have received a copy of the GNU General Public License
26 along with this program; if not, write to the Free Software
27 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
30 Bacula® is a registered trademark of John Walker.
31 The licensor of Bacula is the Free Software Foundation Europe
32 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zurich,
33 Switzerland, email:ftf@fsfeurope.org.
45 Bweb::Gui - Base package for all Bweb object
49 This package define base fonction like new, display, etc..
54 our $template_dir='/usr/share/bweb/tpl';
59 new - creation a of new Bweb object
63 This function take an hash of argument and place them
66 IE : $obj = new Obj(name => 'test', age => '10');
68 $obj->{name} eq 'test' and $obj->{age} eq 10
74 my ($class, %arg) = @_;
79 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
86 my ($self, $what) = @_;
90 print "<pre>" . Data::Dumper::Dumper($what) . "</pre>";
92 print "<pre>$what</pre>";
99 error - display an error to the user
103 this function set $self->{error} with arg, display a message with
104 error.tpl and return 0
109 return $self->error("Can't use this file");
116 my ($self, $what) = @_;
117 $self->{error} = $what;
118 $self->display($self, 'error.tpl');
124 display - display an html page with HTML::Template
128 this function is use to render all html codes. it takes an
129 ref hash as arg in which all param are usable in template.
131 it will use global template_dir to search the template file.
133 hash keys are not sensitive. See HTML::Template for more
134 explanations about the hash ref. (it's can be quiet hard to understand)
138 $ref = { name => 'me', age => 26 };
139 $self->display($ref, "people.tpl");
145 my ($self, $hash, $tpl) = @_ ;
147 my $template = HTML::Template->new(filename => $tpl,
148 path =>[$template_dir],
149 die_on_bad_params => 0,
150 case_sensitive => 0);
152 foreach my $var (qw/limit offset/) {
154 unless ($hash->{$var}) {
155 my $value = CGI::param($var) || '';
157 if ($value =~ /^(\d+)$/) {
158 $template->param($var, $1) ;
163 $template->param('thisurl', CGI::url(-relative => 1, -query=>1));
164 $template->param('loginname', CGI::remote_user());
166 $template->param($hash);
167 print $template->output();
171 ################################################################
173 package Bweb::Config;
175 use base q/Bweb::Gui/;
179 Bweb::Config - read, write, display, modify configuration
183 this package is used for manage configuration
187 $conf = new Bweb::Config(config_file => '/path/to/conf');
198 =head1 PACKAGE VARIABLE
200 %k_re - hash of all acceptable option.
204 this variable permit to check all option with a regexp.
208 our %k_re = ( dbi => qr/^(dbi:(Pg|mysql):(?:\w+=[\w\d\.-]+;?)+)$/i,
209 user => qr/^([\w\d\.-]+)$/i,
210 password => qr/^(.*)$/i,
211 template_dir => qr!^([/\w\d\.-]+)$!,
212 debug => qr/^(on)?$/,
213 email_media => qr/^([\w\d\.-]+@[\d\w\.-]+)$/,
214 graph_font => qr!^([/\w\d\.-]+.ttf)$!,
215 bconsole => qr!^(.+)?$!,
216 syslog_file => qr!^(.+)?$!,
217 log_dir => qr!^(.+)?$!,
222 load - load config_file
226 this function load the specified config_file.
234 unless (open(FP, $self->{config_file}))
236 return $self->error("$self->{config_file} : $!");
238 my $f=''; my $tmpbuffer;
239 while(read FP,$tmpbuffer,4096)
247 no strict; # I have no idea of the contents of the file
254 return $self->error("If you update from an old bweb install, your must reload this page and if it's fail again, you have to configure bweb again...") ;
257 foreach my $k (keys %$VAR1) {
258 $self->{$k} = $VAR1->{$k};
266 load_old - load old configuration format
274 unless (open(FP, $self->{config_file}))
276 return $self->error("$self->{config_file} : $!");
279 while (my $line = <FP>)
282 my ($k, $v) = split(/\s*=\s*/, $line, 2);
294 save - save the current configuration to config_file
302 if ($self->{ach_list}) {
303 # shortcut for display_begin
304 $self->{achs} = [ map {{ name => $_ }}
305 keys %{$self->{ach_list}}
309 unless (open(FP, ">$self->{config_file}"))
311 return $self->error("$self->{config_file} : $!\n" .
312 "You must add this to your config file\n"
313 . Data::Dumper::Dumper($self));
316 print FP Data::Dumper::Dumper($self);
324 edit, view, modify - html form ouput
332 $self->display($self, "config_edit.tpl");
338 $self->display($self, "config_view.tpl");
348 foreach my $k (CGI::param())
350 next unless (exists $k_re{$k}) ;
351 my $val = CGI::param($k);
352 if ($val =~ $k_re{$k}) {
355 $self->{error} .= "bad parameter : $k = [$val]";
361 if ($self->{error}) { # an error as occured
362 $self->display($self, 'error.tpl');
370 ################################################################
372 package Bweb::Client;
374 use base q/Bweb::Gui/;
378 Bweb::Client - Bacula FD
382 this package is use to do all Client operations like, parse status etc...
386 $client = new Bweb::Client(name => 'zog-fd');
387 $client->status(); # do a 'status client=zog-fd'
393 display_running_job - Html display of a running job
397 this function is used to display information about a current job
401 sub display_running_job
403 my ($self, $conf, $jobid) = @_ ;
405 my $status = $self->status($conf);
408 if ($status->{$jobid}) {
409 $self->display($status->{$jobid}, "client_job_status.tpl");
412 for my $id (keys %$status) {
413 $self->display($status->{$id}, "client_job_status.tpl");
420 $client = new Bweb::Client(name => 'plume-fd');
422 $client->status($bweb);
426 dirty hack to parse "status client=xxx-fd"
430 JobId 105 Job Full_plume.2006-06-06_17.22.23 is running.
431 Backup Job started: 06-jun-06 17:22
432 Files=8,971 Bytes=194,484,132 Bytes/sec=7,480,158
433 Files Examined=10,697
434 Processing file: /home/eric/.openoffice.org2/user/config/standard.sod
440 JobName => Full_plume.2006-06-06_17.22.23,
443 Bytes => 194,484,132,
453 my ($self, $conf) = @_ ;
455 if (defined $self->{cur_jobs}) {
456 return $self->{cur_jobs} ;
460 my $b = new Bconsole(pref => $conf);
461 my $ret = $b->send_cmd("st client=$self->{name}");
465 for my $r (split(/\n/, $ret)) {
467 $r =~ s/(^\s+|\s+$)//g;
468 if ($r =~ /JobId (\d+) Job (\S+)/) {
470 $arg->{$jobid} = { @param, JobId => $jobid } ;
474 @param = ( JobName => $2 );
476 } elsif ($r =~ /=.+=/) {
477 push @param, split(/\s+|\s*=\s*/, $r) ;
479 } elsif ($r =~ /=/) { # one per line
480 push @param, split(/\s*=\s*/, $r) ;
482 } elsif ($r =~ /:/) { # one per line
483 push @param, split(/\s*:\s*/, $r, 2) ;
487 if ($jobid and @param) {
488 $arg->{$jobid} = { @param,
490 Client => $self->{name},
494 $self->{cur_jobs} = $arg ;
500 ################################################################
502 package Bweb::Autochanger;
504 use base q/Bweb::Gui/;
508 Bweb::Autochanger - Object to manage Autochanger
512 this package will parse the mtx output and manage drives.
516 $auto = new Bweb::Autochanger(precmd => 'sudo');
518 $auto = new Bweb::Autochanger(precmd => 'ssh root@robot');
522 $auto->slot_is_full(10);
523 $auto->transfer(10, 11);
529 my ($class, %arg) = @_;
532 name => '', # autochanger name
533 label => {}, # where are volume { label1 => 40, label2 => drive0 }
534 drive => [], # drive use [ 'media1', 'empty', ..]
535 slot => [], # slot use [ undef, 'empty', 'empty', ..] no slot 0
536 io => [], # io slot number list [ 41, 42, 43...]
537 info => {slot => 0, # informations (slot, drive, io)
541 mtxcmd => '/usr/sbin/mtx',
543 device => '/dev/changer',
544 precmd => '', # ssh command
545 bweb => undef, # link to bacula web object (use for display)
548 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
555 status - parse the output of mtx status
559 this function will launch mtx status and parse the output. it will
560 give a perlish view of the autochanger content.
562 it uses ssh if the autochanger is on a other host.
569 my @out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} status` ;
571 # TODO : reset all infos
572 $self->{info}->{drive} = 0;
573 $self->{info}->{slot} = 0;
574 $self->{info}->{io} = 0;
576 #my @out = `cat /home/eric/travail/brestore/plume/mtx` ;
579 # Storage Changer /dev/changer:2 Drives, 45 Slots ( 5 Import/Export )
580 #Data Transfer Element 0:Full (Storage Element 1 Loaded):VolumeTag = 000000
581 #Data Transfer Element 1:Empty
582 # Storage Element 1:Empty
583 # Storage Element 2:Full :VolumeTag=000002
584 # Storage Element 3:Empty
585 # Storage Element 4:Full :VolumeTag=000004
586 # Storage Element 5:Full :VolumeTag=000001
587 # Storage Element 6:Full :VolumeTag=000003
588 # Storage Element 7:Empty
589 # Storage Element 41 IMPORT/EXPORT:Empty
590 # Storage Element 41 IMPORT/EXPORT:Full :VolumeTag=000002
595 # Storage Element 7:Empty
596 # Storage Element 2:Full :VolumeTag=000002
597 if ($l =~ /Storage Element (\d+):(Empty|Full)(\s+:VolumeTag=([\w\d]+))?/){
600 $self->set_empty_slot($1);
602 $self->set_slot($1, $4);
605 } elsif ($l =~ /Data Transfer.+(\d+):(Full|Empty)(\s+.Storage Element (\d+) Loaded.(:VolumeTag = ([\w\d]+))?)?/) {
608 $self->set_empty_drive($1);
610 $self->set_drive($1, $4, $6);
613 } elsif ($l =~ /Storage Element (\d+).+IMPORT\/EXPORT:(Empty|Full)( :VolumeTag=([\d\w]+))?/)
616 $self->set_empty_io($1);
618 $self->set_io($1, $4);
621 # Storage Changer /dev/changer:2 Drives, 30 Slots ( 1 Import/Export )
623 } elsif ($l =~ /Storage Changer .+:(\d+) Drives, (\d+) Slots/) {
624 $self->{info}->{drive} = $1;
625 $self->{info}->{slot} = $2;
626 if ($l =~ /(\d+)\s+Import/) {
627 $self->{info}->{io} = $1 ;
629 $self->{info}->{io} = 0;
634 $self->debug($self) ;
639 my ($self, $slot) = @_;
642 if ($self->{slot}->[$slot] eq 'loaded') {
646 my $label = $self->{slot}->[$slot] ;
648 return $self->is_media_loaded($label);
653 my ($self, $drive, $slot) = @_;
655 return 0 if (not defined $drive or $self->{drive}->[$drive] eq 'empty') ;
656 return 0 if ($self->slot_is_full($slot)) ;
658 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} unload $slot $drive 2>&1`;
661 my $content = $self->get_slot($slot);
662 print "content = $content<br/> $drive => $slot<br/>";
663 $self->set_empty_drive($drive);
664 $self->set_slot($slot, $content);
667 $self->{error} = $out;
672 # TODO: load/unload have to use mtx script from bacula
675 my ($self, $drive, $slot) = @_;
677 return 0 if (not defined $drive or $self->{drive}->[$drive] ne 'empty') ;
678 return 0 unless ($self->slot_is_full($slot)) ;
680 print "Loading drive $drive with slot $slot<br/>\n";
681 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} load $slot $drive 2>&1`;
684 my $content = $self->get_slot($slot);
685 print "content = $content<br/> $slot => $drive<br/>";
686 $self->set_drive($drive, $slot, $content);
689 $self->{error} = $out;
697 my ($self, $media) = @_;
699 unless ($self->{label}->{$media}) {
703 if ($self->{label}->{$media} =~ /drive\d+/) {
713 return (defined $self->{info}->{io} and $self->{info}->{io} > 0);
718 my ($self, $slot, $tag) = @_;
719 $self->{slot}->[$slot] = $tag || 'full';
720 push @{ $self->{io} }, $slot;
723 $self->{label}->{$tag} = $slot;
729 my ($self, $slot) = @_;
731 push @{ $self->{io} }, $slot;
733 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
734 $self->{slot}->[$slot] = 'empty';
740 my ($self, $slot) = @_;
741 return $self->{slot}->[$slot];
746 my ($self, $slot, $tag) = @_;
747 $self->{slot}->[$slot] = $tag || 'full';
750 $self->{label}->{$tag} = $slot;
756 my ($self, $slot) = @_;
758 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
759 $self->{slot}->[$slot] = 'empty';
765 my ($self, $drive) = @_;
766 $self->{drive}->[$drive] = 'empty';
771 my ($self, $drive, $slot, $tag) = @_;
772 $self->{drive}->[$drive] = $tag || $slot;
774 $self->{slot}->[$slot] = $tag || 'loaded';
777 $self->{label}->{$tag} = "drive$drive";
783 my ($self, $slot) = @_;
785 # slot don't exists => full
786 if (not defined $self->{slot}->[$slot]) {
790 if ($self->{slot}->[$slot] eq 'empty') {
793 return 1; # vol, full, loaded
796 sub slot_get_first_free
799 for (my $slot=1; $slot < $self->{info}->{slot}; $slot++) {
800 return $slot unless ($self->slot_is_full($slot));
804 sub io_get_first_free
808 foreach my $slot (@{ $self->{io} }) {
809 return $slot unless ($self->slot_is_full($slot));
816 my ($self, $media) = @_;
818 return $self->{label}->{$media} ;
823 my ($self, $media) = @_;
825 return defined $self->{label}->{$media} ;
830 my ($self, $slot) = @_;
832 unless ($self->slot_is_full($slot)) {
833 print "Autochanger $self->{name} slot $slot is empty\n";
838 if ($self->is_slot_loaded($slot)) {
841 print "Autochanger $self->{name} $slot is currently in use\n";
845 # autochanger must have I/O
846 unless ($self->have_io()) {
847 print "Autochanger $self->{name} don't have I/O, you can take media yourself\n";
851 my $dst = $self->io_get_first_free();
854 print "Autochanger $self->{name} you must empty I/O first\n";
857 $self->transfer($slot, $dst);
862 my ($self, $src, $dst) = @_ ;
863 print "<pre>$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst</pre>\n";
864 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst 2>&1`;
867 my $content = $self->get_slot($src);
868 print "$content ($src) => $dst<br/>";
869 $self->{slot}->[$src] = 'empty';
870 $self->set_slot($dst, $content);
873 $self->{error} = $out;
880 my ($self, $index) = @_;
881 return $self->{drive_name}->[$index];
884 # TODO : do a tapeinfo request to get informations
894 for my $slot (@{$self->{io}})
896 if ($self->is_slot_loaded($slot)) {
897 print "$slot is currently loaded\n";
901 if ($self->slot_is_full($slot))
903 my $free = $self->slot_get_first_free() ;
904 print "want to move $slot to $free\n";
907 $self->transfer($slot, $free) || print "$self->{error}\n";
910 $self->{error} = "E : Can't find free slot";
916 # TODO : this is with mtx status output,
917 # we can do an other function from bacula view (with StorageId)
921 my $bweb = $self->{bweb};
923 # $self->{label} => ('vol1', 'vol2', 'vol3', ..);
924 my $media_list = $bweb->dbh_join( keys %{ $self->{label} });
927 SELECT Media.VolumeName AS volumename,
928 Media.VolStatus AS volstatus,
929 Media.LastWritten AS lastwritten,
930 Media.VolBytes AS volbytes,
931 Media.MediaType AS mediatype,
933 Media.InChanger AS inchanger,
935 $bweb->{sql}->{FROM_UNIXTIME}(
936 $bweb->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
937 + $bweb->{sql}->{TO_SEC}(Media.VolRetention)
940 INNER JOIN Pool USING (PoolId)
942 WHERE Media.VolumeName IN ($media_list)
945 my $all = $bweb->dbh_selectall_hashref($query, 'volumename') ;
947 # TODO : verify slot and bacula slot
951 for (my $slot=1; $slot <= $self->{info}->{slot} ; $slot++) {
953 if ($self->slot_is_full($slot)) {
955 my $vol = $self->{slot}->[$slot];
956 if (defined $all->{$vol}) { # TODO : autochanger without barcodes
958 my $bslot = $all->{$vol}->{slot} ;
959 my $inchanger = $all->{$vol}->{inchanger};
961 # if bacula slot or inchanger flag is bad, we display a message
962 if ($bslot != $slot or !$inchanger) {
963 push @to_update, $slot;
966 $all->{$vol}->{realslot} = $slot;
968 push @{ $param }, $all->{$vol};
970 } else { # empty or no label
971 push @{ $param }, {realslot => $slot,
972 volstatus => 'Unknow',
973 volumename => $self->{slot}->[$slot]} ;
976 push @{ $param }, {realslot => $slot, volumename => 'empty'} ;
980 my $i=0; my $drives = [] ;
981 foreach my $d (@{ $self->{drive} }) {
982 $drives->[$i] = { index => $i,
983 load => $self->{drive}->[$i],
984 name => $self->{drive_name}->[$i],
989 $bweb->display({ Name => $self->{name},
990 nb_drive => $self->{info}->{drive},
991 nb_io => $self->{info}->{io},
994 Update => scalar(@to_update) },
1002 ################################################################
1006 use base q/Bweb::Gui/;
1010 Bweb - main Bweb package
1014 this package is use to compute and display informations
1019 use POSIX qw/strftime/;
1025 %sql_func - hash to make query mysql/postgresql compliant
1031 UNIX_TIMESTAMP => '',
1032 FROM_UNIXTIME => '',
1033 TO_SEC => " interval '1 second' * ",
1034 SEC_TO_INT => "SEC_TO_INT",
1037 STARTTIME_DAY => " date_trunc('day', Job.StartTime) ",
1038 STARTTIME_HOUR => " date_trunc('hour', Job.StartTime) ",
1039 STARTTIME_MONTH => " date_trunc('month', Job.StartTime) ",
1040 STARTTIME_PHOUR=> " date_part('hour', Job.StartTime) ",
1041 STARTTIME_PDAY => " date_part('day', Job.StartTime) ",
1042 STARTTIME_PMONTH => " date_part('month', Job.StartTime) ",
1045 UNIX_TIMESTAMP => 'UNIX_TIMESTAMP',
1046 FROM_UNIXTIME => 'FROM_UNIXTIME',
1049 SEC_TO_TIME => 'SEC_TO_TIME',
1050 MATCH => " REGEXP ",
1051 STARTTIME_DAY => " DATE_FORMAT(StartTime, '%Y-%m-%d') ",
1052 STARTTIME_HOUR => " DATE_FORMAT(StartTime, '%Y-%m-%d %H') ",
1053 STARTTIME_MONTH => " DATE_FORMAT(StartTime, '%Y-%m') ",
1054 STARTTIME_PHOUR=> " DATE_FORMAT(StartTime, '%H') ",
1055 STARTTIME_PDAY => " DATE_FORMAT(StartTime, '%d') ",
1056 STARTTIME_PMONTH => " DATE_FORMAT(StartTime, '%m') ",
1060 sub dbh_selectall_arrayref
1062 my ($self, $query) = @_;
1063 $self->connect_db();
1064 $self->debug($query);
1065 return $self->{dbh}->selectall_arrayref($query);
1070 my ($self, @what) = @_;
1071 return join(',', $self->dbh_quote(@what)) ;
1076 my ($self, @what) = @_;
1078 $self->connect_db();
1080 return map { $self->{dbh}->quote($_) } @what;
1082 return $self->{dbh}->quote($what[0]) ;
1088 my ($self, $query) = @_ ;
1089 $self->connect_db();
1090 $self->debug($query);
1091 return $self->{dbh}->do($query);
1094 sub dbh_selectall_hashref
1096 my ($self, $query, $join) = @_;
1098 $self->connect_db();
1099 $self->debug($query);
1100 return $self->{dbh}->selectall_hashref($query, $join) ;
1103 sub dbh_selectrow_hashref
1105 my ($self, $query) = @_;
1107 $self->connect_db();
1108 $self->debug($query);
1109 return $self->{dbh}->selectrow_hashref($query) ;
1115 my @unit = qw(b Kb Mb Gb Tb);
1116 my $val = shift || 0;
1118 my $format = '%i %s';
1119 while ($val / 1024 > 1) {
1123 $format = ($i>0)?'%0.1f %s':'%i %s';
1124 return sprintf($format, $val, $unit[$i]);
1127 # display Day, Hour, Year
1133 $val /= 60; # sec -> min
1135 if ($val / 60 <= 1) {
1139 $val /= 60; # min -> hour
1140 if ($val / 24 <= 1) {
1141 return "$val hours";
1144 $val /= 24; # hour -> day
1145 if ($val / 365 < 2) {
1149 $val /= 365 ; # day -> year
1151 return "$val years";
1154 # get Day, Hour, Year
1160 unless ($val =~ /^\s*(\d+)\s*(\w)\w*\s*$/) {
1164 my %times = ( m => 60,
1170 my $mult = $times{$2} || 0;
1180 unless ($self->{dbh}) {
1181 $self->{dbh} = DBI->connect($self->{info}->{dbi},
1182 $self->{info}->{user},
1183 $self->{info}->{password});
1185 print "Can't connect to your database, see error log\n"
1186 unless ($self->{dbh});
1188 $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
1190 if ($self->{info}->{dbi} =~ /^dbi:Pg/i) {
1191 $self->{dbh}->do("SET datestyle TO 'ISO, YMD'");
1198 my ($class, %arg) = @_;
1200 dbh => undef, # connect_db();
1202 dbi => '', # DBI:Pg:database=bacula;host=127.0.0.1
1208 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
1210 if ($self->{info}->{dbi} =~ /DBI:(\w+):/i) {
1211 $self->{sql} = $sql_func{$1};
1214 $self->{debug} = $self->{info}->{debug};
1215 $Bweb::Gui::template_dir = $self->{info}->{template_dir};
1223 $self->display($self->{info}, "begin.tpl");
1229 $self->display($self->{info}, "end.tpl");
1237 my $arg = $self->get_form("client", "qre_client");
1239 if ($arg->{qre_client}) {
1240 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1241 } elsif ($arg->{client}) {
1242 $where = "WHERE Name = '$arg->{client}' ";
1246 SELECT Name AS name,
1248 AutoPrune AS autoprune,
1249 FileRetention AS fileretention,
1250 JobRetention AS jobretention
1255 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1257 my $dsp = { ID => $cur_id++,
1258 clients => [ values %$all] };
1260 $self->display($dsp, "client_list.tpl") ;
1265 my ($self, %arg) = @_;
1272 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1274 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1276 $self->{sql}->{TO_SEC}($arg{age})
1279 $label = "last " . human_sec($arg{age});
1282 if ($arg{groupby}) {
1283 $limit .= " GROUP BY $arg{groupby} ";
1287 $limit .= " ORDER BY $arg{order} ";
1291 $limit .= " LIMIT $arg{limit} ";
1292 $label .= " limited to $arg{limit}";
1296 $limit .= " OFFSET $arg{offset} ";
1297 $label .= " with $arg{offset} offset ";
1301 $label = 'no filter';
1304 return ($limit, $label);
1309 $bweb->get_form(...) - Get useful stuff
1313 This function get and check parameters against regexp.
1315 If word begin with 'q', the return will be quoted or join quoted
1316 if it's end with 's'.
1321 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1324 qclient => 'plume-fd',
1325 qpools => "'plume-fd', 'test-fd', '...'",
1332 my ($self, @what) = @_;
1333 my %what = map { $_ => 1 } @what;
1353 my %opt_ss =( # string with space
1357 my %opt_s = ( # default to ''
1374 my %opt_p = ( # option with path
1382 my %opt_d = ( # option with date
1387 foreach my $i (@what) {
1388 if (exists $opt_i{$i}) {# integer param
1389 my $value = CGI::param($i) || $opt_i{$i} ;
1390 if ($value =~ /^(\d+)$/) {
1393 } elsif ($opt_s{$i}) { # simple string param
1394 my $value = CGI::param($i) || '';
1395 if ($value =~ /^([\w\d\.-]+)$/) {
1398 } elsif ($opt_ss{$i}) { # simple string param (with space)
1399 my $value = CGI::param($i) || '';
1400 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1403 } elsif ($i =~ /^j(\w+)s$/) { # quote join args
1404 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1406 $ret{$i} = $self->dbh_join(@value) ;
1409 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1410 my $value = CGI::param($1) ;
1412 $ret{$i} = $self->dbh_quote($value);
1415 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1416 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1417 grep { ! /^\s*$/ } CGI::param($1) ];
1418 } elsif (exists $opt_p{$i}) {
1419 my $value = CGI::param($i) || '';
1420 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1423 } elsif (exists $opt_d{$i}) {
1424 my $value = CGI::param($i) || '';
1425 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1432 foreach my $s (CGI::param('slot')) {
1433 if ($s =~ /^(\d+)$/) {
1434 push @{$ret{slots}}, $s;
1439 if ($what{db_clients}) {
1441 SELECT Client.Name as clientname
1445 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1446 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1450 if ($what{db_mediatypes}) {
1452 SELECT MediaType as mediatype
1456 my $medias = $self->dbh_selectall_hashref($query, 'mediatype');
1457 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1461 if ($what{db_locations}) {
1463 SELECT Location as location, Cost as cost FROM Location
1465 my $loc = $self->dbh_selectall_hashref($query, 'location');
1466 $ret{db_locations} = [ sort { $a->{location}
1472 if ($what{db_pools}) {
1473 my $query = "SELECT Name as name FROM Pool";
1475 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1476 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1479 if ($what{db_filesets}) {
1481 SELECT FileSet.FileSet AS fileset
1485 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1487 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1488 values %$filesets] ;
1491 if ($what{db_jobnames}) {
1493 SELECT DISTINCT Job.Name AS jobname
1497 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1499 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1500 values %$jobnames] ;
1503 if ($what{db_devices}) {
1505 SELECT Device.Name AS name
1509 my $devices = $self->dbh_selectall_hashref($query, 'name');
1511 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1522 my $fields = $self->get_form(qw/age level status clients filesets
1524 db_clients limit db_filesets width height
1525 qclients qfilesets qjobnames db_jobnames/);
1528 my $url = CGI::url(-full => 0,
1531 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1533 # this organisation is to keep user choice between 2 click
1534 # TODO : fileset and client selection doesn't work
1543 sub display_client_job
1545 my ($self, %arg) = @_ ;
1547 $arg{order} = ' Job.JobId DESC ';
1548 my ($limit, $label) = $self->get_limit(%arg);
1550 my $clientname = $self->dbh_quote($arg{clientname});
1553 SELECT DISTINCT Job.JobId AS jobid,
1554 Job.Name AS jobname,
1555 FileSet.FileSet AS fileset,
1557 StartTime AS starttime,
1558 JobFiles AS jobfiles,
1559 JobBytes AS jobbytes,
1560 JobStatus AS jobstatus,
1561 JobErrors AS joberrors
1563 FROM Client,Job,FileSet
1564 WHERE Client.Name=$clientname
1565 AND Client.ClientId=Job.ClientId
1566 AND Job.FileSetId=FileSet.FileSetId
1570 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
1572 $self->display({ clientname => $arg{clientname},
1575 Jobs => [ values %$all ],
1577 "display_client_job.tpl") ;
1580 sub get_selected_media_location
1584 my $medias = $self->get_form('jmedias');
1586 unless ($medias->{jmedias}) {
1591 SELECT Media.VolumeName AS volumename, Location.Location AS location
1592 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1593 WHERE Media.VolumeName IN ($medias->{jmedias})
1596 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1598 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1609 my $medias = $self->get_selected_media_location();
1615 my $elt = $self->get_form('db_locations');
1617 $self->display({ ID => $cur_id++,
1618 %$elt, # db_locations
1620 sort { $a->{volumename} cmp $b->{volumename} } values %$medias
1630 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1632 $self->display($elt, "help_extern.tpl");
1635 sub help_extern_compute
1639 my $number = CGI::param('limit') || '' ;
1640 unless ($number =~ /^(\d+)$/) {
1641 return $self->error("Bad arg number : $number ");
1644 my ($sql, undef) = $self->get_param('pools',
1645 'locations', 'mediatypes');
1648 SELECT Media.VolumeName AS volumename,
1649 Media.VolStatus AS volstatus,
1650 Media.LastWritten AS lastwritten,
1651 Media.MediaType AS mediatype,
1652 Media.VolMounts AS volmounts,
1654 Media.Recycle AS recycle,
1655 $self->{sql}->{FROM_UNIXTIME}(
1656 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1657 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1660 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1661 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1663 WHERE Media.InChanger = 1
1664 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1666 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1670 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1672 $self->display({ Medias => [ values %$all ] },
1673 "help_extern_compute.tpl");
1680 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1681 $self->display($param, "help_intern.tpl");
1684 sub help_intern_compute
1688 my $number = CGI::param('limit') || '' ;
1689 unless ($number =~ /^(\d+)$/) {
1690 return $self->error("Bad arg number : $number ");
1693 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1695 if (CGI::param('expired')) {
1697 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1698 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1704 SELECT Media.VolumeName AS volumename,
1705 Media.VolStatus AS volstatus,
1706 Media.LastWritten AS lastwritten,
1707 Media.MediaType AS mediatype,
1708 Media.VolMounts AS volmounts,
1710 $self->{sql}->{FROM_UNIXTIME}(
1711 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1712 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1715 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1716 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1718 WHERE Media.InChanger <> 1
1719 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1720 AND Media.Recycle = 1
1722 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1726 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1728 $self->display({ Medias => [ values %$all ] },
1729 "help_intern_compute.tpl");
1735 my ($self, %arg) = @_ ;
1737 my ($limit, $label) = $self->get_limit(%arg);
1741 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1742 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1743 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1744 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1745 (SELECT count(Job.JobId)
1747 WHERE Job.JobStatus IN ('E','e','f','A')
1750 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1753 my $row = $self->dbh_selectrow_hashref($query) ;
1755 $row->{nb_bytes} = human_size($row->{nb_bytes});
1757 $row->{db_size} = '???';
1758 $row->{label} = $label;
1760 $self->display($row, "general.tpl");
1765 my ($self, @what) = @_ ;
1766 my %elt = map { $_ => 1 } @what;
1771 if ($elt{clients}) {
1772 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1774 $ret{clients} = \@clients;
1775 my $str = $self->dbh_join(@clients);
1776 $limit .= "AND Client.Name IN ($str) ";
1780 if ($elt{filesets}) {
1781 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1783 $ret{filesets} = \@filesets;
1784 my $str = $self->dbh_join(@filesets);
1785 $limit .= "AND FileSet.FileSet IN ($str) ";
1789 if ($elt{mediatypes}) {
1790 my @medias = grep { ! /^\s*$/ } CGI::param('mediatype');
1792 $ret{mediatypes} = \@medias;
1793 my $str = $self->dbh_join(@medias);
1794 $limit .= "AND Media.MediaType IN ($str) ";
1799 my $client = CGI::param('client');
1800 $ret{client} = $client;
1801 $client = $self->dbh_join($client);
1802 $limit .= "AND Client.Name = $client ";
1806 my $level = CGI::param('level') || '';
1807 if ($level =~ /^(\w)$/) {
1809 $limit .= "AND Job.Level = '$1' ";
1814 my $jobid = CGI::param('jobid') || '';
1816 if ($jobid =~ /^(\d+)$/) {
1818 $limit .= "AND Job.JobId = '$1' ";
1823 my $status = CGI::param('status') || '';
1824 if ($status =~ /^(\w)$/) {
1827 $limit .= "AND Job.JobStatus IN ('f','E') ";
1829 $limit .= "AND Job.JobStatus = '$1' ";
1834 if ($elt{volstatus}) {
1835 my $status = CGI::param('volstatus') || '';
1836 if ($status =~ /^(\w)$/) {
1838 $limit .= "AND Media.VolStatus = '$1' ";
1842 if ($elt{locations}) {
1843 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1845 $ret{locations} = \@location;
1846 my $str = $self->dbh_join(@location);
1847 $limit .= "AND Location.Location IN ($str) ";
1852 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
1854 $ret{pools} = \@pool;
1855 my $str = $self->dbh_join(@pool);
1856 $limit .= "AND Pool.Name IN ($str) ";
1860 if ($elt{location}) {
1861 my $location = CGI::param('location') || '';
1863 $ret{location} = $location;
1864 $location = $self->dbh_quote($location);
1865 $limit .= "AND Location.Location = $location ";
1870 my $pool = CGI::param('pool') || '';
1873 $pool = $self->dbh_quote($pool);
1874 $limit .= "AND Pool.Name = $pool ";
1878 if ($elt{jobtype}) {
1879 my $jobtype = CGI::param('jobtype') || '';
1880 if ($jobtype =~ /^(\w)$/) {
1882 $limit .= "AND Job.Type = '$1' ";
1886 return ($limit, %ret);
1897 my ($self, %arg) = @_ ;
1899 $arg{order} = ' Job.JobId DESC ';
1901 my ($limit, $label) = $self->get_limit(%arg);
1902 my ($where, undef) = $self->get_param('clients',
1910 SELECT Job.JobId AS jobid,
1911 Client.Name AS client,
1912 FileSet.FileSet AS fileset,
1913 Job.Name AS jobname,
1915 StartTime AS starttime,
1916 Pool.Name AS poolname,
1917 JobFiles AS jobfiles,
1918 JobBytes AS jobbytes,
1919 JobStatus AS jobstatus,
1920 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1921 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
1924 JobErrors AS joberrors
1927 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
1928 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
1929 WHERE Client.ClientId=Job.ClientId
1930 AND Job.JobStatus != 'R'
1935 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
1937 $self->display({ Filter => $label,
1941 sort { $a->{jobid} <=> $b->{jobid} }
1948 # display job informations
1949 sub display_job_zoom
1951 my ($self, $jobid) = @_ ;
1953 $jobid = $self->dbh_quote($jobid);
1956 SELECT DISTINCT Job.JobId AS jobid,
1957 Client.Name AS client,
1958 Job.Name AS jobname,
1959 FileSet.FileSet AS fileset,
1961 Pool.Name AS poolname,
1962 StartTime AS starttime,
1963 JobFiles AS jobfiles,
1964 JobBytes AS jobbytes,
1965 JobStatus AS jobstatus,
1966 JobErrors AS joberrors,
1967 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1968 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
1971 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
1972 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
1973 WHERE Client.ClientId=Job.ClientId
1974 AND Job.JobId = $jobid
1977 my $row = $self->dbh_selectrow_hashref($query) ;
1979 # display all volumes associate with this job
1981 SELECT Media.VolumeName as volumename
1982 FROM Job,Media,JobMedia
1983 WHERE Job.JobId = $jobid
1984 AND JobMedia.JobId=Job.JobId
1985 AND JobMedia.MediaId=Media.MediaId
1988 my $all = $self->dbh_selectall_hashref($query, 'volumename');
1990 $row->{volumes} = [ values %$all ] ;
1992 $self->display($row, "display_job_zoom.tpl");
1999 my ($where, %elt) = $self->get_param('pools',
2004 my $arg = $self->get_form('jmedias', 'qre_media');
2006 if ($arg->{jmedias}) {
2007 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2009 if ($arg->{qre_media}) {
2010 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2014 SELECT Media.VolumeName AS volumename,
2015 Media.VolBytes AS volbytes,
2016 Media.VolStatus AS volstatus,
2017 Media.MediaType AS mediatype,
2018 Media.InChanger AS online,
2019 Media.LastWritten AS lastwritten,
2020 Location.Location AS location,
2021 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2022 Pool.Name AS poolname,
2023 $self->{sql}->{FROM_UNIXTIME}(
2024 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2025 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2028 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2029 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2030 Media.MediaType AS MediaType
2032 WHERE Media.VolStatus = 'Full'
2033 GROUP BY Media.MediaType
2034 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2036 WHERE Media.PoolId=Pool.PoolId
2040 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2042 $self->display({ ID => $cur_id++,
2044 Location => $elt{location},
2045 Medias => [ values %$all ]
2047 "display_media.tpl");
2054 my $pool = $self->get_form('db_pools');
2056 foreach my $name (@{ $pool->{db_pools} }) {
2057 CGI::param('pool', $name->{name});
2058 $self->display_media();
2062 sub display_media_zoom
2066 my $medias = $self->get_form('jmedias');
2068 unless ($medias->{jmedias}) {
2069 return $self->error("Can't get media selection");
2073 SELECT InChanger AS online,
2074 VolBytes AS nb_bytes,
2075 VolumeName AS volumename,
2076 VolStatus AS volstatus,
2077 VolMounts AS nb_mounts,
2078 Media.VolUseDuration AS voluseduration,
2079 Media.MaxVolJobs AS maxvoljobs,
2080 Media.MaxVolFiles AS maxvolfiles,
2081 Media.MaxVolBytes AS maxvolbytes,
2082 VolErrors AS nb_errors,
2083 Pool.Name AS poolname,
2084 Location.Location AS location,
2085 Media.Recycle AS recycle,
2086 Media.VolRetention AS volretention,
2087 Media.LastWritten AS lastwritten,
2088 Media.VolReadTime/1000000 AS volreadtime,
2089 Media.VolWriteTime/1000000 AS volwritetime,
2090 Media.RecycleCount AS recyclecount,
2091 Media.Comment AS comment,
2092 $self->{sql}->{FROM_UNIXTIME}(
2093 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2094 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2097 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2098 WHERE Pool.PoolId = Media.PoolId
2099 AND VolumeName IN ($medias->{jmedias})
2102 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2104 foreach my $media (values %$all) {
2105 my $mq = $self->dbh_quote($media->{volumename});
2108 SELECT DISTINCT Job.JobId AS jobid,
2110 Job.StartTime AS starttime,
2113 Job.JobFiles AS files,
2114 Job.JobBytes AS bytes,
2115 Job.jobstatus AS status
2116 FROM Media,JobMedia,Job
2117 WHERE Media.VolumeName=$mq
2118 AND Media.MediaId=JobMedia.MediaId
2119 AND JobMedia.JobId=Job.JobId
2122 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2125 SELECT LocationLog.Date AS date,
2126 Location.Location AS location,
2127 LocationLog.Comment AS comment
2128 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2129 WHERE Media.MediaId = LocationLog.MediaId
2130 AND Media.VolumeName = $mq
2134 my $log = $self->dbh_selectall_arrayref($query) ;
2136 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2139 $self->display({ jobs => [ values %$jobs ],
2140 LocationLog => $logtxt,
2142 "display_media_zoom.tpl");
2150 my $loc = $self->get_form('qlocation');
2151 unless ($loc->{qlocation}) {
2152 return $self->error("Can't get location");
2156 SELECT Location.Location AS location,
2157 Location.Cost AS cost,
2158 Location.Enabled AS enabled
2160 WHERE Location.Location = $loc->{qlocation}
2163 my $row = $self->dbh_selectrow_hashref($query);
2165 $self->display({ ID => $cur_id++,
2166 %$row }, "location_edit.tpl") ;
2174 my $arg = $self->get_form(qw/qlocation qnewlocation cost/) ;
2175 unless ($arg->{qlocation}) {
2176 return $self->error("Can't get location");
2178 unless ($arg->{qnewlocation}) {
2179 return $self->error("Can't get new location name");
2181 unless ($arg->{cost}) {
2182 return $self->error("Can't get new cost");
2185 my $enabled = CGI::param('enabled') || '';
2186 $enabled = $enabled?1:0;
2189 UPDATE Location SET Cost = $arg->{cost},
2190 Location = $arg->{qnewlocation},
2192 WHERE Location.Location = $arg->{qlocation}
2195 $self->dbh_do($query);
2197 $self->display_location();
2203 my $arg = $self->get_form(qw/qlocation/) ;
2205 unless ($arg->{qlocation}) {
2206 return $self->error("Can't get location");
2210 SELECT count(Media.MediaId) AS nb
2211 FROM Media INNER JOIN Location USING (LocationID)
2212 WHERE Location = $arg->{qlocation}
2215 my $res = $self->dbh_selectrow_hashref($query);
2218 return $self->error("Sorry, the location must be empty");
2222 DELETE FROM Location WHERE Location = $arg->{qlocation} LIMIT 1
2225 $self->dbh_do($query);
2227 $self->display_location();
2234 my $arg = $self->get_form(qw/qlocation cost/) ;
2236 unless ($arg->{qlocation}) {
2237 $self->display({}, "location_add.tpl");
2240 unless ($arg->{cost}) {
2241 return $self->error("Can't get new cost");
2244 my $enabled = CGI::param('enabled') || '';
2245 $enabled = $enabled?1:0;
2248 INSERT INTO Location (Location, Cost, Enabled)
2249 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2252 $self->dbh_do($query);
2254 $self->display_location();
2257 sub display_location
2262 SELECT Location.Location AS location,
2263 Location.Cost AS cost,
2264 Location.Enabled AS enabled,
2265 (SELECT count(Media.MediaId)
2267 WHERE Media.LocationId = Location.LocationId
2272 my $location = $self->dbh_selectall_hashref($query, 'location');
2274 $self->display({ ID => $cur_id++,
2275 Locations => [ values %$location ] },
2276 "display_location.tpl");
2283 my $medias = $self->get_selected_media_location();
2288 my $arg = $self->get_form('db_locations', 'qnewlocation');
2290 $self->display({ email => $self->{info}->{email_media},
2292 medias => [ values %$medias ],
2294 "update_location.tpl");
2297 sub get_media_max_size
2299 my ($self, $type) = @_;
2301 "SELECT avg(VolBytes) AS size
2303 WHERE Media.VolStatus = 'Full'
2304 AND Media.MediaType = '$type'
2307 my $res = $self->selectrow_hashref($query);
2310 return $res->{size};
2320 my $media = $self->get_form('qmedia');
2322 unless ($media->{qmedia}) {
2323 return $self->error("Can't get media");
2327 SELECT Media.Slot AS slot,
2328 PoolMedia.Name AS poolname,
2329 Media.VolStatus AS volstatus,
2330 Media.InChanger AS inchanger,
2331 Location.Location AS location,
2332 Media.VolumeName AS volumename,
2333 Media.MaxVolBytes AS maxvolbytes,
2334 Media.MaxVolJobs AS maxvoljobs,
2335 Media.MaxVolFiles AS maxvolfiles,
2336 Media.VolUseDuration AS voluseduration,
2337 Media.VolRetention AS volretention,
2338 Media.Comment AS comment,
2339 PoolRecycle.Name AS poolrecycle
2341 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
2342 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
2343 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2345 WHERE Media.VolumeName = $media->{qmedia}
2348 my $row = $self->dbh_selectrow_hashref($query);
2349 $row->{volretention} = human_sec($row->{volretention});
2350 $row->{voluseduration} = human_sec($row->{voluseduration});
2352 my $elt = $self->get_form(qw/db_pools db_locations/);
2357 }, "update_media.tpl");
2364 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
2366 unless ($arg->{jmedias}) {
2367 return $self->error("Can't get selected media");
2370 unless ($arg->{qnewlocation}) {
2371 return $self->error("Can't get new location");
2376 SET LocationId = (SELECT LocationId
2378 WHERE Location = $arg->{qnewlocation})
2379 WHERE Media.VolumeName IN ($arg->{jmedias})
2382 my $nb = $self->dbh_do($query);
2384 print "$nb media updated, you may have to update your autochanger.";
2386 $self->display_media();
2393 my $medias = $self->get_selected_media_location();
2395 return $self->error("Can't get media selection");
2397 my $newloc = CGI::param('newlocation');
2399 my $user = CGI::param('user') || 'unknow';
2400 my $comm = CGI::param('comment') || '';
2401 $comm = $self->dbh_quote("$user: $comm");
2405 foreach my $media (keys %$medias) {
2407 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
2409 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$media'),
2410 (SELECT LocationId FROM Location WHERE Location = '$medias->{$media}->{location}'),
2411 (SELECT VolStatus FROM Media WHERE VolumeName = '$media')
2414 $self->dbh_do($query);
2415 $self->debug($query);
2419 $q->param('action', 'update_location');
2420 my $url = $q->url(-full => 1, -query=>1);
2422 $self->display({ email => $self->{info}->{email_media},
2424 newlocation => $newloc,
2425 # [ { volumename => 'vol1' }, { volumename => 'vol2' },..]
2426 medias => [ values %$medias ],
2428 "change_location.tpl");
2432 sub display_client_stats
2434 my ($self, %arg) = @_ ;
2436 my $client = $self->dbh_quote($arg{clientname});
2437 my ($limit, $label) = $self->get_limit(%arg);
2441 count(Job.JobId) AS nb_jobs,
2442 sum(Job.JobBytes) AS nb_bytes,
2443 sum(Job.JobErrors) AS nb_err,
2444 sum(Job.JobFiles) AS nb_files,
2445 Client.Name AS clientname
2446 FROM Job INNER JOIN Client USING (ClientId)
2448 Client.Name = $client
2450 GROUP BY Client.Name
2453 my $row = $self->dbh_selectrow_hashref($query);
2455 $row->{ID} = $cur_id++;
2456 $row->{label} = $label;
2458 $self->display($row, "display_client_stats.tpl");
2461 # poolname can be undef
2464 my ($self, $poolname) = @_ ;
2468 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
2469 if ($arg->{jmediatypes}) {
2470 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
2471 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
2474 # TODO : afficher les tailles et les dates
2477 SELECT subq.volmax AS volmax,
2478 subq.volnum AS volnum,
2479 subq.voltotal AS voltotal,
2481 Pool.Recycle AS recycle,
2482 Pool.VolRetention AS volretention,
2483 Pool.VolUseDuration AS voluseduration,
2484 Pool.MaxVolJobs AS maxvoljobs,
2485 Pool.MaxVolFiles AS maxvolfiles,
2486 Pool.MaxVolBytes AS maxvolbytes,
2487 subq.PoolId AS PoolId
2490 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
2491 count(Media.MediaId) AS volnum,
2492 sum(Media.VolBytes) AS voltotal,
2493 Media.PoolId AS PoolId,
2494 Media.MediaType AS MediaType
2496 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
2497 Media.MediaType AS MediaType
2499 WHERE Media.VolStatus = 'Full'
2500 GROUP BY Media.MediaType
2501 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2502 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
2504 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
2508 my $all = $self->dbh_selectall_hashref($query, 'name') ;
2511 SELECT Pool.Name AS name,
2512 sum(VolBytes) AS size
2513 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
2514 WHERE Media.VolStatus IN ('Recycled', 'Purged')
2518 my $empty = $self->dbh_selectall_hashref($query, 'name');
2520 foreach my $p (values %$all) {
2521 if ($p->{volmax} > 0) { # mysql returns 0.0000
2522 # we remove Recycled/Purged media from pool usage
2523 if (defined $empty->{$p->{name}}) {
2524 $p->{voltotal} -= $empty->{$p->{name}}->{size};
2526 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
2528 $p->{poolusage} = 0;
2532 SELECT VolStatus AS volstatus, count(MediaId) AS nb
2534 WHERE PoolId=$p->{poolid}
2538 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
2539 foreach my $t (values %$content) {
2540 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
2545 $self->display({ ID => $cur_id++,
2546 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
2547 Pools => [ values %$all ]},
2548 "display_pool.tpl");
2551 sub display_running_job
2555 my $arg = $self->get_form('client', 'jobid');
2557 if (!$arg->{client} and $arg->{jobid}) {
2560 SELECT Client.Name AS name
2561 FROM Job INNER JOIN Client USING (ClientId)
2562 WHERE Job.JobId = $arg->{jobid}
2565 my $row = $self->dbh_selectrow_hashref($query);
2568 $arg->{client} = $row->{name};
2569 CGI::param('client', $arg->{client});
2573 if ($arg->{client}) {
2574 my $cli = new Bweb::Client(name => $arg->{client});
2575 $cli->display_running_job($self->{info}, $arg->{jobid});
2576 if ($arg->{jobid}) {
2577 $self->get_job_log();
2580 $self->error("Can't get client or jobid");
2584 sub display_running_jobs
2586 my ($self, $display_action) = @_;
2589 SELECT Job.JobId AS jobid,
2590 Job.Name AS jobname,
2592 Job.StartTime AS starttime,
2593 Job.JobFiles AS jobfiles,
2594 Job.JobBytes AS jobbytes,
2595 Job.JobStatus AS jobstatus,
2596 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
2597 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2599 Client.Name AS clientname
2600 FROM Job INNER JOIN Client USING (ClientId)
2601 WHERE JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
2603 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2605 $self->display({ ID => $cur_id++,
2606 display_action => $display_action,
2607 Jobs => [ values %$all ]},
2608 "running_job.tpl") ;
2614 my $arg = $self->get_form('jmedias');
2616 unless ($arg->{jmedias}) {
2617 return $self->error("Can't get media selection");
2621 SELECT Media.VolumeName AS volumename,
2622 Storage.Name AS storage,
2623 Location.Location AS location,
2625 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
2626 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2627 WHERE Media.VolumeName IN ($arg->{jmedias})
2628 AND Media.InChanger = 1
2631 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2633 foreach my $vol (values %$all) {
2634 my $a = $self->ach_get($vol->{location});
2637 unless ($a->{have_status}) {
2639 $a->{have_status} = 1;
2642 print "eject $vol->{volumename} from $vol->{storage} : ";
2643 if ($a->send_to_io($vol->{slot})) {
2655 my ($to, $subject, $content) = (CGI::param('email'),
2656 CGI::param('subject'),
2657 CGI::param('content'));
2658 $to =~ s/[^\w\d\.\@<>,]//;
2659 $subject =~ s/[^\w\d\.\[\]]/ /;
2661 open(MAIL, "|mail -s '$subject' '$to'") ;
2662 print MAIL $content;
2672 my $arg = $self->get_form('jobid', 'client');
2674 print CGI::header('text/brestore');
2675 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
2676 print "client=$arg->{client}\n" if ($arg->{client});
2677 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
2681 # TODO : move this to Bweb::Autochanger ?
2682 # TODO : make this internal to not eject tape ?
2688 my ($self, $name) = @_;
2691 return $self->error("Can't get your autochanger name ach");
2694 unless ($self->{info}->{ach_list}) {
2695 return $self->error("Could not find any autochanger");
2698 my $a = $self->{info}->{ach_list}->{$name};
2701 $self->error("Can't get your autochanger $name from your ach_list");
2712 my ($self, $ach) = @_;
2714 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
2716 $self->{info}->save();
2724 my $arg = $self->get_form('ach');
2726 or !$self->{info}->{ach_list}
2727 or !$self->{info}->{ach_list}->{$arg->{ach}})
2729 return $self->error("Can't get autochanger name");
2732 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
2736 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
2738 my $b = $self->get_bconsole();
2740 my @storages = $b->list_storage() ;
2742 $ach->{devices} = [ map { { name => $_ } } @storages ];
2744 $self->display($ach, "ach_add.tpl");
2745 delete $ach->{drives};
2746 delete $ach->{devices};
2753 my $arg = $self->get_form('ach');
2756 or !$self->{info}->{ach_list}
2757 or !$self->{info}->{ach_list}->{$arg->{ach}})
2759 return $self->error("Can't get autochanger name");
2762 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
2764 $self->{info}->save();
2765 $self->{info}->view();
2771 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
2773 my $b = $self->get_bconsole();
2774 my @storages = $b->list_storage() ;
2776 unless ($arg->{ach}) {
2777 $arg->{devices} = [ map { { name => $_ } } @storages ];
2778 return $self->display($arg, "ach_add.tpl");
2782 foreach my $drive (CGI::param('drives'))
2784 unless (grep(/^$drive$/,@storages)) {
2785 return $self->error("Can't find $drive in storage list");
2788 my $index = CGI::param("index_$drive");
2789 unless (defined $index and $index =~ /^(\d+)$/) {
2790 return $self->error("Can't get $drive index");
2793 $drives[$index] = $drive;
2797 return $self->error("Can't get drives from Autochanger");
2800 my $a = new Bweb::Autochanger(name => $arg->{ach},
2801 precmd => $arg->{precmd},
2802 drive_name => \@drives,
2803 device => $arg->{device},
2804 mtxcmd => $arg->{mtxcmd});
2806 $self->ach_register($a) ;
2808 $self->{info}->view();
2814 my $arg = $self->get_form('jobid');
2816 if ($arg->{jobid}) {
2817 my $b = $self->get_bconsole();
2818 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
2822 title => "Delete a job ",
2823 name => "delete jobid=$arg->{jobid}",
2832 my $arg = $self->get_form(qw/media volstatus inchanger pool
2833 slot volretention voluseduration
2834 maxvoljobs maxvolfiles maxvolbytes
2835 qcomment poolrecycle
2838 unless ($arg->{media}) {
2839 return $self->error("Can't find media selection");
2842 my $update = "update volume=$arg->{media} ";
2844 if ($arg->{volstatus}) {
2845 $update .= " volstatus=$arg->{volstatus} ";
2848 if ($arg->{inchanger}) {
2849 $update .= " inchanger=yes " ;
2851 $update .= " slot=$arg->{slot} ";
2854 $update .= " slot=0 inchanger=no ";
2858 $update .= " pool=$arg->{pool} " ;
2861 $arg->{volretention} ||= 0 ;
2862 if ($arg->{volretention}) {
2863 $update .= " volretention=\"$arg->{volretention}\" " ;
2866 $arg->{voluseduration} ||= 0 ;
2867 if ($arg->{voluseduration}) {
2868 $update .= " voluse=\"$arg->{voluseduration}\" " ;
2871 $arg->{maxvoljobs} ||= 0;
2872 if ($arg->{maxvoljobs}) {
2873 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
2876 $arg->{maxvolfiles} ||= 0;
2877 if ($arg->{maxvolfiles}) {
2878 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
2881 $arg->{maxvolbytes} ||= 0;
2882 if ($arg->{maxvolbytes}) {
2883 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
2886 my $b = $self->get_bconsole();
2889 content => $b->send_cmd($update),
2890 title => "Update a volume ",
2896 my $media = $self->dbh_quote($arg->{media});
2898 my $loc = CGI::param('location') || '';
2900 $loc = $self->dbh_quote($loc); # is checked by db
2901 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
2903 if ($arg->{poolrecycle}) {
2904 push @q, "RecyclePoolId=(SELECT PoolId FROM Pool WHERE Name='$arg->{poolrecycle}')";
2906 if (!$arg->{qcomment}) {
2907 $arg->{qcomment} = "''";
2909 push @q, "Comment=$arg->{qcomment}";
2914 SET " . join (',', @q) . "
2915 WHERE Media.VolumeName = $media
2917 $self->dbh_do($query);
2919 $self->update_media();
2926 my $ach = CGI::param('ach') ;
2927 $ach = $self->ach_get($ach);
2929 return $self->error("Bad autochanger name");
2933 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
2934 $b->update_slots($ach->{name});
2942 my $arg = $self->get_form('jobid');
2943 unless ($arg->{jobid}) {
2944 return $self->error("Can't get jobid");
2947 my $t = CGI::param('time') || '';
2950 SELECT Job.Name as name, Client.Name as clientname
2951 FROM Job INNER JOIN Client ON (Job.ClientId = Client.ClientId)
2952 WHERE JobId = $arg->{jobid}
2955 my $row = $self->dbh_selectrow_hashref($query);
2958 return $self->error("Can't find $arg->{jobid} in catalog");
2962 SELECT Time AS time, LogText AS log
2964 WHERE Log.JobId = $arg->{jobid}
2965 OR (Log.JobId = 0 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
2966 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
2971 my $log = $self->dbh_selectall_arrayref($query);
2973 return $self->error("Can't get log for jobid $arg->{jobid}");
2979 $logtxt = join("", map { ($_->[0] . ' ' . $_->[1]) } @$log ) ;
2981 $logtxt = join("", map { $_->[1] } @$log ) ;
2984 $self->display({ lines=> $logtxt,
2985 jobid => $arg->{jobid},
2986 name => $row->{name},
2987 client => $row->{clientname},
2988 }, 'display_log.tpl');
2996 my $arg = $self->get_form('ach', 'slots', 'drive');
2998 unless ($arg->{ach}) {
2999 return $self->error("Can't find autochanger name");
3004 if ($arg->{slots}) {
3005 $slots = join(",", @{ $arg->{slots} });
3006 $t += 60*scalar( @{ $arg->{slots} }) ;
3009 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3010 print "<h1>This command can take long time, be patient...</h1>";
3012 $b->label_barcodes(storage => $arg->{ach},
3013 drive => $arg->{drive},
3024 my @volume = CGI::param('media');
3027 return $self->error("Can't get media selection");
3030 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3033 content => $b->purge_volume(@volume),
3034 title => "Purge media",
3035 name => "purge volume=" . join(' volume=', @volume),
3044 my @volume = CGI::param('media');
3046 return $self->error("Can't get media selection");
3049 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3052 content => $b->prune_volume(@volume),
3053 title => "Prune media",
3054 name => "prune volume=" . join(' volume=', @volume),
3064 my $arg = $self->get_form('jobid');
3065 unless ($arg->{jobid}) {
3066 return $self->error("Can't get jobid");
3069 my $b = $self->get_bconsole();
3071 content => $b->cancel($arg->{jobid}),
3072 title => "Cancel job",
3073 name => "cancel jobid=$arg->{jobid}",
3079 # Warning, we display current fileset
3082 my $arg = $self->get_form('fileset');
3084 if ($arg->{fileset}) {
3085 my $b = $self->get_bconsole();
3086 my $ret = $b->get_fileset($arg->{fileset});
3087 $self->display({ fileset => $arg->{fileset},
3089 }, "fileset_view.tpl");
3091 $self->error("Can't get fileset name");
3095 sub director_show_sched
3099 my $arg = $self->get_form('days');
3101 my $b = $self->get_bconsole();
3102 my $ret = $b->director_get_sched( $arg->{days} );
3107 }, "scheduled_job.tpl");
3110 sub enable_disable_job
3112 my ($self, $what) = @_ ;
3114 my $name = CGI::param('job') || '';
3115 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3116 return $self->error("Can't find job name");
3119 my $b = $self->get_bconsole();
3129 content => $b->send_cmd("$cmd job=\"$name\""),
3130 title => "$cmd $name",
3131 name => "$cmd job=\"$name\"",
3138 return new Bconsole(pref => $self->{info});
3144 my $b = $self->get_bconsole();
3146 my $joblist = [ map { { name => $_ } } $b->list_job() ];
3148 $self->display({ Jobs => $joblist }, "run_job.tpl");
3153 my ($self, $ouput) = @_;
3156 foreach my $l (split(/\r\n/, $ouput)) {
3157 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
3163 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
3169 foreach my $k (keys %arg) {
3170 $lowcase{lc($k)} = $arg{$k} ;
3179 my $b = $self->get_bconsole();
3181 my $job = CGI::param('job') || '';
3183 my $info = $b->send_cmd("show job=\"$job\"");
3184 my $attr = $self->run_parse_job($info);
3186 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3188 my $pools = [ map { { name => $_ } } $b->list_pool() ];
3189 my $clients = [ map { { name => $_ } }$b->list_client()];
3190 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
3191 my $storages= [ map { { name => $_ } }$b->list_storage()];
3196 clients => $clients,
3197 filesets => $filesets,
3198 storages => $storages,
3200 }, "run_job_mod.tpl");
3206 my $b = $self->get_bconsole();
3208 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3218 my $b = $self->get_bconsole();
3220 # TODO: check input (don't use pool, level)
3222 my $arg = $self->get_form('pool', 'level', 'client', 'priority');
3223 my $job = CGI::param('job') || '';
3224 my $storage = CGI::param('storage') || '';
3226 my $jobid = $b->run(job => $job,
3227 client => $arg->{client},
3228 priority => $arg->{priority},
3229 level => $arg->{level},
3230 storage => $storage,
3231 pool => $arg->{pool},
3234 print $jobid, $b->{error};
3236 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";