#!/bin/sh
#
# shell script to create Bacula PostgreSQL tables
#
bindir=@SQL_BINDIR@

if $bindir/psql -f - -d bacula $* <<END-OF-DATA

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 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 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 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 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,
    copy	      integer	  not null default 0,
    stripe	      integer	  not null default 0,
    primary key (jobmediaid)
);

CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);

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,
    labeltype	      integer	  not null default 0,
    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,
    volparts	      integer	  not null default 0,
    volerrors	      integer	  not null default 0,
    volwrites	      integer	  not null default 0,
    volcapacitybytes  bigint	  not null default 0,
    volstatus	      text	  not null
	check (volstatus in ('Full','Archive','Append',
	      'Recycle','Purged','Read-Only','Disabled',
	      'Error','Busy','Used','Cleaning','Scratch')),
    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,
    StorageId	      integer		   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 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
(
    poolid	      serial	  not null,
    name	      text	  not null,
    numvols	      integer	  not null default 0,
    maxvols	      integer	  not null default 0,
    useonce	      smallint	  not null default 0,
    usecatalog	      smallint	  not null default 0,
    acceptanyvolume   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,
    autoprune	      smallint	  not null default 0,
    recycle	      smallint	  not null default 0,
    pooltype	      text			    
      check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
    labeltype	      integer	  not null default 0,
    labelformat       text	  not null,
    enabled	      smallint	  not null 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 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 counters
(
    counter	      text	  not null,
    minvalue	      integer,
    maxvalue	      integer,
    currentvalue      integer,
    wrapcounter       text	  not null,
    primary key (counter)
);



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)
);


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 (9);

-- 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