1#!/bin/sh 2# 3# shell script to update SQLite from version 1.32 to 1.33 4# 5echo " " 6echo "This script will update a bacula database from version 6 to 7." 7echo "Depending on the size of your database," 8echo "this script may take several minutes to run." 9echo " " 10 11# The location of the sqlite program 12bindir=/usr/lib/sqlite 13# The location of your bacula working directory 14cd /var/bacula 15 16if [ -s bacula.db ];then 17 DB_VER=`echo "select * from Version;" | $bindir/sqlite bacula.db | tail -n 1 2>/dev/null` 18 if [ -n "$DB_VER" ] && [ "$DB_VER" -ne "6" ]; then 19 echo "Sorry, this script is designed to update a version 6 database" 20 echo "and you have a version $DB_VER database." 21 exit 1 22 fi 23else 24 echo "Sorry, I can't seem to locate a bacula database." 25 exit 1 26fi 27 28$bindir/sqlite $* bacula.db <<END-OF-DATA 29 30BEGIN TRANSACTION; 31CREATE TEMPORARY TABLE Media_backup ( 32 MediaId INTEGER UNSIGNED AUTOINCREMENT, 33 VolumeName VARCHAR(128) NOT NULL, 34 Slot INTEGER DEFAULT 0, 35 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL, 36 MediaType VARCHAR(128) NOT NULL, 37 FirstWritten DATETIME DEFAULT 0, 38 LastWritten DATETIME DEFAULT 0, 39 LabelDate DATETIME DEFAULT 0, 40 VolJobs INTEGER UNSIGNED DEFAULT 0, 41 VolFiles INTEGER UNSIGNED DEFAULT 0, 42 VolBlocks INTEGER UNSIGNED DEFAULT 0, 43 VolMounts INTEGER UNSIGNED DEFAULT 0, 44 VolBytes BIGINT UNSIGNED DEFAULT 0, 45 VolErrors INTEGER UNSIGNED DEFAULT 0, 46 VolWrites INTEGER UNSIGNED DEFAULT 0, 47 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, 48 VolStatus VARCHAR(20) NOT NULL, 49 Recycle TINYINT DEFAULT 0, 50 VolRetention BIGINT UNSIGNED DEFAULT 0, 51 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 52 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 53 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 54 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 55 InChanger TINYINT DEFAULT 0, 56 MediaAddressing TINYINT DEFAULT 0, 57 VolReadTime BIGINT UNSIGNED DEFAULT 0, 58 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 59 PRIMARY KEY(MediaId) 60 ); 61 62INSERT INTO Media_backup SELECT 63 MediaId, VolumeName, Slot, PoolId, 64 MediaType, FirstWritten, LastWritten, 65 LabelDate, VolJobs, VolFiles, VolBlocks, 66 VolMounts, VolBytes, VolErrors, VolWrites, 67 VolCapacityBytes, VolStatus, Recycle, 68 VolRetention, VolUseDuration, MaxVolJobs, 69 MaxVolFiles, MaxVolBytes, 0, 0, 0, 0 70 FROM Media; 71 72 73DROP TABLE Media; 74 75CREATE TABLE Media ( 76 MediaId INTEGER UNSIGNED AUTOINCREMENT, 77 VolumeName VARCHAR(128) NOT NULL, 78 Slot INTEGER DEFAULT 0, 79 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL, 80 MediaType VARCHAR(128) NOT NULL, 81 FirstWritten DATETIME DEFAULT 0, 82 LastWritten DATETIME DEFAULT 0, 83 LabelDate DATETIME DEFAULT 0, 84 VolJobs INTEGER UNSIGNED DEFAULT 0, 85 VolFiles INTEGER UNSIGNED DEFAULT 0, 86 VolBlocks INTEGER UNSIGNED DEFAULT 0, 87 VolMounts INTEGER UNSIGNED DEFAULT 0, 88 VolBytes BIGINT UNSIGNED DEFAULT 0, 89 VolErrors INTEGER UNSIGNED DEFAULT 0, 90 VolWrites INTEGER UNSIGNED DEFAULT 0, 91 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, 92 VolStatus VARCHAR(20) NOT NULL, 93 Recycle TINYINT DEFAULT 0, 94 VolRetention BIGINT UNSIGNED DEFAULT 0, 95 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 96 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 97 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 98 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 99 InChanger TINYINT DEFAULT 0, 100 MediaAddressing TINYINT DEFAULT 0, 101 VolReadTime BIGINT UNSIGNED DEFAULT 0, 102 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 103 PRIMARY KEY(MediaId) 104 ); 105 106INSERT INTO Media ( 107 MediaId, VolumeName, Slot, PoolId, 108 MediaType, FirstWritten, LastWritten, 109 LabelDate, VolJobs, VolFiles, VolBlocks, 110 VolMounts, VolBytes, VolErrors, VolWrites, 111 VolCapacityBytes, VolStatus, Recycle, 112 VolRetention, VolUseDuration, MaxVolJobs, 113 MaxVolFiles, MaxVolBytes, 114 InChanger, MediaAddressing, 115 VolReadTime, VolWriteTime) 116 SELECT * FROM Media_backup; 117 118DROP TABLE Media_backup; 119 120CREATE INDEX inx8 ON Media (PoolId); 121 122CREATE TABLE Pool_backup ( 123 PoolId INTEGER UNSIGNED AUTOINCREMENT, 124 Name VARCHAR(128) NOT NULL, 125 NumVols INTEGER UNSIGNED DEFAULT 0, 126 MaxVols INTEGER UNSIGNED DEFAULT 0, 127 UseOnce TINYINT DEFAULT 0, 128 UseCatalog TINYINT DEFAULT 1, 129 AcceptAnyVolume TINYINT DEFAULT 0, 130 VolRetention BIGINT UNSIGNED DEFAULT 0, 131 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 132 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 133 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 134 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 135 AutoPrune TINYINT DEFAULT 0, 136 Recycle TINYINT DEFAULT 0, 137 PoolType VARCHAR(20) NOT NULL, 138 LabelFormat VARCHAR(128) NOT NULL, 139 Enabled TINYINT DEFAULT 1, 140 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 141 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 142 UNIQUE (Name), 143 PRIMARY KEY (PoolId) 144 ); 145 146INSERT INTO Pool_backup SELECT 147 PoolId, 148 Name, 149 NumVols, 150 MaxVols, 151 UseOnce, 152 UseCatalog, 153 AcceptAnyVolume, 154 VolRetention, 155 VolUseDuration, 156 MaxVolJobs, 157 MaxVolFiles, 158 MaxVolBytes, 159 AutoPrune, 160 Recycle, 161 PoolType, 162 LabelFormat, 1, 0, 0 163 FROM Pool; 164 165DROP TABLE Pool; 166 167CREATE TABLE Pool ( 168 PoolId INTEGER UNSIGNED AUTOINCREMENT, 169 Name VARCHAR(128) NOT NULL, 170 NumVols INTEGER UNSIGNED DEFAULT 0, 171 MaxVols INTEGER UNSIGNED DEFAULT 0, 172 UseOnce TINYINT DEFAULT 0, 173 UseCatalog TINYINT DEFAULT 1, 174 AcceptAnyVolume TINYINT DEFAULT 0, 175 VolRetention BIGINT UNSIGNED DEFAULT 0, 176 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 177 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 178 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 179 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 180 AutoPrune TINYINT DEFAULT 0, 181 Recycle TINYINT DEFAULT 0, 182 PoolType VARCHAR(20) NOT NULL, 183 LabelFormat VARCHAR(128) NOT NULL, 184 Enabled TINYINT DEFAULT 1, 185 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 186 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 187 UNIQUE (Name), 188 PRIMARY KEY (PoolId) 189 ); 190 191INSERT INTO Pool ( 192 PoolId, 193 Name, 194 NumVols, 195 MaxVols, 196 UseOnce, 197 UseCatalog, 198 AcceptAnyVolume, 199 VolRetention, 200 VolUseDuration, 201 MaxVolJobs, 202 MaxVolFiles, 203 MaxVolBytes, 204 AutoPrune, 205 Recycle, 206 PoolType, 207 LabelFormat, Enabled, ScratchPoolId, RecyclePoolId) 208 SELECT * FROM Pool_backup; 209 210DROP TABLE Pool_backup; 211 212 213DROP TABLE BaseFiles; 214 215CREATE TABLE BaseFiles ( 216 BaseId INTEGER UNSIGNED AUTOINCREMENT, 217 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 218 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 219 FileId INTEGER UNSIGNED REFERENCES File NOT NULL, 220 FileIndex INTEGER UNSIGNED, 221 PRIMARY KEY(BaseId) 222 ); 223 224COMMIT; 225 226DELETE FROM Version; 227INSERT INTO Version (VersionId) VALUES (7); 228 229END-OF-DATA 230