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