#!/bin/sh
#
# shell script to create Bacula PostgreSQL tables
#
bindir=/usr/bin
if $bindir/psql $* bacula -f - <<END-OF-DATA
create table version
(
versionid integer not null
);
INSERT INTO Version (VersionId) VALUES (8);
create table counters
(
counter text not null,
minvalue integer ,
maxvalue integer ,
currentvalue integer ,
wrapcounter text not null,
primary key (counter)
);
create table filename
(
filenameid serial not null,
name text not null,
primary key (filenameid)
);
create index filename_name_idx on filename (name);
create table path
(
pathid serial not null,
path text not null,
primary key (pathid)
);
create index path_name_idx on path (path);
create table fileset
(
filesetid serial not null,
fileset text not null,
md5 text not null,
createtime timestamp without time zone not null,
primary key (filesetid)
);
create index fileset_name_idx on fileset (fileset);
create table pool
(
poolid serial not null,
name text not null,
numvols integer not null
default 0,
maxvols integer not null
default 0,
useonce smallint not null,
usecatalog smallint not null,
acceptanyvolume smallint
default 0,
volretention bigint not null,
voluseduration bigint not null,
maxvoljobs integer not null
default 0,
maxvolfiles integer not null
default 0,
maxvolbytes bigint not null,
autoprune smallint not null
default 0,
recycle smallint
default 0,
pooltype text
check (pooltype is null or (pooltype in ('Backup','Copy','Cloned','Archive','Migration'))),
labelformat text not null,
enabled smallint not null
default 1,
scratchpoolid integer ,
recyclepoolid integer ,
primary key (poolid)
);
create index pool_name_idx on pool (name);
create table client
(
clientid serial not null,
name text not null,
uname text not null,
autoprune smallint
default 0,
fileretention bigint not null,
jobretention bigint not null,
primary key (clientid)
);
create unique index client_name_idx on client (name);
create table media
(
mediaid serial not null,
volumename text not null,
slot integer not null
default 0,
poolid integer not null,
mediatype text not null,
firstwritten timestamp without time zone,
lastwritten timestamp without time zone,
labeldate timestamp without time zone,
voljobs integer not null
default 0,
volfiles integer not null
default 0,
volblocks integer not null
default 0,
volmounts integer not null
default 0,
volbytes bigint not null
default 0,
volerrors integer not null
default 0,
volwrites integer not null
default 0,
volcapacitybytes bigint not null,
volstatus text not null
check (volstatus in ('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy',
'Used','Cleaning')),
recycle smallint not null
default 0,
volretention bigint not null
default 0,
voluseduration bigint not null
default 0,
maxvoljobs integer not null
default 0,
maxvolfiles integer not null
default 0,
maxvolbytes bigint not null
default 0,
inchanger smallint not null
default 0,
mediaaddressing smallint not null
default 0,
volreadtime bigint not null
default 0,
volwritetime bigint not null
default 0,
endfile integer not null
default 0,
endblock bigint not null
default 0,
primary key (mediaid)
);
create unique index media_volumename_id on media (volumename);
create table job
(
jobid serial not null,
job text not null,
name text not null,
type char(1) not null,
level char(1) not null,
clientid integer ,
jobstatus char(1) not null,
schedtime timestamp without time zone not null,
starttime timestamp without time zone ,
endtime timestamp without time zone ,
jobtdate bigint not null,
volsessionid integer not null
default 0,
volsessiontime integer not null
default 0,
jobfiles integer not null
default 0,
jobbytes bigint not null
default 0,
joberrors integer not null
default 0,
jobmissingfiles integer not null
default 0,
poolid integer ,
filesetid integer ,
purgedfiles smallint not null
default 0,
hasbase smallint not null
default 0,
primary key (jobid)
);
create index job_name_idx on job (name);
create table file
(
fileid serial not null,
fileindex integer not null
default 0,
jobid integer not null,
pathid integer not null,
filenameid integer not null,
markid integer not null
default 0,
lstat text not null,
md5 text not null,
primary key (fileid)
);
create index file_jobid_idx on file (jobid);
create index file_fp_idx on file (filenameid, pathid);
--
-- Possibly add one or more of the following indexes
-- if your Verifies are too slow.
--
-- create index file_pathid_idx on file(pathid);
-- create index file_filenameid_idx on file(filenameid);
-- create index file_jpfid_idx on file (jobid, pathid, filenameid);
create table jobmedia
(
jobmediaid serial not null,
jobid integer not null,
mediaid integer not null,
firstindex integer not null
default 0,
lastindex integer not null
default 0,
startfile integer not null
default 0,
endfile integer not null
default 0,
startblock bigint not null
default 0,
endblock bigint not null
default 0,
volindex integer not null
default 0,
primary key (jobmediaid)
);
create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
create table basefiles
(
baseid serial not null,
jobid integer not null,
fileid integer not null,
fileindex integer ,
basejobid integer ,
primary key (baseid)
);
create table unsavedfiles
(
UnsavedId integer not null,
jobid integer not null,
pathid integer not null,
filenameid integer not null,
primary key (UnsavedId)
);
create table CDImages
(
MediaId integer not null,
LastBurn timestamp without time zone not null,
primary key (MediaId)
);
-- Make sure we have appropriate permissions
END-OF-DATA
then
echo "Creation of Bacula PostgreSQL tables succeeded."
else
echo "Creation of Bacula PostgreSQL tables failed."
fi
exit 0