3 # shell script to update SQLite from version 1.36 to 1.37.12
6 echo "This script will update a Bacula SQLite database from version 8 to 9"
7 echo "Depending on the size of your database,"
8 echo "this script may take several minutes to run."
15 ${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
17 CREATE TEMPORARY TABLE Media_backup (
18 MediaId INTEGER UNSIGNED AUTOINCREMENT,
19 VolumeName VARCHAR(128) NOT NULL,
20 Slot INTEGER DEFAULT 0,
21 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
22 MediaType VARCHAR(128) NOT NULL,
23 LabelType TINYINT DEFAULT 0,
24 FirstWritten DATETIME DEFAULT 0,
25 LastWritten DATETIME DEFAULT 0,
26 LabelDate DATETIME DEFAULT 0,
27 VolJobs INTEGER UNSIGNED DEFAULT 0,
28 VolFiles INTEGER UNSIGNED DEFAULT 0,
29 VolBlocks INTEGER UNSIGNED DEFAULT 0,
30 VolMounts INTEGER UNSIGNED DEFAULT 0,
31 VolBytes BIGINT UNSIGNED DEFAULT 0,
32 VolParts INTEGER UNSIGNED DEFAULT 0,
33 VolErrors INTEGER UNSIGNED DEFAULT 0,
34 VolWrites INTEGER UNSIGNED DEFAULT 0,
35 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
36 VolStatus VARCHAR(20) NOT NULL,
37 Recycle TINYINT DEFAULT 0,
38 VolRetention BIGINT UNSIGNED DEFAULT 0,
39 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
40 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
41 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
42 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
43 InChanger TINYINT DEFAULT 0,
44 StorageId INTEGER UNSIGNED REFERENCES Storage,
45 MediaAddressing TINYINT DEFAULT 0,
46 VolReadTime BIGINT UNSIGNED DEFAULT 0,
47 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
48 EndFile INTEGER UNSIGNED DEFAULT 0,
49 EndBlock INTEGER UNSIGNED DEFAULT 0,
53 INSERT INTO Media_backup SELECT
54 MediaId, VolumeName, Slot, PoolId,
55 MediaType, 0, FirstWritten, LastWritten,
56 LabelDate, VolJobs, VolFiles, VolBlocks,
57 VolMounts, VolBytes, 0, VolErrors, VolWrites,
58 VolCapacityBytes, VolStatus, Recycle,
59 VolRetention, VolUseDuration, MaxVolJobs,
60 MaxVolFiles, MaxVolBytes, InChanger, 0, MediaAddressing,
61 VolReadTime, VolWriteTime, EndFile, EndBlock
68 MediaId INTEGER UNSIGNED AUTOINCREMENT,
69 VolumeName VARCHAR(128) NOT NULL,
70 Slot INTEGER DEFAULT 0,
71 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
72 MediaType VARCHAR(128) NOT NULL,
73 LabelType TINYINT DEFAULT 0,
74 FirstWritten DATETIME DEFAULT 0,
75 LastWritten DATETIME DEFAULT 0,
76 LabelDate DATETIME DEFAULT 0,
77 VolJobs INTEGER UNSIGNED DEFAULT 0,
78 VolFiles INTEGER UNSIGNED DEFAULT 0,
79 VolBlocks INTEGER UNSIGNED DEFAULT 0,
80 VolMounts INTEGER UNSIGNED DEFAULT 0,
81 VolBytes BIGINT UNSIGNED DEFAULT 0,
82 VolParts INTEGER UNSIGNED DEFAULT 0,
83 VolErrors INTEGER UNSIGNED DEFAULT 0,
84 VolWrites INTEGER UNSIGNED DEFAULT 0,
85 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
86 VolStatus VARCHAR(20) NOT NULL,
87 Recycle TINYINT DEFAULT 0,
88 VolRetention BIGINT UNSIGNED DEFAULT 0,
89 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
90 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
91 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
92 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
93 InChanger TINYINT DEFAULT 0,
94 StorageId INTEGER UNSIGNED REFERENCES Storage,
95 MediaAddressing TINYINT DEFAULT 0,
96 VolReadTime BIGINT UNSIGNED DEFAULT 0,
97 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
98 EndFile INTEGER UNSIGNED DEFAULT 0,
99 EndBlock INTEGER UNSIGNED DEFAULT 0,
104 MediaId, VolumeName, Slot, PoolId,
105 MediaType, LabelType, FirstWritten, LastWritten,
106 LabelDate, VolJobs, VolFiles, VolBlocks,
107 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
108 VolCapacityBytes, VolStatus, Recycle,
109 VolRetention, VolUseDuration, MaxVolJobs,
110 MaxVolFiles, MaxVolBytes,
111 InChanger, StorageId, MediaAddressing,
112 VolReadTime, VolWriteTime,
114 SELECT * FROM Media_backup;
116 DROP TABLE Media_backup;
117 CREATE INDEX inx8 ON Media (PoolId);
120 CREATE TEMPORARY TABLE JobMedia_backup (
122 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
123 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
124 FirstIndex INTEGER UNSIGNED NOT NULL,
125 LastIndex INTEGER UNSIGNED NOT NULL,
126 StartFile INTEGER UNSIGNED DEFAULT 0,
127 EndFile INTEGER UNSIGNED DEFAULT 0,
128 StartBlock INTEGER UNSIGNED DEFAULT 0,
129 EndBlock INTEGER UNSIGNED DEFAULT 0,
130 VolIndex INTEGER UNSIGNED DEFAULT 0,
131 Copy INTEGER UNSIGNED DEFAULT 0,
132 Stripe INTEGER UNSIGNED DEFAULT 0,
133 PRIMARY KEY(JobMediaId)
136 INSERT INTO JobMedia_backup SELECT
137 JobMediaId, JobId, MediaId,
138 FirstIndex, LastIndex, StartFile,
139 EndFile, StartBlock, EndBlock,
145 CREATE TABLE JobMedia (
147 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
148 MediaId INTEGER UNSIGNED REFERENCES Media NOT NULL,
149 FirstIndex INTEGER UNSIGNED NOT NULL,
150 LastIndex INTEGER UNSIGNED NOT NULL,
151 StartFile INTEGER UNSIGNED DEFAULT 0,
152 EndFile INTEGER UNSIGNED DEFAULT 0,
153 StartBlock INTEGER UNSIGNED DEFAULT 0,
154 EndBlock INTEGER UNSIGNED DEFAULT 0,
155 VolIndex INTEGER UNSIGNED DEFAULT 0,
156 Copy INTEGER UNSIGNED DEFAULT 0,
157 Stripe INTEGER UNSIGNED DEFAULT 0,
158 PRIMARY KEY(JobMediaId)
161 INSERT INTO JobMedia (
162 JobMediaId, JobId, MediaId,
163 FirstIndex, LastIndex, StartFile,
164 EndFile, StartBlock, EndBlock,
165 VolIndex, Copy, Stripe)
166 SELECT * FROM JobMedia_backup;
168 DROP TABLE JobMedia_backup;
170 CREATE TEMPORARY TABLE Pool_backup (
172 Name VARCHAR(128) NOT NULL,
173 NumVols INTEGER UNSIGNED DEFAULT 0,
174 MaxVols INTEGER UNSIGNED DEFAULT 0,
175 UseOnce TINYINT DEFAULT 0,
176 UseCatalog TINYINT DEFAULT 1,
177 AcceptAnyVolume TINYINT DEFAULT 0,
178 VolRetention BIGINT UNSIGNED DEFAULT 0,
179 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
180 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
181 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
182 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
183 AutoPrune TINYINT DEFAULT 0,
184 Recycle TINYINT DEFAULT 0,
185 PoolType VARCHAR(20) NOT NULL,
186 LabelType TINYINT DEFAULT 0,
187 LabelFormat VARCHAR(128) NOT NULL,
188 Enabled TINYINT DEFAULT 1,
189 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
190 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
191 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
192 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
193 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
194 MigrationTime BIGINT UNSIGNED DEFAULT 0,
199 INSERT INTO Pool_backup SELECT
200 PoolId, Name, NumVols, MaxVols,
201 UseOnce, UseCatalog, AcceptAnyVolume,
202 VolRetention, VolUseDuration, MaxVolJobs,
203 MaxVolFiles, MaxVolBytes, AutoPrune,
204 Recycle, PoolType, 0, LabelFormat,
205 Enabled, ScratchPoolId, RecyclePoolId,
213 Name VARCHAR(128) NOT NULL,
214 NumVols INTEGER UNSIGNED DEFAULT 0,
215 MaxVols INTEGER UNSIGNED DEFAULT 0,
216 UseOnce TINYINT DEFAULT 0,
217 UseCatalog TINYINT DEFAULT 1,
218 AcceptAnyVolume TINYINT DEFAULT 0,
219 VolRetention BIGINT UNSIGNED DEFAULT 0,
220 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
221 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
222 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
223 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
224 AutoPrune TINYINT DEFAULT 0,
225 Recycle TINYINT DEFAULT 0,
226 PoolType VARCHAR(20) NOT NULL,
227 LabelType TINYINT DEFAULT 0,
228 LabelFormat VARCHAR(128) NOT NULL,
229 Enabled TINYINT DEFAULT 1,
230 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
231 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
232 NextPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
233 MigrationHighBytes BIGINT UNSIGNED DEFAULT 0,
234 MigrationLowBytes BIGINT UNSIGNED DEFAULT 0,
235 MigrationTime BIGINT UNSIGNED DEFAULT 0,
241 PoolId, Name, NumVols, MaxVols,
242 UseOnce, UseCatalog, AcceptAnyVolume,
243 VolRetention, VolUseDuration, MaxVolJobs,
244 MaxVolFiles, MaxVolBytes, AutoPrune,
245 Recycle, PoolType, LabelType, LabelFormat,
246 Enabled, ScratchPoolId, RecyclePoolId,
247 NextPoolId, MigrationHighBytes,
248 MigrationLowBytes, MigrationTime )
249 SELECT * FROM Pool_backup;
251 DROP TABLE Pool_backup;
253 CREATE TABLE MediaType (
255 MediaType VARCHAR(128) NOT NULL,
256 ReadOnly TINYINT DEFAULT 0,
257 PRIMARY KEY(MediaTypeId)
260 CREATE TABLE Storage (
262 Name VARCHAR(128) NOT NULL,
263 AutoChanger TINYINT DEFAULT 0,
264 PRIMARY KEY(StorageId)
267 CREATE TABLE Device (
269 Name VARCHAR(128) NOT NULL,
270 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
271 StorageId INTEGER UNSIGNED REFERENCES Storage,
272 DevMounts INTEGER UNSIGNED DEFAULT 0,
273 DevReadBytes BIGINT UNSIGNED DEFAULT 0,
274 DevWriteBytes BIGINT UNSIGNED DEFAULT 0,
275 DevReadBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
276 DevWriteBytesSinceCleaning BIGINT UNSIGNED DEFAULT 0,
277 DevReadTime BIGINT UNSIGNED DEFAULT 0,
278 DevWriteTime BIGINT UNSIGNED DEFAULT 0,
279 DevReadTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
280 DevWriteTimeSinceCleaning BIGINT UNSIGNED DEFAULT 0,
281 CleaningDate DATETIME DEFAULT 0,
282 CleaningPeriod BIGINT UNSIGNED DEFAULT 0,
283 PRIMARY KEY(DeviceId)
287 CREATE TABLE Status (
288 JobStatus CHAR(1) NOT NULL,
290 PRIMARY KEY (JobStatus)
293 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
294 ('C', 'Created, not yet running');
295 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
297 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
299 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
300 ('T', 'Completed successfully');
301 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
302 ('E', 'Terminated with errors');
303 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
304 ('e', 'Non-fatal error');
305 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
306 ('f', 'Fatal error');
307 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
308 ('D', 'Verify found differences');
309 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
310 ('A', 'Canceled by user');
311 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
312 ('F', 'Waiting for Client');
313 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
314 ('S', 'Waiting for Storage daemon');
315 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
316 ('m', 'Waiting for new media');
317 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
318 ('M', 'Waiting for media mount');
319 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
320 ('s', 'Waiting for storage resource');
321 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
322 ('j', 'Waiting for job resource');
323 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
324 ('c', 'Waiting for client resource');
325 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
326 ('d', 'Waiting on maximum jobs');
327 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
328 ('t', 'Waiting on start time');
329 INSERT INTO Status (JobStatus,JobStatusLong) VALUES
330 ('p', 'Waiting on higher priority jobs');
334 INSERT INTO Version (VersionId) VALUES (9);