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=''; # by default
1300 my $arg = $self->get_form("client", "qre_client",
1301 "jclient_groups", "qnotingroup");
1303 if ($arg->{qre_client}) {
1304 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1305 } elsif ($arg->{client}) {
1306 $where = "WHERE Name = '$arg->{client}' ";
1307 } elsif ($arg->{jclient_groups}) {
1308 # $filter could already contains client_group_member
1310 JOIN client_group_member USING (ClientId)
1311 JOIN client_group USING (client_group_id)
1312 WHERE client_group_name IN ($arg->{jclient_groups}) ";
1313 } elsif ($arg->{qnotingroup}) {
1316 (SELECT 1 FROM client_group_member
1317 WHERE Client.ClientId = client_group_member.ClientId
1323 SELECT Name AS name,
1325 AutoPrune AS autoprune,
1326 FileRetention AS fileretention,
1327 JobRetention AS jobretention
1328 FROM Client " . $self->get_client_filter() .
1331 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1333 my $dsp = { ID => $cur_id++,
1334 clients => [ values %$all] };
1336 $self->display($dsp, "client_list.tpl") ;
1341 my ($self, %arg) = @_;
1348 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1350 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1352 $self->{sql}->{TO_SEC}($arg{age})
1355 $label = "last " . human_sec($arg{age});
1358 if ($arg{groupby}) {
1359 $limit .= " GROUP BY $arg{groupby} ";
1363 $limit .= " ORDER BY $arg{order} ";
1367 $limit .= " LIMIT $arg{limit} ";
1368 $label .= " limited to $arg{limit}";
1372 $limit .= " OFFSET $arg{offset} ";
1373 $label .= " with $arg{offset} offset ";
1377 $label = 'no filter';
1380 return ($limit, $label);
1385 $bweb->get_form(...) - Get useful stuff
1389 This function get and check parameters against regexp.
1391 If word begin with 'q', the return will be quoted or join quoted
1392 if it's end with 's'.
1397 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1400 qclient => 'plume-fd',
1401 qpools => "'plume-fd', 'test-fd', '...'",
1408 my ($self, @what) = @_;
1409 my %what = map { $_ => 1 } @what;
1431 my %opt_ss =( # string with space
1435 my %opt_s = ( # default to ''
1456 my %opt_p = ( # option with path
1463 my %opt_r = (regexwhere => 1);
1465 my %opt_d = ( # option with date
1470 foreach my $i (@what) {
1471 if (exists $opt_i{$i}) {# integer param
1472 my $value = CGI::param($i) || $opt_i{$i} ;
1473 if ($value =~ /^(\d+)$/) {
1476 } elsif ($opt_s{$i}) { # simple string param
1477 my $value = CGI::param($i) || '';
1478 if ($value =~ /^([\w\d\.-]+)$/) {
1481 } elsif ($opt_ss{$i}) { # simple string param (with space)
1482 my $value = CGI::param($i) || '';
1483 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1486 } elsif ($i =~ /^j(\w+)s$/) { # quote join args "'arg1', 'arg2'"
1487 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1489 $ret{$i} = $self->dbh_join(@value) ;
1492 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1493 my $value = CGI::param($1) ;
1495 $ret{$i} = $self->dbh_quote($value);
1498 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1499 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1500 grep { ! /^\s*$/ } CGI::param($1) ];
1501 } elsif (exists $opt_p{$i}) {
1502 my $value = CGI::param($i) || '';
1503 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1506 } elsif (exists $opt_r{$i}) {
1507 my $value = CGI::param($i) || '';
1508 if ($value =~ /^([^'"']+)$/) {
1511 } elsif (exists $opt_d{$i}) {
1512 my $value = CGI::param($i) || '';
1513 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1520 foreach my $s (CGI::param('slot')) {
1521 if ($s =~ /^(\d+)$/) {
1522 push @{$ret{slots}}, $s;
1528 my $when = CGI::param('when') || '';
1529 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1534 if ($what{db_clients}) {
1536 if ($what{filter}) {
1537 # get security filter only if asked
1538 $filter = $self->get_client_filter();
1542 SELECT Client.Name as clientname
1546 my $clients = $self->dbh_selectall_hashref($query, 'clientname');
1547 $ret{db_clients} = [sort {$a->{clientname} cmp $b->{clientname} }
1551 if ($what{db_client_groups}) {
1553 if ($what{filter}) {
1554 # get security filter only if asked
1555 $filter = $self->get_client_group_filter();
1559 SELECT client_group_name AS name
1560 FROM client_group $filter
1563 my $grps = $self->dbh_selectall_hashref($query, 'name');
1564 $ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} }
1568 if ($what{db_usernames}) {
1574 my $users = $self->dbh_selectall_hashref($query, 'username');
1575 $ret{db_usernames} = [sort {$a->{username} cmp $b->{username} }
1579 if ($what{db_roles}) {
1585 my $r = $self->dbh_selectall_hashref($query, 'rolename');
1586 $ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} }
1590 if ($what{db_mediatypes}) {
1592 SELECT MediaType as mediatype
1596 my $media = $self->dbh_selectall_hashref($query, 'mediatype');
1597 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1601 if ($what{db_locations}) {
1603 SELECT Location as location, Cost as cost
1606 my $loc = $self->dbh_selectall_hashref($query, 'location');
1607 $ret{db_locations} = [ sort { $a->{location}
1613 if ($what{db_pools}) {
1614 my $query = "SELECT Name as name FROM Pool";
1616 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1617 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1620 if ($what{db_filesets}) {
1622 SELECT FileSet.FileSet AS fileset
1626 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1628 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1629 values %$filesets] ;
1632 if ($what{db_jobnames}) {
1634 if ($what{filter}) {
1635 $filter = " JOIN Client USING (ClientId) " . $self->get_client_filter();
1638 SELECT DISTINCT Job.Name AS jobname
1642 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1644 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1645 values %$jobnames] ;
1648 if ($what{db_devices}) {
1650 SELECT Device.Name AS name
1654 my $devices = $self->dbh_selectall_hashref($query, 'name');
1656 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1667 my $fields = $self->get_form(qw/age level status clients filesets
1668 graph gtype type filter db_clients
1669 limit db_filesets width height
1670 qclients qfilesets qjobnames db_jobnames/);
1673 my $url = CGI::url(-full => 0,
1676 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1678 # this organisation is to keep user choice between 2 click
1679 # TODO : fileset and client selection doesn't work
1688 sub get_selected_media_location
1692 my $media = $self->get_form('jmedias');
1694 unless ($media->{jmedias}) {
1699 SELECT Media.VolumeName AS volumename, Location.Location AS location
1700 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1701 WHERE Media.VolumeName IN ($media->{jmedias})
1704 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1706 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1715 my ($self, $in) = @_ ;
1717 my $media = $self->get_selected_media_location();
1723 my $elt = $self->get_form('db_locations');
1725 $self->display({ ID => $cur_id++,
1726 enabled => human_enabled($in),
1727 %$elt, # db_locations
1729 sort { $a->{volumename} cmp $b->{volumename} } values %$media
1739 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1741 $self->display($elt, "help_extern.tpl");
1744 sub help_extern_compute
1748 my $number = CGI::param('limit') || '' ;
1749 unless ($number =~ /^(\d+)$/) {
1750 return $self->error("Bad arg number : $number ");
1753 my ($sql, undef) = $self->get_param('pools',
1754 'locations', 'mediatypes');
1757 SELECT Media.VolumeName AS volumename,
1758 Media.VolStatus AS volstatus,
1759 Media.LastWritten AS lastwritten,
1760 Media.MediaType AS mediatype,
1761 Media.VolMounts AS volmounts,
1763 Media.Recycle AS recycle,
1764 $self->{sql}->{FROM_UNIXTIME}(
1765 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1766 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1769 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1770 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1772 WHERE Media.InChanger = 1
1773 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1775 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1779 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1781 $self->display({ Media => [ values %$all ] },
1782 "help_extern_compute.tpl");
1789 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1790 $self->display($param, "help_intern.tpl");
1793 sub help_intern_compute
1797 my $number = CGI::param('limit') || '' ;
1798 unless ($number =~ /^(\d+)$/) {
1799 return $self->error("Bad arg number : $number ");
1802 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1804 if (CGI::param('expired')) {
1806 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1807 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1813 SELECT Media.VolumeName AS volumename,
1814 Media.VolStatus AS volstatus,
1815 Media.LastWritten AS lastwritten,
1816 Media.MediaType AS mediatype,
1817 Media.VolMounts AS volmounts,
1819 $self->{sql}->{FROM_UNIXTIME}(
1820 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1821 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1824 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1825 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1827 WHERE Media.InChanger <> 1
1828 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1829 AND Media.Recycle = 1
1831 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1835 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1837 $self->display({ Media => [ values %$all ] },
1838 "help_intern_compute.tpl");
1844 my ($self, %arg) = @_ ;
1846 my ($limit, $label) = $self->get_limit(%arg);
1850 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1851 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1852 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1853 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1854 ($self->{sql}->{DB_SIZE}) AS db_size,
1855 (SELECT count(Job.JobId)
1857 WHERE Job.JobStatus IN ('E','e','f','A')
1860 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1863 my $row = $self->dbh_selectrow_hashref($query) ;
1865 $row->{nb_bytes} = human_size($row->{nb_bytes});
1867 $row->{db_size} = human_size($row->{db_size});
1868 $row->{label} = $label;
1870 $self->display($row, "general.tpl");
1875 my ($self, @what) = @_ ;
1876 my %elt = map { $_ => 1 } @what;
1881 if ($elt{clients}) {
1882 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1884 $ret{clients} = \@clients;
1885 my $str = $self->dbh_join(@clients);
1886 $limit .= "AND Client.Name IN ($str) ";
1890 if ($elt{client_groups}) {
1891 my @clients = grep { ! /^\s*$/ } CGI::param('client_group');
1893 $ret{client_groups} = \@clients;
1894 my $str = $self->dbh_join(@clients);
1895 $limit .= "AND client_group_name IN ($str) ";
1899 if ($elt{filesets}) {
1900 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1902 $ret{filesets} = \@filesets;
1903 my $str = $self->dbh_join(@filesets);
1904 $limit .= "AND FileSet.FileSet IN ($str) ";
1908 if ($elt{mediatypes}) {
1909 my @media = grep { ! /^\s*$/ } CGI::param('mediatype');
1911 $ret{mediatypes} = \@media;
1912 my $str = $self->dbh_join(@media);
1913 $limit .= "AND Media.MediaType IN ($str) ";
1918 my $client = CGI::param('client');
1919 $ret{client} = $client;
1920 $client = $self->dbh_join($client);
1921 $limit .= "AND Client.Name = $client ";
1925 my $level = CGI::param('level') || '';
1926 if ($level =~ /^(\w)$/) {
1928 $limit .= "AND Job.Level = '$1' ";
1933 my $jobid = CGI::param('jobid') || '';
1935 if ($jobid =~ /^(\d+)$/) {
1937 $limit .= "AND Job.JobId = '$1' ";
1942 my $status = CGI::param('status') || '';
1943 if ($status =~ /^(\w)$/) {
1946 $limit .= "AND Job.JobStatus IN ('f','E') ";
1947 } elsif ($1 eq 'W') {
1948 $limit .= "AND Job.JobStatus = 'T' AND Job.JobErrors > 0 ";
1950 $limit .= "AND Job.JobStatus = '$1' ";
1955 if ($elt{volstatus}) {
1956 my $status = CGI::param('volstatus') || '';
1957 if ($status =~ /^(\w+)$/) {
1959 $limit .= "AND Media.VolStatus = '$1' ";
1963 if ($elt{locations}) {
1964 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1966 $ret{locations} = \@location;
1967 my $str = $self->dbh_join(@location);
1968 $limit .= "AND Location.Location IN ($str) ";
1973 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
1975 $ret{pools} = \@pool;
1976 my $str = $self->dbh_join(@pool);
1977 $limit .= "AND Pool.Name IN ($str) ";
1981 if ($elt{location}) {
1982 my $location = CGI::param('location') || '';
1984 $ret{location} = $location;
1985 $location = $self->dbh_quote($location);
1986 $limit .= "AND Location.Location = $location ";
1991 my $pool = CGI::param('pool') || '';
1994 $pool = $self->dbh_quote($pool);
1995 $limit .= "AND Pool.Name = $pool ";
1999 if ($elt{jobtype}) {
2000 my $jobtype = CGI::param('jobtype') || '';
2001 if ($jobtype =~ /^(\w)$/) {
2003 $limit .= "AND Job.Type = '$1' ";
2007 return ($limit, %ret);
2018 my ($self, %arg) = @_ ;
2020 $arg{order} = ' Job.JobId DESC ';
2022 my ($limit, $label) = $self->get_limit(%arg);
2023 my ($where, undef) = $self->get_param('clients',
2032 if (CGI::param('client_group')) {
2034 JOIN client_group_member USING (ClientId)
2035 JOIN client_group USING (client_group_id)
2038 my $filter = $self->get_client_filter();
2041 SELECT Job.JobId AS jobid,
2042 Client.Name AS client,
2043 FileSet.FileSet AS fileset,
2044 Job.Name AS jobname,
2046 StartTime AS starttime,
2048 Pool.Name AS poolname,
2049 JobFiles AS jobfiles,
2050 JobBytes AS jobbytes,
2051 JobStatus AS jobstatus,
2052 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2053 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2056 JobErrors AS joberrors
2058 FROM Client $filter $cgq,
2059 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2060 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2061 WHERE Client.ClientId=Job.ClientId
2062 AND Job.JobStatus NOT IN ('R', 'C')
2067 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2069 $self->display({ Filter => $label,
2073 sort { $a->{jobid} <=> $b->{jobid} }
2080 # display job informations
2081 sub display_job_zoom
2083 my ($self, $jobid) = @_ ;
2085 $jobid = $self->dbh_quote($jobid);
2087 # get security filter
2088 my $filter = $self->get_client_filter();
2091 SELECT DISTINCT Job.JobId AS jobid,
2092 Client.Name AS client,
2093 Job.Name AS jobname,
2094 FileSet.FileSet AS fileset,
2096 Pool.Name AS poolname,
2097 StartTime AS starttime,
2098 JobFiles AS jobfiles,
2099 JobBytes AS jobbytes,
2100 JobStatus AS jobstatus,
2101 JobErrors AS joberrors,
2102 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2103 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
2105 FROM Client $filter,
2106 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2107 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2108 WHERE Client.ClientId=Job.ClientId
2109 AND Job.JobId = $jobid
2112 my $row = $self->dbh_selectrow_hashref($query) ;
2114 # display all volumes associate with this job
2116 SELECT Media.VolumeName as volumename
2117 FROM Job,Media,JobMedia
2118 WHERE Job.JobId = $jobid
2119 AND JobMedia.JobId=Job.JobId
2120 AND JobMedia.MediaId=Media.MediaId
2123 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2125 $row->{volumes} = [ values %$all ] ;
2127 $self->display($row, "display_job_zoom.tpl");
2130 sub display_job_group
2132 my ($self, %arg) = @_;
2134 my ($limit, $label) = $self->get_limit(groupby => 'client_group_name', %arg);
2136 my ($where, undef) = $self->get_param('client_groups',
2139 my $filter = $self->get_client_group_filter();
2142 SELECT client_group_name AS client_group_name,
2143 COALESCE(jobok.jobfiles,0) + COALESCE(joberr.jobfiles,0) AS jobfiles,
2144 COALESCE(jobok.jobbytes,0) + COALESCE(joberr.jobbytes,0) AS jobbytes,
2145 COALESCE(jobok.joberrors,0) + COALESCE(joberr.joberrors,0) AS joberrors,
2146 COALESCE(jobok.nbjobs,0) AS nbjobok,
2147 COALESCE(joberr.nbjobs,0) AS nbjoberr,
2148 COALESCE(jobok.duration, '0:0:0') AS duration
2150 FROM client_group $filter LEFT JOIN (
2151 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2152 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2153 SUM(JobErrors) AS joberrors,
2154 SUM($self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2155 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)))
2158 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2159 JOIN client_group USING (client_group_id)
2161 WHERE JobStatus = 'T'
2164 ) AS jobok USING (client_group_name) LEFT JOIN
2167 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2168 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2169 SUM(JobErrors) AS joberrors
2170 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2171 JOIN client_group USING (client_group_id)
2173 WHERE JobStatus IN ('f','E', 'A')
2176 ) AS joberr USING (client_group_name)
2180 my $all = $self->dbh_selectall_hashref($query, 'client_group_name');
2182 my $rep = { groups => [ values %$all ], age => $arg{age}, filter => $label };
2185 $self->display($rep, "display_job_group.tpl");
2190 my ($self, %arg) = @_ ;
2192 my ($limit, $label) = $self->get_limit(%arg);
2193 my ($where, %elt) = $self->get_param('pools',
2198 my $arg = $self->get_form('jmedias', 'qre_media', 'expired');
2200 if ($arg->{jmedias}) {
2201 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2203 if ($arg->{qre_media}) {
2204 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2206 if ($arg->{expired}) {
2208 AND VolStatus = 'Full'
2209 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2210 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2211 ) < NOW() " . $where ;
2215 SELECT Media.VolumeName AS volumename,
2216 Media.VolBytes AS volbytes,
2217 Media.VolStatus AS volstatus,
2218 Media.MediaType AS mediatype,
2219 Media.InChanger AS online,
2220 Media.LastWritten AS lastwritten,
2221 Location.Location AS location,
2222 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2223 Pool.Name AS poolname,
2224 $self->{sql}->{FROM_UNIXTIME}(
2225 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2226 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2229 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2230 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2231 Media.MediaType AS MediaType
2233 WHERE Media.VolStatus = 'Full'
2234 GROUP BY Media.MediaType
2235 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2237 WHERE Media.PoolId=Pool.PoolId
2242 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2244 $self->display({ ID => $cur_id++,
2246 Location => $elt{location},
2247 Media => [ values %$all ],
2249 "display_media.tpl");
2252 sub display_allmedia
2256 my $pool = $self->get_form('db_pools');
2258 foreach my $name (@{ $pool->{db_pools} }) {
2259 CGI::param('pool', $name->{name});
2260 $self->display_media();
2264 sub display_media_zoom
2268 my $media = $self->get_form('jmedias');
2270 unless ($media->{jmedias}) {
2271 return $self->error("Can't get media selection");
2275 SELECT InChanger AS online,
2276 Media.Enabled AS enabled,
2277 VolBytes AS nb_bytes,
2278 VolumeName AS volumename,
2279 VolStatus AS volstatus,
2280 VolMounts AS nb_mounts,
2281 Media.VolUseDuration AS voluseduration,
2282 Media.MaxVolJobs AS maxvoljobs,
2283 Media.MaxVolFiles AS maxvolfiles,
2284 Media.MaxVolBytes AS maxvolbytes,
2285 VolErrors AS nb_errors,
2286 Pool.Name AS poolname,
2287 Location.Location AS location,
2288 Media.Recycle AS recycle,
2289 Media.VolRetention AS volretention,
2290 Media.LastWritten AS lastwritten,
2291 Media.VolReadTime/1000000 AS volreadtime,
2292 Media.VolWriteTime/1000000 AS volwritetime,
2293 Media.RecycleCount AS recyclecount,
2294 Media.Comment AS comment,
2295 $self->{sql}->{FROM_UNIXTIME}(
2296 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2297 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2300 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2301 WHERE Pool.PoolId = Media.PoolId
2302 AND VolumeName IN ($media->{jmedias})
2305 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2307 foreach my $media (values %$all) {
2308 my $mq = $self->dbh_quote($media->{volumename});
2311 SELECT DISTINCT Job.JobId AS jobid,
2313 Job.StartTime AS starttime,
2316 Job.JobFiles AS files,
2317 Job.JobBytes AS bytes,
2318 Job.jobstatus AS status
2319 FROM Media,JobMedia,Job
2320 WHERE Media.VolumeName=$mq
2321 AND Media.MediaId=JobMedia.MediaId
2322 AND JobMedia.JobId=Job.JobId
2325 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2328 SELECT LocationLog.Date AS date,
2329 Location.Location AS location,
2330 LocationLog.Comment AS comment
2331 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2332 WHERE Media.MediaId = LocationLog.MediaId
2333 AND Media.VolumeName = $mq
2337 my $log = $self->dbh_selectall_arrayref($query) ;
2339 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2342 $self->display({ jobs => [ values %$jobs ],
2343 LocationLog => $logtxt,
2345 "display_media_zoom.tpl");
2352 $self->can_do('location_mgnt');
2354 my $loc = $self->get_form('qlocation');
2355 unless ($loc->{qlocation}) {
2356 return $self->error("Can't get location");
2360 SELECT Location.Location AS location,
2361 Location.Cost AS cost,
2362 Location.Enabled AS enabled
2364 WHERE Location.Location = $loc->{qlocation}
2367 my $row = $self->dbh_selectrow_hashref($query);
2369 $self->display({ ID => $cur_id++,
2370 %$row }, "location_edit.tpl") ;
2376 $self->can_do('location_mgnt');
2378 my $arg = $self->get_form(qw/qlocation qnewlocation cost/) ;
2379 unless ($arg->{qlocation}) {
2380 return $self->error("Can't get location");
2382 unless ($arg->{qnewlocation}) {
2383 return $self->error("Can't get new location name");
2385 unless ($arg->{cost}) {
2386 return $self->error("Can't get new cost");
2389 my $enabled = CGI::param('enabled') || '';
2390 $enabled = $enabled?1:0;
2393 UPDATE Location SET Cost = $arg->{cost},
2394 Location = $arg->{qnewlocation},
2396 WHERE Location.Location = $arg->{qlocation}
2399 $self->dbh_do($query);
2401 $self->location_display();
2407 $self->can_do('location_mgnt');
2409 my $arg = $self->get_form(qw/qlocation/) ;
2411 unless ($arg->{qlocation}) {
2412 return $self->error("Can't get location");
2416 SELECT count(Media.MediaId) AS nb
2417 FROM Media INNER JOIN Location USING (LocationID)
2418 WHERE Location = $arg->{qlocation}
2421 my $res = $self->dbh_selectrow_hashref($query);
2424 return $self->error("Sorry, the location must be empty");
2428 DELETE FROM Location WHERE Location = $arg->{qlocation} LIMIT 1
2431 $self->dbh_do($query);
2433 $self->location_display();
2439 $self->can_do('location_mgnt');
2441 my $arg = $self->get_form(qw/qlocation cost/) ;
2443 unless ($arg->{qlocation}) {
2444 $self->display({}, "location_add.tpl");
2447 unless ($arg->{cost}) {
2448 return $self->error("Can't get new cost");
2451 my $enabled = CGI::param('enabled') || '';
2452 $enabled = $enabled?1:0;
2455 INSERT INTO Location (Location, Cost, Enabled)
2456 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2459 $self->dbh_do($query);
2461 $self->location_display();
2464 sub location_display
2469 SELECT Location.Location AS location,
2470 Location.Cost AS cost,
2471 Location.Enabled AS enabled,
2472 (SELECT count(Media.MediaId)
2474 WHERE Media.LocationId = Location.LocationId
2479 my $location = $self->dbh_selectall_hashref($query, 'location');
2481 $self->display({ ID => $cur_id++,
2482 Locations => [ values %$location ] },
2483 "display_location.tpl");
2490 my $media = $self->get_selected_media_location();
2495 my $arg = $self->get_form('db_locations', 'qnewlocation');
2497 $self->display({ email => $self->{info}->{email_media},
2499 media => [ values %$media ],
2501 "update_location.tpl");
2504 ###########################################################
2509 $self->can_do('group_mgnt');
2511 my $grp = $self->get_form(qw/qclient_group db_clients/);
2513 unless ($grp->{qclient_group}) {
2514 return $self->error("Can't get group");
2519 FROM Client JOIN client_group_member using (clientid)
2520 JOIN client_group using (client_group_id)
2521 WHERE client_group_name = $grp->{qclient_group}
2524 my $row = $self->dbh_selectall_hashref($query, "name");
2526 $self->display({ ID => $cur_id++,
2527 client_group => $grp->{qclient_group},
2529 client_group_member => [ values %$row]},
2536 $self->can_do('group_mgnt');
2538 my $arg = $self->get_form(qw/qclient_group jclients qnewgroup/);
2539 unless ($arg->{qclient_group}) {
2540 return $self->error("Can't get groups");
2543 $self->{dbh}->begin_work();
2546 DELETE FROM client_group_member
2547 WHERE client_group_id IN
2548 (SELECT client_group_id
2550 WHERE client_group_name = $arg->{qclient_group})
2552 $self->dbh_do($query);
2555 INSERT INTO client_group_member (clientid, client_group_id)
2557 (SELECT client_group_id
2559 WHERE client_group_name = $arg->{qclient_group})
2560 FROM Client WHERE Name IN ($arg->{jclients})
2563 $self->dbh_do($query);
2565 if ($arg->{qclient_group} ne $arg->{qnewgroup}) {
2568 SET client_group_name = $arg->{qnewgroup}
2569 WHERE client_group_name = $arg->{qclient_group}
2572 $self->dbh_do($query);
2575 $self->{dbh}->commit() or $self->error($self->{dbh}->errstr);
2577 $self->display_groups();
2583 $self->can_do('group_mgnt');
2585 my $arg = $self->get_form(qw/qclient_group/);
2587 unless ($arg->{qclient_group}) {
2588 return $self->error("Can't get groups");
2591 $self->{dbh}->begin_work();
2594 DELETE FROM client_group_member
2595 WHERE client_group_id IN
2596 (SELECT client_group_id
2598 WHERE client_group_name = $arg->{qclient_group});
2600 DELETE FROM bweb_client_group_acl
2601 WHERE client_group_id IN
2602 (SELECT client_group_id
2604 WHERE client_group_name = $arg->{qclient_group});
2606 DELETE FROM client_group
2607 WHERE client_group_name = $arg->{qclient_group};
2609 $self->dbh_do($query);
2611 $self->{dbh}->commit();
2613 $self->display_groups();
2620 $self->can_do('group_mgnt');
2622 my $arg = $self->get_form(qw/qclient_group/) ;
2624 unless ($arg->{qclient_group}) {
2625 $self->display({}, "groups_add.tpl");
2630 INSERT INTO client_group (client_group_name)
2631 VALUES ($arg->{qclient_group})
2634 $self->dbh_do($query);
2636 $self->display_groups();
2643 my $arg = $self->get_form(qw/db_client_groups/) ;
2645 if ($self->{dbh}->errstr) {
2646 return $self->error("Can't use groups with bweb, read INSTALL to enable them");
2651 $self->display({ ID => $cur_id++,
2653 "display_groups.tpl");
2656 ###########################################################
2658 # TODO: avoir un mode qui coupe le programme avec une page d'erreur
2659 # we can also get all security and fill {security} hash
2662 my ($self, $action) = @_;
2663 # is security enabled in configuration ?
2664 if (not $self->{info}->{enable_security}) {
2667 # admin is a special user that can do everything
2668 if ($self->{loginname} eq 'admin') {
2672 if (!$self->{loginname}) {
2673 $self->error("Can't do $action, your are not logged. " .
2674 "Check security with your administrator");
2675 $self->display_end();
2679 if ($self->{security}->{$action}) {
2682 my ($u, $r) = ($self->dbh_quote($self->{loginname}),
2683 $self->dbh_quote($action));
2685 SELECT 1, username, rolename
2687 JOIN bweb_role_member USING (userid)
2688 JOIN bweb_role USING (roleid)
2693 my $row = $self->dbh_selectrow_hashref($query);
2694 # do cache with this role
2696 $self->error("$u sorry, but this action ($action) is not permited. " .
2697 "Check security with your administrator");
2698 $self->display_end();
2701 $self->{security}->{$row->{rolename}} = 1;
2705 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2706 sub get_client_filter
2709 if ($self->{info}->{enable_security}) {
2710 my $u = $self->dbh_quote($self->{loginname});
2712 JOIN (SELECT ClientId FROM client_group_member
2713 JOIN client_group USING (client_group_id)
2714 JOIN bweb_client_group_acl USING (client_group_id)
2715 JOIN bweb_user USING (userid)
2716 WHERE bweb_user.username = $u
2717 ) AS filter USING (ClientId)";
2723 #JOIN client_group USING (client_group_id)" . $b->get_client_group_filter()
2724 sub get_client_group_filter
2727 if ($self->{info}->{enable_security}) {
2728 my $u = $self->dbh_quote($self->{loginname});
2730 JOIN (SELECT client_group_id
2731 FROM bweb_client_group_acl
2732 JOIN bweb_user USING (userid)
2733 WHERE bweb_user.username = $u
2734 ) AS filter USING (client_group_id)";
2740 # role and username have to be quoted before
2741 # role and username can be a quoted list
2744 my ($self, $role, $username) = @_;
2745 $self->can_do("user_mgnt");
2747 my $nb = $self->dbh_do("
2748 DELETE FROM bweb_role_member
2749 WHERE roleid = (SELECT roleid FROM bweb_role
2750 WHERE rolename IN ($role))
2751 AND userid = (SELECT userid FROM bweb_user
2752 WHERE username IN ($username))");
2756 # role and username have to be quoted before
2757 # role and username can be a quoted list
2760 my ($self, $role, $username) = @_;
2761 $self->can_do("user_mgnt");
2763 my $nb = $self->dbh_do("
2764 INSERT INTO bweb_role_member (roleid, userid)
2765 SELECT roleid, userid FROM bweb_role, bweb_user
2766 WHERE rolename IN ($role)
2767 AND username IN ($username)
2772 # role and username have to be quoted before
2773 # role and username can be a quoted list
2776 my ($self, $copy, $user) = @_;
2777 $self->can_do("user_mgnt");
2779 my $nb = $self->dbh_do("
2780 INSERT INTO bweb_role_member (roleid, userid)
2781 SELECT roleid, a.userid
2782 FROM bweb_user AS a, bweb_role_member
2783 JOIN bweb_user USING (userid)
2784 WHERE bweb_user.username = $copy
2785 AND a.username = $user");
2789 # username can be a join quoted list of usernames
2792 my ($self, $username) = @_;
2793 $self->can_do("user_mgnt");
2796 DELETE FROM bweb_role_member
2800 WHERE username in ($username))");
2802 DELETE FROM bweb_client_group_acl
2806 WHERE username IN ($username))");
2813 $self->can_do("user_mgnt");
2815 my $arg = $self->get_form(qw/jusernames/);
2817 unless ($arg->{jusernames}) {
2818 return $self->error("Can't get user");
2821 $self->{dbh}->begin_work();
2823 $self->revoke_all($arg->{jusernames});
2825 DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2827 $self->{dbh}->commit();
2829 $self->display_users();
2835 $self->can_do("user_mgnt");
2837 # we don't quote username directly to check that it is conform
2838 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username jclient_groups/) ;
2840 if (not $arg->{qcreate}) {
2841 $arg = $self->get_form(qw/db_roles db_usernames db_client_groups/);
2842 $self->display($arg, "display_user.tpl");
2846 my $u = $self->dbh_quote($arg->{username});
2848 if (!$arg->{qpasswd}) {
2849 $arg->{qpasswd} = "''";
2851 if (!$arg->{qcomment}) {
2852 $arg->{qcomment} = "''";
2855 # will fail if user already exists
2857 UPDATE bweb_user SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment}
2858 WHERE username = $u")
2861 INSERT INTO bweb_user (username, passwd, comment)
2862 VALUES ($u, $arg->{qpasswd}, $arg->{qcomment})");
2864 $self->{dbh}->begin_work();
2866 $self->revoke_all($u);
2868 if ($arg->{qcopy_username}) {
2869 $self->grant_like($arg->{qcopy_username}, $u);
2871 $self->grant($arg->{jrolenames}, $u);
2875 INSERT INTO bweb_client_group_acl (client_group_id, userid)
2876 SELECT client_group_id, userid
2877 FROM client_group, bweb_user
2878 WHERE client_group_name IN ($arg->{jclient_groups})
2883 $self->{dbh}->commit();
2885 $self->display_users();
2888 # TODO: we miss a matrix with all user/roles
2892 $self->can_do("user_mgnt");
2894 my $arg = $self->get_form(qw/db_usernames/) ;
2896 if ($self->{dbh}->errstr) {
2897 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2900 $self->display({ ID => $cur_id++,
2902 "display_users.tpl");
2908 $self->can_do("user_mgnt");
2910 my $arg = $self->get_form('username');
2911 my $user = $self->dbh_quote($arg->{username});
2913 my $userp = $self->dbh_selectrow_hashref("
2914 SELECT username, passwd, comment
2916 WHERE username = $user
2920 return $self->error("Can't find $user in catalog");
2922 $arg = $self->get_form(qw/db_usernames db_client_groups/);
2923 my $arg2 = $self->get_form(qw/filter db_client_groups/);
2926 #------------+--------
2931 my $role = $self->dbh_selectall_hashref("
2932 SELECT rolename, temp.userid
2934 LEFT JOIN (SELECT roleid, userid
2935 FROM bweb_user JOIN bweb_role_member USING (userid)
2936 WHERE username = $user) AS temp USING (roleid)
2941 db_usernames => $arg->{db_usernames},
2942 username => $userp->{username},
2943 comment => $userp->{comment},
2944 passwd => $userp->{passwd},
2945 db_client_groups => $arg->{db_client_groups},
2946 client_group => $arg2->{db_client_groups},
2947 db_roles => [ values %$role],
2948 }, "display_user.tpl");
2952 ###########################################################
2954 sub get_media_max_size
2956 my ($self, $type) = @_;
2958 "SELECT avg(VolBytes) AS size
2960 WHERE Media.VolStatus = 'Full'
2961 AND Media.MediaType = '$type'
2964 my $res = $self->selectrow_hashref($query);
2967 return $res->{size};
2977 my $media = $self->get_form('qmedia');
2979 unless ($media->{qmedia}) {
2980 return $self->error("Can't get media");
2984 SELECT Media.Slot AS slot,
2985 PoolMedia.Name AS poolname,
2986 Media.VolStatus AS volstatus,
2987 Media.InChanger AS inchanger,
2988 Location.Location AS location,
2989 Media.VolumeName AS volumename,
2990 Media.MaxVolBytes AS maxvolbytes,
2991 Media.MaxVolJobs AS maxvoljobs,
2992 Media.MaxVolFiles AS maxvolfiles,
2993 Media.VolUseDuration AS voluseduration,
2994 Media.VolRetention AS volretention,
2995 Media.Comment AS comment,
2996 PoolRecycle.Name AS poolrecycle,
2997 Media.Enabled AS enabled
2999 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
3000 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
3001 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3003 WHERE Media.VolumeName = $media->{qmedia}
3006 my $row = $self->dbh_selectrow_hashref($query);
3007 $row->{volretention} = human_sec($row->{volretention});
3008 $row->{voluseduration} = human_sec($row->{voluseduration});
3009 $row->{enabled} = human_enabled($row->{enabled});
3011 my $elt = $self->get_form(qw/db_pools db_locations/);
3016 }, "update_media.tpl");
3022 $self->can_do('media_mgnt');
3024 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
3026 unless ($arg->{jmedias}) {
3027 return $self->error("Can't get selected media");
3030 unless ($arg->{qnewlocation}) {
3031 return $self->error("Can't get new location");
3036 SET LocationId = (SELECT LocationId
3038 WHERE Location = $arg->{qnewlocation})
3039 WHERE Media.VolumeName IN ($arg->{jmedias})
3042 my $nb = $self->dbh_do($query);
3044 print "$nb media updated, you may have to update your autochanger.";
3046 $self->display_media();
3052 $self->can_do('media_mgnt');
3054 my $media = $self->get_selected_media_location();
3056 return $self->error("Can't get media selection");
3058 my $newloc = CGI::param('newlocation');
3060 my $user = CGI::param('user') || 'unknown';
3061 my $comm = CGI::param('comment') || '';
3062 $comm = $self->dbh_quote("$user: $comm");
3064 my $arg = $self->get_form('enabled');
3065 my $en = human_enabled($arg->{enabled});
3066 my $b = $self->get_bconsole();
3069 foreach my $vol (keys %$media) {
3071 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3073 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3074 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3075 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3078 $self->dbh_do($query);
3079 $self->debug($query);
3080 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3085 $q->param('action', 'update_location');
3086 my $url = $q->url(-full => 1, -query=>1);
3088 $self->display({ email => $self->{info}->{email_media},
3090 newlocation => $newloc,
3091 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3092 media => [ values %$media ],
3094 "change_location.tpl");
3098 sub display_client_stats
3100 my ($self, %arg) = @_ ;
3102 my $client = $self->dbh_quote($arg{clientname});
3103 # get security filter
3104 my $filter = $self->get_client_filter();
3106 my ($limit, $label) = $self->get_limit(%arg);
3109 count(Job.JobId) AS nb_jobs,
3110 sum(Job.JobBytes) AS nb_bytes,
3111 sum(Job.JobErrors) AS nb_err,
3112 sum(Job.JobFiles) AS nb_files,
3113 Client.Name AS clientname
3114 FROM Job JOIN Client USING (ClientId) $filter
3116 Client.Name = $client
3118 GROUP BY Client.Name
3121 my $row = $self->dbh_selectrow_hashref($query);
3123 $row->{ID} = $cur_id++;
3124 $row->{label} = $label;
3125 $row->{grapharg} = "client";
3127 $self->display($row, "display_client_stats.tpl");
3131 sub display_group_stats
3133 my ($self, %arg) = @_ ;
3135 my $carg = $self->get_form(qw/qclient_group/);
3137 unless ($carg->{qclient_group}) {
3138 return $self->error("Can't get group");
3141 my ($limit, $label) = $self->get_limit(%arg);
3145 count(Job.JobId) AS nb_jobs,
3146 sum(Job.JobBytes) AS nb_bytes,
3147 sum(Job.JobErrors) AS nb_err,
3148 sum(Job.JobFiles) AS nb_files,
3149 client_group.client_group_name AS clientname
3150 FROM Job JOIN Client USING (ClientId)
3151 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3152 JOIN client_group USING (client_group_id)
3154 client_group.client_group_name = $carg->{qclient_group}
3156 GROUP BY client_group.client_group_name
3159 my $row = $self->dbh_selectrow_hashref($query);
3161 $row->{ID} = $cur_id++;
3162 $row->{label} = $label;
3163 $row->{grapharg} = "client_group";
3165 $self->display($row, "display_client_stats.tpl");
3168 # poolname can be undef
3171 my ($self, $poolname) = @_ ;
3175 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3176 if ($arg->{jmediatypes}) {
3177 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3178 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3181 # TODO : afficher les tailles et les dates
3184 SELECT subq.volmax AS volmax,
3185 subq.volnum AS volnum,
3186 subq.voltotal AS voltotal,
3188 Pool.Recycle AS recycle,
3189 Pool.VolRetention AS volretention,
3190 Pool.VolUseDuration AS voluseduration,
3191 Pool.MaxVolJobs AS maxvoljobs,
3192 Pool.MaxVolFiles AS maxvolfiles,
3193 Pool.MaxVolBytes AS maxvolbytes,
3194 subq.PoolId AS PoolId,
3195 subq.MediaType AS mediatype,
3196 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3199 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3200 count(Media.MediaId) AS volnum,
3201 sum(Media.VolBytes) AS voltotal,
3202 Media.PoolId AS PoolId,
3203 Media.MediaType AS MediaType
3205 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3206 Media.MediaType AS MediaType
3208 WHERE Media.VolStatus = 'Full'
3209 GROUP BY Media.MediaType
3210 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3211 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3213 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3217 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3220 SELECT Pool.Name AS name,
3221 sum(VolBytes) AS size
3222 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3223 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3227 my $empty = $self->dbh_selectall_hashref($query, 'name');
3229 foreach my $p (values %$all) {
3230 if ($p->{volmax} > 0) { # mysql returns 0.0000
3231 # we remove Recycled/Purged media from pool usage
3232 if (defined $empty->{$p->{name}}) {
3233 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3235 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3237 $p->{poolusage} = 0;
3241 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3243 WHERE PoolId=$p->{poolid}
3244 AND Media.MediaType = '$p->{mediatype}'
3248 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3249 foreach my $t (values %$content) {
3250 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3255 $self->display({ ID => $cur_id++,
3256 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3257 Pools => [ values %$all ]},
3258 "display_pool.tpl");
3261 sub display_running_job
3265 my $arg = $self->get_form('client', 'jobid');
3267 if (!$arg->{client} and $arg->{jobid}) {
3268 # get security filter
3269 my $filter = $self->get_client_filter();
3272 SELECT Client.Name AS name
3273 FROM Job INNER JOIN Client USING (ClientId) $filter
3274 WHERE Job.JobId = $arg->{jobid}
3277 my $row = $self->dbh_selectrow_hashref($query);
3280 $arg->{client} = $row->{name};
3281 CGI::param('client', $arg->{client});
3285 if ($arg->{client}) {
3286 my $cli = new Bweb::Client(name => $arg->{client});
3287 $cli->display_running_job($self->{info}, $arg->{jobid});
3288 if ($arg->{jobid}) {
3289 $self->get_job_log();
3292 $self->error("Can't get client or jobid");
3296 sub display_running_jobs
3298 my ($self, $display_action) = @_;
3299 # get security filter
3300 my $filter = $self->get_client_filter();
3303 SELECT Job.JobId AS jobid,
3304 Job.Name AS jobname,
3306 Job.StartTime AS starttime,
3307 Job.JobFiles AS jobfiles,
3308 Job.JobBytes AS jobbytes,
3309 Job.JobStatus AS jobstatus,
3310 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3311 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3313 Client.Name AS clientname
3314 FROM Job INNER JOIN Client USING (ClientId) $filter
3316 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3318 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3320 $self->display({ ID => $cur_id++,
3321 display_action => $display_action,
3322 Jobs => [ values %$all ]},
3323 "running_job.tpl") ;
3326 # return the autochanger list to update
3330 $self->can_do('media_mgnt');
3333 my $arg = $self->get_form('jmedias');
3335 unless ($arg->{jmedias}) {
3336 return $self->error("Can't get media selection");
3340 SELECT Media.VolumeName AS volumename,
3341 Storage.Name AS storage,
3342 Location.Location AS location,
3344 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3345 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3346 WHERE Media.VolumeName IN ($arg->{jmedias})
3347 AND Media.InChanger = 1
3350 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3352 foreach my $vol (values %$all) {
3353 my $a = $self->ach_get($vol->{location});
3355 $ret{$vol->{location}} = 1;
3357 unless ($a->{have_status}) {
3359 $a->{have_status} = 1;
3362 print "eject $vol->{volumename} from $vol->{storage} : ";
3363 if ($a->send_to_io($vol->{slot})) {
3364 print "<img src='/bweb/T.png' alt='ok'><br/>";
3366 print "<img src='/bweb/E.png' alt='err'><br/>";
3376 my ($to, $subject, $content) = (CGI::param('email'),
3377 CGI::param('subject'),
3378 CGI::param('content'));
3379 $to =~ s/[^\w\d\.\@<>,]//;
3380 $subject =~ s/[^\w\d\.\[\]]/ /;
3382 open(MAIL, "|mail -s '$subject' '$to'") ;
3383 print MAIL $content;
3393 my $arg = $self->get_form('jobid', 'client');
3395 print CGI::header('text/brestore');
3396 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3397 print "client=$arg->{client}\n" if ($arg->{client});
3398 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3402 # TODO : move this to Bweb::Autochanger ?
3403 # TODO : make this internal to not eject tape ?
3409 my ($self, $name) = @_;
3412 return $self->error("Can't get your autochanger name ach");
3415 unless ($self->{info}->{ach_list}) {
3416 return $self->error("Could not find any autochanger");
3419 my $a = $self->{info}->{ach_list}->{$name};
3422 $self->error("Can't get your autochanger $name from your ach_list");
3427 $a->{debug} = $self->{debug};
3434 my ($self, $ach) = @_;
3435 $self->can_do('configure');
3437 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3439 $self->{info}->save();
3447 $self->can_do('configure');
3449 my $arg = $self->get_form('ach');
3451 or !$self->{info}->{ach_list}
3452 or !$self->{info}->{ach_list}->{$arg->{ach}})
3454 return $self->error("Can't get autochanger name");
3457 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3461 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3463 my $b = $self->get_bconsole();
3465 my @storages = $b->list_storage() ;
3467 $ach->{devices} = [ map { { name => $_ } } @storages ];
3469 $self->display($ach, "ach_add.tpl");
3470 delete $ach->{drives};
3471 delete $ach->{devices};
3478 $self->can_do('configure');
3480 my $arg = $self->get_form('ach');
3483 or !$self->{info}->{ach_list}
3484 or !$self->{info}->{ach_list}->{$arg->{ach}})
3486 return $self->error("Can't get autochanger name");
3489 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3491 $self->{info}->save();
3492 $self->{info}->view();
3498 $self->can_do('configure');
3500 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3502 my $b = $self->get_bconsole();
3503 my @storages = $b->list_storage() ;
3505 unless ($arg->{ach}) {
3506 $arg->{devices} = [ map { { name => $_ } } @storages ];
3507 return $self->display($arg, "ach_add.tpl");
3511 foreach my $drive (CGI::param('drives'))
3513 unless (grep(/^$drive$/,@storages)) {
3514 return $self->error("Can't find $drive in storage list");
3517 my $index = CGI::param("index_$drive");
3518 unless (defined $index and $index =~ /^(\d+)$/) {
3519 return $self->error("Can't get $drive index");
3522 $drives[$index] = $drive;
3526 return $self->error("Can't get drives from Autochanger");
3529 my $a = new Bweb::Autochanger(name => $arg->{ach},
3530 precmd => $arg->{precmd},
3531 drive_name => \@drives,
3532 device => $arg->{device},
3533 mtxcmd => $arg->{mtxcmd});
3535 $self->ach_register($a) ;
3537 $self->{info}->view();
3543 $self->can_do('delete_job');
3545 my $arg = $self->get_form('jobid');
3547 if ($arg->{jobid}) {
3548 my $b = $self->get_bconsole();
3549 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3553 title => "Delete a job ",
3554 name => "delete jobid=$arg->{jobid}",
3562 $self->can_do('media_mgnt');
3564 my $arg = $self->get_form(qw/media volstatus inchanger pool
3565 slot volretention voluseduration
3566 maxvoljobs maxvolfiles maxvolbytes
3567 qcomment poolrecycle enabled
3570 unless ($arg->{media}) {
3571 return $self->error("Can't find media selection");
3574 my $update = "update volume=$arg->{media} ";
3576 if ($arg->{volstatus}) {
3577 $update .= " volstatus=$arg->{volstatus} ";
3580 if ($arg->{inchanger}) {
3581 $update .= " inchanger=yes " ;
3583 $update .= " slot=$arg->{slot} ";
3586 $update .= " slot=0 inchanger=no ";
3589 if ($arg->{enabled}) {
3590 $update .= " enabled=$arg->{enabled} ";
3594 $update .= " pool=$arg->{pool} " ;
3597 if (defined $arg->{volretention}) {
3598 $update .= " volretention=\"$arg->{volretention}\" " ;
3601 if (defined $arg->{voluseduration}) {
3602 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3605 if (defined $arg->{maxvoljobs}) {
3606 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3609 if (defined $arg->{maxvolfiles}) {
3610 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3613 if (defined $arg->{maxvolbytes}) {
3614 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3617 if (defined $arg->{poolrecycle}) {
3618 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3621 my $b = $self->get_bconsole();
3624 content => $b->send_cmd($update),
3625 title => "Update a volume ",
3631 my $media = $self->dbh_quote($arg->{media});
3633 my $loc = CGI::param('location') || '';
3635 $loc = $self->dbh_quote($loc); # is checked by db
3636 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3638 if (!$arg->{qcomment}) {
3639 $arg->{qcomment} = "''";
3641 push @q, "Comment=$arg->{qcomment}";
3646 SET " . join (',', @q) . "
3647 WHERE Media.VolumeName = $media
3649 $self->dbh_do($query);
3651 $self->update_media();
3657 $self->can_do('autochanger_mgnt');
3659 my $ach = CGI::param('ach') ;
3660 $ach = $self->ach_get($ach);
3662 return $self->error("Bad autochanger name");
3666 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3667 $b->update_slots($ach->{name});
3675 my $arg = $self->get_form('jobid', 'limit', 'offset');
3676 unless ($arg->{jobid}) {
3677 return $self->error("Can't get jobid");
3680 if ($arg->{limit} == 100) {
3681 $arg->{limit} = 1000;
3684 my $t = CGI::param('time') || $self->{info}->{display_log_time} || '';
3686 # display only Error and Warning messages
3688 if (CGI::param('error')) {
3689 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3692 # get security filter
3693 $filter .= $self->get_client_filter();
3696 SELECT Job.Name as name, Client.Name as clientname
3697 FROM Job INNER JOIN Client USING (ClientId) $filter
3698 WHERE JobId = $arg->{jobid}
3701 my $row = $self->dbh_selectrow_hashref($query);
3704 return $self->error("Can't find $arg->{jobid} in catalog");
3708 SELECT Time AS time, LogText AS log
3710 WHERE ( Log.JobId = $arg->{jobid}
3711 OR (Log.JobId = 0 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3712 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3717 OFFSET $arg->{offset}
3720 my $log = $self->dbh_selectall_arrayref($query);
3722 return $self->error("Can't get log for jobid $arg->{jobid}");
3728 $logtxt = join("", map { ($_->[0] . ' ' . $_->[1]) } @$log ) ;
3730 $logtxt = join("", map { $_->[1] } @$log ) ;
3733 $self->display({ lines=> $logtxt,
3734 jobid => $arg->{jobid},
3735 name => $row->{name},
3736 client => $row->{clientname},
3737 offset => $arg->{offset},
3738 limit => $arg->{limit},
3739 }, 'display_log.tpl');
3745 $self->can_do('autochanger_mgnt');
3747 my $arg = $self->get_form('ach', 'slots', 'drive');
3749 unless ($arg->{ach}) {
3750 return $self->error("Can't find autochanger name");
3753 my $a = $self->ach_get($arg->{ach});
3755 return $self->error("Can't find autochanger name in configuration");
3758 my $storage = $a->get_drive_name($arg->{drive});
3760 return $self->error("Can't get your drive name");
3766 if ($arg->{slots}) {
3767 $slots = join(",", @{ $arg->{slots} });
3768 $slots_sql = " AND Slot IN ($slots) ";
3769 $t += 60*scalar( @{ $arg->{slots} }) ;
3772 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3773 print "<h1>This command can take long time, be patient...</h1>";
3775 $b->label_barcodes(storage => $storage,
3776 drive => $arg->{drive},
3784 SET LocationId = (SELECT LocationId
3786 WHERE Location = '$arg->{ach}')
3788 WHERE (LocationId = 0 OR LocationId IS NULL)
3797 $self->can_do('purge');
3799 my @volume = CGI::param('media');
3802 return $self->error("Can't get media selection");
3805 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3807 foreach my $v (@volume) {
3809 content => $b->purge_volume($v),
3810 title => "Purge media",
3811 name => "purge volume=$v",
3820 $self->can_do('prune');
3822 my @volume = CGI::param('media');
3824 return $self->error("Can't get media selection");
3827 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3829 foreach my $v (@volume) {
3831 content => $b->prune_volume($v),
3832 title => "Prune volume",
3833 name => "prune volume=$v",
3842 $self->can_do('cancel_job');
3844 my $arg = $self->get_form('jobid');
3845 unless ($arg->{jobid}) {
3846 return $self->error("Can't get jobid");
3849 my $b = $self->get_bconsole();
3851 content => $b->cancel($arg->{jobid}),
3852 title => "Cancel job",
3853 name => "cancel jobid=$arg->{jobid}",
3859 # Warning, we display current fileset
3862 my $arg = $self->get_form('fileset');
3864 if ($arg->{fileset}) {
3865 my $b = $self->get_bconsole();
3866 my $ret = $b->get_fileset($arg->{fileset});
3867 $self->display({ fileset => $arg->{fileset},
3869 }, "fileset_view.tpl");
3871 $self->error("Can't get fileset name");
3875 sub director_show_sched
3879 my $arg = $self->get_form('days');
3881 my $b = $self->get_bconsole();
3882 my $ret = $b->director_get_sched( $arg->{days} );
3887 }, "scheduled_job.tpl");
3890 sub enable_disable_job
3892 my ($self, $what) = @_ ;
3893 $self->can_do('run_job');
3895 my $name = CGI::param('job') || '';
3896 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3897 return $self->error("Can't find job name");
3900 my $b = $self->get_bconsole();
3910 content => $b->send_cmd("$cmd job=\"$name\""),
3911 title => "$cmd $name",
3912 name => "$cmd job=\"$name\"",
3919 return new Bconsole(pref => $self->{info});
3925 $self->can_do('run_job');
3927 my $b = $self->get_bconsole();
3929 my $joblist = [ map { { name => $_ } } $b->list_job() ];
3931 $self->display({ Jobs => $joblist }, "run_job.tpl");
3936 my ($self, $ouput) = @_;
3939 foreach my $l (split(/\r\n/, $ouput)) {
3940 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
3946 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
3952 foreach my $k (keys %arg) {
3953 $lowcase{lc($k)} = $arg{$k} ;
3962 $self->can_do('run_job');
3964 my $b = $self->get_bconsole();
3966 my $job = CGI::param('job') || '';
3968 # we take informations from director, and we overwrite with user wish
3969 my $info = $b->send_cmd("show job=\"$job\"");
3970 my $attr = $self->run_parse_job($info);
3972 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
3973 my %job_opt = (%$attr, %$arg);
3975 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
3977 my $pools = [ map { { name => $_ } } $b->list_pool() ];
3978 my $clients = [ map { { name => $_ } }$b->list_client()];
3979 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
3980 my $storages= [ map { { name => $_ } }$b->list_storage()];
3985 clients => $clients,
3986 filesets => $filesets,
3987 storages => $storages,
3989 }, "run_job_mod.tpl");
3995 $self->can_do('run_job');
3997 my $b = $self->get_bconsole();
3999 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4009 $self->can_do('run_job');
4011 my $b = $self->get_bconsole();
4013 # TODO: check input (don't use pool, level)
4015 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
4016 my $job = CGI::param('job') || '';
4017 my $storage = CGI::param('storage') || '';
4019 my $jobid = $b->run(job => $job,
4020 client => $arg->{client},
4021 priority => $arg->{priority},
4022 level => $arg->{level},
4023 storage => $storage,
4024 pool => $arg->{pool},
4025 fileset => $arg->{fileset},
4026 when => $arg->{when},
4029 print $jobid, $b->{error};
4031 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";