1#!/bin/sh 2# 3# Copyright (C) 2000-2017 Kern Sibbald 4# License: BSD 2-Clause; see file LICENSE-FOSS 5# 6# Shell script to update SQLite tables from Bacula Community version 7# 5.0.x, 5.2.x, 7.0.x, 7.2.x, 7.4.x 8# 9echo " " 10echo "This script will update a Bacula SQLite database from version 12-15 to 16" 11echo " " 12echo "Depending on the current version of your catalog," 13echo "you may have to run this script multiple times" 14echo " " 15 16bindir=@SQLITE_BINDIR@ 17PATH="$bindir:$PATH" 18cd @working_dir@ 19db_name=@db_name@ 20DBVERSION=`sqlite3 ${db_name}.db <<END 21SELECT VersionId FROM Version LIMIT 1; 22END 23` 24if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 15 ] ; then 25 echo " " 26 echo "The existing database is version $DBVERSION !!" 27 echo "This script can only update an existing version 12, 13, 14 or 15 database to version 16." 28 echo "Error. Cannot upgrade this database, which has version $DBVERSION.." 29 echo " " 30 exit 1 31fi 32 33if [ "$DBVERSION" = 12 ] ; then 34sqlite3 $* ${db_name}.db <<END-OF-DATA 35BEGIN; 36 37CREATE TABLE RestoreObject ( 38 RestoreObjectId INTEGER, 39 ObjectName TEXT DEFAULT '', 40 RestoreObject TEXT DEFAULT '', 41 PluginName TEXT DEFAULT '', 42 ObjectLength INTEGER DEFAULT 0, 43 ObjectFullLength INTEGER DEFAULT 0, 44 ObjectIndex INTEGER DEFAULT 0, 45 ObjectType INTEGER DEFAULT 0, 46 FileIndex INTEGER UNSIGNED DEFAULT 0, 47 ObjectCompression INTEGER DEFAULT 0, 48 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 49 PRIMARY KEY(RestoreObjectId) 50 ); 51CREATE INDEX restore_jobid_idx ON RestoreObject (JobId); 52 53UPDATE Version SET VersionId=13; 54COMMIT; 55 56END-OF-DATA 57DBVERSION=13 58fi 59 60if [ "$DBVERSION" = 13 ] ; then 61 62sqlite3 $* ${db_name}.db <<END-OF-DATA 63BEGIN; 64 65ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0; 66UPDATE Version SET VersionId=14; 67 68COMMIT; 69 70END-OF-DATA 71 72DBVERSION=14 73 74fi 75 76if [ "$DBVERSION" = 14 ] ; then 77 sqlite3 $* ${db_name}.db <<END-OF-DATA 78BEGIN; 79 80INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES 81 ('I', 'Incomplete Job',25); 82 83CREATE TABLE new_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 BIGINT 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 ActionOnPurge TINYINT DEFAULT 0, 107 VolRetention BIGINT UNSIGNED DEFAULT 0, 108 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 109 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 110 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 111 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 112 InChanger TINYINT DEFAULT 0, 113 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0, 114 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0, 115 MediaAddressing TINYINT DEFAULT 0, 116 VolReadTime BIGINT UNSIGNED DEFAULT 0, 117 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 118 EndFile INTEGER UNSIGNED DEFAULT 0, 119 EndBlock INTEGER UNSIGNED DEFAULT 0, 120 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0, 121 RecycleCount INTEGER UNSIGNED DEFAULT 0, 122 InitialWrite DATETIME DEFAULT 0, 123 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 124 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 125 Comment TEXT, 126 VolABytes BIGINT UNSIGNED DEFAULT 0, 127 VolAPadding BIGINT UNSIGNED DEFAULT 0, 128 VolHoleBytes BIGINT UNSIGNED DEFAULT 0, 129 VolHoles INTEGER UNSIGNED DEFAULT 0, 130 PRIMARY KEY(MediaId) 131 ); 132INSERT INTO new_Media (MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment) SELECT MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment FROM Media; 133DROP TABLE Media; 134ALTER TABLE new_Media RENAME TO Media; 135CREATE INDEX inx8 ON Media (PoolId); 136 137CREATE TABLE Snapshot ( 138 SnapshotId INTEGER UNSIGNED AUTO_INCREMENT, 139 Name TINYBLOB NOT NULL, 140 JobId INTEGER UNSIGNED DEFAULT 0, 141 FileSetId INTEGER UNSIGNED DEFAULT 0, 142 CreateTDate BIGINT NOT NULL, 143 CreateDate DATETIME NOT NULL, 144 ClientId INTEGER DEFAULT 0, 145 Volume TINYBLOB NOT NULL, 146 Device TINYBLOB NOT NULL, 147 Type TINYBLOB NOT NULL, 148 Retention INTEGER DEFAULT 0, 149 Comment BLOB, 150 primary key (SnapshotId) 151); 152 153CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name); 154 155CREATE INDEX jobtdate_idx on JobHisto (JobTDate); 156 157UPDATE Version SET VersionId=15; 158COMMIT; 159 160END-OF-DATA 161 162DBVERSION=15 163 164fi 165 166if [ "$DBVERSION" -eq 15 ] ; then 167 sqlite3 $* ${db_name}.db <<END-OF-DATA 168 169BEGIN; 170CREATE TABLE basefiles_temp ( 171 BaseId BIGINT, 172 BaseJobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 173 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL, 174 FileId INTEGER UNSIGNED REFERENCES File NOT NULL, 175 FileIndex INTEGER UNSIGNED, 176 PRIMARY KEY(BaseId) 177 ); 178 179INSERT INTO basefiles_temp (BaseId, BaseJobId, JobId, 180 FileId, FileIndex) 181 SELECT BaseId, BaseJobId, JobId, FileId, FileIndex 182 FROM BaseFiles; 183 184CREATE TABLE new_Media ( 185 MediaId INTEGER, 186 VolumeName VARCHAR(128) NOT NULL, 187 Slot INTEGER DEFAULT 0, 188 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 189 MediaType VARCHAR(128) NOT NULL, 190 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0, 191 LabelType TINYINT DEFAULT 0, 192 FirstWritten DATETIME DEFAULT 0, 193 LastWritten DATETIME DEFAULT 0, 194 LabelDate DATETIME DEFAULT 0, 195 VolJobs INTEGER UNSIGNED DEFAULT 0, 196 VolFiles INTEGER UNSIGNED DEFAULT 0, 197 VolBlocks INTEGER UNSIGNED DEFAULT 0, 198 LastPartBytes BIGINT UNSIGNED DEFAULT 0, 199 VolMounts INTEGER UNSIGNED DEFAULT 0, 200 VolBytes BIGINT UNSIGNED DEFAULT 0, 201 VolABytes BIGINT UNSIGNED DEFAULT 0, 202 VolAPadding BIGINT UNSIGNED DEFAULT 0, 203 VolHoleBytes BIGINT UNSIGNED DEFAULT 0, 204 VolHoles INTEGER UNSIGNED DEFAULT 0, 205 VolType INTEGER UNSIGNED DEFAULT 0, 206 VolParts INTEGER UNSIGNED DEFAULT 0, 207 VolCloudParts INTEGER UNSIGNED DEFAULT 0, 208 VolErrors INTEGER UNSIGNED DEFAULT 0, 209 VolWrites BIGINT UNSIGNED DEFAULT 0, 210 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0, 211 VolStatus VARCHAR(20) NOT NULL, 212 Enabled TINYINT DEFAULT 1, 213 Recycle TINYINT DEFAULT 0, 214 ActionOnPurge TINYINT DEFAULT 0, 215 CacheRetention BIGINT UNSIGNED DEFAULT 0, 216 VolRetention BIGINT UNSIGNED DEFAULT 0, 217 VolUseDuration BIGINT UNSIGNED DEFAULT 0, 218 MaxVolJobs INTEGER UNSIGNED DEFAULT 0, 219 MaxVolFiles INTEGER UNSIGNED DEFAULT 0, 220 MaxVolBytes BIGINT UNSIGNED DEFAULT 0, 221 InChanger TINYINT DEFAULT 0, 222 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0, 223 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0, 224 MediaAddressing TINYINT DEFAULT 0, 225 VolReadTime BIGINT UNSIGNED DEFAULT 0, 226 VolWriteTime BIGINT UNSIGNED DEFAULT 0, 227 EndFile INTEGER UNSIGNED DEFAULT 0, 228 EndBlock INTEGER UNSIGNED DEFAULT 0, 229 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0, 230 RecycleCount INTEGER UNSIGNED DEFAULT 0, 231 InitialWrite DATETIME DEFAULT 0, 232 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 233 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0, 234 Comment TEXT, 235 PRIMARY KEY(MediaId) 236 ); 237 238INSERT INTO new_Media (MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolType, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment) SELECT MediaId, VolumeName, Slot, PoolId, MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolParts, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Enabled, Recycle, ActionOnPurge, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, StorageId, DeviceId, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Comment FROM Media; 239DROP TABLE Media; 240ALTER TABLE new_Media RENAME TO Media; 241CREATE INDEX inx8 ON Media (PoolId); 242 243CREATE UNIQUE INDEX Media_Volumename_Id ON Media (VolumeName); 244 245 246DROP TABLE BaseFiles; 247ALTER TABLE basefiles_temp RENAME TO BaseFiles; 248 249CREATE INDEX job_jobtdate_inx ON job (JobTDate); 250ALTER TABLE Pool ADD COLUMN CacheRetention BIGINT DEFAULT 0; 251 252UPDATE Version SET VersionId=16; 253COMMIT; 254 255END-OF-DATA 256 257DBVERSION=16 258 259fi 260 261echo "" 262 263echo "WARNING: !!!! SQLite3 is no longer supported. !!!!" 264echo "WARNING: !!!! Please switch to MySQL or PostgreSQL !!!!" 265echo "WARNING: !!!! as soon as possible. !!!!" 266echo "" 267