/[BackupPC]/trunk/bin/BackupPC_updatedb
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Annotation of /trunk/bin/BackupPC_updatedb

Parent Directory Parent Directory | Revision Log Revision Log


Revision 37 - (hide annotations)
Fri Aug 19 14:57:30 2005 UTC (18 years, 9 months ago) by dpavlin
File size: 8503 byte(s)
added date, time and average items per second

1 dpavlin 14 #!/usr/local/bin/perl -w
2 dpavlin 6
3     use strict;
4     use DBI;
5     use lib "__INSTALLDIR__/lib";
6     use BackupPC::Lib;
7     use BackupPC::View;
8     use Data::Dumper;
9     use Getopt::Std;
10 dpavlin 37 use Time::HiRes qw/time/;
11     use POSIX qw/strftime/;
12 dpavlin 6 use constant BPC_FTYPE_DIR => 5;
13    
14 dpavlin 30 my $debug = 0;
15 dpavlin 14 $|=1;
16 dpavlin 6
17 dpavlin 37 my $t_fmt = '%Y-%m-%d %H:%M:%S';
18    
19 dpavlin 6 my $hosts;
20     my $bpc = BackupPC::Lib->new || die;
21     my %Conf = $bpc->Conf();
22     my $TopDir = $bpc->TopDir();
23 dpavlin 14 my $beenThere = {};
24 dpavlin 6
25 dpavlin 14 my $dsn = "dbi:SQLite:dbname=$TopDir/$Conf{SearchDB}";
26 dpavlin 6
27 dpavlin 14 my $dbh = DBI->connect($dsn, "", "", { RaiseError => 1, AutoCommit => 0 });
28    
29 dpavlin 6 my %opt;
30    
31 dpavlin 32 if ( !getopts("cdm:v", \%opt ) ) {
32 dpavlin 6 print STDERR <<EOF;
33 dpavlin 14 usage: $0 [-c|-d] [-m num]
34 dpavlin 6
35     Options:
36 dpavlin 14 -c create database on first use
37     -d delete database before import
38     -m num import just num increments for one host
39 dpavlin 6 EOF
40     exit 1;
41     }
42    
43     ###################################create tables############################3
44    
45 dpavlin 14 if ($opt{c}) {
46     print "creating tables...\n";
47 dpavlin 6
48 dpavlin 14 $dbh->do(qq{
49     create table hosts (
50     ID INTEGER PRIMARY KEY,
51     name VARCHAR(30) NOT NULL,
52     IP VARCHAR(15)
53 dpavlin 6 );
54 dpavlin 14 });
55 dpavlin 6
56 dpavlin 14 $dbh->do(qq{
57     create table shares (
58     ID INTEGER PRIMARY KEY,
59     hostID INTEGER NOT NULL references hosts(id),
60     name VARCHAR(30) NOT NULL,
61     share VARCHAR(200) NOT NULL,
62     localpath VARCHAR(200)
63 dpavlin 6 );
64 dpavlin 14 });
65 dpavlin 6
66 dpavlin 14 $dbh->do(qq{
67     create table backups (
68     hostID INTEGER NOT NULL references hosts(id),
69     num INTEGER NOT NULL,
70     date DATE,
71     type CHAR(1),
72     PRIMARY KEY(hostID, num)
73 dpavlin 6 );
74 dpavlin 14 });
75 dpavlin 6
76 dpavlin 14 $dbh->do(qq{
77     create table dvds (
78     ID INTEGER PRIMARY KEY,
79     num INTEGER NOT NULL,
80     name VARCHAR(255) NOT NULL,
81     mjesto VARCHAR(255)
82 dpavlin 6 );
83 dpavlin 14 });
84 dpavlin 6
85 dpavlin 14 $dbh->do(qq{
86     create table files (
87     ID INTEGER NOT NULL PRIMARY KEY,
88     shareID INTEGER NOT NULL references shares(id),
89 dpavlin 18 backupNum INTEGER NOT NULL references backups(num),
90 dpavlin 14 name VARCHAR(255) NOT NULL,
91     path VARCHAR(255) NOT NULL,
92     fullpath VARCHAR(255) NOT NULL,
93     date TIMESTAMP NOT NULL,
94     type INTEGER NOT NULL,
95     size INTEGER NOT NULL,
96     dvdid INTEGER references dvds(id)
97 dpavlin 6 );
98 dpavlin 14 });
99 dpavlin 6
100 dpavlin 14 print "creating indexes...\n";
101 dpavlin 6
102 dpavlin 14 foreach my $index (qw(
103     hosts_name
104     backups_hostID
105     backups_num
106     shares_hostID
107     shares_name
108     files_shareID
109     files_path
110     files_name
111     files_date
112     files_size
113     )) {
114     my ($table,$col) = split(/_/, $index);
115     $dbh->do(qq{ create index $index on $table($col) });
116     }
117    
118    
119     }
120    
121     if ($opt{d}) {
122     print "deleting ";
123     foreach my $table (qw(hosts shares files dvds backups)) {
124     print "$table ";
125     $dbh->do(qq{ DELETE FROM $table });
126     }
127     print " done...\n";
128     }
129    
130 dpavlin 32 if ($opt{v}) {
131     print "Debug level at $opt{v}\n";
132     $debug = $opt{v};
133     }
134    
135 dpavlin 6 #################################INSERT VALUES#############################
136    
137     # get hosts
138 dpavlin 8 $hosts = $bpc->HostInfoRead();
139 dpavlin 6 my $hostID;
140     my $shareID;
141    
142 dpavlin 14 my $sth;
143 dpavlin 6
144 dpavlin 14 $sth->{insert_hosts} = $dbh->prepare(qq{
145     INSERT INTO hosts (name, IP) VALUES (?,?)
146     });
147 dpavlin 6
148 dpavlin 14 $sth->{hosts_by_name} = $dbh->prepare(qq{
149     SELECT ID FROM hosts WHERE name=?
150     });
151    
152     $sth->{backups_broj} = $dbh->prepare(qq{
153     SELECT COUNT(*)
154     FROM backups
155     WHERE hostID=? AND num=?
156     });
157    
158     $sth->{insert_backups} = $dbh->prepare(qq{
159     INSERT INTO backups (hostID, num, date, type)
160     VALUES (?,?,?,?)
161     });
162    
163     $sth->{insert_files} = $dbh->prepare(qq{
164     INSERT INTO files
165     (shareID, backupNum, name, path, fullpath, date, type, size)
166     VALUES (?,?,?,?,?,?,?,?)
167     });
168    
169     foreach my $host_key (keys %{$hosts}) {
170    
171     my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
172    
173     $sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
174    
175     unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
176     $sth->{insert_hosts}->execute(
177     $hosts->{$host_key}->{'host'},
178     $hosts->{$host_key}->{'ip'}
179     );
180    
181     $hostID = $dbh->func('last_insert_rowid');
182     }
183    
184     print("host ".$hosts->{$host_key}->{'host'}.": ");
185 dpavlin 6
186 dpavlin 14 # get backups for a host
187     my @backups = $bpc->BackupInfoRead($hostname);
188     print scalar @backups, " increments\n";
189 dpavlin 6
190 dpavlin 14 my $inc_nr = 0;
191    
192     foreach my $backup (@backups) {
193     $inc_nr++;
194     last if ($opt{m} && $inc_nr > $opt{m});
195    
196     my $backupNum = $backup->{'num'};
197     my @backupShares = ();
198    
199 dpavlin 37 print $hosts->{$host_key}->{'host'}, "\t#$backupNum\n";
200 dpavlin 14
201     $sth->{backups_broj}->execute($hostID, $backupNum);
202     my ($broj) = $sth->{backups_broj}->fetchrow_array();
203     next if ($broj > 0);
204    
205 dpavlin 34 my $files = BackupPC::View->new($bpc, $hostname, \@backups, 1);
206 dpavlin 14 foreach my $share ($files->shareList($backupNum)) {
207    
208 dpavlin 37 my $t = time();
209    
210     print strftime($t_fmt,localtime())," ", $share;
211 dpavlin 14 $shareID = getShareID($share, $hostID, $hostname);
212    
213 dpavlin 35 my ($f, $nf, $d, $nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, "", $shareID);
214 dpavlin 37 printf(" %d/%d files %d/%d dirs [%.2f/s]\n",
215     $nf, $f, $nd, $d,
216     ( ($f+$d) / ((time() - $t) || 1) )
217     );
218 dpavlin 14 $dbh->commit();
219     }
220 dpavlin 29
221     $sth->{insert_backups}->execute(
222     $hostID,
223     $backupNum,
224     $backup->{'endTime'},
225     $backup->{'type'}
226     );
227     $dbh->commit();
228    
229 dpavlin 6 }
230     }
231 dpavlin 14 undef $sth;
232 dpavlin 6 $dbh->commit();
233     $dbh->disconnect();
234    
235 dpavlin 14 sub getShareID() {
236 dpavlin 6
237 dpavlin 14 my ($share, $hostID, $hostname) = @_;
238    
239     $sth->{share_id} ||= $dbh->prepare(qq{
240     SELECT ID FROM shares WHERE hostID=? AND name=?
241     });
242    
243     $sth->{share_id}->execute($hostID,$share);
244    
245     my ($id) = $sth->{share_id}->fetchrow_array();
246    
247     return $id if (defined($id));
248    
249     $sth->{insert_share} ||= $dbh->prepare(qq{
250     INSERT INTO shares
251     (hostID,name,share,localpath)
252     VALUES (?,?,?,?)
253     });
254    
255 dpavlin 25 my $drop_down = $hostname . '/' . $share;
256     $drop_down =~ s#//+#/#g;
257    
258     $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef);
259 dpavlin 14 return $dbh->func('last_insert_rowid');
260 dpavlin 6 }
261    
262 dpavlin 14 sub found_in_db {
263    
264     my ($shareID,undef,$name,$path,undef,$date,undef,$size) = @_;
265    
266     $sth->{file_in_db} ||= $dbh->prepare(qq{
267     SELECT count(*) FROM files
268     WHERE shareID = ? and
269     path = ? and
270     name = ? and
271     date = ? and
272     size = ?
273     });
274    
275     my @param = ($shareID,$path,$name,$date,$size);
276     $sth->{file_in_db}->execute(@param);
277     my ($rows) = $sth->{file_in_db}->fetchrow_array();
278 dpavlin 15 # print STDERR ( $rows ? '+' : '-' ), join(" ",@param), "\n";
279 dpavlin 14 return $rows;
280 dpavlin 6 }
281    
282     ####################################################
283     # recursing through filesystem structure and #
284     # and returning flattened files list #
285     ####################################################
286 dpavlin 14 sub recurseDir($$$$$$$$) {
287 dpavlin 6
288 dpavlin 35 my ($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID) = @_;
289 dpavlin 14
290 dpavlin 30 print STDERR "recurse($hostname,$backupNum,$share,$dir,$shareID)\n" if ($debug >= 1);
291 dpavlin 29
292 dpavlin 14 my ($nr_files, $new_files, $nr_dirs, $new_dirs) = (0,0,0,0);
293    
294 dpavlin 27 { # scope
295 dpavlin 29 my @stack;
296 dpavlin 14
297 dpavlin 27 my $filesInBackup = $files->dirAttrib($backupNum, $share, $dir);
298 dpavlin 14
299 dpavlin 27 # first, add all the entries in current directory
300     foreach my $path_key (keys %{$filesInBackup}) {
301     my @data = (
302     $shareID,
303     $backupNum,
304     $path_key,
305     $filesInBackup->{$path_key}->{'relPath'},
306     $filesInBackup->{$path_key}->{'fullPath'},
307     # $filesInBackup->{$path_key}->{'sharePathM'},
308     $filesInBackup->{$path_key}->{'mtime'},
309     $filesInBackup->{$path_key}->{'type'},
310     $filesInBackup->{$path_key}->{'size'}
311     );
312    
313     my $key = join(" ", (
314     $shareID,
315     $dir,
316     $path_key,
317     $filesInBackup->{$path_key}->{'mtime'},
318     $filesInBackup->{$path_key}->{'size'}
319     ));
320    
321    
322     if (! $beenThere->{$key} && ! found_in_db(@data)) {
323 dpavlin 30 print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2);
324 dpavlin 27 $sth->{'insert_files'}->execute(@data);
325     if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
326     $new_dirs++;
327 dpavlin 30 print STDERR " dir\n" if ($debug >= 2);
328 dpavlin 27 } else {
329     $new_files++;
330 dpavlin 30 print STDERR " file\n" if ($debug >= 2);
331 dpavlin 27 }
332     }
333     $beenThere->{$key}++;
334    
335 dpavlin 14 if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {
336 dpavlin 27 $nr_dirs++;
337    
338 dpavlin 29 my $full_path = $dir . '/' . $path_key;
339     push @stack, $full_path;
340 dpavlin 30 print STDERR "### store to stack: $full_path\n" if ($debug >= 3);
341 dpavlin 29
342 dpavlin 27 # my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $backups, $backupNum, $share, $path_key, $shareID) unless ($beenThere->{$key});
343     #
344     # $nr_files += $f;
345     # $new_files += $nf;
346     # $nr_dirs += $d;
347     # $new_dirs += $nd;
348    
349 dpavlin 14 } else {
350 dpavlin 27 $nr_files++;
351 dpavlin 14 }
352     }
353    
354 dpavlin 30 print STDERR "## STACK ",join(", ", @stack),"\n" if ($debug >= 2);
355 dpavlin 14
356 dpavlin 29 while ( my $dir = shift @stack ) {
357 dpavlin 35 my ($f,$nf,$d,$nd) = recurseDir($bpc, $hostname, $files, $backupNum, $share, $dir, $shareID);
358 dpavlin 30 print STDERR "# $dir f: $f nf: $nf d: $d nd: $nd\n" if ($debug >= 1);
359 dpavlin 29 $nr_files += $f;
360     $new_files += $nf;
361     $nr_dirs += $d;
362     $new_dirs += $nd;
363     }
364 dpavlin 14 }
365    
366     return ($nr_files, $new_files, $nr_dirs, $new_dirs);
367 dpavlin 6 }
368    

Properties

Name Value
svn:executable *

  ViewVC Help
Powered by ViewVC 1.1.26