+__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
+;
+
+
+CREATE VIEW backups_burned AS
+SELECT backup_parts.backup_id,
+count(backup_parts.backup_id) as backup_parts,
+count(archive_burned.archive_id) AS burned_parts,
+count(backup_parts.backup_id) = count(archive_burned.archive_id) as burned
+ FROM backup_parts
+ left outer JOIN archive_parts ON backup_part_id = backup_parts.id
+ left join archive on archive.id = archive_id
+ left outer join archive_burned on archive_burned.archive_id = archive.id
+ GROUP BY backup_parts.backup_id ;
+
+
+-- triggers for backup_parts consistency
+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();
+