--- trunk/bin/BackupPC_updatedb	2005/09/23 15:04:37	136
+++ trunk/bin/BackupPC_updatedb	2005/10/12 11:15:19	183
@@ -270,180 +270,112 @@
 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	bigint		not null,
-			PRIMARY KEY(hostID, num, shareID) 
+			inc_size bigint		not null default -1,
+			inc_deleted boolean	default false,
+			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)
+		create table archive_burned (
+			archive_id int references archive(id),
+			date date default now(),
+			iso_size int default -1
 		);
-	});
 
-	$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)
-		);
-	});
-
-	$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)
-		);
 	});
 
-	$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
 	)) {
-		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 +536,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);
 }