X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=blobdiff_plain;f=bin%2FBackupPC_ASA_SearchUpdate;h=929e848dc85e76543c7f8bba20727ae77e37ecb7;hp=5cff61f6f0ebeec4b76ad5d8da307d33efa6c8f8;hb=06017d5181d9c62f8cbd1c6d58d831f54d7d4ad1;hpb=a46edaf622b0e54732428bb1302a03af22b682c7

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index 5cff61f..929e848 100755
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -7,7 +7,7 @@ use DBI;
 use BackupPC::Lib;
 use BackupPC::View;
 use Data::Dumper;
-use Getopt::Std;
+use Getopt::Long::Descriptive;
 use Time::HiRes qw/time/;
 use File::Pid;
 use POSIX qw/strftime/;
@@ -57,42 +57,40 @@ my $index_node_url = $Conf{HyperEstraierIndex};
 
 my $dbh = DBI->connect($dsn, $user, "", { RaiseError => 1, AutoCommit => 0 });
 
-my %opt;
+my ($opt,$usage) = describe_options(
+"%c %o",
+[ 'create|c',	"create database on first use" ],
+[ 'delete|d',	"delete database before import" ],
+[ 'max|m=i',	"import just max increments for one host" ],
+[ 'host|h=s@',	"import just host(s)" ],
+[],
+[ 'verbose|v:i', 'set verbosity (debug) level' ],
+[ 'index|i',	'update full text index' ],
+[ 'junk|j',	"update full text, don't check existing files" ],
+[ 'fast|f',	"don't do anything with full text index" ],
+[ 'quiet|q',	"be quiet for hosts without changes" ],
+[ 'help',	"show help" ],
+);
+
+print($usage->text), exit if $opt->help;
+
+warn "hosts: ",dump( $opt->host );
 
-if ( !getopts("cdm:v:ijfq", \%opt ) ) {
-	print STDERR <<EOF;
-usage: $0 [-c|-d] [-m num] [-v|-v level] [-i|-j|-f]
-
-Options:
-	-c	create database on first use
-	-d	delete database before import
-	-m num	import just num	increments for one host
-	-v num	set verbosity (debug) level (default $debug)
-	-i	update Hyper Estraier full text index
-	-j	update full text, don't check existing files
-	-f	don't do anything with full text index
-	-q	be quiet for hosts without changes
-
-Option -j is variation on -i. It will allow faster initial creation
-of full-text index from existing database.
-
-Option -f will create database which is out of sync with full text index. You
-will have to re-run $0 with -i to fix it.
-
-EOF
-	exit 1;
-}
+#---- subs ----
 
-if ($opt{v}) {
-	print "Debug level at $opt{v}\n";
-	$debug = $opt{v};
-} elsif ($opt{f}) {
-	print "WARNING: disabling full-text index update. You need to re-run $0 -j !\n";
-	$index_node_url = undef;
+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;
 }
 
-#---- subs ----
-
 sub fmt_time {
 	my $t = shift || return;
 	my $out = "";
@@ -110,7 +108,7 @@ sub hest_update {
 
 	my ($host_id, $share_id, $num) = @_;
 
-	my $skip_check = $opt{j} && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";
+	my $skip_check = $opt->junk && print STDERR "Skipping check for existing files -- this should be used only with initital import\n";
 
 	print curr_time," updating fulltext:";
 
@@ -185,6 +183,7 @@ sub hest_update {
 		}
 
 		print "$added";
+		status "| $added";
 
 		$offset += EST_CHUNK;
 
@@ -203,17 +202,17 @@ sub hest_update {
 
 
 ## update index ##
-if ( ( $opt{i} || $opt{j} ) && !$opt{c} ) {
+if ( ( $opt->index || $opt->junk ) && !$opt->create ) {
 	# update all
 	print "force update of Hyper Estraier index ";
-	print "by -i flag" if ($opt{i});
-	print "by -j flag" if ($opt{j});
+	print "by -i flag" if ($opt->index);
+	print "by -j flag" if ($opt->junk);
 	print "\n";
 	hest_update();
 }
 
 ## create tables ##
-if ($opt{c}) {
+if ($opt->create) {
 	sub do_index {
 		my $index = shift || return;
 		my ($table,$col,$unique) = split(/:/, $index);
@@ -225,111 +224,9 @@ if ($opt{c}) {
 
 	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: ";
 
@@ -352,73 +249,6 @@ if ($opt{c}) {
 		do_index($index);
 	}
 
-	print " creating sequence: ";
-	foreach my $seq (qw/dvd_nr/) {
-		print "$seq ";
-		$dbh->do( qq{ CREATE SEQUENCE $seq } );
-	}
-
-	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__
-
 	print "...\n";
 
 	$dbh->commit;
@@ -426,7 +256,7 @@ __END_OF_TRIGGER__
 }
 
 ## delete data before inseting ##
-if ($opt{d}) {
+if ($opt->delete) {
 	print "deleting ";
 	foreach my $table (qw(files dvds backups shares hosts)) {
 		print "$table ";
@@ -451,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{
@@ -483,7 +313,9 @@ foreach my $host_key (@hosts) {
 
 	my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 
-	$sth->{hosts_by_name}->execute($hosts->{$host_key}->{'host'});
+	next if $opt->host && ! grep { m/^$hostname$/ } @{ $opt->host };
+
+	$sth->{hosts_by_name}->execute($hostname);
 
 	unless (($hostID) = $sth->{hosts_by_name}->fetchrow_array()) {
 		$sth->{insert_hosts}->execute(
@@ -505,7 +337,7 @@ foreach my $host_key (@hosts) {
 		($#hosts + 1),
 		$incs
 	);
-	print $host_header unless ($opt{q});
+	print $host_header unless $opt->quiet;
  
 	my $inc_nr = 0;
 	$beenThere = {};
@@ -513,7 +345,7 @@ foreach my $host_key (@hosts) {
 	foreach my $backup (@backups) {
 
 		$inc_nr++;
-		last if (defined $opt{m} && $inc_nr > $opt{m});
+		last if defined $opt->max && $inc_nr > $opt->max;
 
 		my $backupNum = $backup->{'num'};
 		my @backupShares = ();
@@ -526,9 +358,10 @@ foreach my $host_key (@hosts) {
 			strftime($t_fmt,localtime($backup->{startTime})),
 			fmt_time($backup->{endTime} - $backup->{startTime})
 		);
-		print $share_header unless ($opt{q});
+		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)) {
 
@@ -542,7 +375,7 @@ foreach my $host_key (@hosts) {
 			next if ($count > 0);
 
 			# dump host and share header for -q
-			if ($opt{q}) {
+			if ( $opt->quiet ) {
 				if ($host_header) {
 					print $host_header;
 					$host_header = undef;
@@ -581,16 +414,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
 			}
 		}
 
@@ -769,3 +607,196 @@ 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
+ORDER BY backup_id, part_nr
+;
+
+
+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();
+