1 #!/usr/local/bin/perl -w
5 use lib "__INSTALLDIR__/lib";
10 use constant BPC_FTYPE_DIR => 5;
15 my $bpc = BackupPC::Lib->new || die;
16 my %Conf = $bpc->Conf();
17 my $TopDir = $bpc->TopDir();
20 my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";
22 my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 });
26 if ( !getopts("cdm:", \%opt ) ) {
28 usage: $0 [-c|-d] [-m num]
31 -c create database on first use
32 -d delete database before import
33 -m num import just num increments for one host
38 ###################################create tables############################3
41 print "creating tables...\n";
45 ID INTEGER PRIMARY KEY,
46 name VARCHAR(30) NOT NULL,
53 ID INTEGER PRIMARY KEY,
54 hostID INTEGER NOT NULL references hosts(id),
55 name VARCHAR(30) NOT NULL,
56 share VARCHAR(200) NOT NULL,
57 localpath VARCHAR(200)
62 create table backups (
63 hostID INTEGER NOT NULL references hosts(id),
67 PRIMARY KEY(hostID, num)
73 ID INTEGER PRIMARY KEY,
75 name VARCHAR(255) NOT NULL,
82 ID INTEGER NOT NULL PRIMARY KEY,
83 shareID INTEGER NOT NULL references shares(id),
84 backupNum INTEGER NOT NULL references backups(id),
85 name VARCHAR(255) NOT NULL,
86 path VARCHAR(255) NOT NULL,
87 fullpath VARCHAR(255) NOT NULL,
88 date TIMESTAMP NOT NULL,
89 type INTEGER NOT NULL,
90 size INTEGER NOT NULL,
91 dvdid INTEGER references dvds(id)
95 print "creating indexes...\n";
97 foreach my $index (qw(
109 my ($table,$col) = split(/_/, $index);
110 $dbh->do(qq{ create index $index on $table($col) });
118 foreach my $table (qw(hosts shares files dvds backups)) {
120 $dbh->do(qq{ DELETE FROM $table });
125 #################################INSERT VALUES#############################
128 $hosts = $bpc->HostInfoRead();
134 $sth->{insert_hosts} = $dbh->prepare(qq{
135 INSERT INTO hosts (name, IP) VALUES (?,?)
138 $sth->{hosts_by_name} = $dbh->prepare(qq{
139 SELECT ID FROM hosts WHERE name=?
142 $sth->{backups_broj} = $dbh->prepare(qq{
145 WHERE hostID=? AND num=?
148 $sth->{insert_backups} = $dbh->prepare(qq{
149 INSERT INTO backups (hostID, num, date, type)
153 $sth->{insert_files} = $dbh->prepare(qq{
155 (shareID, backupNum, name, path, fullpath, date, type, size)
156 VALUES (?,?,?,?,?,?,?,?)
159 foreach my $host_key (keys %{$hosts}) {
161 my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
163 $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
165 unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
166 $sth->{insert_hosts}->execute(
167 $hosts->{$host_key}->{'host'},
168 $hosts->{$host_key}->{'ip'}
171 $hostID = $dbh->func('last_insert_rowid');
174 print("host ".$hosts->{$host_key}->{'host'}.": ");
176 # get backups for a host
177 my @backups = $bpc->BackupInfoRead($hostname);
178 print scalar @backups, " increments\n";
182 foreach my $backup (@backups) {
184 last if ($opt{m} && $inc_nr > $opt{m});
186 my $backupNum = $backup->{'num'};
187 my @backupShares = ();
189 print $hosts->{$host_key}->{'host'},"\t$backupNum\n";
191 $sth->{backups_broj}->execute($hostID, $backupNum);
192 my ($broj) = $sth->{backups_broj}->fetchrow_array();
195 $sth->{insert_backups}->execute(
198 $backup->{'endTime'},
203 my $files = BackupPC::View->new($bpc, $hostname, \@backups);
204 foreach my $share ($files->shareList($backupNum)) {
207 $shareID = getShareID($share, $hostID, $hostname);
209 my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, \@backups, $backupNum, $share, "", $shareID);
210 print " $nf/$f files $nd/$d dirs\n";
221 my ($share, $hostID, $hostname) = @_;
223 $sth->{share_id} ||= $dbh->prepare(qq{
224 SELECT ID FROM shares WHERE hostID=? AND name=?
227 $sth->{share_id}->execute($hostID,$share);
229 my ($id) = $sth->{share_id}->fetchrow_array();
231 return $id if (defined($id));
233 $sth->{insert_share} ||= $dbh->prepare(qq{
235 (hostID,name,share,localpath)
239 $sth->{insert_share}->execute($hostID,$share, $hostname . $share,undef);
240 return $dbh->func('last_insert_rowid');
245 my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
247 $sth->{file_in_db} ||= $dbh->prepare(qq{
248 SELECT count(*) FROM files
249 WHERE shareID = ? and
256 my @param = ($shareID,$path,$name,$date,$size);
257 $sth->{file_in_db}->execute(@param);
258 my ($rows) = $sth->{file_in_db}->fetchrow_array();
259 print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
263 ####################################################
264 # recursing through filesystem structure and #
265 # and returning flattened files list #
266 ####################################################
267 sub recurseDir($$$$$$$$) {
269 my ($bpc, $hostname, $backups, $backupNum, $share, $dir, $shareID) = @_;
271 my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
273 my $files = BackupPC::View->new($bpc, $hostname, $backups);
274 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
276 # first, add all the entries in current directory
277 foreach my $path_key (keys %{$filesInBackup}) {
282 $filesInBackup->{$path_key}->{'relPath'},
283 $filesInBackup->{$path_key}->{'fullPath'},
284 # $filesInBackup->{$path_key}->{'sharePathM'},
285 $filesInBackup->{$path_key}->{'mtime'},
286 $filesInBackup->{$path_key}->{'type'},
287 $filesInBackup->{$path_key}->{'size'}
290 my $key = join(" ", (
294 $filesInBackup->{$path_key}->{'mtime'},
295 $filesInBackup->{$path_key}->{'size'}
299 if (! $beenThere->{$key} && ! found_in_db(@data)) {
300 $sth->{'insert_files'}->execute(@data);
301 print STDERR "$key\n";
302 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
308 $beenThere->{$key}++;
310 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
313 my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID);
325 return ($nr_files, $new_files, $nr_dirs, $new_dirs);