X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fupdate_sqlite_tables.in;h=d0d9e743d3221c1b5b250b1d284b507efd7ea6e1;hb=b34b84f6913ee6d03d6c838e647c4aedf4bda3f2;hp=4675ba1e31bbde9c596dd53a16526772c915059c;hpb=185a7010ae96c2e391d5f2c77fc42f14020621c4;p=bacula%2Fbacula diff --git a/bacula/src/cats/update_sqlite_tables.in b/bacula/src/cats/update_sqlite_tables.in index 4675ba1e31..d0d9e743d3 100755 --- a/bacula/src/cats/update_sqlite_tables.in +++ b/bacula/src/cats/update_sqlite_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.37.12 # 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, @@ -40,6 +41,7 @@ CREATE TEMPORARY TABLE Media_backup ( MaxVolFiles INTEGER UNSIGNED DEFAULT 0, MaxVolBytes BIGINT UNSIGNED DEFAULT 0, InChanger TINYINT DEFAULT 0, + StorageId INTEGER UNSIGNED REFERENCES Storage, MediaAddressing TINYINT DEFAULT 0, VolReadTime BIGINT UNSIGNED DEFAULT 0, VolWriteTime BIGINT UNSIGNED DEFAULT 0, @@ -55,7 +57,7 @@ INSERT INTO Media_backup SELECT VolMounts, VolBytes, 0, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Recycle, VolRetention, VolUseDuration, MaxVolJobs, - MaxVolFiles, MaxVolBytes, InChanger, MediaAddressing, + MaxVolFiles, MaxVolBytes, InChanger, 0, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock FROM Media; @@ -89,6 +91,7 @@ CREATE TABLE Media ( MaxVolFiles INTEGER UNSIGNED DEFAULT 0, MaxVolBytes BIGINT UNSIGNED DEFAULT 0, InChanger TINYINT DEFAULT 0, + StorageId INTEGER UNSIGNED REFERENCES Storage, MediaAddressing TINYINT DEFAULT 0, VolReadTime BIGINT UNSIGNED DEFAULT 0, VolWriteTime BIGINT UNSIGNED DEFAULT 0, @@ -99,18 +102,70 @@ 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, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, - InChanger, MediaAddressing, + InChanger, StorageId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock) 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, @@ -133,6 +188,10 @@ CREATE TEMPORARY TABLE Pool_backup ( Enabled TINYINT DEFAULT 1, ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, + NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, + MigrationHighBytes BIGINT UNSIGNED DEFAULT 0, + MigrationLowBytes BIGINT UNSIGNED DEFAULT 0, + MigrationTime BIGINT UNSIGNED DEFAULT 0, UNIQUE (Name), PRIMARY KEY (PoolId) ); @@ -143,7 +202,8 @@ INSERT INTO Pool_backup SELECT VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, AutoPrune, Recycle, PoolType, 0, LabelFormat, - Enabled, ScratchPoolId, RecyclePoolId + Enabled, ScratchPoolId, RecyclePoolId, + 0, 0, 0, 0 FROM Pool; DROP TABLE Pool; @@ -169,6 +229,10 @@ CREATE TABLE Pool ( Enabled TINYINT DEFAULT 1, ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, + NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, + MigrationHighBytes BIGINT UNSIGNED DEFAULT 0, + MigrationLowBytes BIGINT UNSIGNED DEFAULT 0, + MigrationTime BIGINT UNSIGNED DEFAULT 0, UNIQUE (Name), PRIMARY KEY (PoolId) ); @@ -179,18 +243,27 @@ INSERT INTO Pool ( VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, AutoPrune, Recycle, PoolType, LabelType, LabelFormat, - Enabled, ScratchPoolId, RecyclePoolId ) + Enabled, ScratchPoolId, RecyclePoolId, + NextPoolId, MigrationHighBytes, + MigrationLowBytes, MigrationTime ) SELECT * FROM Pool_backup; 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, @@ -210,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