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 FROM bweb_user AS a, bweb_role_member JOIN bweb_user USING (userid)
2757 WHERE bweb_user.username = $copy
2758 AND a.username = $user");
2762 # username can be a join quoted list of usernames
2765 my ($self, $username) = @_;
2766 $self->can_do("user_mgnt");
2769 DELETE FROM bweb_role_member
2773 WHERE username in ($username)
2780 $self->can_do("user_mgnt");
2782 my $arg = $self->get_form(qw/jusernames/);
2784 unless ($arg->{jusernames}) {
2785 return $self->error("Can't get user");
2788 $self->{dbh}->begin_work();
2790 $self->revoke_all($arg->{jusernames});
2791 $self->dbh_do("DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2793 $self->{dbh}->commit();
2795 $self->display_users();
2801 $self->can_do("user_mgnt");
2803 # we don't quote username directly to check that it is conform
2804 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username/) ;
2806 if (not $arg->{qcreate}) {
2807 $arg = $self->get_form(qw/db_roles db_usernames/);
2808 $self->display($arg, "display_user.tpl");
2812 my $u = $self->dbh_quote($arg->{username});
2814 if (!$arg->{qpasswd}) {
2815 $arg->{qpasswd} = "''";
2817 if (!$arg->{qcomment}) {
2818 $arg->{qcomment} = "''";
2821 # will fail if user already exists
2823 UPDATE bweb_user SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment}
2824 WHERE username = $u")
2827 INSERT INTO bweb_user (username, passwd, comment)
2828 VALUES ($u, $arg->{qpasswd}, $arg->{qcomment})");
2830 $self->{dbh}->begin_work();
2832 $self->revoke_all($u);
2834 if ($arg->{qcopy_username}) {
2835 $self->grant_like($arg->{qcopy_username}, $u);
2837 $self->grant($arg->{jrolenames}, $u);
2840 $self->{dbh}->commit();
2842 $self->display_users();
2845 # TODO: we miss a matrix with all user/roles
2849 $self->can_do("user_mgnt");
2851 my $arg = $self->get_form(qw/db_usernames/) ;
2853 if ($self->{dbh}->errstr) {
2854 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2857 $self->display({ ID => $cur_id++,
2859 "display_users.tpl");
2865 $self->can_do("user_mgnt");
2867 my $arg = $self->get_form(qw/username db_usernames/);
2868 my $user = $self->dbh_quote($arg->{username});
2870 my $userp = $self->dbh_selectrow_hashref("
2871 SELECT username, passwd, comment
2873 WHERE username = $user
2877 return $self->error("Can't find $user in catalog");
2881 #------------+--------
2886 my $role = $self->dbh_selectall_hashref("
2887 SELECT rolename, temp.userid
2889 LEFT JOIN (SELECT roleid, userid
2890 FROM bweb_user JOIN bweb_role_member USING (userid)
2891 WHERE username = $user) AS temp USING (roleid)
2896 db_usernames => $arg->{db_usernames},
2897 username => $userp->{username},
2898 comment => $userp->{comment},
2899 passwd => $userp->{passwd},
2900 db_roles => [ values %$role],
2901 }, "display_user.tpl");
2905 ###########################################################
2907 sub get_media_max_size
2909 my ($self, $type) = @_;
2911 "SELECT avg(VolBytes) AS size
2913 WHERE Media.VolStatus = 'Full'
2914 AND Media.MediaType = '$type'
2917 my $res = $self->selectrow_hashref($query);
2920 return $res->{size};
2930 my $media = $self->get_form('qmedia');
2932 unless ($media->{qmedia}) {
2933 return $self->error("Can't get media");
2937 SELECT Media.Slot AS slot,
2938 PoolMedia.Name AS poolname,
2939 Media.VolStatus AS volstatus,
2940 Media.InChanger AS inchanger,
2941 Location.Location AS location,
2942 Media.VolumeName AS volumename,
2943 Media.MaxVolBytes AS maxvolbytes,
2944 Media.MaxVolJobs AS maxvoljobs,
2945 Media.MaxVolFiles AS maxvolfiles,
2946 Media.VolUseDuration AS voluseduration,
2947 Media.VolRetention AS volretention,
2948 Media.Comment AS comment,
2949 PoolRecycle.Name AS poolrecycle,
2950 Media.Enabled AS enabled
2952 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
2953 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
2954 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2956 WHERE Media.VolumeName = $media->{qmedia}
2959 my $row = $self->dbh_selectrow_hashref($query);
2960 $row->{volretention} = human_sec($row->{volretention});
2961 $row->{voluseduration} = human_sec($row->{voluseduration});
2962 $row->{enabled} = human_enabled($row->{enabled});
2964 my $elt = $self->get_form(qw/db_pools db_locations/);
2969 }, "update_media.tpl");
2976 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
2978 unless ($arg->{jmedias}) {
2979 return $self->error("Can't get selected media");
2982 unless ($arg->{qnewlocation}) {
2983 return $self->error("Can't get new location");
2988 SET LocationId = (SELECT LocationId
2990 WHERE Location = $arg->{qnewlocation})
2991 WHERE Media.VolumeName IN ($arg->{jmedias})
2994 my $nb = $self->dbh_do($query);
2996 print "$nb media updated, you may have to update your autochanger.";
2998 $self->display_media();
3005 my $media = $self->get_selected_media_location();
3007 return $self->error("Can't get media selection");
3009 my $newloc = CGI::param('newlocation');
3011 my $user = CGI::param('user') || 'unknown';
3012 my $comm = CGI::param('comment') || '';
3013 $comm = $self->dbh_quote("$user: $comm");
3015 my $arg = $self->get_form('enabled');
3016 my $en = human_enabled($arg->{enabled});
3017 my $b = $self->get_bconsole();
3020 foreach my $vol (keys %$media) {
3022 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3024 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3025 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3026 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3029 $self->dbh_do($query);
3030 $self->debug($query);
3031 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3036 $q->param('action', 'update_location');
3037 my $url = $q->url(-full => 1, -query=>1);
3039 $self->display({ email => $self->{info}->{email_media},
3041 newlocation => $newloc,
3042 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3043 media => [ values %$media ],
3045 "change_location.tpl");
3049 sub display_client_stats
3051 my ($self, %arg) = @_ ;
3053 my $client = $self->dbh_quote($arg{clientname});
3055 my ($limit, $label) = $self->get_limit(%arg);
3059 count(Job.JobId) AS nb_jobs,
3060 sum(Job.JobBytes) AS nb_bytes,
3061 sum(Job.JobErrors) AS nb_err,
3062 sum(Job.JobFiles) AS nb_files,
3063 Client.Name AS clientname
3064 FROM Job JOIN Client USING (ClientId)
3066 Client.Name = $client
3068 GROUP BY Client.Name
3071 my $row = $self->dbh_selectrow_hashref($query);
3073 $row->{ID} = $cur_id++;
3074 $row->{label} = $label;
3075 $row->{grapharg} = "client";
3077 $self->display($row, "display_client_stats.tpl");
3081 sub display_group_stats
3083 my ($self, %arg) = @_ ;
3085 my $carg = $self->get_form(qw/qclient_group/);
3087 unless ($carg->{qclient_group}) {
3088 return $self->error("Can't get group");
3091 my ($limit, $label) = $self->get_limit(%arg);
3095 count(Job.JobId) AS nb_jobs,
3096 sum(Job.JobBytes) AS nb_bytes,
3097 sum(Job.JobErrors) AS nb_err,
3098 sum(Job.JobFiles) AS nb_files,
3099 client_group.client_group_name AS clientname
3100 FROM Job JOIN Client USING (ClientId)
3101 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3102 JOIN client_group USING (client_group_id)
3104 client_group.client_group_name = $carg->{qclient_group}
3106 GROUP BY client_group.client_group_name
3109 my $row = $self->dbh_selectrow_hashref($query);
3111 $row->{ID} = $cur_id++;
3112 $row->{label} = $label;
3113 $row->{grapharg} = "client_group";
3115 $self->display($row, "display_client_stats.tpl");
3118 # poolname can be undef
3121 my ($self, $poolname) = @_ ;
3125 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3126 if ($arg->{jmediatypes}) {
3127 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3128 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3131 # TODO : afficher les tailles et les dates
3134 SELECT subq.volmax AS volmax,
3135 subq.volnum AS volnum,
3136 subq.voltotal AS voltotal,
3138 Pool.Recycle AS recycle,
3139 Pool.VolRetention AS volretention,
3140 Pool.VolUseDuration AS voluseduration,
3141 Pool.MaxVolJobs AS maxvoljobs,
3142 Pool.MaxVolFiles AS maxvolfiles,
3143 Pool.MaxVolBytes AS maxvolbytes,
3144 subq.PoolId AS PoolId,
3145 subq.MediaType AS mediatype,
3146 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3149 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3150 count(Media.MediaId) AS volnum,
3151 sum(Media.VolBytes) AS voltotal,
3152 Media.PoolId AS PoolId,
3153 Media.MediaType AS MediaType
3155 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3156 Media.MediaType AS MediaType
3158 WHERE Media.VolStatus = 'Full'
3159 GROUP BY Media.MediaType
3160 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3161 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3163 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3167 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3170 SELECT Pool.Name AS name,
3171 sum(VolBytes) AS size
3172 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3173 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3177 my $empty = $self->dbh_selectall_hashref($query, 'name');
3179 foreach my $p (values %$all) {
3180 if ($p->{volmax} > 0) { # mysql returns 0.0000
3181 # we remove Recycled/Purged media from pool usage
3182 if (defined $empty->{$p->{name}}) {
3183 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3185 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3187 $p->{poolusage} = 0;
3191 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3193 WHERE PoolId=$p->{poolid}
3194 AND Media.MediaType = '$p->{mediatype}'
3198 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3199 foreach my $t (values %$content) {
3200 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3205 $self->display({ ID => $cur_id++,
3206 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3207 Pools => [ values %$all ]},
3208 "display_pool.tpl");
3211 sub display_running_job
3215 my $arg = $self->get_form('client', 'jobid');
3217 if (!$arg->{client} and $arg->{jobid}) {
3220 SELECT Client.Name AS name
3221 FROM Job INNER JOIN Client USING (ClientId)
3222 WHERE Job.JobId = $arg->{jobid}
3225 my $row = $self->dbh_selectrow_hashref($query);
3228 $arg->{client} = $row->{name};
3229 CGI::param('client', $arg->{client});
3233 if ($arg->{client}) {
3234 my $cli = new Bweb::Client(name => $arg->{client});
3235 $cli->display_running_job($self->{info}, $arg->{jobid});
3236 if ($arg->{jobid}) {
3237 $self->get_job_log();
3240 $self->error("Can't get client or jobid");
3244 sub display_running_jobs
3246 my ($self, $display_action) = @_;
3249 SELECT Job.JobId AS jobid,
3250 Job.Name AS jobname,
3252 Job.StartTime AS starttime,
3253 Job.JobFiles AS jobfiles,
3254 Job.JobBytes AS jobbytes,
3255 Job.JobStatus AS jobstatus,
3256 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3257 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3259 Client.Name AS clientname
3260 FROM Job INNER JOIN Client USING (ClientId)
3261 WHERE JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3263 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3265 $self->display({ ID => $cur_id++,
3266 display_action => $display_action,
3267 Jobs => [ values %$all ]},
3268 "running_job.tpl") ;
3271 # return the autochanger list to update
3276 my $arg = $self->get_form('jmedias');
3278 unless ($arg->{jmedias}) {
3279 return $self->error("Can't get media selection");
3283 SELECT Media.VolumeName AS volumename,
3284 Storage.Name AS storage,
3285 Location.Location AS location,
3287 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3288 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3289 WHERE Media.VolumeName IN ($arg->{jmedias})
3290 AND Media.InChanger = 1
3293 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3295 foreach my $vol (values %$all) {
3296 my $a = $self->ach_get($vol->{location});
3298 $ret{$vol->{location}} = 1;
3300 unless ($a->{have_status}) {
3302 $a->{have_status} = 1;
3305 print "eject $vol->{volumename} from $vol->{storage} : ";
3306 if ($a->send_to_io($vol->{slot})) {
3307 print "<img src='/bweb/T.png' alt='ok'><br/>";
3309 print "<img src='/bweb/E.png' alt='err'><br/>";
3319 my ($to, $subject, $content) = (CGI::param('email'),
3320 CGI::param('subject'),
3321 CGI::param('content'));
3322 $to =~ s/[^\w\d\.\@<>,]//;
3323 $subject =~ s/[^\w\d\.\[\]]/ /;
3325 open(MAIL, "|mail -s '$subject' '$to'") ;
3326 print MAIL $content;
3336 my $arg = $self->get_form('jobid', 'client');
3338 print CGI::header('text/brestore');
3339 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3340 print "client=$arg->{client}\n" if ($arg->{client});
3341 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3345 # TODO : move this to Bweb::Autochanger ?
3346 # TODO : make this internal to not eject tape ?
3352 my ($self, $name) = @_;
3355 return $self->error("Can't get your autochanger name ach");
3358 unless ($self->{info}->{ach_list}) {
3359 return $self->error("Could not find any autochanger");
3362 my $a = $self->{info}->{ach_list}->{$name};
3365 $self->error("Can't get your autochanger $name from your ach_list");
3370 $a->{debug} = $self->{debug};
3377 my ($self, $ach) = @_;
3379 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3381 $self->{info}->save();
3389 my $arg = $self->get_form('ach');
3391 or !$self->{info}->{ach_list}
3392 or !$self->{info}->{ach_list}->{$arg->{ach}})
3394 return $self->error("Can't get autochanger name");
3397 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3401 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3403 my $b = $self->get_bconsole();
3405 my @storages = $b->list_storage() ;
3407 $ach->{devices} = [ map { { name => $_ } } @storages ];
3409 $self->display($ach, "ach_add.tpl");
3410 delete $ach->{drives};
3411 delete $ach->{devices};
3418 my $arg = $self->get_form('ach');
3421 or !$self->{info}->{ach_list}
3422 or !$self->{info}->{ach_list}->{$arg->{ach}})
3424 return $self->error("Can't get autochanger name");
3427 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3429 $self->{info}->save();
3430 $self->{info}->view();
3436 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3438 my $b = $self->get_bconsole();
3439 my @storages = $b->list_storage() ;
3441 unless ($arg->{ach}) {
3442 $arg->{devices} = [ map { { name => $_ } } @storages ];
3443 return $self->display($arg, "ach_add.tpl");
3447 foreach my $drive (CGI::param('drives'))
3449 unless (grep(/^$drive$/,@storages)) {
3450 return $self->error("Can't find $drive in storage list");
3453 my $index = CGI::param("index_$drive");
3454 unless (defined $index and $index =~ /^(\d+)$/) {
3455 return $self->error("Can't get $drive index");
3458 $drives[$index] = $drive;
3462 return $self->error("Can't get drives from Autochanger");
3465 my $a = new Bweb::Autochanger(name => $arg->{ach},
3466 precmd => $arg->{precmd},
3467 drive_name => \@drives,
3468 device => $arg->{device},
3469 mtxcmd => $arg->{mtxcmd});
3471 $self->ach_register($a) ;
3473 $self->{info}->view();
3479 my $arg = $self->get_form('jobid');
3481 if ($arg->{jobid}) {
3482 my $b = $self->get_bconsole();
3483 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3487 title => "Delete a job ",
3488 name => "delete jobid=$arg->{jobid}",
3497 my $arg = $self->get_form(qw/media volstatus inchanger pool
3498 slot volretention voluseduration
3499 maxvoljobs maxvolfiles maxvolbytes
3500 qcomment poolrecycle enabled
3503 unless ($arg->{media}) {
3504 return $self->error("Can't find media selection");
3507 my $update = "update volume=$arg->{media} ";
3509 if ($arg->{volstatus}) {
3510 $update .= " volstatus=$arg->{volstatus} ";
3513 if ($arg->{inchanger}) {
3514 $update .= " inchanger=yes " ;
3516 $update .= " slot=$arg->{slot} ";
3519 $update .= " slot=0 inchanger=no ";
3522 if ($arg->{enabled}) {
3523 $update .= " enabled=$arg->{enabled} ";
3527 $update .= " pool=$arg->{pool} " ;
3530 if (defined $arg->{volretention}) {
3531 $update .= " volretention=\"$arg->{volretention}\" " ;
3534 if (defined $arg->{voluseduration}) {
3535 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3538 if (defined $arg->{maxvoljobs}) {
3539 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3542 if (defined $arg->{maxvolfiles}) {
3543 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3546 if (defined $arg->{maxvolbytes}) {
3547 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3550 if (defined $arg->{poolrecycle}) {
3551 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3554 my $b = $self->get_bconsole();
3557 content => $b->send_cmd($update),
3558 title => "Update a volume ",
3564 my $media = $self->dbh_quote($arg->{media});
3566 my $loc = CGI::param('location') || '';
3568 $loc = $self->dbh_quote($loc); # is checked by db
3569 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3571 if (!$arg->{qcomment}) {
3572 $arg->{qcomment} = "''";
3574 push @q, "Comment=$arg->{qcomment}";
3579 SET " . join (',', @q) . "
3580 WHERE Media.VolumeName = $media
3582 $self->dbh_do($query);
3584 $self->update_media();
3591 my $ach = CGI::param('ach') ;
3592 $ach = $self->ach_get($ach);
3594 return $self->error("Bad autochanger name");
3598 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3599 $b->update_slots($ach->{name});
3607 my $arg = $self->get_form('jobid', 'limit', 'offset');
3608 unless ($arg->{jobid}) {
3609 return $self->error("Can't get jobid");
3612 if ($arg->{limit} == 100) {
3613 $arg->{limit} = 1000;
3616 my $t = CGI::param('time') || $self->{info}->{display_log_time} || '';
3618 # display only Error and Warning messages
3620 if (CGI::param('error')) {
3621 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3625 SELECT Job.Name as name, Client.Name as clientname
3626 FROM Job INNER JOIN Client ON (Job.ClientId = Client.ClientId)
3627 WHERE JobId = $arg->{jobid}
3630 my $row = $self->dbh_selectrow_hashref($query);
3633 return $self->error("Can't find $arg->{jobid} in catalog");
3637 SELECT Time AS time, LogText AS log
3639 WHERE ( Log.JobId = $arg->{jobid}
3640 OR (Log.JobId = 0 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3641 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3646 OFFSET $arg->{offset}
3649 my $log = $self->dbh_selectall_arrayref($query);
3651 return $self->error("Can't get log for jobid $arg->{jobid}");
3657 $logtxt = join("", map { ($_->[0] . ' ' . $_->[1]) } @$log ) ;
3659 $logtxt = join("", map { $_->[1] } @$log ) ;
3662 $self->display({ lines=> $logtxt,
3663 jobid => $arg->{jobid},
3664 name => $row->{name},
3665 client => $row->{clientname},
3666 offset => $arg->{offset},
3667 limit => $arg->{limit},
3668 }, 'display_log.tpl');
3676 my $arg = $self->get_form('ach', 'slots', 'drive');
3678 unless ($arg->{ach}) {
3679 return $self->error("Can't find autochanger name");
3682 my $a = $self->ach_get($arg->{ach});
3684 return $self->error("Can't find autochanger name in configuration");
3687 my $storage = $a->get_drive_name($arg->{drive});
3689 return $self->error("Can't get your drive name");
3695 if ($arg->{slots}) {
3696 $slots = join(",", @{ $arg->{slots} });
3697 $slots_sql = " AND Slot IN ($slots) ";
3698 $t += 60*scalar( @{ $arg->{slots} }) ;
3701 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3702 print "<h1>This command can take long time, be patient...</h1>";
3704 $b->label_barcodes(storage => $storage,
3705 drive => $arg->{drive},
3713 SET LocationId = (SELECT LocationId
3715 WHERE Location = '$arg->{ach}')
3717 WHERE (LocationId = 0 OR LocationId IS NULL)
3727 my @volume = CGI::param('media');
3730 return $self->error("Can't get media selection");
3733 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3735 foreach my $v (@volume) {
3737 content => $b->purge_volume($v),
3738 title => "Purge media",
3739 name => "purge volume=$v",
3749 my @volume = CGI::param('media');
3751 return $self->error("Can't get media selection");
3754 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3756 foreach my $v (@volume) {
3758 content => $b->prune_volume($v),
3759 title => "Prune volume",
3760 name => "prune volume=$v",
3770 my $arg = $self->get_form('jobid');
3771 unless ($arg->{jobid}) {
3772 return $self->error("Can't get jobid");
3775 my $b = $self->get_bconsole();
3777 content => $b->cancel($arg->{jobid}),
3778 title => "Cancel job",
3779 name => "cancel jobid=$arg->{jobid}",
3785 # Warning, we display current fileset
3788 my $arg = $self->get_form('fileset');
3790 if ($arg->{fileset}) {
3791 my $b = $self->get_bconsole();
3792 my $ret = $b->get_fileset($arg->{fileset});
3793 $self->display({ fileset => $arg->{fileset},
3795 }, "fileset_view.tpl");
3797 $self->error("Can't get fileset name");
3801 sub director_show_sched
3805 my $arg = $self->get_form('days');
3807 my $b = $self->get_bconsole();
3808 my $ret = $b->director_get_sched( $arg->{days} );
3813 }, "scheduled_job.tpl");
3816 sub enable_disable_job
3818 my ($self, $what) = @_ ;
3820 my $name = CGI::param('job') || '';
3821 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3822 return $self->error("Can't find job name");
3825 my $b = $self->get_bconsole();
3835 content => $b->send_cmd("$cmd job=\"$name\""),
3836 title => "$cmd $name",
3837 name => "$cmd job=\"$name\"",
3844 return new Bconsole(pref => $self->{info});
3850 my $b = $self->get_bconsole();
3852 my $joblist = [ map { { name => $_ } } $b->list_job() ];
3854 $self->display({ Jobs => $joblist }, "run_job.tpl");
3859 my ($self, $ouput) = @_;
3862 foreach my $l (split(/\r\n/, $ouput)) {
3863 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
3869 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
3875 foreach my $k (keys %arg) {
3876 $lowcase{lc($k)} = $arg{$k} ;
3885 my $b = $self->get_bconsole();
3887 my $job = CGI::param('job') || '';
3889 # we take informations from director, and we overwrite with user wish
3890 my $info = $b->send_cmd("show job=\"$job\"");
3891 my $attr = $self->run_parse_job($info);
3893 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
3894 my %job_opt = (%$attr, %$arg);
3896 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3898 my $pools = [ map { { name => $_ } } $b->list_pool() ];
3899 my $clients = [ map { { name => $_ } }$b->list_client()];
3900 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
3901 my $storages= [ map { { name => $_ } }$b->list_storage()];
3906 clients => $clients,
3907 filesets => $filesets,
3908 storages => $storages,
3910 }, "run_job_mod.tpl");
3916 my $b = $self->get_bconsole();
3918 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3928 my $b = $self->get_bconsole();
3930 # TODO: check input (don't use pool, level)
3932 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
3933 my $job = CGI::param('job') || '';
3934 my $storage = CGI::param('storage') || '';
3936 my $jobid = $b->run(job => $job,
3937 client => $arg->{client},
3938 priority => $arg->{priority},
3939 level => $arg->{level},
3940 storage => $storage,
3941 pool => $arg->{pool},
3942 fileset => $arg->{fileset},
3943 when => $arg->{when},
3946 print $jobid, $b->{error};
3948 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";