Tweak postgresql update script to handle previous Bweb install

[bacula/bacula] / bacula / src / cats / make_postgresql_tables.in

diff --git a/bacula/src/cats/make_postgresql_tables.in b/bacula/src/cats/make_postgresql_tables.in
index c169ebf28009ae20668dfe3bfaa6178e881b5b4e..a713c06a4db0c568c8e0ebdf2ee8efdf37e5491f 100644 (file)
--- a/bacula/src/cats/make_postgresql_tables.in
+++ b/bacula/src/cats/make_postgresql_tables.in
@@ -3,8 +3,10 @@
 # shell script to create Bacula PostgreSQL tables
 #
 bindir=@SQL_BINDIR@
 # shell script to create Bacula PostgreSQL tables
 #
 bindir=@SQL_BINDIR@

+PATH="$bindir:$PATH"
+db_name=${db_name:-@db_name@}

 
 

-if $bindir/psql -f - -d bacula $* <<END-OF-DATA
+psql -f - -d ${db_name} $* <<END-OF-DATA

 
 CREATE TABLE filename
 (
 
 CREATE TABLE filename
 (


@@ -13,7 +15,8 @@ CREATE TABLE filename
     primary key (filenameid)
 );
 
     primary key (filenameid)
 );
 

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

 
 CREATE TABLE path
 (
 
 CREATE TABLE path
 (


@@ -22,11 +25,12 @@ CREATE TABLE path
     primary key (pathid)
 );
 
     primary key (pathid)
 );
 

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

 
 CREATE TABLE file
 (
 
 CREATE TABLE file
 (

-    fileid           serial      not null,
+    fileid           bigserial   not null,

     fileindex        integer     not null  default 0,
     jobid            integer     not null,
     pathid           integer     not null,
     fileindex        integer     not null  default 0,
     jobid            integer     not null,
     pathid           integer     not null,


@@ -37,8 +41,17 @@ CREATE TABLE file
     primary key (fileid)
 );
 
     primary key (fileid)
 );
 

-CREATE INDEX file_jobid_idx on file (jobid);
-CREATE INDEX file_fp_idx on file (filenameid, pathid);
+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
 
 --
 -- Possibly add one or more of the following indexes


@@ -46,51 +59,51 @@ CREATE INDEX file_fp_idx on file (filenameid, pathid);
 --
 -- CREATE INDEX file_pathid_idx on file(pathid);
 -- CREATE INDEX file_filenameid_idx on file(filenameid);
 --
 -- 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
+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,
+    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,
+    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           text,

     primary key (jobid)
 );
 
 CREATE INDEX job_name_idx on job (name);
 
     primary key (jobid)
 );
 
 CREATE INDEX job_name_idx on job (name);
 

-CREATE TABLE MAC (
-    JobId            serial      not null,
-    OriginalJobId     serial     not null,
-    JobType          char(1)     not null,
-    JobLevel         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,
-    primary key (jobid)
-);
+-- Create a table like Job for long term statistics 
+CREATE TABLE JobHisto (LIKE Job);
+CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
+

 
 CREATE TABLE Location (
    LocationId        serial      not null,
    Location          text        not null,
 
 CREATE TABLE Location (
    LocationId        serial      not null,
    Location          text        not null,

+   Cost              integer     default 0,
+   Enabled           smallint,

    primary key (LocationId)
 );
 
    primary key (LocationId)
 );
 


@@ -111,15 +124,13 @@ CREATE TABLE jobmedia
     jobmediaid       serial      not null,
     jobid            integer     not null,
     mediaid          integer     not null,
     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,
+    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)
 );
 
     primary key (jobmediaid)
 );
 


@@ -129,46 +140,49 @@ CREATE TABLE media
 (
     mediaid          serial      not null,
     volumename       text        not null,
 (
     mediaid          serial      not null,
     volumename       text        not null,

-    slot             integer     not null default 0,
-    poolid           integer     not null,
+    slot             integer     default 0,
+    poolid           integer     default 0,

     mediatype        text        not null,
     mediatype        text        not null,

-    mediatypeid       integer    not null,
-    labeltype        integer     not null default 0,
+    mediatypeid       integer    default 0,
+    labeltype        integer     default 0,

     firstwritten      timestamp   without time zone,
     lastwritten       timestamp   without time zone,
     labeldate        timestamp   without time zone,
     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,
+    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')),
     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,
-    DeviceId         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,
-    LocationId       integer              default 0,
-    recyclcount       integer    not null default 0,
+    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,
     initialwrite      timestamp   without time zone,

-    scratchpoolid     integer default 0,
-    recyclepoolid     integer default 0,
+    scratchpoolid     integer    default 0,
+    recyclepoolid     integer    default 0,
+    comment          text,

     primary key (mediaid)
 );
 
     primary key (mediaid)
 );
 


