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 10"
7 echo " which is needed to convert from Bacula version 1.38.x to 1.39.x or higher"
8 echo "Depending on the size of your database,"
9 echo "this script may take several minutes to run."
16 ${bindir}/${sqlite} $* bacula.db <<END-OF-DATA
19 CREATE TEMPORARY TABLE Media_backup (
20 MediaId INTEGER UNSIGNED AUTOINCREMENT,
21 VolumeName VARCHAR(128) NOT NULL,
22 Slot INTEGER DEFAULT 0,
23 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
24 MediaType VARCHAR(128) NOT NULL,
25 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
26 LabelType TINYINT DEFAULT 0,
27 FirstWritten DATETIME DEFAULT 0,
28 LastWritten DATETIME DEFAULT 0,
29 LabelDate DATETIME DEFAULT 0,
30 VolJobs INTEGER UNSIGNED DEFAULT 0,
31 VolFiles INTEGER UNSIGNED DEFAULT 0,
32 VolBlocks INTEGER UNSIGNED DEFAULT 0,
33 VolMounts INTEGER UNSIGNED DEFAULT 0,
34 VolBytes BIGINT UNSIGNED DEFAULT 0,
35 VolParts INTEGER UNSIGNED DEFAULT 0,
36 VolErrors INTEGER UNSIGNED DEFAULT 0,
37 VolWrites INTEGER UNSIGNED DEFAULT 0,
38 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
39 VolStatus VARCHAR(20) NOT NULL,
40 Enabled TINYINT DEFAULT 1,
41 Recycle TINYINT DEFAULT 0,
42 VolRetention BIGINT UNSIGNED DEFAULT 0,
43 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
44 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
45 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
46 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
47 InChanger TINYINT DEFAULT 0,
48 StorageId INTEGER UNSIGNED REFERENCES Storage,
49 DeviceId INTEGER UNSIGNED REFERENCES Device,
50 MediaAddressing TINYINT DEFAULT 0,
51 VolReadTime BIGINT UNSIGNED DEFAULT 0,
52 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
53 EndFile INTEGER UNSIGNED DEFAULT 0,
54 EndBlock INTEGER UNSIGNED DEFAULT 0,
55 LocationId INTEGER UNSIGNED REFERENCES Location,
56 RecycleCount INTEGER UNSIGNED DEFAULT 0,
57 InitialWrite DATETIME DEFAULT 0,
58 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
59 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
63 INSERT INTO Media_backup SELECT
64 MediaId, VolumeName, Slot, PoolId,
65 MediaType, LabelType, 0, FirstWritten, LastWritten,
66 LabelDate, VolJobs, VolFiles, VolBlocks,
67 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
68 VolCapacityBytes, VolStatus, 1, Recycle,
69 VolRetention, VolUseDuration, MaxVolJobs,
70 MaxVolFiles, MaxVolBytes, InChanger,
71 StorageId, 0, MediaAddressing,
72 VolReadTime, VolWriteTime, EndFile, EndBlock, 0, 0, 0, 0, 0
80 VolumeName VARCHAR(128) NOT NULL,
81 Slot INTEGER DEFAULT 0,
82 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
83 MediaType VARCHAR(128) NOT NULL,
84 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
85 LabelType TINYINT DEFAULT 0,
86 FirstWritten DATETIME DEFAULT 0,
87 LastWritten DATETIME DEFAULT 0,
88 LabelDate DATETIME DEFAULT 0,
89 VolJobs INTEGER UNSIGNED DEFAULT 0,
90 VolFiles INTEGER UNSIGNED DEFAULT 0,
91 VolBlocks INTEGER UNSIGNED DEFAULT 0,
92 VolMounts INTEGER UNSIGNED DEFAULT 0,
93 VolBytes BIGINT UNSIGNED DEFAULT 0,
94 VolParts INTEGER UNSIGNED DEFAULT 0,
95 VolErrors INTEGER UNSIGNED DEFAULT 0,
96 VolWrites INTEGER UNSIGNED DEFAULT 0,
97 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
98 VolStatus VARCHAR(20) NOT NULL,
99 Enabled TINYINT DEFAULT 1,
100 Recycle TINYINT DEFAULT 0,
101 VolRetention BIGINT UNSIGNED DEFAULT 0,
102 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
103 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
104 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
105 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
106 InChanger TINYINT DEFAULT 0,
107 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
108 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
109 MediaAddressing TINYINT DEFAULT 0,
110 VolReadTime BIGINT UNSIGNED DEFAULT 0,
111 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
112 EndFile INTEGER UNSIGNED DEFAULT 0,
113 EndBlock INTEGER UNSIGNED DEFAULT 0,
114 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
115 RecycleCount INTEGER UNSIGNED DEFAULT 0,
116 InitialWrite DATETIME DEFAULT 0,
117 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
118 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
124 MediaId, VolumeName, Slot, PoolId,
125 MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
126 LabelDate, VolJobs, VolFiles, VolBlocks,
127 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
128 VolCapacityBytes, VolStatus, Enabled, Recycle,
129 VolRetention, VolUseDuration, MaxVolJobs,
130 MaxVolFiles, MaxVolBytes,
131 InChanger, StorageId, DeviceId, MediaAddressing,
132 VolReadTime, VolWriteTime,
133 EndFile, EndBlock, LocationId, RecycleCount, InitialWrite,
134 ScratchPoolId, RecyclePoolId)
135 SELECT * FROM Media_backup;
138 DROP TABLE Media_backup;
139 CREATE INDEX inx8 ON Media (PoolId);
141 CREATE TEMPORARY TABLE job_backup
144 Job VARCHAR(128) NOT NULL,
145 Name VARCHAR(128) NOT NULL,
148 ClientId INTEGER REFERENCES Client DEFAULT 0,
149 JobStatus CHAR NOT NULL,
150 SchedTime DATETIME NOT NULL,
151 StartTime DATETIME DEFAULT 0,
152 EndTime DATETIME DEFAULT 0,
153 RealEndTime DATETIME DEFAULT 0,
154 JobTDate BIGINT UNSIGNED DEFAULT 0,
155 VolSessionId INTEGER UNSIGNED DEFAULT 0,
156 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
157 JobFiles INTEGER UNSIGNED DEFAULT 0,
158 JobBytes BIGINT UNSIGNED DEFAULT 0,
159 JobErrors INTEGER UNSIGNED DEFAULT 0,
160 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
161 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
162 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
163 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
164 PurgedFiles TINYINT DEFAULT 0,
165 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
181 Job VARCHAR(128) NOT NULL,
182 Name VARCHAR(128) NOT NULL,
185 ClientId INTEGER REFERENCES Client DEFAULT 0,
186 JobStatus CHAR NOT NULL,
187 SchedTime DATETIME NOT NULL,
188 StartTime DATETIME DEFAULT 0,
189 EndTime DATETIME DEFAULT 0,
190 RealEndTime DATETIME DEFAULT 0,
191 JobTDate BIGINT UNSIGNED DEFAULT 0,
192 VolSessionId INTEGER UNSIGNED DEFAULT 0,
193 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
194 JobFiles INTEGER UNSIGNED DEFAULT 0,
195 JobBytes BIGINT UNSIGNED DEFAULT 0,
196 JobErrors INTEGER UNSIGNED DEFAULT 0,
197 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
198 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
199 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
200 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
201 PurgedFiles TINYINT DEFAULT 0,
202 HasBase TINYINT DEFAULT 0,
205 CREATE INDEX inx6 ON Job (Name);
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)
213 SELECT * FROM Job_backup;
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 TEXT NOT NULL,
240 Cost INTEGER DEFAULT 0,
242 PRIMARY KEY(LocationId)
247 INSERT INTO Version (VersionId) VALUES (10);