This commit was manufactured by cvs2svn to create tag

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

diff --git a/bacula/src/cats/update_sqlite3_tables.in b/bacula/src/cats/update_sqlite3_tables.in
index fc863e19d990e67f4cd1f47495da551bd6128c04..37be54689a249a16d939eeccc6f783000e4a1e8d 100755 (executable)
--- a/bacula/src/cats/update_sqlite3_tables.in
+++ b/bacula/src/cats/update_sqlite3_tables.in
@@ -1,6 +1,6 @@
 #!/bin/sh
 #
-# shell script to update SQLite from version 1.36 to 1.37.3
+# shell script to update SQLite from version 1.36 to 1.38
 #
 echo " "
 echo "This script will update a Bacula SQLite database from version 8 to 9"
@@ -20,6 +20,7 @@ CREATE TEMPORARY TABLE Media_backup (
    Slot INTEGER DEFAULT 0,
    PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
    MediaType VARCHAR(128) NOT NULL,
+   LabelType TINYINT DEFAULT 0,
    FirstWritten DATETIME DEFAULT 0,
    LastWritten DATETIME DEFAULT 0,
    LabelDate DATETIME DEFAULT 0,
@@ -101,7 +102,7 @@ CREATE TABLE Media (
 
 INSERT INTO Media (
    MediaId, VolumeName, Slot, PoolId,
-   MediaType, FirstWritten, LastWritten,
+   MediaType, LabelType, FirstWritten, LastWritten,
    LabelDate, VolJobs, VolFiles, VolBlocks,
    VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
    VolCapacityBytes, VolStatus, Recycle,
@@ -113,6 +114,58 @@ INSERT INTO Media (
    SELECT * FROM Media_backup;
 
 DROP TABLE Media_backup;
+CREATE INDEX inx8 ON Media (PoolId);
+
+
+CREATE TEMPORARY TABLE JobMedia_backup (
+   JobMediaId INTEGER,
+   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
+   MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
+   FirstIndex INTEGER UNSIGNED NOT NULL,
+   LastIndex INTEGER UNSIGNED NOT NULL,
+   StartFile INTEGER UNSIGNED DEFAULT 0,
+   EndFile INTEGER UNSIGNED DEFAULT 0,
+   StartBlock INTEGER UNSIGNED DEFAULT 0,
+   EndBlock INTEGER UNSIGNED DEFAULT 0,
+   VolIndex INTEGER UNSIGNED DEFAULT 0,
+   Copy     INTEGER UNSIGNED DEFAULT 0,
+   Stripe   INTEGER UNSIGNED DEFAULT 0,
+   PRIMARY KEY(JobMediaId) 
+   );
+
+INSERT INTO JobMedia_backup SELECT
+   JobMediaId, JobId, MediaId,
+   FirstIndex, LastIndex, StartFile,
+   EndFile, StartBlock, EndBlock,
+   VolIndex, 0, 0 
+   FROM JobMedia;
+
+DROP TABLE JobMedia;
+
+CREATE TABLE JobMedia (
+   JobMediaId INTEGER,
+   JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
+   MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
+   FirstIndex INTEGER UNSIGNED NOT NULL,
+   LastIndex INTEGER UNSIGNED NOT NULL,
+   StartFile INTEGER UNSIGNED DEFAULT 0,
+   EndFile INTEGER UNSIGNED DEFAULT 0,
+   StartBlock INTEGER UNSIGNED DEFAULT 0,
+   EndBlock INTEGER UNSIGNED DEFAULT 0,
+   VolIndex INTEGER UNSIGNED DEFAULT 0,
+   Copy     INTEGER UNSIGNED DEFAULT 0,
+   Stripe   INTEGER UNSIGNED DEFAULT 0,
+   PRIMARY KEY(JobMediaId) 
+   );
+
+INSERT INTO JobMedia (
+   JobMediaId, JobId, MediaId,
+   FirstIndex, LastIndex, StartFile,
+   EndFile, StartBlock, EndBlock,
+   VolIndex, Copy, Stripe)
+   SELECT * FROM JobMedia_backup;
+
+DROP TABLE JobMedia_backup;
 
 CREATE TEMPORARY TABLE Pool_backup (
    PoolId INTEGER,
@@ -198,12 +251,19 @@ INSERT INTO Pool (
 DROP TABLE Pool_backup;
 
 CREATE TABLE MediaType (
-   MediaTypeId INTERGER,
+   MediaTypeId INTEGER,
    MediaType VARCHAR(128) NOT NULL,
    ReadOnly TINYINT DEFAULT 0,
    PRIMARY KEY(MediaTypeId)
    );
 
+CREATE TABLE Storage (
+   StorageId INTEGER,
+   Name VARCHAR(128) NOT NULL,
+   AutoChanger TINYINT DEFAULT 0,
+   PRIMARY KEY(StorageId)
+   );
+
 CREATE TABLE Device (
    DeviceId INTEGER,
    Name VARCHAR(128) NOT NULL,
@@ -223,13 +283,56 @@ CREATE TABLE Device (
    PRIMARY KEY(DeviceId)
    );
 
-CREATE TABLE Storage (
-   StorageId INTEGER,
-   Name VARCHAR(128) NOT NULL,
-   AutoChanger TINYINT DEFAULT 0,
-   PRIMARY KEY(StorageId)
+
+CREATE TABLE Status (
+   JobStatus CHAR(1) NOT NULL,
+   JobStatusLong BLOB, 
+   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);
+
 COMMIT;
 
 END-OF-DATA