#
bindir=@SQL_BINDIR@
-if $bindir/psql $* -f - <<END-OF-DATA
-\c bacula
+$bindir/psql -f - -d bacula $* <<END-OF-DATA
-create table version
+CREATE TABLE filename
(
- versionid integer not null
+ filenameid serial not null,
+ name text not null,
+ primary key (filenameid)
);
-INSERT INTO Version (VersionId) VALUES (7);
+CREATE INDEX filename_name_idx on filename (name);
-create table counters
+CREATE TABLE path
(
- counter text not null,
- minvalue integer ,
- maxvalue integer ,
- currentvalue integer ,
- wrapcounter text not null,
- primary key (counter)
+ pathid serial not null,
+ path text not null,
+ primary key (pathid)
);
-create table filename
+CREATE INDEX path_name_idx on path (path);
+
+CREATE TABLE file
(
- filenameid serial not null,
- name text not null,
- primary key (filenameid)
+ 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 filename_name_idx on filename (name);
+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 path
+CREATE TABLE job
(
- pathid serial not null,
- path text not null,
- primary key (pathid)
+ jobid serial not null,
+ job text not null,
+ name text not null,
+ type char(1) not null,
+ level char(1) not null,
+ clientid integer default 0,
+ jobstatus char(1) not null,
+ schedtime timestamp without time zone,
+ starttime timestamp without time zone,
+ endtime timestamp without time zone,
+ realendtime timestamp without time zone,
+ jobtdate bigint default 0,
+ volsessionid integer default 0,
+ volsessiontime integer default 0,
+ jobfiles integer default 0,
+ jobbytes bigint default 0,
+ joberrors integer default 0,
+ jobmissingfiles integer default 0,
+ poolid integer default 0,
+ filesetid integer default 0,
+ purgedfiles smallint default 0,
+ hasbase smallint default 0,
+ priorjobid integer default 0,
+ primary key (jobid)
);
-create index path_name_idx on path (path);
+CREATE INDEX job_name_idx on job (name);
-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 TABLE Location (
+ LocationId serial not null,
+ Location text not null,
+ Cost integer default 0,
+ Enabled smallint,
+ primary key (LocationId)
);
-create index fileset_name_idx on fileset (fileset);
-create table pool
+CREATE TABLE fileset
(
- 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)
+ filesetid serial not null,
+ fileset text not null,
+ md5 text not null,
+ createtime timestamp without time zone not null,
+ primary key (filesetid)
);
-create index pool_name_idx on pool (name);
+CREATE INDEX fileset_name_idx on fileset (fileset);
-create table client
+CREATE TABLE jobmedia
(
- 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)
+ jobmediaid serial not null,
+ jobid integer not null,
+ mediaid integer not null,
+ firstindex integer default 0,
+ lastindex integer default 0,
+ startfile integer default 0,
+ endfile integer default 0,
+ startblock bigint default 0,
+ endblock bigint default 0,
+ volindex integer default 0,
+ copy integer default 0,
+ primary key (jobmediaid)
);
-create unique index client_name_idx on client (name);
+CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
-create table media
+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 not null
- default current_timestamp,
- lastwritten timestamp without time zone not null
- default current_timestamp,
- labeldate timestamp without time zone not null
- default current_timestamp,
- 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,
- drive integer not null
- default 0,
- inchanger smallint not null
- default 0,
- mediaaddressing smallint not null
- default 0,
+ mediaid serial not null,
+ volumename text not null,
+ slot integer default 0,
+ poolid integer default 0,
+ mediatype text not null,
+ mediatypeid integer default 0,
+ labeltype integer default 0,
+ firstwritten timestamp without time zone,
+ lastwritten timestamp without time zone,
+ labeldate timestamp without time zone,
+ voljobs integer default 0,
+ volfiles integer default 0,
+ volblocks integer default 0,
+ volmounts integer default 0,
+ volbytes bigint default 0,
+ volparts integer default 0,
+ volerrors integer default 0,
+ volwrites integer default 0,
+ volcapacitybytes bigint default 0,
+ volstatus text not null
+ check (volstatus in ('Full','Archive','Append',
+ 'Recycle','Purged','Read-Only','Disabled',
+ 'Error','Busy','Used','Cleaning','Scratch')),
+ enabled smallint default 1,
+ recycle smallint default 0,
+ volretention bigint default 0,
+ voluseduration bigint default 0,
+ maxvoljobs integer default 0,
+ maxvolfiles integer default 0,
+ maxvolbytes bigint default 0,
+ inchanger smallint default 0,
+ StorageId integer default 0,
+ DeviceId integer default 0,
+ mediaaddressing smallint default 0,
+ volreadtime bigint default 0,
+ volwritetime bigint default 0,
+ endfile integer default 0,
+ endblock bigint default 0,
+ LocationId integer default 0,
+ recyclecount integer default 0,
+ initialwrite timestamp without time zone,
+ scratchpoolid integer default 0,
+ recyclepoolid integer default 0,
+ comment text,
primary key (mediaid)
);
create unique index media_volumename_id on media (volumename);
-create table job
+
+CREATE TABLE MediaType (
+ MediaTypeId SERIAL,
+ MediaType TEXT NOT NULL,
+ ReadOnly INTEGER DEFAULT 0,
+ PRIMARY KEY(MediaTypeId)
+ );
+
+CREATE TABLE Storage (
+ StorageId SERIAL,
+ Name TEXT NOT NULL,
+ AutoChanger INTEGER DEFAULT 0,
+ PRIMARY KEY(StorageId)
+ );
+
+CREATE TABLE Device (
+ DeviceId SERIAL,
+ Name TEXT NOT NULL,
+ MediaTypeId INTEGER NOT NULL,
+ StorageId INTEGER NOT NULL,
+ DevMounts INTEGER NOT NULL DEFAULT 0,
+ DevReadBytes BIGINT NOT NULL DEFAULT 0,
+ DevWriteBytes BIGINT NOT NULL DEFAULT 0,
+ DevReadBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
+ DevWriteBytesSinceCleaning BIGINT NOT NULL DEFAULT 0,
+ DevReadTime BIGINT NOT NULL DEFAULT 0,
+ DevWriteTime BIGINT NOT NULL DEFAULT 0,
+ DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
+ DevWriteTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
+ CleaningDate timestamp without time zone,
+ CleaningPeriod BIGINT NOT NULL DEFAULT 0,
+ PRIMARY KEY(DeviceId)
+ );
+
+
+CREATE TABLE pool
(
- 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)
+ poolid serial not null,
+ name text not null,
+ numvols integer default 0,
+ maxvols integer default 0,
+ useonce smallint default 0,
+ usecatalog smallint default 0,
+ acceptanyvolume smallint default 0,
+ volretention bigint default 0,
+ voluseduration bigint default 0,
+ maxvoljobs integer default 0,
+ maxvolfiles integer default 0,
+ maxvolbytes bigint default 0,
+ autoprune smallint default 0,
+ recycle smallint default 0,
+ pooltype text
+ check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
+ labeltype integer default 0,
+ labelformat text not null,
+ enabled smallint default 1,
+ scratchpoolid integer default 0,
+ recyclepoolid integer default 0,
+ NextPoolId integer default 0,
+ MigrationHighBytes BIGINT DEFAULT 0,
+ MigrationLowBytes BIGINT DEFAULT 0,
+ MigrationTime BIGINT DEFAULT 0,
+ primary key (poolid)
);
-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 bigint not null,
- markid integer not null
- default 0,
- lstat text not null,
- md5 text not null,
- primary key (fileid)
-);
+CREATE INDEX pool_name_idx on pool (name);
-create table jobmedia
+CREATE TABLE client
(
- 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 integer not null
- default 0,
- endblock integer not null
- default 0,
- volindex integer not null
- default 0,
- primary key (jobmediaid)
+ clientid serial not null,
+ name text not null,
+ uname text not null,
+ autoprune smallint default 0,
+ fileretention bigint default 0,
+ jobretention bigint default 0,
+ primary key (clientid)
);
-create index job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
+create unique index client_name_idx on client (name);
-create table basefiles
+CREATE TABLE Log
(
- baseid serial not null,
- jobid integer not null,
- fileid integer not null,
- fileindex integer ,
- basejobid integer ,
- primary key (baseid)
+ LogId serial not null,
+ JobId integer not null,
+ Time timestamp without time zone,
+ LogText text not null,
+ primary key (LogId)
);
-
-create table unsavedfiles
-(
- UnsavedId integer not null,
- jobid integer not null,
- pathid integer not null,
- filenameid integer not null,
- primary key (UnsavedId)
+create index log_name_idx on Log (JobId);
+
+CREATE TABLE LocationLog (
+ LocLogId SERIAL NOT NULL,
+ Date timestamp without time zone,
+ Comment TEXT NOT NULL,
+ MediaId INTEGER DEFAULT 0,
+ LocationId INTEGER DEFAULT 0,
+ newvolstatus text not null
+ check (newvolstatus in ('Full','Archive','Append',
+ 'Recycle','Purged','Read-Only','Disabled',
+ 'Error','Busy','Used','Cleaning','Scratch')),
+ newenabled smallint,
+ PRIMARY KEY(LocLogId)
);
-alter table pool
- add foreign key (scratchpoolid)
- references pool (poolid) on update cascade on delete cascade;
-
-alter table pool
- add foreign key (recyclepoolid)
- references pool (poolid) on update cascade on delete cascade;
-
-alter table media
- add foreign key (poolid)
- references pool (poolid) on update cascade on delete cascade;
-alter table job
- add foreign key (poolid)
- references pool (poolid) on update cascade on delete cascade;
-alter table job
- add foreign key (filesetid)
- references fileset (filesetid) on update cascade on delete cascade;
-
-alter table job
- add foreign key (clientid)
- references client (clientid) on update cascade on delete cascade;
-
-alter table file
- add foreign key (jobid)
- references job (jobid) on update cascade on delete cascade;
-
-alter table file
- add foreign key (pathid)
- references path (pathid) on update cascade on delete restrict;
-
-alter table file
- add foreign key (filenameid)
- references filename (filenameid) on update cascade on delete cascade;
-
-alter table jobmedia
- add foreign key (jobid)
- references job (jobid) on update cascade on delete cascade;
+CREATE TABLE counters
+(
+ counter text not null,
+ minvalue integer default 0,
+ maxvalue integer default 0,
+ currentvalue integer default 0,
+ wrapcounter text not null,
+ primary key (counter)
+);
-alter table jobmedia
- add foreign key (mediaid)
- references media (mediaid) on update cascade on delete cascade;
-alter table basefiles
- add foreign key (jobid)
- references job (jobid) on update cascade on delete cascade;
-alter table basefiles
- add foreign key (fileid)
- references file (fileid) on update cascade on delete cascade;
+CREATE TABLE basefiles
+(
+ baseid serial not null,
+ jobid integer not null,
+ fileid integer not null,
+ fileindex integer ,
+ basejobid integer ,
+ primary key (baseid)
+);
-alter table basefiles
- add foreign key (basejobid)
- references job (jobid) on update cascade on delete cascade;
+CREATE TABLE unsavedfiles
+(
+ UnsavedId integer not null,
+ jobid integer not null,
+ pathid integer not null,
+ filenameid integer not null,
+ primary key (UnsavedId)
+);
-alter table unsavedfiles
- add foreign key (jobid)
- references job (jobid) on update restrict on delete restrict;
+CREATE TABLE CDImages
+(
+ MediaId integer not null,
+ LastBurn timestamp without time zone not null,
+ primary key (MediaId)
+);
-alter table unsavedfiles
- add foreign key (pathid)
- references path (pathid) on update restrict on delete restrict;
-alter table unsavedfiles
- add foreign key (filenameid)
- references filename (filenameid) on update restrict on delete restrict;
+CREATE TABLE version
+(
+ versionid integer not null
+);
+CREATE TABLE Status (
+ JobStatus CHAR(1) NOT NULL,
+ JobStatusLong TEXT,
+ PRIMARY KEY (JobStatus)
+ );
+
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('C', 'Created, not yet running');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('R', 'Running');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('B', 'Blocked');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('T', 'Completed successfully');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('E', 'Terminated with errors');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('e', 'Non-fatal error');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('f', 'Fatal error');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('D', 'Verify found differences');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('A', 'Canceled by user');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('F', 'Waiting for Client');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('S', 'Waiting for Storage daemon');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('m', 'Waiting for new media');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('M', 'Waiting for media mount');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('s', 'Waiting for storage resource');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('j', 'Waiting for job resource');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('c', 'Waiting for client resource');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('d', 'Waiting on maximum jobs');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('t', 'Waiting on start time');
+INSERT INTO Status (JobStatus,JobStatusLong) VALUES
+ ('p', 'Waiting on higher priority jobs');
+
+
+INSERT INTO Version (VersionId) VALUES (10);
+
+-- Make sure we have appropriate permissions
END-OF-DATA
+pstat=$?
+if test $pstat = 0;
then
echo "Creation of Bacula PostgreSQL tables succeeded."
else
echo "Creation of Bacula PostgreSQL tables failed."
fi
-exit 0
+exit $pstat
projects / bacula / bacula / blobdiff