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 fv_write_path => qr!^([/\w\d\.-]+)$!,
212 template_dir => qr!^([/\w\d\.-]+)$!,
213 debug => qr/^(on)?$/,
214 email_media => qr/^([\w\d\.-]+@[\d\w\.-]+)$/,
215 graph_font => qr!^([/\w\d\.-]+.ttf)$!,
216 bconsole => qr!^(.+)?$!,
217 syslog_file => qr!^(.+)?$!,
218 log_dir => qr!^(.+)?$!,
223 load - load config_file
227 this function load the specified config_file.
235 unless (open(FP, $self->{config_file}))
237 return $self->error("$self->{config_file} : $!");
239 my $f=''; my $tmpbuffer;
240 while(read FP,$tmpbuffer,4096)
248 no strict; # I have no idea of the contents of the file
255 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...") ;
258 foreach my $k (keys %$VAR1) {
259 $self->{$k} = $VAR1->{$k};
267 load_old - load old configuration format
275 unless (open(FP, $self->{config_file}))
277 return $self->error("$self->{config_file} : $!");
280 while (my $line = <FP>)
283 my ($k, $v) = split(/\s*=\s*/, $line, 2);
295 save - save the current configuration to config_file
303 if ($self->{ach_list}) {
304 # shortcut for display_begin
305 $self->{achs} = [ map {{ name => $_ }}
306 keys %{$self->{ach_list}}
310 unless (open(FP, ">$self->{config_file}"))
312 return $self->error("$self->{config_file} : $!\n" .
313 "You must add this to your config file\n"
314 . Data::Dumper::Dumper($self));
317 print FP Data::Dumper::Dumper($self);
325 edit, view, modify - html form ouput
333 $self->display($self, "config_edit.tpl");
339 $self->display($self, "config_view.tpl");
349 foreach my $k (CGI::param())
351 next unless (exists $k_re{$k}) ;
352 my $val = CGI::param($k);
353 if ($val =~ $k_re{$k}) {
356 $self->{error} .= "bad parameter : $k = [$val]";
362 if ($self->{error}) { # an error as occured
363 $self->display($self, 'error.tpl');
371 ################################################################
373 package Bweb::Client;
375 use base q/Bweb::Gui/;
379 Bweb::Client - Bacula FD
383 this package is use to do all Client operations like, parse status etc...
387 $client = new Bweb::Client(name => 'zog-fd');
388 $client->status(); # do a 'status client=zog-fd'
394 display_running_job - Html display of a running job
398 this function is used to display information about a current job
402 sub display_running_job
404 my ($self, $conf, $jobid) = @_ ;
406 my $status = $self->status($conf);
409 if ($status->{$jobid}) {
410 $self->display($status->{$jobid}, "client_job_status.tpl");
413 for my $id (keys %$status) {
414 $self->display($status->{$id}, "client_job_status.tpl");
421 $client = new Bweb::Client(name => 'plume-fd');
423 $client->status($bweb);
427 dirty hack to parse "status client=xxx-fd"
431 JobId 105 Job Full_plume.2006-06-06_17.22.23 is running.
432 Backup Job started: 06-jun-06 17:22
433 Files=8,971 Bytes=194,484,132 Bytes/sec=7,480,158
434 Files Examined=10,697
435 Processing file: /home/eric/.openoffice.org2/user/config/standard.sod
441 JobName => Full_plume.2006-06-06_17.22.23,
444 Bytes => 194,484,132,
454 my ($self, $conf) = @_ ;
456 if (defined $self->{cur_jobs}) {
457 return $self->{cur_jobs} ;
461 my $b = new Bconsole(pref => $conf);
462 my $ret = $b->send_cmd("st client=$self->{name}");
466 for my $r (split(/\n/, $ret)) {
468 $r =~ s/(^\s+|\s+$)//g;
469 if ($r =~ /JobId (\d+) Job (\S+)/) {
471 $arg->{$jobid} = { @param, JobId => $jobid } ;
475 @param = ( JobName => $2 );
477 } elsif ($r =~ /=.+=/) {
478 push @param, split(/\s+|\s*=\s*/, $r) ;
480 } elsif ($r =~ /=/) { # one per line
481 push @param, split(/\s*=\s*/, $r) ;
483 } elsif ($r =~ /:/) { # one per line
484 push @param, split(/\s*:\s*/, $r, 2) ;
488 if ($jobid and @param) {
489 $arg->{$jobid} = { @param,
491 Client => $self->{name},
495 $self->{cur_jobs} = $arg ;
501 ################################################################
503 package Bweb::Autochanger;
505 use base q/Bweb::Gui/;
509 Bweb::Autochanger - Object to manage Autochanger
513 this package will parse the mtx output and manage drives.
517 $auto = new Bweb::Autochanger(precmd => 'sudo');
519 $auto = new Bweb::Autochanger(precmd => 'ssh root@robot');
523 $auto->slot_is_full(10);
524 $auto->transfer(10, 11);
530 my ($class, %arg) = @_;
533 name => '', # autochanger name
534 label => {}, # where are volume { label1 => 40, label2 => drive0 }
535 drive => [], # drive use [ 'media1', 'empty', ..]
536 slot => [], # slot use [ undef, 'empty', 'empty', ..] no slot 0
537 io => [], # io slot number list [ 41, 42, 43...]
538 info => {slot => 0, # informations (slot, drive, io)
542 mtxcmd => '/usr/sbin/mtx',
544 device => '/dev/changer',
545 precmd => '', # ssh command
546 bweb => undef, # link to bacula web object (use for display)
549 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
556 status - parse the output of mtx status
560 this function will launch mtx status and parse the output. it will
561 give a perlish view of the autochanger content.
563 it uses ssh if the autochanger is on a other host.
570 my @out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} status` ;
572 # TODO : reset all infos
573 $self->{info}->{drive} = 0;
574 $self->{info}->{slot} = 0;
575 $self->{info}->{io} = 0;
577 #my @out = `cat /home/eric/travail/brestore/plume/mtx` ;
580 # Storage Changer /dev/changer:2 Drives, 45 Slots ( 5 Import/Export )
581 #Data Transfer Element 0:Full (Storage Element 1 Loaded):VolumeTag = 000000
582 #Data Transfer Element 1:Empty
583 # Storage Element 1:Empty
584 # Storage Element 2:Full :VolumeTag=000002
585 # Storage Element 3:Empty
586 # Storage Element 4:Full :VolumeTag=000004
587 # Storage Element 5:Full :VolumeTag=000001
588 # Storage Element 6:Full :VolumeTag=000003
589 # Storage Element 7:Empty
590 # Storage Element 41 IMPORT/EXPORT:Empty
591 # Storage Element 41 IMPORT/EXPORT:Full :VolumeTag=000002
596 # Storage Element 7:Empty
597 # Storage Element 2:Full :VolumeTag=000002
598 if ($l =~ /Storage Element (\d+):(Empty|Full)(\s+:VolumeTag=([\w\d]+))?/){
601 $self->set_empty_slot($1);
603 $self->set_slot($1, $4);
606 } elsif ($l =~ /Data Transfer.+(\d+):(Full|Empty)(\s+.Storage Element (\d+) Loaded.(:VolumeTag = ([\w\d]+))?)?/) {
609 $self->set_empty_drive($1);
611 $self->set_drive($1, $4, $6);
614 } elsif ($l =~ /Storage Element (\d+).+IMPORT\/EXPORT:(Empty|Full)( :VolumeTag=([\d\w]+))?/)
617 $self->set_empty_io($1);
619 $self->set_io($1, $4);
622 # Storage Changer /dev/changer:2 Drives, 30 Slots ( 1 Import/Export )
624 } elsif ($l =~ /Storage Changer .+:(\d+) Drives, (\d+) Slots/) {
625 $self->{info}->{drive} = $1;
626 $self->{info}->{slot} = $2;
627 if ($l =~ /(\d+)\s+Import/) {
628 $self->{info}->{io} = $1 ;
630 $self->{info}->{io} = 0;
635 $self->debug($self) ;
640 my ($self, $slot) = @_;
643 if ($self->{slot}->[$slot] eq 'loaded') {
647 my $label = $self->{slot}->[$slot] ;
649 return $self->is_media_loaded($label);
654 my ($self, $drive, $slot) = @_;
656 return 0 if (not defined $drive or $self->{drive}->[$drive] eq 'empty') ;
657 return 0 if ($self->slot_is_full($slot)) ;
659 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} unload $slot $drive 2>&1`;
662 my $content = $self->get_slot($slot);
663 print "content = $content<br/> $drive => $slot<br/>";
664 $self->set_empty_drive($drive);
665 $self->set_slot($slot, $content);
668 $self->{error} = $out;
673 # TODO: load/unload have to use mtx script from bacula
676 my ($self, $drive, $slot) = @_;
678 return 0 if (not defined $drive or $self->{drive}->[$drive] ne 'empty') ;
679 return 0 unless ($self->slot_is_full($slot)) ;
681 print "Loading drive $drive with slot $slot<br/>\n";
682 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} load $slot $drive 2>&1`;
685 my $content = $self->get_slot($slot);
686 print "content = $content<br/> $slot => $drive<br/>";
687 $self->set_drive($drive, $slot, $content);
690 $self->{error} = $out;
698 my ($self, $media) = @_;
700 unless ($self->{label}->{$media}) {
704 if ($self->{label}->{$media} =~ /drive\d+/) {
714 return (defined $self->{info}->{io} and $self->{info}->{io} > 0);
719 my ($self, $slot, $tag) = @_;
720 $self->{slot}->[$slot] = $tag || 'full';
721 push @{ $self->{io} }, $slot;
724 $self->{label}->{$tag} = $slot;
730 my ($self, $slot) = @_;
732 push @{ $self->{io} }, $slot;
734 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
735 $self->{slot}->[$slot] = 'empty';
741 my ($self, $slot) = @_;
742 return $self->{slot}->[$slot];
747 my ($self, $slot, $tag) = @_;
748 $self->{slot}->[$slot] = $tag || 'full';
751 $self->{label}->{$tag} = $slot;
757 my ($self, $slot) = @_;
759 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
760 $self->{slot}->[$slot] = 'empty';
766 my ($self, $drive) = @_;
767 $self->{drive}->[$drive] = 'empty';
772 my ($self, $drive, $slot, $tag) = @_;
773 $self->{drive}->[$drive] = $tag || $slot;
775 $self->{slot}->[$slot] = $tag || 'loaded';
778 $self->{label}->{$tag} = "drive$drive";
784 my ($self, $slot) = @_;
786 # slot don't exists => full
787 if (not defined $self->{slot}->[$slot]) {
791 if ($self->{slot}->[$slot] eq 'empty') {
794 return 1; # vol, full, loaded
797 sub slot_get_first_free
800 for (my $slot=1; $slot < $self->{info}->{slot}; $slot++) {
801 return $slot unless ($self->slot_is_full($slot));
805 sub io_get_first_free
809 foreach my $slot (@{ $self->{io} }) {
810 return $slot unless ($self->slot_is_full($slot));
817 my ($self, $media) = @_;
819 return $self->{label}->{$media} ;
824 my ($self, $media) = @_;
826 return defined $self->{label}->{$media} ;
831 my ($self, $slot) = @_;
833 unless ($self->slot_is_full($slot)) {
834 print "Autochanger $self->{name} slot $slot is empty\n";
839 if ($self->is_slot_loaded($slot)) {
842 print "Autochanger $self->{name} $slot is currently in use\n";
846 # autochanger must have I/O
847 unless ($self->have_io()) {
848 print "Autochanger $self->{name} don't have I/O, you can take media yourself\n";
852 my $dst = $self->io_get_first_free();
855 print "Autochanger $self->{name} you must empty I/O first\n";
858 $self->transfer($slot, $dst);
863 my ($self, $src, $dst) = @_ ;
864 print "<pre>$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst</pre>\n";
865 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst 2>&1`;
868 my $content = $self->get_slot($src);
869 print "$content ($src) => $dst<br/>";
870 $self->{slot}->[$src] = 'empty';
871 $self->set_slot($dst, $content);
874 $self->{error} = $out;
881 my ($self, $index) = @_;
882 return $self->{drive_name}->[$index];
885 # TODO : do a tapeinfo request to get informations
895 for my $slot (@{$self->{io}})
897 if ($self->is_slot_loaded($slot)) {
898 print "$slot is currently loaded\n";
902 if ($self->slot_is_full($slot))
904 my $free = $self->slot_get_first_free() ;
905 print "want to move $slot to $free\n";
908 $self->transfer($slot, $free) || print "$self->{error}\n";
911 $self->{error} = "E : Can't find free slot";
917 # TODO : this is with mtx status output,
918 # we can do an other function from bacula view (with StorageId)
922 my $bweb = $self->{bweb};
924 # $self->{label} => ('vol1', 'vol2', 'vol3', ..);
925 my $media_list = $bweb->dbh_join( keys %{ $self->{label} });
928 SELECT Media.VolumeName AS volumename,
929 Media.VolStatus AS volstatus,
930 Media.LastWritten AS lastwritten,
931 Media.VolBytes AS volbytes,
932 Media.MediaType AS mediatype,
934 Media.InChanger AS inchanger,
936 $bweb->{sql}->{FROM_UNIXTIME}(
937 $bweb->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
938 + $bweb->{sql}->{TO_SEC}(Media.VolRetention)
941 INNER JOIN Pool USING (PoolId)
943 WHERE Media.VolumeName IN ($media_list)
946 my $all = $bweb->dbh_selectall_hashref($query, 'volumename') ;
948 # TODO : verify slot and bacula slot
952 for (my $slot=1; $slot <= $self->{info}->{slot} ; $slot++) {
954 if ($self->slot_is_full($slot)) {
956 my $vol = $self->{slot}->[$slot];
957 if (defined $all->{$vol}) { # TODO : autochanger without barcodes
959 my $bslot = $all->{$vol}->{slot} ;
960 my $inchanger = $all->{$vol}->{inchanger};
962 # if bacula slot or inchanger flag is bad, we display a message
963 if ($bslot != $slot or !$inchanger) {
964 push @to_update, $slot;
967 $all->{$vol}->{realslot} = $slot;
969 push @{ $param }, $all->{$vol};
971 } else { # empty or no label
972 push @{ $param }, {realslot => $slot,
973 volstatus => 'Unknow',
974 volumename => $self->{slot}->[$slot]} ;
977 push @{ $param }, {realslot => $slot, volumename => 'empty'} ;
981 my $i=0; my $drives = [] ;
982 foreach my $d (@{ $self->{drive} }) {
983 $drives->[$i] = { index => $i,
984 load => $self->{drive}->[$i],
985 name => $self->{drive_name}->[$i],
990 $bweb->display({ Name => $self->{name},
991 nb_drive => $self->{info}->{drive},
992 nb_io => $self->{info}->{io},
995 Update => scalar(@to_update) },
1003 ################################################################
1007 use base q/Bweb::Gui/;
1011 Bweb - main Bweb package
1015 this package is use to compute and display informations
1020 use POSIX qw/strftime/;
1026 %sql_func - hash to make query mysql/postgresql compliant
1032 UNIX_TIMESTAMP => '',
1033 FROM_UNIXTIME => '',
1034 TO_SEC => " interval '1 second' * ",
1035 SEC_TO_INT => "SEC_TO_INT",
1038 STARTTIME_DAY => " date_trunc('day', Job.StartTime) ",
1039 STARTTIME_HOUR => " date_trunc('hour', Job.StartTime) ",
1040 STARTTIME_MONTH => " date_trunc('month', Job.StartTime) ",
1041 STARTTIME_PHOUR=> " date_part('hour', Job.StartTime) ",
1042 STARTTIME_PDAY => " date_part('day', Job.StartTime) ",
1043 STARTTIME_PMONTH => " date_part('month', Job.StartTime) ",
1046 UNIX_TIMESTAMP => 'UNIX_TIMESTAMP',
1047 FROM_UNIXTIME => 'FROM_UNIXTIME',
1050 SEC_TO_TIME => 'SEC_TO_TIME',
1051 MATCH => " REGEXP ",
1052 STARTTIME_DAY => " DATE_FORMAT(StartTime, '%Y-%m-%d') ",
1053 STARTTIME_HOUR => " DATE_FORMAT(StartTime, '%Y-%m-%d %H') ",
1054 STARTTIME_MONTH => " DATE_FORMAT(StartTime, '%Y-%m') ",
1055 STARTTIME_PHOUR=> " DATE_FORMAT(StartTime, '%H') ",
1056 STARTTIME_PDAY => " DATE_FORMAT(StartTime, '%d') ",
1057 STARTTIME_PMONTH => " DATE_FORMAT(StartTime, '%m') ",
1061 sub dbh_selectall_arrayref
1063 my ($self, $query) = @_;
1064 $self->connect_db();
1065 $self->debug($query);
1066 return $self->{dbh}->selectall_arrayref($query);
1071 my ($self, @what) = @_;
1072 return join(',', $self->dbh_quote(@what)) ;
1077 my ($self, @what) = @_;
1079 $self->connect_db();
1081 return map { $self->{dbh}->quote($_) } @what;
1083 return $self->{dbh}->quote($what[0]) ;
1089 my ($self, $query) = @_ ;
1090 $self->connect_db();
1091 $self->debug($query);
1092 return $self->{dbh}->do($query);
1095 sub dbh_selectall_hashref
1097 my ($self, $query, $join) = @_;
1099 $self->connect_db();
1100 $self->debug($query);
1101 return $self->{dbh}->selectall_hashref($query, $join) ;
1104 sub dbh_selectrow_hashref
1106 my ($self, $query) = @_;
1108 $self->connect_db();
1109 $self->debug($query);
1110 return $self->{dbh}->selectrow_hashref($query) ;
1116 my @unit = qw(b Kb Mb Gb Tb);
1117 my $val = shift || 0;
1119 my $format = '%i %s';
1120 while ($val / 1024 > 1) {
1124 $format = ($i>0)?'%0.1f %s':'%i %s';
1125 return sprintf($format, $val, $unit[$i]);
1128 # display Day, Hour, Year
1134 $val /= 60; # sec -> min
1136 if ($val / 60 <= 1) {
1140 $val /= 60; # min -> hour
1141 if ($val / 24 <= 1) {
1142 return "$val hours";
1145 $val /= 24; # hour -> day
1146 if ($val / 365 < 2) {
1150 $val /= 365 ; # day -> year
1152 return "$val years";
1155 # get Day, Hour, Year
1161 unless ($val =~ /^\s*(\d+)\s*(\w)\w*\s*$/) {
1165 my %times = ( m => 60,
1171 my $mult = $times{$2} || 0;
1181 unless ($self->{dbh}) {
1182 $self->{dbh} = DBI->connect($self->{info}->{dbi},
1183 $self->{info}->{user},
1184 $self->{info}->{password});
1186 print "Can't connect to your database, see error log\n"
1187 unless ($self->{dbh});
1189 $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
1191 if ($self->{info}->{dbi} =~ /^dbi:Pg/i) {
1192 $self->{dbh}->do("SET datestyle TO 'ISO, YMD'");
1199 my ($class, %arg) = @_;
1201 dbh => undef, # connect_db();
1203 dbi => '', # DBI:Pg:database=bacula;host=127.0.0.1
1209 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
1211 if ($self->{info}->{dbi} =~ /DBI:(\w+):/i) {
1212 $self->{sql} = $sql_func{$1};
1215 $self->{debug} = $self->{info}->{debug};
1216 $Bweb::Gui::template_dir = $self->{info}->{template_dir};
1224 $self->display($self->{info}, "begin.tpl");
1230 $self->display($self->{info}, "end.tpl");
1238 my $arg = $self->get_form("client", "qre_client");
1240 if ($arg->{qre_client}) {
1241 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1242 } elsif ($arg->{client}) {
1243 $where = "WHERE Name = '$arg->{client}' ";
1247 SELECT Name AS name,
1249 AutoPrune AS autoprune,
1250 FileRetention AS fileretention,
1251 JobRetention AS jobretention
1256 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1258 my $dsp = { ID => $cur_id++,
1259 clients => [ values %$all] };
1261 $self->display($dsp, "client_list.tpl") ;
1266 my ($self, %arg) = @_;
1273 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1275 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1277 $self->{sql}->{TO_SEC}($arg{age})
1280 $label = "last " . human_sec($arg{age});
1283 if ($arg{groupby}) {
1284 $limit .= " GROUP BY $arg{groupby} ";
1288 $limit .= " ORDER BY $arg{order} ";
1292 $limit .= " LIMIT $arg{limit} ";
1293 $label .= " limited to $arg{limit}";
1297 $limit .= " OFFSET $arg{offset} ";
1298 $label .= " with $arg{offset} offset ";
1302 $label = 'no filter';
1305 return ($limit, $label);
1310 $bweb->get_form(...) - Get useful stuff
1314 This function get and check parameters against regexp.
1316 If word begin with 'q', the return will be quoted or join quoted
1317 if it's end with 's'.
1322 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1325 qclient => 'plume-fd',
1326 qpools => "'plume-fd', 'test-fd', '...'",
1333 my ($self, @what) = @_;
1334 my %what = map { $_ => 1 } @what;
1354 my %opt_ss =( # string with space
1358 my %opt_s = ( # default to ''
1375 my %opt_p = ( # option with path
1383 my %opt_d = ( # option with date
1388 foreach my $i (@what) {
1389 if (exists $opt_i{$i}) {# integer param
1390 my $value = CGI::param($i) || $opt_i{$i} ;
1391 if ($value =~ /^(\d+)$/) {
1394 } elsif ($opt_s{$i}) { # simple string param
1395 my $value = CGI::param($i) || '';
1396 if ($value =~ /^([\w\d\.-]+)$/) {
1399 } elsif ($opt_ss{$i}) { # simple string param (with space)
1400 my $value = CGI::param($i) || '';
1401 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1404 } elsif ($i =~ /^j(\w+)s$/) { # quote join args
1405 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1407 $ret{$i} = $self->dbh_join(@value) ;
1410 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1411 my $value = CGI::param($1) ;
1413 $ret{$i} = $self->dbh_quote($value);
1416 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1417 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1418 grep { ! /^\s*$/ } CGI::param($1) ];
1419 } elsif (exists $opt_p{$i}) {
1420 my $value = CGI::param($i) || '';
1421 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1424 } elsif (exists $opt_d{$i}) {
1425 my $value = CGI::param($i) || '';
1426 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1433 foreach my $s (CGI::param('slot')) {
1434 if ($s =~ /^(\d+)$/) {
1435 push @{$ret{slots}}, $s;
1441 my $when = CGI::param('when') || '';
1442 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1447 if ($what{db_clients}) {
1449 SELECT Client.Name as clientname
1453 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1454 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1458 if ($what{db_mediatypes}) {
1460 SELECT MediaType as mediatype
1464 my $medias = $self->dbh_selectall_hashref($query, 'mediatype');
1465 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1469 if ($what{db_locations}) {
1471 SELECT Location as location, Cost as cost FROM Location
1473 my $loc = $self->dbh_selectall_hashref($query, 'location');
1474 $ret{db_locations} = [ sort { $a->{location}
1480 if ($what{db_pools}) {
1481 my $query = "SELECT Name as name FROM Pool";
1483 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1484 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1487 if ($what{db_filesets}) {
1489 SELECT FileSet.FileSet AS fileset
1493 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1495 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1496 values %$filesets] ;
1499 if ($what{db_jobnames}) {
1501 SELECT DISTINCT Job.Name AS jobname
1505 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1507 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1508 values %$jobnames] ;
1511 if ($what{db_devices}) {
1513 SELECT Device.Name AS name
1517 my $devices = $self->dbh_selectall_hashref($query, 'name');
1519 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1530 my $fields = $self->get_form(qw/age level status clients filesets
1532 db_clients limit db_filesets width height
1533 qclients qfilesets qjobnames db_jobnames/);
1536 my $url = CGI::url(-full => 0,
1539 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1541 # this organisation is to keep user choice between 2 click
1542 # TODO : fileset and client selection doesn't work
1551 sub display_client_job
1553 my ($self, %arg) = @_ ;
1555 $arg{order} = ' Job.JobId DESC ';
1556 my ($limit, $label) = $self->get_limit(%arg);
1558 my $clientname = $self->dbh_quote($arg{clientname});
1561 SELECT DISTINCT Job.JobId AS jobid,
1562 Job.Name AS jobname,
1563 FileSet.FileSet AS fileset,
1565 StartTime AS starttime,
1566 JobFiles AS jobfiles,
1567 JobBytes AS jobbytes,
1568 JobStatus AS jobstatus,
1569 JobErrors AS joberrors
1571 FROM Client,Job,FileSet
1572 WHERE Client.Name=$clientname
1573 AND Client.ClientId=Job.ClientId
1574 AND Job.FileSetId=FileSet.FileSetId
1578 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
1580 $self->display({ clientname => $arg{clientname},
1583 Jobs => [ values %$all ],
1585 "display_client_job.tpl") ;
1588 sub get_selected_media_location
1592 my $medias = $self->get_form('jmedias');
1594 unless ($medias->{jmedias}) {
1599 SELECT Media.VolumeName AS volumename, Location.Location AS location
1600 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1601 WHERE Media.VolumeName IN ($medias->{jmedias})
1604 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1606 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1617 my $medias = $self->get_selected_media_location();
1623 my $elt = $self->get_form('db_locations');
1625 $self->display({ ID => $cur_id++,
1626 %$elt, # db_locations
1628 sort { $a->{volumename} cmp $b->{volumename} } values %$medias
1638 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1640 $self->display($elt, "help_extern.tpl");
1643 sub help_extern_compute
1647 my $number = CGI::param('limit') || '' ;
1648 unless ($number =~ /^(\d+)$/) {
1649 return $self->error("Bad arg number : $number ");
1652 my ($sql, undef) = $self->get_param('pools',
1653 'locations', 'mediatypes');
1656 SELECT Media.VolumeName AS volumename,
1657 Media.VolStatus AS volstatus,
1658 Media.LastWritten AS lastwritten,
1659 Media.MediaType AS mediatype,
1660 Media.VolMounts AS volmounts,
1662 Media.Recycle AS recycle,
1663 $self->{sql}->{FROM_UNIXTIME}(
1664 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1665 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1668 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1669 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1671 WHERE Media.InChanger = 1
1672 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1674 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1678 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1680 $self->display({ Medias => [ values %$all ] },
1681 "help_extern_compute.tpl");
1688 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1689 $self->display($param, "help_intern.tpl");
1692 sub help_intern_compute
1696 my $number = CGI::param('limit') || '' ;
1697 unless ($number =~ /^(\d+)$/) {
1698 return $self->error("Bad arg number : $number ");
1701 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1703 if (CGI::param('expired')) {
1705 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1706 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1712 SELECT Media.VolumeName AS volumename,
1713 Media.VolStatus AS volstatus,
1714 Media.LastWritten AS lastwritten,
1715 Media.MediaType AS mediatype,
1716 Media.VolMounts AS volmounts,
1718 $self->{sql}->{FROM_UNIXTIME}(
1719 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1720 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1723 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1724 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1726 WHERE Media.InChanger <> 1
1727 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1728 AND Media.Recycle = 1
1730 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1734 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1736 $self->display({ Medias => [ values %$all ] },
1737 "help_intern_compute.tpl");
1743 my ($self, %arg) = @_ ;
1745 my ($limit, $label) = $self->get_limit(%arg);
1749 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1750 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1751 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1752 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1753 (SELECT count(Job.JobId)
1755 WHERE Job.JobStatus IN ('E','e','f','A')
1758 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1761 my $row = $self->dbh_selectrow_hashref($query) ;
1763 $row->{nb_bytes} = human_size($row->{nb_bytes});
1765 $row->{db_size} = '???';
1766 $row->{label} = $label;
1768 $self->display($row, "general.tpl");
1773 my ($self, @what) = @_ ;
1774 my %elt = map { $_ => 1 } @what;
1779 if ($elt{clients}) {
1780 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1782 $ret{clients} = \@clients;
1783 my $str = $self->dbh_join(@clients);
1784 $limit .= "AND Client.Name IN ($str) ";
1788 if ($elt{filesets}) {
1789 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1791 $ret{filesets} = \@filesets;
1792 my $str = $self->dbh_join(@filesets);
1793 $limit .= "AND FileSet.FileSet IN ($str) ";
1797 if ($elt{mediatypes}) {
1798 my @medias = grep { ! /^\s*$/ } CGI::param('mediatype');
1800 $ret{mediatypes} = \@medias;
1801 my $str = $self->dbh_join(@medias);
1802 $limit .= "AND Media.MediaType IN ($str) ";
1807 my $client = CGI::param('client');
1808 $ret{client} = $client;
1809 $client = $self->dbh_join($client);
1810 $limit .= "AND Client.Name = $client ";
1814 my $level = CGI::param('level') || '';
1815 if ($level =~ /^(\w)$/) {
1817 $limit .= "AND Job.Level = '$1' ";
1822 my $jobid = CGI::param('jobid') || '';
1824 if ($jobid =~ /^(\d+)$/) {
1826 $limit .= "AND Job.JobId = '$1' ";
1831 my $status = CGI::param('status') || '';
1832 if ($status =~ /^(\w)$/) {
1835 $limit .= "AND Job.JobStatus IN ('f','E') ";
1837 $limit .= "AND Job.JobStatus = '$1' ";
1842 if ($elt{volstatus}) {
1843 my $status = CGI::param('volstatus') || '';
1844 if ($status =~ /^(\w+)$/) {
1846 $limit .= "AND Media.VolStatus = '$1' ";
1850 if ($elt{locations}) {
1851 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1853 $ret{locations} = \@location;
1854 my $str = $self->dbh_join(@location);
1855 $limit .= "AND Location.Location IN ($str) ";
1860 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
1862 $ret{pools} = \@pool;
1863 my $str = $self->dbh_join(@pool);
1864 $limit .= "AND Pool.Name IN ($str) ";
1868 if ($elt{location}) {
1869 my $location = CGI::param('location') || '';
1871 $ret{location} = $location;
1872 $location = $self->dbh_quote($location);
1873 $limit .= "AND Location.Location = $location ";
1878 my $pool = CGI::param('pool') || '';
1881 $pool = $self->dbh_quote($pool);
1882 $limit .= "AND Pool.Name = $pool ";
1886 if ($elt{jobtype}) {
1887 my $jobtype = CGI::param('jobtype') || '';
1888 if ($jobtype =~ /^(\w)$/) {
1890 $limit .= "AND Job.Type = '$1' ";
1894 return ($limit, %ret);
1905 my ($self, %arg) = @_ ;
1907 $arg{order} = ' Job.JobId DESC ';
1909 my ($limit, $label) = $self->get_limit(%arg);
1910 my ($where, undef) = $self->get_param('clients',
1919 SELECT Job.JobId AS jobid,
1920 Client.Name AS client,
1921 FileSet.FileSet AS fileset,
1922 Job.Name AS jobname,
1924 StartTime AS starttime,
1925 Pool.Name AS poolname,
1926 JobFiles AS jobfiles,
1927 JobBytes AS jobbytes,
1928 JobStatus AS jobstatus,
1929 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1930 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
1933 JobErrors AS joberrors
1936 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
1937 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
1938 WHERE Client.ClientId=Job.ClientId
1939 AND Job.JobStatus != 'R'
1944 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
1946 $self->display({ Filter => $label,
1950 sort { $a->{jobid} <=> $b->{jobid} }
1957 # display job informations
1958 sub display_job_zoom
1960 my ($self, $jobid) = @_ ;
1962 $jobid = $self->dbh_quote($jobid);
1965 SELECT DISTINCT Job.JobId AS jobid,
1966 Client.Name AS client,
1967 Job.Name AS jobname,
1968 FileSet.FileSet AS fileset,
1970 Pool.Name AS poolname,
1971 StartTime AS starttime,
1972 JobFiles AS jobfiles,
1973 JobBytes AS jobbytes,
1974 JobStatus AS jobstatus,
1975 JobErrors AS joberrors,
1976 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1977 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
1980 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
1981 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
1982 WHERE Client.ClientId=Job.ClientId
1983 AND Job.JobId = $jobid
1986 my $row = $self->dbh_selectrow_hashref($query) ;
1988 # display all volumes associate with this job
1990 SELECT Media.VolumeName as volumename
1991 FROM Job,Media,JobMedia
1992 WHERE Job.JobId = $jobid
1993 AND JobMedia.JobId=Job.JobId
1994 AND JobMedia.MediaId=Media.MediaId
1997 my $all = $self->dbh_selectall_hashref($query, 'volumename');
1999 $row->{volumes} = [ values %$all ] ;
2001 $self->display($row, "display_job_zoom.tpl");
2008 my ($where, %elt) = $self->get_param('pools',
2013 my $arg = $self->get_form('jmedias', 'qre_media');
2015 if ($arg->{jmedias}) {
2016 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2018 if ($arg->{qre_media}) {
2019 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2023 SELECT Media.VolumeName AS volumename,
2024 Media.VolBytes AS volbytes,
2025 Media.VolStatus AS volstatus,
2026 Media.MediaType AS mediatype,
2027 Media.InChanger AS online,
2028 Media.LastWritten AS lastwritten,
2029 Location.Location AS location,
2030 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2031 Pool.Name AS poolname,
2032 $self->{sql}->{FROM_UNIXTIME}(
2033 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2034 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2037 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2038 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2039 Media.MediaType AS MediaType
2041 WHERE Media.VolStatus = 'Full'
2042 GROUP BY Media.MediaType
2043 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2045 WHERE Media.PoolId=Pool.PoolId
2049 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2051 $self->display({ ID => $cur_id++,
2053 Location => $elt{location},
2054 Medias => [ values %$all ]
2056 "display_media.tpl");
2063 my $pool = $self->get_form('db_pools');
2065 foreach my $name (@{ $pool->{db_pools} }) {
2066 CGI::param('pool', $name->{name});
2067 $self->display_media();
2071 sub display_media_zoom
2075 my $medias = $self->get_form('jmedias');
2077 unless ($medias->{jmedias}) {
2078 return $self->error("Can't get media selection");
2082 SELECT InChanger AS online,
2083 VolBytes AS nb_bytes,
2084 VolumeName AS volumename,
2085 VolStatus AS volstatus,
2086 VolMounts AS nb_mounts,
2087 Media.VolUseDuration AS voluseduration,
2088 Media.MaxVolJobs AS maxvoljobs,
2089 Media.MaxVolFiles AS maxvolfiles,
2090 Media.MaxVolBytes AS maxvolbytes,
2091 VolErrors AS nb_errors,
2092 Pool.Name AS poolname,
2093 Location.Location AS location,
2094 Media.Recycle AS recycle,
2095 Media.VolRetention AS volretention,
2096 Media.LastWritten AS lastwritten,
2097 Media.VolReadTime/1000000 AS volreadtime,
2098 Media.VolWriteTime/1000000 AS volwritetime,
2099 Media.RecycleCount AS recyclecount,
2100 Media.Comment AS comment,
2101 $self->{sql}->{FROM_UNIXTIME}(
2102 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2103 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2106 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2107 WHERE Pool.PoolId = Media.PoolId
2108 AND VolumeName IN ($medias->{jmedias})
2111 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2113 foreach my $media (values %$all) {
2114 my $mq = $self->dbh_quote($media->{volumename});
2117 SELECT DISTINCT Job.JobId AS jobid,
2119 Job.StartTime AS starttime,
2122 Job.JobFiles AS files,
2123 Job.JobBytes AS bytes,
2124 Job.jobstatus AS status
2125 FROM Media,JobMedia,Job
2126 WHERE Media.VolumeName=$mq
2127 AND Media.MediaId=JobMedia.MediaId
2128 AND JobMedia.JobId=Job.JobId
2131 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2134 SELECT LocationLog.Date AS date,
2135 Location.Location AS location,
2136 LocationLog.Comment AS comment
2137 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2138 WHERE Media.MediaId = LocationLog.MediaId
2139 AND Media.VolumeName = $mq
2143 my $log = $self->dbh_selectall_arrayref($query) ;
2145 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2148 $self->display({ jobs => [ values %$jobs ],
2149 LocationLog => $logtxt,
2151 "display_media_zoom.tpl");
2159 my $loc = $self->get_form('qlocation');
2160 unless ($loc->{qlocation}) {
2161 return $self->error("Can't get location");
2165 SELECT Location.Location AS location,
2166 Location.Cost AS cost,
2167 Location.Enabled AS enabled
2169 WHERE Location.Location = $loc->{qlocation}
2172 my $row = $self->dbh_selectrow_hashref($query);
2174 $self->display({ ID => $cur_id++,
2175 %$row }, "location_edit.tpl") ;
2183 my $arg = $self->get_form(qw/qlocation qnewlocation cost/) ;
2184 unless ($arg->{qlocation}) {
2185 return $self->error("Can't get location");
2187 unless ($arg->{qnewlocation}) {
2188 return $self->error("Can't get new location name");
2190 unless ($arg->{cost}) {
2191 return $self->error("Can't get new cost");
2194 my $enabled = CGI::param('enabled') || '';
2195 $enabled = $enabled?1:0;
2198 UPDATE Location SET Cost = $arg->{cost},
2199 Location = $arg->{qnewlocation},
2201 WHERE Location.Location = $arg->{qlocation}
2204 $self->dbh_do($query);
2206 $self->display_location();
2212 my $arg = $self->get_form(qw/qlocation/) ;
2214 unless ($arg->{qlocation}) {
2215 return $self->error("Can't get location");
2219 SELECT count(Media.MediaId) AS nb
2220 FROM Media INNER JOIN Location USING (LocationID)
2221 WHERE Location = $arg->{qlocation}
2224 my $res = $self->dbh_selectrow_hashref($query);
2227 return $self->error("Sorry, the location must be empty");
2231 DELETE FROM Location WHERE Location = $arg->{qlocation} LIMIT 1
2234 $self->dbh_do($query);
2236 $self->display_location();
2243 my $arg = $self->get_form(qw/qlocation cost/) ;
2245 unless ($arg->{qlocation}) {
2246 $self->display({}, "location_add.tpl");
2249 unless ($arg->{cost}) {
2250 return $self->error("Can't get new cost");
2253 my $enabled = CGI::param('enabled') || '';
2254 $enabled = $enabled?1:0;
2257 INSERT INTO Location (Location, Cost, Enabled)
2258 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2261 $self->dbh_do($query);
2263 $self->display_location();
2266 sub display_location
2271 SELECT Location.Location AS location,
2272 Location.Cost AS cost,
2273 Location.Enabled AS enabled,
2274 (SELECT count(Media.MediaId)
2276 WHERE Media.LocationId = Location.LocationId
2281 my $location = $self->dbh_selectall_hashref($query, 'location');
2283 $self->display({ ID => $cur_id++,
2284 Locations => [ values %$location ] },
2285 "display_location.tpl");
2292 my $medias = $self->get_selected_media_location();
2297 my $arg = $self->get_form('db_locations', 'qnewlocation');
2299 $self->display({ email => $self->{info}->{email_media},
2301 medias => [ values %$medias ],
2303 "update_location.tpl");
2306 sub get_media_max_size
2308 my ($self, $type) = @_;
2310 "SELECT avg(VolBytes) AS size
2312 WHERE Media.VolStatus = 'Full'
2313 AND Media.MediaType = '$type'
2316 my $res = $self->selectrow_hashref($query);
2319 return $res->{size};
2329 my $media = $self->get_form('qmedia');
2331 unless ($media->{qmedia}) {
2332 return $self->error("Can't get media");
2336 SELECT Media.Slot AS slot,
2337 PoolMedia.Name AS poolname,
2338 Media.VolStatus AS volstatus,
2339 Media.InChanger AS inchanger,
2340 Location.Location AS location,
2341 Media.VolumeName AS volumename,
2342 Media.MaxVolBytes AS maxvolbytes,
2343 Media.MaxVolJobs AS maxvoljobs,
2344 Media.MaxVolFiles AS maxvolfiles,
2345 Media.VolUseDuration AS voluseduration,
2346 Media.VolRetention AS volretention,
2347 Media.Comment AS comment,
2348 PoolRecycle.Name AS poolrecycle
2350 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
2351 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
2352 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2354 WHERE Media.VolumeName = $media->{qmedia}
2357 my $row = $self->dbh_selectrow_hashref($query);
2358 $row->{volretention} = human_sec($row->{volretention});
2359 $row->{voluseduration} = human_sec($row->{voluseduration});
2361 my $elt = $self->get_form(qw/db_pools db_locations/);
2366 }, "update_media.tpl");
2373 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
2375 unless ($arg->{jmedias}) {
2376 return $self->error("Can't get selected media");
2379 unless ($arg->{qnewlocation}) {
2380 return $self->error("Can't get new location");
2385 SET LocationId = (SELECT LocationId
2387 WHERE Location = $arg->{qnewlocation})
2388 WHERE Media.VolumeName IN ($arg->{jmedias})
2391 my $nb = $self->dbh_do($query);
2393 print "$nb media updated, you may have to update your autochanger.";
2395 $self->display_media();
2402 my $medias = $self->get_selected_media_location();
2404 return $self->error("Can't get media selection");
2406 my $newloc = CGI::param('newlocation');
2408 my $user = CGI::param('user') || 'unknow';
2409 my $comm = CGI::param('comment') || '';
2410 $comm = $self->dbh_quote("$user: $comm");
2414 foreach my $media (keys %$medias) {
2416 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
2418 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$media'),
2419 (SELECT LocationId FROM Location WHERE Location = '$medias->{$media}->{location}'),
2420 (SELECT VolStatus FROM Media WHERE VolumeName = '$media')
2423 $self->dbh_do($query);
2424 $self->debug($query);
2428 $q->param('action', 'update_location');
2429 my $url = $q->url(-full => 1, -query=>1);
2431 $self->display({ email => $self->{info}->{email_media},
2433 newlocation => $newloc,
2434 # [ { volumename => 'vol1' }, { volumename => 'vol2' },..]
2435 medias => [ values %$medias ],
2437 "change_location.tpl");
2441 sub display_client_stats
2443 my ($self, %arg) = @_ ;
2445 my $client = $self->dbh_quote($arg{clientname});
2446 my ($limit, $label) = $self->get_limit(%arg);
2450 count(Job.JobId) AS nb_jobs,
2451 sum(Job.JobBytes) AS nb_bytes,
2452 sum(Job.JobErrors) AS nb_err,
2453 sum(Job.JobFiles) AS nb_files,
2454 Client.Name AS clientname
2455 FROM Job INNER JOIN Client USING (ClientId)
2457 Client.Name = $client
2459 GROUP BY Client.Name
2462 my $row = $self->dbh_selectrow_hashref($query);
2464 $row->{ID} = $cur_id++;
2465 $row->{label} = $label;
2467 $self->display($row, "display_client_stats.tpl");
2470 # poolname can be undef
2473 my ($self, $poolname) = @_ ;
2477 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
2478 if ($arg->{jmediatypes}) {
2479 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
2480 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
2483 # TODO : afficher les tailles et les dates
2486 SELECT subq.volmax AS volmax,
2487 subq.volnum AS volnum,
2488 subq.voltotal AS voltotal,
2490 Pool.Recycle AS recycle,
2491 Pool.VolRetention AS volretention,
2492 Pool.VolUseDuration AS voluseduration,
2493 Pool.MaxVolJobs AS maxvoljobs,
2494 Pool.MaxVolFiles AS maxvolfiles,
2495 Pool.MaxVolBytes AS maxvolbytes,
2496 subq.PoolId AS PoolId
2499 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
2500 count(Media.MediaId) AS volnum,
2501 sum(Media.VolBytes) AS voltotal,
2502 Media.PoolId AS PoolId,
2503 Media.MediaType AS MediaType
2505 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
2506 Media.MediaType AS MediaType
2508 WHERE Media.VolStatus = 'Full'
2509 GROUP BY Media.MediaType
2510 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2511 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
2513 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
2517 my $all = $self->dbh_selectall_hashref($query, 'name') ;
2520 SELECT Pool.Name AS name,
2521 sum(VolBytes) AS size
2522 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
2523 WHERE Media.VolStatus IN ('Recycled', 'Purged')
2527 my $empty = $self->dbh_selectall_hashref($query, 'name');
2529 foreach my $p (values %$all) {
2530 if ($p->{volmax} > 0) { # mysql returns 0.0000
2531 # we remove Recycled/Purged media from pool usage
2532 if (defined $empty->{$p->{name}}) {
2533 $p->{voltotal} -= $empty->{$p->{name}}->{size};
2535 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
2537 $p->{poolusage} = 0;
2541 SELECT VolStatus AS volstatus, count(MediaId) AS nb
2543 WHERE PoolId=$p->{poolid}
2547 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
2548 foreach my $t (values %$content) {
2549 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
2554 $self->display({ ID => $cur_id++,
2555 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
2556 Pools => [ values %$all ]},
2557 "display_pool.tpl");
2560 sub display_running_job
2564 my $arg = $self->get_form('client', 'jobid');
2566 if (!$arg->{client} and $arg->{jobid}) {
2569 SELECT Client.Name AS name
2570 FROM Job INNER JOIN Client USING (ClientId)
2571 WHERE Job.JobId = $arg->{jobid}
2574 my $row = $self->dbh_selectrow_hashref($query);
2577 $arg->{client} = $row->{name};
2578 CGI::param('client', $arg->{client});
2582 if ($arg->{client}) {
2583 my $cli = new Bweb::Client(name => $arg->{client});
2584 $cli->display_running_job($self->{info}, $arg->{jobid});
2585 if ($arg->{jobid}) {
2586 $self->get_job_log();
2589 $self->error("Can't get client or jobid");
2593 sub display_running_jobs
2595 my ($self, $display_action) = @_;
2598 SELECT Job.JobId AS jobid,
2599 Job.Name AS jobname,
2601 Job.StartTime AS starttime,
2602 Job.JobFiles AS jobfiles,
2603 Job.JobBytes AS jobbytes,
2604 Job.JobStatus AS jobstatus,
2605 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
2606 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2608 Client.Name AS clientname
2609 FROM Job INNER JOIN Client USING (ClientId)
2610 WHERE JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
2612 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2614 $self->display({ ID => $cur_id++,
2615 display_action => $display_action,
2616 Jobs => [ values %$all ]},
2617 "running_job.tpl") ;
2623 my $arg = $self->get_form('jmedias');
2625 unless ($arg->{jmedias}) {
2626 return $self->error("Can't get media selection");
2630 SELECT Media.VolumeName AS volumename,
2631 Storage.Name AS storage,
2632 Location.Location AS location,
2634 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
2635 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2636 WHERE Media.VolumeName IN ($arg->{jmedias})
2637 AND Media.InChanger = 1
2640 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2642 foreach my $vol (values %$all) {
2643 my $a = $self->ach_get($vol->{location});
2646 unless ($a->{have_status}) {
2648 $a->{have_status} = 1;
2651 print "eject $vol->{volumename} from $vol->{storage} : ";
2652 if ($a->send_to_io($vol->{slot})) {
2664 my ($to, $subject, $content) = (CGI::param('email'),
2665 CGI::param('subject'),
2666 CGI::param('content'));
2667 $to =~ s/[^\w\d\.\@<>,]//;
2668 $subject =~ s/[^\w\d\.\[\]]/ /;
2670 open(MAIL, "|mail -s '$subject' '$to'") ;
2671 print MAIL $content;
2681 my $arg = $self->get_form('jobid', 'client');
2683 print CGI::header('text/brestore');
2684 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
2685 print "client=$arg->{client}\n" if ($arg->{client});
2686 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
2690 # TODO : move this to Bweb::Autochanger ?
2691 # TODO : make this internal to not eject tape ?
2697 my ($self, $name) = @_;
2700 return $self->error("Can't get your autochanger name ach");
2703 unless ($self->{info}->{ach_list}) {
2704 return $self->error("Could not find any autochanger");
2707 my $a = $self->{info}->{ach_list}->{$name};
2710 $self->error("Can't get your autochanger $name from your ach_list");
2721 my ($self, $ach) = @_;
2723 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
2725 $self->{info}->save();
2733 my $arg = $self->get_form('ach');
2735 or !$self->{info}->{ach_list}
2736 or !$self->{info}->{ach_list}->{$arg->{ach}})
2738 return $self->error("Can't get autochanger name");
2741 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
2745 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
2747 my $b = $self->get_bconsole();
2749 my @storages = $b->list_storage() ;
2751 $ach->{devices} = [ map { { name => $_ } } @storages ];
2753 $self->display($ach, "ach_add.tpl");
2754 delete $ach->{drives};
2755 delete $ach->{devices};
2762 my $arg = $self->get_form('ach');
2765 or !$self->{info}->{ach_list}
2766 or !$self->{info}->{ach_list}->{$arg->{ach}})
2768 return $self->error("Can't get autochanger name");
2771 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
2773 $self->{info}->save();
2774 $self->{info}->view();
2780 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
2782 my $b = $self->get_bconsole();
2783 my @storages = $b->list_storage() ;
2785 unless ($arg->{ach}) {
2786 $arg->{devices} = [ map { { name => $_ } } @storages ];
2787 return $self->display($arg, "ach_add.tpl");
2791 foreach my $drive (CGI::param('drives'))
2793 unless (grep(/^$drive$/,@storages)) {
2794 return $self->error("Can't find $drive in storage list");
2797 my $index = CGI::param("index_$drive");
2798 unless (defined $index and $index =~ /^(\d+)$/) {
2799 return $self->error("Can't get $drive index");
2802 $drives[$index] = $drive;
2806 return $self->error("Can't get drives from Autochanger");
2809 my $a = new Bweb::Autochanger(name => $arg->{ach},
2810 precmd => $arg->{precmd},
2811 drive_name => \@drives,
2812 device => $arg->{device},
2813 mtxcmd => $arg->{mtxcmd});
2815 $self->ach_register($a) ;
2817 $self->{info}->view();
2823 my $arg = $self->get_form('jobid');
2825 if ($arg->{jobid}) {
2826 my $b = $self->get_bconsole();
2827 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
2831 title => "Delete a job ",
2832 name => "delete jobid=$arg->{jobid}",
2841 my $arg = $self->get_form(qw/media volstatus inchanger pool
2842 slot volretention voluseduration
2843 maxvoljobs maxvolfiles maxvolbytes
2844 qcomment poolrecycle
2847 unless ($arg->{media}) {
2848 return $self->error("Can't find media selection");
2851 my $update = "update volume=$arg->{media} ";
2853 if ($arg->{volstatus}) {
2854 $update .= " volstatus=$arg->{volstatus} ";
2857 if ($arg->{inchanger}) {
2858 $update .= " inchanger=yes " ;
2860 $update .= " slot=$arg->{slot} ";
2863 $update .= " slot=0 inchanger=no ";
2867 $update .= " pool=$arg->{pool} " ;
2870 $arg->{volretention} ||= 0 ;
2871 if ($arg->{volretention}) {
2872 $update .= " volretention=\"$arg->{volretention}\" " ;
2875 $arg->{voluseduration} ||= 0 ;
2876 if ($arg->{voluseduration}) {
2877 $update .= " voluse=\"$arg->{voluseduration}\" " ;
2880 $arg->{maxvoljobs} ||= 0;
2881 if ($arg->{maxvoljobs}) {
2882 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
2885 $arg->{maxvolfiles} ||= 0;
2886 if ($arg->{maxvolfiles}) {
2887 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
2890 $arg->{maxvolbytes} ||= 0;
2891 if ($arg->{maxvolbytes}) {
2892 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
2895 my $b = $self->get_bconsole();
2898 content => $b->send_cmd($update),
2899 title => "Update a volume ",
2905 my $media = $self->dbh_quote($arg->{media});
2907 my $loc = CGI::param('location') || '';
2909 $loc = $self->dbh_quote($loc); # is checked by db
2910 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
2912 if ($arg->{poolrecycle}) {
2913 push @q, "RecyclePoolId=(SELECT PoolId FROM Pool WHERE Name='$arg->{poolrecycle}')";
2915 if (!$arg->{qcomment}) {
2916 $arg->{qcomment} = "''";
2918 push @q, "Comment=$arg->{qcomment}";
2923 SET " . join (',', @q) . "
2924 WHERE Media.VolumeName = $media
2926 $self->dbh_do($query);
2928 $self->update_media();
2935 my $ach = CGI::param('ach') ;
2936 $ach = $self->ach_get($ach);
2938 return $self->error("Bad autochanger name");
2942 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
2943 $b->update_slots($ach->{name});
2951 my $arg = $self->get_form('jobid');
2952 unless ($arg->{jobid}) {
2953 return $self->error("Can't get jobid");
2956 my $t = CGI::param('time') || '';
2959 SELECT Job.Name as name, Client.Name as clientname
2960 FROM Job INNER JOIN Client ON (Job.ClientId = Client.ClientId)
2961 WHERE JobId = $arg->{jobid}
2964 my $row = $self->dbh_selectrow_hashref($query);
2967 return $self->error("Can't find $arg->{jobid} in catalog");
2971 SELECT Time AS time, LogText AS log
2973 WHERE Log.JobId = $arg->{jobid}
2974 OR (Log.JobId = 0 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
2975 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
2980 my $log = $self->dbh_selectall_arrayref($query);
2982 return $self->error("Can't get log for jobid $arg->{jobid}");
2988 $logtxt = join("", map { ($_->[0] . ' ' . $_->[1]) } @$log ) ;
2990 $logtxt = join("", map { $_->[1] } @$log ) ;
2993 $self->display({ lines=> $logtxt,
2994 jobid => $arg->{jobid},
2995 name => $row->{name},
2996 client => $row->{clientname},
2997 }, 'display_log.tpl');
3005 my $arg = $self->get_form('ach', 'slots', 'drive');
3007 unless ($arg->{ach}) {
3008 return $self->error("Can't find autochanger name");
3013 if ($arg->{slots}) {
3014 $slots = join(",", @{ $arg->{slots} });
3015 $t += 60*scalar( @{ $arg->{slots} }) ;
3018 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3019 print "<h1>This command can take long time, be patient...</h1>";
3021 $b->label_barcodes(storage => $arg->{ach},
3022 drive => $arg->{drive},
3033 my @volume = CGI::param('media');
3036 return $self->error("Can't get media selection");
3039 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3042 content => $b->purge_volume(@volume),
3043 title => "Purge media",
3044 name => "purge volume=" . join(' volume=', @volume),
3053 my @volume = CGI::param('media');
3055 return $self->error("Can't get media selection");
3058 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3061 content => $b->prune_volume(@volume),
3062 title => "Prune media",
3063 name => "prune volume=" . join(' volume=', @volume),
3073 my $arg = $self->get_form('jobid');
3074 unless ($arg->{jobid}) {
3075 return $self->error("Can't get jobid");
3078 my $b = $self->get_bconsole();
3080 content => $b->cancel($arg->{jobid}),
3081 title => "Cancel job",
3082 name => "cancel jobid=$arg->{jobid}",
3088 # Warning, we display current fileset
3091 my $arg = $self->get_form('fileset');
3093 if ($arg->{fileset}) {
3094 my $b = $self->get_bconsole();
3095 my $ret = $b->get_fileset($arg->{fileset});
3096 $self->display({ fileset => $arg->{fileset},
3098 }, "fileset_view.tpl");
3100 $self->error("Can't get fileset name");
3104 sub director_show_sched
3108 my $arg = $self->get_form('days');
3110 my $b = $self->get_bconsole();
3111 my $ret = $b->director_get_sched( $arg->{days} );
3116 }, "scheduled_job.tpl");
3119 sub enable_disable_job
3121 my ($self, $what) = @_ ;
3123 my $name = CGI::param('job') || '';
3124 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3125 return $self->error("Can't find job name");
3128 my $b = $self->get_bconsole();
3138 content => $b->send_cmd("$cmd job=\"$name\""),
3139 title => "$cmd $name",
3140 name => "$cmd job=\"$name\"",
3147 return new Bconsole(pref => $self->{info});
3153 my $b = $self->get_bconsole();
3155 my $joblist = [ map { { name => $_ } } $b->list_job() ];
3157 $self->display({ Jobs => $joblist }, "run_job.tpl");
3162 my ($self, $ouput) = @_;
3165 foreach my $l (split(/\r\n/, $ouput)) {
3166 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
3172 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
3178 foreach my $k (keys %arg) {
3179 $lowcase{lc($k)} = $arg{$k} ;
3188 my $b = $self->get_bconsole();
3190 my $job = CGI::param('job') || '';
3192 my $info = $b->send_cmd("show job=\"$job\"");
3193 my $attr = $self->run_parse_job($info);
3195 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3197 my $pools = [ map { { name => $_ } } $b->list_pool() ];
3198 my $clients = [ map { { name => $_ } }$b->list_client()];
3199 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
3200 my $storages= [ map { { name => $_ } }$b->list_storage()];
3205 clients => $clients,
3206 filesets => $filesets,
3207 storages => $storages,
3209 }, "run_job_mod.tpl");
3215 my $b = $self->get_bconsole();
3217 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3227 my $b = $self->get_bconsole();
3229 # TODO: check input (don't use pool, level)
3231 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when');
3232 my $job = CGI::param('job') || '';
3233 my $storage = CGI::param('storage') || '';
3235 my $jobid = $b->run(job => $job,
3236 client => $arg->{client},
3237 priority => $arg->{priority},
3238 level => $arg->{level},
3239 storage => $storage,
3240 pool => $arg->{pool},
3241 when => $arg->{when},
3244 print $jobid, $b->{error};
3246 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";