X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=blobdiff_plain;f=bin%2FBackupPC_ASA_SearchUpdate;h=ba499f97323020baee06b6da7e74cefbe51d8ac5;hp=2ce01fced16421a8993f61da2101d6868769f47c;hb=c615e1bfc6f2b0604a2658996f5dd6b6cfa9469c;hpb=6f1417a87b4dbf737df8d0f56f41dcc806a79d0d

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index 2ce01fc..ba499f9 100755
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -78,6 +78,19 @@ warn "hosts: ",dump( $opt->host );
 
 #---- subs ----
 
+sub status {
+	my $text = shift;
+	$text =~ s{\s+$}{};
+	my $new = $0;
+	$new =~ s{^[\w\/]+/(\w+) }{$1 }; # strip path from process name
+	if ( $text =~ m/^\|/ ) {
+		$new =~ s/\|.*/$text/ or $new .= " $text";
+	} else {
+		$new =~ s/\s+.*/ $text/ or $new .= " $text";
+	}
+	$0 = $new;
+}
+
 sub fmt_time {
 	my $t = shift || return;
 	my $out = "";
@@ -170,6 +183,7 @@ sub hest_update {
 		}
 
 		print "$added";
+		status "| $added";
 
 		$offset += EST_CHUNK;
 
@@ -210,111 +224,9 @@ if ($opt->create) {
 
 	print "creating tables...\n";
 
-	$dbh->do( qq{
-		create table hosts (
-			ID	SERIAL		PRIMARY KEY,
-			name	VARCHAR(30)	NOT NULL,
-			IP	VARCHAR(15)
-		);	      
-
-		create table shares (
-			ID	SERIAL		PRIMARY KEY,
-			hostID	INTEGER		NOT NULL references hosts(id),
-			name	VARCHAR(30)	NOT NULL,
-			share	VARCHAR(200)	NOT NULL
-		);	      
-
-		create table dvds (
-			ID	SERIAL		PRIMARY KEY, 
-			num     INTEGER		NOT NULL,
-			name	VARCHAR(255)	NOT NULL,
-			mjesto	VARCHAR(255)
-		);
-
-		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	bigint		not null,
-			inc_size bigint		not null default -1,
-			inc_deleted boolean	default false,
-			parts	integer		not null default 0,
-			PRIMARY KEY(id)
-		); 	      
-
-		create table files (
-			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)
-		);
-
-		create table archive (
-			id 		serial,
-			dvd_nr		int not null,
-			total_size	bigint default -1,
-			note		text,
-			username	varchar(20) not null,
-			date		timestamp default now(),
-			primary key(id)
-		);	
-
-		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)
-		);
-
-		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
-		);
-
-		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)
-		);
-
-		-- report backups and corresponding dvd
-
-		create view backups_on_dvds as
-		select
-			backups.id as id,
-			hosts.name || ':' || shares.name as share,
-			backups.num as num,
-			backups.type as type,
-			abstime(backups.date) as backup_date,
-			backups.size as size,
-			backups.inc_size as gzip_size,
-			archive.id as archive_id,
-			archive.dvd_nr
-		from backups
-		join shares on backups.shareid=shares.id
-		join hosts on shares.hostid = hosts.id
-		left outer join archive_backup on backups.id = archive_backup.backup_id
-		left outer join archive on archive_backup.archive_id = archive.id
-		where backups.parts > 0 and size > 0
-		order by backups.date
-		;
-	});
+	my $sql;
+	{ local $/ = undef; $sql = <DATA> };
+	$dbh->do( $sql );
 
 	print "creating indexes: ";
 
@@ -337,77 +249,6 @@ if ($opt->create) {
 		do_index($index);
 	}
 
-	print " creating sequence: ";
-	foreach my $seq (qw/dvd_nr/) {
-		print "$seq ";
-		$dbh->do( qq{ CREATE SEQUENCE $seq } );
-	}
-
-=for later
-
-	print " creating triggers ";
-	$dbh->do( <<__END_OF_TRIGGER__ );
-
-create or replace function backup_parts_check() returns trigger as '
-declare
-	b_parts	integer;
-	b_counted integer;
-	b_id	integer;
-begin
-	-- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
-	if (TG_OP=''UPDATE'') then
-		b_id := new.id;
-		b_parts := new.parts;
-	elsif (TG_OP = ''INSERT'') then
-		b_id := new.id;
-		b_parts := new.parts;
-	end if;
-	b_counted := (select count(*) from backup_parts where backup_id = b_id);
-	-- raise notice ''backup % parts %'', b_id, b_parts;
-	if ( b_parts != b_counted ) then
-		raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
-	end if;
-	return null;
-end;
-' language plpgsql;
-
-create trigger do_backup_parts_check
-	after insert or update or delete on backups
-	for each row execute procedure backup_parts_check();
-
-create or replace function backup_backup_parts_check() returns trigger as '
-declare
-	b_id		integer;
-	my_part_nr	integer;
-	calc_part	integer;
-begin
-	if (TG_OP = ''INSERT'') then
-		-- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
-		b_id = new.backup_id;
-		my_part_nr = new.part_nr;
-		execute ''update backups set parts = parts + 1 where id = '' || b_id;
-	elsif (TG_OP = ''DELETE'') then
-		-- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
-		b_id = old.backup_id;
-		my_part_nr = old.part_nr;
-		execute ''update backups set parts = parts - 1 where id = '' || b_id;
-	end if;
-	calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
-	if ( my_part_nr != calc_part ) then
-		raise exception ''Update of backup_parts with backup_id % aborted, requested part_nr is % and calulated next is %'', b_id, my_part_nr, calc_part;
-	end if;
-	return null;
-end;
-' language plpgsql;
-
-create trigger do_backup_backup_parts_check
-	after insert or update or delete on backup_parts
-	for each row execute procedure backup_backup_parts_check();
-
-__END_OF_TRIGGER__
-
-=cut
-
 	print "...\n";
 
 	$dbh->commit;
