-CREATE TABLE MediaType (
- MediaTypeId SERIAL,
- MediaType TEXT NOT NULL,
- ReadOnly INTEGER DEFAULT 0,
- PRIMARY KEY(MediaTypeId)
- );
-
-CREATE TABLE Device (
- DeviceId SERIAL,
- Name TEXT NOT NULL,
- MediaTypeId INTEGER NOT NULL,
- StorageId INTEGER UNSIGNED,
- 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 UNSIGNED DEFAULT 0,
- CleaningDate TIMESTAMP WITHOUT TIME ZONE,
- CleaningPeriod BIGINT NOT NULL DEFAULT 0,
- PRIMARY KEY(DeviceId)
- );
-
-CREATE TABLE Storage (
- StorageId SERIAL,
- Name TEXT NOT NULL,
- AutoChanger INTEGER DEFAULT 0,
- PRIMARY KEY(StorageId)
- );
-
-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');
-
-
-DELETE FROM version;
-INSERT INTO version (versionId) VALUES (9);
-
-vacuum;
+bindir=@SQL_BINDIR@
+PATH="$bindir:$PATH"
+db_name=@db_name@
+
+if psql -f - -d ${db_name} $* <<END-OF-DATA
+BEGIN; -- Necessary for Bacula core
+ALTER TABLE JobMedia DROP Copy ;
+ALTER TABLE Job ADD COLUMN HasCache smallint default 0;
+ALTER TABLE Job ADD COLUMN Reviewed smallint default 0;
+ALTER TABLE Job ADD COLUMN Comment text;
+ALTER TABLE JobHisto ADD COLUMN HasCache smallint default 0;
+ALTER TABLE JobHisto ADD COLUMN Reviewed smallint default 0;
+ALTER TABLE JobHisto ADD COLUMN Comment text;
+UPDATE Version SET VersionId=12;
+COMMIT;
+
+BEGIN; -- Can conflict with previous Bweb installation
+ALTER TABLE Status ADD COLUMN Severity int;
+UPDATE Status SET Severity = 15;
+UPDATE Status SET Severity = 100 where JobStatus = 'f';
+UPDATE Status SET Severity = 90 where JobStatus = 'A';
+UPDATE Status SET Severity = 10 where JobStatus = 'T';
+UPDATE Status SET Severity = 20 where JobStatus = 'e';
+UPDATE Status SET Severity = 25 where JobStatus = 'E';
+COMMIT;
+
+BEGIN; -- Can already exists if using 3.1.x release
+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);
+
+COMMIT;
+
+CREATE INDEX basefiles_jobid_idx ON BaseFiles ( JobId );
+
+-- Remove bad PostgreSQL index
+DROP INDEX file_fp_idx;
+
+-- Create the good one
+-- If you want to create this index during production, you can use
+-- CREATE INDEX CONCURRENTLY file_jpf_idx ON File (JobId, PathId, FilenameId)
+-- to make it without locks (require PostgreSQL 8.2 version)
+
+CREATE INDEX file_jpfid_idx on File (JobId, PathId, FilenameId);
+
+ANALYSE;