@@ -203,7 +217,7 @@ CREATE TABLE Device (
    DevWriteTime BIGINT NOT NULL DEFAULT 0,
    DevReadTimeSinceCleaning BIGINT NOT NULL DEFAULT 0,
    DevWriteTimeSinceCleaning 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,
+   CleaningDate timestamp without time zone,

    CleaningPeriod BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY(DeviceId)
    );
    CleaningPeriod BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY(DeviceId)
    );


@@ -213,29 +227,30 @@ CREATE TABLE pool
 (
     poolid           serial      not null,
     name             text        not null,
 (
     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,
+    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         text                          
       check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
     pooltype         text                          
       check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),

-    labeltype        integer     not null default 0,
+    labeltype        integer     default 0,

     labelformat       text       not null,
     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,
+    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)
 );
 
     primary key (poolid)
 );
 


@@ -247,20 +262,45 @@ CREATE TABLE client
     name             text        not null,
     uname            text        not null,
     autoprune        smallint    default 0,
     name             text        not null,
     uname            text        not null,
     autoprune        smallint    default 0,

-    fileretention     bigint     not null,
-    jobretention      bigint     not null,
+    fileretention     bigint     default 0,
+    jobretention      bigint     default 0,

     primary key (clientid)
 );
 
 create unique index client_name_idx on client (name);
 
     primary key (clientid)
 );
 
 create unique index client_name_idx on client (name);
 

+CREATE TABLE Log
+(
+    LogId            serial      not null,
+    JobId            integer     not null,
+    Time             timestamp   without time zone,
+    LogText          text        not null,
+    primary key (LogId)
+);
+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)
+);
+
+

 
 CREATE TABLE counters
 (
     counter          text        not null,
 
 CREATE TABLE counters
 (
     counter          text        not null,

-    minvalue         integer,
-    maxvalue         integer,
-    currentvalue      integer,
+    minvalue         integer     default 0,
+    maxvalue         integer     default 0,
+    currentvalue      integer    default 0,

     wrapcounter       text       not null,
     primary key (counter)
 );
     wrapcounter       text       not null,
     primary key (counter)
 );


@@ -271,12 +311,14 @@ CREATE TABLE basefiles
 (
     baseid           serial                not null,
     jobid            integer               not null,
 (
     baseid           serial                not null,
     jobid            integer               not null,

-    fileid           integer               not null,
+    fileid           bigint                not null,

     fileindex        integer                       ,
     basejobid        integer                       ,
     primary key (baseid)
 );
 
     fileindex        integer                       ,
     basejobid        integer                       ,
     primary key (baseid)
 );
 

+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
+

 CREATE TABLE unsavedfiles
 (
     UnsavedId        integer               not null,
 CREATE TABLE unsavedfiles
 (
     UnsavedId        integer               not null,


@@ -294,6 +336,27 @@ CREATE TABLE CDImages
 );
 
 
 );
 
 

+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 version
 (
     versionid        integer               not null


@@ -301,59 +364,65 @@ CREATE TABLE version
 
 CREATE TABLE Status (
    JobStatus CHAR(1) NOT NULL,
 
 CREATE TABLE Status (
    JobStatus CHAR(1) NOT NULL,

-   JobStatusLong TEXT, 
+   JobStatusLong TEXT,
+   Severity int,

    PRIMARY KEY (JobStatus)
    );
 
    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,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) 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);
+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 (12);

 
 -- Make sure we have appropriate permissions
 
 
 END-OF-DATA
 
 -- 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
 then
    echo "Creation of Bacula PostgreSQL tables succeeded."
 else
    echo "Creation of Bacula PostgreSQL tables failed."
 fi

-exit 0
+exit $pstat