1 #!/usr/local/bin/perl -w
5 use lib "__INSTALLDIR__/lib";
10 use constant BPC_FTYPE_DIR => 5;
16 my $bpc = BackupPC::Lib->new || die;
17 my %Conf = $bpc->Conf();
18 my $TopDir = $bpc->TopDir();
21 my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";
23 my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 });
27 if ( !getopts("cdm:v", \%opt ) ) {
29 usage: $0 [-c|-d] [-m num]
32 -c create database on first use
33 -d delete database before import
34 -m num import just num increments for one host
39 ###################################create tables############################3
42 print "creating tables...\n";
46 ID INTEGER PRIMARY KEY,
47 name VARCHAR(30) NOT NULL,
54 ID INTEGER PRIMARY KEY,
55 hostID INTEGER NOT NULL references hosts(id),
56 name VARCHAR(30) NOT NULL,
57 share VARCHAR(200) NOT NULL,
58 localpath VARCHAR(200)
63 create table backups (
64 hostID INTEGER NOT NULL references hosts(id),
68 PRIMARY KEY(hostID, num)
74 ID INTEGER PRIMARY KEY,
76 name VARCHAR(255) NOT NULL,
83 ID INTEGER NOT NULL PRIMARY KEY,
84 shareID INTEGER NOT NULL references shares(id),
85 backupNum INTEGER NOT NULL references backups(num),
86 name VARCHAR(255) NOT NULL,
87 path VARCHAR(255) NOT NULL,
88 fullpath VARCHAR(255) NOT NULL,
89 date TIMESTAMP NOT NULL,
90 type INTEGER NOT NULL,
91 size INTEGER NOT NULL,
92 dvdid INTEGER references dvds(id)
96 print "creating indexes...\n";
98 foreach my $index (qw(
110 my ($table,$col) = split(/_/, $index);
111 $dbh->do(qq{ create index $index on $table($col) });
119 foreach my $table (qw(hosts shares files dvds backups)) {
121 $dbh->do(qq{ DELETE FROM $table });
127 print "Debug level at $opt{v}\n";
131 #################################INSERT VALUES#############################
134 $hosts = $bpc->HostInfoRead();
140 $sth->{insert_hosts} = $dbh->prepare(qq{
141 INSERT INTO hosts (name, IP) VALUES (?,?)
144 $sth->{hosts_by_name} = $dbh->prepare(qq{
145 SELECT ID FROM hosts WHERE name=?
148 $sth->{backups_broj} = $dbh->prepare(qq{
151 WHERE hostID=? AND num=?
154 $sth->{insert_backups} = $dbh->prepare(qq{
155 INSERT INTO backups (hostID, num, date, type)
159 $sth->{insert_files} = $dbh->prepare(qq{
161 (shareID, backupNum, name, path, fullpath, date, type, size)
162 VALUES (?,?,?,?,?,?,?,?)
165 foreach my $host_key (keys %{$hosts}) {
167 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
169 $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
171 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
172 $sth->{insert_hosts}->execute(
173 $hosts->{$host_key}->{'host'},
174 $hosts->{$host_key}->{'ip'}
177 $hostID = $dbh->func('last_insert_rowid');
180 print("host ".$hosts->{$host_key}->{'host'}.": ");
182 # get backups for a host
183 my @backups = $bpc->BackupInfoRead($hostname);
184 print scalar @backups, " increments\n";
188 foreach my $backup (@backups) {
190 last if ($opt{m} && $inc_nr > $opt{m});
192 my $backupNum = $backup->{'num'};
193 my @backupShares = ();
195 print $hosts->{$host_key}->{'host'},"\t#$backupNum\n";
197 $sth->{backups_broj}->execute($hostID, $backupNum);
198 my ($broj) = $sth->{backups_broj}->fetchrow_array();
201 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
202 foreach my $share ($files->shareList($backupNum)) {
205 $shareID = getShareID($share, $hostID, $hostname);
207 my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
208 print " $nf/$f files $nd/$d dirs\n";
212 $sth->{insert_backups}->execute(
215 $backup->{'endTime'},
228 my ($share, $hostID, $hostname) = @_;
230 $sth->{share_id} ||= $dbh->prepare(qq{
231 SELECT ID FROM shares WHERE hostID=? AND name=?
234 $sth->{share_id}->execute($hostID,$share);
236 my ($id) = $sth->{share_id}->fetchrow_array();
238 return $id if (defined($id));
240 $sth->{insert_share} ||= $dbh->prepare(qq{
242 (hostID,name,share,localpath)
246 my $drop_down = $hostname . '/' . $share;
247 $drop_down =~ s#//+#/#g;
249 $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
250 return $dbh->func('last_insert_rowid');
255 my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
257 $sth->{file_in_db} ||= $dbh->prepare(qq{
258 SELECT count(*) FROM files
259 WHERE shareID = ? and
266 my @param = ($shareID,$path,$name,$date,$size);
267 $sth->{file_in_db}->execute(@param);
268 my ($rows) = $sth->{file_in_db}->fetchrow_array();
269 # print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
273 ####################################################
274 # recursing through filesystem structure and #
275 # and returning flattened files list #
276 ####################################################
277 sub recurseDir($$$$$$$$) {
279 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
281 print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
283 my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
288 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
290 # first, add all the entries in current directory
291 foreach my $path_key (keys %{$filesInBackup}) {
296 $filesInBackup->{$path_key}->{'relPath'},
297 $filesInBackup->{$path_key}->{'fullPath'},
298 # $filesInBackup->{$path_key}->{'sharePathM'},
299 $filesInBackup->{$path_key}->{'mtime'},
300 $filesInBackup->{$path_key}->{'type'},
301 $filesInBackup->{$path_key}->{'size'}
304 my $key = join(" ", (
308 $filesInBackup->{$path_key}->{'mtime'},
309 $filesInBackup->{$path_key}->{'size'}
313 if (! $beenThere->{$key} && ! found_in_db(@data)) {
314 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
315 $sth->{'insert_files'}->execute(@data);
316 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
318 print STDERR " dir\n" if ($debug >= 2);
321 print STDERR " file\n" if ($debug >= 2);
324 $beenThere->{$key}++;
326 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
329 my $full_path = $dir . '/' . $path_key;
330 push @stack, $full_path;
331 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
333 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
345 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
347 while ( my $dir = shift @stack ) {
348 my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
349 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
357 return ($nr_files, $new_files, $nr_dirs, $new_dirs);