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)?$/,
221 enable_security_acl => qr/^(on)?$/,
226 load - load config_file
230 this function load the specified config_file.
238 unless (open(FP, $self->{config_file}))
240 return $self->error("can't load config_file $self->{config_file} : $!");
242 my $f=''; my $tmpbuffer;
243 while(read FP,$tmpbuffer,4096)
251 no strict; # I have no idea of the contents of the file
258 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...") ;
261 foreach my $k (keys %$VAR1) {
262 $self->{$k} = $VAR1->{$k};
270 load_old - load old configuration format
278 unless (open(FP, $self->{config_file}))
280 return $self->error("$self->{config_file} : $!");
283 while (my $line = <FP>)
286 my ($k, $v) = split(/\s*=\s*/, $line, 2);
298 save - save the current configuration to config_file
306 if ($self->{ach_list}) {
307 # shortcut for display_begin
308 $self->{achs} = [ map {{ name => $_ }}
309 keys %{$self->{ach_list}}
313 unless (open(FP, ">$self->{config_file}"))
315 return $self->error("$self->{config_file} : $!\n" .
316 "You must add this to your config file\n"
317 . Data::Dumper::Dumper($self));
320 print FP Data::Dumper::Dumper($self);
328 edit, view, modify - html form ouput
336 $self->display($self, "config_edit.tpl");
342 $self->display($self, "config_view.tpl");
350 # we need to reset checkbox first
352 $self->{display_log_time} = 0;
353 $self->{enable_security} = 0;
354 $self->{enable_security_acl} = 0;
356 foreach my $k (CGI::param())
358 next unless (exists $k_re{$k}) ;
359 my $val = CGI::param($k);
360 if ($val =~ $k_re{$k}) {
363 $self->{error} .= "bad parameter : $k = [$val]";
369 if ($self->{error}) { # an error as occured
370 $self->display($self, 'error.tpl');
378 ################################################################
380 package Bweb::Client;
382 use base q/Bweb::Gui/;
386 Bweb::Client - Bacula FD
390 this package is use to do all Client operations like, parse status etc...
394 $client = new Bweb::Client(name => 'zog-fd');
395 $client->status(); # do a 'status client=zog-fd'
401 display_running_job - Html display of a running job
405 this function is used to display information about a current job
409 sub display_running_job
411 my ($self, $conf, $jobid) = @_ ;
413 my $status = $self->status($conf);
416 if ($status->{$jobid}) {
417 $self->display($status->{$jobid}, "client_job_status.tpl");
420 for my $id (keys %$status) {
421 $self->display($status->{$id}, "client_job_status.tpl");
428 $client = new Bweb::Client(name => 'plume-fd');
430 $client->status($bweb);
434 dirty hack to parse "status client=xxx-fd"
438 JobId 105 Job Full_plume.2006-06-06_17.22.23 is running.
439 Backup Job started: 06-jun-06 17:22
440 Files=8,971 Bytes=194,484,132 Bytes/sec=7,480,158
441 Files Examined=10,697
442 Processing file: /home/eric/.openoffice.org2/user/config/standard.sod
448 JobName => Full_plume.2006-06-06_17.22.23,
451 Bytes => 194,484,132,
461 my ($self, $conf) = @_ ;
463 if (defined $self->{cur_jobs}) {
464 return $self->{cur_jobs} ;
468 my $b = new Bconsole(pref => $conf);
469 my $ret = $b->send_cmd("st client=$self->{name}");
473 for my $r (split(/\n/, $ret)) {
475 $r =~ s/(^\s+|\s+$)//g;
476 if ($r =~ /JobId (\d+) Job (\S+)/) {
478 $arg->{$jobid} = { @param, JobId => $jobid } ;
482 @param = ( JobName => $2 );
484 } elsif ($r =~ /=.+=/) {
485 push @param, split(/\s+|\s*=\s*/, $r) ;
487 } elsif ($r =~ /=/) { # one per line
488 push @param, split(/\s*=\s*/, $r) ;
490 } elsif ($r =~ /:/) { # one per line
491 push @param, split(/\s*:\s*/, $r, 2) ;
495 if ($jobid and @param) {
496 $arg->{$jobid} = { @param,
498 Client => $self->{name},
502 $self->{cur_jobs} = $arg ;
508 ################################################################
510 package Bweb::Autochanger;
512 use base q/Bweb::Gui/;
516 Bweb::Autochanger - Object to manage Autochanger
520 this package will parse the mtx output and manage drives.
524 $auto = new Bweb::Autochanger(precmd => 'sudo');
526 $auto = new Bweb::Autochanger(precmd => 'ssh root@robot');
530 $auto->slot_is_full(10);
531 $auto->transfer(10, 11);
537 my ($class, %arg) = @_;
540 name => '', # autochanger name
541 label => {}, # where are volume { label1 => 40, label2 => drive0 }
542 drive => [], # drive use [ 'media1', 'empty', ..]
543 slot => [], # slot use [ undef, 'empty', 'empty', ..] no slot 0
544 io => [], # io slot number list [ 41, 42, 43...]
545 info => {slot => 0, # informations (slot, drive, io)
549 mtxcmd => '/usr/sbin/mtx',
551 device => '/dev/changer',
552 precmd => '', # ssh command
553 bweb => undef, # link to bacula web object (use for display)
556 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
563 status - parse the output of mtx status
567 this function will launch mtx status and parse the output. it will
568 give a perlish view of the autochanger content.
570 it uses ssh if the autochanger is on a other host.
577 my @out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} status` ;
579 # TODO : reset all infos
580 $self->{info}->{drive} = 0;
581 $self->{info}->{slot} = 0;
582 $self->{info}->{io} = 0;
584 #my @out = `cat /home/eric/travail/brestore/plume/mtx` ;
587 # Storage Changer /dev/changer:2 Drives, 45 Slots ( 5 Import/Export )
588 #Data Transfer Element 0:Full (Storage Element 1 Loaded):VolumeTag = 000000
589 #Data Transfer Element 1:Empty
590 # Storage Element 1:Empty
591 # Storage Element 2:Full :VolumeTag=000002
592 # Storage Element 3:Empty
593 # Storage Element 4:Full :VolumeTag=000004
594 # Storage Element 5:Full :VolumeTag=000001
595 # Storage Element 6:Full :VolumeTag=000003
596 # Storage Element 7:Empty
597 # Storage Element 41 IMPORT/EXPORT:Empty
598 # Storage Element 41 IMPORT/EXPORT:Full :VolumeTag=000002
603 # Storage Element 7:Empty
604 # Storage Element 2:Full :VolumeTag=000002
605 if ($l =~ /Storage Element (\d+):(Empty|Full)(\s+:VolumeTag=([\w\d]+))?/){
608 $self->set_empty_slot($1);
610 $self->set_slot($1, $4);
613 } elsif ($l =~ /Data Transfer.+(\d+):(Full|Empty)(\s+.Storage Element (\d+) Loaded.(:VolumeTag = ([\w\d]+))?)?/) {
616 $self->set_empty_drive($1);
618 $self->set_drive($1, $4, $6);
621 } elsif ($l =~ /Storage Element (\d+).+IMPORT\/EXPORT:(Empty|Full)( :VolumeTag=([\d\w]+))?/)
624 $self->set_empty_io($1);
626 $self->set_io($1, $4);
629 # Storage Changer /dev/changer:2 Drives, 30 Slots ( 1 Import/Export )
631 } elsif ($l =~ /Storage Changer .+:(\d+) Drives, (\d+) Slots/) {
632 $self->{info}->{drive} = $1;
633 $self->{info}->{slot} = $2;
634 if ($l =~ /(\d+)\s+Import/) {
635 $self->{info}->{io} = $1 ;
637 $self->{info}->{io} = 0;
642 $self->debug($self) ;
647 my ($self, $slot) = @_;
650 if ($self->{slot}->[$slot] eq 'loaded') {
654 my $label = $self->{slot}->[$slot] ;
656 return $self->is_media_loaded($label);
661 my ($self, $drive, $slot) = @_;
663 return 0 if (not defined $drive or $self->{drive}->[$drive] eq 'empty') ;
664 return 0 if ($self->slot_is_full($slot)) ;
666 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} unload $slot $drive 2>&1`;
669 my $content = $self->get_slot($slot);
670 print "content = $content<br/> $drive => $slot<br/>";
671 $self->set_empty_drive($drive);
672 $self->set_slot($slot, $content);
675 $self->{error} = $out;
680 # TODO: load/unload have to use mtx script from bacula
683 my ($self, $drive, $slot) = @_;
685 return 0 if (not defined $drive or $self->{drive}->[$drive] ne 'empty') ;
686 return 0 unless ($self->slot_is_full($slot)) ;
688 print "Loading drive $drive with slot $slot<br/>\n";
689 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} load $slot $drive 2>&1`;
692 my $content = $self->get_slot($slot);
693 print "content = $content<br/> $slot => $drive<br/>";
694 $self->set_drive($drive, $slot, $content);
697 $self->{error} = $out;
705 my ($self, $media) = @_;
707 unless ($self->{label}->{$media}) {
711 if ($self->{label}->{$media} =~ /drive\d+/) {
721 return (defined $self->{info}->{io} and $self->{info}->{io} > 0);
726 my ($self, $slot, $tag) = @_;
727 $self->{slot}->[$slot] = $tag || 'full';
728 push @{ $self->{io} }, $slot;
731 $self->{label}->{$tag} = $slot;
737 my ($self, $slot) = @_;
739 push @{ $self->{io} }, $slot;
741 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
742 $self->{slot}->[$slot] = 'empty';
748 my ($self, $slot) = @_;
749 return $self->{slot}->[$slot];
754 my ($self, $slot, $tag) = @_;
755 $self->{slot}->[$slot] = $tag || 'full';
758 $self->{label}->{$tag} = $slot;
764 my ($self, $slot) = @_;
766 unless ($self->{slot}->[$slot]) { # can be loaded (parse before)
767 $self->{slot}->[$slot] = 'empty';
773 my ($self, $drive) = @_;
774 $self->{drive}->[$drive] = 'empty';
779 my ($self, $drive, $slot, $tag) = @_;
780 $self->{drive}->[$drive] = $tag || $slot;
782 $self->{slot}->[$slot] = $tag || 'loaded';
785 $self->{label}->{$tag} = "drive$drive";
791 my ($self, $slot) = @_;
793 # slot don't exists => full
794 if (not defined $self->{slot}->[$slot]) {
798 if ($self->{slot}->[$slot] eq 'empty') {
801 return 1; # vol, full, loaded
804 sub slot_get_first_free
807 for (my $slot=1; $slot < $self->{info}->{slot}; $slot++) {
808 return $slot unless ($self->slot_is_full($slot));
812 sub io_get_first_free
816 foreach my $slot (@{ $self->{io} }) {
817 return $slot unless ($self->slot_is_full($slot));
824 my ($self, $media) = @_;
826 return $self->{label}->{$media} ;
831 my ($self, $media) = @_;
833 return defined $self->{label}->{$media} ;
838 my ($self, $slot) = @_;
840 unless ($self->slot_is_full($slot)) {
841 print "Autochanger $self->{name} slot $slot is empty\n";
846 if ($self->is_slot_loaded($slot)) {
849 print "Autochanger $self->{name} $slot is currently in use\n";
853 # autochanger must have I/O
854 unless ($self->have_io()) {
855 print "Autochanger $self->{name} don't have I/O, you can take media yourself\n";
859 my $dst = $self->io_get_first_free();
862 print "Autochanger $self->{name} you must empty I/O first\n";
865 $self->transfer($slot, $dst);
870 my ($self, $src, $dst) = @_ ;
871 if ($self->{debug}) {
872 print "<pre>$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst</pre>\n";
874 my $out = `$self->{precmd} $self->{mtxcmd} -f $self->{device} transfer $src $dst 2>&1`;
877 my $content = $self->get_slot($src);
878 $self->{slot}->[$src] = 'empty';
879 $self->set_slot($dst, $content);
882 $self->{error} = $out;
889 my ($self, $index) = @_;
890 return $self->{drive_name}->[$index];
893 # TODO : do a tapeinfo request to get informations
903 for my $slot (@{$self->{io}})
905 if ($self->is_slot_loaded($slot)) {
906 print "$slot is currently loaded\n";
910 if ($self->slot_is_full($slot))
912 my $free = $self->slot_get_first_free() ;
913 print "move $slot to $free :\n";
916 if ($self->transfer($slot, $free)) {
917 print "<img src='/bweb/T.png' alt='ok'><br/>\n";
919 print "<img src='/bweb/E.png' alt='ok' title='$self->{error}'><br/>\n";
923 $self->{error} = "<img src='/bweb/E.png' alt='ok' title='E : Can t find free slot'><br/>\n";
929 # TODO : this is with mtx status output,
930 # we can do an other function from bacula view (with StorageId)
934 my $bweb = $self->{bweb};
936 # $self->{label} => ('vol1', 'vol2', 'vol3', ..);
937 my $media_list = $bweb->dbh_join( keys %{ $self->{label} });
940 SELECT Media.VolumeName AS volumename,
941 Media.VolStatus AS volstatus,
942 Media.LastWritten AS lastwritten,
943 Media.VolBytes AS volbytes,
944 Media.MediaType AS mediatype,
946 Media.InChanger AS inchanger,
948 $bweb->{sql}->{FROM_UNIXTIME}(
949 $bweb->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
950 + $bweb->{sql}->{TO_SEC}(Media.VolRetention)
953 INNER JOIN Pool USING (PoolId)
955 WHERE Media.VolumeName IN ($media_list)
958 my $all = $bweb->dbh_selectall_hashref($query, 'volumename') ;
960 # TODO : verify slot and bacula slot
964 for (my $slot=1; $slot <= $self->{info}->{slot} ; $slot++) {
966 if ($self->slot_is_full($slot)) {
968 my $vol = $self->{slot}->[$slot];
969 if (defined $all->{$vol}) { # TODO : autochanger without barcodes
971 my $bslot = $all->{$vol}->{slot} ;
972 my $inchanger = $all->{$vol}->{inchanger};
974 # if bacula slot or inchanger flag is bad, we display a message
975 if ($bslot != $slot or !$inchanger) {
976 push @to_update, $slot;
979 $all->{$vol}->{realslot} = $slot;
981 push @{ $param }, $all->{$vol};
983 } else { # empty or no label
984 push @{ $param }, {realslot => $slot,
985 volstatus => 'Unknown',
986 volumename => $self->{slot}->[$slot]} ;
989 push @{ $param }, {realslot => $slot, volumename => 'empty'} ;
993 my $i=0; my $drives = [] ;
994 foreach my $d (@{ $self->{drive} }) {
995 $drives->[$i] = { index => $i,
996 load => $self->{drive}->[$i],
997 name => $self->{drive_name}->[$i],
1002 $bweb->display({ Name => $self->{name},
1003 nb_drive => $self->{info}->{drive},
1004 nb_io => $self->{info}->{io},
1007 Update => scalar(@to_update) },
1015 ################################################################
1019 use base q/Bweb::Gui/;
1023 Bweb - main Bweb package
1027 this package is use to compute and display informations
1032 use POSIX qw/strftime/;
1034 our $config_file='/etc/bacula/bweb.conf';
1040 %sql_func - hash to make query mysql/postgresql compliant
1046 UNIX_TIMESTAMP => '',
1047 FROM_UNIXTIME => '',
1048 TO_SEC => " interval '1 second' * ",
1049 SEC_TO_INT => "SEC_TO_INT",
1052 STARTTIME_DAY => " date_trunc('day', Job.StartTime) ",
1053 STARTTIME_HOUR => " date_trunc('hour', Job.StartTime) ",
1054 STARTTIME_MONTH => " date_trunc('month', Job.StartTime) ",
1055 STARTTIME_PHOUR=> " date_part('hour', Job.StartTime) ",
1056 STARTTIME_PDAY => " date_part('day', Job.StartTime) ",
1057 STARTTIME_PMONTH => " date_part('month', Job.StartTime) ",
1058 STARTTIME_PWEEK => " date_part('week', Job.StartTime) ",
1059 DB_SIZE => " SELECT pg_database_size(current_database()) ",
1060 CAT_POOL_TYPE => " MediaType || '_' || Pool.Name ",
1064 UNIX_TIMESTAMP => 'UNIX_TIMESTAMP',
1065 FROM_UNIXTIME => 'FROM_UNIXTIME',
1068 SEC_TO_TIME => 'SEC_TO_TIME',
1069 MATCH => " REGEXP ",
1070 STARTTIME_DAY => " DATE_FORMAT(StartTime, '%Y-%m-%d') ",
1071 STARTTIME_HOUR => " DATE_FORMAT(StartTime, '%Y-%m-%d %H') ",
1072 STARTTIME_MONTH => " DATE_FORMAT(StartTime, '%Y-%m') ",
1073 STARTTIME_PHOUR=> " DATE_FORMAT(StartTime, '%H') ",
1074 STARTTIME_PDAY => " DATE_FORMAT(StartTime, '%d') ",
1075 STARTTIME_PMONTH => " DATE_FORMAT(StartTime, '%m') ",
1076 STARTTIME_PWEEK => " DATE_FORMAT(StartTime, '%v') ",
1077 # with mysql < 5, you have to play with the ugly SHOW command
1078 DB_SIZE => " SELECT 0 ",
1079 # works only with mysql 5
1080 # DB_SIZE => " SELECT sum(DATA_LENGTH) FROM INFORMATION_SCHEMA.TABLES ",
1081 CAT_POOL_TYPE => " CONCAT(MediaType,'_',Pool.Name) ",
1082 CONCAT_SEP => " SEPARATOR '' ",
1090 $self->{dbh}->disconnect();
1095 sub dbh_selectall_arrayref
1097 my ($self, $query) = @_;
1098 $self->connect_db();
1099 $self->debug($query);
1100 return $self->{dbh}->selectall_arrayref($query);
1105 my ($self, @what) = @_;
1106 return join(',', $self->dbh_quote(@what)) ;
1111 my ($self, @what) = @_;
1113 $self->connect_db();
1115 return map { $self->{dbh}->quote($_) } @what;
1117 return $self->{dbh}->quote($what[0]) ;
1123 my ($self, $query) = @_ ;
1124 $self->connect_db();
1125 $self->debug($query);
1126 return $self->{dbh}->do($query);
1129 sub dbh_selectall_hashref
1131 my ($self, $query, $join) = @_;
1133 $self->connect_db();
1134 $self->debug($query);
1135 return $self->{dbh}->selectall_hashref($query, $join) ;
1138 sub dbh_selectrow_hashref
1140 my ($self, $query) = @_;
1142 $self->connect_db();
1143 $self->debug($query);
1144 return $self->{dbh}->selectrow_hashref($query) ;
1149 my ($self, @what) = @_;
1150 if ($self->{conf}->{connection_string} =~ /dbi:mysql/i) {
1151 return 'CONCAT(' . join(',', @what) . ')' ;
1153 return join(' || ', @what);
1159 my ($self, $query) = @_;
1160 $self->debug($query, up => 1);
1161 return $self->{dbh}->prepare($query);
1167 my @unit = qw(B KB MB GB TB);
1168 my $val = shift || 0;
1170 my $format = '%i %s';
1171 while ($val / 1024 > 1) {
1175 $format = ($i>0)?'%0.1f %s':'%i %s';
1176 return sprintf($format, $val, $unit[$i]);
1179 # display Day, Hour, Year
1185 $val /= 60; # sec -> min
1187 if ($val / 60 <= 1) {
1191 $val /= 60; # min -> hour
1192 if ($val / 24 <= 1) {
1193 return "$val hours";
1196 $val /= 24; # hour -> day
1197 if ($val / 365 < 2) {
1201 $val /= 365 ; # day -> year
1203 return "$val years";
1209 my $val = shift || 0;
1211 if ($val == 1 or $val eq "yes") {
1213 } elsif ($val == 2 or $val eq "archived") {
1220 # get Day, Hour, Year
1226 unless ($val =~ /^\s*(\d+)\s*(\w)\w*\s*$/) {
1230 my %times = ( m => 60,
1236 my $mult = $times{$2} || 0;
1246 unless ($self->{dbh}) {
1247 $self->{dbh} = DBI->connect($self->{info}->{dbi},
1248 $self->{info}->{user},
1249 $self->{info}->{password});
1251 $self->error("Can't connect to your database:\n$DBI::errstr\n")
1252 unless ($self->{dbh});
1254 $self->{dbh}->{FetchHashKeyName} = 'NAME_lc';
1256 if ($self->{info}->{dbi} =~ /^dbi:Pg/i) {
1257 $self->{dbh}->do("SET datestyle TO 'ISO, YMD'");
1264 my ($class, %arg) = @_;
1266 dbh => undef, # connect_db();
1268 dbi => '', # DBI:Pg:database=bacula;host=127.0.0.1
1274 map { $self->{lc($_)} = $arg{$_} } keys %arg ;
1276 if ($self->{info}->{dbi} =~ /DBI:(\w+):/i) {
1277 $self->{sql} = $sql_func{$1};
1280 $self->{loginname} = CGI::remote_user();
1281 $self->{debug} = $self->{info}->{debug};
1282 $Bweb::Gui::template_dir = $self->{info}->{template_dir};
1290 $self->display($self->{info}, "begin.tpl");
1296 $self->display($self->{info}, "end.tpl");
1302 my $where=''; # by default
1304 my $arg = $self->get_form("client", "qre_client",
1305 "jclient_groups", "qnotingroup");
1307 if ($arg->{qre_client}) {
1308 $where = "WHERE Name $self->{sql}->{MATCH} $arg->{qre_client} ";
1309 } elsif ($arg->{client}) {
1310 $where = "WHERE Name = '$arg->{client}' ";
1311 } elsif ($arg->{jclient_groups}) {
1312 # $filter could already contains client_group_member
1314 JOIN client_group_member USING (ClientId)
1315 JOIN client_group USING (client_group_id)
1316 WHERE client_group_name IN ($arg->{jclient_groups}) ";
1317 } elsif ($arg->{qnotingroup}) {
1320 (SELECT 1 FROM client_group_member
1321 WHERE Client.ClientId = client_group_member.ClientId
1327 SELECT Name AS name,
1329 AutoPrune AS autoprune,
1330 FileRetention AS fileretention,
1331 JobRetention AS jobretention
1332 FROM Client " . $self->get_client_filter() .
1335 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1337 my $dsp = { ID => $cur_id++,
1338 clients => [ values %$all] };
1340 $self->display($dsp, "client_list.tpl") ;
1345 my ($self, %arg) = @_;
1352 "AND $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
1354 ( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
1356 $self->{sql}->{TO_SEC}($arg{age})
1359 $label = "last " . human_sec($arg{age});
1362 if ($arg{groupby}) {
1363 $limit .= " GROUP BY $arg{groupby} ";
1367 $limit .= " ORDER BY $arg{order} ";
1371 $limit .= " LIMIT $arg{limit} ";
1372 $label .= " limited to $arg{limit}";
1376 $limit .= " OFFSET $arg{offset} ";
1377 $label .= " with $arg{offset} offset ";
1381 $label = 'no filter';
1384 return ($limit, $label);
1389 $bweb->get_form(...) - Get useful stuff
1393 This function get and check parameters against regexp.
1395 If word begin with 'q', the return will be quoted or join quoted
1396 if it's end with 's'.
1401 $bweb->get_form('jobid', 'qclient', 'qpools') ;
1404 qclient => 'plume-fd',
1405 qpools => "'plume-fd', 'test-fd', '...'",
1412 my ($self, @what) = @_;
1413 my %what = map { $_ => 1 } @what;
1435 my %opt_ss =( # string with space
1439 my %opt_s = ( # default to ''
1460 my %opt_p = ( # option with path
1467 my %opt_r = (regexwhere => 1);
1469 my %opt_d = ( # option with date
1474 foreach my $i (@what) {
1475 if (exists $opt_i{$i}) {# integer param
1476 my $value = CGI::param($i) || $opt_i{$i} ;
1477 if ($value =~ /^(\d+)$/) {
1480 } elsif ($opt_s{$i}) { # simple string param
1481 my $value = CGI::param($i) || '';
1482 if ($value =~ /^([\w\d\.-]+)$/) {
1485 } elsif ($opt_ss{$i}) { # simple string param (with space)
1486 my $value = CGI::param($i) || '';
1487 if ($value =~ /^([\w\d\.\-\s]+)$/) {
1490 } elsif ($i =~ /^j(\w+)s$/) { # quote join args "'arg1', 'arg2'"
1491 my @value = grep { ! /^\s*$/ } CGI::param($1) ;
1493 $ret{$i} = $self->dbh_join(@value) ;
1496 } elsif ($i =~ /^q(\w+[^s])$/) { # 'arg1'
1497 my $value = CGI::param($1) ;
1499 $ret{$i} = $self->dbh_quote($value);
1502 } elsif ($i =~ /^q(\w+)s$/) { #[ 'arg1', 'arg2']
1503 $ret{$i} = [ map { { name => $self->dbh_quote($_) } }
1504 grep { ! /^\s*$/ } CGI::param($1) ];
1505 } elsif (exists $opt_p{$i}) {
1506 my $value = CGI::param($i) || '';
1507 if ($value =~ /^([\w\d\.\/\s:\@\-]+)$/) {
1510 } elsif (exists $opt_r{$i}) {
1511 my $value = CGI::param($i) || '';
1512 if ($value =~ /^([^'"']+)$/) {
1515 } elsif (exists $opt_d{$i}) {
1516 my $value = CGI::param($i) || '';
1517 if ($value =~ /^\s*(\d+\s+\w+)$/) {
1524 foreach my $s (CGI::param('slot')) {
1525 if ($s =~ /^(\d+)$/) {
1526 push @{$ret{slots}}, $s;
1532 my $when = CGI::param('when') || '';
1533 if ($when =~ /^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})$/) {
1538 if ($what{db_clients}) {
1540 if ($what{filter}) {
1541 # get security filter only if asked
1542 $filter = $self->get_client_filter();
1546 SELECT Client.Name as clientname
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 if ($what{filter}) {
1558 # get security filter only if asked
1559 $filter = $self->get_client_group_filter();
1563 SELECT client_group_name AS name
1564 FROM client_group $filter
1567 my $grps = $self->dbh_selectall_hashref($query, 'name');
1568 $ret{db_client_groups} = [sort {$a->{name} cmp $b->{name} }
1572 if ($what{db_usernames}) {
1578 my $users = $self->dbh_selectall_hashref($query, 'username');
1579 $ret{db_usernames} = [sort {$a->{username} cmp $b->{username} }
1583 if ($what{db_roles}) {
1589 my $r = $self->dbh_selectall_hashref($query, 'rolename');
1590 $ret{db_roles} = [sort {$a->{rolename} cmp $b->{rolename} }
1594 if ($what{db_mediatypes}) {
1596 SELECT MediaType as mediatype
1600 my $media = $self->dbh_selectall_hashref($query, 'mediatype');
1601 $ret{db_mediatypes} = [sort {$a->{mediatype} cmp $b->{mediatype} }
1605 if ($what{db_locations}) {
1607 SELECT Location as location, Cost as cost
1610 my $loc = $self->dbh_selectall_hashref($query, 'location');
1611 $ret{db_locations} = [ sort { $a->{location}
1617 if ($what{db_pools}) {
1618 my $query = "SELECT Name as name FROM Pool";
1620 my $all = $self->dbh_selectall_hashref($query, 'name') ;
1621 $ret{db_pools} = [ sort { $a->{name} cmp $b->{name} } values %$all ];
1624 if ($what{db_filesets}) {
1626 SELECT FileSet.FileSet AS fileset
1630 my $filesets = $self->dbh_selectall_hashref($query, 'fileset');
1632 $ret{db_filesets} = [sort {lc($a->{fileset}) cmp lc($b->{fileset}) }
1633 values %$filesets] ;
1636 if ($what{db_jobnames}) {
1638 if ($what{filter}) {
1639 $filter = " JOIN Client USING (ClientId) " . $self->get_client_filter();
1642 SELECT DISTINCT Job.Name AS jobname
1646 my $jobnames = $self->dbh_selectall_hashref($query, 'jobname');
1648 $ret{db_jobnames} = [sort {lc($a->{jobname}) cmp lc($b->{jobname}) }
1649 values %$jobnames] ;
1652 if ($what{db_devices}) {
1654 SELECT Device.Name AS name
1658 my $devices = $self->dbh_selectall_hashref($query, 'name');
1660 $ret{db_devices} = [sort {lc($a->{name}) cmp lc($b->{name}) }
1671 my $fields = $self->get_form(qw/age level status clients filesets
1672 graph gtype type filter db_clients
1673 limit db_filesets width height
1674 qclients qfilesets qjobnames db_jobnames/);
1677 my $url = CGI::url(-full => 0,
1680 $url =~ s/^.+?\?//; # http://path/to/bweb.pl?arg => arg
1682 # this organisation is to keep user choice between 2 click
1683 # TODO : fileset and client selection doesn't work
1692 sub get_selected_media_location
1696 my $media = $self->get_form('jmedias');
1698 unless ($media->{jmedias}) {
1703 SELECT Media.VolumeName AS volumename, Location.Location AS location
1704 FROM Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1705 WHERE Media.VolumeName IN ($media->{jmedias})
1708 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1710 # { 'vol1' => { [volumename => 'vol1', location => 'ici'],
1719 my ($self, $in) = @_ ;
1721 my $media = $self->get_selected_media_location();
1727 my $elt = $self->get_form('db_locations');
1729 $self->display({ ID => $cur_id++,
1730 enabled => human_enabled($in),
1731 %$elt, # db_locations
1733 sort { $a->{volumename} cmp $b->{volumename} } values %$media
1743 my $elt = $self->get_form(qw/db_pools db_mediatypes db_locations/) ;
1745 $self->display($elt, "help_extern.tpl");
1748 sub help_extern_compute
1752 my $number = CGI::param('limit') || '' ;
1753 unless ($number =~ /^(\d+)$/) {
1754 return $self->error("Bad arg number : $number ");
1757 my ($sql, undef) = $self->get_param('pools',
1758 'locations', 'mediatypes');
1761 SELECT Media.VolumeName AS volumename,
1762 Media.VolStatus AS volstatus,
1763 Media.LastWritten AS lastwritten,
1764 Media.MediaType AS mediatype,
1765 Media.VolMounts AS volmounts,
1767 Media.Recycle AS recycle,
1768 $self->{sql}->{FROM_UNIXTIME}(
1769 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1770 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1773 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1774 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
1776 WHERE Media.InChanger = 1
1777 AND Media.VolStatus IN ('Disabled', 'Error', 'Full')
1779 ORDER BY expire DESC, recycle, Media.VolMounts DESC
1783 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1785 $self->display({ Media => [ values %$all ] },
1786 "help_extern_compute.tpl");
1793 my $param = $self->get_form(qw/db_locations db_pools db_mediatypes/) ;
1794 $self->display($param, "help_intern.tpl");
1797 sub help_intern_compute
1801 my $number = CGI::param('limit') || '' ;
1802 unless ($number =~ /^(\d+)$/) {
1803 return $self->error("Bad arg number : $number ");
1806 my ($sql, undef) = $self->get_param('pools', 'locations', 'mediatypes');
1808 if (CGI::param('expired')) {
1810 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1811 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1817 SELECT Media.VolumeName AS volumename,
1818 Media.VolStatus AS volstatus,
1819 Media.LastWritten AS lastwritten,
1820 Media.MediaType AS mediatype,
1821 Media.VolMounts AS volmounts,
1823 $self->{sql}->{FROM_UNIXTIME}(
1824 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
1825 + $self->{sql}->{TO_SEC}(Media.VolRetention)
1828 INNER JOIN Pool ON (Pool.PoolId = Media.PoolId)
1829 LEFT JOIN Location ON (Location.LocationId = Media.LocationId)
1831 WHERE Media.InChanger <> 1
1832 AND Media.VolStatus IN ('Purged', 'Full', 'Append')
1833 AND Media.Recycle = 1
1835 ORDER BY Media.VolUseDuration DESC, Media.VolMounts ASC, expire ASC
1839 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
1841 $self->display({ Media => [ values %$all ] },
1842 "help_intern_compute.tpl");
1848 my ($self, %arg) = @_ ;
1850 my ($limit, $label) = $self->get_limit(%arg);
1854 (SELECT count(Pool.PoolId) FROM Pool) AS nb_pool,
1855 (SELECT count(Media.MediaId) FROM Media) AS nb_media,
1856 (SELECT count(Job.JobId) FROM Job) AS nb_job,
1857 (SELECT sum(VolBytes) FROM Media) AS nb_bytes,
1858 ($self->{sql}->{DB_SIZE}) AS db_size,
1859 (SELECT count(Job.JobId)
1861 WHERE Job.JobStatus IN ('E','e','f','A')
1864 (SELECT count(Client.ClientId) FROM Client) AS nb_client
1867 my $row = $self->dbh_selectrow_hashref($query) ;
1869 $row->{nb_bytes} = human_size($row->{nb_bytes});
1871 $row->{db_size} = human_size($row->{db_size});
1872 $row->{label} = $label;
1874 $self->display($row, "general.tpl");
1879 my ($self, @what) = @_ ;
1880 my %elt = map { $_ => 1 } @what;
1885 if ($elt{clients}) {
1886 my @clients = grep { ! /^\s*$/ } CGI::param('client');
1888 $ret{clients} = \@clients;
1889 my $str = $self->dbh_join(@clients);
1890 $limit .= "AND Client.Name IN ($str) ";
1894 if ($elt{client_groups}) {
1895 my @clients = grep { ! /^\s*$/ } CGI::param('client_group');
1897 $ret{client_groups} = \@clients;
1898 my $str = $self->dbh_join(@clients);
1899 $limit .= "AND client_group_name IN ($str) ";
1903 if ($elt{filesets}) {
1904 my @filesets = grep { ! /^\s*$/ } CGI::param('fileset');
1906 $ret{filesets} = \@filesets;
1907 my $str = $self->dbh_join(@filesets);
1908 $limit .= "AND FileSet.FileSet IN ($str) ";
1912 if ($elt{mediatypes}) {
1913 my @media = grep { ! /^\s*$/ } CGI::param('mediatype');
1915 $ret{mediatypes} = \@media;
1916 my $str = $self->dbh_join(@media);
1917 $limit .= "AND Media.MediaType IN ($str) ";
1922 my $client = CGI::param('client');
1923 $ret{client} = $client;
1924 $client = $self->dbh_join($client);
1925 $limit .= "AND Client.Name = $client ";
1929 my $level = CGI::param('level') || '';
1930 if ($level =~ /^(\w)$/) {
1932 $limit .= "AND Job.Level = '$1' ";
1937 my $jobid = CGI::param('jobid') || '';
1939 if ($jobid =~ /^(\d+)$/) {
1941 $limit .= "AND Job.JobId = '$1' ";
1946 my $status = CGI::param('status') || '';
1947 if ($status =~ /^(\w)$/) {
1950 $limit .= "AND Job.JobStatus IN ('f','E') ";
1951 } elsif ($1 eq 'W') {
1952 $limit .= "AND Job.JobStatus = 'T' AND Job.JobErrors > 0 ";
1954 $limit .= "AND Job.JobStatus = '$1' ";
1959 if ($elt{volstatus}) {
1960 my $status = CGI::param('volstatus') || '';
1961 if ($status =~ /^(\w+)$/) {
1963 $limit .= "AND Media.VolStatus = '$1' ";
1967 if ($elt{locations}) {
1968 my @location = grep { ! /^\s*$/ } CGI::param('location') ;
1970 $ret{locations} = \@location;
1971 my $str = $self->dbh_join(@location);
1972 $limit .= "AND Location.Location IN ($str) ";
1977 my @pool = grep { ! /^\s*$/ } CGI::param('pool') ;
1979 $ret{pools} = \@pool;
1980 my $str = $self->dbh_join(@pool);
1981 $limit .= "AND Pool.Name IN ($str) ";
1985 if ($elt{location}) {
1986 my $location = CGI::param('location') || '';
1988 $ret{location} = $location;
1989 $location = $self->dbh_quote($location);
1990 $limit .= "AND Location.Location = $location ";
1995 my $pool = CGI::param('pool') || '';
1998 $pool = $self->dbh_quote($pool);
1999 $limit .= "AND Pool.Name = $pool ";
2003 if ($elt{jobtype}) {
2004 my $jobtype = CGI::param('jobtype') || '';
2005 if ($jobtype =~ /^(\w)$/) {
2007 $limit .= "AND Job.Type = '$1' ";
2011 return ($limit, %ret);
2022 my ($self, %arg) = @_ ;
2023 $self->can_do('r_view_job');
2025 $arg{order} = ' Job.JobId DESC ';
2027 my ($limit, $label) = $self->get_limit(%arg);
2028 my ($where, undef) = $self->get_param('clients',
2037 if (CGI::param('client_group')) {
2039 JOIN client_group_member USING (ClientId)
2040 JOIN client_group USING (client_group_id)
2043 my $filter = $self->get_client_filter();
2046 SELECT Job.JobId AS jobid,
2047 Client.Name AS client,
2048 FileSet.FileSet AS fileset,
2049 Job.Name AS jobname,
2051 StartTime AS starttime,
2053 Pool.Name AS poolname,
2054 JobFiles AS jobfiles,
2055 JobBytes AS jobbytes,
2056 JobStatus AS jobstatus,
2057 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2058 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
2061 JobErrors AS joberrors
2063 FROM Client $filter $cgq,
2064 Job LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2065 LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2066 WHERE Client.ClientId=Job.ClientId
2067 AND Job.JobStatus NOT IN ('R', 'C')
2072 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
2074 $self->display({ Filter => $label,
2078 sort { $a->{jobid} <=> $b->{jobid} }
2085 # display job informations
2086 sub display_job_zoom
2088 my ($self, $jobid) = @_ ;
2089 $self->can_do('r_view_job');
2091 $jobid = $self->dbh_quote($jobid);
2093 # get security filter
2094 my $filter = $self->get_client_filter();
2097 SELECT DISTINCT Job.JobId AS jobid,
2098 Client.Name AS client,
2099 Job.Name AS jobname,
2100 FileSet.FileSet AS fileset,
2102 Pool.Name AS poolname,
2103 StartTime AS starttime,
2104 JobFiles AS jobfiles,
2105 JobBytes AS jobbytes,
2106 JobStatus AS jobstatus,
2107 JobErrors AS joberrors,
2108 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2109 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)) AS duration
2111 FROM Client $filter,
2112 Job LEFT JOIN FileSet ON (Job.FileSetId = FileSet.FileSetId)
2113 LEFT JOIN Pool ON (Job.PoolId = Pool.PoolId)
2114 WHERE Client.ClientId=Job.ClientId
2115 AND Job.JobId = $jobid
2118 my $row = $self->dbh_selectrow_hashref($query) ;
2120 # display all volumes associate with this job
2122 SELECT Media.VolumeName as volumename
2123 FROM Job,Media,JobMedia
2124 WHERE Job.JobId = $jobid
2125 AND JobMedia.JobId=Job.JobId
2126 AND JobMedia.MediaId=Media.MediaId
2129 my $all = $self->dbh_selectall_hashref($query, 'volumename');
2131 $row->{volumes} = [ values %$all ] ;
2133 $self->display($row, "display_job_zoom.tpl");
2136 sub display_job_group
2138 my ($self, %arg) = @_;
2139 $self->can_do('r_view_job');
2141 my ($limit, $label) = $self->get_limit(groupby => 'client_group_name', %arg);
2143 my ($where, undef) = $self->get_param('client_groups',
2146 my $filter = $self->get_client_group_filter();
2149 SELECT client_group_name AS client_group_name,
2150 COALESCE(jobok.jobfiles,0) + COALESCE(joberr.jobfiles,0) AS jobfiles,
2151 COALESCE(jobok.jobbytes,0) + COALESCE(joberr.jobbytes,0) AS jobbytes,
2152 COALESCE(jobok.joberrors,0) + COALESCE(joberr.joberrors,0) AS joberrors,
2153 COALESCE(jobok.nbjobs,0) AS nbjobok,
2154 COALESCE(joberr.nbjobs,0) AS nbjoberr,
2155 COALESCE(jobok.duration, '0:0:0') AS duration
2157 FROM client_group $filter LEFT JOIN (
2158 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2159 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2160 SUM(JobErrors) AS joberrors,
2161 SUM($self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(EndTime)
2162 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime)))
2165 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2166 JOIN client_group USING (client_group_id)
2168 WHERE JobStatus = 'T'
2171 ) AS jobok USING (client_group_name) LEFT JOIN
2174 SELECT client_group_name AS client_group_name, COUNT(1) AS nbjobs,
2175 SUM(JobFiles) AS jobfiles, SUM(JobBytes) AS jobbytes,
2176 SUM(JobErrors) AS joberrors
2177 FROM Job JOIN client_group_member ON (Job.ClientId = client_group_member.ClientId)
2178 JOIN client_group USING (client_group_id)
2180 WHERE JobStatus IN ('f','E', 'A')
2183 ) AS joberr USING (client_group_name)
2187 my $all = $self->dbh_selectall_hashref($query, 'client_group_name');
2189 my $rep = { groups => [ values %$all ], age => $arg{age}, filter => $label };
2192 $self->display($rep, "display_job_group.tpl");
2197 my ($self, %arg) = @_ ;
2199 my ($limit, $label) = $self->get_limit(%arg);
2200 my ($where, %elt) = $self->get_param('pools',
2205 my $arg = $self->get_form('jmedias', 'qre_media', 'expired');
2207 if ($arg->{jmedias}) {
2208 $where = "AND Media.VolumeName IN ($arg->{jmedias}) $where";
2210 if ($arg->{qre_media}) {
2211 $where = "AND Media.VolumeName $self->{sql}->{MATCH} $arg->{qre_media} $where";
2213 if ($arg->{expired}) {
2215 AND VolStatus = 'Full'
2216 AND ( $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2217 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2218 ) < NOW() " . $where ;
2222 SELECT Media.VolumeName AS volumename,
2223 Media.VolBytes AS volbytes,
2224 Media.VolStatus AS volstatus,
2225 Media.MediaType AS mediatype,
2226 Media.InChanger AS online,
2227 Media.LastWritten AS lastwritten,
2228 Location.Location AS location,
2229 (volbytes*100/COALESCE(media_avg_size.size,-1)) AS volusage,
2230 Pool.Name AS poolname,
2231 $self->{sql}->{FROM_UNIXTIME}(
2232 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2233 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2236 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2237 LEFT JOIN (SELECT avg(Media.VolBytes) AS size,
2238 Media.MediaType AS MediaType
2240 WHERE Media.VolStatus = 'Full'
2241 GROUP BY Media.MediaType
2242 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
2244 WHERE Media.PoolId=Pool.PoolId
2249 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2251 $self->display({ ID => $cur_id++,
2253 Location => $elt{location},
2254 Media => [ values %$all ],
2256 "display_media.tpl");
2259 sub display_allmedia
2263 my $pool = $self->get_form('db_pools');
2265 foreach my $name (@{ $pool->{db_pools} }) {
2266 CGI::param('pool', $name->{name});
2267 $self->display_media();
2271 sub display_media_zoom
2275 my $media = $self->get_form('jmedias');
2277 unless ($media->{jmedias}) {
2278 return $self->error("Can't get media selection");
2282 SELECT InChanger AS online,
2283 Media.Enabled AS enabled,
2284 VolBytes AS nb_bytes,
2285 VolumeName AS volumename,
2286 VolStatus AS volstatus,
2287 VolMounts AS nb_mounts,
2288 Media.VolUseDuration AS voluseduration,
2289 Media.MaxVolJobs AS maxvoljobs,
2290 Media.MaxVolFiles AS maxvolfiles,
2291 Media.MaxVolBytes AS maxvolbytes,
2292 VolErrors AS nb_errors,
2293 Pool.Name AS poolname,
2294 Location.Location AS location,
2295 Media.Recycle AS recycle,
2296 Media.VolRetention AS volretention,
2297 Media.LastWritten AS lastwritten,
2298 Media.VolReadTime/1000000 AS volreadtime,
2299 Media.VolWriteTime/1000000 AS volwritetime,
2300 Media.RecycleCount AS recyclecount,
2301 Media.Comment AS comment,
2302 $self->{sql}->{FROM_UNIXTIME}(
2303 $self->{sql}->{UNIX_TIMESTAMP}(Media.LastWritten)
2304 + $self->{sql}->{TO_SEC}(Media.VolRetention)
2307 Media LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
2308 WHERE Pool.PoolId = Media.PoolId
2309 AND VolumeName IN ($media->{jmedias})
2312 my $all = $self->dbh_selectall_hashref($query, 'volumename') ;
2314 foreach my $media (values %$all) {
2315 my $mq = $self->dbh_quote($media->{volumename});
2318 SELECT DISTINCT Job.JobId AS jobid,
2320 Job.StartTime AS starttime,
2323 Job.JobFiles AS files,
2324 Job.JobBytes AS bytes,
2325 Job.jobstatus AS status
2326 FROM Media,JobMedia,Job
2327 WHERE Media.VolumeName=$mq
2328 AND Media.MediaId=JobMedia.MediaId
2329 AND JobMedia.JobId=Job.JobId
2332 my $jobs = $self->dbh_selectall_hashref($query, 'jobid') ;
2335 SELECT LocationLog.Date AS date,
2336 Location.Location AS location,
2337 LocationLog.Comment AS comment
2338 FROM Media,LocationLog INNER JOIN Location ON (LocationLog.LocationId = Location.LocationId)
2339 WHERE Media.MediaId = LocationLog.MediaId
2340 AND Media.VolumeName = $mq
2344 my $log = $self->dbh_selectall_arrayref($query) ;
2346 $logtxt = join("\n", map { ($_->[0] . ' ' . $_->[1] . ' ' . $_->[2])} @$log ) ;
2349 $self->display({ jobs => [ values %$jobs ],
2350 LocationLog => $logtxt,
2352 "display_media_zoom.tpl");
2359 $self->can_do('r_location_mgnt');
2361 my $loc = $self->get_form('qlocation');
2362 unless ($loc->{qlocation}) {
2363 return $self->error("Can't get location");
2367 SELECT Location.Location AS location,
2368 Location.Cost AS cost,
2369 Location.Enabled AS enabled
2371 WHERE Location.Location = $loc->{qlocation}
2374 my $row = $self->dbh_selectrow_hashref($query);
2376 $self->display({ ID => $cur_id++,
2377 %$row }, "location_edit.tpl") ;
2383 $self->can_do('r_location_mgnt');
2385 my $arg = $self->get_form(qw/qlocation qnewlocation cost/) ;
2386 unless ($arg->{qlocation}) {
2387 return $self->error("Can't get location");
2389 unless ($arg->{qnewlocation}) {
2390 return $self->error("Can't get new location name");
2392 unless ($arg->{cost}) {
2393 return $self->error("Can't get new cost");
2396 my $enabled = CGI::param('enabled') || '';
2397 $enabled = $enabled?1:0;
2400 UPDATE Location SET Cost = $arg->{cost},
2401 Location = $arg->{qnewlocation},
2403 WHERE Location.Location = $arg->{qlocation}
2406 $self->dbh_do($query);
2408 $self->location_display();
2414 $self->can_do('r_location_mgnt');
2416 my $arg = $self->get_form(qw/qlocation/) ;
2418 unless ($arg->{qlocation}) {
2419 return $self->error("Can't get location");
2423 SELECT count(Media.MediaId) AS nb
2424 FROM Media INNER JOIN Location USING (LocationID)
2425 WHERE Location = $arg->{qlocation}
2428 my $res = $self->dbh_selectrow_hashref($query);
2431 return $self->error("Sorry, the location must be empty");
2435 DELETE FROM Location WHERE Location = $arg->{qlocation} LIMIT 1
2438 $self->dbh_do($query);
2440 $self->location_display();
2446 $self->can_do('r_location_mgnt');
2448 my $arg = $self->get_form(qw/qlocation cost/) ;
2450 unless ($arg->{qlocation}) {
2451 $self->display({}, "location_add.tpl");
2454 unless ($arg->{cost}) {
2455 return $self->error("Can't get new cost");
2458 my $enabled = CGI::param('enabled') || '';
2459 $enabled = $enabled?1:0;
2462 INSERT INTO Location (Location, Cost, Enabled)
2463 VALUES ($arg->{qlocation}, $arg->{cost}, $enabled)
2466 $self->dbh_do($query);
2468 $self->location_display();
2471 sub location_display
2476 SELECT Location.Location AS location,
2477 Location.Cost AS cost,
2478 Location.Enabled AS enabled,
2479 (SELECT count(Media.MediaId)
2481 WHERE Media.LocationId = Location.LocationId
2486 my $location = $self->dbh_selectall_hashref($query, 'location');
2488 $self->display({ ID => $cur_id++,
2489 Locations => [ values %$location ] },
2490 "display_location.tpl");
2497 my $media = $self->get_selected_media_location();
2502 my $arg = $self->get_form('db_locations', 'qnewlocation');
2504 $self->display({ email => $self->{info}->{email_media},
2506 media => [ values %$media ],
2508 "update_location.tpl");
2511 ###########################################################
2516 $self->can_do('r_group_mgnt');
2518 my $grp = $self->get_form(qw/qclient_group db_clients/);
2520 unless ($grp->{qclient_group}) {
2521 return $self->error("Can't get group");
2526 FROM Client JOIN client_group_member using (clientid)
2527 JOIN client_group using (client_group_id)
2528 WHERE client_group_name = $grp->{qclient_group}
2531 my $row = $self->dbh_selectall_hashref($query, "name");
2533 $self->display({ ID => $cur_id++,
2534 client_group => $grp->{qclient_group},
2536 client_group_member => [ values %$row]},
2543 $self->can_do('r_group_mgnt');
2545 my $arg = $self->get_form(qw/qclient_group jclients qnewgroup/);
2546 unless ($arg->{qclient_group}) {
2547 return $self->error("Can't get groups");
2550 $self->{dbh}->begin_work();
2553 DELETE FROM client_group_member
2554 WHERE client_group_id IN
2555 (SELECT client_group_id
2557 WHERE client_group_name = $arg->{qclient_group})
2559 $self->dbh_do($query);
2562 INSERT INTO client_group_member (clientid, client_group_id)
2564 (SELECT client_group_id
2566 WHERE client_group_name = $arg->{qclient_group})
2567 FROM Client WHERE Name IN ($arg->{jclients})
2570 $self->dbh_do($query);
2572 if ($arg->{qclient_group} ne $arg->{qnewgroup}) {
2575 SET client_group_name = $arg->{qnewgroup}
2576 WHERE client_group_name = $arg->{qclient_group}
2579 $self->dbh_do($query);
2582 $self->{dbh}->commit() or $self->error($self->{dbh}->errstr);
2584 $self->display_groups();
2590 $self->can_do('r_group_mgnt');
2592 my $arg = $self->get_form(qw/qclient_group/);
2594 unless ($arg->{qclient_group}) {
2595 return $self->error("Can't get groups");
2598 $self->{dbh}->begin_work();
2601 DELETE FROM client_group_member
2602 WHERE client_group_id IN
2603 (SELECT client_group_id
2605 WHERE client_group_name = $arg->{qclient_group});
2607 DELETE FROM bweb_client_group_acl
2608 WHERE client_group_id IN
2609 (SELECT client_group_id
2611 WHERE client_group_name = $arg->{qclient_group});
2613 DELETE FROM client_group
2614 WHERE client_group_name = $arg->{qclient_group};
2616 $self->dbh_do($query);
2618 $self->{dbh}->commit();
2620 $self->display_groups();
2627 $self->can_do('r_group_mgnt');
2629 my $arg = $self->get_form(qw/qclient_group/) ;
2631 unless ($arg->{qclient_group}) {
2632 $self->display({}, "groups_add.tpl");
2637 INSERT INTO client_group (client_group_name)
2638 VALUES ($arg->{qclient_group})
2641 $self->dbh_do($query);
2643 $self->display_groups();
2650 my $arg = $self->get_form(qw/db_client_groups/) ;
2652 if ($self->{dbh}->errstr) {
2653 return $self->error("Can't use groups with bweb, read INSTALL to enable them");
2658 $self->display({ ID => $cur_id++,
2660 "display_groups.tpl");
2663 ###########################################################
2668 if (not $self->{info}->{enable_security}) {
2671 # admin is a special user that can do everything
2672 if ($self->{loginname} eq 'admin') {
2675 if (!$self->{loginname}) {
2679 if (defined $self->{security}) {
2682 $self->{security} = {};
2683 my $u = $self->dbh_quote($self->{loginname});
2686 SELECT use_acl, rolename
2688 JOIN bweb_role_member USING (userid)
2689 JOIN bweb_role USING (roleid)
2692 my $rows = $self->dbh_selectall_arrayref($query);
2693 # do cache with this role
2697 foreach my $r (@$rows) {
2698 $self->{security}->{$r->[1]}=1;
2701 $self->{security}->{use_acl} = $rows->[0]->[0];
2705 # TODO: avoir un mode qui coupe le programme avec une page d'erreur
2706 # we can also get all security and fill {security} hash
2709 my ($self, $action) = @_;
2710 # is security enabled in configuration ?
2711 if (not $self->{info}->{enable_security}) {
2714 # admin is a special user that can do everything
2715 if ($self->{loginname} eq 'admin') {
2719 if (!$self->{loginname}) {
2720 $self->error("Can't do $action, your are not logged. " .
2721 "Check security with your administrator");
2722 $self->display_end();
2726 if (!$self->{security}->{$action}) {
2727 $self->error("$self->{loginname} sorry, but this action ($action) " .
2728 "is not permited. " .
2729 "Check security with your administrator");
2730 $self->display_end();
2740 if (!$self->{info}->{enable_security} or
2741 !$self->{info}->{enable_security_acl})
2746 if ($self->get_roles()) {
2747 return $self->{security}->{use_acl};
2753 # JOIN Client USING (ClientId) " . $b->get_client_filter() . "
2754 sub get_client_filter
2757 if ($self->use_filter()) {
2758 my $u = $self->dbh_quote($self->{loginname});
2760 JOIN (SELECT ClientId FROM client_group_member
2761 JOIN client_group USING (client_group_id)
2762 JOIN bweb_client_group_acl USING (client_group_id)
2763 JOIN bweb_user USING (userid)
2764 WHERE bweb_user.username = $u
2765 ) AS filter USING (ClientId)";
2771 #JOIN client_group USING (client_group_id)" . $b->get_client_group_filter()
2772 sub get_client_group_filter
2775 if ($self->use_filter()) {
2776 my $u = $self->dbh_quote($self->{loginname});
2778 JOIN (SELECT client_group_id
2779 FROM bweb_client_group_acl
2780 JOIN bweb_user USING (userid)
2781 WHERE bweb_user.username = $u
2782 ) AS filter USING (client_group_id)";
2788 # role and username have to be quoted before
2789 # role and username can be a quoted list
2792 my ($self, $role, $username) = @_;
2793 $self->can_do("r_user_mgnt");
2795 my $nb = $self->dbh_do("
2796 DELETE FROM bweb_role_member
2797 WHERE roleid = (SELECT roleid FROM bweb_role
2798 WHERE rolename IN ($role))
2799 AND userid = (SELECT userid FROM bweb_user
2800 WHERE username IN ($username))");
2804 # role and username have to be quoted before
2805 # role and username can be a quoted list
2808 my ($self, $role, $username) = @_;
2809 $self->can_do("r_user_mgnt");
2811 my $nb = $self->dbh_do("
2812 INSERT INTO bweb_role_member (roleid, userid)
2813 SELECT roleid, userid FROM bweb_role, bweb_user
2814 WHERE rolename IN ($role)
2815 AND username IN ($username)
2820 # role and username have to be quoted before
2821 # role and username can be a quoted list
2824 my ($self, $copy, $user) = @_;
2825 $self->can_do("r_user_mgnt");
2827 my $nb = $self->dbh_do("
2828 INSERT INTO bweb_role_member (roleid, userid)
2829 SELECT roleid, a.userid
2830 FROM bweb_user AS a, bweb_role_member
2831 JOIN bweb_user USING (userid)
2832 WHERE bweb_user.username = $copy
2833 AND a.username = $user");
2837 # username can be a join quoted list of usernames
2840 my ($self, $username) = @_;
2841 $self->can_do("r_user_mgnt");
2844 DELETE FROM bweb_role_member
2848 WHERE username in ($username))");
2850 DELETE FROM bweb_client_group_acl
2854 WHERE username IN ($username))");
2861 $self->can_do("r_user_mgnt");
2863 my $arg = $self->get_form(qw/jusernames/);
2865 unless ($arg->{jusernames}) {
2866 return $self->error("Can't get user");
2869 $self->{dbh}->begin_work();
2871 $self->revoke_all($arg->{jusernames});
2873 DELETE FROM bweb_user WHERE username IN ($arg->{jusernames})");
2875 $self->{dbh}->commit();
2877 $self->display_users();
2883 $self->can_do("r_user_mgnt");
2885 # we don't quote username directly to check that it is conform
2886 my $arg = $self->get_form(qw/username qpasswd qcomment jrolenames qcreate qcopy_username jclient_groups/) ;
2888 if (not $arg->{qcreate}) {
2889 $arg = $self->get_form(qw/db_roles db_usernames db_client_groups/);
2890 $self->display($arg, "display_user.tpl");
2894 my $u = $self->dbh_quote($arg->{username});
2896 $arg->{use_acl}=(CGI::param('use_acl')?'true':'false');
2898 if (!$arg->{qpasswd}) {
2899 $arg->{qpasswd} = "''";
2901 if (!$arg->{qcomment}) {
2902 $arg->{qcomment} = "''";
2905 # will fail if user already exists
2908 SET passwd=$arg->{qpasswd}, comment=$arg->{qcomment},
2909 use_acl=$arg->{use_acl}
2910 WHERE username = $u")
2913 INSERT INTO bweb_user (username, passwd, use_acl, comment)
2914 VALUES ($u, $arg->{qpasswd}, $arg->{use_acl}, $arg->{qcomment})");
2916 $self->{dbh}->begin_work();
2918 $self->revoke_all($u);
2920 if ($arg->{qcopy_username}) {
2921 $self->grant_like($arg->{qcopy_username}, $u);
2923 $self->grant($arg->{jrolenames}, $u);
2927 INSERT INTO bweb_client_group_acl (client_group_id, userid)
2928 SELECT client_group_id, userid
2929 FROM client_group, bweb_user
2930 WHERE client_group_name IN ($arg->{jclient_groups})
2935 $self->{dbh}->commit();
2937 $self->display_users();
2940 # TODO: we miss a matrix with all user/roles
2944 $self->can_do("r_user_mgnt");
2946 my $arg = $self->get_form(qw/db_usernames/) ;
2948 if ($self->{dbh}->errstr) {
2949 return $self->error("Can't use users with bweb, read INSTALL to enable them");
2952 $self->display({ ID => $cur_id++,
2954 "display_users.tpl");
2960 $self->can_do("r_user_mgnt");
2962 my $arg = $self->get_form('username');
2963 my $user = $self->dbh_quote($arg->{username});
2965 my $userp = $self->dbh_selectrow_hashref("
2966 SELECT username, passwd, comment, use_acl
2968 WHERE username = $user
2972 return $self->error("Can't find $user in catalog");
2974 $arg = $self->get_form(qw/db_usernames db_client_groups/);
2975 my $arg2 = $self->get_form(qw/filter db_client_groups/);
2978 #------------+--------
2983 my $role = $self->dbh_selectall_hashref("
2984 SELECT rolename, temp.userid
2986 LEFT JOIN (SELECT roleid, userid
2987 FROM bweb_user JOIN bweb_role_member USING (userid)
2988 WHERE username = $user) AS temp USING (roleid)
2993 db_usernames => $arg->{db_usernames},
2994 username => $userp->{username},
2995 comment => $userp->{comment},
2996 passwd => $userp->{passwd},
2997 use_acl => $userp->{use_acl},
2998 db_client_groups => $arg->{db_client_groups},
2999 client_group => $arg2->{db_client_groups},
3000 db_roles => [ values %$role],
3001 }, "display_user.tpl");
3005 ###########################################################
3007 sub get_media_max_size
3009 my ($self, $type) = @_;
3011 "SELECT avg(VolBytes) AS size
3013 WHERE Media.VolStatus = 'Full'
3014 AND Media.MediaType = '$type'
3017 my $res = $self->selectrow_hashref($query);
3020 return $res->{size};
3030 my $media = $self->get_form('qmedia');
3032 unless ($media->{qmedia}) {
3033 return $self->error("Can't get media");
3037 SELECT Media.Slot AS slot,
3038 PoolMedia.Name AS poolname,
3039 Media.VolStatus AS volstatus,
3040 Media.InChanger AS inchanger,
3041 Location.Location AS location,
3042 Media.VolumeName AS volumename,
3043 Media.MaxVolBytes AS maxvolbytes,
3044 Media.MaxVolJobs AS maxvoljobs,
3045 Media.MaxVolFiles AS maxvolfiles,
3046 Media.VolUseDuration AS voluseduration,
3047 Media.VolRetention AS volretention,
3048 Media.Comment AS comment,
3049 PoolRecycle.Name AS poolrecycle,
3050 Media.Enabled AS enabled
3052 FROM Media INNER JOIN Pool AS PoolMedia ON (Media.PoolId = PoolMedia.PoolId)
3053 LEFT JOIN Pool AS PoolRecycle ON (Media.RecyclePoolId = PoolRecycle.PoolId)
3054 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3056 WHERE Media.VolumeName = $media->{qmedia}
3059 my $row = $self->dbh_selectrow_hashref($query);
3060 $row->{volretention} = human_sec($row->{volretention});
3061 $row->{voluseduration} = human_sec($row->{voluseduration});
3062 $row->{enabled} = human_enabled($row->{enabled});
3064 my $elt = $self->get_form(qw/db_pools db_locations/);
3069 }, "update_media.tpl");
3075 $self->can_do('r_media_mgnt');
3077 my $arg = $self->get_form('jmedias', 'qnewlocation') ;
3079 unless ($arg->{jmedias}) {
3080 return $self->error("Can't get selected media");
3083 unless ($arg->{qnewlocation}) {
3084 return $self->error("Can't get new location");
3089 SET LocationId = (SELECT LocationId
3091 WHERE Location = $arg->{qnewlocation})
3092 WHERE Media.VolumeName IN ($arg->{jmedias})
3095 my $nb = $self->dbh_do($query);
3097 print "$nb media updated, you may have to update your autochanger.";
3099 $self->display_media();
3105 $self->can_do('r_media_mgnt');
3107 my $media = $self->get_selected_media_location();
3109 return $self->error("Can't get media selection");
3111 my $newloc = CGI::param('newlocation');
3113 my $user = CGI::param('user') || 'unknown';
3114 my $comm = CGI::param('comment') || '';
3115 $comm = $self->dbh_quote("$user: $comm");
3117 my $arg = $self->get_form('enabled');
3118 my $en = human_enabled($arg->{enabled});
3119 my $b = $self->get_bconsole();
3122 foreach my $vol (keys %$media) {
3124 INSERT LocationLog (Date, Comment, MediaId, LocationId, NewVolStatus)
3126 NOW(), $comm, (SELECT MediaId FROM Media WHERE VolumeName = '$vol'),
3127 (SELECT LocationId FROM Location WHERE Location = '$media->{$vol}->{location}'),
3128 (SELECT VolStatus FROM Media WHERE VolumeName = '$vol')
3131 $self->dbh_do($query);
3132 $self->debug($query);
3133 $b->send_cmd("update volume=\"$vol\" enabled=$en");
3138 $q->param('action', 'update_location');
3139 my $url = $q->url(-full => 1, -query=>1);
3141 $self->display({ email => $self->{info}->{email_media},
3143 newlocation => $newloc,
3144 # [ { volumename => 'vol1' }, { volumename => 'vol2'},..]
3145 media => [ values %$media ],
3147 "change_location.tpl");
3151 sub display_client_stats
3153 my ($self, %arg) = @_ ;
3154 $self->can_do('r_view_stats');
3156 my $client = $self->dbh_quote($arg{clientname});
3157 # get security filter
3158 my $filter = $self->get_client_filter();
3160 my ($limit, $label) = $self->get_limit(%arg);
3163 count(Job.JobId) AS nb_jobs,
3164 sum(Job.JobBytes) AS nb_bytes,
3165 sum(Job.JobErrors) AS nb_err,
3166 sum(Job.JobFiles) AS nb_files,
3167 Client.Name AS clientname
3168 FROM Job JOIN Client USING (ClientId) $filter
3170 Client.Name = $client
3172 GROUP BY Client.Name
3175 my $row = $self->dbh_selectrow_hashref($query);
3177 $row->{ID} = $cur_id++;
3178 $row->{label} = $label;
3179 $row->{grapharg} = "client";
3181 $self->display($row, "display_client_stats.tpl");
3185 sub display_group_stats
3187 my ($self, %arg) = @_ ;
3189 my $carg = $self->get_form(qw/qclient_group/);
3191 unless ($carg->{qclient_group}) {
3192 return $self->error("Can't get group");
3195 my ($limit, $label) = $self->get_limit(%arg);
3199 count(Job.JobId) AS nb_jobs,
3200 sum(Job.JobBytes) AS nb_bytes,
3201 sum(Job.JobErrors) AS nb_err,
3202 sum(Job.JobFiles) AS nb_files,
3203 client_group.client_group_name AS clientname
3204 FROM Job JOIN Client USING (ClientId)
3205 JOIN client_group_member ON (Client.ClientId = client_group_member.clientid)
3206 JOIN client_group USING (client_group_id)
3208 client_group.client_group_name = $carg->{qclient_group}
3210 GROUP BY client_group.client_group_name
3213 my $row = $self->dbh_selectrow_hashref($query);
3215 $row->{ID} = $cur_id++;
3216 $row->{label} = $label;
3217 $row->{grapharg} = "client_group";
3219 $self->display($row, "display_client_stats.tpl");
3222 # poolname can be undef
3225 my ($self, $poolname) = @_ ;
3229 my $arg = $self->get_form('jmediatypes', 'qmediatypes');
3230 if ($arg->{jmediatypes}) {
3231 $whereW = "WHERE MediaType IN ($arg->{jmediatypes}) ";
3232 $whereA = "AND MediaType IN ($arg->{jmediatypes}) ";
3235 # TODO : afficher les tailles et les dates
3238 SELECT subq.volmax AS volmax,
3239 subq.volnum AS volnum,
3240 subq.voltotal AS voltotal,
3242 Pool.Recycle AS recycle,
3243 Pool.VolRetention AS volretention,
3244 Pool.VolUseDuration AS voluseduration,
3245 Pool.MaxVolJobs AS maxvoljobs,
3246 Pool.MaxVolFiles AS maxvolfiles,
3247 Pool.MaxVolBytes AS maxvolbytes,
3248 subq.PoolId AS PoolId,
3249 subq.MediaType AS mediatype,
3250 $self->{sql}->{CAT_POOL_TYPE} AS uniq
3253 SELECT COALESCE(media_avg_size.volavg,0) * count(Media.MediaId) AS volmax,
3254 count(Media.MediaId) AS volnum,
3255 sum(Media.VolBytes) AS voltotal,
3256 Media.PoolId AS PoolId,
3257 Media.MediaType AS MediaType
3259 LEFT JOIN (SELECT avg(Media.VolBytes) AS volavg,
3260 Media.MediaType AS MediaType
3262 WHERE Media.VolStatus = 'Full'
3263 GROUP BY Media.MediaType
3264 ) AS media_avg_size ON (Media.MediaType = media_avg_size.MediaType)
3265 GROUP BY Media.MediaType, Media.PoolId, media_avg_size.volavg
3267 LEFT JOIN Pool ON (Pool.PoolId = subq.PoolId)
3271 my $all = $self->dbh_selectall_hashref($query, 'uniq') ;
3274 SELECT Pool.Name AS name,
3275 sum(VolBytes) AS size
3276 FROM Media JOIN Pool ON (Media.PoolId = Pool.PoolId)
3277 WHERE Media.VolStatus IN ('Recycled', 'Purged')
3281 my $empty = $self->dbh_selectall_hashref($query, 'name');
3283 foreach my $p (values %$all) {
3284 if ($p->{volmax} > 0) { # mysql returns 0.0000
3285 # we remove Recycled/Purged media from pool usage
3286 if (defined $empty->{$p->{name}}) {
3287 $p->{voltotal} -= $empty->{$p->{name}}->{size};
3289 $p->{poolusage} = sprintf('%.2f', $p->{voltotal} * 100/ $p->{volmax}) ;
3291 $p->{poolusage} = 0;
3295 SELECT VolStatus AS volstatus, count(MediaId) AS nb
3297 WHERE PoolId=$p->{poolid}
3298 AND Media.MediaType = '$p->{mediatype}'
3302 my $content = $self->dbh_selectall_hashref($query, 'volstatus');
3303 foreach my $t (values %$content) {
3304 $p->{"nb_" . $t->{volstatus}} = $t->{nb} ;
3309 $self->display({ ID => $cur_id++,
3310 MediaType => $arg->{qmediatypes}, # [ { name => type1 } , { name => type2 } ]
3311 Pools => [ values %$all ]},
3312 "display_pool.tpl");
3315 sub display_running_job
3318 $self->can_do('r_view_running_job');
3320 my $arg = $self->get_form('client', 'jobid');
3322 if (!$arg->{client} and $arg->{jobid}) {
3323 # get security filter
3324 my $filter = $self->get_client_filter();
3327 SELECT Client.Name AS name
3328 FROM Job INNER JOIN Client USING (ClientId) $filter
3329 WHERE Job.JobId = $arg->{jobid}
3332 my $row = $self->dbh_selectrow_hashref($query);
3335 $arg->{client} = $row->{name};
3336 CGI::param('client', $arg->{client});
3340 if ($arg->{client}) {
3341 my $cli = new Bweb::Client(name => $arg->{client});
3342 $cli->display_running_job($self->{info}, $arg->{jobid});
3343 if ($arg->{jobid}) {
3344 $self->get_job_log();
3347 $self->error("Can't get client or jobid");
3351 sub display_running_jobs
3353 my ($self, $display_action) = @_;
3354 $self->can_do('r_view_running_job');
3356 # get security filter
3357 my $filter = $self->get_client_filter();
3360 SELECT Job.JobId AS jobid,
3361 Job.Name AS jobname,
3363 Job.StartTime AS starttime,
3364 Job.JobFiles AS jobfiles,
3365 Job.JobBytes AS jobbytes,
3366 Job.JobStatus AS jobstatus,
3367 $self->{sql}->{SEC_TO_TIME}( $self->{sql}->{UNIX_TIMESTAMP}(NOW())
3368 - $self->{sql}->{UNIX_TIMESTAMP}(StartTime))
3370 Client.Name AS clientname
3371 FROM Job INNER JOIN Client USING (ClientId) $filter
3373 JobStatus IN ('C','R','B','e','D','F','S','m','M','s','j','c','d','t','p')
3375 my $all = $self->dbh_selectall_hashref($query, 'jobid') ;
3377 $self->display({ ID => $cur_id++,
3378 display_action => $display_action,
3379 Jobs => [ values %$all ]},
3380 "running_job.tpl") ;
3383 # return the autochanger list to update
3387 $self->can_do('r_media_mgnt');
3390 my $arg = $self->get_form('jmedias');
3392 unless ($arg->{jmedias}) {
3393 return $self->error("Can't get media selection");
3397 SELECT Media.VolumeName AS volumename,
3398 Storage.Name AS storage,
3399 Location.Location AS location,
3401 FROM Media INNER JOIN Storage ON (Media.StorageId = Storage.StorageId)
3402 LEFT JOIN Location ON (Media.LocationId = Location.LocationId)
3403 WHERE Media.VolumeName IN ($arg->{jmedias})
3404 AND Media.InChanger = 1
3407 my $all = $self->dbh_selectall_hashref($query, 'volumename');
3409 foreach my $vol (values %$all) {
3410 my $a = $self->ach_get($vol->{location});
3412 $ret{$vol->{location}} = 1;
3414 unless ($a->{have_status}) {
3416 $a->{have_status} = 1;
3419 print "eject $vol->{volumename} from $vol->{storage} : ";
3420 if ($a->send_to_io($vol->{slot})) {
3421 print "<img src='/bweb/T.png' alt='ok'><br/>";
3423 print "<img src='/bweb/E.png' alt='err'><br/>";
3433 my ($to, $subject, $content) = (CGI::param('email'),
3434 CGI::param('subject'),
3435 CGI::param('content'));
3436 $to =~ s/[^\w\d\.\@<>,]//;
3437 $subject =~ s/[^\w\d\.\[\]]/ /;
3439 open(MAIL, "|mail -s '$subject' '$to'") ;
3440 print MAIL $content;
3450 my $arg = $self->get_form('jobid', 'client');
3452 print CGI::header('text/brestore');
3453 print "jobid=$arg->{jobid}\n" if ($arg->{jobid});
3454 print "client=$arg->{client}\n" if ($arg->{client});
3455 print "\n\nYou have to assign this mime type with /usr/bin/brestore.pl\n";
3459 # TODO : move this to Bweb::Autochanger ?
3460 # TODO : make this internal to not eject tape ?
3466 my ($self, $name) = @_;
3469 return $self->error("Can't get your autochanger name ach");
3472 unless ($self->{info}->{ach_list}) {
3473 return $self->error("Could not find any autochanger");
3476 my $a = $self->{info}->{ach_list}->{$name};
3479 $self->error("Can't get your autochanger $name from your ach_list");
3484 $a->{debug} = $self->{debug};
3491 my ($self, $ach) = @_;
3492 $self->can_do('r_configure');
3494 $self->{info}->{ach_list}->{$ach->{name}} = $ach;
3496 $self->{info}->save();
3504 $self->can_do('r_configure');
3506 my $arg = $self->get_form('ach');
3508 or !$self->{info}->{ach_list}
3509 or !$self->{info}->{ach_list}->{$arg->{ach}})
3511 return $self->error("Can't get autochanger name");
3514 my $ach = $self->{info}->{ach_list}->{$arg->{ach}};
3518 [ map { { name => $_, index => $i++ } } @{$ach->{drive_name}} ] ;
3520 my $b = $self->get_bconsole();
3522 my @storages = $b->list_storage() ;
3524 $ach->{devices} = [ map { { name => $_ } } @storages ];
3526 $self->display($ach, "ach_add.tpl");
3527 delete $ach->{drives};
3528 delete $ach->{devices};
3535 $self->can_do('r_configure');
3537 my $arg = $self->get_form('ach');
3540 or !$self->{info}->{ach_list}
3541 or !$self->{info}->{ach_list}->{$arg->{ach}})
3543 return $self->error("Can't get autochanger name");
3546 delete $self->{info}->{ach_list}->{$arg->{ach}} ;
3548 $self->{info}->save();
3549 $self->{info}->view();
3555 $self->can_do('r_configure');
3557 my $arg = $self->get_form('ach', 'mtxcmd', 'device', 'precmd');
3559 my $b = $self->get_bconsole();
3560 my @storages = $b->list_storage() ;
3562 unless ($arg->{ach}) {
3563 $arg->{devices} = [ map { { name => $_ } } @storages ];
3564 return $self->display($arg, "ach_add.tpl");
3568 foreach my $drive (CGI::param('drives'))
3570 unless (grep(/^$drive$/,@storages)) {
3571 return $self->error("Can't find $drive in storage list");
3574 my $index = CGI::param("index_$drive");
3575 unless (defined $index and $index =~ /^(\d+)$/) {
3576 return $self->error("Can't get $drive index");
3579 $drives[$index] = $drive;
3583 return $self->error("Can't get drives from Autochanger");
3586 my $a = new Bweb::Autochanger(name => $arg->{ach},
3587 precmd => $arg->{precmd},
3588 drive_name => \@drives,
3589 device => $arg->{device},
3590 mtxcmd => $arg->{mtxcmd});
3592 $self->ach_register($a) ;
3594 $self->{info}->view();
3600 $self->can_do('r_delete_job');
3602 my $arg = $self->get_form('jobid');
3604 if ($arg->{jobid}) {
3605 my $b = $self->get_bconsole();
3606 my $ret = $b->send_cmd("delete jobid=\"$arg->{jobid}\"");
3610 title => "Delete a job ",
3611 name => "delete jobid=$arg->{jobid}",
3619 $self->can_do('r_media_mgnt');
3621 my $arg = $self->get_form(qw/media volstatus inchanger pool
3622 slot volretention voluseduration
3623 maxvoljobs maxvolfiles maxvolbytes
3624 qcomment poolrecycle enabled
3627 unless ($arg->{media}) {
3628 return $self->error("Can't find media selection");
3631 my $update = "update volume=$arg->{media} ";
3633 if ($arg->{volstatus}) {
3634 $update .= " volstatus=$arg->{volstatus} ";
3637 if ($arg->{inchanger}) {
3638 $update .= " inchanger=yes " ;
3640 $update .= " slot=$arg->{slot} ";
3643 $update .= " slot=0 inchanger=no ";
3646 if ($arg->{enabled}) {
3647 $update .= " enabled=$arg->{enabled} ";
3651 $update .= " pool=$arg->{pool} " ;
3654 if (defined $arg->{volretention}) {
3655 $update .= " volretention=\"$arg->{volretention}\" " ;
3658 if (defined $arg->{voluseduration}) {
3659 $update .= " voluse=\"$arg->{voluseduration}\" " ;
3662 if (defined $arg->{maxvoljobs}) {
3663 $update .= " maxvoljobs=$arg->{maxvoljobs} " ;
3666 if (defined $arg->{maxvolfiles}) {
3667 $update .= " maxvolfiles=$arg->{maxvolfiles} " ;
3670 if (defined $arg->{maxvolbytes}) {
3671 $update .= " maxvolbytes=$arg->{maxvolbytes} " ;
3674 if (defined $arg->{poolrecycle}) {
3675 $update .= " recyclepool=\"$arg->{poolrecycle}\" " ;
3678 my $b = $self->get_bconsole();
3681 content => $b->send_cmd($update),
3682 title => "Update a volume ",
3688 my $media = $self->dbh_quote($arg->{media});
3690 my $loc = CGI::param('location') || '';
3692 $loc = $self->dbh_quote($loc); # is checked by db
3693 push @q, "LocationId=(SELECT LocationId FROM Location WHERE Location=$loc)";
3695 if (!$arg->{qcomment}) {
3696 $arg->{qcomment} = "''";
3698 push @q, "Comment=$arg->{qcomment}";
3703 SET " . join (',', @q) . "
3704 WHERE Media.VolumeName = $media
3706 $self->dbh_do($query);
3708 $self->update_media();
3714 $self->can_do('r_autochanger_mgnt');
3716 my $ach = CGI::param('ach') ;
3717 $ach = $self->ach_get($ach);
3719 return $self->error("Bad autochanger name");
3723 my $b = new Bconsole(pref => $self->{info},timeout => 60,log_stdout => 1);
3724 $b->update_slots($ach->{name});
3731 $self->can_do('r_view_log');
3733 my $arg = $self->get_form('jobid', 'limit', 'offset');
3734 unless ($arg->{jobid}) {
3735 return $self->error("Can't get jobid");
3738 if ($arg->{limit} == 100) {
3739 $arg->{limit} = 1000;
3741 # get security filter
3742 my $filter = $self->get_client_filter();
3745 SELECT Job.Name as name, Client.Name as clientname
3746 FROM Job INNER JOIN Client USING (ClientId) $filter
3747 WHERE JobId = $arg->{jobid}
3750 my $row = $self->dbh_selectrow_hashref($query);
3753 return $self->error("Can't find $arg->{jobid} in catalog");
3756 # display only Error and Warning messages
3758 if (CGI::param('error')) {
3759 $filter = " AND LogText $self->{sql}->{MATCH} 'Error|Warning' ";
3763 if (CGI::param('time') || $self->{info}->{display_log_time}) {
3764 $logtext = 'LogText';
3766 $logtext = $self->dbh_strcat('Time', ' ', 'LogText')
3770 SELECT count(1) AS nbline, JobId AS jobid,
3771 GROUP_CONCAT($logtext $self->{sql}->{CONCAT_SEP}) AS logtxt
3773 SELECT JobId, Time, LogText
3775 WHERE ( Log.JobId = $arg->{jobid}
3777 AND Time >= (SELECT StartTime FROM Job WHERE JobId=$arg->{jobid})
3778 AND Time <= (SELECT COALESCE(EndTime,NOW()) FROM Job WHERE JobId=$arg->{jobid})
3782 OFFSET $arg->{offset}
3788 my $log = $self->dbh_selectrow_hashref($query);
3790 return $self->error("Can't get log for jobid $arg->{jobid}");
3793 $self->display({ lines=> $log->{logtxt},
3794 nbline => $log->{nbline},
3795 jobid => $arg->{jobid},
3796 name => $row->{name},
3797 client => $row->{clientname},
3798 offset => $arg->{offset},
3799 limit => $arg->{limit},
3800 }, 'display_log.tpl');
3806 $self->can_do('r_autochanger_mgnt');
3808 my $arg = $self->get_form('ach', 'slots', 'drive');
3810 unless ($arg->{ach}) {
3811 return $self->error("Can't find autochanger name");
3814 my $a = $self->ach_get($arg->{ach});
3816 return $self->error("Can't find autochanger name in configuration");
3819 my $storage = $a->get_drive_name($arg->{drive});
3821 return $self->error("Can't get your drive name");
3827 if ($arg->{slots}) {
3828 $slots = join(",", @{ $arg->{slots} });
3829 $slots_sql = " AND Slot IN ($slots) ";
3830 $t += 60*scalar( @{ $arg->{slots} }) ;
3833 my $b = new Bconsole(pref => $self->{info}, timeout => $t,log_stdout => 1);
3834 print "<h1>This command can take long time, be patient...</h1>";
3836 $b->label_barcodes(storage => $storage,
3837 drive => $arg->{drive},
3845 SET LocationId = (SELECT LocationId
3847 WHERE Location = '$arg->{ach}')
3849 WHERE (LocationId = 0 OR LocationId IS NULL)
3858 $self->can_do('r_purge');
3860 my @volume = CGI::param('media');
3863 return $self->error("Can't get media selection");
3866 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3868 foreach my $v (@volume) {
3870 content => $b->purge_volume($v),
3871 title => "Purge media",
3872 name => "purge volume=$v",
3881 $self->can_do('r_prune');
3883 my @volume = CGI::param('media');
3885 return $self->error("Can't get media selection");
3888 my $b = new Bconsole(pref => $self->{info}, timeout => 60);
3890 foreach my $v (@volume) {
3892 content => $b->prune_volume($v),
3893 title => "Prune volume",
3894 name => "prune volume=$v",
3903 $self->can_do('r_cancel_job');
3905 my $arg = $self->get_form('jobid');
3906 unless ($arg->{jobid}) {
3907 return $self->error("Can't get jobid");
3910 my $b = $self->get_bconsole();
3912 content => $b->cancel($arg->{jobid}),
3913 title => "Cancel job",
3914 name => "cancel jobid=$arg->{jobid}",
3920 # Warning, we display current fileset
3923 my $arg = $self->get_form('fileset');
3925 if ($arg->{fileset}) {
3926 my $b = $self->get_bconsole();
3927 my $ret = $b->get_fileset($arg->{fileset});
3928 $self->display({ fileset => $arg->{fileset},
3930 }, "fileset_view.tpl");
3932 $self->error("Can't get fileset name");
3936 sub director_show_sched
3940 my $arg = $self->get_form('days');
3942 my $b = $self->get_bconsole();
3943 my $ret = $b->director_get_sched( $arg->{days} );
3948 }, "scheduled_job.tpl");
3951 sub enable_disable_job
3953 my ($self, $what) = @_ ;
3954 $self->can_do('r_run_job');
3956 my $name = CGI::param('job') || '';
3957 unless ($name =~ /^[\w\d\.\-\s]+$/) {
3958 return $self->error("Can't find job name");
3961 my $b = $self->get_bconsole();
3971 content => $b->send_cmd("$cmd job=\"$name\""),
3972 title => "$cmd $name",
3973 name => "$cmd job=\"$name\"",
3980 return new Bconsole(pref => $self->{info});
3986 $self->can_do('r_run_job');
3988 my $b = $self->get_bconsole();
3990 my $joblist = [ map { { name => $_ } } $b->list_job() ];
3992 $self->display({ Jobs => $joblist }, "run_job.tpl");
3997 my ($self, $ouput) = @_;
4000 foreach my $l (split(/\r\n/, $ouput)) {
4001 if ($l =~ /(\w+): name=([\w\d\.\s-]+?)(\s+\w+=.+)?$/) {
4007 if (my @l = $l =~ /(\w+)=([\w\d*]+)/g) {
4013 foreach my $k (keys %arg) {
4014 $lowcase{lc($k)} = $arg{$k} ;
4023 $self->can_do('r_run_job');
4025 my $b = $self->get_bconsole();
4027 my $job = CGI::param('job') || '';
4029 # we take informations from director, and we overwrite with user wish
4030 my $info = $b->send_cmd("show job=\"$job\"");
4031 my $attr = $self->run_parse_job($info);
4033 my $arg = $self->get_form('pool', 'level', 'client', 'fileset', 'storage');
4034 my %job_opt = (%$attr, %$arg);
4036 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4038 my $pools = [ map { { name => $_ } } $b->list_pool() ];
4039 my $clients = [ map { { name => $_ } }$b->list_client()];
4040 my $filesets= [ map { { name => $_ } }$b->list_fileset() ];
4041 my $storages= [ map { { name => $_ } }$b->list_storage()];
4046 clients => $clients,
4047 filesets => $filesets,
4048 storages => $storages,
4050 }, "run_job_mod.tpl");
4056 $self->can_do('r_run_job');
4058 my $b = $self->get_bconsole();
4060 my $jobs = [ map {{ name => $_ }} $b->list_job() ];
4070 $self->can_do('r_run_job');
4072 my $b = $self->get_bconsole();
4074 # TODO: check input (don't use pool, level)
4076 my $arg = $self->get_form('pool', 'level', 'client', 'priority', 'when', 'fileset');
4077 my $job = CGI::param('job') || '';
4078 my $storage = CGI::param('storage') || '';
4080 my $jobid = $b->run(job => $job,
4081 client => $arg->{client},
4082 priority => $arg->{priority},
4083 level => $arg->{level},
4084 storage => $storage,
4085 pool => $arg->{pool},
4086 fileset => $arg->{fileset},
4087 when => $arg->{when},
4090 print $jobid, $b->{error};
4092 print "<br>You can follow job execution <a href='?action=dsp_cur_job;client=$arg->{client};jobid=$jobid'> here </a>";