--- trunk/bin/BackupPC_updatedb 2005/09/23 08:54:11 131 +++ trunk/bin/BackupPC_updatedb 2005/11/17 17:56:25 241 @@ -143,7 +143,7 @@ my $where = ''; my @data; - if ($host_id && $share_id && $num) { + if (defined($host_id) && defined($share_id) && defined($num)) { $where = qq{ WHERE hosts.id = ? AND @@ -209,7 +209,7 @@ $doc->add_attr('@uri', $uri); foreach my $c (@{ $sth->{NAME} }) { - $doc->add_attr($c, $row->{$c}) if ($row->{$c}); + $doc->add_attr($c, $row->{$c}) if (defined($row->{$c})); } #$doc->add_attr('@cdate', fmt_date($row->{'date'})); @@ -256,10 +256,10 @@ ## update index ## -if (($opt{i} || $opt{j} || ($index_path && ! -e $index_path)) && !$opt{c}) { +if (($opt{i} || $opt{j} || ($index_path && ! -e $TopDir . $index_path)) && !$opt{c}) { # update all print "force update of HyperEstraier index "; - print "importing existing data" unless (-e $index_path); + print "importing existing data" unless (-e $TopDir . $index_path); print "by -i flag" if ($opt{i}); print "by -j flag" if ($opt{j}); print "\n"; @@ -270,180 +270,127 @@ if ($opt{c}) { sub do_index { my $index = shift || return; - my ($table,$col,$unique) = split(/_/, $index); + my ($table,$col,$unique) = split(/:/, $index); $unique ||= ''; - $index =~ s/,/_/g; + $index =~ s/\W+/_/g; + print "$index on $table($col)" . ( $unique ? "u" : "" ) . " "; $dbh->do(qq{ create $unique index $index on $table($col) }); } print "creating tables...\n"; - - $dbh->do(qq{ + + $dbh->do( qq{ create table hosts ( ID SERIAL PRIMARY KEY, name VARCHAR(30) NOT NULL, IP VARCHAR(15) ); - }); - - $dbh->do(qq{ + create table shares ( ID SERIAL PRIMARY KEY, hostID INTEGER NOT NULL references hosts(id), name VARCHAR(30) NOT NULL, - share VARCHAR(200) NOT NULL, - localpath VARCHAR(200) + share VARCHAR(200) NOT NULL ); - }); - $dbh->do(qq{ create table dvds ( ID SERIAL PRIMARY KEY, num INTEGER NOT NULL, name VARCHAR(255) NOT NULL, mjesto VARCHAR(255) ); - }); - - $dbh->do(qq{ + create table backups ( + id serial, hostID INTEGER NOT NULL references hosts(id), num INTEGER NOT NULL, date integer NOT NULL, type CHAR(4) not null, shareID integer not null references shares(id), - size integer not null, - PRIMARY KEY(hostID, num, shareID) + size bigint not null, + inc_size bigint not null default -1, + inc_deleted boolean default false, + parts integer not null default 1, + PRIMARY KEY(id) ); - }); - #do_index('backups_hostid,num_unique'); - - - $dbh->do(qq{ create table files ( - ID SERIAL PRIMARY KEY, - shareID INTEGER NOT NULL references shares(id), - backupNum INTEGER NOT NULL, - name VARCHAR(255) NOT NULL, - path VARCHAR(255) NOT NULL, - date integer NOT NULL, - type INTEGER NOT NULL, - size INTEGER NOT NULL + ID SERIAL, + shareID INTEGER NOT NULL references shares(id), + backupNum INTEGER NOT NULL, + name VARCHAR(255) NOT NULL, + path VARCHAR(255) NOT NULL, + date integer NOT NULL, + type INTEGER NOT NULL, + size bigint NOT NULL, + primary key(id) ); - }); - - $dbh->do( qq{ - create table archive - ( - id int not null, + create table archive ( + id serial, dvd_nr int not null, + total_size bigint default -1, note text, username varchar(20) not null, - date timestamp, + date timestamp default now(), primary key(id) ); - } - ); - $dbh->do( qq{ - create table archive_backup - ( - archive_id int not null, - backup_id int not null, - status text, + create table archive_backup ( + archive_id int not null references archive(id) on delete cascade, + backup_id int not null references backups(id), primary key(archive_id, backup_id) ); - }); - - $dbh->do( qq{ - create table workflows( - id int not null, - step_id int not null, - start timestamp, - stop timestamp, - username varchar(20), - archive_id int not null, - running boolean default true, - primary key(id) - ); - }); - $dbh->do( qq{ - create table workflow_step - ( - step_id int not null, - code text, - next_step int, - stop boolean default false, - primary key(step_id) + create table archive_burned ( + archive_id int references archive(id), + date timestamp default now(), + part int not null default 1, + copy int not null default 1, + iso_size bigint default -1 ); - }); - $dbh->do( qq{ - alter table workflow_step - add constraint fk_workflow_next_step - foreign key(next_step) - references workflow_step(step_id); - }); - - $dbh->do( qq{ - alter table workflows - add constraint fk_workflows_step_id - foreign key(step_id) - references workflow_step(step_id); - }); - - $dbh->do( qq{ - alter table workflows - add constraint fk_workflows_archive_id - foreign key(archive_id) - references archive(id); - }); - - $dbh->do( qq{ - create table workflow_log - ( - workflow_id int not null, - step_id int not null, - date timestamp not null, - status text, - primary key(workflow_id, step_id) + create table backup_parts ( + id serial, + backup_id int references backups(id), + part_nr int not null check (part_nr > 0), + tar_size bigint not null check (tar_size > 0), + size bigint not null check (size > 0), + md5 text not null, + items int not null check (items > 0), + date timestamp default now(), + primary key(id) ); }); - $dbh->do( qq{ - alter table workflow_log - add constraint fk_workflow_log_workflow_id - foreign key (workflow_id) - references workflows(id); - }); - - $dbh->do( qq{ - alter table workflow_log - add constraint fk_workflow_log_step_id - foreign key (step_id) - references workflow_step(step_id); - }); - - print "creating indexes:"; + print "creating indexes: "; foreach my $index (qw( - hosts_name - backups_hostID - backups_num - shares_hostID - shares_name - files_shareID - files_path - files_name - files_date - files_size + hosts:name + backups:hostID + backups:num + backups:shareID + shares:hostID + shares:name + files:shareID + files:path + files:name + files:date + files:size + archive:dvd_nr + archive_burned:archive_id + backup_parts:backup_id,part_nr )) { - print " $index"; do_index($index); } + + print " creating sequence: "; + foreach my $seq (qw/dvd_nr/) { + print "$seq "; + $dbh->do( qq{ CREATE SEQUENCE $seq } ); + } + + print "...\n"; $dbh->commit; @@ -604,14 +551,14 @@ $sth->{insert_share} ||= $dbh->prepare(qq{ INSERT INTO shares - (hostID,name,share,localpath) - VALUES (?,?,?,?) + (hostID,name,share) + VALUES (?,?,?) }); my $drop_down = $hostname . '/' . $share; $drop_down =~ s#//+#/#g; - $sth->{insert_share}->execute($hostID,$share, $drop_down ,undef); + $sth->{insert_share}->execute($hostID,$share, $drop_down); return $dbh->last_insert_id(undef,undef,'shares',undef); } @@ -683,8 +630,32 @@ $filesInBackup->{$path_key}->{'size'} )); + # daylight saving time change offset for 1h + my $dst_offset = 60 * 60; + + my $key_dst1 = join(" ", ( + $shareID, + $dir, + $path_key, + $filesInBackup->{$path_key}->{'mtime'} - $dst_offset, + $filesInBackup->{$path_key}->{'size'} + )); + + my $key_dst2 = join(" ", ( + $shareID, + $dir, + $path_key, + $filesInBackup->{$path_key}->{'mtime'} + $dst_offset, + $filesInBackup->{$path_key}->{'size'} + )); + my $found; - if (! defined($beenThere->{$key}) && ! ($found = found_in_db($key, @data)) ) { + if ( + ! defined($beenThere->{$key}) && + ! defined($beenThere->{$key_dst1}) && + ! defined($beenThere->{$key_dst2}) && + ! ($found = found_in_db($key, @data)) + ) { print STDERR "# key: $key [", $beenThere->{$key},"]" if ($debug >= 2); if ($filesInBackup->{$path_key}->{'type'} == BPC_FTYPE_DIR) {