#!/bin/sh
#
# shell script to create Bacula Ingres tables
#
bindir=@SQL_BINDIR@
PATH="$bindir:$PATH"
#db_name=${db_name:-@db_name@}
#db_user=${db_user:-@db_user@}
db_name=@db_name@
db_user=@db_user@

sql $* -u${db_user} ${db_name} <<END-OF-DATA

SET AUTOCOMMIT ON\g

CREATE SEQUENCE filename_seq;
CREATE TABLE filename
(
    filenameid	      integer	  not null default filename_seq.nextval,
    name	      varchar(256)	  not null,
    primary key (filenameid)
);

-- ALTER TABLE filename ALTER COLUMN name SET STATISTICS 1000;
CREATE UNIQUE INDEX filename_name_idx on filename (name);

CREATE SEQUENCE path_seq;
CREATE TABLE path
(
    pathid	      integer	  not null default path_seq.nextval,
    path	      varchar(256)	  not null,
    primary key (pathid)
);

-- ALTER TABLE path ALTER COLUMN path SET STATISTICS 1000;
CREATE UNIQUE INDEX path_name_idx on path (path);

CREATE SEQUENCE file_seq;
CREATE TABLE file
(
    fileid	      integer8	  not null default file_seq.nextval,
    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	      varchar(256)	  not null,
    md5 	      varchar(256)	  not null,
    primary key (fileid)
);

CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);

-- If you need performances, you can remove this index
-- the database engine is able to use the composite index
-- to find all records with a given JobId
CREATE INDEX file_jobid_idx on file(jobid);

--
-- Add this if you have a good number of job
-- that run at the same time
-- ALTER SEQUENCE file_fileid_seq CACHE 1000;

--
-- 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 SEQUENCE Job_seq;
CREATE TABLE Job
(
    JobId             integer     not null default Job_seq.nextval,
    Job               varchar(256)        not null,
    Name              varchar(256)        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,
    ReadBytes         bigint      default 0,
    JobErrors         integer     default 0,
    JobMissingFiles   integer     default 0,
    PoolId            integer     default 0,
    FilesetId         integer     default 0,
    PriorJobid        integer     default 0,
    PurgedFiles       smallint    default 0,
    HasBase           smallint    default 0,
    HasCache          smallint    default 0,
    Reviewed          smallint    default 0,
    Comment           varchar(256),
    primary key (jobid)
);

CREATE INDEX job_name_idx on job (name);

-- Create a table like Job for long term statistics 
CREATE SEQUENCE JobHisto_seq;
CREATE TABLE JobHisto
(
    JobId             integer     not null default JobHisto_seq.nextval,
    Job               varchar(256)        not null,
    Name              varchar(256)        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,
    ReadBytes         bigint      default 0,
    JobErrors         integer     default 0,
    JobMissingFiles   integer     default 0,
    PoolId            integer     default 0,
    FilesetId         integer     default 0,
    PriorJobid        integer     default 0,
    PurgedFiles       smallint    default 0,
    HasBase           smallint    default 0,
    HasCache          smallint    default 0,
    Reviewed          smallint    default 0,
    Comment           varchar(256),
    primary key (jobid)
);

CREATE INDEX jobhisto_idx on JobHisto ( StartTime );


CREATE SEQUENCE Location_seq;
CREATE TABLE Location (
   LocationId	      integer	  not null default Location_seq.nextval,
   Location	      varchar(256)	  not null,
   Cost 	      integer	  default 0,
   Enabled	      smallint,
   primary key (LocationId)
);

CREATE SEQUENCE fileset_seq;
CREATE TABLE fileset
(
    filesetid	      integer	  not null default fileset_seq.nextval,
    fileset	      varchar(256)	  not null,
    md5 	      varchar(256)	  not null,
    createtime	      timestamp without time zone not null,
    primary key (filesetid)
);

CREATE INDEX fileset_name_idx on fileset (fileset);

CREATE SEQUENCE jobmedia_seq;
CREATE TABLE jobmedia
(
    jobmediaid	      integer	  not null default jobmedia_seq.nextval,
    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,
    primary key (jobmediaid)
);

CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);

CREATE SEQUENCE media_seq;
CREATE TABLE media
(
    mediaid	      integer	  not null default media_seq.nextval,
    volumename	      varchar(256)	  not null,
    slot	      integer	  default 0,
    poolid	      integer	  default 0,
    mediatype	      varchar(256)	  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	      varchar(256)	  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,
    ActionOnPurge     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	      varchar(256),
    primary key (mediaid)
);

create unique index media_volumename_id on media (volumename);

CREATE SEQUENCE MediaType_seq;
CREATE TABLE MediaType (
   MediaTypeId INTEGER NOT NULL DEFAULT MediaType_seq.nextval,
   MediaType varchar(256) NOT NULL,
   ReadOnly INTEGER NOT NULL DEFAULT 0,
   PRIMARY KEY(MediaTypeId)
   );

