3 # shell script to update SQLite from version 1.38 to 2.0
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 2.0.x or higher"
8 echo "Depending on the size of your database,"
9 echo "this script may take several minutes to run."
17 ${bindir}/${sqlite} $* ${db_name}.db <<END-OF-DATA
20 CREATE TEMPORARY TABLE Media_backup (
21 MediaId INTEGER UNSIGNED AUTOINCREMENT,
22 VolumeName VARCHAR(128) NOT NULL,
23 Slot INTEGER DEFAULT 0,
24 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
25 MediaType VARCHAR(128) NOT NULL,
26 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
27 LabelType TINYINT DEFAULT 0,
28 FirstWritten DATETIME DEFAULT 0,
29 LastWritten DATETIME DEFAULT 0,
30 LabelDate DATETIME DEFAULT 0,
31 VolJobs INTEGER UNSIGNED DEFAULT 0,
32 VolFiles INTEGER UNSIGNED DEFAULT 0,
33 VolBlocks INTEGER UNSIGNED DEFAULT 0,
34 VolMounts INTEGER UNSIGNED DEFAULT 0,
35 VolBytes BIGINT UNSIGNED DEFAULT 0,
36 VolParts INTEGER UNSIGNED DEFAULT 0,
37 VolErrors INTEGER UNSIGNED DEFAULT 0,
38 VolWrites INTEGER UNSIGNED DEFAULT 0,
39 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
40 VolStatus VARCHAR(20) NOT NULL,
41 Enabled TINYINT DEFAULT 1,
42 Recycle TINYINT DEFAULT 0,
43 VolRetention BIGINT UNSIGNED DEFAULT 0,
44 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
45 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
46 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
47 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
48 InChanger TINYINT DEFAULT 0,
49 StorageId INTEGER UNSIGNED REFERENCES Storage,
50 DeviceId INTEGER UNSIGNED REFERENCES Device,
51 MediaAddressing TINYINT DEFAULT 0,
52 VolReadTime BIGINT UNSIGNED DEFAULT 0,
53 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
54 EndFile INTEGER UNSIGNED DEFAULT 0,
55 EndBlock INTEGER UNSIGNED DEFAULT 0,
56 LocationId INTEGER UNSIGNED REFERENCES Location,
57 RecycleCount INTEGER UNSIGNED DEFAULT 0,
58 InitialWrite DATETIME DEFAULT 0,
59 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
60 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
64 INSERT INTO Media_backup SELECT
65 MediaId, VolumeName, Slot, PoolId,
66 MediaType, LabelType, 0, FirstWritten, LastWritten,
67 LabelDate, VolJobs, VolFiles, VolBlocks,
68 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
69 VolCapacityBytes, VolStatus, 1, Recycle,
70 VolRetention, VolUseDuration, MaxVolJobs,
71 MaxVolFiles, MaxVolBytes, InChanger,
72 StorageId, 0, MediaAddressing,
73 VolReadTime, VolWriteTime, EndFile, EndBlock, 0, 0, 0, 0, 0
81 VolumeName VARCHAR(128) NOT NULL,
82 Slot INTEGER DEFAULT 0,
83 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
84 MediaType VARCHAR(128) NOT NULL,
85 MediaTypeId INTEGER UNSIGNED REFERENCES MediaType DEFAULT 0,
86 LabelType TINYINT DEFAULT 0,
87 FirstWritten DATETIME DEFAULT 0,
88 LastWritten DATETIME DEFAULT 0,
89 LabelDate DATETIME DEFAULT 0,
90 VolJobs INTEGER UNSIGNED DEFAULT 0,
91 VolFiles INTEGER UNSIGNED DEFAULT 0,
92 VolBlocks INTEGER UNSIGNED DEFAULT 0,
93 VolMounts INTEGER UNSIGNED DEFAULT 0,
94 VolBytes BIGINT UNSIGNED DEFAULT 0,
95 VolParts INTEGER UNSIGNED DEFAULT 0,
96 VolErrors INTEGER UNSIGNED DEFAULT 0,
97 VolWrites INTEGER UNSIGNED DEFAULT 0,
98 VolCapacityBytes BIGINT UNSIGNED DEFAULT 0,
99 VolStatus VARCHAR(20) NOT NULL,
100 Enabled TINYINT DEFAULT 1,
101 Recycle TINYINT DEFAULT 0,
102 VolRetention BIGINT UNSIGNED DEFAULT 0,
103 VolUseDuration BIGINT UNSIGNED DEFAULT 0,
104 MaxVolJobs INTEGER UNSIGNED DEFAULT 0,
105 MaxVolFiles INTEGER UNSIGNED DEFAULT 0,
106 MaxVolBytes BIGINT UNSIGNED DEFAULT 0,
107 InChanger TINYINT DEFAULT 0,
108 StorageId INTEGER UNSIGNED REFERENCES Storage DEFAULT 0,
109 DeviceId INTEGER UNSIGNED REFERENCES Device DEFAULT 0,
110 MediaAddressing TINYINT DEFAULT 0,
111 VolReadTime BIGINT UNSIGNED DEFAULT 0,
112 VolWriteTime BIGINT UNSIGNED DEFAULT 0,
113 EndFile INTEGER UNSIGNED DEFAULT 0,
114 EndBlock INTEGER UNSIGNED DEFAULT 0,
115 LocationId INTEGER UNSIGNED REFERENCES Location DEFAULT 0,
116 RecycleCount INTEGER UNSIGNED DEFAULT 0,
117 InitialWrite DATETIME DEFAULT 0,
118 ScratchPoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
119 RecyclePoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
125 MediaId, VolumeName, Slot, PoolId,
126 MediaType, MediaTypeId, LabelType, FirstWritten, LastWritten,
127 LabelDate, VolJobs, VolFiles, VolBlocks,
128 VolMounts, VolBytes, VolParts, VolErrors, VolWrites,
129 VolCapacityBytes, VolStatus, Enabled, Recycle,
130 VolRetention, VolUseDuration, MaxVolJobs,
131 MaxVolFiles, MaxVolBytes,
132 InChanger, StorageId, DeviceId, MediaAddressing,
133 VolReadTime, VolWriteTime,
134 EndFile, EndBlock, LocationId, RecycleCount, InitialWrite,
135 ScratchPoolId, RecyclePoolId)
136 SELECT * FROM Media_backup;
139 DROP TABLE Media_backup;
140 CREATE INDEX inx8 ON Media (PoolId);
142 CREATE TEMPORARY TABLE job_backup
145 Job VARCHAR(128) NOT NULL,
146 Name VARCHAR(128) NOT NULL,
149 ClientId INTEGER REFERENCES Client DEFAULT 0,
150 JobStatus CHAR NOT NULL,
151 SchedTime DATETIME NOT NULL,
152 StartTime DATETIME DEFAULT 0,
153 EndTime DATETIME DEFAULT 0,
154 RealEndTime DATETIME DEFAULT 0,
155 JobTDate BIGINT UNSIGNED DEFAULT 0,
156 VolSessionId INTEGER UNSIGNED DEFAULT 0,
157 VolSessionTime INTEGER UNSIGNED DEFAULT 0,
158 JobFiles INTEGER UNSIGNED DEFAULT 0,
159 JobBytes BIGINT UNSIGNED DEFAULT 0,
160 JobErrors INTEGER UNSIGNED DEFAULT 0,
161 JobMissingFiles INTEGER UNSIGNED DEFAULT 0,
162 PoolId INTEGER UNSIGNED REFERENCES Pool DEFAULT 0,
163 FileSetId INTEGER UNSIGNED REFERENCES FileSet DEFAULT 0,
164 PriorJobId INTEGER UNSIGNED REFERENCES Job DEFAULT 0,
165 PurgedFiles TINYINT DEFAULT 0,
166 HasBase TINYINT DEFAULT 0
169 INSERT INTO Job_backup SELECT
170 JobId, Job, Name, Type, Level, ClientId, JobStatus,
171 SchedTime, StartTime, EndTime, 0,
172 JobTDate, VolSessionId, VolSessionTime,
173 JobFiles, JobBytes, JobErrors, JobMissingFiles,
174 PoolId, FileSetId, 0, PurgedFiles, HasBase
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,
206 CREATE INDEX inx6 ON Job (Name);
209 JobId, Job, Name, Type, Level, ClientId, JobStatus,
210 SchedTime, StartTime, EndTime, RealEndTime,
211 JobTDate, VolSessionId, VolSessionTime,
212 JobFiles, JobBytes, JobErrors, JobMissingFiles,
213 PoolId, FileSetId, PriorJobId, PurgedFiles, HasBase)
214 SELECT * FROM Job_backup;
216 DROP TABLE Job_backup;
218 CREATE TABLE LocationLog (
220 Date DATETIME NOT NULL,
221 Comment TEXT NOT NULL,
222 MediaId INTEGER UNSIGNED REFERENCES Media DEFAULT 0,
223 LocationId INTEGER UNSIGNED REFERENCES LocationId DEFAULT 0,
224 NewVolStatus VARCHAR(20) NOT NULL,
225 NewEnabled TINYINT NOT NULL,
226 PRIMARY KEY(LocLogId)
231 JobId INTEGER UNSIGNED REFERENCES Job NOT NULL,
232 Time DATETIME NOT NULL,
233 LogText TEXT NOT NULL,
236 CREATE INDEX LogInx1 ON File (JobId);
238 CREATE TABLE Location (
240 Location TEXT NOT NULL,
241 Cost INTEGER DEFAULT 0,
243 PRIMARY KEY(LocationId)
248 INSERT INTO Version (VersionId) VALUES (10);