3 # shell script to update SQLite from version 1.38 to 1.39
6 echo "This script will update a Bacula SQLite database from version 9 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
18 CREATE TEMPORARY TABLE Media_backup (
19 MediaId INTEGER UNSIGNED AUTOINCREMENT,
20 VolumeName VARCHAR(128) NOT NULL,
21 Slot INTEGER DEFAULT 0,
22 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
23 MediaType VARCHAR(128) NOT NULL,
24 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
25 LabelType TINYINT DEFAULT 0,
26 FirstWritten DATETIME DEFAULT 0,
27 LastWritten DATETIME DEFAULT 0,
28 LabelDate DATETIME DEFAULT 0,
29 VolJobs INTEGER UNSIGNED DEFAULT 0,
30 VolFiles INTEGER UNSIGNED DEFAULT 0,
31 VolBlocks INTEGER UNSIGNED DEFAULT 0,
32 VolMounts INTEGER UNSIGNED DEFAULT 0,
33 VolBytes BIGINT UNSIGNED DEFAULT 0,
34 VolParts INTEGER UNSIGNED DEFAULT 0,
35 VolErrors INTEGER UNSIGNED DEFAULT 0,
36 VolWrites INTEGER UNSIGNED DEFAULT 0,
37 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
38 VolStatus VARCHAR(20) NOT NULL,
39 Enabled TINYINT DEFAULT 1,
40 Recycle TINYINT DEFAULT 0,
41 VolRetention BIGINT UNSIGNED DEFAULT 0,
42 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
43 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
44 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
45 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
46 InChanger TINYINT DEFAULT 0,
47 StorageId INTEGER UNSIGNED REFERENCES Storage,
48 DeviceId INTEGER UNSIGNED REFERENCES Device,
49 MediaAddressing TINYINT DEFAULT 0,
50 VolReadTime BIGINT UNSIGNED DEFAULT 0,
51 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
52 EndFile INTEGER UNSIGNED DEFAULT 0,
53 EndBlock INTEGER UNSIGNED DEFAULT 0,
54 LocationId INTEGER UNSIGNED REFERENCES Location,
55 RecycleCount INTEGER UNSIGNED DEFAULT 0,
56 InitialWrite DATETIME DEFAULT 0,
57 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
58 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
62 INSERT INTO Media_backup SELECT
63 MediaId, VolumeName, Slot, PoolId,
64 MediaType, LabelType, 0, FirstWritten, LastWritten,
65 LabelDate, VolJobs, VolFiles, VolBlocks,
66 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
67 VolCapacityBytes, VolStatus, 1, Recycle,
68 VolRetention, VolUseDuration, MaxVolJobs,
69 MaxVolFiles, MaxVolBytes, InChanger,
70 StorageId, 0, MediaAddressing,
71 VolReadTime, VolWriteTime, EndFile, EndBlock, 0, 0, 0, 0, 0
78 MediaId INTEGER UNSIGNED AUTOINCREMENT,
79 VolumeName VARCHAR(128) NOT NULL,
80 Slot INTEGER DEFAULT 0,
81 PoolId INTEGER UNSIGNED REFERENCES Pool NOT NULL,
82 MediaType VARCHAR(128) NOT NULL,
83 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType NOT NULL,
84 LabelType TINYINT DEFAULT 0,
85 FirstWritten DATETIME DEFAULT 0,
86 LastWritten DATETIME DEFAULT 0,
87 LabelDate DATETIME DEFAULT 0,
88 VolJobs INTEGER UNSIGNED DEFAULT 0,
89 VolFiles INTEGER UNSIGNED DEFAULT 0,
90 VolBlocks INTEGER UNSIGNED DEFAULT 0,
91 VolMounts INTEGER UNSIGNED DEFAULT 0,
92 VolBytes BIGINT UNSIGNED DEFAULT 0,
93 VolParts INTEGER UNSIGNED DEFAULT 0,
94 VolErrors INTEGER UNSIGNED DEFAULT 0,
95 VolWrites INTEGER UNSIGNED DEFAULT 0,
96 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
97 VolStatus VARCHAR(20) NOT NULL,
98 Enabled TINYINT DEFAULT 1,
99 Recycle TINYINT DEFAULT 0,
100 VolRetention BIGINT UNSIGNED DEFAULT 0,
101 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
102 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
103 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
104 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
105 InChanger TINYINT DEFAULT 0,
106 StorageId INTEGER UNSIGNED REFERENCES Storage,
107 DeviceId INTEGER UNSIGNED REFERENCES Device,
108 MediaAddressing TINYINT DEFAULT 0,
109 VolReadTime BIGINT UNSIGNED DEFAULT 0,
110 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
111 EndFile INTEGER UNSIGNED DEFAULT 0,
112 EndBlock INTEGER UNSIGNED DEFAULT 0,
113 LocationId INTEGER UNSIGNED REFERENCES Location,
114 RecycleCount INTEGER UNSIGNED DEFAULT 0,
115 InitialWrite DATETIME DEFAULT 0,
116 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
117 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
123 MediaId, VolumeName, Slot, PoolId,
124 MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
125 LabelDate, VolJobs, VolFiles, VolBlocks,
126 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
127 VolCapacityBytes, VolStatus, Enabled, Recycle,
128 VolRetention, VolUseDuration, MaxVolJobs,
129 MaxVolFiles, MaxVolBytes,
130 InChanger, StorageId, DeviceId, MediaAddressing,
131 VolReadTime, VolWriteTime,
132 EndFile, EndBlock, LocationId, RecycleCount, InitialWrite,
133 ScratchPoolId, RecyclePoolId)
134 SELECT * FROM Media_backup;
137 DROP TABLE Media_backup;
138 CREATE INDEX inx8 ON Media (PoolId);
140 CREATE TEMPORARY TABLE job_backup
143 Job VARCHAR(128) NOT NULL,
144 Name VARCHAR(128) NOT NULL,
147 ClientId INTEGER REFERENCES Client DEFAULT 0,
148 JobStatus CHAR NOT NULL,
149 SchedTime DATETIME NOT NULL,
150 StartTime DATETIME DEFAULT 0,
151 EndTime DATETIME DEFAULT 0,
152 RealEndTime DATETIME DEFAULT 0,
153 JobTDate BIGINT UNSIGNED DEFAULT 0,
154 VolSessionId INTEGER UNSIGNED DEFAULT 0,
155 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
156 JobFiles INTEGER UNSIGNED DEFAULT 0,
157 JobBytes BIGINT UNSIGNED DEFAULT 0,
158 JobErrors INTEGER UNSIGNED DEFAULT 0,
159 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
160 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
161 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
162 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
163 PurgedFiles TINYINT DEFAULT 0,
164 HasBase TINYINT DEFAULT 0,
168 INSERT INTO Job_backup SELECT
169 JobId, Job, Name, Type, Level, ClientId, JobStatus,
170 SchedTime, StartTime, EndTime, 0,
171 JobTDate, VolSessionId, VolSessionTime,
172 JobFiles, JobBytes, JobErrors, JobMissingFiles,
173 PoolId, FileSetId, 0, PurgedFiles, HasBase,
179 CREATE TEMPORARY TABLE Job
182 Job VARCHAR(128) NOT NULL,
183 Name VARCHAR(128) NOT NULL,
186 ClientId INTEGER REFERENCES Client DEFAULT 0,
187 JobStatus CHAR NOT NULL,
188 SchedTime DATETIME NOT NULL,
189 StartTime DATETIME DEFAULT 0,
190 EndTime DATETIME DEFAULT 0,
191 RealEndTime DATETIME DEFAULT 0,
192 JobTDate BIGINT UNSIGNED DEFAULT 0,
193 VolSessionId INTEGER UNSIGNED DEFAULT 0,
194 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
195 JobFiles INTEGER UNSIGNED DEFAULT 0,
196 JobBytes BIGINT UNSIGNED DEFAULT 0,
197 JobErrors INTEGER UNSIGNED DEFAULT 0,
198 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
199 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
200 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
201 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
202 PurgedFiles TINYINT DEFAULT 0,
203 HasBase TINYINT DEFAULT 0,
207 INSERT INTO Job SELECT
208 JobId, Job, Name, Type, Level, ClientId, JobStatus,
209 SchedTime, StartTime, EndTime, RealEndTime,
210 JobTDate, VolSessionId, VolSessionTime,
211 JobFiles, JobBytes, JobErrors, JobMissingFiles,
212 PoolId, FileSetId, PriorJobId, PurgedFiles, HasBase
215 DROP TABLE Job_backup;
217 CREATE TABLE LocationLog (
219 Date DATETIME NOT NULL,
220 Comment TEXT NOT NULL,
221 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
222 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
223 NewVolStatus VARCHAR(20) NOT NULL,
224 NewEnabled TINYINT NOT NULL,
225 PRIMARY KEY(LocLogId)
230 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
231 Time DATETIME NOT NULL,
232 LogText TEXT NOT NULL,
235 CREATE INDEX LogInx1 ON File (JobId);
237 CREATE TABLE Location (
239 Location VARCHAR(128) NOT NULL,
240 Cost INTEGER DEFAULT 0,
242 PRIMARY KEY(LocationId)
247 INSERT INTO Version (VersionId) VALUES (10);