X-Git-Url: http://git.rot13.org/?p=BackupPC.git;a=blobdiff_plain;f=bin%2FBackupPC_ASA_SearchUpdate;h=d3f97549d7ed17d82066bb3466af1a8922c31f17;hp=279b8b0b1fb17cec684928d12a495a9836227bc3;hb=57307aaa251ad94e09d3bf3bf6663d58aa9d7ae7;hpb=7cc35df2d8753d1eddd17d3f377fffd6ef316c1d

diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate
index 279b8b0..d3f9754 100755
--- a/bin/BackupPC_ASA_SearchUpdate
+++ b/bin/BackupPC_ASA_SearchUpdate
@@ -224,7 +224,7 @@ if ($opt->create) {
 
 	print "creating tables...\n";
 
-	$dbh->do( qq{
+	foreach my $sql ( split(/;/, qq{
 		create table hosts (
 			ID	SERIAL		PRIMARY KEY,
 			name	VARCHAR(30)	NOT NULL,
@@ -259,6 +259,19 @@ if ($opt->create) {
 			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),
@@ -281,10 +294,10 @@ if ($opt->create) {
 			primary key(id)
 		);	
 
-		create table archive_backup (
+		create table archive_parts (
 			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)
+			backup_part_id	int not null references backup_parts(id),
+			primary key(archive_id, backup_part_id)
 		);
 
 		create table archive_burned (
@@ -295,40 +308,31 @@ if ($opt->create) {
 			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
-		;
-	});
+--
+--		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
+--		;
+	})) {
+		warn "SQL: $sql\n";
+		$dbh->do( $sql );
+	}
 
 	print "creating indexes: ";