1 ################################################################
6 Bweb - A Bacula web interface
7 Bacula® - The Network Backup Solution
9 Copyright (C) 2000-2007 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");
1298 my $where=' WHERE true '; # by default
1300 my $arg = $self->get_form("client", "qre_client",
1301 "jclient_groups", "qnotingroup");
1303 my ($filter, undef) = $self->get_param('username');
1305 if ($arg->{qre_client}) {
1306 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1307 } elsif ($arg->{client}) {
1308 $where = "WHERE Name = '$arg->{client}' ";
1309 } elsif ($arg->{jclient_groups}) {
1310 # $filter could already contains client_group_member
1311 $where = ($filter?'':"
1312 JOIN client_group_member USING (ClientId)
1313 JOIN client_group USING (client_group_id)") .
1315 " WHERE client_group_name IN ($arg->{jclient_groups}) ";
1316 } elsif ($arg->{qnotingroup}) {
1319 (SELECT 1 FROM client_group_member
1320 WHERE Client.ClientId = client_group_member.ClientId
1326 SELECT Name AS name,
1328 AutoPrune AS autoprune,
1329 FileRetention AS fileretention,
1330 JobRetention AS jobretention
1331 FROM Client " . $self->get_client_filter() .
1334 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1336 my $dsp = { ID => $cur_id++,
1337 clients => [ values %$all] };
1339 $self->display($dsp, "client_list.tpl") ;
1344 my ($self, %arg) = @_;
1351 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1353 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1355 $self->{sql}->{TO_SEC}($arg{age})
1358 $label = "last " . human_sec($arg{age});
1361 if ($arg{groupby}) {
1362 $limit .= " GROUP BY $arg{groupby} ";
1366 $limit .= " ORDER BY $arg{order} ";
1370 $limit .= " LIMIT $arg{limit} ";
1371 $label .= " limited to $arg{limit}";
1375 $limit .= " OFFSET $arg{offset} ";
1376 $label .= " with $arg{offset} offset ";
1380 $label = 'no filter';
1383 return ($limit, $label);
1388 $bweb->get_form(...) - Get useful stuff
1392 This function get and check parameters against regexp.
1394 If word begin with 'q', the return will be quoted or join quoted
1395 if it's end with 's'.
1400 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1403 qclient => 'plume-fd',
1404 qpools => "'plume-fd', 'test-fd', '...'",
1411 my ($self, @what) = @_;
1412 my %what = map { $_ => 1 } @what;
1434 my %opt_ss =( # string with space
1438 my %opt_s = ( # default to ''
1459 my %opt_p = ( # option with path
1466 my %opt_r = (regexwhere => 1);
1468 my %opt_d = ( # option with date
1473 foreach my $i (@what) {
1474 if (exists $opt_i{$i}) {# integer param
1475 my $value = CGI::param($i) || $opt_i{$i} ;
1476 if ($value =~ /^(\d+)$/) {
1479 } elsif ($opt_s{$i}) { # simple string param
1480 my $value = CGI::param($i) || '';
1481 if ($value =~ /^([\w\d\.-]+)$/) {
1484 } elsif ($opt_ss{$i}) { # simple string param (with space)
1485 my $value = CGI::param($i) || '';
1486 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1489 } elsif ($i =~ /^j(\w+)s$/) { # quote join args "'arg1', 'arg2'"
1490 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1492 $ret{$i} = $self->dbh_join(@value) ;
1495 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1496 my $value = CGI::param($1) ;
1498 $ret{$i} = $self->dbh_quote($value);
1501 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1502 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1503 grep { ! /^\s*$/ } CGI::param($1) ];
1504 } elsif (exists $opt_p{$i}) {
1505 my $value = CGI::param($i) || '';
1506 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1509 } elsif (exists $opt_r{$i}) {
1510 my $value = CGI::param($i) || '';
1511 if ($value =~ /^([^'"']+)$/) {
1514 } elsif (exists $opt_d{$i}) {
1515 my $value = CGI::param($i) || '';
1516 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1523 foreach my $s (CGI::param('slot')) {
1524 if ($s =~ /^(\d+)$/) {
1525 push @{$ret{slots}}, $s;
1531 my $when = CGI::param('when') || '';
1532 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1537 if ($what{db_clients}) {
1538 my $filter=''; my $filter_where='';
1539 if ($what{username}) {
1540 # get security filter only if asked
1541 $filter = $self->get_client_filter();
1542 ($filter_where, undef) = $self->get_param('username');
1546 SELECT Client.Name as clientname
1547 FROM Client $filter WHERE true $filter_where
1550 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1551 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1555 if ($what{db_client_groups}) {
1557 SELECT client_group_name AS name
1561 my $grps = $self->dbh_selectall_hashref($query, 'name');
1562 $ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} }
1566 if ($what{db_usernames}) {
1572 my $users = $self->dbh_selectall_hashref($query, 'username');
1573 $ret{db_usernames} = [sort {$a->{username} cmp $b->{username} }
1577 if ($what{db_roles}) {
1583 my $r = $self->dbh_selectall_hashref($query, 'rolename');
1584 $ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} }
1588 if ($what{db_mediatypes}) {
1590 SELECT MediaType as mediatype
1594 my $media = $self->dbh_selectall_hashref($query, 'mediatype');
1595 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1599 if ($what{db_locations}) {
1601 SELECT Location as location, Cost as cost
1604 my $loc = $self->dbh_selectall_hashref($query, 'location');
1605 $ret{db_locations} = [ sort { $a->{location}
1611 if ($what{db_pools}) {
1612 my $query = "SELECT Name as name FROM Pool";
1614 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1615 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1618 if ($what{db_filesets}) {
1620 SELECT FileSet.FileSet AS fileset
1624 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1626 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1627 values %$filesets] ;
1630 if ($what{db_jobnames}) {
1632 SELECT DISTINCT Job.Name AS jobname
1636 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1638 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1639 values %$jobnames] ;
1642 if ($what{db_devices}) {
1644 SELECT Device.Name AS name
1648 my $devices = $self->dbh_selectall_hashref($query, 'name');
1650 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1661 my $fields = $self->get_form(qw/age level status clients filesets
1663 db_clients limit db_filesets width height
1664 qclients qfilesets qjobnames db_jobnames/);
1667 my $url = CGI::url(-full => 0,
1670 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1672 # this organisation is to keep user choice between 2 click
1673 # TODO : fileset and client selection doesn't work
1682 sub get_selected_media_location
1686 my $media = $self->get_form('jmedias');
1688 unless ($media->{jmedias}) {
1693 SELECT Media.VolumeName AS volumename, Location.Location AS location
1694 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1695 WHERE Media.VolumeName IN ($media->{jmedias})
1698 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1700 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1709 my ($self, $in) = @_ ;
1711 my $media = $self->get_selected_media_location();
1717 my $elt = $self->get_form('db_locations');
1719 $self->display({ ID => $cur_id++,
1720 enabled => human_enabled($in),
1721 %$elt, # db_locations
1723 sort { $a->{volumename} cmp $b->{volumename} } values %$media
1733 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1735 $self->display($elt, "help_extern.tpl");
1738 sub help_extern_compute
1742 my $number = CGI::param('limit') || '' ;
1743 unless ($number =~ /^(\d+)$/) {
1744 return $self->error("Bad arg number : $number ");
1747 my ($sql, undef) = $self->get_param('pools',
1748 'locations', 'mediatypes');
1751 SELECT Media.VolumeName AS volumename,
1752 Media.VolStatus AS volstatus,
1753 Media.LastWritten AS lastwritten,
1754 Media.MediaType AS mediatype,
1755 Media.VolMounts AS volmounts,
1757 Media.Recycle AS recycle,
1758 $self->{sql}->{FROM_UNIXTIME}(
1759 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1760 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1763 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1764 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1766 WHERE Media.InChanger = 1
1767 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1769 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1773 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1775 $self->display({ Media => [ values %$all ] },
1776 "help_extern_compute.tpl");
1783 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1784 $self->display($param, "help_intern.tpl");
1787 sub help_intern_compute
1791 my $number = CGI::param('limit') || '' ;
1792 unless ($number =~ /^(\d+)$/) {
1793 return $self->error("Bad arg number : $number ");
1796 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1798 if (CGI::param('expired')) {
1800 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1801 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1807 SELECT Media.VolumeName AS volumename,
1808 Media.VolStatus AS volstatus,
1809 Media.LastWritten AS lastwritten,
1810 Media.MediaType AS mediatype,
1811 Media.VolMounts AS volmounts,
1813 $self->{sql}->{FROM_UNIXTIME}(
1814 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1815 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1818 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1819 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1821 WHERE Media.InChanger <> 1
1822 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1823 AND Media.Recycle = 1
1825 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1829 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1831 $self->display({ Media => [ values %$all ] },
1832 "help_intern_compute.tpl");
1838 my ($self, %arg) = @_ ;
1840 my ($limit, $label) = $self->get_limit(%arg);
1844 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1845 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1846 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1847 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1848 ($self->{sql}->{DB_SIZE}) AS db_size,
1849 (SELECT count(Job.JobId)
1851 WHERE Job.JobStatus IN ('E','e','f','A')
1854 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1857 my $row = $self->dbh_selectrow_hashref($query) ;
1859 $row->{nb_bytes} = human_size($row->{nb_bytes});
1861 $row->{db_size} = human_size($row->{db_size});
1862 $row->{label} = $label;
1864 $self->display($row, "general.tpl");
1869 my ($self, @what) = @_ ;
1870 my %elt = map { $_ => 1 } @what;
1875 if ($elt{clients}) {
1876 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1878 $ret{clients} = \@clients;
1879 my $str = $self->dbh_join(@clients);
1880 $limit .= "AND Client.Name IN ($str) ";
1884 if ($elt{client_groups}) {
1885 my @clients = grep { ! /^\s*$/ } CGI::param('client_group');
1887 $ret{client_groups} = \@clients;
1888 my $str = $self->dbh_join(@clients);
1889 $limit .= "AND client_group_name IN ($str) ";
1893 if ($elt{filesets}) {
1894 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1896 $ret{filesets} = \@filesets;
1897 my $str = $self->dbh_join(@filesets);
1898 $limit .= "AND FileSet.FileSet IN ($str) ";
1902 if ($elt{mediatypes}) {
1903 my @media = grep { ! /^\s*$/ } CGI::param('mediatype');
1905 $ret{mediatypes} = \@media;
1906 my $str = $self->dbh_join(@media);
1907 $limit .= "AND Media.MediaType IN ($str) ";
1912 my $client = CGI::param('client');
1913 $ret{client} = $client;
1914 $client = $self->dbh_join($client);
1915 $limit .= "AND Client.Name = $client ";
1919 my $level = CGI::param('level') || '';
1920 if ($level =~ /^(\w)$/) {
1922 $limit .= "AND Job.Level = '$1' ";
1927 my $jobid = CGI::param('jobid') || '';
1929 if ($jobid =~ /^(\d+)$/) {
1931 $limit .= "AND Job.JobId = '$1' ";
1936 my $status = CGI::param('status') || '';
1937 if ($status =~ /^(\w)$/) {
1940 $limit .= "AND Job.JobStatus IN ('f','E') ";
1941 } elsif ($1 eq 'W') {
1942 $limit .= "AND Job.JobStatus = 'T' AND Job.JobErrors > 0 ";
1944 $limit .= "AND Job.JobStatus = '$1' ";
1949 if ($elt{volstatus}) {
1950 my $status = CGI::param('volstatus') || '';
1951 if ($status =~ /^(\w+)$/) {
1953 $limit .= "AND Media.VolStatus = '$1' ";
1957 if ($elt{locations}) {
1958 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1960 $ret{locations} = \@location;
1961 my $str = $self->dbh_join(@location);
1962 $limit .= "AND Location.Location IN ($str) ";
1967 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
1969 $ret{pools} = \@pool;
1970 my $str = $self->dbh_join(@pool);
1971 $limit .= "AND Pool.Name IN ($str) ";
1975 if ($elt{location}) {
1976 my $location = CGI::param('location') || '';
1978 $ret{location} = $location;
1979 $location = $self->dbh_quote($location);
1980 $limit .= "AND Location.Location = $location ";
1985 my $pool = CGI::param('pool') || '';
1988 $pool = $self->dbh_quote($pool);
1989 $limit .= "AND Pool.Name = $pool ";
1993 if ($elt{jobtype}) {
1994 my $jobtype = CGI::param('jobtype') || '';
1995 if ($jobtype =~ /^(\w)$/) {
1997 $limit .= "AND Job.Type = '$1' ";
2001 # fill this only when security is enabled
2002 if ($elt{username} and $self->{info}->{enable_security}) {
2003 if ($self->{loginname} ne 'admin') {
2004 my $u = $self->dbh_quote($self->{loginname});
2005 $ret{username}=$self->{loginname};
2006 $limit .= "AND bweb_user.username = $u ";
2010 return ($limit, %ret);
2021 my ($self, %arg) = @_ ;
2023 $arg{order} = ' Job.JobId DESC ';
2025 my ($limit, $label) = $self->get_limit(%arg);
2026 my ($where, undef) = $self->get_param('clients',
2036 if (CGI::param('client_group')) {
2038 JOIN client_group_member USING (ClientId)
2039 JOIN client_group USING (client_group_id)
2042 my $filter = $self->get_client_filter();
2044 $cgq = ($filter)?$filter:$cgq;
2047 SELECT Job.JobId AS jobid,
2048 Client.Name AS client,
2049 FileSet.FileSet AS fileset,
2050 Job.Name AS jobname,
2052 StartTime AS starttime,
2054 Pool.Name AS poolname,
2055 JobFiles AS jobfiles,
2056 JobBytes AS jobbytes,
2057 JobStatus AS jobstatus,
2058 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2059 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2062 JobErrors AS joberrors
2065 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2066 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2067 WHERE Client.ClientId=Job.ClientId
2068 AND Job.JobStatus NOT IN ('R', 'C')
2073 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2075 $self->display({ Filter => $label,
2079 sort { $a->{jobid} <=> $b->{jobid} }
2086 # display job informations
2087 sub display_job_zoom
2089 my ($self, $jobid) = @_ ;
2091 $jobid = $self->dbh_quote($jobid);
2093 # get security filter
2094 my $filter = $self->get_client_filter();
2095 my ($filter_where, undef) = $self->get_param('username');
2098 SELECT DISTINCT Job.JobId AS jobid,
2099 Client.Name AS client,
2100 Job.Name AS jobname,
2101 FileSet.FileSet AS fileset,
2103 Pool.Name AS poolname,
2104 StartTime AS starttime,
2105 JobFiles AS jobfiles,
2106 JobBytes AS jobbytes,
2107 JobStatus AS jobstatus,
2108 JobErrors AS joberrors,
2109 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2110 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
2112 FROM Client $filter,
2113 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2114 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2115 WHERE Client.ClientId=Job.ClientId
2116 AND Job.JobId = $jobid
2120 my $row = $self->dbh_selectrow_hashref($query) ;
2122 # display all volumes associate with this job
2124 SELECT Media.VolumeName as volumename
2125 FROM Job,Media,JobMedia
2126 WHERE Job.JobId = $jobid
2127 AND JobMedia.JobId=Job.JobId
2128 AND JobMedia.MediaId=Media.MediaId
2131 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2133 $row->{volumes} = [ values %$all ] ;
2135 $self->display($row, "display_job_zoom.tpl");
2138 sub display_job_group
2140 my ($self, %arg) = @_;
2142 my ($limit, $label) = $self->get_limit(groupby => 'client_group_name', %arg);
2144 my ($where, undef) = $self->get_param('client_groups',
2150 SELECT client_group_name AS client_group_name,
2151 COALESCE(jobok.jobfiles,0) + COALESCE(joberr.jobfiles,0) AS jobfiles,
2152 COALESCE(jobok.jobbytes,0) + COALESCE(joberr.jobbytes,0) AS jobbytes,
2153 COALESCE(jobok.joberrors,0) + COALESCE(joberr.joberrors,0) AS joberrors,
2154 COALESCE(jobok.nbjobs,0) AS nbjobok,
2155 COALESCE(joberr.nbjobs,0) AS nbjoberr,
2156 COALESCE(jobok.duration, '0:0:0') AS duration
2158 FROM client_group LEFT JOIN (
2159 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2160 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2161 SUM(JobErrors) AS joberrors,
2162 SUM($self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2163 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)))
2166 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2167 JOIN client_group USING (client_group_id)
2169 WHERE JobStatus = 'T'
2172 ) AS jobok USING (client_group_name) LEFT JOIN
2175 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2176 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2177 SUM(JobErrors) AS joberrors
2178 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2179 JOIN client_group USING (client_group_id)
2181 WHERE JobStatus IN ('f','E', 'A')
2184 ) AS joberr USING (client_group_name)
2188 my $all = $self->dbh_selectall_hashref($query, 'client_group_name');
2190 my $rep = { groups => [ values %$all ], age => $arg{age}, filter => $label };
2193 $self->display($rep, "display_job_group.tpl");
2198 my ($self, %arg) = @_ ;
2200 my ($limit, $label) = $self->get_limit(%arg);
2201 my ($where, %elt) = $self->get_param('pools',
2206 my $arg = $self->get_form('jmedias', 'qre_media', 'expired');
2208 if ($arg->{jmedias}) {
2209 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2211 if ($arg->{qre_media}) {
2212 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2214 if ($arg->{expired}) {
2216 AND VolStatus = 'Full'
2217 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2218 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2219 ) < NOW() " . $where ;
2223 SELECT Media.VolumeName AS volumename,
2224 Media.VolBytes AS volbytes,
2225 Media.VolStatus AS volstatus,
2226 Media.MediaType AS mediatype,
2227 Media.InChanger AS online,
2228 Media.LastWritten AS lastwritten,
2229 Location.Location AS location,
2230 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2231 Pool.Name AS poolname,
2232 $self->{sql}->{FROM_UNIXTIME}(
2233 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2234 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2237 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2238 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2239 Media.MediaType AS MediaType
2241 WHERE Media.VolStatus = 'Full'
2242 GROUP BY Media.MediaType
2243 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2245 WHERE Media.PoolId=Pool.PoolId
2250 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2252 $self->display({ ID => $cur_id++,
2254 Location => $elt{location},
2255 Media => [ values %$all ],
2257 "display_media.tpl");
2260 sub display_allmedia
2264 my $pool = $self->get_form('db_pools');
2266 foreach my $name (@{ $pool->{db_pools} }) {
2267 CGI::param('pool', $name->{name});
2268 $self->display_media();
2272 sub display_media_zoom
2276 my $media = $self->get_form('jmedias');
2278 unless ($media->{jmedias}) {
2279 return $self->error("Can't get media selection");
2283 SELECT InChanger AS online,
2284 Media.Enabled AS enabled,
2285 VolBytes AS nb_bytes,
2286 VolumeName AS volumename,
2287 VolStatus AS volstatus,
2288 VolMounts AS nb_mounts,
2289 Media.VolUseDuration AS voluseduration,
2290 Media.MaxVolJobs AS maxvoljobs,
2291 Media.MaxVolFiles AS maxvolfiles,
2292 Media.MaxVolBytes AS maxvolbytes,
2293 VolErrors AS nb_errors,
2294 Pool.Name AS poolname,
2295 Location.Location AS location,
2296 Media.Recycle AS recycle,
2297 Media.VolRetention AS volretention,
2298 Media.LastWritten AS lastwritten,
2299 Media.VolReadTime/1000000 AS volreadtime,
2300 Media.VolWriteTime/1000000 AS volwritetime,
2301 Media.RecycleCount AS recyclecount,
2302 Media.Comment AS comment,
2303 $self->{sql}->{FROM_UNIXTIME}(
2304 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2305 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2308 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2309 WHERE Pool.PoolId = Media.PoolId
2310 AND VolumeName IN ($media->{jmedias})
2313 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2315 foreach my $media (values %$all) {
2316 my $mq = $self->dbh_quote($media->{volumename});
2319 SELECT DISTINCT Job.JobId AS jobid,
2321 Job.StartTime AS starttime,
2324 Job.JobFiles AS files,
2325 Job.JobBytes AS bytes,
2326 Job.jobstatus AS status
2327 FROM Media,JobMedia,Job
2328 WHERE Media.VolumeName=$mq
2329 AND Media.MediaId=JobMedia.MediaId
2330 AND JobMedia.JobId=Job.JobId
2333 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2336 SELECT LocationLog.Date AS date,
2337 Location.Location AS location,
2338 LocationLog.Comment AS comment
2339 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2340 WHERE Media.MediaId = LocationLog.MediaId
2341 AND Media.VolumeName = $mq
2345 my $log = $self->dbh_selectall_arrayref($query) ;
2347 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2350 $self->display({ jobs => [ values %$jobs ],
2351 LocationLog => $logtxt,
2353 "display_media_zoom.tpl");
2360 $self->can_do('location_mgnt');
2362 my $loc = $self->get_form('qlocation');
2363 unless ($loc->{qlocation}) {
2364 return $self->error("Can't get location");
2368 SELECT Location.Location AS location,
2369 Location.Cost AS cost,
2370 Location.Enabled AS enabled
2372 WHERE Location.Location = $loc->{qlocation}
2375 my $row = $self->dbh_selectrow_hashref($query);
2377 $self->display({ ID => $cur_id++,
2378 %$row }, "location_edit.tpl") ;
2384 $self->can_do('location_mgnt');
2386 my $arg = $self->get_form(qw/qlocation qnewlocation cost/) ;
2387 unless ($arg->{qlocation}) {
2388 return $self->error("Can't get location");
2390 unless ($arg->{qnewlocation}) {
2391 return $self->error("Can't get new location name");
2393 unless ($arg->{cost}) {
2394 return $self->error("Can't get new cost");
2397 my $enabled = CGI::param('enabled') || '';
2398 $enabled = $enabled?1:0;
2401 UPDATE Location SET Cost = $arg->{cost},
2402 Location = $arg->{qnewlocation},
2404 WHERE Location.Location = $arg->{qlocation}
2407 $self->dbh_do($query);
2409 $self->location_display();
2415 $self->can_do('location_mgnt');
2417 my $arg = $self->get_form(qw/qlocation/) ;
2419 unless ($arg->{qlocation}) {
2420 return $self->error("Can't get location");
2424 SELECT count(Media.MediaId) AS nb
2425 FROM Media INNER JOIN Location USING (LocationID)
2426 WHERE Location = $arg->{qlocation}
2429 my $res = $self->dbh_selectrow_hashref($query);
2432 return $self->error("Sorry, the location must be empty");
2436 DELETE FROM Location WHERE Location = $arg->{qlocation} LIMIT 1
2439 $self->dbh_do($query);
2441 $self->location_display();
2447 $self->can_do('location_mgnt');
2449 my $arg = $self->get_form(qw/qlocation cost/) ;
2451 unless ($arg->{qlocation}) {
2452 $self->display({}, "location_add.tpl");
2455 unless ($arg->{cost}) {
2456 return $self->error("Can't get new cost");
2459 my $enabled = CGI::param('enabled') || '';
2460 $enabled = $enabled?1:0;
2463 INSERT INTO Location (Location, Cost, Enabled)
2464 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2467 $self->dbh_do($query);
2469 $self->location_display();
2472 sub location_display
2477 SELECT Location.Location AS location,
2478 Location.Cost AS cost,
2479 Location.Enabled AS enabled,
2480 (SELECT count(Media.MediaId)
2482 WHERE Media.LocationId = Location.LocationId
2487 my $location = $self->dbh_selectall_hashref($query, 'location');
2489 $self->display({ ID => $cur_id++,
2490 Locations => [ values %$location ] },
2491 "display_location.tpl");
2498 my $media = $self->get_selected_media_location();
2503 my $arg = $self->get_form('db_locations', 'qnewlocation');
2505 $self->display({ email => $self->{info}->{email_media},
2507 media => [ values %$media ],
2509 "update_location.tpl");
2512 ###########################################################
2517 $self->can_do('group_mgnt');
2519 my $grp = $self->get_form(qw/qclient_group db_clients/);
2521 unless ($grp->{qclient_group}) {
2522 return $self->error("Can't get group");
2527 FROM Client JOIN client_group_member using (clientid)
2528 JOIN client_group using (client_group_id)
2529 WHERE client_group_name = $grp->{qclient_group}
2532 my $row = $self->dbh_selectall_hashref($query, "name");
2534 $self->display({ ID => $cur_id++,
2535 client_group => $grp->{qclient_group},
2537 client_group_member => [ values %$row]},
2544 $self->can_do('group_mgnt');
2546 my $arg = $self->get_form(qw/qclient_group jclients qnewgroup/);
2547 unless ($arg->{qclient_group}) {
2548 return $self->error("Can't get groups");
2551 $self->{dbh}->begin_work();
2554 DELETE FROM client_group_member
2555 WHERE client_group_id IN
2556 (SELECT client_group_id
2558 WHERE client_group_name = $arg->{qclient_group})
2560 $self->dbh_do($query);
2563 INSERT INTO client_group_member (clientid, client_group_id)
2565 (SELECT client_group_id
2567 WHERE client_group_name = $arg->{qclient_group})
2568 FROM Client WHERE Name IN ($arg->{jclients})
2571 $self->dbh_do($query);
2573 if ($arg->{qclient_group} ne $arg->{qnewgroup}) {
2576 SET client_group_name = $arg->{qnewgroup}
2577 WHERE client_group_name = $arg->{qclient_group}
2580 $self->dbh_do($query);
2583 $self->{dbh}->commit() or $self->error($self->{dbh}->errstr);
2585 $self->display_groups();
2591 $self->can_do('group_mgnt');
2593 my $arg = $self->get_form(qw/qclient_group/);
2595 unless ($arg->{qclient_group}) {
2596 return $self->error("Can't get groups");
2599 $self->{dbh}->begin_work();
2602 DELETE FROM client_group_member
2603 WHERE client_group_id IN
2604 (SELECT client_group_id
2606 WHERE client_group_name = $arg->{qclient_group});
2608 DELETE FROM client_group
2609 WHERE client_group_name = $arg->{qclient_group};
2611 $self->dbh_do($query);
2613 $self->{dbh}->commit();
2615 $self->display_groups();
2622 $self->can_do('group_mgnt');
2624 my $arg = $self->get_form(qw/qclient_group/) ;
2626 unless ($arg->{qclient_group}) {
2627 $self->display({}, "groups_add.tpl");
2632 INSERT INTO client_group (client_group_name)
2633 VALUES ($arg->{qclient_group})
2636 $self->dbh_do($query);
2638 $self->display_groups();
2645 my $arg = $self->get_form(qw/db_client_groups/) ;
2647 if ($self->{dbh}->errstr) {
2648 return $self->error("Can't use groups with bweb, read INSTALL to enable them");
2653 $self->display({ ID => $cur_id++,
2655 "display_groups.tpl");
2658 ###########################################################
2660 # TODO: avoir un mode qui coupe le programme avec une page d'erreur
2661 # we can also get all security and fill {security} hash
2664 my ($self, $action) = @_;
2665 # is security enabled in configuration ?
2666 if (not $self->{info}->{enable_security}) {
2669 # admin is a special user that can do everything
2670 if ($self->{loginname} eq 'admin') {
2674 if (!$self->{loginname}) {
2675 $self->error("Can't do $action, your are not logged. " .
2676 "Check security with your administrator");
2677 $self->display_end();
2681 if ($self->{security}->{$action}) {
2684 my ($u, $r) = ($self->dbh_quote($self->{loginname}),
2685 $self->dbh_quote($action));
2687 SELECT 1, username, rolename
2689 JOIN bweb_role_member USING (userid)
2690 JOIN bweb_role USING (roleid)
2695 my $row = $self->dbh_selectrow_hashref($query);
2696 # do cache with this role
2698 $self->error("$u sorry, but this action ($action) is not permited. " .
2699 "Check security with your administrator");
2700 $self->display_end();
2703 $self->{security}->{$row->{rolename}} = 1;
2707 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2708 sub get_client_filter
2711 if ($self->{info}->{enable_security}) {
2713 JOIN client_group_member USING (ClientId)
2714 JOIN client_group USING (client_group_id)
2715 JOIN bweb_client_group_acl USING (client_group_id)
2716 JOIN bweb_user USING (userid) ";
2722 # role and username have to be quoted before
2723 # role and username can be a quoted list
2726 my ($self, $role, $username) = @_;
2727 $self->can_do("user_mgnt");
2729 my $nb = $self->dbh_do("
2730 DELETE FROM bweb_role_member
2731 WHERE roleid = (SELECT roleid FROM bweb_role
2732 WHERE rolename IN ($role))
2733 AND userid = (SELECT userid FROM bweb_user
2734 WHERE username IN ($username))");
2738 # role and username have to be quoted before
2739 # role and username can be a quoted list
2742 my ($self, $role, $username) = @_;
2743 $self->can_do("user_mgnt");
2745 my $nb = $self->dbh_do("
2746 INSERT INTO bweb_role_member (roleid, userid)
2747 SELECT roleid, userid FROM bweb_role, bweb_user
2748 WHERE rolename IN ($role)
2749 AND username IN ($username)
2754 # role and username have to be quoted before
2755 # role and username can be a quoted list
2758 my ($self, $copy, $user) = @_;
2759 $self->can_do("user_mgnt");
2761 my $nb = $self->dbh_do("
2762 INSERT INTO bweb_role_member (roleid, userid)
2763 SELECT roleid, a.userid
2764 FROM bweb_user AS a, bweb_role_member
2765 JOIN bweb_user USING (userid)
2766 WHERE bweb_user.username = $copy
2767 AND a.username = $user");
2771 # username can be a join quoted list of usernames
2774 my ($self, $username) = @_;
2775 $self->can_do("user_mgnt");
2778 DELETE FROM bweb_role_member
2782 WHERE username in ($username)
2789 $self->can_do("user_mgnt");
2791 my $arg = $self->get_form(qw/jusernames/);
2793 unless ($arg->{jusernames}) {
2794 return $self->error("Can't get user");
2797 $self->{dbh}->begin_work();
2799 $self->revoke_all($arg->{jusernames});
2800 $self->dbh_do("DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2802 $self->{dbh}->commit();
2804 $self->display_users();
2810 $self->can_do("user_mgnt");
2812 # we don't quote username directly to check that it is conform
2813 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username/) ;
2815 if (not $arg->{qcreate}) {
2816 $arg = $self->get_form(qw/db_roles db_usernames/);
2817 $self->display($arg, "display_user.tpl");
2821 my $u = $self->dbh_quote($arg->{username});
2823 if (!$arg->{qpasswd}) {
2824 $arg->{qpasswd} = "''";
2826 if (!$arg->{qcomment}) {
2827 $arg->{qcomment} = "''";
2830 # will fail if user already exists
2832 UPDATE bweb_user SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment}
2833 WHERE username = $u")
2836 INSERT INTO bweb_user (username, passwd, comment)
2837 VALUES ($u, $arg->{qpasswd}, $arg->{qcomment})");
2839 $self->{dbh}->begin_work();
2841 $self->revoke_all($u);
2843 if ($arg->{qcopy_username}) {
2844 $self->grant_like($arg->{qcopy_username}, $u);
2846 $self->grant($arg->{jrolenames}, $u);
2849 $self->{dbh}->commit();
2851 $self->display_users();
2854 # TODO: we miss a matrix with all user/roles
2858 $self->can_do("user_mgnt");
2860 my $arg = $self->get_form(qw/db_usernames/) ;
2862 if ($self->{dbh}->errstr) {
2863 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2866 $self->display({ ID => $cur_id++,
2868 "display_users.tpl");
2874 $self->can_do("user_mgnt");
2876 my $arg = $self->get_form(qw/username db_usernames/);
2877 my $user = $self->dbh_quote($arg->{username});
2879 my $userp = $self->dbh_selectrow_hashref("
2880 SELECT username, passwd, comment
2882 WHERE username = $user
2886 return $self->error("Can't find $user in catalog");
2890 #------------+--------
2895 my $role = $self->dbh_selectall_hashref("
2896 SELECT rolename, temp.userid
2898 LEFT JOIN (SELECT roleid, userid
2899 FROM bweb_user JOIN bweb_role_member USING (userid)
2900 WHERE username = $user) AS temp USING (roleid)
2905 db_usernames => $arg->{db_usernames},
2906 username => $userp->{username},
2907 comment => $userp->{comment},
2908 passwd => $userp->{passwd},
2909 db_roles => [ values %$role],
2910 }, "display_user.tpl");
2914 ###########################################################
2916 sub get_media_max_size
2918 my ($self, $type) = @_;
2920 "SELECT avg(VolBytes) AS size
2922 WHERE Media.VolStatus = 'Full'
2923 AND Media.MediaType = '$type'
2926 my $res = $self->selectrow_hashref($query);
2929 return $res->{size};
2939 my $media = $self->get_form('qmedia');
2941 unless ($media->{qmedia}) {
2942 return $self->error("Can't get media");
2946 SELECT Media.Slot AS slot,
2947 PoolMedia.Name AS poolname,
2948 Media.VolStatus AS volstatus,
2949 Media.InChanger AS inchanger,
2950 Location.Location AS location,
2951 Media.VolumeName AS volumename,
2952 Media.MaxVolBytes AS maxvolbytes,
2953 Media.MaxVolJobs AS maxvoljobs,
2954 Media.MaxVolFiles AS maxvolfiles,
2955 Media.VolUseDuration AS voluseduration,
2956 Media.VolRetention AS volretention,
2957 Media.Comment AS comment,
2958 PoolRecycle.Name AS poolrecycle,
2959 Media.Enabled AS enabled
2961 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
2962 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
2963 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2965 WHERE Media.VolumeName = $media->{qmedia}
2968 my $row = $self->dbh_selectrow_hashref($query);
2969 $row->{volretention} = human_sec($row->{volretention});
2970 $row->{voluseduration} = human_sec($row->{voluseduration});
2971 $row->{enabled} = human_enabled($row->{enabled});
2973 my $elt = $self->get_form(qw/db_pools db_locations/);
2978 }, "update_media.tpl");
2984 $self->can_do('media_mgnt');
2986 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
2988 unless ($arg->{jmedias}) {
2989 return $self->error("Can't get selected media");
2992 unless ($arg->{qnewlocation}) {
2993 return $self->error("Can't get new location");
2998 SET LocationId = (SELECT LocationId
3000 WHERE Location = $arg->{qnewlocation})
3001 WHERE Media.VolumeName IN ($arg->{jmedias})
3004 my $nb = $self->dbh_do($query);
3006 print "$nb media updated, you may have to update your autochanger.";
3008 $self->display_media();
3014 $self->can_do('media_mgnt');
3016 my $media = $self->get_selected_media_location();
3018 return $self->error("Can't get media selection");
3020 my $newloc = CGI::param('newlocation');
3022 my $user = CGI::param('user') || 'unknown';
3023 my $comm = CGI::param('comment') || '';
3024 $comm = $self->dbh_quote("$user: $comm");
3026 my $arg = $self->get_form('enabled');
3027 my $en = human_enabled($arg->{enabled});
3028 my $b = $self->get_bconsole();
3031 foreach my $vol (keys %$media) {
3033 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3035 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3036 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3037 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3040 $self->dbh_do($query);
3041 $self->debug($query);
3042 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3047 $q->param('action', 'update_location');
3048 my $url = $q->url(-full => 1, -query=>1);
3050 $self->display({ email => $self->{info}->{email_media},
3052 newlocation => $newloc,
3053 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3054 media => [ values %$media ],
3056 "change_location.tpl");
3060 sub display_client_stats
3062 my ($self, %arg) = @_ ;
3064 my $client = $self->dbh_quote($arg{clientname});
3065 # get security filter
3066 my $filter = $self->get_client_filter();
3067 my ($filter_where, undef) = $self->get_param('username');
3068 my ($limit, $label) = $self->get_limit(%arg);
3071 count(Job.JobId) AS nb_jobs,
3072 sum(Job.JobBytes) AS nb_bytes,
3073 sum(Job.JobErrors) AS nb_err,
3074 sum(Job.JobFiles) AS nb_files,
3075 Client.Name AS clientname
3076 FROM Job JOIN Client USING (ClientId) $filter
3078 Client.Name = $client
3081 GROUP BY Client.Name
3084 my $row = $self->dbh_selectrow_hashref($query);
3086 $row->{ID} = $cur_id++;
3087 $row->{label} = $label;
3088 $row->{grapharg} = "client";
3090 $self->display($row, "display_client_stats.tpl");
3094 sub display_group_stats
3096 my ($self, %arg) = @_ ;
3098 my $carg = $self->get_form(qw/qclient_group/);
3100 unless ($carg->{qclient_group}) {
3101 return $self->error("Can't get group");
3104 my ($limit, $label) = $self->get_limit(%arg);
3108 count(Job.JobId) AS nb_jobs,
3109 sum(Job.JobBytes) AS nb_bytes,
3110 sum(Job.JobErrors) AS nb_err,
3111 sum(Job.JobFiles) AS nb_files,
3112 client_group.client_group_name AS clientname
3113 FROM Job JOIN Client USING (ClientId)
3114 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3115 JOIN client_group USING (client_group_id)
3117 client_group.client_group_name = $carg->{qclient_group}
3119 GROUP BY client_group.client_group_name
3122 my $row = $self->dbh_selectrow_hashref($query);
3124 $row->{ID} = $cur_id++;
3125 $row->{label} = $label;
3126 $row->{grapharg} = "client_group";
3128 $self->display($row, "display_client_stats.tpl");
3131 # poolname can be undef
3134 my ($self, $poolname) = @_ ;
3138 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3139 if ($arg->{jmediatypes}) {
3140 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3141 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3144 # TODO : afficher les tailles et les dates
3147 SELECT subq.volmax AS volmax,
3148 subq.volnum AS volnum,
3149 subq.voltotal AS voltotal,
3151 Pool.Recycle AS recycle,
3152 Pool.VolRetention AS volretention,
3153 Pool.VolUseDuration AS voluseduration,
3154 Pool.MaxVolJobs AS maxvoljobs,
3155 Pool.MaxVolFiles AS maxvolfiles,
3156 Pool.MaxVolBytes AS maxvolbytes,
3157 subq.PoolId AS PoolId,
3158 subq.MediaType AS mediatype,
3159 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3162 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3163 count(Media.MediaId) AS volnum,
3164 sum(Media.VolBytes) AS voltotal,
3165 Media.PoolId AS PoolId,
3166 Media.MediaType AS MediaType
3168 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3169 Media.MediaType AS MediaType
3171 WHERE Media.VolStatus = 'Full'
3172 GROUP BY Media.MediaType
3173 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3174 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3176 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3180 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3183 SELECT Pool.Name AS name,
3184 sum(VolBytes) AS size
3185 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3186 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3190 my $empty = $self->dbh_selectall_hashref($query, 'name');
3192 foreach my $p (values %$all) {
3193 if ($p->{volmax} > 0) { # mysql returns 0.0000
3194 # we remove Recycled/Purged media from pool usage
3195 if (defined $empty->{$p->{name}}) {
3196 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3198 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3200 $p->{poolusage} = 0;
3204 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3206 WHERE PoolId=$p->{poolid}
3207 AND Media.MediaType = '$p->{mediatype}'
3211 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3212 foreach my $t (values %$content) {
3213 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3218 $self->display({ ID => $cur_id++,
3219 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3220 Pools => [ values %$all ]},
3221 "display_pool.tpl");
3224 sub display_running_job
3228 my $arg = $self->get_form('client', 'jobid');
3230 if (!$arg->{client} and $arg->{jobid}) {
3231 # get security filter
3232 my $filter = $self->get_client_filter();
3233 my ($filter_where, undef) = $self->get_param('username');
3236 SELECT Client.Name AS name
3237 FROM Job INNER JOIN Client USING (ClientId) $filter
3238 WHERE Job.JobId = $arg->{jobid} $filter_where
3241 my $row = $self->dbh_selectrow_hashref($query);
3244 $arg->{client} = $row->{name};
3245 CGI::param('client', $arg->{client});
3249 if ($arg->{client}) {
3250 my $cli = new Bweb::Client(name => $arg->{client});
3251 $cli->display_running_job($self->{info}, $arg->{jobid});
3252 if ($arg->{jobid}) {
3253 $self->get_job_log();
3256 $self->error("Can't get client or jobid");
3260 sub display_running_jobs
3262 my ($self, $display_action) = @_;
3263 # get security filter
3264 my $filter = $self->get_client_filter();
3265 my ($filter_where, undef) = $self->get_param('username');
3268 SELECT Job.JobId AS jobid,
3269 Job.Name AS jobname,
3271 Job.StartTime AS starttime,
3272 Job.JobFiles AS jobfiles,
3273 Job.JobBytes AS jobbytes,
3274 Job.JobStatus AS jobstatus,
3275 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3276 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3278 Client.Name AS clientname
3279 FROM Job INNER JOIN Client USING (ClientId) $filter
3281 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3284 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3286 $self->display({ ID => $cur_id++,
3287 display_action => $display_action,
3288 Jobs => [ values %$all ]},
3289 "running_job.tpl") ;
3292 # return the autochanger list to update
3296 $self->can_do('media_mgnt');
3299 my $arg = $self->get_form('jmedias');
3301 unless ($arg->{jmedias}) {
3302 return $self->error("Can't get media selection");
3306 SELECT Media.VolumeName AS volumename,
3307 Storage.Name AS storage,
3308 Location.Location AS location,
3310 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3311 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3312 WHERE Media.VolumeName IN ($arg->{jmedias})
3313 AND Media.InChanger = 1
3316 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3318 foreach my $vol (values %$all) {
3319 my $a = $self->ach_get($vol->{location});
3321 $ret{$vol->{location}} = 1;
3323 unless ($a->{have_status}) {
3325 $a->{have_status} = 1;
3328 print "eject $vol->{volumename} from $vol->{storage} : ";
3329 if ($a->send_to_io($vol->{slot})) {
3330 print "<img src='/bweb/T.png' alt='ok'><br/>";
3332 print "<img src='/bweb/E.png' alt='err'><br/>";
3342 my ($to, $subject, $content) = (CGI::param('email'),
3343 CGI::param('subject'),
3344 CGI::param('content'));
3345 $to =~ s/[^\w\d\.\@<>,]//;
3346 $subject =~ s/[^\w\d\.\[\]]/ /;
3348 open(MAIL, "|mail -s '$subject' '$to'") ;
3349 print MAIL $content;
3359 my $arg = $self->get_form('jobid', 'client');
3361 print CGI::header('text/brestore');
3362 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3363 print "client=$arg->{client}\n" if ($arg->{client});
3364 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3368 # TODO : move this to Bweb::Autochanger ?
3369 # TODO : make this internal to not eject tape ?
3375 my ($self, $name) = @_;
3378 return $self->error("Can't get your autochanger name ach");
3381 unless ($self->{info}->{ach_list}) {
3382 return $self->error("Could not find any autochanger");
3385 my $a = $self->{info}->{ach_list}->{$name};
3388 $self->error("Can't get your autochanger $name from your ach_list");
3393 $a->{debug} = $self->{debug};
3400 my ($self, $ach) = @_;
3401 $self->can_do('configure');
3403 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3405 $self->{info}->save();
3413 $self->can_do('configure');
3415 my $arg = $self->get_form('ach');
3417 or !$self->{info}->{ach_list}
3418 or !$self->{info}->{ach_list}->{$arg->{ach}})
3420 return $self->error("Can't get autochanger name");
3423 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3427 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3429 my $b = $self->get_bconsole();
3431 my @storages = $b->list_storage() ;
3433 $ach->{devices} = [ map { { name => $_ } } @storages ];
3435 $self->display($ach, "ach_add.tpl");
3436 delete $ach->{drives};
3437 delete $ach->{devices};
3444 $self->can_do('configure');
3446 my $arg = $self->get_form('ach');
3449 or !$self->{info}->{ach_list}
3450 or !$self->{info}->{ach_list}->{$arg->{ach}})
3452 return $self->error("Can't get autochanger name");
3455 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3457 $self->{info}->save();
3458 $self->{info}->view();
3464 $self->can_do('configure');
3466 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3468 my $b = $self->get_bconsole();
3469 my @storages = $b->list_storage() ;
3471 unless ($arg->{ach}) {
3472 $arg->{devices} = [ map { { name => $_ } } @storages ];
3473 return $self->display($arg, "ach_add.tpl");
3477 foreach my $drive (CGI::param('drives'))
3479 unless (grep(/^$drive$/,@storages)) {
3480 return $self->error("Can't find $drive in storage list");
3483 my $index = CGI::param("index_$drive");
3484 unless (defined $index and $index =~ /^(\d+)$/) {
3485 return $self->error("Can't get $drive index");
3488 $drives[$index] = $drive;
3492 return $self->error("Can't get drives from Autochanger");
3495 my $a = new Bweb::Autochanger(name => $arg->{ach},
3496 precmd => $arg->{precmd},
3497 drive_name => \@drives,
3498 device => $arg->{device},
3499 mtxcmd => $arg->{mtxcmd});
3501 $self->ach_register($a) ;
3503 $self->{info}->view();
3509 $self->can_do('delete_job');
3511 my $arg = $self->get_form('jobid');
3513 if ($arg->{jobid}) {
3514 my $b = $self->get_bconsole();
3515 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3519 title => "Delete a job ",
3520 name => "delete jobid=$arg->{jobid}",
3528 $self->can_do('media_mgnt');
3530 my $arg = $self->get_form(qw/media volstatus inchanger pool
3531 slot volretention voluseduration
3532 maxvoljobs maxvolfiles maxvolbytes
3533 qcomment poolrecycle enabled
3536 unless ($arg->{media}) {
3537 return $self->error("Can't find media selection");
3540 my $update = "update volume=$arg->{media} ";
3542 if ($arg->{volstatus}) {
3543 $update .= " volstatus=$arg->{volstatus} ";
3546 if ($arg->{inchanger}) {
3547 $update .= " inchanger=yes " ;
3549 $update .= " slot=$arg->{slot} ";
3552 $update .= " slot=0 inchanger=no ";
3555 if ($arg->{enabled}) {
3556 $update .= " enabled=$arg->{enabled} ";
3560 $update .= " pool=$arg->{pool} " ;
3563 if (defined $arg->{volretention}) {
3564 $update .= " volretention=\"$arg->{volretention}\" " ;
3567 if (defined $arg->{voluseduration}) {
3568 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3571 if (defined $arg->{maxvoljobs}) {
3572 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3575 if (defined $arg->{maxvolfiles}) {
3576 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3579 if (defined $arg->{maxvolbytes}) {
3580 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3583 if (defined $arg->{poolrecycle}) {
3584 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3587 my $b = $self->get_bconsole();
3590 content => $b->send_cmd($update),
3591 title => "Update a volume ",
3597 my $media = $self->dbh_quote($arg->{media});
3599 my $loc = CGI::param('location') || '';
3601 $loc = $self->dbh_quote($loc); # is checked by db
3602 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3604 if (!$arg->{qcomment}) {
3605 $arg->{qcomment} = "''";
3607 push @q, "Comment=$arg->{qcomment}";
3612 SET " . join (',', @q) . "
3613 WHERE Media.VolumeName = $media
3615 $self->dbh_do($query);
3617 $self->update_media();
3623 $self->can_do('autochanger_mgnt');
3625 my $ach = CGI::param('ach') ;
3626 $ach = $self->ach_get($ach);
3628 return $self->error("Bad autochanger name");
3632 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3633 $b->update_slots($ach->{name});
3641 my $arg = $self->get_form('jobid', 'limit', 'offset');
3642 unless ($arg->{jobid}) {
3643 return $self->error("Can't get jobid");
3646 if ($arg->{limit} == 100) {
3647 $arg->{limit} = 1000;
3650 my $t = CGI::param('time') || $self->{info}->{display_log_time} || '';
3652 # display only Error and Warning messages
3654 if (CGI::param('error')) {
3655 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3658 # get security filter
3659 $filter .= $self->get_client_filter();
3660 my ($filter_where, undef) = $self->get_param('username');
3663 SELECT Job.Name as name, Client.Name as clientname
3664 FROM Job INNER JOIN Client USING (ClientId) $filter
3665 WHERE JobId = $arg->{jobid} $filter_where
3668 my $row = $self->dbh_selectrow_hashref($query);
3671 return $self->error("Can't find $arg->{jobid} in catalog");
3675 SELECT Time AS time, LogText AS log
3677 WHERE ( Log.JobId = $arg->{jobid}
3678 OR (Log.JobId = 0 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3679 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3684 OFFSET $arg->{offset}
3687 my $log = $self->dbh_selectall_arrayref($query);
3689 return $self->error("Can't get log for jobid $arg->{jobid}");
3695 $logtxt = join("", map { ($_->[0] . ' ' . $_->[1]) } @$log ) ;
3697 $logtxt = join("", map { $_->[1] } @$log ) ;
3700 $self->display({ lines=> $logtxt,
3701 jobid => $arg->{jobid},
3702 name => $row->{name},
3703 client => $row->{clientname},
3704 offset => $arg->{offset},
3705 limit => $arg->{limit},
3706 }, 'display_log.tpl');
3712 $self->can_do('autochanger_mgnt');
3714 my $arg = $self->get_form('ach', 'slots', 'drive');
3716 unless ($arg->{ach}) {
3717 return $self->error("Can't find autochanger name");
3720 my $a = $self->ach_get($arg->{ach});
3722 return $self->error("Can't find autochanger name in configuration");
3725 my $storage = $a->get_drive_name($arg->{drive});
3727 return $self->error("Can't get your drive name");
3733 if ($arg->{slots}) {
3734 $slots = join(",", @{ $arg->{slots} });
3735 $slots_sql = " AND Slot IN ($slots) ";
3736 $t += 60*scalar( @{ $arg->{slots} }) ;
3739 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3740 print "<h1>This command can take long time, be patient...</h1>";
3742 $b->label_barcodes(storage => $storage,
3743 drive => $arg->{drive},
3751 SET LocationId = (SELECT LocationId
3753 WHERE Location = '$arg->{ach}')
3755 WHERE (LocationId = 0 OR LocationId IS NULL)
3764 $self->can_do('purge');
3766 my @volume = CGI::param('media');
3769 return $self->error("Can't get media selection");
3772 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3774 foreach my $v (@volume) {
3776 content => $b->purge_volume($v),
3777 title => "Purge media",
3778 name => "purge volume=$v",
3787 $self->can_do('prune');
3789 my @volume = CGI::param('media');
3791 return $self->error("Can't get media selection");
3794 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3796 foreach my $v (@volume) {
3798 content => $b->prune_volume($v),
3799 title => "Prune volume",
3800 name => "prune volume=$v",
3809 $self->can_do('cancel_job');
3811 my $arg = $self->get_form('jobid');
3812 unless ($arg->{jobid}) {
3813 return $self->error("Can't get jobid");
3816 my $b = $self->get_bconsole();
3818 content => $b->cancel($arg->{jobid}),
3819 title => "Cancel job",
3820 name => "cancel jobid=$arg->{jobid}",
3826 # Warning, we display current fileset
3829 my $arg = $self->get_form('fileset');
3831 if ($arg->{fileset}) {
3832 my $b = $self->get_bconsole();
3833 my $ret = $b->get_fileset($arg->{fileset});
3834 $self->display({ fileset => $arg->{fileset},
3836 }, "fileset_view.tpl");
3838 $self->error("Can't get fileset name");
3842 sub director_show_sched
3846 my $arg = $self->get_form('days');
3848 my $b = $self->get_bconsole();
3849 my $ret = $b->director_get_sched( $arg->{days} );
3854 }, "scheduled_job.tpl");
3857 sub enable_disable_job
3859 my ($self, $what) = @_ ;
3860 $self->can_do('run_job');
3862 my $name = CGI::param('job') || '';
3863 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3864 return $self->error("Can't find job name");
3867 my $b = $self->get_bconsole();
3877 content => $b->send_cmd("$cmd job=\"$name\""),
3878 title => "$cmd $name",
3879 name => "$cmd job=\"$name\"",
3886 return new Bconsole(pref => $self->{info});
3892 $self->can_do('run_job');
3894 my $b = $self->get_bconsole();
3896 my $joblist = [ map { { name => $_ } } $b->list_job() ];
3898 $self->display({ Jobs => $joblist }, "run_job.tpl");
3903 my ($self, $ouput) = @_;
3906 foreach my $l (split(/\r\n/, $ouput)) {
3907 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
3913 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
3919 foreach my $k (keys %arg) {
3920 $lowcase{lc($k)} = $arg{$k} ;
3929 $self->can_do('run_job');
3931 my $b = $self->get_bconsole();
3933 my $job = CGI::param('job') || '';
3935 # we take informations from director, and we overwrite with user wish
3936 my $info = $b->send_cmd("show job=\"$job\"");
3937 my $attr = $self->run_parse_job($info);
3939 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
3940 my %job_opt = (%$attr, %$arg);
3942 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3944 my $pools = [ map { { name => $_ } } $b->list_pool() ];
3945 my $clients = [ map { { name => $_ } }$b->list_client()];
3946 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
3947 my $storages= [ map { { name => $_ } }$b->list_storage()];
3952 clients => $clients,
3953 filesets => $filesets,
3954 storages => $storages,
3956 }, "run_job_mod.tpl");
3962 $self->can_do('run_job');
3964 my $b = $self->get_bconsole();
3966 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3976 $self->can_do('run_job');
3978 my $b = $self->get_bconsole();
3980 # TODO: check input (don't use pool, level)
3982 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
3983 my $job = CGI::param('job') || '';
3984 my $storage = CGI::param('storage') || '';
3986 my $jobid = $b->run(job => $job,
3987 client => $arg->{client},
3988 priority => $arg->{priority},
3989 level => $arg->{level},
3990 storage => $storage,
3991 pool => $arg->{pool},
3992 fileset => $arg->{fileset},
3993 when => $arg->{when},
3996 print $jobid, $b->{error};
3998 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";