Fix bug in bvfs_update function, should work much better now

[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 91bf1efc4734eb765b277404eeaa21bd16b51867..a713c06a4db0c568c8e0ebdf2ee8efdf37e5491f 100644 (file)
--- a/bacula/src/cats/make_postgresql_tables.in
+++ b/bacula/src/cats/make_postgresql_tables.in
@@ -3,9 +3,10 @@
 # shell script to create Bacula PostgreSQL tables
 #
 bindir=@SQL_BINDIR@
+PATH="$bindir:$PATH"
 db_name=${db_name:-@db_name@}
 
-$bindir/psql -f - -d ${db_name} $* <<END-OF-DATA
+psql -f - -d ${db_name} $* <<END-OF-DATA
 
 CREATE TABLE filename
 (
@@ -40,7 +41,7 @@ CREATE TABLE file
     primary key (fileid)
 );
 
-CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
+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
@@ -59,32 +60,35 @@ CREATE INDEX file_jobid_idx on file(jobid);
 -- CREATE INDEX file_pathid_idx on file(pathid);
 -- CREATE INDEX file_filenameid_idx on file(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     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,
-    purgedfiles       smallint   default 0,
-    hasbase          smallint    default 0,
-    priorjobid       integer     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)
 );
 
@@ -92,7 +96,7 @@ CREATE INDEX job_name_idx on job (name);
 
 -- Create a table like Job for long term statistics 
 CREATE TABLE JobHisto (LIKE Job);
-CREATE INDEX jobhisto_idx ON jobhisto ( starttime );
+CREATE INDEX jobhisto_idx ON JobHisto ( StartTime );
 
 
 CREATE TABLE Location (
@@ -127,7 +131,6 @@ CREATE TABLE jobmedia
     startblock       bigint      default 0,
     endblock         bigint      default 0,
     volindex         integer     default 0,
-    copy             integer     default 0,
     primary key (jobmediaid)
 );
 
@@ -314,6 +317,8 @@ CREATE TABLE basefiles
     primary key (baseid)
 );
 
+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
+
 CREATE TABLE unsavedfiles
 (
     UnsavedId        integer               not null,
@@ -331,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
@@ -338,54 +364,55 @@ CREATE TABLE version
 
 CREATE TABLE Status (
    JobStatus CHAR(1) NOT NULL,
-   JobStatusLong TEXT, 
+   JobStatusLong TEXT,
+   Severity int,
    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 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 Status (JobStatus,JobStatusLong) VALUES
-   ('a', 'SD despooling attributes');
-INSERT INTO Status (JobStatus,JobStatusLong) VALUES
-   ('i', 'Doing batch insert file records');
-
-INSERT INTO Version (VersionId) VALUES (11);
+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