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