1 #!/usr/local/bin/perl -w
4 use lib "__INSTALLDIR__/lib";
11 use Time::HiRes qw/time/;
13 use POSIX qw/strftime/;
15 use constant BPC_FTYPE_DIR => 5;
22 my $pidfile = new File::Pid;
24 if (my $pid = $pidfile->running ) {
25 die "$0 already running: $pid\n";
26 } elsif ($pidfile->pid ne $$) {
28 $pidfile = new File::Pid;
31 print STDERR "$0 using pid ",$pidfile->pid," file ",$pidfile->file,"\n";
33 my $t_fmt = '%Y-%m-%d %H:%M:%S';
36 my $bpc = BackupPC::Lib->new || die;
37 my %Conf = $bpc->Conf();
38 my $TopDir = $bpc->TopDir();
41 my $dsn = $Conf{SearchDSN} || die "Need SearchDSN in config.pl\n";
42 my $user = $Conf{SearchUser} || '';
44 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
48 if ( !getopts("cdm:v:", \%opt ) ) {
50 usage: $0 [-c|-d] [-m num] [-v|-v level]
53 -c create database on first use
54 -d delete database before import
55 -m num import just num increments for one host
56 -v num set verbosity (debug) level (default $debug)
61 ###################################create tables############################3
65 my $index = shift || return;
66 my ($table,$col,$unique) = split(/_/, $index);
69 $dbh->do(qq{ create $unique index $index on $table($col) });
72 print "creating tables...\n";
76 ID SERIAL PRIMARY KEY,
77 name VARCHAR(30) NOT NULL,
84 ID SERIAL PRIMARY KEY,
85 hostID INTEGER NOT NULL references hosts(id),
86 name VARCHAR(30) NOT NULL,
87 share VARCHAR(200) NOT NULL,
88 localpath VARCHAR(200)
93 create table backups (
94 hostID INTEGER NOT NULL references hosts(id),
96 date integer NOT NULL,
97 type CHAR(4) not null,
98 shareID integer not null references shares(id),
99 size integer not null,
100 PRIMARY KEY(hostID, num, shareID)
104 #do_index('backups_hostid,num_unique');
108 ID SERIAL PRIMARY KEY,
109 num INTEGER NOT NULL,
110 name VARCHAR(255) NOT NULL,
117 ID SERIAL PRIMARY KEY,
118 shareID INTEGER NOT NULL references shares(id),
119 backupNum INTEGER NOT NULL,
120 name VARCHAR(255) NOT NULL,
121 path VARCHAR(255) NOT NULL,
122 date integer NOT NULL,
123 type INTEGER NOT NULL,
124 size INTEGER NOT NULL,
125 dvdid INTEGER references dvds(id)
129 print "creating indexes:";
131 foreach my $index (qw(
154 foreach my $table (qw(files dvds backups shares hosts)) {
156 $dbh->do(qq{ DELETE FROM $table });
164 print "Debug level at $opt{v}\n";
168 #################################INSERT VALUES#############################
171 $hosts = $bpc->HostInfoRead();
177 $sth->{insert_hosts} = $dbh->prepare(qq{
178 INSERT INTO hosts (name, IP) VALUES (?,?)
181 $sth->{hosts_by_name} = $dbh->prepare(qq{
182 SELECT ID FROM hosts WHERE name=?
185 $sth->{backups_count} = $dbh->prepare(qq{
188 WHERE hostID=? AND num=? AND shareid=?
191 $sth->{insert_backups} = $dbh->prepare(qq{
192 INSERT INTO backups (hostID, num, date, type, shareid, size)
196 $sth->{insert_files} = $dbh->prepare(qq{
198 (shareID, backupNum, name, path, date, type, size)
199 VALUES (?,?,?,?,?,?,?)
203 my $t = shift || return;
205 my ($ss,$mm,$hh) = gmtime($t);
206 $out .= "${hh}h" if ($hh);
207 $out .= sprintf("%02d:%02d", $mm,$ss);
211 foreach my $host_key (keys %{$hosts}) {
213 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
215 $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
217 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
218 $sth->{insert_hosts}->execute(
219 $hosts->{$host_key}->{'host'},
220 $hosts->{$host_key}->{'ip'}
223 $hostID = $dbh->last_insert_id(undef,undef,'hosts',undef);
226 print "host ".$hosts->{$host_key}->{'host'}.": ";
228 # get backups for a host
229 my @backups = $bpc->BackupInfoRead($hostname);
230 my $incs = scalar @backups;
231 print "$incs increments\n";
236 foreach my $backup (@backups) {
239 last if ($opt{m} && $inc_nr > $opt{m});
241 my $backupNum = $backup->{'num'};
242 my @backupShares = ();
244 printf("%-10s %2d/%-2d #%-2d %s %5s/%5s files (date: %s dur: %s)\n",
245 $hosts->{$host_key}->{'host'},
246 $inc_nr, $incs, $backupNum,
247 $backup->{type} || '?',
248 $backup->{nFilesNew} || '?', $backup->{nFiles} || '?',
249 strftime($t_fmt,localtime($backup->{startTime})),
250 fmt_time($backup->{endTime} - $backup->{startTime})
253 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
254 foreach my $share ($files->shareList($backupNum)) {
258 $shareID = getShareID($share, $hostID, $hostname);
260 $sth->{backups_count}->execute($hostID, $backupNum, $shareID);
261 my ($count) = $sth->{backups_count}->fetchrow_array();
262 # skip if allready in database!
263 next if ($count > 0);
266 print strftime($t_fmt,localtime())," ", $share;
268 my ($f, $nf, $d, $nd, $size) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
270 $sth->{insert_backups}->execute(
273 $backup->{'endTime'},
282 my $dur = (time() - $t) || 1;
283 printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
285 ($size / 1024 / 1024),
297 print "total duration: ",fmt_time(time() - $start_t),"\n";
303 my ($share, $hostID, $hostname) = @_;
305 $sth->{share_id} ||= $dbh->prepare(qq{
306 SELECT ID FROM shares WHERE hostID=? AND name=?
309 $sth->{share_id}->execute($hostID,$share);
311 my ($id) = $sth->{share_id}->fetchrow_array();
313 return $id if (defined($id));
315 $sth->{insert_share} ||= $dbh->prepare(qq{
317 (hostID,name,share,localpath)
321 my $drop_down = $hostname . '/' . $share;
322 $drop_down =~ s#//+#/#g;
324 $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
325 return $dbh->last_insert_id(undef,undef,'shares',undef);
333 my ($key, $shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
335 return $beenThere->{$key} if (defined($beenThere->{$key}));
337 $sth->{file_in_db} ||= $dbh->prepare(qq{
339 WHERE shareID = ? and
345 my @param = ($shareID,$path,$date,$size);
346 $sth->{file_in_db}->execute(@param);
347 my $rows = $sth->{file_in_db}->rows;
348 print STDERR "## found_in_db ",( $rows ? '+' : '-' ), join(" ",@param), "\n" if ($debug >= 3);
350 $beenThere->{$key}++;
352 $sth->{'insert_files'}->execute(@data) unless ($rows);
356 ####################################################
357 # recursing through filesystem structure and #
358 # and returning flattened files list #
359 ####################################################
360 sub recurseDir($$$$$$$$) {
362 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
364 print STDERR "\nrecurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
366 my ($nr_files, $new_files, $nr_dirs, $new_dirs, $size) = (0,0,0,0,0);
371 print STDERR "# dirAttrib($backupNum, $share, $dir)\n" if ($debug >= 2);
372 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
374 # first, add all the entries in current directory
375 foreach my $path_key (keys %{$filesInBackup}) {
376 print STDERR "# file ",Dumper($filesInBackup->{$path_key}),"\n" if ($debug >= 3);
381 $filesInBackup->{$path_key}->{'relPath'},
382 $filesInBackup->{$path_key}->{'mtime'},
383 $filesInBackup->{$path_key}->{'type'},
384 $filesInBackup->{$path_key}->{'size'}
387 my $key = join(" ", (
391 $filesInBackup->{$path_key}->{'mtime'},
392 $filesInBackup->{$path_key}->{'size'}
396 if (! defined($beenThere->{$key}) && ! found_in_db($key, @data)) {
397 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
399 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
401 print STDERR " dir\n" if ($debug >= 2);
404 print STDERR " file\n" if ($debug >= 2);
406 $size += $filesInBackup->{$path_key}->{'size'} || 0;
409 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
412 my $full_path = $dir . '/' . $path_key;
413 push @stack, $full_path;
414 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
416 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
428 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
430 while ( my $dir = shift @stack ) {
431 my ($f,$nf,$d,$nd, $s) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
432 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
441 return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);