@@ -440,7 +281,7 @@ INSERT INTO hosts (name, IP) VALUES (?,?)
 });
 
 $sth->{hosts_by_name} = $dbh->prepare(qq{
-SELECT ID FROM hosts WHERE name=?
+SELECT id FROM hosts WHERE name=?
 });
 
 $sth->{backups_count} = $dbh->prepare(qq{
@@ -471,14 +312,15 @@ my $host_nr = 0;
 foreach my $host_key (@hosts) {
 
 	my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
+	$hostname = lc $hostname;
 
-	next if $opt->host && ! grep { m/^$hostname$/ } @{ $opt->host };
+	next if $opt->host && ! grep { m/^$hostname$/i } @{ $opt->host };
 
 	$sth->{hosts_by_name}->execute($hostname);
 
 	unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 		$sth->{insert_hosts}->execute(
-			$hosts->{$host_key}->{'host'},
+			$hostname,
 			$hosts->{$host_key}->{'ip'}
 		);
 
@@ -518,8 +360,9 @@ foreach my $host_key (@hosts) {
 			fmt_time($backup->{endTime} - $backup->{startTime})
 		);
 		print $share_header unless $opt->quiet;
+		status "$hostname $backupNum $share_header";
 
-		my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_first => 1 });
+		my $files = BackupPC::View->new($bpc, $hostname, \@backups, { only_increment => 1 });
 
 		foreach my $share ($files->shareList($backupNum)) {
 
@@ -572,16 +415,21 @@ foreach my $host_key (@hosts) {
 			}
 
 			my $dur = (time() - $t) || 1;
-			printf(" %d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]\n",
+			my $status = sprintf("%d/%d files %d/%d dirs %0.2f MB [%.2f/s dur: %s]",
 				$nf, $f, $nd, $d,
 				($size / 1024 / 1024),
 				( ($f+$d) / $dur ),
 				fmt_time($dur)
 			);
+			print " $status\n";
+			status "$hostname $backupNum $status";
 
 			if ($nf + $nd > 0) {
-				eval { hest_update($hostID, $shareID, $backupNum) };
-				warn "ERROR: $@" if $@;
+				status "$hostname $backupNum full-text | indexing";
+				#eval { hest_update($hostID, $shareID, $backupNum) };
+				#warn "ERROR: $@" if $@;
+				hest_update($hostID, $shareID, $backupNum);
+				# eval breaks our re-try logic
 			}
 		}
 
@@ -760,3 +608,208 @@ sub recurseDir($$$$$$$$) {
 	return ($nr_files, $new_files, $nr_dirs, $new_dirs, $size);
 }
 
+__DATA__
+
+create table hosts (
+	ID	SERIAL		PRIMARY KEY,
+	name	VARCHAR(30)	NOT NULL,
+	IP	VARCHAR(15)
+);	      
+
+create table shares (
+	ID	SERIAL		PRIMARY KEY,
+	hostID	INTEGER		NOT NULL references hosts(id),
+	name	VARCHAR(30)	NOT NULL,
+	share	VARCHAR(200)	NOT NULL
+);	      
+
+create table dvds (
+	ID	SERIAL		PRIMARY KEY, 
+	num     INTEGER		NOT NULL,
+	name	VARCHAR(255)	NOT NULL,
+	mjesto	VARCHAR(255)
+);
+
+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	bigint		not null,
+	inc_size bigint		not null default -1,
+	inc_deleted boolean	default false,
+	parts	integer		not null default 0,
+	PRIMARY KEY(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(),
+	filename text not null,
+	primary key(id)
+);
+
+create table files (
+	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)
+);
+
+create sequence dvd_nr;
+
+create table archive (
+	id 		serial,
+	dvd_nr		int not null,
+	total_size	bigint default -1,
+	note		text,
+	username	varchar(20) not null,
+	date		timestamp default now(),
+	primary key(id)
+);	
+
+create table archive_parts (
+	archive_id	int not null references archive(id) on delete cascade,
+	backup_part_id	int not null references backup_parts(id),
+	primary key(archive_id, backup_part_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
+);
+
+-- report backups and corresponding dvd
+
+--create view backups_on_dvds as
+--select
+--	backups.id as id,
+--	hosts.name || ':' || shares.name as share,
+--	backups.num as num,
+--	backups.type as type,
+--	abstime(backups.date) as backup_date,
+--	backups.size as size,
+--	backups.inc_size as gzip_size,
+--	archive.id as archive_id,
+--	archive.dvd_nr
+--from backups
+--join shares on backups.shareid=shares.id
+--join hosts on shares.hostid = hosts.id
+--left outer join archive_backup_parts on backups.id = archive_backup_parts.backup_id
+--left outer join archive on archive_backup_parts.archive_id = archive.id
+--where backups.parts > 0 and size > 0
+--order by backups.date
+--;
+
+
+-- used by BackupPC_ASA_BurnArchiveMedia
+CREATE VIEW archive_backup_parts AS
+SELECT
+	backup_parts.backup_id,
+	archive_id,
+	dvd_nr,
+	backup_part_id,
+	hosts.name as host,
+	shares.name as share,
+	backups.num as num,
+	backups.date as date,
+	backup_parts.part_nr as part_nr,
+	backups.parts as parts,
+	backup_parts.size as size,
+	backup_parts.md5 as md5,
+	backup_parts.items,
+	backup_parts.filename
+FROM backup_parts
+JOIN archive_parts ON backup_parts.id = backup_part_id
+JOIN archive       ON archive_id = archive.id
+JOIN backups       ON backup_id = backups.id
+JOIN hosts         ON hostid = hosts.id
+JOIN shares        ON shareid = shares.id
+;
+
+
+CREATE VIEW backups_burned AS
+SELECT backup_parts.backup_id,
+count(backup_parts.backup_id) as backup_parts,
+count(archive_burned.archive_id) AS burned_parts,
+count(backup_parts.backup_id) = count(archive_burned.archive_id) as burned
+   FROM backup_parts 
+   left outer JOIN archive_parts ON backup_part_id = backup_parts.id
+   left join archive on archive.id = archive_id
+   left outer join archive_burned on archive_burned.archive_id = archive.id
+  GROUP BY backup_parts.backup_id ;
+
+
+-- triggers for backup_parts consistency
+create or replace function backup_parts_check() returns trigger as '
+declare
+	b_parts	integer;
+	b_counted integer;
+	b_id	integer;
+begin
+	-- raise notice ''old/new parts %/% backup_id %/%'', old.parts, new.parts, old.id, new.id;
+	if (TG_OP=''UPDATE'') then
+		b_id := new.id;
+		b_parts := new.parts;
+	elsif (TG_OP = ''INSERT'') then
+		b_id := new.id;
+		b_parts := new.parts;
+	end if;
+	b_counted := (select count(*) from backup_parts where backup_id = b_id);
+	-- raise notice ''backup % parts %'', b_id, b_parts;
+	if ( b_parts != b_counted ) then
+		raise exception ''Update of backup % aborted, requested % parts and there are really % parts'', b_id, b_parts, b_counted;
+	end if;
+	return null;
+end;
+' language plpgsql;
+
+create trigger do_backup_parts_check
+	after insert or update or delete on backups
+	for each row execute procedure backup_parts_check();
+
+create or replace function backup_backup_parts_check() returns trigger as '
+declare
+	b_id		integer;
+	my_part_nr	integer;
+	calc_part	integer;
+begin
+	if (TG_OP = ''INSERT'') then
+		-- raise notice ''trigger: % backup_id %'', TG_OP, new.backup_id;
+		b_id = new.backup_id;
+		my_part_nr = new.part_nr;
+		execute ''update backups set parts = parts + 1 where id = '' || b_id;
+	elsif (TG_OP = ''DELETE'') then
+		-- raise notice ''trigger: % backup_id %'', TG_OP, old.backup_id;
+		b_id = old.backup_id;
+		my_part_nr = old.part_nr;
+		execute ''update backups set parts = parts - 1 where id = '' || b_id;
+	end if;
+	calc_part := (select count(part_nr) from backup_parts where backup_id = b_id);
+	if ( my_part_nr != calc_part ) then
+		raise exception ''Update of backup_parts with backup_id % aborted, requested part_nr is % and calulated next is %'', b_id, my_part_nr, calc_part;
+	end if;
+	return null;
+end;
+' language plpgsql;
+
+create trigger do_backup_backup_parts_check
+	after insert or update or delete on backup_parts
+	for each row execute procedure backup_backup_parts_check();
+