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';
58 new - creation a of new Bweb object
62 This function take an hash of argument and place them
65 IE : $obj = new Obj(name => 'test', age => '10');
67 $obj->{name} eq 'test' and $obj->{age} eq 10
73 my ($class, %arg) = @_;
78 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
85 my ($self, $what) = @_;
89 print "<pre>" . Data::Dumper::Dumper($what) . "</pre>";
91 print "<pre>$what</pre>";
98 error - display an error to the user
102 this function set $self->{error} with arg, display a message with
103 error.tpl and return 0
108 return $self->error("Can't use this file");
115 my ($self, $what) = @_;
116 $self->{error} = $what;
117 $self->display($self, 'error.tpl');
123 display - display an html page with HTML::Template
127 this function is use to render all html codes. it takes an
128 ref hash as arg in which all param are usable in template.
130 it will use global template_dir to search the template file.
132 hash keys are not sensitive. See HTML::Template for more
133 explanations about the hash ref. (it's can be quiet hard to understand)
137 $ref = { name => 'me', age => 26 };
138 $self->display($ref, "people.tpl");
144 my ($self, $hash, $tpl) = @_ ;
146 my $template = HTML::Template->new(filename => $tpl,
147 path =>[$template_dir],
148 die_on_bad_params => 0,
149 case_sensitive => 0);
151 foreach my $var (qw/limit offset/) {
153 unless ($hash->{$var}) {
154 my $value = CGI::param($var) || '';
156 if ($value =~ /^(\d+)$/) {
157 $template->param($var, $1) ;
162 $template->param('thisurl', CGI::url(-relative => 1, -query=>1));
163 $template->param('loginname', CGI::remote_user());
165 $template->param($hash);
166 print $template->output();
170 ################################################################
172 package Bweb::Config;
174 use base q/Bweb::Gui/;
178 Bweb::Config - read, write, display, modify configuration
182 this package is used for manage configuration
186 $conf = new Bweb::Config(config_file => '/path/to/conf');
197 =head1 PACKAGE VARIABLE
199 %k_re - hash of all acceptable option.
203 this variable permit to check all option with a regexp.
207 our %k_re = ( dbi => qr/^(dbi:(Pg|mysql):(?:\w+=[\w\d\.-]+;?)+)$/i,
208 user => qr/^([\w\d\.-]+)$/i,
209 password => qr/^(.*)$/i,
210 fv_write_path => qr!^([/\w\d\.-]*)$!,
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!^(.+)?$!,
218 stat_job_table => qr!^(\w*)$!,
219 display_log_time => qr!^(on)?$!,
220 enable_security => qr/^(on)?$/,
225 load - load config_file
229 this function load the specified config_file.
237 unless (open(FP, $self->{config_file}))
239 return $self->error("can't load config_file $self->{config_file} : $!");
241 my $f=''; my $tmpbuffer;
242 while(read FP,$tmpbuffer,4096)
250 no strict; # I have no idea of the contents of the file
257 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...") ;
260 foreach my $k (keys %$VAR1) {
261 $self->{$k} = $VAR1->{$k};
269 load_old - load old configuration format
277 unless (open(FP, $self->{config_file}))
279 return $self->error("$self->{config_file} : $!");
282 while (my $line = <FP>)
285 my ($k, $v) = split(/\s*=\s*/, $line, 2);
297 save - save the current configuration to config_file
305 if ($self->{ach_list}) {
306 # shortcut for display_begin
307 $self->{achs} = [ map {{ name => $_ }}
308 keys %{$self->{ach_list}}
312 unless (open(FP, ">$self->{config_file}"))
314 return $self->error("$self->{config_file} : $!\n" .
315 "You must add this to your config file\n"
316 . Data::Dumper::Dumper($self));
319 print FP Data::Dumper::Dumper($self);
327 edit, view, modify - html form ouput
335 $self->display($self, "config_edit.tpl");
341 $self->display($self, "config_view.tpl");
349 # we need to reset checkbox first
351 $self->{enable_security} = 0;
352 $self->{display_log_time} = 0;
354 foreach my $k (CGI::param())
356 next unless (exists $k_re{$k}) ;
357 my $val = CGI::param($k);
358 if ($val =~ $k_re{$k}) {
361 $self->{error} .= "bad parameter : $k = [$val]";
367 if ($self->{error}) { # an error as occured
368 $self->display($self, 'error.tpl');
376 ################################################################
378 package Bweb::Client;
380 use base q/Bweb::Gui/;
384 Bweb::Client - Bacula FD
388 this package is use to do all Client operations like, parse status etc...
392 $client = new Bweb::Client(name => 'zog-fd');
393 $client->status(); # do a 'status client=zog-fd'
399 display_running_job - Html display of a running job
403 this function is used to display information about a current job
407 sub display_running_job
409 my ($self, $conf, $jobid) = @_ ;
411 my $status = $self->status($conf);
414 if ($status->{$jobid}) {
415 $self->display($status->{$jobid}, "client_job_status.tpl");
418 for my $id (keys %$status) {
419 $self->display($status->{$id}, "client_job_status.tpl");
426 $client = new Bweb::Client(name => 'plume-fd');
428 $client->status($bweb);
432 dirty hack to parse "status client=xxx-fd"
436 JobId 105 Job Full_plume.2006-06-06_17.22.23 is running.
437 Backup Job started: 06-jun-06 17:22
438 Files=8,971 Bytes=194,484,132 Bytes/sec=7,480,158
439 Files Examined=10,697
440 Processing file: /home/eric/.openoffice.org2/user/config/standard.sod
446 JobName => Full_plume.2006-06-06_17.22.23,
449 Bytes => 194,484,132,
459 my ($self, $conf) = @_ ;
461 if (defined $self->{cur_jobs}) {
462 return $self->{cur_jobs} ;
466 my $b = new Bconsole(pref => $conf);
467 my $ret = $b->send_cmd("st client=$self->{name}");
471 for my $r (split(/\n/, $ret)) {
473 $r =~ s/(^\s+|\s+$)//g;
474 if ($r =~ /JobId (\d+) Job (\S+)/) {
476 $arg->{$jobid} = { @param, JobId => $jobid } ;
480 @param = ( JobName => $2 );
482 } elsif ($r =~ /=.+=/) {
483 push @param, split(/\s+|\s*=\s*/, $r) ;
485 } elsif ($r =~ /=/) { # one per line
486 push @param, split(/\s*=\s*/, $r) ;
488 } elsif ($r =~ /:/) { # one per line
489 push @param, split(/\s*:\s*/, $r, 2) ;
493 if ($jobid and @param) {
494 $arg->{$jobid} = { @param,
496 Client => $self->{name},
500 $self->{cur_jobs} = $arg ;
506 ################################################################
508 package Bweb::Autochanger;
510 use base q/Bweb::Gui/;
514 Bweb::Autochanger - Object to manage Autochanger
518 this package will parse the mtx output and manage drives.
522 $auto = new Bweb::Autochanger(precmd => 'sudo');
524 $auto = new Bweb::Autochanger(precmd => 'ssh root@robot');
528 $auto->slot_is_full(10);
529 $auto->transfer(10, 11);
535 my ($class, %arg) = @_;
538 name => '', # autochanger name
539 label => {}, # where are volume { label1 => 40, label2 => drive0 }
540 drive => [], # drive use [ 'media1', 'empty', ..]
541 slot => [], # slot use [ undef, 'empty', 'empty', ..] no slot 0
542 io => [], # io slot number list [ 41, 42, 43...]
543 info => {slot => 0, # informations (slot, drive, io)
547 mtxcmd => '/usr/sbin/mtx',
549 device => '/dev/changer',
550 precmd => '', # ssh command
551 bweb => undef, # link to bacula web object (use for display)
554 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
561 status - parse the output of mtx status
565 this function will launch mtx status and parse the output. it will
566 give a perlish view of the autochanger content.
568 it uses ssh if the autochanger is on a other host.
575 my @out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} status` ;
577 # TODO : reset all infos
578 $self->{info}->{drive} = 0;
579 $self->{info}->{slot} = 0;
580 $self->{info}->{io} = 0;
582 #my @out = `cat /home/eric/travail/brestore/plume/mtx` ;
585 # Storage Changer /dev/changer:2 Drives, 45 Slots ( 5 Import/Export )
586 #Data Transfer Element 0:Full (Storage Element 1 Loaded):VolumeTag = 000000
587 #Data Transfer Element 1:Empty
588 # Storage Element 1:Empty
589 # Storage Element 2:Full :VolumeTag=000002
590 # Storage Element 3:Empty
591 # Storage Element 4:Full :VolumeTag=000004
592 # Storage Element 5:Full :VolumeTag=000001
593 # Storage Element 6:Full :VolumeTag=000003
594 # Storage Element 7:Empty
595 # Storage Element 41 IMPORT/EXPORT:Empty
596 # Storage Element 41 IMPORT/EXPORT:Full :VolumeTag=000002
601 # Storage Element 7:Empty
602 # Storage Element 2:Full :VolumeTag=000002
603 if ($l =~ /Storage Element (\d+):(Empty|Full)(\s+:VolumeTag=([\w\d]+))?/){
606 $self->set_empty_slot($1);
608 $self->set_slot($1, $4);
611 } elsif ($l =~ /Data Transfer.+(\d+):(Full|Empty)(\s+.Storage Element (\d+) Loaded.(:VolumeTag = ([\w\d]+))?)?/) {
614 $self->set_empty_drive($1);
616 $self->set_drive($1, $4, $6);
619 } elsif ($l =~ /Storage Element (\d+).+IMPORT\/EXPORT:(Empty|Full)( :VolumeTag=([\d\w]+))?/)
622 $self->set_empty_io($1);
624 $self->set_io($1, $4);
627 # Storage Changer /dev/changer:2 Drives, 30 Slots ( 1 Import/Export )
629 } elsif ($l =~ /Storage Changer .+:(\d+) Drives, (\d+) Slots/) {
630 $self->{info}->{drive} = $1;
631 $self->{info}->{slot} = $2;
632 if ($l =~ /(\d+)\s+Import/) {
633 $self->{info}->{io} = $1 ;
635 $self->{info}->{io} = 0;
640 $self->debug($self) ;
645 my ($self, $slot) = @_;
648 if ($self->{slot}->[$slot] eq 'loaded') {
652 my $label = $self->{slot}->[$slot] ;
654 return $self->is_media_loaded($label);
659 my ($self, $drive, $slot) = @_;
661 return 0 if (not defined $drive or $self->{drive}->[$drive] eq 'empty') ;
662 return 0 if ($self->slot_is_full($slot)) ;
664 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} unload $slot $drive 2>&1`;
667 my $content = $self->get_slot($slot);
668 print "content = $content<br/> $drive => $slot<br/>";
669 $self->set_empty_drive($drive);
670 $self->set_slot($slot, $content);
673 $self->{error} = $out;
678 # TODO: load/unload have to use mtx script from bacula
681 my ($self, $drive, $slot) = @_;
683 return 0 if (not defined $drive or $self->{drive}->[$drive] ne 'empty') ;
684 return 0 unless ($self->slot_is_full($slot)) ;
686 print "Loading drive $drive with slot $slot<br/>\n";
687 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} load $slot $drive 2>&1`;
690 my $content = $self->get_slot($slot);
691 print "content = $content<br/> $slot => $drive<br/>";
692 $self->set_drive($drive, $slot, $content);
695 $self->{error} = $out;
703 my ($self, $media) = @_;
705 unless ($self->{label}->{$media}) {
709 if ($self->{label}->{$media} =~ /drive\d+/) {
719 return (defined $self->{info}->{io} and $self->{info}->{io} > 0);
724 my ($self, $slot, $tag) = @_;
725 $self->{slot}->[$slot] = $tag || 'full';
726 push @{ $self->{io} }, $slot;
729 $self->{label}->{$tag} = $slot;
735 my ($self, $slot) = @_;
737 push @{ $self->{io} }, $slot;
739 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
740 $self->{slot}->[$slot] = 'empty';
746 my ($self, $slot) = @_;
747 return $self->{slot}->[$slot];
752 my ($self, $slot, $tag) = @_;
753 $self->{slot}->[$slot] = $tag || 'full';
756 $self->{label}->{$tag} = $slot;
762 my ($self, $slot) = @_;
764 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
765 $self->{slot}->[$slot] = 'empty';
771 my ($self, $drive) = @_;
772 $self->{drive}->[$drive] = 'empty';
777 my ($self, $drive, $slot, $tag) = @_;
778 $self->{drive}->[$drive] = $tag || $slot;
780 $self->{slot}->[$slot] = $tag || 'loaded';
783 $self->{label}->{$tag} = "drive$drive";
789 my ($self, $slot) = @_;
791 # slot don't exists => full
792 if (not defined $self->{slot}->[$slot]) {
796 if ($self->{slot}->[$slot] eq 'empty') {
799 return 1; # vol, full, loaded
802 sub slot_get_first_free
805 for (my $slot=1; $slot < $self->{info}->{slot}; $slot++) {
806 return $slot unless ($self->slot_is_full($slot));
810 sub io_get_first_free
814 foreach my $slot (@{ $self->{io} }) {
815 return $slot unless ($self->slot_is_full($slot));
822 my ($self, $media) = @_;
824 return $self->{label}->{$media} ;
829 my ($self, $media) = @_;
831 return defined $self->{label}->{$media} ;
836 my ($self, $slot) = @_;
838 unless ($self->slot_is_full($slot)) {
839 print "Autochanger $self->{name} slot $slot is empty\n";
844 if ($self->is_slot_loaded($slot)) {
847 print "Autochanger $self->{name} $slot is currently in use\n";
851 # autochanger must have I/O
852 unless ($self->have_io()) {
853 print "Autochanger $self->{name} don't have I/O, you can take media yourself\n";
857 my $dst = $self->io_get_first_free();
860 print "Autochanger $self->{name} you must empty I/O first\n";
863 $self->transfer($slot, $dst);
868 my ($self, $src, $dst) = @_ ;
869 if ($self->{debug}) {
870 print "<pre>$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst</pre>\n";
872 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst 2>&1`;
875 my $content = $self->get_slot($src);
876 $self->{slot}->[$src] = 'empty';
877 $self->set_slot($dst, $content);
880 $self->{error} = $out;
887 my ($self, $index) = @_;
888 return $self->{drive_name}->[$index];
891 # TODO : do a tapeinfo request to get informations
901 for my $slot (@{$self->{io}})
903 if ($self->is_slot_loaded($slot)) {
904 print "$slot is currently loaded\n";
908 if ($self->slot_is_full($slot))
910 my $free = $self->slot_get_first_free() ;
911 print "move $slot to $free :\n";
914 if ($self->transfer($slot, $free)) {
915 print "<img src='/bweb/T.png' alt='ok'><br/>\n";
917 print "<img src='/bweb/E.png' alt='ok' title='$self->{error}'><br/>\n";
921 $self->{error} = "<img src='/bweb/E.png' alt='ok' title='E : Can t find free slot'><br/>\n";
927 # TODO : this is with mtx status output,
928 # we can do an other function from bacula view (with StorageId)
932 my $bweb = $self->{bweb};
934 # $self->{label} => ('vol1', 'vol2', 'vol3', ..);
935 my $media_list = $bweb->dbh_join( keys %{ $self->{label} });
938 SELECT Media.VolumeName AS volumename,
939 Media.VolStatus AS volstatus,
940 Media.LastWritten AS lastwritten,
941 Media.VolBytes AS volbytes,
942 Media.MediaType AS mediatype,
944 Media.InChanger AS inchanger,
946 $bweb->{sql}->{FROM_UNIXTIME}(
947 $bweb->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
948 + $bweb->{sql}->{TO_SEC}(Media.VolRetention)
951 INNER JOIN Pool USING (PoolId)
953 WHERE Media.VolumeName IN ($media_list)
956 my $all = $bweb->dbh_selectall_hashref($query, 'volumename') ;
958 # TODO : verify slot and bacula slot
962 for (my $slot=1; $slot <= $self->{info}->{slot} ; $slot++) {
964 if ($self->slot_is_full($slot)) {
966 my $vol = $self->{slot}->[$slot];
967 if (defined $all->{$vol}) { # TODO : autochanger without barcodes
969 my $bslot = $all->{$vol}->{slot} ;
970 my $inchanger = $all->{$vol}->{inchanger};
972 # if bacula slot or inchanger flag is bad, we display a message
973 if ($bslot != $slot or !$inchanger) {
974 push @to_update, $slot;
977 $all->{$vol}->{realslot} = $slot;
979 push @{ $param }, $all->{$vol};
981 } else { # empty or no label
982 push @{ $param }, {realslot => $slot,
983 volstatus => 'Unknown',
984 volumename => $self->{slot}->[$slot]} ;
987 push @{ $param }, {realslot => $slot, volumename => 'empty'} ;
991 my $i=0; my $drives = [] ;
992 foreach my $d (@{ $self->{drive} }) {
993 $drives->[$i] = { index => $i,
994 load => $self->{drive}->[$i],
995 name => $self->{drive_name}->[$i],
1000 $bweb->display({ Name => $self->{name},
1001 nb_drive => $self->{info}->{drive},
1002 nb_io => $self->{info}->{io},
1005 Update => scalar(@to_update) },
1013 ################################################################
1017 use base q/Bweb::Gui/;
1021 Bweb - main Bweb package
1025 this package is use to compute and display informations
1030 use POSIX qw/strftime/;
1032 our $config_file='/etc/bacula/bweb.conf';
1038 %sql_func - hash to make query mysql/postgresql compliant
1044 UNIX_TIMESTAMP => '',
1045 FROM_UNIXTIME => '',
1046 TO_SEC => " interval '1 second' * ",
1047 SEC_TO_INT => "SEC_TO_INT",
1050 STARTTIME_DAY => " date_trunc('day', Job.StartTime) ",
1051 STARTTIME_HOUR => " date_trunc('hour', Job.StartTime) ",
1052 STARTTIME_MONTH => " date_trunc('month', Job.StartTime) ",
1053 STARTTIME_PHOUR=> " date_part('hour', Job.StartTime) ",
1054 STARTTIME_PDAY => " date_part('day', Job.StartTime) ",
1055 STARTTIME_PMONTH => " date_part('month', Job.StartTime) ",
1056 STARTTIME_PWEEK => " date_part('week', Job.StartTime) ",
1057 DB_SIZE => " SELECT pg_database_size(current_database()) ",
1058 CAT_POOL_TYPE => " MediaType || '_' || Pool.Name ",
1061 UNIX_TIMESTAMP => 'UNIX_TIMESTAMP',
1062 FROM_UNIXTIME => 'FROM_UNIXTIME',
1065 SEC_TO_TIME => 'SEC_TO_TIME',
1066 MATCH => " REGEXP ",
1067 STARTTIME_DAY => " DATE_FORMAT(StartTime, '%Y-%m-%d') ",
1068 STARTTIME_HOUR => " DATE_FORMAT(StartTime, '%Y-%m-%d %H') ",
1069 STARTTIME_MONTH => " DATE_FORMAT(StartTime, '%Y-%m') ",
1070 STARTTIME_PHOUR=> " DATE_FORMAT(StartTime, '%H') ",
1071 STARTTIME_PDAY => " DATE_FORMAT(StartTime, '%d') ",
1072 STARTTIME_PMONTH => " DATE_FORMAT(StartTime, '%m') ",
1073 STARTTIME_PWEEK => " DATE_FORMAT(StartTime, '%v') ",
1074 # with mysql < 5, you have to play with the ugly SHOW command
1075 DB_SIZE => " SELECT 0 ",
1076 # works only with mysql 5
1077 # DB_SIZE => " SELECT sum(DATA_LENGTH) FROM INFORMATION_SCHEMA.TABLES ",
1078 CAT_POOL_TYPE => " CONCAT(MediaType,'_',Pool.Name) ",
1086 $self->{dbh}->disconnect();
1091 sub dbh_selectall_arrayref
1093 my ($self, $query) = @_;
1094 $self->connect_db();
1095 $self->debug($query);
1096 return $self->{dbh}->selectall_arrayref($query);
1101 my ($self, @what) = @_;
1102 return join(',', $self->dbh_quote(@what)) ;
1107 my ($self, @what) = @_;
1109 $self->connect_db();
1111 return map { $self->{dbh}->quote($_) } @what;
1113 return $self->{dbh}->quote($what[0]) ;
1119 my ($self, $query) = @_ ;
1120 $self->connect_db();
1121 $self->debug($query);
1122 return $self->{dbh}->do($query);
1125 sub dbh_selectall_hashref
1127 my ($self, $query, $join) = @_;
1129 $self->connect_db();
1130 $self->debug($query);
1131 return $self->{dbh}->selectall_hashref($query, $join) ;
1134 sub dbh_selectrow_hashref
1136 my ($self, $query) = @_;
1138 $self->connect_db();
1139 $self->debug($query);
1140 return $self->{dbh}->selectrow_hashref($query) ;
1145 my ($self, @what) = @_;
1146 if ($self->{conf}->{connection_string} =~ /dbi:mysql/i) {
1147 return 'CONCAT(' . join(',', @what) . ')' ;
1149 return join(' || ', @what);
1155 my ($self, $query) = @_;
1156 $self->debug($query, up => 1);
1157 return $self->{dbh}->prepare($query);
1163 my @unit = qw(B KB MB GB TB);
1164 my $val = shift || 0;
1166 my $format = '%i %s';
1167 while ($val / 1024 > 1) {
1171 $format = ($i>0)?'%0.1f %s':'%i %s';
1172 return sprintf($format, $val, $unit[$i]);
1175 # display Day, Hour, Year
1181 $val /= 60; # sec -> min
1183 if ($val / 60 <= 1) {
1187 $val /= 60; # min -> hour
1188 if ($val / 24 <= 1) {
1189 return "$val hours";
1192 $val /= 24; # hour -> day
1193 if ($val / 365 < 2) {
1197 $val /= 365 ; # day -> year
1199 return "$val years";
1205 my $val = shift || 0;
1207 if ($val == 1 or $val eq "yes") {
1209 } elsif ($val == 2 or $val eq "archived") {
1216 # get Day, Hour, Year
1222 unless ($val =~ /^\s*(\d+)\s*(\w)\w*\s*$/) {
1226 my %times = ( m => 60,
1232 my $mult = $times{$2} || 0;
1242 unless ($self->{dbh}) {
1243 $self->{dbh} = DBI->connect($self->{info}->{dbi},
1244 $self->{info}->{user},
1245 $self->{info}->{password});
1247 $self->error("Can't connect to your database:\n$DBI::errstr\n")
1248 unless ($self->{dbh});
1250 $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
1252 if ($self->{info}->{dbi} =~ /^dbi:Pg/i) {
1253 $self->{dbh}->do("SET datestyle TO 'ISO, YMD'");
1260 my ($class, %arg) = @_;
1262 dbh => undef, # connect_db();
1264 dbi => '', # DBI:Pg:database=bacula;host=127.0.0.1
1270 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
1272 if ($self->{info}->{dbi} =~ /DBI:(\w+):/i) {
1273 $self->{sql} = $sql_func{$1};
1276 $self->{loginname} = CGI::remote_user();
1277 $self->{debug} = $self->{info}->{debug};
1278 $Bweb::Gui::template_dir = $self->{info}->{template_dir};
1286 $self->display($self->{info}, "begin.tpl");
1292 $self->display($self->{info}, "end.tpl");
1300 my $arg = $self->get_form("client", "qre_client", "jclient_groups", "qnotingroup");
1302 if ($arg->{qre_client}) {
1303 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1304 } elsif ($arg->{client}) {
1305 $where = "WHERE Name = '$arg->{client}' ";
1306 } elsif ($arg->{jclient_groups}) {
1307 $where = "JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
1308 JOIN client_group USING (client_group_id)
1309 WHERE client_group_name IN ($arg->{jclient_groups})";
1310 } elsif ($arg->{qnotingroup}) {
1313 (SELECT 1 FROM client_group_member
1314 WHERE Client.ClientId = client_group_member.ClientId
1321 SELECT Name AS name,
1323 AutoPrune AS autoprune,
1324 FileRetention AS fileretention,
1325 JobRetention AS jobretention
1330 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1332 my $dsp = { ID => $cur_id++,
1333 clients => [ values %$all] };
1335 $self->display($dsp, "client_list.tpl") ;
1340 my ($self, %arg) = @_;
1347 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1349 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1351 $self->{sql}->{TO_SEC}($arg{age})
1354 $label = "last " . human_sec($arg{age});
1357 if ($arg{groupby}) {
1358 $limit .= " GROUP BY $arg{groupby} ";
1362 $limit .= " ORDER BY $arg{order} ";
1366 $limit .= " LIMIT $arg{limit} ";
1367 $label .= " limited to $arg{limit}";
1371 $limit .= " OFFSET $arg{offset} ";
1372 $label .= " with $arg{offset} offset ";
1376 $label = 'no filter';
1379 return ($limit, $label);
1384 $bweb->get_form(...) - Get useful stuff
1388 This function get and check parameters against regexp.
1390 If word begin with 'q', the return will be quoted or join quoted
1391 if it's end with 's'.
1396 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1399 qclient => 'plume-fd',
1400 qpools => "'plume-fd', 'test-fd', '...'",
1407 my ($self, @what) = @_;
1408 my %what = map { $_ => 1 } @what;
1430 my %opt_ss =( # string with space
1434 my %opt_s = ( # default to ''
1455 my %opt_p = ( # option with path
1462 my %opt_r = (regexwhere => 1);
1464 my %opt_d = ( # option with date
1469 foreach my $i (@what) {
1470 if (exists $opt_i{$i}) {# integer param
1471 my $value = CGI::param($i) || $opt_i{$i} ;
1472 if ($value =~ /^(\d+)$/) {
1475 } elsif ($opt_s{$i}) { # simple string param
1476 my $value = CGI::param($i) || '';
1477 if ($value =~ /^([\w\d\.-]+)$/) {
1480 } elsif ($opt_ss{$i}) { # simple string param (with space)
1481 my $value = CGI::param($i) || '';
1482 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1485 } elsif ($i =~ /^j(\w+)s$/) { # quote join args "'arg1', 'arg2'"
1486 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1488 $ret{$i} = $self->dbh_join(@value) ;
1491 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1492 my $value = CGI::param($1) ;
1494 $ret{$i} = $self->dbh_quote($value);
1497 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1498 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1499 grep { ! /^\s*$/ } CGI::param($1) ];
1500 } elsif (exists $opt_p{$i}) {
1501 my $value = CGI::param($i) || '';
1502 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1505 } elsif (exists $opt_r{$i}) {
1506 my $value = CGI::param($i) || '';
1507 if ($value =~ /^([^'"']+)$/) {
1510 } elsif (exists $opt_d{$i}) {
1511 my $value = CGI::param($i) || '';
1512 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1519 foreach my $s (CGI::param('slot')) {
1520 if ($s =~ /^(\d+)$/) {
1521 push @{$ret{slots}}, $s;
1527 my $when = CGI::param('when') || '';
1528 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1533 if ($what{db_clients}) {
1535 SELECT Client.Name as clientname
1539 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1540 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1544 if ($what{db_client_groups}) {
1546 SELECT client_group_name AS name
1550 my $grps = $self->dbh_selectall_hashref($query, 'name');
1551 $ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} }
1555 if ($what{db_usernames}) {
1561 my $users = $self->dbh_selectall_hashref($query, 'username');
1562 $ret{db_usernames} = [sort {$a->{username} cmp $b->{username} }
1566 if ($what{db_roles}) {
1572 my $r = $self->dbh_selectall_hashref($query, 'rolename');
1573 $ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} }
1577 if ($what{db_mediatypes}) {
1579 SELECT MediaType as mediatype
1583 my $media = $self->dbh_selectall_hashref($query, 'mediatype');
1584 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1588 if ($what{db_locations}) {
1590 SELECT Location as location, Cost as cost
1593 my $loc = $self->dbh_selectall_hashref($query, 'location');
1594 $ret{db_locations} = [ sort { $a->{location}
1600 if ($what{db_pools}) {
1601 my $query = "SELECT Name as name FROM Pool";
1603 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1604 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1607 if ($what{db_filesets}) {
1609 SELECT FileSet.FileSet AS fileset
1613 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1615 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1616 values %$filesets] ;
1619 if ($what{db_jobnames}) {
1621 SELECT DISTINCT Job.Name AS jobname
1625 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1627 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1628 values %$jobnames] ;
1631 if ($what{db_devices}) {
1633 SELECT Device.Name AS name
1637 my $devices = $self->dbh_selectall_hashref($query, 'name');
1639 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1650 my $fields = $self->get_form(qw/age level status clients filesets
1652 db_clients limit db_filesets width height
1653 qclients qfilesets qjobnames db_jobnames/);
1656 my $url = CGI::url(-full => 0,
1659 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1661 # this organisation is to keep user choice between 2 click
1662 # TODO : fileset and client selection doesn't work
1671 sub display_client_job
1673 my ($self, %arg) = @_ ;
1675 $arg{order} = ' Job.JobId DESC ';
1676 my ($limit, $label) = $self->get_limit(%arg);
1678 my $clientname = $self->dbh_quote($arg{clientname});
1681 SELECT DISTINCT Job.JobId AS jobid,
1682 Job.Name AS jobname,
1683 FileSet.FileSet AS fileset,
1685 StartTime AS starttime,
1686 JobFiles AS jobfiles,
1687 JobBytes AS jobbytes,
1688 JobStatus AS jobstatus,
1689 JobErrors AS joberrors
1691 FROM Client,Job,FileSet
1692 WHERE Client.Name=$clientname
1693 AND Client.ClientId=Job.ClientId
1694 AND Job.FileSetId=FileSet.FileSetId
1698 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
1700 $self->display({ clientname => $arg{clientname},
1703 Jobs => [ values %$all ],
1705 "display_client_job.tpl") ;
1708 sub get_selected_media_location
1712 my $media = $self->get_form('jmedias');
1714 unless ($media->{jmedias}) {
1719 SELECT Media.VolumeName AS volumename, Location.Location AS location
1720 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1721 WHERE Media.VolumeName IN ($media->{jmedias})
1724 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1726 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1735 my ($self, $in) = @_ ;
1737 my $media = $self->get_selected_media_location();
1743 my $elt = $self->get_form('db_locations');
1745 $self->display({ ID => $cur_id++,
1746 enabled => human_enabled($in),
1747 %$elt, # db_locations
1749 sort { $a->{volumename} cmp $b->{volumename} } values %$media
1759 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1761 $self->display($elt, "help_extern.tpl");
1764 sub help_extern_compute
1768 my $number = CGI::param('limit') || '' ;
1769 unless ($number =~ /^(\d+)$/) {
1770 return $self->error("Bad arg number : $number ");
1773 my ($sql, undef) = $self->get_param('pools',
1774 'locations', 'mediatypes');
1777 SELECT Media.VolumeName AS volumename,
1778 Media.VolStatus AS volstatus,
1779 Media.LastWritten AS lastwritten,
1780 Media.MediaType AS mediatype,
1781 Media.VolMounts AS volmounts,
1783 Media.Recycle AS recycle,
1784 $self->{sql}->{FROM_UNIXTIME}(
1785 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1786 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1789 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1790 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1792 WHERE Media.InChanger = 1
1793 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1795 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1799 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1801 $self->display({ Media => [ values %$all ] },
1802 "help_extern_compute.tpl");
1809 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1810 $self->display($param, "help_intern.tpl");
1813 sub help_intern_compute
1817 my $number = CGI::param('limit') || '' ;
1818 unless ($number =~ /^(\d+)$/) {
1819 return $self->error("Bad arg number : $number ");
1822 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1824 if (CGI::param('expired')) {
1826 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1827 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1833 SELECT Media.VolumeName AS volumename,
1834 Media.VolStatus AS volstatus,
1835 Media.LastWritten AS lastwritten,
1836 Media.MediaType AS mediatype,
1837 Media.VolMounts AS volmounts,
1839 $self->{sql}->{FROM_UNIXTIME}(
1840 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1841 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1844 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1845 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1847 WHERE Media.InChanger <> 1
1848 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1849 AND Media.Recycle = 1
1851 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1855 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1857 $self->display({ Media => [ values %$all ] },
1858 "help_intern_compute.tpl");
1864 my ($self, %arg) = @_ ;
1866 my ($limit, $label) = $self->get_limit(%arg);
1870 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1871 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1872 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1873 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1874 ($self->{sql}->{DB_SIZE}) AS db_size,
1875 (SELECT count(Job.JobId)
1877 WHERE Job.JobStatus IN ('E','e','f','A')
1880 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1883 my $row = $self->dbh_selectrow_hashref($query) ;
1885 $row->{nb_bytes} = human_size($row->{nb_bytes});
1887 $row->{db_size} = human_size($row->{db_size});
1888 $row->{label} = $label;
1890 $self->display($row, "general.tpl");
1895 my ($self, @what) = @_ ;
1896 my %elt = map { $_ => 1 } @what;
1901 if ($elt{clients}) {
1902 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1904 $ret{clients} = \@clients;
1905 my $str = $self->dbh_join(@clients);
1906 $limit .= "AND Client.Name IN ($str) ";
1910 if ($elt{client_groups}) {
1911 my @clients = grep { ! /^\s*$/ } CGI::param('client_group');
1913 $ret{client_groups} = \@clients;
1914 my $str = $self->dbh_join(@clients);
1915 $limit .= "AND client_group_name IN ($str) ";
1919 if ($elt{filesets}) {
1920 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1922 $ret{filesets} = \@filesets;
1923 my $str = $self->dbh_join(@filesets);
1924 $limit .= "AND FileSet.FileSet IN ($str) ";
1928 if ($elt{mediatypes}) {
1929 my @media = grep { ! /^\s*$/ } CGI::param('mediatype');
1931 $ret{mediatypes} = \@media;
1932 my $str = $self->dbh_join(@media);
1933 $limit .= "AND Media.MediaType IN ($str) ";
1938 my $client = CGI::param('client');
1939 $ret{client} = $client;
1940 $client = $self->dbh_join($client);
1941 $limit .= "AND Client.Name = $client ";
1945 my $level = CGI::param('level') || '';
1946 if ($level =~ /^(\w)$/) {
1948 $limit .= "AND Job.Level = '$1' ";
1953 my $jobid = CGI::param('jobid') || '';
1955 if ($jobid =~ /^(\d+)$/) {
1957 $limit .= "AND Job.JobId = '$1' ";
1962 my $status = CGI::param('status') || '';
1963 if ($status =~ /^(\w)$/) {
1966 $limit .= "AND Job.JobStatus IN ('f','E') ";
1967 } elsif ($1 eq 'W') {
1968 $limit .= "AND Job.JobStatus = 'T' AND Job.JobErrors > 0 ";
1970 $limit .= "AND Job.JobStatus = '$1' ";
1975 if ($elt{volstatus}) {
1976 my $status = CGI::param('volstatus') || '';
1977 if ($status =~ /^(\w+)$/) {
1979 $limit .= "AND Media.VolStatus = '$1' ";
1983 if ($elt{locations}) {
1984 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1986 $ret{locations} = \@location;
1987 my $str = $self->dbh_join(@location);
1988 $limit .= "AND Location.Location IN ($str) ";
1993 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
1995 $ret{pools} = \@pool;
1996 my $str = $self->dbh_join(@pool);
1997 $limit .= "AND Pool.Name IN ($str) ";
2001 if ($elt{location}) {
2002 my $location = CGI::param('location') || '';
2004 $ret{location} = $location;
2005 $location = $self->dbh_quote($location);
2006 $limit .= "AND Location.Location = $location ";
2011 my $pool = CGI::param('pool') || '';
2014 $pool = $self->dbh_quote($pool);
2015 $limit .= "AND Pool.Name = $pool ";
2019 if ($elt{jobtype}) {
2020 my $jobtype = CGI::param('jobtype') || '';
2021 if ($jobtype =~ /^(\w)$/) {
2023 $limit .= "AND Job.Type = '$1' ";
2027 # fill this only when security is enabled
2028 if ($elt{username} and $self->{info}->{enable_security}) {
2029 my $u = $self->dbh_quote($self->{loginname});
2030 $ret{username}=$self->{loginname};
2031 $limit .= "AND bweb_user.username = $u ";
2034 return ($limit, %ret);
2045 my ($self, %arg) = @_ ;
2047 $arg{order} = ' Job.JobId DESC ';
2049 my ($limit, $label) = $self->get_limit(%arg);
2050 my ($where, undef) = $self->get_param('clients',
2060 if (CGI::param('client_group')) {
2062 LEFT JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2063 LEFT JOIN client_group USING (client_group_id)
2068 SELECT Job.JobId AS jobid,
2069 Client.Name AS client,
2070 FileSet.FileSet AS fileset,
2071 Job.Name AS jobname,
2073 StartTime AS starttime,
2075 Pool.Name AS poolname,
2076 JobFiles AS jobfiles,
2077 JobBytes AS jobbytes,
2078 JobStatus AS jobstatus,
2079 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2080 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2083 JobErrors AS joberrors
2086 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2087 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2089 WHERE Client.ClientId=Job.ClientId
2090 AND Job.JobStatus NOT IN ('R', 'C')
2095 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2097 $self->display({ Filter => $label,
2101 sort { $a->{jobid} <=> $b->{jobid} }
2108 # display job informations
2109 sub display_job_zoom
2111 my ($self, $jobid) = @_ ;
2113 $jobid = $self->dbh_quote($jobid);
2116 SELECT DISTINCT Job.JobId AS jobid,
2117 Client.Name AS client,
2118 Job.Name AS jobname,
2119 FileSet.FileSet AS fileset,
2121 Pool.Name AS poolname,
2122 StartTime AS starttime,
2123 JobFiles AS jobfiles,
2124 JobBytes AS jobbytes,
2125 JobStatus AS jobstatus,
2126 JobErrors AS joberrors,
2127 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2128 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
2131 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2132 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2133 WHERE Client.ClientId=Job.ClientId
2134 AND Job.JobId = $jobid
2137 my $row = $self->dbh_selectrow_hashref($query) ;
2139 # display all volumes associate with this job
2141 SELECT Media.VolumeName as volumename
2142 FROM Job,Media,JobMedia
2143 WHERE Job.JobId = $jobid
2144 AND JobMedia.JobId=Job.JobId
2145 AND JobMedia.MediaId=Media.MediaId
2148 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2150 $row->{volumes} = [ values %$all ] ;
2152 $self->display($row, "display_job_zoom.tpl");
2155 sub display_job_group
2157 my ($self, %arg) = @_;
2159 my ($limit, $label) = $self->get_limit(groupby => 'client_group_name', %arg);
2161 my ($where, undef) = $self->get_param('client_groups',
2167 SELECT client_group_name AS client_group_name,
2168 COALESCE(jobok.jobfiles,0) + COALESCE(joberr.jobfiles,0) AS jobfiles,
2169 COALESCE(jobok.jobbytes,0) + COALESCE(joberr.jobbytes,0) AS jobbytes,
2170 COALESCE(jobok.joberrors,0) + COALESCE(joberr.joberrors,0) AS joberrors,
2171 COALESCE(jobok.nbjobs,0) AS nbjobok,
2172 COALESCE(joberr.nbjobs,0) AS nbjoberr,
2173 COALESCE(jobok.duration, '0:0:0') AS duration
2175 FROM client_group LEFT JOIN (
2176 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2177 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2178 SUM(JobErrors) AS joberrors,
2179 SUM($self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2180 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)))
2183 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2184 JOIN client_group USING (client_group_id)
2186 WHERE JobStatus = 'T'
2189 ) AS jobok USING (client_group_name) LEFT JOIN
2192 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2193 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2194 SUM(JobErrors) AS joberrors
2195 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2196 JOIN client_group USING (client_group_id)
2198 WHERE JobStatus IN ('f','E', 'A')
2201 ) AS joberr USING (client_group_name)
2205 my $all = $self->dbh_selectall_hashref($query, 'client_group_name');
2207 my $rep = { groups => [ values %$all ], age => $arg{age}, filter => $label };
2210 $self->display($rep, "display_job_group.tpl");
2215 my ($self, %arg) = @_ ;
2217 my ($limit, $label) = $self->get_limit(%arg);
2218 my ($where, %elt) = $self->get_param('pools',
2223 my $arg = $self->get_form('jmedias', 'qre_media', 'expired');
2225 if ($arg->{jmedias}) {
2226 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2228 if ($arg->{qre_media}) {
2229 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2231 if ($arg->{expired}) {
2233 AND VolStatus = 'Full'
2234 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2235 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2236 ) < NOW() " . $where ;
2240 SELECT Media.VolumeName AS volumename,
2241 Media.VolBytes AS volbytes,
2242 Media.VolStatus AS volstatus,
2243 Media.MediaType AS mediatype,
2244 Media.InChanger AS online,
2245 Media.LastWritten AS lastwritten,
2246 Location.Location AS location,
2247 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2248 Pool.Name AS poolname,
2249 $self->{sql}->{FROM_UNIXTIME}(
2250 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2251 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2254 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2255 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2256 Media.MediaType AS MediaType
2258 WHERE Media.VolStatus = 'Full'
2259 GROUP BY Media.MediaType
2260 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2262 WHERE Media.PoolId=Pool.PoolId
2267 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2269 $self->display({ ID => $cur_id++,
2271 Location => $elt{location},
2272 Media => [ values %$all ],
2274 "display_media.tpl");
2277 sub display_allmedia
2281 my $pool = $self->get_form('db_pools');
2283 foreach my $name (@{ $pool->{db_pools} }) {
2284 CGI::param('pool', $name->{name});
2285 $self->display_media();
2289 sub display_media_zoom
2293 my $media = $self->get_form('jmedias');
2295 unless ($media->{jmedias}) {
2296 return $self->error("Can't get media selection");
2300 SELECT InChanger AS online,
2301 Media.Enabled AS enabled,
2302 VolBytes AS nb_bytes,
2303 VolumeName AS volumename,
2304 VolStatus AS volstatus,
2305 VolMounts AS nb_mounts,
2306 Media.VolUseDuration AS voluseduration,
2307 Media.MaxVolJobs AS maxvoljobs,
2308 Media.MaxVolFiles AS maxvolfiles,
2309 Media.MaxVolBytes AS maxvolbytes,
2310 VolErrors AS nb_errors,
2311 Pool.Name AS poolname,
2312 Location.Location AS location,
2313 Media.Recycle AS recycle,
2314 Media.VolRetention AS volretention,
2315 Media.LastWritten AS lastwritten,
2316 Media.VolReadTime/1000000 AS volreadtime,
2317 Media.VolWriteTime/1000000 AS volwritetime,
2318 Media.RecycleCount AS recyclecount,
2319 Media.Comment AS comment,
2320 $self->{sql}->{FROM_UNIXTIME}(
2321 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2322 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2325 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2326 WHERE Pool.PoolId = Media.PoolId
2327 AND VolumeName IN ($media->{jmedias})
2330 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2332 foreach my $media (values %$all) {
2333 my $mq = $self->dbh_quote($media->{volumename});
2336 SELECT DISTINCT Job.JobId AS jobid,
2338 Job.StartTime AS starttime,
2341 Job.JobFiles AS files,
2342 Job.JobBytes AS bytes,
2343 Job.jobstatus AS status
2344 FROM Media,JobMedia,Job
2345 WHERE Media.VolumeName=$mq
2346 AND Media.MediaId=JobMedia.MediaId
2347 AND JobMedia.JobId=Job.JobId
2350 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2353 SELECT LocationLog.Date AS date,
2354 Location.Location AS location,
2355 LocationLog.Comment AS comment
2356 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2357 WHERE Media.MediaId = LocationLog.MediaId
2358 AND Media.VolumeName = $mq
2362 my $log = $self->dbh_selectall_arrayref($query) ;
2364 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2367 $self->display({ jobs => [ values %$jobs ],
2368 LocationLog => $logtxt,
2370 "display_media_zoom.tpl");
2378 my $loc = $self->get_form('qlocation');
2379 unless ($loc->{qlocation}) {
2380 return $self->error("Can't get location");
2384 SELECT Location.Location AS location,
2385 Location.Cost AS cost,
2386 Location.Enabled AS enabled
2388 WHERE Location.Location = $loc->{qlocation}
2391 my $row = $self->dbh_selectrow_hashref($query);
2393 $self->display({ ID => $cur_id++,
2394 %$row }, "location_edit.tpl") ;
2402 my $arg = $self->get_form(qw/qlocation qnewlocation cost/) ;
2403 unless ($arg->{qlocation}) {
2404 return $self->error("Can't get location");
2406 unless ($arg->{qnewlocation}) {
2407 return $self->error("Can't get new location name");
2409 unless ($arg->{cost}) {
2410 return $self->error("Can't get new cost");
2413 my $enabled = CGI::param('enabled') || '';
2414 $enabled = $enabled?1:0;
2417 UPDATE Location SET Cost = $arg->{cost},
2418 Location = $arg->{qnewlocation},
2420 WHERE Location.Location = $arg->{qlocation}
2423 $self->dbh_do($query);
2425 $self->location_display();
2431 my $arg = $self->get_form(qw/qlocation/) ;
2433 unless ($arg->{qlocation}) {
2434 return $self->error("Can't get location");
2438 SELECT count(Media.MediaId) AS nb
2439 FROM Media INNER JOIN Location USING (LocationID)
2440 WHERE Location = $arg->{qlocation}
2443 my $res = $self->dbh_selectrow_hashref($query);
2446 return $self->error("Sorry, the location must be empty");
2450 DELETE FROM Location WHERE Location = $arg->{qlocation} LIMIT 1
2453 $self->dbh_do($query);
2455 $self->location_display();
2462 my $arg = $self->get_form(qw/qlocation cost/) ;
2464 unless ($arg->{qlocation}) {
2465 $self->display({}, "location_add.tpl");
2468 unless ($arg->{cost}) {
2469 return $self->error("Can't get new cost");
2472 my $enabled = CGI::param('enabled') || '';
2473 $enabled = $enabled?1:0;
2476 INSERT INTO Location (Location, Cost, Enabled)
2477 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2480 $self->dbh_do($query);
2482 $self->location_display();
2485 sub location_display
2490 SELECT Location.Location AS location,
2491 Location.Cost AS cost,
2492 Location.Enabled AS enabled,
2493 (SELECT count(Media.MediaId)
2495 WHERE Media.LocationId = Location.LocationId
2500 my $location = $self->dbh_selectall_hashref($query, 'location');
2502 $self->display({ ID => $cur_id++,
2503 Locations => [ values %$location ] },
2504 "display_location.tpl");
2511 my $media = $self->get_selected_media_location();
2516 my $arg = $self->get_form('db_locations', 'qnewlocation');
2518 $self->display({ email => $self->{info}->{email_media},
2520 media => [ values %$media ],
2522 "update_location.tpl");
2525 ###########################################################
2531 my $grp = $self->get_form(qw/qclient_group db_clients/);
2534 unless ($grp->{qclient_group}) {
2535 return $self->error("Can't get group");
2540 FROM Client JOIN client_group_member using (clientid)
2541 JOIN client_group using (client_group_id)
2542 WHERE client_group_name = $grp->{qclient_group}
2545 my $row = $self->dbh_selectall_hashref($query, "name");
2547 $self->display({ ID => $cur_id++,
2548 client_group => $grp->{qclient_group},
2550 client_group_member => [ values %$row]},
2558 my $arg = $self->get_form(qw/qclient_group jclients qnewgroup/);
2559 unless ($arg->{qclient_group}) {
2560 return $self->error("Can't get groups");
2563 $self->{dbh}->begin_work();
2566 DELETE FROM client_group_member
2567 WHERE client_group_id IN
2568 (SELECT client_group_id
2570 WHERE client_group_name = $arg->{qclient_group})
2572 $self->dbh_do($query);
2575 INSERT INTO client_group_member (clientid, client_group_id)
2577 (SELECT client_group_id
2579 WHERE client_group_name = $arg->{qclient_group})
2580 FROM Client WHERE Name IN ($arg->{jclients})
2583 $self->dbh_do($query);
2585 if ($arg->{qclient_group} ne $arg->{qnewgroup}) {
2588 SET client_group_name = $arg->{qnewgroup}
2589 WHERE client_group_name = $arg->{qclient_group}
2592 $self->dbh_do($query);
2595 $self->{dbh}->commit() or $self->error($self->{dbh}->errstr);
2597 $self->display_groups();
2603 my $arg = $self->get_form(qw/qclient_group/);
2605 unless ($arg->{qclient_group}) {
2606 return $self->error("Can't get groups");
2609 $self->{dbh}->begin_work();
2612 DELETE FROM client_group_member
2613 WHERE client_group_id IN
2614 (SELECT client_group_id
2616 WHERE client_group_name = $arg->{qclient_group});
2618 DELETE FROM client_group
2619 WHERE client_group_name = $arg->{qclient_group};
2621 $self->dbh_do($query);
2623 $self->{dbh}->commit();
2625 $self->display_groups();
2632 my $arg = $self->get_form(qw/qclient_group/) ;
2634 unless ($arg->{qclient_group}) {
2635 $self->display({}, "groups_add.tpl");
2640 INSERT INTO client_group (client_group_name)
2641 VALUES ($arg->{qclient_group})
2644 $self->dbh_do($query);
2646 $self->display_groups();
2653 my $arg = $self->get_form(qw/db_client_groups/) ;
2655 if ($self->{dbh}->errstr) {
2656 return $self->error("Can't use groups with bweb, read INSTALL to enable them");
2661 $self->display({ ID => $cur_id++,
2663 "display_groups.tpl");
2666 ###########################################################
2669 # TODO: avoir un mode qui coupe le programme avec une page d'erreur
2670 # we can also get all security and fill {security} hash
2673 my ($self, $action) = @_;
2674 # is security enabled in configuration ?
2675 if (not $self->{info}->{enable_security}) {
2678 # admin is a special user that can do everything
2679 if ($self->{loginname} eq 'admin') {
2683 if (!$self->{loginname}) {
2684 $self->error("Can't do $action, your are not logged. " .
2685 "Check security with your administrator");
2686 $self->display_end();
2690 if ($self->{security}->{$action}) {
2693 my ($u, $r) = ($self->dbh_quote($self->{loginname}),
2694 $self->dbh_quote($action));
2696 SELECT 1, username, rolename
2698 JOIN bweb_role_member USING (userid)
2699 JOIN bweb_role USING (roleid)
2704 my $row = $self->dbh_selectrow_hashref($query);
2705 # do cache with this role
2707 $self->error("$u sorry, but this action ($action) is not permited. " .
2708 "Check security with your administrator");
2709 $self->display_end();
2712 $self->{security}->{$row->{rolename}} = 1;
2715 # role and username have to be quoted before
2716 # role and username can be a quoted list
2719 my ($self, $role, $username) = @_;
2720 $self->can_do("user_mgnt");
2722 my $nb = $self->dbh_do("
2723 DELETE FROM bweb_role_member
2724 WHERE roleid = (SELECT roleid FROM bweb_role
2725 WHERE rolename IN ($role))
2726 AND userid = (SELECT userid FROM bweb_user
2727 WHERE username IN ($username))");
2731 # role and username have to be quoted before
2732 # role and username can be a quoted list
2735 my ($self, $role, $username) = @_;
2736 $self->can_do("user_mgnt");
2738 my $nb = $self->dbh_do("
2739 INSERT INTO bweb_role_member (roleid, userid)
2740 SELECT roleid, userid FROM bweb_role, bweb_user
2741 WHERE rolename IN ($role)
2742 AND username IN ($username)
2747 # role and username have to be quoted before
2748 # role and username can be a quoted list
2751 my ($self, $copy, $user) = @_;
2752 $self->can_do("user_mgnt");
2754 my $nb = $self->dbh_do("
2755 INSERT INTO bweb_role_member (roleid, userid)
2756 SELECT roleid, a.userid
2757 FROM bweb_user AS a, bweb_role_member
2758 JOIN bweb_user USING (userid)
2759 WHERE bweb_user.username = $copy
2760 AND a.username = $user");
2764 # username can be a join quoted list of usernames
2767 my ($self, $username) = @_;
2768 $self->can_do("user_mgnt");
2771 DELETE FROM bweb_role_member
2775 WHERE username in ($username)
2782 $self->can_do("user_mgnt");
2784 my $arg = $self->get_form(qw/jusernames/);
2786 unless ($arg->{jusernames}) {
2787 return $self->error("Can't get user");
2790 $self->{dbh}->begin_work();
2792 $self->revoke_all($arg->{jusernames});
2793 $self->dbh_do("DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2795 $self->{dbh}->commit();
2797 $self->display_users();
2803 $self->can_do("user_mgnt");
2805 # we don't quote username directly to check that it is conform
2806 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username/) ;
2808 if (not $arg->{qcreate}) {
2809 $arg = $self->get_form(qw/db_roles db_usernames/);
2810 $self->display($arg, "display_user.tpl");
2814 my $u = $self->dbh_quote($arg->{username});
2816 if (!$arg->{qpasswd}) {
2817 $arg->{qpasswd} = "''";
2819 if (!$arg->{qcomment}) {
2820 $arg->{qcomment} = "''";
2823 # will fail if user already exists
2825 UPDATE bweb_user SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment}
2826 WHERE username = $u")
2829 INSERT INTO bweb_user (username, passwd, comment)
2830 VALUES ($u, $arg->{qpasswd}, $arg->{qcomment})");
2832 $self->{dbh}->begin_work();
2834 $self->revoke_all($u);
2836 if ($arg->{qcopy_username}) {
2837 $self->grant_like($arg->{qcopy_username}, $u);
2839 $self->grant($arg->{jrolenames}, $u);
2842 $self->{dbh}->commit();
2844 $self->display_users();
2847 # TODO: we miss a matrix with all user/roles
2851 $self->can_do("user_mgnt");
2853 my $arg = $self->get_form(qw/db_usernames/) ;
2855 if ($self->{dbh}->errstr) {
2856 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2859 $self->display({ ID => $cur_id++,
2861 "display_users.tpl");
2867 $self->can_do("user_mgnt");
2869 my $arg = $self->get_form(qw/username db_usernames/);
2870 my $user = $self->dbh_quote($arg->{username});
2872 my $userp = $self->dbh_selectrow_hashref("
2873 SELECT username, passwd, comment
2875 WHERE username = $user
2879 return $self->error("Can't find $user in catalog");
2883 #------------+--------
2888 my $role = $self->dbh_selectall_hashref("
2889 SELECT rolename, temp.userid
2891 LEFT JOIN (SELECT roleid, userid
2892 FROM bweb_user JOIN bweb_role_member USING (userid)
2893 WHERE username = $user) AS temp USING (roleid)
2898 db_usernames => $arg->{db_usernames},
2899 username => $userp->{username},
2900 comment => $userp->{comment},
2901 passwd => $userp->{passwd},
2902 db_roles => [ values %$role],
2903 }, "display_user.tpl");
2907 ###########################################################
2909 sub get_media_max_size
2911 my ($self, $type) = @_;
2913 "SELECT avg(VolBytes) AS size
2915 WHERE Media.VolStatus = 'Full'
2916 AND Media.MediaType = '$type'
2919 my $res = $self->selectrow_hashref($query);
2922 return $res->{size};
2932 my $media = $self->get_form('qmedia');
2934 unless ($media->{qmedia}) {
2935 return $self->error("Can't get media");
2939 SELECT Media.Slot AS slot,
2940 PoolMedia.Name AS poolname,
2941 Media.VolStatus AS volstatus,
2942 Media.InChanger AS inchanger,
2943 Location.Location AS location,
2944 Media.VolumeName AS volumename,
2945 Media.MaxVolBytes AS maxvolbytes,
2946 Media.MaxVolJobs AS maxvoljobs,
2947 Media.MaxVolFiles AS maxvolfiles,
2948 Media.VolUseDuration AS voluseduration,
2949 Media.VolRetention AS volretention,
2950 Media.Comment AS comment,
2951 PoolRecycle.Name AS poolrecycle,
2952 Media.Enabled AS enabled
2954 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
2955 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
2956 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2958 WHERE Media.VolumeName = $media->{qmedia}
2961 my $row = $self->dbh_selectrow_hashref($query);
2962 $row->{volretention} = human_sec($row->{volretention});
2963 $row->{voluseduration} = human_sec($row->{voluseduration});
2964 $row->{enabled} = human_enabled($row->{enabled});
2966 my $elt = $self->get_form(qw/db_pools db_locations/);
2971 }, "update_media.tpl");
2978 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
2980 unless ($arg->{jmedias}) {
2981 return $self->error("Can't get selected media");
2984 unless ($arg->{qnewlocation}) {
2985 return $self->error("Can't get new location");
2990 SET LocationId = (SELECT LocationId
2992 WHERE Location = $arg->{qnewlocation})
2993 WHERE Media.VolumeName IN ($arg->{jmedias})
2996 my $nb = $self->dbh_do($query);
2998 print "$nb media updated, you may have to update your autochanger.";
3000 $self->display_media();
3007 my $media = $self->get_selected_media_location();
3009 return $self->error("Can't get media selection");
3011 my $newloc = CGI::param('newlocation');
3013 my $user = CGI::param('user') || 'unknown';
3014 my $comm = CGI::param('comment') || '';
3015 $comm = $self->dbh_quote("$user: $comm");
3017 my $arg = $self->get_form('enabled');
3018 my $en = human_enabled($arg->{enabled});
3019 my $b = $self->get_bconsole();
3022 foreach my $vol (keys %$media) {
3024 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3026 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3027 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3028 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3031 $self->dbh_do($query);
3032 $self->debug($query);
3033 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3038 $q->param('action', 'update_location');
3039 my $url = $q->url(-full => 1, -query=>1);
3041 $self->display({ email => $self->{info}->{email_media},
3043 newlocation => $newloc,
3044 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3045 media => [ values %$media ],
3047 "change_location.tpl");
3051 sub display_client_stats
3053 my ($self, %arg) = @_ ;
3055 my $client = $self->dbh_quote($arg{clientname});
3057 my ($limit, $label) = $self->get_limit(%arg);
3061 count(Job.JobId) AS nb_jobs,
3062 sum(Job.JobBytes) AS nb_bytes,
3063 sum(Job.JobErrors) AS nb_err,
3064 sum(Job.JobFiles) AS nb_files,
3065 Client.Name AS clientname
3066 FROM Job JOIN Client USING (ClientId)
3068 Client.Name = $client
3070 GROUP BY Client.Name
3073 my $row = $self->dbh_selectrow_hashref($query);
3075 $row->{ID} = $cur_id++;
3076 $row->{label} = $label;
3077 $row->{grapharg} = "client";
3079 $self->display($row, "display_client_stats.tpl");
3083 sub display_group_stats
3085 my ($self, %arg) = @_ ;
3087 my $carg = $self->get_form(qw/qclient_group/);
3089 unless ($carg->{qclient_group}) {
3090 return $self->error("Can't get group");
3093 my ($limit, $label) = $self->get_limit(%arg);
3097 count(Job.JobId) AS nb_jobs,
3098 sum(Job.JobBytes) AS nb_bytes,
3099 sum(Job.JobErrors) AS nb_err,
3100 sum(Job.JobFiles) AS nb_files,
3101 client_group.client_group_name AS clientname
3102 FROM Job JOIN Client USING (ClientId)
3103 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3104 JOIN client_group USING (client_group_id)
3106 client_group.client_group_name = $carg->{qclient_group}
3108 GROUP BY client_group.client_group_name
3111 my $row = $self->dbh_selectrow_hashref($query);
3113 $row->{ID} = $cur_id++;
3114 $row->{label} = $label;
3115 $row->{grapharg} = "client_group";
3117 $self->display($row, "display_client_stats.tpl");
3120 # poolname can be undef
3123 my ($self, $poolname) = @_ ;
3127 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3128 if ($arg->{jmediatypes}) {
3129 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3130 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3133 # TODO : afficher les tailles et les dates
3136 SELECT subq.volmax AS volmax,
3137 subq.volnum AS volnum,
3138 subq.voltotal AS voltotal,
3140 Pool.Recycle AS recycle,
3141 Pool.VolRetention AS volretention,
3142 Pool.VolUseDuration AS voluseduration,
3143 Pool.MaxVolJobs AS maxvoljobs,
3144 Pool.MaxVolFiles AS maxvolfiles,
3145 Pool.MaxVolBytes AS maxvolbytes,
3146 subq.PoolId AS PoolId,
3147 subq.MediaType AS mediatype,
3148 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3151 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3152 count(Media.MediaId) AS volnum,
3153 sum(Media.VolBytes) AS voltotal,
3154 Media.PoolId AS PoolId,
3155 Media.MediaType AS MediaType
3157 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3158 Media.MediaType AS MediaType
3160 WHERE Media.VolStatus = 'Full'
3161 GROUP BY Media.MediaType
3162 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3163 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3165 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3169 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3172 SELECT Pool.Name AS name,
3173 sum(VolBytes) AS size
3174 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3175 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3179 my $empty = $self->dbh_selectall_hashref($query, 'name');
3181 foreach my $p (values %$all) {
3182 if ($p->{volmax} > 0) { # mysql returns 0.0000
3183 # we remove Recycled/Purged media from pool usage
3184 if (defined $empty->{$p->{name}}) {
3185 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3187 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3189 $p->{poolusage} = 0;
3193 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3195 WHERE PoolId=$p->{poolid}
3196 AND Media.MediaType = '$p->{mediatype}'
3200 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3201 foreach my $t (values %$content) {
3202 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3207 $self->display({ ID => $cur_id++,
3208 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3209 Pools => [ values %$all ]},
3210 "display_pool.tpl");
3213 sub display_running_job
3217 my $arg = $self->get_form('client', 'jobid');
3219 if (!$arg->{client} and $arg->{jobid}) {
3222 SELECT Client.Name AS name
3223 FROM Job INNER JOIN Client USING (ClientId)
3224 WHERE Job.JobId = $arg->{jobid}
3227 my $row = $self->dbh_selectrow_hashref($query);
3230 $arg->{client} = $row->{name};
3231 CGI::param('client', $arg->{client});
3235 if ($arg->{client}) {
3236 my $cli = new Bweb::Client(name => $arg->{client});
3237 $cli->display_running_job($self->{info}, $arg->{jobid});
3238 if ($arg->{jobid}) {
3239 $self->get_job_log();
3242 $self->error("Can't get client or jobid");
3246 sub display_running_jobs
3248 my ($self, $display_action) = @_;
3251 SELECT Job.JobId AS jobid,
3252 Job.Name AS jobname,
3254 Job.StartTime AS starttime,
3255 Job.JobFiles AS jobfiles,
3256 Job.JobBytes AS jobbytes,
3257 Job.JobStatus AS jobstatus,
3258 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3259 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3261 Client.Name AS clientname
3262 FROM Job INNER JOIN Client USING (ClientId)
3263 WHERE JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3265 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3267 $self->display({ ID => $cur_id++,
3268 display_action => $display_action,
3269 Jobs => [ values %$all ]},
3270 "running_job.tpl") ;
3273 # return the autochanger list to update
3278 my $arg = $self->get_form('jmedias');
3280 unless ($arg->{jmedias}) {
3281 return $self->error("Can't get media selection");
3285 SELECT Media.VolumeName AS volumename,
3286 Storage.Name AS storage,
3287 Location.Location AS location,
3289 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3290 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3291 WHERE Media.VolumeName IN ($arg->{jmedias})
3292 AND Media.InChanger = 1
3295 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3297 foreach my $vol (values %$all) {
3298 my $a = $self->ach_get($vol->{location});
3300 $ret{$vol->{location}} = 1;
3302 unless ($a->{have_status}) {
3304 $a->{have_status} = 1;
3307 print "eject $vol->{volumename} from $vol->{storage} : ";
3308 if ($a->send_to_io($vol->{slot})) {
3309 print "<img src='/bweb/T.png' alt='ok'><br/>";
3311 print "<img src='/bweb/E.png' alt='err'><br/>";
3321 my ($to, $subject, $content) = (CGI::param('email'),
3322 CGI::param('subject'),
3323 CGI::param('content'));
3324 $to =~ s/[^\w\d\.\@<>,]//;
3325 $subject =~ s/[^\w\d\.\[\]]/ /;
3327 open(MAIL, "|mail -s '$subject' '$to'") ;
3328 print MAIL $content;
3338 my $arg = $self->get_form('jobid', 'client');
3340 print CGI::header('text/brestore');
3341 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3342 print "client=$arg->{client}\n" if ($arg->{client});
3343 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3347 # TODO : move this to Bweb::Autochanger ?
3348 # TODO : make this internal to not eject tape ?
3354 my ($self, $name) = @_;
3357 return $self->error("Can't get your autochanger name ach");
3360 unless ($self->{info}->{ach_list}) {
3361 return $self->error("Could not find any autochanger");
3364 my $a = $self->{info}->{ach_list}->{$name};
3367 $self->error("Can't get your autochanger $name from your ach_list");
3372 $a->{debug} = $self->{debug};
3379 my ($self, $ach) = @_;
3381 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3383 $self->{info}->save();
3391 my $arg = $self->get_form('ach');
3393 or !$self->{info}->{ach_list}
3394 or !$self->{info}->{ach_list}->{$arg->{ach}})
3396 return $self->error("Can't get autochanger name");
3399 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3403 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3405 my $b = $self->get_bconsole();
3407 my @storages = $b->list_storage() ;
3409 $ach->{devices} = [ map { { name => $_ } } @storages ];
3411 $self->display($ach, "ach_add.tpl");
3412 delete $ach->{drives};
3413 delete $ach->{devices};
3420 my $arg = $self->get_form('ach');
3423 or !$self->{info}->{ach_list}
3424 or !$self->{info}->{ach_list}->{$arg->{ach}})
3426 return $self->error("Can't get autochanger name");
3429 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3431 $self->{info}->save();
3432 $self->{info}->view();
3438 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3440 my $b = $self->get_bconsole();
3441 my @storages = $b->list_storage() ;
3443 unless ($arg->{ach}) {
3444 $arg->{devices} = [ map { { name => $_ } } @storages ];
3445 return $self->display($arg, "ach_add.tpl");
3449 foreach my $drive (CGI::param('drives'))
3451 unless (grep(/^$drive$/,@storages)) {
3452 return $self->error("Can't find $drive in storage list");
3455 my $index = CGI::param("index_$drive");
3456 unless (defined $index and $index =~ /^(\d+)$/) {
3457 return $self->error("Can't get $drive index");
3460 $drives[$index] = $drive;
3464 return $self->error("Can't get drives from Autochanger");
3467 my $a = new Bweb::Autochanger(name => $arg->{ach},
3468 precmd => $arg->{precmd},
3469 drive_name => \@drives,
3470 device => $arg->{device},
3471 mtxcmd => $arg->{mtxcmd});
3473 $self->ach_register($a) ;
3475 $self->{info}->view();
3481 my $arg = $self->get_form('jobid');
3483 if ($arg->{jobid}) {
3484 my $b = $self->get_bconsole();
3485 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3489 title => "Delete a job ",
3490 name => "delete jobid=$arg->{jobid}",
3499 my $arg = $self->get_form(qw/media volstatus inchanger pool
3500 slot volretention voluseduration
3501 maxvoljobs maxvolfiles maxvolbytes
3502 qcomment poolrecycle enabled
3505 unless ($arg->{media}) {
3506 return $self->error("Can't find media selection");
3509 my $update = "update volume=$arg->{media} ";
3511 if ($arg->{volstatus}) {
3512 $update .= " volstatus=$arg->{volstatus} ";
3515 if ($arg->{inchanger}) {
3516 $update .= " inchanger=yes " ;
3518 $update .= " slot=$arg->{slot} ";
3521 $update .= " slot=0 inchanger=no ";
3524 if ($arg->{enabled}) {
3525 $update .= " enabled=$arg->{enabled} ";
3529 $update .= " pool=$arg->{pool} " ;
3532 if (defined $arg->{volretention}) {
3533 $update .= " volretention=\"$arg->{volretention}\" " ;
3536 if (defined $arg->{voluseduration}) {
3537 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3540 if (defined $arg->{maxvoljobs}) {
3541 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3544 if (defined $arg->{maxvolfiles}) {
3545 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3548 if (defined $arg->{maxvolbytes}) {
3549 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3552 if (defined $arg->{poolrecycle}) {
3553 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3556 my $b = $self->get_bconsole();
3559 content => $b->send_cmd($update),
3560 title => "Update a volume ",
3566 my $media = $self->dbh_quote($arg->{media});
3568 my $loc = CGI::param('location') || '';
3570 $loc = $self->dbh_quote($loc); # is checked by db
3571 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3573 if (!$arg->{qcomment}) {
3574 $arg->{qcomment} = "''";
3576 push @q, "Comment=$arg->{qcomment}";
3581 SET " . join (',', @q) . "
3582 WHERE Media.VolumeName = $media
3584 $self->dbh_do($query);
3586 $self->update_media();
3593 my $ach = CGI::param('ach') ;
3594 $ach = $self->ach_get($ach);
3596 return $self->error("Bad autochanger name");
3600 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3601 $b->update_slots($ach->{name});
3609 my $arg = $self->get_form('jobid', 'limit', 'offset');
3610 unless ($arg->{jobid}) {
3611 return $self->error("Can't get jobid");
3614 if ($arg->{limit} == 100) {
3615 $arg->{limit} = 1000;
3618 my $t = CGI::param('time') || $self->{info}->{display_log_time} || '';
3620 # display only Error and Warning messages
3622 if (CGI::param('error')) {
3623 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3627 SELECT Job.Name as name, Client.Name as clientname
3628 FROM Job INNER JOIN Client ON (Job.ClientId = Client.ClientId)
3629 WHERE JobId = $arg->{jobid}
3632 my $row = $self->dbh_selectrow_hashref($query);
3635 return $self->error("Can't find $arg->{jobid} in catalog");
3639 SELECT Time AS time, LogText AS log
3641 WHERE ( Log.JobId = $arg->{jobid}
3642 OR (Log.JobId = 0 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3643 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3648 OFFSET $arg->{offset}
3651 my $log = $self->dbh_selectall_arrayref($query);
3653 return $self->error("Can't get log for jobid $arg->{jobid}");
3659 $logtxt = join("", map { ($_->[0] . ' ' . $_->[1]) } @$log ) ;
3661 $logtxt = join("", map { $_->[1] } @$log ) ;
3664 $self->display({ lines=> $logtxt,
3665 jobid => $arg->{jobid},
3666 name => $row->{name},
3667 client => $row->{clientname},
3668 offset => $arg->{offset},
3669 limit => $arg->{limit},
3670 }, 'display_log.tpl');
3678 my $arg = $self->get_form('ach', 'slots', 'drive');
3680 unless ($arg->{ach}) {
3681 return $self->error("Can't find autochanger name");
3684 my $a = $self->ach_get($arg->{ach});
3686 return $self->error("Can't find autochanger name in configuration");
3689 my $storage = $a->get_drive_name($arg->{drive});
3691 return $self->error("Can't get your drive name");
3697 if ($arg->{slots}) {
3698 $slots = join(",", @{ $arg->{slots} });
3699 $slots_sql = " AND Slot IN ($slots) ";
3700 $t += 60*scalar( @{ $arg->{slots} }) ;
3703 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3704 print "<h1>This command can take long time, be patient...</h1>";
3706 $b->label_barcodes(storage => $storage,
3707 drive => $arg->{drive},
3715 SET LocationId = (SELECT LocationId
3717 WHERE Location = '$arg->{ach}')
3719 WHERE (LocationId = 0 OR LocationId IS NULL)
3729 my @volume = CGI::param('media');
3732 return $self->error("Can't get media selection");
3735 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3737 foreach my $v (@volume) {
3739 content => $b->purge_volume($v),
3740 title => "Purge media",
3741 name => "purge volume=$v",
3751 my @volume = CGI::param('media');
3753 return $self->error("Can't get media selection");
3756 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3758 foreach my $v (@volume) {
3760 content => $b->prune_volume($v),
3761 title => "Prune volume",
3762 name => "prune volume=$v",
3772 my $arg = $self->get_form('jobid');
3773 unless ($arg->{jobid}) {
3774 return $self->error("Can't get jobid");
3777 my $b = $self->get_bconsole();
3779 content => $b->cancel($arg->{jobid}),
3780 title => "Cancel job",
3781 name => "cancel jobid=$arg->{jobid}",
3787 # Warning, we display current fileset
3790 my $arg = $self->get_form('fileset');
3792 if ($arg->{fileset}) {
3793 my $b = $self->get_bconsole();
3794 my $ret = $b->get_fileset($arg->{fileset});
3795 $self->display({ fileset => $arg->{fileset},
3797 }, "fileset_view.tpl");
3799 $self->error("Can't get fileset name");
3803 sub director_show_sched
3807 my $arg = $self->get_form('days');
3809 my $b = $self->get_bconsole();
3810 my $ret = $b->director_get_sched( $arg->{days} );
3815 }, "scheduled_job.tpl");
3818 sub enable_disable_job
3820 my ($self, $what) = @_ ;
3822 my $name = CGI::param('job') || '';
3823 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3824 return $self->error("Can't find job name");
3827 my $b = $self->get_bconsole();
3837 content => $b->send_cmd("$cmd job=\"$name\""),
3838 title => "$cmd $name",
3839 name => "$cmd job=\"$name\"",
3846 return new Bconsole(pref => $self->{info});
3852 my $b = $self->get_bconsole();
3854 my $joblist = [ map { { name => $_ } } $b->list_job() ];
3856 $self->display({ Jobs => $joblist }, "run_job.tpl");
3861 my ($self, $ouput) = @_;
3864 foreach my $l (split(/\r\n/, $ouput)) {
3865 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
3871 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
3877 foreach my $k (keys %arg) {
3878 $lowcase{lc($k)} = $arg{$k} ;
3887 my $b = $self->get_bconsole();
3889 my $job = CGI::param('job') || '';
3891 # we take informations from director, and we overwrite with user wish
3892 my $info = $b->send_cmd("show job=\"$job\"");
3893 my $attr = $self->run_parse_job($info);
3895 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
3896 my %job_opt = (%$attr, %$arg);
3898 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3900 my $pools = [ map { { name => $_ } } $b->list_pool() ];
3901 my $clients = [ map { { name => $_ } }$b->list_client()];
3902 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
3903 my $storages= [ map { { name => $_ } }$b->list_storage()];
3908 clients => $clients,
3909 filesets => $filesets,
3910 storages => $storages,
3912 }, "run_job_mod.tpl");
3918 my $b = $self->get_bconsole();
3920 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3930 my $b = $self->get_bconsole();
3932 # TODO: check input (don't use pool, level)
3934 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
3935 my $job = CGI::param('job') || '';
3936 my $storage = CGI::param('storage') || '';
3938 my $jobid = $b->run(job => $job,
3939 client => $arg->{client},
3940 priority => $arg->{priority},
3941 level => $arg->{level},
3942 storage => $storage,
3943 pool => $arg->{pool},
3944 fileset => $arg->{fileset},
3945 when => $arg->{when},
3948 print $jobid, $b->{error};
3950 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";