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

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index 034642f..929e848 100755
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -224,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: ";
 
@@ -351,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;
@@ -598,8 +425,10 @@ foreach my $host_key (@hosts) {
 
 			if ($nf + $nd > 0) {
 				status "$hostname $backupNum full-text | indexing";
-				eval { hest_update($hostID, $shareID, $backupNum) };
-				warn "ERROR: $@" if $@;
+				#eval { hest_update($hostID, $shareID, $backupNum) };
+				#warn "ERROR: $@" if $@;
+				hest_update($hostID, $shareID, $backupNum);
+				# eval breaks our re-try logic
 			}
 		}
 
@@ -778,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();
+