1#!/bin/sh 2# 3# Copyright (C) 2000-2015 Kern Sibbald 4# License: BSD 2-Clause; see file LICENSE-FOSS 5# shell script to update SQLite from version 2.0 to 3.0 6# 7echo " " 8echo "This script will update a Bacula SQLite database from version 10 to 11" 9echo " which is needed to convert from Bacula version 2.0.x to 3.0.x or higher" 10echo "Depending on the size of your database," 11echo "this script may take several minutes to run." 12echo " " 13 14bindir=@SQLITE_BINDIR@ 15PATH="$bindir:$PATH" 16cd @working_dir@ 17db_name=@db_name@ 18 19sqlite3 $* ${db_name}.db <<END-OF-DATA 20BEGIN TRANSACTION; 21 22CREATE TEMPORARY TABLE job_backup AS SELECT * FROM Job; 23DROP TABLE Job; 24 25CREATE TABLE Job 26( 27 JobId INTEGER, 28 Job VARCHAR(128) NOT NULL, 29 Name VARCHAR(128) NOT NULL, 30 Type CHAR NOT NULL, 31 Level CHAR NOT NULL, 32 ClientId INTEGER REFERENCES Client DEFAULT 0, 33 JobStatus CHAR NOT NULL, 34 SchedTime DATETIME NOT NULL, 35 StartTime DATETIME DEFAULT 0, 36 EndTime DATETIME DEFAULT 0, 37 RealEndTime DATETIME DEFAULT 0, 38 JobTDate BIGINT UNSIGNED DEFAULT 0, 39 VolSessionId INTEGER UNSIGNED DEFAULT 0, 40 VolSessionTime INTEGER UNSIGNED DEFAULT 0, 41 JobFiles INTEGER UNSIGNED DEFAULT 0, 42 JobBytes BIGINT UNSIGNED DEFAULT 0, 43 ReadBytes BIGINT UNSIGNED DEFAULT 0, 44 JobErrors INTEGER UNSIGNED DEFAULT 0, 45 JobMissingFiles INTEGER UNSIGNED DEFAULT 0, 46 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 47 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0, 48 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0, 49 PurgedFiles TINYINT DEFAULT 0, 50 HasBase TINYINT DEFAULT 0, 51 PRIMARY KEY(JobId) 52 ); 53CREATE INDEX inx6 ON Job (Name); 54 55INSERT INTO Job (JobId, Job, Name, Type, Level, ClientId, JobStatus, 56SchedTime, StartTime, EndTime, RealEndTime, JobTDate, VolSessionId, 57VolSessionTime, JobFiles, JobBytes, JobErrors, JobMissingFiles, 58PoolId, FileSetId, PriorJobId, PurgedFiles, HasBase) SELECT 59JobId, Job, Name, Type, Level, ClientId, JobStatus, SchedTime, StartTime, 60EndTime, RealEndTime, JobTDate, VolSessionId, VolSessionTime, JobFiles, 61JobBytes, JobErrors, JobMissingFiles, PoolId, FileSetId, PriorJobId, 62PurgedFiles, HasBase FROM Job_backup; 63 64DROP TABLE Job_backup; 65 66-- ---------------------------------------------------------------- 67-- New ActionOnPurge field 68 69CREATE TEMPORARY TABLE pool_backup AS SELECT * FROM Pool; 70DROP TABLE Pool; 71 72CREATE TABLE Pool ( 73 PoolId INTEGER, 74 Name VARCHAR(128) NOT NULL, 75 NumVols INTEGER UNSIGNED DEFAULT 0, 76 MaxVols INTEGER UNSIGNED DEFAULT 0, 77 UseOnce TINYINT DEFAULT 0, 78 UseCatalog TINYINT DEFAULT 1, 79 AcceptAnyVolume TINYINT DEFAULT 0, 80 VolRetention BIGINT UNSIGNED DEFAULT 0, 81 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 82 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 83 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 84 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 85 AutoPrune TINYINT DEFAULT 0, 86 Recycle TINYINT DEFAULT 0, 87 ActionOnPurge TINYINT DEFAULT 0, 88 PoolType VARCHAR(20) NOT NULL, 89 LabelType TINYINT DEFAULT 0, 90 LabelFormat VARCHAR(128) NOT NULL, 91 Enabled TINYINT DEFAULT 1, 92 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 93 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 94 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 95 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0, 96 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0, 97 MigrationTime BIGINT UNSIGNED DEFAULT 0, 98 UNIQUE (Name), 99 PRIMARY KEY (PoolId) 100 ); 101 102INSERT INTO Pool (PoolId, Name, NumVols, MaxVols, UseOnce, UseCatalog, 103AcceptAnyVolume, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, 104MaxVolBytes, AutoPrune, Recycle, PoolType, LabelType, 105LabelFormat, Enabled, ScratchPoolId, RecyclePoolId, NextPoolId, 106MigrationHighBytes, MigrationLowBytes, MigrationTime) 107SELECT PoolId, Name, NumVols, MaxVols, UseOnce, UseCatalog, AcceptAnyVolume, 108VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, AutoPrune, 109Recycle, PoolType, LabelType, LabelFormat, Enabled, 110ScratchPoolId, RecyclePoolId, NextPoolId, MigrationHighBytes, 111MigrationLowBytes, MigrationTime FROM pool_backup; 112 113DROP TABLE pool_backup; 114 115-- ---------------------------------------------------------------- 116-- New ActionOnPurge field 117 118CREATE TEMPORARY TABLE media_backup AS SELECT * FROM Media; 119DROP TABLE Media; 120 121CREATE TABLE Media ( 122 MediaId INTEGER, 123 VolumeName VARCHAR(128) NOT NULL, 124 Slot INTEGER DEFAULT 0, 125 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 126 MediaType VARCHAR(128) NOT NULL, 127 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0, 128 LabelType TINYINT DEFAULT 0, 129 FirstWritten DATETIME DEFAULT 0, 130 LastWritten DATETIME DEFAULT 0, 131 LabelDate DATETIME DEFAULT 0, 132 VolJobs INTEGER UNSIGNED DEFAULT 0, 133 VolFiles INTEGER UNSIGNED DEFAULT 0, 134 VolBlocks INTEGER UNSIGNED DEFAULT 0, 135 VolMounts INTEGER UNSIGNED DEFAULT 0, 136 VolBytes BIGINT UNSIGNED DEFAULT 0, 137 VolParts INTEGER UNSIGNED DEFAULT 0, 138 VolErrors INTEGER UNSIGNED DEFAULT 0, 139 VolWrites INTEGER UNSIGNED DEFAULT 0, 140 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, 141 VolStatus VARCHAR(20) NOT NULL, 142 Enabled TINYINT DEFAULT 1, 143 Recycle TINYINT DEFAULT 0, 144 ActionOnPurge TINYINT DEFAULT 0, 145 VolRetention BIGINT UNSIGNED DEFAULT 0, 146 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 147 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 148 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 149 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 150 InChanger TINYINT DEFAULT 0, 151 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0, 152 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0, 153 MediaAddressing TINYINT DEFAULT 0, 154 VolReadTime BIGINT UNSIGNED DEFAULT 0, 155 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 156 EndFile INTEGER UNSIGNED DEFAULT 0, 157 EndBlock INTEGER UNSIGNED DEFAULT 0, 158 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0, 159 RecycleCount INTEGER UNSIGNED DEFAULT 0, 160 InitialWrite DATETIME DEFAULT 0, 161 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 162 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 163 Comment TEXT, 164 PRIMARY KEY(MediaId) 165 ); 166 167CREATE INDEX inx8 ON Media (PoolId); 168 169INSERT INTO Media ( 170 MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, 171 LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, 172 VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, 173 VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, 174 VolRetention, VolUseDuration, MaxVolJobs, 175 MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, 176 MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, 177 LocationId, RecycleCount, InitialWrite, ScratchPoolId, 178 RecyclePoolId, Comment) 179SELECT MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, 180 LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, 181 VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, 182 VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, 183 VolRetention, VolUseDuration, MaxVolJobs, 184 MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, 185 MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, 186 LocationId, RecycleCount, InitialWrite, ScratchPoolId, 187 RecyclePoolId, Comment FROM media_backup; 188 189DROP TABLE media_backup; 190 191-- ---------------------------------------------------------------- 192 193UPDATE Version SET VersionId=11; 194 195COMMIT; 196 197-- If you have already this table, you can remove it with: 198-- DROP TABLE JobHistory; 199 200-- Create a table like Job for long term statistics 201CREATE TABLE JobHisto ( 202 JobId INTEGER, 203 Job VARCHAR(128) NOT NULL, 204 Name VARCHAR(128) NOT NULL, 205 Type CHAR NOT NULL, 206 Level CHAR NOT NULL, 207 ClientId INTEGER REFERENCES Client DEFAULT 0, 208 JobStatus CHAR NOT NULL, 209 SchedTime DATETIME NOT NULL, 210 StartTime DATETIME DEFAULT 0, 211 EndTime DATETIME DEFAULT 0, 212 RealEndTime DATETIME DEFAULT 0, 213 JobTDate BIGINT UNSIGNED DEFAULT 0, 214 VolSessionId INTEGER UNSIGNED DEFAULT 0, 215 VolSessionTime INTEGER UNSIGNED DEFAULT 0, 216 JobFiles INTEGER UNSIGNED DEFAULT 0, 217 JobBytes BIGINT UNSIGNED DEFAULT 0, 218 ReadBytes BIGINT UNSIGNED DEFAULT 0, 219 JobErrors INTEGER UNSIGNED DEFAULT 0, 220 JobMissingFiles INTEGER UNSIGNED DEFAULT 0, 221 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 222 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0, 223 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0, 224 PurgedFiles TINYINT DEFAULT 0, 225 HasBase TINYINT DEFAULT 0 226 ); 227CREATE INDEX inx61 ON JobHisto (StartTime); 228 229END-OF-DATA 230