From b34c67f0bd159364d2fada4930d53987e78047fc Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Fri, 28 Jan 2011 16:32:24 +0100 Subject: [PATCH] begin rewrite to select backup_parts --- bin/BackupPC_ASA_SearchUpdate | 80 ++++++++++--------- lib/BackupPC/Search.pm | 146 ++++++++-------------------------- 2 files changed, 75 insertions(+), 151 deletions(-) diff --git a/bin/BackupPC_ASA_SearchUpdate b/bin/BackupPC_ASA_SearchUpdate index e691bd8..b921594 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,11 @@ 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_id int not null references backups(id), + backup_part_id int not null references backup_parts(id), + primary key(archive_id, backup_id, backup_part_id) ); create table archive_burned ( @@ -295,41 +309,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(), - filename text not null, - 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: "; diff --git a/lib/BackupPC/Search.pm b/lib/BackupPC/Search.pm index f2dac44..77a68d3 100644 --- a/lib/BackupPC/Search.pm +++ b/lib/BackupPC/Search.pm @@ -374,35 +374,6 @@ sub getGzipSize($$) ); } -sub getVolumes($) { - my $id = shift; - - my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize"; - - my $sth = $dbh->prepare(qq{ - select - size - from backup_parts - where backup_id = ? - order by part_nr asc - }); - - $sth->execute($id); - - my $cumulative_size = 0; - my $volumes = 1; - - while(my ($size) = $sth->fetchrow_array) { - if ($cumulative_size + $size > $max_archive_size) { - $volumes++; - $cumulative_size = $size; - } else { - $cumulative_size += $size; - } - } - - return ($volumes,$cumulative_size); -} sub getBackupsNotBurned($) { @@ -414,36 +385,19 @@ sub getBackupsNotBurned($) { print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql order: $order\n"; my $sql = qq{ - SELECT - backups.hostID AS hostID, - hosts.name AS host, - shares.name AS share, - backups.num AS backupnum, - backups.type AS type, - backups.date AS date, - date_part('epoch',now()) - backups.date as age, - backups.size AS size, - backups.id AS id, - backups.inc_size AS inc_size, - backups.parts AS parts - FROM backups - INNER JOIN shares ON backups.shareID=shares.ID - INNER JOIN hosts ON backups.hostID = hosts.ID - LEFT OUTER JOIN archive_backup ON archive_backup.backup_id = backups.id - WHERE backups.inc_size > 0 AND backups.size > 0 AND backups.inc_deleted is false AND archive_backup.backup_id IS NULL AND backups.parts > 0 - GROUP BY - backups.hostID, - hosts.name, - shares.name, - backups.num, - backups.shareid, - backups.id, - backups.type, - backups.date, - backups.size, - backups.inc_size, - backups.parts - ORDER BY $order + SELECT + p.id, + p.filename, + b.date, + date_part('epoch',now()) - b.date as age, + p.size, + count(ab.date) as copies + FROM backup_parts p + JOIN backups b ON b.id = p.backup_id + LEFT OUTER JOIN archive_parts ap ON ap.backup_part_id = p.id + LEFT OUTER JOIN archive_burned ab ON ab.archive_id = ap.archive_id + GROUP BY p.id,filename,b.date,age,p.size,p.part_nr + ORDER BY b.date,p.part_nr }; my $sth = $dbh->prepare( $sql ); my @ret; @@ -452,15 +406,6 @@ print STDERR "## sort=". ($param->{'sort'} || 'no sort param') . " burn sql orde while ( my $row = $sth->fetchrow_hashref() ) { $row->{'age'} = sprintf("%0.1f", ( $row->{'age'} / 86400 ) ); #$row->{'age'} = sprintf("%0.1f", ( (time() - $row->{'date'}) / 86400 ) ); - - my $max_archive_size = $Conf{ArchiveMediaSize} || die "no ArchiveMediaSize"; - if ($row->{size} > $max_archive_size) { - ($row->{volumes}, $row->{inc_size_calc}) = getVolumes($row->{id}); - } - - # do some cluster calculation (approximate) - $row->{inc_size} = int(( ($row->{inc_size} + 1023 ) / 2 ) * 2); - $row->{inc_size_calc} ||= $row->{inc_size}; push @ret, $row; } @@ -680,8 +625,8 @@ function update_size(name, checked, suma) { suma -= size; } - var volumes = parseInt( element_id("prt" + name).value); - debug('update_size('+name+','+checked+') suma: '+suma+' volumes: '+volumes); + debug('update_size('+name+','+checked+') suma: '+suma); +/* FIXME if (volumes > 1) { if (checked) { element_id("volumes").innerHTML = "This will take "+volumes+" mediums!"; @@ -693,7 +638,7 @@ function update_size(name, checked, suma) { element_id("volumes").style.display = 'none'; } } - +*/ return suma; } @@ -823,59 +768,34 @@ EOF3 } . - sort_header($param, 'Share', 'share', 'center') . - sort_header($param, '#', 'num', 'center') . - qq{ - Type - } . + sort_header($param, 'Filename', 'filename', 'left') . sort_header($param, 'Date', 'date', 'center') . sort_header($param, 'Age/days', 'age', 'center') . sort_header($param, 'Size', 'size', 'center') . - sort_header($param, 'gzip size', 'incsize', 'center') . qq{ - medias + copies }; my @color = (' bgcolor="#e0e0e0"', ''); - my $i = 0; - my $host = ''; + my $i = 1; +# my $img_url = $Conf{CgiImageDirURL}; foreach my $backup ( getBackupsNotBurned($param) ) { - if ($host ne $backup->{'host'}) { - $i++; - $host = $backup->{'host'}; - } - my $ftype = ""; - - my $checkbox_key = $backup->{'hostid'}. '_' .$backup->{'backupnum'} . '_' . $backup->{'id'}; - - $retHTML .= - ' - '; - - if (($backup->{'inc_size'} || 0) > 0) { - $retHTML .= ' - '; - } - - my $img_url = $Conf{CgiImageDirURL}; - - $retHTML .= - '' . - '' . $backup->{'host'} . ':' . $backup->{'share'} . '' . - '' . $backup->{'backupnum'} . '' . - '' . $backup->{'type'} . '' . - '' . epoch_to_iso( $backup->{'date'} ) . '' . - '' . $backup->{'age'} . '' . - '' . unit($backup->{'size'}) . '' . - '' . unit($backup->{'inc_size'}) . '' . - '' . - '' . - '' . ( qq{media} x $backup->{volumes} ) . '' . - - "\n"; + $retHTML .= join('' + ,'' + ,'' + ,'' + ,'' + ,'' + ,'', $backup->{'filename'}, '' + ,'', epoch_to_iso( $backup->{'date'} ), '' + ,'', $backup->{'age'}, '' + ,'', unit($backup->{'size'}), '' + ,'', '*' x $backup->{copies}, '' + ,"\n" + ); } $retHTML .= ""; -- 2.20.1