CREATE SEQUENCE Storage_seq;
CREATE TABLE Storage (
   StorageId INTEGER NOT NULL DEFAULT Storage_seq.nextval,
   Name varchar(256) NOT NULL,
   AutoChanger INTEGER NOT NULL DEFAULT 0,
   PRIMARY KEY(StorageId)
   );

CREATE SEQUENCE Device_seq;
CREATE TABLE Device (
   DeviceId INTEGER NOT NULL DEFAULT Device_seq.nextval,
   Name varchar(256) 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 SEQUENCE pool_seq;
CREATE TABLE pool
(
    poolid	      integer	  not null default pool_seq.nextval,
    name	      varchar(256)	  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,
    ActionOnPurge     smallint    default 0,
    pooltype	      varchar(256)			    
      check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
    labeltype	      integer	  default 0,
    labelformat       varchar(256)	  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 pool_name_idx on pool (name);

CREATE SEQUENCE client_seq;
CREATE TABLE client
(
    clientid	      integer	  not null default client_seq.nextval,
    name	      varchar(256)	  not null,
    uname	      varchar(256)	  not null,
    autoprune	      smallint	  default 0,
    fileretention     bigint	  default 0,
    jobretention      bigint	  default 0,
    primary key (clientid)
);

create unique index client_name_idx on client (name);

CREATE SEQUENCE Log_seq;
CREATE TABLE Log
(
    LogId	      integer	  not null default Log_seq.nextval,
    JobId	      integer	  not null,
    Time	      timestamp   without time zone,
    LogText	      varchar(256)	  not null,
    primary key (LogId)
);
create index log_name_idx on Log (JobId);

CREATE SEQUENCE LocationLog_seq;
CREATE TABLE LocationLog (
   LocLogId INTEGER NOT NULL DEFAULT LocationLog_seq.nextval,
   Date timestamp   without time zone,
   Comment varchar(256) NOT NULL,
   MediaId INTEGER DEFAULT 0,
   LocationId INTEGER DEFAULT 0,
   newvolstatus varchar(256) not null
	check (newvolstatus in ('Full','Archive','Append',
	      'Recycle','Purged','Read-Only','Disabled',
	      'Error','Busy','Used','Cleaning','Scratch')),
   newenabled smallint,
   PRIMARY KEY(LocLogId)
);



CREATE TABLE counters
(
    counter	      varchar(256)	  not null,
    minvalue	      integer	  default 0,
    maxvalue	      integer	  default 0,
    currentvalue      integer	  default 0,
    wrapcounter       varchar(256)	  not null,
    primary key (counter)
);


CREATE SEQUENCE basefiles_seq;
CREATE TABLE basefiles
(
    baseid	      integer		    not null default basefiles_seq.nextval,
    jobid	      integer		    not null,
    fileid	      bigint		    not null,
    fileindex	      integer			    ,
    basejobid	      integer			    ,
    primary key (baseid)
);

CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );

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 PathHierarchy
(
     PathId integer NOT NULL,
     PPathId integer NOT NULL,
     CONSTRAINT pathhierarchy_pkey PRIMARY KEY (PathId)
);

CREATE INDEX pathhierarchy_ppathid 
          ON PathHierarchy (PPathId);

CREATE TABLE PathVisibility
(
      PathId integer NOT NULL,
      JobId integer NOT NULL,
      Size int8 DEFAULT 0,
      Files int4 DEFAULT 0,
      CONSTRAINT pathvisibility_pkey PRIMARY KEY (JobId, PathId)
);
CREATE INDEX pathvisibility_jobid
             ON PathVisibility (JobId);

CREATE TABLE version
(
    versionid	      integer		    not null
);

CREATE TABLE Status (
   JobStatus CHAR(1) NOT NULL,
   JobStatusLong varchar(256),
   Severity int,
   PRIMARY KEY (JobStatus)
   );

\g

INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('C', 'Created, not yet running',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('R', 'Running',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('B', 'Blocked',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('T', 'Completed successfully', 10);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('E', 'Terminated with errors', 25);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('e', 'Non-fatal error',20);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('f', 'Fatal error',100);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('D', 'Verify found differences',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('A', 'Canceled by user',90);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('F', 'Waiting for Client',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('S', 'Waiting for Storage daemon',15);
INSERT INTO Status (JobStatus,JobStatusLong) VALUES
   ('m', 'Waiting for new media');
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('M', 'Waiting for media mount',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('s', 'Waiting for storage resource',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('j', 'Waiting for job resource',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('c', 'Waiting for client resource',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('d', 'Waiting on maximum jobs',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('t', 'Waiting on start time',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('p', 'Waiting on higher priority jobs',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('a', 'SD despooling attributes',15);
INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
   ('i', 'Doing batch insert file records',15);

INSERT INTO Version (VersionId) VALUES (11);

-- Make sure we have appropriate permissions
\g

END-OF-DATA
pstat=$?
if test $pstat = 0; 
then
   echo "Creation of Bacula Ingres tables succeeded."
else
   echo "Creation of Bacula Ingres tables failed."
fi
exit $pstat