3 # Copyright (C) 2000-2017 Kern Sibbald
4 # License: BSD 2-Clause; see file LICENSE-FOSS
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
10 echo "This script will update a Bacula SQLite database from version 12-15 to 16"
12 echo "Depending on the current version of your catalog,"
13 echo "you may have to run this script multiple times"
16 bindir=@SQLITE_BINDIR@
20 DBVERSION=`sqlite3 ${db_name}.db <<END
21 SELECT VersionId FROM Version LIMIT 1;
24 if [ "$DBVERSION" -lt 12 -o "$DBVERSION" -gt 15 ] ; then
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.."
33 if [ "$DBVERSION" = 12 ] ; then
34 sqlite3 $* ${db_name}.db <<END-OF-DATA
37 CREATE 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)
51 CREATE INDEX restore_jobid_idx ON RestoreObject (JobId);
53 UPDATE Version SET VersionId=13;
60 if [ "$DBVERSION" = 13 ] ; then
62 sqlite3 $* ${db_name}.db <<END-OF-DATA
65 ALTER TABLE File ADD COLUMN DeltaSeq smallint default 0;
66 UPDATE Version SET VersionId=14;
76 if [ "$DBVERSION" = 14 ] ; then
77 sqlite3 $* ${db_name}.db <<END-OF-DATA
80 INSERT INTO Status (JobStatus,JobStatusLong,Severity) VALUES
81 ('I', 'Incomplete Job',25);
83 CREATE TABLE new_Media (
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,
126 VolABytes BIGINT UNSIGNED DEFAULT 0,
127 VolAPadding BIGINT UNSIGNED DEFAULT 0,
128 VolHoleBytes BIGINT UNSIGNED DEFAULT 0,
129 VolHoles INTEGER UNSIGNED DEFAULT 0,
132 INSERT 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;
134 ALTER TABLE new_Media RENAME TO Media;
135 CREATE INDEX inx8 ON Media (PoolId);
137 CREATE 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,
150 primary key (SnapshotId)
153 CREATE UNIQUE INDEX snapshot_idx ON Snapshot (Device, Volume, Name);
155 CREATE INDEX jobtdate_idx on JobHisto (JobTDate);
157 UPDATE Version SET VersionId=15;
166 if [ "$DBVERSION" -eq 15 ] ; then
167 sqlite3 $* ${db_name}.db <<END-OF-DATA
170 CREATE TABLE basefiles_temp (
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,
179 INSERT INTO basefiles_temp (BaseId, BaseJobId, JobId,
181 SELECT BaseId, BaseJobId, JobId, FileId, FileIndex
184 CREATE TABLE new_Media (
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,
238 INSERT 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;
240 ALTER TABLE new_Media RENAME TO Media;
241 CREATE INDEX inx8 ON Media (PoolId);
243 CREATE UNIQUE INDEX Media_Volumename_Id ON Media (VolumeName);
246 DROP TABLE BaseFiles;
247 ALTER TABLE basefiles_temp RENAME TO BaseFiles;
249 CREATE INDEX job_jobtdate_inx ON job (JobTDate);
250 ALTER TABLE Pool ADD COLUMN CacheRetention BIGINT DEFAULT 0;
252 UPDATE Version SET VersionId=16;
263 echo "WARNING: !!!! SQLite3 is no longer supported. !!!!"
264 echo "WARNING: !!!! Please switch to MySQL or PostgreSQL !!!!"
265 echo "WARNING: !!!! as soon as possible. !!!!"